16,877 views

Consider the following database relations containing the attributes

• Book_id
• Subject_Category_of_book
• Name_of_Author
• Nationality_of_Author

With Book_id as the primary key.

1. What is the highest normal form satisfied by this relation?

2. Suppose the attributes Book_title and Author_address are added to the relation, and the primary key is changed to {Name_of_Author, Book_title}, what will be the highest normal form satisfied by the relation?

If bid is primary key then we can have -->

bid --> subject Category of book, author name, author nationality

As per discussions mentioned here, it should be in 2NF, but why not 3NF? bid can act as a super key since it is candidate key which suggests this can be in 3NF.

As Arjun Sir told, Many things are not specified in the question. Hence, you have to take all the possibilities. Now, one possibility might be that name_of_author --> Nationality_of_Author or something like that. So, this will cause a transitive dependency Hence not in 3nf. Also, we can surely say that it is in 2nf because it is clearly given that Book_id is the primary key.

And one more thing, if you have any doubt relating questions then only put your question here. Otherwise, put it in the answer section.

Since Book_id is the key we have,

• Book_id $\to$ Subject_Category_of_book
• Book_id $\to$ Name_of_Author
• Book_id $\to$ Nationality_of_Author

If we assume no other FD is there (this is not specified in the question), the relation is in BCNF as the LHS of every FD is primary key which is also a super key.

a. 2NF

b. New set of FDs are

• Book_id $\to$ Subject_Category_of_book
• Book_id $\to$ Name_of_Author
• Book_id $\to$ Nationality_of_Author
• Book_id $\to$ Book_title
• {Name_of_Author, Book_title} $\to$ Nationality_of_Author
• {Name_of_Author, Book_title} $\to$ Author_address
• {Name_of_Author, Book_title} $\to$ Book_id

One thing to notice here is only the primary key is being changed from Book_id to {Book_title, Name_of_Author}, but Book_id is still a key as based on convention Book_id always determines Book_title. Again if we assume no other FD, the relation is in BCNF as LHS of every FD is a super key. But it is logical to assume the FD

Name_of_Author $\to$ Author_address

(won't be valid if two authors have same address and should have been explicit in the question) and this FD is a partial FD on the candidate key  {Name_of_Author, Book_title} as Name_of_Author is a part of the key and Author_address is not a key attribute. So, this violates 2NF and relation is now just in 1NF. (Debatable if we can assume FDs)

by

we might have 2 same author name for different authors so author name->a

ut.hor address is not logical to think
Nice explanation 👍

@AkshayDixit

yes i think this is exactly what the question is asking for !

• Book_id  ――――――――――――――― i
• Subject_Category_of_book  ――――――― c
• Name_of_Author  ――――――――――― a
• Nationality_of_Author  ―――――――――  n
• Book_title     ―――――――――――――  t

first part) i --> c a n
which satisfies BCNF

second part)
i --> c a n
a t --> i c n d
CKs = { {a t} , {i t} }
first FD satisfies BCNF
second FD violates 2NF
Hence, in 1NF

a t --> i c n d

Here 'at' is not proper subset of any candidate key. For partial dependency 'at' should be proper subset of candidate key ,not equal to candidate key. So it is not partial dependency and thus it should be in 2NF. First FD is already in 2NF (since BCNF). Please clear my confusion. How second FD violating 2NF condition ?

This is the clear-cut approach
It’s so clearly explained. I am wondering why this is not the best answer? Answer marked best is ambiguous.
as there is only one relation that has a primary key with only one attribute, so we can derive only one functional dependency that is :

Book_id->Book_id, Subject_Category_of_book, Name_of_Author, Nationality_of_Author.

and here the LHS is a superkey so, it is in BCNF.

But when the attributes Book_title and Author_address are added to the relation, and the primary key is changed to {Name_of_Author,Book_title}.

We get an additional functional dependency which is:

Name_of_Author,Book_title-> Book_id, Subject_Category_of_book, Name_of_Author, Nationality_of_Author,Name_of_Author,Book_title

Book_id->Book_id, Subject_Category_of_book, Name_of_Author, Nationality_of_Author.

that is a non prime attribute is determining some non prime atrributes.

So, it is in 1NF(Because there is  partial dependency in 1st fd).
by
I don't think there should be confusion.

Bookid- B

Category - C

Name - N

Nationality - Y

1st Part:

B is the key.

So B --> CNY

Clearly BCNF since LHS is superkey. (All candidate keys are superkeys)

2nd Part:

Title - T

So the new key that includes B should also contain these two, since none derives them. So BTD is our new C. K.

Given non trivial FDs now:

B -> CNY // not 3NF

NT -> BCYD

Also B is a part of C. K.  that is deriving non key attributes. So neither 2 NF.

Thus,  1NF

(All I did was apply the definitions literally)
by