Questions
INFO20003_2025_SM2 Exam: Database Systems (INFO20003_2025_SM2)- Requires Respondus LockDown Browser
Matching
Query Optimisation Plan 1 Compute the following results for the below Plan 1: The result size of the child join in pages (marked as 'A' in the diagram) The cost of the child join in I/Os (marked as 'B' in the diagram). The cost of the parent join in I/Os (marked as 'C' in the diagram) Figure 3.3.1: PLAN 1. 1: The result size of the child join in PAGES (marked as 'A' in the diagram) 2: The cost of the child join in I/Os (marked as 'B' in the diagram) 3: The cost of the parent join in I/Os (marked as 'C' in the diagram)
Options
A.[Select this if none of the other options are correct]
B.8
C.63,000
D.20,000
E.10,250
F.6,300
G.2,000
H.200
I.8,250
View Explanation
Verified Answer
Please login to view
Step-by-Step Analysis
Question restatement: You are asked to compute three results for Plan 1: (1) the result size of the child join in PAGES (marked as 'A'), (2) the cost of the child join in I/Os (marked as 'B'), and (3) the cost of the parent join in I/Os (marked as 'C'). The provided answer options include a list of possible numerical values, and the correct matches are given as: 2,000 for the child join result size in pages, 10,250 for the child-join I/O cost, and 6,300 for the parent-join I/O cost. When evaluating each option, think about what each value would represent and whether it logically fits the metric being asked for.
Option analysis for each given choice:
- Option '2,000' (as the answer for the child join result size in PAGES):
This number represents a potential page-count for the output of the child join. To judge its validity, you should consider the estimated cardinality after the join and how many pages that would translate into......Login to view full explanationLog in for full answers
We've collected over 50,000 authentic exam questions and detailed explanations from around the globe. Log in now and get instant access to the answers!
Similar Questions
Query Optimisation Plan 2 Compute the following results for the below Plan 2: The result size of the selection on vendor in pages (marked as 'A' in the diagram) The result size of the child join in pages (marked as 'B' in the diagram) The cost of the selection on vendor in I/Os (note this also accounts for the index access cost as well) (marked as 'C' in the diagram) The cost of the child join in I/Os (marked as 'D' in the diagram) The cost of the parent join in I/Os (marked as 'E' in the diagram) Figure 3.3.2: PLAN 2. 1: The result size of the selection on Vendor in PAGES (marked as 'A' in the diagram) 2: The result size of the child join in PAGES (marked as 'B' in the diagram) 3: The cost of the selection on Vendor in I/Os (note this also accounts for the index access cost as well) (marked as 'C' in the diagram) 4: The cost of the child join in I/Os (marked as 'D' in the diagram) 5: The cost of the parent join in I/Os (marked as 'E' in the diagram)
Single Relation Plan C What would happen if our query changed and became: SELECT hotspotID FROM hotspot WHERE postcode = 2600 AND establishmentYear > 2020 AND establishmentYear < 2022; Assuming that the unclustered B+tree index on establishmentYear from the previous question is the only index available, would the cost of the best plan change?
Single Relation Plan B Compute the estimated cost of plan alternatives, assuming that an unclustered B+tree index on (establishmentYear) is the only index available. Suppose there are 10 index pages. Give the lowest (estimated) cost in I/Os after considering all access methods available. Round up any decimals to the nearest integer (e.g., 3.3 rounds up to 4).
Single Relation Plan A Compute the estimated result size for the query, and the reduction factor of each filter. 1: RF(establishmentYear) 2: RF(postcode) 3: Result Size (tuples)
More Practical Tools for Students Powered by AI Study Helper
Making Your Study Simpler
Join us and instantly unlock extensive past papers & exclusive solutions to get a head start on your studies!