ITC556 Database Systems: Normalization to 3NF and Data Integrity

This document presents a solution to ITC556 Database Systems Assessment Item 3, focusing on database normalization and referential integrity. The solution begins by identifying functional dependencies (partial, full, and transitive) within a given table. It then demonstrates the normalization process, transforming the initial table through First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) to eliminate redundancy and maintain data consistency. Each step is explained with diagrams illustrating the dependencies and table structures. Finally, the solution addresses referential integrity, depicting the relationships between the decomposed tables and emphasizing the importance of maintaining logical connections. The document includes references to support the concepts and methodologies used in the normalization process. Desklib is a platform where students can find similar solved assignments and study resources.

ITC556 – Database System
Assessment Item3 – Normalisation
Student Name: Manpreet Singh
Student ID: 11662075

Manpreet Singh 11662075
Table of contents
TASK1....................................................................................................................................................2
TASK 2....................................................................................................................................................3
1NF....................................................................................................................................................3
2NF....................................................................................................................................................4
3NF....................................................................................................................................................5
Task 3.....................................................................................................................................................6
References.............................................................................................................................................7
List of figures
Figure 1: dependency............................................................................................................................2
Figure 2: 1NF.........................................................................................................................................3
Figure 3: 2NF.........................................................................................................................................4
Figure 4: 3NF.........................................................................................................................................5
1

Manpreet Singh 11662075
TASK1
Figure 1: dependency
This table consists of 3 functional dependency that is a partial, full and transitive dependency.
The table consists of two primary keys (Psarris, 2004)
 ISBN
 TITLE
Edition shows only full dependency with both ISBN and Title.
Autor_FirstName, Author_lastName, and date is partially dependent on ISBN
Publisher and media is partially dependent on Title
Media is dependent on a nonprime attribute Publisher which depicts transitive dependency
2

Manpreet Singh 11662075
TASK 2
Normalisation is done to remove the redundancy and maintain consistency within the database. I
have normalized the given table to 3NF and show the functional dependency at each stage
1NF
The given table is completely in 1NF. All the attributes names are unique and there exists an atomic
value for each attribute.
The table consists of two primary keys
 ISBN
 TITLE
Editor is fully dependent on both primary key ISBN and Title.
Autor_FirstName, Author_lastName, and date is partially dependent on ISBN
Publisher and media is partially dependent on Title
Figure 2: 1NF
3

Manpreet Singh 11662075
2NF
For a table to be in 2NF form there are two mandatory conditions:
 The table should be in 1NF
 There should not be any partial dependency.
The table completely satisfies the pre-required condition to be in 1NF. But there exists some partial
dependency because of two primary keys. There I have divided the complete table to remove partial
dependency (UMU, 2016).
Title table which has attributes publisher and media with title as primary key.
Author table which has attributes author_firstName, author_lastName and date with ISBN as
primary key.
There will be a different table for edition in which edition is fi=ully dependent upon the ISBN and
TITLE.
Figure 3: 2NF
4

Manpreet Singh 11662075
3NF
For a table to be in 3NF the table needs to be in 2NF first and secondly, there should be no transitive
dependency within the tables.
Media has a transitive dependency on publisher; this dependency needs to be removed to make the
table in 3NF
There will be a different table for publisher and media.
Figure 4: 3NF
Now table does not contain any partial dependency and transitive dependency, so there table is in
3NF form.
5

Manpreet Singh 11662075
Task 3
Referential Integrity
Referential Integrity depicts the dependency of on table on another table. I have shown integrity
relationships which are possible with the decomposed tables.
Referential integrity contains is helpful in maintaining the logical relationships between different
tables (Moldovan, 2006).
6

Manpreet Singh 11662075
References
 Moldovan, G., & Valeanu, M. (2006). INTEGRITY CONSTRAINTS IN DISTRIBUTED
DATABASES. Acta Universitatis Apulensis. Mathematics-Informatics, 11, 313-324.
 Psarris, K. (2004). An experimental evaluation of data dependence analysis techniques. IEEE
Transactions on parallel and Distributed systems. Vol. 15, no. 3, pp. 196-213
 UMU. (2016). Functional Dependencies and Normalization. UMU. Retrieved from
https://www8.cs.umu.se/kurser/TDBC86/H06/Slides/13_OH_fd.pdf
7