Database System Assignment: Relational Database Analysis and Report

This assignment solution addresses database system concepts, focusing on normalization, relational database implementation, and specific procedures. The solution includes the analysis of client and car entities, detailing functional dependencies for each, with ClientID and RegNo as primary keys, respectively. The relational database is implemented using MS Access, and the report highlights key learnings, such as the use of subreports and date functions like DateAdd() and Now(). The most challenging aspect of the assignment was setting constraints for car registration numbers using input masks. The solution demonstrates a comprehensive understanding of database design, implementation, and reporting, providing valuable insights into practical database management techniques. This assignment is a comprehensive look at database design and implementation, covering key topics such as normalization, relational databases, and MS Access implementation, offering practical insights and solutions.

Running head: DATABASE SYSTEM
Database System
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM
1.Normalization
a. The list of relations for the ASS Database are provided below:
Client (ClientID (primary key), FullName, Address, Phone, Level)
Car (RegNo (primary key), Model, Maker, Color, ProducedYear, CarPrice, WarrantyPeriod)
PartUsed (PartID (primary key), JobNo (foreign key), Quantity, PartCost)
Service (JobNo (primary key), JobType, Description, StartTime, FinishTime, LabourCost)
Employee (EmployeeID (primary key), FullName, Address, Phone, EmployeeType, Qualification,
Experience)
b. Functional dependencies
The entities that are to be used for description in this report are client and Car
The Functional dependencies for Client is provided below:

2
DATABASE SYSTEM
ClientID FullName, Address, Phone, Level
The client table has ClientID as the primary key and all the information regarding the clients can
be acquired from the client table by using the ClientID. Hence the 3NF normalization for the relation is
also maintained efficiently. The FullName, address, Phone and the Level express the transitive
dependency for the table and also the data redundancy and the data duplication issues can also be resolved
easily by the table.
The Functional Dependencies for the Car table is provided below:
RegNo Model, Maker, Color, ProducedYear, CarPrice, WarrantyPeriod
The Car table has RegNo as the primary key and all the information regarding the clients can be
acquired from the client table by using the RegNo. Hence the 3NF normalization for the relation is also
maintained efficiently. The Model, Maker, Color, ProducedYear, CarPrice and WarrantyPeriod express
the transitive dependency for the table and also the data redundancy and the data duplication issues can
also be resolved easily by the table.
2.Relational Database Implementation
The relational database would be implemented in MS Access.
3.Interesting procedure that were learnt during the course of this assignment
a. One of the very interesting concept about the database that was learned during the course of this
assignment was that of the report and the sub report. It is very efficient to include a sub report within a
repost that provides the information on various type of data for the assignment.

3
DATABASE SYSTEM
b. Secondly the concept of date was also learned during the course of this assignment. The DateAdd()
method and the Date() Methods were very efficient for obtaining different types of outcomes for the data
fetching requirements of this assignment. In addition to this, the current data fetching process Now() was
also very efficient and also allowed the queries to obtain the current date and time of the machine.
4.The most complex task in the assignment
The most complex task in this assignment was setting the constrains for the registration number
of the cars for the clients. This procedure involves the use of input mask that was relatively new and did
consume a lot of time before implementation. Hence, this is thought to be the most complex process in
the whole assignment.