COIT20247 Database Design & Development (T1 2018) Assignment 2 Report

This report details the implementation of a database for the CQI, covering the mapping from an ER diagram to a relational database, normalization, and the creation of SQL queries and reports. The report includes sections on normalization, SQL queries (including examples like selecting home policy counts and finding rejected claims), and a discussion of the learning features and complex aspects of the assignment. The student successfully implemented various database features, including subqueries, aggregate functions, and the use of a normalized database, demonstrating a strong understanding of database design and development principles. The report concludes with an overview of the implemented functionalities and the usefulness of the database for management purposes within CQI. The report also provides the references used for completing the assignment.

COIT20247 Database Design and development (T12018)
Assignment 2
Student ID:
Module Tutor:
Date:
Implementation Report
Introduction
The report is summarising the database that is based upon the CQI. The database
implementation is completely done in the assignment for the CQI. The mapping from

ER diagram to the relational database is built first and then the database equerries and
reports are done after inserting the data into tables.
Normalization
ER Diagram Mapping
Functional Dependency
Person
Person (FPersonID, FPersonName, FStreet, FCity, FPostcode, FPersonType)
FPersonNameFPersonID
FPersonTypeFPersonID
All the fields just like person name and person type are depending upon the person ID
only which is working as a primary key in the Person table.
Property
Property (FPropertyID, FStreet, FCity, FPostcode, FPersonID)
FStreetFPropertyID
1 | P a g e

FCityFPropertyID
FPostcodeFPropertyID
FPersonIDFPropertyID
All the fields just are depending upon the person ID only which is working as a
primary key in the Person table.
Normalisation
Third Normalized database is obtained by applying the following rules at all the
tables-
- Create different tables for similar type of data.
- Generate primary key in all tables.
- All fields should be depended upon the primary key only.
- There should be no transitive dependency in any table.
Just like Property and Person tables, all the tables are satisfying the above rules.
Therefore, complete data is in the third normalized form.
(Juniper Networks 2018)
(Cambridge University Press 2008)
SQL Queries
Query 1
2 | P a g e

SELECT sbq1.Home_Policy, sbq2.Home_Content_PolicyFROM (SELECT Count(*) AS
Home_Policy FROM Home_Building) AS sbq1, (SELECT Count(*) AS Home_Content_Policy
FROM Home_Content) AS sbq2;
Query 2
SELECT PersonID, PersonName, Street, City, Postcode, PersonTypeFROM PersonWHERE
PersonID not in (Select PersonID from Assessment_Report) and PersonType='Assessor';
Query 3
SELECT sbq1.Settled_Claim, sbq2.Rejected_ClaimFROM (SELECT Count(*) AS Settled_Claim
FROM Settled_Claim) AS sbq1, (SELECT Count(*) AS Rejected_Claim FROM Rejected_Claim)
AS sbq2;
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;
3 | P a g e

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

Learning Features
These are the features which I learned during the working on the assignment-
- Database reports
- Database queries
- Database sub queries
- Aggregate functions
- Use of group by clause
- Use of normalized database
Complex Features
The assignment is very interesting and useful. All the features implemented in the
assignment are very interesting. I found difficulties in query 1 and query 3 only but I
resolved that by studying the sub queries.
Conclusion
The assignment is briefing all the points developed in MS Access. All the things are
implemented perfectly like queries, reports etc. everything is very interesting and
useful. The database report is summarizing the claim detail which is very helpful for
manager level persons of CQI. All queries are also very useful for management.
References
Cambridge University Press (2008). Normalization (equivalence classing of terms).
[online]. Available from:
https://nlp.stanford.edu/IR-book/html/htmledition/normalization-equivalence-
classing-of-terms-1.html [Accessed: 13-May-2018]
5 | P a g e

Juniper Networks (2018). Normalization. [online.] Available
https://www.juniper.net/documentation/en_US/junos/topics/reference/
configuration-statement/normalization-edit-protocols-mpls-container-lsp-
splitting-merging.html/ [Accessed 13 May 2018].
6 | P a g e