Normalize the following table(question corrected).
Steps for normlization
This is a follow-up article to the previous article where we discussed the printing mistake and ways to approach it. There we solved the question as it is, i.e., without correcting it. Here, we solve the same question with another approach. That is, we correct(i.e., swap) the columns(Cust_address and city) and answer the question accordingly.
Note: Even after correcting the columns, the data is still incomplete and non-matching. For example, the customer(Cust_Id, 1) has two cities Ktm and Pok. But the corresponding Cust_address, Zip_code, and House_no are single. Another example is, for Cust_Id 3, the city is Narayangadh and Cust_address is Baneshwor. We will ignore these issues and proceed to normalization.Back to top
1. First Normal Form(1NF)
The column city has non-atomic values ('Ktm, Pok' and 'But, Bhair'), which means multiple cities. Let's decompose the table and bring the multi-valued attribute 'city' to a different table R2 and the rest of the attributes will be in R1.
Now, the tables R1 and R2 are in First Normal Form since there aren't any no-atomic values.
Back to top
2. Second Normal Form(2NF)
1. Check for partial dependencies.
In R1, there is a single(non-composite) primary key. Hence R1 does NOT have partial dependencies.
In R2, there are only prime attributes. Hence, R2 does NOT have partial dependencies too.
Thus, R1 and R2 are in the Second Normal Form.
Back to top
3. Third Normal Form(3NF)
There are transitive dependencies in the relation R1.
Cust_Id → Items
Items → Company
i.e., Cust_Id → Items → Company
Items → Price
i.e., Cust_Id → Items → Price
Let's decompose R1 into R3 and R4 to bring it to the Third Normal Form.
Note: Here, the column Items is not suitable to be a Primary Key because item names are not unique. It is always a safer option to add a surrogate(artificial) primary key for it. So, we are adding the extra column Item_ID as a primary key.
There aren't any transitive dependencies anymore in R3 and R4. Hence, our final tables are R2, R4 and R5 which are in the Third Normal Form(3NF). The question has not targeted any particular normal form. In such cases, the relations in 3NF are considered good enough.Back to top