CS614 Assignment 3 Solution 3 Fall 2021 - 2022
Assignment Question 1:
As we all know, cluster index determines the sort order of the base table. Consider the following table and create two possible cluster indexes and also write one advantage of this technique.
EMPLOYEE:
|
Emp_ID |
Emp_Name |
Salary |
Phone_No |
Designation |
|
1010 |
Talha Junaid |
15000 |
0400-4661548 |
Clerk |
|
1015 |
Zubair Mushtaq |
20000 |
0500-3224578 |
Computer Operator |
|
1025 |
Awais Ahmad |
25000 |
0600-9441325 |
Superintendent |
|
1045 |
Imran Akram |
20000 |
0700-3112314 |
Clerk |
|
1160 |
Ayesha khalid |
15000 |
0800-4558217 |
Computer Operator |
|
1195 |
Imrana Latif |
15000 |
0900-2597861 |
Clerk |
|
1199 |
Shoaib Hanif |
20000 |
0300-1234567 |
Computer Operator |
Assignment Solution 1:
Cluster Index on
Designation
|
Emp_ID |
Emp_Name |
Salary |
Phone_No |
Designation |
|
1010 |
Talha Junaid |
15000 |
0400-4661548 |
Clerk |
|
1045 |
Imran Akram |
20000 |
0700-3112314 |
Clerk |
|
1195 |
Imrana Latif |
15000 |
0900-2597861 |
Clerk |
|
1015 |
Zubair Mushtaq |
20000 |
0500-3224578 |
Computer Operator |
|
1160 |
Ayesha khalid |
15000 |
0800-4558217 |
Computer Operator |
|
1199 |
Shoaib Hanif |
20000 |
0300-1234567 |
Computer Operator |
|
1025 |
Awais Ahmad |
25000 |
0600-9441325 |
Superintendent |
Cluster Index on Salary
|
Emp_ID |
Emp_Name |
Salary |
Phone_No |
Designation |
|
1010 |
Talha Junaid |
15000 |
0400-4661548 |
Clerk |
|
1160 |
Ayesha khalid |
15000 |
0800-4558217 |
Computer Operator |
|
1195 |
Imrana Latif |
15000 |
0900-2597861 |
Clerk |
|
1015 |
Zubair Mushtaq |
20000 |
0500-3224578 |
Computer Operator |
|
1045 |
Imran Akram |
20000 |
0700-3112314 |
Clerk |
|
1199 |
Shoaib Hanif |
20000 |
0300-1234567 |
Computer Operator |
|
1025 |
Awais Ahmad |
25000 |
0600-9441325 |
Superintendent |
Assignment Question 2:
Suppose you are working on a pipeline
architecture. A complex query that processes 30,000 records in a table is being
executed by 15 processors.
Now your task is to calculate the speed-up
of this pipeline architecture.
Hint
1: Processing a single record is considered
as a task and each record takes 1 millisecond (ms) to process.
Hint
2: Number of processors is considered as the
number of stages of pipeline.
Assignment Solution 2:
Ts = 1x30000 =
30000
Tp = T + (N-1)
x T/M
Tp = 1 + (30000 - 1 x 1/5
Tp
= 1+(29999)x0.66
Tp = 1+1999.9
Tp
= 2000.9
Speed
- Up = Ts/Tp
Speed
- Up = 30000/2000.9
Speed
- Up = 15