Data Base Management systems59NotesAmity Directorate of Distance & Online Education2 Multimedia 1 3002 Data Structure 1 1903 DBMS 1 2953 Multimedia 2 3003 Computer Networks 5 250Fig. 7.5: Relation not in 2NFThis table lists the various orders of computers books, the quantity of each associated book and the unit price of each book. The combination of order_no and title is the primary key since both order_no and title can repeat in the table. This table is in first normal form as every attribute is atomic, but it is not in second normal form because unit_price is not functionally dependent on order_no part of the concatenated primary key, but only on the title component. Qty, on the other hand, is functionally dependent on concatenated primary key.It can be seen that the prices of some books are repeated because a title can be ordered in multiple orders. For example, title ‘DBMS’ exists in two orders, order_no1 and order_no3. Thus if the prices of this book is to be updated then we have to update it in all corresponding entries in the table. Moreover if we want to add a book to our library, then no details of this book can be inserted until an order is placed for this book. Further suppose order_no 2 is cancelled, so we have to delete this order from the database, which will loose all the information about the book including the price of the book.For this table, to be in second normal form, we have to decompose it in two tables as follows:ORDER_MASTER(order_no, title, qty) and BOOK_MASTER (title, unit_price)Now the tables will become as:ORDER_MASTEROrder_No Title Qty1 Computer Networks 11 Graphics 11 DBMS 22 Multimedia 12 Data Structure 13 DBMS 13 Multimedia 23 Computer Networks 5BOOK_MASTERTitle Unit_PriceComputer Networks 250Graphics 275DBMS 295Multimedia 300Data Structure 190Fig. 7.6: Relations in 2NF
60Data Base Management systemsNotesAmity Directorate of Distance & Online EducationAll the problems of deletion, insertion and updation, which were in the previous case are now eliminated. The unit_price of each book is stored only once and that is in a separate table, Book_master. A new book will make an entry just in book_master table. If price of any book is modified then it will be modified only in the book_master table. Moreover, cancellation of any order will not loose the price information of that book, because now prices of the books are stored in a separate table, book_master.Third Normal FormA relation schema R <S, F> is in 3NF, if for all nontrivial functional dependencies in F+ of the form X →A, either X contains a key (i.e., X is a superkey) or A is a prime attribute. A database schema is in 3NF if every relation schema included in the database schema is in 3 NF.A relation is in the third normal form, if it is in second normal form and no nonprime attribute is functionally dependent on other nonprime attributes.