10

12

13

14

15

16

a)

a)
b)

a)
b)
0)
ಬ

0)

1192078 Pages:4

Consider the following schema:
Suppliers(sid, sname, address)
Parts(pid, pname, colour)
Catalog (sid. pid, cost)
The key fields are underlined. Foreign key in Catalog are sid and pid referring to
Supplier and Parts, respectively. Write the relational algebra expression for the
following queries:
(i) Find the sids of suppliers who supply some red or green part.
(ii) Find the sids of suppliers who supply every part.
(iii)Find the names of suppliers who supply some red part.

PART C

Answer all questions, each carries 3 marks.

Illustrate the concept of trigger in SQL with an example.
Give any three examples to illustrate the schema modification statements in SQL.
Explain any three uses of attribute closure algorithm.
Consider the following setF of functional dependencies for relation schema
र = (A, 8, C, D, E).
= {^ —BC,CD—E, த)
Compute the canonical cover of F.

PART D

Answer any two full questions, each carries 9 marks.

What is an assertion?
Write an assertion for the bank database to ensure that the assets value for the
Perryridge branch is equal to the sum of all the amounts lent by the Perryridge branch.
The schema for branch and loan are shown below:
branch(branch_name, branch_city, assets)
loan(loan_number, branch_name, amount)
Explain the difference between BCNF and 3NF with an example.
Given below are two sets of FDs for a relation R(A,B,C,D,E).Are they equivalent?
Fl = (2൭, AB9C, ൧0൭൧൦. DPE}
F2 = {A>BC, D>AE}
Give suitable example for nested sub queries. Write the advantages of nested query?
Suppose that we decompose the schema R = (A, B, C, D, E) into
R1I(A, 8, C)
R2(A, D, E)
Test whether the given decomposition is a lossless-join decomposition, if the
following set F of functional dependencies holds in R:
F= {A >BC,D >, ए > 0೧, 8 ೨ A}

PARTE

Answer any four full questions, each carries 10 marks.

What is the main difference between a primary index and a clustering index? Give
examples.
How does multilevel indexing improve the efficiency of searching an index file?
Explain your answer.
Is it possible in general to have two primary indices on the same relation for different
search keys? Explain your answer.
Draw the structure of a B+-tree with q-1 search values. Differentiate between internal
nodes and leave nodes of B+-tree.
Construct a B+-tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)

Page 2 of 3

(9)

(3)
(3)
(3)
(3)

(2)
(7)

(4)
(5)

(4)
(5)

(3)
(4)
(3)
(5)
(5)