Answered: a) Consider the relational schemas of…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

Hey experts, please help me out on this :( xx

Sample database:

For all the implemented tasks, your report or output must include a listing of all PL/SQL
statements processed. To achieve that put the following SQL*Plus commands in all
your scripts:
SPOOL file-name
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.

Transcribed Image Text:Indexing a) Consider the relational schemas of the TPCHR sample database. 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 and write the 'create index' statement to create the index. 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 than accessing the tables through an index. i. ii. iii. iv. V. SELECT p_brand, p_type, p_retailprice FROM part; SELECT count(*) FROM part WHERE p_brand= 'Brand# 18'; SELECT I_orderkey, count(*) FROM lineitem WHERE I_orderkey = 1184000 GROUP BY I_orderkey; SELECT max(ps_suppkey) FROM partsupp; SELECT ps_suppkey, ps_partkey, ps_supplycost FROM partsupp WHERE ps_suppkey = 3699 AND ps_partkey = 700; Deliverables Submit your spooled file solution1a.lst (or solution1a.pdf) that contains your SQL script and the output from the execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed. Remember to set ECHO option of SQL*Plus to ON!

Expert Solution

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions