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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) May 2009
VR-3774 [Total Marks : 60]
N.B:	(1)	Question no.1 is compulsory.
	(2)	Attempt any four questions out of remaining six questions.
	(3)	Make suitable assumptions if needed.
1.	(a)	Describe the overall architecture of DBMS with diagram.	10
	(b)	Define following terms with examples :-      (i) Entity and Entity set      (ii) Primary, candidate and superkey      (iii) Aggregation      (iv) Weak Entity set      (v) Generalization and specialization.	10
2.	(a)	Draw an E-R diagram for a university database consisting of 4 entities :-      (i) Student            (ii) Class      (ii) 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.	12
	(b)	What is a purpose of wait-die and wound-wait scheme. For the following wait for graphs, state what will happen in case of wait-die and wound-wait schemes.	08
3.	(a)	Discuss the different security and authorization mechanisms in database management system.	10
	(b)	For the given database, write SQL queries.      Employee (Eid, Name Street, City)      Works (Eid, Cid, Salary      Manager (Eid, manager_Name)      Company (Cid, Comapnay_Name, City)   (i)  Modify the database so that 'Jack' now lives in 'Newyork' 03 (ii)  Find all employees in the database who live in the same cities as the company for which they work 04 (iii) Give all employee s of 'ANZ' corporation dependencies :- 03	07
4.	(a)	Consider relation R(PQRSTU) with following dependencies :-      P → Q, ST → PR, S → U. State R is in which normal form? Decompose it no BCNF. Show step by step procedure.	10
	(b)	Explain following relational algebra operations :-      (i) Natural Join      (ii) Assignment      (iii) Generalization Projection      (iv) Set inserction.	10
5.	(a)	Explain view serializablility and conflict serializability with proper examples.	10
	(b)	What are triggers? Give an example. lllustrate the cases when triggers must not be used.	10
6.	(a)	Explain strict two phase locking protocol. Show that it ensures conflict serializability but does not ensure freedom from deadlocks.	10
	(b)	What is a transaction ? Draw state transition diagram and explain properties of transaction.	10
7.	Write short notes on :-		20
	(a)	Integrity constraints
	(b)	Time stamp ordering protocol
	(c)	Shadow paging
	(d)	Hashing technique.