Logical Design for CQI Insurance Case Study

This article presents the logical design for CQI Insurance Case Study, including an entity relationship diagram, business rules, assumptions, and justifications. The article also provides a list of relations derived from the entity relationship diagram, which are in 3NF.

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)