Database Normalization Project

The assignment focuses on creating a comprehensive database normalization project, detailing the entity relationship diagram (ERD), business rules, assumptions, and definitions of various entities related to a car service and sales business. It includes normalization up to the third normal form (3NF) and references relevant literature on database management.

STUDENT NAME
STUDENT ID
COURSE ID
COURSE NAME

Contents
1. ERD.......................................................................................................................................................................... 3
2. Business rules..........................................................................................................................................................4
3. Assumptions............................................................................................................................................................4
4. Entities and attribute definitions.............................................................................................................................4
Complaint....................................................................................................................................................................4
Staff............................................................................................................................................................................. 4
Client........................................................................................................................................................................... 5
Booking....................................................................................................................................................................... 5
Service......................................................................................................................................................................... 5
Car_for_service...........................................................................................................................................................5
Parts............................................................................................................................................................................ 5
Sales............................................................................................................................................................................ 5
Sales_car..................................................................................................................................................................... 6
Normalization up to 3NF.................................................................................................................................................6
References...................................................................................................................................................................... 8

1. ERD
Entity relationship diagram modelled using Chens notation.
Figure 1: Entity religion diagram

2. Business rules
The following are the business rules identified;
 AAS repairs two types of cars. Cars bought from AAS and cars bought from other dealerships.
 AAS engages in sale of vehicles to its customers
 AAS acquires car parts from various manufacturers. These vehicle parts are used to repair car which are
booked for repair services.
 A service has to be booked for by the client before it happens. After the booking the service of repairing the
vehicle is done by one or more mechanics.
 After a service is done, a client is allowed to file a complaint regarding that service.
3. Assumptions
The following are the assumptions added on top of the business rules;
 Every service has to be paid for by the client who booked for the service. The payment done by the client
for a certain service should equal the cost of labor and the total cost of all the parts used for repairing
the car.
 Cars for sale by AAS are recorded in a different entity from the cars that are brought in for service. This is
because the cars have different types of attributes as some of the cars brought in for service are not
originally bought from AAS.
 Payment for all sold cars by any salesman are done immediately after the sale happens.
 A client who had booked a service for his or her car file more than one complaint regarding that
complaint.
 Every complaint has an issue after it is addressed by AAS there is an outcome which are all recorded in
the complaints entity.
4. Entities and attribute definitions
The following entities and their attributes were identified.
Complaint
The complaints entity is used store complaints lodged by a client who has gotten a service from AAS. A client has the
right to many complaints from a service. The following are its attributes;
 ComplaintID- Every complaint filed by the client has a complaintID that is used to identify it. So for example
complaint 1 is identified using complaintID 1.
 Issue- this attribute holds the issue of the complaint that the client wants to file. For example the issue of a
complaint could be incomplete work.
 Outcome- This attribute or field holds the outcome that is expected when the complaint is resolved by AAR.
For example the outcome could hold value work completed for the issue of incomplete work.
Staff
The staff entity is used to hold details about the staff working in AAS. The staff entity has the following attributes.
 EmployeeNO- Every employee has a unique employee number which is assigned to them when they are
employed at AAS. The employeeNO attribute is used to hold the value of the employee number of every
employee.
 Name- the name attribute is sued to hold the name of every employee.
 Contacts- the contacts attribute is used to hold the contact details of every employee working at AAS. This is
a multivalued attribute because contact details range from phone number, address or even email.
 Type- the type attribute is used to hold the type of the employee where by an employee can either be a
mechanic, salesperson or an administration staff.

Client
The client entity is sued to hold details of a client registered to AAS. A client buys or gets service from AAS. The
following are the attributes of the client entity.
 clientID- every client has a unique clientID which is allocated to them when they are registered with AAS. The
clientID attribute is sued to hold the value of the client ID of every employee.
 Name- the name attribute is used to hold the name of every client that is registered to AAS.
 Address- the address attribute is sued to hold the address of every employee that is registered to AAS. The
address value is usually the physical address of the employee.
 Is_senior- the is_senor attribute is used to hold the value indicating whether a client is a senior citizen or not
