Database Implementation Report: AAS Database Management System

This report provides a comprehensive overview of the AAS (presumably, an auto-service system) database management system. It details the database normalization process, mapping an ER diagram to the Third Normal Form (3NF) and explaining functional dependencies. The report includes the structure of the Client and PartType tables, emphasizing primary keys for data integrity. Furthermore, it showcases several SQL queries used to retrieve and manipulate data, covering topics such as joining tables, filtering data using WHERE clauses, ordering results, using aggregate functions (COUNT), and date-based filtering. A summary highlights the system's job services information and acknowledges limitations like the absence of database forms and outer joins. The report concludes with a reflection on the learning outcomes, including data types, subqueries, and inner joins, along with references to relevant resources.

Student ID –
Date -
Module Tutor -
1 | P a g e

Implementation Report
Introduction
The AAS database management is being shown in the report. The complete detail
along with database normalization is being shown in the report.
Normalization
Mapping of ER Diagram to Third Normal Form
Functional Dependency
Client
Client (FFClientID, FFClientName, FFStreet, FFCity, FFState, FFPostcode, FFPhone,
FFIsSenior)
The detail of different clients may be same. In that situation, to find a particular client,
a specific primary key is required. Client ID will work as a primary key for the Client
table.
FFClientName  FFClientID
FFState  FFClientID
2 | P a g e

PartType
PartType (FFPartID, FFPartDescription, FFPricePerUnit)
The detail of different parts may be same. In that situation, to find a particular part, a
specific primary key is required. Part ID will work as a primary key for the PartType
table.
FFPricePerUnit  FFPartID
Normalisation Demonstration
Tables below are satisfying the rules of 3 NF-
Client (FFClientID, FFClientName, FFStreet, FFCity, FFState, FFPostcode, FFPhone,
FFIsSenior)
PartType (FFPartID, FFPartDescription, FFPricePerUnit)
- Each table is having separate identity.
- Each table have primary key.
- No transitive dependency.
(H. Bdr 2013)
(teratrax.com n.d.)
3 | P a g e

SQL Queries
Query 1
SELECT FJobNbr, FJobDescription, FServiceDate, FTotalCost, Service.FRegoNbr,
FMake, FModel, FProducedYear FROM Service INNER JOIN Car ON
Service.FRegoNbr=Car.FRegoNbr WHERE FJobNbr not in (Select FJobNbr from
PartUsed);
Query 2
SELECT FEmployeeName, FPhone, FLabourCost FROM Employee INNER JOIN
LabourWork ON Employee.FEmployeeID=LabourWork.FEmployeeID WHERE
FLabourCost>300 ORDER BY FLabourCost;
4 | P a g e

Query 3
SELECT TOP 1 FClientFirstName, FClientLastName, Car.FRegoNbr, FMake,
FModel, FProducedYear, FServiceDate, FTotalCost FROM Service INNER JOIN
(Car INNER JOIN (Client INNER JOIN CarOwner ON
Client.FClientID=CarOwner.FClientID) ON Car.FRegoNbr=CarOwner.FRegoNbr)
ON Service.FRegoNbr=Car.FRegoNbr ORDER BY FTotalCost DESC;
5 | P a g e

Query 4
SELECT Client.FClientFirstName, Client.FClientLastName, Client.FPhone,
Count(Complaint.FComplaintID) AS NumberOfComplaints FROM Client INNER
JOIN ((Car INNER JOIN (Service INNER JOIN Complaint ON
Service.FJobNbr=Complaint.FJobNbr) ON Car.FRegoNbr=Service.FRegoNbr)
INNER JOIN CarOwner ON Car.FRegoNbr=CarOwner.FRegoNbr) ON
Client.FClientID=CarOwner.FClientID GROUP BY Client.FClientFirstName,
Client.FClientLastName, Client.FPhone ORDER BY
Count(Complaint.FComplaintID) DESC;
6 | P a g e

Query 5
SELECT Client.FClientID, Client.FClientFirstName, Client.FClientLastName,
Count(Car.FMake) AS NumberOfFordCars FROM Car INNER JOIN (Client INNER
JOIN CarOwner ON Client.FClientID=CarOwner.FClientID) ON
Car.FRegoNbr=CarOwner.FRegoNbr GROUP BY Client.FClientID,
Client.FClientFirstName, Client.FClientLastName, Car.FMake HAVING
Car.FMake='Ford';
7 | P a g e

Query 6
SELECT Service.FRegoNbr, FServiceDate, FTotalCost, FMake, FModel FROM
Service INNER JOIN Car ON Service.FRegoNbr=Car.FRegoNbr WHERE
FServiceDate Between (DateAdd("m",-3,Date())) And Date() ORDER BY
FServiceDate;
Summary Report
The information of Job Services is shown below-
8 | P a g e

Limitations
Only small limitations are there in the assignment like below-
- No making of database forms
- Not use of outer join queries etc.
Learning Characters
Lots of learning features are there in the assignment like below-
- Database report
- Different data types
- Use of sub queries
- Use of inner join queries
Conclusion
The report is showing the complete way of database development and
implementation. Complete functional dependency and normalisation process is shown
in the report. The report is showing the important learning and limitations also.
References
H. Bdr (2013), SQL Database, [Online]. Available:
http://harkachalise.blogspot.in/2013/03/simple-and-composite-attributes.html.
[Accessed: 19-September-2017]
teratrax.com n.d., SQL Server Data Types and Ranges, [Online]. Available:
<http://www.teratrax.com/sql-server-data-types-ranges/> [Accessed: 19-September-
2017]
9 | P a g e