QuestionQuestion

Consider a business case application in which consultants represent businesses on cases. For this purpose we have the database schema that consists of the following three relation schemata:

– Business={bid, bname, bopeningdate} with key {bid}
– Consultant={cno, cname, cconsultancy} with key {cno}
– Case={bid, cno, date, type} with key {bid,cno,date}

Assume 100 tuples fit on each page, also for intermediate tables.
Assume we have 102 buffer pages.
Assume Business has 50,000 tuples.
Assume Consultant has 10,000 tuples.
Assume Case has 100,000 tuples.
Assume that businesses and consultants are uniformly distributed across the cases.
Assume 10% of the cases have a “merger” type.
Assume Business has a clustered index on bid, Consultant has a clustered index on cno and Case has a clustered index on [bid, cno].

Consider the following query:

select b.bname, co.cname
from consultant co, business b, case c
where c.type= “merger” and b.bid=c.bid and co.cno=c.cno

State any assumptions that you make when calculating the cost of the query evaluation.

Questions

1. Draw an expression tree for the query above, where the joins are carried out before the selection and projection.

2. What is the cost of the query evaluation where
- the join between Case and Client is executed first and
- the join between Case and Client is executed using a block nested loop join and
- the subsequent join is executed using an index nested loop join and
- the selection and projection are executed on the fly?

3. What is the cost of the query evaluation where
- the selection is carried out on Case before the first join and
- the join between Case and Client is the first join and
- the join between Case and Client is executed using a block nested loop join and
- the subsequent join is executed using an index nested loop join and
- the projection is executed on the fly?

4. What is the cost of the best query evaluation plan you can find for this query?
Explain your answer.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

1. Draw an expression tree for the query above, where the joins are carried out before the selection and projection.

2. What is the cost of the query evaluation where
- the join between Case and Client is executed first
+ Client is stored in 500 pages
+ Case is stored in 1000 pages

- the join between Case and Client is executed using a block nested loop join
+ We have enough buffers to hold an inmemory hash table for 100 pages of Case. "we have 102 buffer pages"
+ We have to scan Case, at cost of 1000 I/Os
+ For each lOa-page block of Case, we have to scan Client.
+ Therefore, we perform 1000/100 = 10 scans of Client, each costing 500 l/Os.
+ The total cost is 1000 + 10 . 500 = 6000 l/Os....

By purchasing this solution you'll be able to access the following files:
Solution.docx.

$98.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats