Data Modeling Using the

Entity-Relationship (ER) Model (1-2)

Dr. Adil Yousif

Lecture 5

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 1- 1

Lecture Outline
◼ Overview of Database Design Process
◼ Example Database Application (COMPANY)
◼ ER Model Concepts
◼ Entities and Attributes
◼ Entity Types, Value Sets, and Key Attributes
◼ Relationships and Relationship Types
◼ Weak Entity Types
◼ Roles and Attributes in Relationship Types
◼ ER Diagrams - Notation
◼ ER Diagram for COMPANY Schema
◼ Alternative Notations – UML class diagrams, others
◼ Relationships of Higher Degree

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 2

Overview of Database Design Process

◼ Two main activities:

◼ Database design
◼ Applications design
◼ Focus in this chapter on conceptual database
design
◼ To design the conceptual schema for a database
application
◼ Applications design focuses on the programs and
interfaces that access the database
◼ Generally considered part of software engineering
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 3
Overview of Database Design Process

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 4

Methodologies for Conceptual
Design
◼ Entity Relationship (ER) Diagrams (This Chapter)
◼ Enhanced Entity Relationship (EER) Diagrams
(Chapter 4)
◼ Use of Design Tools in industry for designing and
documenting large scale designs
◼ The UML (Unified Modeling Language) Class
Diagrams are popular in industry to document
conceptual database designs

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 5

Example COMPANY Database
◼ We need to create a database schema design
based on the following (simplified) requirements
of the COMPANY Database:
◼ The company is organized into DEPARTMENTs.
Each department has a name, number and an
employee who manages the department. We keep
track of the start date of the department manager.
A department may have several locations.
◼ Each department controls a number of
PROJECTs. Each project has a unique name,
unique number and is located at a single location.

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 6

Example COMPANY Database
(Continued)
◼ The database will store each EMPLOYEE’s social
security number, address, salary, sex, and
birthdate.
◼ Each employee works for one department but may
work on several projects.
◼ The DB will keep track of the number of hours per
week that an employee currently works on each
project.
◼ It is required to keep track of the direct supervisor of
each employee.
◼ Each employee may have a number of
DEPENDENTs.
◼ For each dependent, the DB keeps a record of name,
sex, birthdate, and relationship to the employee. Slide 3- 7
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei
ER Model Concepts
◼ Entities and Attributes
◼ Entity is a basic concept for the ER model. Entities are
specific things or objects in the mini-world that are
represented in the database.
◼ For example the EMPLOYEE John Smith, the Research
DEPARTMENT, the ProductX PROJECT
◼ Attributes are properties used to describe an entity.
◼ For example an EMPLOYEE entity may have the attributes
Name, SSN, Address, Sex, BirthDate
◼ A specific entity will have a value for each of its attributes.
◼ For example a specific employee entity may have Name='John
Smith', SSN='123456789', Address ='731, Fondren, Houston,
TX', Sex='M', BirthDate='09-JAN-55‘
◼ Each attribute has a value set (or data type) associated with
it – e.g. integer, string, date, enumerated type, …

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 8

