Due Date: 27-Jan-2020

Objective:

The objective of this assignment is to enhance the learning capabilities of the students about:

• Join Techniques
• Hash based join
• Sort-Merge join

Instructions:

Please read the following instructions carefully before submitting assignment:

You need to use MS word document to prepare and submit the assignment on VU-LMS.

It should be clear that your assignment will not get any credit if:

• The assignment is submitted after due date.
• The assignment is not in the required format (.doc or docx)
• The submitted assignment does not open or file is corrupt.
• Assignment is copied(partial or full) from any source (websites, forums, students, etc)

HyperMart is an online shopping store currently acquiring a large number of customers.

Normalized table structures for the shopping store are given below:

Order Table

M

Order details Table

Table Information

• Assume 1:12 record count ratio between Order and Order detail for online store’s database
• Assume 10,000 orders

Task

For the given relations you are required to calculate the following costs in terms of I/O operations and find out which joining technique is better for given scenario. Tasks you are required to perform are as under;

1. Cost of Sort-Merge Join

Ans:

Ratio between order and order detail table is 1:12

As 10,000 orders are there , 12×10,000=120,000 records will be in order detail table.

Cost of sort merge=M log M+N log N+(M+N)

M=10,000

N=120,000

=10,000 log (10,000)+120,000 log(120,000)+(10,000+120,000)

=10,000(4)+120,000(5.07918124605)+(10,000+120,000)

=40,000+60950.17495+130,000

=779,501.7495 

1. Cost of Hash-Join

Ans:

In matching phase cost=M+N I/Os

=10,000+120,000

=130,000

Cost of hash join=260,000+130,000

=390,000 I/Os

1. On the basis of your calculations, suggest better joining technique between sort-merge and hash join for the given scenario

Ans:

For the give scenario hash joining technique is better as its cost is about 390,000 I/Os

While the cost of merge sort is about 779,501.7495 

Best of Luck!