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

December, 2006

1. (a) Consider the following relations :

Emp(EmpNo, Ename, DNo, Sal)

Dept(DeptNo, Dnarne, DMgrNo)

Proj(ProjNo, ProjName, DNum)

Works-on(EmpNo, PNo, Hrs)

Write appropriate queries in SQL and relational algebra for the following : (2x5=10)

(i) Retrieve the names of all employees in a department who work for more than 10 hrs per , week on 'Proj X' project.

(ii) Find the names and addresses of all employees who work for Research Department.

(iii) Find all the employees who do not work on any project.

(iv) Find all the employees who work on all projects of Department No. 5.

(v) For every project, list Project No, Dept No and the Department Manager's name, number and salary.

(b) Why is the concurrency control needed ? Discuss with examples. (6)

(c) What is two phase locking protocol ? How does it guarantee serializability ? (4)

(d) Explain the architecture of DBMS with the help of a diagram. (4)

(e) Access methods are measured by access and storage efficiencies. Define each term and its major objectives. Which is the most important consideration in a batch environment ? Give reasons. (6)

2. (a) What is log ? How can log be used to recover from failure ? How is log based recovery different frorn checkpoint recovery ? (8)

(b) What are the different security policies ? Discuss any two of them. (7)

3. (a) Draw an ER diagram for a bank. Each bank can have multiple branches and each branch can have multiple accounts and loans. List all the entities and relationships between them. Also list all the constraints. State all the assumptions made. (8)

(b) What is a functional dependency ? When are the two sets of functional dependencies equivalent ? How can we determine there equivalece? (7)

4. (a) Discuss the advantages and disadvantages of OODBMS in comparison with RDBMS. (8)

(b) Explain the different access strategies. State how the availability of such access aids influences query processing. (7)

5. (a) What is a timestamp ? How does the system generate timestamps ? Discuss the timestamp ordering protocol for concurrency control. (8)

(b) Discuss the term Normalization. What are the different normal forms ? Explain BCNF and 5NF with examples. (7)