Unformatted text preview:

Last updated: November 24, 2003Relational Database Design; Embedded and Form Interfaces to Databases COSC 3480 Lab5+6Last updated: November 24, 2003Lab.# 5 (total score =110, Draft Report due Th., November 20, 2003 in lab)The task is to design a relational flight reservation database; this database will be used in Lab.#6 to developSQL-form/PRO*C interfaces to query and update the designed database. The database to be designed has tosatisfy the following information requirements:Customers that are characterized by ssn (unique), name, and address make reservations for flight connections. Inthis application, flight connections are limited to using (legs of) at most 3 different flights. Each flight ischaracterized by a unique flight number and can have up to three legs; each leg represents a direct connectionfrom one airport to another airport and is characterized by a start time and arrival time. A maximum number ofseats is associated with a flight, and for each leg of a flight is has to be known how many seat are currentlyavailable. Moreover, a price is associated with a flight connection. Flight connections as well as legs of flightsmust be connected (e.g. flying from A to B and then from C to E is not a flight connection, whereas flying fromA to B and then from B to C is). Moreover, temporal constraints must hold with respect to flight connections andwith respect to legs of a flight in that the arrival time has to be at least 15 minutes before the departure time ofthe next leg of the flight or flight connection. Customers typically request to go from city A to city B and want tofind all connections that are available from city A to city and their price. The price of a flight is computed asfollows: a price (in dollar) is associated with each leg of a flight; the price of a flight connection is computed byadding the prices of the legs of the flight connection; however, connections that involve 3 legs are discounted by10%, connections that involve 4 legs are discounted by 30%, and connection that involve five or more legs arediscounted by 50%. To simplify the project we are only interested in reservations for a single day, and timesdisplayed refer to Houston time. If a customer requests a reservation for a flight connection either all legs of thatconnection are reserved or the request is rejected, if there is at least one leg for which no space is available.Moreover, a unique reservation number is associated with the successful reservation of a customer.Remark: You are allowed to define additional attributes for the database to facilitate programming (e.g. sequencenumbers).Project5 Tasks include:1. Design an E/R Schema for the flight reservation database2. Map the E/R Schema to a “good” relational schema3. Create tables for the relational schema and fill it (preferably using the Oracle Bulk Loader (http://www-db.stanford.edu/~ullman/fcdb/oracle/or-load.html)) with useful data including all the flights andreservations that can be found on the last page of this document.4. Write a small program that asks the user to input a start city and an end-city and returns all connectionsgoing from city1 to city2 having to change the aircraft at most once (even connections that involvebooked legs of flights should be included in your answer).Deliverables: Submit a draft report at the due date that explains the E/R diagram you designed and howthe relational schema was obtained. Moreover, also briefly explain your approach to solve task4.Relational Database Design; Embedded and Form Interfaces to Databases COSC 3480 Lab5+6Lab.# 6 (total score =180, Report due: Th., December 4 in class; program demos on Dec. 2/8/9)The goal of this project is to define a “user friendly”, interactive application program front end to the flight reservation database using PRO*C and Oracle forms (alternatively you can use the JDBC Java interface with Oracle forms or Oracle web-interfaces, but using any of these interfaces will not be discussed in the lab; students that use the web interface successfully will receive extra credit). In summary, the interface you develop uses C/C++, embedded SQL and one of the many form tools Oracles provides.The program that you develop operates on the database you created in the previous project and provides interactive support for the following 8 activities:1. Insert a new flight (flights are limited to at most 2 stops (3 legs))2. List all flights that descent from a given city giving the flight number, the departure time and the cities served with arrival time; also indicate which legs of the flight are booked.3. Insert a new customer4. List all available1 connections (with at most 2 plane changes) between city1 and city2 and their associated price5. Select a displayed connection and reserve the associated legs (flights) of that connection6. List all reservations of a customer; support cancellation of a listed reservation 7. Remove a flight from the database using its flight number8. Terminate Session (should also remove all tuples from the database)Before the user chooses one (or several) of the 8 activities from your user-interface, your program should load the database you created in Project 5. In general, this project is defined more openly (you have more freedom in deciding how to solve a particular problem), and might require some reading and trial-and-error on your own. Moreover, if you like to use other features of ORACLE for this project, feel free to ask for a permission to do so. You should also be prepared to demo your system in the last week of the semester (first week of December). Hints: Before implementing particular queries in your embedded SQL program, it might be a good idea to test those queries using SQL*PLUS with the database you created in the previous project. Moreover, center on getting the PRO*C part running first (approx. 140-150 points are allocated for this part), and then provide a “simple” or a “very fancy” user interface to your program, depending on how much time you have left. Also if you have problems completing the project, make sure that your program solves at least some subtasks correctly, in which case you will get credit for those subtasks. Programs that are not running at all will receive a very low score.Submission guidelines and deliverables: Submit a report that addresses the following:1. E/R diagram and changes made to project#5 E/R with explanation.2. Relational


View Full Document

UH COSC 3480 - COSC 3480 Lab5+6

Download COSC 3480 Lab5+6
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view COSC 3480 Lab5+6 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view COSC 3480 Lab5+6 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?