ISLAMIC UNIVERSITY IN UGANDA

Bridging Communities

DATABASE SYSTEMS
Chapter 4 : Entity–Relationship Modeling

Dr. Guma Ali

 Objectives (1/1)
At the end of the chapter, the student should be able to:
• Define entity-relationship diagram (ERD).
• State the reasons for using ERD.
• List the symbols & notations used in ERD.
• Explain the components of the ERD.
• Outline the steps involved in creating ERD.

2
 Introduction (1/3)
• An entity-relationship diagram (ERD) is a visual
representation of entities and their relationships to each
other in a database.
• It shows the relationships between entities, which are
objects or concepts that are stored in the database, and
their attributes.
• ERDs help in understanding how data is connected and
the relationships between different components of the
system. 3
Introduction (2/3)

4
 Introduction (3/3)
• ERD is created based on three principal components:
▪ entities,
▪ attributes,
▪ relationships.
• It is used in database design to define data elements,
their attributes, and the relationships among entities.
• ERDs are commonly used in the conceptual phase of
database design to visualize data and understand its
interactions. 5
Importance of ERD in Database
Design(1/1)

6
ERD Notation and Symbols (1/2)

7
Components of ERD (1/48)

9
 Components of ERD (2/48)
❑ Entities
• The entity is a real-world object or concept that has a
unique identity and can be identified by its attributes.
• For example,
 Person: Employee, Student, Patient, etc.
 Place: Store, Building, etc.
 Object: Machine, product, Car, etc.
 Event: Sale, Registration, Renewal, etc.
 Concept: Account, Course, etc. 10
 Components of ERD (3/48)
• The entity set can be considered a collection of similar
types of entities.
• In the entity set, there can be some entities that exist
which can contain similar types of values.
• For example, the employee set will contain information
from all employees.
• In an ERD, an entity is represented by a rectangle with
its name written inside.
11
 Components of ERD (4/48)
• Entities are further subdivided into:
o Strong entity
o Weak entity

12
 Components of ERD (5/48)
⚫ Strong Entity
• A strong entity is an entity that has a primary key
attribute, which uniquely identifies each instance of the
entity.
• It has its own unique key (primary key) and does not
depend on other entities for identification.
• A strong entity can exist independently of any other
entity.
13
 Components of ERD (6/48)
o For example, in a database for a school, a
"Student" entity would be a strong entity because
it has its own unique identifier, such as a
Student_Number that distinguishes one student
from another.
• Strong entity types are sometimes referred to as parent,
owner, or dominant entities.

14
 Components of ERD (7/48)
⚫ Weak Entity
• A weak entity is an entity that depends on the existence
of another entity.
• A weak entity cannot be uniquely identified on its own
and depends on a strong entity for its identification.
• It typically has a partial key, and its full identification
comes from combining the partial key with a strong
entity's primary key.
15
 Components of ERD (8/48)
• Each entity occurrence cannot be uniquely identified
using only the attributes associated with that entity
type.

16
 Components of ERD (9/48)
❑ Attribute
• An attribute is a characteristic or property that
describes an entity or a relationship between entities.
• Attributes are used to provide more detailed
information about the entities or relationships
represented in the diagram.
• Attributes can be classified as being simple or
composite, single-valued or multi-valued, and derived.
17
 Components of ERD (10/48)
⚫ Simple Attribute
• Simple attribute is an attribute composed of a single
component with an independent existence.
• Simple attributes cannot be further subdivided into
smaller components.
o Examples of simple attributes include position and
salary of the Staff entity.
• Simple attributes are sometimes called atomic attributes
18
 Components of ERD (11/48)
⚫ Composite Attribute
• A composite attribute is an attribute that can be
subdivided into smaller parts, where each part
represents a more basic attribute with independent
meaning.
• Each part of the composite attribute provides more
detailed information about the entity.
o For example, consider an entity Customer with a
composite attribute Address. 19
 Components of ERD (12/48)
o The Address can be broken down into smaller
components such as Street, City, State, and Zip Code.

o In this case, the Address attribute is a Composite

Attribute that can be split into the following components:

20
 Components of ERD (13/48)
▪ Street: 123 Main St / 456 Oak Ave
▪ City: Springfield / Chicago
▪ State: IL
▪ Zip Code: 62701 / 60616

