Lecture 2: Introduction to Entity Relationship
(ER) model
Dr Tran Duc Minh and Dr Hung Tran
DATCOM Lab
Faculty of Data Science and Artificial Intelligence
College of Technology, National Economics University
Email: [email protected], [email protected]
August 29, 2024
DATCOM Lab (DATCOM Lab) August 29, 2024 1 / 51
Outline
1 Overview
2 Entities
3 Attributes
4 Relationships
5 Connectivity and Cardinality
6 Existence Dependence
7 Relationship Strength
DATCOM Lab (DATCOM Lab) August 29, 2024 2 / 51
Overview
Overview
Conceptual models are used in the conceptual design of databases.
The Entity Relationship Model (ERM) is a conceptual model used to
understand and design the data requirements of an organization.
The Entity Relationship Diagram (ERD) is a graphical representation
of the ERM. It helps to visualize the database structure, making it
easier to understand how data is organized and how different pieces of
data are related.
ERDs depict the database’s main components:
▶ Entities
▶ Attributes
▶ Relationships
DATCOM Lab (DATCOM Lab) August 29, 2024 3 / 51
Entities
Entities
An entity is an object of interest to the end user.
An entity actually refers to the entity set and not to a single entity
occurrence.
An entity in the ERM corresponds to a table in the relational
environment.
The ERM refers to a table row as an entity instance or entity
occurrence.
DATCOM Lab (DATCOM Lab) August 29, 2024 4 / 51
Attributes
Attributes
Attributes are characteristics of entities.
For example, the STUDENT entity includes the attributes
STU LNAME, STU FNAME, STU INITIAL, STU EMAIL and
STU PHONE
DATCOM Lab (DATCOM Lab) August 29, 2024 5 / 51
Attributes
Required and Optional Attributes
A required attribute is an attribute that must have a value.
An optional attribute is an attribute that does not require a value.
For example: The two boldfaced attributes in the Crow’s Foot
notation indicate that data entry will be required. The remaining
attributes are not presented in boldface in the entity box, these are
optional attributes.
DATCOM Lab (DATCOM Lab) August 29, 2024 6 / 51
Attributes
Domains
Attributes have a domain. A domain is the set of possible values for
a given attribute.
For example:
▶ The domain for a grade point average (GPA) attribute is written (0,
10) because the lowest possible GPA value is 0 and the highest possible
value is 10.
▶ The domain for a gender attribute consists of only two possibilities:
Male or Female.
▶ The domain for a person’s date of birth attribute consists of all dates
that fit in a range.
DATCOM Lab (DATCOM Lab) August 29, 2024 7 / 51
Attributes
Identifiers (Primary Keys)
The ERM uses identifiers which are one or more attributes that
uniquely identify each entity instance.
In the relational model, entities are mapped to tables, and the entity
identifier is mapped as the table’s primary key (PK).
Identifiers are underlined in the ERD
▶ IMAGE OF the Crow’s Foot notation WITH UNDERLINE OF KEY (if
possible)
▶ Key attributes are also underlined in a frequently used shorthand
notation for the table structure, called a relational schema, that uses
the following format:
CAR (CAR VIN, MOD CODE, CAR YEAR, CAR COLOR)
Each car is identified by a unique Vehicle Identification Number, or
CAR VIN.
DATCOM Lab (DATCOM Lab) August 29, 2024 8 / 51
Attributes
Composite Identifiers
A composite identifier is a primary key composed of more than one
attribute.
For example: Each CLASS entity instance (occurrence) is identified
using the composite primary key of CRS CODE, CLASS SECTION,
and CLASS SECTION.
CLASS (CLASS CODE, CRS CODE, CLASS SECTION,
CLASS TIME, ROOM CODE, PROF NUM)
DATCOM Lab (DATCOM Lab) August 29, 2024 9 / 51
Attributes
Composite and Simple Attributes
A composite attribute, not to be confused with a composite key, is
an attribute that can be further subdivided to yield additional
attributes.
▶ For example: A phone number such as 615-898-2368 may be divided
into an area code (615), an exchange number (898), and a four-digit
code (2368).
A simple attribute is an attribute that cannot be subdivided.
▶ For example: Age, sex, and marital status would be classified as simple
attributes.
DATCOM Lab (DATCOM Lab) August 29, 2024 10 / 51
Attributes
Single-Valued Attributes
A single-valued attribute is an attribute that can have only a single
value.
▶ A single-valued attribute is not necessarily a simple attribute.
For example
▶ A part’s serial number (such as SE-08-02-189935) is single-valued, but
it is a composite attribute because it can be subdivided into the region
in which the part was produced (SE), the plant within that region (08),
the shift within the plant (02), and the part number (189935).
DATCOM Lab (DATCOM Lab) August 29, 2024 11 / 51
Attributes
Multivalued Attributes
Multivalued attributes are attributes that can have many values for
a single entity instance.
For example
▶ An EMP DEGREE attribute might store the string ”BBA, MBA, PHD”
to indicate three different degrees held.
In the Chen ERM, multivalued attributes are shown by a double line
connecting the attribute to the entity. The Crow’s Foot notation does
not identify multivalued attributes.
DATCOM Lab (DATCOM Lab) August 29, 2024 12 / 51
Attributes
Implementing Multivalued Attributes
If multivalued attributes exist, the designer must decide on one of two
possible courses of action:
1 Within the original entity, create several new attributes, one for each
component of the original multivalued attribute.
▶ For example: the CAR entity’s attribute CAR COLOR can be split to
create the new attributes CAR TOPCOLOR, CAR BODYCOLOR, and
CAR TRIMCOLOR, which are then assigned to the CAR entity.
▶ The downside to this approach is that new changes in the environment
will never create a situation where an instance would have more values
than before.
DATCOM Lab (DATCOM Lab) August 29, 2024 13 / 51
Attributes
Implementing Multivalued Attributes
If multivalued attributes exist, the designer must decide on one of two
possible courses of action:
2 Create a new entity composed of the original multivalued attribute’s
components. Then, this new entity is related to the original entity in
a 1:M relationship.
For example:
▶ Creating an entity named CAR COLOR allows the designer to define
color for different sections of the car.
DATCOM Lab (DATCOM Lab) August 29, 2024 14 / 51
Attributes
Implementing Multivalued Attributes
If multivalued attributes exist, the designer must decide on one of two
possible courses of action:
2 Create a new entity composed of the original multivalued attribute’s
components. Then, this new entity is related to the original entity in
a 1:M relationship.
For example:
▶ Then, this new CAR COLOR entity is related to the original CAR
entity in a 1:M relationship.
DATCOM Lab (DATCOM Lab) August 29, 2024 15 / 51
Attributes
Implementing Multivalued Attributes
If multivalued attributes exist, the designer must decide on one of two
possible courses of action:
2 Create a new entity composed of the original multivalued attribute’s
components. Then, this new entity is related to the original entity in
a 1:M relationship.
▶ Creating a new entity in a 1:M relationship with the original entity
yields several benefits: it is a more flexible, expandable solution, and it
is compatible with the relational model.
DATCOM Lab (DATCOM Lab) August 29, 2024 16 / 51
Attributes
Derived Attributes
A derived attribute is an 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.
For example
▶ An employee’s age, EMP AGE, may be found by computing the integer
value of the difference between the current date and the EMP DOB.
A derived attribute is indicated in the Chen notation by a dashed line
that connects the attribute and the entity. The Crow’s Foot notation
does not have a method for distinguishing the derived attribute from
other attributes.
DATCOM Lab (DATCOM Lab) August 29, 2024 17 / 51
Attributes
Derived Attributes
DATCOM Lab (DATCOM Lab) August 29, 2024 18 / 51
Attributes
Derived Attributes
Derived attributes are sometimes referred to as computed attributes.
The decision to store derived attributes in database tables depends on
the processing requirements and the constraints placed on a particular
application. The designer should be able to balance the design in
accordance with such constraints.
Advantages and disadvantages of storing derived attributes
DATCOM Lab (DATCOM Lab) August 29, 2024 19 / 51
Relationships
Why should a larger table within a database be divided?
DATCOM Lab (DATCOM Lab) August 29, 2024 20 / 51
Relationships
Why should a larger table within a database be divided?
DATCOM Lab (DATCOM Lab) August 29, 2024 21 / 51
Relationships
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.
▶ For example: a STUDENT takes a CLASS, a PROFESSOR teaches a
CLASS, a DIVISION is managed by an EMPLOYEE, and an
AIRCRAFT is flown by a CREW.
DATCOM Lab (DATCOM Lab) August 29, 2024 22 / 51
Relationships
Relationships
Relationships between entities always operate in both directions.
For example 1, to define the relations hip between the entities named
CUSTOMER and INVOICE, we would specify that:
▶ A CUSTOMER may generate many INVOICEs.
▶ Each INVOICE is generated by one CUSTOMER.
Because we know both directions of the relationship between
CUSTOMER and INVOICE, it is easy to see that this relationship can
be classified as 1:M.
DATCOM Lab (DATCOM Lab) August 29, 2024 23 / 51
Relationships
Relationships
The relationship classification is difficult to establish if we know only
one side of the relationship.
For example 2, if we specify that:
▶ A DIVISION is managed by one EMPLOYEE.
We don’t know if the relationship is 1:1 or 1:M. Therefore, we should
ask the question ”Can an employee manage more than one division?”
▶ If the answer is yes, the relationship is 1:M
▶ If the answer is no, the relationship is 1:1
DATCOM Lab (DATCOM Lab) August 29, 2024 24 / 51
Connectivity and Cardinality
Connectivity and Cardinality
The term connectivity is used to describe the classification of the
relationship between entities. Classifications include 1:1, 1:M, and
M:N.
Cardinality expresses the minimum and maximum number of entity
occurrences associated with one occurrence of the related entity.
▶ In the ERD, cardinality is indicated by placing the appropriate numbers
beside the entities using the format (x,y).
DATCOM Lab (DATCOM Lab) August 29, 2024 25 / 51
Existence Dependence
Existence Dependence
An entity is said to be existence-dependent if its existence depends
on one or more other entities.
▶ An entity is existence-dependent if it has a mandatory foreign key (a
foreign key attribute that cannot be null).
▶ For example: If an employee wants to claim one or more dependents
for tax-withholding purposes. The DEPENDENT entity is clearly
existence-dependent on the EMPLOYEE entity because it is impossible
for the dependent to exist apart from the EMPLOYEE in the database.
DATCOM Lab (DATCOM Lab) August 29, 2024 26 / 51
Existence Dependence
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.
For example: In a university database, we have two entities,
STUDENT and COURSE.
▶ A STUDENT entity can exist independently of the COURSE entity
because a student can be admitted to the university and exist in the
database even if they haven’t enrolled in any courses yet.
▶ A COURSE entity can also exist independently of the STUDENT
entity. The university can offer a course regardless of whether any
students have enrolled in it.
DATCOM Lab (DATCOM Lab) August 29, 2024 27 / 51
Relationship Strength
Weak (Non-Identifying) Relationships
The concept of relationship strength is based on how the primary
key of a related entity is defined.
A weak relationship, also known as a non-identifying relationship,
exists if the primary key of the related entity does not contain a
primary key component of the parent entity.
The Crow’s Foot notation depicts a weak relationship by placing a
dashed relationship line between the entities.
▶ For example:
DATCOM Lab (DATCOM Lab) August 29, 2024 28 / 51
Relationship Strength
Weak (Non-Identifying) Relationships
DATCOM Lab (DATCOM Lab) August 29, 2024 29 / 51
Relationship Strength
Strong (Identifying) Relationships
A strong (identifying) relationship exists when the primary key of
the related entity contains a primary key component of the parent
entity.
The Crow’s Foot notation depicts the strong (identifying) relationship
with a solid line between the entities
▶ For example:
DATCOM Lab (DATCOM Lab) August 29, 2024 30 / 51
Relationship Strength
Strong (Identifying) Relationships
DATCOM Lab (DATCOM Lab) August 29, 2024 31 / 51
Weak Entities
Weak Entities
A weak entity is one that meets two conditions:
1 The entity is existence-dependent; it cannot exist without the entity
with which it has a relationship.
2 The entity has a primary key that is partially or totally derived from the
parent entity in the relationship.
For example, a company insurance policy insures an employee and any
dependents. 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. DEPENDENT is the weak entity in the relationship
”EMPLOYEE has DEPENDENT.”
DATCOM Lab (DATCOM Lab) August 29, 2024 32 / 51
Weak Entities
Weak Entities
A strong (identifying) relationship indicates that the related entity is
weak.
The Chen notation identifies the weak entity by using a double-walled
entity rectangle. The Crow’s Foot notation uses the relationship line
and the PK/FK designation to indicate whether the related entity is
weak.
DATCOM Lab (DATCOM Lab) August 29, 2024 33 / 51
Weak Entities
Weak Entities
DATCOM Lab (DATCOM Lab) August 29, 2024 34 / 51
Weak Entities
Weak Entities
DATCOM Lab (DATCOM Lab) August 29, 2024 35 / 51
Relationship Strength
Relationship Participation
Optional participation means that one entity occurrence does not
require a corresponding entity occurrence in a particular relationship.
For example: In the ”COURSE generates CLASS” relationship, an
entity occurrence (row) in the COURSE table does not necessarily
require the existence of a corresponding entity occurrence in the
CLASS table. Therefore, the CLASS entity is considered to be
optional to the COURSE entity.
In Crow’s Foot notation, an optional relationship between entities is
shown by drawing a small circle (O) on the side of the optional entity.
DATCOM Lab (DATCOM Lab) August 29, 2024 36 / 51
Relationship Strength
Relationship Participation
Mandatory participation means that one entity occurrence requires
a corresponding entity occurrence in a particular relationship.
▶ For example: An EMPLOYEE works in a DIVISION (A person cannot
be an employee without being assigned to a company’s division.)
If the mandatory participation is depicted graphically, it is typically
shown as a small hash mark across the relationship line, similar to the
Crow’s Foot depiction of a connectivity of 1.
DATCOM Lab (DATCOM Lab) August 29, 2024 37 / 51
Relationship Degree
Relationship Degree
A relationship degree indicates the number of entities or
participants associated with a relationship.
A unary relationship exists when an association is maintained within
a single entity.
▶ For example: An EMPLOYEE might manage another EMPLOYEE.
A binary relationship exists when two entities are associated.
▶ For example: PROFESSOR teaches CLASS.
A ternary exists when three entities are associated.
▶ For example: A DOCTOR prescribes a DRUG for a PATIENT.
DATCOM Lab (DATCOM Lab) August 29, 2024 38 / 51
Relationship Degree
Unary Relationships
For example: An employee within the EMPLOYEE entity is the
manager for one or more employees within that entity.
DATCOM Lab (DATCOM Lab) August 29, 2024 39 / 51
Relationship Degree
Binary Relationships
A binary relationship exists when two entities are associated in a
relationship.
In fact, to simplify the conceptual design, most higher-order (ternary
and higher) relationships are decomposed into appropriate equivalent
binary relationships whenever possible.
For example: ”A PROFESSOR teaches one or more CLASSes”
represents a binary relationship.
DATCOM Lab (DATCOM Lab) August 29, 2024 40 / 51
Relationship Degree
Ternary and Higher-Order Relationships
A ternary relationship implies an association among three different
entities.
For example: A DOCTOR prescribes a DRUG for a PATIENT.
▶ A DOCTOR writes one or more PRESCRIPTIONs.
▶ A PATIENT may receive one or more PRESCRIPTIONs.
▶ A DRUG may appear in one or more PRESCRIPTIONs.
DATCOM Lab (DATCOM Lab) August 29, 2024 41 / 51
Relationship Degree
Ternary and Higher-Order Relationships
DATCOM Lab (DATCOM Lab) August 29, 2024 42 / 51
Relationship Degree
Ternary and Higher-Order Relationships
DATCOM Lab (DATCOM Lab) August 29, 2024 43 / 51
Recursive Relationships
Recursive Relationships
A recursive relationship is one in which a relationship can exist
between occurrences of the same entity set. Such a condition is found
within a unary relationship.
For example:
▶ A 1:1 unary relationship may be expressed by ”an EMPLOYEE may be
married to one and only one other EMPLOYEE.”
DATCOM Lab (DATCOM Lab) August 29, 2024 44 / 51
Recursive Relationships
Recursive Relationships
For example: The 1:1 Recursive Relationship ”EMPLOYEE is married
to EMPLOYEE”
DATCOM Lab (DATCOM Lab) August 29, 2024 45 / 51
Recursive Relationships
Recursive Relationships
A recursive relationship is one in which a relationship can exist
between occurrences of the same entity set. Such a condition is found
within a unary relationship.
For example:
▶ A 1:M unary relationship can be expressed by ”an EMPLOYEE may
manage many EMPLOYEEs, and each EMPLOYEE is managed by one
EMPLOYEE.”
DATCOM Lab (DATCOM Lab) August 29, 2024 46 / 51
Recursive Relationships
Recursive Relationships
For example: Implementation of the 1:M Recursive Relationship
”EMPLOYEE manages EMPLOYEE”
DATCOM Lab (DATCOM Lab) August 29, 2024 47 / 51
Recursive Relationships
Recursive Relationships
A recursive relationship is one in which a relationship can exist
between occurrences of the same entity set. Such a condition is found
within a unary relationship.
For example:
▶ The M:N unary relationship may be expressed by ”a COURSE may be
a prerequisite to many other COURSEs, and each COURSE may have
many other COURSEs as prerequisites.”
DATCOM Lab (DATCOM Lab) August 29, 2024 48 / 51
Recursive Relationships
Recursive Relationships
For example: Implementation of the M:N Recursive Relationship
”COURSE requires COURSE”
DATCOM Lab (DATCOM Lab) August 29, 2024 49 / 51
Associative (Composite) Entities
Associative (Composite) Entities
A recursive relationship is one in which a relationship can exist
between occurrences of the same entity set. Such a condition is found
within a unary relationship.
For example:
▶ A 1:1 unary relationship may be expressed by ”an EMPLOYEE may be
married to one and only one other EMPLOYEE.”
DATCOM Lab (DATCOM Lab) August 29, 2024 50 / 51
Associative (Composite) Entities
Questions
DATCOM Lab (DATCOM Lab) August 29, 2024 51 / 51
