(Homework Solution): Question 1 (5 marks) Consider two relations called Invoice and Customers.

Question 1 (5 marks) Consider two relations called Invoice and Customers. Imagine that the Customers relation has 10 p SELECT* FROM Customers INNER JOIN Invoice ON Customers.Cust_ID Invoice.Cust_ID; We wish to evaluate an equijoin between Customers and Invoice, with an equality condition Customers.Cust ID Invoice.Cust_ID. There are 502 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap files. Neither relation has any indexes built on it. Consider the alternative join strategies described below and calculate the cost of each alternative. Evaluate the algorithms using the number of disk I/O’s (i.e. pages) as the cost. For each strategy, provide the formulae you use to calculate your cost estimates. a) Page-oriented nested loops join. Consider Customers as the outer relation. (1 mark) b) Block-oriented nested loops join. Consider Customers as the outer relation.(1 mark) c) Sort-Merge join. Assume that Sort-Merge Join can be done in 2 passes. (1 mark) d) Hash Join (1 mark) e) What would be the lowest possible cost to perform this query, assuming that no indexes are built on any of the two relations, and assuming that sufficient buffer space is available? What would be the minimum buffer size required to achieve this cost? Explain briefly. (1 mark)

Expert Answer

answers

Assume I/O to be 100tuples/page – C’r and 80tuples/page – I’s then

Let C be the 1,000 customers and I be the 5,000 invoices pages then Cost can be calculated using following formula:

Cost = C+C*I = 1,000+1,000*5,000=5,001,000 (considering Customers as outer relation)

b.) Block-oriented nested loops join –

Cost=C+(C/(B-2))*N

B pages of Memory buffer = 502

then Cost will be = 1,000+(1,000/(502-2))*5,000 = 11,000

c.) Sort-Merge join –

Cost = (Cost to sort Customer+ Cost to sort Invoice) = ((No.of passes for each write and for each read page*C)+(No.of passes for each write and for each read page*I) + C + I)

= 4*1,000+ 4*5,000 + 1,000 + 5,000 = 30,000

d.) Hash Join –

Cost to partition : 2C (read and write) = 2*1,000 = 2,000

Cost to partition : 2I (read and write ) = 2*5,000 = 10,000

Cost to join partition : C+I = 6,000

Total : 3*(C+I) = 3*(1,000+5,000)=18,000

e.) Minimum cost to perform the query if no indexes are built will be

C+C*C’r*(Indexes given+1) = 1,000+1,000*100(0+1) = 101,000

———————————————————————————————————————————————–

Do not forget to Thumbs up.

Thank you. Happy Learning

Top Quality and Well-Researched Papers

All ourbpapers are written from scratch. In fact, Clients who ask for paraphrasing services are highly discouraged. We have writers ready to craft any paper from scratch and deliver quality ahead of time.

Professional and Experienced Academic Writers

Our writers keeps you posted on your papers progress - providing you with paper outline/draft. You are also at liberty to communicate directly with your writer.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time, in many cases quite ahead of time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Type of paper needed:

Pages:

Academic level:

Total price:

$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.