University of Delhi 2010 M.C.A 2nd yr4th DATABACE APPLICATIONS UNIVERSITY - Question Paper
$138 Your RoU No .........................
This question paper contains 4 printed pages.
Paper MCA-405 DATABASE APPLICATIONS
(Admissions of 2008 and before)
Tune : 3 hours Maximum Marks : 60
(Write your Roll No. on the top immediately on receipt of this question paper.)
Attempt ail questions.
A database is maintained for several hotels, and the bookings in respective hotels.
Given the following relations for a Hotel Database
a. Hotel (Hotel no. Name, Address)
Hotel numbers must be between 100 and 1000
b Room (Room no. Hotel no. Type, Price)
Type of room can be can be single, double, or family,
Price (room rent for a day) must be between Rs500 00 and RslOOO 00,
Roomno must be between 1 and 100
tl
c. Booking (Hotel no. Guest No. Date From. Date_To. RoomNo)
Date Jrom must be less or equal to Date To and both dates should be more than today's date
d. Guest (Guest No. Name, Address)
Guest numbers should be greater than zero
The underlined data items given above are primary keys.
1. Write Create Tab)e, statements for the above relations. Include all possible appropriate constraints for all tables. 5
2. Create a view containing the hotel name and the names of guests staying at the hotel.
2
P.XO.
3. Assume there are users MANAGER and SECRETARY, Give these two users full access to this view, with the privilege to pass the access on to other users. Give the user ACCOUNTS, a SELECT access to the view. 2
*
4. Give syntax for finding access privileges on objects granted to users. Revoke the access from the user ACCOUNTS. 2
5. Give syntax for creating sequences and explain how to find current value of a sequence. Assume that Hotel numbers start from 100 and may go upto 1000. Create a sequence hotel_no_sequence and use it to insert a row in the table Hotel. 2
6. Write a PL/SQL procedure that prompts a user to enter guest details and inserts a record into the Guest table. If the guest already exists in the database then exception should be raised with appropriate error message. 3
7. Suppose you use the exception When others in a PL/SQL subprogram, and you want to find out the reason for the exception, how will you interpret the error message?
1
8. Write a PL/SQL function that takes two arguments viz the rate of price increase (e.g. 5%) for a room and the room type, and updates the ROOM table. If this price increase is more than 10%, the function should raise user-defined exception toojiighincrease. 3
9. Create a trigger in PL/SQL that will be invoked when the price of the room in the Room table is updated with price less than the price already stored in the table. In this case, Room table should not get updated and an appropriate message must be printed.
# 3
10. There are following two constraints on booking a room:
The same room cannot be doubly booked and The same guest cannot have overlapping bookings Create a trigger in PL/SQL which gets invoked when a room is being booked for a guest which ensures that both the above constraints are not violated.
11. The owners of the company maintaining the Hotel Database, want to know whether they should use Object Relational features of Oracle for their database or should continue to use only the relational features. Give your opinion in favour and against using the Object relational features in maintaining Hotel Database. %
12. Convert the Hotel Database schema given above into Obfect Relational Database schema so that Object Relational features of Oracle DBMS can be utilized. Do the following changes to the relational schema:
a) Create type room t with attributes Room no> Hofei no. Type, and Price. *
b) Create type telephone of varray type which can contain maximum four telephone numbers 1
c) Create type hotel J with attributes Hotel no. Name, Address, and nested table for rooms, varray for telephones. 2
d) Create table, Hotel of type hotel t
1
e) Insert one row for hoteljio 110, with three rooms and two telephone numbers. J
f) Write a query for finding ail details of all rooms in a specified hoteljio.(say hotel jio =110). Show the output with column names 2
g) Write a query for finding all telephone numbers of a specified hotel no.
1
h) For a hotel with specified hotel no, it is required to add one new room to it Write a query for this pupose. 2
1
i) Create type guest_t with attributes guest no. name, address
j) Create table guests of type guest j 1
1
k) Insert one row into guests table. Assume your own data.
I) Create type booking with attributes hotel no. guest no. date from, date to, room_no, reference to guest type and reference to hotel type. 2
m) Create table bookings of booking t
1
n) Insert one row into bookings table with your own data.
o) Given the hotel no and guestjio, print the hotel name and guest name without using join in the query. Show the output. , 2
13. What type of statistics should be stored by DBMS to be able to derive estimates of relational algebra operations? 2
14. Give the heuristics rules normally used during quay processing and optimization. 3
15. Using the hotel schema, draw a relational algebra tree for the following query and heuristic rules to transform the query into a more efficient form. 5
SELECT r.roomno, r.Type, r.Price FROM Room r, Booking b, Hotel h WHERE r.Roomno * b.Roomno
AND b.Hoteljno = h.Hotel no
AND h.Name Taj AND r.price > 500,
State the transformation rules used in each step.
|
Attachment: |
| Earning: Approval pending. |
