Specialty Imports – Database Normalization

This report discusses the importance of implementing database normalization for Specialty Imports, a vehicle dealership. It covers the planning and analysis, assumptions, entities, and steps of normalization for efficient management of customer bases and sales process. The report also includes functional dependencies and references for further reading.

Student Name
Student ID 1
Specialty Imports – Database Normalization
Submitted By
Course
Professor
Date

Student Name
Student ID 2
Introduction
Specialty Imports is a vehicle dealership which is situated on the "auto strip" in the area of
North-West Brampton where few other dealerships are situated as Brampton is right now moving
numerous dealerships into one central location. Specialty Imports has a conventional dealership
authoritative structure. The parts as well as service manager is like a business that operates inside
a business and has not been firmly administered. Accounts manager is fundamentally responsible
of records receivable and different parts of revenue. Most administration work is charged on
bank cards and this processing on papers is processed very quickly.
After the client and businessperson agrees on the deal, the sales invoice is made and signed by
both the purchaser and salesman. The main concern of the owner is to monitor existing and
potential clients, the vehicles that they look for, the vehicles he has in stock and that they have
obtained. An on-line on request framework that can display the name, address and phone number
of forthcoming clients who have shown their enthusiasm for chosen vehicles. The scan criteria
for finding such clients would incorporate integration of make, model name, color, upholstery
style and year of make of their favored vehicles. He has discovered that a few clients will think
about a few possible vehicles, so various inclinations must be dealt with. In this report, we will
normalize the forms that are provided. All the entities which we have considered for
implementing database for the organization will be in Third Normal Form.
Planning & Analysis
Executing database undertaking will give specialists the tool to manage their customer bases in
an efficient way and to upgrade the sales process of the association, and can help to give better
advertising systems. Implementing database can help to lessen the redundancy of information.

Student Name
Student ID 3
Executing database can upgrade efficiency, reduce costs, and improve vehicle sales over
different locations in Brampton. As opposed to each dealership area being an island onto itself,
there will be one centralized database that has a place with the Specialty Imports so the critical
information can be shared among with other dealers. Any business administrator or manager can
investigate a customer and see their history, including which vehicle they have requested, which
sales man they chatted with, and what they require in a vehicle.
Maurice will have the ability to successfully make sense of which salesman each customer has a
place with, and which agents should get advantage for an explicit arrangement, facilitating the
contentions among sales agents and it decreases staff turnover. Transferring information to the
database will get the precise outcome about specific client following and that will give business
people and directors the information they bring to sell more vehicles. It must be possible if a
specific procedure is actualized for the usage of database. (Rothaar, 2014).
Normalization
When building up the outline of a relational database, a standout amongst the most vital aspect to
be considered is to guarantee that the duplication is limited. Normalization is the way toward
organizing the information in the database. Normalization is most important which is utilized to
limit the redundancy from a connection or set of relations. It is likewise used to minimize the
unwanted attributes like Insertion, Update and Deletion Anomalies. The center idea of database
normalization is to isolate the tables into small sub-tables and store pointers to information as
opposed to repeating it. Without Normalization, it is hard to deal with and update database,
without confronting information loss. Insertion, Updation and Deletion anomalies are extremely
frequent if Database isn't normalized. (Goel, 2018).

Student Name
Student ID 4
Assumptions
Following assumptions can be considered while developing 3NF database for Specialty Imports
Organization:
 A customer can purchase as well as can show his interest in particular vehicle. All the customers’
details will be saved in customer table which are identified by unique customerid.
 There will be unique vehicleno for each serial no of the vehicle which can be uniquely identified
for a vehicle.
 All the insurance details of the vehicle will be saved in Insurance table.
 There will be different invoices with different invoice no for sales as well as service invoices.
 All the inventory of the service parts will be automatically updated in Parts table.
 All the service details of the vehicle like invoice id, work done will be saved in transaction table
of service.
Entities
The main entities which can be used in the database implementation are as following:
Entity Definition
Vehicle Vehicle table will be used to store all the important details vehicle
with serial no.
Employee As the name shows, all the details of employees with their designation
will be saved in Employee table.
Customers Customers’ details will be stored in Customers table. It will be master
table.
Sales_Invoice The sales made by customers will be stored in this entity.
Insurance When a customer purchases a vehicle, he spends some amount on
insurance as well. All insurance details will be saved in Insurance
table.

