Paper Name: Database Management Systems     

Paper Code: IT 604

 

 

  1. Answer any five of  the  following questions  ----

a)      Define discriminator with an example .

b)      What are  the advantages of PL/SQL over SQL ?

c)      Why normalization is required ?

d)      What is arity of a relation ?

e)      What conditions must be fulfilled if  we want to perform intersection operation on two relations  ?

f)        Why can we have at most one primary or clustering index on a file but several secondary indexes ?

g)      Distinguish between strong and weak entity with suitable example.

h)      Why is referential integrity important ?

i)        What are the basic components of DBMS ?

j)         Define block and blocking factor of a file.

 

     2 .  a)     Describe Three-schema Architecture.

           b)     Define super key, candidate key and primary key with a suitable example.

           c)     What are the main functions of DBA?

 

3.  a)     Explain the terms briefly

i)  Derived Attribute

                    ii) Domain

                   iii) One to many relationship

b)      Draw  an E-R diagram for a hospital using at least five entity sets.

 

4.      a)   Define following terms (giving expression) in respect of Relational Algebra.

 i) Selection

                  ii)    Natural Join  

b)      Consider the following relational schema.

Employee(employee-name, street, city)

Works(employee-name, company-name, salary)

Company(company-name, city)

Manages(employee-name, manager-name)

                   For each of the following queries write an expression in the relational algebra.

i)   Find the names and cities of residence of all employees who work for  First Bank Corporation.

I             ii) Find the names of all employees who live in the same city as the company for which they work.

                   iii) Find the names of all employees who earn more than every employee of Small Bank

                         Corporation.

             iv) Assume the companies may be located in several cities. Find all companies located in

                   every city in which Small Bank Corporation is located.

 

 

 

5.      a) What is  Trigger ?

       b) Consider the following relation schema of Library.

  Book(book-id, title)

             Author(book-id, author-name)

             Book-copies(book-id, branch-id, no-of-copy)

             Library-branch(branch-id, branch-name)

             Book-loan(book-id, branch-id, card-no)

             Borrower(card-no, name, city)

           Write the following queries using SQL.

i)        List the name of books borrowed by the borrowers who reside in KOLKATA.

ii)         Retrieve the names of borrowers who do not borrow any book.

iii)        Retrieve the name of the library branches which have maximum number of books.

iv)        Retrieve the name of borrowers who have borrowed at least 3 books and not more than

         10 books.

 6.  a)   Define Functional Dependency  with suitable example.

b)      Define MVD with suitable example.

c)      Find a loss-less join decomposition of

           R = { PAN, PI, DI, DRUG, QTY, COST } into Boyce-Codd normal form with set of

       functional dependencies

            F = { PAN ® PI, PI®DI, PI,DRUG®QTY, QTY®COST }

 

<![if !supportLists]>7.      a) Discuss the “insertion anomalies”,  “updation anomalies” and “deletion anomalies” with

          respect to normal forms with suitable example and suggest a method to overcome them.

      b) Why a relation that is in 3NF generally considered good although BCNF is stronger than 3NF.

c)      Discuss the problem of spurious tuples and state how we may prevent it.

 

8.      a) Explain multi-level indexing with an example.

b) Draw a left-biased B-tree from the following sequence assuming order of B-tree is 4.

         10, 24, 23, 11, 31, 16, 26, 35, 29, 20, 46

 

9.      a) What do you mean by ACID properties of a transaction. Explain the usefulness of each with

          example.

b)      State two phase locking protocol with  an example.

c)      Explain the distinction between serial schedule  and serializable schedule.

 

10.  a) Two data items A and B are to be updated by two transactions T1 and T2 respectively.

           T2 is nested within T1. Considering the relevant read and write statements only, the

            transactions are shown below:

.              T1 :     read A;

                          :

                T2 :    read B;

                          :

                          write B;

                           :                                      (1)

                          end  T2;   

                            :

                          write A;                    

                            :                                      (2) 

                           end T1 ;

 

 

     At the time of crash recovery, what would be the content of the log and what recovery action

     will be taken if

       i) Crash occurs at (1) under the deferred update principle.

ii)     Crash occurs at (1) under the immediate update principle.

     iii) Crash occurs at (2) under the deferred update principle.   

            b)  Describe the following types of locks-

                        binary,  shared,  exclusive 

 

  1. a) Write down  the advantages and disadvantages of distributed database system.

b)      Calculate the cost of linear and binary search for both key and non-key attributes of a relation

       r in terms of the following :

i)                    nr ® number of tuples in r.

ii)                   mr ® number of blocks in r.

iii)                 SC(A,r) ® the average number of records that satisfy an equality condition on attribute A. Here, search  is used for selection operation with equality comparison.

 

    12 . Write Short notes on the following

a)      Specialization and Generalization.

b)       4 NF

c)       Data Independence

d)      Shadow paging

e)      Views

f)        Fragmentation in DDBMS.