ITC556 CSU: Normalizing Database Tables for a Home Library Relation

This assignment solution demonstrates the normalization of a database table representing a home library, adhering to the guidelines provided in the ITC556 course at CSU. The solution includes drawing a dependency diagram to illustrate functional dependencies within the initial table, decomposing the table into a set of 3NF (Third Normal Form) relations to eliminate redundancy and improve data integrity, and constructing a relational schema for each 3NF relation, highlighting referential integrity constraints. The process involves transforming the initial table through First Normal Form (1NF) and Second Normal Form (2NF) before achieving the final 3NF structure. References to established database systems literature are included to support the methodology. Desklib offers this document as a resource, among many others, for students seeking assistance with their studies.

1
Normalization of Database Tables
Name
Institution
Normalization of Database Tables

2
Normalization of Database Tables
1. Dependency diagram
ISB
N
Titl
e
Author_LastNam
e
Author_FirstNam
e
Publishe
r
Dat
e
Editio
n
Medi
a
The title, date and edition are fully dependant on the ISBN of the book,
The media through which the is transitively dependant on the publisher.
2. Decompose the home library into a set of 3NF relations
1st Normal Form – rules
ISBN Title Publisher Date Edition Media
369852 Cosmos Random
House
1980 1 Book
741258 No Secrets Elektra 1972 1 CD
654789 Symphony
No 3 Dur
Eroica Op 55
1805 1 CD
789654 On the Decay
of the art of
Lying
Project
Gutenberg
1880 1 eBook
258963 The
Adventures
of Sherlock
Holmes
Project
Gutenberg
1 eBook
125896 The Devine
Comedy
Project
Gutenberg
1 eBook
357951 The
Hitchhickers
Guide to the
Galaxy
Pan books 1979 1 Book
852369 The Return of
the king,
soundtrack
Reprise 2003 1 CD
831975 Unseen Doubleday 2009 1 Book

3
Normalization of Database Tables
Academicals
AuthorID ISBN Author _LastName Author_FirstName
A1 369852 Sagan Carl
A2 741258 Simon Carly
A3 654789 Beethoven Ludwig
A4 789654 Twain Mark
A5 258963 Conan Doyle Arthur
A6 125896 Alighieri Dante
A7 357951 Adams Douglas
A8 852369 Shore Howard
A9 831975 Pratchett Terry
2nd NormallForm – it must abide by the rules of 1st NormalForm + non-key columns
should be dependent on the primary key Coronel & Morris (2016.
ISBN Title Date Edition PublisherID
369852 Cosmos 1980 1 P1
741258 No Secrets 1972 1 P2
654789 Symphony No 3
Dur Eroica Op
55
1805 1 P3
789654 On the Decay of
the Art of Lying
1880 1 P4
258963 The Adventures
of Sherlock
Holmes
1 P5
125896 The Divine
Comedy
1 P6
357951 The
Hitchhickers
Guide to the
Galaxy
1979 1 P7
852369 The Return of
the king,
soundtrack
2003 1 P8
831975 Unseen
Academicals
20009 1 P9
PublisherID Publisher Media
P1 Random House Book

4
Normalization of Database Tables
P2 Elektra CD
P3 CD
P4 Project Gutenberg eBook
P5 Project Gutenberg eBook
P6 Project Gutenberg eBook
P7 Pan Books Book
P8 Reprise CD
P9 Doubleday Book
AuthorID Author _LastName Author_FirstName
A1 Sagan Carl
A2 Simon Carly
A3 Beethoven Ludwig
A4 Twain Mark
A5 Conan Doyle Arthur
A6 Alighieri Dante
A7 Adams Douglas
A8 Shore Howard
A9 Pratchett Terry
AuthorID ISBN
A1 369852
A2 741258
A3 654789
A4 789654
A5 258963
A6 125896
A7 357951
A8 852369
A9 831975
3rd NormalForm should follow 2NF rules + non-key columns should be non-transitively
dependant on primary key
PublisherID Publisher
P1 Random House
P2 Elektra
P3
P4 Project Gutenberg
P5 Project Gutenberg
P6 Project Gutenberg

5
Normalization of Database Tables
P7 Pan Books
P8 Reprise
P9 Doubleday
Publisher Media
Random House Book
Elektra CD
CD
Project Gutenberg eBook
Project Gutenberg eBook
Project Gutenberg eBook
Pan Books Book
Reprise CD
Doubleday Book
ISBN Title Date Edition PublisherID
369852 Cosmos 1980 1 P1
741258 No Secrets 1972 1 P2
654789 Symphony No 3
Dur Eroica Op
55
1805 1 P3
789654 On the Decay of
the Art of Lying
1880 1 P4
258963 The Adventures
of Sherlock
Holmes
1 P5
125896 The Divine
Comedy
1 P6
357951 The
Hitchhickers
Guide to the
Galaxy
1979 1 P7
852369 The Return of
the king,
soundtrack
2003 1 P8
831975 Unseen
Academicals
20009 1 P9

6
Normalization of Database Tables
AuthorID Author _LastName Author_FirstName
A1 Sagan Carl
A2 Simon Carly
A3 Beethoven Ludwig
A4 Twain Mark
A5 Conan Doyle Arthur
A6 Alighieri Dante
A7 Adams Douglas
A8 Shore Howard
A9 Pratchett Terry
AuthorID ISBN
A1 369852
A2 741258
A3 654789
A4 789654
A5 258963
A6 125896
A7 357951
A8 852369
A9 831975
3. Draw a relational schema for each of the 3NF relations. This is according to Dongare et.al
(2011)

7
Normalization of Database Tables
Author Details
In the bookdetails table ISBN is the primary key whereas publisherID is the foreign key.
In the bookauthor relation ISBN is the primary key whereas AuthorID is the foreign key.

8
Normalization of Database Tables
References
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
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.
Website: https://www.youtube.com/watch?v=vkUyuLLgmwQ.