IT - 304
Database Systems
for BS (IT)
Lecture 6:
Entity Relationship (E-R) Modeling
Hasan Raza
Lecturer CS & IT
Govt. Postgraduate College, Sheikhupura
Existence Dependency
An instance of one entity can’t exist without the existence of some other
related entity.
An entity having an existence dependency is referred as Weak Entity and it
may optionally be represented by double rectangle.
An entity type A is said existent-dependent on an entity type B if the
existence of A depends on the existence of B
If B is deleted ,A must also be deleted. A is subordinate and B is dominant.
For Example:
Employee’s dependents cannot exist without employee.
If the employee doesn’t exist, i.e., all dependents of employee are deleted if employee
is deleted
For example, a company insurance policy insures an employee and any
dependents. For the purpose of describing an insurance policy, an
EMPLOYEE might or might not have a DEPENDENT, but the DEPENDENT
must be associated with an EMPLOYEE. Moreover, the DEPENDENT
cannot exist without the EMPLOYEE; that is, a person cannot get insurance
coverage as a dependent unless the person is a dependent of an employee.
DEPENDENT is the weak entity in the relationship “EMPLOYEE has
DEPENDENT.”
Relationship Strength
Weak (non-identifying)
One entity is existence-independent on another
PK of related entity doesn’t contain PK component of parent
entity
Strong (identifying)
One entity is existence-dependent on another
PK of related entity contains PK component of parent entity
4
Relationship Strength
Weak (non-identifying)
COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION,
CRS_CREDIT)
CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION,
CLASS_TIME, ROOM_CODE, PROF_NUM)
5
Relationship Strength
Strong (identifying)
COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION,
CRS_CREDIT)
CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME,
ROOM_CODE, PROF_NUM)
6
Composite First_Name Last_Name Middle_Name
attribute
Identifier
Dependent_Name Date_of_Birth
Employee_ID Employee_Name
DEPENDENT
ENTITY
Has
EMPLOYEE
Weak entity
Strong entity Identifying
relationship
ER Model – Basic Constructs
Entity Strong Weak
Strong
Identifying
Relationship
Attribute Multi valued
Data model segment for class scheduling
Weak Entity
Existence-dependent on another entity
Has primary key that is partially or totally
derived from parent entity
Associative (Composite) Entities
This associative entity (also known as a composite or
bridge entity) is composed of the primary keys of each of
the entities to be connected.
Used to ‘bridge’ between M:N relationships
Composite Entities (con’t.)
Think back to relationship
connectivity for a moment,
We had 3 different types of
connectivity.
One to One
One to Many
Many to Many
Composite Entities (con’t.)
When finalizing your ERD's
you should not leave many to
many relationships in the
diagram
Many to Many relationship will
cause a problem when we
convert our ERD into the
relational model.
We can eliminate many to
many relationships by
including composite entities in
our diagram
Composite Entities (con’t.)
Converting to Composite Entities
The first thing we need to do is to
identify where in our ERD we
have many to many relationship.
If we spot any, we turn the
relationship symbol into the
composite entity symbol.
The composite entity is represented by
combining the relationship(diamond)
symbol with the entity symbol(box)
14
Composite Entities (con’t.)
Converting to Composite Entities
Entity names are
usually nouns(objects) and
relationship are usually described
with a verb(action)
Since the composite entity is more
of an entity than a relationship, we
should change the name of the
relationship
15
Composite Entities (con’t.)
Converting to Composite Entities
We then need to re-do the relationship
connectivity. The class and student
entity are no longer directly related to
each other.
The new relationship is now between
the class and enrollment, and the
student and enrollment.
We need to reconsider the connectivity
between the 3 new entities.
Each class will have many enrollments and
each student will have many enrollments as
well.
16
Composite Entities (con’t.)
Converting to Composite Entities
We now have a composite entity, The composite entity like
any other entities needs to have a key.
In the case of a composite entity, we never give it its own
ID number
In the case of a composite entity will always have a
composite key that is the combination of the other two
table's key,
Example, if the Class entity has a classID, and the Student
entity has a stuID, the composite entity's key will be ClassID
and StuID.
free to give extra attributes to
the composite entity. in this
case let's gibe it an enrollment
date.
Composite Entities (con’t.)
19
Enhanced ERD (EERD)
ENTITY SUPERTYPES AND SUBTYPES
Purpose
Supertypes and subtypes occur frequently in the real world
food order types (Dine in, Takeaway)
grocery bag types (paper, plastic)
payment types (check, cash, credit)
You can typically associate ‘choices’ of something with supertypes and
subtypes.
For example, what will be the method of payment – cash, check or credit
card?
Understanding real world examples helps us understand how and when to
model them.
Evaluating Entities
Often some instances of an entity have attributes and/or relationships that
other instances do not have.
Imagine a business which needs to track payments from customers.
Customers can pay by cash, by check, or by credit card.
All payments have some common attributes: payment date, payment
amount, and so on.
But only credit cards would have a “card number” attribute.
And for credit card and check payments, we may need to know which
CUSTOMER made the payment, while this is not needed for cash payments.
Evaluating Entities
Should we create a single
PAYMENT entity or three
separate entities CASH,
CHECK, and CREDIT CARD?
And what happens if in the
future we introduce a fourth
method of payment?
Super Type and Sub Type
A Super type is a generic entity type that is subdivided into subtypes.
A Subtype is a subset of a super type that shares the common attributes of the super type
plus it has some of its own attributes that distinguish it from other subtypes.
The super type and subtypes employ the concept of Generalization and Categorization.
Generalization is the concept that some entities are subtypes of other more general entities.
E.g. Bird, Vehicle etc.
Categorization is the concept that an entity comes in various subtypes.
Super type & Subtype representation is used to represent the entities that are very much
similar.
Attribute Inheritance:
Subtype entities inherit values of all attributes of the supertype
Subtype entities inherit their primary key from their supertype
An instance of a subtype is also an instance of the supertype
E-R Model
Super Types, Subtypes - Example
EMPLOYEE TYPE ATTRIBUTES
1. Hourly ENo, EName, Address, Hire_Date, Hourly_Rate
2. Salaried ENo, EName, Address, Hire_Date, Salary
3. Contract ENo, EName, Address, Hire_Date, Rate
We have three choices for ER diagram:
1. Define a single entity type Employee with all the possible attributes in it.
2. Define a separate entity type for each employee type.
3. Define a supertype with all the common attributes and introduce its
subtypes having only the applicable attributes.
ER Diagram
Employee supertype with three subtypes
All employee subtypes will have
emp nbr, name, address, and
date-hired
Each employee subtype will
also have its own attributes
E-R Model
Super Types, Subtypes
The relationship between each subtype and its super type is called ISA
relationship.
The relationship is read from the subtype to the super type.
The cardinality of the relationship from subtype to super type is always
mandatory one and from super type to subtype is optionally one.
Henceforth, cardinality may be omitted in diagram.
Supertype/subtype relationships in a hospital
Both outpatients and
resident patients are
cared for by a
responsible physician
Only resident patients are
assigned to a bed
Example of generalization Three entity types: CAR, TRUCK, and MOTORCYCLE
All these types
of vehicles
have common
attributes
Generalization to VEHICLE supertype
So we put
the shared
attributes in
a supertype
Note: no subtype for
motorcycle, since it has no
unique attributes
Constraints in Supertype/ Completeness Constraint
Completeness Constraints: Whether an instance of a supertype
must also be a member of at least one subtype
Total Specialization Rule: Yes (double line)
Partial Specialization Rule: No (single line)
Figure 4-6 Examples of completeness constraints
a) Total specialization rule
A patient must be
either an outpatient or
a resident patient
Figure 4-6 Examples of completeness constraints (cont.)
b) Partial specialization rule
A vehicle could
be a car, a
truck, or neither
Constraints in Supertype/ Disjointness constraint
Disjointness Constraints: Whether an instance of a supertype may
simultaneously be a member of two (or more) subtypes
Disjoint Rule: An instance of the supertype can be only ONE of the subtypes
Overlap Rule: An instance of the supertype could be more than one of the subtypes
Figure 4-7 Examples of disjointness constraints
a) Disjoint rule a) Disjoint rule
A patient can either be
outpatient or resident, but not
both
Figure 4-7 Examples of disjointness constraints (cont.)
b) Overlap rule b) Overlap rule
A part may be
both purchased
and
manufactured
Figure 4-10 Example of supertype/subtype hierarchy
Textbook
Carlos Coronel, Steve Morris, “Database Systems” Design, Implementation, Management, 12th Ed.
Course Technology, 2016”.
Reference book
Jeffrey Hoffer, “Modern Database Management ” Design,
Implementation, Management, 10th Edition”
Thomas Connolly, “Database Systems: A Practical Approach to
Design, Implementation and Management (6th Ed.)”
Elmasri, “Fundamentals of Database Systems: (7th Ed.)”
https://www.vertabelo.com/blog/crow-s-foot-notation/
