FUNDAMENTAL DATABASE

SYSTEMS(ITEC2071)
Data Modeling Using the Entity-Relationship
(ER) Model
 Outline
2

 Example Database Application (COMPANY)

 ER Model Concepts
 Entities and Attributes
 Entity Types, Value Sets, and Key Attributes
 Relationships and Relationship Types
 Strong and Weak Entity Types
 Roles and Attributes in Relationship Types
 ER Diagrams - Notation
 ER Diagram for COMPANY Schema
 Alternative Notations – UML class diagrams, others

Data Modeling Using the Entity-Relationship (ER) Model

10/01/22
 Entity Relationship Diagrams for Data Modelling
3

 Data modelling focuses on data flows and data stores

represented in the data flow diagrams.
 Data models can be of two types:
 Logical data models – describe logical organisation and
presentation of data. A technique for logical data
modelling is the entity-relationship diagrams.
 Physical data models – produced in the design phase,
give implementation details about how data will
actually be stored in databases and files.
 An Entity-Relationship Diagram (ERD) shows how the
data that flows in the system is organised and used.
Data Modeling Using the Entity-Relationship
10/01/22 (ER) Model
 Overview of database design process
4

 Two main activities

 Database design

 Application design

 Focus in the chapter on database design

 To design the conceptual schema for database application

 Applications design focus the programs and interfaces that

access the database

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

Database Design Phases

Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley

Systems Development Life Database Development
Cycle Process

Project Identification Enterprise modeling

and Selection

Project Initiation
and Planning
Conceptual data modeling
Analysis

Logical Design Logical database design

Physical database design and

Physical Design
definition

Implementation Database implementation

Maintenance Database maintenance

Data Modeling Using the Entity-Relationship (ER) Model 6
10/01/22
 Example COMPANY Database
7

 Requirements of the Company (oversimplified for illustrative

purposes)
 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.
 Each department controls a number of PROJECTs. Each
project has a name, number and is located at a single
location.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

8
Example COMPANY Database (Cont.)

 We store each EMPLOYEE’s social security number,

address, salary, sex, and birth date. 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,
birth date, and relationship to employee.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 ER Model Concepts
9

 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 a 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, …

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Types of Attributes (1)
10

 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 Previous Degrees of a STUDENT. Denoted as {Color} or
{Previous Degrees}.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

A composite attribute
 Types of Attributes (2)
12

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

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Types of Attributes (3)
13

 Null Values
 NULL applies to attributes which are not applicable or
which do not have values.
 You may enter the value NA (meaning not applicable)

 Value of a key attribute can not be null.

 Default value - assumed value if no explicit value

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

14
Entity Types and Key Attributes
 Entities with the same basic attributes are grouped or typed into an entity type.
For example, the EMPLOYEE entity type or the PROJECT entity type.
 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.
 A key attribute may be composite. For example, VehicleTagNumber is a key
of the CAR entity type with components (Number, State).
 An entity type may have more than one key. For example, the CAR entity type
may have two keys:
 Vehicle Identification Number (popularly called VIN) and
 VehicleTag Number (Number, State), also known as license plate numbe r.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Entities and attributes
15

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Entity Type vs. Entity set
16

 Entity type is a collection (set) of entities that have the same

attribute

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 ENTITY SET corresponding to the
ENTITY TYPE CAR
17

CAR
Registration(RegistrationNumber, State), VehicleID, Make, Model, Year, (Color)

car1
((ABC 123, TEXAS), TK629, Ford Mustang, convertible, 1999, (red, black))
car2
((ABC 123, NEW YORK), WP9872, Nissan 300ZX, 2-door, 2002, (blue))
car3
((VSY 720, TEXAS), TD729, Buick LeSabre, 4-door, 2003, (white, blue))
.
.
.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Conceptual design of Car
18

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Conceptual design of COMPANY database
19

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 SUMMARY OF ER-DIAGRAM NOTATION FOR ER SCHEMAS
20
Meaning

ENTITY TYPE

WEAK ENTITY TYPE

RELATIONSHIP TYPE

