edited by
19,950 views
53 votes
53 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?

edited by

5 Answers

Best answer
60 votes
60 votes

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
131 votes
131 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

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

NT -> BCYD

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)

Related questions

31 votes
31 votes
8 answers
1
Kathleen asked Sep 25, 2014
10,204 views
Which normal form is considered adequate for normal relational database design?$2NF$$5NF$$4NF$$3NF$
24 votes
24 votes
2 answers
3
Kathleen asked Sep 25, 2014
12,607 views
Which of the following statements is true?SLR parser is more powerful than LALRLALR parser is more powerful than Canonical LR parserCanonical LR parser is more powerful t...