The Gateway to Computer Science Excellence
+36 votes

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?

in Databases by
edited by | 7.2k views
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.


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.

5 Answers

+45 votes
Best answer

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)

edited by

a. Book_id is key => key is single attribute . therefore no partial dependency = 2NF

b.(Name_of_author,Book_title) = 2 attribute key . may have partial dependency .therefore not in 2NF

Book_title may derive subject_category_of_book.

as explained in above comment name_of_author derive nationality. but how its possible.

plz explain it .

The FDs I have taken as assumed. I suppose you can just leave this question as without exact FD we cannot say anything.
ok sir
@Arjun sir, please change the answer . It's creating confusion!
a. it should be in BCNF, as the relation

R={Book_id, Subject_Category_of_book, Name_of_Author, Nationality_of_Author}

Book_id is the only key and no other FD is possible, how is it in 2NF?
ok, so can we leave this...

 Sir why answer to a) part is 2NF. It is asking for Highest Normal Form. Will it not be BCNF

the first relation will be BCNF, as Book_id can determine the rest 3 attributes, the LHS of the Functional dependency is Super Key, which makes it BCNF

moreover in the second part of the question you gave this dependency 

  • Book_id → Book_title

which i believe is wrong, as it is not according to the question


Name_of_Author →→ Author_address

(won't be valid if two authors have same address and should have been explicit in the question)

it should not be valid for same author names rather same address...2 authors may have same address what is the problem in it @Arjun sir? but if 2 authors have same names then author name wont be a key any more


We can't presume anything  in Gate even if it  seems to be logical.  @Arjun 

You have added few FDs that are not mentioned in the Question. Most of the time Questions are from General Scenarios and although we may think to give a logical sense to the Question but its not needed or we can say not demanded by the Questionnaire

So if I go only with what I understand from the Question my answer would be :


  • Book_id  ――――――――――――――― B
  • Subject_Category_of_book  ――――――― C
  • Name_of_Author  ――――――――――― N
  • Nationality_of_Author  ―――――――――  A
  • Book_title     ―――――――――――――  T
  • Author_address   ―――――――――――  Z

A part :

Since Book_id is specifically mentioned as Primary Key :

B --> CNA

Now for Relation  BCNA, its the only FD and LHS is the Key therefore its in BCNF.

B part  :

Now we have added two more attributes in Relation BCNA they are Book Title ( T) and Author_address (Z) 

New Relation becomes BCNATZ

But earlier dependency still holds : B --> CAN ( from part A)

and new dependency as per question is : TN --> BCAZ as TN is the primary key 

but if you will try to find the other Candidates key you will get BT 

So BT and TN are the CKs , so prime attributes are BTN

And B--> CAN or B--> CA ( decomposition ) is partial dependency 

Therefore this relation is in 1NF only.





+92 votes
  • 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


this is appropriate description yes

second part)  
i --> c a n  
a t --> i c n d
CKs = { {a t} , {i t} }

 first FD violates 2NF
because i is  proper subset of candidate key {i t} and
RHS is non-prime attribute

second FD satisfies BCNF
because LHS is superkey

Hence, in 1NF
In 2nd part ,

why included

i->c a n


please explain
Part 2 is extension of Part 1
in second part, primary key is changed, but "i" is still there some another candidate key on schema, that is why i->can is included in part 2.

in part (b):

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

i -> c,a,n

{i,t} is a CK

so, {i,t} -> i,t,c,a,n,d

how d (address of the author) derived from {i,t} ???

as nothing is mentioned about FD's then why did u assume only this FD .other FD may be present then highest normal form become 2NF.

Why is {i, t} a CK? Isn't t redundant? CK should be minimal.


how are you determining fds here .Is there any procedure for that.


It is already given in part a that "i" is the primary key. So we can say that i->can.

Also in the 2nd part 2 more attributes are added and since PK is changed to "at" so we know that "at" derives all the attributes of the modified table i.e. at->icnd.

Now FDs are i->can and at->icnd.  Break the FD's using decomposition rule

i->c;  i->a;   i->n;   at->i;   at->c;   at->n;  at->d

(i)+={ican},  (it)+={itcand}.  "it" is also a candidate key.

Hence the prime attributes are { i,t,a}.

Now we check FD  i->n  : 'i' is a proper subset of candidate key and 'n' is a non prime attribute for which it becomes a Partial FD. Hence 2nf is violated.


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
0 votes
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).
edited by
0 votes
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


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)
–2 votes

lets rename the attributes


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


CE->ABD ce is PK

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


Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
52,315 questions
60,432 answers
95,257 users