DATA MODELLING

USING ER DIAGRAMS
Prof. Koliya Pulasinghe
 OBJECTIVES
• Data Modelling using ER Model
 CONCEPTUAL MODELING
ER DIAGRAMS
• Many versions of ER-diagrams; differ
both in their appearance and in their
meaning
• We will use the version appearing in the
book (Database Management Systems
by Elmasri Navathe)
• Have a formal semantics (meaning) that
must be thoroughly understood, in order
to create correct diagrams
 ENTITY-RELATIONSHIP
(ER) MODEL
Requirements gathering

Conceptual database design

high level data model(ER)created

The 2 main constructs of the ER model:
Entities ,Relationships.
different books use different graphical notation.
ER DIAGRAMS
 E-R NOTATION
SYMBOL MEANING
ENTITY

WEAK ENTITY

RELATIONSHIP

IDENTIFYING
RELATIONSHIP

ATTRIBUTE

KEY ATTRIBUTE
 E-R NOTATION
SYMBOL MEANING
MULTIVALUED ATTRIBUTE

COMPOSITE ATTRIBUTE

DERIVED ATTRIBUTE

E1 R E2 TOTAL PARTICIPATION OF E2 IN R

E1
1
R
N
E2 CARDINALITY RATIO 1:N FOR
E1:E2 IN R

R
(min,max)
E
STRUCTURAL CONSTRAINT (min,
max) ON PARTICIPATION OF E IN R
 ENTITY

• An entity is an object in the real world with an independent existence

(place, person, car, house, company, job, university, etc).

• A collection of similar entities is called an entity set.

• Graphically,
ENTITY STUDENT

• First letter of each word in the entity name is uppercase

• E.g., Student

• Normally an entity is named using a ‘noun’ or ‘noun phrase’

 ATTRIBUTES

• An entity is described using a set of attributes

• Graphically, Oval.
Attribute

Name

• For example staff has 3 attributes

STAFF Address
• Name
• Position
• Salary
Age
 BOC NETWORK

• BOC is the largest bank network having over 5,000 branches and over
500,000 customers. Customers can open accounts in any bank branch.
• Identify
• Entities
• At least 3 attributes /entity
 SLIIT STUDENT
REGISTRATION
New students are required to produce their national ID
card number, name, address, age, date of birth and
gender during the registration process. they are also
required to select the degree program of their choice.
The programs are selected based on the entry criteria,
specialty, duration, fees etc.
• Entity ?
• Attributes ?
 ENTITY SET
Example: name, id, age & salary are attributes in Employee entity

Salary
salary name
e1
age id
e2 Age
e3 EMPLOYEE
e4
e5 Id
e6
…
Name
Employee Entity set
 EXAMPLE

Name Manf

JUICE

• Entity set Juice has two attributes, name and manf

(manufacturer).
• Each Juice entity has values for these two
attributes, e.g. (JustJUice, Anheuser-Busch)
 DOMAIN OF AN ATTRIBUTE

The domain of an attribute specifies the set of possible values

that the attribute can have.

age : integer values from 0 - 120

name: 20 character string

COMPOSITE ATTRIBUTES

• Composite attributes can be divided into smaller subparts.

Graphically,

Name EMPLOYEE

Surname Middle_name
First_name
 YOUR TURN!!

Think about the library database.

• Identify entities , attributes and domain of attributes
MULTI VALUED ATTRIBUTES

• Attributes containing multiple values are called multivalued

attributes. Double lines

STUDENT

E-mail
Surname First_name

gmail , yahoo, sliit e-mail address

 YOUR TURN!!

• Give an example for a multi valued attribute in library database

 DERIVED ATTRIBUTES

• Some attributes can be derived - called derived

attributes. Dotted line
• For example, age is derived from date of birth & current date.

Employee

age
name

Derived attribute
 AN EXERCISE
stuNu stuName stuMajor stuDob stuHrs stuYr stuGpa stuAge
m

