BuyFindarrow_forward

Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482

Solutions

Chapter
Section
BuyFindarrow_forward

Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482
Chapter 11, Problem 30P
Textbook Problem
3 views

Problems 29–32 are based on the following query:

SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE)
FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE
WHERE CUS_AREACODE = '615'
GROUP BY CUS_CODE;

Assuming that you follow the recommendations you gave in Problem 29, how would you rewrite the query?

Program Plan Intro

Usage of derived attributes:

  • The derived attributes are used to minimize the computations that are established in the execution of queries and join operations.
  • Derived attributes are highly used during the execution of the aggregate queries.

Recommendation that is given to the designer in the usage of derived attributes:

The below are the recommendations that are given to the designer for the usage of derived attributes:

  • The given query utilizes the aggregate function “MAX” in order to compute the customer’s maximum invoice line number.
  • The given table gets increased at the rows rate of 15,000 per month and because of that the query can take significant amount of time in order of executing the rows that gets increases.
  • The given query is estimated by to perform a full table scan in order to compute the maximum invoice line value since it uses the aggregate function MAX as an expression (LINE_UNITS*LINE_PRICE) rather than using it as a simple table column.
  • Since the query optimizer chose to perform a full table scan it could take some amount of time to compute the desired.
  • It is recommended to use derived attribute to speed up the query processing by using the attribute “LINE_TOTAL” in the “LINE_TABLE”.
  • Thus next creating an index on “LINE_TOTAL”.
  • The query will be greatly benefited in the execution process by using the index.

Explanation of Solution

Query creation:

The recommendation given was:

  • Using a derived attribute “LINE_TOTAL” and creating the index “LINE_TOTAL”...

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 EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX...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 P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE,...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 a buffer overflow, and how is it used against a Web server?

Principles of Information Security (MindTap Course List)

What is physical independence?

Database Systems: Design, Implementation, & Management

List the two basic knurl patterns.

Precision Machining Technology (MindTap Course List)

8. How do common stock and preferred stock differ?

Cornerstones of Financial Accounting

Describe the Ohms Law.

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

What are vulnerabilities?

Management Of Information Security

What s the Hawthorne Effect? Have you ever experienced it? When and where?

Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)

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

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

____ is the process of dividing the disk into tracks and sectors.

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

What are the best ways to remove slag between filler weld passes?

Welding: Principles and Applications (MindTap Course List)