Chapter 4:

Logical Database Design and

the Relational Model

Modern Database Management

Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi

© 2011 Pearson Education, Inc. Publishing as Prentice Hall

1
 Objectives
List properties of relations
Create tables with entity and relational integrity
constraints
Transform E-R and EER diagrams to relations
Use normalization to convert anomalous tables to well-
structured relations
Define first, second, and third normal form
Describe problems from merging relations

2
 INTRODUCTION
Conceptual data modeling (ER Model) is
about understanding the organization—
getting the requirements right.
Logical database design is about creating
stable database structures—correctly
expressing the requirements in a
technical language. Both are important
steps that must be performed carefully.

3
 Relation
A relation is a named, two-dimensional table of data.
A table qualifies as a relation when it satisfies the
following conditions:
It has a unique name.
Every attribute value is atomic (not multivalued, not composite).
Every row is unique (does not have two rows with exactly the
same values for all their fields).
Attributes (columns) in tables have unique names.
Order of columns and rows are irrelevant.

4
 Correspondence with E-R
Model
Relations (tables) correspond with
entity types and with
many-to-many relationship types.
Rows correspond with
entity instances and with
many-to-many relationship instances.
Columns correspond with attributes.

NOTE: The word relation (in relational database) is

NOT the same as the word relationship (in E-R
model).
5
 Relation Example

EMPLOYEE1 (EmpID, Name, DeptName, Salary)

6
Figure 4-2 Eliminating multivalued attributes

(a) Table with repeating groups

7
Figure 4-2 Eliminating multivalued attributes

(b) Employee2 relation

8
 Key Fields
Keys are special fields
Primary key unique identifier of a relation
Foreign key an attribute in a relation that serves as the
primary key of another relation. It establishes the relationship
between two tables.
Keys can be simple (a single field) or composite (more
than one field).

9
 Sample Database
Relational database may consist of any
number of relations
Schema – describes the structure of database
Schema can be represented by
Name of relations followed by attributes in
parentheses
Graphical representation

10
Figure 1-3 Comparison of enterprise and project level data models

Segment of a project-level data model

11
Figure 4-3 Schema for four relations (Pine Valley Furniture Company)

Primary Key
Foreign Key
(implements 1:N relationship
between customer and order)
Combined, these are a composite primary
key (uniquely identifies the order
line)…individually they are foreign keys
(implement M:N relationship between
order and product)

12
Sample Database

13
 Integrity Constraints
Rules limiting acceptable values and actions
Purpose is to facilitate maintaining accuracy
and integrity of data
There are three types
Domain constraints
Entity Integrity
Referential Integrity

14
 Integrity Constraints
Domain Constraints - All of the values
that appear in a column of a relation must
be from the same domain.
Allowable values for an attribute.
Domain definition usually consists of the
following components: domain name,
meaning, data type, size (or length), and
allowable values or allowable range (if
applicable)
15
 Domain definitions enforce domain integrity constraints

16
 Integrity Constraints
Entity Integrity
Every relation has a primary key and no
primary key attribute may be null.
• All primary key fields MUST have data

17
 Integrity Constraints
Referential Integrity constraint is a rule
that maintains consistency among the rows of
two relations.
Any foreign key value (on the relation of the
many side) MUST match a primary key value
in the relation of the one side. (Or the foreign
key can be null)

18
Figure 4-5
Referential integrity constraints (Pine Valley Furniture)

Referential
integrity
constraints are
drawn via arrows
from dependent to
parent table

19
 Integrity Constraints
Referential Integrity
• Delete Rules - For instance, what happens to order
data when a customer who has submitted orders is
deleted?
1. Restrict–don’t allow delete of “parent” side if related rows exist
in “dependent” side
2. Cascade–automatically delete “dependent” side rows that
correspond with the “parent” side row to be deleted
3. Set-to-Null–set the foreign key in the dependent side to null if
deleting from the parent side

20
Figure 4-6 SQL table definitions

Referential
integrity
constraints are
implemented with
foreign key to
primary key
references

21
Transforming EER Diagrams
into Relations

22
 Mapping Entities to Relations
1. Simple attributes: E-R attributes map
directly onto the relation

23
 Figure 4-8 Mapping a regular entity