IDENTIFYING RELATIONSHIP TYPE

ATTRIBUTE

KEY ATTRIBUTE

MULTIVALUED ATTRIBUTE

COMPOSITE ATTRIBUTE

DERIVED ATTRIBUTE

TOTAL PARTICIPATION OF E2 IN R

CARDINALITY RATIO 1:N FOR E1:E2 IN R

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22
STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION
Strong vs. Weak Entities
 Strong entities
 exist independently of other types of entities
 has its own unique identifier
 represented with single-line rectangle
 Weak entity
 dependent on a strong entity…cannot exist on its own
 does not have a unique identifier
 represented with double-line rectangle
 Identifying relationship
 links strong entities to weak entities
 represented with double line diamond

Data Modeling Using the Entity-Relationship (ER) Model 21

10/01/22
Strong entity Identifying relationship Weak entity

22
 Weak Entity Types
23
 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:
Suppose that a DEPENDENT entity is identified by the dependent’s first
name and birth date, and the specific EMPLOYEE that the dependent is
related to. DEPENDENT is a weak entity type with EMPLOYEE as its
identifying entity type via the identifying relationship type
DEPENDENT_OF

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

Entity Relationship Diagrams for Data Modelling
 The elements of an ERD are the following:
 Entity – multiple instances about which the data is collected.

 Attributes – specific details or information about an entity.

 Relationships – associations between entities. A relationship

has:
 Cardinality - The number of instances of entity B that can
be associated with each instance of entity A.
 Minimum Cardinality or Modality - The minimum
number of instances of entity B that may be associated
with each instance of entity A.
 Maximum Cardinality - The maximum number of
instances of entity B that may be associated with each
instance of entity A
Data
24 Modeling Using the Entity-Relationship
10/01/22 (ER) Model
Entity Relationship Diagrams for Data Modelling
Relationships
 A one-to-one relationship (1:1) exists when the maximum

cardinality for each entity in the relationship is 1.

 A one-to-many (1:N) relationship exists when the

maximum cardinality on one side is 1 and the maximum on

the other side is many.
 A many-to-many (M:N) relationship exists when the

maximum on both sides is many.

Data
25 Modeling Using the Entity-Relationship
10/01/22 (ER) Model
Entity Relationship Diagrams for Data Modelling
 Types of entity relationships and example of their representation:

Data
26 Modeling Using the Entity-Relationship
10/01/22 (ER) Model
Entity Relationship Diagrams for Data Modelling
 Implement One-to-One Relationships
 In a relational database, one-to-one relationships between entities
can be implemented by merging the two tables/entities together.
 Implement One-to-Many Relationships
 Place the primary key of the entity that can occur only once as a
foreign key in the entity that can occur many times.
 Implement Many-to-Many Relationships
 In a relational database, many-to-many relationships between
entities can be implemented by creating a new entity that links the
other two entities together. In most cases, the primary key of the
new entity consists of the primary keys of the entities that it is
relating together.
 Spit it into two one-to-many relationships!

Data
27 Modeling Using the Entity-Relationship
10/01/22 (ER) Model
 Entity Relationship Diagrams for Data Modelling
28

Student Module
Student ID (pk) Module ID(pk)
Student first name Module name
Student last name Module Credits
… …

Student Module
Enrollment Module ID(pk)
Student ID (pk) Module ID (pk) + (fk) in Module name
Student first name Student ID (pk) + (fk)
Student last name Module Credits
… …

Data Modeling Using the Entity-Relationship

10/01/22 (ER) Model
Steps to Construct Entity Relationship Diagrams
 Like constructing DFDs and data dictionaries, the construction of ERDs is
an iterative and evolutionary process that requires refinement as a better
understanding of the system to be developed is achieved.
1. Identify entities – use DFDs and use cases to identify multiple instance
entities.
2. Add attributes – requires detailed analysis and perhaps use of
requirements gathering techniques.
3. Assign identifiers or Primary keys – Each entity type must have an
attribute or set of attributes that distinguishes one instance from other
instances of the same type.
4. Foreign Keys - The attributes of a foreign key in one entity are the
attributes of a primary key in another entity.
5. Identify Relationships – involves establishing the cardinality and the
modality of each relationship.
Data
29 Modeling Using the Entity-Relationship
10/01/22 (ER) Model
 Entity/Relationship Modelling
