Avr Reservation System
Passenger Reservation System
Problem Description
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.
Database Design
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
Problem Description
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.
Database Design
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