ER Data Model

Introduction

We have seen what ER diagram is and what its basic concepts are. Now let us see how to draw the ER diagram using these concepts. This diagram is the first step in designing the database.

ER Diagram Symbols

Since ER diagram is the pictorial representation of real world objects, it involves various symbols and notation to draw the diagrams. Let us see one by one below.

Entity: Rectangles are used to represent the entity in the diagram. Name of the Entity is written inside the rectangle.

A strong entity is represented by simple rectangle as shown above. A weak entity is represented by two rectangles as shown below.

Attribute: An oval shape is used to represent the attributes. Name of the attribute is written inside the oval shape and is connected to its entity by a line.

Multivalued attributes are represented by double oval shape; where as derived attributes are represented by oval shape with dashed lines. A composite attribute is also represented by oval shape, but these attribute will be connected to its parent attribute forming a tree structure.

Primary Key: An underline to the attribute name is put to represent the primary key. The key attribute of the weak entity is represented by dashed underline.

Relationship: A diamond shape is used to show the relationship between the entities. A mapping with weak entity is shown using double diamond. Relationship name will be written inside them.

Cardinality of Relationship: Different developers use different notation to represent the cardinality of the relationship. Not only for cardinality, but for other objects in ER diagram will have slightly different notations. But main difference is noticed in the cardinality. For not to get confused with many, let us see two types of notations for each.

One-to-one relation: – A one-to-one relationship is represented by adding ‘1’ near the entities on the line joining the relation. In another type of notation one dash is added to the relationship line at both ends.

One-to-Many relation: A one-to-many relationship is represented by adding ‘1’ near the entity at left hand side of relation and ‘N’ is written near the entity at right side. Other type of notation will have dash at LHS of relation and three arrow kind of lines at the RHS of relation as shown below.

Many-to-Many relation: A one-to-many relationship is represented by adding ‘M’ near the entity at left hand side of relation and ‘N’ is written near the entity at right side. Other type of notation will have three arrow kinds of lines at both sides of relation as shown below.

Participation Constraints: Total participation constraints are shown by double lines and partial participations are shown as single line.

Complete ER diagram

Let us create a simple ER diagram for a STUDENT database. What is the requirement of this database?

‘Student attends class. Each class is divided into one or more sections. Each class will have its own specified subjects. Students have to attend all the subjects of the class that he attends’.

Now let us identify what are the entities? STUDENT, CLASS, SECTION, SUBJECT are the entities. Attributes of these entities are not specified here. But we know what could be the entities of each of the entities. We can list them as below at this point of time.

What are the relationships we have? ‘Attends’, ‘has section’, ‘have subjects’ and ‘studies subjects’ are the relations here. With this knowledge of requirement, we can draw the ER diagram as below.

Observe the diagram carefully. Did we miss or drew it correctly? Are we missing anything on the diagram? Is it inferring correct requirement? What are our observations?

• Age attribute can be derived from DOB. Hence we have to draw dashed oval.
• Address is a composite attribute. We have to draw its sub attributes too. So that we will be very clear about his address details.
• If we see the SECTION entity, by section id, will we be able get the section that student attends? There is no relation mentioned between Student and Section. But Section is mapped only with Class. What do we understand from this? Section is a weak entity. Hence we have to represent it properly.
• If we look at ‘attends’ relationship between STUDENT and CLASS, we can have ‘Joining Date’ and ‘Total Number of Hours’ attributes. But it is an attribute of relation. We have to show them in the diagram.
• Since each class is having different subjects and Students attends those subjects, we can modify the relation ‘studies’ to ‘has’ relation on the relation ‘attends’.

Now the diagram will change to reflect all above points.

Are done with complete diagram? We have not added the cardinality and participation in the diagram.

What are the participation constraints here?

• All the Students attend any one of the class, but class can have only certain group of students. Hence total participation of Students and partial participation of class in ‘Attends’ relation.
• All the class has section and all the section has class. Hence both are total participation.
• All the Students study some of the subjects specific for their class and each class has only some group of subjects. Hence partial participation of both STUDENT and CLASS. Each subject will be studied by some students and it will be part of some class. Hence this also partial participation.

What are the cardinalities of all the relationship?

• Each Student attends only one class at a time. Hence it is a 1: 1 relation.
• Each class has one or more sections. Hence it can be considered as 1: N relation.
• Each student attends many subjects and each class has many subjects. Hence it is a 1:N relation.

Note: If you look at STUDENT and CLASS relationship as many Students attend one class, then it would be an M: 1 relation. It is all up to the developer, how he looks at the requirement.

Now it is a complete ER diagram for simple Student database.