CQI Insurance System: Data Modeling and ERD - Assignment 1

This assignment presents a solution to a data modeling problem focused on CQI Insurance. It includes an Entity Relationship Diagram (ERD) illustrating the relationships between entities such as policies, customers, claims, and assessors. The solution outlines assumptions and business rules governing the insurance operations, including policy types, purchase conditions, claim processes, and assessment procedures. Furthermore, the ERD is transformed into a logical design, specifying tables with primary and foreign keys to represent the database schema in 3rd Normal Form (3NF). This assignment showcases the process of translating business requirements into a structured database model, essential for efficient data management and application development. Desklib offers a wealth of similar solved assignments and study tools to aid students in their academic pursuits.

COVER PAGE

Contents
1 Entity Relationship Diagram.....................................................................................................................3
2 Assumptions and business rules...............................................................................................................3
3 Logical Design...........................................................................................................................................4

1 Entity Relationship Diagram
Figure 1: Entity relationship diagram
2 Assumptions and business rules
 CQI sells insurance policies to its customers and for a customer to become a member of CQI
insurance, he or she has to purchase one of the insurance policies provided by CQI.
 CQI sells three types of insurance policies to its customers; home-building policy, home-contents
policy and home-building-content insurance policy.
 When a member makes a purchase of a policy, the member has to pay in full the amount of the
premium whether it is per year or per month.
 When a member policy expires, the customer has to purchase a new policy to extend the
validity of the policy insurance.
 A member who has a policy can make more than one claim as long as the expiry date of the
policy has not reached.
 The claim made by a customer is only related one policy because a member is not supposed to
have more than policy at the same time.
 When a claim is made by a customer, two types of assessments are done for the claim by an
assessor; initial assessment and final assessment. When the initial assessment is done the
assessor has to give a recommendation on whether to process the claim further or to reject it. If
the initial claim recommends to process the claim further, final assessment is done on the

assessment which gives the total amount to be settled and may contain a memo of the items
that are to be covered by the claim.
3 Logical Design
Policies (policyID, type,description)
policyPurchase (purchaseID, datePurchased, time, expiryDate, policyID, customerID) foreign key
(policyID) references policies (policyID), foreign key (customerID) references customer (customerID)
customer (customerID ,name ,email ,address , phone)
payment (paymentID, amount, paymentDate, modeOfPayment, status, purchaseID) foreign key
(purchaseID) references policyPurchase (purchaseID)
customeritems (itemID, itemName, itemType, status, note, customerID) foreign key (customerID)
references customer (customerID)
claim (claimID,dateClaimed, reason, dateFiled, status, customerID) foreign key (customerID) references
customer (customerID)
claimItems (claimID, itemID) foreign key (claimID) references claim (claimID), foreign key (itemID)
references item (itemID)
Assessor (assesorID, name, email, hone, postalCode)
Assessment (assessmentID, assessorID, assessmentDate, startTime, endTime, claimID) foreign key
(assessorID) references assessor (assessorID), foreign key (claimID) references claim (calimID)
finalAssessment (assessmentID,amount, memo) foreign key (assessmentID) references assessment
(assessmentID)
initialAssessment (assessmentID, recommendation) foreign key (assessmentID) references assessment
(assessmentID)