ER MODEL

Before define ER Model, there are some terms which is require to understand ER model properly. In short, ER Model is a diagrammatic representation of entire Database tables. It represents a High Level Database Design.

For Designing the Softwares : This phase is done in softwares by UML (Unified Modelling Language).

For Designing the Database : ER Model is used to design database.

Definitions :

Entity : an object (Record in the table). It a a real world object, such as person, place, thing, event, or concept about which information is recorded. For example, In a banking environment, entities are CUSTOMERS, BANK SUPPLIERS etc. Entities may be –

• Physical – Can be touched(sensed) Example Fan.
• Conceptual – Cannot be touched. Example course offered, brand name etc.

Storing conceptual entities is a big problem.

Entity Types : is defined as a collection of entities that have the same attributes. For example, employee in a company database. As same attributes are shared by the employee entities, but such entity has its own value for each attributes.

Entity Set : Collection of similar entities of same type that share the same attribute at any point in time. For example, a Faculty set may contain all the teachers of a college, Set of all persons, Set of Companies etc. The Entity Set is also called extension of the Entity Type.

Attribute : It is the name of the column. An attribute gives the characteristics of the entity.  It is also called as data element, data field, a field, a data item, or an elementary item.

Some examples of Attributes which clear the concept of attributes –

a) A house can be described by its colour, size and surroundings 
b) A bank account can be described by its bank account number, account 
   type, account balance, account statement etc.
c) A customer of bank may be described by : name, address, customer ID 
   number, phone number.

So, in example a) colour, size and surroundings are attributes of entity 
   "HOUSE".
    In example b) account number, type, balance and statement are 
    attributes of entity "ACCOUNT"
    In example c) name address ID number and phone number are attributes 
    of entity "CUSTOMER"

Data Value : It is the actual data or information contained in each data element(attribute). The data element “Supplier Name” can take value as “Anil Mittal”

Difference between Entity, Attributes, and Data Values –

ENTITY	ATTRIBUTES(OR DATA ELEMENTS)	VALUES
CUSTOMER	Customer ID Number Customer Name Customer Address Customer Phone Number	985678 Ankit Mittal B-176,MC Colony, Bhiwani 57845-69821
ACCOUNT	Account Type Account Number Account Balance Account Statement	Savings 1800221133665587 125000 5000_Credit
SUPPLIER	Supplier Name Supplier Address Supplier Phone Number Supplier Bill Number	Vishal Rastogi H-456, Shiv Nagar, Bhiwani 789546256 B-896541

Difference Between Entity, Entity Type and Entity Sets – 

Entity	Entity Type	Entity Set
Entity is a person, place, thing, event, or even a concept. It may be tangible or intangible.	An entity type defines the collection of entities that have the same attributes.	An entity set is a set of entities of the same type that share the same properties at any point in time (or) It is the collection of entities of an entity type at a point of time.
For Example, Entity is e1 or e2 or e3 or an apple where e1 e2 e3 are employees and an apple is a fruit.	For example, entity type is EMPLOYEE or Fruit	Entity set is a bucket of apple, banana, mango etc. or {e1,e2……}

Example :

Entity Type	Employee
Attributes	Name, Age, Pay
Entity Set (extension)	e1 (Rakesh, 40, 10000) e2 (Arun, 55, 8500) e3 (Sandeep, 30, 14000)

Notations Of above Definitions in ER Diagram –

	Entity Type
	Entity Set
	Attribute

Types of Relationships in ER Diagram

Different types of relationships in er diagram are – 1) Relationships based on degree, 2) Recursive Relationship. Let us discuss these one by one.

Relationship and Relationship Set :  

Relationships connect the entities and represent meaningful dependencies between them.It represents an association among several entities.
Relationships sets is a set of relationships of the same type. It is a mathematical relation on entity sets (n>=2). Relationship set R is a subset of –

{(r1,r2,r3,....rn)| r1∈E1, r2∈E2, rn∈En}

where r1,r2,….rn are called relationships and E1,E2,….En are entity sets.

The way in which two or more entity types are related is called relation type.For example, consider a relationship type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which associates or links each employee with the department the employee works for.
The WORKS_FOR relation type is shown as –

---

---

In the above figure, each instance of relation type WORKS_FOR i.e.(r1, r2,…,r5) is connected to instances of employee and department entities. Employee e1, e2 and e5 work for department  d2 and employee e3 and e4 work for department d1.

Notation to Represent Relation Type in ER Diagram-

Relation types are represented as diamond shaped boxes.

Degree of a Relationship Type-

The number of participating entity types is known as the degree of relationship type.

Types of Relationship Type Based on Degree –

• Binary Relationship – A relationship type of degree two is called binary relationship. The WORKS_FOR in above figure is a binary relationship as  there are two participating entities-employee and department.
  
• Ternary Relationship- A relationship type of degree three is a ternary relationship for example, in the below figure supply relationship connects three entities SUPPLIER, PART AND PROJECT.
  
  The above diagram can be read as – a supplier supplies the parts to projects
• N-ary Relationship Set – A relationship type of degree n is called n ary relationship . For example

Role Names-

A relationship type has a name which signifies what role a participating entity plays in that relationship instance. The role names helps to explain what the  relationship means.