(a) CUSTOMER entity type with simple attributes

(b) CUSTOMER relation

24
 Mapping Entities to Relations
1. Simple attributes: E-R attributes map directly
onto the relation
2. Composite attributes: Use only their
simple, component attributes

25
 Figure 4-9 Mapping a composite attribute

(a) CUSTOMER
entity type with
composite
attribute

(b) CUSTOMER relation with address detail

26
 Mapping Entities to Relations
1. Simple attributes: E-R attributes map directly
onto the relation
2. Composite attributes: Use only their simple,
component attributes
3. Multivalued Attribute: Becomes a
separate relation with a foreign key
taken from the superior entity

27
 Figure 4-10 Mapping an entity with a multivalued attribute

(a)

Multivalued attribute becomes a separate relation with foreign key

(b)

One–to–many relationship between original entity and new relation

28
 Mapping Entities to Relations
1. Simple attributes: E-R attributes map directly
onto the relation
2. Composite attributes: Use only their simple,
component attributes
3. Multivalued Attribute: Becomes a separate
relation with a foreign key taken from the
superior entity
4. Derived Attribute: Not included in the
relational model
29
Mapping Binary Relationships
One-to-Many–Primary key on One side
becomes a foreign key on Many side

30
Figure 4-12 Example of mapping a 1:M relationship
a) Relationship between customers and orders

Note the mandatory one

b) Mapping the relationship

Again, no null value

in the foreign
key…this is because
Foreign key
of the mandatory
minimum cardinality
31
 Mapping Weak Entities
Becomes a separate relation with a foreign
key taken from the superior entity
Primary key composed of:
Partial identifier of weak entity plus
Primary key of identifying relation (strong entity)

32
Figure 4-11 Example of mapping a weak entity

a) Weak entity DEPENDENT

33
Figure 4-11 Example of mapping a weak entity (cont.)

b) Relations resulting from weak entity

NOTE: the domain constraint

for the foreign key should
NOT allow null value if
DEPENDENT is a weak
entity
Foreign key

Composite primary key

34
Mapping Binary Relationships
One-to-Many–Primary key on One side
becomes a foreign key on the Many side
Many-to-Many–Create a new relation
with primary keys of the two entities as
its primary key

35
 Figure 4-13 Example of mapping an M:N relationship
a) Completes relationship (M:N)

The Completes relationship will need to become a separate relation

36
Figure 4-13 Example of mapping an M:N relationship (cont.)
b) Three resulting relations

Composite primary key

Foreign key
New
Foreign key
intersection
relation

37
Mapping Binary Relationships
One-to-Many–Primary key on One side
becomes a foreign key on Many side
Many-to-Many–Create a new relation with
primary keys of the two entities as its primary
key
One-to-One–Primary key on
mandatory side becomes a foreign key
on optional side

38
Figure 4-14 Example of mapping a binary 1:1 relationship
a) In charge relationship (1:1)

Often in 1:1 relationships, one direction is optional

39
Figure 4-14 Example of mapping a binary 1:1 relationship (cont.)
b) Resulting relations

Foreign key goes in the relation on the optional side,

matching the primary key on the mandatory side

40
Mapping Associative Entities
Identifier Not Assigned
Default primary key for association relation is
composed of primary keys of the two entities (as in
M:N relationship)
Identifier Assigned
It is natural and familiar to end-users
Default identifier may not be unique

41
Figure 4-15 Example of mapping an associative entity
a) An associative entity

42
Figure 4-15 Example of mapping an associative entity (cont.)
b) Three resulting relations

Composite primary key formed from the two foreign keys

43
Figure 4-16 Example of mapping an associative entity with
an identifier
a) SHIPMENT associative entity

44
Figure 4-16 Example of mapping an associative entity with
an identifier (cont.)
b) Three resulting relations

Primary key differs from foreign keys

45
Mapping Unary Relationships
One-to-Many–Recursive foreign key in
the same relation

46
 Figure 4-17 Mapping a unary 1:N relationship

(a) EMPLOYEE entity with unary relationship

(b) EMPLOYEE relation with recursive foreign key

47
Mapping Unary Relationships
One-to-Many–Recursive foreign key in
the same relation
Many-to-Many–Two relations:
One for the entity type
One for an associative relation in
which the primary key has two
attributes, both taken from the
primary key of the entity

