Set No. 1
Code No: RR411004
in
IV B.Tech I Semester Supplimentary Examinations, May/Jun 2009 DATA BASE MANAGEMENT SYSTEMS ( Common to Electronics & Instrumentation Engineering and Electronics & Control Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆
i. ii. iii. iv. v.
relational schema relational database schema domain cardinality of a relation degree of a relation.
ld .
1. (a) Explain the following with examples.
[2+2+2+2+2]
or
(b) What is a domain constraint? Explain with the help of an example. 2. (a) What are the various salient features of the QBE ? (b) Explain the following :
Relational database query. Query language SQL Embedded SQL.
uW
i. ii. iii. iv.
[6]
[7]
[2+2+2+3]
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]
nt
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.
Aj
(b) Define query optimization and at what point during query processing does optimization occur? [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) Construct an E-R diagram for university registrar’s office. The office maintains data about each class, including the instructor, the enrollment and the time and place of the class meetings. For each student class pair, a grade is recorded. Determine the entities and relationships that exist between the entities. Also construct the tabular representation of the entities and relationships. 1 of 2
Set No. 1
Code No: RR411004
(b) What is an entity type? What is an entityset? Explain the difference between the entity, entity type and entityset? [10+6]
(b) Explain Wait-die and Wound-wait in Deadlock prevention?
in
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. [8+8]
Aj
nt
uW
or
⋆⋆⋆⋆⋆
ld .
8. Explain WAL Protocol, UNDO algorithm, Check pointing and Media Recovery? [16]
2 of 2
Set No. 2
Code No: RR411004
in
IV B.Tech I Semester Supplimentary Examinations, May/Jun 2009 DATA BASE MANAGEMENT SYSTEMS ( Common to Electronics & Instrumentation Engineering and Electronics & Control Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. Explain the different types of integrity constraints with suitable examples.
[16]
ld .
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. (a) What is meant by page pinning in the buffer pool. Who is responsible for pinning pages. Who is responsible for unpinning pages.
or
(b) Describe two possible page formats. What is the trade-off between them. [10+6] 4. (a) Discuss about cost based optimization.
(b) Give a detailed account of heuristic optimization.
[8+8]
uW
5. (a) What is indexing ? Explain with an example. (b) Explain about query processing.
[8+8]
nt
6. (a) Suppose the scheme R =(A,B,C,D,E) decomposed into R1(A,B,C) and R2(A,D,E). The following set of functional dependencies hold. A− > BC CD− > E B− > D E− > A Give a lossless-join , dependency-preserving decomposition of the scheme R into BCNF. (b) Show that if a relation scheme is in BCNF, then it is also in 3NF.
[10+6]
7. (a) Discuss about deadlock detection and starvation.
Aj
(b) Explain read-only and write-only protocols and read-before-write protocol in Serializability. [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 of 1
Set No. 3
Code No: RR411004
in
IV B.Tech I Semester Supplimentary Examinations, May/Jun 2009 DATA BASE MANAGEMENT SYSTEMS ( Common to Electronics & Instrumentation Engineering and Electronics & Control Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. Write short notes on:
ld .
(a) Key constraints (b) General constraints (c) Relational calculus.
[6+5+5]
Find the names of suppliers who supply red part. Find the sids of suppliers who supply all parts. Find the pids of parts that are supplied by at least two different suppliers. Find the pids of the most expensive parts supplied by the suppliers named TATA. [3+3+3+3]
uW
i. ii. iii. iv.
or
2. (a) Consider the following schema: Suppliers (sid: integer, sname: string, address: string) Parts (Pid: integer, pname; string, color: string) Catalog ( Sid: integer, pid: integer, cost: real) Write the following queries in SQL.
(b) Why are null values not preferable in a relation.
[4]
3. (a) Write a note on dense and sparse indexing.
nt
(b) Write a note on fixed and variable length records.
[8+8]
4. Write short notes on the following: (a) Recursive partitioning
(b) Hash table overflow
[5+5+6]
Aj
(c) Materialized evaluation
5. Discuss in detail about estimating the cost of an evaluation plan for a query block. [16]
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 1 of 2
Set No. 3
Code No: RR411004
(b) Discuss the BCNF and III Normal Form with examples.
[8+8]
7. (a) Explain the concept of log based recovery. (b) Briefly explain why recovery is needed.
[10+6]
in
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]
Aj
nt
uW
or
ld .
⋆⋆⋆⋆⋆
2 of 2
Set No. 4
Code No: RR411004
in
IV B.Tech I Semester Supplimentary Examinations, May/Jun 2009 DATA BASE MANAGEMENT SYSTEMS ( Common to Electronics & Instrumentation Engineering and Electronics & Control Engineering) Time: 3 hours Max Marks: 80 Answer any FIVE Questions All Questions carry equal marks ⋆⋆⋆⋆⋆ 1. (a) Explain about various binary operations with examples in relational algebra.
2. (a) Explain with an example in SQL
Unspecified where-clause and use of Asterisk Exist and not exists Explicit sets and NULLS Renaming attributes and joined tables.
or
i. ii. iii. iv.
[12+4]
ld .
(b) Discuss the use of rename operator with an example.
[2+2+2+2]
uW
(b) Consider the following scheme for the COMPANY database. The primary keys are underlined. Employee (SSN, Fname, Lname, Birthdate, Address, Salary, Dnumber) Department (Dnumber, Dname, Dlocation) Perform the following operations using SQL. Assume the data: i. Insert a record into employee table ii. Delete an employee with SSN equal to 10. iii. Update the Dnumber of the employee tuple having salary greater than Rs 10,000. iv. Retrieve the name and address of all employees who work for the ”XYZ” department. [2+2+2+2]
nt
3. (a) What are the differences between static and dynamic files. (b) Discuss the techniques for allocating file blocks on disk.
[8+8]
Aj
4. Consider the three basic techniques selection, iteration and partitioning, and the relational algebra operators selection, projection and join. For each technique operator pair, describe an algorithm based on the technique for evaluating the operator. [16] 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. 7. (a) Write a note on 1 of 2
[8+8]
Set No. 4
Code No: RR411004 i. Conflict-serializable schedule ii. View serializable schedule iii. Strict schedule
[3+3+4]
(b) State and Justify Thomas write rule and recoverability.
[6]
(b) Contrast ARIES with Shadow-page based recovery?
Aj
nt
uW
or
⋆⋆⋆⋆⋆
2 of 2
[5+5+6]
ld .
(c) Explain 3 main principles of ARIES algorithm?
in
8. (a) What are the merits and demerits of using Fuzzy dumps for media recovery?
