Database Management Systems - May 2012

Computer Engineering (Semester 4)

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) Describe the different database users.(5 marks) 1 (b) Define Normalization. Explain 1NF, 2NF with suitable examples.(5 marks) 1 (c) Define Entity Integrity and Referential Integrity with examples(5 marks) 1 (d) What is a schedule? What is a serializable schedule?(5 marks) 2 (a) Give the advantages of DBMS over File System.(10 marks) 2 (b) Explain in detail log based recovery.(10 marks) 3 (a) Explain view serializability and conflict serializability with proper examples.(10 marks) 3 (b) What is a transaction? Explain ACID properties of a transaction.(10 marks) 4 (a) Draw an E-R diagram for a university database consisting of 4 entities:-
(i) Student (ii) Class (iii) Department (iv) Faculty
and convert it to tables
* A student has a unique id, the student can enrol for multiple classes and has at most one major
* Faculty must belong to department and faculty can take multiple classes.
* Every student will get a grade for the class he/she has enrolled.(10 marks) 4 (b) What do you mean by deadlock? What are different techniques for deadlock detection and prevention?(10 marks) 5 (a) Explain insertion of an entry in a B⁺ Tree.(10 marks) 5 (b) Describe the overall architechture of a DBMS with suitable diagram.(10 marks) 6 (a) For the following given database, write SQL Queries:
person (<u>driver_id#</u>, name, address)
car (<u>license</u>, model, year)
accident (<u>reportCno</u>, date, location)
owns (<u>driver_id#</u>, license)
participated (driver_id, car, report_number, damage_amount)
(i) (Find the total number of people who owned cars that were involved in an accident in 2007.
(ii) Find the number of accidents in which the cars belonging to Ajay were involved.
(iii) Find the number of accidents that were reported in Mumbai region in the year 2004.(10 marks) 6 (b) Explain recovery using shadow paging.(10 marks) 7 (a) Explain following Relational algebra operations with suitable example
i. Natural Join
ii. Rename
iii. Set Difference
iv. Generalized projection
(10 marks)

Write short notes on any two:-

7 (b) (1) Constraints of Generalisation and Specialisation(5 marks) 7 (b) (2) Buffer Management(5 marks) 7 (b) (3) Security and Authorization in DBMS(5 marks) 7 (b) (4) Hashing(5 marks)