COIT20247 Database Design And Development

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.

 

Introduction

The report is portraying the database design and implementation of the complete database of CQI. All important CQI features are being shown in the report like database report, queries etc. 

ER Diagram Mapping

Person (FlPersonID, FlPersonName, FlStreet, FlCity, FlPostcode, FlPersonType)

Assessor (FlPersonID, FlQualificationName, FlDateQualified)

Foreign Key (FlPersonID) References Person

Property (FlPropertyID, FlStreet, FlCity, FlPostcode, FlPersonID)

Foreign Key (FlPersonID) References Person

Policy (FlPolicyID, FlEffectiveFromDate, FlPremiumAmount, FlPaidDate, FlIsHomeBuildingPolicy, FlIsHomeContentsPolicy, FlTotalPolicyAmount, FlPropertyID)

Foreign Key (FlPropertyID) References Property

Home_Building (FlPolicyID, FlHouseType, FlYearBuilt, FlIsAlarmFitted, FlHasWindowsLocks, FlInsuredAmount, FlBuildingPremiumAmount)

Foreign Key (FlPolicyID) References Policy

Home_Content (FlPolicyID, FlContentPremiumAmount)

Foreign Key (FlPolicyID) References Policy

Content_Item (FlItemID, FlItemName, FlManufacturer, FlModel, FlPurchasePrice, FlQuantity, FlClaimedStatus, FlPolicyID)

Foreign Key (FlPolicyID) References Policy

Claim (FlClaimID, FlLodgedDate, FlStatus, FlPolicyID, FlPersonID)

Foreign Key (FlPersonID) References Person

Settled_Claim (FlClaimID, FlSettledDate, FlAmountSettled)

Foreign Key (FlClaimID) References Claim

Rejected_Claim (FlClaimID, FlRejectedDate, FlRejectedReason)

Foreign Key (FlClaimID) References Claim

Items_Claimed (FlClaimID, FlItemID, FlItemName, FlQuantity, FlItemStatus)

Foreign Key (FlClaimID) References Claim

Foreign Key (FlItemID) References Content_Item

Assessment_Report (FlReportID, FlReportDate, FlInitialRecommendation, FlReportType, FlClaimID, FlPersonID)

Foreign Key (FlClaimID) References Claim

Foreign Key (FlPersonID) References Assessor

Final_Assessment_Report (FlReportID, FlFinalAssessmentDate, FlTotalAmount, FlCostReportMemo)

Foreign Key (FlReportID) References Assessment_Report

Assessor Table

Assessor (FlPersonID, FlQualificationName, FlDateQualified)

FlQualificationName àFlPersonID

FlDateQualified àFlPersonID

The Fields of the assessor table are depending upon the Person ID field only that is playing the role of primary key in the assessor table.

ItemsClaimed

ItemsClaimed (FlClaimID, FlItemID, FlItemName, FlQuantity, FlStatus)

FlItemName à FlClaimID, FlItemID

FlQuantity àFlClaimID, FlItemID

FlStatus à FlClaimID, FlItemID

The Fields of the Items Claimed table are depending upon the Claim ID field only that is playing the role of primary key in the Items Claimed table.

Normalisation

The normalized database is created in the following manner-

• Each table contains only related data e.g. customer table only contains the customer’s data.
• Make primary key in all the tables.
• No field except primary key should uniquely identify the records in the tables.
• There should be no transitive dependency in any table.

All tables are satisfying the above rules. That is why; the CQI database is in third normal form.

(Easy Computer Academy 2017)

(BigennersBook 2017)

SQL Queries

Query 1

SELECT sq1.Total_No_Home_Policy, sq2.Total_No_Home_Content_Policy FROM (SELECT Count(*) AS Total_No_Home_Policy FROM Home_Building)  AS sq1, (SELECT Count(*) AS Total_No_Home_Content_Policy FROM Home_Content)  AS sq2;

Query 2

SELECT PersonID, PersonName, Street, City, Postcode, PersonType FROM PersonWHERE PersonID not in (Select PersonID from Assessment_Report) and PersonType='Assessor';

Query 3

SELECT sq1.Total_No_Settled_Claim, sq2.Total_No_Rejected_Claim FROM (SELECT Count(*) AS Total_No_Settled_Claim FROM Settled_Claim)  AS sq1, (SELECT Count(*) AS Total_No_Rejected_Claim FROM Rejected_Claim)  AS sq2;

Query 4

SELECT * FROM Person WHERE PersonID in (Select Property.PersonID FROM Property INNER JOIN (Policy INNER JOIN Claim ON Policy.PolicyID = Claim.PolicyID) ON Property.PropertyID = Policy.PropertyIDWhere Claim.Status='Rejected');

Query 5

SELECT TOP 1 ClaimID, AmountSettled, SettledDate FROM Settled_Claim ORDER BY AmountSettled DESC;

Query 6

SELECT Policy.PolicyID, Policy.IsHomeBuildingPolicy, Policy.IsHomeContentsPolicy, Policy.PremiumAmount FROM PolicyWHERE Policy.IsHomeBuildingPolicy<>0 and Policy.IsHomeContentsPolicy<>0;

Learning Features

These are the features in the assignment that are giving great knowledge-

• How to make database report
• How to use sub queries
• How to use inner joins
• How to use normalization steps

Complex Features

I only faced the problems in the following assignment functions-

1. Query 1 – using of sub query
2. Query 3- using of sub query
3. Making database report

Conclusion

The report is showing all the RDBMS features along with benefits of the assignment. It is showing the knowledge that is being provided by the assignment. The normalization technique is fully described in the report.

References

Easy Computer Academy (2017), Database Normalization Explained in Simple English, Retrieved from https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/

BigennersBook (2017), Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database: Foreign Keys, Retrieved from:

https://beginnersbook.com/2015/05/normalization-in-dbms/