DATABASE MANAGEMENT SYSTEM PROJECT

ISM 7510 Online Term Project Note: Please do the projects in groups of 3 or 4. Please select your own team. Please submit each milestone via the digital drop box on blackboard. Please make sure that every milestone that you submit contains your group member names and unique names (email ids).

Once you have formed a group, you need to pick one of the four scenarios listed at the end of this document. All of your milestones will be based on the scenario that your group chooses.

The details of what you have to do for each part of the term project are described below.

Milestone 1: ER diagram 50 Points

Description

Your task for milestone 1 is to convert the situation description enclosed at the end of this document into an ER diagram. It should contain all entities and relationships with cardinality, existence, and optionality indicated. I do not expect artistic perfection, but I expect what you submit to be very easy to understand.

On another sheet of paper provide a list of all the tables represented in the ER diagram, in functional notation. This will give you a chance to specify which attributes go in which table, and where the foreign keys are, etc. This will give you the foundation for the next part of the project, where you implement the tables in Oracle.

What to complete for Milestone 1:

• One page containing your ER diagram • Another page containing the functional notation of all the tables in the ER diagram, the

primary key, the foreign keys, and other attributes in each entity. • A third page that lists any assumptions you needed to make to complete your ER diagram

Milestone 2: Table implementation 35 Points

Description

For the previous part of the project, you described a set of tables needed to represent the information in an ER diagram. In this part you will actually implement these tables in MySQL.

1

You only have to implement those tables that you described in functional notation of the previous part of the project.

You should create six files:

1. A command file to create the tables 2. A command file to delete the tables 3. A command file to create those indices that are necessary for each table 4. A command file to drop these indices 5. A command file to add one record to each table 6. A command file to delete all records from each table

When defining the tables, remember to include information about primary keys, foreign keys, uniqueness, and nullness. Annotate your ER diagram where necessary to ensure that I can tell the correspondence between the ER diagram and your SQL tables.

What to complete for Milestone 2:

• The six files • A print-out of the output of each of these six files, labeled accordingly (a screenshot of each output is fine as well, we will test this milestone by running the files).

Milestone 3: Reports and Queries 40 Points

Description

For this part of the project you are to implement queries that display data gathered from queries. You should define 5 important questions for your business, along with the queries necessary to gather the information for each report. The goal here is to ask interesting questions of your business and answer them using SQL queries.

What to complete for Milestone 3

• Your 5 SQL files (1 for each report) • A one-page write-up of the value of each of the reports you produced. The best way to

do this is by using a list numbered 1 to 5 that briefly defines the use and importance of each report. It should be just a sentence or two for each report.

• A print out showing the output for each report. A screenshot is fine. Each report should be formatted to look like a report. Be sure to write the report number and your group member names on the each page of the print-out

2

ISM 7510 Online Final Project Situations

General description

You will be a member of a group. Your group will be focused on one segment of the value chain. Your assignment is to develop a database application for one segment of the travel industry, including hotels, airlines, car rental agencies, and travel agents. You will be graded on how much value you add to your group (your group members will grade your performance), and how well your group does (I will grade this). Each group will pick one situation.

The Situation

Each organization has its own needs and concerns. Each also wants to have its own data. However, each also knows that it should share as much data as possible with other complementary firms in the travel industry. As a result, four firms are getting together on a joint project to hire several consulting firms to design and implement four separate database systems-- -one each for a hotel, an airline, a car rental company, and a travel agency---that are able to cooperate and share data.

1) Hotel

