COIT20247 Database Design & Development (T12018) Implementation Report

This report details the implementation of a CQI database, covering aspects such as normalization, ER diagrams, and relational database design. It includes SQL queries with their results and a database report, demonstrating features like functional dependencies and normalization rules. The report outlines the structure of tables like Person, Assessor, Property, Policy, Home_Building, Home_Content, Content_Item, Claim, Settled_Claim, Rejected_Claim, Items_Claimed, Assessment_Report, and Final_Assessment_Report. Functional dependencies within tables like Assessor and Property are explained, along with normalization rules adhering to the third normal form. SQL queries are provided to extract specific data, such as the number of home and content policies, assessor details, settled and rejected claim counts, and policy information. The report concludes by summarizing learning features like database report creation, query construction, and normalization techniques, while also acknowledging complex features like subqueries and database reporting, all resolved through research.

Studfd
COIT20247 Database Design and
Development (T12018)
May 20
Student ID:
Module Tutor:

S
Implementation Report
Introduction
The report is showing everything that is developed in the CQI database. Al queries
with query results and database report are shown in the report very clearly. All
implemented features like normalization rules; functional dependencies etc. are also
being mentioned in the report.
Normalization
ER Diagram Mapping
2 | P a g e

Relational Database
Person (FilddPersonID, FilddPersonName, FilddStreet, FilddCity, FilddPostcode,
FilddPersonType)
Assessor (FilddPersonID, FilddQualificationName, FilddDateQualified)
Foreign Key (FilddPersonID) References Person
Property (FilddPropertyID, FilddStreet, FilddCity, FilddPostcode, FilddPersonID)
Foreign Key (FilddPersonID) References Person
Policy (FilddPolicyID, FilddEffectiveFromDate, FilddPremiumAmount,
FilddPaidDate, FilddIsHomeBuildingPolicy, FilddIsHomeContentsPolicy,
FilddTotalPolicyAmount, FilddPropertyID)
Foreign Key (FilddPropertyID) References Property
Home_Building (FilddPolicyID, FilddHouseType, FilddYearBuilt,
FilddIsAlarmFitted, FilddHasWindowsLocks, FilddInsuredAmount,
FilddBuildingPremiumAmount)
Foreign Key (FilddPolicyID) References Policy
Home_Content (FilddPolicyID, FilddContentPremiumAmount)
Foreign Key (FilddPolicyID) References Policy
Content_Item (FilddItemID, FilddItemName, FilddManufacturer, FilddModel,
FilddPurchasePrice, FilddQuantity, FilddClaimedStatus, FilddPolicyID)
Foreign Key (FilddPolicyID) References Policy
Claim (FilddClaimID, FilddLodgedDate, FilddStatus, FilddPolicyID, FilddPersonID)
Foreign Key (FilddPersonID) References Person
Settled_Claim (FilddClaimID, FilddSettledDate, FilddAmountSettled)
Foreign Key (FilddClaimID) References Claim
Rejected_Claim (FilddClaimID, FilddRejectedDate, FilddRejectedReason)
Foreign Key (FilddClaimID) References Claim
3 | P a g e

Items_Claimed (FilddClaimID, FilddItemID, FilddItemName, FilddQuantity,
FilddItemStatus)
Foreign Key (FilddClaimID) References Claim
Foreign Key (FilddItemID) References Content_Item
Assessment_Report (FilddReportID, FilddReportDate, FilddInitialRecommendation,
FilddReportType, FilddClaimID, FilddPersonID)
Foreign Key (FilddClaimID) References Claim
Foreign Key (FilddPersonID) References Assessor
Final_Assessment_Report (FilddReportID, FilddFinalAssessmentDate,
FilddTotalAmount, FilddCostReportMemo)
Foreign Key (FilddReportID) References Assessment_Report
4 | P a g e

Functional Dependency in Tables
Assessor
Assessor (FilddPersonID, FilddQualificationName, FilddDateQualified)
FilddQualificationName  FilddPersonID
FilddDateQualified  FilddPersonID
All fields are only depending upon the person id field which is playing primary key
role in the accessor table.
Property
Property (FilddPropertyID, FilddStreet, FilddCity, FilddPostcode, FilddPersonID)
FilddStreet FilddPropertyID
FilddCity FilddPropertyID
FilddPostcode FilddPropertyID
FilddPersonID FilddPropertyID
All fields are only depending upon the property id field which is playing primary key
role in the property table.
Normalisation Rules
These are the rules to be in third normal form-
First Normal Form
- All tables are unique and having unique data.
Second Normal Form
- Each table has primary key.
- All fields should depend upon the primary key only.
Third Normal Form
- Remove transitive dependency from all tables.
(Rdbms.ca n.d.)
(Dwbi.org n.d.)
5 | P a g e

SQL Queries
Query 1
SELECT qryHome.NumOfHomePolicy, qryContent.NumOfHomeContentPolicy FROM (SELECT
Count(*) AS NumOfHomePolicy FROM Home_Building) AS qryHome, (SELECT Count(*) AS
NumOfHomeContentPolicy FROM Home_Content) AS qryContent;
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 qrySettled.NumOfSettledClaim, qryRejected.NumOfRejectedClaim
FROM (SELECT Count(*) AS NumOfSettledClaim FROM Settled_Claim) AS qrySettled, (SELECT
Count(*) AS NumOfRejectedClaim FROM Rejected_Claim) AS qryRejected;
6 | 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;
7 | P a g e

Rejected Claim Summary Report
Learning Features
Learning features of the assignment are as follows-
- Database report making
- Queries making
- Normalization techniques
Complex Features
Complex features of the assignment are as follows-
- Sub query
- Database report
All the above mentioned topics were complex but resolved by doing research.
Conclusion
The report is summarizing all the topics that are necessary for database
implementation in relational database management system. the report is giving full
knowledge about RDBMS.
8 | P a g e

References
Rdbms.ca (n.d.). Database Normalization. Retrieved from:
https://rdbms.ca/database/normalization.html.
Dwbi.org (n.d.), Understanding Database Normalization with easy Examples.
Retrieved from:
https://dwbi.org/database/rdbms/73-database-normalization.
9 | P a g e