CQI Insurance Database: ER Diagram, Logical Design & 3NF Relations

This report outlines the database design for CQ Insurance (CQI), which offers home-buildings and home-contents insurance policies. The design includes an Entity Relationship (ER) diagram representing entities like Customer, Policy, Claim, Assessor, and Payment, along with their relationships. Business rules are defined, such as customers purchasing only one policy at a time and paying full premium for one-year coverage. Assumptions are made regarding policy changes and payment flexibility. The logical design translates the ER diagram into 3NF relations, including tables for Assessor, Assessments, Claim, ClaimItems, Customer, CustomerItems, InitialAssessment, FinalAssessment, Payment, PolicyType, HomeBuilding, HomeContent, HomeBuildingContent and Policies, ensuring data integrity and minimizing redundancy. This comprehensive design supports CQI's operations and data management needs. Desklib provides this document and many other solved assignments for students.

COVER PAGE

Contents
1 Entity Relationship diagram......................................................................................................................3
2 Business rules...........................................................................................................................................3
3 Assumptions.............................................................................................................................................4
4 Justifications.............................................................................................................................................4
5 Logical Design...........................................................................................................................................4

1 Entity Relationship diagram
Figure 1: entity relationship diagram
2 Business rules
CQI operates within the following business rules as defined in the case study;

 CQI sells three types of insurance covers to its customers; home-buildings policy, home-contents
policy, combined home-buildings- contents policy
 For a customer to be a member he or she has to purchase one type of policy and CQI collects
customer information for future communication.
 A customer can only purchase one policy at a time
 Upon purchasing a policy the customer must pay the full premium for the respective policy
before the customer is covered for a one year.
 CQI has to collect details of the property that the customer wants to insure at the time the
customer is purchasing a policy.
 When a customer lodges a claim, CQI has to confirm that the customer is still covered by their
policy by verifying the expiry date of the policy. CQI also verifies that the items being claimed by
the customer are covered by the policy.
 A claim is related to only one policy.
 A claim can have more than one item as long as it is covered by the policy of the customer.
 An assessor performs two types of assessments for a claim; initial assessment and final
assessment.
3 Assumptions
To back up the case study, the following assumptions were made
 To change from one policy to another, the customer has to buy and pay for the other policy.
 The customer can make more than one payment for a single policy in the case where the
customer wants to extend the validity of the policy
4 Justifications
The assumptions are justified because;
 The customer has to buy another policy when switching from one policy to another because a
customer is only supposed to have one policy at a time.
 The customer can make more than payment for a single policy because to extend the validity of
a policy for example with one year, the customer needs to pay the required amount and the
expiry date of the policy is pushed ahead by another year. This will solve the issue of having to
purchase a policy every time the current one expires.
5 Logical Design
The following relations have been derived from the entity relationship diagram and are in 3NF because
all repeating groups have been eliminated, no partial dependencies exist and all transitive dependencies
have been removed.
 Assessor (assessorID, email,name , phone)
 Assessments (assessmentID, type, claimID, assessorID) foreign key (claimID) references claims
(claimID), foreign key (assessorID) references assessors (assessorID).

 Claim (claimID,policyID, claimDate, description) Foreign key (policyID) references policies
(policyID)
 claimItems (claimID, itemID) foreign key (claimID) references claims (claimID), foreign key
(itemID) references tems (itemID)
 Customer (customerID, name, phone, email, address)
 CustomerItems (itemID, customerID, itemName, type) Foreign key (customerID) references
customer (customerID)
 IntialAssessment(assessmentID, recommendation) Foreign key (assessmentID) references
assessments (assessmentID)
 finalAssessment(assessmentID, totalAmount,memo) Foreign key (assessmentID) references
assessments (assessmentID)
 Payment (paymentID, customerID, policyID, amount, paymentDate) Foreign key (customerID)
references customer (customerID), Foreign key (policyID) references policies (policyID)
 Policytype (policyTypeID, type )
 homeBuilding (policyTypeID,details) ), Foreign key (policyTypeID) references policytype
(policyTpeID)
 homeContent (policyTypeID,details) ), Foreign key (policyTypeID) references policytype
(policyTpeID)
 homeBuildingContent (policyTypeID,details) ), Foreign key (policyTypeID) references policytype
(policyTpeID)
 policies (policyID, customerID, policyTypeID, type, date, expiryDate) Foreign key (customerID)
references customer (customerID), Foreign key (policyTypeID) references policytype
(policyTpeID)