The hotel reserves rooms through both its own telephone operators and its connections to travel agencies. The hotel has several locations, each with its own employees (that aren't shared with other locations).

The hotel rooms can have different rates because they offer different amenities---different number of beds, with or without bathrooms, balconies, TVs, etc. Rooms are normally available to book for whole weeks (though sometimes rooms are booked for less than or more than a week), with different rates applying for any given room for different months in the year. A room's rate is determined by referencing both its "type" (e.g., "Deluxe Ocean View", "Standard") and the month. The "type" also specifies the amenities that are available with that room. Each hotel defines its own room types. Further, a room type (e.g., "Standard") at one hotel does not necessarily have the same amenities as a room with that same room type at another hotel. The booking "locks in" the price of the room for that customer---if the price changes for that room for that time period, the price for that customer does not change. Hotels routinely reserve rooms of a certain type for a certain day but delay assigning a customer to a particular room until a time closer to the actual check in date or until the customer supplies a credit card number.

The hotel has a "frequent guest" program that gives its members a percentage off their total bill (excluding tax). If the guest is a "gold" member, then the guest receives a 5% discount; if a "platinum", then it's 10%. These discount rates have changed in the last few years. Through these changes, the structure of the program has remained the same and is expected to remain

3

unchanged; discounts are expected to continue to be based on member class. The number of categories may change as might the discount percentages.

Employees of the airline get a 5% discount on top of any other discounts. For the guest to get this discount, the guest has to supply his or her employee number and currently be employed by the airline. Employment status should be verifiable through connection to the airline's database.

While guests are at the hotel, they have additional charges that are put on their bill. Two that we are concerned with here are those for phone calls and those for room service. The important information about a phone call is the time it was made, how long it was, to what phone number, whether it is local or long distance, and the appropriate charge. The important information about a room service charge is the receipt number, the time the initial room service request was made, and the charge amount.

The hotel likes to keep track of what its employees do. The hotel likes to note which employee handles the initial reservation, which employee handles the check in, which one takes a call for room service, and which one actually takes the food to the room.

When a reservation is made by a travel agency in connection with a tour package, the travel agency typically asks for a certain number of reservations to hold rooms but does not supply customer names (because the travel agency doesn't have reservations yet). As the travel agent gets an actual reservation for its tour package, it converts the package reservation that is holding a room type into a reservation for a person that reserves a specific room. The travel agency receives the bill for the room itself; the customer only pays for charges to the room.

2) Airline

The airline reserves seats through both its own telephone operators and its connections to travel agencies. A trip reservation involves one or more legs, with each leg consisting of one or more flights. For example, a trip from Detroit to El Paso, TX and back might consist of two legs: 1) Detroit to El Paso, and 2) El Paso to Detroit. The first leg could consist of two legs: 1) Detroit to Dallas on NW #515, and 2) Dallas to El Paso on AA Shuttle #42. "Leg" is a customer-oriented concept. The airline does not really care (for internal scheduling purposes) about legs. Customers care about legs because that is how they think about their travel plans: "I'm going to San Francisco, Phoenix, and then back to Detroit." Unlike the hotel charges, the cost is independent of the season though it does depend on whether the seat is economy, coach, business, or first class (or whatever classifications this particular airline may come up with in the future). The cost changes when the airline decides it needs to be changed.

Flight numbers get reused; that is, there might be a NW #515 every day --- but there is never more than one NW #515 on any particular day. A flight number is defined by the airport from which it takes off, the airport at which it terminates, and the time of its departure. When a flight is set up, an airplane of a particular type is assigned to it. At some later date an actual plane of that type is assigned to fly that route on that date. At some point a pilot, co-pilot, and flight crew are assigned to the flight. The flight crew is a group of flight attendants that always work

4

together (until reassignment to another crew). A flight attendant is never part of more than one crew at any time though sometimes they aren't assigned to any crews.

The airline has much information about its planes. The plane type is known (e.g., Boeing 707). This determines how many seats the plane has. Each seat has a different designation (e.g., 1A, 1B). Each seat can be of one or more types (e.g., coach, first class). Some seats (those near the divider) can have multiple possible types because the plane can be reconfigured while its on the ground preparing for take-off.

A reservation "locks in" the price of a flight for a person even if the price of the seat is changed. Assigning a seat involves knowing the particular reservation number, the specific flight that is being reserved, the seat that is being assigned, and the type that it will be on that particular flight.

The airline has a "frequent flier" program that gives its members a certain number of points for every flight they pay for with cash. These points are reimburseable for other flights. Flights within the Continental US or Europe or Asia cost 15000 points per leg, between Alaska or Hawaii and the Continental US or Asia cost 25000 per leg, while all others cost 30000 per leg. The points earned on a flight depend on the flight itself. Customers not only like to see the number of points they have earned but also the detailed activity in the account. Customers can only pay for a whole reservation with his or her points; he or she cannot pay for one leg with points and another with cash. Points are earned by the customer by "leg"; the airline may still set points that can be earned by flight number internally, but the customer will see points earned by leg on his/her program summary. Further, the points that can be earned on a flight can change at any time. The customer's account should show the number of points that were awarded at the time the flight was taken.

3) Car Rental Company

The car rental company rents cars through both its own telephone operators and its connections to travel agencies. A car rental has a pick-up and drop-off location. These locations are all known to the company because each one is a franchisee of the company. The company has daily and weekly rates, all of which vary depending on the class of the car (e.g., subcompact, compact, minivan). There is a maximum daily mileage allotment that does not depend on the class of the car though the company also allows rentals (at a higher price) to have "unlimited mileage". In addition to the "frequent renter" program described below, the company also gives an additional 5% discount to customers either whose employers have corporate accounts or whose employer is the airline. In the second case the car rental company must know the employee's employee id number and must know if he/she is a current employee. This second fact should be verified by the Internet link.

