COIT20247 Database Design And Development 4

Objectives

• To develop a conceptual data model diagram
• To perform logical design (optional)

Assignment Specification

In this assignment, you need to perform the following tasks for the given case study:

• Draw an Entity Relationship (ER) diagram
• Perform logical design (optional)
• Create 3NF Relations (optional)

Case Study

CQ Insurance (CQI) offers insurance policies to insure Home-buildings and Home-Contents of Australian residents.  For each of their properties (Homes) of interest, the customers can apply for either a Home-buildings policy or a Home-contents policy or a combined Home-buildings-contents policy.  CQI allows a customer to have only one of the above mentioned policies for each of his/her properties.

When new customers approach for an insurance policy (hereafter called as policy), CQI collects their details so that they can communicate with them in future. Each policy requires full premium payment prior to the coverage period of one year.  At the time of preparing the policies, CQI collects the details of the property (Home) and/or items (Home contents) depending upon the type of the policy that the customer is interested in.

At the time when a customer lodges an insurance claim (hereafter called as claim) such as collapse of false ceiling, loss of home contents, etc., CQI ensures that the insurance policy has not lapsed prior to processing the claims and further verifies that the items claimed have been covered by the respective policy.  A claim should be related to only one policy but each claim is not limited to only one item that has been covered by the policy.  As part of the claim-processing for each of the claims, an assessor (appointed by CQI) prepares and submits an initial assessment and if required the assessor also prepares and submits a final assessment.  The assessor’s initial assessment may recommend either to further process or reject the related claim. The assessor’s final assessment will provide the total amount that can be settled relating to the claim and may contain a memo that describes the recommended settlement amount for each of the items that have been claimed.

Assignment Requirements

In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionally perform logical design to produce appropriate 3NF Relations.

The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.

Answer:

Business Rules

• Each property belongs to any one of the customer
• Each policy belongs to any one of the policy type (home-buildings, Home-content and etc.)
• Each policy belong to any one of the customer’s property
• Each policy claim belong to any of the customer’s policy
• Each policy claim assessed by any one of the CQ staff (assessor)

Assumptions

• Each CQ Staff belong to any of the staff type (policy agent, assessor and etc.)
• Each policy handled by any one of the CQ staff (policy agent)

ERD

Relational Schema

PolicyType (PolicyTypeID, PolicyTypeName)

Primary Key (PolicyTypeID)

Customer (CustomerID, CustFirstName, CustLastName, Gender, DOB, Phone, Email)

Primary Key (CustomerID)

Property (PropertyID, Location, City, Zip, CustomerID)

Primary Key (PropertyID)

Foreign Key (CustomerID) References Customer (CustomerID)

CQStaffType (CQStaffTypeID, CQStaffTypeName)

Primary Key (CQStaffTypeID)

CQStaff (CQStaffID, CQStaffName, CQStaffTypeID)

Primary Key (CQStaffID)

Foreign Key (CQStaffTypeID) References CQStaffType (CQStaffTypeID)

Policy (PolicyID, PolicyDescription, PolicyTypeID, StartDate, ExpiryDate, PolicyAmount, CustomerID, PropertyID, CQStaffID)

Primary Key (PolicyID)

Foreign Key (PolicyTypeID) References PolicyType (PolicyTypeID)

Foreign Key (CustomerID) References Customer (CustomerID),

Foreign Key (PropertyID) References Property (PropertyID),

Foreign Key (CQStaffID) References CQStaff (CQStaffID)

PolicyClaim (ClaimID, ClaimDate, ClaimDescription, CustomerID, PolicyID, CQStaffID)

Primary Key (ClaimID)

Foreign Key (CustomerID) References Customer (CustomerID)

Foreign Key (CQStaffID) References CQStaff (CQStaffID)

Foreign Key (PolicyID) References Policy (PolicyID)

ClaimAssessment (AssessmentID, AssessmentDate, Memo, ClaimAmount, ClaimID, CQStaffID, IsApproved)

Primary Key (AssessmentID)

Foreign Key (ClaimID) References PolicyClaim (ClaimID)

Foreign Key (CQStaffID) References CQStaff (CQStaffID)

Note:IT Write Up

Underline - Primary Key

Italics - Foreign Key