o Breakdown of the Composite Attribute:

21
 Components of ERD (14/48)
o Here, the Address is a Composite Attribute because it
consists of several related components that together
provide the complete address information.
o In database design, these subcomponents could be stored
as separate fields to allow querying individual parts of
the address more easily.

22
 Components of ERD (15/48)
⚫ Single-Valued Attribute
• Single-valued attribute is an attribute that holds a
single value for each occurrence of an entity type.
• The majority of attributes are single-valued.
o For example, each occurrence of the Branch entity
type has a single value for the branch number
(branchNo) attribute (B003), and therefore the
branchNo attribute is referred to as being single-
valued. 23
 Components of ERD (16/48)
⚫ Multi-Valued Attribute
• Multi-valued attribute is an attribute that holds multiple
values for each occurrence of an entity type.
o For example, each occurrence of the Branch
entity type can have multiple values for the telNo
attribute (e.g., branch number B003 has telephone
numbers 0141-339-2178 and 0141-339-4439) and
therefore the telNo attribute in this case is multi-
valued. 24
 Components of ERD (17/48)
⚫ Derived Attribute
• A derived attribute is an attribute whose value is
calculated from other attributes in the database, rather
than being stored directly.
• It is typically not physically stored but derived when
needed through computation.
o For example, consider an entity Person that stores
the person's DateOfBirth, and we need to derive
their Age. 25
 Components of ERD (18/48)
o The Age attribute can be calculated based on the
current date and the DateOfBirth attribute.

o Age is derived from the formula:

▪ Age = Current Date - DateOfBirth

26
 Components of ERD (19/48)
Attributes on Relationships

• Attributes can also be assigned to relationships.

o For example, consider the relationship Advertises,

which associates the Newspaper and
PropertyForRent entity types.

• To record the date the property was advertised and the

cost, we associate this information with the Advertises
relationship as attributes called dateAdvert and cost.
27
 Components of ERD (20/48)
• We represent attributes associated with a relationship
type using the same symbol as an entity type.
• However, to distinguish between a relationship with an
attribute and an entity, the rectangle representing the
attribute(s) is associated with the relationship using a
dashed line.
o For example, Figure 12.13 shows the Advertises
relationship with the attributes dateAdvert and cost.
28
Components of ERD (21/48)

29
 Components of ERD (22/48)
• The presence of one or more attributes assigned to a
relationship may indicate that the relationship conceals
an unidentified entity type.
o For example, the presence of the dateAdvert and
cost attributes on the Advertises relationship
indicates the presence of an entity called Advert.
• Attributes are shown as ellipses (ovals) connected to
their respective entity.
30
 Components of ERD (23/48)
❑ Relationship
• A relationship represents how two or more entities are
related to each other.
• Relationships are represented by diamonds or lines
connecting the entities.
• Relationships are identified with verbs or verb phrases
such as, A student attends a lecture, A lecturer conducts
a lecture, A student studies a course
31
 Components of ERD (24/48)
Types of Relationships
⚫ Unary relationship: Relationship between instances of
the same entity (e.g., an employee manages other
employees).
⚫ Binary relationship: Relationship between instances of
two different entities (e.g., Student enrolls in Course).
⚫ Ternary relationship: Relationship involving three
entities (e.g., Supplier, Product, and Warehouse).

32
 Components of ERD (25/48)
Relationship Occurrence
• Relationship occurrence is a uniquely identifiable
association that includes one occurrence from each
participating entity type.
• A relationship occurrence indicates the particular entity
occurrences that are related.
o Consider a relationship type called Has, which
represents an association between Branch and Staff
entities, i.e., Branch Has Staff. 33
 Components of ERD (26/48)
• Each occurrence of the Has relationship associates
one Branch entity occurrence with one Staff entity
occurrence.

34
 Components of ERD (27/48)
Structural Constraints
• The constraints should reflect the restrictions on the
relationships as perceived in the “real world.”
• The main type of constraint on relationships is called
multiplicity.
• Multiplicity is the number of possible occurrences of an
entity type that may relate to a single occurrence of an
associated entity type through a particular relationship.
35
 Components of ERD (28/48)
❑ Cardinality

• Cardinality defines the nature of the relationship

