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