210345 Aunt Annie IT, Fine Art 14 Feb 23 2 3.26 26

6 1990

300638 Bert Baxter IT 23 Jun 33 3 2.59 24

1 1992

200782 Corinne IT, 5 Oct 27 1 3.59 25

4 Creevey Education 1991

765080 David Doo Arts 28 Aug 16 1 0 22

2 1994

963596 Eli Edwards Arts, 15 Nov 35 2 2.73 24

2 Teaching 1992

211384 Fiona IT 30 Jul 28 2 3.27 26

3 Fawcett 1990
TICK WHERE APPLICABLE
Attribute simple single- composite multivalue derived
valued d
stuNum

stuName

stuMajor

stuDob

stuHrs

stuYr

stuGpa

stuAge
 KEY ATTRIBUTES
• key attribute - Minimal set of attributes which
uniquely identify an entity in the entity set.
Underlined
salary name
e1
age id
e2
e3
e4
e5
e6
…
Employee Entity set
 PRIMARY KEY

• There can be multiple key attributes called

candidate keys in a single entity.

• A single candidate key is designated as the

primary key. Name

Employee

Candidate
eid nic
key
 COMPOSITE KEY

• Sometimes, a group of attributes make up the key.

This is called a composite key.

• Example :
Composite key = (student no + Unit Number +
ST ID Unit ID Marks
marks)
IT1601 IT103 85
IT1601 IT104 78
IT1602 IT103 72
IT1603 IT104 82
 SUPER KEY
• Any other set of attributes that uniquely identify a tuple is
called the superkey of a relation
Student ( SID, Name, Address, Contact, GPA)

• What is the minimal set of attributes that uniquely identify

the relation ?
• SID =Referred to as Primary Key

• (SID + Name) Is this unique?

• Yes, but NOT the minimal set
• Referred to as Super Key
Students should enroll for courses. Student need
to provide studentID number, name, address , date
of birth, gender, contact number when he/she is
going to register for a course. University keeps
track of course number , course name , offering
faculty ,duration and course fee of each course.

How do you represent this scenario ?

How do we find the course name of the student
IT160001
name
address
phone

student gender

courseNo
studentID dob

course coursename
fee

duration faculty
 RELATIONSHIP
• Relationship is an association among two or more entities.
• Collection of similar relationships - relationship set .
• Shown as a line connecting the associated entities, labelled with
the name of the relationship.

• Normally a relationship is named using a ‘verb’ or ‘verb group’.

N 1
Student registers Course

relationship
 EXAMPLE:
RELATIONSHIPS

name addr name manf

Bar Sells Juice Bars sell some

juices.
regNo
Drinkers like
Frequents Likes some juices.

Drinkers frequent
some bars.
name Drinker addr
DEGREE OF A RELATIONSHIP

Degree of a relationship is the number of participating

entities in the relationship.

• Degree / Number of Entities = 2 Or binary

N 1
Student registers Course

relationship
 DEGREE OF RELATIONSHIP

• = no of participating entities
• Relationships can be classified based on their degree into
• Binary – relationship with two participants-
Degree/No of Entities = 2
• Ternary – relationship with three participants
Degree/No of Entities = 3
• Quaternary – relationship with four participants
Degree/No of Entities = 4
TERNARY RELATIONSHIPS

• Sometimes, we need a relationship that connects more than two entity

sets.
• Suppose that drinkers will only drink certain juices at certain bars.
• Our three binary relationships Likes, Sells, and Frequents do not allow us
to make this distinction.
• But a 3-way/ ternary relationship would.
A TYPICAL RELATIONSHIP
SET

Bar Drinker Juice

Joe’s Bar Ann Avocado super
Sue’s Bar Ann Apple Lite
Sue’s Bar Ann Watermelon cool
Joe’s Bar Bob Mango Lite
Joe’s Bar Bob Avocado super
Joe’s Bar Cal Avocado super
Sue’s Bar Cal Apple Lite
 TERNARY RELATIONSHIP

