COIT20247 Database Design and Development (T1 2018) Assignment 2

This report details the implementation of a database design assignment, focusing on normalization concepts. It includes an ER diagram mapping, table structures with primary and foreign keys, and functional dependencies within tables like Assessor and Property. The report explains the normalization process, adhering to First, Second, and Third Normal Forms. It also presents SQL queries for data retrieval and analysis, covering topics such as home policy counts, assessor identification, claim summaries, and premium calculations. The document concludes with reflections on learning and complex features encountered during the assignment.

COIT20247 Database Design and Development (T1
2018)
Assignment 2
Student ID:
Module Tutor:
5/21/2018

Implementation Report
Introduction
The report is explaining in brief all the important features developed in the
assignment. Normalization concept is the base of the assignment and the report is
completely describing the normalization process.
Normalization
ER Diagram Mapping
Person (FFPersonID, FFPersonName, FFStreet, FFCity, FFPostcode, FFPersonType)
Assessor (FFPersonID, FFQualificationName, FFDateQualified)
Foreign Key (FFPersonID) References Person
Property (FFPropertyID, FFStreet, FFCity, FFPostcode, FFPersonID)
Foreign Key (FFPersonID) References Person
Policy (FFPolicyID, FFEffectiveFromDate, FFPremiumAmount, FFPaidDate,
FFIsHomeBuildingPolicy, FFIsHomeContentsPolicy, FFTotalPolicyAmount,
FFPropertyID)
Foreign Key (FFPropertyID) References Property
Home_Building (FFPolicyID, FFHouseType, FFYearBuilt, FFIsAlarmFitted,
FFHasWindowsLocks, FFInsuredAmount, FFBuildingPremiumAmount)
Foreign Key (FFPolicyID) References Policy
Home_Content (FFPolicyID, FFContentPremiumAmount)
Foreign Key (FFPolicyID) References Policy
Content_Item (FFItemID, FFItemName, FFManufacturer, FFModel,
FFPurchasePrice, FFQuantity, FFClaimedStatus, FFPolicyID)
Foreign Key (FFPolicyID) References Policy
Claim (FFClaimID, FFLodgedDate, FFStatus, FFPolicyID, FFPersonID)
Foreign Key (FFPersonID) References Person
2 | P a g e

Settled_Claim (FFClaimID, FFSettledDate, FFAmountSettled)
Foreign Key (FFClaimID) References Claim
Rejected_Claim (FFClaimID, FFRejectedDate, FFRejectedReason)
Foreign Key (FFClaimID) References Claim
Items_Claimed (FFClaimID, FFItemID, FFItemName, FFQuantity, FFItemStatus)
Foreign Key (FFClaimID) References Claim
Foreign Key (FFItemID) References Content_Item
Assessment_Report (FFReportID, FFReportDate, FFInitialRecommendation,
FFReportType, FFClaimID, FFPersonID)
Foreign Key (FFClaimID) References Claim
Foreign Key (FFPersonID) References Assessor
Final_Assessment_Report (FFReportID, FFFinalAssessmentDate, FFTotalAmount,
FFCostReportMemo)
Foreign Key (FFReportID) References Assessment_Report
3 | P a g e

Functional Dependency in Tables
Assessor
Assessor (FFPersonID, FFQualificationName, FFDateQualified)
FFQualificationName  FFPersonID
FFDateQualified  FFPersonID
Property
Property (FFPropertyID, FFStreet, FFCity, FFPostcode, FFPersonID)
FFStreet FFPropertyID
FFCity FFPropertyID
FFPostcode FFPropertyID
FFPersonID FFPropertyID
Normalisation
The database is build by applying the rules of normalization like below-
First Normal Form
To make the database in first normal form, create separate tables to contain separate
data.
Second Normal Form
To make the database in second normal form, set primary key for all the tables and all
fields should depend upon that only.
Third Normal Form
To make the database in third normal form, remove transitive dependency.
(Watt A. n.d.)
(Wyllys E. R. 2005)
4 | P a g e

SQL Queries
Query 1
SELECT qr1.HomePolicy, qr2.HomeContentPolicy FROM (SELECT Count(*) AS HomePolicy
FROM Home_Building) AS qr1, (SELECT Count(*) AS HomeContentPolicy FROM
Home_Content) AS qr2;
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 qr1.SettledClaim, qr2.RejectedClaim FROM (SELECT Count(*) AS SettledClaim FROM
Settled_Claim) AS qr1, (SELECT Count(*) AS RejectedClaim FROM Rejected_Claim) AS qr2;
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

Rejected Claim Summary Report
Learning Features
All the below mentioned features are very knowledgeable features in the assignment-
- Query generation
- Database report generation
- Normalization concept
Complex Features
All the below mentioned features had little bit complexity but have been resolved
now-
- SQL sub queries
- SQL join queries
Conclusion
Everything is very clearly mentioned in the report and giving complete knowledge
about relational database management system.
7 | P a g e

References
Watt A. (n.d.). Chapter 12 Normalization. [online]. Retrieved from:
https://opentextbc.ca/dbdesign01/chapter/chapter-12-normalization/
Wyllys E. R. (2005), Overview of Normalization. Retrieved from:
https://www.ischool.utexas.edu/~wyllys/DMPAMaterials/normover.html
8 | P a g e