Skip to content

WA1. ER Diagrams

Statement

  • Construct an E-R diagram for a car insurance company whose customers own one or more cars each.
  • Each car has associated with it zero to any number of recorded accidents

Solution

  • Let’s start with analyzing the context of the problem to extract the most important entities:

    • Customer. represents a customer of the insurance company, who pays a monthly premium for insurance, and hopes for a claim in case of an accident.
    • Car. represents a car owned by a customer, which can be involved in an accident.
    • Accident. represents an accident that can involve one or more cars, and which can be reported by a customer.
    • Employee. represents an employee of the insurance company, who can process claims, add new customers, cars, accidents, and so on.

  • A quick lock at the discussion above will lead to extracting more minor entities, like:

    • Monthly Payment. represents the monthly payment of a customer for the insurance of a car that may be linked to an Invoice, Reminder, and so on.
    • Joining Fee. represents the joining fee of a customer for the insurance of a car that may be linked to Referral, Discount, and so on.
    • Claim. represents a claim of a customer for compensation for an accident, this represents a complex case that may be linked to Police Report, Insurance Report, Repair Bill, Court Case, and so on.
  • All the highlighted bold names above are candidate entities for the E-R diagram, but we are only going to include the four most important ones to keep this assignment manageable; however, likely, an insurance company will not survive without properly managing all the other entities.
  • We call the 4 entities above main entities because they are the most important ones in the context of the problem: Customer, Car, Accident, and Employee.
  • Let’s discuss the attributes of the main entities:
    • Customer: ID, Name, Address, Email, Status (Active, Inactive, Suspended, etc.), and so on.
    • Car: ID, Make, Model, Year, Color, and so on.
    • Accident: ID, Date, Location, Customer, Car, and so on.
    • Employee: ID, Name, Address, Email, and so on.
  • Now let’s discuss the relationships between these main entities:
    • A customer can own one or more cars, and a car can be owned by one and only one customer.
    • Each car can be involved in zero or more accidents, and each accident can involve one or more cars.
    • Each employee gets assigned one or more customers, and each customer can be assigned to one or more employees.
  • The relationship between Accident, Car, and Customer is a many-to-many relationship, which means that we need to create a join table to represent this relationship (Sharma et all, 2010). The join table will have two foreign keys, one for the Car and one for the Customer along with the Accident ID and the primary key will be the combination of the Accident ID, the Car ID, and the Customer ID.
  • The diagram is shown below:

ER Diagram

  • Symbols explanations (SmartDraw, n.d.):

ER Diagram Symbols

References