name addr name manf

regNo Bar Juice

Preferences

Drinker
name addr
 TERNARY RELATIONSHIP
name
Location
name dname
ssn lot did budget

Each employee can work at

most in one department at Employees works_In Departments
a single location

12-233 D10

12-354 •
•
D12

12-243
• D13
12-299 • Rome

London

Paris
 CARDINALITY

The cardinality ratio for a binary relationship specifies the

number of relationship instances that an entity can participate in.

• There are three types of cardinality ratios for binary relationships.

• one-to-one (1: 1)
• one-to-many (1 : N)
• many-to-many. (N : M)
 ONE-TO-ONE RELATIONSHIP

An employee manages at most one department. A department can have

only one manager managing it.
1 1
Employee Manages Department
 YOUR TURN !!

• Think about the Library database and give an example of a 1:1

relationship
 ONE-TO-MANY
RELATIONSHIP

• An employee works in at most one department.

N 1
Employee Works in Department

• This is a one-to-many (or many-to-one) relationship

 EXAMPLE: ONE - MANY
RELATIONSHIP

• Favorite, from Drinker to Juice is one-many.

• A drinker has at most one favorite juice.
• But a juice can be the favorite of any number of drinkers, including
zero.

N 1
Drinker Favorite Juice
 YOUR TURN !!

• Think about the Library database and give an example of a 1:N

relationship
 MANY-TO-MANY RELATIONSHIP

• An employee can work on several projects. A project can have many

employees working on it.

M N
Employee Works on Project
 EXAMPLE: MANY - MANY
RELATIONSHIP

• Likes, from Drinker to Juice is many-many.

Drinker N like M Juice

 YOUR TURN !!

• Think about the Library database and give an example of a N:M

relationship
 EXERCISE

Identify the Cardinality

• Doctor patient
• Principal School
• Mother child
• Husband wife
• Teacher Student
Rosell, 23 years old, a diploma holder joined the ‘ABC’ company as a
management trainee. She joined the Production department on
01/01/2005. When Rosell successfully completed her Commerce
degree, she was promoted as Finance Executive and transferred to the
Finance department from 02/02/2006.
Considering her MBA qualification, the management of the company
promoted her as Assistant Manager - Business Development and
transferred to the Business Development department with effect from
03/03/2008.
 DESCRIPTIVE
ATTRIBUTES
A relationships can also have descriptive attributes . Used to record
information about the relationship rather than any one of the
participating entities.

since
startdate

N 1
Employee Works in Department

Nimal works in accounts dept since 2008

 DESCRIPTIVE
ATTRIBUTES

1/2/99

e011 1/2/09
HR
e022
1/5/01
Sales
e033 1/2/07
Marketing
e044
1/3/05

Departments
Employees
Work_in
ATTRIBUTE ON RELATIONSHIP

Bar Sells Juice

price

Price is a function of both the bar and the juice,

not of one alone.
ER DIAGRAM
YOUR TURN !
 YOUR TURN !!!

• Identify the entities -> nouns

• Identify the relationships -> verbs

• Determine
• Cardinality

• Participation (total / partial)

RESTRICTIONS- CONSTRAINTS

What is the criteria to become a student at SLIIT?

Register for a degree
Mandatory /compulsory

Student MUST be registered in a degree

Restriction - Constraint
How do we present this information in the data base??
 • Student must be registered in a degree.

Registe
Student Course
• N r 1
PARTICIPATING CONSTRAINTS

• Participating constraint specifies whether the existence of an

entity depends on its being related to another entity via the
relationship type.
 PARTICIPATION CONSTRAINTS
• For example, if we specify that an employee must always work for a
department.
• Then we say that the relationship “works in” is in total participation
from employee entity to department entity.

(double lines)  total participation

Employee Works in Department

Employee must work in a department

Department may or may not have employees
 E-R MODEL (CONTD.)