48
Figure 4-18 Mapping a unary M:N relationship

(a) Bill-of-materials
relationships (M:N)

(b) ITEM and

COMPONENT
relations

49
 Mapping Ternary (and n-ary)
Relationships
One relation for each entity and one for
associative entity
Associative entity has foreign keys to each entity
in relationship

50
 Figure 4-19 Mapping a ternary relationship

a) PATIENT TREATMENT Ternary relationship with

associative entity

51
 Figure 4-19 Mapping a ternary relationship (cont.)

b) Mapping the ternary relationship PATIENT TREATMENT

Remember This is why But this makes a It would be

that the treatment date very better to create a
primary key and time are cumbersome surrogate key
MUST be included in the key… like Treatment#
unique composite
primary key

52
Mapping Supertype/Subtype
Relationships
One relation for supertype and for each subtype
Supertype attributes go into supertype relation
Subtype attributes go into each subtype;
Primary key of supertype relation also becomes
primary key of subtype relation
1:1 relationship established between supertype
and each subtype, with supertype as primary
table

53
Figure 4-20 Supertype/subtype relationships

54
Figure 4-21
Mapping supertype/subtype relationships to relations

These are implemented as one-to-one

relationships

55
 Data Normalization
Tool to validate and improve a logical design
so that it satisfies certain constraints that
avoid unnecessary duplication of data
Process of decomposing relations with
anomalies to produce smaller, well-
structured relations

56
 Well-Structured Relations
A relation that contains minimal data redundancy
and allows users to insert, delete, and update rows
without causing data inconsistencies
Goal is to avoid anomalies
Insertion Anomaly–e.g. certain information cannot be
recorded
Deletion Anomaly–e.g. deleting rows may cause loss of
data that would be needed for other future rows
Modification Anomaly–e.g. changing data in a row forces
changes to other rows because of duplication

General rule of thumb: A table should not pertain to

more than one entity type
57
 Example–Figure 4-2b

Question–Is this a relation? Answer–Yes: Unique rows and

no multivalued attributes
Question–What’s the primary key? Answer–Composite: EmpID,
CourseTitle

58
 Anomalies in this Table
Insertion–
can’t enter new employee without having the employee take a class
Deletion–
if we remove employee 140, we lose information about the
existence of a Tax Acc class

Modification–
giving a salary increase to employee 100 forces us to update
multiple records
Why do these anomalies exist?
Because there are two themes (entity types) in this
one relation. This results in data duplication and an
unnecessary dependency between the entities
59
Main Goals of Normalization
Minimize data redundancy, thereby avoiding
anomalies and conserving storage space
Make it easier to maintain data (insert,
update , delete)
Simplify enforcement of referential integrity
constraints
Provide a better design and a stronger basis
for future growth

60
 Functional Dependencies and
Keys
Functional Dependency: The value of one
attribute (the determinant) uniquely
determines the value of another attribute
EmpID, CourseTitle®DateCompleted
SSN ®Name, Address, Birthdate
VIN ®Make, Model, Color
ISBN ®Title, FirstAuthorName, Publisher

Value of the other attribute cannot be computed based on

the value of the determinant, rather, if you know the value
of the determinant, there can be only one value for the
other attribute
61
Functional Dependencies and
Keys
Candidate Key: Attribute, or group of
attributes, that unique identifies a row in a
relation. One of the candidate keys will
become the primary key
• E.g. if there is both credit card number and SS# in a
table… both are candidate keys
Unique identification. Each nonkey attribute
should functionally depend on key
Nonredundancy. No attribute in the key can be
deleted without destroying the property of a
unique identification

62
Figure 4.22 Steps in normalization

3rd normal form is

generally considered
sufficient

63
 First Normal Form
No multivalued attributes
Every attribute value is atomic
A primary key has been identified
All relations are in 1st Normal Form

64
Table with multivalued attributes, not in 1st normal form

Note: this is NOT a relation

65
Table with no multivalued attributes and unique rows, in 1st
normal form

Note: this is a relation, but not a well-structured one

66
 Anomalies in this Table
