ER Diagram in DBMS: The ER diagram or the Entity relationship diagram is basically used to express the logical structure of a database in graphical form. There is a quote – “A picture is worth a thousand words“. So, anything which is expressed graphically or pictorially is more comprehensible and also easy to understand. That is why a graphical or pictorial representation of a database plays a very important role while defining a database structure. So, now let’s start with the basic components of an ER diagram in DBMS.
Components of ER Diagram in DBMS:
- Double Rectangle
- Double Ellipse
- Double Lines
- Dashed Ellipse
Each component of the ER diagram mentioned above has a different meaning. Normally each entity is denoted by rectangles and each and every attributes that an entity has is denoted by the ellipse.
For example, student and books are the two entities. Both of them may have their own attributes. The student entity may have attributes like student_name, roll_no, address, date_of_birth, phone_number etc. And on the other hand, the books entity may have attributes like book_name, ISBN, author, price etc.
Diamonds are used for defining the relationship between two or more entities. In the above example, if we consider “student reads books” then “reads” will be the relationship between the two entities student and books. All entities with their attributes and all the entities with relationships are linked by lines.
These 4 components ie. Rectangle, Ellipse, Diamonds and lines are called the basic component of an ER diagram in DBMS. In addition to these 4, we have also another 4 components – Double Ellipse, Dashed Ellipse, Double lines, Double Rectangles.
The double ellipse is used to define multivalued attributes and the dashed ellipse is used for derived attributes. In the above example, the phone_number and the author are the multivalued attributes. Because a student may have more than one phone_number and also a book may have multiple authors. So, they are denoting with the double ellipse in the above ER diagram.
On the other hand, we have placed the age attribute within the dashed ellipse. Because if we know the date of birth of a student we can easily calculate the age of a student. That is why it is a derived attribute.
Now, look at the address attribute. It is subdivided into 3 parts- city, state and pin. So, if any attribute which can be subdivided into many parts, then it will be called as a composite attribute.
The double lines are used to define the total participation between the different entities and relationships. The double rectangle defines the week entity. Week entities do not have any primary keys but they can have partial keys.
Types of Relationships in ER Diagram:
ER diagram in DBMS can have 4 types of relationships. They are –
One to One (1: 1)
Suppose we have 2 entities named department and manager. Since we know that each department can have only one manager, so it will be represented as one to one relationship in the ER diagram.
One to Many (1: N)
Consider 2 entities father and children. So, one father may have many children. So, this relationship – “father has the child” is considered as a one to many relationships in DBMS.
Many to One (N: 1)
In the above example, we are considering two entities – employee and company. So, one company normally has many employees or we can say, many employees can work for one company. That is why it is a many to one relationship.
Many to Many (N: N)
“Student studies subject” – this is a simple example of many to many relationships. Because many students can study more than one subjects i.e. many subjects.