250
275
295
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
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.
