Database Management Systems - May 2014
Information Technology (Semester 3)
TOTAL MARKS: 80
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any three from the remaining questions.
(3) Assume data if required.
(4) Figures to the right indicate full marks. 1 (a) Define the following terms :-
(ii) Primary Key
(iv) Strong entity set
(v) Lock Point
(10 marks) 1 (b) Consider the following relation :-
Given the state which of the following dependancies may hold in the above relation ? If the dependancy cannot hold explain why by specifying the tuples that cause the violation :-
(i) A → B
(ii) B → C
(iii) C → B
(iv) B → A
(v) C → A
(10 marks) 2 (a) Explain different data models with its advantages and disadvantages.(10 marks) 2 (b) Explain Generalization, Specialization and Aggregation with the help of an example.(10 marks) 3 (a) Construct on E-R diagram for a car-insurance company that has a set of customers each of whom owns one or more cars. Each car has associated with it zero to any number of recorded accidents.(10 marks) 3 (b) Define Deadlock Detection and Recovery.(10 marks) 4 (a) Consider the following relations for a book club :-
Members (Members-Id, Name, Designation, Age)
Books (Book-Id, Booktitle, BookAuthor, Bookpublisher, Bookprice)
Reserves (Member-Id, Book-Id, Date)
Write SQL queries for following statements :-
(i) Find the names of members who are professor older than 50 years.
(ii) List the titles of books reserved by professors.
(iii) Find Ids of members who have reserved books that cost more that ? 500.
(iv) Find the authors and titles of book reserved on 20-09-2012.(10 marks) 4 (b) What do you mean by serializability schedule? How would you test whether given schedule S is conflict serializable.(10 marks) 5 (a) Consider a dependency diagram of relation R and normalize it up to third normal form. :
(10 marks) 5 (b) Explain shadow paging method.(10 marks) 6 (a) Draw the Query tree for the following relational algebra expression :-
ΠCustomer-name(σ branch-city="Brooklyn" ∧ balance >1000 ((branch ⋈ (account ⋈ depositor)))
(10 marks) 6 (b) Explain the following relational algebra operations with proper examples :-
(i) Natural join
(iv) Set-Intersection operation