QUESTION 3 ZZ Burger Store is still using file-based method in their operational business. They record...

 
 

Transcribed Image Text:

QUESTION 3 ZZ Burger Store is still using file-based method in their operational business. They record the customer orders in Excel spreadsheet as shown in Table 2. Customers should have customer ID, customer name and their phone number. Table 2: ORDER DATA Table Order ID Order Date Menu Name Quantity Customer Name Customer Phone Number Staff ID and Name Fish 012- 11 30/06/2022 2 William 301 Willy Burger 34567899 012- 11 30/06/2022 Benjo 2 William 34567899 301 Willy Chicken 012- 12 30/06/2022 1 Andrew 302 Wendy Burger 1122334 Mutton 012- 13 07/08/2022 Kebab 1 Zach 301 Willy 5555555 Burger Chicken Double 012- 13 07/08/2022 3 Zach 301 Willy Cheese 5555555 Burger Answer the following questions. a) State the normal form of the above table. Explain your answer with reasons. (3 marks) b) Convert the table into a relational schema. Identify the primary key(s) and partial dependencies and/or transitive dependencies. (6 marks) c) Normalize the relational schema in (b) until third normal form (3NF). Show all your work. (11 marks) QUESTION 3 ZZ Burger Store is still using file-based method in their operational business. They record the customer orders in Excel spreadsheet as shown in Table 2. Customers should have customer ID, customer name and their phone number. Table 2: ORDER DATA Table Order ID Order Date Menu Name Quantity Customer Name Customer Phone Number Staff ID and Name Fish 012- 11 30/06/2022 2 William 301 Willy Burger 34567899 012- 11 30/06/2022 Benjo 2 William 34567899 301 Willy Chicken 012- 12 30/06/2022 1 Andrew 302 Wendy Burger 1122334 Mutton 012- 13 07/08/2022 Kebab 1 Zach 301 Willy 5555555 Burger Chicken Double 012- 13 07/08/2022 3 Zach 301 Willy Cheese 5555555 Burger Answer the following questions. a) State the normal form of the above table. Explain your answer with reasons. (3 marks) b) Convert the table into a relational schema. Identify the primary key(s) and partial dependencies and/or transitive dependencies. (6 marks) c) Normalize the relational schema in (b) until third normal form (3NF). Show all your work. (11 marks)