Entity Relationship (ER) Model

Entity Relationship Model

DBMS Tutorial 2

DBMS Introduction

The Entity Relationship Model:

Jack is part of database team and he needs to present the database design to business users. The business users are non-technical and it's difficult for them to read a verbose design document. What can Jack do? Jack needs to use an Entity Relation (ER), Model.

The ER model is a graphical representation of entities and their relationships which helps in understanding data independent of the actual database implementation. Let us understand some key terms used in ER Modelling.

Term	Definition	Examples
Entity	Real world objects which have an independent existence and about which we intend to collect data.	Employee, Computer
Attribute	A property that describes an entity.	Name, Salary

A sample ER Diagram representing the Employee entity along with its attributes is presented below:

Relationships:

Relationships are the association of one entity with another entity. Each relationship has a name e.g. a Computer is allocated to an Employee.

There can be more than one relationship between entities, e.g. an Employee works in a Department while the head of the department (also an employee) manages a Department.

A relationship can also exist between instances of the same entity, e.g. an Employee reports to another Employee.

The Cardinality of Relationships:

The Cardinality of Relationship is the number of instances in an entity which is associated with the number of instances in another entity. For the relationship between Employee and Computer, it helps us answer questions like how many computers can be allocated to an employee, can computers be shared between employees, can employees exist without being allocated a computer, etc. e.g. if 0 or 1 computer can be allocated to 0 or 1 employee then the cardinality of relationship between these two entities will be 1:1.

The cardinality of relationships are of three types: 1:1, 1:N and M:N

A relationship with cardinality 1:1 is also called a one-to-one relationship or a 1:1 relationship.

Some entities in the context of Infosys are Employee, Computer, Project, Salaried Account. Can you identify relationships and cardinality for these relationships?

Crowfoot Notation:

Crowfoot Notation is one of the ways to represent the cardinality of the relationship in an ER Model. The notation comprises of four symbols and one of them need to be used for each entity in a relationship.

Let us say the relationship between employee and computer is such that a computer must be allocated to one and only one employee but an employee can be allocated zero or any number of computers. Such a relationship is represented by the diagram below:

Relationships and Foreign Keys:

Foreign keys need to be created in tables in order to establish relationship between entities.

The table in which foreign key will be created depends upon the cardinality of the relationship. Let us now discuss about types of cardinalities and how it impacts foreign key creation.

1:1 Relationship:

1:1 relationship represents the association between a single occurrence of one entity and a single occurrence of the second entity. For e.g. consider a company where each employee can be allocated a maximum of 1 computer and computers are not shared between employees.

The Allot_Dt attribute is not a property of employee or computer. It belongs to the relationship and is hence represented differently in the ER Model.

We can see that the employee table has two additional attributes - CompId and Allot_Dt. CompId is a foreign key to establish the link between these two tables. Allot_Dt which is the attribute of the relationship is always stored in the table that has the foreign key.

Alternatively, we could also have added Id and Allot_Dt attributes in computer table to establish the link.

1 : N Relationship:

1 : N Relationship represents the association between the single occurrence of one entity and multiple occurrences of the second entity. For e.g. consider a company where each employee can be allocated to many computers but still, computers cannot be shared between employees.

In 1 : N relationships, the foreign key, and relationship attributes are always added to the many (N) side of the relationship. Hence these attributes are added to the Computer table. The reverse solution will not work.

M:N Relationship:

M:N Relationship represents the association between multiple occurrences of both entities. For e.g. consider a company where each employee can be allocated to many computers and computers can be shared between employees.

In M : N relationships, the relationship is represented by a completely new table that has a composite primary key. Such a structure requires two foreign keys on the new table linking to the primary keys of each of the parent tables. The attribute of the relationship resides on this new table.

Quiz: Relationships:

Quiz 19: In a relationship between two entities, one and only one instance of the first entity is related to zero or many instances of the second entity. Which of the following symbols in Crows Feet notation represents this relationship?
a) A
b) B ---
c) C
d) D

Quiz 20: In a many to one relationship, the primary key of one entity acts as a foreign key on which side?
a) On the side where a single (one) relationship is defined
b) On the side where many relationships are defined ---
c) On both the sides
d) Neither of them

Quiz 21: A many to many relationships between two entities usually results in how many tables?
a) Two
b) Three ---
c) Four
d) Five