Search code examples
databaserelational-databasedatabase-normalizationthird-normal-form

Is the example in wikipedia about 3NF conform it?

This is screenshot from the article about normal forms taken from Wikipedia. enter image description here

There is stated that in order to conform 3NF the Genre Name column must be put to it's own dictionary table.

My question is, is the Author Nationality breaking 3NF there also?

Solution

  • Yes, you are right, Author Nationality is also breaking 3NF.

    Explanation is as follows. Book can identify author, but not the other way round. Therefore author is functionally dependent on book. Same thing for author and author nationality. Author nationality is functionally dependent on author. There you have your transitive dependency: author nationality -> author -> book.

    Another thing that could be optimized in the book table would be the column thickness. It's functionally dependent on pages. Having that in an extra table would be overkill though, as this information can be easily derived from pages. I personally wouldn't store that information in the database. If you want to have this information in the database, you could create a view like

    CREATE VIEW v_book AS 
SELECT b.*, 
case when pages between 0 and 100 then 'slim' else 'thick' end as thickness 
FROM book b;

    The book table should look like

    book | author_id | pages | genre_id | publisher_id

    with another table author

    author_id | author_name | author_nationality