Database Principles:

Fundamentals of Design,
Implementation, and Management
Tenth Edition

Chapter 7
Data Modeling with Entity Relationship
Diagrams
 Objectives
• In this chapter, students will learn:
– The main characteristics of entity relationship
components
– How relationships between entities are defined,
refined, and incorporated into the database
design process
– How ERD components affect database design
and implementation

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 The Entity Relationship Model (ERM)
• ER model forms the basis of an ER diagram
• ERD represents conceptual database as viewed by
end user
• ERDs depict database’s main components:
– Entities
– Attributes
– Relationships

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Entities
• Refers to entity set and not to single entity
occurrence
• Corresponds to table and not to row in relational
environment
• In Chen and Crow’s Foot models, entity is
represented by rectangle with entity’s name
• The entity name, a noun, is written in capital letters

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Entities
• Represented as a rectangle in E-R diagram

Example:

Student

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attributes
• Characteristics of entities
• Chen notation: attributes represented by ovals
connected to entity rectangle with a line
– Each oval contains the name of attribute it
represents

• Crow’s Foot notation: attributes written in attribute

box below entity rectangle

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attributes
Example

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attribute Types
• Simple/Atomic or Composite (number of sub-parts):
 Simple: one part
 Composite: divided into sub-parts
 e.g. Name = surname + first_name
• Single- or multi-valued (number of values per entity):
 Single-valued: one value for a particular entity
 Multi-valued: a set of values
 e.g. Student has a number of courses
• Null attributes: unknown, not applicable or missing
• Derived / Calculated: values of attributes can be
derived from other attributes (not stored; computed
when required) e.g: age from DOB
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attributes (cont’d.)
• Composite attribute can be subdivided
• Simple attribute cannot be subdivided

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attributes (cont’d.)
• Single-value attribute can have only a single
value
• Multivalued attributes can have many values

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attribute Types (continued)

• NULL attributes have no value

– not 0 (zero)
– not a blank string
• Attributes can be “nullable” where a null value is
allowed, or “not nullable” where they must have a
value.

• Represented by:

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attributes (cont’d.)
• Derived / Calculated Attributes:
• M:N relationships and multivalued attributes
should not be implemented
• Create several new attributes for each of the
original multivalued attributes’ components
• Create new entity composed of original multivalued
attributes’ components
• Derived attribute: value may be calculated from
other attributes
• Need not be physically stored within database
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Attribute Types (continued)

Professor Years Teaching

Start_Date

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Relationships
• Association between entities
• Participants are entities that participate in a
relationship
• Relationships between entities always operate in
both directions
• Relationship can be classified as 1:M
• Relationship classification is difficult to establish if
only one side of the relationship is known
• Represented by diamonds in E-R diagram

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Connectivity and Cardinality
• Connectivity
– Describes the relationship classification
• Cardinality
– Expresses minimum and maximum number of
entity occurrences associated with one occurrence
of related entity
• Established by very short statements known as
business rules

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Connectivity and Cardinality
 Connectivity:

Used to describe the relationship classification

– one to one

– one to many

– many to one

– many to many

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Connectivity and Cardinality
 Cardinality
Expresses minimum and maximum number of entity
occurrences associated with one occurrence of related entity
Example:
Each team has one or more trainers.
Each team has at least 12 at most 22 players.
A player may play in only one team and a trainer may work for only one
team.
Team (12,22)
plays Player
(1,1)

(1,M)

Work for

(1,1)

Trainer

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 The Basic Chen ERD

A One-to-Many (1:M) Relationship:

A Painter can paint many Paintings;
Painter paints Painting Each Painting is painted by one Painter

A Many-to-Many (M:N) Relationship:

Employee learns Skill An Employee can learn many Skills;
each Skill can be learned by many
Employees

A One-to-One (1:1) Relationship:

Employee manages Store an Employee manages one Store;
each Store is managed by one Employee

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Degree of Relationships
 Degree of a relationship is the number of entity
types that participate in it

• Unary Relationship

• Binary Relationship

• Ternary Relationship

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Examples

• Can have more than one starts at

relationship between Road Town
entities.
ends at

• Can have recursive (UNARY) manager

relationships and can indicate Employee works for
roles for clarity. worker

date

• A relationship can also have

Client orders Book
descriptive attributes.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Ternary Relationship

Name
proj_name

location Project Hires Contractor

Address
budget
end_date
start_date
end_date
Contract
number start_date
value

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Keys in E/R Diagrams
• Identifiers: one or more attributes that
uniquely identify each entity instance

• Composite identifier: primary key composed

of more than one attribute

Identifiers (Primary Key ) must be underlined!!!!

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 E-R Diagram Symbols

Entity Set Relationship

Weak Entity One-to-one link

Set
Many-to-one link

Attribute Many-to-many link

Multivalued number Key Attribute

Attribute

Attribute can be
Derived Attribute
NULL
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 The Entity Relationship Data Model –
Extra Information

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 The Object Oriented Model
• Modeled both data and their relationships in a single
structure known as an object
• Object-oriented data model (OODM) is the basis for the
object-oriented database management system (OODBMS)
• OODM is said to be a semantic data model
• Object is an abstraction of a real-world entity
• Attributes describe the properties of an object
• Objects that share similar characteristics are grouped in
classes
• Classes are organized in a class hierarchy
• Inheritance is the ability of an object within the class
hierarchy to inherit the attributes and methods of classes
above it

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 The Object Oriented Model
(continued)

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Developing an ER Diagram:
Practices
Exercise 1:
Draw an ERD for the following specification.
Student: id, name, dob
Department: dno, dname
Course: ccode, title, credits
Rules:
A student is enrolled in one department
In one department, there are many students.
A student takes many courses
A course is taken by many students.
A course is offered by one department
A department offers many courses.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Practices

