Wednesday, October 9, 2013

Advanced Databases assessment 1(October 2013)

Answer all questions
Part A(3 X 2 = 6)
1) Give the importance of aggregation in ER model.
2)What do you mean by an insertion anomaly in normalization?
3)State objective of distributed query processing using the semi join operation.

Part B(9 + 10 = 19)
4) Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course:
student(SSN,Name,Major,Bdate)
course(course#,Cname,Dept)
Enroll(SSN,Course#,Semester,Grade)
Book_Adoption(course#,Semester,Book_ISBN)
Text(Book_ISBN,Book_Title,Publisher,Author)

Draw an Extended ER diagram and convert in to relational schema diagram with all integrity constraints.Normalize the database upto 3NF.(9)

5)Using Hotel schema given below,draw an initial relational algebra query tree for the query and use the heuristic rules to optimize that tree into a more efficient final query tree.Discuss each step and state any transformation rules used in the process.

Hotel Schema:
Hotel (hotelNo,hotelName,city)
Room (roomNo,hotelNo,type,price)
Booking (hotelNo,guestNo,datefrom,daateTo,roomNo)
Guest (guestNo,guestName,guestAddress)

query
Select g.guestNo,g.guestName
from Room r,Hotel h,Booking b,Guest g
where h.hotelNo=b.hotelNo AND g.guestNo= b.guestNo AND
h.hotelNo = r.hotelNo AND h.hotelName = 'GPH' AND
dateFrom >= '1-Jan-13' AND dateTo <= '31-June-13'        (10)

 (OR)

6) Do all types of fragmentation using TEXT relation in Q.No 4              (10)