between two entities, specifically how many instances of
one entity relate to instances of another entity.

• Cardinal relationships are essential for accurately

modeling and understanding the interactions and
constraints between entities in a database.

36
 Components of ERD (29/48)
• The four types of cardinal relationships are:
• One-to-One Relationships
• One-to-Many Relationships
• Many-to-One Relationships
• Many-to-Many Relationships

37
 Components of ERD (30/48)
 One-to-One (1:1) Relationships
• A one-to-one (1:1) relationship is where one instance of
an entity is associated with one instance of another
entity.
• In this type of cardinality mapping, an entity in A is
connected to at most one entity in B or we can say that
a unit or item in B is connected to at most one unit or
item in A.
38
 Components of ERD (31/48)
o For example, consider entities Person and
Passport.
o Each Person has only one Passport, and each
Passport is assigned to only one Person.
o In an ERD, there would be a direct connection
between the Person and Passport entities, indicating
that one person can only have one passport, and
one passport belongs to one person.

39
 Components of ERD (32/48)
 One-to-Many (1:*) Relationships
• One-to-many (1:*) relationship is where one instance of
an entity is associated with multiple instances of
another entity.
• In this type of cardinality mapping, an entity in A is
associated with any number of entities in B or we can
say that one unit or item in B can be connected to at
most one unit or item in A.
40
 Components of ERD (33/48)
o For example, consider entities Teacher and Class.
o A Teacher can teach many Classes, but each Class
is managed by only one Teacher.
o In an ERD, there would be a connection from
Teacher to Class, showing that one teacher can be
responsible for several classes, but each class is
managed by one teacher.

41
 Components of ERD (34/48)
 Many-to-One (*:1) Relationship
• Many-to-one (*:1) relationship is where more than one
element of an entity is related to a single element of
another entity.
• More than one entity from entity set A can be associated
with at most one entity of entity set B.
• However, an entity from entity set B may or may not be
associated with more than one entity from entity set A.
42
 Components of ERD (35/48)
o For example, consider entities Employee and
Department
o Many Employees can work in one Department, but
each Employee belongs to only one Department.
o In an ERD, the connection between Employee and
Department would show multiple employees linked
to a single department, but each employee works in
only one department.

43
 Components of ERD (36/48)
 Many-to-Many (*:*) Relationship
• Many-to-many (*:*) relationship is where multiple
instances of an entity are associated with multiple
instances of another entity.
• One entity from A can be associated with more than one
entity from B and vice versa.

44
 Components of ERD (37/48)
o For example, consider entities Student and Course
o A Student can enroll in many Courses, and a
Course can have many Students enrolled.
o In an ERD, there would be a many-to-many
connection between Student and Course, usually
represented through an intermediate associative
entity (like Enrollment), showing the linkage
between students and courses.

45
 Components of ERD (38/48)
 Entity Key
• An entity key is an attribute or a set of attributes that
uniquely identifies an entity within an entity set in a
database.
• There are many types of entity keys in the database:
▪ Candidate key
▪ Primary key
▪ Foreign key
▪ Alternate key
▪ Composite key 46
 Components of ERD (39/48)
 Candidate Key
• A Candidate key is a minimal set of attributes that can
uniquely identify a tuple in a relation.
• There can be more than one candidate key in a table,
and each can serve as a primary key.
o Example: Consider a table Employee:

47
 Components of ERD (40/48)

• In this case:
o EmployeeID and SSN are candidate keys because
both can uniquely identify a row in the table.
o Only one of these will be selected as the Primary
Key, while the other remains a Candidate Key.

48
 Components of ERD (41/48)
 Primary Key
• The primary key is the candidate key that is chosen to
uniquely identify records in a table.
• It must contain unique values, and it cannot contain
NULL values.
o Example: Using the same Employee table, let’s say
the EmployeeID is chosen as the primary key:

49
 Components of ERD (42/48)

• Here, EmployeeID is the Primary Key since it uniquely

identifies each employee in the table.

50
 Components of ERD (43/48)
 Foreign Key

• A foreign key is an attribute (or set of attributes) in one

table that refers to the Primary Key of another table.

• It is used to establish relationships between tables.

o Example: Consider two tables: Employee and

Department:

51
Components of ERD (44/48)

52
 Components of ERD (45/48)
