Test Papers / Previous Question Papers of IGNOU CS15 Relational Database Management System December 2001

MCA (III Yr)
Term-End Examination

December, 2001

CS15 : Relational Database Management System

Time: 3 hours
Maximum Marks: 75

1.
(a) A university has many academic unit named schools. Each school is administered by a Head of School. The school has administrative and teaching staff. A school offers many courses. A course consists of many subjects A subject is taught to students who have registered for a subject in a class room by a teacher. Draw the necessary E-R diagram for the university specifying aggregation, generalization or specialization hierarchy if any. Create 3 NF table from your design.

(b) What is statically embedded SQL? How is it different to dynamically embedded SQL? What are the applications of embedded SQL?

(c) “If a relation is broken into BCNF, it will be loss less and Dependency Preserving” prove or disprove the above statement with the help of an example. Compare BCNF with Fourth Normal From.

(d) Assume that the Railway reservation system is implemented using an RDBMS. What are the concurrency control measure one has to take, in order to avoid concurrency related problems in the above system? How can the deadlock be avoided in this system?

(e) Suppose you are asked to design a university database system, what are the security measures that may be proposed by you must define various security levels for physical security, database access and external scheme.)

2.
(a) A relation R (A, B, C, D, E, F) have the following set of dependency:
A ® CD; B ® C; F® DE; F® A
Is the decomposition of R. In R1 (A, B, C,) R2 (A, F, D) and R3 (E, F) dependency reversing and losses decomposition?

(b)
What is meant by schedule in the context of concurrent execution of transaction in a RDBMS? What is serialisable schedule? How can it be found whether a given schedule is serialisable or not.

(c) Compare and contrast the features of log based recovery mechanism versus check pointing based recovery. Suggest applications where you will prefer log based recovery scheme over check pointing. Give an example of check pointing based recovery scheme.

3.
(a) What are the steps one must take with its database management system, in order to ensure disaster recovery? Define the process of recovery in case of disaster.

(b) Describe the terms multi-valued dependency and join dependency with the help of an example each.

(c) What is the purpose of Audit Trails? How can they be used in a Banking Database System? Describe in the context of Banking System of India.

4.
(a) Assume the following relations:

R1 (a, b, c) with FDs a®b and a®c.
The size of relation = 1000 tuples
Records per block of secondary storage = 10
Storage sequence in the order of attribute ‘a’.


R2 (c, d) with FD c®d.
The size of relation 2000 tuples
Records per block 20
Storage in sequence of attribute ‘C’

What will be the size of natural join of R1 and R2? How many block transfer may be needed to compute the join. You may assume that total 5 buffers are available for calculating the join. Make available assumptions, if any.

(b) What is two phase locking? Describe with help of an example. Will two phase locking result in serialisable schedule? Will two phase locking result in deadlock? Justify your answer with the help of an example.

(c) What is shadow paging scheme? Where is it used?

(d) Can views be used for database updation? Justify your answer.

5.
(a) What is query optimization? Describe with the help of an example.

(b) What is the purpose of Intention Mode locking scheme? Describe various locks used in Intention locking scheme.

(c) What is the multi-version technique of concurrency control? Describe with the help of an example. Will this scheme result in rollback and/or deadlock? Justify your answer.

6. Describe the following terms in the context of Relational DBMS and/or Distributed DBMS. Give example wherever needed:

(a) Distributed catalog

(b) Semijoin operation

(c) Database triggers and their use

(d) Two phase commit protocol

(e) Client – server architecture of DBMS.