where by senior citizens are entitled to a discount. The is_senior fields holds the seniority status of the client.
Booking
The booking entity is sued to hold details of a booking made by a client. When a client’s wants a service for their car
from AAS they have making a booking. The booking entity holds the details regarding the booking. The following are
that attributes that make up the booking entity.
 bookingID- the bookingID attribute is used to hold the unique id assigned to every booking made by a client.
 Date- the date attribute is used to hold the date that the booking was made.
Service
After a booking is done, it results to a service thus the service entity is used to hold the details of a service done for a
certain service requested by a client through a booking. The following attributes make up the service entity.
 ServiceID- the serviceID is the unique attribute used to hold unique of every service done by AAS on a car.
 Description- the description field is used to hold the description of the service that has been done on a
vehicle.
Car_for_service
The car for service entity is sued to record details of cars that are brought in for service in AAS. These type of cars are
not the same as the cars for sale as some are not purchased from AAS. The following are the attributes of entity.
 Service_carID- the service_carID field is used to hold the unique id of every car that is brought in for service
at AAS.
 Model- the odel field or attribute is sued to hold the model type of every car that is brought in for service at
AAS.
Parts
When a car is serviced at AAS, some of the services undertaken require new parts for replacements. The parts entity
is sued to hold details of parts that are used for various car servicing in AAS. The following are the attributes making
up the entity.
 partID- the partID attribute is used to hold the unique id of every part that is used by AAS for servicing a car.
 Brand- the brand attribute is used to hold details of the brand of the part used for a servicing a vehicle.
 Manufacturer- the manufacturer attribute or field is used to hold the value of the manufacturer that made
the part used for servicing a car.
Sales
The sales entity is used to record details of a sale done by a salesperson in AAS. Sales involves sell of new cars and
not the ones brought in for a service. THe following are the attributes that make up the sales entity
 saleID- every sale of a car has a unique id. The saleID attribute is used to hold the value of the unique sale id.
 Price- the price entity is used to hold the details of the price that a car was sold for.
 Warranty- the warranty attribute is used to hold the value containing the warranty information of the car
sold for a certain sale.

Sales_car
The sale_car entity is sued to hold details of cars that are for sale in AAS. Cars that are for sale are new and are
recorded in a different entity from the cars brought in for service as they have different attributes. THe following are
the attributes that make up the sales_car entity.
 Sale_carID- the sales_carID attribute is used to hold the unique id of every car sold at AAS.
 Model- the model attribute is used to hold the value containing the model of the car sold for a certain sale.
Normalization up to 3NF
These entities have not been normalized to 3NF because of the following reasons;
 There is existence of repeating groups in some of the entities
 There is existence of partial dependencies in some of the relations
 There is existence of transitive dependencies in some of the relations.
The following relations are achieved by eliminating the existence of the three conditions in each of the relations to
achieve table in 3NF.
The following diagram shows the entity relationship diagram of the relations in 3NF

Relations in 3NF
Staff (employeeNO,name, email, phone, address, type)
Mechanic_info(infoID, employeeNO, trade_level, expertise)
Salesman_info (infoID, employeeNO)
Admin_qualifications(qualificationID,qualification, employeeNO)
Clients (clientID, name, address, phone, email, is_senior)
Booking (bookingID, serviceID, clientID,service_carNO, employeeNO, is_car_AAS)
Sales (saleID, price, warranty, sales_carID, clientID)
Sales_car (sales_carID, quantity, modelNO, color)
Model (modelNO, name)
Service_job (jobID, bookingID, employeeNO, labour_cost, part_cost,start_time, finish_time)
Service (serviceID, name, description)
Service_car (service_carID, modelNO, color)
Job_parts (job_partID,jobID, quantity)
Parts (partID, description, manufacturerID,quantity, brand_info, Unit_price)
Manufacturers (manufacturerID, name)
Job_mechanics (jobID, employeeNO)
Payments (paymentID, amount, date, jobID)
Complaints(complaintID, issue, outcome, employeeNO, jobID, clientID)

References
Baral, S. (2014, August 21). Solved MCQ on Database Normalization set-1. Retrieved August 10, 2017, from
http://www.siteforinfotech.com/2014/08/solved-mcq-on-database-normalization.html
Silberschatz, A., Korth, H. F., & Sudarshan, S. (1986). Database System Concepts. McGraw-Hill Education.
Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd Edition ed.). New York: McGraw-Hill
Education.