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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) December 2010
GT-6474 [Total Marks : 100]
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)	Draw the E-R diagram for banking enterprise (State assumptions clearly). Convert E-R diagram into tables.	10
	(b)	What is transaction? Discuss state transition diagram and properties of transaction .	10
2.	(a)	Given the following relational schema. Division (div #, div-name, director) Department (dept #, dept-name, location, div #) Employee (emp#, emp-name, salary, address, dept#) State the following queries in SQL :   (i) Get the employee name, dept-name and division name for all employees whose salary is above 20,000/- 03 (ii) List the name of all employees who work in "Marketing" division. 03 (iii) List the dept-name and employee names in that dept, for all department whose location is "Mumbai" 04
	(b)	Explain following relational algebra operators with suitable example :-      (i) Cartesian product      (ii) Outer join      (iii) Generalized Projection      (iv) Set difference      (v) Rename.	10
3.	(a)	Give the advantages of DBMS over fileio system.	10
	(b)	(i) What is the condition for a lossless decomposition of a relation ? Give example. 05 (ii) Explain the terms total participation and partial participation with example. 05	10
4.	(a)	Define serializability. Explain conflict and view serializability.	10
	(b)	What do you understand by deadlocks in database system? Explain how it is prevented.	10
5.	(a)	Companies manufacture ranges of products which are purchased by customers. The relational scheme for this operation is given as :- COMPANY (Company-code, Company-name, Director#, Director-name, {product-name, cost, {cust#, customer-name, address}}) where { } represents a repairing groups.   (i) State the definitions of first, second and third normal forms. (ii) Normalize the above relation to third normal form.	12
	(b)	Explain DDI, DML, TCL, DCL, with example.	08
6.	(a)	Explain 2 phase locking protocol.	10
	(b)	Explain following with the example.     (i) Simple and composite attributes   (ii) Aggregation   (iii) Ternery Relationship   (iv) Weak entity set.	10
7.	Write short notes on (any four) :-		20
	(a)	Hashing
	(b)	B+ tree
	(c)	Triggers
	(d)	Views
	(e)	Shadow Paging.