No. of Pages: 3

APJ ABDUL KALAM TECHNOLOGICAL UNIVERSITY

FIRST SEMESTER M.TECH DEGREE EXAMINATION, DECEMBER 2017
Branch: Computer Science & Engineering

Stream(s): Computer Science & Engineering

01 CS6103 : Topics in Database Technology

Answer any twofull questions from each part

Limit answers to the required points.

Max. Marks: 60 Duration: 3 hours

PARTA

1.9. Assume we have a Department relation with 50 records and an Employee relation
with 6000 records stored in 10 and 2000 disk blocks respectively. There exists
secondary indexes on attributes ENO of Employee and MGRSSN of
Department with number of index levels XENO = 4 and XMCRSSN = 2,
respectively. Assume, every Department record has a matching Employee
record. When the query,

DEPARTMENT 05 mcrssn=NOEMPLOYEE is to be executed with a single loop
join, show the importance of Join Selection factor by computing the number of
block accesses required in each case (choice (4) of relation in outer loop).

‎Consider the tables FACULTY (FID, FNAME, DESIGNATION, PI IONE, EMAIL,DNUMBER),‏ .حا
‎COURSE(CNO, CNAME, CREDI&), STUDEM(ROLLNO, NAME, ADDRESS, AGE) and‏
‎ENROLMENT(FID, CNO, ROLLNO, GRADE) where FID, CNO and ROLLNO in ENROLMENT‏
‎are foreign keys referring to the primary keys with the same names. Show an initial‏
‎query tree for the following query and optimize it using the rules of heuristics:‏

‎SELECT FNAME, CNAME, ROLLNO, NAME
FROM FACULTY F, COURSE C, STUDENTS, ENROLMENT E
WHERE F.FID=Eand CCNO=E.CNO and S.ROLLNO = E.ROLLNO and DESIGNATION - 'Professor' and

‎CRADE = ‘D’and CNAME = 'DBS' (4) ८. Justify the statement: The worst case performance of External Sort-
Merge of degree 2 is (2 * b) + (2 * (b * (loga(b))) (2)

‎1

‎2. a. Consider the relations (ಗಿ, B,C), r2(C, 0, E), and F), with primary keys A, ©) and F,

‎respectively. Assume that rl has 1000 tuples, r2 has 1500 tuples, and r3 has 750 tuples.
Estimate the size of rl r2 r3