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

lets rename the attributes

a)

Let Bookid=A,subject-category-of-book=B,Name-of-author=C,Nationality-of-Author=D

A is the Pk

hence A⁺⁼{BCD}

F .D is A->BCD

Since there is  one attributed CK,there is no prime attribute hence,no partial dependency,so Relation is 2NF

No transitive dependency so 3NF

Non trivial dependency A->BCD

LHS is SK (CK is SK too)

So highest form is BCNF

b)Let Book-Title=E

Author-Address=F

CE->ABD ce is PK

There is a non trivial dependency and LHS of which is SK so BCNF.