Data Normalization in Databases: A Practical Approach (ITC 556)

Desklib provides past papers and solved assignments for students. This project demonstrates data normalization techniques.

ITC 556
Assessment item 3
Student ID- 11613503
Student name- Karunakar Burugula

Contents
DATA NORMALIZATION:..............................................................................................................................3
(a) Dependency Diagram:....................................................................................................................4
(b) 1NF:................................................................................................................................................5
2NF:.....................................................................................................................................................5
(c) Crow’s Foot ERD:................................................................................................................................7
Conclusion...................................................................................................................................................8
References...................................................................................................................................................9

DATA NORMALIZATION:
Normalization is the process to minimize the redundancy and anomalies from the tables.
Anomalies include Insert, update and delete. Data Normalization increase the consistency of the
database and there are three most common forms of normalization, 1NF, 2NF, and 3NF.
Dependency diagram is used to show the relationships among every entity and attributes which
are present in a given table. Dependency is represented by rectangles representing attributes and
an arrow showing dependency. The following scenario is about a building in which different
rooms are present and in every room, more than one item can be present. We had applied the
normalization technique to remove partial dependency as well as a transitive dependency which
comes under 2NF and 3NF normalization form. To reach till 3NF, it is necessary to maintain the
data in the 1NF form, that is, in atomic form, which means every table should have a single value
and in a 2NF form which means partial dependency should not be present. Then only we can
remove the transitive dependency from the table which comes under the 3NF form.
After data normalization, we can finally draw the Crow’s Foot ERD. This diagram represents
entities as boxes and relationships as lines between the boxes. Therefore, the whole relation
between the entities can be easily represented by this type of diagram.

(a) Dependency Diagram:
Figure 1: DEPENDENCY DIAGRAM
Partial Dependencies:
Relation1: (ITEM_ID, ITEM_LABEL)
Relation2: (BLDG_CODE, BLDG_NAME, BLDG_MANAGER)
BLDG_CODE, ROOM_NUMBER, and ITEM_ID are primary keys here because both can be
uniquely identified. Using BLDG_CODE we can easily find the name and manager of the
building but it is creating the partial dependency and ITEM_LABEL is dependent on ITEM_ID
which is also creating partial dependency, therefore, to normalize the data we need to remove the
partial dependency which is occurring.
Partial Dependency occurs when any non-prime attribute is depending on a prime attribute. In
this case, BLDG_NAME and BLDG_MANAGER are non-prime attributes which are dependent
on a prime attribute which is BLDG_CODE. So we will remove this dependency for further
normalization.

(b) 1NF:
Figure 2: AVAILIBILE_ITEMS
AVAILIBILE_ITEMS (BLDG_CODE, ROOM_NUMBER, ITEM_ID, ITEM_LABEL,
BLDG_NAME, BLDG_MANAGER)
2NF:
Figure 3: ITEMS
Figure 3: BLDG

Figure 4: AVAILABLE_ ITEMS
ITEMS (ITEM_ID, ITEM_LABEL)
BLDG (BLDG_CODE, BLDG_NAME, BLDG_MANAGER)
AVAILABLE_ ITEMS (BLDG_CODE, ROOM_NUMBER, ITEM_ID)
This is how we can normalize the given database by decomposing the table, we need not convert
the table into 3NF form now, and as at this level, we had already achieved the most normalized
form of the data as there is no transitive dependency occurring in the scenario.

(c) Crow’s Foot ERD:
Figure 6: Crow’s Foot ERD

Conclusion
The conclusion of the given scenario is that BLDG_CODE and ROOM_NUMBER are acting as
primary keys but some attributes are depending on BLDG_CODE and not on ROOM_NUMBER
that create a partial dependency that is why we have created another entity named Building and
made BLDG_CODE, BLDG_NAME, and BLDG_MANAGER as its attributes. BLDG_CODE
acting as a foreign key in the entity named AVAILABLE_ITEMS and ROOM_NUMBER as its
primary key. There is one to many relationships between the entity BLGD and
AVAILABLE_ITEMS, as one building can have more than one AVAILABLE_ITEMS. Similar,
ITEM and AVAILABLE_ITEMS are also related with each other through one to many
relationships.

References
 McMinn, P. S., Wright, C. J., McCurdy, C. J., & Kapfhammer, G. M. (2017). Automatic
detection and removal of ineffective mutants for the mutation analysis of relational
database schemas. IEEE Transactions on Software Engineering.
 DiScala, M., & Abadi, D. J. (2016, June). Automatic generation of normalized relational
schemas from nested key-value data. In Proceedings of the 2016 International
Conference on Management of Data (pp. 295-310). ACM.
 Papenbrock, T., & Naumann, F. (2017). Data-driven Schema Normalization.
In EDBT (pp. 342-353).