Database Normalization: Functional Dependency and Third Normal Form

This assignment provides a detailed analysis of database normalization, focusing on functional dependencies and the transformation of a database table into the third normal form (3NF). It begins by identifying functional, partial, and transitive dependencies within a sample database table, determining appropriate primary keys for the job, driver, and client entities. The analysis includes a step-by-step breakdown of how to identify jobID, driverID, and clientID as primary keys based on their unique identification of rows and the dependencies of other attributes. The assignment then demonstrates the process of converting a non-normalized table into 3NF by eliminating partial and transitive dependencies, resulting in a set of normalized tables for jobs, drivers, and clients. Desklib offers a wide range of solved assignments and study resources to aid students in understanding complex topics.

Running head: DATABASE NORMALIZATION
Database Normalization
Name of the Student
Name of the University
Author’s note:

1DATABASE NORMALIZATION
JobI
D
JobDateTi
me
driverI
D
driverNa
me
taxiI
D
clientI
D
clientNa
me
jobPickUpAddr
ess
1 25/07/14
10.00
D1 Joe Bull T1 C1 Anne
Woo
1 Stporrie RD,
Paisley
2 29/07/14
10.00
D1 Joe Bull T1 C1 Anne
Woo
1 Stporrie RD,
Paisley
3 30/07/14
11.00
D2 Tom Win T2 C1 Anne
Woo
3 High Street,
Paisley
4 02/08/14
13.00
D3 Jim Jones T3 C2 Mark Tin 1A Lady Lane,
Paisley
5 02/08/14
13.00
D4 Steven
Win
T1 C3 John Seal 22 Red Road,
Paisley
6 25/08/14
13.00
D2 Tom Win T2 C4 Karen
Bow
17 High Street,
Paisley
a) Functional Dependency:
The functional dependency is considered as the relationship among attributes of a
database entity where one attribute of the entity can uniquely define another attribute. The

2DATABASE NORMALIZATION
functional dependency serves as a constraint among two attributes in a database server. In the
above database table there are eight attributes in the table.
The above entity has fully functional dependency, partial dependency and transitive
dependency. The jobDateTime is partially dependent on the jobID. On the other hand, the
driverName is partially dependent on the dervierID. The client name dependents on the clientID.
This means that the primary keys should be jobID, driverID and clientID.
jobID  JobDateTime, driverID, driverName, taxiID, clientID, clientName,
jobPickUpAddress
driverID  driverName
clientID  clientName
driverID  taxiID
The transitive dependency driverID  taxiID exists on jobID through driverID. This can
also be stated as the jobID attribute is functionally determining the taxiID through the driverID
attribute. Moreover, neither the driverID nor ythe taxiID functionally determines the jobID.
jobID  JobDateTime, driverID, driverName, taxiID, clientID, clientName,
jobPickUpAddress
driverID  driverName
clientID  clientName
jobID, driverID  taxiID
jobID, clientID jobPickUpAddress

3DATABASE NORMALIZATION
In the above section, the five functional dependencies has been shown. The fully
functional dependencies are shown in the following section.
jobID, jobDateTime  driverID
jobID, jobDateTime, jobPickUpAddress  clientID
It can be stated that every value of the jobID and jobDateTime is associated with the
single value of driverID. On the other hand, the jobID, jobDateTime, jobPickUpAddress is
associated with the clientID. However, none of relation is eligible for fully functional
dependency as driverID and clientID is dependent on the subset of the collection of attributes
which is jobID. Therefore, the fully functional dependencies are as following.
jobID  driverID
jobID  clientID
b) Selection of Primary Key:
As per the analysis of the FastCabs database, the jobID is the primary key of the job
table. This is because, the driverID, clientID, jobDateTime, jobPickUpLocation are dependent on
this attribute. As the jobID will be unique for each row, there will be no alternate keys in the job
table.
On the other hand, the driver name and taxiID is dependent on the driverID. The driverID
can be used for defining all the rows uniquely. This makes the driverID primary key of the driver
table. The client name is only dependent on the clientID. Like the driver table, the clientID can
be used for uniquely identifying all the rows of client table uniquely.

4DATABASE NORMALIZATION
Therefore the primary keys of the database tables has been shown through the following
table structure.
JobI
D
JobDateTim
e
driverI
D
clientI
D
jobPickUpAddres
s
1 25/07/14
10.00
D1 C1 1 Stporrie RD,
Paisley
2 29/07/14
10.00
D1 C1 1 Stporrie RD,
Paisley
3 30/07/14
11.00
D2 C1 3 High Street,
Paisley
4 02/08/14
13.00
D3 C2 1A Lady Lane,
Paisley
5 02/08/14
13.00
D4 C3 22 Red Road,
Paisley
6 25/08/14
13.00
D2 C4 17 High Street,
Paisley
driverID driverName taxiID
D1 Joe Bull T1
D2 Tom Win T2

5DATABASE NORMALIZATION
D3 Jim Jones T3
D4 Steven Win T1
clientI
D
clientNam
e
C1 Anne Woo
C2 Mark Tin
C3 John Seal
C4 Karen Bow
The above tables shows that the jobId, driverID and clientID is perfect primary keys for
the entities. As per the data in the job table, all the non-key attributes are dependent on the jobID.
Same way, the driverID and clientID is accurately defining each row uniquely.
c) 3rd Normal Form:
JobI
D
JobDateTi
me
driverI
D
driverNa
me
taxiI
D
clientI
D
clientNa
me
jobPickUpAddr
ess
1 25/07/14
10.00
D1 Joe Bull T1 C1 Anne
Woo
1 Stporrie RD,
Paisley

6DATABASE NORMALIZATION
2 29/07/14
10.00
D1 Joe Bull T1 C1 Anne
Woo
1 Stporrie RD,
Paisley
3 30/07/14
11.00
D2 Tom Win T2 C1 Anne
Woo
3 High Street,
Paisley
4 02/08/14
13.00
D3 Jim Jones T3 C2 Mark Tin 1A Lady Lane,
Paisley
5 02/08/14
13.00
D4 Steven
Win
T1 C3 John Seal 22 Red Road,
Paisley
6 25/08/14
13.00
D2 Tom Win T2 C4 Karen
Bow
17 High Street,
Paisley
The above table is not in the 3rd normal form. To be in the third normal form, a table
must be in 1st and 2nd normal form respectively. The table is in the first normal form. It is
because, each attribute of the table has atomic values. However, the table is not in the second
normal form as there is partial dependency among the non-key attributes. The transitive
dependency is also present in the tables as shown in the functional dependency part. The final
3rd normal form tables are as following.
JobI
D
JobDateTim
e
driverI
D
clientI
D
jobPickUpAddres
s
1 25/07/14 D1 C1 1 Stporrie RD,

7DATABASE NORMALIZATION
10.00 Paisley
2 29/07/14
10.00
D1 C1 1 Stporrie RD,
Paisley
3 30/07/14
11.00
D2 C1 3 High Street,
Paisley
4 02/08/14
13.00
D3 C2 1A Lady Lane,
Paisley
5 02/08/14
13.00
D4 C3 22 Red Road,
Paisley
6 25/08/14
13.00
D2 C4 17 High Street,
Paisley
driverID driverName taxiID
D1 Joe Bull T1
D2 Tom Win T2
D3 Jim Jones T3
D4 Steven Win T1
clientI clientNam

8DATABASE NORMALIZATION
D e
C1 Anne Woo
C2 Mark Tin
C3 John Seal
C4 Karen Bow