Implementation Report: CQI Database Design and Development (COIT20247)

This report presents the implementation of the CQI database, designed and implemented using MS Access. It includes an introduction, an overview of the database structure, and normalization principles. The report features an ER diagram and functional dependency analysis for the Claim and Assessor tables. It also provides SQL queries, including subqueries, joins, and aggregate functions, to retrieve specific data from the database. The report concludes with a claim summary report and discusses learning features such as subqueries, database normalization, and report generation. The student also highlights the challenges faced and the conclusion summarizes the report's support for understanding the functionality of Relational Database Management Systems, such as MS Access.

COIT20247 Database
Design and
Development
(T12018)
Assessment Item 2 - Assignment 2
Student ID:
Module Tutor:
5/12/2018

Implementation Report
Introduction
The report is presenting the CQI database. The CQI database is designed and
implemented in MS Access. All the required queries and reports are completed in the
MS Access database.
Normalization
ER Diagram Mapping
Functional Dependency
Claim
Claim (ClaimID, LodgedDate, Status, PolicyID, PersonID)
LodgedDate  ClaimID
Status  ClaimID
PolicyID  ClaimID
PersonID  ClaimID
All fields in the table Claim depend upon the ClaimID which is the primary key and
uniquely identify the records in the Claim table.
1 | P a g e

Assessor
Assessor (PersonID, QualificationName, DateQualified)
QualificationName  PersonID
DateQualified  PersonID
All fields in the table Assessor depend upon the PersonID which is the primary key
and uniquely identify the records in the Assessor table.
Normalization
The normalized database is the best to store the data. It is very efficient. Following
steps are required to achieve the third normalized database-
1. Make separate tables with similar type of data e.g. Claim table for claims,
property table for properties etc.
2. Set one primary key in every table.
3. All fields should only depend upon the primary key.
4. Remove transitive dependency from every table.
All the tables in the CQI database are following the above mentioned rules. So, the
database of CQI is in third normal form.
(Easy Computer Academy 2017)
(Bigenners Book 2017)
2 | P a g e

SQL Queries
Query 1
SELECT subHome.No_Home_Policy, subContent.No_Home_Content_Policy FROM (SELECT
Count(*) AS No_Home_Policy FROM Home_Building) AS subHome, (SELECT Count(*) AS
No_Home_Content_Policy FROM Home_Content) AS subContent;
Query 2
SELECT PersonID, PersonName, Street, City, Postcode, PersonTypeFROM PersonWHERE
PersonID not in (Select PersonID from Assessment_Report) and PersonType='Assessor';
Query 3
SELECT subSettled.No_Settled_Claim, subRejected.No_Rejected_Claim FROM (SELECT
Count(*) AS No_Settled_Claim FROM Settled_Claim) AS subSettled, (SELECT Count(*) AS
No_Rejected_Claim FROM Rejected_Claim) AS subRejected;
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');
3 | P a g e

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;
4 | P a g e

Claim Summary Report
Learning Features
There are lots of learning features found in the assignment like below-
- Sub Queries generation
- Database normalization
- Database reports.
- Database complex queries like inner joins etc.
Complex Features
The assignment is very smooth working assignment. All the tasks are done without
any complication. I found some issue while making inner join queries but completed
it after some research.
Conclusion
The report is very much supportive to understand the functionality of Relational
Database Management System such as MS Access. The CQI database is build in MS
Access and the report is showing the complete working of CQI.
5 | P a g e

References
Easy Computer Academy (2017). Database Normalization Explained in Simple
English. [online]. Available from:
https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-
explained-in-simple-english/ [Accessed: 12-May-2018]
Bigenners Book (2017). Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in
Database: Foreign Keys. [online]. Available from:
https://beginnersbook.com/2015/05/normalization-in-dbms/ [Accessed: 12-
May-2018]
6 | P a g e