CODEWITHSUNDEEP
databaseoracle-databasedatabase-designnormalizationdatabase-normalization
Some Body
Some Body

Reputation: 2031

Does this design confrorm 2NF?

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 ?

Upvotes: 1

Views: 146

Answers (2)

keyser
keyser

Reputation: 19189

A table is in 2NF if and only if, it is in 1NF and no non prime attribute is dependent on any proper subset of any candidate key of the table (from wikipedia).

I have to assume that your table is in 1NF.
Your candidate (and primary) keys are book_id and author_id.

Your non-prime attributes are title, first_name and last_name. None of these attributes depend on any proper subset of the candidate keys (which is easily realized since they don't have any subsets). The only problem with this design is that the foreign key book_id makes book-author a 1:1 relationship (i.e. One book -> one author. One author -> one book). This could easily be fixed with a new table holding book_id and author_id

1NF (also wikipedia):

  • There's no top-to-bottom ordering to the rows.
  • There's no left-to-right ordering to the columns.
  • There are no duplicate rows
  • Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
  • All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

Which is how we're used to construct relational databases.

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881363

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.

Upvotes: 3

Related Questions