Normalization

The sample solution of ASS database is used for mapping the ERD into a set of relations into 3 NF.

The table Employee

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)

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

To make it in 3NF the following table is created

Car (RegNo PK, Make, Model, Colour, Manufacturing Year, Price, WarrantyDue Date, PurchasedFromASS)

The service table given in the example is

The service table after normalization

Service (JobNbr PK, JobDescription, Service Date, TotalCost, StartTime, FinishTime, LabourCost)

Parts table before normalization

The parts table after normalization

PartUsed (PartID PK, JobNbr FK, QuantityUsed, PartCost)

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

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:

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.

Implementation of Relational Database

Microsoft Access is used for the development of the relational database of the system.

Short Implementation report

1. 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.
2. 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.

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.

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.