Database Management Systems - May 2013

Computer Engineering (Semester 4)

TOTAL MARKS: 80
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any three from the remaining questions.
(3) Assume data if required.
(4) Figures to the right indicate full marks. 1 (a) Galleries keep information about artists, their names (which are unique), birthplaces, age and style of art. For each piece of the artwork the artist, the year it was made, its unique title, its type of art, (e.g. painting, sculpture photograph) and its price must be stored. Pieces of artwork are also classified into groups of various kinds e.g. portraits, still life work by Picasso or works of 19th century; a given piece of work may be long to more than one group.
Each group is identified by a name (like those given) that describes the group. Galleries keep info about customer's like persons (unique name, address, total amount spent and the artist and group of all that customer like.

Draw the ER diagram for the database.(8 marks) 1 (b) For the above database:
Explain the rules to map E-R schema to relations.(6 marks) 1 (c) For the above database:
Explain Conflict Seriliazibility.(6 marks) 2 (a) Company manufactures ranges of products which are purchased by customers. The relational schema 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.
(ii) Normalise the above relation to 3NF.(12 marks) 2 (b) Explain 2PL protocol.(8 marks) 3 (a) What is recoverable schedule? Why recoverability of schedule is desirable? Explain checkpoint based recovery mechanism.(10 marks) 3 (b) What is the condition for lossless de-composition of a relation? Give Example.(5 marks) 3 (c) Explain Trigger with example.(5 marks) 4 (a) EMP (eid : Integers, ename : string, age : interger, 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.
Add Ram as an employee with eid = 101 , age = 32, salary = 75000.
(iii) Give every employee 10% rise.
(iv) Find total no of employees working in department = Computer
(v) Arrange employees in descending order of their salary.(10 marks) 4 (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 marks) 5 (a) What is a transaction? Explain ACID properties of a transaction.(10 marks) 5 (b) Explain data dictionary storage.(5 marks)

Solve any
one:-

5 (c) Give the structure of B⁺ tree.(5 marks) 5 (d) Explain static hashing.(5 marks) 6 (a) Explain UNDO and REDO operations for log based recovery. How are they used for recovery?(8 marks) 6 (b) Give one protocol that prevents deadlock(6 marks) 6 (c) Describe different methods of deadlock recovery.(6 marks)

Write short notes on (any four)

7 (a) Explain Views in SQL (5 marks) 7 (b) Keys and Referential Integrity(5 marks) 7 (c) Data Independence and its types(5 marks) 7 (d) Timestamp and ordering protocols(5 marks) 7 (e) Index definition in SQL(5 marks)