GATE CSE 1998 | Question: 26

Question

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?

Comments

Please explain..?

---

Please someone provide Unique answer to this problem.

 

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.

---

@Pankajpat2014 

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.

Answer 1

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)

Comments

Nice explanation 👍

---

@AkshayDixit

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

---

@Arjun Sir , Why answer to part a) 2NF ? they have asked for highest normal form satisfied which can be BCNF also.

and how we can say b) 1NF ? I mean if we not take book_id → book_title(they just said that book_title and author_address are added ) then this relation can be in 2NF also which is also possible highest normal form.

Answer 2

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

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

Comments

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.

---

best answer acc to me.
Just a correction, first FD violates 2NF and second FD satisfies BCNF.
So, 1NF.

Answer 3

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

and we already have :

 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).

Answer 4

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:

Two new attributes got added,  

Address - D

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)

Comments

@Kedar S where it is written that Name_of_aurhor can't determine Nationality of author . If question didn't mention anything explicitly then we have to consider all possibilities .