II B.Tech II Semester Supplimentary Examinations, Apr/May 2008
DATA BASE MANAGEMENT SYSTEMS
( Common to Computer Science & Engineering, Information Technology
and Computer Science & Systems Engineering)
DATA BASE MANAGEMENT SYSTEMS
( Common to Computer Science & Engineering, Information Technology
and Computer Science & Systems Engineering)
Answer any FIVE Questions
All Questions carry equal marks
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 where
the 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’s
A ! BC F ! A
C ! A
D ! E E ! D
is the decomposition of R into R1 (A,C,D), R2 (B,C,D) and R3 (E,F,D) loss
less? Explain the requirement of loss less decomposition. [8+8]
7. (a) Define the concept of a schedule for a set of concurrent transactions. Give a
suitable example.
(b) Explain how does granularity of locking affect the performance of concurrency
control 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 in
dynamic hashing.
(b) Write a note on indexed sequential files. [10+6]
4. (a) Consider the following SQL query for a bank database
SelectT.branch − name
FrombranchT, branchS
whereT.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 does
optimization 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 dependencies
A− >> BC
B− >> CD
E− >> AD
List 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 the
file organization is suitable. [16]
4. (a) Consider the following SQL query for a bank database
SelectT.branch − name
FrombranchT, branchS
whereT.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 does
optimization occur? [8+8]
5. Show that the following equivalences hold and explain how they can be applied to
improve 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 are
desirable.
(b) Consider the relation scheme Emp Dept( Ename, SSN, Bdate, Address, Dnum-
ber, Dname, DMGRSSN) and the following set of FD’s
F={ SSN− > Ename,Bdate, Address, Dnumber
Dnumber− > 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 it
not require a log. [16]
Set No. 3
1. (a) What is a Data model ? List the important data models [8]
(b) Explain
i. DDL
ii. DML
iii. Data sublanguage
iv. 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 queries
before 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 with
examples. [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 be
Undone and Redone? Why is this order important?
(b) What is a log in the content of DBMS? How does check pointing eliminate
some of the problems associated with log based recovery? [8+8]
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 where
the 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’s
A ! BC F ! A
C ! A
D ! E E ! D
is the decomposition of R into R1 (A,C,D), R2 (B,C,D) and R3 (E,F,D) loss
less? Explain the requirement of loss less decomposition. [8+8]
7. (a) Define the concept of a schedule for a set of concurrent transactions. Give a
suitable example.
(b) Explain how does granularity of locking affect the performance of concurrency
control 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 in
dynamic hashing.
(b) Write a note on indexed sequential files. [10+6]
4. (a) Consider the following SQL query for a bank database
SelectT.branch − name
FrombranchT, branchS
whereT.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 does
optimization 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 dependencies
A− >> BC
B− >> CD
E− >> AD
List 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 the
file organization is suitable. [16]
4. (a) Consider the following SQL query for a bank database
SelectT.branch − name
FrombranchT, branchS
whereT.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 does
optimization occur? [8+8]
5. Show that the following equivalences hold and explain how they can be applied to
improve 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 are
desirable.
(b) Consider the relation scheme Emp Dept( Ename, SSN, Bdate, Address, Dnum-
ber, Dname, DMGRSSN) and the following set of FD’s
F={ SSN− > Ename,Bdate, Address, Dnumber
Dnumber− > 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 it
not require a log. [16]
Set No. 3
1. (a) What is a Data model ? List the important data models [8]
(b) Explain
i. DDL
ii. DML
iii. Data sublanguage
iv. 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 queries
before 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 with
examples. [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 be
Undone and Redone? Why is this order important?
(b) What is a log in the content of DBMS? How does check pointing eliminate
some of the problems associated with log based recovery? [8+8]
