CHAPTER 4

ENTITY RELATIONSHIP (ER)

MODELING
Contents
 Entity-Relationship (ER) model
 Entities
 Attributes
 Relationships
 Connectivity
 Cardinality
 Relationship strength
 Relationship participation
 Weak entities
 Relationship degree
 Composite entities
 Entity Supertypes and Subtypes
 A comparison of ER Modelling Symbols
 Developing an ER diagram
The Entity Relationship (ER) Model

 ER model forms the basis of an ER diagram

 ERD represents conceptual database as viewed by

end user

 ERDs show database’s main components:

 Entities

 Attributes

 Relationships
Entities
 Refers to entity set and not to single entity occurrence

 Corresponds to table and not to row in relational

environment
 ERM refers to a row as entity instance or entity occurrence

 In both Chen and Crow’s Foot models, entity is

represented by rectangle containing entity’s name

 Entity name, a noun, is usually written in capital letters

Entities

STUDENT
Attributes
 Characteristics of entities

 In Chen model, attributes are represented by ovals and

are connected to entity rectangle with a line

 Each oval contains the name of attribute it represents

 In Crow’s Foot model, attributes are written in attribute

box below entity rectangle
Attributes
Domains

 Attributes have domain

 Domain is attribute’s set of possible values

 Attributes may share a domain

Primary Keys
 Underlined in the ERD

 Key attributes are also underlined in frequently

used table structure shorthand
 Example:

TABLE NAME (KEY_ATTRIBUTE 1, ATTRIBUTE 2, …. ATTRIBUTE K)

Primary Keys

 Primary keys ideally composed of only single

attribute
STUDENT(STD_NUM, STD_NAME, STD_GENDER,
STD_ADDRESS)

 Possible to use a composite key

 Primary key composed of more than one attribute
CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME,
CLASS_ROOM, PROF_NUM)
Types of Attributes

 Composite attribute

 Simple attribute

 Single-value attribute

 Multivalued attribute

 Derived attribute
Composite and Simple Attributes
 Composite attribute
 An attribute that can be further subdivided to yield
additional attributes.
 Examples:
 ADDRESS - Street, City, State, Zip
 PHONE NUMBER - Area code, Exchange number

 Simple attribute
 An attribute that cannot be subdivided
 Examples:
 Age
 Gender
 Marital status
 Single-Valued and
Multivalued Attributes
 Single-value attribute
 can have only a single value
 Examples:
 A person can have only one IC number
 A manufactured part can have only one serial number

 Multivalued attributes
 can have many values
 Examples:
 A person may have several college degrees
 A household may have several phones with different numbers
 A car’s color may be subdivided into many colors (roof, body and
trim)
Multivalued Attribute in an Entity
Resolving Multivalued Attribute Problems
Resolving Multivalued Attribute Problems

 Although conceptual model can handle M:N relationships

and multivalued attributes, you should not implement
them in relational DBMS

 Solution
1. Within original entity, create several new attributes, one
for each of the original multivalued attribute’s components
 Can lead to major structural problems in table

2. Create new entity composed of original multivalued

attribute’s components
Resolving Multivalued Attribute Problems
Resolving Multivalued Attribute Problems
Derived Attributes

 Attribute whose value may be calculated (derived)

from other attributes

 Need not be physically stored within database

 Can be derived by using an algorithm

 e.g. EMP_AGE (can be derived by deducting current

date and EMP_DOB)
Derived Attributes
Relationships

 Association between entities

 Participants
 are entities that participate in a relationship
 The relationship name is an active or passive verb
 A STUDENT takes a CLASS
 Relationships between entities always operate in both directions
 A CUSTOMER may generate many INVOICEs
 Each INVOICE is generated by one CUSTOMER
(Relationship can be classified as 1:M)
 Relationship classification is difficult to establish if we know only
one side of the relationship
Connectivity and Cardinality
 Connectivity
 Used to describe the relationship classification
 e.g. One-to-one, one-to-many, many-to-many

 Cardinality
 Expresses minimum and maximum number of entity
occurrences associated with one occurrence of related
entity
 placed beside entities using the format (x,y)= (min,max)
 Connectivity and Cardinality

Cardinality (1,4) indicates that the PROFESSOR table’s FK value occurs at least
once and no more than four times in the CLASS table.
Cardinality (1,1) indicates that each class is taught by one and only one professor.
Cardinality (1,N) indicates, there would be no upper limit to the number of classes
a professor might teach.
Existence Dependence
 Existence dependence
 Entity exist in database only when it is associated with
