Database Normalization and Multi-valued Dependencies

This report delves into the process of database normalization, focusing on the identification and removal of multi-valued dependencies. It presents a comprehensive analysis of a given dataset, demonstrating how to achieve Third Normal Form (3NF) and address multi-valued dependencies through table decomposition. The report includes a dependency diagram, multi-valued dependency analysis, table structures in 3NF, and a Crow's Foot diagram illustrating the relationships between entities.

Table of Contents
Introduction................................................................................................................................2
Task a – Dependency Diagram..............................................................................................3
Task b- Multi-valued Dependencies......................................................................................4
Task c- Table Structures in 3NF............................................................................................5
Task d- Crow’s Foot Diagram................................................................................................7
Conclusion..................................................................................................................................8
References..................................................................................................................................9
List of Figures
Figure 1: Dependency Figure.....................................................................................................3
Figure 2: Multi-valued Dependencies........................................................................................4
Figure 3: Division 1...................................................................................................................4
Figure 4: Division 2...................................................................................................................4
Figure 5: Division 3...................................................................................................................4
Figure 6: Empl table...................................................................................................................5
Figure 7: Prof Table...................................................................................................................5
Figure 8: Dept Table..................................................................................................................5
Figure 9: InfoOfProf Table........................................................................................................6
Figure 10: Crow’s Foot Diagram...............................................................................................7

Introduction
The entire report is being created based on the topic Normalisation where the data that was
being provided is purely normalized into the final form that is the Third Normal Form. Multi-
valued attributes are also found within the scenario and that is being removed in the Fourth
Normal Form. Each and every normal form is explained properly within the report and every
part of the normalization is explained properly. At the last of the report, crow’s foot diagram
is also being created using the draw.io.

Task a – Dependency Diagram
Figure 1: Dependency Figure
The above diagram shows the dependency diagram properly, as we could easily see the
presence of the Primary keys that are Emp_Name for uniquely identifying each of the
employees with their names, Dept_Code helps in uniquely identifying each of the department
successfully and at last the Prof_Office for uniquely identifying the Professor details
successfully (Roibás et al., 2018). As with the help of the diagram, it could be easy to predict
that there are no partial as well as transitive dependencies present and the only dependency
that is present is the Multi-valued dependency. It can be seen as:
Multi-valued Dependency:
Prof_Office -> Advisee, Committee_Code, Journal_Code

Task b- Multi-valued Dependencies
Multi-valued dependencies could be defined as the dependency where a single primary key
has a lot of other attributes dependent upon them but the condition is that the attributes must
not be dependent to each other (Puente, V. L. 2016). For removing such dependencies the
data must be in the Fourth Normal Form.
Prof_Office -> Advisee
Prof_Office -> Committee_Code
Prof_Office -> Journal_Code
Above mentioned multi-valued dependencies, are being explained as that Advisee is purely
dependent upon the primary key that is named as Prof_Office. Similarly, the other two
attributes are also dependent upon the primary key Prof_Office.
Figure 2: Multi-valued Dependencies
The above diagram signifies the Multi-valued dependencies and the below diagrams shows
the solution or the removal of the multi-valued dependencies.
Figure 3: Division 1
Figure 4: Division 2

Figure 5: Division 3
Task c- Table Structures in 3NF
As it is mentioned above properly that there is no presence of the partial or the transitive
dependencies therefore, all the tables are already in the Third Normal Form (Goelman et al.,
2018). Just the only thing that is to be done here is separating of all the tables in order to
identify each of the data separately.
Table Empl
Figure 6: Empl table
Emp_Name -> Emp_Num
Table Prof
Figure 7: Prof Table
Prof_Office -> Prof_Rank
Table Dept
Figure 8: Dept Table

Dept_Code -> Dept_Name
Table InfoOfProf
Figure 9: InfoOfProf Table
Prof_Office -> Advisee, Committee_Code, Journal_Code

Task d- Crow’s Foot Diagram
Figure 10: Crow’s Foot Diagram

Conclusion
The created report is purely based on the Normalisation part where the final output is in the
normalized form. Different normal forms are explained in the report and a final crow’s foot
diagram is created on draw.io on the basis of all the based tables. The report also shows the
multi-valued dependencies clearly in the report.

References
Goelman, D., & Dietrich, S. W. (2018, February). A Visual introduction to conceptual
database design for all. In Proceedings of the 49th ACM Technical Symposium on Computer
Science Education (pp. 320-325). ACM.
Roibás, L., Loiseau, E., & Hospido, A. (2018). On the feasibility and interest of applying
territorial Life Cycle Assessment to determine subnational normalisation factors. Science of
The Total Environment, 626, 1086-1099.
Puente, V. L. (2016). Learning Sets of Antecedent-restricted Functional and Multivalued
Dependencies with Queries. Theory of Computing Systems, 59(3), 500-516.