WBUT Question Papers EC Database Management System B Tech 7th Sem 2011-12

WBUT Question Papers EC

Database Management System B Tech 7th  Sem  2011-12

 

Time Allotted ; 3 Hours

Full Marks :

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 alternatives for any ten of the following :                                                                                      10 x 1 = 10

i)                The collection of data stored in a database at a particular moment is called the

a) Relation                               b) Schema

c) Instance                               d) None of these.

ii)              Which of thfe following is true ?

a)              Superkey is always a candidate key

b)              Every 3NF schema is also in BCNF

c)    Generalization is top-down design approach

d)              None of these.

iii)        The §et of permitted values of an attribute is called its

a)     tuple                                  b) domain

c) relation                                d) none of these.

iv)             Transitive dependency is removed in

a)               INF                                           b) 2NF

c)              3NF d) 4NF.

 

v)           NULL stands for

a) zero                                    b) space

c) garbage                              d) absence of any value.

vi)         Entity type in ER diagram is equivalent to ……………….  in

relational model.

a) intension of a relation b) extension of a relation c) tuple     d) attribute.

vii)       ER diagram is a tool for designing the database at

a)           physical level

b)           conceptual level

c)           view level                                                            »

d)           physical, logical and view level.

viii)     Relational algebra is

a) procedural                         b) non-procedural

c) object oriented                   dj none of these.

ix)         Which of the following statements is correct ?

a)           Output of a relational algebra operation is a relation

b)           Relational algebra operator acts on one or more relations

c)           Both (a) and (b)

d)           Neither (a) and (b).

x)           Specify the most appropriate option : join and natural join of two relations generate

a)           same tiiples in the output relation

b)           output relation of same schema

c)           both (a) and (b)

d)           neither (a) nor (b).

xi)         Which of the following statements is correct in relational model, for a relation ?

a)           Rows are ordered

b)           Columns are unordered

c)           Both {a) and (b)

d)           Neither (a) nor (b).

xii)       Which of the following is a DDL statement ?

a)   SELECT              b) DROP

c) DELETE                d) UPDATE.

 

GROUP -B ( Short Answer Type Questions )

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

  1. a) Compare primary and secondary indexing.

b)          What is the advantage of an ordered file ?                   4+1

  1. What is the impact of referential integrity on DML operations with the associated relations ?
  2. What are the functions of Database Manager and DML precompiler.      3 + 2
  3. Define weak entity type and specify the schema of the corresponding table in database.                                                        2 + 3
  4. Define schedule and conflict serializable schedule.     2 + 3

GROUP -C ( Long Answer Type Questions )

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

  1. a) Why do we normalize a relation ?

b)          Each student has unique roll. In student database for each student following information is to be stored : Roll, Name, Address, Dept. Code, Dept. Name and also for each subject in which he / she appears for examination store sub code, sub name,, full marks, pass marks, score, further assume the following functional dependencies ; U

Roll —> Name , address, dept code Dept code -» dept name

Sub code sub name, full marks, pass marks Roll, sub code -» score.

Normalize the database up to 3NF showing the steps. Indicate primary and foreign keys.                                         5+10

Consider the following schemas :

ITEM (ICQDE, INAME, PRICE)

ORDER (ORDER NO, ICQDE, QTY)

Write down the SQL statements for the following :

for each order show order no and total value [total value – sum of (QTY * PRICE) of all the items in the order )

show the order numbers in which item named as COMPUTER has been requested

for each item show item name and total quantity order find out the total number of orders.                                        5 + 4 + 4 + 2

What is mapping constraint ? How does it influence the design of database ?

b) Draw the ER diagram for the system given as follows :

An organization has number of faculties who are expert in one or more subjects, for each subject, number of such experts are there, system will store faculty and subject information and must support query on finding expertise on subjects. Student get enrolled to have training on one or more subjects. System will keep student information also one faculty is allotted to teach one or more subjects for one subject only one faculty is assigned. System must keep the information regarding such assignment.                    (4 + 4) + 7

  1. a) Explain two-phase locking protocol.

b)              Considering immediate database modification, describe the principle of log-based recovery.

c)              Assume, two relations to be joined are sorted on joining attributed. Write an optimal join strategy.                   4 + 5 + 6

  1. Write short notes on the following :
  2. a)  Database Security

b)              Database Trigger

c)               File Processing Based System vs DBMS

d)  Lossless decomposition.                                             3 + 5 + 3

Leave a Comment