Identify if the Products Table is Normalized
Identify if the Products Table is Normalized
Products_UNF Table:Unnormalized Table (UNF)
Query Explanation:
-
1NF (First Normal Form):
-
Ensures that each field contains atomic values, meaning no lists or repeating groups. The data in
Products_UNFis already atomic, so it satisfies 1NF.
-
-
2NF (Second Normal Form):
-
Removes partial dependency, where attributes depend on part of the primary key. In the
Products_UNFtable, supplier details depend only onSupplierName, not the whole primary key. -
To move to 2NF, we create a separate
Suppliers1table to store supplier details and reference them bySupplierIDin theProducts_2NFtable.
-
SQL Query:
-- Step 1: Unnormalized Table (UNF)
CREATE TABLE Products_UNF (
ProductID INT,
ProductName VARCHAR(100),
Category VARCHAR(50),
SupplierName VARCHAR(100),
SupplierContact VARCHAR(15),
SupplierEmail VARCHAR(100),
UnitPrice DECIMAL(10,2)
);
-- Step 2: Insert unnormalized data (repeating suppliers)
INSERT INTO Products_UNF VALUES
(1, 'Laptop', 'Electronics', 'TechWorld', '9876543210', 'tech@world.com', 50000.00),
(2, 'Tablet', 'Electronics', 'TechWorld', '9876543210', 'tech@world.com', 30000.00),
(3, 'Monitor', 'Electronics', 'DisplayHub', '9123456789', 'support@displayhub.com', 15000.00),
(4, 'Headphones', 'Audio', 'SoundMax', '9988776655', 'info@soundmax.com', 5000.00),
(5, 'Speaker', 'Audio', 'SoundMax', '9988776655', 'info@soundmax.com', 7000.00);
-- Step 3: 1NF - Already satisfied (atomic values, no repeating groups)
-- Step 4: 2NF - Move supplier data to separate table
CREATE TABLE Suppliers1 (
SupplierID INT PRIMARY KEY,
SupplierName VARCHAR(100),
SupplierContact VARCHAR(15),
SupplierEmail VARCHAR(100)
);
INSERT INTO Suppliers1 VALUES
(1, 'TechWorld', '9876543210', 'tech@world.com'),
(2, 'DisplayHub', '9123456789', 'support@displayhub.com'),
(3, 'SoundMax', '9988776655', 'info@soundmax.com');
-- Step 5: Create normalized Products table (referencing SupplierID)
CREATE TABLE Products_2NF (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Category VARCHAR(50),
UnitPrice DECIMAL(10,2),
SupplierID INT,
FOREIGN KEY (SupplierID) REFERENCES Suppliers1(SupplierID)
);
INSERT INTO Products_2NF VALUES
(1, 'Laptop', 'Electronics', 50000.00, 1),
(2, 'Tablet', 'Electronics', 30000.00, 1),
(3, 'Monitor', 'Electronics', 15000.00, 2),
(4, 'Headphones', 'Audio', 5000.00, 3),
(5, 'Speaker', 'Audio', 7000.00, 3);
Output:
