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

WBUT Exam Papers EE

Database Management System B Tech 6th Sem June 2008

I Full Marks : 70

GROUP – A (Multiple Choice Type Questions)

  1. Choose the correct alternatives for the following :

Q Relational Calculus is a

a) Procedural language                  b) Non-procedural language

c) Structured Query language  d) none of these.

10 Cardinality ratio means

a)              number of attributes associated with an entity

b)              number of relations of an entity-relationship diagram

c)              number of entities to which another entity can be associated via relationship set

d)              none of these.

til) The entity integrity constraint states that

a)              no primary key value can be null

b)               a part of the key may be null –

c)              duplicate object values are allowed – d) none of these.

iv)  In a relational data model, the columns of a table are called

a)   Relation  b) Tuple

c)Attribute d)         Degree.

v)               BCNF Is in

a)   1 NF        b)         2 NF

c)  3 NF        d)         PJNF.

vi)             Consider the schema R (ABCD ) and functional dependencies A B, C -> D. Then the decomposition of R into R1 (AB) and R2 ( CD) is

a)              dependency preserving and lossless join

b)               lossless Join but not dependency preserving

c)               dependency preserving but not lossless join

d)              not dependency preserving and not lossless join.

vii)           If a set of attributes, K, in relation to schema J?1 is a foreign key for Rlt then

a)              every tuple; of R1 has a distinct value for k

b)               K Is a key for some other relation

c)               K cannot have a null value for tuples in ftl

d)              K is a primary kqr for R1.

viii)         Lack of normalization can lead to which one of the following problems ?

a)        Lost updates b) Insertion problems

c)           Deadlock      d) None of these.

ix)             In 2-phase locking a transaction must ,                       –

a)              release all its locks at the same time

b)               NOT obtain any new locks once it has started releasing locks

c)               only obtain locks on items not used by any other transactions

d)              ensure that deadlocks will never occur.

x)               A data model is a collection of conceptual tools for describing

a)              data and data relationships

b)               data semantics and consistency constraints

c)                    data, data relationship, data semantics and consistency constraints ‘ d) none of these.

GROUP – B (Short Answer Type Questions)

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

  1. Discuss the entity integrity and referential integrity constraints. Why is each considered important ? Explain with suitable example.
  2. Write the concepts of dense index and sparse index with example. When is it preferable to use a dense index rather than a sparse index ? Explain.
  3. What is two phase locking protocol ? How does it guarantee serializability ?
  4. Suppose you have a classical music collection consisting of CDs and/or LPs and/or tapes, and you want to build a database that will let you find which recording you have for a specific composer ( e.g. Sibelius ) or conductor ( e.g. Simon Rattle ) or soloist ( e.g. Arthur Grumiaux) or work ( e.g. Beethovan’s Fifth) or orchestra ( e.g. the NYPO ) or kind of work ( e.g. violin concerto ) or chamber group ( e.g. the Kronos Quartet). Draw an E-R diagram for this database.
  5. Compute the closure of the following set F of functional dependencies for relation schema:

R = [A.B,C,D,E)

A —> BC, CD —^ E, B —> D, E A _

List the candidate keys for R.

C8/B.TBCH {lT)/8KM-6/IT-604/08

GROUP -C (Long Answer Type Questions)

Answer any three questions.

  1. a) “Every relation in BCNF is also in 3 NF ; however, a relation in 3 NF is not
  2. necessarily in BCNF.” Explain.                                                                                         3

b)              Consider the relation

R = {A, B, C, D, E, F, G, H, I, J) and the set of functional dependencies

F= {{A, B} -> { C }.{A } -» { D, E}, { B} -> { F}, {F} {G, HI {D}{I, J}).

What is the key for R ? Decompose R into 3 NF.                                                              5

c)               What is MVDs ? What do you mean by lossless ( or non-additive ) Join property of a decomposition ?                                                                                                                              21 + 2 \

d)              Consider the relation schema EMP-DEPT ( ENO, ENAME, DOB, ADDRESS, DNUMBER, DPiAME, DMGRENO ) with the following set G of functional dependencies on EMP-DEPT :

