Does this design confrorm 2NF?

Question

Ok i have 2 tables. A book table with the the primary key book_id and another field called title. And an author table with the primary key author_id and fields first_name,last_name and a foreign key book_id.

Does my design conform to 2NF ?

Answer 1

Generally (and in layman's terms), normalisation at level one means:

• no possibility of duplicate rows.
• no ordering inherent in the rows or columns.
• each "cell" contains only one piece of information.

For 2NF, you have the extra constraint that every column is dependent on the entire key (directly or indirectly through another non-key column).

So I would say it conforms to 2NF in this case since it meets the criteria for 1NF and the book does depend on the author.

However, it's still not necessarily a good design, especially since you should always start at 3NF, as a general rule. A better solution would be the full blown:

books:
    book_id
    title
authors:
    author_id
    name
books_and_authors:
    book_id
    author_id

That way, you have a many-to-many (including zero) relationship between books and authors.