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.

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

   (a) Data abstraction

   (b) Network data model

   (c) Trigger

   (d) Trivial functional dependency

   (e) Serializable schedule

2. (a) Who is database administrator? What are the main functions of database administrator? (2+3=5)

   (b) Construct an E-R diagram for online course registration where students registers courses online. (5)

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

   teacher (TID, TName, Qualification)

   teaches (TID, CID)

   course (CID, CName, CCode)

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

   (a) Find the names of all teachers who have PhD qualification.

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

   (c) Find the total number of courses taught by Rem Prasad.

4. (a) Discuss referential integrity with example. (5)

   (b) What is functional dependency? Why do we need inference rules? (2.5+2.5=5)

5. What are the benefits of using normalization? Discuss 1NF, 2NF, and 3NF with suitable example. (2.5+7.5=10)

6. (a) Why do we need concurrency control? Discuss two phase locking protocol. (2+3=5)

   (b) Why do we need database recovery? Discuss shadow paging technique for database recovery. (2+3=5)