Database Design: ERD, Normalization, Relational Schema for Insurance

This database project presents a comprehensive solution for designing an insurance system database. It begins with an Entity-Relationship Diagram (ERD) to visually represent the database structure, followed by normalization to ensure data integrity and reduce redundancy. The solution defines a relational schema, detailing the tables, attributes, primary keys, and foreign keys. Functional dependencies are analyzed to support the normalization process. The assignment includes SQL create statements for all tables, along with example queries to retrieve and manipulate data. Furthermore, the project covers query implementations to extract specific information from the database. A report query is also provided to generate a customized report. The author reflects on the learning experience, highlighting the creation of candidate keys, validation rules, and complex queries involving multiple tables.

ERD

Normalization
Relational Schema
PersonType (PersonTypeID, PersonTypeName)
Primary Key (PersonTypeID)
Address (AddressID, Street, City, Postcode)
Primary Key (AddressID)
Person (PersonID, PersonName, AddressID, PersonTypeID)
Primary Key (PersonID),
Foreign Key (AddressID) References Address (AddressID),
Foreign Key (PersonTypeID) References PersonType (PersonTypeID)
Property (PropertyID, AddressID, PersonID)
Primary Key (PropertyID),
Foreign Key (AddressID) References Address (AddressID),
Foreign Key (PersonID) References Person (PersonID)
Policy (PolicyID, EffectiveFromDate, PremiumAmount, PaidDate, IsHomeBuildingPolicy,
IsHomeContentsPolicy,PropertyID)
Primary Key (PolicyID),
Foreign Key (PropertyID) References Property (PropertyID)
Home_Content (PolicyID, ContentPremiumAmount)
Primary Key (PolicyID),
Foreign Key (PolicyID) References Policy (PolicyID)
Home_Building (PolicyID, HouseType, YearBuilt, IsAlarmFitted, HasWindowLocks, InsuredAmount,
BuildingPremiumAmount)

Primary Key (PolicyID),
Foreign Key (PolicyID) References Policy (PolicyID)
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchaseDate, Quantity, ClaimedStatus,
PolicyID)
Primary Key (ItemID),
Foreign Key (PolicyID) References Home_Content (PolicyID)
Claim (ClaimID, LodgedDate, Status, PolicyID)
Primary Key (ClaimID),
Foreign Key (PolicyID) References Policy (PolicyID)
ItemsClaimed (ClaimID, ItemID, Quantity, ItemStatus)
Primary Key (ClaimID, ItemID),
Foreign Key (ClaimID) References Claim (ClaimID)
Foreign Key (ItemID) References Content_Item (ItemID)
Settled_Claim (ClaimID, SettledDate, AmountSettled)
Primary Key (ClaimID),
Foreign Key (ClaimID) References Claim (ClaimID)
Rejected_Claim (ClaimID, RejectedDate, RejectedReason)
Primary Key (ClaimID),
Foreign Key(ClaimID) References Claim (ClaimID)
Assessor (PersonID)
Primary Key (PersonID),
Foreign Key(PersonID) References Person (PersonID)

Qualification (QualificationID, QualificationName)
Primary Key (QualificationID)
AssessorQualify (PersonID, QualificationID, DateQualified)
Primary Key (PersonID, QualificationID),
Foreign Key(PersonID) References Assessor (PersonID)
Foreign Key(QualificationID) References Qualification (QualificationID)
Assessment_Report (ReportID, ReportDate, InitialRecommendation, ReportType, PersonID, ClaimID)
Primary Key (ReportID)
Foreign Key (PersonID) References Assessor (PersonID),
Foreign Key (ClaimID) References Claim (ClaimID)
Final_Assessment_Report (ReportID, FinalAssessmentDate,TotalAmount, CostReportMemo)
Primary Key (ReportID)
Foreign Key (ReportID) References Assessment_Report (ReportID)

