BuyFindarrow_forward

Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900

Solutions

Chapter
Section
BuyFindarrow_forward

Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900
Chapter 11, Problem 23P
Textbook Problem
34 views

Problems 22–24 are based on the following query:

SELECT P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE, V_STATE
FROM PRODUCT P, VENDOR V
WHERE P.V_CODE = V.V_CODE
  AND V_STATE = 'NY'
AND V_AREACODE = '212'
ORDER BY P_PRICE;

Write the commands required to create the indexes you recommended in Problem 22.

Program Plan Intro

Indexes:

  • Indexing is a technique that is used in SQL (Structured Query Language) for optimizing the performance.
  • Indexes are used when a small set of rows needs to be selected from the table that is large in size.
  • The selection of rows is made by specifying conditions.

Rules for using indexes:

The below are the rules how the indexes can be used

  • When an indexed column appears within itself in the search criteria of “WHERE” or “HAVING” clause.
  • When an indexed column appears within itself in “GROUP BY” or “ORDER BY”clause.
  • When the indexed column is applied with functions “MAX” and “MIN”.
  • When the indexed column’s data sparsity is high.

Syntax for creating INDEX:

The below mentioned is the syntax for creating the index:

CREATE [UNIQUE]INDEXindexname ON tablename(column1 [, column2])

Explanation of Solution

Recommended indexes:

  • The given query uses the attributes “V_STATE” and “V_AREACODE” in the condition criteria.
  • Equality comparison is also used in the condition criteria.
  • The indexes that are to be created are:
    • Index on the attribute “V_STATE”
    • Index on the attribute “V_AREACODE”

Creation of the SQL commands:

The index for the given query is generated with the following names “VEN_MYNDX1” and “VEN_MYNDX2”...

Still sussing out bartleby?

Check out a sample textbook solution.

See a sample solution

The Solution to Your Study Problems

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

Get Started

Chapter 11 Solutions

Database Systems: Design, Implementation, & Management
Show all chapter solutions
add
Ch. 11 - What are optimizer hints, and how are they used?Ch. 11 - What are some general guidelines for creating and...Ch. 11 - Most query optimization techniques are designed to...Ch. 11 - What recommendations would you make for managing...Ch. 11 - What does RAID stand for, and what are some...Ch. 11 - SELECT SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE,...Ch. 11 - Problem 1 and 2 are based on the following query:...Ch. 11 - Using Table 11.4 as an example, create two...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 15 and 16 are based on the following...Ch. 11 - Problems 15 and 16 are based on the following...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - SELECT SELECT P_CODE, P_DESCRIPT, P_PRICE,...Ch. 11 - Problems 2224 are based on the following query:...Ch. 11 - Problems 2224 are based on the following query:...Ch. 11 - Problems 25 and 26 are based on the following...Ch. 11 - Problems 25 and 26 are based on the following...Ch. 11 - Problems 27 and 28 are based on the following...Ch. 11 - Problems 27 and 28 are based on the following...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...

Additional Engineering Textbook Solutions

Find more solutions based on key concepts
Show solutions add
What is the radius of a 3.65-inch-diameter circle?

Precision Machining Technology (MindTap Course List)

Describe the effect of pressure on an enclosed volume of a gas.

Automotive Technology: A Systems Approach (MindTap Course List)

For the cooling of steel plates discussed in Section 18.4 (Figure 18.17) using linear interpolation, estimate t...

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

What is a mandatory access control?

Management Of Information Security

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)

List steps for using Boot Camp to install the Windows operating system on a Mac computer.

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)

Can all electrodes be used with a leading angle? Why or why not?

Welding: Principles and Applications (MindTap Course List)

What unique characteristic of zero-day exploits make them so dangerous?

Network+ Guide to Networks (MindTap Course List)