Motor Schema

Motor Actuarial DB Model
Pilot Project

V1.0.0
Open Underwriter
Mark Gaywood, Dick Anderson

Entity Relationship Diagram
ERD Motor

Name Value
Name ERD Ghana Motor
Data Model Logical

Summary

 
Name Documentation
verVersion

With this table we will track versions of Schema, alterations and static data

bodENBodyType

Vehicle Body Type

covENCover

Cover type

insENInsuredType

Insured Type

vehVehicle

Vehicle Details

makENMake

Vehicle Make Type

cascadeMakeModel

Relationship between Vehicle Make and Model

modENModel

Vehicle Model Type

souENSoundSystem

Vehicle Sound System Type

ccyENCurrency

Currency Type

polPolicy

Main Policy Document

parENParked

Where Vehicle Parked Type

reaENReason

Reason for uploading Policy Data

claENClaimType

Claim Type

accENAccessories

Vehicle Accessories Type

mdfENModifications

Vehicle Modifications Type

clmClaim

Claim Details

risENRiskCode

Vehicle Risk Code e.g. X.1 , X.4, TAXI-Z405, HIRING CARS Z405, MINIBUS,MAXIBUS, Y3 etc.

weiENWeightType

Unit of Weight

driDriver

Driver Table

accAccident

Accident details

addAddress

Address information

theTheftFire

Third party fire and theft information

natENNationality

Nationality Type

occENOccupation

Occupation Type

couENCountry

Country Type

empENEmploymentType

Employment Type

Documentation

Ghana Motor Data Schema

Details

 
Entity - verVersion

Columns Summary
Name DataType Constraints Nullable Documentation
verOverall varchar(16) Yes

Overall version of the published schema

verSchema int(10) No

this is the schema version

verAlteration int(10) No

this is the most resent alteration that have been applied to the Schema

verStaticData int(10) No

This is the most recent version that applies the schema/alteration

Entity - bodENBodyType

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - covENCover

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - insENInsuredType

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - vehVehicle

Columns Summary

Name DataType Constraints Nullable Documentation
vehID int(11) PKUnique No

Unique Identifier

vehPolicyIDpol int(11) FK ( polPolicy.polID ) Yes
vehIDmak varchar(32) FK ( makENMake.name ) Yes
vehIDmod int(11) FK ( modENModel.modID ) Yes
vehIDbod varchar(32) FK ( bodENBodyType.name ) Yes
vehIDsou varchar(32) FK ( souENSoundSystem.name ) Yes
vehIDpar varchar(32) FK ( parENParked.name ) Yes
vehIDacc varchar(32) FK ( accENAccessories.name ) Yes
vehIDmdf varchar(32) FK ( mdfENModifications.name ) Yes
vehIDrsk varchar(32) FK ( risENRiskCode.name ) Yes
vehAlarmFitted tinyint Yes

Does vehicle have a car alarm fitted

vehBoughtFromNew tinyint Yes

Was vehicle bought from new

vehYearOfManufacture int(4) Yes

Year of vehicle's manufacture

vehCapacitiyVolume decimal(10, 3) Yes

Vehicle's carrying capacity by volume

vehCommercialVehicle tinyint Yes

Is this a commercial vehicle

vehCompanyVehicle tinyint Yes

Vehicle is owned by a company

vehCoverStart date Yes

When did the cover start

vehCoverEnd date Yes

When does cover end

vehDriverAirBagFitted tinyint Yes

Does the vehicle have a driver's airbag fitted

vehEngineSizeCC int(5) Yes

Vehicle Engine capacity (cc)

vehMilage int(7) Yes

Vehicle's current mileage

vehPassengerAirbagFitted tinyint Yes

Does the vehicle have a passenger's airbag fitted

vehRentalVehicle tinyint Yes

Is vehicle a rental

vehRighthandDrive tinyint Yes

Is the vehicle right-hand drive

vehRoadWorthyCertificate tinyint Yes

Does the vehicle hold a current Road worthy certificate

