Chapter 4

Conceptual data Modeling

Entity-Relationship (ER) Modeling

1
Lesson contents
Overview of Database Design Stages,
Process.
ER-Model Concepts
◦ Part I
 Entity
 Attribute
 ER –Diagram notation
◦ Part II
 ER –Relationship
 Cardinality
 Participation constraints

2
Lesson objective
At the end of this lesson students/you/are
able to:
◦ Define the word Entity with respect to
database concept
◦ Define attribute of an entity
◦ Describe types of attribute with respect to
database concept
◦ Describe the advantage of key attribute in a
given entity

3
Lesson objective…
At the end of this lesson students/you/are able to:
◦ Identify ER diagramatic notations
◦ Identify different Entities given a problem description
◦ Identify different attributes given a problem
description
◦ Identify relationship between entities given the
problem description
◦ Describe participation constraints of an entity
◦ Draw ER Diagram with appropriate attribute and
relationship given a problem description

4
Database Design Stages
1. Requirement Analysis
2. Conceptual Design
3. Logical Design
4. Physical Design
5. Implementation

5
Requirement Analysis
The requirement analysis is an
investigation phase, during which all the
needed information are gathered and
analyzed.
Requirement is analyzed and the following
points are identified
◦ Objects
◦ Interaction between objects
◦ User Forms and Reports
◦ …
6
Example on Requirement Analysis
The problem is to design a database system
for “X Household and Office Furniture
P.L.C” based on the following information.
◦ The information are kept on daily basis about what the
employees do, what resource they use, which furniture
are made out of the resource, and all the sale and order
records.
◦ Furniture is produced by an employee from resource (s).
◦ Every furniture is described by the furniture Id, catalog
no, model type, color, production start date, production
complete date and price; and every resource is described
by stock no, type, current cost, and avail quantity.
◦ A sale of furniture is made with a receipt for both regular
customers and buyers.

7
… Example on Requirement Analysis
◦ Regular customers are described by customer Id,
name, address and contact person; where as, no detail
record is established for buyers.
◦ Orders for furniture are allowed only for regular
customers and in ordering a furniture, the customer
may pay part of the total price and left in debt, then
pay the debt in some other time.
◦ For the payment transactions three types of receipt are
used; one for sale, one for order and one for due
payment (debt).
◦ A single receipt of each type is prepared only for one
buyer or customer but a number of furniture can be
sold or ordered by one receipt.
◦ Every receipt is prepared by an employee and it has
receipt number, prepared date, total price, and tax.
8
Conceptual Design
The two common conceptual models for
Relational Database system are
◦ Entity – Relationship (E/R) model/EER
◦ Object-Oriented Data Language (ODL) model

9
ER-Model Concepts

◦ Part I
 Entity
 Attribute
 ER –Diagram notation

10
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

11
Entity-Relationship (E/R) Model

Widely used conceptual level data model

◦ proposed by Peter P Chen in 1970s
Data model to describe the database system
at the requirements collection stage
◦ high level description.
◦ easy to understand for the enterprise managers.
◦ enough to be used for system building.

12
Entity
Entity-athing of independent physical or
conceptual existence and distinguishable.
◦ In the University database context,
 an individual student, faculty member, a class room, a
course are entities.

Entity Type-Collection of entities all having

the same properties.
◦ Student entity set –collection of all student entities.
Course entity set –collection of all course entities.

13
Attributes
Each entity is described by a set of
attributes/properties.
Student entity
◦ StudName–name of the student.
◦ IDNumber–the roll number of the student.
◦ Sex–the gender of the student etc.
All entities in an Entity type have the same set
of attributes.
Chosen set of attributes –amount of detail in
modeling.
14
Types of Attributes
Simple Attributes
◦ having atomic or indivisible values.
◦ example: Dept–a string

Composite Attributes
◦ having several components in the value
◦ example: Qualification with components(DegreeName, Year,
UniversityName)
•Derived Attributes
◦ Attribute value is dependent on some other attribute.
◦ example: Age depends on DateOfBirth.
So age is a derived attribute.

15
Types of Attributes
Single-valued
◦ having only one value rather than a set of values
◦ for instance, PlaceOfBirth–single string value.
Multi-valued
◦ having a set of values rather than a single value.
◦ for instance, CoursesEnrolled attribute for
student
EmailAddress attribute for student
PreviousDegreeattribute for student.

16
Entity Sets and Key Attributes

Each entity type will have a collection of entities stored in the

database
◦ Called the entity set

Entity set is the current state of the entities of that type that are
stored in the database
Key– an attribute or a collection of attributes whose value(s)
uniquely identify an entity in the entity set.
◦ For instance,
 IDNumber -Key for Student entity set
 EmpID -Key for Staff entity set

A keyfor an entity set may have more than one attribute.

Each key is underlined

17
Domains of Attributes
Each attributes takes values from a set
called its domain
◦ For instance, studentAge–{18,19, …, 55}
◦ HomeAddress–character strings of length 35
Each attribute has a value set (or data type)
associated with it – e.g. integer, string, sub
range, enumerated type, …Domain

18
Diagrammatic Notation for Entities

