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.