vehRoadWorthyCertificateExpiry date Yes

Vehicle's current road worthy certificate expiry date

vehSeatingCapacity int(3) Yes

vehicle seating capacity

vehSubjectToLoan tinyint Yes

Is the vehicle currently subject to an outstanding loan agreement

vehToolsStroredDayTime tinyint Yes

are tools stored in the vehicle during the daytime

vehToolsStroredNightTime tinyint Yes

are tools stored in the vehicle during the nighttime

vehTrackingSystemFitted tinyint Yes

is the vehicle fitted with a tracking system

vehTrailer tinyint Yes

is the vehicle used for towing a trailer

vehUsageBusiness tinyint Yes

is the vehicle used for business

vehUsageCarriageOfGoods tinyint Yes

is the vehicle used for the carriage of goods

vehUsageSocialHome tinyint Yes

is the vehicle used for home or socially

vehWhenIsuredTookPossession date Yes

what date did the insure take possession of the vehicle

vehSumInsuredLocalAmount decimal(15, 2) Yes

vehicle sum insured amount in the local currency

vehSumInsuredLocalIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehSumInsuredForeignAmount decimal(15, 2) Yes

vehicle sum insured amount in the foreign currency

vehSumInsuredForeignIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehRiskCurrencyAmount decimal(15, 2) Yes

vehicle risk amount

vehRiskCurrencyIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehPremiumForeignAmount decimal(15, 2) Yes

vehicle foreign premium amount in foreign currency

vehPremiumForeignIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehCurrentEstimateedValueAmount decimal(15, 2) Yes

vehicle's current estimated value

vehCurrentEstimatedValueIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehPremiumLocalAmount decimal(15, 2) Yes

vehicle premium in local currency

vehPremiumLocalIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehValueWhenNewAmount decimal(15, 2) Yes

value of vehicle when new

vehValueWhenNewIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
vehUnladedWeightAmount decimal(10, 2) Yes

vehicle's unladed weight

vehUnladedWeightIDwei varchar(3) FK ( weiENWeightType.name ) Yes
vehMaxLoadWeightAmount decimal(10, 2) Yes

vehicle's maximum loading amount

vehMaxLoadWeightIDwei varchar(3) FK ( weiENWeightType.name ) Yes
vehNightTimeLocationIDadd int(11) FK ( addAddress.addID ) Yes
vehDayTimeLocationIDadd int(11) FK ( addAddress.addID ) Yes

Entity - makENMake

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
makID int(11) Unique No

Entity - cascadeMakeModel

Columns Summary

Name DataType Constraints Nullable Documentation
makID int(11) FK ( makENMake.makID ) No
modlID int(11) FK ( modENModel.modID ) No
validFrom date Yes
validTo date Yes

Entity - modENModel

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) No
modID int(11) Unique No

Entity - souENSoundSystem

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - ccyENCurrency

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(3) Unique No
description int(11) Yes
symbol char(3) Yes
validFrom date Yes
validTo date Yes

Entity - polPolicy

Columns Summary

Name DataType Constraints Nullable Documentation
polID int(11) PKUnique No
polCoverTypeIDcov varchar(32) FK ( covENCover.name ) Yes

Policy cover type e.g Comprehensive

poInsuredTypeIDins varchar(32) FK ( insENInsuredType.name ) Yes

Policy Insured Type e.g. Company, Private

polReasonIDrea varchar(32) FK ( reaENReason.name ) Yes

Business Source e.g. Direct, Marketing, Broker, Agent, Reinsurance, Banc assurance)

polReplaced date Yes

date policy record was replace on system, if null then this is the first instance

polClientID varchar(100) Yes

To be populated by the company uploading the data with a value that helps them relate this record back to their internal systems. This should not hold a value that would allow others (outside of the company supplying the record) to identify the policy. Specifically, this should not be populated with a policy number. This column is not expected to be unique in the staging or master databases. It is expected that records relating to the same policy will have the same polClientID.

