The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+27 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?

asked in Databases by Veteran (59.5k points) | 3.1k views
Please explain..?

4 Answers

+28 votes
Best answer

No FDs are given. So, assuming them from the attributes. 

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

We can also have the following FDs

  • Name_of_Author $\to$ Nationality_of_Author

This FD won't be there if two authors exist with the same name. But Author_id being not there and {Name_of_Author, Book_title} in (b) part, shows that Name_of_Author is indeed unique. 

Now, Name_of_Author $\to$ Nationality_of_Author is a transitive functional dependency as both side of the FD are non-key attributes and the FD is non-trivial. So, the relation is not in 3NF. Since there is only one key (since no other attribute determine Book_id and Book_id is a key), it is in 2NF. 

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 $\to$ Nationality_of_Author
  • Name_of_Author $\to$ Author_address
  • {Book_title, Name_of_Author} $\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. 

So, now Name_of_Author $\to$ Author_address becomes a partial FD as Name_of_Author is a part of a key and Author_address is not a key attribute. So, relation is now just in 1NF. 

answered by Veteran (355k points)
selected 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!
+38 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

answered by Boss (30.7k points)

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.

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).
answered by Active (2.1k points)
edited by
–3 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.

answered by Active (3.5k points)

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

38,053 questions
45,543 answers
48,879 users