ER MODEL

PREPARED BY:
SIMANTIKA RAY
 ER MODEL:

Introduction
 The Entity Relationship(ER) data model was first proposed by
Senko and others.

 It is the diagrammatic representation of entities and the

relationships among them.

 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.

 BASIC CONCEPTS:
 Entity
 An entity is a “thing” or “object” in the real world that is distinguishable from
all other objects.
 It is the basic object of the ER model, which represents a thing with an
independent physical or conceptual existence .
.
 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.
Student
 It is usually denoted by a labeled rectangle.

 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.
Roll
 It is denoted by a labeled ellipse.
Various types of attributes are
 Simple / atomic attribute

 The attribute that can’t be further subdivided into smaller parts having
independent meanings.
 It is denoted by ellipse.
 Ex: Roll, Age of a student entity.
Student Roll

 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.
 Single valued attribute:
 Attributes that can take a single value for a particular entity.
Ex: Age, Rollno etc.

 Multivalued attribute:
 Attributes that can have more than one values for a particular entity.
 It is denoted by double ellipse.

 Ex: Phoneno, dependantnames , email etc.

 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.
 date-of-birth may be referred to as a base attribute, or a stored attribute.
 The value of a derived attribute is not stored, but is computed when required.
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.
Degree of Relationship set(n-ary relationship):
 Unary Relationship

 Binary Relationship

 Ternary Relationship
Constraints on relationship types
1. mapping cardinality
Cardinality specifies the number of entity instances associated with
another entity, participating in a relationship.
One-to-one:

One-to-many
1
Department
N
engages Faculty
 Many-to-one

 Many-to-many
2. Relationship participation
 If all the entities of an entity set are participating in the
relationship then it is known as total participation.
 It is denoted by double lines .
 Ex : Student opts course ie. There can’t be a student without
opting for any course.

 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.
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 enables the user to
distinguish entities from each other.

 Thus keys are one or more attributes taken together.

 Both Entities and Relationships can have keys.

 Entity Set Keys
 Relationship keys
A) ENTITY SET KEYS

There are various types of Entity keys:

 Super Key

 Candidate Key

 Primary Key

 Alternate Key

 Foreign Key
1. Super Key
 It is a set of one or more attributes that collectively , uniquely
identifies an entity in an entity set.

 List of super keys:

1. Employee_ID
2. Employee_ID, Employee_Address
3. Employee_Name, Employee_Address
4. Passport_Number, Employee_Name
5. PAN
6. PAN, Employee_Name
7. License_Number,Employee_Address
8. Employee_Name, Employee_Address,PAN
9. Employee_ID, Employee_Address, ,Employee_Name
 2. Candidate key
 It can be defined as minimal Super Key ie a super key which contains no extraneous
attributes.

List of candidate keys:

1. Employee_ID
2. Employee_ID, Employee_Address
3. Employee_Name, Employee_Address
4. Passport_Number, Employee_Name
5. PAN
6. PAN, Employee_Name
7. License_Number,Employee_Address
8. Employee_Name, Employee_Address,PAN
9. Employee_ID, Employee_Address, ,Employee_Name
 3. Primary key:
 It is a Candidate Key that is used by the database designer for unique identification of
each row in a table .
 A Primary Key can consist of one or more attributes of a table.

Primary key of the above table can be any one of the followings:
1. Employee_ID Primary Key
2. Employee_Name, Employee_Address
3. Passport_Number
4. PAN
5. License_Number
 4. Alternate key(or unique key):

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

 After considering “Employee_ID” as primary key, the possible candidate

keys are as follows:
1. Employee_Name, Employee_Address
2. Passport_Number
3. PAN
4. License_Number
 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.
5. Foreign Key
 A foreign key is an attribute or combination of attribute
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.
Example of foreign key of a table referring to its own
primary key:
SUPER KEYS

CANDIDATE KEYS

ALTERNATE
KEYS
PRIMARY
KEY
B) 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.
 The structure of the primary key for the relationship set depends on the mapping
cardinality of the relationship set.
 In the above diagram, the entity set “customer” and “account”, and the relationship
set “depositor”, with attribute "access-date".
 The primary key of depositor will be:
 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 .
ER Diagram
An E-R diagram can express the overall logical structure of a database
graphically.
 ER DIAGRAM EXAMPLES:
 ER diagram showing one-to-many relationship between
customer and loan entity.

 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.
ER diagram showing Descriptive attributes associated with a relationship set:

ER diagram of entity with composite, derived and multivalued attribute:

 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:
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.

WEAK ENTITY SETS:

 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 relationship associating the weak entity set with the

identifying entity set is called the identifying relationship.

 The identifying relationship is denoted by a double diamond.

 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 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.

 An identifying relationship should not have any descriptive

attribute.
 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.
Difference between Strong Entity Set and Weak Entity
Set:
EXTENDED E-R 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.
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.

 It is depicted by a triangle component labeled ISA.

 The ISA relationship may also be referred to as a superclass-

subclass relationship
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.
Generalization Specialization
Attribute inheritance:

 It is mechanism by which the attributes of the higher-level entity sets are

inherited by the lower-level entity sets.

 For example, customer and employee inherit the attributes of person.

Constraints on Generalizations:
1. Constraint involves determining which entities can be members of a given
lower-level entity set.
a. Condition-defined:

a. User -defined

2. Constraint relates to whether or not entities may belong to more than one
lower-level entity set within a single generalization.
a.. Disjoint

a. Overlapping
3. Completeness constraint:
It 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.

a. Total generalization or specialization

a. Partial generalization or specialization

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.
 Aggregation is an abstraction through which relationships are treated as higher
level entities.
 It shows relationship among relationships.
DESIGN ISSUES
 There are different ways of defining entity sets and relationships among
them.
 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.
 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.

1.Use of Entity Set vs Attribute

2. Entity Sets Vs Relationship Sets

 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 :

 The data are stored multiple times, wasting storage space, and
 Updates potentially leave the data in an inconsistent state.
Binary versus n-ary Relationship Sets:
 It is possible to replace a nonbinary (n-ary, for n > 2)
relationship set by a number of distinct binary relationship sets.

 There are some relationships that are naturally non-binary.

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.
 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.
 DATABASE DESIGN FOR BANKING ENTERPRISE

Requirements:
 The bank is organized into branches.

 Bank customers are identified by their customer-id values. A customer may

be associated with a particular banker, who may act as a loan officer
or personal banker.

 Bank employees are identified by their employee-id values.

 The bank offers two types of accounts—savings and checking accounts.

 A loan originates at a particular branch and can be held by one or more

customers.