Insertion–
if new product is ordered for order 1007 of existing customer,
customer data must be re-entered, causing duplication
Deletion–
if we delete the Dining Table from Order 1006, we lose
information concerning this item's finish and price

Modification (Update)–
changing the price of product ID 4 requires update in multiple
records
There are multiple themes (entity types) in one relation.
This results in duplication and an unnecessary
dependency between the entities
67
 Second Normal Form
1NF PLUS every non-key attribute is fully
functionally dependent on the ENTIRE
primary key
Every non-key attribute must be defined by the entire
key, not by only part of the key
No partial functional dependencies

68
Functional dependencies of INVOICE

Full Dependency
OrderID, ProductID è OrderQuantity

Partial Dependency
OrderID è OrderDate, CustomerID, CustomerName, CustomerAddress
ProductID è ProductDescription, ProductFinish, ProductStandardPrice

Transitive Dependency
OrderID è CustomerID è CustomerName, CustomerAddress

69
 Functional Dependencies
Full dependency exists when nonkey attribute is
functionally dependent on the entire primary key

Partial functional dependency exists when a

nonkey attribute is functionally dependent on part
(but not all) of the primary key

Transitive dependency is between primary key

and one or more non-key attributes that are
dependent on the primary key via another nonkey
attribute

70
Figure 4-27 Functional dependency diagram for INVOICE

There are partial dependencies.

Therefore, NOT in 2nd Normal Form

71
Figure 4-28 Removing partial dependencies

Getting it into
Second Normal
Form
Partial dependencies are removed, but there
are still transitive dependencies

72
 Third Normal Form
2NF PLUS no transitive dependencies
(functional dependencies on non-primary-key attributes)
Note: This is called transitive, because primary key is a
determinant for another attribute, which in turn is a
determinant for a third
Solution:
Non-key determinant with transitive dependencies go into a new
table;
Non-key determinant becomes primary key in the new table and
stays as foreign key in the old table

73
Figure 4-29 Removing partial dependencies

Getting it
into Third
Normal Form

Transitive dependencies are removed

74
 Boyce-Codd Normal Form
3NF PLUS every determinant in the relation
is a candidate key
A non-key attribute should not be a determinant for a
key attribute
Solution: Two step process
First step: determinant that is not a candidate key becomes a
component of the primary key
Second step: Decompose the relation to eliminate any partial
functional dependencies

75
Figure B-1 Relation in 3NF, but not in BCNF
(a) Relation with sample data

(b) Functional dependencies in STUDENT ADVISOR

76
 Anomalies in this Table
Insertion–
if new advisor is assigned to Physics department information
about this advisor cannot be inserted until at least one student is
assigned to the new advisor
Deletion–
if student 789 withdraws from school, we lose the information
that Bach advises in Music

Update–
if in Physics advisor Hawking is replaced by Einstein this change
requires update in multiple records

There are multiple themes (entity types) in one relation.

77
Figure B-2 Converting a relation to BCNF relations
(a) Revised STUDENT ADVISOR relations (1NF)

(b) Two relations in BCNF

78
Figure B-2 Converting a relation to BCNF relations
(c) Relations with sample data

79
 Fourth Normal Form
BCNF PLUS multivalued dependencies are
removed.
Multivalued dependencies exist when there are at least
three attributes (e.g. A, B, and C) in a relation and for
each value of A there is a well defined set of values of B,
and a well defined set of values for C. However, the set of
values of B is independent of set C and vice versa.
Solution: Divide the relation into two new relations. Each
of the relations contains two attributes that have a
multivalued relationship in the original relation.

80
Figure B-4 Data with multivalued dependencies
(a) View of courses, instructors, and textbooks

81
Figure B-4 Data with multivalued dependencies
(b) Relation in BCNF

82
Figure B-5 Relation in 4NF

83
 Merging Relations
View Integration–Combining entities from multiple ER
models into common relations
Issues to watch out for when merging entities from
different ER models:
Synonyms–two or more attributes with different names
but same meaning
Homonyms–attributes with same name but different
meanings
Transitive dependencies–even if relations are in 3NF prior
to merging, they may not be after merging
Supertype/subtype relationships–may be hidden prior to
merging
84
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.

Copyright © 2011 Pearson Education, Inc. Publishing as

Prentice Hall

85