WBUT Question Papers EE Database Management System B Tech 6th Sem

WBUT Question Papers EE

Database Management System B Tech 6th Sem

 

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 Jar as practicable.

GROUP-A ( Multiple Choice Type Questions)

1. Choose the correct alternatives for the following :

10 x 1 = 10

i)              A table can have only one

a) Primary key      b) Candidate key .

c) Super key        d) all of these.

ii)            What is a RDBMS terminology for a set of legal values that an attribute can have ?

a) Tuple            b) Relation

c) Attribute        d) Domain.

Ill) What is the smallest unit of data in a relational model ?

a)     Datatype   b) Field

c)  Data value   d) None of these.

tv) The word ‘loss’ In lossless refers to

a) loss of Information b) loss of attributes c) loss of relations ^ d) none of these.

v)             SELECT operation in SQL is a

a)            data queiy language

b)            data definition language

c)             data manipulation language

d)            data control language.

vi)           2NF is always in

a)     INF b) BCNF

c) MVD              d) none of these.

vii)         When all the attributes in a relation describe and depend upon the primary key, the relation is said to be in

a)      INF b) 2NF

c) 3NF              d) 4NF.

viii)       The concurrency control has the problem of

a)            lost updates        b) dirty read c) unrepeatable read d) all of these.

ix)          What separates the physical aspects of data storage from the logical aspects of data representation ?

a)     Data    b) Schema

c)     Constraints  d) Relationship.

x)            What schema defines how and where the data are organized in a physical data storage ?

a)       External     b) Internal

c)         Conceptual   d) None of these.

GROUP-B ( Short Answer Type Questions)

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

  1. a) Define ‘meta data’.

b)        What is the difference between ‘Strong Entity Set’ & Weak Entity Set’ ?                             ,2 + 3

  1. Discuss the entity integrity and referential integrity constrains. 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. Define BCNF. How does it differ from 3NF ? Why is it considered stronger from 3NF ?
  4. With suitable examples, show how recovery in a database system can be done using LOG files with —

a)         immediate updation

b)   deferred updation.                  2×2-

GROUP -C (Long Answer Type Questions)

Answer any three of the following. 3 x 15 = 45 7‘ ^ Consider the relational database as given below and write down expressions in relational algebra for the following queries.   •

Material_Master {item_id, item name, reorder level)

Material_Dts ( item_id, Supplier_id, Pharchase_date, Qty, Utcost)

i) Select the quantities of each purchased material alphabetically.

Ji) Select the names of materials which have the highest total quantity.              1

iii)          Replace the material name ‘power supply’ with

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

b)            Give an example of derived attribute.

c)             Design a Generalization-Speeialization 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.

d)            List two reasons why ‘null’ values might be introduced into the database ?                    6 + 2 + +

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

Flights (flno, from, to. distance, departs, arrives, price) Aircraft (aid. aname, cruising .range)

Certified (eid, aid) Employees (eid, ename, salary) 1)   Identify the flights that can be piloted by eveiy pilot whose salary is more than $1,00,000.

U) Find the elds of employees who make the second highest salary.

Hi) Print the names and salary of every non-pilot whose salary is more than the average salary for pilots. tv) For all aircraft with cruising_range over 1000 miles, find the name of the aircraft and the average salary of all pilots eertifled for this aircraft.

V) Find the names of pilots who can operate planes with a range greater than 3000 miles but are not eertifled on any Boeing aircraft,

b)            Specify the query in SQL to’declare a ‘Cursor-ito tod names & cities of residence of customers who both an account and a loan at a particular bank branch

in the same city as that customer.

c)             The saleman_master table records the salesman_no, name, rate_of_commission, qtd_sales. The commission_amount and date_of_payment along with the salesman.no is calculated and recorded in commission_payable table.

Write a PL/SQL block of code such that depending upon the user entered salesman_no, the commission_amount is calculated and inserted into the commission_payable table. If the user enters a saleman_.no that is not in the saleman_master table, then the PL/SQL block must display appropriate error message back to the user.

d)           What is a trigger ?

Consider the following relational schema :

An employee can work in more than one department ; the pct_time field of the works relation shows the percentage of time that a given employee works in a ^ given department:

Emp (eid, ename, age, salary)

Works (eid, did, pct_time)

Dept (did, budget, managerid)

Write a Trigger to ensure each of the following requirements, considered independently,

i)              Emplyoees must make a minimum salaiy of $1000. tO Eveiy manager must also be an employee, lift The total percentage of all appointments for an employee must be under 100% / iy) A manager must always have a higher salary than any employee that he or she manages.

  1. a) For a given Relvar R = {A, B, C, D, E, F} and set of

functional dependencies F = { AB -> C, C -» A,

BCD, ACDB, BE-> C, CEFA, CF-* BD,

D -> £F}, find the irreducible set & candidate keys.

b)    Use the definition of functional dependency to argue that each of Armstrong’s axioms (reflexivity. augmentation, pseiido-transitivity, union & decomposition) are sound.

c)     T Explain the following terms ‘partial functional dependency’ and ‘non-transitive dependency’ with examples.

d)     Consider the following proposed rule for functional dependencies :

If-A —> B and C —» B, then A —> C, Prove that this rule .   is not sound by showing a relation r that satisfies

A B and C -» B, but does not satisfy A-> C.

4 + 5 + 3 + 3

  1. a) Consider the relation R f A, C, D, E) with the set of

F.ss. I A -> C, B C, C D, DC C, CE -> A }. Suppose the relation has been decomposed by the relations JR1 ( A, D ), R2/( A, B ), R3 ( B, E ), R4 ( C, D, E) and R5 ( A, E ). Is this decomposition lossy or lossless ? Justify your answer.

CS/B.Tech (TO/SEM-6/rr-604/2010

b)          Given a database schema named PLANE_INFO . (flight_no, date, plane, airline, from, to, miles), the functional dependency diagram is given below:

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

c)           If D be the set of all functional and multivalued dependencies then write down the rules to compute the D+ ( Closure of D}. ‘

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:

1) Identify the candidate key(s) for R.

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

Bfl If R is not in BCNF, decompose it into a set of BCNF relations that preserve the dependencies :

I)              C-* D,C-* A, B->C

II)            B->C, D ->A

III)          ABC -* D, D A

IV)         A -> B, BC -4 D, A -> C

V)           AB -» C, AB -* D, C A, D B.

  1. a) Distinguish between locking and time stamp protocols for concurrency controls. Explain multi-version two-phase locking.

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

c)             Define three concurrency problems, dirty read, non- repeatable read, phantoms.

d)            Consider the following two transactions :

Tj : read (A);

read (B);

if A = 0, then B : B + 1 ;

write (B)

T2 : read ( B) ;

read ( A ) ;           :

if B = 0, then A : A + 1 ;

write ( A)   *

Add lock and unlock instructions to transactions T r and T 2 , so that they observe the two-phase locking

protocol. Can the execution of these transactions result in a deadlock ?

e)            What are the roles of the Analysis, Redo and Undo phases in the recovery algorithm ‘^RIES* ?

Leave a Comment