Types of Attributes (1)
◼ Simple
◼ Each entity has a single atomic value for the attribute. For
example, SSN or Sex.
◼ Composite
◼ The attribute may be composed of several components. For
example:
◼ Address(Apt#, House#, Street, City, State, ZipCode, Country), or
◼ Name(FirstName, MiddleName, LastName).
◼ Composition may form a hierarchy where some components
are themselves composite.
◼ Multi-valued
◼ An entity may have multiple values for that attribute. For
example, Color of a CAR or PreviousDegrees of a STUDENT.
◼ Denoted as {Color} or {PreviousDegrees}.

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 9

Types of Attributes (2)
◼ In general, composite and multi-valued attributes
may be nested arbitrarily to any number of levels,
although this is rare.
◼ For example, PreviousDegrees of a STUDENT is a
composite multi-valued attribute denoted by
{PreviousDegrees (College, Year, Degree, Field)}
◼ Multiple PreviousDegrees values can exist
◼ Each has four subcomponent attributes:
◼ College, Year, Degree, Field

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 10

Example of a composite attribute

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 11

Entity Types and Key Attributes (1)
◼ Entities with the same basic attributes are
grouped or typed into an entity type.
◼ For example, the entity type EMPLOYEE
and PROJECT.
◼ An attribute of an entity type for which each
entity must have a unique value is called a
key attribute of the entity type.
◼ For example, SSN of EMPLOYEE.

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 12

Entity Types and Key Attributes (2)
◼ A key attribute may be composite.
◼ VehicleTagNumber is a key of the CAR entity

type with components (Number, State).

◼ An entity type may have more than one key.
◼ The CAR entity type may have two keys:

◼ VehicleIdentificationNumber (popularly called VIN)

◼ VehicleTagNumber (Number, State), aka license
plate number.
◼ Each key is underlined (Note: this is different from
the relational schema where only one “primary
key is underlined).
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 13
 Entity Set
◼ Each entity type will have a collection of entities
stored in the database
◼ Called the entity set or sometimes entity collection
◼ Previous slide shows three CAR entity instances in
the entity set for CAR
◼ Same name (CAR) used to refer to both the entity
type and the entity set
◼ However, entity type and entity set may be given
different names
◼ Entity set is the current state of the entities of that
type that are stored in the database
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 14
Value Sets (Domains) of Attributes
◼ Each simple attribute is associated with a value
set
◼ E.g., Lastname has a value which is a character
string of upto 15 characters, say
◼ Date has a value consisting of MM-DD-YYYY
where each letter is an integer
◼ A value set specifies the set of values associated
with an attribute

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 15

Displaying an Entity type
◼ In ER diagrams, an entity type is displayed in a
rectangular box
◼ Attributes are displayed in ovals
◼ Each attribute is connected to its entity type
◼ Components of a composite attribute are
connected to the oval representing the composite
attribute
◼ Each key attribute is underlined
◼ Multivalued attributes displayed in double ovals
◼ See the full ER notation in advance on the next
slide
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 17
NOTATION for ER diagrams

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 18

Entity Type CAR with two keys and a
corresponding Entity Set

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 19

Initial Conceptual Design of Entity Types
for the COMPANY Database Schema

◼ Based on the requirements, we can identify four

initial entity types in the COMPANY database:
◼ DEPARTMENT
◼ PROJECT
◼ EMPLOYEE
◼ DEPENDENT
◼ Their initial conceptual design is shown on the
following slide
◼ The initial attributes shown are derived from the
requirements description
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 20
Initial Design of Entity Types:
EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 21

Refining the initial design by introducing
relationships

◼ The initial design is typically not complete

◼ Some aspects in the requirements will be
represented as relationships
◼ ER model has three main concepts:
◼ Entities (and their entity types and entity sets)
◼ Attributes (simple, composite, multivalued)
◼ Relationships (and their relationship types and
relationship sets)
◼ We introduce relationship concepts next

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 22

Relationships and Relationship Types (1)
◼ A relationship relates two or more distinct entities with a
specific meaning.
◼ For example, EMPLOYEE John Smith works on the ProductX
PROJECT, or EMPLOYEE Franklin Wong manages the
Research DEPARTMENT.
◼ Relationships of the same type are grouped or typed into
a relationship type.
◼ For example, the WORKS_ON relationship type in which
EMPLOYEEs and PROJECTs participate, or the MANAGES
relationship type in which EMPLOYEEs and DEPARTMENTs
participate.
◼ The degree of a relationship type is the number of
participating entity types.
◼ Both MANAGES and WORKS_ON are binary relationships.

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 23

Relationship instances of the WORKS_FOR N:1
relationship between EMPLOYEE and DEPARTMENT

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 24

Relationship instances of the M:N WORKS_ON
relationship between EMPLOYEE and PROJECT

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 25

Relationship type vs. relationship set (1)

◼ Relationship Type:
◼ Is the schema description of a relationship
◼ Identifies the relationship name and the
participating entity types
◼ Also identifies certain relationship constraints
◼ Relationship Set:
◼ The current set of relationship instances
represented in the database
◼ The current state of a relationship type

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 26

Relationship type vs. relationship set (2)
◼ Previous figures displayed the relationship sets
◼ Each instance in the set relates individual participating
entities – one from each participating entity type
◼ In ER diagrams, we represent the relationship type as
follows:
◼ Diamond-shaped box is used to display a relationship

type
◼ Connected to the participating entity types via straight

lines
◼ Note that the relationship type is not shown with an

arrow. The name should be typically be readable from

left to right and top to bottom.
Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 27
Refining the COMPANY database
schema by introducing relationships
◼ By examining the requirements, six relationship types are
identified
◼ All are binary relationships( degree 2)
◼ Listed below with their participating entity types:
◼ WORKS_FOR (between EMPLOYEE, DEPARTMENT)
◼ MANAGES (also between EMPLOYEE, DEPARTMENT)
◼ CONTROLS (between DEPARTMENT, PROJECT)
◼ WORKS_ON (between EMPLOYEE, PROJECT)
◼ SUPERVISION (between EMPLOYEE (as subordinate),
EMPLOYEE (as supervisor))
◼ DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 28

ER DIAGRAM – Relationship Types are:
WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 29

Discussion on Relationship Types
◼ In the refined design, some attributes from the initial entity
types are refined into relationships:
◼ Manager of DEPARTMENT -> MANAGES
◼ Works_on of EMPLOYEE -> WORKS_ON
◼ Department of EMPLOYEE -> WORKS_FOR
◼ etc
◼ In general, more than one relationship type can exist
between the same participating entity types
◼ MANAGES and WORKS_FOR are distinct relationship
types between EMPLOYEE and DEPARTMENT
◼ Different meanings and different relationship instances.

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 30

Constraints on Relationships
◼ Constraints on Relationship Types
◼ (Also known as ratio constraints)
◼ Cardinality Ratio (specifies maximum participation)
◼ One-to-one (1:1)
◼ One-to-many (1:N) or Many-to-one (N:1)
◼ Many-to-many (M:N)
◼ Existence Dependency Constraint (specifies minimum
participation) (also called participation constraint)
◼ zero (optional participation, not existence-dependent)
◼ one or more (mandatory participation, existence-dependent)

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 31

Many-to-one (N:1) Relationship

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 32

Many-to-many (M:N) Relationship

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 3- 33

 Questions

Copyright © 2016 Ramez Elmasr and Shamkant B. Navathei Slide 1- 34