Database Implementation Report

The report presents a detailed overview of the database implementation for AAS, including normalization processes, SQL queries, and a summary of job services. It highlights the limitations and learning points, providing a comprehensive understanding of database design and implementation.

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

Implementation Report
Introduction
The database implementation of AAS is being shown in the report. The complete
functionality and features of AAS database are defined and described in the report.
The normalization process is also shown in detail.
Normalization
Mapping of ER Diagram to Third Normal Form
(Studytonight.com 2016)
Functional Dependency
Client
Client (ClientID, ClientName, Street, City, State, Postcode, Phone, IsSenior)
Lot of Clients may share same information like name, address etc. To find the specific
client, an identity is required. The Client Id is the unique or primary key that is
helping in finding the specific client.
ClientName  ClientID
State  ClientID
2 | P a g e

PartType
PartType (PartID, PartDescription, PricePerUnit)
Lot of parts may have same prive. To find the specific part, an identity is required.
The part Id is the unique or primary key that is helping in finding the specific part.
PricePerUnit  PartID
Normalisation Demonstration
A table is called in 3 NF if all the below mentioned rules are concerned-
- The table include the specific data.
- The table include the primary key.
- No transitive depend should be in the table.
Following tables are showing the best example of third normalization-
Client (ClientID, ClientName, Street, City, State, Postcode, Phone, IsSenior)
PartType (PartID, PartDescription, PricePerUnit)
Client and Part Type tables, both are satisfying the 3 NF rules. Therefore, both are in
3 NF.
(Freetutes.com n.d.)
3 | P a g e

SQL Queries
Query 1
SELECT JobNbr, JobDescription, ServiceDate, TotalCost, Service.RegoNbr, Make,
Model, ProducedYearFROM Service INNER JOIN Car ON
Service.RegoNbr=Car.RegoNbr WHERE JobNbr not in (Select JobNbr from
PartUsed);
Query 2
SELECT EmployeeName, Phone, LabourCost FROM Employee INNER JOIN
LabourWork ON Employee.EmployeeID=LabourWork.EmployeeID WHERE
LabourCost>300 ORDER BY LabourCost;
Query 3
SELECT TOP 1 ClientFirstName, ClientLastName, Car.RegoNbr, Make, Model,
ProducedYear, ServiceDate, TotalCost FROM Service INNER JOIN (Car INNER
JOIN (Client INNER JOIN CarOwner ON Client.ClientID=CarOwner.ClientID) ON
Car.RegoNbr=CarOwner.RegoNbr) ON Service.RegoNbr=Car.RegoNbr ORDER BY
TotalCost DESC;
4 | P a g e

Query 4
SELECT Client.ClientFirstName, Client.ClientLastName, Client.Phone,
Count(Complaint.ComplaintID) AS NumberOfComplaints FROM Client INNER
JOIN ((Car INNER JOIN (Service INNER JOIN Complaint ON Service.JobNbr =
Complaint.JobNbr) ON Car.RegoNbr = Service.RegoNbr) INNER JOIN CarOwner
ON Car.RegoNbr = CarOwner.RegoNbr) ON Client.ClientID = CarOwner.ClientID
GROUP BY Client.ClientFirstName, Client.ClientLastName, Client.Phone ORDER
BY Count(Complaint.ComplaintID) DESC;
Query 5
SELECT Client.ClientID, Client.ClientFirstName, Client.ClientLastName,
Count(Car.Make) as NumberOfFordCars FROM Car INNER JOIN (Client INNER
JOIN CarOwner ON Client.ClientID = CarOwner.ClientID) ON Car.RegoNbr =
CarOwner.RegoNbr group by Client.ClientID, Client.ClientFirstName,
Client.ClientLastName, Car.Make having Car.Make='Ford';
5 | P a g e

Query 6
SELECT Service.RegoNbr, ServiceDate, TotalCost, Make, Model FROM Service
INNER JOIN Car ON Service.RegoNbr = Car.RegoNbr where ServiceDate between
(DateAdd("m",-3,Date())) And Date() order by ServiceDate;
Summary Report
The information of Job Services is shown below-
6 | P a g e

Limitations
Some limitations have noticed into the assignment like below-
- Lack of database forms
- Lack of complex queries
Learning Characters
There are lots of learning characters in the assignment. these are the major learning
points that are noticed in the assignment-
- Report in MS Access
- Normalisation techniques
- Use of all types of data types
Conclusion
The report is showing the complete detail of database design and implementation. All
the important points and drawbacks are described in the report. After studying the
report, a user can easily handle the database implementation independently.
References
Freetutes.com (n.d.), Types of attributes, [Online]. Available:
http://www.freetutes.com/systemanalysis/sa7-types-of-attributes.html.
[Accessed: 9-September-2017]
Studytonight.com (2016), E-R Diagram, [Online]. Available:
http://www.studytonight.com/dbms/er-diagram.php. [Accessed: 9-September-
2017]
7 | P a g e