Answer it
P1 a3 cafe normalise
6 marksYou mark it yourself against the marking points
A school café records orders in one table, OrderLine. Its primary key is the composite key (order_id, item_code). The table is already in first normal form.
| order_id | item_code | order_date | customer_id | customer_name | item_name | unit_price | quantity |
|---|---|---|---|---|---|---|---|
| 501 | C1 | 2026-10-12 | 77 | Lena | Flat white | 3.20 | 2 |
| 501 | P4 | 2026-10-12 | 77 | Lena | Croissant | 2.50 | 1 |
| 502 | C1 | 2026-10-12 | 80 | Sam | Flat white | 3.20 | 1 |
| 503 | T2 | 2026-10-13 | 77 | Lena | Mint tea | 2.80 | 2 |
Assume that each order is placed by one customer on one date, and that an item's unit_price is its current price, which is the same on every order.
(a) Identify one partial dependency in OrderLine. [1]
(b) Identify one transitive dependency in OrderLine. [1]
(c) Construct a set of tables in third normal form that holds the same data as OrderLine. For each table, give its name and attributes, and state its primary key and any foreign keys. [4]
[6]