COIT20247 Database Design and Development Implementation Report

This report covers the implementation of CQI database, including normalization rules, ER diagram mapping, functional dependencies, and SQL queries. It also includes a summary of the learning and complex features of the assignment.

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