RECENT ASSIGNMENT

Know the quuotation
ENTITY RELATIONSHIP DIAGRAM

• Super Type: Policy, Assessor, Customer
• Sub Type: Homebuilding Policy, Home Content Policy for the super type Policy, Initial Assessment and Final Assessment for the Super type Assessor, and Home and Home Content for the super type customer.
• Phone No is the multivalued attribute represented inside curly brackets.
• Age is the derived attribute and shown using square brackets.
• Assessment entity represent the associativity among the Assessor entity and the Insurance Claim entity.
ASSUMPTIONS AND BUSINESS RULES
• Each customer may have one policy for each of the property.
• Customer may own property which can be home or the home content or the both.
• Each customer applies for none or one and only one policy which can be either Home Building Policy, Home Content Policy or the Combined Home Building Content Policy depending upon the nature of the property the customer own.
• Each policy must have one or many customer.
• Each policy requires a full premium payment before the end of coverage period which can be done once as a full payment or as the installment till the coverage period end. Thus, each policy may have one full premium payment or many partial payment.
• Each payment is for one policy.
• Policy coverage depends on the policy status and the policy period and the policy coverage determines the premium payment as well.
• One Homebuilding Policy covers the insurance for one property which is Home while one Home Content Policy covers the insurance for the Home Content.
• One claim is related to one and only one policy.
• Each claim may consists of one or more items covered by one policy.
• Each customer lodges one or many claims depending on the nature of disaster such as loss or theft of the home content.
• Assessor prepare for one assessment as initial assessment or many assessment containing final assessment as well.
• Initial assessment consists of one recommendation which is either to further process or to reject the claim.
• Each assessor assesses one claim or many insurance claim.
• Many or one insurance claim is assessed by one and only one assessor.
LOGICAL DESIGN
Customer (CustomerId, CustomerName, Address, PhoneNo, DateOfBirth, Age, AnnualIncome)
Policy (PolicyId, PolicyNumber, PolicyEffectiveDate, PolicyExpiryDate, TotalAmount, PolicyStatus, PolicyType, CustomerId)
Foreign key (CustomerId) references Customer (CustomerId)
PremiumPayment (PaymentId, paidDate, paidAmount, paymentMethod, BankName, CardType, CardNumber, CardExpiryDate, PolicyId, CustomerId)
Foreign key (CustomerId) references Customer (CustomerId)
Foreign key (PolicyId) references Policy (PolicyId)
InsuranceClaim (ClaimId, ClaimDate, CustomerId)
Foreign key (CustomerId) references Customer (CustomerId)
PolicyCoverage (CoverageId, CoveragePeriod, PolicyId)
Foreign key (PolicyId) references Policy (PolicyId)
HomeBuildingPolicy (policyId, policyNumber, PolicyEffectiveDate, PolicyExpiryDate, TotalAmount, PolicyStatus, CustomerID, HomeBuildingType)
Foreign key (CustomerId) references Customer (CustomerId)
HomeContentPolicy (policyId, policyNumber, PolicyEffectiveDate, PolicyExpiryDate, TotalAmount, PolicyStatus, CustomerID, HomeContentType)
Foreign key (CustomerId) references Customer (CustomerId)
Home (HomeNo, HomeAddress, HomeMarketValue)
HomeContent (HomeContentId, HomeContentAddress)
Assessor (AssessorId, AssessorName)
Assessment (AssessmentResult)
InitialAssessment (AssessmentRecommendation)
FinalAssessment (TotalAmount, MemoDetails)

ACKNOWLEDGEMENTS
This assignment consisting of the Entity Relationship Diagram, Business rules and Assumptions and the Logical Design is prepared by using the learning materials from the Moodle that has been provided by the COIT20247 Unit which includes the lecture slides and the tutorial guidelines with solution. While the Microsoft word has been used for the preparation of document.