another related entity occurrence
 e.g. Entity DEPENDENT depends on EMPLOYEE to exist

 Foreign key attribute cannot be null

 Existence independence
 Entity can exist apart from one or more related entities
 e.g. Entity LECTURER can exist without entity CLUB

 Sometimes refers to such an entity as strong or regular

entity
Relationship Strength
 Weak (non-identifying) relationships
 Exists if PK of related entity does not contain PK component
of parent entity, e.g.

COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)

CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE,
PROF_NUM)
Weak (Non-Identifying) Relationships

dashed line
Weak (Non-Identifying) Relationships
Relationship Strength
 Strong (Identifying) Relationships
 Exists
when PK of related entity contains PK component
of parent entity e.g.
COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)
CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM)
Strong (Identifying) Relationships

solid line
Strong (Identifying) Relationships
Weak Entities

 Weak entity meets two conditions

1. Existence-dependent
 Cannot exist without entity with which it has a relationship

2. Has primary key that is partially or totally derived from

parent entity in relationship

 Database designer usually determines whether an entity

can be described as weak based on business rules
Weak Entities
Weak Entities
Relationship Participation

 Optional participation
 One entity occurrence does not require corresponding
entity occurrence in particular relationship
 Optional entities relationship is shown by drawing a small circle
(o) on the side of the optional entity (min always 0)

 Mandatory participation
 One entity occurrence requires corresponding entity
occurrence in particular relationship
 Symbol | ( min always one)
Relationship Participation
OPTIONAL

 CLASS is optional. It is possible for a PROFESSOR not to teach a

CLASS

 (0,3) indicates a PROFESSOR may teach no class at all or as many

as three CLASSes

 A CLASS must be taught by a PROFESSOR

Relationship Participation
OPTIONAL

 CLASS is optional. It is possible for a COURSE not to generate a

CLASS.

 This condition is generated by the constraint that some courses are

taught only once a year not every semester.

 (0,N) indicates a COURSE may generate no class at all or as many

CLASSes required
Relationship Participation
MANDATORY

 CLASS is mandatory. This condition is created by the

constraint “Each COURSE generates one or more CLASSes”.

 Each COURSE in the “generates” relationship must have at

least one CLASS
Relationship Participation
Relationship Degree
 Indicates number of entities or participants
associated with a relationship

 Unary relationship
 Association is maintained within single entity
 Binary relationship
 Two entities are associated
 Ternary relationship
 Three entities are associated
Three Types of Relationship Degree
The Implementation of
a Ternary Relationship
Recursive Relationships
 Relationship can exist between occurrences of the same
entity set
 Naturally found within unary relationship
 Examples:
 1:1 Recursive Relationship
 An EMPLOYEE may be married to one and only one other EMPLOYEE
 1:M Recursive Relationship
 An EMPLOYEE may manage many EMPLOYEEs.
 M:N Recursive Relationship
 A COURSE may be prerequisite to many other COURSEs and a
COURSE may have many other COURSEs as prerequisite.
 ER Representation of
Recursive Relationships
1:1 Recursive Relationship
1:M Recursive Relationship
M:N Recursive Relationship
Composite Entities

 Also known as bridge entities

 Composed of primary keys of each of the

entities to be connected

 May also contain additional attributes that play

no role in connective process
Composite Entities
Composite Entities
Composite Entities
Entity Supertypes and Subtypes
 In real world, many businesses employ people with wide range of
skills & special qualifications.
 e.g. in aviation business; employs pilots, mechanics, accountants,
database managers, etc.
 Each pilot share certain characteristics with other employees such as
last name (EMP_LNAME) & hire date (EMP_HIRE_DATE).
 Many pilot characteristics are not shared by other types of
employees such as EMP_LICENSE, EMP_RATINGS & EMP_MED_TYPE
(this generate null values for employees who are not pilots).
 These unshared characteristics create problems when you try to store
all employees’ attributes in the same table.
Nulls Created by Unique Attributes
Entity Supertypes and Subtypes

 We can solve this problem by:

 Creating separate entities to store specific/unique

attributes (PILOT) and common attributes
(EMPLOYEE).

 PILOT is a subtype of EMPLOYEE and EMPLOYEE is

the supertype of PILOT.
The EMPLOYEE-PILOT supertype-subtype
relationship
Table Name: EMPLOYEE