Exercise 2:
There are many activities. Each activity has a name, a fee, a
maximum capacity and number of types (such as tennis,
swimming,baseball,etc...). A student may participate in many
activities and there may be many students participating in each
activity. A student has name (first_name, last_name), a cgpa,
and a dob. Each activity is supervised by one and only one coach.
But a coach may supervise many activities. Each coach has a
name, an identity number and a salary.

Clearly indicate the Primary Keys.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Practices
Exercise 3:

Draw an ERD for the Moon Hotel. There are many rooms in the hotel.
Each room has a unique room number, type and price. There may be
many customers in each room. But a customer may rent only one room.
Each customer has a unique customer_id, name, date of birth and a
phone number. There are many departments in the hotel. For each
department you need to store deptno, and name. Departments are
identified by deptno. There are many employees working for a
department, but an employee works for only one department. Each
employee has an emp_id, name and salary. One of the employees is the
manager of a department. An employee may serve many rooms but only
one employee serves each room.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Practices
Exercise 4:
Draw Entity – Relationship Diagram for the following scenario:

There are many branches. Each branch has the following

attributes (branchno, address, fax_no, telno). There are many
employees. Each employee has the following attributes (name,
address, position, salary, gender, empno). Name of an employee
is structure that contains two fields (last_name, first_name),
address of employee is also a structure that contains (no,
street, city). In each branch there are many employees
working. Each employee works for a single branch. Each branch
is managed by one and only one employee. And each employee
manages one and only one branch.

Clearly indicate the Primary Keys.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Practices
Exercise 5:
Draw Entity – Relationship Diagram for the following
scenario:
A company has a number of employees. The attributes of
employee include employee_id (identifier), name, address, and
birthdate. The company also has several projects. The
attributes of a project include project_id (identifier),
project_name, and start_date. Each employee may be assigned
to one or more projects, or may not be assigned to a project. A
project must have at least one employee assigned and may have
any number of employees assigned. An employee’s billing rate
varies by project and the company wishes to record the
applicable billing_rate (billing_rate) for each employee when
assigned to a particular project.

Clearly indicate the Primary Keys.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Homeworks
Homework 1:
Draw Entity – Relationship Diagram for the following scenario:

The system is required to store information about movies for a

Movie Hire Business. The users wish to keep track of the
following data: Members have a username (no two members are
allowed to have the same username), password and address.
Every movie has a unique movie id, title, year released and
category. A movie only has one director but a director may have
directed more than one movie. The unique director id, first and
last names as well as address is stored for each director.
Address of director is composed of street, city and country. A
movie can be hired by many members and a member may hire
many movies. You should keep track of the date the movie is
hired, as well as the date the movie is due to be returned.

Clearly indicate the Primary Keys.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Homeworks
Homework 2:
Draw Entity – Relationship Diagram for the following scenario:
A department employs up to 30 employees, but an employee is
employed by one department. For each employee you need to store
unique employee id, name, address and salary. Departments are
identified by department id and also have a name. Some employees
are not assigned to any department. A division operates many
departments, but each department is operated by one division. An
employee may be assigned at the most 3 projects, and a project may
have at the most 6 employees assigned to it. A project may have at
least one employee assigned to it. Each project is identified by
unique name and has a budget. A project can be related to other
projects. There can be many related projects. One of the employees
manages each department, and each department is managed by only
one employee. One of the employees runs each division, and each
division is run by only one employee. For each division, store unique
id and name.
Clearly indicate the Primary Keys.

© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Homeworks
Homework 3:
Draw Entity – Relationship Diagram for the following
scenario:
There are many branches. Each branch has the following
attributes (branch_no, address, fax_no, tel_no). There
are many employees. Each employee has the following
attributes (name, address, position, salary, gender,
emp_no). Name of an employee is a structure that
contains two fields (last_name, first_name), address of
employee is also a structure that contains (no, street,
city). The gender field is enumerated (FEMALE, MALE).
In each branch there are many employees working. Each
employee works for a single department. Each branch is
managed by one and only one employee. And each employee
manages one and only one department.
Clearly indicate the Primary Keys.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.
 Homeworks
Homework 4:
Draw Entity – Relationship Diagram for the following scenario:
The firm has a number of sales offices in several states. We need to
store unique office number and location for each sales office. Each
sales office is assigned to one or more employees. You need to store
the following information for each employee; unique employee id,
employee name, and salary. An employee must be assigned to only one
sales office. For each sales office, there is always one employee
assigned to manage that office. An employee may manage only the sales
office to which he/she is assigned. The firm lists property for sale.
Attributes of property include property id (identifier) and Location
(composed of Address, City, State, and Zip Code). Each property must
be listed with one (and only one) of the sales offices. A sales office
may have many number of properties listed, or may have no properties
listed. Each unit of property has one or more owners. For each owner
we need to store unique owner id and owner name. An owner may own
one or more properties, and we need to keep the percentage of
properties owned by each owner.
Clearly indicate the Primary Keys.
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition.
May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.