Normalize the following to 3F Carmax – a Fortune 500 corporation – is a national

Question

Normalize the following to 3F
Carmax – a Fortune 500 corporation – is a national chain of no-haggle auto dealers. You have been asked by their Chief Information Officer to suggest a database design that reflects a shift in their business model from offering no-haggle prices to one that allows buyers to name your own price. Carmax has given you a list of variables that they collect during the sales process and has asked that you normalize the variables to 3NF. Please explicitly state any assumptions you need to make during your normalization exercise. A list of the variable names provided by Carmax is as follows:

CustomerName

CustomerAddress

CustomerPhoneNumber

CustomerSSN (used for running a credit check if a customer asks to take out a car loan) CustomerEmail

State/LocalTaxes (anywhere from 3-8%)

VIN (vehicle identification number of car being purchased by customer)

SalesAgreementID (this is the ID of the contract between Carmax and the customer)

Shipping (if the car is shipped to the client, Carmax bills $100 for distances < 50 miles; $250 otherwise)

UpgradePackage (clients can request a security system to be added to the car)

VehicleTotalCost (VehicleSalesPrice + ShippingFee + UpgradePackage – TradeInValue + Taxes) LocationID (a unique number of the Carmax location where the car was sold to the customer) LocationAddress

SalesRepID (9-digit unique number)

SalesRepName

TradeInVIN (if a trade in applies, this is the VIN number of the car that is traded in by the customer)

TradeInMake (make of a car being traded in)

TradeInModel (model of a car being traded in)

TradeInValue (the value assigned to the trade in car by Carmax – cars can be traded in many times)

VehicleStockPrice (the price the dealership paid to the manufacturer or seller for the vehicle)

VehicleSalesPrice (the price of which the vehicle is sold by Carmax to the customer)

SalesRepCommission (the amount a Sales Rep earns for each sale based on a negotiated personal rate)

CarSoldDate (the date the customer signed the sales agreement)

PaymentInCashCheck (the amount the customer paid for the car in cash or by check) PaymentInCreditCard (the amount the customer paid for the car using a credit card) PaymentInLoan (the amount financed by the customer if not paid by cash, check or credit card) LoanAgreementID

LoanCompanyName

LoanCompanyAddress

VehicleMake (this is the make of the car bought from Carmax by the customer)

VehicleModel

VehicleYear

VehicleExtColor

NameYourOwnPrice (if the customer used a name your own price, this is the dollar amount they cited) CreditScore (a client seeking a loan is assigned a credit score that helps to assess their credit worthiness) LoanInterestRate

LoanTermInMonths

TemporaryTagNumber (all cars receive a temporary tag when they are sold by Carmax)

PermanentLicensePlate (after 30 days, the state assigns each car a permanent license plate number)

HowDidYouHearAboutUs (Carmax asks new customers how did they first hear about Carmax)

LicensingAuthority (the state from which the car receives its permanent tag)

InsuranceCompany (all cars must be insured by an insurance carrier before they can be driven off the lot)

Margin (Carmax computes the dollar profits earned on each individual car sale)

DealerID (Carmax doesn’t service cars but will refer new customer to a local car dealer that does) DealerName

Explanation / Answer

Functional Dependencies:

CustomerSSN -> CustomerName,CustomerAddress, CustomerPhoneNumber, CustomerEmail

VIN -> VehicleMake ,VehicleModel,VehicleYear,VehicleExtColor, PermanentLicensePlate, LicensingAuthority ,InsuranceCompany

SalesAgreementID ->VehicleStockPrice , VehicleSalesPrice ,ShippingFee ,UpgradePackage, TradeInValue , Taxes , CarSoldDate, PaymentInCashCheck, PaymentInCreditCard , PaymentInLoan , TemporaryTagNumber, NameYourOwnPrice, HowDidYouHearAboutUs, Margin

LocationID -> LocationAddress

SalesRepID -> SalesRepName,SalesRepCommission

TradeInVIN -> TradeInMake ,TradeInModel ,TradeInValue

LoanAgreementID->LoanCompanyName , LoanCompanyAddress,CreditScore , LoanInterestRate, LoanTermInMonths

DealerID -> DealerName

Normalization to 3NF

Customer(CustomerSSN , CustomerName,CustomerAddress, CustomerPhoneNumber, CustomerEmail)

Vehicle(VIN , VehicleMake ,VehicleModel,VehicleYear,VehicleExtColor, PermanentLicensePlate, LicensingAuthority ,InsuranceCompany )

Sales(SalesAgreementID , VehicleStockPrice , VehicleSalesPrice ,ShippingFee ,UpgradePackage, TradeInValue , Taxes , CarSoldDate, PaymentInCashCheck, PaymentInCreditCard , PaymentInLoan , TemporaryTagNumber, NameYourOwnPrice, HowDidYouHearAboutUs, Margin, VIN, CustomerSSN,LocationID,SalesRepID, LoanAgreementID, DealerID,)

Location(LocationID , LocationAddress)

SalesRep(SalesRepID , SalesRepName,SalesRepCommission)

TradeIn(TradeInVIN , TradeInMake ,TradeInModel ,TradeInValue)

Loan(LoanAgreementID, LoanCompanyName , LoanCompanyAddress,CreditScore , LoanInterestRate, LoanTermInMonths)

Dealer(DealerID , DealerName)

underlined are primary keys and italicised are foreign keys.

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.