Mumbai University Previous year question papers Advanced Database Management Systems Dec 2009

Mumbai University Previous year question papers

V Sem CSE Examination Dec 2009

Advanced Database Management Systems

N.S.: (1) Question NO.1 is compulsory.

2) Attempt total five questions.

(3) Figures to the right indicate full marks’.

1. Given below is a case study for an art museum database. The museum has a collection of ART_OBJECTs. Each ART_OBJECT 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 DrawnOn (paper, canvas, wood, etc.) and Style (modern, abstract, etc.). A SCULPTURE has a Material from which it was created (wood, stone, etc.), Height, Weight and Style. And a STATUE has some similar characters. An art object in the OTHER category has a Type (print, photo, etc.) and Style. ART_OBJECTs are also categorized as PERMANENT_COLLECTION that are owned by the museum (which has information onthe DateAcquired,whether it is OnDisplay or stored and Cost) or BORROWED, which has information on the Collection (from which it was borrowed), DateBorrowed and DateReturned. The museum keeps track of ARTIST’s information, if known: Name, DateBorn, DateDied (if not living), MainStyle, Description. The Name is assumed to be unique. Different EXHIBITIONs 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.

(b) Show mapping of EER diagram into relational schema.

(c) Write queries for the following :-

(i) List all ART_OBJECTS that were on display at the exhibition held on ’26-0ct-09′.

(ii) List Title and Description of all paintings that are PERMANENT and. Paint_type = ‘oil’.


2. (a) WAhadtNios..mhLeaen.t by~~cost based query ~o~p~tim.,iza Ion?v’L<isJtvcCostt f~u,nctions for SELECT Jf1I”0

and JOIN query operations.

(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 he transformationrules.




f, Passenger p, Crew cfflightno= p.flightno AND fflightno = c.flightno 806-23-04′ AND fto = 8FRA’ AND c.job = 8Pilot’


3. (a) Explain Hash Join algorithm.

(b) Let Relations R1(A,B,C) and R2(C,D,F) have the following properties. R1 has 20,000 tuples, R2 has 45,000 tuples, 25 tuples of R1 fit in one block and 30 tuples of R2 fit in one block. Estimatethe numberof blockaccessesrequired,usingeach of the followingjoin strategies

(a) Nested loop join

(b) Merge join

(c) Hash join


4. (a) Explain different Joins such AS EQUIJOIN, NATURALJOIN, OUTER JOIN AND LEFT OUTER JOIN with suitable examples.

(b) What are the main architectures used for building parallel database~ ? Give advantages and disadvantages of each.


5. (a) What is DTD? Give DTD for an XML representation of the following nested relational 10

schema Emp=(ename,Childrenset setof(Chiidren),Skiliset setof(Skills»

Children=(name, Birthday)

Birthday( day,month ,year)

Skills=(type, examset,setoff( exams»



(b) Explain with examples nested relation in ORDBMS. . 10


6. (a) State the purpose of Two Phase Commit protocol. Explain two phases in detail. 10

(b) Explainthe needof Replication.How Queriesare processedin ReplicatedDistributed databases?


7. Write short notes on any four of the following :-

(a) Vertical and Horizontal fragmentation in distributed databases

(b) Object Relational features in SQL3

(c) Concurrency control techniques in distributed databases

(d) EXIST and NOT EXIST clause in SQL

(e) Compare ROBMS and ORDBMS.

Leave a Comment