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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) May 2011
1.	(a)	Draw E-R diagram for online Railway Ticket Reservation system. Convert E-R diagram into tables.	10
	(b)	Describe the overall Architecture of DBMS with the diagram.	10
2.	(a)	Explain any 4NF is a normal form more desirable than BCNF.	10
	(b)	Give the advantages of DBMS over file system.	10
3.	(a)	Explain organization of records in files, in the sequential file organization, why is an overflow block used even if there is, at the moment, only one overflow record?	10
	(b)	What is transaction? Discuss ACID properties of transaction.	10
4.	(a)	Explain the distinction between closed and opned hashing. Discuss the relative merits of each technique in database application.	10
	(b)	For given database, write SQL queries.   Employee (EID, Name, Street, City) Works (EID, CID, Salary) Manager (EID, Manager_Name) Company (CID, Company_Name, City)   (i) Modify the database so that 'KHADAK' now lives in "NEPAL"   (ii) Find all employees in the database who lives in the same cities as the company for which they work.   (iii) Give all employee of SHARAYU Steel' a 10% raise in salary.	10
5.	(a)	What is recoverable schedule? Why is recoverability of schedule desirable? Are there any circumstances under which it would be desirable to allow non-recoverable schedule? Explain your answer.	10
	(b)	Explain the types of constraints with an example.	10
6.	(a)	When a transaction is rolled back under timestamp ordering, it is assigned a new 'timestamp'. Why can it not simply keep its old timestamp?	10
	(b)	Explain in detail Log-Based recovery.	10
7.	Write short notes on (any two) :-		20
	(a)	B+ Tree
	(b)	Shadow Paging
	(c)	Group by and order by clause in SQL
	(d)	Triggers