WBUT Exam Papers EE Database Management System B Tech 6th Sem 2012

WBUT Exam Papers EE

Database Management System B Tech 6th Sem 2012

Time Allotted : 3 Flours

Full Marks : 70

The figures in the margin indicate full marks.

Candidates are required to give their answers in their own wordp

as far as practicable,

GROUP – A ( Multiple Choice Type Questions )

  1. Choose the correct alternatives for any ten of the following

i)                A table can have only one

a) Primary key ’                   b) Candidate key

c) Super ke)*                       d) \ all of these,

ii)              What is the smallest unit of data in a relational model ?

a) Data type            b) Field

c) Data value               . d) None of these.

iii)             2 NF is always in

a) INF                                 b) BCNF

c) MUD                               d) None of these.

iv)          Select operation in SQL is a

a)           data query language

b)           data definition language

c)            DML

d)           DCL.

v)            In ER model symbol is used for

a) attribute                             b) entity

c) relation                               d) none of these.

vi)          BCNF is a type of

a) Indexing                             b) DFD

c) Normalization                      d) none of these.

vii)        what is the cardinality of a table with 1000 rows and 10 columns ?

a) 10 b) 100 c) 1000                  d) None of these.

viii)       Which operator performs pattern matching in SQL ?

a)            Except                                   b) Intersect

c)             Like                                        d) All of these.

ix)          An index on the search key is called a

a)  primary index       b) secondary index

c)     multilevel index        d) all of these.

X) Which phase is not part of a two phase locking protocol ?

a)   Growing phase   b) Shrinking phase

c) Stabilization phase d) None of these.

xi)          The data dictionary tells the DBMS,

a)           What files are in the database

b)           What attributes are possessed by data

c)            What these files contain

d)           All of these.

xii)        Fore DMC commands are

a)            Create, update, delete, select

b)           Insert, update, drop, select

c)            Create, alter, delete, select

d)           Insert , modify, delete, select.

xiii)       Which is not ran ACID property ?

a)       Atomicity     b) Integrity

c)      Consistency  d) Durability.

GROUP -B ( Short Answer Type Questions )

Answer any three of the following.

  1. Explain DDL, DML & DCL.
    1. Consider the following “Sailor” and “Reserve” relation :
    2. Reserve (sid, bid, day)                                               “

Sailor (sid, sname, rating, age)

Formulate relational algebra Query :

a)   Find names of sailors who have reserved boat # XXX.

b)   Find names and ages of sailors who have reserved a

  1. Explain “two phase” locking protocol.
  2. Define BCNF. How does it differ from 3NF ? Why is it considered stronger than 3 NF ?
  3. Discuss the “entity integrity” and “referential integrity” constraint. Why is it considered important ? Explain with suitable example.

GROUP -C

( Long Answer Type Questions )

t . f

Answer any three of the following. 3 x 15 = 45

  1. a) Find out the closure of attribute set (AG) i.e. (AG)+ in the

Set of FD’s F are as given below :

R = {A, B, C, G,H, 1}

F = { A -> B, A —> C, CG -> H, CG -> I, B -» H } is (AG) is a super key of R ?

b)           What are the differences between Embedded SQL and Dynamic SQL ?

c)            Define super key, candidate key and primary key.

d)           Compare between 3NF and BCNF with example.

  1. a) Consider the following relation and write question in SQL :

\|) Flights (flow, from, to, distance, departs, arrives,

• Price)                                                                   ,

x)            Aircraft (Aid, aname, cruising – range), y) Certified (eid, aid), z) Employees (eid, ename, salary)

i)            Identify the flights that can be piloted by every pilot whose salary is more than $ 1,00,000.

ii)           Find the eids of employees who make the second highest salary.

iii)         Find the names of pilots who can aperate , planes with a range greater than 3000 mintes

but are not certified on any Boeing aircraft.

iv)         Print the names and salary of every non-pilot whose salary is more than average salary for pilots.

b)           Specify the query in SQL to declare a “Cursor” to find names & cities of residence of customers who have both an account and a loan at a particular bank branch in the same city as that customer.

c)           The sales mari-master table records the salesman-no, name, rate-of-coJnmission, qtd-sales. The commission- amount and date-of-payment along with the salesman- no is calculated and recorded in commission-payable table.

Write a PL/SQL block of code such that depending upon the user entered salesman-no, the Commission-amount is calculated and inserted into the commission-payable table.                                     4+5 + 6

  1. a) What are dense and sparse indexing ? Explain with an

example.

b)           Create a B+ tree with the plowing key : order-3, key = 8, 5, 1,7, 3, 12, 9, 6.

c)            What is a view ?

10. a) What is multiple inheritance ?

b)           What is attribute inheritance ?

c)            Dfraw ER diagram showing cardinality :

A bill is sent to a customer. A customer may receive many bills. A clerk works in a bank. A bank has many clerks. Students appear for seats in a college. Each student can get almost one seat. A college has many seats. A student can sent many applications.

d)           With an example describe specialization and generalisation.  2 + 2 + 4 + 2 + 2 + 3

  1. a) Consider the relation R (A, B, C, D, E) with the set of FD’s.

F • {A -V Cv B C, C -» D, DC -> C, CE -> A}.

Suppose the relation has been decomposed by relations Rj (A, D), R2 (A, B), R3 (B, E), R4 (C, D, E), Rg (A, E).

Is this decomposition lossy or lossless ? Justify your

answer.        b)    Use the definition of FD to argue that each of

“Armstrong Axiom’ namely reflexivity, augmentation, pseudo transitivity, union & decomposition are sound.

c)              What is a trigger ? How many types of trigger are there ?

 

Leave a Comment