a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the file TPCHR, CREATE TABLE REGION( R_REGIONKEY NUMBER (12) NOT NULL, R_NAME CHAR (25) NOT NULL, NOT NULL, R COMMENT VARCHAR (152) CONSTRAINT REGION_PKEY PRIMARY KEY (R_REGIONKEY), CONSTRAINT REGION CHECK CHECK (R_REGIONKEY >= 0)); CREATE TABLE NATION ( N_NATIONKEY NUMBER (12) NOT NULL, N_NAME CHAR(25) NOT NULL, N REGIONKEY NUMBER (12) NOT NULL, N COMMENT VARCHAR (152) NOT NULL, CONSTRAINT NATION_PKEY PRIMARY KEY (N_NATIONKEY), CONSTRAINT NATION_FKEY1 FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY), CONSTRAINT NATION_CHECK1 CHECK (N_NATIONKEY >= 0)); CREATE TABLE PART ( P PARTKEY P NAME P MFGR P_BRAND P TYPE NUMBER (12) NOT NULL, VARCHAR (55) NOT NULL, VARCHAR (25) NOT NULL, CHAR(10) NOT NULL, VARCHAR (25) NOT NULL, P SIZE NUMBER (12) NOT NULL, P CONTAINER CHAR(10) NOT NULL, P RETAILPRICE NUMBER (12,2) NOT NULL, NOT NULL, P COMMENT VARCHAR (23) CONSTRAINT PART PEKEY PRIMARY KEY (P_PARTKEY), CONSTRAINT PART_CHECK1 CHECK (P_PARTKEY >= 0), CONSTRAINT PART CHECK2 CHECK (P SIZE >= 0), CONSTRAINT PART_CHECK3 CHECK (P RETAIL PRICE >= 0)); CREATE TABLE SUPPLIER ( S_SUPPKEY S NAME NUMBER (12) NOT NULL, S ADDRESS S NATIONKEY S PHONE S ACCTBAL S COMMENT CHAR(25) NOT NULL, VARCHAR (40) NOT NULL, NUMBER (12) NOT NULL, CHAR (15) NOT NULL, NUMBER (12,2) NOT NULL, VARCHAR (101) NOT NULL, CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY), CONSTRAINT SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT SUPPLIER_CHECK1 CHECK (S_SUPPKEY >= 0)); CREATE TABLE PARTSUPP ( PS PARTKEY NUMBER (12) NOT NULL, PS SUPPKEY NUMBER (12) NOT NULL, PS AVAILOTY NUMBER (12) NOT NULL, PS_SUPPLY COST NUMBER (12,2) NOT NULL, PS COMMENT VARCHAR (199) 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), CONSTRAINT PARTSUPP CHECK1 CHECK (PS PARTKEY >= 0), CONSTRAINT PARTSUPP CHECK2 CHECK (PS AVAILOTY >= 0), CONSTRAINT PARTSUPP_CHECK3 CHECK (PS SUPPLYCOST >= 0)); CREATE TABLE CUSTOMER ( C_CUSTKEY C NAME NUMBER (12) NOT NULL, VARCHAR (25) NOT NULL, C ADDRESS VARCHAR (40) NOT NULL, C NATIONKEY NUMBER (12) NOT NULL, C PHONE CHAR(15) NOT NULL, C ACCTBAL NUMBER (12,2) NOT NULL, C MKTSEGMENT CHAR(10) NOT NULL, C COMMENT VARCHAR (117) NOT NULL, CONSTRAINT CUSTOMER_PKEY PRIMARY KEY (C_CUSTKEY), CONSTRAINT CUSTOMER FKEY1 FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT CUSTOMER_CHECK1 CHECK (C_CUSTKEY >= 0)); CREATE TABLE ORDERS ( O ORDERKEY O CUSTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, O ORDERSTATUS CHAR(1) NOT NULL, O TOTALPRICE NUMBER (12,2) NOT NULL, O ORDERDATE DATE O_ORDERPRIORITY CHAR (15) O CLERK NOT NULL, NOT NULL, CHAR (15) NOT NULL, O SHIPPRIORITY NUMBER (12) NOT NULL, O COMMENT VARCHAR (79) NOT NULL, CONSTRAINT ORDERS PKEY PRIMARY KEY (O_ORDERKEY), CONSTRAINT ORDERS FKEY1 FOREIGN KEY (O CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY), CONSTRAINT ORDER_CHECKI CHECK (O_TOTALPRICE >= 0)); CREATE TABLE LINEITEM( L_ORDERKEY L PARTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, L SUPPKEY NUMBER (12) NOT NULL, L LINENUMBER NUMBER (12) NOT NULL, L QUANTITY NUMBER (12,2) NOT NULL, DATE NUMBER (12,2) L_EXTENDEDPRICE NUMBER (12,2) NOT NULL, L DISCOUNT L TAX NUMBER (12,2) NOT NULL, NOT NULL, L_RETURNFLAG CHAR(1) L LINESTATUS CHAR (1) NOT NULL, NOT NULL, NOT NULL, NOT NULL, L RECEIPT DATE L SHIPMODE DATE NOT NULL, NOT NULL, NOT NULL, L SHIPDATE L COMMITDATE DATE L SHIPINSTRUCT CHAR(25) L COMMENT CHAR(10) NOT NULL, VARCHAR (44) CONSTRAINT LINEITEM PKEY PRIMARY KEY (L_ORDERKEY, L LINENUMBER), CONSTRAINT LINEITEM_FKEY1 FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY), CONSTRAINT LINEITEM_FKEY2 FOREIGN KEY (L_PARTKEY) REFERENCES PART (P_PARTKEY), CONSTRAINT LINEITEM_FKEY3 FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS SUPPKEY), CONSTRAINT LINEITEM_FKEY4 FOREIGN KEY (L_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY), CONSTRAINT LINEITEM_CHECK1 CHECK (L_QUANTITY >= 0), CONSTRAINT LINEITEM_CHECK2 CHECK (L_EXTENDEDPRICE >= 0), CONSTRAINT LINEITEM CHECK3 CHECK (L TAX >= 0), CONSTRAINT LINEITEM_CHECK4 CHECK (L DISCOUNT BETWEEN 0.00 AND 1.00) ); In addition, the following indexes were created: CREATE INDEX A1TASK3IDX1 ON ORDERS (O_CLERK, O_ORDERSTATUS); CREATE INDEX A1TASK3IDX2 ON LINEITEM(L_ORDERKEY); For each one of the 10 types of different query or data manipulation statements listed below, provide one SQL statement consistent with the respective type and such that processing of SQL statement benefits from at least one of the indices over the relational tables ORDERS and LINEITEM. i. Projection queries (SELECT without WHERE clause). Selection queries (SELECT with WHERE claluse). ii. iii. iv. V. Join queries (join of two or more relational tables). Queries processed by accessing only an index. Group by query (SELECT with GROUP BY clause and aggregation function). vi. Antijoin query (SELECT with NOT IN, NOT EXISTS). vii. viii. Simple aggregation queries (SELECT with MAX, MIN, SUM, functions). Update statement. ix. Delete statement. X. Query with sorting (SELECT with ORDER BY clause). (4.0 marks) a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the file TPCHR, CREATE TABLE REGION( R_REGIONKEY NUMBER (12) NOT NULL, R_NAME CHAR (25) NOT NULL, NOT NULL, R COMMENT VARCHAR (152) CONSTRAINT REGION_PKEY PRIMARY KEY (R_REGIONKEY), CONSTRAINT REGION CHECK CHECK (R_REGIONKEY >= 0)); CREATE TABLE NATION ( N_NATIONKEY NUMBER (12) NOT NULL, N_NAME CHAR(25) NOT NULL, N REGIONKEY NUMBER (12) NOT NULL, N COMMENT VARCHAR (152) NOT NULL, CONSTRAINT NATION_PKEY PRIMARY KEY (N_NATIONKEY), CONSTRAINT NATION_FKEY1 FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY), CONSTRAINT NATION_CHECK1 CHECK (N_NATIONKEY >= 0)); CREATE TABLE PART ( P PARTKEY P NAME P MFGR P_BRAND P TYPE NUMBER (12) NOT NULL, VARCHAR (55) NOT NULL, VARCHAR (25) NOT NULL, CHAR(10) NOT NULL, VARCHAR (25) NOT NULL, P SIZE NUMBER (12) NOT NULL, P CONTAINER CHAR(10) NOT NULL, P RETAILPRICE NUMBER (12,2) NOT NULL, NOT NULL, P COMMENT VARCHAR (23) CONSTRAINT PART PEKEY PRIMARY KEY (P_PARTKEY), CONSTRAINT PART_CHECK1 CHECK (P_PARTKEY >= 0), CONSTRAINT PART CHECK2 CHECK (P SIZE >= 0), CONSTRAINT PART_CHECK3 CHECK (P RETAIL PRICE >= 0)); CREATE TABLE SUPPLIER ( S_SUPPKEY S NAME NUMBER (12) NOT NULL, S ADDRESS S NATIONKEY S PHONE S ACCTBAL S COMMENT CHAR(25) NOT NULL, VARCHAR (40) NOT NULL, NUMBER (12) NOT NULL, CHAR (15) NOT NULL, NUMBER (12,2) NOT NULL, VARCHAR (101) NOT NULL, CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY), CONSTRAINT SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT SUPPLIER_CHECK1 CHECK (S_SUPPKEY >= 0)); CREATE TABLE PARTSUPP ( PS PARTKEY NUMBER (12) NOT NULL, PS SUPPKEY NUMBER (12) NOT NULL, PS AVAILOTY NUMBER (12) NOT NULL, PS_SUPPLY COST NUMBER (12,2) NOT NULL, PS COMMENT VARCHAR (199) 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), CONSTRAINT PARTSUPP CHECK1 CHECK (PS PARTKEY >= 0), CONSTRAINT PARTSUPP CHECK2 CHECK (PS AVAILOTY >= 0), CONSTRAINT PARTSUPP_CHECK3 CHECK (PS SUPPLYCOST >= 0)); CREATE TABLE CUSTOMER ( C_CUSTKEY C NAME NUMBER (12) NOT NULL, VARCHAR (25) NOT NULL, C ADDRESS VARCHAR (40) NOT NULL, C NATIONKEY NUMBER (12) NOT NULL, C PHONE CHAR(15) NOT NULL, C ACCTBAL NUMBER (12,2) NOT NULL, C MKTSEGMENT CHAR(10) NOT NULL, C COMMENT VARCHAR (117) NOT NULL, CONSTRAINT CUSTOMER_PKEY PRIMARY KEY (C_CUSTKEY), CONSTRAINT CUSTOMER FKEY1 FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT CUSTOMER_CHECK1 CHECK (C_CUSTKEY >= 0)); CREATE TABLE ORDERS ( O ORDERKEY O CUSTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, O ORDERSTATUS CHAR(1) NOT NULL, O TOTALPRICE NUMBER (12,2) NOT NULL, O ORDERDATE DATE O_ORDERPRIORITY CHAR (15) O CLERK NOT NULL, NOT NULL, CHAR (15) NOT NULL, O SHIPPRIORITY NUMBER (12) NOT NULL, O COMMENT VARCHAR (79) NOT NULL, CONSTRAINT ORDERS PKEY PRIMARY KEY (O_ORDERKEY), CONSTRAINT ORDERS FKEY1 FOREIGN KEY (O CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY), CONSTRAINT ORDER_CHECKI CHECK (O_TOTALPRICE >= 0)); CREATE TABLE LINEITEM( L_ORDERKEY L PARTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, L SUPPKEY NUMBER (12) NOT NULL, L LINENUMBER NUMBER (12) NOT NULL, L QUANTITY NUMBER (12,2) NOT NULL, DATE NUMBER (12,2) L_EXTENDEDPRICE NUMBER (12,2) NOT NULL, L DISCOUNT L TAX NUMBER (12,2) NOT NULL, NOT NULL, L_RETURNFLAG CHAR(1) L LINESTATUS CHAR (1) NOT NULL, NOT NULL, NOT NULL, NOT NULL, L RECEIPT DATE L SHIPMODE DATE NOT NULL, NOT NULL, NOT NULL, L SHIPDATE L COMMITDATE DATE L SHIPINSTRUCT CHAR(25) L COMMENT CHAR(10) NOT NULL, VARCHAR (44) CONSTRAINT LINEITEM PKEY PRIMARY KEY (L_ORDERKEY, L LINENUMBER), CONSTRAINT LINEITEM_FKEY1 FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY), CONSTRAINT LINEITEM_FKEY2 FOREIGN KEY (L_PARTKEY) REFERENCES PART (P_PARTKEY), CONSTRAINT LINEITEM_FKEY3 FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS SUPPKEY), CONSTRAINT LINEITEM_FKEY4 FOREIGN KEY (L_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY), CONSTRAINT LINEITEM_CHECK1 CHECK (L_QUANTITY >= 0), CONSTRAINT LINEITEM_CHECK2 CHECK (L_EXTENDEDPRICE >= 0), CONSTRAINT LINEITEM CHECK3 CHECK (L TAX >= 0), CONSTRAINT LINEITEM_CHECK4 CHECK (L DISCOUNT BETWEEN 0.00 AND 1.00) ); In addition, the following indexes were created: CREATE INDEX A1TASK3IDX1 ON ORDERS (O_CLERK, O_ORDERSTATUS); CREATE INDEX A1TASK3IDX2 ON LINEITEM(L_ORDERKEY); For each one of the 10 types of different query or data manipulation statements listed below, provide one SQL statement consistent with the respective type and such that processing of SQL statement benefits from at least one of the indices over the relational tables ORDERS and LINEITEM. i. Projection queries (SELECT without WHERE clause). Selection queries (SELECT with WHERE claluse). ii. iii. iv. V. Join queries (join of two or more relational tables). Queries processed by accessing only an index. Group by query (SELECT with GROUP BY clause and aggregation function). vi. Antijoin query (SELECT with NOT IN, NOT EXISTS). vii. viii. Simple aggregation queries (SELECT with MAX, MIN, SUM, functions). Update statement. ix. Delete statement. X. Query with sorting (SELECT with ORDER BY clause). (4.0 marks)
Expert Answer:
Related Book For
Database Systems Design Implementation and Management
ISBN: 978-1337627900
13th edition
Authors: Carlos Coronel, Steven Morris
Posted Date:
Students also viewed these databases questions
-
Professional Image Briefcases is an exclusive producer of handcrafted, stylish cases. The company assembles each case with care and attention to detail. This laborious process requires the completion...
-
Infinity & Beyond, Inc. is a producer of high-tech fashion merchandise. The company's marketing department has identified a new product concept through discussions with potential customers conducted...
-
You are a talented seamstress and designer. After you graduate with a degree in fashion design, you start a small business with some financial help from your parents. Your business has been quite...
-
Describe the effects of emotions on breathing.
-
How has high population, urbanization, mobility, and increasing dependence on manufacturing and information affected family and gender?
-
On a daily basis, the Vampire Van is dispatched from Maplewood Hospital to pickup blood and platelet donations made at its local donation centers. The distances in miles between all locations may be...
-
You have estimated that your current computer is going to last one more year. Then you will need to buy a new computer. You have some cash now but it does not make sense to buy a computer if you do...
-
Their 1.5 inch, 90-degree copper elbow (see below) is a very popular item in the plumbing line that comes packaged in 10 elbows per box. The box costs about $65 to Western, and Western sells it at...
-
Compute cash flows from financing activities using the above company information. Note: Amounts to be deducted should be indicated by a minus sign. Financing Activities
-
Using the nomograph provided overleaf and the following information, suggest a series of size and mass reduction steps to yield an appropriately sampled 0.5 g analytical sample of ore. (you need to...
-
Required: a ) Compute the predetermined overhead rate for the year. b ) Compute the amount of under - or overapplied overhead for the year. Prepare a schedule of cost of goods manufactured for the...
-
What is the ASC reference for the content of the presentation of receivables from officers, employees, or affiliates? What is the ASC reference for the five steps in the revenue recognition process?
-
Describe and explain your choice of developing your own creativity. a. Using any ONE (1) of the idea generation methods, suggest any potential service or product idea that may be within the IT...
-
Tendering is the main method of procurement in the Kenyan public sector. You are required to; State the requirements that a supplier should fulfill in order to qualify for tendering in Kenya, What is...
-
For a wide sense stationary process: Select one: O a. the variance is constant O b. all of these O c. the covariance is constant O d. the expected value is constant
-
The activities listed in lines 2125 serve primarily as examples of A) Underappreciated dangers B) Intolerable risks C) Medical priorities D) Policy failures
-
Use the Food Critics Club (FCC) graph database that was created and used earlier in the text for use with Neo4j. Create a restaurant node with restaurant id is 10000, the name Hungry Much, and...
-
Based on the following query: SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM(LINE_UNITS) > (SELECT MAX(LINE_UNITS) FROM LINE); What is the likely data sparsity of the LINE_UNITS...
-
What is a sequence? Write its syntax.
-
The reciprocating mass, crank radius, and connecting-rod length of each of the cylinders in a two-cylinder in-line engine are given by \(m, r\), and \(l\), respectively. The crank angles of the two...
-
The force transmitted by an internal combustion engine of mass \(500 \mathrm{~kg}\), when placed directly on a rigid floor, is given by \[F_{t}(t)=(18,000 \cos 300 t+3600 \cos 600 t) \mathrm{N}\]...
-
A shaft, having a stiffness of \(3.75 \mathrm{MN} / \mathrm{m}\), rotates at \(3600 \mathrm{rpm}\). A rotor, having a mass of \(60 \mathrm{~kg}\) and an eccentricity of 2000 microns, is mounted on...
Study smarter with the SolutionInn App