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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) May 2013
GS-6996 [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.	Galleries keep information about artists, their names (which are unique), birthplaces, age and style of art. For each piece of artwork the artist the year it was made, its unique title, its type of art (eg. painting, sculpture, photograph) and its price eg portraits still life work by Picasso or works of 19th Century; a given piece of work may belong to more than one group. Each group is identified by a name (like those given) that describes the group Galleries keep into about customer's like persons (unique name, address, total amount spent and the artist and group of all the customers like.
	(a)	Draw the ER diagram for the database.	08
	(b)	Explain the rules to map ER to relations.	06
	(c)	Explain Conflict serializibility.	06
2.	(a)	Company manufactures ranges of products which are purchased by customers. The relational scheme for this operation is given as : Company (company-code, cname, Director #. Director-name, {product-name, cost {cost #, Customer name, address} }) Where {...} represents the repeating groups.   (i) State the definitions of 1NF, 2NF, and # NF. 06 (ii) Normalize the above relational to 3NF. 06	06
	(b)	Explain 2 PL protocol.	08
3.	(a)	What is recoverable schedule? Why recoverability of schedule is desirable? Explain check point based recovery mechanism?	10
	(b)	What is the condition for lossless de-composition of a relation? Give example.	05
	(c)	Explain triggers with example.	05
4.	(a)	EMP (eid : inlegers, ename : string, age : integar, salary : real). Works (eid : integer, did : integer, pctime : integer) DEPT (did : integer, dname : string, budget : real, managerid : integer) (i) Write SQL statement to create works relation. 02 (ii) Add Ram as an employee with id = 101, age = 32, salary =₹ 75000; 02 (iii) Give every employee 10% rise. 02 (iv) Find total no. of employees working in the department = 'Computer'. 02 (v) Arrange employees in descending order of their salary. 02
	(b)	Explain organization of records in files, in the sequential file organization, why is an overflow block used even if there is at a given point only one overflow record?	10
5.	(a)	What is transaction? Discuss acid properties of a transaction.	10
	(b)	Explain data dictionary storage.	10
	(c)	Give the structure of B+ tree.            OR Explain static hashing.	05
6.	(a)	Explain UNDO and REDO operations for log based recovery. How are they used during recovery?	08
	(b)	Give one protocol that prevents deadlock.	06
	(c)	Describe different methods of deadlock recovery.	06
7.	Write short notes on (any four) :-		20
	(a)	Views in SQL
	(b)	keys and Referential integrity
	(c)	Data independence and its types
	(d)	Timestamp and ordering protocols
	(e)	Index definition in SQL.