Question

Bill Arrow of the PROPS airplane service business that we learned about for 1 also has an auto rental business at that same small airport. He has been managing this part of his “empire” with a large, complicated spreadsheet. The spreadsheet has met his needs in the past, but with the success of the database you designed and implemented for the airplane storage and service parts of his business, he has recognized the value of the database to store, access, protect his data. He has asked you to develop a plan to incorporate his existing spreadsheet data into new database.

The spreadsheet contains data for the cars in the rental fleet. It tracks the maker, model, VIN number, body style, and number of doors for each car. It also tracks the dealers from whom Bill bought the cars with dealer name and address. (Most dealers have brick and mortar buildings and physical addresses. However, there are also some Tesla autos in the fleet. Bill buys these units directly from Tesla through a web site.)

The following table represents a portion of the spreadsheet. Using this table as a starting point, show the steps you would take to design a new, normalized table(s) that could be added to the existing PROPS database. There is no need to consider how this new table(s) would be actually be added to the PROPS database

PROPS Auto Rental Spreadsheet

Maker	Model	VIN Number	Body Style	Doors	Model Year	Dealer	Address
Ford (F)	Fusion (F)	123456789	Sedan (S)	4	2020	Valley Ford (VF)	123 Valley Road Glenview, VA
	F-150 (F-1)	234567890	Pickup (P)	2	2020
	Escape (E)	34567891	SUV	5	2020	Ridgeline Ford (RF)	1515 Ridge Rd. Crestview, SC
Tesla (TS)	Y	908070123	Hatch (H)	5	2020	Tesla (T)	Tesla.com
Toyota (T)	Camry (C)	987654321	Sedan (S)	4	2019	Mid-Town Toyota (MT)	321 Main St Ridge City, NC
	RAV4 (R)	678954321	SUV	5	2020	Ridgeline Ford (RF)	1515 Ridge Rd. Crestview, SC

1. Convert the PROPS Auto Rental spreadsheet into a 1NF relational database table(s). You may add fields to the table, if necessary. You may use the abbreviations from the spreadsheet in your table. Indicate prime key field(s).   Provide written justification for your solution, if necessary. Draw the table as a grid. (20 points)

2. Put the 1NF table from Question 7 into 2NF. Provide written justification for your solution, if necessary. (20 points)

3. Put the 2NF tables from Question 8 into 3NF. Provide written justification for your solution, if necessary. (20 points)

Answer 1

ORIGINAL DATABASE-

Maker

Model

VIN Number

Body Style

Doors

Model Year

Dealer

Address

To normalize this data base -

What is normalization?

It is the process of simplifying the database or breaking the large table into small multiple tables to reduce data redundancy and to remove certain anomalies sucha as update , insert, delete anomaly.

There are 6 Normal Forms of the normalization, But here we have to do Upto 3rd Normal Form.

1. 1st NORMAL FORM-

In first normal form each attribute of the table should be atomic in value that is it should not have multiple values.

Since this table has all the atomic values in it so it is in 1NF already.

VIN Number

Maker

Model

Body Style

Doors

Model Year

Dealer

Address

2. 2nd NORMAL FORM-

In second normal form it should follow these two rules-

a- It should be in 1st Normal Form.

b- And all the non-key attributes should be fully functional dependent on the primary key.
It should not have Partial Dependency.

Therefore after converting the table into 2NF we have two tables now-

Table 1- Vehichle info table-

This table has VIN number as the primary key and the maker, mode, dealer and address are fully functional dependent on it.

VIN Number

Maker

Model

Dealer

Address

Table 2- Model description table-

This table has model as a primary key and the body style , doors and model year are fully functional dependent on it.

Model

Body Style

Doors

Model Year

3. 3rd NORM FORM-

In third normal form it should follow these two rules-

a- It should be in 2nd Normal Form.

b- And it can not contain any transitive partial dependency.

Therefore after converting the table into 3NF we have three tables now-

Table 1- Vehichle info table-

This table has VIN number as the primary key and the maker, mode, dealerID as foriegn key, dealer and address atrtributes are removed to remove any transitive partial dependency on it.

VIN Number

Maker

Model

DealerID

Table 2-Dealer INfo Table-

This table has been created separately in order to store dealers personal info separately and to remove the transitive partial dependency of table 1. This has dealerID as primary key

DealerID

Dealer

Address

Table 3- Model description table-

This table has model as a primary key and the body style , doors and model year are fully functional dependent on it and do not have any transitive partial dependency also, so it is in 3nf only.

Model

Body Style

Doors

Model Year

ques- Where did you get Dealer ID from?

Answer- I created it separately in order to create a unique key in dealers info table. DealerID will be auto incremental from 1,2,3,4 and so on..

Here in these tables, The Primay key of each table is in bold and underlined the foriegn keys are italics and underlined.

If any doubt, please feel free to ask and comment, i would like to answer them.
Thank you