COIT20247 Database Design and Development: CQI Implementation Report

This report presents the database design and implementation for the CQI system, covering key features such as database reports and SQL queries. It includes an ER diagram illustrating the database structure, along with detailed explanations of normalization techniques to ensure data integrity. The report provides functional dependencies for the Assessor and ItemsClaimed tables. It also showcases SQL queries designed to extract specific information from the database, such as the number of home policies, settled and rejected claims, and specific policy details. The report highlights learning features like database reporting, subqueries, and inner joins, while also addressing challenges encountered during the assignment. The conclusion emphasizes the report's demonstration of RDBMS features and the benefits of the assignment, reinforcing the knowledge gained through the implementation process.

COIT20247 Database Design and Development (T12018)
Assignment 2
5/16/2018
Student ID:
Module Tutor:

Implementation Report
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.
Normalization
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

Functional Dependency
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;

Claim Report
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/