Customers can rent cars from The Payless Car Rental. The Payless Car Rental has

Question

Customers can rent cars from The Payless Car Rental. The Payless Car Rental has three brands of cars: Toyota, Ford and Hyundai. Cars are maintained by the dealers of the corresponding brands (e.g., for examinations and inspections). The Payless Car Rental keeps the location and contact phone numbers of these dealers. Every time a customer rents a car, a daily rate is determined. The rate may change over time. The Payless Car Rental keeps track of the days that the customer rents the car. The total charge of a rental transaction is usually equal to the sum of the total rental charge (which is equal to daily rate multiplied by the number of rental days) and some fees. The customer can either pay the total charge in full, or pay using up to 4 payment installments. E.g, with 4 installments, the customer pays 25% each time, for 4 times. The interests are ignored. Paying in full is equivalent to paying using 1 installment. The Payless Car Rental use an installment ID with the values of “Install1”, “Install2”, “Install3”, and “Install4” to differentiate between installments of the same transaction. The customer can pay installments using different types of payments (e.g., cash, credit cards, or checks). To keep track of information, The Payless Car Rental assigns IDs to appropriate entities it identifies and creates the following 1NF table. T1(CUS_ID, CUS_Name, CUS_Email, CUS_Phone, Car_ID, Car_Type, Dealer_Name, Dealer_Location, Dealer_Phone, Rent_Days, Rent_Rate, Total_Charge, Install_ID, Install_Pay_Amount, Install_Payment_Type)

1. Given the above information and the table T1, determine the appropriate primary key for T1. Then convert T1 to 2NF tables. Do not go further to 3NF. Mark the primary keys of all tables in bold. Under line the foreign keys of all tables.

Explanation / Answer

Answer for Question:

As given in problem statement

Separate the Entities based on given information about each Entity

Customer( CUS_ID(Primary Key), CUS_Name, CUS_Mail)

Dealer( Dealer_Name, Dealer_Location , Dealer_Phone, Rent_Days, Rent_Rate)

In Dealer Table Dealer_Name and Dealer_Location combined called as Primary Key

Instalment( Install_ID(Primary Key), Install_Pay_Amount, Install_Payment_Type)

Car(Car_ID(Primary Key), Car_Type)

Relation between Customer and Car
Customer_Car(Car_ID (Foreign Key),Car_Id(Foreign Key))

Relation Between Car and Dealer
(Car_Id(Foreign Key), Rent_Days, Rent_Rate)

Relation Between the Car and Instalment and Customer
Customer_Car_Instalment(Install_ID(Foreign Key),Car_ID (Foreign Key),Car_Id(Foreign Key))

The above relations are satisfies given customer needs.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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