Coit20247 Database Design And The Assessment Answers

Even though a property can be owned by many persons, it is assumed that only one person owns a property. A person’s details may be existing even though the related property details are not available at that time

A person can have many properties.

A policy cannot exist without the property details that it is covering. However, a property’s details may be existing and it may be related to a policy later on.
A policy can be either Home Building policy or Home Content policy or it can be of both.

A Home Content policy can cover many items of different kind/type. A policy can cover more than one item of the same kind. i.e., If there are more than one piece of item of the same kind such as 60” LED LG TV to be covered in the same policy, then quantity value has to be entered appropriately.

Each claim is for one policy only and every claim requires an existing policy. Due to operational reasons, a claim may contain details of some contents other than those mentioned in Home content policy. A claim when processed results into either settled or rejected claim only. If a claim is rejected, then it cannot be settled at all. i.e., combination of settlement of some items and rejection of remaining items from the same claim are not performed in this model.

Each claim is handled by one assessor only.
An assessor may handle many claims but for each claim, he/she submits one initial and one final report only and hence final report is assumed to be an extension of initial report (initial report has been treated as report).

Reason for rejecting a claim may be due to lapsed period or as recommended by the assessor.An assessor is also a person and there could many other type of persons in future. An assessor can have many qualifications.

Total premium amount is the sum of Home Building Premium and Content premium amount for a combined policy.
The amount settled for a claim is as per the final assessment submitted by the assessor.

The details of the recommendation for claim settlement is submitted in the form of memo/report by the assessor.

Answer:

Normalized from of the ERD

Customers (CustomerID, Name, Address, Email)

Properties (PropertyID, Adress, Details, CustomerID)

Policy (PolicyNumber, PolicyDate, Type, PropertyID)

HomeBuildingPolicy (PolicyNumber, Details, StartDate, Duration, EndDate)

HomeContentPolicy (PolicyNumber, Details, StartDate, Duration, EndDate)

Content_Item (ItemID, Itemname, Manufacturer, Model, Purchas Price, Quentity, PolicyNumber)

Assessor (AssessorId, Name, Address, Qualification, DateofQualification)

Claim (ClaimId, claimsDate, AssessorId, PolicyNumber, CustomerNumber, ReportID, ClaimStatusID)

Claim_Status (CalimStatusID, Name)

Settled_Claim (CalimStatusID, SettledDate, AmountSettled, PolicyNumber)

Rejected_Claim (CalimStatusID, RejectedDate, Reason, PolicyNumber)

Report (ReportID, ReportDate, InitialRecomendation, FinalAssesmentDate, TotalCost)

Normalization correctly listed for two of the relations

Assessor (AssessorId, Name, Address, Qualification, DateofQualification)

In the above relationship the AssessorID is the primary key. The assessor name cannot be a unique identifier. Because the two or more assessor can have the same name. like name assessor address, qualification and date of qualification can be same with two or more assessors. But AssessorID is a unique identifier because it cannot be same as other value. All other attributes do not depend on the other attributes. Therefore, on functional dependency is present in the above relationship. So, this relationship in the 1^st, 2^nd, and 3^rd normal form.

Report (ReportID, ReportDate, InitialRecomendation, FinalAssesmentDate, TotalCost)

Every report has a ReportID, report date, initial recommendation, final assessment date, and total cost. All these things can be same as other report but the ReportID cannot be same as other. All other attributes are depending on the ReportID. Therefore, is not any functional dependency in the above relationship. Therefore, it is in 3^rd normal for.

Implementation report

In this assessment I learn many things, two of them are given bellow:

1) Normalization is one of the main interesting thing for me. First I study about the normalization on the internet and I found that I it is more interesting to identified the relation between the entities and attributes.

2) another thing is the database design in MS access. It is very easier than other database technology. It is not so much complex. Also the report (object) is also very interesting.

Bibliography

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Cengage Learning.

Harrington, J. L. (2016). Relational database design and implementation. Morgan Kaufmann.

Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and data warehouses. Prospect Press.

Kanade, A., Gopal, A., & Kanade, S. (2014, February). A study of normalization and embedding in MongoDB. In Advance Computing Conference (IACC), 2014 IEEE International (pp. 416-421). IEEE.

McMinn, P., Wright, C. J., & Kapfhammer, G. M. (2015). An analysis of the effectiveness of different coverage criteria for testing relational database schema integrity constraints. Department of Computer Science, University of Sheffield, Tech. Rep