Functional Dependencies
We take relationship between the Content_Item and Claim
Relation 1
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchaseDate, Quantity, ClaimedStatus)
ItemID is the primary key which identifies the item. It is a unique key. There is no repeating
group. Hence, this relation is in 1NF
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchaseDate, Quantity, ClaimedStatus,
PolicyID)
Other than ItemID may have repeated groups. But Item ID differentiate the other attributes.
PolicyID is the foreign key. There is no partial dependencies. Hence this relation is in 2NF
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchaseDate, Quantity, ClaimedStatus,
PolicyID)
ItemName, Manufacturer, Model may occur more than once. But there are no transitive
dependencies
Relation 2
Claim (ClaimID, LodgedDate, Status)
ClaimID is the primary key which identifies the claim. It is a unique key. There is no repeating
group. Hence, this relation is in 1NF
Claim (ClaimID, LodgedDate, Status, PolicyID)
LodgedDate or status may have repeating data. But differentiate one from other using ClaimID.
PolicyID is the foreign key. There is no partial dependencies. Hence this relation is in 2NF
Claim (ClaimID, LodgedDate, Status, PolicyID)
LodgedDate, Status may occur more than once. But there are no transitive dependencies

The relationship between Claim and Content_Item
Claim
Policy
ClaimedPolicy
1
M N
1

Create Statements
CREATE TABLE PersonType (PersonTypeID INTEGER, PersonTypeName TEXT(30) NOT NULL,
Primary Key (PersonTypeID));
CREATE TABLE Address (AddressID INTEGER, Street TEXT(45) NOT NULL, City TEXT(30) NOT
NULL, Postcode INTEGER NOT NULL, Primary Key (AddressID));
CREATE TABLE Person (PersonID INTEGER, PersonName TEXT(45) NOT NULL, AddressID INTEGER
NOT NULL, PersonTypeID INTEGER NOT NULL, Primary Key (PersonID), Foreign Key(AddressID)
References Address(AddressID), Foreign Key(PersonTypeID) References
PersonType(PersonTypeID));
CREATE TABLE Property (PropertyID INTEGER, AddressID INTEGER NOT NULL, PersonID INTEGER
NOT NULL, Primary Key (PropertyID), Foreign Key(AddressID) References Address(AddressID),
Foreign Key(PersonID) References Person (PersonID));
CREATE TABLE Policy (PolicyID INTEGER, EffectiveFromDate DATE NOT NULL, PremiumAmount
CURRENCY NOT NULL, PaidDate DATE NOT NULL, IsHomeBuildingPolicy YESNO NOT NULL,
IsHomeContentsPolicy YESNO NOT NULL,PropertyID INTEGER NOT NULL, Primary Key (PolicyID),
Foreign Key(PropertyID) References Property (PropertyID));
CREATE TABLE Home_Content (PolicyID INTEGER, ContentPremiumAmount CURRENCY NOT
NULL, Primary Key (PolicyID), Foreign Key(PolicyID) References Policy (PolicyID));
CREATE TABLE Home_Building (PolicyID INTEGER, HouseType TEXT(15) NOT NULL, YearBuilt
INTEGER NOT NULL, IsAlarmFitted YESNO NOT NULL, HasWindowLocks YESNO NOT NULL,
InsuredAmount CURRENCY NOT NULL, BuildingPremiumAmount CURRENCY NOT NULL, Primary
Key (PolicyID), Foreign Key(PolicyID) References Policy (PolicyID));
CREATE TABLE Content_Item (ItemID INTEGER, ItemName TEXT(30) NOT NULL, Manufacturer
TEXT(45) NOT NULL, Model TEXT(15) NOT NULL, PurchaseDate DATE NOT NULL, Quantity
INTEGER NOT NULL, ClaimedStatus TEXT(30) NOT NULL, PolicyID INTEGER NOT NULL, Primary
Key (ItemID), Foreign Key(PolicyID) References Home_Content (PolicyID));