30

 Example
 In a University database we might have entities for
Students, Modules and Lecturers. Students might have
attributes such as their ID, Name, and Course, and
could have relationships with Modules (enrolment) and
Lecturers (tutor/tutee)

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Entity/Relationship Diagrams
31

 E/R Models are often

represented as E/R Lecturer ID

diagrams that Name Course

 Give a conceptual view of the
database
 Are independent of the Tutors Student
choice of DBMS
 Can identify some problems
in a design
Module Studies

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Diagramming Entities
32

 In an E/R Diagram, an
entity is usually drawn as a Lecturer ID

box with rounded corners Name Course

 The box is labelled with
the name of the class of
Tutors Student
objects represented by that
entity

Module Studies

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Diagramming Attributes
33

 In an E/R Diagram
attributes may be drawn as Lecturer ID

ovals Name Course

 Each attribute is linked to
its entity by a line
Tutors Student
 The name of the attribute is
written in the oval

Module Studies

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Relationships
34

 Relationships are an  Relationships have

association between two or  A name
more entities  A set of entities that
 Each Student takes several participate in them
Modules  A degree - the number of
 Each Module is taught by a entities that participate (most
Lecturer have degree 2)
 Each Employee works for a  A cardinality ratio
single Department

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Cardinality Ratios
35

 Each entity in a  One to one (1:1)

relationship can participate  Each lecturer has a unique office
in zero, one, or more than  One to many (1:M)
one instances of that  A lecturer may tutor many
students, but each student has
relationship just one tutor
 This leads to 3 types of  Many to many (M:M)
relationship…  Each student takes several
modules, and each module is
taken by several students

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Diagramming Relationships
36

 Relationships are links

between two entities Lecturer ID

 The name is given in a Name Course

diamond box
 The ends of the link show Tutors Student
cardinality

Module Studies

One Many

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Removing M:M Relationships
37

 Many to many
relationships are difficult to Student
represent
 We can split a many to Student
Has
many relationship into two
one to many relationships
Studies Enrolment
 An entity represents the
M:M relationship
In
Module

Module

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Making E/R Models
38

 To make an E/R model you  General guidelines

need to identify  Since entities are things or
 Enitities objects they are often nouns
in the description
 Attributes
 Attributes are facts or
 Relationships
properties, and so are often
 Cardinality ratios nouns also
 from a description  Verbs often describe
relationships between entities

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example
39

A university consists of a number of departments. Each

department offers several courses. A number of modules
make up each course. Students enrol in a particular course and
take modules towards the completion of that course. Each
module is taught by a lecturer from the appropriate
department, and each lecturer tutors a group of students

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - Entities
40

A university consists of a number of departments. Each

department offers several courses. A number of modules
make up each course. Students enrol in a particular course
and take modules towards the completion of that course. Each
module is taught by a lecturer from the appropriate
department, and each lecturer tutors a group of students

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - Relationships
41

 A university consists of a number of departments. Each

department offers several courses. A number of modules
make up each course. Students enrol in a particular course
and take modules towards the completion of that course.
Each module is taught by a lecturer from the appropriate
department, and each lecturer tutors a group of students

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
42

Entities: Department, Course, Module, Lecturer, Student

Department

Course Module Lecturer

Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
43

Each department offers several courses

Offers Department

Course Module Lecturer

Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
44

A number of modules make up each courses

Offers Department

Course Includes Module Lecturer

Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
45

Students enrol in a particular course

Offers Department

Course Includes Module Lecturer

Enrols In Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
46

Students … take modules

Offers Department

Course Includes Module Lecturer

Takes

Enrols In Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
47

Each module is taught by a lecturer

Offers Department

Course Includes Module Teaches Lecturer

Takes

Enrols In Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
48

a lecturer from the appropriate department

Offers Department Employs

