Assignment 2: Database Normalization

This homework assignment focuses on database normalization. A student submitted a solution detailing the process of normalizing a single, poorly designed 'orders' table into a more efficient relational database structure. The original table contained redundant data and non-atomic values, leading to inconsistencies and management difficulties. The solution demonstrates the normalization process up to 3NF (Third Normal Form), resulting in separate tables for orders, customers, items, and order items. Each table is designed with primary and foreign keys to ensure data integrity and eliminate redundancy. The summary highlights the improvements achieved through normalization, emphasizing atomicity, reliability, consistency, and the elimination of data redundancy. The normalized database is described as more manageable, understandable, and reliable, free from potential human or computational errors.

assignment 2 solution
Student name:
1 November, 2016

Contents
ORIGNAL TABLE STRUCTURE.................................................................................................................2
NORMALIZATION AND FINAL TABLE CREATION....................................................................................2
SUMMARY.............................................................................................................................................3
1 | P a g e

ORIGNAL TABLE STRUCTURE
The design of the single orders table is as follows:
Order_id
Order_date
Customer_id
Customer_name
Customer_address
Customer_city
Customer_state
Item_id
Item_description
Item_qty
Item_price
Item_total_price
Order_total_price
NORMALIZATION AND FINAL TABLE CREATION
To create a reliable and easily manageable and understandable database structure we applied
normalization on the above table structure, up to 3NF. The final structure developed from the above
single table is as follows:
2 | P a g e

Order table order item table
Order_id (PK)
Order_date
Customer_id (FK)
Order_total_price
Customer table items table
Customer_id (PK)
Customer_name
Customer_address
Customer_city
Customer_state
SUMMARY
The original table had redundant data like customer name, address, item name, description etc.
moreover, the values were not atomic. For example if a person’s order contains more than one item
then, item id, name and description will have multiple values. This increases data complexity and
redundancy and also reduces the consistency of the data. When we design a database it should be
1. Atomic.
2. Reliable
3. Consistent
4. Non-redundant
5. Complete
So in the large table, all the database properties were failing. The database could have human
errors or even computation errors. Also, the table structure was hard to manage and update if
3 | P a g e
Order_id (PK, FK)
Item_id (PK, FK)
Item_qty
Item_total_price
Item_id (PK)
Item_description
Item_price

required. So we had to apply normalization, to make it atomic and easily understandable and
manageable. After applying normalization we see that all table are linked using foreign keys and
each record in the table is uniquely stored using the primary key. There will be no redundancy as
all the values are atomic in nature. For example, item name, description, price, address etc. we
have also created a separate table to store order items. This has been done because an order
may have multiple items. So to maintain the atomicity, we carried out normalization operation.
Moreover order id and item id together form the primary key. Hence there is no error or
duplicity of records that can take place in an order. Now the database is reliable and in a
consistent state. The attributes are completely dependent on the respective primary keys and
there no partial or transitive dependency as well. The breakup of table was thus necessary to
improve the overall design of the database.
4 | P a g e