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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) May 2010
AN-3439 [Total Marks : 60]
N.B:	(1)	Question no.1 is compulsory.
	(2)	Solve any four questions from remaining.
	(3)	Make suitable assumptions if needed.
1.	(a)	Draw an E-R diagram and reduce it to relational database model for a university database for scheduling of classrooms for final exams. This database could be modelled using entities as exam (course_name, section_number, room_number, time); course (name, department, C_number), room (r_number, capacity, building). Entity section is dependent on course.	10
	(b)	What is recoverable schedule? Why recoverability of schedule is desirable? Explain recovery with concurrent transaction?	10
2.	(a)	Explain view serializability and conflict seralizability with proper example?	10
	(b)	Explain following terms with suitable example :- (i) primary Key     (ii) Candidate Key     (iii) Foreign Key     (iv) Super Key.	10
3.	(a)	For the following given database, write SQL queries :-      Person (driver_id #, Name, address)      car (license, model, year)      accident (report_no, date, location)      owns (driver_id #, license )      Participated (driver_id, Car, report_number, damage_amount)   (i) Find the total number of people who owned cars that were involved in accident in 1995. 04 (ii) Find the number of accidents in which the cars belonging to "Sunil K." were involved. 03 (iii) Update the damage amount for car with license number "Mum2022" in the accident with report number "AR2197" to Rs. 5000. 03
	(b)	Explain following relational algebra with suitable example :-      (i) Natural Join      (ii) Assignment      (iii) Set intersection      (iv) Generalization Projection.	10
4.	(a)	What is transaction? Discuss ACID properties of transcation?	10
	(b)	Define normalization? Explain 1NF, 2NF, 3NF, and BCNF?	10
5.	(a)	What do you mean by deadlock? What are various techniques for deadlock prevention and detection?	10
	(b)	Explain various types of constraints with an example?	10
6.	(a)	Explain insertion of entry in B+ tree.	10
	(b)	Explain various functions of a database administrator? List five responsibilities of a DBMS?	10
7.	Write short notes on the following :-		20
	(a)	Generalization and Specialization
	(b)	Buffer Management
	(c)	Security and Authorization in DBMS
	(d)	Hashing.