Car Rental System
An agency that rents the same car many times: the example that shows what an E-R schema records about time, and what it silently cannot forbid.
Advanced
The diagram
Opening an example creates a copy in your own projects. The original example is never modified.
The problem
A car rental agency operates from several branches. Each branch is identified by a code and located in a city, it owns a fleet of vehicles, and every vehicle belongs to exactly one branch — the one that bought it and is responsible for its maintenance. A vehicle is identified by its plate, and the agency also stores its model and the odometer reading taken the last time it came back. Customers are registered once, with a customer number, their full name and their driving licence number; a customer may rent nothing at all for years, or rent three times in the same month. Every rental is opened at a branch and records the moment the vehicle leaves and, later, the moment it comes back; while the vehicle is still out, the return moment does not exist yet. A rental is paid in one or more instalments — typically a deposit at pickup and the balance at return — and each payment belongs to exactly one rental. Finally, the agency insists on a rule it considers non-negotiable: the same vehicle may be rented many times, but two rentals of the same vehicle must never overlap in time.
Requirements
- The system must record customers, each with a unique customer number, a full name and a driving licence number.
- The system must record vehicles, each with a unique plate, a model and the odometer reading taken at the last return.
- The system must record branches, each with a unique branch code and a city.
- Every vehicle belongs to exactly one branch; a branch owns zero, one or many vehicles.
- The system must record each rental with a rental number, the customer, the vehicle, the branch where the vehicle was collected, the pickup moment, and the return moment once the vehicle is back.
- A customer may have made zero, one or many rentals; every rental is made by exactly one customer.
- Every rental is collected at exactly one branch; a branch may be the collection point of zero, one or many rentals.
- The system must record each payment with a payment number and an amount; a rental may be settled by zero, one or many payments, and every payment settles exactly one rental.
- No two rentals of the same vehicle may overlap in time; a rental whose vehicle has not come back yet has no return moment.
Entities and attributes
| Entity | Attributes | Identifier |
|---|---|---|
| Customer | customer_no, full_name, driving_licence | customer_no |
| Rental | rental_no, picked_up_at, returned_at | rental_no |
| Vehicle | plate, model, odometer | plate |
| Payment | payment_no, amount | payment_no |
| Branch | branch_code, city | branch_code |
Relations and cardinalities
| Relation | From | To | Cardinality (min, max) | Note |
|---|---|---|---|---|
| Makes | Customer | Rental | (0,N) – (1,1) |
A customer may have made zero, one or many rentals, while every rental is made by exactly one customer. The maximum of 1 is a business rule, not a technicality: an anonymous rental would have nobody to invoice and nobody to charge for a damaged car. |
| Uses | Rental | Vehicle | (0,N) – (1,1) |
A rental names exactly one vehicle, and a vehicle is rented zero, one or many times over its life. The many side is Rental, and this is precisely where the temporal-validity problem of this example appears. |
| Pays | Rental | Payment | (0,N) – (1,1) |
A rental may be settled by zero, one or many payments — a deposit at pickup and the balance at return — and every payment settles exactly one rental. This is why amount belongs to Payment and not to Rental. |
| OwnedBy | Vehicle | Branch | (0,N) – (1,1) |
A branch owns zero, one or many vehicles, and every vehicle belongs to exactly one branch. This is a permanent fact about the fleet: it is decided when the vehicle is bought and does not depend on any rental. |
| PickedUpAt | Rental | Branch | (0,N) – (1,1) |
A branch is the collection point of zero, one or many rentals, and every rental is collected at exactly one branch. Distinct from OwnedBy: the same rental would still exist, and would be perfectly legal, if the vehicle belonged to another branch. |
Reading the diagram
- The diagram has five entities. Customer, Vehicle, Branch and Payment exist on their own: each has an identifier the agency really uses (customer_no, plate, branch_code, payment_no) and could be listed without any rental in the database. Rental is the entity in the middle: it exists to record that a customer took a vehicle from a branch for a period, and every association in the diagram either starts from it or ends in it.
- Makes links Customer and Rental. The pair written on the Customer side is (0,N): a customer may have made no rentals at all, and there is no upper limit worth declaring. The pair on the Rental side is (1,1): every rental has exactly one customer. Read the two together and you have the sentence from the requirements — many rentals, one customer — which is the definition of a one-to-many. Notice which side carries the fact: 'this rental was made by customer 4471' is a property of the rental, which is why the foreign key will sit on Rental later.
- Uses links Rental and Vehicle with the same shape, and it is the association that gives this example its subject. A rental names exactly one vehicle; a vehicle is rented many times over the years. The model records that history correctly — one Vehicle row, many Rental rows pointing at it — and it records it without redundancy: the plate is stored once, not once per rental.
- What the model does not say is the rule the agency cares most about. Cardinality counts instances, not time: it says how many rentals a vehicle may take part in, never when they may happen. Nothing in Uses, and nothing in any other association of this diagram, forbids a second rental of the same plate from starting before the first one has ended. Two overlapping rentals for the same car are, as far as the schema is concerned, perfectly ordinary rows. This is a limitation of the model, not a mistake in the drawing, and pretending otherwise is the most expensive error in this example: reading the (1,1) on the Vehicle side as 'one rental at a time' would actually forbid the vehicle from ever being rented twice, since the pair would then state that a vehicle takes part in exactly one rental in its whole life. The rule has to be enforced where time can be compared: a database exclusion constraint on (plate, period), a trigger that checks for an intersecting interval before insert and update, or an application check. Each has a cost. The constraint is the only one that survives a second program writing to the same tables; the application check is the only one that can produce a good error message for the clerk at the counter. In a serious system you do both, and you still keep the diagram, because the diagram is what documents that the rule exists.
- returned_at is deliberately nullable, and its null is not an absence of information: it means the vehicle has not come back yet. That single meaning supports the most frequent query of the agency — which cars are out right now — and it is the reason the attribute cannot be replaced by a flag or by a separate entity without splitting one fact in two. The companion rule picked_up_at <= returned_at, when returned_at is not null, is again outside the diagram: it is a check constraint on the row.
- Pays attaches Payment to Rental, with (0,N) on the Rental side and (1,1) on the Payment side: a rental may be settled in several instalments, and every payment settles exactly one rental. Because of this shape, amount lives on Payment and not on Rental. A total on Rental would be a derived value — the sum of the payments — and derived values that are also stored have to be maintained, which is how the invoice total and the payments stop agreeing. A rental with no payment yet is legal, and it is a useful state: it is a rental that has not been paid.
- Branch appears twice, and this is the part students most often flatten. OwnedBy says which branch owns the vehicle: one branch, decided when the car is bought, valid for the life of the car. PickedUpAt says where this particular rental started: one branch, decided per rental, frequently not the owner. The two associations have different lifetimes, different subjects (a vehicle versus a rental) and different reasons to change, so they are two facts and not one. Merge them — derive the pickup branch from the owner branch — and the agency can no longer sell a one-way rental; store the owner branch on Rental as well, and the same fleet fact is repeated on every rental row, free to disagree with itself. Two associations, two roles, one Branch entity.
- Finally, read the diagram as a set of promises and check them against the requirements one by one: every rental has one customer, one vehicle and one pickup branch; every vehicle has one owner; a customer, a branch, a vehicle and a payment all exist independently. Everything else the agency asked for — no overlapping rentals, a return after a pickup, an instalment that does not exceed the agreed price — is a rule about values and about time, and belongs to the level below the diagram.
From the conceptual model to the logical schema
Customer(customer_no, full_name, driving_licence) PRIMARY KEY (customer_no) Branch(branch_code, city) PRIMARY KEY (branch_code) Vehicle(plate, model, odometer, branch_code) PRIMARY KEY (plate) FOREIGN KEY (branch_code) REFERENCES Branch(branch_code) Rental(rental_no, customer_no, plate, pickup_branch_code, picked_up_at, returned_at) PRIMARY KEY (rental_no) FOREIGN KEY (customer_no) REFERENCES Customer(customer_no) FOREIGN KEY (plate) REFERENCES Vehicle(plate) FOREIGN KEY (pickup_branch_code) REFERENCES Branch(branch_code) Payment(payment_no, rental_no, amount) PRIMARY KEY (payment_no) FOREIGN KEY (rental_no) REFERENCES Rental(rental_no)