Normalization of Database Systems: A Comprehensive Report

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

DATABASE SYSTEMS ASSESSMENT ITEM
3 - NORMALISATION

Contents
Introduction......................................................................................................................................2
Dependency Diagram......................................................................................................................3
Multivalued dependencies...............................................................................................................4
Structure of 3NF..............................................................................................................................6
Crow’s Foot ERD............................................................................................................................8
Conclusion.......................................................................................................................................9
References......................................................................................................................................10
Figure 1: Dependency Diagram.......................................................................................................3
Figure 2: Multivalued Dependency.................................................................................................4
Figure 3: Advisee.............................................................................................................................4
Figure 4: Journal Code.....................................................................................................................4
Figure 5: Committee Code...............................................................................................................5
Figure 6: Department.......................................................................................................................6
Figure 7: Employee..........................................................................................................................6
Figure 8: Professor...........................................................................................................................7
Figure 9: InfoProfessor....................................................................................................................7
Figure 10: ER Diagram....................................................................................................................8

Introduction
The report consists of assessment item 3 and contains the normalization of the given data. There
are various tables and diagram that are provided in the report so as to explain the normalization
of the provided data appropriately. The assessment is based on the data that is given. There are
overall three different parts of the assessment that is covered in the report.

Dependency Diagram
Figure 1: Dependency Diagram
The given dependency diagram is based on the tables that are provided. There are nine entities
that are dependent on each other and they are shown in the above diagram. The names of the
entities are Emp_Num, Emp_Name, Dept_Code, Dept_Num, Prof_Room, Prof_Office, Advisee,
Journal_Code, Committee_Code. These are all different entities (Jukic et al., 2016).
The diagram shows that there are no partial as well as transitive dependencies present so there is
no need to eliminate the partial & transitive dependencies. But there is a multivalued dependency
that is present and it needed to be removed. The dependency is shown below in the next part.

Multivalued dependencies
A multivalued dependency is a dependency that exists when multiple attributes are independent
of each other but they are dependent on primary key attribute (Shora et al., 2016). So here this
kind of situation occurs in the given dependency:
Figure 2: Multivalued Dependency
The above-multivalued dependency shows the dependency of the entities advisee, journal_code
and committee_code on the entity prof_office.
Prof_Office Advisee
Prof_Office Journal_Code
Prof_Office Committee_Code
So this displays the multivalued dependency that is included in 4NF and needed to be removed.
For eliminating, we have divided the tables and mentioned here:
Figure 3: Advisee
Figure 4: Journal Code

Figure 5: Committee Code
This is how the multivalued dependency is eliminated by splitting the tables.

Structure of 3NF
The third normal form is removed so as to remove the transitive dependency that may occur in
the tables and to normalize the table it is essential to remove it (Eessaar, 2016). Transitive
dependency occurs when a non-prime attribute gets dependent on another non-prime attribute.
So this type of structure cannot be seen in the above dependency diagram that means there is no
transitive dependency occurring here.
There are table structures that are given here:
Table Department:
Figure 6: Department
This is the department table in which Dept_Code is the primary key and the non-prime attribute
Dept_Num is dependent on the prime attribute dept_code.
Table Employee:
Figure 7: Employee
This is the employee table that contains the Emp_Name as the primary key because in the given
data all employees have a unique name for unique identification and the non-prime attribute
emp_num is dependent on Emp_Name.
Table Professor:

Figure 8: Professor
This is the professor table in which Prof_Office is the primary key and the Prof_Rank is
dependent on the primary key attribute Prof_Office.
Table InfoProfessor:
Figure 9: InfoProfessor
This shows the multivalued dependency as the Advisee, journal_code and committee code are
dependent on the primary key named as Prof_Office. This is the multivalued dependency that is
eliminated in the previous section of the task.

Crow’s Foot ERD
The Crow’s foot Entity-relationship diagram is provided here that consists of various attributes
and relationships (Gaikwad et al., 2017). The following diagram is created by using draw.io and
the image is attached here:
Figure 10: ER Diagram

Conclusion
The above report consists of the overall understanding of the normalization and its different
form. Here the understanding of the 4NF is also developed that is related to the multivalued
dependency that is eliminated by dividing the table structure. The overall report consists of
various diagrams related to the table and their explanation.

References
 Eessaar, E. (2016). The database normalization theory and the theory of normalized
systems: finding common ground. Baltic Journal of Modern Computing, 4(1), 5.
 Shora, A. R., Alam, A., & Biswas, R. (2016). Intuitionistic Fuzzy Multivalued
Dependency and Intuitionistic Fuzzy Fourth Normal Form. In Proceedings of the 4th
International Conference on Frontiers in Intelligent Computing: Theory and Applications
(FICTA) 2015 (pp. 393-404). Springer, New Delhi.
 Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to
databases and data warehouses. Prospect Press.
 Gaikwad, A. S., Kadri, F. A., Khandagle, S. S., & Tava, N. I. (2017). Review on
Automation Tool for ERD Normalization. International Research Journal of Engineering
and Technology (IRJET)[Online], 4(2), 1323-1325.