E B4E561 Pages: 4

12. a. Illustrate the use of assertions with a typical example. (3) b. Consider a relation (A,B,C,D,E,F)
with A as the only key. Assume that the dependencies (6) EF and C-DEH hold on R.

(i) Is Ris in 2NF? If not, decompose (௦ 2NF. (ii) Is Ris in 3NF? If not, decompose to
3NF.

13. In the following tables ADVISOR and TAUGHTBYare foreign keyd referring to the (9) table
PROFESSOR. ROLLNO and COURSEID in ENROLLMENT refer to tables with primary keys of
the same name.

STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR) COURSE(COURSEID,
CNAME, TAUGHTBY, CREDITS)

PROFESSOR(PROFID,PNAME, PHONE)

ENROLLMENT(ROLLNO, COURSEID, GRADE)

Write SQL expressions for the following queries:
(i) Names of courses taught by ‘Prof. Raju’.
(ii) Names of students who have not enrolled for any course taught by ‘Prof. Ganapathy’.
(iii) | For each course, name of the course and number of students enrolled for the course.

14. Assume that the relation R(P,Q,S,T,U) with FDs P -> 5, Q > 5, 5 > 1, TU— 5, SU — Pis (9)
decomposed into 5 relations: R1(P,T), R2(P,Q), R(QU), R4(S,T,U) and R5(P,U). Apply the
standard algorithm to test if the decomposition is alossless-join decomposition.

PARTE
Answer any four full questions
15. Consider the tables R (A, 8, C), T(D,E,F), S(G, 11) and U(A,D, ©, 1) where A, D and Gin (10) U are foreign

keys referring to the primary keys with the same names. Show an initialquery tree for the following
query and optimize it using the rules of heuristics:

select 8, E, ೮, H, 1 from

R, T, 5, U

where R.A = U.A

and T.D = U.D and 5.6 = U.G

and R.C = ‘TEXT’ and U.I > 20 and T.E = 25

16 Consider a file with 2,00,000 records stored in a disk with fixed length blocks of size 256 (10) bytes. Each
record is of size 50 bytes. The primary key is 4 bytes and block pointer is 6 bytes. Compute the
following, assuming that multi-level primary index is used as access path:

(i) Blocking factor for data records

(ii) Blocking factor for index records

(iii) | Number of data blocks

(iv) | Number of First level index blocks
(v) Number of levels of multi level index

17. a. Argue that two-phase locking ensures serializability. (4) b. Illustrate clustering index and

secondary index with typical, real examples. (6)

Page 3 of 4