– Part III
19
Chapter 14 Normalization
Review Questions
14.1 Describe the purpose of normalizing data.
The purpose of normalization is to identify a suitable set of relations that support the data
requirements of an enterprise. The characteristics of a suitable set of relations include the
following:
14.2 Discuss the alternative ways that normalization can be used to support database design.
Normalization is a formal technique that can be used at any stage of database design. However
in this section we highlight two main approaches for using normalization as illustrated in
14.3 Describe the types of update anomalies that may occur on a relation that has redundant data.
A major aim of relational database design is to group attributes into relations so as to minimize
20
14.4 Describe the concept of functional dependency.
Functional dependency describes the relationship between attributes in a relation. For example, if
A and B are attributes of relation R, B is functionally dependent on A (denoted A B), if each
14.5 What are the main characteristics of functional dependencies that are used when normalizing
a relation?
In summary, the functional dependencies that we use in normalization have the following
characteristics:
14.6 Describe how a database designer typically identifies the set of functional dependencies
associated with a relation.
Identifying all functional dependencies between a set of attributes should be relatively simple
if the meaning of each attribute and the relationships between the attributes are well
14.7 Describe the characteristics of a table in Unnormalized Form (UNF) and describe how such a
table is converted to a First Normal Form (1NF) relation.
14.8 What is the minimal normal form that a relation must satisfy? Provide a definition for this
normal form.
– Part III
21
14.9 Describe the two approaches to converting an Unnormalized Normal Form (UNF) table to
First Normal Form (1NF) relation(s).
There are two common approaches to removing repeating groups from unnormalized tables:
(b) By placing the repeating data, along with a copy of the original key attribute(s), in a
separate relation. Sometimes the unnormalized table may contain more than one repeating
14.10 Describe the concept of full functional dependency and describe how this concept relates to
2NF. Provide an example to illustrate your answer.
14.11 Describe the concept of transitive dependency and describe how this concept relates to 3NF.
Provide an example to illustrate your answer.
14.12 Discuss how the definitions of 2NF and 3NF based on primary keys differ from the general
definitions of 2NF and 3NF. Provide an example to illustrate your answer.
The above definitions for second (2NF) and third normal form (3NF) disallow partial or
transitive dependencies on the primary key of relations to avoid update anomalies. However,
– Part III
22
Exercises
14.13 Continue the process of normalizing the Client and PropertyRentalOwner 1NF relations shown
in Figure 14.13 to 3NF relations. At the end of this process check that the resultant 3NF
relations are the same as those produce from the alternative ClientRental 1NF relation shown
in Figure 14.16.
14.14 Examine the Patient Medication Form for the Wellmeadows Hospital case study shown in
Figure 14.18.
See Figure 14.18.
(a) Identify the functional dependencies represented by the data shown in the form in
Figure 14.18.
patientNo fullName
– Part III
23
(b) Describe and illustrate the process of normalizing the data shown in Figure 14.18 to
First (1NF), Second (2NF), and Third (3NF).
First Normal Form
patientNo, drugNo, startDate, fullName, wardNo, wardName, bedNo, name,
(c) Identify the primary, alternate, and foreign keys in your 3NF relations.
patientNo (FK), drugNo(FK), startDate, wardNo(FK), bedNo, unitsPerDay, finish
14.15 The table shown in Figure 14.19 lists dentist/patient appointment data. A patient is given an
appointment at a specific time and date with a dentist located at a particular surgery. On each
day of patient appointments, a dentist is allocated to a specific surgery for that day.
See Figure 14.19.
(a) The table shown in Figure 14.19 is susceptible to update anomalies. Provide
examples of insertion, deletion, and update anomalies.
– Part III
(b) Describe and illustrate the process of normalizing the table shown in Figure 14.19 to
3NF. State any assumptions you make about the data shown in this table.
PK
3NF
staffNo
aDate
aTime
dentistName
patNo
patName
PK
fd2
fd4
fd1
fd3
fd5
1NF
surgeryNo
staffNo
aDate
surgeryNo
staffNo
dentistName
staffNo
aDate
aTime
patNo
patName
FK
AK
staffNo
dentistName
fd2
No
aDate
fd4
surgeryNo
FK
patNo
patName
PK
staffNo
aDate
aTime
fd1
FK
PK
patNo
fd5
25
14.16 An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland.
The table shown in Figure 14.20 lists the time spent by agency staff working at various hotels.
The National Insurance Number (NIN) is unique for every member of staff.
See Figure 14.20.
(a) The table shown in Figure 14.20 is susceptible to update anomalies. Provide
examples of insertion, deletion, and update anomalies.
(b) Describe and illustrate the process of normalizing the table shown in Figure 14.20 to
3NF. State any assumptions you make about the data shown in this table.
– Part III
26
NIN
contractNo
hours
eName
hNo
hLoc
NIN
contractNo
hours
NIN
contractNo
hours
contractNo
hNo
PK
PK
FK
PK
1NF
contractNo
hNo
hLoc
FK
FK
27
14.17 A company called FastCabs provides a taxi service to clients. The table shown in Figure 14.21
displays some details of client bookings for taxis. Assume that a taxi driver is assigned to a
single taxi but a taxi can be assigned to one or more drivers.
See Figure 14.21.
(a) Identify the functional dependencies that exist between the columns of the table in Figure
14.18 Applying normalisation to 3NF on the table shown in Figure 14.21 results in the formation of
the three 3NF tables shown in Figure 14.22.
See Figure 14.22.
(a) Identify the functional dependencies that exist between the columns of each table in Figure
14.19 Student can lease university flats and some of the details of leases held by students for places
in university flats are shown in Figure 14.23. A place number (placeNo) uniquely identifies
each single room in all flats and is used when leasing a room to a student.
See Figure 14.23.
(a) Identify the functional dependencies that exist between the columns of the table in Figure
14.20 Applying normalisation to 3NF on the table shown in Figure 14.23 results in the formation of
the four tables shown in Figure 14.24.
(a) Identify the functional dependencies that exist between the columns of each table in Figure
– Part III
28