• If the relationship is not in total participation, then it is known as in

partial.

• For example, from Department to Employee

• Department may or may not have Employees

PARTICIPATION CONSTRAINTS

Participation Constraint
Student?
 WEAK ENTITY

• Parents employed?
• Does the company cover THE CHILDRENS medical insurance?
• How do you claim your medical bills
• Can you get it reimbursed or through your parents
• Is the same coverage given to children after resignation
 WEAK ENTITY

Strong entity Weak entity

1 M
Employee covers Dependent

age
age Double lined
idid name
box

Partial key
 WEAK ENTITY
• Some entities can’t exist on its own.
• Its’ existence-dependent on another entity, i.e., it cannot exist
without the entity with which it has a relationship.

• It inherits the part of the primary key from the entity to which it is
related.

• Entity types without any key attributes is called weak entity types.
• The attributes in the weak entity participating in the key are
called partial keys.

• The owner entity and the weak entity participates in an

identifying relationship.

• The cardinality of the identifying relationship is either one-to-one

or one-to-many from owner entity to weak entity.

• The weak entity must have total participation in the identifying

relationship.
 EXAMPLE- WEAK ENTITY

address
atmID since transac# amount

type
1 N
ATM facilitates Transactions
 RECURSIVE
RELATIONSHIP
• Have you formed your project groups
• What is the structure?
• Leader and members

1 N
Leader Leads Member

X
• Both leader and members are students

1 N
Student Leads Student
 E-R MODEL (CONTD.)
Student leads set of students in group
assignments.
leads

Group leader Member

1 N

Student
 RECURSIVE
RELATIONSHIP
• Entities participating in a relationship need not be distinct. Such
relationships are called recursive relationships.

• Each entity in the relationship play a role in the relationship. It is

recommended to state the role in recursive relationships.
 RECURSIVE RELATIONSHIPS

• In most companies, each employee (except the CEO) is supervised by

one manager. Of course, not all employees are managers.

eno name eno name

X
1 supervis N
Manager Employee
e
salar salar
y Job y Job
tittle tittle

eno name
1 Manager
supervis
Employee
e
salar
y Job N Subordinates
tittle
 YOUR TURN !

• Draw an ER diagram for the following requirements.

• A company database needs to store information about employees

(identified by NIC, salary, position, phone, office);
• departments (identified by dno, with department name and annual
budget);
• children of employees (with name, and age as attributes).
• Employees work in departments; each department is managed by
an employee; a child must be identified uniquely by name when the
parent (who is an employee; assume that only parent works for the
company) is known. We are not interested about a child once the
parent leaves the company.
ALTERNATE NOTATION – MIN: MAX

• Used to specify the number of possible occurrences of

each participating entity type in a relationship.

• Contains two parts

• Min
• Max
• For example, (0,1)
• Min = 0
• Max = 1

• Max of a multiplicity range denotes Cardinality

• Min of the range denotes Participation constraint
THE (MIN,MAX) NOTATION

(1,1)
(0,1)

1 1

Employee may or may not mange a department. He can at most

manage one department.
A department must be managed by an employee
THE (MIN,MAX) NOTATION

(0,N)
(1,1)

N 1
Employee must(NN
work for a department. .
A department may or may not have any employee
 ENHANCED ER MODELLING
• ER modelling does not capture all the
semantics of client’s domain, such as
• ‘ISA’ (‘is a’) relationship or specialization-
generalization
• ‘Manager’ entity type ‘is a’ sub entity of
‘Staff’ entity.

• Enhanced ER models represent the above

relationships, hence capture client’s domain
more comprehensively.
DIAGRAMMATIC REPRESENTATION
OF ‘ISA’ RELATIONSHIP
name
position
staffNo

Staff Specialization/generalization
indicator

ISA Both hourly employees &

contract employees
Belong to the type Staff
Hourly_Emp Contract_Emp

Hours worked
Contract_ duration
Hourly rate