Passenger Reservation System
Rutgers Transit is a passenger transportation company. They are located in Cleveland and provide several bus lines that go to a number of destinations. The company currently has about 1,000 buses spread over 60 routes. Of these, 700 are regular, 200 are semi-luxury, and the remaining are super deluxe buses. The seating capacities are 48, 42, and 36, respectively. Receiving reservations in advance facilitates the planning process. For this reason, the company is encouraging reservations of at least one week in advance by offering better prices.
As the business grew, the company faced difficulties in managing passenger reservations. The managers decided to computerize the reservation system. For this purpose they built a database that keeps detailed records of the buses owned, routes offered, passenger reservations, etc. They are now in the process of building a database decision support system that will facilitate the process of managing passenger reservations.
We present the main entity types of this database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. Identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the relations created, indicate its normal form. If the relation is not in the 3NF, decompose it into 3NF relations.
1. Bus: The main attributes are identification number, brand name, capacity, category, descriptions, unit cost, etc.
2. Passenger: The main attributes are identification number, name, address, e-mail, birthday, current balance, etc.
3. Route: The main attributes are identification number, origin, destination, starting time and day (of the week), arrival time and day, duration, price, etc.
4. Trip: The main attributes are identification number, origin, destination, starting time and day, arrival time and day, duration, price, etc.
Note the following: (a) When a passenger makes a reservation, the following information is recorded: number of seats reserved, special concession required, etc. If seats are available, seats of the passenger’s choice are allotted. However, if seats are not available, the system provides alternatives to help the passenger make a decision. (b) A route consists of a number of trips. For example, the route Cleveland-Miami consists of the following trips: Cleveland-Cincinnati, Cincinnati-Atlanta, Atlanta-Jacksonville, Jacksonville-Orlando, and Orlando-Miami; (c) Passengers get a discount for reservations made at least one week in advance. (d) Students and seniors get a 50% discount. (e) Employees and their eligible family members may travel for free once a year for a maximum of 3,000 miles. (f) A passenger reserves as many seats as the number of family members that are traveling. The system should keep detailed information about the passenger’s dependents.
Access Application Development
The following are some of the queries, forms, and reports one can create in order to increase the functionality of the database:
1. The following set of queries facilitates the passenger reservation process:
a. List the route numbers that make a stop at the destination requested by the passenger. This destination could be the final or an intermediate point of the route.
b. List the total number of available seats for a particular route on a particular date.
c. Present the earliest date on which a certain number of seats are available for a particular destination on a particular route.
d. Present the cost of travel by various bus...
Please join StudyMode to read the full document