Advanced Database Management Systems - Dec 2011

Information Technology (Semester 5)

TOTAL MARKS: 80
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any three from the remaining questions.
(3) Assume data if required.
(4) Figures to the right indicate full marks.

This Qs paper appeared for ADBMS of Computer Engineering (Semester 5)

1(a) Construct an EER diagram for airline database. The airline database contains the 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 are 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 following information: the flight number, origin, destination, departure time and arrival time. Relevant assumptions, if required, can be made.(8 marks) 1(b) Convert above EER diagram into relational schema(7 marks) 1(c) Explain the terms unstructured data in XML and fragmentation in distributed database(5 marks) 2(a) Explain various parallel database architectures(10 marks) 2(b) Explain query processing in Distributed database system(10 marks) 3(a) Explain EER-to-relational model mapping(10 marks) 3(b) What is XML DTD?(10 marks) 4(a) What is heuristic rule in query optimization? Explain transformation rules(10 marks) 4(b) Explain nested loop join and block nested loop join algorithms in query processing.(10 marks) 5(a) Explain macro life cycle in database design methodology(10 marks) 5(b) What is equijoin, natural join, outer join, self join? Explain with suitable example.(10 marks) 6(a) Write SQL expressions considering the following relations with the given fields.

DEPT (Ono, Dname, Location, Numstaft)
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.

(10 marks) 6(b) Explain external merge sorting in query processing(10 marks)

7. Write detailed notes on (any two):

7(a) XML schema(10 marks) 7(b) Client Server Architecture(10 marks) 7(c) Object relational features in SQL3.(10 marks)