COIT20247 Database Design and Development (T1 2018) CQI Report

This report presents the database development of CQI using MS Access. It provides a comprehensive overview of the database design, including the ER diagram, normalization, and the structure of various tables such as Person, Property, Policy, and Claim. The report details functional dependencies, demonstrating how fields depend on primary keys. It achieves third normal form through separate tables, primary keys, and avoidance of transitive dependencies. The report also includes SQL queries that showcase the database's functionality, covering topics like subqueries, counts, and joins to retrieve and manipulate data. The assignment highlights learning features such as the use of subqueries, database queries, and reports, providing practical working knowledge. The conclusion emphasizes the clarity of the database development process and the use of MS Access features, including queries, reports, and relationships. References to online resources support the concepts discussed.

COIT20247 Database Design and Development (T12018)
Assessment item 2—Assignment2
MAY 10, 2018
Student ID:
Module Tutor:

Implementation Report
Introduction
The database development of CQI is done in MS Access and the report is describing
the same. Complete detail about CQI database and all the features and functionality of
MS Access is being shown in the report.
Normalization
ER Diagram Mapping
Person (PersonID, PersonName, Street, City, Postcode, PersonType)
Assessor (PersonID, QualificationName, DateQualified)
Foreign Key (PersonID) References Person
Property (PropertyID, Street, City, Postcode, PersonID)
Foreign Key (PersonID) References Person
Policy (PolicyID, EffectiveFromDate, PremiumAmount, PaidDate,
IsHomeBuildingPolicy, IsHomeContentsPolicy, TotalPolicyAmount, PropertyID)
Foreign Key (PropertyID) References Property
Home_Building (PolicyID, HouseType, YearBuilt, IsAlarmFitted,
HasWindowsLocks, InsuredAmount, BuildingPremiumAmount)
Foreign Key (PolicyID) References Policy
Home_Content (PolicyID, ContentPremiumAmount)
Foreign Key (PolicyID) References Policy
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchasePrice, Quantity,
ClaimedStatus, PolicyID)
Foreign Key (PolicyID) References Policy
Claim (ClaimID, LodgedDate, Status, PolicyID, PersonID)
Foreign Key (PersonID) References Person
Settled_Claim (ClaimID, SettledDate, AmountSettled)
Foreign Key (ClaimID) References Claim
1 | P a g e

Rejected_Claim (ClaimID, RejectedDate, RejectedReason)
Foreign Key (ClaimID) References Claim
Items_Claimed (ClaimID, ItemID, ItemName, Quantity, ItemStatus)
Foreign Key (ClaimID) References Claim
Foreign Key (ItemID) References Content_Item
Assessment_Report (ReportID, ReportDate, InitialRecommendation, ReportType,
ClaimID, PersonID)
Foreign Key (ClaimID) References Claim
Foreign Key (PersonID) References Assessor
Final_Assessment_Report (ReportID, FinalAssessmentDate, TotalAmount,
CostReportMemo)
Foreign Key (ReportID) References Assessment_Report
(teratrax.com n.d.)
Functional Dependency
Table Person
Person (PersonID, PersonName, Street, City, Postcode, PersonType)
PersonNamePersonID
PersonTypePersonID
All the fields depend upon the primary key just like PersonName and PersoneType.
There is not any other field that identify the persons except PersonID.
Table Property
Property (PropertyID, Street, City, Postcode, PersonID)
StreetPropertyID
CityPropertyID
2 | P a g e

PostcodePropertyID
PersonIDPropertyID
All the fields depend upon the primary key PropertyIDonly.There is not any other
field that identify the propert except PropertyID.
Normalisation
A third normal form is achieved by the following way-
- Make all the separate tables.
- Make primary key in all separate tables.
- No fields should uniquely identify the record except primary or composite
key.
- No transitive dependency should occur into any table.
Property and Person both the tables are satisfying the above normalization rules. Not
only Person and Property, all the tables are satisfying the third normal form.
(Tutorialspoint.com (2018)
SQL Queries
Query 1
SELECT subQuery1.Home_Policy, subQuery2.Home_Content_PolicyFROM (SELECT Count(*)
AS Home_Policy FROM Home_Building) AS subQuery1, (SELECT Count(*) AS
Home_Content_Policy FROM Home_Content) AS subQuery2;
3 | P a g e

Query 2
SELECT PersonID, PersonName, Street, City, Postcode, PersonTypeFROM PersonWHERE
PersonID not in (Select PersonID from Assessment_Report) and PersonType='Assessor';
Query 3
SELECT subQuery1.Settled_Claim, subQuery2.Rejected_ClaimFROM (SELECT Count(*) AS
Settled_Claim FROM Settled_Claim) AS subQuery1, (SELECT Count(*) AS Rejected_Claim
FROM Rejected_Claim) AS subQuery2;
Query 4
SELECT * FROM PersonWHERE PersonID in (Select Property.PersonIDFROM 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;
4 | P a g e

Query 6
SELECT Policy.PolicyID, Policy.IsHomeBuildingPolicy, Policy.IsHomeContentsPolicy,
Policy.PremiumAmount FROM PolicyWHERE Policy.IsHomeBuildingPolicy<>0 and
Policy.IsHomeContentsPolicy<>0;
Claim Summary Report
5 | P a g e

Learning Features
Learning features while database development of CQI are as follows-
- Learned to use the sub queries.
- Learned to use the database queries.
- Learned to use the database reports.
- Learned to make normalized database
Complex Features
The assignment is very practical and giving complete working knowledge. There are
not so much complex points. I faced only small issues in making the database report.
Conclusion
The report is showing the complete working and functionality of database
development of CQI. The user can easily understand the full concept of the database
development. Most of the supported features of MS Access are used in the assignment
like queries, reports, relations etc.
The report is showing all the queries along with query results which is showing the
database working very clearly.
References
6 | P a g e

Tutorialspoint.com(2018).DBMS - Normalization.[online]. Available from:
https://www.tutorialspoint.com/dbms/database_normalization.htm [Accessed:
10-May-2018]
teratrax.com (n.d.). SQL Server Data Types and Ranges. [online.] Available
from:http://www.teratrax.com/sql-server-data-types-ranges/ [Accessed 10
May 2018].
7 | P a g e