...

View Full Version : Table Design Questions



Anishgiri
09-30-2011, 06:03 AM
Book ID Book Title Book Genre Author Author Nationality
1 Title one Science Fiction Joey Cruz French
2 Title two Science Fiction Anne Santos French

In the above table, Book Title column and author nationality column in database normalization is called transitive dependency right? But does the table already satisfied the requirement on Norm 2 which is no partial dependencies (where an attribute is dependent on only a part of a primary key).? How can I address this transitive dependency issue here, what is the proper table structures? Should I remove the author nationality column here?

Old Pedant
09-30-2011, 08:35 AM
I would argue that you should remove both Book Genre and Author, as well as Author Nationality.



Table: Nationalities
natid int primary key
nation varchar

Table: Authors
authorid int primary key
natid int references Nationalities(natid)
author varchar

Table: Genres
genreid int primary key
gname varchar

Table: Books
bookid int primary key
booktitle varchar
genreid int references Genres(gnereid)
authorid int references Aurhors(authorid)

But even if you don't go that far, then clearly Author Nationality should be removed to a separate table.

Yes, your table is in Norm2 form. It should be at least in Norm3 form.

Anishgiri
09-30-2011, 09:25 AM
I would argue that you should remove both Book Genre and Author, as well as Author Nationality.



Table: Nationalities
natid int primary key
nation varchar

Table: Authors
authorid int primary key
natid int references Nationalities(natid)
author varchar

Table: Genres
genreid int primary key
gname varchar

Table: Books
bookid int primary key
booktitle varchar
genreid int references Genres(gnereid)
authorid int references Aurhors(authorid)

But even if you don't go that far, then clearly Author Nationality should be removed to a separate table.

Yes, your table is in Norm2 form. It should be at least in Norm3 form.

Thanks for the response. I can remove Book Genre and Author so as to save space and avoid update anomaly like when the author change his name right? Did I still miss something on the benefits of removing both Book Genre and Author? Just to make clarification, if I only remove the Author Nationality column, it will already satisfy the requirements to in norm 3 form?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum