Database Management Systems - May 2015

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) Draw E-R diagram for online Ticket Railway Reservation System. Convert E-R diagram into tables.(10 marks) 1 (b) Explain following Relational algebra operations with examples.
i) Set difference
ii) Generalized Projection
iii) Natual join
iv) Rename(10 marks) 2 (a) What is recoverable schedule? Why recoverability of schedule is desirable? Explain recovery with concurrent transaction.(10 marks) 2 (b) Explain following terms with suitable example
i) Primary key
ii) Candidate key
iii) Foreign key
iv) Super key(10 marks) 3 (a) What is transaction? Discuss ACID properties of transaction?(10 marks) 3 (b) Define Normalization? Explain 1NF, 2NF, 3NF and BCNF.(10 marks) 4 (a) For the following given database write SQL queries:
Person (driver-id#, name, address)
Car (license, model, year)
Accident (reporcno, date, location)
Owns (driver_id#, license)
Participated (drivercid, car, report_number, damage_amount)
i) Find the total number of people who owned car that were involved in accident 2004.
Find the number of accident in which the cars belonging to HT" were involved
iii) Update the damage amount for car with license number "Mum2011" in the accident with report number "AR120" to Rs. 4000."(10 marks) 4 (b) Describe overall architecture of DBMS with diagram.(10 marks) 5 (a) Explain various types of constraints with an example.(10 marks) 5 (b) Explain sort-merge join algorithm in query processing.(10 marks)

Write short notes on any four:

6 (a) Generalization and Aggregation.(5 marks) 6 (b) Total Participation and Partial Participation.(5 marks) 6 (c) Division Operator.(5 marks) 6 (d) Shadow page recovery(5 marks) 6 (e) Cost Based query optimization.(5 marks)