Normalization Lab · Database Management System

SQLin-browser engine · runs locallynormalization

-- A denormalized table full of redundancy and update anomalies.
-- Convert it into 3NF by designing the relations below.

CREATE TABLE SALE_ORDERS (
  OrderNo INT PRIMARY KEY,
  Customer VARCHAR(50),
  City     VARCHAR(30),
  Item     VARCHAR(30),
  Qty      INT,
  UnitPrice DECIMAL(8,2)
);

INSERT INTO SALE_ORDERS VALUES
  (1, 'Alice', 'Lalitpur', 'Keyboard', 2, 1500.00),
  (2, 'Bob',   'Kathmandu', 'Mouse',   1, 800.00),
  (3, 'Alice', 'Lalitpur', 'Monitor',  1, 12000.00),
  (4, 'Cara',  'Pokhara',  'Keyboard', 1, 1500.00);

-- Task: split into 1NF -> 2NF -> 3NF relations. Example targets:
--   CUSTOMER(Customer, City)            -- removes the City -> Customer dependency
--   ITEM(Item, UnitPrice)               -- removes the UnitPrice -> Item dependency
--   ORDER_LINE(OrderNo, Item, Qty)      -- keeps only the order details
--   ORDERS(OrderNo, Customer)           -- one order belongs to one customer

-- Create the 3NF tables and re-run the data:
--   CREATE TABLE CUSTOMER (Customer VARCHAR(50) PRIMARY KEY, City VARCHAR(30));
--   CREATE TABLE ITEM (Item VARCHAR(30) PRIMARY KEY, UnitPrice DECIMAL(8,2));
--   CREATE TABLE ORDERS (OrderNo INT PRIMARY KEY, Customer VARCHAR(50));
--   CREATE TABLE ORDER_LINE (OrderNo INT, Item VARCHAR(30), Qty INT, PRIMARY KEY (OrderNo, Item));
--   INSERT INTO CUSTOMER VALUES ('Alice','Lalitpur'), ('Bob','Kathmandu'), ('Cara','Pokhara');
--   INSERT INTO ITEM VALUES ('Keyboard',1500.00), ('Mouse',800.00), ('Monitor',12000.00);
--   INSERT INTO ORDERS VALUES (1,'Alice'), (2,'Bob'), (3,'Alice'), (4,'Cara');
--   INSERT INTO ORDER_LINE VALUES (1,'Keyboard',2), (2,'Mouse',1), (3,'Monitor',1), (4,'Keyboard',1);
--   SELECT ORDERS.OrderNo, CUSTOMER.Customer, CUSTOMER.City, ORDER_LINE.Item, ORDER_LINE.Qty, ITEM.UnitPrice
--   FROM ORDERS JOIN CUSTOMER USING (Customer) JOIN ORDER_LINE USING (OrderNo) JOIN ITEM USING (Item);

Ctrl Enter to run36 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

-- Start: denormalized SALE_ORDERS CREATE TABLE SALE_ORDERS ( OrderNo INT PRIMARY KEY, Customer VARCHAR(50), City VARCHAR(30), Item VARCHAR(30), Qty INT, UnitPrice DECIMAL(8,2) ); -- Target relations (3NF): -- CUSTOMER(Customer, City) -- ITEM(Item, UnitPrice) -- ORDERS(OrderNo, Customer) -- ORDER_LINE(OrderNo, Item, Qty)