CREATE TABLE Claim (ClaimID INTEGER, LodgedDate DATE NOT NULL, Status TEXT(30) NOT
NULL, PolicyID INTEGER NOT NULL, Primary Key (ClaimID), Foreign Key(PolicyID) References
Policy (PolicyID));
CREATE TABLE ItemsClaimed (ClaimID INTEGER, ItemID INTEGER, Quantity INTEGER NOT NULL,
ItemStatus TEXT(30) NOT NULL, Primary Key (ClaimID, ItemID), Foreign Key(ClaimID) References
Claim (ClaimID),Foreign Key(ItemID) References Content_Item (ItemID));
CREATE TABLE Settled_Claim (ClaimID INTEGER, SettledDate DATE NOT NULL, AmountSettled
CURRENCY NOT NULL, Primary Key (ClaimID), Foreign Key(ClaimID) References Claim
(ClaimID));
CREATE TABLE Rejected_Claim (ClaimID INTEGER, RejectedDate DATE NOT NULL,
RejectedReason TEXT(30) NOT NULL, Primary Key (ClaimID), Foreign Key(ClaimID) References
Claim (ClaimID));
CREATE TABLE Assessor (PersonID INTEGER NOT NULL, Primary Key (PersonID), Foreign
Key(PersonID) References Person (PersonID));
CREATE TABLE Qualification (QualificationID INTEGER, QualificationName TEXT(30) NOT NULL,
Primary Key (QualificationID));
CREATE TABLE AssessorQualify (PersonID INTEGER, QualificationID INTEGER, DateQualified DATE
NOT NULL, Primary Key (PersonID, QualificationID), Foreign Key(PersonID) References Assessor
(PersonID), Foreign Key(QualificationID) References Qualification (QualificationID));
CREATE TABLE Assessment_Report (ReportID INTEGER, ReportDate DATE NOT NULL,
InitialRecommendation TEXT(30) NOT NULL, ReportType TEXT(30) NOT NULL, PersonID INTEGER
NOT NULL, ClaimID INTEGER NOT NULL, Primary Key (ReportID), Foreign Key(PersonID)
References Assessor (PersonID), Foreign Key(ClaimID) References Claim (ClaimID));
CREATE TABLE Final_Assessment_Report (ReportID INTEGER, FinalAssessmentDate DATE NOT
NULL,TotalAmount CURRENCY NOT NULL, CostReportMemo TEXT(100), Primary Key (ReportID),
Foreign Key(ReportID) References Assessment_Report (ReportID));

Query 1:
SELECT SUM(IsHomeBuildingPolicy) AS [Number of Building Policy], SUM(IsHomeContentsPolicy)
AS [Number of Content Policy] FROM Policy;
Query 2:
SELECT * FROM Person WHERE PersonID IN (SELECT PersonID FROM Assessor WHERE PersonID
NOT IN (SELECT PersonID FROM Assessment_Report));
Query 3:
SELECT Status, COUNT(*) AS NumberOfClaim FROM Claim GROUP BY Status;

Query 4:
SELECT * FROM Person WHERE PersonID IN (SELECT PersonID FROM Property WHERE
PropertyID IN (SELECT PropertyID FROM Policy WHERE PolicyID IN (SELECT PolicyID FROM Claim
WHERE Status='R')));
Query 5:
SELECT MAX(AmountSettled) AS HighestSettledClaim FROM Settled_Claim;
Query 6:
SELECT p.PolicyID, (hc.ContentPremiumAmount+hb.BuildingPremiumAmount) AS [Total Amount
of Premium Paid] FROM Policy p, Home_Content hc, Home_Building hb WHERE
p.PolicyID=hc.PolicyID AND p.PolicyID=hb.PolicyID;

Report
Query:
SELECT p.PropertyID, adr.Street, adr.City, adr.Postcode, py.PolicyID, py.EffectiveFromDate,
py.PremiumAmount, c.LodgedDate FROM Property AS p, Policy AS py, Claim AS c, Address AS
adr WHERE p.PropertyID=py.PropertyID AND py.PolicyID=c.PolicyID AND
p.AddressID=adr.AddressID AND c.Status='R' ORDER BY c.LodgedDate;
Implementation Report
Learn from this project
1. I have learnt how to create the candidate key (more than one primary key in a table)
2. Create the validity rule for particular field of the table (quantity > 0 in content_item table)
3. Create the query using more than two tables
Complexity for this project from my experience in this project
 Create the report from customized query