Database Design

Entity-relationship Model
(ER model)

1
1. Requirement analysis
2. Conceptual database design - ER
3. Logical database design – relational
4. Schema refinement
5. Physical database design
6. Application and security design

2
 Entity-Relationship Model
• Entity-Relationship (ER) model is a popular
conceptual data model.

• This model is used in the design of

database applications

• The model describes data to be stored and

the constraints over the data.

3
 Entity-Relationship diagram
(E-R diagram)
• The E-R model can be presented graphically
by an E-R diagram.

age address

phone
name

Employee attribute

entity
4
 Key
• A superkey is any set of attributes which can
uniquely identify an entity.

– E.g. Apartment ( street, number, floor, flat, size,

number_of_rooms, year )

• A key is a minimal set of attributes whose values

uniquely identify an entity in the set.

– E.g. Apartment ( street, number, floor, flat, size,

number_of_rooms, year )
---not minimal … not a key, but a superkey

5
 Keys

• A key is also called a candidate key.

• There could be more than one candidate

key.

6
 lecturer = Nizar Ali location

date
course = Database course
C1 location = SCI5 time
lecturer
date = 30 Oct 2006

time = 8:30am Lecture

• For example, {location,date,time} is a key.

• {lecturer, date, time} is also a key.

7
• The key should depend on the real life
possibility rather than on the current set of
the data.

• For example, in a database which contains only

two employees aged 30 and 40,
the age may distinguish each employee.
However, there can be in the future a new
employee with the same age as an existing
employee.
Age is therefore not a key.

8
• For employee, if (name, address, age, phone) still cannot be a
key, or we think it is too cumbersome, we can add an extra
attribute as a key.

employee 1

name = Aso Ibrahem ID

age
address = 25, Azmer, Sulaimani address

ID = A234980 phone
name
age = 35

phone = 3112343 Employee

9
 Relationships
• A relationship is an association among two
or more entities.
Example:
E1 = ( Nizar, Ary, Sarbast, Sozan )
E2 = ( CSC1234, CSC4567, CSC6677, CSC3399 )

Lectuer course
teach

Nizar CSC1234

Sarbast CSC4567

Ari CSC6677

Sozan CSC3399

10
• A relationship must be uniquely identified by the
participating entities, without reference to the
descriptive attributes.
– In the previous example, each relationship must be
uniquely identified by the combination of the employee id
and the department did.

• Thus, for each employee-department pair, we

cannot have more than one associated “since” value.
– (Azad-ID, Dept ABC, 1-2003)
(Azad-ID, Dept ABC, 2-2004)

11
• A relationship set can also be represented
by an E-R diagram.

id Phone_no

name address
Course_id title

attribute Lecturer Teach course

entity relationship

12
• A relationship can also have descriptive attributes.
• Descriptive attributes are used to record
information about the relationship.

Attribute of the relationship

Since
(date)

ID Phone_no

Name Address did dname

Employee Works_in Department

13
• Suppose now each department has offices in several
locations and we want to record the locations at which each
employee works.
• This relationship is ternary.

Since
(date)

ID Phone_no

Name Address did dname

Employee Works_in Department

address Location capacity

14
1. Can an employee work in two locations for the same department ?
2. Can an employee work in two projects for the same department ?

Project

ID Phone_no

Name Address did dname

Employee Works_in Department

address Location capacity

15
 Project

ID Phone_no

Name Address did dname

Employee Works_in Department

address Location capacity

• (emp1, dept1, location1, proj1)

• (emp1, dept1, location1, proj2)
• ------------------------------------------
• (emp1, dept1, location2, proj2)
• (emp1, dept2, location1, proj1) 16
• Recursive Relationship
– Sometimes a relationship might involve two
entities in the same entity set.

ID Phone_no
Employee

Name Address

supervisor
Employee

subordinate
Reports_to

×
Reports_to Employee

17
 Key constraints
(mapping constraints)
• The mapping of a binary relationship can be
classified into the following cases:

