Q7 Normalization.txt

-- Create the database
CREATE DATABASE MyDatabase;

-- Use the database
USE MyDatabase;

-- Create the tables
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    Phone VARCHAR(20)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Subtotal DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Apply normalization
-- This example follows at least the third normal form (3NF)

-- Ensure atomicity: Each column should hold only one piece of information
-- CustomerID, Name, Email, Phone are atomic in the Customers table
-- OrderID, CustomerID, OrderDate, TotalAmount are atomic in the Orders table
-- ProductID, Name, Price are atomic in the Products table
-- OrderDetailID, OrderID, ProductID, Quantity, Subtotal are atomic in the OrderDetails table

-- Eliminate repeating groups: Each table should contain only related data
-- Customers, Orders, Products, and OrderDetails tables contain related data without repeating groups

-- Eliminate redundant data: Store data in only one place
-- CustomerID in the Orders table references CustomerID in the Customers table, avoiding redundant customer information in multiple orders
-- ProductID in the OrderDetails table references ProductID in the Products table, avoiding redundant product information in multiple order details

-- Normalize relationships between tables: Ensure relationships are defined properly
-- Orders table has a foreign key (CustomerID) referencing the primary key in the Customers table
-- OrderDetails table has foreign keys (OrderID, ProductID) referencing the primary keys in the Orders and Products tables respectively