Database Implementation Report: Normalization and Queries

This report details the implementation of a database, focusing on normalization, functional dependencies, and query design. The report includes an ER diagram with tables for Policies, Assessors, Claims, Customers, Property, Items, and Assessment Reports. It explains functional dependencies and demonstrates the normalization process, particularly the 3NF, using the Customers and Assessors tables as examples. The implementation section highlights the use of a report wizard and the 'Not In' function in queries, along with the challenges faced during database normalization. A bibliography of relevant sources is also included. The report showcases the student's understanding of database design principles and practical application. This report is a great resource for students studying database design and implementation.

Running head: DATABASE IMPLEMEMTATION
Database Implementation
Name of the Student:
Name of the University:
Author Note

1
DATABASE IMPLEMEMTATION
Normalization
Set of Relations
Policies (PolicyID (primary key), CustomerID (foreign key) EffectFrom, PremiumAmount,
PaidDate, PolicyType, TotalPolicyAmount)
Assessor (AssessorID (primary key), AssessorName, Qualification, QualificationDate)
Claims (ClaimID (primary key), PolicyID (foreign key), LoggedDate, Status, ItemID (foreign
key), Actiondate, AmountSettled, RejectReason)
Customers (CustomerId (primary key), CustomerName, Address)
Property (PropertyID (primary key), CustomerID (foreign key), Address, Postcode)
Items (ItemID (primary key), PropertyID (forgein key), ItemName, Price)
Assessment Report (ReportID (primary key), ClaimID (foreign key) ReportDate, AssessorID
(foreign key), ReportType)
Functional Dependencies
Attribute Dependencies
PolicyID CustomerID, EffectFrom, PremiumAmount, PaidDate, PolicyType,
TotalPolicyAmount
AssesorID AssessorName, Qualification, QualificationDate
ClaimID PolicyID, LoggedDate, Status, ItemsID, Actiondate, AmountSettled,
RejectReason
CustomerID CustomerName, Address
PropetyID CustomerID, Address, Postcode

2
DATABASE IMPLEMEMTATION
ItemID ItemName, PropertyID, Price
ReportID ClaimID, ReportDate, AssessorID, ReportType
3 NF Demonstration
For the description of the normalization the customers and the assessors have been chosen as
from the given ER Diagram Person has been normalized to customers and assessors.
Customers (CustomerId (primary key), CustomerName, Address)
Property (PropertyID (primary key), CustomerID (foreign key), Address, Postcode)
i. Property ID is considered to be primary key which is stated like a property. It comes up with
single kind of property name per property ID as it mainly contains a proper kind of name which
is used for recording each kind of stated property. Although a Property can have more than one
owners but for this case only a single owner is considered, the case study defines the fact that
there is requirement for recording the multiple owners in the system and hence and single owner
would be enough for the database. Hence, it can be defined as 1NF.
ii. Here PropertyID is considered to be a primary key. It comes up with candidate key in the table
but that is foreign key and it is not guaranteed to be unique, because it is assumed that there
would only be single customer for a particular property, but in real case scenarios there can be
more than one customer for a single property. Address is also not stated to be for sure. This
mainly focus on the fact that PropertyID functionally which mainly checks every kind of
attribute which is present in the table. So, this can be defined as 2NF.

3
DATABASE IMPLEMEMTATION
iii. The CustomerID is not generally used for analyzing any other attribute which is present in the
table since it comes up two Customers can easily have like name; likewise, for address. So no
kind of transitive dependency comes into action. So, as a result all the needs of various forms
ranging from one to third normal form have been met.
Implementation Report
1. The most important topic that was very interesting was the report. The report had to be
created from the wizard view using the query named reportquery. Secondly the Not in
function in the query was also very important and also very interesting.
2. The most difficult task was that of normalizing the database and hence I normalized the
table accordingly so that the query could be run easily.

4
DATABASE IMPLEMEMTATION
Bibliography
Aref, M., ten Cate, B., Green, T.J., Kimelfeld, B., Olteanu, D., Pasalic, E., Veldhuizen, T.L. and
Washburn, G., 2015, May. Design and implementation of the LogicBlox system. In Proceedings
of the 2015 ACM SIGMOD International Conference on Management of Data (pp. 1371-1382).
ACM.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on
Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision
Mechanics, Optics & Mechatronics, (49), p.74.
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle tracking
system using GPS/GSM/GPRS technology and smartphone application. In Internet of Things
(WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Liu, C., Zhang, L. and Pan, Q., 2016. Database Design and Implementation of Natural Disaster
Monitoring System. Journal of Residuals Science & Technology, 13(5).