Database Implementation Report: Normalization and Implementation

This report details the implementation of a database, focusing on normalization and database design principles. The report begins with an overview of the database schema, including entities such as Assessors, Customers, Claims, and Policies, along with their attributes and functional dependencies. It demonstrates the process of normalization, specifically showing how the 'Customers' and 'Assessors' entities are brought to the third normal form (3NF). The report explains the primary keys, candidate keys, and how the tables satisfy the 1NF, 2NF, and 3NF requirements. It also includes an implementation report discussing the challenges and interesting aspects of the assignment, such as join operations and report generation. The author also reflects on the difficulties of normalization. The report concludes with a bibliography of relevant sources.

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

1
DATABASE IMPLEMEMTATION
Normalization
Set of Relations
Assessor (Assessor_ID [PK] , Assessor_name, Qualification, Date_of_Qualification)
Claim (Claim_ID [PK], Claim_date, Assessor_ID [FK], Policy_Number [FK], Claim_Status)
Claim_Status (Claim_Status_ID [PK], Status_Name)
Content_Item (Item_ID [PK], Item_Name, Manufacturer, Model, Purchase_Price, Quantity,
Policy_Number [FK])
Customers (Customer_ID [PK], Name, Address, Email)
HomeBuildingPolicy (Policy_Number [FK], Start_Date, Duration, End_Date)
HomeContent Policy (Policy_Number [FK], Start_Date, Duration, End_Date)
Policy (Policy_Number [PK], Policy_Date, Type, Property_ID)
Properties (Property_ID [PK], Address, Details, Customer_ID [FK])
Rejected_Claim (Claim_ID [FK], Rejected_date, Reason, Claim_ID [FK])
Report (Report_ID [PK], ReportDate, Initial_Recommendation, Final_Assessment_Date,
Total_Cost, Claim_ID [FK])
Settled_Claim (Claim_Status_ID [FK], Settled_Date, Amount_Settled, Claim_ID [FK])
Functional Dependencies
Attribute Dependencies
Assessor_ID Assessor_name, Qualification, Date_of_Qualification

2
DATABASE IMPLEMEMTATION
Claim_ID Claim_date, Assessor_ID, Policy_Number, Claim_Status
Status_Name
Item_ID Item_Name, Manufacturer, Model, Purchase_Price, Quantity,
Policy_Number
Customer_ID Name, Address, Email
Policy_Number Start_Date, Duration, End_Date
Policy_Number Start_Date, Duration, End_Date
Policy_Number Policy_Date, Type, Property_ID
Property_ID Address, Details, Customer_ID [FK]
Claim_ID Rejected_date, Reason, Claim_ID [FK]
Report_ID ReportDate, Initial_Recommendation, Final_Assessment_Date,
Total_Cost, Claim_ID
Claim_Status_ID Settled_Date, Amount_Settled, Claim_ID
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 (Customer_ID [PK], Name, Address, Email)
i. The primary key is CustomerID which identifies a customer. There will be one customer
name per customer ID because only one name is recorded for each Customer. Even
though a customer may have multiple addresses (e.g. a home address and a work
address), the case study states that there is a need to record the home address only and

3
DATABASE IMPLEMEMTATION
hence there will be only one address for each Customer. Thus, there are no repeating
groups. Hence, this relation is in 1NF.
ii. The primary key is CustomerID. This is the only candidate key since CustomerName is
not guaranteed to be unique (two different customers may have the same name). Address
is also not guaranteed to be unique. This means that CustomerID functionally determines
every other attribute in the table. There is a single valued simple candidate key
(CustomerId) and therefore no partial dependencies are possible. Hence, the relation is in
2NF.
iii. Name cannot be used to functionally determine any other attribute in the table since two
different Customers may have the same name; likewise for address and date of birth.
Therefore there are no transitive dependencies in the table. Therefore it meets the
requirements of first, second and third normal form.
Assessor (Assessor_ID [PK] , Assessor_name, Qualification, Date_of_Qualification)
i. The primary key is AssessorID which identifies a Assessor. There will be one Assessor name
per Assessor ID because only one name is recorded for each Assessor. Even though a Assessor
may have multiple addresses (e.g. a home address and a work address), the case study states that
there is a need to record the home address only and hence there will be only one address for each
Assessor. There will be only one qualification for each Assessor. Thus, there are no repeating
groups. Hence, this relation is in 1NF.
ii. The primary key is AssessorID. This is the only candidate key since Name is not guaranteed
to be unique (two different Assessors may have the same name). Address is also not guaranteed
to be unique. This means that Assessor ID functionally determines every other attribute in the

4
DATABASE IMPLEMEMTATION
table. There is a single valued simple candidate key (AssessorId) and therefore no partial
dependencies are possible. Hence, the relation is in 2NF.
iii. Name cannot be used to functionally determine any other attribute in the table since two
different Assessors may have the same name; likewise for address and date of birth. Therefore
there are no transitive dependencies in the table. Therefore it meets the requirements of first,
second and third normal form.
Implementation Report
1. The most interesting part of the assignment was the join operations. It provides the user
to join 2, 3 and more tables as per their requirement. The second topic was the report. It is
a beautiful way of presenting the data.
2. The normalization was one of the most difficult tasks for me. The ERD made by me in
the previous assessment had to be converted into the database, hence, I thought that tables
were extra in the ERD and those tables were removed from the database as they had no
primary keys.

5
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).