Student Name
Student ID 5
Vehicle_Purchase This is the transactional table for purchasing a vehicle from
manufacturer. It will help to add the inventory.
Parts This is the master table for all the parts that can be used for service.
Service_Invoice An invoice will be issued on each service. All its details will be saved
in this table.
Service_details It will store all the history of service like what type of services have
been done in a vehicle.
Normalization of Entities
Vehicle (VehicleNo, SerialNo, Make, Model, Year, ExteriorColor, Trim, PurchaseCost,
ListPrice, StkinHand)
Employee (EmployeeID, Fname, Lname, Address, City, ZipCode, Email, Position)
Customer (CustomerID, Fname, Lname, Address, City, ZipCode, Email, Required_vehicle*)
Sales_Invoice (Sales_InvoiceNo, CustomerID*, VehicleNo*, EmplyeeID*, Sales_Date, Taxes,
Discount, NetPrice)
Insurance (InsuranceID, CustomerID*, Sales_InvoiceNo*, Insurance_Coverage,
Insurance_Date, Renewal_date, Insurance_Amount, Monthly_Installment)
Vehicle_Purchase (PurchaseNo, VehicleNo*, Purchase_date, Qty, Taxes, Purchase_price,
Manufacturer, Dealership)
Parts (PartID, Part_Name, Manufacturer, Price, Stkinhand)
Service_Invoice (Service_InvoiceNo, CustomerID*, VehicleNo*, Service_Date, Total_Cost,
Labor, Tax, EmployeeID*)

Student Name
Student ID 6
Data Diagram
Steps of Normalization
Vehicle (VehicleNo, SerialNo, Make, Model, Year, CustomerName, EmployeeName,
InvoiceNo, SalesDate, Tax, Discount).
The above schema is not normalized as a vehicle can be sold many times and can have many
invoice no. It will be difficult to uniquely identify each sale invoice no. Hence, above schema is
not normalized.

Student Name
Student ID 7
Vehicle (VehicleNo, SerialNo, Make, Model, Year, ExteriorColor, Trim, PurchaseCost,
ListPrice, StkinHand)
Employee (EmployeeID, Fname, Lname, Address, City, ZipCode, Email, Position)
Customer (CustomerID, Fname, Lname, Address, City, ZipCode, Email, Required_vehicle*)
Sales_Invoice (Sales_InvoiceNo, CustomerID*, VehicleNo*, EmplyeeID*, Sales_Date, Taxes,
Discount, NetPrice)
The above relation is in 1NF because each attribute will have single value as VehicleNo and
Sales_InvoiceNo are primary keys. The above relationship is not in 2nF and 3NF.
Functional dependency in above relation will be as following:
VehicleNo  Make, Model, Manufacturer
Sales_InvoiceNo  Customer Name, Employee Name, Sales_Date, Vehicle Model.
2NF In order to make above relationship in 2NF, the rules are:
 The relationship must be in 1NF.
 No partial-dependency exist between the given attributes.
I have considered the example of vehicle entity. I have taken VehicleNo, Sales_InvoiceNo as
primary key. VehicleNo mainly identifies a vehicle. It will be unique as make, model and
manufacturer can be repeatable. No repeating group exists in the entity. Therefore, we have
taken VehicleNo as primary key which is considered as candidate key as name can be repeated.
We have taken VehicleNo as primary key. This will be taken as candidate key as the make and
model can be duplicated. Manufacturer can also duplicate in the entity. Therefore, VehicleNo is

Student Name
Student ID 8
single valued candidate key because of no partial dependency. Therefore, we can say that
relationship is in 2NF.
Vehicle make, model cannot be used to determine a vehicle from the entity as the two vehicles
can have same make and model. Therefore, there is no transitive dependencies in the table.
Hence, we can say that table is in 1NF, 2NF and 3NF.
References
Goel, A. (2018). DBMS Normalization: 1NF, 2NF, 3NF and BCNF with Examples. Retrieved
from - https://hackr.io/blog/dbms-normalization
Rothaar, T. (2014). Fictional Business Case for Car Dealership CRM. Retrieved from
-https://www.slideshare.net/teresarothaar/rothaar-businesscaseproject