In this tutorial I will try to demonstrate how we can draw an ER diagram on the basis of some data requirements.

Consider this document it’s called company data requirements and it describes all of the pieces of data, information and the relationships them. This is a good example of a document if you want to design a database for a company.

Let’s say that they want you to design a database to store information about a company well they might give you such document and then your job would be to take this document and convert it into a database schema which you can then store information in and do all such sort of stuff.

This tutorial will help you convert a document
into an ER diagram.

let’s summarize the above document:

the company is organized into branches each branch has a unique number a name and a particular employee who manages it. The company makes its money by selling to clients each client has a name and a unique number to identify it. The foundation of the company is its
employees each employee has a name, birthday, sex, salary and a unique number.
An employee can work for one branch at a time and each branch will be managed by one of the employees that work there. At the branch an employee may also act as a supervisor for other employees.

There’s a lot
more so if you’re given a
document like this how do you go about
converting this into a database schema
so the first thing we want to do is
create an ER diagram.

Identifying Entities

The company is organized into branches each branch has a unique number and a name so notice branch is going to be our entity right. We are defining an entity branch and it’s going to have two attributes a branch ID whichwill be our primary key and then obviously the branch name.

Next we have the company makes its money by selling to clients so clients
will be another entity. Like branch Each client has a name and a unique number to identify it.

The foundation of the company is
its employees each employee has a name,
birthday, sex, salary and employee ID as primary key.

Finding RelationShips

As employee can work for one branch at a time so over here we have a
relationship. A branch can have any number of employees working for it and an employee can work for one branch that’s
what that cardinality relationship is defining here.

Next we have another relationship that says each
branch will be managed by one of the
employees we also have another
that is employee can have at most
one supervisor. This supervision
is actually a relationship that an employee has to itself.

We have another relationship as a branch may handle a
number of clients however a single client may only be handled by one branch at a time so we have this new
relationship between the branch and the
client also we have our cardinality relationship which is a branch can handle any number of clients.

Since multiple employees can work with the same client so now we have a relationship between employees and clients.

We have our final little section of this requirements document that says many branches will need to work with suppliers to buy inventory for each
supplier will keep track of their name. So we have a new Supplier entity and a relationship of supplies between branch and branch supplier.

This is our final ER diagram:

We’ve been able to map out all of the different entities, attributes on the entities in the above diagram. The entity relationship diagram is an easy way to visualize tables, columns, and their relationship. so what we can do now is we can take this ER diagram and depending on the different relationships and the different cardinality ratios we can convert this into a database schema.

credits – mikedane.com