It 330 Lab 3 Normalization: Objective Normalize Table To Thi
It 330 Lab 3 Normalizationobjectivenormalize Table To Third Normal F
It 330 Lab 3: Normalization Objective: Normalize table to third normal form (3NF) Submission requirements: · For all text and image submissions, use MS Word. · For all SQL code submissions, use MS Word. · For all diagrams submissions, use MS Visio. · If the submission is more than one file: 1. Name each item appropriately. a. For example: LAB3-Normalization-yourName.vsd, LAB3-Questions-yourName.docx 2. Save each item in a single folder. 3. This folder should also be named appropriately. a. For example: LAB3-yourName 4. Compress the folder. 5. Submit the compressed file in Blackboard. Lab: For this assignment, you will normalize the below Orders table to 3NF. Make sure to identify all the entities and their attributes, especially if you make any new items (such as entity or attributes). The following is a list of possible attributes for ORDERS; you may have more: ORDERS OrderNo CustomerNo CustomerName CustomerAddress CustomerCity CustomerState CustomerZip OrderDate ItemsOrdered Total Submit dependency diagrams for all entities you create. The following table is a sample of data, before ORDERS was normalized. Please start with the below table and then write out each dependency diagram as you move from 1NF to 2NF to 3NF. Show your progress and submit your final document with all dependency diagrams in Blackboard. The following table is a sample, before ORDERS was normalized: ORDER NO ORDER DATE CUSTOMER NO CUSTOMER NAME CUSTOMER ADDRESS ITEMS ORDERED /1/ ACME Co 1234 1st St. 1A4536, Flange, 7lbs, $75;4-OR2400, Injector, .5lbs, $108;4-OR2403, Injector, .5lbs, $116;1-4I5436, Head, 63lbs, $/1/ Sneed Corp. 555 Main Ave. 1-3X9567, Pump, 5lbs, $62./1/ ZZZ & Co. 4242 SW 2nd 7-8G9200; Fan, 3lbs, $84;1-8G5437, Fan, 3lbs, $15;1-3H6250, Control, 5lbs, $
Paper For Above instruction
Introduction
Normalization is a systematic approach in database design aimed at minimizing redundancy and dependency by organizing fields and tables according to specific rules. The primary goal of normalization is to ensure data integrity and efficient data management, especially as the database evolves. This paper describes the process of normalizing an unstructured Orders table into Third Normal Form (3NF), illustrating the progressive steps through First Normal Form (1NF), Second Normal Form (2NF), and finally, 3NF, supported by dependency diagrams and entity identification.
Initial Table Analysis and Attributes
The starting point is an Orders table containing attributes such as OrderNo, CustomerNo, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZip, OrderDate, ItemsOrdered, and Total. The table initially contains duplicate data, multiple items per order, and potential redundancies. For example, customer details are repeated across multiple orders, and items within an order are stored as concatenated strings, which violate normalization principles.
The attributes are identified as follows:
- Orders: OrderNo, OrderDate, Total
- Customer: CustomerNo, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZip
- Items: ItemsOrdered (containing multiple items per order)
To normalize, we need to decompose this structure into related entities and eliminate redundancies.
First Normal Form (1NF)
To achieve 1NF, the table must have atomic values; that is, each field contains only indivisible values, and there must be a unique primary key for each record.
In the current table, the ItemsOrdered attribute contains multiple items separated by semicolons and commas, violating the atomicity condition. Therefore, the first step is to break down these concatenated strings into individual item records.
The normalized structure involves creating a separate table for individual items, with each item linked to its order via OrderNo. This entails creating a new entity, say, OrderItems, with attributes such as OrderNo, ItemID, ItemDescription, Quantity, and Price.
The Orders table now contains unique OrderNo, OrderDate, CustomerNo, and Total, while the Customer table holds customer-related attributes.
Second Normal Form (2NF)
To achieve 2NF, the table must be in 1NF and all non-key attributes depend fully on the primary key. Partial dependencies must be eliminated.
In this context, the primary key of Orders is OrderNo, and for Customer-related attributes, they depend solely on CustomerNo. Thus, Customer details should be in a separate Customer table to eliminate redundancy and partial dependency.
Furthermore, since ItemsOrdered attributes are split into OrderItems, dependencies exist where ItemDescription, Quantity, and Price depend on ItemID. Hence, an Item table should be created containing ItemID, ItemDescription, and Price.
This decomposes the data into three main entities:
- Customer(CustomerNo, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZip)
- Orders(OrderNo, OrderDate, CustomerNo, Total)
- Items(ItemID, ItemDescription, Price)
And an associative table:
- OrderItems(OrderNo, ItemID, Quantity)
This structure ensures that each non-key attribute depends fully on its primary key and eliminates partial dependencies.
Third Normal Form (3NF)
To achieve 3NF, we must remove transitive dependencies, meaning non-key attributes cannot depend on other non-key attributes.
In this case, CustomerAddress, CustomerCity, CustomerState, and CustomerZip depend on CustomerNo, which is already a primary key in the Customer table, satisfying 3NF.
Similarly, in OrderItems, the Quantity depends on the relationship between OrderNo and ItemID, and not on other non-key attributes.
The final normalized schema is:
- Customer(CustomerNo PRIMARY KEY, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZip)
- Orders(OrderNo PRIMARY KEY, OrderDate, CustomerNo FOREIGN KEY, Total)
- Items(ItemID PRIMARY KEY, ItemDescription, Price)
- OrderItems(OrderNo FOREIGN KEY, ItemID FOREIGN KEY, Quantity)
Dependency Diagrams
Dependency diagrams illustrate the relationships and dependencies between entities during each normalization phase:
- In 1NF, the diagram shows all attributes stored in a single table with atomic values.
- In 2NF, the diagram demonstrates the separation of customer data into a distinct entity, with foreign key relationships.
- In 3NF, the diagrams show the relational structure with no transitive dependencies, clearly separating entity attributes, and establishing primary and foreign keys.
Conclusion
Through systematic normalization, the Orders table was decomposed from a flat, potentially redundant structure into a well-organized relational schema in 3NF. This process minimized redundancy, ensured data integrity, and improved database efficiency. Documenting dependency diagrams at each stage provided clarity and guided the normalization process, illustrating the logical evolution of the database design from raw data to a normalized schema.