Full Marks: 60
Pass Marks: 24
Time: 3 hours

Candidates are required to give their answers in their own words as far as practicable. The figures in the margin indicate full marks.

Attempt all the questions. (6x10=60)

1. Answer the following questions in short: (5x2=10)

   (a) Specialization

   (b) Hierarchical data model

   (c) Domain constraint

   (d) Non trivial functional dependency

   (e) Checkpoint

2. (a) What is data definition language? How is it different from data manipulation language? (2+3=5)

   (b) Construct an E-R diagram for online book store where customers buy books online. (5)

3. Consider the following database, where primary keys are bold

   student (SID, SName, semester)

   studies (SID, CID)

   course (CID, CName, CCode)

   Construct the following relational algebra and SQL queries for this database. (10)

   (a) Find the names of all students in third semester.

   (b) Find the name of all courses studied by Ram.

   (c) Find the total number of students who study DBMS.

4. (a) Discuss loss-less decomposition and dependency preservation property of normalization.. (2.5+2.5=5)

   (b) Discuss 3NF with example. How is it different from BCNF? (3+2=5)

5. (a) Discuss timestamp based concurrency control technique. (4)

   (b) What is deadlock? Discuss deadlock prevention, avoidence, and detection and recovery techniques. (2+4=6)

6. (a) Why do we need recovery? Discuss different types of failures. (2+3=5)

   (b) What is log-based recovery? Discuss the recovery technique for the loss of non-volatile storage. (2+3=5)