ADVANCE DATABASE MANAGEMENT SYSTEM (ADBMS) DECEMBER 2009 SEMESTER 5 COMPUTER SCIENCE

ADVANCE DATABASE MANAGEMENT SYSTEM (ADBMS) DECEMBER 2009 SEMESTER 5 COMPUTER SCIENCE

Con. 5265-09.                                   (REVISED COURSE)                               SP-8558

                                                               (3 Hours)                                       [Total Marks: 100]

N.B. (1) Question No.1 is compulsory.
        (2) Attempt total five questions.
        (3) Figures to the right indicate full marks.

1. Give below is a case study for an museum database. The museum has a collection of
     ART_OBJECTs. Each ART_OBJECTs has a unique IdNo, an Artist, a Year when it was
     created, a Title and a Description. The art objects are categorized in several ways as discussed
     below. ART_OBJECTs are categorized based on their type. There are three main types :
     PAINTING, SCULPTURE and STATUE plus another type called OTHER  to accommodate
     objects that do not fall into one of the three main types.

     A PAINTING has a  PaintType (oil, watercolor, etc), material  on which it is drawn on
     (papers, canvas, wood,etc) and style (modem, abstract, etc). A SCULPTURE has a material
      from which it was created (wood, stone ,etc), Height, Weight  and style. And a stutue has some
      similar characters. And art object in the OTHER category has a type (print, photo, etc) and
      style. ART_OBJETCs are also categorized as PERMANENT _COLLECTION that are
      owned by the museum (which has information on the Data Acquired, whether it is OnDisplay
      or stored and cost) or  BORROWED, which has information on the collection (from which
      it is borrowed ) DataBorrowed and DataReturned. The museum keeps track of ARTIST's
      information, if know: Name, DateBorn, DateDied, (if not living, MainStyle,Discription).
      The name is asumed to be unique. Different EXHIBITION occur , each having a Name,
      StartDate, EndDate and is related to all the art objects that were on display during the
      exhibition.

    (a) Draw EER Diagram  for the above database. [7 Marks]
    (b) Show mapping of EER diagram into relational schema. [7 Marks]
    (c) Write queries for the following: - [6 Marks]
           (i) List all ART_OBJECTS that were on display at the exhibition held on '26-Oct-09'.
          (ii) List Title and Description of all paintings that are PERMANENT and Paint_type = 'oil'.

2. (a) What is meant by cost based  query optimization? List cost functions for SELECT and JOIN
          query operations. [10 Marks]
    (b) Consider the SQL Query given below. Draw initial query tree and transform this initial query
          tree into final query tree using heuristic query optimization. Also state the transformation
          rules. [10 Marks]
          SELECT    p.ticketno
          FROM      Flight f, Passenger p, Crew c
          WHERE    f.flightno = p.flghtno   AND  f.flightno = c.flightno
           AND        f.date = '06-23-04'   AND  f.to = 'FRA'
          AND         p.name = c.name      AND  c.job = 'pilot'

3. (a) Explain Hash Join algorithm. [8 Marks]
    (b) Let relations R1(A,B,C) and R2(C,D,F) have the following properties. [12 Marks]
             R1 has 20,000 tuples, R2 has 45,000 tuples,
             25 tuples of R1 fit in one block and 30 tuples of P2 fit in one block.
         Estimate the number of block accesses required, using each of the following join strategies
             (a) Nested Loop join
             (b) Merge Join
             (c) Hash Join

4. (a) Explain different join such AS EQUIJOIN, NATURAL JOIN, OUTER JOIN AND LEFT
         JOIN OUTER JOIN with suitable examples. [10 Marks]
    (b) What are the main architecture used for building parallel database? Give advantages
          and disadvantages of each. [10 Marks]

5. (a) What is DTD? Give DTD for an XML representation of the following nested relational
          schema [10 Marks]
              Emp=(ename, Chidrenset setoff(Children, Skillset setof(Skills))
              Children=(name, Birthday)
              Birthday(day,month,year)
              Skills=(type, examset,setoff(exams))
              Exams=(year, city)
      (b) Explain with examples nested relation in ORDBMS. [10 Marks]

6. (a) State the purpose of Two Phase Commit protocol. Explain  two phases in detail. [10 Marks]
    (b) Explain the need of Replication, How Queries are processed in Replication Distributed
         database? [10 Marks]

7. Write short notes on any four of the following:- [20 Marks]
     (a) Vertical and Horizontal fragmentation in distributed database.
     (b) Object Relational features in SQL3
     (c) Concurency control techniques in distributed databases
     (d) EXIST and NOT EXIST clause in SQL
     (e) Compare RDBMS and ORDBMS.