entity -rectangle
attribute -ellipse connected to rectangle
multi-valued attribute -double ellipse
composite attribute -ellipse connected to
ellipse
derived attribute-dashed ellipse
…

19
Summary of notation for ER diagrams

20
GROUP Work

Instruction
 Make a group of 2 or 3 students.
 After discussion a randomly selected student
from any group will draw the partial ER
diagram ( Entities with their attributes)
Time given:
4 minute -discussion of basic terms
11 minutes -identification of Entities & Attributes

21
Discussion……
1. Discuss the following terms (4 minutes)
◦ Entity concept
 Entity, Entity Type ,Entity Set

◦ Attribute
◦ Types of attribute
 single-valued, Multi-valued, Composite, Derived

◦ Key attribute
◦ Domain of attribute
22
Discussion……
[Link] the given Problem description (11
minutes)
A. Identify the possible Entities and explain
why you select them as Entities
B. Identify possible attributes for each of the
Entities identified above (A)
C. Identify possible Key attribute for each Entity
type identified above (A)
D. Using ER diagram notation draw partial ER
Diagram for each entity separately
23
ER –Model Concept….
◦ Part II
 ER –Relationship
 Cardinality
 Participation constraints

24
Relationships

When two or more entities are associated with

each other
we have an instance of a Relationship.
•E.g. student Sara enrolls in Database management
course
•Relationship enrolls has Student and Course as the
participating entities.
Enrolls is called a relationship Type
Relationship Set : is a Set of similar relationships

25
Relationships….
Degree of a relationship
Degree: the number of participating entities.
◦ Degree 2: binary
◦ Degree 3: ternary
◦ Degree n: n-ary
Recursive relationship: An entity set relating to
itself gives rise to a recursive relationship
E.g. the relationship prerequisiteOf is an
example of a recursive relationship on the
entity Course
26
 Diagrammatic Notation for Relationships

Relationship –diamond shaped box

Rectangle of each participating entity is connected by a

line to this diamond. Name of the relationship is written

in the box

27
Cardinality ratio
Binary Relationships and Cardinality Ratio

The number of entities from E2 that an entity from E1can

possibly be associated through R (and vice-versa) determines
the cardinality ratio of R.
Four possibilities are usually specified:
◦ one-to-one (1:1)
◦ one-to-many (1:N)
◦ many-to-one (N:1)
◦ many-to-many (M:N)

28
Cardinality Ratios
One-to-one: An E1 entity may be associated with
at most one E2 entity and similarly an E2 entity
may be associated with at most one E1 entity.
One-to-many: An E1entity may be associated with
many E2 entities whereas an E2 entity may be
associated with at most one E1entity.
Many-to-one: …( similar to above)
Many-to-many : Many E1entities may be
associated with a single E2 entity and a single
E1entity may be associated with many E2entities.
Eg. For each of the relationship

29
One-to-Many
 A student is belongs to at most one Department
 A Department can have any number of students

Student Belongs Department

Department Belongs Student 30

Many-to-Many
 A student is belongs to any number of Department
 A Department can have any number of Student

Student Belongs Department

Department Belongs Student 31

One-to-One
 A Professor can teach at most one Course
 A course can be taught by at most one Professor

professor Teach Course

Professor Teach Course 32

Participation Constraints

An entity set may participate in a relation

either totally or partially•
Total participation: Every entity in the set
is involved in some association (or tuple)
of the relationship.
Partial participation: Not all entities in
the set are involved in association (or
tuples) of the relationship.

33
Example of total/partial Participation

34
GROUP Work 2

Instruction
 Make a group of 2 or 3 students.
 After discussion a randomly selected student
from any group will draw the Full ER diagram
( Entities with their attributes and
relationships)
Time given:
4 minute -discussion of basic terms
11 minutes -identification of Entities & Attributes.
35
Discussion……
1. Discuss the following terms (4 minutes)
◦ Entity relationship
 Types of Relationship type

◦ Cardinality ratio
 Types of cardinality Ratios

◦ Participation Constraints

36
Discussion……
2. For the Entities type that you identified in Group
discussion 1: (11 minutes)
A. Identify the possible Relationship type and
explain why you select them as Relationship type
B. Identify the Cardinality Ratio of each relationship
type
C. Identify the existing Participation constraint of
each relationship identified
D. Using ER diagram notation draw full ER Diagram
with the corresponding attribute, relationship type,
cardinality Ratio and Participation constraints

37
Weak Entity Sets
An entity set that does not have a primary key is referred to as
a weak entity set.
The existence of a weak entity set depends on the existence of
a identifying entity set
◦ it must relate to the identifying entity set via a total, one-to-many
relationship set from the identifying to the weak entity set
◦ Identifying relationship depicted using a double diamond
The discriminator (or partial key) of a weak entity set is the
set of attributes that distinguishes among all the entities of a
weak entity set.
The primary key of a weak entity set is formed by the primary
key of the strong entity set on which the weak entity set is
existence dependent, plus the weak entity set’s discriminator.

38
 Weak Entity Set Notations
Double rectangles for weak entity set
Double diamond for weak entity relationship
Dashed underscore for discriminator

39
Summary
Entity
Attributes
Relationship type
Cardinality ratio
Participation constraints
ER Diagram
Weak Entity
Relationship attributes

40