Insurance Policies Database Design, Normalization, Implementation Essay.

Tasks in implementing the ER model

1 Normalization

1. Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensurethat your relations meet 3NF. There is no need to show your 
2. Select any two (2) of your relations from the previous step, 1a), and perform the following for each ofthose two relations:
   • List all the functional dependencies exist in the
   • Demonstrate that the relation meets Third Normal Form (3NF).

In implementing the ER model provided, you must complete the following tasks:

• Create all the relations in a Microsoft Access database. Consider each attribute in every tableand make appropriate choices regarding data types & sizes, indexes, required/not required and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements)
• Create relationships as appropriate. Enforce referential integrity for all appropriaterelationships in the database. Apply cascade update and/or delete options wherever necessary.
• Review the default index created by Access for each table. You should ensure that theindexes follow the guidelines given in the unit 
• Populate the database with sample data of your own. You must include sufficient sample data to testyour queries and report.  Please note that the expected result of query questions may depend on the actual sample data

You are required to implement integrity constraints within the database to enforce the following requirements:

• Insured amount should be positive currency and expressed in 2 decimals only.
• Policy date should have appropriate date value.
• Name of persons such as customer and assessor should not be null.
• Each policy should be related to one property/home.

Create queries to answer the following information requests:

Note: Do not use the Access query builder (QBE) to create your queries – you should type the queries manually using SQL view/editor.

The marking process of the queries for information requests may also consider the effectiveness of your SQL statements that have been used in the queries. It is important that your SQL statement must reflect the correct business logic & SQL syntax.  (Use enough sample data so that the output is produced by each and every query).

1. Display the number of policies issued/created for each category of policies.
2. Display the details of assessor(s) who has/have never performed/completed any assessment report.
3. Display the number of settled and rejected claims.
4. Display the details of customer(s) whose claim(s) has/have been rejected.

Note: Amongst many claims by a single customer, even if anyone claim has been rejected you need to display the details of that customer.

5. Display the highest amount of the claim-settlement(s) that has/have been done.

6. Display the total amount of premium paid for each of combined Home-buildings-contents policies.

Even though a property can be owned by many persons, it is assumed that only one person owns a property.  A person’s details may be existing even though the related property details are not available at that time

A person can have many properties.

A policy cannot exist without the property details that it is covering.  However, a property’s details may be existing and it may be related to a policy later on.

A policy can be either Home Building policy or Home Content policy or it can be of both.

A Home Content policy can cover many items of different kind/type. A policy can cover more than one item of the same kind.  i.e., If there are more than one piece of item of the same kind such as 60” LED LG TV to be covered in the same policy, then quantity value has to be entered appropriately.

Each claim is for one policy only and every claim requires an existing policy.  Due to operational reasons, a claim may contain details of some contents other than those mentioned in Home content policy.   A claim when processed results into either settled or rejected claim only.  If a claim is rejected, then it cannot be settled at all.  i.e., combination of settlement of some items and rejection of remaining items from the same claim are not performed in this model.

Each claim is handled by one assessor only.

An assessor may handle many claims but for each claim, he/she submits one initial and one final report only and hence final report is assumed to be an extension of initial report (initial report has been treated as report).

Reason for rejecting a claim may be due to lapsed period or as recommended by the assessor.

An assessor is also a person and there could many other type of persons in future.

An assessor can have many qualifications.

Total premium amount is the sum of Home Building Premium and Content premium amount for a combined policy.

The amount settled for a claim is as per the final assessment submitted by the assessor.

The details of the recommendation for claim settlement is submitted in the form of memo/report by the assessor.

Tasks in implementing the ER model

Title: Insurance Policies Database Design, Normalization And Implementation

Mapping the ERD to a set of relations in Third Normal Form (3NF)

PERSON(PersonID, PersonName,address,city,postcode,persontype)

PROPERTY(PropertyID,Address,city,postcode,PersonID)

CONTENT_ITEM(ItemID,ItemName,Manufacturer,Model,PurchasePrice,Quantity,ClaimedStatus)

