CSE3330 Project #1

Tuesday October 4, before midnight
Worth 4% of the final grade


Description

This project must be done individually. No copying is permitted. This is the first project from a series of three. You are going to build a database application in three stages:

Platform

You need to use phpMyAdmin to manage your database from a web browser. Open https://omega.uta.edu/myadmin/ in a web browser. You should be prompted for a username and password. Enter the username and password you use to log in to your MySQL database. If you require assistance with phpMyAdmin, please contact the OIT Computing Helpdesk. There is online documentation available from this web page. On the left-side menu, select the database under your user name. Then you can create new tables by specifying the table name and number of rows. Recall that each table must have exactly one primary key, which may consist of more than one attributes. To specify that an attribute is a foreign key, you go to the Structure menu of the table and select "Relation view". Then you edit the Internal Relations, which are basically the foreign keys.

Domain

Best Travel is a new travel agency that is being set up with the objective of providing the best services to its customers by keeping at the leading edge of technology. Thus, they would not only like to provide the traditional travel agency services, but they also keep detailed records on their customers and their preferences in order to customize their services to the clients' needs and preferences. You have been hired to set-up an information system to assist Best Travel achieve its goals.

The travel agency keeps records about a number of items. First and foremost, it keeps track of the airports in the system which are uniquely identified by their international codes (for example, for Dallas/Fort Worth airport the code is DFW). The name of the airport, its location (city and country) and its phone number are other essential information. There is information about the airlines (also called carriers in the business) that are also kept by the travel agency. The more important information about the airlines that are stored are their unique names, their addresses, the contact phone number.

One of the more important pieces of information that needs to be maintained is that related to flights. There is a need to know the carrier, the flight number, the departure and arrival (destination) airports, the departure and arrival times and the schedule (weekdays) for a given flight. One complication that is noticed during requirements analysis is that different types of airplanes may be assigned to the same flight on different weekdays. Thus, the number of available seats for each flight (both at the business and the economy class) change. Furthermore, the fare seems to change for different days (weekends are cheaper than weekdays) Therefore, there may be a need to keep track of these flight "instances" in addition to the more generic flight information. An airplane may be assigned to several flight instances (of course as long as their times do not conflict, but we are not concerned with this scheduling problem; that is the responsibility of the airlines). A flight instance, on the other hand, uses only one airplane. Needless to say, each flight has only one departure and arrival airport but each airport has many incoming and outgoing flights.

Since different airplanes may be assigned to a given flight on different days, customers have a tendency to ask what type of plane flies at a particular day. So, airplane information is also kept by the travel agency. The typical information that is maintained are the airplane identity (BOEING, AIRBUS, etc.), type, what type of an airplane it is (e.g., jet vs turboprop), the (business and economy class) capacity of the plane, and other physical information such as maximum range, length, etc. that some strange customers may ask for.

An essential piece of the data that Best Travel wants to maintain is data about its customers. This passenger information includes the name, address, telephone number, the set of frequent flyer memberships that the customer has (if any), and the preferred payment method (e.g., credit cards and their numbers).

When a customer makes a reservation, it should be kept in the company's database. The reservation record should identify the customer, the travel agent who made the reservation, the flights that are part of the same reservation (e.g., a flight from Dallas to Denver and a flight from Denver to San Fransisco), the payment method for this reservation and the status of the reservation. Typically, a reservation is either OK'ed which means it is confirmed and a seat is reserved for the customer, or the reservation is on a waiting list. Note that it is possible for one flight leg on a reservation to be OK'ed and another flight leg to be on the waiting list.

Requirements

Create your Best Travel database using phpMyAdmin based on the following schema. Feel free to add more tables and/or attributes if you like.

Customer ( cust_ID, name, address, phone )
Carrier ( carrier_name, address, phone )
Frequent_flyer ( carrier_name, number, cust_ID )
Credit_card ( card_number, cust_ID, type, bank, expiration_date )
Airport ( code, name, country, city, phone )
Airplane_type ( make, model, type, business_capacity, economy_capacity, range, weight, length, wingspan )
Airplane ( airplane_ID, make, model )
Flight ( carrier_name, number, schedule, departure_airport, departure_time, arrival_airport, arrival_time )
Flight_instance ( carrier_name, number, date, airplane_ID, business_fare, economy_fare, available_business_seats, available_economy_seats )
Reservation ( res_ID, cust_ID, travel_agent, card_number )
Reservation_flight ( res_ID, carrier_name, number, date, seat, status )
In this schema, the foreign keys are:
Frequent_flyer.cust_ID -> Customer
Credit_card.cust_ID -> Customer
Airplane[make,model] -> Airplane_type
Flight.departure_airport -> Airport
Flight.arrival_airport -> Airport
Flight_instance[carrier_name,number] -> Flight
Flight_instance.airplane_ID  -> Airplane
Reservation.cust_ID -> Customer
Reservation.card_number -> Credit_card
Reservation_flight.res_ID -> Reservation
Reservation_flight[carrier_name,number,date] -> Flight_instance

Then insert some data into the tables. Each table should contain at least four rows. Try to write your data in such way that you can get meaningful answers for various queries (such as, give me the flight number of a certain reservation). You should declare all keys and foreign keys in your schema.

What to Submit

You must handin your relational schema as follows. From the phpMyAdmin, select your database and then select Export. Note that this exports the entire database, not a single table. From the Export form, check the Relations box at the "Add into comments" menu and the "Save as file" box at the bottom. Then push Go and save your file (the filename should be your username .sql). Then you use the following form to submit this file electronically. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated.

Submit Project #1:

Last modified: 09/20/11 by Leonidas Fegaras