Table Name: PILOT

Entity Supertypes and Subtypes

 Entity supertype
 Generic entity type that is related to one or more
entity subtypes
 Contains common characteristics (shared attributes)

 Entity subtypes
 Contains unique characteristics of each entity
subtype (unique attributes)
Specialization Hierarchy

 Shows arrangement of higher-level entity supertypes

(parent entities) and lower-level entity subtypes (child
entities)

 Relationships sometimes described in terms of “IS-A”

relationships (1:1 relationship)

 Subtype can exist only within context of supertype and

every subtype can have only one supertype to which it is
directly related

 Can have many levels of supertype/subtype relationships

Specialization Hierarchy
Specialization Hierarchy

 Support attribute inheritance

 Define special supertype attribute known as

subtype discriminator

 Define disjoint/overlapping constraints and

complete/partial constraints
Inheritance
 Subtype will inherit primary key, attributes and
relationships of the supertype.

 PILOT, MECHANIC & ACCOUNTANT inherit

attributes EMP_NUM, EMP_LNAME, EMP_FNAME,
EMP_INITIAL, EMP_HIRE_DATE from EMPLOYEE

 All entity subtypes inherit primary key EMP_NUM

from EMPLOYEE

 All entity subtypes inherit relationship with

DEPENDENT entity
Subtype Discriminator

 Subtype discriminator: Attribute in supertype entity

that determines to which subtype the supertype
occurrence is related.

 EMP_TYPE is the subtype discriminator in this case.

 Refer to figure 6.2, the supertype related to a

PILOT subtype if EMP-TYPE = “P”,
MECHANIC subtype if EMP-TYPE = “M” and
ACCOUNTANT subtype if EMP-TYPE = “A”
Disjoint and Overlapping Constraints
 Disjoint subtypes
 Also known as non-overlapping subtypes
 Subtypes that contain unique subset of supertype entity set

 Each entity instance of the supertype can appear in only one

of the subtypes.
 e.g. a PILOT cannot be a mechanic at the same time
d
 Overlapping subtypes
 Subtypes that contain nonunique subsets of supertype entity set
 Each entity instance of the supertype can appear in more than
one subtypes.
 e.g. an EMPLOYEE can be a STUDENT at the same time o
Disjoint and Overlapping Constraints
Alternative Notation (Disjoint)
Alternative Notation (Overlapping)
Completeness Constraint
 Specifies whether each entity supertype occurrence must also
be a member of at least one subtype.

 Partial completeness:
 not every supertype occurrence is a member of a subtype

 notation:

 Total completeness:
 every supertype occurrence must be a member of at least
one subtype
 notation:
 Completeness Constraint

Specialization Hierarchy Constraint Scenarios

Type Disjoint Constraint Overlapping Constraint
Partial Supertype has optional subtypes Supertype has optional subtypes
Subtype discriminator can be null Subtype discriminator can be null
Subtype sets are unique Subtype sets are not unique

Total Every supertype occurrence is a Every supertype occurrence is a

member of a (at least one) subtype member of a (at least one) subtype
Subtype discriminator cannot be null Subtype discriminator cannot be null
Subtype sets are unique Subtype sets are not unique
Specialization and Generalization
 Specialization:
 Top-down process of identifying lower-level, more specific entity
subtypes from a higher-level entity supertype
 Grouping unique characteristics and relationships of the subtypes

 Generalization:
 Bottom-up process of identifying a higher-level more generic entity
supertype from lower-level entity subtypes
 Grouping common characteristics and relationships of the subtypes
Developing an ER Diagram

 Database design is iterative rather than linear or

sequential process

 Iterative process
 Based on repetition of processes and procedures
Developing an ER Diagram

 Building an ERD usually involves the following activities:

 Create detailed narrative of organization’s description of
operations
 Identify business rules based on description of operations
 Identify main entities and relationships from business rules
 Develop initial ERD
 Identify attributes and primary keys that adequately
describe entities
 Revise and review ERD
Developing an ER Diagram:
A Case Study of Tiny College

1. Tiny College is divided into several schools

 Each school is administered by a dean who is a professor
 A professor not necessarily a dean
 Each dean can administer only one school

2. Each school is composed of several departments

 The smallest number of departments operated by a school is one and
the largest is indeterminate (N)
 Each department belongs to a single school