1-to-1 1-to Many Many-to-1 Many-to-Many

18
• One-to-many
– One-to-many constraint from A to B: an entity in B can
be associated with at most one entity in A.
– Each child can appear in at most one mother-child
relationship.

Mother Child
Mother-of

Lisa Susan

Rose Mike

Cathy Cindy

Tina Eddie

19
• Child has a key constraint in the mother-of
relationship set.
• This restriction can be indicated by an arrow in the
E-R diagram.

mid age

name address
cid name

Mother Mother-of Child

Intuitively, the arrow states that given a child entity, we

can uniquely determine the mother-of relationship.

20
Many-to-one
– Similar to one-to-many

ID Phone_no

Name Address did dname

Employee Works_in Department

Each employee works in one department

21
• One-to-one
– If the relationship between A and B satisfies the one-to-
one mapping constraint from A to B, then
– an entity in A is related to at most one entity in B, and
– an entity in B is related to at most one entity in A.

EMPLOYEE DEPARTMENT
MANAGES

22
 ID Phone_no

Name Address did dname

Employee manages Department

An employee can associate with at

most one department via the
Relationship “manages”.
A department can associate with at
most one employee via the relationship
“manages”

23
• Many-to-many
– An entity in A is associated with any number of
entities in B, and
an entity in B is associated with any number of
entities in A.
– In fact, it means that there is no restriction in
the mapping
CUSTOMER BORROWER LOAN

24
• A participation constraint imposes some
requirements on whether an entity participates in
a relationship.

– Total participation - each entity in the entity

set must participate in at least one relationship.

– partial - an entity in the entity set may not

participate in a relationship.

25
 ID Phone_no

name address dept-number dept-name

Employee WORKS-FOR Department

If the participation of an entity set in a relationship set

is total, the two are connected by a thick link;
independently, the presence of an arrow indicates a key
constraint.
26
 Weak Entities
• Strong entity
– An entity which has a super key.
– Each entity can be distinguished from other entities in
the same set.

• Weak entity
– Without super key.
– May not be able to distinguish themselves from others
without associations with entities in other entity sets.

27
 Class Hierarchies
• Sometimes it is natural to classify the
entities in an entity set into subclasses.

name
ID address

Employee

Hour_worked
Contract_ID
Hour_wages ISA

Hourly-Emp Contract_Emp

28
• We can specify two kinds of constraints
with respect to ISA hierarchies,
– Overlap constraints
• Determine whether two subclasses are allowed to
contain the same entity.
E.g. an employee can be an Hourly_Emp as well as a
Contract_emp entity

– Covering constraints
• Determine whether the entities in the subclasses
collectively include all entities in the superclass.
e.g. every Employees entity also have to be an
Hourly_Emps or a Contract_emps.

29
 Aggregation

• Sometimes, we have to model a relationship

between a collection of entities and relationships.

• Aggregation allows us to indicate that a

relationship set (identified through a dashed box)
participates in another relationship set.

30
 Aggregation
• Example
– Consider an entity set called project.
– Each project entity is sponsored by one or more
departments.
– A department that sponsors a project might
assign employees to monitor the sponsorship.
– Intuitively, monitors should be a relationship
set that associates a Sponsors relationship
(rather than a project or department entity)
with an Employee entity.

31
 name
ID address

Employee

Monitors until

started-on dname
pid pbudget did budget

Project Sponsors Department

32
 Exercise #1
A university database contains information about professors
(identified by social security number) and courses
(identified by courseid). Professors teach courses; each of
the following situations concerns the Teaches relationship
set. For each situation, draw an ER diagram that describes
it.
• Professors can teach the same course in several semesters,
and each offering must be recorded.

33
33
 Exercise #2
Professors can teach the same course in several semesters, and
only the most recent such offering needs to be recorded.

34
34
 Exercise # 3 and 4
• Every professor teaches exactly one course (no more, no
less)

• Every professor teaches exactly one course (no more, no

less), and every course must be taught by some professor

35
35