COIT20247 Database Design and Development: Implementation Report

This report provides a comprehensive overview of the database design and implementation for CQI, covering key aspects such as normalization to the third normal form (3NF), ER diagrams, and SQL queries. The report includes a mapping of the ERD into a set of relations, justification for functional de...Read More

COIT20247 Database Design and Development (T12018)
Assessment Item 2 – Assignment 2
Student ID:
Module Tutor:

Implementation Report
Introduction
The report is totally based upon the database design and implementation. The database for CQI is
implemented in the assignment and the report is giving complete knowledge about it.
Normalization
ER Diagram Mapping
Person (FlddPersonID, FlddPersonName, FlddStreet, FlddCity, FlddPostcode, FlddPersonType)
Assessor (FlddPersonID, FlddQualificationName, FlddDateQualified)
Foreign Key (FlddPersonID) References Person
Property (FlddPropertyID, FlddStreet, FlddCity, FlddPostcode, FlddPersonID)
Foreign Key (FlddPersonID) References Person
Policy (FlddPolicyID, FlddEffectiveFromDate, FlddPremiumAmount, FlddPaidDate,
FlddIsHomeBuildingPolicy, FlddIsHomeContentsPolicy, FlddTotalPolicyAmount,
FlddPropertyID)

Foreign Key (FlddPropertyID) References Property
Home_Building (FlddPolicyID, FlddHouseType, FlddYearBuilt, FlddIsAlarmFitted,
FlddHasWindowsLocks, FlddInsuredAmount, FlddBuildingPremiumAmount)
Foreign Key (FlddPolicyID) References Policy
Home_Content (FlddPolicyID, FlddContentPremiumAmount)
Foreign Key (FlddPolicyID) References Policy
Content_Item (FlddItemID, FlddItemName, FlddManufacturer, FlddModel, FlddPurchasePrice,
FlddQuantity, FlddClaimedStatus, FlddPolicyID)
Foreign Key (FlddPolicyID) References Policy
Claim (FlddClaimID, FlddLodgedDate, FlddStatus, FlddPolicyID, FlddPersonID)
Foreign Key (FlddPersonID) References Person
Settled_Claim (FlddClaimID, FlddSettledDate, FlddAmountSettled)
Foreign Key (FlddClaimID) References Claim
Rejected_Claim (FlddClaimID, FlddRejectedDate, FlddRejectedReason)
Foreign Key (FlddClaimID) References Claim
Items_Claimed (FlddClaimID, FlddItemID, FlddItemName, FlddQuantity, FlddItemStatus)
Foreign Key (FlddClaimID) References Claim
Foreign Key (FlddItemID) References Content_Item
Assessment_Report (FlddReportID, FlddReportDate, FlddInitialRecommendation,
FlddReportType, FlddClaimID, FlddPersonID)
Foreign Key (FlddClaimID) References Claim
Foreign Key (FlddPersonID) References Assessor
Final_Assessment_Report (FlddReportID, FlddFinalAssessmentDate, FlddTotalAmount,
FlddCostReportMemo)
Foreign Key (FlddReportID) References Assessment_Report

Functional Dependency
Assessor Table
Assessor (FlddPersonID, FlddQualificationName, FlddDateQualified)
FlddQualificationName FlddPersonID
FlddDateQualified FlddPersonID
All fields are depending upon the Person ID field only.
ItemsClaimed
ItemsClaimed (FlddClaimID, FlddItemID, FlddItemName, FlddQuantity, FlddStatus)
FlddItemName  FlddClaimID, FlddItemID
FlddQuantity FlddClaimID, FlddItemID
FlddStatus  FlddClaimID, FlddItemID
All fields are depending upon the Claim ID field only.
Normalisation
The database of CQI is implemented in third normal form-
1. All tables are having separate data.
2. All are having primary keys.
3. All fields are depending upon the primary key only.
4. No table should have transitive dependency.
The complete database is satisfying the above defined rules.
(Brombarg P. n.d.)
(BeginnersBook 2017)

SQL Queries
Query 1
SELECT QuerySub1.HomePolicyNumber, QuerySub2.HomeContentPolicyNumber FROM (SELECT
Count(*) AS HomePolicyNumber FROM Home_Building) AS QuerySub1, (SELECT Count(*) AS
HomeContentPolicyNumber FROM Home_Content) AS QuerySub2;
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 QuerySub1.SettledClaimNumber, QuerySub2.RejectedClaimNumber FROM (SELECT
Count(*) AS SettledClaimNumber FROM Settled_Claim) AS QuerySub1, (SELECT Count(*) AS
RejectedClaimNumber FROM Rejected_Claim) AS QuerySub2;
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
There are so many learning points in the assignment like below-
- Database normalization
- SQL queries
- Database reports
All features are very important and very the basic of the RDBMS.
Complex Features
Following features in the assignment have some complexity-
- Sub queries
- Database report
But I understood the concept and completed the task successfully.
Conclusion
The report is showing all the important features that are necessary in the RDBMS like database
normalization, query concept etc. All the features are implemented in the assignment very
carefully and the report is demonstrating all the features.

References
Brombarg P. (n.d.), SQL SERVER Database Normalization Basics for Developers, Retrieved
from: http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-
developers.aspx /
BeginnersBook (2017), Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database:
Foreign Keys, Retrieved from:
https://beginnersbook.com/2015/05/normalization-in-dbms/