4

If someone could guide me on helping me understand if my results are correct I would appreciate that. Let's say I have the following table:

BOOK (BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher)

AuthorAffil refers to the affiliation of author. Suppose the following functional dependencies exist:

{BookTitle} -> {Publisher, BookType} 
{BookType} -> {ListPrice} 
{AuthorName} -> {AuthorAffil} 

I am asked if this table is in 1NF, 2NF, or 3NF.

I believe this table is in 1NF because no two rows of data contain repeating information.

If this was to be a 2NF, it would have to look like:

R1 = {BookTitle} -> {Publisher, BookType, ListPrice} 
R2 = {AuthorName} -> {AuthorAffil} 
R3 = {AuthorName, BookTitle} -> {extras} 

I would like some help to know if I am understanding this concept or if there are any mistakes. Any type of help is welcomed.

  • Hi @user1404664 if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – EagleOne May 1 '15 at 8:53
2

Let us address first normal form and second normal form distinctly.

First Normal Form (1NF)

You cannot say a table is or is not in 1NF because no two rows contain repeating information. By repeating information you may be thinking that BOOK doesn't look like this:

BOOK { BookTitle, AuthorName1, AuthorName2, AuthorName3, BookType, ListPrice, AuthorAffil, BookPublisher  }

This really isn't the definition of 1NF. Instead, BOOK is in 1NF only if each column in every row will have a single value of whatever type, no matter how arbitrarily complex, that is defined for that column. While adding multiple AuthorNames columns may be bad design, it does not violate 1NF as each contains the name of a single author. Now what would violate 1NF is if we had an AuthorNames column and defined it to contain a comma separated list of author names. In that case we know absolutely BOOK is not in 1NF.

Its also possible we have designed the table variable to be in 1NF, assuming only a single author name would ever be placed into the AuthorName column, but our users start putting in comma separated lists of authors. Now the resulting table is not in 1NF even though we meant it to be. In both these cases the table isn't even a relational table anymore because it no longer follows the discipline necessary to gain the properties of a relation.

Secondly, you cannot say BOOK is or is not in 1NF without the presence of at least one candidate key! As stated right now, BOOK has no candidate keys and thus the same value is allowed to be entered for every column in two or more rows. This would result in duplicate rows and a table with duplicate rows is not a relational table!

Second Normal Form (2NF)

It appears from looking at the attempt to decompose BOOK a candidate key of BookTitle,AuthorName was assumed. Even if this assumption were correct, it is not correct to say the original table is now in 2NF. Instead, of the 3 tables resulting from the decomposition, each must now be evaluated with respect to the functional dependencies. R2 is in 5NF as its single non key column is fully functional dependent on the key and that key is the only candidate key and it is a single column. R3 is in 5NF as its only non key column (the assumed "extras") are also fully functionally dependent on the single candidate key and there are no additional candidate keys. It is only R1 that is now in 2NF but not 3NF as it has a non key column, ListPrice, that is functionally dependent on another non key column, BookType, and thus forms a transitive dependency.

The assumption of a single candidate key of BookTitle,AuthorName may also not be correct! Perhaps in this particular book world every book of interest has only one author. If that were the case then the creation of R3 is incorrect with respect to the actual functional dependencies. Thus it is vital to clarify all the candidate keys and all the functional dependencies, join dependencies, and multi-value dependencies, with the subject matter experts and to never assume them. Otherwise the wrong design results. Assuming many authors per book when really there can be only one per book results in an R3 that allows the FD of BookTitle --> AuthorName to be violated. Assumming one author per book when really there can be many per book results in a table similar to the original BOOK where users find they only have room for one author name, not the many they need, and they find their only choice is to enter sets of author names, delimited by something like commas or pipes.

References

Fabian Pascal's Practical Database Foundation Series and CJ Date's Relational Theory for Computer Professionals are excellent references for understanding what makes a table a relational table and the fundamentals of normalization. It is from these sources that all the information in this answer was derived.

1

Define the key

The first step is defining the key. Supposing that can't exists two books with the same title, the key is BookTitle.

Normalization

1NF We can think that the Relation is in 1NF if we suppose that a book has one author. We will not have, for example

           BookTitle                               AuthorName
Data Structures & Other Objects Using C++     Michael Main, Walter J. Savitch

but we will have

     BookTitle                                    AuthorName
Java: A Beginner's Guide                           Herbert Schildt

We can think it because we have "AuthorName" and not "Authorsnames" or other.

2NF If {BookTitle} is the key, the relation is surely in 2NF as every relation with a key composed by only one attribute

3NF We have to consider every transitive functional dependency.

They are:

{BookType} --> {ListPrice} 
{AuthorName} --> {AuthorAffil} 

So the 3NF normalization is:

 Books {BookTitle (key), AuthorName (fk: Authors), Booktype (fk:BookTypes),Publisher)
 Authors {AuthorName (key), AuthorAffil}
 BookTypes {BookType (key), ListPrice}

Where fk means Foreign key to another table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.