Rr220502-data-base-management-systems

Set No. 1

Code No: RR220502

II B.Tech II Semester Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS ( Common to Computer Science & Engineering, Information Technology and Computer Science & Systems Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) Consider the following schema for a COMPANY database Employee (Name, SSN, Address, Sex, Salary, Dnumber) Department (Dname, Dnumber, MGRSSN, MGRSTART date) Dept-locations (Dnumber, Dlocations) Project (Pname, Pnumber, Plocation, Dnumber) Works-on (ESSN, Pnumber, Hours) Dependent (ESSN, Dependent-name, Sex, Bdate, Relationship) Write the queries in Relational Algebra to i. Retrieve all employees who either work in department 4 and make over 25,000 per year or work in department 5 and make over 30,000 ii. Retrieve the Social Security numbers of all employees who either work in department 5 or directly supervise a employee who works in department 5. iii. Retrieve the name and address of all employees who work for the “Research” department iv. List all the projects on which employee “Smith” is working. [3+3+3+3] (b) What is relational completeness? If a query language is relationally complete, can you write any desired query in that language [4] 2. (a) Give the various methods of managing data security. (b) Describe the “dynamic SQL”.

[8+8]

3. Consider the two internal organizations for heap files (using lists of pages and a directory of pages). (a) Describe them briefly and explain the trade-offs. Which organization would you choose if records are variable in length. (b) Can you suggest a single page format to implement both internal file organizations? [8+8] 4. Discuss the merits and demerits of hash join, sort-merge join and block nested loops join. [16]

1 of 2

Set No. 1

Code No: RR220502 5. (a) Write short notes on - cost-based optimization - heuristic optimization (b) Detail on the structure of query optimization.

[5+5+6]

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 C →A D →E

F →A 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) Explain Binary locks, Shared Locks and Exclusive locks ? (b) What is the two phase locking protocal? How does is guarantee serializability. [6+10] 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

Set No. 2

Code No: RR220502

II B.Tech II Semester Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS ( Common to Computer Science & Engineering, Information Technology and Computer Science & Systems Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) What is an unsafe query? Give an example and explain why it is important to disallow such queries. [6]

(b) Consider the following schema given. The primary keys are underlined. PROJECT(Projectnu Project Name, Chief Architect) EMPLOYEE( Empnum, Empname) ASSIGNED TO(Projectnum, Empnum) Write the following queries in Tuple relational calculus i. Find Empnum of employees working on project COMP454 ii. Find the details of employees(both number and name) working on the project COMP365 iii. Find the details of employees working on the “Database” project iv. Find the Empnum?s of employees who do not work on project COMP464 v. Find the Empnum?s of employees who work on all projects. [1+2+2+2+3] 2. (a) what are the various salient features of the QBE ?

[7]

(b) Explain the following : i. ii. iii. iv.

Relational database query. Query language SQL Embedded SQL.

[2+2+2+3]

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. Give a detailed account of the following: (a) Query evaluation plans. (b) Pipelined evaluation.

[8+8]

5. Discuss in detail about estimating the cost of an evaluation plan for a query block. [16] 6. (a) Explain why 4NF is more desirable Normal Form than BCNF. 1 of 2

Set No. 2

Code No: RR220502

(b) Consider the relation R(A,B,C,D,E) and FD’s A− > BC C− > A D− > E F− > A E− > D Is the Decomposition R in to R1(A,C,D) , R2(B,C,D) and R3(E,F,D) loss less? Explain the requirement of lossless decomposition. [8+8] 7. (a) Explain the concept of log based recovery. (b) Briefly explain why recovery is needed.

[10+6]

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

Set No. 3

Code No: RR220502

II B.Tech II Semester Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS ( Common to Computer Science & Engineering, Information Technology and Computer Science & Systems 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) Give the various methods of managing data security. (b) Describe the “dynamic 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) Explain external sort - merge algorithm. (b) Discuss about estimation of the size of joins.

[10+6]

5. Discuss different types of parameters that are used in cost functions. Where is this information kept? [16] 6. (a) List the three design goals for relational database and explain why they are desirable. 1 of 2

Set No. 3

Code No: RR220502

(b) Consider the relation scheme Emp Dept( Ename, SSN, Bdate, Address, Dnumber, 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) Consider two transactions as follows: Transaction 1: Fac salary:=Fac salary+1025.00 Transaction 2: Fac salary:= Fac salary *1.1 What precaution, if any, would you suggest if these were to run concurrently? Write a pseudo code program for these transactions using an appropriate scheme to avoid undesirable results. (b) Explain Wait-die and Wound-wait in Deadlock prevention?

[8+8]

8. (a) What are different Recovery Techniques used in Transaction Failures? (b) Explain how System Crash and Media Failure occurs? ⋆⋆⋆⋆⋆

2 of 2

[10+6]

Set No. 4

Code No: RR220502

II B.Tech II Semester Supplimentary Examinations, Aug/Sep 2008 DATA BASE MANAGEMENT SYSTEMS ( Common to Computer Science & Engineering, Information Technology and Computer Science & Systems Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) Distinguish between DDL and DML. (b) Explain various integrity constraints. 2. (a) what are the various salient features of the QBE ?

[6+10] [7]

(b) Explain the following : i. ii. iii. iv.

Relational database query. Query language SQL Embedded SQL.

[2+2+2+3]

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) Consider the following SQL query for a bank database SelectT.branch − name F rombranchT, 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. Discuss different types of parameters that are used in cost functions. Where is this information kept? [16] 6. (a) Explain the following terms i. ii. iii. iv.

Relationship instance Composite attribute Multivalued attribute Derived attribute.

[2+2+2+3]

(b) Construct an E-R diagram for a car insurance company with a set of customers, each of whom owns a number of cars. Each car has a number of recorded accidents associate with it. Determine the entities and relationships that exists between the entities. Also construct the tabular representation of the entities and relationships. [7] 1 of 2

Set No. 4

Code No: RR220502

7. (a) Define these terms atomicity, consistency, isolation, durability, schedule, blind write. (b) Write a note on the properties of tansactions.

[12+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