cs614 assignment no.3 fall 2019 2020

cs614 assignment no.3 fall 2019 2020

 

Assignment No. 03
Semester: Fall 2019
CS614: Data Warehousing
Total Marks: 15

 

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)

 

Question

 

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

 

Order_ID Order_Date
100 2-Feb-2019
201 2-Feb-2019
300 4-Feb-2019
1

….

 

 

M

Order details Table

 

Detail_ID Order_ID Product_ID Product_Quantity Sale_Amount
100 100 2 1 100
101 100 3 2 200
200 201 5 1 50
201 201 7 1 90
300 300 15 1 600
301 300 56 1 800
302 300 57 1 850
…. ….

 

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!

 

 

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: