JNTU Previous Year Exam Questions

JNTU Database Management Systems question paper Regulation 2005-2007-2009 / JNTU Hyderabad-Kakinada Previous Year Exam Questions / JNTU Previous Year Computer Science and Engineering, Information Technology, and Computer Science and System Engineering Question Papers / Jawaharlal Nehru Technological University Database Management System Previous Year Question Papers

Database Management Systems Question - Nov 2007

Database Management Systems Question - May 2010

Database Management Systems Question - April 2011

JNTU Database Management Systems Question April 2011

JNTU Database Management Systems question paper - April 2011 Regulation 2009 / JNTU Hyderabad-Kakinada Previous Year 2011 Exam Questions / JNTU Previous Year 2011 Computer Science and Engineering, Information Technology, and Computer Science and System Engineering Question Papers / Jawaharlal Nehru Technological University Database Management System Previous Year Question Papers

Code No: R09220502                           R09                                      Set No. 1 II B.Tech II Semester Examinations, APRIL - 2011 DATABASE MANAGEMENT SYSTEMS Common to ME, IT, MECT, AME, CSE, ECE Time: 3 hours                                                                       Max Marks: 75 Answer any FIVE Questions All Questions carry equal marks ***** 1. (a) Give a note on storage manager component of database system structure. (b) Make a comparison between Database system and File system. [7+8] 2. (a) How the Data is stored in External Storage? (b) Explain file organization & indexing? [7+8] 3. (a) Construct an ER 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. (b) What is the composite attribute? How to model it in the ER diagram? Explain with an example. [7+8] 4. Explain the difference between three storage types, Volatile, Non-Volatile and Stable in terms of I/O cost. [15] 5. (a) Discuss about Tuple Relational Calculus in detail. (b) Write the following queries in Tuple Relational Calculus for following Schema. Sailors (sid: integer, sname: string, rating: integer, age: real) Boats (bid: integer, bname: string, color: string) Reserves (sid: integer, bid: integer, day: date) i. Find the names of sailors who have reserved a red boat ii. Find the names of sailors who have reserved at least one boat iii. Find the names of sailors who have reserved at least two boats iv. Find the names of sailors who have reserved all boats. [7+8] 6. (a) Explain constraints on an Entity set. (b) Explain constraints on a Relationship set. [7+8] 7. (a) Write the following queries in SQL using Nested queries concept for following Schema. Sailors (sid: integer, sname: string, rating: integer, age: real) Boats (bid: integer, bname: string, color: string) Reserves (sid: integer, bid: integer, day: date) i. Find the names of sailors who have reserved both red and green boat ii. Find the names of sailors who have reserved all boats iii. Find the names of sailors who have not reserved red boat iv. Find sailors whose rating is better than some sailor called raghu. (b) What is a correlated nested query? Explain with an example. [11+4] 8. Consider the following two transactions:                                       T1:     read(A);                                                 read(B);                                                 if A=0 then B:= B + 1;                                                 write(B).                                       T2:     read(B);                                                 read(A);                                                 if B=0 then A:= A + 1;                                                 write(A). Let the consistency requirement be A=0 V B=0, with A=B=0 the initial values. (a) Show that every serial execution involving these two transactions preserves the consistency of the Database? (b) Show a concurrent execution of T1 and T2 that produces a non serializable Schedule? (c) Is there a concurrent execution of T1 and T2 that produces a serializable Schedule? [5+5+5] ___________________

