Database Design and Development 1: Normalization and Implementation

This report presents a database design and development solution, focusing on normalization and relational database implementation. It begins with a detailed explanation of normalization, transforming tables to 3NF using a sample ASS database. The report then describes the implementation of a relational database using Microsoft Access. It highlights the application of functional dependencies, the removal of transitive dependencies, and the challenges faced, particularly in setting constraints within the car table and linking it to the customer table. The report also discusses the learning experiences, including the preparation of reports and the use of date-related functions. The bibliography includes relevant database systems and design resources. The report provides a comprehensive overview of database design principles and practical implementation.

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
Table of Contents
1. Normalization............................................................................................................................................2
2. Implementation of Relational Database.....................................................................................................4
3. Short Implementation report......................................................................................................................4
4.The most complex task in the assignment..................................................................................................5
Bibliography..................................................................................................................................................6

2
DATABASE DESIGN AND DEVELOPMENT
1. Normalization
a. The sample solution of ASS database is used for mapping the ERD into a set of relations into 3 NF.
The table Employee
EmployeeID PK Full
Name
Street City State postCode Phone EmployeeType
For making it in 3 NF the following new table is created
Employee (EmployeeID PK, Full Name, Address (Street, City, State, postCode), Phone, Type,
Noof ExperienceYears, Certificate Level)
ClientID PK Full
Name
Street City State postCode Phone IsSenior
The client table in already generalized and thus no changes are made in the client table
Client (ClientID PK, Full Name, Address (Street, City, State), Phone, Seniority Level)
The car table given in the example is
RegnNbr PK Make Model Colou
r
Produce
d Year
Price Warrant
y Due
date
Purchasedfrom
ASS
To make it in 3NF the following table is created
Car (RegNo PK, Make, Model, Colour, Manufacturing Year, Price, WarrantyDue Date,
PurchasedFromASS)

3
DATABASE DESIGN AND DEVELOPMENT
The service table given in the example is
JobNbr
PK
JobDescriptio
n
ServiceDat
e
TotalCos
t
The service table after normalization
Service (JobNbr PK, JobDescription, Service Date, TotalCost, StartTime, FinishTime,
LabourCost)
Parts table before normalization
PartID
PK
PartDescriptio
n
PricePerUni
t
The parts table after normalization
PartUsed (PartID PK, JobNbr FK, QuantityUsed, PartCost)

4
DATABASE DESIGN AND DEVELOPMENT
b. The car and the client is selected from the relation and the functional dependency that exist with the
relation are given below:
Client (ClientID PK, Full Name, Address (Street, City, State), Phone, Seniority Level)
Client has the following functional dependency and is given below
ClientID FullName, Address, Phone, Level
For the client table the client ID is the primary key and the transitive dependency are removed for
normalizing the table into 3NF. The removal of the transitive dependency does not affect the database and
still it can be joined to get the information about the client using the client ID. The risk of redundancy
during the insertion of data in the table is removed my making the table into third normal form and
removal of the dependency.
Car (RegNo PK, Make, Model, Colour, Manufacturing Year, Price, WarrantyDue Date,
PurchasedFromASS)
The car table is created and the functional dependency of the car table is demonstrated below:
RegNo Model, Maker, Color, ProducedYear, CarPrice, WarrantyPeriod
The table contains the registration number of the car and the model, manufactured year, car price,
WarrantyDue, PurchasedFromASS. The transitive dependency is removed from the table and normalized
into 3NF for reduction of the redundancy of data and resolve the duplicate entry issues raised during the
entry of data into the tables. The car table can be joined using the registration number and all the data
required can be fetched by joining the tables.
2. Implementation of Relational Database
Microsoft Access is used for the development of the relational database of the system.

5
DATABASE DESIGN AND DEVELOPMENT
3. Short Implementation report
a. The two interesting things or procedures that were learnt during the advancement of the assignment is
the preparation of the report and the removal of the transitive dependency between the tables. It is
important to remove the transitive dependency between the tables for making the table in third normal
form.
b. The complexity faced during the development of the database is generalization of the table into 3NF
but the previous solution was deeply analyzed for the preparation of the preparation of the database. The
dateAdd and the Date methods was studied for the getting different outcomes from the database and the
use of now() function is also studied for getting an effective result of the querry and fetch the date and
time from the computer.
4.The most complex task in the assignment
The complexity of the assignment lies during the setting of the constraints in the car table i.e. the
registration number and linking it with the other table i.e the customer to get the appropriate outcome of
the querry. The complexity of the report was resolved by taking an input mask that is new and a lot of
time is spend for the implementation.

6
DATABASE DESIGN AND DEVELOPMENT
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage
Learning.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
Hsieh, W.C.Y., Lloyd, A. and Veach, E.H., Google Inc., 2017. Systems and methods of increasing
database access concurrency using granular timestamps. U.S. Patent 9,747,310.
Kinser, A.S., Hammerle, P.P., Kinser, E.P., Lending, D.P. and Nightingale-Massart, J.P., 2013. Your
Office: Microsoft Access 2013, Comprehensive. Prentice Hall Press.