Guide IV
Chapters 14 – 15 Normalization
14.1 Explain the purpose of data normalization and describe the main steps in the normalization
process.
14.2 The table shown below displays the details of the roles played by actors/actresses in films.
filmNo fTitle dirNo director actorNo aName role timeOnScreen
F1100 Happy Days D101 Jim Alan A1020 Sheila Toner Jean Simson 15.45
D101 Jim Alan A1222 Peter Watt Tom Kinder 25.38
D101 Jim Alan A1020 Sheila Toner Silvia Simpson 22.56
F1109 Snake Bite D076 Sue Ramsay A1567 Steven McDonald Tim Rosey 19.56
D076 Sue Ramsay A1222 Peter Watt Archie Bold 10.44
(a) Describe why the table shown below is not in first normal form (1NF).
(b) The table shown above is susceptible to update anomalies. Provide examples of how
insertion, deletion, and modification anomalies could occur on this table.
(c) Identify the functional dependencies represented by the table shown above. State any
assumptions you make about the data shown in this table (if necessary).
(d) Using the functional dependencies identified in part (c), describe and illustrate the
process of normalization by converting the table shown in Figure 1 to Boyce Codd
Normal Form (BCNF). Identify the primary and foreign keys in your BCNF relations.
Guide IV
The process of normalization is shown in figure below.
3NF / BCNF
PK
Guide IV
(e) Sketch an Entity Relationship model for the data shown in table above.
14.3 Briefly describe how the techniques of normalization and Entity Relationship modeling can be
used to produce a set of relations with desirable properties.
14.4 Describe the purpose of normalizing data and identify the four most commonly used normal
forms.
The normalization process, as first proposed by Codd (1972a), takes a relational schema through a
series of tests to assess whether or not it belongs to a certain normal form. Normalizing data is the
Discuss how normal forms support a database designer.
14.5 The table below lists customer/car hire data. Each customer may hire cars from various
outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to
a customer on a given date.
carReg
make
model
custNo
custName
hireDate
outletNo
outletLoc
M565 0GD
Ford
Escort
C100
Smith, J
14/5/98
01
Bearsden
M565 0GD
Ford
Escort
C201
Hen, P
15/5/98
01
Bearsden
N734 TPR
Nissan
Sunny
C100
Smith, J
16/5/98
01
Bearsden
M134 BRP
Ford
Escort
C313
Blatt, O
14/5/98
02
Kelvinbridge
M134 BRP
Ford
Escort
C100
Smith, J
20/5/98
02
Kelvinbridge
M611 0PQ
Nissan
Sunny
C295
Pen, T
20/5/98
02
Kelvinbridge
(a) The data in the table is susceptible to update anomalies. Provide examples of how
insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State any
assumptions you make about the data.
(c) Using the functional dependencies identified in part (b), describe and illustrate the process of
normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the
primary and foreign keys in your 3NF relations.
Guide IV
PK
2NF
3NF / BCNF
1NF
PK
Guide IV
(d) Sketch an Entity Relationship model for the data shown in Table 1. Show all the entities,
relationships, and attributes.
Either of the following ER diagrams would be correct.
Car
Hire
14.6 Examine the table shown below. This table represents the hours worked per week for
temporary staff at each branch of a company.
staffNo branchNo
BranchAddress name position hoursPerWeek
S4555 B002 City Center Plaza, Seattle, WA 98122
Ellen Layman Assistant 16
S4555 B004 16 14th Avenue, Seattle, WA
98128
Ellen Layman Assistant 9
S4612 B002 City Center Plaza,Seattle, WA 98122 Dave Sinclair Assistant 14
S4612 B004 16 14th Avenue, Seattle, WA
98128
Dave Sinclair Assistant 10
(a) The table shown above is susceptible to update anomalies. Provide examples of how
insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State
any assumptions you make about the data (if necessary).
staffNo branchNo
branchAddress name position hoursPerWeek
S4555 B002 City Center Plaza, Seattle, WA 98122
Ellen Layman Assistant 16
(c) Using the functional dependencies identified in part (b), describe and illustrate the
process of normalization by converting Table 1 to Third Normal Form (3NF)
relations. Identify the primary and foreign keys in your 3NF relations.
Guide IV
Guide IV
Converting to 2NF
staffNo branchNo
BranchAddress name position hoursPerWeek
branchNo
branchAddress staffNo name position
Composite
primary key
Guide IV
The student should then describe the process of normalizing the table to 2NF by removing the
partial dependencies.
(d) Create an Entity Relationship (ER) model using the Unified Modeling Language
(UML) to represent the data shown in Figure 1. Your ER model should show all
entities, relationships, and attributes.
14.7 The table below lists customer/car hire data. Each customer may hire cars from various
outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to
a customer on a given date.
appNo date/time instructorID iFName iLName clientID cFName cLName cAddress
1001 25/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley
1102 29/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley
1203
30/07/00.11.00
I344
Tom
Jones
C034
Anne
Way 111 Storrie Road, Paisley
1334
2/08/00.13.00
I666
Karen
Black
C089
Mark
Fields 120 Lady Lane, Paisley
1455 2/08/00.13.00 I957 Steven Smith C019 John Brown 13 Renfrew Road, Paisley
1676 25/08/00.10.00 I344 Tom Jones C039 Karen Worth 34 High Street, Paisley
(a) The data in the table is susceptible to update anomalies. Provide examples of how
insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State
any assumptions you make about the data.
(c) Using the functional dependencies identified in part (b), describe and illustrate the
process of normalization by converting Table 1 to Third Normal Form (3NF) relations.
Identify the primary and foreign keys in your 3NF relations.
Guide IV
appNo date/time instructorID iFName iLName clientID cFName cLName cAddress
1001
25/07/00.10.00
I456
Jane
Watt
C034
Anne
Way
111 Storrie Road, Paisley
1102 29/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley
The primary key of the original relation is appointment number (appNo) and the relation is
already in 2NF. The original relation also has two alternate keys instructorID, dateTime and
clientID, dateTime. The functional dependencies fd2 to fd5 represent transitive dependencies
in the original relation and must be removed to create the following relations in 3NF.
Appointment (appNo, dateTime, instructorID, clientID)
Primary Key appNo
fd1
1334
2/08/00.13.00
I666
Karen
Black
C089
Mark
Fields
120 Lady Lane, Paisley
1676
25/08/00.10.00
I344
Tom
Jones
C039
Karen
Worth
34 High Street, Paisley
14.8 The table below lists customer/car hire data. Each customer may hire cars from various
outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to a
customer on a given date.
jobID pickupDateTime
driverID
dFName dLName clientID cFName cLName cAddress
1001
25/07/00.10.00
I456
Jane
Watt
C034
Anne
Way
111 Storrie Road, Paisley
1102 29/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley
1203 30/07/00.11.00 I344 Tom Jones C034 Anne Way 111 Storrie Road, Paisley
1334
2/08/00.13.00
I666
Karen
Black
C089
Mark
Fields
120 Lady Lane, Paisley
1455
2/08/00.13.00
I957
Steven
Smith
C019
John
Brown
13 Renfrew Road, Paisley
1676 25/08/00.10.00 I344 Tom Jones C039 Karen Worth 34 High Street, Paisley
(a) The data in the table is susceptible to update anomalies. Provide examples of how
insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State
any assumptions you make about the data.
(c) Using the functional dependencies identified in part (b), describe and illustrate the
process of normalization by converting Table 1 to Third Normal Form (3NF) relations.
Identify the primary and foreign keys in your 3NF relations.
Guide IV
jobID pickupDateTime driverID
dFName dLName clientID cFName cLName cAddress
1001 25/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley
PK
FK FK
PK
PK
14.9 The table below provides osme sample data for an agency called Hotel Services supplies part-
time/temporary staff to hotels within Strathclyde region. The relation in Figure 2 lists the
number of hours worked by each staff at various hotels. The relation is first normal form
(1NF). Assuming that a contract is for one hotel only but a staff may work in more than one
hotel on different contracts, identify the functional dependencies represented by the data in
this relation.
Contracts
NIN contractNo hours eName hNo hLoc
1135 C1024 16 Smith, J. H25 East Kilbride
Functional dependencies
(NIN, contractNo) —-> {hours, eName, hNo, hLoc}
14.10 Given the following relation schema and its functional dependencies:
(a) Specify candidate keys and state the primary key.
(b) Assuming that the relation is in first normal form (1NF), describe and illustrate the
process of normalising the relation schema to second (2NF) and third (3NF) normal
forms. Identify the primary and foreign keys in your schemas.
A B C D E F G H
RR
fd1
fd2
fd3
fd4 fd5
Guide IV
– fd4, fd5 violate 3NF definition. Decompose
14.11 The table below represents data about employees of a company and the projects they work on.
An employee may work on one or more projects a certain number of hours
EmpProjects
projName empNo projBudget hours projManager managerDOB
Hardware E1 20000 20 Smith Jan 63
Hardware E3 20000 20 Smith Jan 63
Assuming that the functional dependencies in the relation in Figure 2 will hold for any
additional data, which of the following functional dependencies are true and which are false?
Justify your answer.
pk
RR
RR1 pk fk
pk
Guide IV
v) projName projManager true; One projManager for every ProjName
14.12 Given the following relational schema and its functional dependencies:
RentalInfo
(a) Specify candidate keys and state the primary key.
(b) Assuming that the relation is in first normal form (1NF), describe and illustrate the
process of normalising the relational schema to second (2NF) and third (3NF) normal
forms. Identify the primary and foreign keys in your third normal forms.
2NF: fd3 and fd54 violates the definition of 2NF. Decompose relation.
fd1
fd5
custNo propertyNo custName pAddress rentStart ownerNo
OName
fd1
fd2
fd3
fd4
fd5
Guide IV
3NF: fd5 violates 3NF definition. Decompose relation.
14.13(a) Give a set of functional dependencies for the relational schema R(A, B, C, D) with
primary key AB under which R is in 1NF but not in 2NF.
(b) Give a set of functional dependencies for the relational schema R(A, B, C, D) with
primary key AB under which R is in 2NF but not in 3NF.
(c) Consider C. If A is a
candidate key of R, could R be in BCNF and, if so, under what conditions?
14.14 Consider a relational schema R(A, B, C, D, E) with the following functional
dependencies: A B, BC E, and ED A.
(a) List all keys of R.
(b) Is R in 3NF?
(c) Is R in BCNF?
14.15 Consider the following relational schemas and functional dependencies. Assume that the
relations have been produced from a relation ABCDEFGHI and that all known
dependencies for this relation are listed. State the strongest normal form for each one
and, if appropriate, decompose to BCNF.
pk pk fk
Guide IV
(a) R(A, B, C, D, E); A B, C D
(b) S(A, B, F); AC E, B F
(c) T(A, D); D G, G H
(d) U(C, D, H, G); A I, I A
(e) V(A,C, E, I)
14.16 Consider the relational schema R(A, B, C, D). For each of the following functional
dependencies identify the candidate key(s) for R and state its strongest normal form. If
appropriate, decompose to BCNF.
(a) B C, C A, C D
(b) B C, D A
(c) ABC D, D A
(d) A B, A C, BC D
(e) AB C, AB D, C A, D B
(a) D violate