Database Design: Dependencies and Normalization to 3NF

Desklib provides solved assignments and past papers to help students succeed.

Introduction
The below-created report is based on the dependencies and normalization part. The sample
data that was being provided is being evaluated and a proper scenario is being explained in a
proper way in 4 different parts. The first part contains the dependency diagram followed by
the second part where multi-valued dependencies are shown and also a proper solution is
provided to solve the multi-valued dependencies, in third part the normalization until the 3NF
is mentioned and at last crow foot’s diagram is created using draw.io.

Tasks
Part a – Dependency Diagram
Figure 1: Dependency Diagram
In the above-mentioned diagram, there are three primary keys shown named as Emp_Name,
Dept_Code, and Prof_Office (Al-Masree, H. K. 2015). Each of the Primary keys has some of
the other attributes depended upon them. Emp_Num depends upon the Emp_Name whereas
Dept_Name depends upon the Dept_Code. Prof_Office is the next primary key on which
three different attributes are dependent named as Advisee, Committee_Code and
Journal_Code.
There is no partial and transitive Dependency present in the above-mentioned diagram rather
there is only Multi-valued Dependency present and that has to be removed to make the entire
diagram into normalized form. A multi-valued dependency could be seen in the diagram
below.
Multi-valued Dependency:
Prof_Office -> Advisee
Prof_Office -> Committee_Code
Prof_Office -> Journal_Code

Part b- Multi-valued Dependencies
Multi-valued Dependencies are mainly being removed in the Fourth Normal Form and by
removing the multi-valued dependencies, the entire data becomes properly normalized
(Molodtsov, D. A. E. 2019). The set of data being provided to us also contains the multi-
valued dependencies such as:
Prof_Office -> Advisee
Prof_Office -> Committee_Code
Prof_Office -> Journal_Code
Here, Prof_Office is the primary key present above and Committee_Code, Advisee, as well
as Journal_Code, are the independent attributes i.e. not dependent on each other. But all these
three attributes named as Committee_Code, Advisee, and Journal_Code are dependent upon
the Primary Key named as Prof_Office.
Figure 2: Multi-valued Dependencies
Multi-valued Dependencies could be removed by dividing this single dependency diagram
into different parts or the tables such as:
Figure 3: Diagram 1
Figure 4: Diagram 2
Figure 5: Diagram 3

Part c- Table Structures in 3NF
3NF is the Third Normal Form that is mainly responsible for the removal of the Transitive
dependencies. Transitive dependencies could be understood as the dependency where a non-
prime attribute is completely dependent upon the non-prime attribute. Here in the above case,
no such dependencies are found so, the shown below table structure is present in the Third
Normal Form.
Table Employee
Figure 6: Table Employee
Emp_Name -> Emp_Num
Emp_Name is the Primary Key present in the above-created table that is named as Employee
and Emp_Num is dependent upon the Primary Key Emp_Name.
Table Professor
Figure 7: Table Professor
Prof_Office -> Prof_Rank
Prof_Office is the Primary Key present in the above-created table that is named as Professor
and Prof_Rank is dependent upon the Primary Key Prof_Office.

Table Department
Figure 8: Table Department
Dept_Code -> Dept_Name
Dept_Code is the Primary Key present in the above-created table that is named as
Department and Dept_Name is dependent upon the Primary Key Dept_Code.
Table ProfessorData
Figure 9: ProfessorData
Prof_Office -> Advisee, Committee_Code, Journal_Code
Prof_Office is the Primary Key present in the above-created table that is named as
ProfessorData and Advisee, Committee_Code and Journal_Code are dependent upon the
Primary Key Prof_Office.

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

Conclusion
The below-created report is based on the dependencies and normalization part. The entire
report contains different parts and each of the part is properly explained in the report. The
final part of the assignment contains the crow’s foot diagram that is created using the tables
that are created in Third Normal Form.

References
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Zhao, P., Zhu, Y., Yu, J., Zhang, Y., & Wu, J. (2018, May). A Visualization Scheme for
Multi-Entity Relationship. In Proceedings of the 18th ACM/IEEE on Joint Conference on
Digital Libraries (pp. 401-402). ACM.
Molodtsov, D. A. E. (2019). Soft dynamical extrapolation of the multi-valued
dependencies. Nechetkie Sistemy i Myagkie Vychisleniya, 14(1), 5-18.
Xiyin, L., Lijun, C., & Zhang, Z. (2015). The study of XML functional dependency and
multi-valued dependency and inference rules set. International Journal of Database Theory
and Application, 8(6), 343-354.