ENTITY RELATIONSHIP(ER) MODEL

The Entity Relationship(ER) data model was first proposed by Senko and others. It is based on a
perception of a real world, which consists of a set of basic objects or entities and relationships among them. It
expresses the database structure graphically.
Features of ER Model
1. It allows describing the data of real world enterprise in terms of objects and their relationships.
2. It is widely used to develop an initial design.
3. It is a high level conceptual data model.
4. Provides a set of useful concepts which can be easily implemented in a database system.
5. It describes data as a collection of entities, relationships and attributes.
BASIC CONCEPTS:
Entity
It is the basic object of the ER model, which represents a thing with an independent existence. It may have a
physical existence (such as person, student, car etc) or it may have a conceptual existence(such as a company, a
university, a job etc). It represents a class of things.
Entity sets
A collection of similar kinds of entities is known as entity set or entity type. It is a set of entities of same type
which share the same properties such as students, employees etc). It is usually denoted by a labeled rectangle.

Label

Attributes
It is a property used to describe a specific feature of the entity. An entity is represented by a set of attributes. For
each attribute there is a set of permitted values, called the domain of that attribute. Each entity may have its
own value for each attribute. It is denoted by a labeled ellipse.

Label

Various types of attributes are

i. Simple / atomic attribute
The attribute that can’t be further subdivided into smaller parts having independent meanings. It is
denoted by ellipse.
1
 Ex: Firstname, Lastname of a person entity.
ii. Composite attribute
Attributes that can be further subdivided into smaller parts / units, and each individual unit contains
a specific meaning. It is denoted by subdivided ellipse.
Ex; Name of an employee can be subdivided into Firstname and lastname. Address can be divided
into plot, area, city and state.

iii. Single valued attribute:

Attributes that can take a single value for a particular entity. Ex: Age, Rollno etc.
iv. Multivalued attribute:
Attributes that can have more than one values for a particular entity. The minimum and maximum
number of values that can be attached must be specified. It is denoted by double ellipse.

Ex: Phoneno, dependantnames etc.

v. Derived attribute:
Attributes that can be obtained or derived from other related attributes. It is denoted by dotted
ellipse.

Ex: Age can be derived from current date and date of birth stored earlier.
The attribute that is already stored directly, from which we can derive other attributes, is known as a
stored attribute. Ex: Dateofbirth for calculating age.
vi. Null attribute:
Attribute whose value is unknown or doesn’t have a value i.e missing value.

2
Relationship
It is an association among the entities. It can be a rule for communication among the entities . It is
used to model the interactions between the entities. Ex: student opts course.

Relationship sets
It is the set of relationships of the same type. It is denoted by a labeled rhombus.

Ex: All instances of relationship “opts” forms a relationship set.

The number of entity sets that participate in a relationship set is known as degree of the relationship set.
The relationship between entities can be of various types:
- 1 entity set is unary relationship

Doctor Treats

- 2 entity sets is binary relationships

Student Course
Opts
3
- 3 entity sets is ternary relationships

Customer Account
Access

Branch
- n entity sets is nary relationships

-
Constraints on relationship types
i. Relationship or mapping cardinality
Cardinality specifies the number of entity instances associated with another entity, participating in a
relationship. They are mostly useful in describing binary relationship sets. Various classifications
are:
a. One-to-one
An entity in the 1st set is associated with at most one entity in 2nd set, and an entity in second set
is associated with at most one entity in first set.
Ex: University Vice Chancellor
U1 V1
1 1
U2 University has Vice Chancellor
V2

U3 V3

b. One-to-many
An entity in 1st set is associated with any number of entities in 2nd set, but an entity in 2nd set is
associated with at most one entity in 1st set.
4
 Ex: Department Faculty

D1 F1
1 N
F2 Department engages Faculty

D2 F3
c. Many-to-one
An entity in 1st set is associated with at most one entity in 2nd set, but an entity in 2nd set is
associated with any number of entities in 1st set.
Ex : Subject Teacher

S1 T1

S2 N Taught 1
Subject Teacher
by
S3 T2

d. Many-to-many
Entities in 1st set and 2nd set are associated with any number of entities from each other.
Ex : Books Author

B1 A1
N Written N
Book Author
B2 A2 by

B3 A3

ii. Relationship participation

If all the entities of an entity set are participating in the relationship then it is known as total
participation(ie no entity is left out) . It is denoted by double lines
Ex : Student opts course ie. There can’t be a student without opting for any course.

Student Opts Course

5
 If one or more entities of an entity set are left out without participating in the relationship then it
