COIT20247 Database Design: Database Normalization and SQL Report

This report details the implementation of database normalization concepts within a Relational Database Management System (RDBMS). It includes mapping an ER diagram to at least the Third Normal Form (3NF), demonstrating functional dependencies within tables (Assessor and Property), and providing SQL queries for data retrieval and analysis. The report highlights key learning features such as database query generation, report generation, normalization, SQL joins, and sub-queries. It concludes with a summary of the assignment's features and provides references for further reading. Desklib provides access to similar assignments and past papers for students.

COIT20247 Database Design and
Development (T1 2018)
Assignment 2
Student ID:
Student Name:
Tutor Name:
Date:

Implementation Report
Introduction
Complete report is demonstrating the database normalization concept in the RDBMS.
Normalization is the basic concept in RDBMS to make the accurate database
management system.
Normalization
ER Diagram Mapping
Person (FiilddPersonID, FiilddPersonName, FiilddStreet, FiilddCity, FiilddPostcode,
FiilddPersonType)
Assessor (FiilddPersonID, FiilddQualificationName, FiilddDateQualified)
Foreign Key (FiilddPersonID) References Person
Property (FiilddPropertyID, FiilddStreet, FiilddCity, FiilddPostcode, FiilddPersonID)
Foreign Key (FiilddPersonID) References Person
Policy (FiilddPolicyID, FiilddEffectiveFromDate, FiilddPremiumAmount,
FiilddPaidDate, FiilddIsHomeBuildingPolicy, FiilddIsHomeContentsPolicy,
FiilddTotalPolicyAmount, FiilddPropertyID)
Foreign Key (FiilddPropertyID) References Property
Home_Building (FiilddPolicyID, FiilddHouseType, FiilddYearBuilt,
FiilddIsAlarmFitted, FiilddHasWindowsLocks, FiilddInsuredAmount,
FiilddBuildingPremiumAmount)
Foreign Key (FiilddPolicyID) References Policy
Home_Content (FiilddPolicyID, FiilddContentPremiumAmount)
Foreign Key (FiilddPolicyID) References Policy
Content_Item (FiilddItemID, FiilddItemName, FiilddManufacturer, FiilddModel,
FiilddPurchasePrice, FiilddQuantity, FiilddClaimedStatus, FiilddPolicyID)
Foreign Key (FiilddPolicyID) References Policy
2 | P a g e

Claim (FiilddClaimID, FiilddLodgedDate, FiilddStatus, FiilddPolicyID,
FiilddPersonID)
Foreign Key (FiilddPersonID) References Person
Settled_Claim (FiilddClaimID, FiilddSettledDate, FiilddAmountSettled)
Foreign Key (FiilddClaimID) References Claim
Rejected_Claim (FiilddClaimID, FiilddRejectedDate, FiilddRejectedReason)
Foreign Key (FiilddClaimID) References Claim
Items_Claimed (FiilddClaimID, FiilddItemID, FiilddItemName, FiilddQuantity,
FiilddItemStatus)
Foreign Key (FiilddClaimID) References Claim
Foreign Key (FiilddItemID) References Content_Item
Assessment_Report (FiilddReportID, FiilddReportDate,
FiilddInitialRecommendation, FiilddReportType, FiilddClaimID, FiilddPersonID)
Foreign Key (FiilddClaimID) References Claim
Foreign Key (FiilddPersonID) References Assessor
Final_Assessment_Report (FiilddReportID, FiilddFinalAssessmentDate,
FiilddTotalAmount, FiilddCostReportMemo)
Foreign Key (FiilddReportID) References Assessment_Report
3 | P a g e

Functional Dependency in Tables
Assessor
Assessor (FiilddPersonID, FiilddQualificationName, FiilddDateQualified)
FiilddQualificationName  FiilddPersonID
FiilddDateQualified  FiilddPersonID
Only primary key is uniquely identifying the records in the Accessor table and all
fields are depending upon it only.
Property
Property (FiilddPropertyID, FiilddStreet, FiilddCity, FiilddPostcode, FiilddPersonID)
FiilddStreet FiilddPropertyID
FiilddCity FiilddPropertyID
FiilddPostcode FiilddPropertyID
FiilddPersonID FiilddPropertyID
Only primary key is uniquely identifying the records in the Property table and all
fields are depending upon it only.
Normalisation
Third normalization is acheied in the CQI database by applying the normalization
rules in the three steps-
First Normal Form
1. Make unique tables having distinct data.
Second Normal Form
1. Set primary key for each table.
2. Every fields should depend upon the primary key only.
Third Normal Form
1. Get rid of transitive dependencies from all tables.
(condor.depaul.edu n.d.)
(Wyllys E. R. 2005)
4 | P a g e

SQL Queries
Query 1
SELECT qrHome.NumHomePolicy, qrContent.NumHomeContentPolicy FROM (SELECT
Count(*) AS NumHomePolicy FROM Home_Building) AS qrHome, (SELECT Count(*) AS
NumHomeContentPolicy FROM Home_Content) AS qrContent;
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 qrSettled.NumSettledClaim, qrRejected.NumRejectedClaim
FROM (SELECT Count(*) AS NumSettledClaim FROM Settled_Claim) AS qrSettled, (SELECT
Count(*) AS NumRejectedClaim FROM Rejected_Claim) AS qrRejected;
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
These are the important learning features of the assignment-
- Database query generation
- Database report generation
- Use of database normalization
- Use of SQL joins
- Use of sub queries
Complex Features
Features in which I faced some complexity are as follows-
- Database sub queries
- SQL joins
Conclusion
All features of the assignment are being shown in the report briefly. The report is the
complete package of the database normalization.
7 | P a g e

References
condor.depaul.edu (n.d.). Normalization. Retrieved from:
http://condor.depaul.edu/gandrus/240IT/accesspages/normalization3.htm
Wyllys E. R. (2005), Overview of Normalization. Retrieved from:
https://www.ischool.utexas.edu/~wyllys/DMPAMaterials/normover.html
8 | P a g e