The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+28 votes
4.1k 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?

asked in Databases by Veteran (59.7k points)
edited by | 4.1k views
0
Please explain..?
0
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.

4 Answers

+35 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)

answered by Veteran (367k points)
edited by
+2

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 .

+8
The FDs I have taken as assumed. I suppose you can just leave this question as without exact FD we cannot say anything.
0
ok sir
0
@Arjun sir, please change the answer . It's creating confusion!
0
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?
0
ok, so can we leave this...
+58 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.9k points)
0

this is appropriate description yes

+14
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
+1
In 2nd part ,

why included

i->c a n

?

please explain
0
Part 2 is extension of Part 1
+3
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.
0

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} ???

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

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

+1

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

+1

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.

0

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 ?

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 (3.5k points)
edited by
–3 votes

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.

answered by Active (3.6k points)

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

44,147 questions
49,639 answers
163,293 comments
65,807 users