Database Design: Normalization Assignment - Patient Report Analysis

This assignment solution addresses a database design problem involving the normalization of a patient report table. The solution begins with a dependency diagram illustrating the relationships between attributes within the table. It then identifies the initial normal form of the table. The core of the solution involves decomposing the table into a set of 3NF (Third Normal Form) tables, with accompanying dependency diagrams for each. Finally, the solution presents a relational data model, including all necessary attributes and integrity constraints. The assignment demonstrates an understanding of database normalization principles, including the identification and removal of partial and transitive dependencies, and the creation of an entity-relationship diagram.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University
Author’s name:

1DATABASE DESIGN
Table of Contents
1. Dependency Diagram:............................................................................................................2
2. Normal Form:.........................................................................................................................2
3. Normalization:........................................................................................................................2
4. Relational Database Model:...................................................................................................4
Bibliography:..............................................................................................................................5

2DATABASE DESIGN
1. Dependency Diagram:
Figure 1: Dependency Diagram of Patient Report Table
(Source: Created by Author)
2. Normal Form:
The proposed table is in the first normal form. The proposed table has a primary key
which fulfils the first condition of the first normal form. Another condition of the first normal
for is that every attribute must have only atomic values. The proposed table has only atomic
values therefore it is in the first normal form. Now, the second normal form states that the
non-key attributes must be dependent on only the key attributes. However, as shown in the
dependency diagram of proposed database, the non-key attributes are dependent on other
non-key attributes. This is why the table is not in the second normal form. If the table is not
in second normal form, it cannot be in third normal form.
3. Normalization:
First Normal Form: As shown in the figure 1 dependency diagram, the table is in the
first normal form.
Second Normal Form: The proposed table has partial dependency. As shown in the
table, the Triage_Category, Clinician_name, Clinician_location, Departing_status are

3DATABASE DESIGN
dependent on the Presenting_problem. If the partial dependency is removed from the table
then a new table will be created. The new table will be named as Problem. The problem_id
will be the primary key of the table and it will be used as foreign key in Patient table.
Third Normal Form: The third normal form remains in a table when there is at least
one transitive dependency among minimum three attributes (Coronel & Morris, 2016). The
new table name clinic is created for removing the transitive dependency from Problem table.

4DATABASE DESIGN
4. Relational Database Model:
Figure 1: Entity Relationship Diagram of Patient Report Table
(Source: Created by Author)

5DATABASE DESIGN
Bibliography:
Albarak, M., Alrazgan, M., & Bahsoon, R. (2017). Database Normalization Debt: A Debt-
Aware Approach to Reason about Normalization Decisions in Database
Design. arXiv preprint arXiv:1711.06109.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R., & Navathe, S. (2016). Fundamentals of database systems. London: Pearson.
Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., ... & Lin, M.
(2015). How I do it: a practical database management system to assist clinical
research teams with data collection, organization, and reporting. Academic
radiology, 22(4), 527-533.