WBUT Question Papers Database Management System B Tech 6th Sem June 2009

WBUT Question Papers

Database Management System B Tech 6th Sem June 2009

time : 3 Hours }

full Marks : 70

GROUP -A (Multiple Choice Type Questions)

  1. Choose the correct alternatives for the following :

1) Overall logical structure of a database can be expressed graphically by a) ER diagram  b) Records

c) Relations              d) Hierarchy,

ii) A normal form in which every determinant is a key is

a) 2NF                    b) 3NF

c) BCNF                   d) 4NF.

ill) Which of the following levels of abstrction involves the view of data ?

a)    External level -,    b) Conceptual level

* C) Physical level        d) None of these,

iv)              One of the shortcomings of file system is

a)       data availability  b) fixed records

c)        sequential records d) lack of security.

v)                The ability to modify the internal schema without causing any change to external schema is

a    physical data independence b) logical data independence

c)                external data independence d) none of these.

l eras unm

d)   vii)            Which of the following features is supported in the relational database model ?

b)                  Multi-valued attributes

c) Associations with multiplicities d) Generalization relationships. ________________________________________

viii)          Four DML commands are

a)               CREATE, UPDATE, DELETE, SELECT

b)               INSERT, UPDATE, DROP, SELECT

c)                CREATE, ALTER, DELETE, SELECT

d)               INSERT, MODIFY, DELETE, SELECT

e)                INSERT, UPDATE, DELETE, SELECT.                 ___

ix)             Given the relation schema Bank ( BankID, AccountNumb, Balance, Customer ) with FDs :

{ BankID, AccountNumb -> Balance; BankID, AccounNumb -> Customer; Customer -> BankID }.                                ,

What is the highest normal form for the relation schema Bank ?

a)       First    b) Second

c)                           Third                     d) Boyce Codde.

x)              A relation is considered to be in second normal form if it is in first normal form and it has no

dependencies.

a)       referential   b) functional

c)      partial key               d) transitive.

JS/B.TECH (IT) / SEM-6 /IT-604/09

GROUP -B ( Short Answer Type Questions )

Answer any three of the following questions.

2           a) What do you mean by functional dependency ? b) What are the main characteristics of functional dependencies ?

3           Define BCNF. How does It differ from 3NF ? Why Is It considered a stronger than

3 NF ?

4          What are ACID properties of a database transaction ? How are they selected to the

concurrency control ?

5. a) What is the difference between a database and a table ?

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

Give an example of supertype/subtype relationship where the overlap rule

applies.2

What is inheritance in generalization hierarchies ?

GROUP -C ( Long Answer Type Questions )

Answer any three of the following questions.

Given a database schema named PLANEJNFO (flight_no. date,  plane, airline.

Decompose it up to Boyce-Codd Normal Form ( BCNF ).

b)               Consider the 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.

c)                Given F = { A -* B, B -*■ C}. Find an instance of a relation that satisfies F but does not satisfy B -* A. Can you find an instance that satisfies F but not A -* C ?

  1. a) Consider the relation schemas given below :

STUDENT (student_id, name )

ENROLLEDIN (student_id, subject_code)

SUBJECTS (subject_code, lecturer)

Write relational algebra for the following :

i)              Who teaches CP1500 or CP3020

ii)             Who teaches at least two different subjects ?

ill) What are the names of the students taking a subject taught by Roger ?

b)               Write down the differences between DBMS and Traditional File Processing

System.

c)                Describe ACID properties in DBMS.

d)               Give an example of derived attribute.    (2+2+2J+3+4+2

  1. a) Explain the roles of a database administrator ( DBA).

b)               Write a row trigger ( in SQL ) to insert the existing values of the table SALARY ( employee no, basic salary, commission, deduction, department ) into a table named OLDINFO when the SALARY table is updated.

c)                What is aggregation ? Discuss with an example.

d)               Draw a functional dependency diagram ( FD diagram ) that is in 3 NF but not in BCNF. Decompose that FD diagram into BCNF.      5 + 4 + 3 + 3

6782 (11/06)”

  1. a) Draw an E-i? diagram for the following :

A department store operates In several cities. In a city there is one headquarters coordinating the local operations. A city may have several stores. Stores hold any amount of items. Customers place their orders for any number of items to a given store.

b)               Why we need query optimization ?

c)                Consider the relation R (A, B, C, D, E ) with the set of F = { A -*• C, B -* C, C -* D, DC -* C, CE -* A }. Suppose the delation has been decomposed by the relations R1 (A, D }R2 ( A, B } R3 ( B, E ) J?4 ( C, D, E ), R5 (A, E ). Is this decomposition lossy or lossless ? Justify your answer.                              7 + 2 + 6

  1. Write short notes on any three of the following :        3×5

a)               Vertical and Horizontal Fragmentation

b)               Armstrong’s axioms

c)                Two-phase locking protocol

d)               Conflict serializability

e)                Theta ( 0 ) join.

Leave a Comment