cs614 assignment no.3 fall 2019 2020

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



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

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



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


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





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





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


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)



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




  1. Cost of Hash-Join


In matching phase cost=M+N I/Os



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


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!



Leave a Reply

Your email address will not be published.