order_sheet holds the order lines of a shop in one table. Its key is (order_id, product_id). Its FDs:

order_id               → order_date, customer_id
customer_id            → customer_name, customer_city
product_id             → product_name, unit_price
(order_id, product_id) → quantity

order_id → … and product_id → … are partial dependencies (2NF). customer_id → … is transitive (3NF). Decompose the table so that every determinant is a key.

1. How many tables does the BCNF design have?
2. Create customer(customer_id, customer_name, customer_city).
3. Create product(product_id, product_name, unit_price).
4. Create orders(order_id, order_date, customer_id) with a foreign key to customer.
5. Create order_line(order_id, product_id, quantity) with foreign keys to orders and product.
6. Join the four tables and return the nine columns of order_sheet, in its order. The result must have the same rows as order_sheet.

Keep order_sheet: the last check compares your tables with it.