Task

Indexing

A user CSCI315 used the following CREATE TABLE statements to create a sample database.

CREATE TABLE ORDERS(
O_ID NUMERIC(10) NOT NULL, /* Unique ID of order */
O_C_ID NUMERIC(10) NOT NULL, /* Customer ID */
O_DATE TIMESTAMP NOT NULL, /* Order date an time */
O_TOTAL NUMERIC(15,2) NOT NULL, /* Total for this order */
CONSTRAINT ORDERS_PKEY PRIMARY KEY (O_ID) );

CREATE TABLE ORDER_LINE(
OL_O_ID NUMERIC(10) NOT NULL, /* Order ID */
OL_ITEM NUMERIC(10) NOT NULL, /* Item name */
OL_QTY NUMERIC(3) NOT NULL, /* Quantity of item */
CONSTRAINT ORDER_LINE_PKEY PRIMARY KEY (OL_O_ID, OL_ITEM),
CONSTRAINT ORDER_LINE_FKEY2 FOREIGN KEY (OL_O_ID)
REFERENCES ORDERS(O_ID) );

(1) Write SELECT statements that implement the following queries.

(i) Find the total number of orders submitted in 2015.

(ii) List all distinct customer IDs (O_C_ID) who submitted the largest number of orders in 2014.

(iii) Find the total number if orders submitted by each customer.

(iv) Find the distinct names of items (OL_ITEM) included in at least one order.

(v) Find the total number of values in a column OL_ITEM.

(vi) Find a name of item ordered with the largest quantity.

(vii) Find the total number of rows in a relational table ORDERS.

(viii) Find the total (O_TOTAL) of all orders in each year.

(2) Write smallest number of CREATE INDEX statement such that each one of the queries listed above is processed through "index-only" computations. It means the indexes must be created in such a way such that there is no need to access the relational tables listed above to find the results of queries and the indexes must be "shared" by the possibly largest number of queries.