Code No: R09220502                           R09                                      Set No. 2 II B.Tech II Semester Examinations, APRIL - 2011 DATABASE MANAGEMENT SYSTEMS Common to ME, IT, MECT, AME, CSE, ECE Time: 3 hours                                                                       Max Marks: 75 Answer any FIVE Questions All Questions carry equal marks ***** 1. (a) Briefly explain the Database Design process. (b) Define these terms: Entity, Entity set, Attribute, Key. [7+8] 2. Explain schema refinement in Database Design? [15] 3. (a) Write a note on DBMS? Explain Database System Applications. (b) What is a Data Model? Explain ER data model. [7+8] 4. During its execution, a transaction passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain why each state transition may occur? [15] 5. (a) How is Data organized in Tree based index? (b) When would users use a Tree based index? [7+8] 6. Explain ARIES. [15] 7. (a) Consider the following Relations Student (snum: integer, sname: string, major: string, level: string, age: integer) Class (name: string, meets_at: time, room: string, fid: integer) Enrolled (snum: integer, cname: string) Faculty (fid: integer, fname: string, deptid: integer) Write the following queries in SQL. i. Find the names of students not enrolled in any class. ii. Find the names of students enrolled in the maximum number of classes. iii. Print the level and the average age of students for that level, for each level. iv. Print the level and the average age of the students for that level, for all levels except JR. (b) Explain following in brief                    i. Triggers                    ii. Assertions [11+4] 8. 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) The key fields are underlined. The catalog relation lists the price changes for parts by supplies. Write the following Queries in Relational Algebra and tuple relational calculus. (a) Find the sids of suppliees who supply some red part and some green part (b) Find the sids of suppliees who supply every part (c) Find the sids of suppliees who supply every red or green part (d) Find the pids of parts supplied by at least two different suppliees. [15] __________________________

Code No: R09220502                           R09                                      Set No. 3 II B.Tech II Semester Examinations, APRIL - 2011 DATABASE MANAGEMENT SYSTEMS Common to ME, IT, MECT, AME, CSE, ECE Time: 3 hours                                                                       Max Marks: 75 Answer any FIVE Questions All Questions carry equal marks ***** 1. (a) Explain covering constraints & overlap constraints. (b) Give a detail note on weak entity set. [7+8] 2. (a) Explain functional dependency with an example? (b) Compare Third NF and BCNF, explain with examples? [11+4] 3. (a) What is the relationship between files & indexes? (b) What is the search key for an index? (c) What is Data entry in an index? [7+4+4] 4. (a) Explain the Database users and user interfaces. (b) Discuss the function of Database Administrator. [9+6] 5. (a) Discuss about joins in Relational Algebra with examples. (b) Explain about set operations in Relational Algebra with examples. [7+8] 6. Explain shadow-copy technique for Atomicity and Durability? [15] 7. (a) Consider the following Relations Student (snum: integer, sname: string, major: string, level: string, age: integer) Class (name: string, meets_at: time, room: string, fid: integer) Enrolled (snum: integer, cname: string) Faculty (fid: integer, fname: string, deptid: integer) Write the following queries in SQL. i. Find the names of all juniors (level = JR) who are enrolled in a class  taught by I. teach. ii. Find the age of the oldest student who is either a History major or enrolled in a course taught by I. teach. iii. Find the names of all classes that either meet in a room R128 or have five or more students enrolled. iv. Find the number of all students who are enrolled in two classes that meet at the same time. (b) What is a trigger and what are its 3 parts. Explain in detail. [11+4] 8. Stable Storage can’t be Implemented, Explain why it can't be? [15] ___________________

Code No: R09220502                           R09                                      Set No. 4 II B.Tech II Semester Examinations, APRIL - 2011 DATABASE MANAGEMENT SYSTEMS Common to ME, IT, MECT, AME, CSE, ECE Time: 3 hours                                                                       Max Marks: 75 Answer any FIVE Questions All Questions carry equal marks ***** 1. (a) Explain how to differentiate attributes in Entity set? (b) Explain all the functional dependencies in Entity sets? [7+8] 2. (a) Discuss about DDL and DML. (b) What are five main functions of Database Administrator? Explain [8+7] 3. (a) Construct an ER diagram for a bank Database. Bank maintains data about customers, their loans, their deposits, lockers. Determine the entities and relationships. (b) Define the terms: Entity Set, Role, Relationship set, Aggregation. [7+8] 4. (a) Explain dynamic Data structure? (b) Explain overflow of pages? [7+8] 5. (a) Explain about the basic form of a SQL queries. (b) Write the following queries in SQL for the following schema. Sailors (sid: integer, sname: string, rating: integer, age: real) Boats (bid: integer, bname: string, color: string) Reserves (sid: integer, bid: integer, day: date) i. Compute increments for the ratings of persons who have sailed two different boats on the same day. ii. Find the ages of sailors whose names begins and ends with B and has at least three characters. iii. Find the colors of boats reserved by Raghu. iv. Find the sids of all sailors who have reserved red boats but not green boats. [7+8] 6. (a) Define the divisible operation in terms of the basic relational algebra operations. Describe a typical query that calls for division. Unlike join, the division operation is not given special treatment in database systems. Explain why. (b) Database Systems use some variant of Relational Algebra to represent query evaluation plans. Explain why Algebra is suitable for this purpose. [7+8] 7. (a) What is Thomas write rule? (b) Explain the time-stamp ordering protocol? [7+8] 8. Explain advanced recovery Techniques? [15] _____________________

