New

Learn smart - Learn online. Upto 80% off on courses for a limited time. View Courses

Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

Stuck on Your Question?

Get 24x7 live help from our Top Tutors. All subjects covered.

loader
250 words

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Students Who Viewed This Also Studied

6 Pages
Entity Relationship Modeling

Week 9 Lab May 10 2021 Student ID: Student Name: Module Tutor: Report Stored Procedure 1 -- Create Test Procedure DROP PROCEDURE IF EXISTS test; DELIMITER // CREATE P ...

Course

ITEC200

Subject

Database

University

Australian Catholic University

Season

Spring

52 Pages
Systems Analysis

ITEC200 Data and Information Management Week 9 Database Design Process Dr Mehdi Rajaeian Semester 1, 2021 What are we doing this week? 2| Faculty of Business and Law | Peter Faber Business School ...

Course

ITEC200

Subject

Database

University

Australian Catholic University

Season

Spring

25 Pages
IMAT5103-Database Systems and Design

Stage 1: Scenario and Conceptual Database Design Task 1.1: Selection of the case upon which the database design and implementation is to be based irst, you need to identify a suitable case study (yo ...

Course

IMAT5103

Subject

Database

University

De Montfort University

4 Pages
CSCI237 Introduction to Computers and Information Systems

Part 1 How many tables are there in this database? What are the fields of each table? what are their primarykeys? Is there any foreign key in any of these tables? Answer these questions and use MS A ...

Course

CSCI237

Type

Programming

Subject

Database

University

Columbia College

ICT330 Database Management Systems

Question

Answered

Questions:

Task 3 Indexing
a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the file TPCHR.
An index ordersIdx(o_orderStatus, o_clerk, o_totalPrice) has been created over the relational table
 
ORDERS.
Construct SELECT statements that will use the index ordersIdx in the following ways:
i. Execution of the first SELECT statement must traverse the index vertically and it MUST NOT access a relational table ORDERS.
ii. Execution of the second SELECT statement must traverse the index vertically and then horizontally at the leaf level of the index and it MUST NOT access the relational table ORDERS.
iii. Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it MUST NOT access the relational table ORDERS.
iv. Execution of the fourth SELECT statement must traverse the index vertically and it MUST access the relational table ORDERS.
v. Execution of the fifth SELECT statement must traverse the index vertically and then horizontally and it MUST access a relational table ORDERS.
 
Deliverables
A file Task3a.pdf with CREATE INDEX statements that improve the performance of the queries listed (i, ii, iii, iv, and v above) and the execution plan showing how the queries uses the created index to speed up the queries. Use ‘Explain plan for …’ to generate the execution plan for each of the SELECT statements that you have proposed and created. Next use the ‘SELECT * FROM TABLE(dbms_xplan.display) to display the generated execution plan to show that your proposed
SELECT statements indeed perform according to the specification.
b. Referring to the same TPCHR conceptual schema, we would like to improve performance of the following group of queries:
i. Find the total number of orders that include at least one item (relational schema LINEITEM) with tax rate = 0.1 from orders made in year ‘1998’.
ii. Find the comment (L_COMMENT) of all items (relational schema LINEITEM) included in the orders that were handle by a clerk with the number ‘clerk#000000136’.
iii. Find the names of customers who submitted orders in a given year.
 
Find index that speeds up processing of the statements in the best possible way listed above, and create the index. You are only allowed to create ONE index per query. The best possible way means the database system will execute a query and uses the index proposed by you.
 
Use ‘Explain plan for …’ to generate the execution plan for each of the SELECT statements that you have proposed and created. Next use the ‘SELECT * FROM TABLE(dbms_xplan.display) to display the generated execution plan to show that your proposed SELECT statements indeed perform according to the specification. 

ICT330 Database Management Systems

Answer in Detail

Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

13 More Pages to Come in This Document. Get access to the complete answer.

Specially designed essay help services have made MyAssignmenthelp.com one of the most prolific proposal essay help providers. We have arrays of expert essay writers who successfully deliver essay help depending on broad ranges of subjects and topics. We provide nursing essay help, MBA essay help, English Essay writing help and law essay help to students from different fields of study. Broad ranges of essay writing services assist us to offer quality contents within a specific time limit.

More ICT330 ICT330 Database Management Systems: Questions & Answers

Q
icon

We aren't endorsed by this University

Entity Relationship Modeling

Week 9 Lab May 10 2021 Student ID: Student Name: Module Tutor: Report Stored Procedure 1 -- Create Test Procedure DROP PROCEDURE IF EXISTS test; DELIMITER // CREATE PROCEDURE test() BEGIN DECLARE avg_amount Double; DECLARE rental_amount Double; ...

View Answer
Q
icon

We aren't endorsed by this University

Systems Analysis

ITEC200 Data and Information Management Week 9 Database Design Process Dr Mehdi Rajaeian Semester 1, 2021 What are we doing this week? 2| Faculty of Business and Law | Peter Faber Business School •After completing this module, you will be able to: • Design databases using the six phases in ...

View Answer
Q
icon

We aren't endorsed by this University

IMAT5103-Database Systems and Design

Stage 1: Scenario and Conceptual Database Design Task 1.1: Selection of the case upon which the database design and implementation is to be based irst, you need to identify a suitable case study (your choice or a choice from the list provided in the appendix) from which to derive your database req ...

View Answer
Q
icon

We aren't endorsed by this University

CSCI237 Introduction to Computers and Information Systems

Part 1 How many tables are there in this database? What are the fields of each table? what are their primarykeys? Is there any foreign key in any of these tables? Answer these questions and use MS Access tocreate the tables and their relationships. Hint: Do not forget that the cardinality of the ...

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

Choose Our Best Expert to Help You

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?