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

MCA (III Yr)
Term-End Examination

CS15 (S): Relational Database Management System

Time: 3 hours
Maximum Marks: 75

1. (a) Consider a relational database consisting of the following relations : (10)
SUPPLIERS (S#, SNAME, STATUS, CITY)
PARTS (P#, PNAME, COLOUR, WEIGHT, CITY)
PROJECTS (J#, JNAME, CITY) SPJ (S#, P#, J#, QUANTITY)

Express the following queries using relational algebra and SQL :
(i) Get full details of parts supplied by a supplier from Goa.
(ii) Get supplier names for suppliers who have not supplied the part P2.
(iii) Get part numbers that are supplied to all the projects o{ Goa.
(iv) Get all cities in which at least one supplier, part or project is located.
(v) Get part numbers for parts that either weigh more than 20 kg or are supplied by Supplier S2.

(b) What is meant by Log based recovery techniques ? Explain the write-ahead log strategy for recovery in a centralized DBMS, with the help of an example. (5)

(c) Give four advantages and two disadvantages of Distributed Database Management System. (4)

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

(e) What is Query Processing ? Describe the steps involved in query Processing. (5)

2. (a) Explain the shadow paging recovery scheme with the help of a suitable diagram. (5)

(b) Explain the Sort-Merge method of the two variable expression query evaluation, with the help of an example. (5)

(c) Convert the following ER diagram into 3NF tables. (5)
-----DIAGRAM-----
Please note a loan may belong to more than one branches. And a branch can give many loans.

3. (a) Explain two-phase locking with the help of a suitable example- What benefits are provided by strict two-phase locking ? What are its disadvantages ? (6)

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

(c) What is a join index ? Explain its use in a relational database management system. (3)

4. (a) A table STUDENT has columns Roll_No, Class, Name and Marks. Write a program using embedded SQL to display the classwise report for the number of students in different mark ranges as follows : (9)

(b) Describe 4NF with the help of an example. Would you prefer BCNF over 4NF ? Justify your answer. (6)

5. (a) Explain the following terms in the context of Relational DBMS and/or Distributed DBMS : (15)
(i) Database triggers and their use
(ii) Client - Server architecture of DBMS
(iii) Audit trails
(iv) 3NF
(vi) Distributed catalog
Also give an example of each.