polSystemID varchar(100) Yes

Generated by ARDR when records are added to the master database. This is generated using the database's UUID function.

polInception date Yes

policy inception date

polExpiry date Yes

policy expiration date

polLeadInsurer tinyint Yes

is this the lead insurer

polCoInsuranceCover decimal(9, 4) Yes

percentage co - insured

polFacultativeCover decimal(9, 4) Yes

percentage of facultative cover

polQuotaShare decimal(9, 4) Yes

percentage of quote share

polTax decimal(9, 4) Yes

percentage of Tax

polUWYear int(4) Yes

underwriting year or year of account

polGrossPremiumAmount decimal(9, 2) Yes

gross premium amount

polGrossPremiumIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
polDriverIDdri int(11) FK ( driDriver.driID ) Yes

Entity - parENParked

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - reaENReason

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - claENClaimType

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - accENAccessories

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - mdfENModifications

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - clmClaim

Columns Summary

Name DataType Constraints Nullable Documentation
clmID int(11) PKUnique No
clmlPolicyIDpol int(11) FK ( polPolicy.polID ) Yes
clmIDveh int(11) FK ( vehVehicle.vehID ) Yes
clmIDcla varchar(32) FK ( claENClaimType.name ) Yes
clmAmountPaidAmount decimal(12, 2) Yes

amount paid in claim to insured

clmAmountPaidIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
clmLegalFeesAmount decimal(12, 2) Yes

legal fees incurred by claim

clmLegalFeesIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
clmRecoveriesAmount decimal(12, 2) Yes

total recoveries made against claim

clmRecoveriesIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
clmTotalLossIncuredAmount decimal(12, 2) Yes

total loss of claim = paid + legal -recoveries

clmTotalLossIncurredIDccy varchar(3) FK ( ccyENCurrency.name ) Yes
clmIncidentAddressIDadd int(11) FK ( addAddress.addID ) Yes

where incident occurred

clmIncidentMileageAtTime int(10) Yes

main vehicle's mileage at the time of the incident

clmIncidentOccured date Yes

when incident occurred

Entity - risENRiskCode

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - weiENWeightType

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(3) Unique No
validFrom date Yes
validTo date Yes

Entity - driDriver

Columns Summary

Name DataType Constraints Nullable Documentation
driID int(11) PKUnique No
driAverageAnnualMilage int(10) Yes

Average annual mileage

driLicenceFirstIssued date Yes

Date licence first issued

driMainDriver tinyint Yes

Is this the main driver

driPassedDrivingTest date Yes

Date driving test passed

driVehicleOwner tinyint Yes

Is this the vehicle owner

driYearsLicenceHeld int(2) Yes

How many years has the licence been held

driDateOfBirth date Yes

Date of Birth

driDefectiveHearing tinyint Yes

Does driver have defective hearing

driDefectiveVision tinyint Yes

Does driver have defective vision

driMale tinyint Yes

Is driver male

driLastCriminalOffence date Yes

Date of last criminal offence

driLastDrivingOffence date Yes

Date of last driving offence

driLastMotorRelatedAccident date Yes

Date of last motor related accident

driLastMotorRelatedClaim date Yes

Date of last motor related claim

driMotorInsuranceCancelledByInsurer tinyint Yes

Previously has motor insurance been cancelled by insurer

driMotorInsuranceConditionsAdded tinyint Yes

Previously have condition been added by insurer to the motor insurance

driMotorInsuranceRefusedOnRenewal tinyint Yes

Previously has motor insurance been refused on renewal by insurer

driNumberOfCriminalOffences int(2) Yes

Number of criminal offences

driNumberOfDrivingOffenceConvictions int(2) Yes

Number of driving offence convictions

driNumberOfMotorRelatedAccidents int(2) Yes Number of motor related accidents
driNumberOfMotorRelatedClaims int(2) Yes

Number of motor related claims

driPreviouslyDeclineMotorInsurance tinyint Yes

Has been previously declined motor insurance