G = { ENO ->'{ ENAME, DOB, ADDRESS, DNUMBER } , DNUMBER -» { DNAME,

• DMGRENO}}.

Calculate the closure {ENO }+ .                                                                                       2

  1. a) Consider the foUowing relations and write queries in SQL :

‘ Material-Master (item-id, item name, reorder level)

Material-Dts (item-id, Supplier-id, Purchase-data, Qty, Utcost)

i)   Select the quantities of each purchased material alphabetically

ii)              Select the names of materials which have the highest total quantity HQ Replace the material name “power supply” with “UPS”

iv) Increase the quantities of material.purchased by “ABC” for all purchases done after February, 2003.

b)              Write a procedure In PL/SQL for computing the income tax of persons using the following conditions:                                                                                                                                           4

i)                Income upto Rs. 50,000, income tax is nil

a)              Income from Rs. 50,000 to 1,00,000, income tax is 10% ill) Income from Rs. 1,00,000 to 1,50,000, income tax is 20% iv) Income more than Rs. 1,50,000, income tax is 30%.

c)               Specify the quezy in SQL to declare a ‘Cursor’ to find the names and cities of residence of customers who have more than 10,000 in any account.                                                                   4

d)              What is a trigger ?

Instead of allowing negative a/c balance, a bank deals with overdrafts by setting the a/c balance zero and creating a loan in the amount of the overdraft (an overdraft is an event when a customer’s withdrawal amount exceeds the current a/c balance of the customer). The bank gives this loan a loan number identical to the a/c no. of the overdrawn a/c. ’

Write the TRIGGER in SQL for the above event.          3

  1. a) Explain the following terms “fully functional dependency” and “non-transitive dependency” with examples.              3

b)              Use the definition of functional dependency to argue that each of Armstrong’s axioms ( reflexivity, augmentation, transitivity, union and decomposition ) is sound.                3

c)               For a given Relvar R = {A, B, C, D, E, F, G, H, I, J } and set of functional dependencies F = {ABD E, AB —> G, B —> F, C -> J, CJ -» /, G —» H}, find the irreducible set and candidate keys- 3

d)              Suppose you are given a relation R with four attributes, ABCD. For each of the . following sets of FD’s assuming those are the only dependencies that hold for R,

do the following :                                     3

0 Identify the candidate key(s) for R.

ii)              Identify the best normal form that R satisfies ( 1 NF, 2 NF, 3 NF, BCNF).

Hi) If R is not in BCNF, decomposes it into a set of BCNF relations that preserve the dependencies.;

  1. C->D, C->A,B->C
  2. B —> C, D —> A
  3. ABC -)D,D->A
  4. A -> B, BC -> D, A -> C
  5. AB —> C, AB —> D, C A D —> B.
  6.  I JBBffl \i

e)              Discuss the “insertion anomalies”, “updation anomalies” and “deletion anomalies” with respect to normal forms with suitable example and suggest a method to overcome them.          3

10. a) What is blocking factor ? Explain the difference between B-tree and B +-tree

indexing with proper example.                         1+4

b)             What is Schedule ? Describe the growing phase and shrinking phase with example of the two-phase locking protocol. 2 + 4

c)              Briefly discuss the ACID properties of transaction in DBMS. 4

11. £0 What is query optimization ? Write down the steps to process a Mgh level

. query.

b)             What do you mean by serializability ? Consider two transactions T, and T„ . Such that

T1 : Rj (A) W1 (A) R1 (B) W1 (B)

T2 : R2 (A) W2 (A) R2 (C) W2 (C)

Let schedule S :

R1 (A) W1 (A) R2 (A) W2 (A) R1 (B) W1 (B) R2 (C) W2 ( C ).

Find out whether the given schedule S is conflict serializable or not.                      1+4

cj What is view ?                                                                                                      .

Consider the tables :

employee ( emp-code, emp-name, designation, DOJ, basic-sal, dept_code) and

dept ( dept_code, dept_name )

Create a view employee-dept with following attributes :

(emp-name, designation, dept_name).

END

Leave a Comment