Course Name: Database Management System

Course Code: 18CS53

Module 1- Chapter 3
Data Modeling Using the Entity-
Relationship (ER) Model
 Chapter 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

Slide 3- 3
 Overview of Database Design Process
 Two main activities:
 Database design
 Applications design
 Focus in this chapter on 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

Slide 3- 4
Overview of Database Design Process

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.

Slide 3- 6
 Example COMPANY Database
(Contd.)
 We store each EMPLOYEE’s social security
number, address, salary, sex, and birthdate.

Each employee works for one department but may
work on several projects.

We keep track of the number of hours per week that
an employee currently works on each project.

We also keep track of the direct supervisor of each
employee.
 Each employee may have a number of
DEPENDENTs.

For each dependent, we keep track of their name,
sex, birthdate, and relationship to the employee.

Slide 3- 7
 ER Model Concepts
 Entities and Attributes

Entities are specific objects or things 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, subrange, enumerated type, …

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}.

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

Slide 3- 10
Example of a composite attribute

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.

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).


VehicleIdentificationNumber (popularly called VIN)

VehicleTagNumber (Number, State), aka license
plate number.
 Each key is underlined

Slide 3- 13
 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 CAR example on next slide

Slide 3- 14
Entity Type CAR with two keys and a
corresponding Entity Set

Slide 3- 15
 Entity Set
 Each entity type will have a collection of entities
stored in the database
 Called the entity set
 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
 Entity set is the current state of the entities of that
type that are stored in the database

Slide 3- 16
 Initial 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 design is shown on the following slide
 The initial attributes shown are derived from the
requirements description

Slide 3- 17
 Design of Entity Types
Example COMPANY Database
 Req 1: 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.

Slide 3- 18
 Design of Entity Types
Example COMPANY Database
 Req 2: Each department controls a number of
PROJECTs. Each project has a unique name,
unique number and is located at a single location.

Slide 3- 19
 Example COMPANY Database
 Req 3: We store each EMPLOYEE’s social security
number, address, salary, sex, and birthdate.

Each employee works for one department but may work on
several projects.

We keep track of the number of hours per week that an
employee currently works on each project.

We also keep track of the direct supervisor of each employee.

Slide 3- 20
 Example COMPANY Database
 Each employee may have a number of
DEPENDENTs.

For each dependent, we keep track of their name,
sex, birthdate, and relationship to the employee.

Slide 3- 21
 Initial Design of Entity Types:
EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

Slide 3- 22
 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

Slide 3- 23
 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.

Slide 3- 24
Relationship instances of the WORKS_FOR
N:1 relationship between EMPLOYEE and
DEPARTMENT

Slide 3- 25
Relationship instances of the M:N
WORKS_ON relationship between
EMPLOYEE and PROJECT

Slide 3- 26
 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)

Slide 3- 27
ER DIAGRAM – Relationship Types are:
WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

Slide 3- 28
 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.
 Recursive Relationship Type
 An relationship type whose with the same participating
entity type in distinct roles
 Example: the SUPERVISION relationship
 EMPLOYEE participates twice in two distinct roles:
 supervisor (or boss) role
 supervisee (or subordinate) role
 Each relationship instance relates two distinct
EMPLOYEE entities:
 One employee in supervisor role
 One employee in supervisee role

Slide 3- 30
 Weak Entity Types
 An entity that does not have a key attribute
 A weak entity must participate in an identifying
relationship type with an owner or identifying entity type
 Entities are identified by the combination of:
 A partial key of the weak entity type
 The particular entity they are related to in the identifying entity

type
 Example:
 A DEPENDENT entity is identified by the dependent’s first name,

and the specific EMPLOYEE with whom the dependent is related

 DEPENDENT is a weak entity type

 EMPLOYEE is its identifying entity type via the identifying

relationship type DEPENDENT_OF

Slide 3- 31
 Constraints on Relationships
 Constraints on Relationship Types
 (Also known as ratio constraints)
 The cardinality ratio for a binary relationship specifies the
maximum number of relationship instances that an entity
can participate in.

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- partial participation, not existence-dependent)

one or more (mandatory- total participation, existence-
dependent)
Slide 3- 32
Many-to-one (N:1) Relationship

Slide 3- 33
Many-to-many (M:N) Relationship

Slide 3- 34
Recursive Relationship Type is: SUPERVISION
(participation role names are shown)

Slide 3- 35
 Attributes of Relationship types
 A relationship type can have attributes:
 For example, HoursPerWeek of WORKS_ON
 Its value for each relationship instance describes
the number of hours per week that an EMPLOYEE
works on a PROJECT.

A value of HoursPerWeek depends on a particular
(employee, project) combination
 Most relationship attributes are used with M:N
relationships

In 1:N relationships, they can be transferred to the
entity type on the N-side of the relationship

Slide 3- 36
Example Attribute of a Relationship Type:
Hours of WORKS_ON

Slide 3- 37
 Notation for Constraints on
Relationships
 Cardinality ratio (of a binary relationship): 1:1,
1:N, N:1, or M:N
 Shown by placing appropriate numbers on the
relationship edges.
 Participation constraint (on each participating
entity type): total (called existence dependency)
or partial.
 Total shown by double line, partial by single line.
 NOTE: These are easy to specify for Binary
Relationship Types.

Slide 3- 38
 The (min,max) notation for
relationship constraints

Read the min,max numbers next to the entity type and looking
away from the entity type

Slide 3- 39
COMPANY ER Schema Diagram using (min, max) notation

Slide 3- 40
Summary
of notation
for ER
diagrams

Slide 3- 41
An ER diagram for a UNIVERSITY database schema