driPreviousNCD tinyint Yes

Previous no claims discount

driRequiredIncreasePremium tinyint Yes

Has there been the need to increase the premium

driSufferedFits tinyint Yes

Does the driver suffer from fits

driCurrentLicenceHeld tinyint Yes

Does driver hold a current driving licence

driLicencedIssuedIDcou varchar(32) FK ( couENCountry.country ) Yes

Country licence issued

driNatioinalityIDnat varchar(32) FK ( natENNationality.name ) Yes

Driver nationality

driOccuptationIDocc varchar(32) FK ( occENOccupation.name ) Yes

Driver occupatioin

driEmploymentStatusIDemp varchar(32) FK ( empENEmploymentType.name ) Yes

Drivers employment status

Entity - accAccident

Columns Summary

Name DataType Constraints Nullable Documentation
accID int(11) PKUnique No
accCarryingGoods tinyint Yes

Were good being carried at time of accident

accDamageToThirdParty tinyint Yes

Was there damage to a third party

accDriverAirbagDeployed tinyint Yes

Did the driver airbags deploy

addDriverAtTimeOfAccidentIDdri int(11) FK ( driDriver.driID ) Yes

Who was the driver at the time of the accident

accDriverLiable tinyint Yes

Is the driver liable for the accident

accInjuryToDriver tinyint Yes

Was there injury to the driver

accInjuryOtherVehicleDrivers tinyint Yes

Was there injury sustained by any other vehicles driver

accInjuryToOtherVehiclePassengers tinyint Yes

Was there any injury sustained by any of the other vehicles passengers

accInjuryToPassengers tinyint Yes

Was there any injury sustained by the other vehicles passengers

accInjuryToThirdParties tinyint Yes

Was there any injury sustained by any of the other third parties

accInsuredDriving tinyint Yes

Was a named insured from the policy driving

accLightsOn tinyint Yes

Were the lights on after light up time

accNumberOfVehiclesInvolved int(3) Yes

How many vehicles were involved in the accident

accPassengerAirbagDeployed tinyint Yes

Did passenger airbag(s) deploy

accPoliceProsecutingDriver tinyint Yes

Are the police prosecuting driver due to this accident

accPoliceRecorded tinyint Yes

Was this accident recorded by the police

accPoliceWitness tinyint Yes

Did the police witness the accident

accSeatBeltsUsed tinyint Yes

Were the seatbelts in use at the time of the accident.

accIDclm int(11) FK ( clmClaim.clmID ) Yes

Entity - addAddress

Columns Summary

Name DataType Constraints Nullable Documentation
addID int(11) PKUnique No
addRoad varchar(100) Yes
addAreaCode varchar(50) Yes

Either Postal code or area

Entity - theTheftFire

Columns Summary

Name DataType Constraints Nullable Documentation
theID int(11) PKUnique No
theAlarmOn tinyint Yes

Was an alarm on at time of theft

theAllWindowsAndDoorsSecured tinyint Yes

Were all windows and doors in the vehicle secured

theAnyToolsInVehicle tinyint Yes

Were any tools or equipment in vehicle at time of theft

theKeysInVehicle tinyint Yes

Were ignition keys in vehicle

theKeysStolenWithVehicle tinyint Yes

Were ignition keys stolen with vehicle

theLockedInGarage tinyint Yes

Was vehicle in a locked garaged prior to theft

theClaimIDclm int(11) Yes
theIDclm int(11) FK ( clmClaim.clmID ) Yes

Entity - natENNationality

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - occENOccupation

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

Entity - couENCountry

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(3) Unique No

ISO 3 character name

country varchar(32) Unique Yes

Full Country name

validFrom date Yes
validTo date Yes

Entity - empENEmploymentType

Columns Summary

Name DataType Constraints Nullable Documentation
name varchar(32) Unique No
validFrom date Yes
validTo date Yes

7 Attachments
1102 Views
Average (0 Votes)
Comments
No comments yet. Be the first.