DATABASE MANAGEMENT SYSTEM QUESTION PAPERS

II B.Tech II Semester Supplimentary Examinations,
Apr/May 2008
Answer any FIVE Questions
All Questions carry equal marks
Set No. 1
1. (a) Explain the drawbacks of traditional file processing systems with examples.(b) Explain the three levels of data abstraction. [7+9]
2. (a) What is a view? Explain the a views in SQL?(b) Explain nested queries with example in SQL ? [8+8]
3. (a) Which of the three basic file organizations would you choose for a file wherethe most frequent operations are as follows,i.Search for records based on a range of field values.ii. Perform insert and scans where the order of records does not matter.iii. Search for a record based on a particular field value.(b) Define dense index.(c) How does multi level indexing improve the performance of searching an indexfile[6+4+6]
4. (a) Explain about projection based on sorting.(b) Explain about projection based on hashing. [8+8]
5. (a) What is indexing ? Explain with an example.(b) Explain about query processing. [8+8]
6. (a) Explain functional dependencies and multivalued dependencies with examples.(b) Consider the relation R(A,B,C,D,E,F) and FD’sA ! BC F ! AC ! AD ! E E ! Dis the decomposition of R into R1 (A,C,D), R2 (B,C,D) and R3 (E,F,D) lossless? Explain the requirement of loss less decomposition. [8+8]
7. (a) Define the concept of a schedule for a set of concurrent transactions. Give asuitable example.(b) Explain how does granularity of locking affect the performance of concurrencycontrol algorithm. [8+8]
8. Explain WAL Protocol, UNDO algorithm, Check pointing and Media Recovery?[16]
Set No. 2
1. Write short notes on:(a) Key constraints(b) General constraints(c) Relational calculus. [6+5+5]
2. (a) What is a view? Explain the a views in SQL?(b) Explain nested queries with example in SQL ? [8+8]
3. (a) Explain the limitations of static hashing. Explain how this is overcome indynamic hashing.(b) Write a note on indexed sequential files. [10+6]
4. (a) Consider the following SQL query for a bank databaseSelectT.branch − nameFrombranchT, branchSwhereT.Assets ¿ S.assetsandS.branch − city = ”HY DERABAD′′Write an efficient relational algebra expression that is equivalent to the query.(b) Define query optimization and at what point during query processing doesoptimization occur? [8+8]
5. (a) What is indexing ? Explain with an example.(b) Explain about query processing. [8+8]
6. (a) Let R=(A,B,C,D,E) and letMbe the following set of multivalued dependenciesA− >> BCB− >> CDE− >> ADList the nontrivial dependencies in M+(b) Describe the properties of normalized and unnormalized relations. [10+6]
7. (a) Explain the concept of transaction atomicity.(b) How does the two phase locking protocol ensures serializability? [6+10]
8. Explain in detail the ARIES recovery method. [16]
Set No. 3
1. (a) What is DBMS? Explain the advantages of DBMS. [2+5=7](b) What is a data model? Explain the relational data model. [3+6=9]
2. (a) Give the various methods of managing data security.(b) Describe the “dynamic SQL”. [8+8]
3. Discuss the difference between index sequential and hashed file organizations. Com-pare their storage and access efficiencies. List the applications where each of thefile organization is suitable. [16]
4. (a) Consider the following SQL query for a bank databaseSelectT.branch − nameFrombranchT, branchSwhereT.Assets ¿ S.assetsandS.branch − city = ”HY DERABAD′′Write an efficient relational algebra expression that is equivalent to the query.(b) Define query optimization and at what point during query processing doesoptimization occur? [8+8]
5. Show that the following equivalences hold and explain how they can be applied toimprove the efficiency of certain updates.(a) (r1 [ r2) [ r3 = r1 [ (r2 [ r3)(b) r1 [ r2 = r2 = r2 [ r3(c) σp(r1 − r2) = σp(r1) − σp(r2) [5+4+7]
6. (a) List the three design goals for relational database and explain why they aredesirable.(b) Consider the relation scheme Emp Dept( Ename, SSN, Bdate, Address, Dnum-ber, Dname, DMGRSSN) and the following set of FD’sF={ SSN− > Ename,Bdate, Address, DnumberDnumber− > Dname, DMGRSSN}Calculate the closer {SSN}+ and {Dnumber}+ with respect to F. [6+10]
7. (a) What information does the dirty page table and transaction table contain?(b) Give a short notes on recovery from deadlock. [6+10]
8.Describe the shadow paging recovery technique. Under what circumstances does itnot require a log. [16]
Set No. 3
1. (a) What is a Data model ? List the important data models [8](b) Explaini. DDLii. DMLiii. Data sublanguageiv. Host language [2+2+2+2]
2. (a) Discuss the various DDL, DML commands with illustrations in SQL.(b) Why are null values not preferred in a relation? [12+4]
3. Give algorithms for inserting a new key into a B-tree [16]
4. (a) Discuss about cost based optimization.(b) Give a detailed account of heuristic optimization. [8+8]
5. (a) Discuss the reasons for converting SQL queries into relational algebra queriesbefore optimization is done.(b) What is meant by query execution plan? Explain its significance. [10+6]
6. (a) Explain the functional dependencies and multi valued dependencies with ex-amples.(b) What is normalization? Discuss the 1NF,2NF, and 3NF Normal forms withexamples. [8+8]
7. (a) Explain timestamp ordering with an algorithm.(b) Explain different locking Techniques for concurrency control. [8+8]
8. (a) When a system recovers from a crash ? In what order must transaction beUndone and Redone? Why is this order important?(b) What is a log in the content of DBMS? How does check pointing eliminatesome of the problems associated with log based recovery? [8+8]