COLLEGE OF INFORMATION AND COMPUTING SCIENCES
ICS2607 Information Management
MODULE 3
Data Design: Entity-Relationship
Model
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Intended Learning Outcomes
At the end of this topic, the learners will be able to:
• create and interpret Entity-Relationship (ER) diagrams
accurately, representing entities, attributes, relationships,
and cardinality;
• design a relational database schema by identifying
entities, attributes, and relationships, and then apply
normalization techniques to eliminate data redundancy,
ensuring data integrity;
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Intended Learning Outcomes
At the end of this topic, the learners will be able to:
• define and apply cardinality and participation constraints
correctly within an ER diagram, understanding how they
govern relationships between entities; and
• evaluate a given database design, identifying potential
improvements for efficiency, data integrity, and usability,
and apply optimizations as needed.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Topic Outline
I.The Entity Relationship Model
II.Developing ER Diagram
III.Database Design Challenges
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
I: The Entity-Relationship Model
• The Entity-Relationship Model is a conceptual framework
used in database design to describe the structure of a
database. It focuses on representing entities (objects,
concepts, things) and the relationships between them.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
The Entity-Relationship Model
• An Entity-Relationship Diagram (ERD) is a visual
representation of the Entity-Relationship Model. It is a
diagrammatic way to illustrate the entities, attributes,
and relationships within a database system.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
The Entity-Relationship Model
• ERDs are commonly used during the database design
phase to create a clear and understandable overview of
the data model.
• They provide a means for stakeholders to communicate
about the structure and requirements of a database
system.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
ERD Conventions: Chen’s Notation
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
ERD Conventions: Crow’s Foot Notation
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Entities
• An entity in the ERM corresponds to a table—not to a row—in
the relational environment.
• The ERM refers to a table row as an entity instance or entity
occurrence.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
• Required Attributes
- attributes that must have a value (requires)
• Optional Attributes
- attribute that does not require a value; therefore, it can
be left empty.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
• Simple Attributes
- attribute that cannot be subdivided.
• Composite Attributes
- attributes that can be further subdivided to yield
additional attributes.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
• Single-Valued Attributes
- attribute that can have only a single value.
• Multi-valued Attributes
- attributes that can have many values
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
• Domains
• the set of possible values for a given attribute
• Identifiers
• identifiers—one or more attributes that uniquely
identify each entity instance.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
• Derived Attributes
• attribute whose value is calculated (derived) from
other attributes. The derived attribute need not be
physically stored within the database; instead, it can
be derived by using an algorithm.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Attributes
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
To Store or Not to Store?
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationships
• A relationship is an association between entities.
• The entities that participate in a relationship are
also known as participants, and each relationship
is identified by a name that describes the
relationship.
• The relationship name is an active or passive verb.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationships
• Relationships between entities always operate in
both directions. To define the relationship
• between the entities named CUSTOMER and
INVOICE, you would specify that:
• A CUSTOMER may generate many INVOICEs.
• Each INVOICE is generated by one CUSTOMER.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationships
• Types of Relationships
• 1:1 (one is to one) -- rare
• 1:M (one is to many) – ideal
• M:N (many is to many) – cannot be directly
implemented
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationships
• 1 : 1 Relationship
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationships
• 1 : M Relationship
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationships
• M : M Relationship
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Connectivity and Cardinality
• The term connectivity is used to describe the
relationship classification.
• Cardinality expresses the minimum and
maximum number of entity occurrences
associated with one occurrence of the related
entity.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Connectivity and Cardinality
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Connectivity and Cardinality
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Existence Dependence
• An entity is said to be existence-dependent if it
can exist in the database only when it is
associated with another related entity
occurrence.
• Thus, it is required to have a mandatory foreign
key—that is, a foreign key attribute that cannot be
null.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Existence Dependence
• Dependent (loan)--dominant
• Independent (payment) – subordinate
• If a loan is deleted, then all associated payment will be
deleted also.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Existence Independence
• If an entity can exist apart from all of its related
entities, then it is existence-independent, and it is
referred to as a strong entity or regular entity.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Strength
• Weak (Non-Identifying) Relationship
• Exists if the primary key of the related entity does not
contain a primary key component of the parent entity.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Strength
• Weak (Non-Identifying) Relationship
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Strength
An implementation of
weak relationship.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Strength
• Strong (Identifying) Relationship
• Exists when the primary key of the related entity
contains a primary key component of the parent
entity.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Strength
• Strong (Identifying) Relationship
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Strength
An implementation of
strong relationship.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Weak Entity
• A weak entity is one that meets two conditions:
• The entity is existence-dependent; it cannot exist
without the entity with which it has a relationship.
• The entity has a primary key that is partially or totally
derived from the parent entity in the relationship.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Participation
• Optional participation means that one entity
occurrence does not require a corresponding
entity occurrence in a particular relationship.
• Mandatory participation means that one entity
occurrence requires a corresponding entity
occurrence in a particular relationship.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Participation
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Participation
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Relationship Degree
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Developing ER Diagram
• The process of database design is iterative rather
than a linear or sequential process. It involves:
• Creating a detailed narrative of the organization’s
description of operations.
• identifying the business rules based on the description
of operations.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Developing ER Diagram
- Identifying the main entities and relationships
from the business rules.
- Developing the initial ERD.
- Identifying the attributes and primary keys
that adequately describe the entities.
- Revising and review the ERD.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
ERD: An Example
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Example:
Draw an ER diagram for the following application from
the manufacturing industry:
1. Each supplier has a unique name.
2. More than one supplier can be located in the same
city.
3. Each part has a unique part number.
4. Each part has a color.
5. A supplier can supply more than one part.
6. A part can be supplied by more than one supplier.
7. A supplier can supply a fixed quantity of each part.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Example#2
UPS prides itself on having up-to-date information on the processing
and current location of each shipped item. To do this, UPS relies
on a company-wide information system. Shipped items are the
heart of the UPS product tracking information system. Shipped
items can be characterized by item number (unique), weight,
dimensions, insurance amount, destination, and final delivery date.
Shipped items are received into the UPS system at a single retail
center. Retail centers are characterized by their type, uniqueID, and
address. Shipped items make their way to their destination via
one or more standard UPS transportation events (i.e., flights,
truck deliveries). These transportation events are characterized by
a unique scheduleNumber, a type (e.g, flight,truck), and a
deliveryRoute.
i) Draw an Entity Relationship Diagram (ERD) for the above
scenario by clearly identifying entities, attributes and relationships.
ii) Clearly indicate cardinality constraints in the above ERD.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#1: Printer Supply
Departments, identified by ID, operate a variety of printers, each
printer located in a particular room. We need to keep all rooms
information, each room is identified by a roomNo and buildingNo,
location. Printers are supplied by a number of suppliers, identified by
name, with each supplier charging a different price for a given printer,
but also providing different date. A room can have many printers. Each
supplier can supply many printers.
Draw the ERD.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#2: Car Dealership
Create an ERD for a car dealership. The dealership sells both new and used
cars, and it operates a service facility. Base your design on the following
business rules:
• A salesperson may sell many cars, but each car is sold by only one
salesperson.
• A customer may buy many cars, but each car is bought by only one
customer.
• A salesperson writes a single invoice for each car he or she sells.
• A customer gets an invoice for each car he or she buys.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#2: Car Dealership
• A customer may come in just to have his or her car serviced; that is, a customer need
not buy a car to be classified as a customer.
• When a customer takes one or more cars in for repair or service, one service ticket is
written for each car.
• The car dealership maintains a service history for each of the cars serviced. The service
records are referenced by the car’s serial number.
• A car brought in for service can be worked on by many mechanics, and each mechanic
may work on many cars.
• A car that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning
a fuel injector nozzle does not require providing new parts).
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#3: Department ERD
A department employs many employees, but each
employee is employed by one department.
Some employees, known as "rovers," are not assigned to
any department.
A division operates many departments, but each
department is operated by one division.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#3: Department ERD
An employee may be assigned to many projects, and a project
may have many employees assigned to it.
A project must have at least one employee assigned to it.
One of the employees manages each department, and each
department is managed by one employee
One of the employees runs each division, and each division is run
by one employee. Draw the ERD.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#4: Publishing House
Based on the following business rules, draw a Crow's Foot Model:
• A course may require none or one other course as its
prerequisite. One course can be a prerequisite for only one
other course.
• One course can use as many as 10 resources. The resources can
be Web sites, journal articles, images, or audios
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#4: Publishing House
• One resource will be used by at least one course or many
courses
• One resource can have multiple creators, but only the first
creator is recorded in the CREATOR table
• One creator may develop multiple resources
• One publisher can publish many resources
• One resource is published by one publisher or hasn't been
published yet.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#5: Hospital Appointment
A doctor can be scheduled for many appointments but many not have
any schedule at all. Each appointment is scheduled with exactly one
doctor. Doctor is compulsory for appointment and appointment is
optional for doctor.
A patient can schedule one or more appointments. One appointment is
scheduled with exactly one patient. An appointment must generate
exactly one bill, a bill is generated by only one appointment. Both are
compulsory for each other.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#5: Hospital Appointment
One payment is applied to exactly one bill and one bill can be
paid off over time by several payments. Payment is optional for
bill while bill is compulsory for payment.
One payment is applied to exactly one patient and one patient
can makes payment several times. Patient is compulsory for
payment while payment is optional for patient.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Scenario#5: Hospital Appointment
A bill can be outstanding, having nothing yet paid on it at all. One
patient can make many payments, but a single payment is made by
only one patient. Some patients are insured by an insurance
company. If they are insured, they can only carry insurance with one
company. An insurance company can have many patients carry their
policies. For patients that carry insurance, the insurance company
will make payments, each single payment is made by exactly one
insurance company. Draw the ERD.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Database Design Challengers
• Database designers must often make design
compromises that are triggered by conflicting
goals, such as adherence to
• design standards (design elegance),
• processing speed, and
• information requirements.
COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Reference
Coronel, C. et al. (2017). Database Systems: Design ,
Implementation, and Management (11th ed). USA:
Cengage Learning.
