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

WBUT Exam Papers EE

Database Management System B Tech 6th Sem 2011

Time Allotted : 3 Hours

Full Marks : 70

The figures in the margin indicate full marks.

Candidates are required to give their answers in their own words

as far as practicable.

GROUP – A

( Multiple Choice Type Questions )

  1. Choose the correct alternative for the following: 10 x 1 = 10

i)                 One of the shortcomings of file system is

a) data availability                 b) fixed records

c) sequential records d) lack of security.

ii)               A trigger is

a) a statement that enables to start any DBMS

b)             a statement that is executed by the user when debugging an application program

c)             a condition the system tests for the validity of the database user

d)             a statement that is executed automatically by the system of a modification.

iii)          One difference between TRUNCATE and DELETE command is

a)           TRUNCATE deletes the table but DE£ETE only deletes records     •

b)           DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back

c)            TRUNCATE can be rolled back but DELETE cannot be rolled back

d)           TRUNCATE is a DML command but DELETE is a DDL command.

iv)          Which defining a numeric number field that can hold 3 digits before the decimal point and 3 digits after the decimal points, the width would be given as

 

a) 6 c) 8

b) 7 d) 3.

 

 

v)            Advantage of locking algorithms in concurrent execution

of DB transaction is……………

a)            deadlock

b)           concurrency

c)            consistency

d)           none of these.

vi)           Consider the schema R = ( S, T, U, V ) and the dependencies S —> T, T —> U, U ~>V and V -> S. Let R= (/?landR2) be a decomposition such that R1 n R2 = 0. The decomposition is :

a) Not in 2NF  b) In 2NF but not in 3NF

c) In 3NF but not in 2NF d) In both 2NF and 3NF.

vii)        EMPNO ENAME SAL A822

RAMASWAMY        3500 A812

NARAYAN        5000 A973

UMESH        2850

A500                     BALAJI                        5750              .

Use these data for the following questions.

Select SAL from EMP El where 3 > ( Select count ( * ) from Emp E2 where jEI • SAL > E2 • SAL) will retrieve

a)     3500,5000,2500 b) 5000,2850 c) 2850,5750  d) 5000,5750.

viii)       The information about data in a database is called

a)           meta data                               b) tera data

c) hyper data                          d) none of these.

ix)          R = {/, J, K,L}, F = {/ —>iC, ZL-+J, JK ->L,L->K}, The

candidate keys are

a)         J and K       b) JK

c)            Only I d) JK and JL.

x)           Consider the primary key foreign key relationship between Employee and Department table via Dept ID column. If you try to delete a department in Department table which is referred in Employee table, Oracle by default does not allow this. This is known as ON DELETE

a)         SET CASCADE     b) SET DEFAULT

c)           RESTRICT   d) SET NULL.

GROUP -B ( Short Answer Type Questions )

Answer any three of the following.              3×5= 15

  1. a) What is the difference between a database and

b)              Why are entity integrity and referential integrity important in a database ?                                                                 2 + 3

  1. a) What is lossless decomposition ?
  2. b)   Draw a functional dependency diagram (FD diagram)

that is in 3NF but not in BCNF. Decompose that FD diagram into BCNF.

  1. Design a Generalization-Specialization hierarchy for a motor-

vehicle sales company. The company sells motor-cycles,

passenger cars, vans, buses. Justify your placement of

attributes at each level of the hierarchy. Explain why they

should not be placed at a higher or lower level.

  1. What is the difference between Primary Index, Secondaiy Index and Clustering Index ?
  2. What are the recovery implications of :

a)           forcing buffers to the database at COMMIT ?

b)           fever physically writing buffers to the database prior to

COMMIT ?

GROUP -C

( Long Answer Type Questions )

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

  1. a) Hotel ( Hotel No, Hotel Name, Address )                                                                .

Room ( Room No, Hotel No, Type, Price_pn)

Booking ( Hotel No, Guest No. Date From, Date To, Room No )

Guest ( Guest No. Guest Name, Guest Address ) where the underlined column names are primary key. Write down expressions in relational algebra for the following queries :

i)   list all the hotels which are situated in Kolkata

ii)           list all single rooms with a charge below Rs. 1000 per night

iii)         list the names of all guest who are going to stay at ITC hotel from 25th December to 1st January.

iv)         list the price per night and type of all rooms at Grand Hotel. 6

b)           What do you mean by composite attribute and derived attribute ? Give example.                                                                           2

c)            Some of the entities relevant to a technical university are given below :

i)            STUDENT and ENGG-BRANCH (students register for engg branches)

ii)            BOOK and BOOK-COPY ( books have copies )

iii)          ENGG-BRANCH and SECTION (branches have section)

iv)          SECTION and CLASS-ROOM ( sections are scheduled in classrooms )

v)            FACULTY and ENGG-BRANCH ( faculty teaches in a particular branch )

For each of them, indicate the type of relationship existing among them ( for example, one-to-one, one-to-many or many-to-many ). Draw a relationship diagram for each of them.                                                            5

d)           An E-R diagram can be viewed as a graph. What do the following means in terms of the structure of an enterprise schema ?

i)             The graph is disconnected

ii)   The graph is cyclic                                                      2

  1. a) Compute the closure of the following set F of functional

dependencies for relational scheme.

R = (A, B, C, D, E)

A-> BC, CD-> E, B-> D, E->A                                               3

b)           Consider the relation R = { A, B, C, D, E, F, G, H, I, J }

and the set of functional dependencies :

F = {AB-> C, A-> DE, B-> F, F-> GH, D-> IJ }

Decompose R into 3NF.

c)            What do you mean by lossless and dependency

preserving decomposition ?

d)           What is MDV ? Explain with an example.

  1. a) Consider relation R ( a, B, C ) and a set of functional dependencies F = { A-> BC, B-> C, A->B,. AB->C } Compute the canonical cover for F.                                                                                  * 0

b)          Define BCNF. How does it differ from 3NF ? Why is BCNF considered to be stronger than 3NF ?

C) Draw a functional dependency diagram that is in 3NF

but not in BCNF. Decompose that FD diagram into BCNF.

d) Explain candidate key with an example.                                   2

10. a) State Two-phase Commit protocol and discuss the implications of a failure on the part of i) the coordinator

11) a participant, during each of the two phases. 4

b)          Describe the wait-die and wound-wait protocols for deadlock prevention.                                                                        9

C) Define three concurrency problems : dirty read, non- repeatable read, phantoms.                                                                         3

d)         Let Tu r2, and T3 be transactions that operate on the same data items A, B and C. Let r, ( A ) mean that 7j reads A, w, ( A ) means that T, writes A and so on for T2 and T3 . Consider the following schedule SI: r2(C), r2( B), w2 (B),r3 (B),r3 (C), r,(A), (A),u,3 (B), w3(C),r2{A),rl(B),w1(B),w2{A)

Is the schedule serializable ?                                               ,

e) What are the roles of the Analysis, Redo and Undo phases in the recovery algorithm ‘ARIES’.                                                3

  1. a) State the difference between the following in brief : Primary Index versus Secondary Index.

b)  What is blocking factor ? Explain the difference between – B-tree and B+tree indexing with proper example.

c)             Explain deferred update technique for recovery” with its advantages and disadvantages.

What are the causes of bucket overflow in a hash file organization ?

e)              Construct a B+ tree for the following set of key values :

( 2, 3, 5, 7, 11, 17, 19, 23, 29, 31 )

Assume that the tree is initially empty and values are added in ascending order. Construct B+ -trees for the cases where the numbers of pointers that will fit in one node is as follows :

i)                Four

ii)              Six

iii)           Eight

Leave a Comment