Thus 2NF can be violated only when a key is a composite key or in other words

Thus 2nf can be violated only when a key is a

This preview shows page 5 - 8 out of 19 pages.

250 275 295
Image of page 5
Data Base Management systems 59 Notes Amity Directorate of Distance & Online Education 2 Multimedia 1 300 2 Data Structure 1 190 3 DBMS 1 295 3 Multimedia 2 300 3 Computer Networks 5 250 Fig. 7.5: Relation not in 2NF This 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_MASTER Order_No Title Qty 1 Computer Networks 1 1 Graphics 1 1 DBMS 2 2 Multimedia 1 2 Data Structure 1 3 DBMS 1 3 Multimedia 2 3 Computer Networks 5 BOOK_MASTER Title Unit_Price Computer Networks 250 Graphics 275 DBMS 295 Multimedia 300 Data Structure 190 Fig. 7.6: Relations in 2NF
Image of page 6
60 Data Base Management systems Notes Amity Directorate of Distance & Online Education All 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 Form A 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.
Image of page 7
Image of page 8

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture