last updated: March 29, 4:44p
due date: electronic submission
Monday, April 23, 5p; submit hard copy in Tuesday 24, 2000 in class
Problem 1 (Database Creation and Schema Consolidation):
Based on the
results obtained in Project1 by your group members, define and create a single
relational database for the Hotel Management application of Assignment1, and
fill the database with "realistic data" (at least 12 tuples 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. Moreover, submit an E/R diagram that
provides a higher level view of the relational database you designed.
Problem 2 (Check-based Constraints in Oracle):
Add two check
constraints to the Oracle Library Schema that do the following: Check that
prices for single or double rooms are never higher than $200. Check if persons
do not have more than two reservations for different hotels for the same day (if
this is the case the third reservation would be rejected).
Show the revised schema, its successful declaration, and the response of Oracle to inserts that violate the two constraints.
Problem 3 (Views):
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
that each view definition provides for the hotel management application.
Problem 4 (Triggers):
Write ORACLE triggers that updates one of the
derived attributes in the hotel management database when updates (insertions,
deletions, modifications) in the hotel management database occur. Write a second
trigger that prints a message (or, alternatively, puts a tuple into a "red-flag"
table) if a customers charges exceed more than $1000 for a particular stay. 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 three cases, but not in the last one.
Problem 5 (PL/SQL Functions and Procedures):
Define a stays-in-operator (using PL/SQL stored functions and
procedures) that takes a day, and person, and a hotel-id as its input, and
returns true in the case that the particular person stays (or is scheduled to
stay) in the particular hotel for that day. You can assume that persons that use
this operator will only use the present date or future dates when using this
operator.
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.
Submission List:
Hard copy should include execution results if applicable. For electronic submission, send code as attached files to an email to yilin@bayou.uh.edu. Use text only format.