Insurance Policies Database Design, Normalization And Implementation

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;

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order