COIT20247: Database Design and Development - CQI Implementation Report

This report provides a comprehensive overview of a CQI (Continuous Quality Improvement) database design and implementation. It begins with an introduction outlining the report's scope, followed by an ER (Entity-Relationship) diagram illustrating the database structure, including entities like Person, Property, Policy, Claim, and their relationships. The report details functional dependencies within the database tables, ensuring data integrity and consistency. Normalization principles are applied to optimize the database design. Several SQL queries are presented to retrieve specific data, such as counting home policies and content policies, finding assessors not in assessment reports, comparing settled and rejected claims, retrieving claims with rejected status, finding the highest settled claim, and selecting policies with both home building and content coverage. The report also includes a claim summary report and concludes with a summary of key learning features, including database queries, reports, normalization, and functional dependencies, along with complex features like subqueries and inner joins. References to relevant resources are provided.

Student ID:
Tutor:

Implementation Report
Introduction
The report is showing every point and feature of CQI in brief. All points are
completed that are given into the assignment and the report is summarising all the
points like ER Mapping, functional dependencies, queries, database report etc.
Normalization
ER Diagram Mapping
Person (PersonID, PersonName, Street, City, Postcode, PersonType)
Assessor (PersonID, QualificationName, DateQualified)
Foreign Key (PersonID) References Person
Property (PropertyID, Street, City, Postcode, PersonID)
Foreign Key (PersonID) References Person
Policy (PolicyID, EffectiveFromDate, PremiumAmount, PaidDate,
IsHomeBuildingPolicy, IsHomeContentsPolicy, TotalPolicyAmount, PropertyID)
Foreign Key (PropertyID) References Property
Home_Building (PolicyID, HouseType, YearBuilt, IsAlarmFitted,
HasWindowsLocks, InsuredAmount, BuildingPremiumAmount)
Foreign Key (PolicyID) References Policy
Home_Content (PolicyID, ContentPremiumAmount)
2 | P a g e

Foreign Key (PolicyID) References Policy
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchasePrice, Quantity,
ClaimedStatus, PolicyID)
Foreign Key (PolicyID) References Policy
Claim (ClaimID, LodgedDate, Status, PolicyID, PersonID)
Foreign Key (PersonID) References Person
Settled_Claim (ClaimID, SettledDate, AmountSettled)
Foreign Key (ClaimID) References Claim
Rejected_Claim (ClaimID, RejectedDate, RejectedReason)
Foreign Key (ClaimID) References Claim
Items_Claimed (ClaimID, ItemID, ItemName, Quantity, ItemStatus)
Foreign Key (ClaimID) References Claim
Foreign Key (ItemID) References Content_Item
Assessment_Report (ReportID, ReportDate, InitialRecommendation, ReportType,
ClaimID, PersonID)
Foreign Key (ClaimID) References Claim
Foreign Key (PersonID) References Assessor
Final_Assessment_Report (ReportID, FinalAssessmentDate, TotalAmount,
CostReportMemo)
Foreign Key (ReportID) References Assessment_Report
3 | P a g e

Functional Dependency in Tables
Claim
Claim (ClaimID, LodgedDate, Status, PolicyID, PersonID)
LodgedDate  ClaimID
Status  ClaimID
PolicyID  ClaimID
PersonID  ClaimID
Claim ID is working as a primary key for the Claim table.
Property
Property (PropertyID, Street, City, Postcode, PersonID)
Street PropertyID
City PropertyID
Postcode PropertyID
PersonID PropertyID
Property ID is working as a primary key for the Property table.
(Janalta Interactive Inc. 2015)
Normalisation
The CQI database is built by mapping the given ER Diagram to the Relational
database and by applying the following normalization rules-
- Make separate tables for specific data with primary keys.
- All fields should depend upon the primary key of the table.
- No transitive dependency in any table.
(W3schools.in n.d.)
4 | P a g e

SQL Queries
Query 1
SELECT SubHome.Home_Policy, subContent.Home_Content_Policy FROM (SELECT Count(*)
AS Home_Policy FROM Home_Building) AS SubHome, (SELECT Count(*) AS
Home_Content_Policy FROM Home_Content) AS subContent;
Query 2
SELECT Person.PersonID, Person.PersonName, Person.Street, Person.City, Person.Postcode,
Person.PersonType FROM Person WHERE Person.PersonID not in (Select PersonID from
Assessment_Report) and Person.PersonType='Assessor';
Query 3
SELECT subSettle.Settled_Claim, subReject.Rejected_Claim FROM (SELECT Count(*) AS
Settled_Claim FROM Settled_Claim) AS subSettle, (SELECT Count(*) AS Rejected_Claim
FROM Rejected_Claim) AS subReject;
5 | P a g e

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.PropertyID Where 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 AS ['Total Premium Amount'] FROM Policy WHERE
Policy.IsHomeBuildingPolicy<>0 and Policy.IsHomeContentsPolicy<>0;
6 | P a g e

Claim Summary Report
Learning Features
These are the learning points in the assignment-
- Database queries
- Database report
- Database normalization
- Functional dependencies
Complex Features
These are the learning points in the assignment-
- Sub queries
- Inner joins
Conclusion
The report is the complete package of knowledge about normalization in the
Relational Database Management System. All the important features are shown like
functional dependencies, queries etc.
7 | P a g e

References
W3schools.in (n.d.), Database Normalization. [online]. Retrieved from :
https://www.w3schools.in/dbms/database-normalization/. [Accessed 17 May
2018].
Janalta Interactive Inc. (2015). Functional Dependency. [Online]. Retrieved from:
http://www.techopedia.com/definition/19504/functional-dependency.
[Accessed 17 May 2018].
8 | P a g e