ER Diagram to Create Database Schema Made Simple: What You Need to Know
I’m going to walk you through ER (entity relationship) diagrams in this blog: What is an ER Diagram? How to create an ER Diagram ? and Why do we need it? One of the most important things to do when developing a database is to construct a database schema, which is all the different tables and the different attributes on those tables. An ER diagram can be used to bridge the gap between database or storage requirements and the database schema that will be implemented in the database management system.
— I’m sure you must be wondering Why do we need to use ER Diagrams in DBMS?
We utilize ER diagrams for a variety of reasons, some of which are listed below:
- Gives us a logical picture of our database and assists us in visualizing it.
- Using attribute, relation, and entity, it assists us in determining the relationship between distinct tables.
- Give a high-level overview of the database, as well as the types of data that will be stored.
Over here I have these data Requirements of a company and what this document does is it describes all of the different data, all the pieces of information and the relationships between those pieces of information and this is an excellent example of a document that one might get if they’re working for a company and want you to design a database. Let’s say they want the person to create a database to store information about a company.
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. We'll also want to keep track of when the current manager started as a manager.An employee can act as a supervisor for other employees at the branch; an employee may also serve as the supervisor for employees at other branches. An employee can have at most one supervisor.A branch may handle several clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.Employees can work with clients controlled by their branch to sell them stuff. If necessary, multiple employees can work with the same client. Well, want to keep track of how many dollars' worth of stuff each employee sells to each client they work with.Many branches will need to work with suppliers to buy inventory. For each supplier, we'll keep track of their name and the type of product they're selling at the branch. A single supplier may supply products to multiple branches.
let’s go ahead and take a look !
1 — The company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it.
The company is divided into branches. Every branch has its own number and name .So, branch is going to be our entity, and it’s going to have two attributes, a branch ID which is going to be our primary key cause it has a unique number and a branch name .
2 — The company makes its money by selling to clients. Each client has a name and a unique number to identify it.
The corporation then makes money by selling to clients. As a result, a new entity is created. Each client is identified by a name and a unique number. So, we have our client, who has a client ID that identifies them, as well as their client name, which is simply the name.
3 — The foundation of the company is its employees. Each employee has a name, birthday, sex, salary and a unique number.
Over here, The entity is employee, and we have the employee ID, which is the primary key, birthday, and name, so we get first and last name, salary and then sex. We also have a derived attribute which is going to be the age that can be derived from the employee’s birth date and how old they are at any given point.
4 — An employee can work for one branch at a time, and each branch will be managed by one of the employees. We’ll also want to keep track of when the current manager started as a manager.
It says the employee can work for one branch at a time. So, this have a relationship(cardinality 1:N), i.e. “Works For”. This means an employee over here can work for a branch, and a branch can have an employee working for them; it is defined as a TOTAL PARTICIPATION (ref: Glossary).
So, next we have another relationship (cardinality 1:1). It says “each branch will be managed by one of the employees that work there. We’ll also want to keep track of when the current manager started as manager”. An attribute is defined (start_date) to keep track of when the employee started as manager. All branches must have someone managing them so, it’ll be a Total participation whereas not all employees need to be managers of a branch. Thus, it is defined as SINGLE PARTICIPATION OR PARTIAL PARTICIPATION (ref: Glossary).
5 — An employee can act as a supervisor for other employees at the branch; an employee may also serve as the supervisor for employees at other branches. An employee can have at most one supervisor.
Employee can have at most one supervisor. So, over here we get this supervision relationship. The supervision relationship is actually a relationship that an employee has to itself. So, this is a relationship between employees. employee can be supervised by another employee and an employee can be the supervisor of another employee.
6 — A branch may handle several clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.
It’s a relationship(cardinality 1:N) between branch and client. The client has a total participation because every client must be handled by a branch but , the branch has a partial participation, which means that not all branches need to have clients, Maybe there’s a corporate branch that doesn’t work with any clients.
7 — Employees can work with clients controlled by their branch to sell them stuff. If necessary, multiple employees can work with the same client. Well, want to keep track of how many dollars’ worth of stuff each employee sells to each client they work with.
This is a relationship(cardinality N:M) between employees and clients. All clients need to interact with the branch through an employee so , a total participation with the employee but not all employees need to interact with clients.
Company wants to keep track of how many dollars worth of stuff each employee sells to each client they work with !
On this works with relationship ,a sales attribute is defined So, the employee can sell to the client and the client can buy from the employee.
8 — Many branches will need to work with suppliers to buy inventory. For each supplier, we’ll keep track of their name and the type of product they’re selling at the branch. A single supplier may supply products to multiple branches.
Here , The need is to use a weak entity and an identifying relationship. The weak entity is “branch supplier”. It has a supplier name and a supply type, but the branch supplier is going to supply a specific branch. To keep track of which branch suppliers are supplying which branches. we’re going to have to use this identifying relationship that means the branch supplier supplies a branch and a branch gets supplied by a supplier.
This is basically our ER diagram , that is acquired from the “requirements document” and been able to map out all of the different entities, all the different attributes on the entities and all the different relationships. An ER diagram is just linking all the information together to visually represent it.
Now ,what we can do is take this ER diagram and depending on the different relationships, the different cardinality ratios, the different participations. let’s go ahead and convert this into a database schema !
STEP 1: Mapping of regular entity types
For each regular entity types create a relation (table) that includes all the simple attributes of that entity.
Step 2: Mapping of weak entity types
For each weak entity type create a relation that includes all simple attributes of the weak entity and the primary key for the new relation should be the partial key of the weak entity plus the primary key of its owner.
Step 3: Mapping of binary 1 to 1 relationship types
Include one side of the relationship as a foreign key in the other favor total participation.
Step 4: Mapping of binary 1 to N relationship types
Include the 1’s side of primary key as a foreign key on the N side relation.
Step 5: Mapping of binary M to N relationship types
Create a new relation (table) who’s primary key is the combination of both entities’ primary key’s also, include any relationship attributes.
Glossary
♦Entity — Entity is just an object that we want to model and store information about.
♦Attributes — Attributes are specific pieces of information about an entity.
♦Primary key — A primary key is going to be an attribute that’s going to uniquely identify an entry in the database table. a primary key is just like a normal attribute, but we’re going to underline.
♦Weak entity — an entity that cannot be uniquely identified by its attributes alone.
♦Relationship Cardinality — the number of instances of an entity from a relation that can be associated with the relation.
♦Multi-valued attribute — an attribute that can have more than one value . It is just like an attribute but with an extra circle.
♦Multiple Entities — you can define more than one entity in the diagram.
♦Derived attribute — an attribute that can be derived from the other attributes.
♦Composite attribute — an attribute that can be broken up into sub attributes .
There’s only going to be five steps that is needed to convert this into relations. But, if there is more advanced types of ER diagrams, then there are going to be more steps. Each of the steps is in itself a database table.
This sums up our ER diagram , if you have a very simple set of database storage or requirements, then obviously the schema is going to be very simple and you might not need something like an ER diagram. But with something like this, an ER diagram is hugely useful. We have seen different types of relationship ,entities and attributes and linking all the data requirements with each other that helps us to make a database schema which in fact will ease the work in SQL while creating a database.
let’s take a look into The Quickest Way to Create Company Database Using SQL to learn more about how to create a database using a query language.