When a reservation is made, the company indicates that the rental is for a car of a certain type but does not assign any particular car. The reservation does not become firm until the day before the car is set to be rented if the company has the customer's credit card number at that time. If the company does not know it at that time, the reservation is cancelled. Once the reservation becomes firm, the reservation is assigned a specific car from the company's fleet.

5

Cars are kept in their inventory at the same locations where the cars are picked up and dropped off. Of course, cars aren't always in the locations they need to be. A customer might want to pick up a Ford Contour at location X while location X does not have any of these cars in stock. The company's task is to find where the nearest Ford Contour is and transport it to location X by the time it is needed. Failing this the company could rent out a car in a higher price range to the customer.

The car rental company has a "frequent renter" program that gives its members 6% off the total bill if it's less than $300 (not including tax), 9% off if it's between $300 and $500, and 12% off if it's more than $500. The discounts only take effect if the customer has rented a car in the last 60 days. As a promotional item, the company likes to send its frequent renters an annual statement showing how much they've saved in renting with them in the past year.

Rentals that are gathered by travel agencies work the same; however, the travel agency receives 1% of the total bill as a "finder's fee". This amount is added to the travel agency's account. Once a year on the travel agency's anniversary of association with the car rental company, each travel agent is sent a check covering this amount. With the check, the car rental company sends a list of rentals that the travel agency made for the company.

Travel agencies also make reservations for their packages. They usually start by putting "holds" on a bunch of cars (car types, actually) before the agency even knows the customers names. Then, as the agency books reservations for its packages, the agency converts the one of the holds into a firm reservation for a particular person. The person is not billed for this rental --- the travel agency receives the bill.

To track productivity of its employees, the company likes to keep track of which employee took a rental, which one checked out a car (that is, verified its mileage and condition), and which one checked in a car (the same).

4) Travel agency

The travel agency wishes to use a computer database to assist with their vacation package booking service. They put together their own vacation packages, each of which offers accommodations in hotels located in a number of towns or cities (the stops on the tour), transportation between the cities (bus, ferry, cruise ship, or train), and hotel and car reservations in whichever cities are needed. The package has a total price that is set by the travel agency and is not directly calculated from the price of the underlying components of the package (e.g., rental car or hotel room). The travel agency updates information about these packages daily, including current prices, new packages, changes to existing packages. The travel agency does not retain historical information about these packages---that is, no one can tell what the old price was or how it was originally configured.

The travel agency will also book flights to (and from) the origination (destination) of the package. The price of these flights are not included in the price of the package.

6

The travel agency wants the database to indicate the geographical areas in which each city is located, for example, the French Alps, French Riviera, Western Europe, Wisconsin Dells, Midwestern US, Southeast Asia, etc. This allows customers to request packages that go through one or more of these areas or hotels that are located in one of these areas.

The travel agency offers complementary services in which they will also reserve hotel rooms, airline seats, or rental cars. Each company will maintain its own copy of the reservation but should keep no more information than it needs to track the reservation (the travel agency) or to manage its reservations (the other companies). The travel agency does not offer the ability to search its reservations (e.g., find the cheapest hotel room in Michigan). It merely offers the ability to make a reservation with a particular company (e.g., reserve a car with Hertz in Chicago on August 3, or reserve a car with Avis in a particular city on some dates, or get a hotel room at the Days Inn in South Bend during the first weekend in September). Of course, these hotels and airlines and car rental companies have connections to other travel agencies as well.

The hotel rooms can have different rates because they offer different amenities---different number of beds, with or without bathrooms, balconies, TVs, etc. The amenities available depend on the particular hotel in the particular city the customer is interested in. Rooms are normally available to book for whole weeks (though sometimes rooms are booked for less than or more than a week), with different rates applying for any given room for different months in the year. A room's rate is determined by referencing both its "type" (e.g., "Deluxe Ocean View", "Standard") and the month. The "type" also specifies the amenities that are available with that room. Each hotel defines its own room types. The booking "locks in" the price of the room for that customer- --if the price changes for that room for that time period, the price for that customer does not change. Hotels routinely reserve rooms of a certain type for a certain day but delay assigning a customer to a particular room until a time closer to the actual check in date. When the hotel assigns the room to a customer, the hotel sends this information via the Internet link to the travel agency.

To assist their repeat customers, the agency also keeps track of their customers' preferences--- e.g., does the customer prefer an aisle or window seat, smoking or non-smoking room.

The agency likes to keep track of the productivity of their agents. To assist in this, the agency likes to track which employees handles any of the reservations (package, car, etc.).

7