ER Diagram and Logical Design for CQI Insurance Database

This project provides a comprehensive database design solution for CQ Insurance (CQI), an insurance company offering home-buildings, home-contents, and combined policies. The solution includes an Entity Relationship (ER) diagram representing the entities and relationships within the insurance business, such as customers, policies, claims, and assessors. The business rules derived from the case study specify that customers must purchase one policy, pay the full premium, and claims are verified against policy expiration. The logical design translates the ER diagram into 3NF relations, defining tables like Policytype, Policies, Customer, Claim, CustomerItems, ClaimItems, Assessor, Assessments, InitialAssessment, Payment, and FinalAssessment, with appropriate primary and foreign keys to ensure data integrity and relationships. Assumptions are made to clarify the assessment process, justifying the need for initial and final assessments to ensure thorough claim verification. Desklib provides access to this and many other solved assignments.

COVER PAGE

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

1 Entity Relationship diagram

2 Business rules
The following business rules were derived from the case study.
 CIQ specializes in selling of three types of insurance policies to its customers. These types of
insurance policies are;
o Home-buildings policy
o Home-contents policy
o Combined home-buildings-contents policy
• For a potential customer to be registered as a member, he or she must purchase one policy. A
customer can only purchase one type of policy at a time meaning there cannot be a time that a
customer has two policies at the same time.
• After purchasing an insurance policy, customers are supposed to pay the full premium amount
before for the insurance to be functional.
• When a customer makes a claim CQI verifies that the policy has not expired.
• The claim made by the customer is only related to one policy.
• The claim made by the customer can cover more than one item among those which that had
been insured by the policy.
• A claim is verified by an assessor. There is the initial claim which has the recommendation and
the final.
3 Assumptions
The following assumptions were made on top of the business rules;
 Foe every claim made, the assessor has to make two assessments: the initial assessment so as to
provide the recommendation and the final assessment which has the details of the total amount
to be settles and a memo of the items that have been claimed
4 Justifications
The assumption made in section 3 above is justified because to smoothen the process of verifying a
claim every claim should undergo through two assessments to make sure that every claimed that is filed
by a customer is fully assessed and verified to determine whether to compensate the customer or not.
5 Logical Design
The following relations have been derived from the entity relationship diagram and are in 3NF
Policytype (policyTypeID, name , details)
policies (policyID, customerID, policyTypeID, type, date, expiryDate)
Foreign key (customerID) references customer (customerID),
Foreign key (policyTypeID) references policytype (policyTpeID)
Customer (customerID, name, phone, email, address)

Claim (claimID,policyID, claimDate, description)
Foreign key (policyID) references policies (policyID)
CustomerItems (itemID, customerID, itemName, type)
Foreign key (customerID) references customer (customerID)
claimItems (claimID, itemID)
foreign key (claimID) references claims (claimID),
foreign key (itemID) references tems (itemID)
Assessor (assessorID, email,name , phone)
Assessments (assessmentID, type, claimID, assessorID)
Foreign key (claimID) references claims (claimID),
Foreign key (assessorID) references assessors (assessorID).
IntialAssessment(assessmentID, recommendation)
Foreign key (assessmentID) references assessments (assessmentID)
Payment (paymentID, customerID, policyID, amount, paymentDate)
Foreign key (customerID) references customer (customerID),
Foreign key (policyID) references policies (policyID)
finalAssessment(assessmentID, totalAmount,memo)
Foreign key (assessmentID) references assessments (assessmentID)