Aptitude | GATE CS 1998 | Question 84

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.

• (a). What is the highest normal form satisfied by this relation ?
• (b). 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?

Answer:
Explanation:

• (a). R(Book_id, Subject_Category_of_book, Name_of_Author , Nationality_of_Author)
  Given that Book_id as the Primary Key.

  Therefore { Book_id → catalog Name Author , Nationality Author}

  
  
  

  Hence Given relation is in BCNF.

• (b). Two attributes Book_title and Author_address are added to the relation

  Then , R(bid , catalog , Name Author , Nationality Author , Book Title , Author Address )

  FD are :-

  { bid →  Catalog Name Author ,   Nationality Author , 
  Name Author Book Title → bid catalog Nationality Author Author Address } 
  

  Here first FD because of previous relation is second FD because of new primary key .

  Candidate keys of this relation is :

  [Name Author , Book Title , bid , book title  ]   

  Here partial dependency exist ( i.e. relation is not in 2NF)

  Hence relation is in 1NF.

Quiz of this Question