Database Design and Development: Normalization and Implementation

This document presents a comprehensive solution to a database design and development assignment. The solution begins with the process of normalization, transforming the initial database structure into third normal form (3NF) to eliminate data redundancy and improve data integrity. The assignment details the creation of several tables, including Employee, Client, Car, Service, and Parts, and demonstrates the application of normalization principles to each. It explains how dependencies are removed and foreign keys are defined to ensure data consistency during table joins. The solution utilizes MS Access for relational database implementation, highlighting the techniques learned during the assignment, such as removing transitive dependencies and the use of SQL queries for data retrieval. The report also discusses the complexities involved in designing queries for fetching data from multiple tables and concludes with a bibliography of relevant resources. Desklib provides this assignment solution to help students understand and excel in database design and development.

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. Relational Database Implementation.........................................................................................................5
3. Short report for implementation.................................................................................................................5
4. Complexity in the assignment....................................................................................................................5
Bibliography..................................................................................................................................................7

2
DATABASE DESIGN AND DEVELOPMENT
1. Normalization
1.1. The specimen arrangement of ASS database is utilized for mapping the ERD into an arrangement of
relations into 3 NF.
The Employee table created is as follows:
EmployeeID PK Full
Name
Street City State postCode Phone EmployeeType
After normalization the table created is as follows:
Employee (EmployeeID PK, Full Name, Street, City, State, postCode, Phone, Type, Noof
ExperienceYears, Certificate Level)
The client table created is as follows:
ClientID PK Full
Name
Street City State postCode Phone IsSenior
The car table given created is as follows:
RegnNbr PK Make Model Colour Produced
Year
Price Warranty
Due date
Purchasedfrom
ASS
For normalization of the car table the dependencies are removed and the accompanying table is made
Car (RegNo PK, Make, Model, Colour, Manufacturing Year, Price, WarrantyDue Date,
PurchasedFromASS)
Service table

3
DATABASE DESIGN AND DEVELOPMENT
Job_Nb
r PK
Job_Descriptio
n
Service_Dat
e
Total_Cos
t
After normalization the created service table is given below
Service (Job_Nbr PK, Job_Description, Service_Date, Total_Cost, Start_Time, Finish_Time,
Labour_Cost)
The parts table given
Part_I
D
[PK]
Part_Descriptio
n
Price_Per_Un
it
The parts table after standardization
Part_Used (Part_ID PK, Job_Nbr [FK], Quantity_Used, Part_Cost)

4
DATABASE DESIGN AND DEVELOPMENT
1.2. The customer and the client is chosen from the connection and the practical reliance that exist with
the connection are given underneath:
Client (Client_ID [PK], Full_Name, Street, City, State, Phone, Seniority_Level)
Customer has the accompanying practical reliance and is given underneath
Client_ID Full_Name, Address, Phone, Level
The customer table is created for the removal of the transitive dependency and removal of the
dependency between the other tables. The database is required to be created such that it can respond to the
querry and it is required to be customized for removal of the transitive dependency of the table and make
the table in third normal form.
Car (ReNo [PK], Make, Model, Colour, Manufacturing_Year, Price, Warranty_Due Date,
Purchased_From_ASS)

5
DATABASE DESIGN AND DEVELOPMENT
The car table is made and the useful reliance of the car table is exhibited beneath:
RegNo Model, Maker, Color, ProducedYear, CarPrice, WarrantyPeriod
The car table created after normalization and the model number, maker of the car, colour,
manufactured year, price and warranty period of the car is included in the table for removal of the
dependency and get the result of the querry from the table without joining different tables. For the
removal of the transitive dependency the tables linked with the car tables are required to be analyzed and
the new table is required to be created.
2. Relational Database Implementation
MS Access is utilized for the advancement of the database of the framework.
3. Short report for implementation
3.1. The techniques that were learnt during the commencement of the task for the development of the
database and removing the transitive dependency between the previous tables for making it in third
normal form. The transitive dependency is required to be removed and foreign key is required to be
defined such that no data is lost during joining the table.
3.2. During the course of the assignment the use of the Microsoft access was learned and its use in
different field of software development was also learnt. The different features of Microsoft Access such
as the export and import of data and creating querry, viewing and editing of the report was also learnt
completion of the assignment. The use Add date and the time now function for filtering the querry was
analyzed and implemented in the database for creating the querry.
4. Complexity in the assignment
Microsoft Access is simple and easy to use with a GUI that helps to manage the applications and
the database engine to import or directly link it with different application or databases. But there are

6
DATABASE DESIGN AND DEVELOPMENT
different complexity regarding the unpredictability of the designing a querry for fetching the data from
different tables by joining them for getting the appropriate result.