City Guilds Model Questions Vol1 2020 Information Technology Question 5

A small e‑commerce company wants to store information about its customers, the products it sells, the orders placed and the items contained in each order. The following details are required: - Each customer has a unique CustomerID, name, email and contact number. - Each product has a unique ProductID, product name, category and unit price. - An order is identified by a unique OrderID, is placed by a single customer on a specific order date, and may contain one or more items. - Each order item records the OrderID, ProductID, quantity ordered and the unit price at the time of the order. Using this description, answer the following: (a) List all functional dependencies that can be inferred from the description. (b) Convert the information into relational tables that are in Third Normal Form (3NF). For each table give the table name, its attributes, the primary key and any foreign keys. (c) Write an SQL query that lists the names of customers who placed orders between 1 October 2023 and 31 December 2023 and whose total order value in that period exceeds ₦500 000. The list should be ordered from highest to lowest total spend. (d) The OrderItems table will store the fields OrderID (INT, 4 bytes), ProductID (INT, 4 bytes), Quantity (INT, 4 bytes) and UnitPrice (DECIMAL(10,2), 8 bytes). If the table contains 12 500 rows, calculate the total storage required for this table in megabytes (MB), ignoring any storage overhead.