1

2. a. Consider the following statistics about a relational table EN{PLOYEE: - There are (5)

12000 rows each of size 200 bytes. There exists a clustering index on SALARY attribute
with XSALARY = 3, secondary index on attribute DNO with 2, a secondary index on
the at-fribute SEX XSEX = 1. Half of employees are male. On the average 20% of
employees belong to the same departrnent. 40% employees have salary >25,000.

Fsamate the minimum execution cost of the following query assumiilg that the blocking
factor is 4.
05۸۸۲۱۷۰28000 AND SEX ٠ emale' AND DNC - 5(EMPLOYEE)

b. Argue that two-phase locking ensures serializability.(2)

c. What is the significance of the *-property in the Bell-Lapadula model? Illus&ate.
(3)

3. a. There are two relational tables BOOK (ISBN, TITLE, PRICE, PUBCODE) and (5) PUBLISHER

4.

(PUBCODE, NAME, ADDRESS) where PUBCODE in BOOK is the foreign key referring to the
PUBLISHER table. There are 6000 BOOK records each of size 50 bytes and 50 PUBLISHER records
each of size 100 bytes. Assuming that there are 6 buffers and that block size is 400 bytes, compute the
cost of (i) sorting the BOOK table and (ii) joining the two tables using nested-loop join.

b. "Transaction log for a recovery system with deferred database modification does not have to (3) store
old values of data items." Justify this statement. http:/'u-ww.ktuonline.com

c. In the following multi-level table, if the a user with classification C wants to see all the (2) attribute
values in the row with account no CA234, write any two possible modified combination of classification
attributes for the row.

ACCNO NAME BALANCE
SB123 U REENA C 101015 5
CA234 ‏تا‎ LEENA S 20001 u 8
1۸33 5 MEENA TS 10001 5

PART 11

a. There are four sites 51, 52, 53 and 54 in ೩ distributed DB system with weights 1, 2, 3 (8)
and 3 respectively. If a data item X is replicated across these sites, find the read quorum and
write quorum such that, imajority protocol is followed ii. biased protocol is followed

In each case, argue that the assigned quorum values enable the system to follow the
corresponding protocol.

b. List out any two indexing schemes for spatial data. How are they different from each (2)
other?

a. Give examples of situations, in the context of query processing, where inter- (1) operation
and intra-operation parallelism can be used.