Filed under:
IGNOU MCA
Question Paper of CS-15 : RDBMS of June 2003
Qns 1. a. A bank has many branches, the bank has many customers. A customer can open many different kinds of accounts with the bank. Any customer of the bank can take loan from the bank. All branches can give loans. Banks have also installed automatic teller machines, from which a customer can withdraw from his/her bank. Draw the ER Diagram for the bank specifying aggregation, generalization or specialization hierarchy, if any. Create 3NF tables of your design. Make suitable assumptions, if any (10)
b. Why is embedded SQL used? Describe an application that need embedded language. How are static embedded SQL different from dynamic embedded SQL? (5)
c. “Fourth Normal Form is dependency preserving and ensures lossless decomposition”. Justify or falsify the above statement giving reasons and examples. (5)
d. An airline reservation allows many customer to book tickets simultaneously. What are the basic concurrency related problems that you may encounter, in case no concurrency control mechanism is in place? What is the solution proposed by you to overcome the concurrency related problem as above? (5)
e. Design a database security scheme for a library issue and return system (Hint: you may define security levels for database access and external schema) (5)
Qns 2. a. A relation R (A,B,C,D,E,F,G) has the following set of dependency:
AB -> CD
A -> B
F -> DEF
G -> H
Is the decomposition of R into R1 (A,B,C,D), R2(G,H), R3(F,D,E) dependency preserving and lossless decomposition? Justify your answer. (5)
b. Why do you need to take checkpoint which slows down the system at the time of checkpoint even though you have write ahead log mechanism in place that ensures recover? Justify your answer with the help of an example. (5)
c. What is serialisability? How can serialisability be ensured? Do you need to restrict concurrent execution of transaction to ensure serialisability? Justify your answer. Give an example of transactions and how you can force serialisability in those transactions. (5)
Qns 3. a. How can you recover from media failure on which your database was stored? Describe the mechanism of such recovery. (5) b. What is multi-valued dependency? How is it different from join dependency and functional dependency? Explain with the help of an example each. (5)
c. How can you track crime related to database manipulations using audit trails? Describe with the help of an example. Do such mechanisms exist in modern database Management? (5)
Qns 4. a. What will be the minimum number of block transfers needed to compute the natural join of relation R1 and R2 having the following statistics: (5) R2 (αβγ) has α as the key. R1 (β, γ) has β as the key. Size of R1 is 1500 tuples having 10 records/block. Size of R2 is 500 tuples having 50 records/block. The number of available buffers in main memory for join is 10. You can also assume that the records are stored as per the key value. Make and state suitable assumptions, if any.
b. What is two phase locking protocol? Can this protocol result in deadlock? Explain with the help of an example. (5)
c. What are the conditions of deadlock in Database Management System? How can it be detected in distributed system? How can deadlock be avoided? (5)
Qns 5. a. Describe how a query that invokes join, selection and projection operations can be optimized. Explain the above with a suitable example. (5)
b. What is the multiversion technique for concurrency control? Describe with the help of an example. (5)
c. Compare and contrast the features of simple locking, intention mode locking and time stamping mechanism from the viewpoint of transaction control under concurrent transitions. (5)
Qns 6. Describe the following terms in the context of RDBMS/DDBMS. Give examples wherever needed. (15)
a. Contents of Data Dictionary
b. Integrity and Trigger
c. Distributed Queries
d. Two Phase Commit Protocol
e. ODBC and JDBC Standards
|