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

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

                                                             (3 Hours)                                      [Total Marks :100]

Q.1 (a) Construct an EER diagram for the airline database. The airline database contains
             information about passengers, flights, departure, employees and aircrafts. For passengers
             name, address, phone number and related flight information is recorded. For employees
             name, address, salary an identification number and flight information is recorded. Not all
             employees can fly aircraft; just the pilots. For such employees their qualifications i.e. what
             kind of planes they can fly is recorded. For planes the model number and make is
             recorded. The airline has many aircrafts of a certain type. For flights it is required to keep
             the following information: the flight number, origin, destination, departure time and arrival
             time. Note that for the same source destination, there can be many flights per week.
             Relevant assumptions if required, can be made. [8 Marks]
     (b) Convert above EER diagram into relational schema. [7 Marks]
     (c) Explain the terms unstructured data in XML and fragmentation in distributed
          database. [5 Marks]

Q.2 (a) Explain various parallel database architectures. [10 Marks]
       (b) Explain query processing in Distributed database system. [10 Marks]

Q.3 (a) Explain EER-to-relational model mapping. [10 Marks]
       (b) What is XML DTD? [10 Marks]

Q.4 (a)What is heuristic rule in query optimization? Explain transformation rules. [10 Marks]
       (b) Explain nested loop join and block nested loop join algorithms in query
             processing. [10 Marks] 

Q.5 (a) Explain macro life cycle in database design methodology. [10 Marks]
       (b) What is euijoin, natural join, outer join, self join? Explain with suitable example. [10 Marks]

Q.6 (a) Write SQL expression considering the following relations with the given fields. [10 Marks]
              DEPT (Dno, Dname, Location, Numstaff)
              EMP (Eno, Ename, Salary, Supno, Dno)
              WORKS (Eno, Pno, Role)
              PROJ (Pno, Pname, Ptype, Budget)
              i) Get the number of employees having salary more than Rs. 50,000/- and working on more
                 than 1 project.
             ii) List the employees working on more than 2 projects.
            iii) List the projects on which more than 7 employees are working.
            iv) Find the names and budgets of projects which have more than 7 employees working
                 on them.
       (b) Explain external merge sorting in query processing. [10 Marks]

Q.7 Write detailed notes on (any two): [20 Marks]
        i) XML schema
       ii) Client server architecture
      iii) Object relational features in SQL3.