$2088 Pages: 4

PART D
Answer any two full questions, each carries 9 marks.

12 Consider the following relations:

13

14

15

16

FACULTY(FNO, NAME, GENDER, AGE, SALARY, DNUM)
DEPARTMENT(DNO, DNAME, DPHONE)

COURSE(CNO, CNAME, CREDITS, ODNO)
TEACHING(EFNO, CNO, SEMESTER)

DNUM is a foreign key that identifies the department to which a faculty belongs.
ODNO is a foreign key identifying the department that offers a course.Write SQL
expressions for the following queries:

(a) Names and department names of faculty members. (b) Names of faculty members
not offering any course. (c) Names of departments offering more than three courses, in
alphabetic order.

Given a relation R(A,B,C,D,E,F,G, 11) with keys BD and C and _ functional
dependencies 1-20, EF and HC, decompose the R into the highest normal form
possible.

a)

b)

a)
b)

For the relations listed below, write SQL statements to create the database schema.
Assume suitable data types.

ALBUMS(ALBUM#, ALBUM-NAME, PRODUCED-BY, YEAR)
SONGS(SONG#, SONG-START, DURATION, ALBUM#)

SUNGBY(ARITISTNAME, SONG#)

Why the following is table, TRAININFO, not in INF? How can we make it 1 NF?

Trains

Route No Rout Name Distance

Name
AP Express

TVC-DELHI

Train No

MAS-TVC 179

PART E

Answer any four full questions, each carries 10 marks.
Define the following: (i) physical record (ii) logical record (iii) blocking factor.
There are 12000 records in a data file. Each record in the file is of 75 bytes.
Compute the number of block accesses if (i) Single level secondary index is
available on a field of size 15 bytes. (ii) Multilevel index is available on the same
field.
Assume that the block size is 394 bytes, that un-spanned organization is used and
that block and record pointers are 5 and 7 bytes, respectively.

How is clustering index different from primary index?
Illustrate structure of B-Tree and B+-Tree and explain how they are different.
Page 3 of 4

(9)

(9)

(5)

(4)

(3)
(7)

(3)
(5)