Solution to Exercise 1

Solution

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.