Home > Uncategorized > Sample Relational Database Model * Motor Vehicle Insurance Policy Management Scenario

Sample Relational Database Model * Motor Vehicle Insurance Policy Management Scenario

I have been asked by some people interested in SQL and its design to explain a relational database model. I will try to do that here.

The following data model is designed to hold information relating to Motor Vehicle Insurance Policies. For this scenario we need to define the following facts:

These facts define the requirements which the Database must meet and should be agreed between the Database User and the Database Designer prior to physical creation.

An insurance company writes policies for drivers. One policy can cover many drivers and also many vehicles, but a vehicle can be related to only one policy. Drivers can share one or more vehicles (e.g. a husband and wife own one vehicle and they both drive the same vehicle or a family can have multiple vehicles).

The company gets a master list of violations from the Department of Motor Vehicles. These violations are then input into the system and used to determine the price of the policy. A driver may commit more than one violation. One or more drivers can commit the same violation. The system should keep a track of all customers – active (with insurance) and inactive (held in an archive – for canceled customers). All customers should be able to get a quote, insurance or cancel the insurance.

The draft facts have been defined as:

The Entities required should include:

  • Drivers
  • Vehicles
  • Policies
  • Insurance Groups
  • Violations
  • Link_VehiclesDrivers
  • Link_ViolationsDrivers

The Entities are related as follows:

  • The relationship between the tblVehicles and tblDrivers tables is Many-To-Many (a vehicle may be driven by one or more drivers; a driver may drive one or more vehicles), so a link table should be created (e.g. tblLink_VechiclesDrivers).
  • The relationship between the tblVehicles and tblInsuranceGroups tables is One-To-Many (a vehicle may belong to only one insurance group; many vehicles can belong to the same or different insurance groups).
  • The relationship between the tblViolations and tblDrivers tables is Many-To-Many (a driver may commit one or more violations; a violation may be commited by one or more drivers), so a link table should be created (e.g. tblLink_ViolationsDrivers).
  • The relationship between the tblPolices and tblVehicles is tables is One-To-Many (a policy can cover one or more vehicles; a vehicle can be covered and related to only one policy).

When asking questions of the database we may need to know:

  1. How many violations has Driver ‘X’ had
  2. Has Driver ‘X’ previously been insured with us
  3. What insurance group is [car type here]
  4. When does Driver ‘X’s Policy run out.

Lets examine what the table structure might look like:

motor_policy_data_model

Advertisements
Categories: Uncategorized
  1. besho
    January 15, 2013 at 5:23 pm

    where the answer of 4 questions

    • jwilliamsoh
      January 26, 2014 at 4:31 pm

      ?? I am sorry I don’t know what you are referring to. I just saw this and see its year old question so you probably don’t know either. Sorry

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: