databasedev.co.uk - database solutions and downloads for microsoft access

Motor Vehicle Insurance Policy Management Scenario

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 cancelled 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.

The following data model allows these questions to be answered and allows the information contained above to be stored logically and in a structured manner.

To view the model please go to: Relationships for Motor Insurance Policy Management Data Model

Please download the data model relating to Relationships for Motor Vehicle Insurance Policy Management which is contained in the zipped up Microsoft Word document.