HOME_BUILDING(BuildingID,HouseType,YearBuilt,IsAlarmfitted,HasWindowLocks,InsuredAmount,BuildingPremiumAmount)

POLICY(PolicyID,EffectivefromDate,PremiumAmount,PaidDate,IsHomeBuildingPolicy,IsHomeContentPolicy,TotalAmount, BuildingID, ItemID, PropertyID)

ASSESSOR(AssessorID, QualificationName,DateQualified)

ASSESSMENT_REPORT(ReportID,ReportDate,InitialRecommendation,ReportType,FinalAssessementDate,TotalAmount,CostReportMemo, AssessorID)

CLAIM(ClaimID,LogdeDate,Status,ItemName,Quantity,ItemStatus,PolicyID,AssessorID, PersonID)

SETTLED_CLAIM(SettleID,SettledDate,AmountSettled, ClaimID)

REJECTED_CLAIM(RejectID,RejectedDate,RejectedReason, ClaimID)

After mapping the ER diagram into the set of relational tables as shown above two relations which includes the PERSON and PROPERTY are selected to demonstrate that they are fully normalized.

List of functional dependency

PERSON(PersonID, PersonName, address,city, postcode, persontype)

PROPERTY(PropertyID, Address, city, postcode, PersonID)

Relations is in Third Normal Form (3NF)

PERSON

PERSON(PersonID, PersonName, address, city, postcode, persontype)

1. The PersonID is the primary key that is used to identify a person. In this table the PersonID will be associated by only one person name and hence no person with a particular person id will be allowed to record more than one name, however the person will also record one home address, city and postal code details against his person id and also the person type will be either customer or the assessor and hence the table is in first normal form.
2. The primary key is PersonID.This attribute is unique to all other attributes and hence selected as the only candidate key however other attributes including the personName cannot be used since they can represent many different values, therefore the only single value candidate key is the PersonID and it does not allow any form of partial dependency and thus the relation remains in second normal form.
3. PersonName is hardly used in determination of other attributes ,this is because there could be many people who could be sharing same names in the same database also the address, city ,postcode and persontype table attributes cannot be used since no transitive dependency that exists and hence the PERSON table has meet all the first second and third normalization forms.

PROPERTY

PROPERTY(PropertyID, Address, city, postcode, PersonID)

1. The PropertyID is the primary key that is used to identify a property. In this table the PropertyID will be associated by only one property name and hence no property with a particular property id will be allowed to record more than property name , however the property will also keep record of only one home address, city and postal code details against the property id moreover the person id will be referencing the person table and one property will be owned by one person hence only one person id is allowed to be recorded in the property table and therefore it is now in first normal form.
2. The primary key is PropertyID. This attribute is unique in this table and hence it is selected as the only candidate key however other attributes including the property name cannot be used since they can represent many different values, therefore the only single value candidate key is the PropertyID and it does not allow any form of partial dependency and thus the relation remains in second normal form.
3. The property Name is hardly used in determination of other attributes, this is because there could be many properties which shares same names in the same table, also the address, city, postcode and person id table attributes cannot be used since no transitive dependency that exists and hence the PROPERTY table has meet all the first second and third normalization forms.
4. The foreign key is PersonID .This attribute is used to reference and associate a property to a certain owner through referencing the PersonID in the person table, however the foreign key does not accommodate any value that is not recorded in the person table PERSONID attribute, the other attributes like the property name, address, city, postalcode cannot be used as foreign key since they don’t relate with the person table and therefore the table is in third normal form.

In the implementation process of the database the Microsoft access 2010 database tool was used where I was able to learn various processes of implementing the database which included the following.

1. How to create the database and tables.

In the process of the database implementation I was able to understand how to implement the database and how to create new tables and giving them respective names where the appropriate field’s properties were used to enhance the integrity of the database (Ullman 2016).

1. How to create queries and queries.

However I was able to research and understand how to create the queries and also to use the generated queries in creating the claims report as it was required.

In the implementation process the most challenging part was how to create the report from the query but through the research and practices I was able to successfully implement it comfortably.

The below figure show the various steps followed while implementing the database.

