Test Papers / Previous Question Papers of IGNOU CS15 Relational Database Management System June 2002

MCA (III Yr)
Term-End Examination

June, 2002

CS15 : Relational Database Management System

Time: 3 hours
Maximum Marks: 75

1.
(a)A department store consists of many item sections. A section is in charge. The store has three kinds of employees: accounts and billing; administrators; section maintainers. A request for purchase of items for the store is initiated by accounts and billing department which has the inventory details. Purchases are made by administration with proper quality checks from specified vendors. The section maintainers update the inventory placed at shelf’s. Draw the E-R diagram for the store specifying aggregation, generalization or specialization hierarchy, if any. Translate your E-R diagram to relation schema and the design the tables in 3NF.

(b)What is a host language? Why is it needed? What is the purchase of dynamic embedded SQL? Explain with the help of an example.

(c)“Practically, one must decompose the tables only till 3NF as it is loss less, dependency preserving decomposition.” Comment on the above statement. Give example in support of your answer. How is 3NF different from BCNF?

(d)A bank has a client-server database for account withdrawal. What are the concurrency related problems that may occur? How can these problems be resolved? (e)Assuming that you are the data security administrator of a public sector bank, what are the different security and privacy measures that you will propose for its customer’s data.

2.
(a)A relation R (A, B, C, D, E, F, G, H) has the following set of functional dependency:
Is the decomposition of R in R1 (A, D, E, H), R2 (A, C), R3 (B, F, G) R4 (A, G) loss less and dependency preserving decomposition? Justify your answer.

(b)What is serialisability of transactions schedule? Describe with the help of an example. Does non-serialisable schedule result in concurrency related problems? Justify your answer.

(c)What is checkpoint? Why is it needed? What are the actions which are taken by DBMS at a checkpoint? Explain with the help of an example.

3.
(a)What is 5NF? How is it related to JOIN dependency? Explain with the help of an example.

(b)How will you recover from storage media failure such as on-line hard disk on which your database is stored? Discuss with the help of an example.

(c)What are statistical databases? What are the additional security/privacy concerns for statistical databases?

4.
(a)Why does deadlock occur in concurrent execution of transaction where locking scheme is followed? How can you detect deadlock in database system? How is the problem of deadlock resolved? Explain with the help of an example.

(b)What is a view in databases? How can views be used in implementing security of data in database systems? Can views be used for updation of data? Justify your answer. How can views be used in implementing concepts of data independence? Explain with the help of an example.

(c)Consider the following Join operation :

Assuming that A, B and C are the primary keys to relations R1, R2 and R3 respectively and all the relations are stored on their primary keys, find the total number of block transfers needed for the join operation. Please not the following:

Size of R1 = 1000 tuples
Size of R2 = 2000 tuples
Size of R3 = 3000 tuples
Records per block for R1, R2 and R3 : 100 records/block

Total number of memory buffers available = 10 nos.

5.
(a)What is time stamping? Explain a mechanism of concurrency control that uses time stamping with the help of an example.

(b)What is intention mode locking? Describe the various intention mode locks with the help of an example.

(c)What are the basic steps which DBMS might follow for query optimization? Describe with the help of an example.

6.
Describe the following terms in the context of distributed database management system and/or relational database management system with the help on an example (wherever needed).

(a)Deadlock detection in distributed database system

(b)Two phase commit protocol in distributed database system

(c)Data integrity in database systems

(d)ODBC

(e)Client-Server Databases