ACID Properties in DBMS Explained

What are ACID properties? / Why ACID properties must be held by a transaction? / ACID properties explained with  example / What would be the problems with transactions without ACID properties?

ACID Properties

ACID properties are set of properties that guarantee a reliable, error free and consistent database transaction. It means a safer transaction. [Click on the following links Atomicity, Consistency, Isolation, Durability to know more]

Atomicity	It refers to “all or nothing”. That means, in a transaction every instruction is executed and the changes (specified through instructions) are made successfully in the database.
Consistency	It refers to a consistent database after execution. The consistent state may be old or new.
Isolation	All transactions that are executed simultaneously need to ensure the individuality. That is, the resultant state of database, after the simultaneous execution of several transactions should be equivalent to the serial execution of all the transactions.
Durability	It refers to the persistence of the changes made in the database. That is, after the successful completion of the transaction, the system must store the changes permanently in the hard disk.

 

Example:

Let us assume that a transaction T1 transfers 5000 from, say account A to account B. Also assume that the initial balance of A is 10000 and B is 10000. Transaction T1 shown below depicts the actual implementation of transfer of money from one account to the other;

Transaction T1

1. Read (A) 2. A:=A-5000; 3. Write(A) 4. Read(B) 5. B:=B+5000; 6. Write(B)

 Figure 1 - Transaction T1

Figure 2 - Transaction States

The table shown above clearly shows the instructions that are executed to complete the transaction T1. At first, T1 reads the value of A and perform subtraction (the amount that is to be transferred to B must be deduced from A). Then T1 writes the new value of A into the table. Next, T1 reads B, perform an addition, and writes the new value of A into the table. Hence, at the end of successful completion of T1, we will have 5000 in account A and 15000 in account B as shown in Figure 2.

Let us assume that system which executes the transaction T1 crashes exactly after the instruction ‘Write (A)’. After the crash, when the system recovers, we would find a new value of A, i.e, 5000. But, the value of B will be 10000, i.e, B is untouched. The reason is that the amount deduced from A is not added with B. This is an unwanted situation.

To get rid of the above situation, we need to ensure that every transaction is reliable. The ACID properties ensure this reliability for every transaction.

Atomicity ensures that either T1 can enter into the new consistent state S2 after successful completion of transaction or T1 can remain in the old consistent state S1 without affecting A and B.

Consistency ensures that the changes made are legal. That is, either S1 or S2, both should be valid states. It can be evaluated in many ways. For example, for our transaction T1, the sum of A and B, before and after the transaction will be same.

At S1 (Before transaction), A + B = 10000 + 10000 = 20000

At S2 (After transaction), A + B = 5000 + 15000 = 20000

Isolation: Assume that there is one more transaction T2 executed simultaneously along with T1 as given in Figure 3; also assume that the initial balance of account C is 5000.

Transaction T1	Transaction T2
1. Read (A) 2. A:=A-5000; 3. Write(A) 4. Read(B) 5. B:=B+5000; 6. Write(B)	1. Read (A) 2. A:=A-2000; 3. Write(A) 4. Read(C) 5. C:=C+2000; 6. Write(C)

  Figure 3 - Transaction T1 and T2 (Concurrent Execution)

T1 transfers 5000 from account A to B, and T2 transfers 2000 from account A to C. Instruction 3 in both transactions write new value of A into the database. If both transactions read A at the same time, then the Write which happened later would decide the new value of A. that is, T1 writes new value of A after T2 writes A, then the new value of A would be 5000. If T2 writes A after T1 has written, then the new value of A would be 8000. Both of these values are inconsistent. In a consistent transaction execution, at the end of the transactions, we need A with balance 3000, B with 15000, and C with 7000. Hence, these two transactions should be executed in isolated manner. Isolation property ensures the serial execution of transaction and leads to consistent databases.

Durability ensures that at the end of every successful transaction the data must be written permanently into the hard disk. That is, at the end the table should have new consistent values of A, B, and C in the hard disk.

************************

Go back to Transactions Management in DBMS home page