16. Consider the following relation for published books
BOOK (Book title, Author name Book Type List price, Author affil, Publisher)
Author_affil refers to the affiliation of author Suppose the following dependencies exist
Book_title Publisher
Book_type Book type List pro
Author_name Author altil
a What normal form is the relation in? Explain your answer
b. Apply normalization until you cannot decompose the relations further State the reasons
behind each decomposition
Answers
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.