COIT20247 Database Design and Development: Normalization Report

This report details the process of database normalization using a CQI database developed in MS Access. It provides a comprehensive overview of the normalization process, including the ER diagram, mapping, and logical design of the database. The report illustrates the application of normalization rules to achieve the third normal form, ensuring data integrity and minimizing redundancy. It includes SQL queries demonstrating data retrieval and manipulation, along with an analysis of functional dependencies within the tables. The report also highlights the learning features, such as SQL queries, subqueries, and normalization concepts, along with complex features like inner joins. The conclusion summarizes the report's key findings, emphasizing the importance of database design and development. References to relevant resources are also included.

Assignment 2
Student ID:
Tutor:

Implementation Report
Introduction
The report is giving complete information about database normalization. It is showing
complete normalization process through CQI database that is developed in MS Access
database. It is showing all query results, database report, functional dependencies in
between tables etc.
Normalization
ER Diagram Mapping
2 | P a g e

Logical Design
Person (FielddPersonID, FielddPersonName, FielddStreet, FielddCity,
FielddPostcode, FielddPersonType)
Assessor (FielddPersonID, FielddQualificationName, FielddDateQualified)
Foreign Key (FielddPersonID) References Person
Property (FielddPropertyID, FielddStreet, FielddCity, FielddPostcode,
FielddPersonID)
Foreign Key (FielddPersonID) References Person
Policy (FielddPolicyID, FielddEffectiveFromDate, FielddPremiumAmount,
FielddPaidDate, FielddIsHomeBuildingPolicy, FielddIsHomeContentsPolicy,
FielddTotalPolicyAmount, FielddPropertyID)
Foreign Key (FielddPropertyID) References Property
Home_Building (FielddPolicyID, FielddHouseType, FielddYearBuilt,
FielddIsAlarmFitted, FielddHasWindowsLocks, FielddInsuredAmount,
FielddBuildingPremiumAmount)
Foreign Key (FielddPolicyID) References Policy
Home_Content (FielddPolicyID, FielddContentPremiumAmount)
Foreign Key (FielddPolicyID) References Policy
Content_Item (FielddItemID, FielddItemName, FielddManufacturer, FielddModel,
FielddPurchasePrice, FielddQuantity, FielddClaimedStatus, FielddPolicyID)
Foreign Key (FielddPolicyID) References Policy
Claim (FielddClaimID, FielddLodgedDate, FielddStatus, FielddPolicyID,
FielddPersonID)
Foreign Key (FielddPersonID) References Person
Settled_Claim (FielddClaimID, FielddSettledDate, FielddAmountSettled)
Foreign Key (FielddClaimID) References Claim
Rejected_Claim (FielddClaimID, FielddRejectedDate, FielddRejectedReason)
Foreign Key (FielddClaimID) References Claim
3 | P a g e

Items_Claimed (FielddClaimID, FielddItemID, FielddItemName, FielddQuantity,
FielddItemStatus)
Foreign Key (FielddClaimID) References Claim
Foreign Key (FielddItemID) References Content_Item
Assessment_Report (FielddReportID, FielddReportDate,
FielddInitialRecommendation, FielddReportType, FielddClaimID, FielddPersonID)
Foreign Key (FielddClaimID) References Claim
Foreign Key (FielddPersonID) References Assessor
Final_Assessment_Report (FielddReportID, FielddFinalAssessmentDate,
FielddTotalAmount, FielddCostReportMemo)
Foreign Key (FielddReportID) References Assessment_Report
(Tech Terms 2018)
4 | P a g e

Functional Dependency in Tables
Assessor
Assessor (FielddPersonID, FielddQualificationName, FielddDateQualified)
FielddQualificationName  FielddPersonID
FielddDateQualified  FielddPersonID
Person ID is the primary key of Assessor table and uniquely identifying the records in
Assessor table. All other fields are depending only upon the Person ID primary key.
Property
Property (FielddPropertyID, FielddStreet, FielddCity, FielddPostcode,
FielddPersonID)
FielddStreet FielddPropertyID
FielddCity FielddPropertyID
FielddPostcode FielddPropertyID
FielddPersonID FielddPropertyID
Property ID is the primary key of Property table and uniquely identifying the records
in Property table. All other fields are depending only upon the Property ID primary
key.
Normalisation
The third normalized database is achieved by applying following rules on the CQI
database-
- Produce separate tables for separate data.
- Set primary key in all new separate tables.
- Al other fields should depend upon the primary key only.
- No transitive dependency is belonging to any table.
The tables defined above Assessor and Property is satisfying the above rules. All the
tables are similarly satisfying the above mentioned rules. That is why; CQI database is
in third normal form.
(Hoff T. 2018)
5 | P a g e

SQL Queries
Query 1
SELECT sqryHome.HomePolicy, sqryContent.HomeContentPolicy FROM (SELECT Count(*) AS
HomePolicy FROM Home_Building) AS sqryHome, (SELECT Count(*) AS HomeContentPolicy
FROM Home_Content) AS sqryContent;
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 sqrySettled.SettledClaim, sqryRejected.RejectedClaim
FROM (SELECT Count(*) AS SettledClaim FROM Settled_Claim) AS sqrySettled, (SELECT
Count(*) AS RejectedClaim FROM Rejected_Claim) AS sqryRejected;
6 | 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;
7 | P a g e

Rejected Claim Summary Report
Learning Features
I obtained so much knowledge about the following features of CQI database-
- Received knowledge about SQL Queries
- Received knowledge about SQL Sub Queries
- Received knowledge about normalization
- Received knowledge about functional dependencies
Complex Features
These are the topics in which I faced little bit complexity but resolved later and now
I’ve full command on RDBMS-
- Sub queries
- Inner join queries
Conclusion
The report is completely showing detail about CQI database design and development.
All the important information of CQI is shown in the report like dependencies among
different tables etc.
8 | P a g e

References
Tech Terms (2018). RDBMS. [online]. Available from:
https://techterms.com/definition/rdbms. [Accessed 17 May 2018]
Hoff T. (2018), The Mother of All Database Normalizations Debates on Coding
Error. [online]. Available from:
http://highscalability.com/blog/2008/7/16/the-mother-of-all-database-
normalization-debates-on-coding-h.html. [Accessed 17 May 2018]
9 | P a g e