Question 1)a) With essential examples, define the following:
i) Super Key
ii) Primary Key
b) How the EER diagram can be reduced to relations? Describe.
c) Consider the following relational schema Hotel(hotelNo, hotelName, city) Room(roomNo, hotelNo, type, price) Booking(BookingID, hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest(guestNo, guestName, guestAddress) Write the following queries in SQL.
i) Write the names of hotels located in Bangalore.
ii) List the no. of rooms currently available in each hotel.
iii) Find the roomNo. that is booked for longest period of time(need not be contiguous) by one or more customers in Hotel H10.
Question 2)a) What is Nesting? Demonstrate the usage of collect function and sub queries in nesting.
b) Briefly describe the various approaches proposed to make the objects persistent.
c) Consider a database schema with a relation Dept whose attributes are as shown below, with types specified for multivalued attributes.
Dept = (Dname, MgrID, EmployeeSet multiset(Employee), ProjectSet multiset(Project)) Employee = (EmpID, Ename, BirthDate, Salary, SkillSet multiset(Skill)) Project = (ProjID, PName, StartDate, Budget) Skill = (Type, Grade)
i) Define the above schema in SQL:2003, with appropriate types for each attribute
ii) Using the above schema, write the following queries in SQL:2003.
iii)Determine the names of all employees who have born after 1-1- 1970.
iv) List all the skill types possessed by the employees.
v) List all the employees who have ‘A’ grade in any of the skills.
Question 3)a) Briefly describe the following in the context of Data Servers:
i) Locking
ii) Data Caching
iii) Lock Caching
b) Describe the different partitioning techniques used for I/O parallelism. Which of these techniques are appropriate for point queries? Why?
c) Explain fragment and replicate, and asymmetric fragment and replicate join methods for non-equijoin conditions.