Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Expert Solution & Answer
Chapter 11, Problem 20P
Explanation of Solution
Creating two alternative access plans with the data that are provided:
The below table shows the two alternative access plans for the data that are provided:
Given:
Total vendors: 10,000
Number of vendors in the cityTN : 113
Assumption:
Considering there are no indexes and reading each row will have I/O cost of “1”.
Table creation:
| Plan | Step | Operations | I/O operations | I/O cost | Resulting row sets | Total I/O cost |
| A | A1 |
Performs full table scan on the table VENDOR. Select rows with V_STATE= “TN” |
10,000 | 10,000 | 113 | 10,000 |
| A2 | Perform SORT operations and select rows that matches the given criteria.(V_STATE). | 113 | 113 | 113 | 10,113 | |
| B | B1 |
Performs Index scan for the given area code. (VEN_MYNDX1). Select rows based on the state... |
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
True or False for each of the following :
1- ROP in Oman is planning to open various branches in and around Oman. ROP has many online services like paying fines, renew vehicle license and more services. In this scenario ROP must ‘choose Multiple Server and Single Client topology.
2-Use the query given below on the following table.
Table Name: students
ID | Student id | Student_name1 | 100 A2 | 101 B
Query: DELETE * FROM students where Student_id=100;
State whether the query stated in the question will delete all records from the given table..
3-In html element the universal attribute class can have same names for many elements.
4-In html form, dropdown box selected attribute will not select option by default.
5- In HTML form, post method will hide data inside the request.
Q2) Consider the following table is used to store contact information: Name | Company | Adcress | Pnonel | PhoneZ | Phone3 | ZpCode Joe | ABC 123 5532 2234 |3211 12345 Jane |XYZ 456 [3421 | 14454 Chris |PDQ [789 | 2341 | 6655 | 14423 The table is considered as not normalized, explain why? And what is the INF of the table?
Problem 109
Query to display the lowest average cost of books within a subject and the highest average cost of books within a subject.
SELECT Min (AVERAGE_COST) AS "Lowest Avg Cost", Max (AVERAGE_COST) AS "Highest Avg Cost"
FROM (SELECT BOOK_SUBJECT,Round(Avg (BOOK_COST),2) AS AVERAGE_COST FROM BOOK
GROUP BY BOOK_SUBJECT);
I got an error ERROR 1248 (42000) at line 1: Every derived table must have its own alias
How can I edit this query to make work? thanks
Chapter 11 Solutions
Database Systems: Design, Implementation, & Management
Ch. 11 - Prob. 1RQCh. 11 - What index should you create? Write the required...Ch. 11 - What is the focus of most performance-tuning...Ch. 11 - What are database statistics, and why are they...Ch. 11 - How are database statistics obtained?Ch. 11 - What database statistics measurements are typical...Ch. 11 - How is the processing of SQL DDL statements (such...Ch. 11 - In simple terms, the DBMS processes a query in...Ch. 11 - If indexes are so important, why not index every...Ch. 11 - What is the difference between a rule-based...
Ch. 11 - Prob. 11RQCh. 11 - What are some general guidelines for creating and...Ch. 11 - Prob. 13RQCh. 11 - Prob. 14RQCh. 11 - Prob. 15RQCh. 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 - Prob. 6PCh. 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 - Prob. 19PCh. 11 - Prob. 20PCh. 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 - Prob. 27PCh. 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:...
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
- Problems 1721 are based on the following query: SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = 'TN' ORDER BY V_NAME; Assume that you have 10,000 different products stored in the PRODUCT table and that you are writing a web-based interface to list all products with a quantity on hand (P_QOH) that is less than or equal to the minimum quantity, P_MIN. What optimizer hint would you use to ensure that your query returns the result set to the web interface in the least time possible? Write the SQL code.arrow_forwardProvide MySQL screenshots of these queries and their execution: 1. Query: DELIMITER // CREATE PROCEDURE displayStudents( IN departmentName VARCHAR(255) ) BEGIN SELECT * FROM Students WHERE Department=departmentName; END // DELIMITER ; CALL displayStudents('SCOPE'); 2. Query: DELIMITER $$ CREATE PROCEDURE countStudents( IN location VARCHAR(25), OUT total INT ) BEGIN SELECT COUNT(*) INTO Total FROM Students WHERE Address = location; END$$ DELIMITER ; CALL countStudents('Mumbai',@total); SELECT @total; 3. Query: DELIMITER // CREATE PROCEDURE DisplayName() BEGIN SELECT NAME FROM Students END;// DELIMITER ; CALL DisplayName()arrow_forward
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning