CS614 Data Warehousing Assignment 3 Solution Fall 2013

Assignment comprises of 20 Marks. Note that no assignment will be accepted after due date via email in any case (whether it is the case of load shedding or emergency electric failure or internet malfunctioning etc.). Hence, refrain from uploading assignment in the last hour of the deadline, and try to upload Solutions at least 02 days before the deadline to avoid inconvenience later on.

For any query please contact: CS614@vu.edu.pk

Table: Developer

DeveloperID DeveloperName DeveloperEmail DeveloperJoiningDate DeveloperCurrentSalary

 

01 Ali 01/01/2011 60000
02 Waseem 13/01/2011 55000
03 Waqas 20/04/2011 51000
04 Rashid 01/03/2011 58000
05 Qamar 01/04/2011 59000
06 Altaf 01/02/2011 53000
07 Akram 28/03/2011 56000
08 Wasif 07/02/2011 54000
09 Basit 25/08/2011 42000
10 Amir 24/07/2011 44000
11 Latif 01/08/2011 47000
12 Raza 01/07/2011 43000
13 Kashif 07/06/2011 46000
14 Noman 01/05/2011 49000
15 Naveed 01/06/2011 48000
16 Atif 11/05/2011 45000
17 Raheel 01/09/2011 30000
18 Sohail 02/09/2011 29000
19 Ibrar 01/10/2011 21000
20 Jawad 04/11/2011 26000
21 Bilal 10/10/2011 25000
22 Danish 01/11/2011 22000
23 Jameel 26/12/2011 28000
24 Khuram 01/12/2011 23000

 

Table: ProjectBacklog

BackLogID CreatedBy DateCreated ProjectID

 

BLG001 Ali 24/03/2012 INV785
BLG113 Asim 07/04/2013 MIS341
BLG124 Waseem 01/02/2012 DSS478
BLG111 Atif 05/07/2013 MIS123
BLG125 Aslam 04/12/2013 WWW110
BLG144 Rashid 25/09/2012 INV541

 

Table: Story

StoryID StoryName StoryDescription StoryStatus BackLogID

 

SRCH98 Search_Inv User should be able to search any inventory item IP BLG001
LGN12 Login User should be able to login IP BLG124
DRW17 Draw_SH User should be able to physically draw shape C BLG113
LGOUT145 LogOut User should be able to logout IP BLG001
DISPM76 Disp_Menu A popup menu should be displayed on right click C BLG111
SESSM23 Session_Maintain Session should be maintained properly IP BLG124
UNDO25 Undo Undo the latest task D BLG125
REDO45 Redo Redo undone task IP BLG144

 

Question No. 1

Create and pictorially (graphically) show the dense index on DeveloperID column in Developer table.

Question No. 2

Create and pictorially (graphically) show the two-level sparse index on DeveloperJoiningDate column in Developer table

Question No. 3

Consider the following query:

“Select ProjectBacklog.BackLogID, ProjectBacklog.DateCreated, Story.StoryID, Story.StoryName from ProjectBacklog, Story where ProjectBacklog.DateCreated < ‘31/12/2012’ and Story.StoryStatus = ‘IP’ and ProjectBacklog.BackLogID = Story.BackLogID”

Suppose this query is executed using Naive Nested-Loop join (i.e. there is no index created on both ProjectBacklog and Story tables). Mention that which table should be the Outer table to get minimum I/O by manually calculating the cost in both cases i.e. when “ProjectBacklog” is outer table and when “Story” is outer table.

Note: You also need to mention the calculations in your solutions.

DOWNLOAD SOLUTION HERE
loading...