1. PERSON table.

PersonID, PersonName,address,city,postcode,persontype

1. PROPERTY table.

(PropertyID,Address,city,postcode,PersonID)

• CONTENT_ITEM table.

(ItemID,ItemName,Manufacturer,Model,PurchasePrice,Quantity,ClaimedStatus)

1. HOME_BUILDING table.

(BuildingID,HouseType,YearBuilt,IsAlarmfitted,HasWindowLocks,InsuredAmount,BuildingPremiumAmount)

1. POLICY table.

(PolicyID,EffectivefromDate,PremiumAmount,PaidDate,IsHomeBuildingPolicy,IsHomeContentPolicy,TotalAmount, BuildingID, ItemID)

1. ASSESSOR

(AssessorID, QualificationName,DateQualified)

• ASSESSMENT_REPORT

(ReportID,ReportDate,InitialRecommendation,ReportType,FinalAssessementDate,TotalAmount,CostReportMemo, AssessorID)

• CLAIM  table.

(ClaimID,LogdeDate,Status,ItemName,Quantity,ItemStatus, PolicyID,AssessorID) 

1. SETTLED_CLAIM table.

(SettleID,SettledDate,AmountSettled, ClaimID)

1. REJECTED_CLAIM table.

(RejectID,RejectedDate,RejectedReason, ClaimID)

1. Database relational diagram

In the implementation of the database the integrity practices were used which included the following:

1. Used of positive values and two decimals in the currency insured amounts.
2. All date’s fields were set to accept only the date values.

• All fields were set not to accept null values.

1. There was established relationship between the various tables including the policy and property table.

Database queries

Display the number of policies issued/created for each category of policies

SQL CODES

SELECT Count(BuildingID) AS HOMEBUILDING_POLICY, Count(ItemID) AS HOME_CONTENT_POLICY

FROM POLICY;

SCREEN SHOT

SELECT ASSESSOR.Names FROM ASSESSOR WHERE ASSESSOR.CompletedAssesment=0; (Bipin 2014).

SELECT COUNT(REJECTED_CLAIM.ClaimID) as REJECTED_CLAIMS , COUNT(SETTLED_CLAIM.ClaimID) AS SETTLED_CLAIMS FROM REJECTED_CLAIM, SETTLED_CLAIM;

SELECT PERSON.PersonName, PERSON.address, PERSON.city, PERSON.postcode,REJECTED_CLAIM.PersonIDFROM REJECTED_CLAIM INNER JOIN PERSON ON REJECTED_CLAIM.PersonID = PERSON.PersonID;

SELECT Max([AmountSettled]) AS Highest_Amount_Claim FROM SETTLED_CLAIM INNER JOIN CLAIM ON SETTLED_CLAIM.ClaimID = CLAIM.ClaimID;

Select [Content_Item]![Purchaseprice]+[Home_Building]![Buildingpremiumamount] As Total_Premium_For_Home_Buildings_Contents, Policy.Policyid From Home_Building Inner Join (Content_Item Inner Join Policy On Content_Item.Itemid = Policy.Itemid) On Home_Building.Buildingid = Policy.Buildingid; (Abraham 2013).

Report

Report Query 

SELECT CLAIM.Claimid, CLAIM.Logdedate, POLICY.Policyid, POLICY.Premiumamount, CLAIM.Status, CLAIM.Itemname, CLAIM.Quantity, CLAIM.Itemstatus, PERSON.Personname As Customer_Name FROM (CLAIM INNER JOIN POLICY ON CLAIM.Policyid = POLICY.Policyid) INNER JOIN PERSON ON CLAIM.Personid = PERSON.Personid;

References

Abraham, S.(2013) Database System Concepts .2^nd edn.Michigan:McGraw-Hill.

Bipin, D.(2014) An Introduction to Database Systems.5^th edn.Boston:Addison-Wessley Publication.

Raghu ,R.(2015) Database Management Systems. 3^rd edn.New York:McGraw-Hill Higher Education

Ullman,D.(2016) Principles of Database Systems.1^st edn. Berlin:Springer publishers;