• In this case, DepartmentID in the Employee table is a
Foreign Key referencing the DepartmentID in the
Department table.
• This creates a relationship between the Employee and
Department tables.

53
 Components of ERD (46/48)
 Alternate Key
• An alternate key is any candidate key that is not
selected as the primary key.
• It can still uniquely identify rows in the table.
o Example: In the Employee table example:

• Here, SSN is an Alternate Key since it can uniquely

identify each row but is not chosen as the Primary Key.
54
 Components of ERD (47/48)
 Composite Key

• A composite key is a key that consists of more than one

attribute to uniquely identify a row.

o Example: Consider a StudentCourse table where

students can enroll in multiple courses:

55
 Components of ERD (48/48)
• Here, neither StudentID nor CourseID alone can
uniquely identify a record.

• However, the combination of StudentID and CourseID

forms a Composite Key, which can uniquely identify
each enrollment record.

56
How to Create an ERD (1/9)

57
 How to Create an ERD (2/9)
Example
• In a university, a student enrolls in Courses. A
student must be assigned to at least one or more
Courses. Each course is taught by a single Professor.
To maintain instruction quality, a professor can
deliver only one course.

58
 How to Create an ERD (3/9)
Step 1: Entity Identification
• We have three entities
▪ Student
▪ Course
▪ Professor

59
 How to Create an ERD (4/9)
Step 2: Relationship Identification
• We have the following two relationships
▪ The student is assigned a course
▪ Professor delivers a course

60
 How to Create an ERD (5/9)
Step 3: Cardinality Identification
• For the problem statement, we know that,
▪ A student can be assigned multiple courses
▪ A professor can deliver only one course

61
 How to Create an ERD (6/9)
Step 4: Identify Attributes
• Initially, it’s important to identify the attributes without
mapping them to a particular entity.
• Once, you have a list of attributes, you need to map
them to the identified entities.
• Ensure an attribute is to be paired with exactly one
entity.

62
 How to Create an ERD (7/9)
• If you think an attribute should belong to more than one
entity, use a modifier to make it unique.
• Once the mapping is done, identify the primary keys.
• If a unique key is not readily available, create one.

63
How to Create an ERD (8/9)

64
 How to Create an ERD (9/9)
Step 5: Create the ERD
• A more modern representation of ERD Example

65
Best Practice for Developing Effective
ER Diagrams (1/2)
• Creating ERD is essential for designing robust
databases and ensuring that data relationships are
accurately represented.
• Some of the best practices for developing effective
ERD include:

66
Best Practice for Developing Effective
ER Diagrams (2/2)

67
 ERD Tasks (1/2)
• Arua city is interested in designing a database that
will track its researchers. Information of interest
includes researcher name, title, position; university
name, location, enrollment; and research interests.
Each researcher is associated with only one
institution, and each researcher has several research
interests.

68
 ERD Tasks (2/2)
• A salesperson may manage many other salespeople. A
salesperson is managed by only one salespeople. A
salesperson can be an agent for many customers. A customer
is managed by one salespeople. A customer can place many
orders. An order can be placed by one customer. An order
lists many inventory items. An inventory item may be listed
on many orders. An inventory item is assembled from many
parts. A part may be assembled into many inventory items.
Many employees assemble an inventory item from many
parts. A supplier supplies many parts. A part may be supplied
by many suppliers.
69
 Reading Lists (1/2)
1. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020).
Database System Concepts (7th ed.). New York: McGraw-
Hill.
2. Lemahieu, W., Broucke, V. S., & Baesens, B. (2018).
Principles of database management: The practical guide to
storing, managing and analyzing big and small data.
Cambridge University Press.
3. Elmasri, R., & Navathe, S. (2017). Fundamentals of
Database Systems (7th ed.). Pearson India
70
 Reading Lists (2/2)
4. Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database
systems: Introduction to databases and data warehouses.
Prospect Press.
5. Connolly, T., & Begg, C. (2015). Database Systems: A
Practical Approach to Design, Implementation, and
Management (6th ed.). Essex, Harlow, England: Pearson
Education Limited.
6. Hoffer, J. A., Ramesh, V., & Heikki, T. (2013). Modern
Database Management (11th ed.). Boston, U.S: Pearson
Education Limited. 71
72