ASSESSMENT 3: NORMALIZATION - 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