Intermediate

Car rental branches: ERD case study

A regional car rental company with several branches needs to track its fleet and rentals.

The case study

  1. The company has several branches. Each branch has a branch code, a city and a phone number.
  2. Every car belongs to exactly one branch, and each branch has at least one car. A car has a registration number, a make, a model and a daily rate.
  3. Customers have a customer number, a name and a licence number.
  4. A rental has a rental number, a pickup date, a return date and a total cost. Each rental is for exactly one car and one customer. A customer can make many rentals. A car can be rented many times, but a new car may not have been rented yet.
  5. Each rental is handled by one employee. Employees have an employee number and a name, and each works at exactly one branch. A branch employs at least one employee.

Your task

Draw an Entity Relationship Diagram in Crow's Foot notation. Show every entity with its attributes, mark each primary key (PK) and foreign key (FK), and set the minimum and maximum cardinality at both ends of every relationship. Resolve any many-to-many relationship with an associative entity.

Watch out: not every noun in the case study is an entity. Some only describe something else.

Draw it in the ERD Lab and get feedback on each entity, relationship and key.

Open the ERD Lab

Model answer

Show the model answer
Crow's Foot ERD for Car rental branchesownsemploysis rented inmakeshandlesBranchPK Branch codeCityPhone numberCarPK Registration numberMakeModelDaily rateFK Branch codeEmployeePK Employee numberNameFK Branch codeRentalPK Rental numberPickup dateReturn dateTotal costFK Registration numberFK Customer numberFK Employee numberCustomerPK Customer numberNameLicence number

Entities and keys

  • Branch: Branch code (PK), City, Phone number
  • Car: Registration number (PK), Make, Model, Daily rate, Branch code (FK)
  • Employee: Employee number (PK), Name, Branch code (FK)
  • Rental: Rental number (PK), Pickup date, Return date, Total cost, Registration number (FK), Customer number (FK), Employee number (FK)
  • Customer: Customer number (PK), Name, Licence number

Relationships, read both ways

  • Each branch owns one or more cars. Each car belongs to exactly one branch.
  • Each branch employs one or more employees. Each employee works at exactly one branch.
  • Each car is rented in zero or more rentals. Each rental is for exactly one car.
  • Each customer makes one or more rentals. Each rental is made by exactly one customer.
  • Each employee handles zero or more rentals. Each rental is handled by exactly one employee.

Nouns that are not entities

  • City: City describes where a branch is. It's an attribute of Branch.
  • Daily rate: Daily rate describes a car. It's an attribute of Car.
  • Company: The company owns the system. There's only one, so it isn't modelled.
  • Licence number: Licence number describes a customer. It's an attribute of Customer.