ITC556 Database Systems: Database Normalization and 3NF Solution

This assignment solution for ITC556 Database Systems focuses on database normalization, specifically transforming a table to the Third Normal Form (3NF). It details the process of removing partial and transitive dependencies through successive normalization steps (1NF, 2NF, and 3NF). The solution identifies functional dependencies within the initial table and demonstrates how to decompose it into multiple tables to achieve 3NF. It also includes dependency diagrams illustrating the relationships between attributes and a discussion of referential integrity to ensure data consistency after decomposition. The assignment emphasizes the importance of eliminating redundancy and maintaining a consistent database state.

ITC556 – Database System
Assessment Item3 – Normalisation
Student Name: Saketh Chava
Student ID: 11613481

Saketh Chava 11613481
Table of contents
TASK1....................................................................................................................................................2
Task 2.....................................................................................................................................................4
1NF....................................................................................................................................................4
2NF....................................................................................................................................................5
3NF....................................................................................................................................................6
TASK 3....................................................................................................................................................7
References.............................................................................................................................................8
List of figures
Figure 1: dependency diagram..............................................................................................................3
Figure 2: 1NF.........................................................................................................................................4
Figure 3: 2NF dependency.....................................................................................................................5
Figure 4: 3NF dependency.....................................................................................................................6
Figure 5: Referential Integrity................................................................................................................7
1

Saketh Chava 11613481
TASK1
The table is decomposed in 3NF; all the transitive dependency and partial dependencies
have been removed from the table. There will be in total 5 tables after decomposition
(Costa, 2017)
2 NF functional dependencies
 firstName & lastName is completely dependent to ISBN
 Lastname has complete dependency on firstName
 Editor & date is co is fully dependent on the ISBNmpletely
dependent on Title & ISBN
 Publisher & media has completely dependent to Title
2

Saketh Chava 11613481
Figure 1: dependency diagram
Functional dependencies:
 Author_firstName and edition is completely dependent on ISBN
 Author_LastName has a complete dependent upon Author_firstName
(decomposition from transitive dependency)
 Editor & date is completely dependent upon ISBN and Title
 Publisher is completely dependent on Title
 The date is completely dependent on the publisher.
 Media is completely dependent on TITLE and ISBN
3

Saketh Chava 11613481
Task 2
To crumble the home library to 3 NF set where first table is undergo 1 Normalization Form to 2
Normalization Form and then 3 Normalization form is achieved.
Primary keys
 TITLE
 ISBN
1NF
All the attributes in the table are atomic and unique such that there will not be any two
columns of the same name in the same table.
The given table is in 1 NF
Full dependency
The title is fully dependent on both the primary keys that is ISBN and TITLE
Partial dependency
 Author_firstName, Author_LastName, and Edition are partially dependent on the
ISBN
 Publisher and date are partially dependent on the TITLE.
Transitive dependency
 Author_LastName has a transitive dependency on the Author_firstName
 the date has a transitive dependency on Publisher
Figure 2: 1NF
4

Saketh Chava 11613481
2NF
After satisfying the 1 NF, the tables have been decomposed to its 2NF.
There is need to remove the partial dependency in order to be in 2 NF.
Full dependency
 media fully dependent on ISBN and Title
 Publisher and date are fully dependent on Title
 Author_firstName, Author_LastName, and edition is fully dependent on the ISBN
Transitive dependency
 Date has a transitive dependency on the publisher
 Author_lastname has a transitive dependency on Autor_firstName (Kim, 2017)
Figure 3: 2NF dependency
5

Saketh Chava 11613481
3NF
Since the table has a transitive dependency, so for being in 3 NF, partial dependency needs
to be removed from the current table.
A functional dependency in 3 NF
 Author_firstName and edition is completely dependent on the ISBN
 Author_LastName has a complete dependent on the Author_firstName
(decomposition from transitive dependency)
 Editor & date is completely dependent upon ISBN & Title
 Publisher is completely dependent upon Title
 Date is completely dependent upon publisher.
 Media is completely dependent on TITLE & ISBN (Jin, 2016)
Figure 4: 3NF dependency
6

Saketh Chava 11613481
TASK 3
Figure 5: Referential Integrity
Referential integrity determines how the tables will be linked to each other, after
decomposition to its 3 NF. The complete table is in consistent state and there is no
redundancy possible in database (Koehler, 2017).
7

Saketh Chava 11613481
References
 Costa, S. S. F., Wegmann, D., & Ripperger, J. A. (2017). Normalisation against Circadian and
Age-Related Disturbances Enables Robust Detection of Gene Expression Changes in Liver of
Aged Mice. PloS one, 12(1), e0169615.
 Jin, C., Lall, A., Xu, J. J., Zhang, Z., & Zhou, A. (2016). Distributed error estimation of
functional dependency. Information Sciences, 345, 156-176.
 Kim, J. C., Chun, S., & Lee, C. S. (2017). Multi-modal motion dictionary learning for facial
expression recognition. International Journal of Computational Vision and Robotics, 7(4),
443-453.
 Koehler, H., & Link, S. (2017). Inclusion dependencies and their interaction with functional
dependencies in SQL. Journal of Computer and System Sciences, 85, 104-131.
8