12.1 Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if it is:

		In first normal form and
		Every non-key attribute in the table is fully functionally dependent on the primary key.

Consider the Member_Department table shown in Figure 12.1.

		It has attributes Member_Id, Dept_No, Position and Dept_location.
		The table’s primary key is (Member_Id, Dept_No).
		All attributes in the table are atomic and thus the table is in 1NF meeting the first condition of being in second normal form.
		The functional dependencies of attributes in the table are as follows:

			Attribute Dept_location is functionally dependent on Attribute Dept_No (Dept_No → Dept_location) but not fully functionally dependent on (Member_Id, Dept_No).
			Attribute Position is fully functionally dependent on Member_Id and Dept_No (Member_Id, Dept_No → Position).

12.1 Second Normal Form (2NF)

		From the functional dependencies listed above, it can be seen that the non-key attribute Dept_location is not fully functionally dependent on the primary key (Member_Id, Dept_No). This violates the 2NF’s second condition.
		The problems with the Member_Dept table include the following:

			The department location is repeated in every row that includes the department.
			If the location of the department changes, every row that includes the department must be updated. Data inconsistency might occur if not all rows are updated.

12.1 Second Normal Form (2NF)

		To normalize the Member_Department table into a 2NF, it has to be decomposed into two tables as shown in Figure 12.2.
		The primary key of the first table is (Member_Id, Dept_No) and for the second table is Dept_No. The two tables meet the full functional dependency rule of the 2NF.
		Note how the Dept_location attribute that violates the 2NF in the Member_Dept table has been moved to a new table.

12.1 Second Normal Form (2NF)

Example

Consider the Orders table shown in Figure 12.3. The primary key for the table is (Order_Id, Item_Id). The table is not in 2NF. Explain why and normalize the table into 2NF.

Solution:

		The table is in 1NF as it does not include any multi-valued attributes.
		To check whether the table is in 2NF, non-key attributes’ functional dependency on the primary key need to be examined:

12.1 Second Normal Form (2NF)

			Quantity is fully functionally dependent on (Order_Id, Item_Id).
			Price is fully functionally dependent on (Order_Id, Item_Id).
			Item_Description is partially functionally dependent on (Order_Id, Item_Id) as it is only functionally dependent on Item_Id.

		As not all non-key attributes are fully functionally dependent on the primary key, the table is violating 2NF.
		To normalize the Orders table into 2NF, the offending attribute which is Item_Description in this case needs to be separated into a new table.
		Thus, the Orders table is split into two tables as shown in Figure 12.4.

12.1 Second Normal Form (2NF)