Question

Scenario: An auto shop is designing a database to keep track of repairs. So far, we have this UNF relation, with some sample data shown. Normalize to 1NF.

REPAIRS: # VIN, Make, Model, Year, ( Mileage, Date, Problem, Technician, Cost )

VIN	Make	Model	Year	Mileage	Date	Problem	Technician	Cost
15386355	Ford	Taurus	2000	128242	6/6/2014	Won’t start	Gary	$300
15386355	Ford	Taurus	2000	129680	6/20/2014	Tail light out	Trisha
43532934	Honda	Civic	2010	38002	6/18/2014	Brakes slow	Gary	$240
15386355	Ford	Taurus	2000	130786	7/5/2014	Windshield chip	Trisha	$20

Explain your reasoning here (not what you did but why you did it). Why did you create the relations the way you did?

Your 1NF relation or relations go here.

Answer 1

1. Analyzing the given relation:

The given relation has the details about the repair made on a Vehicle over time. Where:

a) VIN#: Is the unique number for the vehicle

b) Make, Model, Year: Are the attributes of Vehicle which shows its details and are same for a VIN for all repairs

c) Mileage, Date, Problem, Technician, Cost: These are the attributes of vehicle repair that shows the details about a vehicle at the time of repair, these values will change with each repair of same vehicle.

2. Why is it not in 1NF

The data relation shared is not in 1NF as:

a) It has no primary key assigned to the relation.

b) There are repeating group on attribute (Make, Model, Year) i.e. these values are being repeated for the vehicle for each repair as below where marked values are repeating groups:

3. Decomposing relation to make it in 1NF

The relation must be decomposed below to get the 1NF:

a) VEHICLE(VIN, Make, Model, Year)

Primary key: VIN

b) REPAIRS(VIN, Mileage, Date, Problem Technician, Cost)

Primary key: VIN, Mileage

Foreign key: VIN from VEHICLE (VIN)

Assumption: Every time a vehicle is brought into for repair, it will have different mileage, although a vehicle can be brought for repair on same date.

4. Why decomposition is done in this way

After decomposition the tables will look as below:

VEHICLE: The primary key is VIN and there is no repeating of any attribute value for same other attributes

REPAIRS: The table will look as below after normalization where the primary key defined without having repeating group:

Thus decomposing in such a way was removing the conditions which were being violated for meeting 1^st normal form of the database. Thus these relations are decomposed in this way.