ER to Relational Mapping Second Normal Form 2

ER to Relational Mapping

Second Normal Form (2 NF) A relation (table) is said to be in 2 nd normal form � If it is in normal form (INF) and � If it contains a multi-attribute key(composite key) then all the non key attributes must be dependent on the complete set of the key attribute (composite key) and not any part of it. Consider the following table: Transaction Composite Key Sale Purchase Transaction_Id Product_Id Transaction_Date Transaction_Type Quantity Product_Descriptive Product_Price

In the previous table, the attributes Product_Description and the Product_Price are dependent only on Product_Id and the attribute Transaction_Type depends only on Transaction_Id , but these attributes do not depend upon the complete set of key attributes(composite key). Therefore, the table is not in the Second Normal Form can be achieved by reproducing the table as follows: Transaction_Type Transaction_Id 1 N Transaction_Type F/K Transaction_Id Product_Id Transaction_Date Quantity Product_Detail N Composite key F/K 1 Product_Id Product_Developmen t Product_Price F/K-Foreign Key P/K The decomposition of ‘Transaction’ table into ‘Transaction_Type’, ‘Transaction’ and ‘Product_Detail’ tables, normalizes the database as in the ‘Transaction’ table now all the nonkey attributes are dependent on complete set of multi-attribute key (composite key).

Third Normal Key (3 NF) A table is said to be in Third Normal Form (3 NF) if � It is in first and second normal form, and � Every non-key attribute is directly dependent on the key attribute. In a table which is in third normal form, all the non-key attributes are independent of each other and directly depend upon the key attribute. Order_Id Customer_Address Order_Date Quantity Price Amount Derived attribute Amount = Price x Quantity The table ‘Order’ satisfies the conditions required for first normal form, i. e. , key attribute is defined, the attributes are atomic and there are no repetitive groups. Therefore, the table is in the first normal form and since it doesn’t contains any composite key, it is automatically in the second normal form. But in the table Customer_Address, a non-key attribute, is dependent on a non-key attribute Customer_Id and not on Order_Id, the key attribute and therefore, the table ‘Order’ is not in the Third Normal Form. The Third Normal Form can be achieved by removing Customer_Address from the table as shown in next slide: -

Order_Id Customer_Address Order_Date Quantity Price Amount Customer P/K N F/K 1 Customer_Id Customer_Address From the ‘Order’ table attribute ‘Amount’ is removed as it is derived from other attributes ‘Quantity’ and ‘Price’. This can be derived at any time and therefore, it is not advisable to keep it in the database.