Database Management Systems - Jun 2012
Computer Science Engg. (Semester 5)
TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1 (a) Discuss the main characteristics of the database approach. How does it differ from traditional file systems?(10 marks) 1 (b) Explain the components modules of DBMS and their interaction, with the help of a diagram.(10 marks) 2 (a) With a diagram describe the three schema architecture of database systems.(4 marks) 2 (b) Discuss with examples, different types of attributes. (6 marks) 2 (c) Design an ER diagram for keeping track of information about a hospital database taking into account atleast four entities.(10 marks) 3 (a) Consider the following two tables T1 and T2. Show the result of the following operations. (Assume T1 and T2 are union compatible).
(ii) T1 ?T1?P=T2?AT2
(iii) T1?T1?P=T2? A AND T1?R=T2?C T2
(iv) T1 U T2
(v) T1 ?T1?Q=T2?BT2(10 marks) 3 (b) Give the ER to relational algorithm. Dicuss each step, with an example.(10 marks) 4 (a) Consider the following schema:
SAILORS (sid, sname, rating, age)
BOATS (bid, bname, color)
RESERVES (sid, bid, day)
Write the queries in relational algebra to:
(i) Find the names of sailors who have reserved boat number '103'
(ii) FInd the names of sailors who have reserved a 'red' and a 'green' boat
(iii) FInd the names of sailors who have reserved at least one boat.
(iv) Find the names of sailors with age over 20 years, who have reserved a red boat.(12 marks) 4 (b) Explain IN and EXISTS operators of SQL with suitable examples.(8 marks) 5 (a) How is a 'view' created and dropped? What are the problems associated with updating of views?(10 marks) 5 (b) What is embedded SQL? With an example, illustrate how would you connect to a database, fetch records and display. Also explain the concept of stored procedure, in brief.(10 marks) 6 (a) What is functional dependency? Write an algorithm to find the minimal cover for a set of functional depedencies.(10 marks) 6 (b) Why normalization is required? Explain the first, second and third normal forms with an example.(10 marks) 7 (a) Explain multivalued dependency and fourth normal form, with an example.(10 marks) 7 (b) What are ACID properties? Explain.(6 marks) 7 (c) Write and explain two phase locking protocol.(4 marks) 8 (a) What is write-ahead logging? What is forced to disk at the time a transaction commits?(6 marks) 8 (b) Write and explain time stamp based ordering algorithm.(8 marks) 8 (c) Write a note on check pointing.(6 marks)