Normalize for reporting and performance
SQL
Hard
2 views
Problem Description
OrdersReport(order_id, customer_city, product_category, qty, amount). Explain in description that this is a report table and show base normalized tables in SQL.
Output Format
DDL statement(s)
Sample Test Case
Input:
OrdersReport is denormalized
Output:
Normalized base tables
Constraints
Keep reporting separate from OLTP
Official Solution
CREATE TABLE Customers (customer_id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100)); CREATE TABLE Products (product_id INT PRIMARY KEY, name VARCHAR(100), category VARCHAR(100)); CREATE TABLE Orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)); CREATE TABLE OrderItems (order_id INT, product_id INT, qty INT, price DECIMAL(10,2), PRIMARY KEY(order_id, product_id));
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!