Database Management Systems - Visvesvaraya Technological University Dec 09/Jan 10 Questions

Database Management Systems - Visvesvaraya Technological University Dec 09/Jan 10 Questions / December 2009-January 2010 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                      06CS54

Fifth Semester B.E. Degree Examination, Dec.09/Jan.10

Database Management Systems

Time: 3 hrs.                                                     Max. Marks: 100

Note: Answer any FIVE full questions, selecting at least TWO questions from each part.

PART – A

1. a. Explain the typical components of a DBMS with a neat diagram. (10 Marks)

b. Define and explain the following terms with an example each:

(i) Snapshot  (ii) Intension  (iii) Extension  (iv) Schema construct (05 Marks)

c. What is meant by “Persistent storage for program objects”? Explain. (05 Marks)

2. a. Explain how role names are assigned in case of recursive relationships? Illustrate this concept with a diagram. (06 Marks)

b. What is meant by partial key? Explain. (04 Marks)

c. Design an ER diagram for keeping track of information about an AIRLINE database taking into account at least six entities. (10 Marks)

3. a. Define referential integrity constraint. Explain the importance of referential integrity constraint. How is this constraint implemented in SQL? (08 Marks)

b. Consider the following relations and write relational algebra queries:

Employee(FName, SSN, Salary, Super-SSN, DNo);

WorksON(ESSN, PNO, Hours);

Department(DName, Dno, Mgr-SSN);

Dependent(ESSN, Dependent-Name);

(i) Retrieve the highest salary paid in each department.

(ii) Retrieve the name of managers who have more than two dependents.

(iii) Retrieve the number of employees and their average salary working in each department. (12 Marks)

4. a. Explain IN and EXISTS operators with suitable examples. (08 Marks)

b. Consider the same data given in Q3(b), and write the following queries in SQL:

(i) Retrieve the name of all employees who do not have supervisor.

(ii) Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

(iii) Retrieve the SSN of all employees who work on project numbers 1, 2, 3. (12 Marks)

PART – B

5. a. How is a view created and dropped? What problems are associated with updating of views? (10 Marks)

b. What is embedded SQL? With an example, illustrate how would you connect to a database, fetch records and display. Also explain the concept of stored procedure in brief. (10 Marks)

6. a. Which normal form is based on the concept of transitive functional dependency? Explain with an example the decomposition into 3NF. (10 Marks)

b. Define multi-valued dependency. Explain 4NF with an example. (10 Marks)

7. a. Explain 3 phases involved in an ARIES algorithm with an appropriate example. (10 Marks)

b. Given a relation with 4 attributes R = {A B C D} and the following FDs, identify the candidate key for R and the highest normal form.

(i) C -> D, C -> A, B -> C (ii) B -> C, D -> A. (10 Marks)

8. Write short notes on the following;

a. Two phase locking protocol

b. Transaction support in SQL

c. Write ahead log protocol

d. Time stamp ordering algorithm       (20 Marks)

***********