Normalization of Home Library Database Relation to 3NF

This report presents a step-by-step normalization process applied to a Home Library relation. It begins by explaining the concept of database normalization, which restructures a database to reduce CUD (create, update, delete) anomalies, optimize physical design, reduce redundancy, and improve data integrity. The normalization process focuses on achieving the third normal form (3NF). The report includes a functional dependency diagram (FDD) to illustrate the dependencies within the Home Library relation. It then decomposes the initial relation into 1NF, 2NF, and finally 3NF relations, creating new tables such as HOME_LIBRARY, AUTHOR, PUBLISHER, and BOOK. The process involves identifying primary keys, addressing partial dependencies, and eliminating transitive dependencies. The report concludes with a relational schema diagram, demonstrating the referential integrity among the decomposed relations, ensuring a well-structured and efficient database design. Desklib provides access to a wide range of solved assignments and study tools to aid students in understanding complex topics.

Title Page
1

Table of Contents
Background......................................................................................................................................3
Database Normalization...................................................................................................................3
Task 1...........................................................................................................................................3
Task 2...........................................................................................................................................4
Task 3...........................................................................................................................................4
References........................................................................................................................................6
2

Background
The aim of this paper is to present the step by step normalization process applied on the given
Home Library table. Normalization in the context of databases, is a process that restructures a
database to reduce create, update, and delete or CUD anomalies from the database (Fong, 2015),
(Christopher Allen, 2013). It also helps in optimizing the physical database design by reducing
redundancy and by improving the data integrity. Data normalization process has some normal
forms and each normal form has certain rules. When a database is restructured and fulfills the
rules of a normal form, then the database is said to be in that normal form (Harrington, 2016). In
this assignment, normalization up to 3NF or third normal form has been considered.
The given Home Relation table is,
ISBN Title Author_LastName Author_FirstName Publisher Date Edition Media
369852 Cosmos Sagan Carl Random House 1980 1 Book
741258 No Secrets Simon Carly Elektra 1972 1 CD
654789 Symphony No 3 Dur Eroica Op
55 Beethoven Ludwig 1805 1 CD
789654 On the Decay of the Art of Lying Twain Mark Project
Gutenberg 1880 1 eBook
258963 The Adventures of Sherlock
Holmes Conan Doyle Arthur Project
Gutenberg 1 eBook
125896 The Divine Comedy Alighieri Dante Project
Gutenberg 1 eBook
357951 The Hitchhikers Guide to the
Galaxy Adams Douglas Pan books 1979 1 Book
852369 The Return of the King,
Soundtrack Shore Howard Reprise 2003 1 CD
831975 Unseen Academicals Pratchett Terry Doubleday 2009 1 Book
Currently, the relation Home Library has not fulfilled the condition of any normal form.
Database Normalization
Task 1
An FDD or Functional Dependency Diagram shows the functional dependencies of a relation in
a graphical format. A functional dependency constraint exists between two sets of attributes of a
relation. One set must functionally determine the other set (Silberschatz, Korth, & Sudarshan,
2014).
3

The functional dependency diagram for the Home Library is,
ISBN Title Author_Last
Name
Author_First
Name Publisher Date Edition Media
Task 2
1NF
The candidate keys of the Home Library relation can be the ISBN and Title. The ISBN has been
selected as the primary key. So, the 1NF relation has the following relational structure.
HOME_LIBRARY (ISBN, Title, Author_LastName, Author_FirstName, Publisher, Date,
Edition, Media)
2NF
Now, the primary key has a single attribute and no other attribute is partially dependent on it
(Christopher Allen, 2013). However, from the given Home Relation table, it is clear that there
are redundancies and anomalies in the data. The table summarizes information about books,
authors and publishers. To reduce the anomalies, the 2NF decomposition has been done. The
new relations are given below.
 HOME_LIBRARY (ISBN, Author_ID, Publisher)
 AUTHOR (Author_ID, Author_LastName, Author_FirstName)
 PUBLISHER (Publisher)
 BOOK (ISBN, Title, Date, Edition, Media)
3NF
Now, all four relations are in 2NF. The new Author_ID attribute has been added to enforce
referencial integrity. It is the primary key of the AUTHOR relation. Now, there is no transitive
dependency in any relation. So, the new relations in 3NF are,
 HOME_LIBRARY (ISBN, Author_ID, Publisher)
4

 AUTHOR (Author_ID, Author_LastName, Author_FirstName)
 PUBLISHER (Publisher)
 BOOK (ISBN, Title, Date, Edition, Media)
Task 3
The relational schema diagram for the Home Library database shows the referential integrity
among the decomposed relations.
ISBN Title Date Edition Media
Author_ID Author_Last
Name
Author_First
Name
Publisher
ISBN Author_ID Publisher
5

References
Christopher Allen, S. C. (2013). Introduction to Relational Databases and SQL Programming.
McGraw Hill Professional.
Date, C. (2012). Database Design and Relational Theory. O'Reilly Media, Inc.
Fong, J. S. (2015). Information Systems Reengineering, Integration and Normalization. Springer.
Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
Silberschatz, A., Korth, H. F., & Sudarshan, S. (2014). Database System Concepts. McGraw-
Hill.
6