Database Normalization and Design Assignment Solution - Fall 2024

This assignment solution addresses a database normalization problem, starting with a sample professor record table imported from an Excel spreadsheet. The solution includes a dependency diagram to illustrate attribute dependencies within the table. It identifies and explains the multivalued dependencies present, focusing on how attributes like advisee, committee code, and journal code relate to the employee number. The solution then normalizes the table to achieve the third normal form (3NF), creating new tables to store prof rank and prof office values and removing transitive dependencies. Finally, the assignment concludes with an Entity Relational Diagram (ERD) to visualize the database structure. The solution is supported by a bibliography of relevant sources.

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

1DATABASE NORMALIZATION AND DESIGN
Table of Contents
Dependency Diagram:.....................................................................................................................2
Multivalued Dependency:................................................................................................................2
Normalization:.................................................................................................................................2
Entity Relational Diagram:..............................................................................................................4
Bibliography:...................................................................................................................................5

2DATABASE NORMALIZATION AND DESIGN
Dependency Diagram:
Figure 1: Dependency Diagram
(Source: Created by Author)
Multivalued Dependency:
The multivalued dependency states that two or more attributes are independent of each
other but dependent on another attribute. In this case, the advisee, committee code and journal
code are independent of each other but dependent on the employee number.
The representation of the multivalued dependency is as following.
EMP_NUM   ADVISEE
EMP_NUM   COMMITTEE_CODE
EMP_NUM   JOURNAL_CODE
Normalization:
The advisee, committee_code and journal_code are multivalued attributes in the table. If
these attributes are converted into atomic values then following table is create for a single row
instance. Then for each atomic value an instance of the employee has to be created.

3DATABASE NORMALIZATION AND DESIGN
The second normal form states that a database must not have any partial dependency. The
partial dependency has been shown in the following section.
To normalize the table, two new tables are created which store the prof rank and prof
office values.
The third normal form is achieved by removing the transitive dependency. The following
diagram shows the transitive dependency in the employee table.
After the transitive dependency is removed, the database becomes following.

4DATABASE NORMALIZATION AND DESIGN
Entity Relational Diagram:
Figure 2: Entity Relational Model
(Source: Created by Author)

5DATABASE NORMALIZATION AND DESIGN
Bibliography:
Attallah, B, "Simplifying Database Normalization within a Visual Interactive Simulation
Model.". in International Journal of Database Management Systems, 9, 2017, 57-69.
Zeng, H, C Qiu, & Q Cui, "Drug-Path: a database for drug-induced pathways.". in Database,
2015, 2015, bav061.