Relational Database Management System Question Paper of 3rd Semester IT(D) Download Previous Years Question Paper 1

S.B. Roll No.

RELATIONAL DATABASE MANAGEMENT SYSTEM

3^rd Exam/Comp/IT/0090/Dec’08

Duration : 3 Hrs.

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

1.                 Chose the correct or best alternative in the following

a.                 Which of the following is a reason to model data?

i.                   Understand each user’s perspective of data

ii.                 Understand the data itself irrespective of the physical representation

iii.              Understand the use of data across application areas

iv.               All of the above

b.                If an entity can belong to only one lower level entity then the constraint is

i.                  Disjoint

ii.                Partial

iii.             Overlapping

iv.              Single

c.                 The common column is eliminated in

i.                 Theta join

ii.               Outer join

iii.            Natural join

iv.            Composed join

d.             In SQL. Testing whether a sub query is empty is done using

i.                Distinct

ii.             Unique

iii.          Null

iv.                        Exists

e.                  Use of UNIQUE while defining an attribute of a table in SQL means that the attribute value are

i.                        Distinct values

ii.                        Cannot have NULL

iii.                        Both (a) and (b)

iv.                        Same is primary key

f.                   The cost of reading and writing temporary files while evaluating a query can be reduced by

i.                        Building indices

ii.                        Pipelining

iii.                        Join ordering

iv.                        None of the above

g.                 A transaction is in _________ state after the final statement has been executed.

i.                        Partially committed

ii.                        Active

iii.                        Committed

iv.                        None of the above

h.                 In multiple granularity of locks SIX lock is compatible with

i.                        IX

ii.                        IS

iii.                        S

iv.                        SIX

i.                   The statement that is executed automatically  by the system as a side effect of the modification of the database is

i.                        Backup

ii.                        Assertion

iii.                        Recovery

iv.                        Trigger

j.                    The normal form that is not necessarily dependency preserving is

i.                        2NF

ii.                        3NF

iii.                        BCNF

iv.                        4NF

Section –B

Note: Answer all questions. Each question carries 6 marks.

2.                   

a.                 Briefly describe the different kinds of users of a DBMS.

b.                 Explain the following. Give an example.

i.                        Superkey

ii.                        Weak entity set

iii.                        Attribute inheritance

c.                   

i.                        Define the concept of aggregation. Give two example where this concept is useful.

ii.                        Define the following

1.                 A relation.

2.                 Atom of domain relational calculus.

d.                 Derive the union rule, decomposition rule and the pseudo transitivity rule using the three Armstrong’s  axioms.

e.                  Define multi valued dependency. What do understand by trivial multivalve dependency?

f.                   Given R(A, B, C, D,E ) and  M the set of multi valued dependencies

i.                        A →→ BC

ii.                        B→→CD

iii.                        E→→AD

Is R in 4NF? Justify your answer. If is not, decompose R into 4NF

Section –C

Note: Attempt any two questions

3.                 Given the relations R(A, B, C) and S (C,D,E,F) given an expression in tuple relational calculus that is equivalent to each of the following.

a.                 II _ABC (R)

b.                 σ _E=10(S)

c.                  R∞ S

d.                 R S 

4.                 Given the relations Staff (staff No, position, salary) and property ( number , rent, staff No) given below. The staff looks after a given property.

Staff

staff No                position                salary

                             manager               50000.00

                             assistant               15000.00

                             supervisor            25000.00

                             manager               45000.00

          Property

          Number                rent                                staffno

          PA14                    5000.00                         SL21

          PG4                      6000.00                         SG5

          PL94                    10000.00                       SL21

Give the result table for the following SQL          queries

i.                   SELECT     position, COUNT(Staff NO) AS POS, my count

FROM                  Staff

ii.                 SELECT     staff no

FROM                  staff

WHERE               salary>(SELECT AVG(salary) FROM staff)

iii.              SELECT     staff no

FROM                  property

GROUP BY         staff no

HAVING    COUNT(*)>1

iv.              INSERT INTO     staff no

VALUES    (‘SG33, assistant’)

5.                   

a.                 Describe the four main ways of optimizing disk block access.

b.                 Describe the algorithm for updating indices for a single level index when a record is

i.                        Inserted

ii.                        Delected

What will be the modification if there are multilevel indices.