COIT20247: Database Design and Development (T1 2018) Report

This report presents an implementation analysis of a database design and development assignment for the COIT20247 course. The report focuses on the CQI database, detailing the mapping of an ER diagram to a normalized relational database, along with functional dependencies. It outlines the normalization process, adhering to third normal form principles, and provides a series of SQL queries designed to extract and manipulate data. The report also highlights key learning features, such as the correct use of subqueries and inner joins, and discusses the complexities encountered during the implementation, particularly with subqueries. The CQI database, built in MS Access, is summarized, illustrating how a user can understand its complete working through the provided ER diagram mapping, normalization, queries, and reports. The report concludes by summarizing the database design and development process, including references to supporting materials.

Student ID:
Student Name:
COIT20247 Database Design and Development(T12018)
Assessment Item 2 – Assignment 2
5/12/2018

Implementation Report
Introduction
The report is showing the inclusive detail about CQI database. In CQI database, the queries and
database reports are created to show the summary data for the upper level employees like
administrators etc. The assignment is showing the proper way to map the ER diagram into the
normalized relational database.
Normalization
ER Diagram Mapping

Functional Dependency
Assessor Table
Assessor (PersonID, QualificationName, DateQualified)
QualificationName PersonID
DateQualified PersonID
Every field is depending upon the PersonID, means only the PersonID can uniquely identify the
assessor’s records. PersonID is the primary key in the Assessor table.
ItemsClaimed Table
ItemsClaimed (ClaimID, ItemID, ItemName, Quantity, Status)
ItemName  ClaimID, ItemID
Quantity  ClaimID, ItemID
Status  ClaimID, ItemID
The table ItemsClaimed is having the composite key (ClaimID, ItemID). Another fields depend
upon the composite key (ClaimID, ItemID) only.
Normalisation
There are some rules to obtain the third normalized database as following-
- Separate all tables according to the data.
- Set primary or composite key in all tables.
- All fields must depend upon the primary or composite key.
- Eradicate transitive dependencies from the entire database.
Above defined tables- Assessor and ItemsClaimed; both are following the above mentioned
rules. Therefore both are in third normal form. All tables are similarly following the above
mentioned rules.
(Chapple M. 2018)
(geeksforgeeks.org n.d.)

SQL Queries
Query 1
SELECT sq1.Home_Policy, sq2.Home_Content_Policy FROM (SELECT Count(*) AS Home_Policy FROM
Home_Building) AS sq1, (SELECT Count(*) AS Home_Content_Policy FROM Home_Content) AS sq2;
Query 2
SELECT PersonID, PersonName, Street, City, Postcode, PersonTypeFROM PersonWHERE PersonID not in
(Select PersonID from Assessment_Report) and PersonType='Assessor';
Query 3
SELECT sq1.Settled_Claim, sq2.Rejected_ClaimFROM (SELECT Count(*) AS Settled_Claim FROM
Settled_Claim) AS sq1, (SELECT Count(*) AS Rejected_Claim FROM Rejected_Claim) AS sq2;
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;
Query 6
SELECT Policy.PolicyID, Policy.IsHomeBuildingPolicy, Policy.IsHomeContentsPolicy,
Policy.PremiumAmount FROM PolicyWHERE Policy.IsHomeBuildingPolicy<>0 and
Policy.IsHomeContentsPolicy<>0;
Claim Report

Learning Features
These are the learning features in the assignment-
- Very correct sue of sub queries.
- Very correct use of inner join queries.
- Very correct use of database report.
- How to generate normalized database.
Complex Features
The assignment is implementing all the features available in MS Access. Everything is very clear
and easy to understand. I found some complexity in sub queries. It was tricky but it is resolved
now.
Conclusion
The report is summarizing everything related to CQI. Entire database of CQI is built in MS
Access. The report is showing the ER diagram to normalized database mapping and functional
dependency also. All the queries and report is also being displayed into the report.
After studying the report, a user can easily understand the complete working of CQI.

References
Chapple M. (2018). Database Normalization Basics. [online]. Available from:
https://www.lifewire.com/database-normalization-basics-1019735 [Accessed: 12-May-
2018]
geeksforgeeks.org (n.d.). Database Normalization | Normal Forms. [online.] Available from:
https://www.geeksforgeeks.org/database-normalization-normal-forms/ [Accessed 12 May
2018].