Name : ……………………………………………………………

Roll No. : ……………………………………………..…………..

Invigilator's Signature : ………………………………………..

CS/B.TECH(CSE)/SEM-5/CS-502/2011-12
2011
DATABASE MANAGEMENT SYSTEMS
Time Allotted : 3 Hours Full Marks : 70

om
The figures in the margin indicate full marks
Candidates are required to give their answers in their own words as
far as practicable.
t.c
GROUP – A
( Multiple Choice Type Questions )
bu

1. Choose the correct alternat ves for any ten of the following :
yw

10 × 1 = 10

i) A top-to bottom relationship among the items in a

database is established by a
m

a) hierarchical schema

b) relational schema

c) network schema

d) all of these.

5301 [ Turn over

CS/B.TECH(CSE)/SEM-5/CS-502/2011-12

ii) In relational model, degree of a relation is

a) No. of rows b) schema

c) No. of attributes d) No. of key attributes.

iii) Relational calculus is a

a) procedural language

om
b) non-procedural language

c) query language
t.c
d) normalization technique.
bu

iv) Domain can be d fined as

yw

a) the value of a field b) value of a tuple

c) value of a table d) none of these.

m

v) For R = { J, K, L } F = { JK –>L L –> K }

the candidate keys are

a) J and K b) JK

c) only J d) JK and JL.

5301 2
 CS/B.TECH(CSE)/SEM-5/CS-502/2011-12

vi) Additional schema for a relationship set is essential in

case of

a) one-to-many relationship

b) many-to-one relationship

c) many-to-many relationship

d) one of these.

vii) Which of the following is true ?

om
a) A super key is always a candidate key

b) Every 3NF schema is also in BCNF

t.c
c) Generalization is a b ttom-up design approach

d) None of these.
bu

viii) Serializability of oncurrent transactions are ensured by

a) locking b) time-stamping
yw

c) both of these d) none of these.

ix) Which of the following is not a DDL statement ?

m

a) ALTER b) DROP

c) CREATE d) SELECT.

x) Which index is specified on the non-ordering fields of a

file ?

a) Primary b) Clustering

c) Secondary d) None of these.

5301 3 [ Turn over

CS/B.TECH(CSE)/SEM-5/CS-502/2011-12

xi) A transaction is said to be atomic, if and only if

a) transaction is partially completed

b) transaction is fully completed

c) transaction does not take place

d) none of these.

GROUP – B

om
( Short Answer Type Questions )
Answer any three of the following 3 × 5 = 15

2. What is weak entity type ? What will be the schema for

t.c
1 1
representing such entity type i a database table ? 2 +2
2 2

3. Consider the following relations for a database that keeps

bu

track of business trips of salesperson in a sales office :

SALESPERSON ( SSN Name, Start_ Year, Dept_No )

yw

TRIP (SSN, From_City, To_City, Departure_Date, Return_Date,

Trip ID )
m

EXPENSE ( Trip ID, Account#,. Amount )

Specify the following queries in either relational algebra or in

SQL :

a) Give the details ( all attributes of TRIP relation ) for trip

that exceeded Rs. 2,000 in expenses.
b) Print SSN of salesman who took trips to 'Andaman'.
1 1
2 +2
2 2

5301 4
 CS/B.TECH(CSE)/SEM-5/CS-502/2011-12

4. What is the difference between immediate updation and

deferred updation of database ? Write down the utility of

check point mechanism in log based recovery. 2+3

5. What is functional dependency ? Define foreign key. 2+3

6. In joining two relations when will you prefer merge

algorithm ? Explain, how secondary indexing can help in

om
join operation. 2+3

GROUP – C
( Long Answer Type Questions )
t.c
Answer any three of he following. 3 × 15 = 45

7. Consider the following tab es DEPT ( DCODE, DNAME ),

bu

EMP ( ECODE, ENAME, BASIC, DCODE, DT_JN )

yw

Write down the SQL statements for the following :

a) For each department, show DNAME and total basic of

the employees in the department.
m

b) Find out the name of the departments where no person

is working.

c) Find out the name of the employees who are working in

the department named as 'ABC'.

d) Find out the maximum basic among the employees who

has joined after year 2000. 5+5+3+2

5301 5 [ Turn over

CS/B.TECH(CSE)/SEM-5/CS-502/2011-12

8. a) Why is normalization done ? Describe the anomalies.

b) Consider each order has unique order_id for each
order, following information are stored :
order_id, order_dt, customer name, customer address,
salesman name, salesman address and for each
requested item store itemcode, itename, quantity and
rate.
Further assume, following functional dependencies :
salesman name → salesman address
customer name → customer address

om
order_id → order_dt, salesman name, customer name.
order_id, icode → quantity
icode → iname, rate
Normalize the data structure up to 3 µF, showing the
t.c
steps. Indicate PK & FK also. 6+9
9. The IT Training Group ( Kolkata ) has contacted you to create
a conceptual model by using the Entity — Relationship data
bu

model for a database that will meet the information needs for
its training program. Th Company Director has provided the
following description of the training group's operating
yw

environment. The Company has twelve instructors and can

handle up to one hundred trainees per training session. The
Compa y offers five advanced technology courses, each of
m

which is taught by a teaching team of two or more

instructors. Each instructor is assigned to a maximum of two
teaching teams or may be assigned to do research. Each
trainee undertakes one advanced technology course per
training session.
a) Draw an ER diagram for IT Training Group ( Kolkata )
based on the preceding information.
b) Distinguish between Single-valued vs Multi-valued and
Stored vs Derived attributes. 10 + 5

5301 6
 CS/B.TECH(CSE)/SEM-5/CS-502/2011-12

10. a) Describe ACID properties of a transaction.

b) In a concurrent schedule, when do two instructions

conflict ?

c) Deadlock cannot occur in time stamp based protocol.

Why ?

d) What is cascading rollback ? 4+4+4+3

11. Write short notes on the following :

om
a) Security features in DBMS.

b) Advantages of Database Management system over File

processing system.
t.c
c) Spurious tuples and Dangling tuples. 5+5+5
bu
yw
m

5301 7 [ Turn over