Project2 COSC 6340:
Using Oracle and PL/SQL
for the Hotel Management Database

last updated: April 7, 4:44p
due date: Tuesday, April 11, 2000 in class

Problem 1 (Database Creation) --- Group Part:
Define and create a relational database for the Hotel Management application of Assignment1, and fill the database with "realistic data" (at least 12 tupels in each relation). Also define primary keys, and foreign keys in the relational schema. Additionally, define at least 2 derived attributes for the created database (e.g. number of reservations per day per hotel) --- if your 'original' relational schema already contains two derived attributes, you do not need to define additional derived attributes.

Problem 2 (Views) --- Individual Task:
Define two useful views for the hotel management database that facilitate querying the database. Do not only submit the definition of your views, but also discuss (in a paragraph or two) the benefits each view definition provides for the hotel management application.

Problem 3 (Cursors, etc...) --- Group Task:
Write three PL/SQL programs (See the PL/SQL Guide) to perform operations on your hotel management database. Each should be nontrivial, illustrating a feature or features such as local variables, multiple SQL statements, loops, and branches. At least one of the programs should involve a cursor. We encourage you to be imaginative (more "powerful" examples will receive higher scores). However, here are some sorts of things you might try if you can't think of something more interesting:

a)
Create a new relation and load it with values computed from one or more existing relations.
b)
Enforce a constraint by searching your database for violations and fixing them in some way.

Hand in a listing of your programs and scripts showing them working. You should demonstrate that the programs had their intended effect by querying (before and after) some relation of the hotel management database that was changed by the program. These queries may be included in the file that holds your PL/SQL programs for convenience. Document each program, explaining its task and how the task was accomplished, and evaluate the suitability of PL/SQL to automate this task.

Problem 4 (Triggers) --- Individual Task:
Write ORACLE triggers that update one of the derived attributes in the hotel management database when updates (insertions, deletions, modifications) in the hotel management database occur.

For the trigger program hand in your code and a script showing the triggers declared. Also, the script should show, for each trigger, the effect of four database modifications. Three modifications should activate the trigger, and the fourth one not. Show in the script queries that demonstrate that the trigger has an effect in the first case and not in the second.

Problem 5 (PL/SQL Functions and Procedures) --- Individual task:
Define a room-available-operator (using PL/SQL stored functions and procedures) that takes a day, and room-category, and a hotel-id as its input, and returns true in the case that a room of the given room type is available (and false if this is not the case).

Hand in listings of your code and scripts showing them called at least once each. Also, show in the script the results of queries that demonstrate the functions have had their intended effect. Also explain in a paragraph how the operator facilitates the working with the hotel management database.

Note: You should turn in just one copy for the group tasks (problem 1 and 3). For individual parts (problem 2,4,5), everybody should turn in a copy of their individual work (which should be different from everybody else's).