Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) May 2012

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) May 2012
GN-5537 [Total Marks : 100]
N.B:	(1)	Question no.1 is compulsory.
	(2)	Attempt any four questions out of ruminating six questions.
	(3)	Make suitable assumptions if needed.
1.	(a)	Describe the different database users.	05
	(b)	Define Normalization. Explain 1NF, 2NF with suitable examples.	05
	(c)	Define entity integrity and Referential integrity with examples.	05
	(d)	What is schedule? What is a serializable schedule?	05
2.	(a)	Give the advantages of DBMS over File System.	10
	(b)	Explain in detail log based recovery.	10
3.	(a)	Explain view serializability and Conflict serializability with proper examples.	10
	(b)	What is transaction? Discuss ACID properties of a transaction.	10
4.	(a)	Draw and 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 enroll 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
	(b)	What do you mean by deadlock? What are the different techniques for deadlock detection and prevention?	10
5.	(a)	Explain insertion of an entry in a B+ tree	10
	(b)	Describe the overall architecture of a DBMS with suitable diagram.	10
6.	(a)	Fro the following given database, write SQL queries :- person (driver_id, name, address) Car (license, model, year) accident (reportCno, date, location) owns (driver_id#, license) participated (drivecid, 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 "KHADAK"  were involved. (iii) Find the number of accidents that were reported in Mumbai region in the year 2004.	10
	(b)	Explain recovery using Shadow paging.	10
7.	(a)	Explain following relational algebra operations with suitable example :- (i)  Natural Join (ii)  Rename (iii)  Set Difference (iv)  Generalized Projection.	10
	(b)	Write a short note on any :- (i) Constraints of Generalization and Specilaization (ii) Buffer Managament (iii) Security and Authorization in DBMS (iv) Hashing