Database Design and Development: Normalization, Relations & Report

This document provides a comprehensive overview of database design and development, focusing on normalization and relational database concepts. It includes a set of relations for a vehicle category, location, car, employee, customer, corporate customers, individual customers, rental, and booking system. Functional dependencies between attributes are identified, and a 3NF demonstration is provided for Customers and Location tables. The report also reflects on the implementation process, highlighting challenges faced during constraint creation. The document concludes with a bibliography of relevant academic resources. Desklib offers a wealth of similar assignments and study tools to aid students in their academic pursuits.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database Design and Development
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN AND DEVELOPMENT
Normalization
Set of Relations
Vehicle Category (CategoryID (PK), CategoryName, DailyHireRate)
Location (LoctionID (PK), LocationName, Address, isMainOffice)
Car (RegNo (PK), CategoryID (FK) Make, Model, ManufactureYear, Colour, LocationID (FK),
NumberOfSeats)
Employee (EmployeeID (PK), FullName, BirthDate, Phone, SkillLevel, AnnualSalary,
LocationID (FK))
Customer (CustomerID (PK), FullName, BirthDate, Address, LicenseNo, CustomerType)
Corcatrporate Customers (ABN (PK), CustomerID (FK), OrganizationName, TravelSpecialDeal,
CustomerID (FK))
Individual Customers (IndividualID (PK), Insurance, CustomerID (FK))
Rental (RentalID(PK), EmployeeID (FK), RegNo (FK), PickUpLocation, DropOffLocation,
StartDate, EndDate, CustomerID (FK), RentalCost)
Booking (ReservationID (PK), Description, BookingDate, CustomerID (FK), RegNo (FK),
EmployeeID (FK))
Functional Dependencies
Attribute Dependencies
CategoryID CategoryName, DailyHireRate
RegNo CategoryID, Make, Model, ManufactureYear, Colour, LocationID,
NumberOfSeats
EmployeeID FullName, BirthDate, Phone, SkillLevel, AnnuaSalary, LocationID
LoctionID LocationName, Address, isMainOffice

2
DATABASE DESIGN AND DEVELOPMENT
CustomerID FullName, BirthDate, Address, LicenseNo, CustomerType
ABN CustomerID, OrganizationName, TravelSpecialDeal
IndividualID Insurance, CustomerID
RentalID EmployeeID, RegNo, PickUpLocation, DropOffLocation,
StartDate, EndDate, CustomerID, RentalCost
ReservationID Description, BookingDate, CustomerID, RegNo, EmployeeID
3 NF Demonstration
The Customers and the Location table has been selected here for the demostration of the
normalization of the and justificatgion for the same has been provided below:
Customers (CustomerID (PK), FullName, BirthDate, Address, LicenseNo, CustomerType)
i. CustomerID is used here for the identification of the Customers in the database and there
is only a single FullName per CustomerID. Other records in the table are not unique and
hence there are no that are repeating and hence, this a 1 NF relation.
ii. The Customer table also has a primary key that is the single candidate key if the table and
no other keys provide the unique value like the CustomerID key. The CustomerID
determines functionally all the other attributes of the tuple. Hence, the relation upgrades
to 2NF.
iii. There are no other fields in the table that can functionally determine the values in the
table. Hence, there are no existence of transitive dependency within the table and hence
relation can be defined as 3 NF.
Location (LoctionID (FK), LocationName, Address, isMainOffice)

3
DATABASE DESIGN AND DEVELOPMENT
i. Firstly, the LocationID is used for defining the other attributed of the table and it is only
the single primary key of the table. It is a unique value and can-not be duplicated. Hence
it provides the 1 NF properties.
ii. The primary key LocationID is the only candidate key in the table and provides the
unique feature to the able. All the other values do not resist the duplicity and hence, it is
the single attribute that provides the functional dependencies to the table. Hence, the
relation is in 2NF.
iii. No other field in the table provides the option of functional dependencies and hence, the
transitive dependencies are not present and hence the relation is in the third normal form.
Implementation Report
During the implementation of the database there were various type of interesting topics
that were learnt by me and them the creation of the report and the processes that involved the
creation of the integrities in the database deserves special mentions.
In addition to this it should also be noted that I had faced a lot of difficulty in creating the
constrains of the regNo field in the Car table of the Microsoft Access Database table.

4
DATABASE DESIGN AND DEVELOPMENT
Bibliography
Aref, M., ten Cate, B., Green, T.J., Kimelfeld, B., Olteanu, D., Pasalic, E., Veldhuizen, T.L. and
Washburn, G., 2015, May. Design and implementation of the LogicBlox system. In Proceedings
of the 2015 ACM SIGMOD International Conference on Management of Data (pp. 1371-1382).
ACM.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on
Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision
Mechanics, Optics & Mechatronics, (49), p.74.
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle tracking
system using GPS/GSM/GPRS technology and smartphone application. In Internet of Things
(WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Liu, C., Zhang, L. and Pan, Q., 2016. Database Design and Implementation of Natural Disaster
Monitoring System. Journal of Residuals Science & Technology, 13(5).