Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    May 2010
    Posts
    217
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Design Questions

    Code:
    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?
    Last edited by Anishgiri; 09-30-2011 at 06:12 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    I would argue that you should remove both Book Genre and Author, as well as Author Nationality.

    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    May 2010
    Posts
    217
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I would argue that you should remove both Book Genre and Author, as well as Author Nationality.

    Code:
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •