Database System Assignment - Database Concepts and Implementation

This assignment solution delves into database system concepts, focusing on normalization, relational database implementation, and key procedures. It begins with an overview of the ASS database, including its relations (Client, Car, Complaint, Employee, EmployeeType, LabourWork, PartType, PartUsed, and Service), and then details the functional dependencies for the Client and Car tables, ensuring 3NF normalization. The solution suggests implementing the relational database in MS Access. The assignment also highlights interesting procedures learned, such as value masking for setting constraints and the use of DateAdd(), Date(), and Now() methods for querying. The most complex task involved setting up a subreport to calculate the average total service cost for a car. The document concludes with a bibliography citing relevant database management system resources.

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, State, Street, City PostalCode, Phone, Level)
Car (RegNo (primary key), Model, Maker, Color, ProducedYear, CarPrice, WarrantyPeriod, ClientID)
Complaint (ComplaintID (primary key),, JobNumber, Reason, Lodgeddate, Outcome)
Employee (EmployeeID (primary key), FullName, Street, City, State, PostalCode, Phone,
EmployeeType)
EmployeeType (EmployeeType (primary key), Type, Desc)
LabourWork (EmpleyeeID, JobNO, StartTime, FnishTime, LabourCost)
PartType (PartID, PartDescription, PricePerUnit, PartUsed)
PartUsed (PartID (primary key), JobNo (foreign key), QuantityUsed, PartCost)
Service (JobNo (primary key), Description, ServiceDate, TotalCost, RegNO (forgienKey),
ReplacementRequired)
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:
ClientID FullName, State, Street, City PostalCode, 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, State, Street, City PostalCode, Phone and the Level express

2
DATABASE SYSTEM
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. In this assignment the concept was value masking was also learnt. The value masking was required for
setting up the constrains in the system. This was used in the case of the registration number of the car.
Specific constrains for the car was mentioned in this assignment and hence the value masking provided
ample scope to describe the constrains aptly.
b. During the course of this assignment the concepts of Date methods were learnt in this report. The
DateAdd() method and the Date() Methods were used in this assignment for fetching the required queries.
The assignment had the use of the Date() method in the query number 6 and the method was very
efficient in obtaining the data as per the requirement of the assignment. The Now() function available in

3
DATABASE SYSTEM
the system provides the current date and tie for the system. This function was also of great use during the
course of the assignment.
4.The most complex task in the assignment
The most complex task for the assignment was the setting up the sub report in report. There was a
requirement of setting up of an average of the total cost for the services for a car. The average function
was not available in the single report and function of average was implemented in the sub report and the
sub report was included within the main report.

4
DATABASE SYSTEM
Bibliography
Gouhar, A., 2017. Database Management System. International Journal of Engineering Science, 11766.
Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly available and
scalable database management system. FernUniversität, Fakultät für Mathematik und Informatik.