Rr321903-data-base-management-systems

Set No. 1

Code No: RR321903

III B.Tech Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS (Electronics & Computer Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) What is DBMS? Explain the advantages of DBMS (b) What is a data model? Explain the relational data model

[2+5=7] [3+6=9]

2. (a) Consider the following schema given. The primary keys are underlined. Sailors(sailor-id, sailor-name, sailor-rating, sailor-age) Boats(boat-id, boat-name, boat-color) Reserves(sailor-id, boat-id, day) Write the Nested queries in SQL. i. ii. iii. iv.

Find Find Find Find

the the the the

names names names names

of of of of

sailors sailors sailors sailors

who have reserved boat number 120 who have reserved a green boat who have not reserved a green boat with the highest rating [2+2+3+3]

(b) Explain the GROUP BY and HAVING clauses.

[6]

3. What are the techniques used to improve the performance of disks in RAID. [16] 4. (a) Describe in detail the structure of query optimizers. (b) When must we create a nonclustering index despite the advantages of a clustering index? Explain. [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) Compute the canonical cover (Fc ) of the following set F of functional dependencies for relation scheme R =(A,B,C,D,E) A− > BC CD− > E B− > D E− > A (b) Discuss the BCNF and III Normal Form with examples.

[8+8]

7. (a) Explain Binary locks, Shared Locks and Exclusive locks ? (b) What is the two phase locking protocal? How does is guarantee serializability. [6+10] 1 of 2

Set No. 1

Code No: RR321903 8. Explain in detail the ARIES recovery method. ⋆⋆⋆⋆⋆

2 of 2

[16]

Set No. 2

Code No: RR321903

III B.Tech Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS (Electronics & Computer Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) Explain the drawbacks of traditional file processing systems with examples. (b) Explain the three levels of data abstraction.

[7+9]

2. (a) Consider the following schema given. The primary keys are underlined. Sailors(sailor-id, sailor-name, sailor-rating, sailor-age) Boats(boat-id, boat-name, boat-color) Reserves(sailor-id, boat-id, day) Write the Nested queries in SQL. i. ii. iii. iv.

Find Find Find Find

the the the the

names names names names

of of of of

sailors sailors sailors sailors

who have reserved boat number 120 who have reserved a green boat who have not reserved a green boat with the highest rating [2+2+3+3]

(b) Explain the GROUP BY and HAVING clauses.

[6]

3. What are differences among primary, secondary and clustering indices? How do these differences affect the implementation of indices? Which of the indexes are dense and which are not. [16] 4. (a) Explain about projection based on sorting. (b) Explain about projection based on hashing.

[8+8]

5. Write short notes on the following. (a) SQL query translation process. (b) Equivalences of relational algebra.

[6+10]

6. (a) Describe the term multivalued dependency(MVD). Is the decomposition in 4NF always dependency preserving and lossless? Justify your answer with the help of an example. (b) What is join dependency? How is it different to that of multivalued dependency and functional dependency? Give an example for join dependencies and multivalued dependencies. [8+8] 7. (a) Develop an example showing how a single locking protocol could lead to a dead lock . (b) Explain about commit and roll back operations . 1 of 2

[10+6]

Set No. 2

Code No: RR321903

8. (a) Explain the purpose of the check(D)point mechanism.

[5]

(b) How does the frequency of check point affects the following: i. System performance when no failure occurs. ii. The time it takes to recovery from a system crash. iii. The time it takes to recover from a disk crash. ⋆⋆⋆⋆⋆

2 of 2

[3+4+4]

Set No. 3

Code No: RR321903

III B.Tech Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS (Electronics & Computer Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) Consider the following schema Suppliers(sid, sname, saddress) Parts(pid, pname, color) Catalog(sid, pid, cost) The key fields are underlined. Write the following queries in relational algebra i. ii. iii. iv. v.

Find the names of suppliers who supply blue part Find the sids of suppliers who supply every red part Find the pids of parts that are supplied by at least two different suppliers Find all the pids of parts supplied by supplier with sid=200 Find the pids of parts supplied by every supplier at less than Rs 500. [2+2+2+2+2]

(b) Given two relations R1 and R2 , where R1 contains N1 tuples, R2 contains N2 tuples, and N2 > N1 > 0 , give the minimum and maximum possible sizes (in tuples) for the result relation produced by each of the following relational algebra expressions. In each case, state any assumptions about the schemas for R1 and R2 that are needed to make the expression meaningful. i. Selecting all the tuples from R1 where a=5 ii. Projecting the attribute b from R2 iii. R1 X R2

[2+2+2]

2. (a) Consider the following schema given. The primary keys are underlined. Sailors(sailor-id, sailor-name, sailor-rating, sailor-age) Boats(boat-id, boat-name, boat-color) Reserves(sailor-id, boat-id, day) Write the Nested queries in SQL. i. ii. iii. iv.

Find Find Find Find

the the the the

names names names names

of of of of

sailors sailors sailors sailors

who have reserved boat number 120 who have reserved a green boat who have not reserved a green boat with the highest rating [2+2+3+3]

(b) Explain the GROUP BY and HAVING clauses.

[6]

3. (a) Draw a comparison between ordered indexing and hashing. (b) Explain the organization of records in files. 1 of 2

[6+10]

Set No. 3

Code No: RR321903

4. (a) Discuss in detail the steps involved in processing a query. (b) Explain any one of the algorithms for computing the join of relations. [8+8] 5. Discuss in detail about estimating the cost of an evaluation plan for a query block. [16] 6. (a) Explain why the PJNF is more desirable normal form than 4NF. (b) Explain Domain Key Normal Form(DKNF) with example.

[8+8]

7. (a) With an example explain Serial and non-Serial Serializability schedule. (b) Write a note on concept of tansaction processing 8. Explain in detail the ARIES recovery method. ⋆⋆⋆⋆⋆

2 of 2

[8+8] [16]

Set No. 4

Code No: RR321903

III B.Tech Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS (Electronics & Computer Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. Write short notes on: (a) Key constraints (b) General constraints (c) Relational calculus.

[6+5+5]

2. (a) What are views? Discuss the problems encountered in modifying database through views. (b) What is an embedded SQL? Give examples.

[10+6]

3. (a) Construct a B tree of order 2 to maintain the keys 1,9,8,6,4,5 and 10. (b) Show how 4 is replaced by 7 in the tree constructed in the above question. [8+8] 4. Discuss about the following: (a) The system R optimizer. (b) The iterator interface for operators and access methods.

[10+6]

5. Discuss different types of parameters that are used in cost functions. Where is this information kept? [16] 6. (a) Why are the certain functional dependencies called trivial functional dependencies. (b) compute the closer of (Ft) for the following set F of functional dependencies for relation scheme R = (A,B,C,D,E) A → BC CD → E

E →A

B →D Using the functional dependencies compute the canonical cover Fc . 7. Write short notes on (a) dead lock. (b) exclusive lock.

1 of 2

[8+8]

Set No. 4

Code No: RR321903 (c) binary lock. (d) live lock.

[4+4+4+4]

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] ⋆⋆⋆⋆⋆

2 of 2