Database Design and Implementation Project

This document details a database design project involving the creation of tables (Client, Car, CarOwner, Service, Complaint) and defining relationships between them. It includes detailed SQL queries to retrieve specific data, such as client complaint history, number of complaints per client, service records with filtering by date, and the count of Ford cars owned by clients. The document also provides a summary report outlining the project's scope, limitations, and key learnings.

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