Car Rental Database Normalization

[rhfh]

Hi guys,

I'll doing a car rental database with 4 tables. Below is my the data after normalization..Could you guys pls tells me is it correct? If it's wrong, what to amend? Thanks so much..

Customer {CustomerID, FirstName, LastName, Address, ContactNo, Email}
Agent {AgentID, FirstName, LastName, ContactNo, Email, RentaLID(FK)}
Vehicle {VehiclePlateNo, Type, Brand, Model, ManufacturingDate, Color}
Rental {VehiclePlateNo, CustomerID, Hour, Rate, TotalAmount, Date, Time-In, Time-Out}
*RentaLID = VehiclePlateNo + CustomerID

Best Regards,
Roland

[seslie]

Hi rhfh,

Looks good to me, although i would make Rental table have its own unique primary key though. For example, what if a particular customer likes a particular car and wants to use that car more than once?

check out this link for a more comprehensive example. Be sure to check out other database models too. should help you.

Regards,
Seslie

hi Roland!

Well, if this is homework which has been assigned by FH (kind of Austrian or German college, not University), your solution is insufficient.

1st, assuming there is a many to many relationship between customers and vehicles then rental forms this relationship. Thus primary key of rental is incomplete for the same customer (customerid) won't be able to rent same car (VehiclePlateNo) twice.

2nd, an agent may broker more than as ONE car. Therefore this requirement can not be modelled through a simple foreign key (VehiclePlateNo, CustomerID) of that agent (what would express a one to many relationship only). A better idea seems to be adding the primary key of the agent who brokered the relevant rental to the relationship rental, there agentid will become foreign key of rental. (And rental would be able to manage all relevant information of a certain rental process.)

3rd, what is contactno of both customers and agents? If contactno is considered to record contacts between agents and customers, you should put into account there are usually more than as ONE contact, e.g. an agent may have various contacts to customers. However, your design only allows to managed ONE contact per agent.

Note btw., it is not a good idea to concatenate VehiclePlateNo and CustomerID together to building a composite key. yet, in your specific solution this doesn't matter for this poor composite foreign key is already misplaced in agent.

I hope, I have been able to give you some impressions on your homework. You may draw an UML or ERM graphic to visualize your design for better understanding.

-- 1stDAN