12

13

14

15

a)

a)

b)

b)

a)

b)

02000CS208052002

PART 0
Answer any two full questions, each carries 9 marks.
Consider the schema given below.

person (driver-id, name, address)
car (reg-no, model, year, driver-id)
accident (report-number, date, location)
participated (driver-id, reg-no, report-number, damage-amount)
Write SQL queries for the following
a. Find the name of driver, who is drives the car with reg-no='AABB2000'. Find
the total number of people who were involved in car accidents in 01-01-1989.
९. Find the number of accidents in which the cars belonging to “John Smith” were
involved.
d. Update the damage amount for the car with reg-no “AABB2000"in the accident
with report number “AR2197” to $3000.
Compute the closure of the following set F of functional dependencies for relation
schema R = (A, 3, ಛೈ 1), E ).
A—BC
CD—E
3-> 12
E-A
List the candidate keys for R.
Define 3NF and BCNF. Let R (A, B, C, D, E) be a relational schema in which the
following functional dependencies are known to hold: AB ಎಲ್ಲೆ C ‏ج‎ E and 8 ‏ج‎
‎D . Identify the highest normal form
Illustrate triggers with a suitable example.
२ = (^, 8, ‏,ل‎ 1), E). We decompose it into 1२1 = (A, 3, C), R2 = (ಛೆ, ಐ, £). The set
of functional dependencies is: A + BC, CD — 2, 8 > 1), 8 க. Check whether
this decomposition is a lossless join decomposition or not.
PART E

Answer any four full questions, each carries 10 marks.
Compare primary indexing, secondary indexing and clustered indexing with
suitable diagram.

Define the structure of B+ tree

Page 3 of 4

(9)

(3)

(6)

(4)

(5)

(6)

(4)