Database Normalization Report: Dependency Diagrams and 3NF

Desklib provides solved assignments and past papers to help students understand database normalization.

Contents
Introduction......................................................................................................................................2
A. Dependency diagram..................................................................................................................3
B. Multi-valued dependency............................................................................................................4
Dependencies...............................................................................................................................4
C. 3NF structure...............................................................................................................................6
Table Employee...........................................................................................................................6
Table Detail Professor.................................................................................................................7
Table Department........................................................................................................................7
Table Professor............................................................................................................................8
D. Crow Foot Notation....................................................................................................................9
Conclusion.....................................................................................................................................11
References......................................................................................................................................12
Figure 1: Dependency Diagram.......................................................................................................3
Figure 2: Multi-Valued....................................................................................................................4
Figure 3: Advisee.............................................................................................................................4
Figure 4: Journal Code.....................................................................................................................5
Figure 5: Committee Code...............................................................................................................5
Figure 6: Employee..........................................................................................................................6
Figure 7: Table Detail Professor......................................................................................................7
Figure 8: Table Department.............................................................................................................7
Figure 9: Table Professor.................................................................................................................8
Figure 10: Crow Foot Diagram......................................................................................................10

Introduction
The normalization is defined in this assignment report using the dependency diagram and the
dependencies. The report consists of the ERD diagram which shows the dependencies in the
database. The process of normalization is defined as the process of reducing the relations in the
database and presenting the data. The process of normalization consists of many forms which
mainly include the 1NF, 2NF, 3NF, and BCNF. The data of the database is represented in the
form of graphs and diagrams. This process helps in the removal of redundant data so that the
integrity of the data can be improved. It helps in the scaling of data and store productivity.

A. Dependency diagram
The dependency diagram shows the dependencies of any system and its process flow. The
diagram here represents the working of the process according to the provided table. Here, there
are nine different entities that are dependent on each other and these dependencies are shown
with the help of the diagram here (Molodtsov, 2019). It can be seen that the entity EMP_NUM is
dependent on EMP_NAME that is a prime attribute. Another dependency can be seen here is
among the non-prime attribute that is DEPT_NUM which is dependent on DEPT_CODE which
is a prime attribute. PROF_OFFICE is another prime attribute on which PROF_RANK is
dependent and other three non-prime attributes are also dependent.
Figure 1: Dependency Diagram

B. Multi-valued dependency
Figure 2: Multi-Valued
A Multi-Valued dependency can be seen when the other non-prime attributes are dependent on
prime attributes but they do not depend on each other. These attributes do not depend on each
other but all the attributes dependent on PROF_OFFICE so this problem can be resolved by
splitting the tables into three different tables. They are provided here:
Dependencies
Figure 3: Advisee

Figure 4: Journal Code
Figure 5: Committee Code

C. 3NF structure
The third normal form of the normalization can be achieved by eliminating various dependencies
and redundancies that can be observed in the tables (Jukic et al., 2016). The table that is provided
has some redundancy that needs to be removed and this is possible by dividing the tables. There
are overall four tables that include table EMPLOYEE, table DEPARTMENT, and table
DETAILPROFESSOR and table PROFESSOR.
Table Employee
Figure 6: Employee
The above diagram shows the employees table in which a non-prime attribute named as
EMP_NUM is dependent on the prime attribute that is named as EMP_NAME (Coronel &
Morris, 2016).

Table Detail Professor
Figure 7: Table Detail Professor
The table here is named as DetailProfessor as the table consists of the information of the
professor. Here the prime attribute is PROF_OFFICE on which other non-prime attributes are
dependent. This can be seen in the above diagram.
Table Department
Figure 8: Table Department
The diagram here shows the department table where the non-prime attribute is dependent on the
prime attribute that is the DEPT_NUM is dependent on the DEPT_CODE.

Table Professor
Figure 9: Table Professor
The table PROF_OFFICE is the prime attribute here on which the PROF_RANK is dependent so
the table is normalized and given above.

D. Crow Foot Notation
This provided diagram is the entity-relationship diagram that is drawn using crow’s foot
notation. There are four entities that are related to each other and they have different attributes as
well. All the constraints like primary and foreign keys are added in the entity-relationship
diagram. The relationships are also added that include one-to-many and one-to-one relationships.
Foreign keys are in italic font and primary keys are bold and underlined.

Figure 10: Crow Foot Diagram

Conclusion
The dependency and the dependencies diagram that was discussed in the report above are used
for making the normalization understand. From this, the understanding of the normalization and
how it is used in the database management system becomes clear and easy. The report has
included the types of normalization and which normalization is used in which case. The report
has concluded the use of dependencies and how the diagrams of the dependency are made. The
report consists of the diagram for the crowfoot and 3NF which shows the relationship between
the entities, attributes, and the keys like foreign and primary.

References
 Coronel, C., & Morris, S. (2016). Database systems: design, implementation, &
management. Cengage Learning.
 Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases
and data warehouses ProspectPress.
 Molodtsov, D. A. E. (2019). Soft dynamical extrapolation of the multi-valued
dependencies. Nechetkie Sistemy i Myagkie Vychisleniya, 14(1), 5-18.