In the first example WORKS_FOR  relationship type, employee plays the role of worker and department plays the role of employee(because a department consists of a number of employees.

Recursive  Relationship

If the same entity type participate more than once in a  relationship type in different roles then such  relationship types are called recursive  relationship. For example, in the below figure REPORTS_TO is a recursive relationship as the Employee entity type plays two roles – 1) Supervisor and 2) Subordinate.

You can also define the above example of recursive relationship as the relationship between a manager and an employee. An employee is a manager as well as employee.

This is commonly know as a ‘pig’s ear’.

To implement recursive relationship, a foreign key of the employee’s manager number would be held in each employee record.

Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB, Emp_NI_Number, Manager_no);

Manager no - (this is the employee no of the employee's manager)

Another more Complicated Example of Recursive Relationship :

Relationship between a person and their parents :

Relationship Constraints in DBMS –

There are three Types of Relationship Constraints-

1. Structural Constraints
   •  Participation Constraints
   • Cardinality Ratio
2. Overlap Constraints
3. Covering Constraints

Structural Constraints are applicable for binary relationships and
Overlap and Covering Constraints are applicable for EERD(Extended ER Diagrams).

Participation (or) Optionality Constraints-

Participation concerns with the involvement of entities in a relationship. It specifies whether the existence of an entity depends on another entity. There are two types of Participation Constraints –

1. Total/Mandatory Participation
2. Partial/Optional Participation

Notations of Different Types of Participation In ER Diagram –

Total/Mandatory Participation or Existence Dependency-

Participation is said to be total if every entity in E participates in atleast one relationship in R (or) every entity in entity set must depend on another entity. For example, every department will have a startup date as a department was created on that startup date (SINCE ATTRIBUTE of MANAGE relationship). and that department is being handled from that date through a Manager. So, the participation of DEPARTMENT entity in the “MANAGE” relationship type is total.

Total Participation is also known as Existence Dependency.
In ER Diagram, it is represented as a Double Line, connecting the participating entity to the relationship.

Partial/Optional Participation-

Participation is said to be partial if only some entities in E participate in relationships in R (or) some entities in entity set are depend on some another entities in entity set. For example, It is not necessary that all employees manage some department Because all employees may not be the Manager . So the participation of “EMPLOYEE” entity in the “MANAGES” relationship type is partial.

                            (Figure-1)

Cardinality/Mapping Cardinality-

Cardinality expresses the number of entities to which another entity can be associated via a relationship set (or)
It specifies the number of relationship instances that an entity can participate in a relation set.
There are 4 types of Cardinality Ratios :

• One-to-One Cardinality (1:1)
• One-to-Many Cardinality (1:m)
• Many-to-One Cardinality (m:1)
• Many-to-Many Cardinality (m:n)

Notations of Different Types of Cardinality In ER Diagram –

One-to-One Cardinality (1:1) –

An entity in set A is associated with atmost one entity in B, and an entity in B is associated with atmost one entity in A. This type of cardinality is referred as one to one Cardinality.
For example, an Employee as a  Manager manage only one Department  and the vice versa is also true as a department have only one Manager

Create Table MANAGE
( SSN varchar(10),
  DNO varchar(15),
  Primary key(SSN),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "MANAGE" relationship - SSN,DNO

One to Many Cardinality (1:M) –

An entity in A is associated with any number (0 or more) with an entity B, but a entity in B, however can be associated with atmost one entity in A. For example, An employee as a Manager can manage more than one Department.

Create Table MANAGE
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(DNO),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "MANAGE" relationship - DNO

Many to One Cardinality (M:1) –

An entity in A is associated with atmost one entity in B. An entity in B, however, can be associated with any number (0 or more) of entities in A. For example, An Employee can work only for one Department, But each Department can have 0 or more employees.

Create Table WORKS_ON
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(SSN),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "WORKS_ON" relationship - SSN

Many to Many Cardinality (M:N) –

An entity in A is associated with any number (0 or more) of entities in B, and an entity in B is associated with any number( 0 or more) of entities in A. For example, An Employee can works on several Projects and a Project may have several Employees.

Create Table WORKS_ON
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(SSN,DNO),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "WORKS_ON" relationship - SSN PID

Some Points regarding Cardinality and Participation –

1. Minimum Cardinality = 0, for partial participation and equal to 1 for 
   total participation.
2. Maximum Cardinality = n, if one entity occurrence relates to multiple
   entity occurrences.
3. Minimum Cardinality give partial or total participation.
4. Maximum Cardinality give the maximum number of entities that is related
   to.

Relation Between Cardinality and Joins

Cardinality means it is a relation between tables using joins which tells that how many rows of one table will match with rows in other tables when these tables are joined.
In other words Relationships between the tables define Cardinality while explaining how each table links to the other.

Example :

Consider the two tables Consumer(custID) and Bill(BID, FK_custID). Each consumer pays the electricity bill every month. So, a customer will be associated with 12 bills after one year. It means there is one to many relationship between consumer and the bills he paid.  In this case, the cardinality is defined from primary key of the Customer table(PK_Custid) to the foreign key (FK_Custid) of the Bill table.

The number of same rows in both the tables depends upon which join is used.

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.

STUDENT	CLASS	SECTION	SUBJECT
STUDENT_ID	CLASS_ID	SECTION_ID	SUBJECT_ID
STUDENT_NAME	CLASS_NAME	SECTION_NAME	SUBJECT_NAME
ADDRESS
DOB
AGE
CLASS_ID
SECTION_ID

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.