Database Design & Development Report

This report details a database design and development project focusing on a car database. It begins by explaining database normalization, specifically the Third Normal Form (3NF), and its importance in minimizing redundancy and dependency. The report then defines the entities within the car database, including Employee, SalesPerson, Admin, Mechanic, Part_type, Client, Cars, Complaint, Service, and Part_used, outlining their attributes and relationships. Functional dependencies are discussed, illustrating how they are used in the normalization process. The core of the report presents six SQL queries designed to retrieve specific information from the database, demonstrating practical application of database design principles. These queries address various scenarios, such as identifying services without part replacements, finding mechanics with high labor costs, and determining the most expensive services. Finally, the report includes references to relevant academic papers on database normalization and functional dependencies.

Cars Database 1
Database Design & Development
Submitted By
Course
Professor
Date

Cars Database 2
Normalization
Normalization is the process of minimizing redundancy as well as dependency of data in a
database design. (https://www.guru99.com, 2017). Third Normal Form (3NF) depends on the
idea of transitive reliance. A utilitarian reliance X → Y in a connection blueprint R is a transitive
reliance if there exists an arrangement of traits Z in R that is neither an applicant key nor a subset
of any key of R, and both X → Z and Z → Y hold. As indicated by Codd's unique definition, a
connection mapping R is in 3NF and also it fulfills 2NF and no nonprime quality of R is
transitively indigent on the primary key. (Chavan, S., B. & Dr. Meshram, B., B., 2012).
Entities
1. Employee  EmployeeID, emp_name, emp_address, emp_phone, emp_type.
2. SalesPerson  EmpID, emp_name, experience
Foreign key  EmpID references Employee
3. Admin  EmpID, qualification
Foreign key  EmpID references Employee
4. Mechanic  EmpID, certificate_level
Foreign key  EmpID references Employee
5. Part_type  PartID, Part_desc, price_per_unit
6. Client  ClientID, client_name, client_address, client_phone, senior_citizen.
7. Cars  RegNobr, make, model, color, produced_year, price, warranty_date,
purchased_from_ASS, EmpID, ClientID.

Cars Database 3
Foreign key  EmpID references SalesPerson, ClientID references client
8. Complaint  ComplaintID, reason, lodged_date, outcome, ClientID, regnobr.
Foreign key  ClientID references client, regnobr references cars.
9. Service  JobNbr, Job_desc, service_date, total_cost, complaintid, EmpID.
Foreign key  EmpID references Mechanic, complaintid references complaint
10. Part_used  JobNbr, PartID, qty_used, part_cost, regnobr.
Foreign key  Regnobr references cars.
Functional Dependencies
Functional dependency is the set of relationship between two attributes. These are defined as
the imperative metadata which is used for performing normalization process, to purge the data
and in addition many other tasks. To fulfill all these requirements is the main challenge for
database analysis. Various methodologies can be used to normalize an unnormalized data. In
order to implement normalization process, it is important to check attributes of an entity
whether any dependency exists in them or not. In the given scenario, we will first check the
dependency in these attributes by checking their key values. In case, if these attributes satisfy
the conditions of functional dependency, the database can be easily normalized. FD also
describes Boyce Codd Formula. The main advantage of performing functional dependency
step is to remove the repetition of data. It is mainly candidate key which helps to representing
values of the attributes. The primary key that exists in the database ensures that information is
unique and not null whereas the foreign key is the key that mainly ensures that data can be
predicted. As in the given scenario, we make multiple tables according to the requirement, few

Cars Database 4
of them are master tables whereas others are child tables that refers their master tables through
primary key. (Kulkarni, A., Batule, S., 2016).
In the given scenario, let’s take the relationship of cars and clients. In cars table, RegNobr is
unique for each car and it is dependent on Make and Model. RegNobr  make, model. In case
of Client table, ClientID is dependent on Client_name like ClientID  Client_name. This shows
the functional dependency between different attributes of table cars and client.
For being in third normal form (3NF), there must be a relationship between cars and client table
in order to reduce redundancy and dependency of the attributes. For making the tables in 3NF, I
have used the concept that each client purchases a car from the showroom. The number of cars
purchased can be more than one. So these tables will be interlinked by using a foreign key.
Client will be master table and cars will be child table. It will be one to many relationship. Table
Cars will be linked with column ClientID. Every time when a client purchases a car, the
respective ClientID will also be saved in database.
SQL Queries
Which services didn’t require the replacement of any parts? List those service details that
include job number, job description, service date, total cost and the corresponding car’s
registration number, make, model and produced year.
SELECT service.JobNbr, service.Job_desc, service.service_date, service.total_cost,
cars.RegNobr, cars.Make, cars.Model, cars.Color, cars.Purchased_year
FROM service, complaint, cars
WHERE (((service.Job_desc)='No Part Required') AND ((service.complaintid)=[complaint].
[complaintid]) AND ((complaint.RegNobr)=[cars].[RegNobr]));

Cars Database 5
2. Which mechanics have worked on any service job with charging labor cost more than
$300? List the mechanics name and phone number as well as the labor cost. Order the
result by the labor cost in ascending.
SELECT LaborWork.EmployeeID, Employee.Emp_name, Employee.Phone,
LaborWork.LaborCost
FROM (Employee INNER JOIN Mechanic ON Employee.EmployeeID = Mechanic.EmpID)
INNER JOIN (Service INNER JOIN LaborWork ON Service.JobNbr = LaborWork.JobNbr) ON
(Mechanic.EmpID = Service.EmpID) AND (Mechanic.EmpID = LaborWork.EmployeeID)
WHERE (((LaborWork.LaborCost)>300));

Cars Database 6
3. Find the service(s) that has/have the most expensive total cost. This includes the client
name and car registration number, make, model, year, service date and the total cost.
SELECT client.Client_name, complaint.RegNobr, Cars.Make, Cars.Model, Cars.Color,
Cars.Purchased_year, Service.service_date, Service.total_cost
FROM ((client INNER JOIN Cars ON client.ClientID = Cars.ClientID) INNER JOIN complaint
ON (client.ClientID = complaint.clientID) AND (Cars.RegNobr = complaint.RegNobr)) INNER
JOIN Service ON complaint.ComplaintID = Service.complaintID where
service.total_cost=(select max(total_cost) from service);

Cars Database 7
4. How many complaints were made by each client? Show the client’s name, phone, and the
number of complaints. Order the list so that the client with the most complaints appears
first.
SELECT Count(complaint.ClientID) AS CountOfComplaintID, client.Client_name,
client.client_phone
FROM client, complaint
WHERE (((complaint.clientID)=[client].[clientid]))
GROUP BY complaint.clientid, client.Client_name, client.client_phone ;

Cars Database 8
5. List details of clients who own more than one Ford vehicle. That is, list details of clients
who have more than one car with the make of “Ford”. The output should include the
Client’s ID, name and the number of Ford cars that they own. Order the result by client
last name.
SELECT client.ClientID, client.Client_name, client.client_address, client.client_phone,
Cars.Make, count(cars.RegNobr)
FROM client INNER JOIN Cars ON client.ClientID = Cars.ClientID
WHERE (((Cars.Make)='Ford'))
group by cars.RegNobr, client.ClientID, client.Client_name, client.client_address,
client.client_phone, Cars.Make;

Cars Database 9
6. Show details of services that have been done during the last 3 months. Be sure that
upcoming services do not appear in your output but the current day’s service are included.
You should show the registration number, make and model of the car involved, as well as
the date of service and the total cost. Order the output by the date of service.
SELECT complaint.RegNobr, Cars.Make, Cars.Model, Cars.Color, Service.service_date,
Service.total_cost
FROM (Cars INNER JOIN complaint ON Cars.RegNobr = complaint.RegNobr) INNER JOIN
Service ON complaint.ComplaintID = Service.complaintID
where month([Service.service_date])>6
ORDER BY Service.service_date;

Cars Database 10
References
Chavan, S., B. & Dr. Meshram, B., B. (2012). Study of Various Normal forms and Functional
Dependency. International Journal of Emerging Technology and Advanced Engineering.
Retrieved from - http://www.ijetae.com/files/Volume2Issue7/IJETAE_0712_68.pdf
Kulkarni, A., Batule, S. (2016). Functional Dependencies Discovery in RDBMS. International
Journal of Advanced Research in Computer Science and Software Engineering. Retrieved
from -https://www.ijarcsse.com/docs/papers/Volume_6/4_April2016/V6I4-0231.pdf
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples. Retrieved from -
https://www.guru99.com/database-normalization.html

Cars Database 11