Solution to Exercise 1

In this lesson we will discuss the solution to exercise 1.

We'll cover the following


The second normal form states that it should meet all the rules for 1NF and there must be no partial dependencies between any of the columns with the primary key.

Customer-order table

Cust_Id Cust_Name Order_Id Order_Detail Order_Category
1 Jack 34 Shampoo Hygiene
2 Bruce 22 TV Electronics
3 Amanda 84 Shirts Clothing
4 James 12 Shoes Clothing
2 Bruce 62 Glasses Clothing
5 Veronica 84 Shirts Clothing

First, we can see that the table above is in the first normal form; it obeys all the rules of the first normal form.

Secondly, the primary key consists of the Cust_Id and the Order_Id. Combined, they are unique assuming the same customer would not order the same thing.

However, the table is not in the second normal form because there are partial dependencies of primary keys and columns. Cust_Name is dependent on Cust_Id and there’s no real link between a customer’s name and what he/she purchased. The Order_Detail and Order_Category are also dependent on the Order_Id, but they are not dependent on the Cust_Id, because there is no link between a Cust_Id and an Order_Detail or their Order_Category.

To make this table comply with the second normal form, you need to separate the columns into three tables.

First, create a table to store the customer details as shown below:

Customer table

Cust_Id Cust_Name
1 Jack
2 Bruce
3 Amanda
4 James
5 Veronica

The next step is to create a table to store the details of each order:

Orders table

Order_Id Order_Detail Order_Category
34 Shampoo Hygiene
22 TV Electronics
84 Shirts Clothing
12 Shoes Clothing
62 Glasses Clothing

Finally, create a third table storing just the Cust_Id and the Order_Id to keep track of all the orders for a customer:

Customer-order table

Cust_Id Order_Id
1 34
2 22
3 84
4 12
2 62
5 84

The third table is simply used to link the first two tables.

Now all the tables are in 2NF as there is no partial dependency between any column.

Get hands-on with 1200+ tech skills courses.