E4810 Pages: 4

to the deleted STUDENT tuple are also deleted. Write SQL statements to
specify this foreign key requirement.

9 Illustrate use of assertions with an example.

10 When do you say that two sets of functional dependencies are equivalent?

Give a brief explanation.

11 What is meant by transitive dependency? Given an example.

12

a)
b)

9

13

14 a)

b)

15 a)

PART D
Answer any two full questions, each carries 9 marks.
Consider the following relations:

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

COURSE(CNO, CNAME, CREDITS, ODNO)
TEACHING(ENO, 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:

Course numbers and names of 3-credit courses offered by ‘CS’ department.
Names of faculty members teaching maximum3 courses.

Names of departments along with number of courses offered by each of
them, in the increasing order of number of courses; exclude departments
which do not offer any course.

Given a relation R(A1,A2,A3,A4,A5) with functional dependencies
Al—A2A4 and A4—A5, check if the decomposition R1(A1,A2,A3),
R2(A1,A4), R3(A2,A4,A5)is lossless.

For the relations listed below, write SQL statements for the updates that
follow. (Assume suitable domains for attributes.)

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

Update the year of the album with name ‘SUHANA RATH’ to 2018.

Delete the album ‘YADON KI BAARISH’ along with all the songs in it.

Briefly discuss 3NF and BCNF with suitable real examples.

PARTE
Answer any four full questions, each carries 10 marks.
Distinguish between denseindex and sparseindex and give examples for each.

Page 3 of 4