ER Data Model

• Entity • Degree of Relationship

• Strong Entity • Cardinality
• Weak Entity
• Participation
• Attribute
constraints
• Simple Attribute
• Composite Attribute
• Single valued Attribute
• Multi valued Attribute
• Stored Attribute
• Derived Attribute
• Key Attribute
 ER Diagram or ER Data Model
• ENTITY
It is a real word object.

Entity Type
Entity set
 Entity types:
• Strong Entity: Entities having its own attribute
as primary keys are called strong entity. For
example, STUDENT has STUDENT_ID as
primary key. Hence it is a strong entity.
• Weak Entity: Entities which cannot form their
own attribute as primary key are known weak
entities. These entities will derive their primary
keys from the combination of its attribute and
primary key from its mapping entity.
• Consider CLASS and SECTION entity. The SECTION has
SECTION _ID and NAME as its attribute. But SECTION _ID
alone cannot be a primary key, since it fails to tell for which
course it is related to. We will not be uniquely identifying the
course section by this attribute alone.
• But if this attribute along with CLASS_ID gives the meaning
for each section and we can uniquely identify the sections.
 ATTRIBUTE
• Simple Attribute
These kinds of attributes have values which cannot be divided
further.
Eg. STUDENT_ID attribute which cannot be divided further. Passport Number
is unique value and it cannot be divided.
Composite Attribute

This kind of attribute can be divided further to more than one simple
attribute.

For example, address of a person. Here address can be further divided as

Door#, street, city, state and pin which are simple attributes.
• Derived Attribute

Derived attributes are the one whose value can be obtained from other
attributes of entities in the database.

For example, Age of a person can be obtained from date of birth and
current date. Average salary, annual salary, total marks of a student etc are
few examples of derived attribute.
• Stored Attribute

The attribute which gives the value to get the derived attribute are called
Stored Attribute.

In example above, age is derived using Date of Birth. Hence Date of Birth is
a stored attribute.
Single Valued Attribute

These attributes will have only one value.

For example, EMPLOYEE_ID, passport#, driving license#, SSN etc have only
single value for a person.
Multi-Valued Attribute

These attribute can have more than one value at any point of time.

Ex: Car-color,phone-number
 Keys
Keys are the attributes of the entity, which
uniquely identifies the record of the entity. For
example STUDENT_ID identifies individual
students, passport#, license # etc.
 Degrees of Relationship

• In a relationship two or more number of entities can participate.

• The number of entities who are part of a particular relationship is called

degrees of relationship.

• If only two entities participate in the mapping, then degree of relation is 2

or binary.

• If three entities are involved, then degree of relation is 3 or ternary.

• If more than 3 entities are involved then the degree of relation is called n-
degree or n-nary.
 Cardinality of Relationship

• number of instances of one entity is mapped to

number of instances of another entity is known as
cardinality of a relationship.

• Based on the cardinality, there are 4 types of

relationship.
 Types of Relationship Mapping

1. One - to - One Relationship

2. One - to - Many Relationship
3. Many - to - One Relationship
4. Many - to - Many Relationship
• 1. One - to - One RelationshipIn One - to -
One Relationship, one entity is related with
only one other entity.
One row in a table is linked with only one row in another table and vice
versa.
For example: A Country can have only one Capital City.
• One - to - Many RelationshipIn One - to -
Many Relationship, one entity is related to
many other entities.

• One row in a table A is linked to many rows in a table B, but one row in a
table B is linked to only one row in table A.
For example: One Department has many Employees.
• Many - to - One RelationshipIn Many - to -
One Relationship, many entities can be related
with only one other entity.

For example: No. of Employee works for Department.

• Multiple rows in Employee table is related with only one row in
Department table.
• Many - to - Many Relationship
In Many - to - Many Relationship, many
entities are related with the multiple other
entities.

• This relationship is a type of cardinality which refers the relation between

two entities.
For example: Various Books in a Library are issued by many Students.
 Participation Constraints

two types of Participation Constraints,

1. Total Participation
2. Partial Participation
 1. Total Participation
• In Total Participation, every entity in the set is
involved in some association of the
relationship.
• It is indicated by a double line between entity
and relationship.
For example: Every Department must have a
Manager.
 2. Partial Participation
In Partial Participation, not all entities in the set
are involved in association of the relationship.

It is indicated by a single line (______) between

entity and relationship.
 EER Model
.
• EER is a high-level data model that
incorporates the extensions to the original ER
model
 It is a diagrammatic technique for
displaying the following concepts
• Sub Class and Super Class
• Specialization and Generalization
• Union or Category
• Aggregation
 A. Sub Class and Super Class

• Sub class and Super class

relationship leads the
concept of Inheritance.
• The relationship between
sub class and super class is
denoted with symbol.
 1. Super Class
• Super class is an entity type that has a
relationship with one or more subtypes.
• An entity cannot exist in database merely by
being member of any super class.
For example: Shape super class is having sub
groups as Square, Circle, Triangle.
 2. Sub Class
• Sub class is a group of entities with unique
attributes.
• Sub class inherits properties and attributes
from its super class.
For example: Square, Circle, Triangle are the
sub class of Shape super class.
 1. Generalization
• Generalization is the process of generalizing the entities
which contain the properties of all the generalized
entities.
• It is a bottom approach, in which two lower level entities
combine to form a higher level entity.
• Generalization is the reverse process of Specialization.
• It defines a general entity type from a set of specialized
entity type.
• It minimizes the difference between the entities by
identifying the common features.
In the above example, Tiger, Lion, Elephant can all be generalized as
Animals.
 2. Specialization
• Specialization is a process that defines a group entities
which is divided into sub groups based on their
characteristic.
• It is a top down approach, in which one higher entity can
be broken down into two lower level entity.
• It maximizes the difference between the members of an
entity by identifying the unique characteristic or
attributes of each member.
• It defines one or more sub class for the super class and
also forms the superclass/subclass relationship.
In the above example, Employee can be specialized as
Developer or Tester, based on what role they play in an
Organization.
 C. Category or Union
• Category represents a single super class or sub class relationship
with more than one super class.

• It can be a total or partial participation.

For example Car booking, Car owner can be a person, a bank (holds
a possession on a Car) or a company.
• Category (sub class) → Owner is a subset of the union of the three
super classes → Company, Bank, and Person.
• A Category member must exist in at least one of its super classes.
 D. Aggregation
• Aggregation is a process that represent a
relationship between a whole object and its
component parts.
• It abstracts a relationship between objects and
viewing the relationship as an object.
• It is a process when two entity is treated as a
single entity.
In the above example, the relation between College and Course is acting as
an Entity in Relation with Student.
 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.
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
• 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’.
• 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.
 Transform ER Diagram into Tables
The basic rule for converting the ER diagrams into tables is
• Convert all the Entities in the diagram to tables.
• All single valued attributes of an entity is converted to a column of the
table
• Key attribute in the ER diagram becomes the Primary key of the table.
• Declare the foreign key column, if applicable.
• Any multi-valued attributes are converted into new table.
• Any composite attributes are merged into same table as different columns.
• One can ignore derived attribute, since it can be calculated at any time.
Thank you