COIT20247 Database Design and Development (T12018) Assessment Report

This report provides a comprehensive overview of the CQI database, implemented as a normalized database in MS Access. It details the database design, including the Entity-Relationship (ER) diagram, table structures, and functional dependencies. The report covers normalization principles, ensuring data integrity and efficiency. Furthermore, it presents a series of SQL queries designed to extract and manipulate data, demonstrating the database's practical applications. The assignment also highlights the learning features, complexities faced, and provides a conclusion summarizing the key aspects of the CQI database, including normalization, SQL queries, and report generation. The report also references external resources used in the database design and development process.

Assessment Item 2 – Assignment 2
Student ID:
Tutor:

Implementation Report
Introduction
The report is giving complete overview about CQI database that is implemented as
normalized database in MS Access. The CQI database is containing queries, reports
etc. that are very helpful for management.
Normalization
ER Diagram Mapping
Person (FielldPersonID, FielldPersonName, FielldStreet, FielldCity, FielldPostcode,
FielldPersonType)
Assessor (FielldPersonID, FielldQualificationName, FielldDateQualified)
Foreign Key (FielldPersonID) References Person
Property (FielldPropertyID, FielldStreet, FielldCity, FielldPostcode, FielldPersonID)
Foreign Key (FielldPersonID) References Person
Policy (FielldPolicyID, FielldEffectiveFromDate, FielldPremiumAmount,
FielldPaidDate, FielldIsHomeBuildingPolicy, FielldIsHomeContentsPolicy,
FielldTotalPolicyAmount, FielldPropertyID)
Foreign Key (FielldPropertyID) References Property
2 | P a g e

Home_Building (FielldPolicyID, FielldHouseType, FielldYearBuilt,
FielldIsAlarmFitted, FielldHasWindowsLocks, FielldInsuredAmount,
FielldBuildingPremiumAmount)
Foreign Key (FielldPolicyID) References Policy
Home_Content (FielldPolicyID, FielldContentPremiumAmount)
Foreign Key (FielldPolicyID) References Policy
Content_Item (FielldItemID, FielldItemName, FielldManufacturer, FielldModel,
FielldPurchasePrice, FielldQuantity, FielldClaimedStatus, FielldPolicyID)
Foreign Key (FielldPolicyID) References Policy
Claim (FielldClaimID, FielldLodgedDate, FielldStatus, FielldPolicyID,
FielldPersonID)
Foreign Key (FielldPersonID) References Person
Settled_Claim (FielldClaimID, FielldSettledDate, FielldAmountSettled)
Foreign Key (FielldClaimID) References Claim
Rejected_Claim (FielldClaimID, FielldRejectedDate, FielldRejectedReason)
Foreign Key (FielldClaimID) References Claim
Items_Claimed (FielldClaimID, FielldItemID, FielldItemName, FielldQuantity,
FielldItemStatus)
Foreign Key (FielldClaimID) References Claim
Foreign Key (FielldItemID) References Content_Item
Assessment_Report (FielldReportID, FielldReportDate, FielldInitialRecommendation,
FielldReportType, FielldClaimID, FielldPersonID)
Foreign Key (FielldClaimID) References Claim
Foreign Key (FielldPersonID) References Assessor
Final_Assessment_Report (FielldReportID, FielldFinalAssessmentDate,
FielldTotalAmount, FielldCostReportMemo)
Foreign Key (FielldReportID) References Assessment_Report
3 | P a g e

Functional Dependency in Tables
Assessor
Assessor (FielldPersonID, FielldQualificationName, FielldDateQualified)
FielldQualificationName  FielldPersonID
FielldDateQualified  FielldPersonID
All assessor records are depending upon the Person ID as it is the primary key in the
Assessor table.
Property
Property (FielldPropertyID, FielldStreet, FielldCity, FielldPostcode, FielldPersonID)
FielldStreet FielldPropertyID
FielldCity FielldPropertyID
FielldPostcode FielldPropertyID
FielldPersonID FielldPropertyID
All property records are depending upon the Property ID as it is the primary key in the
Property table.
Normalisation
The CQI database is designed as third normalized database by using the following
rules-
- Create new tables for separated data.
- Set primary key in all newly created tables.
- All fields depend upon the primary key only.
- Transitive dependency should not occur in any table.
(USA Informa 2018)
(BigennersBook 2017)
4 | P a g e

SQL Queries
Query 1
SELECT homeSubQuery.Home_Policy, contentSubQuery.Home_Content_Policy FROM
(SELECT Count(*) AS Home_Policy FROM Home_Building) AS homeSubQuery, (SELECT
Count(*) AS Home_Content_Policy FROM Home_Content) AS contentSubQuery;
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 settledSubQuery.Settled_Claim, rejectedSubQuery.Rejected_Claim
FROM (SELECT Count(*) AS Settled_Claim FROM Settled_Claim) AS settledSubQuery,
(SELECT Count(*) AS Rejected_Claim FROM Rejected_Claim) AS rejectedSubQuery;
5 | 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;
6 | P a g e

Claim Summary Report
Learning Features
The assignment is giving lots of knowledge in the following fields-
- Database SQL queries
- Database report
- Work on sub queries
- Work on join queries
Complex Features
I faced complexity only in two areas-
- Sub queries
- Join queries
Conclusion
The report is completely based upon the CQI database and showing all the features
that are used in making the CQI database like normalization, functional dependencies,
queries, reports etc.
7 | P a g e

References
BigennersBook (2017), Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in
Database: Foreign Keys, Retrieved from:
https://beginnersbook.com/2015/05/normalization-in-dbms/
USA Informa (2018), SQL by Design: Why You Need Database Normalization.
Retrieved from:
http://www.itprotoday.com/risk-and-compliance/ibm-executives-press-us-
lawmakers-not-adopt-eu-privacy-law.
8 | P a g e