b. Consider the following statistics about a relational table, STUDENT. There are
20000 rows, each of size 200 bytes. There exists a clustering index on GRADE
attribute with XCRADE = 3, secondary index on attribute DNO with ,XDNO = 2,
and a secondary index on SEX, XSEX = 1. Half the students are males. There are
only five departments and student distribution to the departments is almost
equal. 50% of students are having grade > 5. Estimate the minimum cost of the
following query assuming blocking factor is 4.

GRADE 5 and SEX emale' and DNC) - 5 (STUDENT) (5)

c. Assume Tl, 1 2, 13 are three transactions having its time stamps 5, 10 and 20. If TI
requests an item held by 72, what action would be taken on TI and 12 if the
deadlock prevention scheme is i ) wait-die ii) wound-wait

(3)

a. Consider the following instances of a multi-level schema, R

Name அ சிறு. மூவர்‌ 76

Mahesh U 4000 C Fair 5 5
Gaurav 0 8000 5 Good C 5

i) How a user with security classification 5 would see the relation?

ii)How it would be, if security classification of the userisC? (2)
0. List out the different architectural models for parallel systems (3)

6. Suppose we have arelation with record and cach records requires 10 bytes. Let the disk-
block size be 4,096 bytes. Let the minimum number of buffers (ng)to be provided is
cubic root of b, where b is number of blocks required to hold the relation.

Assume we would sort the records by Sort-Merge algorithm

1. How many sorted sub-files will there be after the initial pass of the sort?

|| How many passes (including the initial pass just considered) are required
to sort this file? http:/"mxww.ktuonline.com iii. Find the total number of block
transfers required for the sorting (5)

PART B

a. How hash partitioning is different from range partitioning? What factors (3) could result
ina skew when relation is partitioned by i) Hash partitioning ii)
Range partitioning

0. Illustrate and explain asymmetric fragment-and-replicate join and symmetric (4)
fragment-and-replicate join.

2
c, Will functional dependencies be preserved if a relation is temporal relation by
adding a time attribute? How is this problem handled in a temporal database?