Code: AC14 / AT11                                    Subject: DATABASE MANAGEMENT SYSTEMS

Flowchart: Alternate Process: DECEMBER 2007
Time: 3 Hours                                                                                                     Max. Marks: 100

 

NOTE: There are 9 Questions in all.

·      Question 1 is compulsory and carries 20 marks. Answer to Q. 1. must be written in the space provided for it in the answer book supplied and nowhere else.

·      Out of the remaining EIGHT Questions answer any FIVE Questions. Each question carries 16 marks.

·      Any required data not explicitly given, may be suitably assumed and stated.

 

 

Q.1       Choose the correct or best alternative in the following:                                         (2x10)

          

a.       A functional dependency of the form  is trivial if

 

                   (A)                                           (B)  

(C)    *                                        (D) 

       

b.      The normalization was first proposed by ______________.

 

(A)    code                                            (B)  Codd

(C) Boyce Codd                                 (D)  Boyce

            

             c.   The division operator divides a dividend A of degree m+n by a divisor relation B of degree n and produces a result of degree

                         

(A)    m – 1                                           (B)  m + 1

(C)  m * m                                          (D)  m

 

d.   _______ operator is used to compare a value to a list of literals values that have been specified.  

 

(A) LIKE                                            (B) COMPARE

(C) BETWEEN                                   (D)  IN

 

e.   Which of the following is not a characteristic of a relational database model?

 

      (A) Table                                            (B) Tree like structure

      (C) Complex logical relationship          (D) Records

 

f.    Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R.

 

      (A) It will result in a deadlock situation.

      (B) It will immediately be rejected.

      (C) It will immediately be granted.

      (D) It will be granted as soon as it is released by A .

 

g.   In E-R Diagram total participation is represented by

 

      (A) double lines                                   (B) Dashed lines

      (C)  single line                                     (D) Triangle

 

h.   The FD   A ® B , DB ® C implies

 

      (A) DA ® C                                      (B) A ® C

      (C) B ® A                                         (D) DB ® A

 

i.    The graphical representation of a query is ________.

 

      (A) B-Tree                                          (B) graph

      (C) Query Tree                                   (D) directed graph

 

j.    Union operator is a :

 

      (A) Unary Operator                            (B) Ternary Operator

      (C) Binary Operator                            (D) Not an operator

 

 

 

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

 

 

 

 

Q.2       a.   The entity set EMPLOYEE is a generalization of the entity sets FULL_TIME_EMPLOYEE and PART_TIME_EMPLOYEE. The former is a generalization of the entity sets FACULTY with attributes degree and subject of interest and STAFF with attribute classification; the latter, is a generalization of the entity sets TEACHING with attribute stipend and CASUAL_FACULTY with attribute hour rate. STAFF inherits the attribute Salary of the entity set FULL_TIME_EMPLOYEE and the latter, in turn, inherits the attributes of EMPLOYEE. FULL_TIME_EMPLOYEE is a specialization of the entity set EMPLOYEE and is differentiated by the additional attribute Salary. Similarly, PART_TIME_EMPLOYEE is specialization differentiated by the presence of the attribute Type. Each employee must have attributes empno, name and hire_date.

 

                        (i) Draw an E-R diagram for the system.

                        (ii) Convert this E-R diagram to relational tables.                                         (12)

 

b.   Explain the difference between a one-to-many and a many-to-many relationship. Which logical data structures have one-to-many and which have many-to-many relationship?          (4)

 

  Q.3     a.   What are the DBMS languages? Explain.                                                          (6)

 

             b.   What are Armstrong’s inference rules?

                   Suppose we are given relation R with attributes A, B, C, D, E, F, and the FDs,

                   A®BC                                              

                   B ®E

                   CD ®EF

                   Prove that FD  AD®F also holds in R.                                                           (10)

 

  Q.4     a.   Consider the relations:                                                                                           

 

                   PROJECT(proj#, proj_name)

                   EMPLOYEE(emp#, emp_name)

                   ASSIGNED(proj#, emp#)

 

                   Use relational algebra to express the following queries:

(i)  Find the employee number of employees who work on at least all of  the projects that employee 107 works on.

(ii) Get the employee number of employees who work on all projects.                 (8)

 

             b.   Use tuple and domain calculus to express the following query                                                                                                                    (8)

                   Compile a list of employee number of employees who work on all projects         (8)

 

  Q.5     a.   What is the difference between serial and sequential files? How searching is applied on both?                                                                     (8)

 

             b.   Discuss the problem of Spurious tuples and how we may prevent it.                    (8)

 

  Q.6           Explain the followings:

                   (i) Third normal form

                   (ii) Query Processing

                   (iii) Relational Completeness

                   (iv) Radix conversion method                                                                           (16)

 

  Q.7     a.   Consider the relations                                                                                            

                   EMP(ENO,ENAME,AGE,BASIC)

                   WORK_ON(ENO,DNO)

                   DEPT(DNO,DNAME,CITY)

 

                   Express the following queries in SQL

(i) Find names of employees whose basic pay is greater than average basic pay.

(ii) Find the sum of the basic pay of all the employees, the maximum basic pay, the minimum basic pay and the average basic pay.                          (8)

 

             b.   What are the General Transformation Rules for Relational operations?                (8)

 

  Q.8     a.    Draw a B-tree of order 5 by inserting the following data     

                       (2  14  12  4  22  8  16  26  20  10  38  18  36 24  6  48  28  40  42  32)

                   After constructing the B-tree delete the following data:

                   (i) delete 36

                   (ii) delete 32                                                                                            (6+4=10)

       

             b.   What is a view?  Create a view of EMP table named DEPT 20, to show the employees in department 20 and their annual salary.                         (6)

 

  Q.9     a.   Discuss the timestamp ordering protocol for concurrency control. How does strict timestamp ordering differs from basic timestamp ordering?            (8)

 

              b.  Explain the shadow paging recovery technique.                                                   (8)