3.6k 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?

edited ago | 3.6k views
0

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.

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

+6
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
• Book_id  ――――――――――――――― i
• Subject_Category_of_book  ――――――― c
• Name_of_Author  ――――――――――― a
• Nationality_of_Author  ―――――――――  n
• Book_title     ―――――――――――――  t

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

0

this is appropriate description

+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

?

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

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 ?

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

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

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

CE->ABD ce is PK

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