Consider the following SELECT statement: SELECT O ORDERDATE, SUM(L_QUANTITY) FROM LINEITEM JOIN ORDERS ON LINEITEM.L ORDERKEY...

 

Transcribed Image Text:

Consider the following SELECT statement: SELECT O ORDERDATE, SUM(L_QUANTITY) FROM LINEITEM JOIN ORDERS ON LINEITEM.L ORDERKEY = ORDERS.O ORDERKEY GROUP BY O ORDERDATE; (1) Write SQL statement that denormalizes a relational table LINEITEM. The denormalization supposed to speedup the processing of a new SELECT statement that retrieves the same results as SELECT statement listed above and it uses only a denormalized table LINEITEM. (2 marks) (2) Write SQL statement that reloads data from the relational table ORDERS into the denormalized table LINEITEM created in a step (1). (3 marks) (3) Write a new SELECT statement that retrieves the same results as the original SELECT statement listed above and it uses only a denormalized table LINEITEM. (1 mark) (4) Explain what data redundancies are caused by the denormalization of a relational table LINEITEM and why a new SELECT statement created in a step (4) can be processed faster than the original SELECT statement. (2 marks) Consider the following SELECT statement: SELECT O ORDERDATE, SUM(L_QUANTITY) FROM LINEITEM JOIN ORDERS ON LINEITEM.L ORDERKEY = ORDERS.O ORDERKEY GROUP BY O ORDERDATE; (1) Write SQL statement that denormalizes a relational table LINEITEM. The denormalization supposed to speedup the processing of a new SELECT statement that retrieves the same results as SELECT statement listed above and it uses only a denormalized table LINEITEM. (2 marks) (2) Write SQL statement that reloads data from the relational table ORDERS into the denormalized table LINEITEM created in a step (1). (3 marks) (3) Write a new SELECT statement that retrieves the same results as the original SELECT statement listed above and it uses only a denormalized table LINEITEM. (1 mark) (4) Explain what data redundancies are caused by the denormalization of a relational table LINEITEM and why a new SELECT statement created in a step (4) can be processed faster than the original SELECT statement. (2 marks)