Indexing Consider a relational database that consists of the relational tables created by the following CREATE TABLE statements: CREATE TABLE PART( P_PARTKEY NUMBER(12) NOT NULL, P_NAME VARCHAR(55) NOT NULL, P_RETAILPRICE NUMBER(12,2) NOT NULL, CONSTRAINT PART_PEKEY PRIMARY KEY (P_PARTKEY) ); CREATE TABLE SUPPLIER( S_SUPPKEY NUMBER(12) NOT NULL, S_NAME CHAR(25) NOT NULL S_NATIONKEY NUMBER(12) NOT NULL, S_PHONE CHAR(15) NOT NULL, CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY) ); CREATE TABLE PARTSUPP( PS_PARTKEY NUMBER(12) NOT NULL, PS_SUPPKEY NUMBER(12) NOT NULL, PS_SUPPLYCOST NUMBER(12,2) NOT NULL, CONSTRAINT PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY), CONSTRAINT PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY), CONSTRAINT PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY) ); Determine what index should be created to improve the performance of the queries listed below in the best possible way. Consider each one of the queries as an individual case. If you decide that an index should be created, then list the names of attributes that form an index key. Remember that the order of attributes in an index key is important. If you decide not to create an index explain why the performance will not suffer when a new index is not available. Assume that all relational tables are large enough to make full tables scan more time consuming that accessing the tables through an index. i) SELECT * FROM PART WHERE P_NAME = 'MOTHER-BOARD' AND P_RETAILPRICE = 250; ii) SELECT COUNT(*) FROM PART WHERE P_NAME= 'COOLING-FAN'; iii) SELECT PS_SUPPKEY FROM PARTSUPP WHERE PS_PARTKEY = 'MOTHER-BOARD'; v) SELECT S_NAME FROM SUPPLIER; vi) SELECT PS_PARKEY,COUNT(*) FROM PARTSUPP GROUP BY PS_PARTKEY HAVING COUNT(*) >3;

Indexing

Consider a relational database that consists of the relational tables created by the following CREATE TABLE statements:

CREATE TABLE PART(

P_PARTKEY NUMBER(12) NOT NULL,

P_NAME VARCHAR(55) NOT NULL,

P_RETAILPRICE NUMBER(12,2) NOT NULL,

CONSTRAINT PART_PEKEY PRIMARY KEY (P_PARTKEY) );

CREATE TABLE SUPPLIER(

S_SUPPKEY NUMBER(12) NOT NULL,

S_NAME CHAR(25) NOT NULL

S_NATIONKEY NUMBER(12) NOT NULL,

S_PHONE CHAR(15) NOT NULL,

CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY) );

CREATE TABLE PARTSUPP(

PS_PARTKEY NUMBER(12) NOT NULL,

PS_SUPPKEY NUMBER(12) NOT NULL,

PS_SUPPLYCOST NUMBER(12,2) NOT NULL,

CONSTRAINT PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY), CONSTRAINT PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY),

CONSTRAINT PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY) );

Determine what index should be created to improve the performance of the queries listed below in the best possible way. Consider each one of the queries as an individual case. If you decide that an index should be created, then list the names of attributes that form an index key. Remember that the order of attributes in an index key is important. If you decide not to create an index explain why the performance will not suffer when a new index is not available. Assume that all relational tables are large enough to make full tables scan more time consuming that accessing the tables through an index.

i) SELECT * FROM PART WHERE P_NAME = 'MOTHER-BOARD' AND P_RETAILPRICE = 250;

ii) SELECT COUNT(*) FROM PART WHERE P_NAME= 'COOLING-FAN';

iii) SELECT PS_SUPPKEY FROM PARTSUPP WHERE PS_PARTKEY = 'MOTHER-BOARD';

v) SELECT S_NAME FROM SUPPLIER;

vi) SELECT PS_PARKEY,COUNT(*) FROM PARTSUPP GROUP BY PS_PARTKEY HAVING COUNT(*) >3;

Subject: MySQL