SCRIPT
Overview of the Data
MENU Table: Shows available dishes with their IDs, names, and prices.
CUSTOMERS, ORDERS, ORDER ITEMS: The entities that capture the ordering process by
customers.
Main Table (Unnormalized Form)
The Main table contains redundant and repetitive data:
Customer data, order details, and dish information are all stored in one table.
Multiple values in one field (e.g., DishID, DishName, Quantity, Price) are combined, which
violates the rules of atomicity.
It is difficult to manage updates, deletions, or insertions without affecting multiple rows.
1NF (First Normal Form)
Transformation to 1NF:
Objective: Eliminate repeating groups and ensure that each field contains only atomic values.
Changes: Separate rows for each item ordered:
o Each order is split into individual items.
o Each row now represents a unique combination of CustomerID, OrderID, DishID,
and other relevant details.
Result:
Each row is atomic, with no repeating groups.
Redundant data remains, as Customer and Order information is repeated for each dish
ordered.
2NF (Second Normal Form)
Transformation to 2NF:
Objective: Remove partial dependencies; fields should depend on the whole primary key, not
just part of it.
Changes: Split the data into separate tables:
o CUSTOMERS: Contains customer details with CustomerID as the primary key.
o ORDERS: Contains order details, linked by CustomerID (foreign key).
o ORDER ITEMS: Contains the details of each dish ordered, linked by OrderID (foreign
key).
Result:
Data redundancy is reduced but not completely eliminated.
Dependencies between fields are now appropriately structured.
3NF (Third Normal Form)
Transformation to 3NF:
Objective: Remove transitive dependencies; non-key attributes should not depend on other
non-key attributes.
Changes:
o Split the ORDER ITEMS into ORDER ITEMS and DISH tables:
DISH: Contains dish details separated from the ORDER ITEMS table to
eliminate redundancy.
Result:
Each table has a single, well-defined purpose.
Redundancy is minimized, and the database structure is efficient and easier to maintain.
Final Database Structure
CUSTOMERS: Stores unique customer information.
ORDERS: Stores order details, with each order linked to a customer.
DISH: Stores unique dish information.
ORDER ITEMS: Links orders to specific dishes, with quantity and price details.
Summary
1. Main: All information is mixed and unnormalized.
2. 1NF: Each piece of information is atomic, but redundancy remains.
3. 2NF: Partial dependencies removed, creating separate tables for customers, orders, and
order items.
4. 3NF: Transitive dependencies removed, with a dedicated DISH table to store dish details
separately.
This normalization process improves data consistency, reduces redundancy, and
makes the database more scalable and easier to maintain.