Course Includes Module Teaches Lecturer

Takes

Enrols In Student

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
49

each lecturer tutors a group of students

Offers Department Employs

Course Includes Module Teaches Lecturer

Takes

Enrols In Student Tutors

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
50

Offers Department Employs

Course Includes Module Teaches Lecturer

Takes

Enrols In Student Tutors

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Entities and Attributes
51

 Sometimes it is hard to tell  General guidelines

if something should be an  Entities can have attributes
entity or an attribute but attributes have no smaller
 They both represent objects parts
or facts about the world  Entities can have
 They are both often relationships between them,
represented by nouns in but an attribute belongs to a
descriptions single entity

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example
52

We want to represent information about products in a

database. Each product has a description, a price and a
supplier. Suppliers have addresses, phone numbers, and
names. Each address is made up of a street address, a city,
and a postcode.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - Entities/Attributes
53

 Entities or attributes:  Products, suppliers, and

 product addresses all have smaller
 description
parts so we can make them
 price
 supplier
entities
 address  The others have no smaller
 phone number parts and belong to a single
 name
entity
 street address
 city
 postcode

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
54

Price

Description Product

Street address

Name Supplier Address City

Phone number Postcode

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - Relationships
55

 Each product has a supplier  Each supplier has an

 Each product has a single address
supplier but there is nothing  A supplier has a single
to stop a supplier supplying address
many products  It does not seem sensible for
 A many to one relationship two different suppliers to
have the same address
 A one to one relationship

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
56

Price

Description Product

Has A Street address

Name Supplier Has A Address City

Phone number Postcode

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 One to One Relationships
57

 Some relationships  Example - the supplier-

between entities, A and B, address relationship
might be redundant if  Is one to one
 It is a 1:1 relationship  Every supplier has an address
between A and B  We don’t need addresses that
 Every A is related to a B and are not related to a supplier
every B is related to an A

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Redundant Relationships
58

 We can merge the two

entities that take part in a a x

redundant relationship
together b A B y

 They become a single entity

c z
 The new entity has all the
attributes of the old one
a x

b AB y

c z

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Example - E/R Diagram
59

Price

Description Product

Has A

Name Supplier City

Phone number Postcode Street address

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Making E/R Diagrams
60

 From a description of the  Draw the E/R diagram and

requirements identify the then
 Entities  Look at one to one
 Attributes relationships as they might be
redundant
 Relationships
 Look at many to many
 Cardinality ratios of the
relationships as they might
relationships
need to be split into two one
to many links

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Debugging Designs
61

 With a bit of practice E/R

diagrams can be used to
Student
plan queries
 You can look at the diagram How can you
and figure out how to find Has find a list of
useful information students who
 If you can’t find the are enrolled
Enrolment in Database
information you need, you
systems?
may need to change the
design In

Module

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Debugging Designs
62

ID (3) For each instance of Enrolment in the

Student result of (2) find the corresponding Student
Name

Has

ID (2) Find instances of the Enrolment entity

Enrolment with the same Code as the result of (1)
Code

In

Code (1) Find the instance of the Module entity with

Module title ‘Database Systems’
Title
Data Modeling Using the Entity-Relationship (ER) Model 10/01/22
 Assignment(10%)
63

“A database will be made to store information about patients

in a hospital. On arrival, each patient’s personal details
(name, address, and telephone number) are recorded where
possible, and they are given an admission number. They
are then assigned to a particular ward (Accident and
Emergency, Cardiology, Oncology, etc.). In each ward
there are a number of doctors and nurses. A patient will be
treated by one doctor and several nurses over the course of
their stay, and each doctor and nurse may be involved with
several patients at any given time.”
                      

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22

 Assignment (20%)
64

 Identify the entities, attributes, relationships, and

cardinality ratios from the description.
                       
 Draw an entity-relationship diagram showing the items you
identified.

 Many-to-many relationships are hard to represent in SQL

tables. Explain why many-to-many relationships cause
problems in SQL tables, and show how these problems
may be overcome.

Data Modeling Using the Entity-Relationship (ER) Model 10/01/22