Logical Design for CQI Insurance Company

This article presents the logical design for CQI Insurance Company, including an entity relationship diagram, business rules, assumptions, and relations in 3NF. The article discusses the policies provided by CQI, customer details, policy purchases, claims, payments, assessors, and assessments. The article also explains the steps involved in normalizing the relations to 3NF.

COVER PAGE

Contents
1 Entity Relationship diagram......................................................................................................................3
2 Business rules...........................................................................................................................................3
3 Assumptions.............................................................................................................................................4
3.1 Assumption justification....................................................................................................................4
4 Logical Design...........................................................................................................................................4
4.1 Relations in 3NF:................................................................................................................................6
4.1.1 Policy_type.................................................................................................................................6
4.1.2 Customer....................................................................................................................................6
4.1.3 Customer_policy.........................................................................................................................6
4.1.4 Claim...........................................................................................................................................6
4.1.5 Payment......................................................................................................................................7
4.1.6 claimItems..................................................................................................................................7
4.1.7 Assessor......................................................................................................................................7
4.1.8 Assessments...............................................................................................................................8

1 Entity Relationship diagram
Figure 1: ERD
2 Business rules
The following business rules were derived from rules were derived from the case study to come up with
the entity relationship diagram.
 CQI has three types of insurance policies that can be purchased by customers.
 For a customer to be registered as a member of CQI the customer has to purchase a policy as
the customer details are corrected during the time of purchasing a policy.
 A customer can only purchase one type of policy. The customer has only one policy as a member
of CQI, thus a customer cannot have one or more policies at the same time.
 When the customer buys a policy they have to pay a full premium and the expiry date of the
policy is updated.
 A policy can insure more than one item.

 A customer can make more than one claim before the expiry date of a policy. The expiry date of
a policy is used to determine whether the policy is valid or not.
 A policy purchased by the customer can insure more than one property.
 When a customer makes a claim, the claim is assessed by an assessor. The initial claim can give a
process recommendation or reject recommendation. The final assessment gives the amount to
be settled and a memo with the items.
3 Assumptions
To facilitate the business rules while coming up with the relationship diagram the following assumptions
were made;
 A customer can pay more than once for a policy. Upon paying the full premium for a policy the
customer can pay the second time if he or she wants to renew the policy if it has already
reached its expiry date
3.1 Assumption justification
 Its essential for a customer to pay for a policy for more than once because instead of a customer
having to a buy a new policy every time the previous policy expires, the customer can just pay
for the existing policy to renew and extend the expiry date. By doing this, the customer will not
have to purchase a new policy every time the existing one expires unless the customer wants to
change from one policy to another.
4 Logical Design
The logical design involves coming up with relations that can be implemented as tables in a database
with their corresponding relationships. To perform logical design normalization of the relations gotten in
ERD shown in figure 1 above must be normalized to 3NF.
Normalization to 3NF involves performing the following steps;
1. Converting relations to 1NF- Converting of relations to 1NF involves eliminating all repeating
groups and identifying primary keys for every relation
2. Converting relations to 2NF- this involves elimination of all partial dependencies to make sure
very relation has a candidate key on which all other relations are dependent on.
3. Converting relations to 3NF- this involves taking the relations gotten in 2NF and eliminating all
the transitive dependency to make the key attribute to determine all the non key attributes in
every relation.
At 3NF a detailed ERD can be constructed to show the normalized tables and their keys.

Figure 2: Normalized ERD

4.1 Relations in 3NF:
4.1.1 Policy_type
This relation is used to hold details of the policies that are provided by CQI. Its assumed every policy
comes with the policyTypeID, a name and a description. It has the following attributes;
 policyTypeID
 name
 description
Constraints
 Primary key (policyTypeID)
4.1.2 Customer
This relation is used to hold details of the customer. It’s assumed every customer has a uniquew
customerID, name, phone number, email and an address. The relation has the following attrinutes;
 customerID
 phone
 email
 address
Constraints
 primary key (customerID)
4.1.3 Customer_policy
This relation is used to hold details of the policy purchased by a customer. It’s assumed that every
purchase has date and expiry date.
It has the following attributes
 policyID
 customerID
 policyTypeID
 type
 date
 expiryDate
Constraints
 primary key (policyID)
 Foreign key (customerID) references customer (customerID)
 Foreign key (policyTypeID) references policy_type (policyTpeID)
4.1.4 Claim
The claim relation is used to hold details about a claim made by a customer. Its assumed every claim has
a unique claimID, date and details about the claim.
Attributes;

 claimID
 policyID
 claimDate
 details
Constraints
 primary key (claimID)
 Foreign key (policyID) references pcustomer_policy (policyID)
4.1.5 Payment
This relation holds details about the payments made by customers for their policy purchases. Its
assumed that every payment has an amount and date of payment.
Attributes;
 paymentID
 customerOD
 policyID
 amount
 paymentDate
Cosntraints
 Primary key (paymentID)
 Foreign key (customerID) references customer (customerID), Foreign key (policyID) references
pcustomer_policy (policyID)
 CustomerItems (itemID, customerID, itemName, type) Foreign key (customerID) references
customer (customerID)
4.1.6 claimItems
This relation holds details about the items that are being claimed for compensation in a certain claim. Its
assumed a claim can have one or more items.
Attributes;
 claimID
 itemID
Constraints;
 Primary key (claimID, itemID)
 foreign key (claimID) references claims (claimID)
 foreign key (itemID) references customeritems (itemID)
4.1.7 Assessor
This relation holds details of assessors. Its assumed every assessor has name and a phone number.
Attributes
 assessorID

 name
 phone
Constraints
 Primary key (assessorID)
4.1.8 Assessments
This relation is used to hold details of the assessments done for a certain claim. Its assumed an
assessment can either be an initial or final assessment.
Attributes
 assessmentID
 type
 recommendation
 amount
 claimID
 assessorID
Constraints
 Primary key (assessmentID)
 foreign key (claimID) references claims (claimID)
 Foreign key (assessorID) references assessors (assessorID).