is known as partial participation.
Ex : Course opted by students ie there can be a course which is not opted by any student.

Existence Dependencies
 If the existence of an entity X depends on the existence of an entity Y, then X is existent dependent on
Y ie if Y is deleted so is X.
 Y is known as dominant entity, while X is known as subordinate entity.

DESIGN ISSUES
 There are different ways of defining entity sets and relationships among them. Various designing shapes
are used to represent a relationship, an entity, and its attributes.
 The notions of entity set and relationship set is not precise. It is possible to represent the entity set and
relationship sets in a variety ways.
 It leads to a complex structure of the ER diagram and certain issues that does not meet the characteristics
of the real-world enterprise model.

Some basic design issues and solutions concerning them are as follows:
1. Use of Entity Set vs Attributes
2. Use of Entity Set Vs Relationship set
3. Binary Vs n-ary Relationship set.
4. Placement of Relationship attributes.

6
Use of Entity Set vs Attributes
Consider the entity set "instructor" with attributes ID, Name, Phone_number, Salary..It can easily be argued that
a Phone_number is an entity in its own right with attributes phone-number and location .

If we takethis point of view, we must redefine the employee entity set as:
• The employee entity set with attributes ID, Name, Salary
• The phone entity set with attributes phone-number and location
• The relationship set inst_phone, which denotes the association between instructors and the phone numbers that
they have.
Choice mainly depends on the structure of the enterprise being modeled and on the semantics associated with
the attribute in question.

 Treating a telephone number as an attribute phone-number implies that employees have precisely one
telephone number each.
 Treating a telephone as an entity phone permits employees to have several telephone numbers
