COIT20247 Database Design and Development (T12018) - Assignment 2 Implementation Report

This report covers the implementation of the database design for CQI, including normalization, ER diagram mapping, functional dependency, SQL queries, and learning features. The database is implemented in third normal form and all tables have separate data, primary keys, and fields depending upon the primary key only. The report also includes complex features such as sub queries and database reports.

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/