Normalization in Database Systems: A Comprehensive Report

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

DATABASE SYSTEMS ASSESSMENT ITEM
3 - NORMALISATION

Contents
Introduction......................................................................................................................................3
Part A: Dependency Diagram..........................................................................................................4
Part B: Multi-valued dependency....................................................................................................5
Part C: Structure of 3NF..................................................................................................................7
Part D: Crow’s Foot Diagram..........................................................................................................9
Conclusion.....................................................................................................................................10
References......................................................................................................................................11
Figure 1: Dependency Diagram.......................................................................................................4
Figure 2: Multi-valued dependency.................................................................................................5
Figure 3: COMMITTEE_CODE....................................................................................................5
Figure 4: JOURNAL_CODE...........................................................................................................6
Figure 5: ADVISEE.........................................................................................................................6
Figure 6: Table PROF_DETAIL.....................................................................................................7
Figure 7: Table PROFESSOR.........................................................................................................7
Figure 8: Table DEPARTMENT.....................................................................................................8
Figure 9: Table EMPLOYEE..........................................................................................................8
Figure 10: Crow’s Foot Notation.....................................................................................................9

Introduction
The assessment is based on the normalization in which a table is provided that need is
normalized and need to show properly in the assessment. Normalization is used to eliminate the
redundancy from the relations that are provided in the table so the overall report will consist of
various tables and diagrams through which the provided data can be normalized appropriately.
The three forms of normalization are 1NF, 2NF, and 3NF. So in the given table the task will be
to identify the conditions of these normal forms and convert the overall table into 3 NF.

Part A: Dependency Diagram
Figure 1: Dependency Diagram
The overall dependency diagram that is created by using the given table is mentioned above with
all the dependencies of the entities. There are three entities that are a primary key named:
 DEPT_CODE
 EMP_NAME
 PROF_OFFICE
All these entities cannot have duplicate values as the primary key helps in unique identification
so these are mentioned in the table with the bold and underlined font. Other entities apart from
these entities that are dependent on the primary keys are given as:
 DEPT_NUM
 EMP_NUM
 COMMITTEE_CODE
 JOURNAL_CODE
 ADVISEE
 PROF_RANK
All these are the other entities that are added in the dependency diagrams and they are dependent
on other entities (Brahimi et al., 2016). So on closer scrutiny, it can be noticed that instead of
partial dependency that can be removed in 2NF and the transitive dependency that can be
removed in 3NF are not present but multi-valued dependency can be seen and need to be
eliminated. So this is explained in the coming part of the report.

Part B: Multi-valued dependency
This dependency occurs when there is more than one attribute present that is independent with
each other but has dependency on the attribute which is the primary key. In the overall
dependency diagram t this condition is noticed in the following table:
Figure 2: Multi-valued dependency
Therefore, the above image shows the multi-valued dependency where the dependency occurs
among:
PROF_OFFICE COMMITTEE_CODE
PROF_OFFICE JOURNAL_CODE
PROF_OFFICE ADVISEE
And here PROF_OFFICE is the primary key on which these three particular attributes are
dependent but they are independent among each other (Ferreira, 2016). This shows the 4NF and
is eliminated by dividing the table into the following tables:

Figure 3: COMMITTEE_CODE
Figure 4: JOURNAL_CODE
Figure 5: ADVISEE

Part C: Structure of 3NF
Normalization has three different normal forms 1NF, 2NF, and 3NF. In order to bring the table
into third normal form it is essential to eliminate transitive dependency from the table. This
dependency condition can be found when the non-prime attributes are dependent on the non-
prime attribute (Chun et al., 2018). And according to the provided table this condition cannot be
seen which says that the transitive dependency is absent in the table.
Different structures of the tables are provided here:
PROF_DETAIL:
Figure 6: Table PROF_DETAIL
The above table shows that the PROF_OFFICE is the primary key and all other independent
attributes are dependent on the PROF_OFFICE attribute. The complete table is named as
PROF_DETAIL that shows the details of the professor. The primary key is shown with the bold
and underlined font.
PROFESSOR:
Figure 7: Table PROFESSOR

This table has PROF_OFFICE as its primary attribute on which PROF_RANK attribute is
dependent. The name of the table is a PROFESSOR here. The primary key is shown with the
bold and underlined font.
DEPARTMENT:
Figure 8: Table DEPARTMENT
This table is named as the DEPARTMENT table in which DEPT_CODE is primary key and
DEPT_NUM is dependent on the primary key (Khan & Rao, 2001). The display of the primary
key is in font bold and underlined.
EMPLOYEE:
Figure 9: Table EMPLOYEE
This is the EMPLOYEE table in which EMP_NAME is the primary key on which attribute
EMP_NUM is dependent. The attribute is primary key that is displayed in font bold and
underlined.

Part D: Crow’s Foot Diagram
The Crow’s foot notation is provided here that will show the table name along with its column
names showing the constraints like foreign key and primary key. Along with the relationships
that are shown here:
Figure 10: Crow’s Foot Notation

Conclusion
The overall report contains the detail information of the normalization with the help of diagram
and description is also given. By the above assessment, it can be understood that normalization is
used in order to eliminate the redundancy that may present in the table. Three different forms of
normalization are understood above. Additionally the knowledge of multi-valued dependency is
also shown that is included in the 4th normal form of the normalization. This dependency is
eliminated by dividing the whole table into three different tables. So, overall assessment
provided an in-depth knowledge of the normalization.