Developing an ER Diagram
Developing an ER Diagram
3. Each department may offer courses
▪ Tiny college had some departments that were classified as “research
only” therefore not offer any courses.
Developing an ER Diagram
4. A class is a section of a course
 The department may offer several sections (classes) of the same course
 A class may not exist in certain semester
Developing an ER Diagram

5. Each department may have many professors assigned to it.

 One and only one professor chairs a department
 No professor is required to accept the chair position
Developing an ER Diagram

6. Each professor may teach up to four classes

 A professor may also be on a research contract and teach no classes at all
Developing an ER Diagram
7. Student may enroll in several classes, but (s)he takes each class only once
during any given enrollment period
 Each student may enroll in up to six classes and each class may have up to 35
students
 A class can exist initially even though no students have enrolled in it
Developing an ER Diagram
8. Each department has several (many) students
 There are departments that only concentrate on research and does not have
any students
 Each student has only a single major and is associated with a single
department
Developing an ER Diagram
9. Each student has an advisor in his or her department
 An advisor is also a professor but not all professor advise students
 Each advisor counsels several students
Developing an ER Diagram
10. Each class is taught in a room and the room is in the building
 A building can contain many rooms but each room is found in a single
building
 A building may not contain any class e.g. store building
 A room may be used for many classes
Developing an ER Diagram
Complete ERD for Tiny College
A Comparison of ER Modeling Symbols
The Chen Representation
of the Invoicing Problem
The Crow’s Foot Representation
of the Invoicing Problem
Database Design Challenges:
Conflicting Goals
 Database design must conform to design standards

 High processing speeds are often a top priority in

database design

 Quest for timely information might be focus of

database design
Summary
 Entity relationship (ER) model
 Uses ERD to represent conceptual database as viewed
by end user
 ERM’s main components:
 Entities
 Relationships
 Attributes

 Includes connectivity and cardinality notations

Summary
 Connectivities and cardinalities are based on
business rules
 In ERM, M:N relationship is valid at conceptual level
 ERDs may be based on many different ERMs
 Database designers are often forced to make
design compromises
Exercise
Exercise
 You are going to develop a database that will store information
about journals. Each journal may have any number of issues (for
example monthly issue or three-monthly issues (every three month
an issue is published). Etc ).
 Each issue is identified by its number and date issued. Each issue
contains a number of articles. The length in terms of number of
words is kept for each article together with the number of
diagrams in the article.
 Each article may be written by one or more writers. The writer’s
name and address as well as fee paid to a writer for an article
is also recorded. A writer may contribute as many articles to any
journal.

 Draw E-R diagram to represent the given information. Do not

forget to include all connectivities, cardinalities and attributes.
 KRUE Recording Company wants to keep a database of all its artists,
composers and albums under its company.

nikruslawati-
uitm 2017-

• An artist is assigned a unique artist code. Other information required

ITS432

of an artist his/her name, address and telephone number.

• An artist can perform in many albums. Each album is identified by an
album code. Each album may have more than one artist performing
on it. The name and year released are also stored.
• Composers produce many albums. Composers information such as
composer‘s unique identification number, name, telephone number and
address. The company also wants to keep track of the number of
songs a composer contribute in an album.

1. Use the following business rules to create a complete E-R diagram:

2. Prepare the relational database structure for the ER diagram you developed in
question 1. Underline all the primary keys and understrike(*) all the foreign keys.
 Given the following business scenario, create a Crow’s Foot ERD using a specialization
hierarchy if appropriate.
 Granite Sales Company keeps information on employees and the departments
that they work in.
 For each department, the department name, internal mail box number, and
office phone extension are kept. A department can have many assigned
employees, and each employee is assigned to only one department.
 Employees can be salaried employees, hourly employees, or contract
employees. All employees are assigned an employee number. This is kept
along with the employee’s name and address. For hourly employees, hourly
wage and target weekly work hours are stored (e.g. the company may target 40
hours/week for some, 32 hours/week for others, and 20 hours/week for others).
Some salaried employees are salespeople that can earn a commission in
addition to their base salary.
 For all salaried employees, the yearly salary amount is recorded in the system.
For salespeople, their commission percentage on sales and commission
percentage on profit are stored in the system. For example, John is a
salesperson with a base salary of $50,000 per year plus 2-percent commission
on the sales price for all sales he makes plus another 5 percent of the profit on
each of those sales.
 For contract employees, the beginning date and end dates of their contract are
stored along with the billing rate for their hours.