The Art of Analysis - Research Paper Example

To normalize the table into the third form it means that the tables must attain the following status or characteristics:1. There should be elements or groups of elements that repeat themselves within a table2. There should be no partial dependencies on a concatenated key3. Non-key attributes should not depend on each other. The first step is to design a table for all of the attributes below then start to normalize it up to the third normal form. Orders: Order_id Order_dateCustomer_id Customer_name Customer_address Customer_city Customer_state Item_id Item_description Item_qty Item_price Item_total_price Order_total_priceFigure 1 table with all the fieldsThe First Normal Form In this step redundancy or repetition of data is reduced while at the same time ensures that there is a unique column of the table that identify each item in the table uniquely.

Looking at the above table there is no single column that can uniquely identify each of the items in a table. Therefore, two columns will be combined to form a primary key. This means that the uniqueness of the items are identified by Order_ID and Item_ID, this is called concatenation.OrdersOrder_id(PK) Order_date Customer_id Customer_name Customer_address Customer_city Customer_state Item_id (PK)Item_description Item_qty Item_price Item_total_price Order_total_priceDiagram 1The Second Normal Form Dependencies should not be partial on the combined key; this is the main task in this stage.

The items in the table must depend on all the combined keys, attributes should not depend on only one of the keys. Sending of normal form fails if this is not attained. Item total price and order total price are derivatives, hence, they should not be a part of the database all these fields, apart from the item quantity, do not fully depend on both order id and item id, hence, the table fails the 2nd normal form standards or requirements. OrdersOrder_id(PK) Order_date Customer_id Customer_name Customer_address Customer_city Customer_state Item_id (PK)Item_description Item_qty Item_price Item_total_price Order_total_priceDiagram 2.

The 1st normal form The solution to this is to split the table into two tables so that the items that depend on order id fall in the table with order-id as the primary key, while those items that depend on item-id fall in that table too. Given that item_qty fully depends on order id, item id field and order id field, which is also in the order-item table as primary forming a many to one relation indicated in the diagrams. After this the table now passed to the 2nd normal form by having field which depends fully on the primary key in the orders table (Diagram 3) but in order-items table the item_price and item_descripition do not depend on order_id as in case with the primary key, hence, further splitting of this table is needed.

With diagram 4 all the requirements for the 2nd normalization have been achieved. Diagram 3 Diagram 4 . The 2nd normalized form The 3d normal form The following should be attained1. There should be elements or groups of elements that repeat themselves within a table2. There should be no partial dependencies on a concatenated key3. Non-key attributes should not depend on each other.To avoid repetition, for instance, if a customer makes several orders, their address and name will be repeated in the order tables, hence, another table called Customers table is created.

Diagram 5. The 3rd normal formFigure 2. The 3rd normalized formFigure 3 ReferencesLanger, Art. (1997). The Art of Analysis, devotes considerable space to normalization. Springer-Verlag Telos.Dr. Codd's Seminal. (1969). Paper on database normalization. Retrieved from www.acm.org/classics/nov95Coulson, Fred. (2010). 3 Normal Forms Database. Retrieved from http://www.phlonx.com/resources/nf3/