COIT20247 Database Design and Development: Normalization to 3NF

This report focuses on database design and development, specifically addressing normalization to the Third Normal Form (3NF). It includes a set of relations, functional dependencies, and a demonstration of 3NF decomposition. The report details the normalization process for assessor and claim-related entities, ensuring that there are no transitive dependencies and that non-key attributes are dependent on the primary key. It also covers the implementation of a report using queries and wizards, emphasizing the removal of transitive dependencies for database development. The report references various academic sources to support its methodology and findings, providing a comprehensive overview of database normalization principles and their practical application.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database Design and Development
Name of the Student
Name of the University
Author’s Note

1
DATABASE DESIGN AND DEVELOPMENT
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 Assessor_ID [PK] , Assessor_name, Qualification,

2
DATABASE DESIGN AND DEVELOPMENT
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]
Third Normal Form Decomposition
The solution is normalized into third normal form because there is no transitive dependency
between the table and the non key attributes are dependent on the primary key.

3
DATABASE DESIGN AND DEVELOPMENT
The third normal form is normalized and ER diagram is used for the demonstration of the 3 NF.
Assessor (Assessor_ID [PK] , Assessor_name, Qualification, Date_of_Qualification)
i. The primary key is Assessor_ID which identifies an Assessor name. An Assessor name
is used per Assessor ID. The other details such as thee qualification, date of qualification is
also recorded in the assessor table and there are no repetitive groups.
ii. The primary key is Assessor_ID and there are no candidate keys and thus there are no
partial dependency in the table.
iii. The Assessor Name cannot be used for determining 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 report is prepared by combining the queries and the wizard and from the design view
the report option is selected for the development of the report from the database.

4
DATABASE DESIGN AND DEVELOPMENT
2. For the preparation of the report the tables are normalized into third normal form and the
transitive dependencies are removed for the development of the database.

5
DATABASE DESIGN AND DEVELOPMENT
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., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Hogan, R. (2018). A Practical Guide to Database Design. Chapman and Hall/CRC.
Hussain, M. W. U. (2015). Design and development of an advanced database system with
multitenant architecture advanced security using transparent data encryption data
redaction.
Liu, C., Zhang, L., & Pan, Q. (2016). Database Design and Implementation of Natural Disaster
Monitoring System. Journal of Residuals Science & Technology, 13(5).
Liu, P., Yao, G. Q., Cai, H. Y., & Yang, Z. (2015, November). Design and Implementation of
Warehouse Management System Based on B/S Mode. In Computer Science and
Applications (CSA), 2015 International Conference on (pp. 146-150). IEEE.
Nascimento, L. M. E., Ferreira, A. C. M., & Gonzalez, S. A. (2018, March). Design and
development of a geo-referenced database to radionuclides in food. In Journal of
Physics: Conference Series (Vol. 975, No. 1, p. 012045). IOP Publishing.

6
DATABASE DESIGN AND DEVELOPMENT
Parks, R. F., & Hall, C. (2016). Front-End and Back-End Database Design and Development:
Scholar’s Academy Case Study. Information Systems Education Journal, 14(2), 58.
Stonebraker, M., Deng, D., & Brodie, M. L. (2017). Application-Database Co-Evolution: A New
Design and Development Paradigm. New England Database Day, 1-3.