Database Management System

SET -1 Code: 3220503 II B. Tech. – II Semester Regular Examinations, April 2009.

DATABASE MANAGEMENT SYSTEM (Common to CSE and IT) 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.

(8+8)

2. Explain different constructs that adds expressiveness to the ER Model. (16) 3. a) What is a view and explain different types of views and how they can be created using SQL? (6) b) Consider the following database. Employee (employee-name, street, city) Works (employee-name, company-name, salary) Company (company-name, city) Manager (employee-name, manager-name) Give an expression in the relational algebra, the tuple relational calculus, and the domain relational calculus, for the following queries. i. Find the names of all employees who work for “XYZ” bank. ii. Find the street, city of the employee working under manager “Krishna”. (5+5) 4. a) Why are null values not preferable in a relation? b) What is an unsafe query? Give an example and explain why it is important to disallow such queries. c) Describe the “dynamic SQL”. d) Explain different Binary Operations? (3+4+3+6)

5.

a) What is dependency preserving for decomposition? Explain why it is important. b) Define 3NF and explain with an example?

(8+8)

SET -1 Code: 3220503 6. a) Define the concept of schedule for a set of concurrent transaction. Give a suitable example. b) Explain how granularity of locking affects the performance of concurrency control algorithm. (8+8) 7.

8.

a) What is log in DBMS? How does check point eliminate some of the problems associated with log based recovery? b) Explain 3 main properties of ARIES Algorithm

(10+6)

Explain about the B+ tree and its structure in detail with an example.

(16)

SET -2 Code: 3220503

II B. Tech. – II Semester Regular Examinations, April 2009. DATABASE MANAGEMENT SYSTEM (Common to CSE and IT) Time: 3 Hours

Max Marks: 80 Answer any FIVE questions All questions carry equal marks ***********

1. a) What are the five main functions of a Database Administrator?

b) What are the major disadvantages of file processing system?

(8+8)

2. Explain the several choices and issues involved in Conceptual design with ER Model. (16)

3. In logical database design, describe how an ER diagram translated into a relational database schema (collection of tables with associated constraints)

(16)

4. a) For the following relational database write the expressions in SQL. Branch (branch name, Branchcity, Assets) Customer (customername, customerstreet, customercity) Loan (Branchname, loan number, Amount) Borrower (customername, loan number) Account (Branchname, Account number, balance) Depositor (customername, Account number) i. Find the names of all branches in Loan Schema? ii. Find all customers having loan, account or both at bank? iii. Display customernames in alphabetical order who have a loan at the Newyork branch? iv. Find set of all customer names who have an account at the bank?

(8)

SET -2

Code: 3220503

b) Explain different types of Joins in detail with suitable example?

(8)

5. a) Define BCNF. How does BCNF differ from 3NF? Explain with an example.

b) What are the advantages of normalized relations over the unnormalized relations? (10+6)

6. Describe each of the following locking protocols. a) 2PL. b) Strict 2PL. c) Conservative 2PL. (5+5+6)

7. a) When a system recovers from a crash? In what order must transaction be Undone and Redone? Why is this order important?

b) What are different Recovery Techniques used in Transaction Failures? (8+8)

8. 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 above.

(10+6)

SET -3 Code: 3220503

II B. Tech. – II Semester Regular Examinations, April 2009.

DATABASE MANAGEMENT SYSTEM (Common to CSE and IT) Time: 3 Hours

Max Marks: 80 Answer any FIVE questions All questions carry equal marks ***********

1. What are the types of languages a database system provides? Explain with examples. (16) 2. A company database needs to store information about employees( ssn, name, designation, salary, address, phone), departments(dno, dname, budget) and children of employees(name, age). Employee works in departments; each department is managed by an employee, a child must be identified uniquely by name when the parent(who is an employee; assume that only one parent works for the company) is known. We are not interested in information about child once the parent leaves the organization. Draw an ER diagram that captures this information.

(16)

3. a) Define relational algebra, Tuple & Domain relational calculus. b) What are the SQL constructs to modify destroy the structure of tables, views? c) In logical database design, while converting ER to relational model how the weak entity set is translated. (5+5+6) 4. Using the following relational database, give the expressions in SQL. Branch (branch name, branch city, assets), customer (customer name, customer street, customer city), Loan (branch name, loan number, amount), Borrower (customer name, Loan number), Account (branch name, account number, balance), Depositor (Customer name, account number) i. find the names of all customers whose street address include substring ’Main’ ii. Find average balance for each customer who lives in Harrison and having at least three accounts? iii. Find the customers from all branches who have maximum loan or deposit in that branch. iv. Find all customers who have a loan at Newyork bank and whose name is neither smith nor jones v. Find all the customers who reside outside Newyork and having both Loan and Deposit at Newyork branch. (3+3+3+3+4)

Code: 3220503

SET -3

5. a) Define 4 NF and explain with an example? b) Let R=(A,B,C,D,E) and let M be the following set of multi valued dependencies A− >> BC B− >> CD E− >> AD List the nontrivial dependencies in M+ (8+8) 6. a) Explain the concept of transaction atomicity. b) How does the two phase locking protocol ensures serializability? 7.

(8 + 8)

a) Discuss the un-do and re-do operations and explain the recovery techniques that use these operations? b) When a system recovers from a crash? In what order must transaction be Undone and Redone? Why is this order important? (8 + 8)

8.

Explain all the operations on B+ tree by taking a sample example.

(16)

SET -4 Code: 3220503

II B. Tech. – II Semester Regular Examinations, April 2009.

DATABASE MANAGEMENT SYSTEM (Common to CSE and IT) 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) 2. a) Write about the different types of attributes. b) What are the two types of constraints in E.R diagram? Explain. c) What is the difference between a candidate key and a primary key for a given relation? What is a super key? (4+6+6) 3. Write the relational algebra, the tuple relational calculus expressions for the following relational database queries? Sailor (sailor id, Boat id, sailorname, rating, age) Reserves (Sailor id, Boat id, Day) Boat (boat id, Boatname, color) i. Find the sailorname, age of the youngest sailor? ii. Find the sailorname, color of the boat reserved of those sailors whose age is more than 35 iii. Find the Number of reservations for each red boat on a particular day? iv. Find the average age of sailor for each rating level (4+4+4+4) 4. a) What is an embedded SQL? Give examples. (4) b) Using the relational schema: STUDENT (Stname, Stnum, Totalmarks, Average, Year, Semester) HOSTEL (Stnum, Roomnum) Represent the following queries in SQL. i. Get the student details such as name and total marks of 2nd year 2nd semester students ii. Obtain the room number allotted to “Kumar” iii. Obtain the names of students staying in room number 101 and secured more than 60% of marks. iv. Get the name and marks of students who are in the room number 101 in ascending order of name. (3+3+3+3)

SET -4 Code: 3220503

5.

a) What is join dependency? How is it different from multi-valued dependency and functional dependency, and explain join dependencies and multi-valued dependencies with suitable example. 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) lossless? Explain the requirement of loss less decomposition. (8 + 8)

6. Explain the following: i. Serializable schedule ii. Unrepeatable read iii. Avoids-cascading-aborts schedule (6+5+5) 7. a) Explain different types of failures that arise due to loss of non-volatile storage. b) Explain in detail about advanced recovery and Remote backup systems. (6 +10) 8.

Explain in detail about Cluster Index, Hash based and Tree based indexing. (16)