Database Systems Assignment: Normalization, ERD, and Schema Design

This database systems assignment solution focuses on normalizing a given table through step-by-step decompositions to achieve the third normal form (3NF). It begins with the initial table and progresses through the first and second normal forms. The solution then presents the final schema design, including tables for Hire, Driver, Cab, Client, and Address, with appropriate primary and foreign keys. A Crow's Foot Entity-Relationship Diagram (ERD) visually represents the relationships between these entities. The assignment concludes with a bibliography of relevant database systems textbooks. The solution demonstrates a practical understanding of database design principles, including normalization and ERD creation, crucial for organizing and managing data effectively.

Running head: DATABASE SYSTEMS
DATABASE SYSTEMS
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEMS
Dependency Diagram
The given table is provided below:
Clien
t ID
Client
Name
Cab Hire Time Ca
b
ID
Cab Details Pickup
Address
Driver
Name
Driver's
License
Number
124 Shaun 5/09/201914:20 26 Subaru
lmpreza
2015
6 Kambara
Or, Mulgrave
Jeffrey
Etheridg
e
92476531
9
124 Shaun 7/09/201911:00 11 Toyota
Camry
2007
14
Gainsboroug
h Ave,
Wheelers
Hill
Sam
Harper
74087247
3
124 Shaun 12/09/201916:4
5
18 Honda
Civic 2009
6 Kambara
Or, Mulgrave
Sam
Harper
74087247
3
345 Patric
k
6/09/2019 20:10 34 Ford
Falcon
2010
52
Brunswick
Rd, Mitcham
Sanjay
Ugle
84378512
4
345 Patric
k
6/09/2019 22:30 34 Ford
Falcon
2010
8 Columbia
AveJ
C.roydon
Jeffrey
Etheridg
e
92476531
9
209 Sarah 3/09/2019 09:00 11 Toyota 21 Devon St, Vipul 72359620

2
DATABASE SYSTEMS
Camry
2007
Chaltenham Jad 1
209 Sarah 12/09/201913:1
5
34 Ford
Falcon
2010
21 Devon St,
Chaltenham
lshan
Mishra
52108724
3
209 Sarah 15/09/201918:0
5
29 Holden
Commodor
e 2012
21 Devon St,
Chaltenham
Sam
Harper
74087247
3
The above table has been developed in order to take into account the duplicities in the
data. The dependency diagram from the table developed above has been provided below:
Step by step decompositions
Step 1: first normal form
Hire (clientID (pk), ClientName, CabHireTime, CabID, CabDetails, PickUpAddress,
DriverName, DriverLicenseNumber)
Step 2: second normal form

3
DATABASE SYSTEMS
Hire (clientID (pk), ClientName, CabHireTime, CabID, PickUpAddress, DriverName (fk))
Driver (DriverName (pk), DriverLicenseNumber)
Cab (CabID (pk), CabDetails)
Step 3: third normal form

4
DATABASE SYSTEMS
Hire (HireID (pk), CabHireTime, CabID (fk), DriverID (fk), AddressID (pk))
Driver (DriverID (pk), DriverName, DriverLicenseNumber)
Cab (CabID (pk), CabDetails)
Client (ClientID (pk), ClientName)
Address (AddressID (pk), ClientID (fk), PickUpAddress)
Final schema design
Hire (HireID (pk), CabHireTime, CabID (fk), DriverID (fk), AddressID (pk))

5
DATABASE SYSTEMS
Driver (DriverID (pk), DriverName, DriverLicenseNumber)
Cab (CabID (pk), CabDetails)
Client (ClientID (pk), ClientName)
Address (AddressID (pk), ClientID (fk), PickUpAddress)
Crows Foot ERD

6
DATABASE SYSTEMS
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.