ITC556 Database Systems: Normalization and Relational Schema Design

This report provides a comprehensive analysis of database normalization, specifically focusing on achieving the Third Normal Form (3NF) for a 'Home Library' database. It begins with identifying functional dependencies within the initial database schema and represents these dependencies through diagrams. The report then details the step-by-step decomposition of the 'Home Library' relation into a set of 3NF relations, starting from the First Normal Form (1NF), progressing through the Second Normal Form (2NF), and culminating in the Third Normal Form (3NF). Each normalization step is explained with clear diagrams and justifications. The final output includes relational schemas for each 3NF relation, along with integrity constraints, ensuring data consistency and validity. The report uses ISBN, Book and Author tables to achieve normalization. This document available on Desklib, is a valuable resource for students studying database systems.

ITC556
Database Systems
ASSESSMENT 3
NORMALIZATION
Student Name-
Student ID-
1

Table of Contents
Functional Dependency Diagram......................................................................................3
Decomposing Home Library into set of 3NF relations.......................................................5
First Normalization.........................................................................................................5
Second Normalization....................................................................................................5
Third Normalization........................................................................................................7
Relational Schema for each 3NF relation and Integrity Constraints.................................8
References.........................................................................................................................9
List of Figures
Figure 1- ISBN..................................................................................................................3
Figure 2- Book..................................................................................................................3
Figure 3- Author...............................................................................................................4
Figure 4- Final Dependency Diagram............................................................................4
Figure 5- First Normal Form...........................................................................................5
Figure 6- ISBN 2nd Normal Form.....................................................................................6
Figure 7- Book 2nd Normal Form...................................................................................6
Figure 8- Book 3rd Normal Form...................................................................................7
Figure 9- Author 3rd Normal Form.................................................................................7
Figure 10- Relational schema.........................................................................................8
2

Functional Dependency Diagram
When an attribute in any relation depends on some other attribute in a form X-> Y, then
it is known as a functional dependency. Here Y is the attribute which depends on X
(Beeri& Honeyman,1981).
The table which has been given, here Title and ISBN can act as a pair of candidate
keys. ISBN is the primary key.
The functional dependency diagrams for each table from the given table can be drawn
as:
Figure 1- ISBN
ISBN can be the primary key. The non-prime attributes are Date, Edition, Publisher, and
Media.
3

Figure 2- Book
The Title is the primary key. The attribute depending on it is AuthorID.
Figure 3- Author
The primary key is AuthorID. The non-prime attributes are Author_Firstname and
Author_LastName. Author_LastName depended on Author_LastName causing
transitive dependency. Therefore, a new entity was formed.
Final Dependency Diagram
4

This diagram displays all the dependencies in the relation given.
Figure 4- Final Dependency Diagram
Decomposing Home Library into a set of 3NF relations
First Normalization
Home Library (ISBN, Title, Author_FirstName, Author_Lastname, Publisher, Date,
Edition, Media)
5

Figure 5- First Normal Form
The values are not repetitive, and each column has only one value, justifying first
normal form.
Second Normalization
For a relation to justifying the second level of normalization, the partial dependencies
from it should be removed (Dongare et.al, 2011).
The relation Home Library (ISBN, Title, Author_FirstName, Author_Lastname,
Publisher, Date, Edition, Media) contained two partial relational dependencies:
ISBN (ISBN, Date, Edition, Publisher, Media)
Title (Title,Author_FirstName, Author_LastName)
Therefore, ISBN and Book were the two entities created.
ISBN (ISBN, Date, Edition, Publisher, Media)
6

Figure 6- ISBN 2nd Normal Form
The primary key is ISBN, also being the only candidate key. Therefore, no case of
partial dependency exists. So, it fulfills the rule of the 2nd level of normalization.
Book (Title, Author_FirstName, Author_Lastname)
Figure 7- Book 2nd Normal Form
The primary key is Title, also being the only candidate key. Therefore, no case of partial
dependency exists. So, it fulfills the rule of the 2nd level of normalization.
7

Third Normalization
For being in the third state of normal form, there should be no transitive dependency, no
functional dependency between two non-prime attributes (Fuller,2006).
In the relation Book (Title, Author_FirstName, Author_Lastname), Author_LastName
depended on Author_LastName causing transitive dependency. Thus, new entity Author
was formed.
Book (Title, AuthorID)
Figure 8- Book 3rd Normal Form
Due to the presence of a single non-prime attribute, there is the null possibility of
transitive dependency making the relation third normalized.
Author (AuthorID, Author_FirstName, Author_Lastname)
8

Figure 9- Author 3rd Normal Form
Both Author_FirstName andAuthor_Lastname depend on AuthorID. Therefore, there is
no transitive dependency making the relation third normalized.
Relational Schema for each 3NF relation and Integrity Constraints
ISBN (ISBN, Date, Edition, Publisher, Media)
Book (Title, AuthorID,ISBN)
Author (AuthorID, Author_FirstName, Author_Lastname)
*The bold and underline signifies the attribute is the primary key and the italic signifies it
is a foreign key. The primary key cannot be null. This integrity constraint holds for every
primary key.
9

Figure 10- Relational schema
10

References
 Beeri, C., &Honeyman, P. (1981). Preserving functional dependencies. SIAM
Journal on Computing, 10(3), 647-656.
 Dongare, Y. V., Dhabe, P. S., & Deshmukh, S. V. (2011). RDBNorma:-A semi-
automated tool for relational database schema normalization up to third normal
form. arXiv preprint arXiv:1103.0633.
 Fuller, A. (2006). Normalization: How far is far enough?. TechRepublic. Retrieved
22 April 2018, from https://www.techrepublic.com/article/normalization-how-far-is-
far-enough/
11

12