associated with them also we shall keep extra information about a telephone, such as its location, or its
type (mobile, video phone, or plain old telephone etc.
 Thus, treating phone number as an entity is more general than treating it as an attribute.

7
Entity Sets Vs Relationship Sets:
 It is difficult to examine if an object can be best expressed by an entity set or relationship set.
 Possible guideline is to designate a relationship set to describe an action that occurs between entities.
 we assumed that a bank loan is modeled as an entity.
 An alternative is to model a loan not as an entity, but rather as a relationship between customers and
branches, with loan-number and amount as descriptive attributes.
 Each loan is represented by a relationship between a customer and a branch.
 If every loan is held by exactly one customer and is associated with exactly one branch, we may find
satisfactory the design where a loan is represented as a relationship.
 Loan as a relationship may not hold good in case several customers hold a loan jointly.
 we must replicate the values for the descriptive attributes loan-number and amount in each such
relationship.
 Two problems arise as a result of replication :
i. The data are stored multiple times, wasting storage space, and
ii. Updates potentially leave the data in an inconsistent state, where the values differ in two
relationships for attributes that are supposed to have the same value.
 The problem of replication of the attributes loan-number and amount is absent in the original design
because there loan is an entity set.

8
Binary versus n-ary Relationship Sets
 Relationships in databases are often binary. Some relationships that appear to be nonbinary could
actually be better represented by several binary relationships.
 It is always possible to replace a nonbinary (n-ary, for n > 2) relationship set by a number of distinct
binary relationship sets.
 For instance, one could create a ternary relationship parent, relating a child to his/her mother and
father. However, such a relationship could also be represented by two binary relationships,
mother and father, relating a child to his/her mother and father separately. Using the two
relationships mother and father allows us record a child’s mother, even if we are not aware of the
father’s identity; a null value would be required if the ternary relationship parent is used. Using
binary relationship sets is preferable in this case.

consider the abstract ternary (n = 3) relationship set R, relating entity sets A, B, and C. We replace the
relationship set R by an entity set E, and create three relationship sets:
• RA, relating E and A
• RB, relating E and B
• RC, relating E and C
9
Create a special identifying attribute for E.
Add any attributes for R to E.
For each relationship (ai, bi, ci) in the relationship set R, we create
i. a new entity ei in the entity set E.
ii. add (ei, ai) in RA
iii. add (ei, bi) in RB
iv. add (ei, ci) in RC

We can generalize this process in a straightforward manner to n-ary relationship sets. But there are some
relationships that are naturally non-binary. Example : works-on.

Placement of Relationship Attributes:

 The cardinality ratio of a relationship can affect the placement of relationship attributes.
 For one-to-one relationship sets, the relationship attributes can be associated with either one of the
participating entity sets, rather than with the relationship set.

10
 Attributes of a one-to-many or many-to-one relationship set shall be repositioned to only the entity set
on the "many" side of the relationship.

 For many-to-many relationship sets, the attributes must be associated with the relationship set rather
than either one of the participating entities.

11
KEYS
 Conceptually, individual entities are distinct; from a database perspective.
 The difference among them must be expressed in terms of their attributes.
 The values of the attribute values of an entity must be such that they shall uniquely identify the entity.
 A key allows us to identify a set of attributes that suffice to distinguish entitiesfrom each other.
 Keys also help uniquely identify relationships, and thus distinguish relationships from each other.
 Thus keys are one or more attributes taken together.
 Both Entities and Relationships can have keys.
1. Entity Set Keys
2. Relationship keys
1. Entity Set Keys
There are various types of keys:
1. Super key
It is a set of one or more attributes that collectively , uniquely identifies an entity in an entity set.
E.g. of Super Key
a. ID
b. ID, Name
12
 c. ID, Address
d. ID, Department_ID
e. ID, Salary
f. Name, Address
g. Name, Address, Department_ID

2. candidate key
It can be defined as minimal Super Key or irreducible Super Key ie a super key which contains no
extraneous attributes. It can’t contain NULL values.. In other words an attribute or a combination of attribute
that identifies the record uniquely but none of its proper subsets can identify the records uniquely.

E.g. of Candidate Key

a. ID
b. Name, Address

3. Primary key:
A Candidate Key that is used by the database designer for unique identification of each row in a table is
known as Primary Key. A Primary Key can consist of one or more attributes of a table.

E.g. of Primary Key

Database designer can use one of the Candidate Key as a Primary Key. In this case we have “ID” and “Name,
Address” as Candidate Key, we will consider “ID” Key as a Primary Key as the other key is the combination of
more than one attribute.

4. Alternate key(or unique key):

Alternate Key can be any of the Candidate Keys except for the Primary Key.

 A key that consists of only one attribute is known as “simple” key.

 A key that involves more than one attribute is known as “composite” key.
 All the keys(super key / candidate key / primary key/alternate key) can be either simple or composite.

13
5. Foreign key:
A foreign key is an attribute or combination of attributes in one table that points to the primary key of
same or another table. Its values are derived from the primary key, so it must either match with the values
of primary key or it must be null.

The attribute "Dept-No" is the foreign key in "Employee" table.

2. RELATIONSHIP KEYS:
 The primary key of a relationship set allows us to distinguish among the various relationships of a
relationship set.
 If the relationship set R has no attributes associated with it, then the set of attributes
primary-key(E1) ∪ primary-key(E2) ∪ · · · ∪ primary-key(En)
describes an individual relationship in set R.
 If the relationship set R has attributes a1, a2, · · · , am associated with it, then the set of attributes
primary-key(E1) ∪ primary-key(E2) ∪ · · · ∪ primary-key(En) ∪ {a1, a2, . . . , am}
describes an individual relationship in set R.
 In both of the above cases, the set of attributes
primary-key(E1) ∪ primary-key(E2) ∪ · · · ∪ primary-key(En)
forms a superkey for the relationship set.

Example: consider the entity sets Customer and Account, and the relationship set depositor, with attribute
"access-date". The primary key of Depositor will be:
14
Case 1: Primary Key(Customer) U Primary Key(Account) if the relationship is many-to-many.
Case 2: Primary Key(Customer) if the relationship is many-to-one from Customer to Account.
Case 3: Primary Key(Account) if the relationship is many-to-one from Account to Customer.
Case 4: Primary Key(Customer) or Primary Key(Account) if the relationship is one-to-one .

ENTITY-RELATIONSHIP DIAGRAM
An E-R diagram can express the overall logical structure of a database graphically. E-R diagrams are simple
and clear. Such a diagram consists of the following major components:
• Rectangles, which represent entity sets
• Ellipses, which represent attributes
• Diamonds, which represent relationship sets
• Lines, which link attributes to entity sets and entity sets to relationship sets
• Double ellipses, which represent multivalued attributes
• Dashed ellipses, which denote derived attributes
• Double lines, which indicate total participation of an entity in a relationship set
• Double rectangles, which represent weak entity sets

15
 [Symbols of ER diagram]

ER diagram showing one-to-many relationship between customer and loan entity.

16
 ER diagram showing many-to-one relationship between customer and loan entity.

 ER diagram showing one-to-one relationship between customer and loan entity.

 ER diagram showing many-to-many relationship between customer and loan entity.

17
 ER diagram showing Descriptive attributes associated with a relationship set:

 ER diagram with role indicator:

 We indicate roles in E-R diagrams by labeling the lines that connect diamonds to rectangles.

 The labels “manager” and “worker” are called “roles”. They specify how “employee” entities
interact via “works_for” relationship set.
 Role labels are optional and are used to clarify the semantics of the relationship.

ER diagram with ternary relationship:

18
 ER diagram showing total participation:

 ER diagram for showing alternative notations for cardinality limits:

 ER diagram can show the constraints on the number of times each entity participates in a
relationship.
 An edge between an entity set and a binary relationship set can have an associated minimum and
maximum cardinality, shown in the form l..h, where l is the minimum and h the maximum
cardinality.
 A minimum value of 1 indicates total participation of the entity set in the relationship set.
 A maximum value of 1 indicates that the entity participates in at most one relationship.
 A maximum value ∗ indicates no limit.

Strong Entity Set

The entity which contains the primary key attribute is known as the strong entity or regular entity. Ex:
Student entity containing rollno, name, address etc.

19
WEAK ENTITY SET
 An entity set that has a primary key is termed a strong entity set. Ex: Student entity containing rollno,
name, address etc.
 The entity which doesn’t contain the primary key attribute is known as weak entity set and is denoted
by double rectangle.
 Entities of a weak entity set can not be identified independently, but can be uniquely identified in
conjunction with the primary key attribute of another entity, called the identifying or owner entity of
this weak entity.
 The identifying relationship is denoted by a double diamond.
 Every weak entity must be associated with an identifying entity; that is, the weak entity set is said to be
existence dependent on the identifying entity set.
 The identifying entity set is said to own the weak entity set that it identifies.
 The relationship associating the weak entity set with the identifying entity set is called the identifying
relationship.
 The identifying relationship is many to one from the weak entity set to the identifying entity set.
 The participation of the weak entity set in the relationship is total.

The existence of a weak entity set depends on the existence of a strong entity set; it must relate to the strong
set via a one-to-many relationship set.

Ex : "payment" entity set depends on the "loan" entity set.

20
  The discriminator(or partial key) of a weak entity set is a set of attributes that distinguishes among all
the entities of a weak entity set that depends on one particular strong entity.
 The discriminator of a weak entity set is shown with a dashed line.
 The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus
the weak entity set’s discriminator.
 The primary key of the weak entity set “payment” is {loan-number, payment-number},
 where loan-number is the primary key of the identifying entity set
 payment-number is the discriminator of the payment entity set since for each loan, a payment
number uniquely identifies one single payment for that loan.

EXTENDED E-R FEATURES:

Although the basic E-R concepts can model most database features, some aspects of a database may be more
aptly expressed by certain extensions to the basic E-R model like
 Specialization
 Generalization
 Higher- and lower-level entity sets
 Attribute inheritance
 Aggregation.

21
Specialization:
 The process of designating sub groupings within an entity set is called specialization.
 It is a top-down design process.
 In specialization a group of entity is divided into sub group on their characteristic.
 Example:
Specialization of person allows us to distinguish persons according to whether they are "Employees" or
"Customers". Specialization of "account" creates two entity sets "saving account" and "checking
account".
 The specialized entity sets will have all the attributes of original entity set with some additional
attributes.
 In E-R diagram, specialization is depicted by a triangle component labeled ISA,
 The ISA relationship may also be referred to as a superclass-subclass relationship.

[Specialization and Generalization]

22
 Generalization
 It is a bottom up design process that combines a number of entity sets that share the same features
into a higher-level entity set..
 Generalization is opposite of specialization (reverse of specialization).
 In generalization, a number of entities are brought together into one generalized entity based on their
similar characteristics.
 Specialization and Generalization are simple inversion of each other and are represented in the same
way in the ER diagram.
 It is the abstraction process of viewing sets of objects as a single general class by concentrating on
general characteristics, while ignoring their differences.
 It is the union of lower level entities producing a higher level entity.
 It is denoted by a triangle.

Attribute Inheritance:
 The A crucial property of the higher- and lower-level entities created by specialization and
generalization is attribute inheritance.
 The attributes of the higher-level entity sets are said to be inherited by the lower-level entity sets.
 For example, customer and employee inherit the attributes of person.

23
 

Whether a given portion of an E-R model was arrived at by specialization or generalization, the outcome is
basically the same:
• A higher-level entity set with attributes and relationships that apply to all of its lower-level entity sets
• Lower-level entity sets with distinctive features that apply only within a particular lower-level entity set

Constraints on Generalizations:
1. Constraint involves determining which entities can be members of a given lower-level entity set.
Such membership may be one of the following:

a. Condition-defined: In condition-defined lower-level entity sets, membership is evaluated on the basis

of whether or not an entity satisfies an explicit condition or predicate.
Example:
 All persons above 65 years are members of "senior-citizen" entity set. "senior-citizen" ISA "person".
 All accounts having account_type="Savings" are included in the "saving-account" entity set.
"saving-account" ISA "account"

 Only those entities that satisfy the particular condition are allowed to belong to the lower-level entity set.

24
 Since all the lower-level entities are evaluated on the basis of the same attribute (in this case, on account-
type), this type of generalization is said to be attribute-defined.

b. User-defined. User-defined lower-level entity sets are not constrained by a membership condition;
rather, the database user assigns entities to a given entity set.

Example:
The database user decides the membership of a particular employee in a particular project group
without any constraint.

2. Constraint relates to whether or not entities may belong to more than one lower-level entity set within
a single generalization.
The lower-level entity sets may be one of the following:

a. Disjoint. A disjointness constraint requires that an entity belong to no more than one lower-level entity
set.
We can note a disjointedness constraint in an E-R diagram by adding the word disjoint next to the
triangle symbol.
Example:
An account entity can belong to only one lower level entity set either a savings account or a
checking account, but cannot be both.

b. Overlapping. In overlapping generalizations, the same entity may belong tomore than one lower-
level entity set within a single generalization.
Example:
Some employees may be in more than one project group.

3. completeness constraint on a generalization or specialization, specifies whether or not an entity in the

higher-level entity set must belong to at least one of the lower-level entity sets within the generalization/
specialization.

25
 This constraint may be one of the following:
 Total generalization or specialization. Each higher-level entity must belong to a lower-level entity set.
The "account" generalization is total.
 Partial generalization or specialization. Some higher-level entities may not belong to any lower-level
entity set.
The work team entity sets illustrate a partial specialization. Since employees are assigned to a
team only after 3 months on the job, some employee entities may not be members of any of the lower-
level team entity sets.

 Partial generalization is the default.

 We shall specify total generalization in an E-R diagram by using a double line to connect the box
representing the higher-level entity set to the triangle symbol.

The account generalization is total: All account entities must be either a savings account or a checking
account.

Aggregation
 It is a feature of entity relationship model that allows a relationship set to participate in another
relationship set.
 This is indicated on an ER diagram by drawing a dashed box around the aggregation.
 The meaning of aggregation is way of abstraction.
 The process through which one can treat the relationship as higher level entities is known as
aggregation.
 It shows relationship among relationships.

Example:
Let's consider the ternary relationship works-on, between a employee, branch, and job. Now, suppose we want
to record managers for tasks performed by an employee at a branch; that is, we want to record managers for
(employee, branch, job) combinations.

Let us assume that there is an entity set manager.

26
  Aggregation is an abstraction through which relationships are treated as higher level entities.
 We regard the relationship set works-on (relating the entity sets employee, branch, and job) as a higher-
level entity set called works-on.
 Such an entity set is treated in the same manner as is any other entity set.
 We shall then create a binary relationship manages between works-on and manager to represent who
manages what tasks.

ER DIAGRAM EXAMPLES
1. ER Diagram for Bank
Data Requirements
 The bank is organized into branches. Each branch is located in a particular city and is identified by a
unique name. The bank monitors the assets of each branch.
 Bank customers are identified by their customer-id values. The bank stores each customer’s name, and
the street and city where the customer lives. Customers may have accounts and can take out loans. A
customer may be associated with a particular banker, who may act as a loan officer or personal banker
for that customer.
 Bank employees are identified by their employee-id values. The bank administration stores the name
and telephone number of each employee, the names of the employee’s dependents, and the employee-
id number of the employee’s manager. The bank also keeps track of the employee’s start date and, thus,
length of employment.

27
 The bank offers two types of accounts—savings and checking accounts. Accounts can be held by more
than one customer, and a customer can have more than one account. Each account is assigned a unique
account number. The bank maintains a record of each account’s balance, and the most recent date on
which the account was accessed by each customer holding the account. In addition, each savings
account has an interest rate, and overdrafts are recorded for each checking account.
 A loan originates at a particular branch and can be held by one or more customers. A loan is
identified by a unique loan number. For each loan, the bank keeps track of the loan amount and the loan
payments. Although a loanpayment number does not uniquely identify a particular payment among
those for all the bank’s loans, a payment number does identify a particular payment for a specific loan.
The date and amount are recorded for each payment.

28
2. ER DIAGRAM FOR COLLEGE:

3. ER DIAGRAM FOR A CAR INSURANCE COMPANY.

29
4.ER DIAGRAM FOR MOTOR VEHICLE SALES COMPANY:

30