Comfort Bus (CB) operates a bus company that specializes in traveling on freeways between major cities in California, Oregon, Washington, and Nevada states. The firm needs to create a passenger reservation system.
Background
The owner of CB wants to provide better service to customers. He decided to hire a professional to develop a passenger booking system. When the system is online, the customers can check the bus routes and schedule, book, or cancel their bookings from their computers or phones. The company can also run reports to show its sales and generate information to improve its operations. You are hired to help the owner design a database for supporting the system. In an interview with the owner, you found the following information about the expected system.

1. CB currently has a fleet of 50 buses. The capacity of passenger seats depends on the bus model. The system must track the bus make, model, year, seat capacity, vehicle ID, and license plate.
2. CB operates 10 routes now (e.g., San Francisco-to-Los Angeles), but it can add more routes in the future if its business goes well. Each route specifies a unique route number, departure, destination, departure time, and arrival time.
3. The system also needs to maintain the drivers’ data, so they can be appointed to drive a route trip on a specific date. A driver’s data must include the first name, last name, date of birth, gender, driver’s license number, license expired date, and hire date.
4. A route trip captures data about which route, bus, trip date, and trip price. The trip price is dynamic. The company will set the price for a route (e.g., SF to LA on 5/01 at 9 AM) on a specific date and time based on various factors (e.g., supply and demand). For example, the bus route trips on the same route, SF-to-LA, but on different dates may have different prices. Like buying an air ticket for an SF-to-LA flight, you may see a different price when choosing a different date. The trip price determines how much a bus ticket costs if the route trip is related to a bus ticket.
5. The system needs passengers’ names and emails to deliver e-tickets and receipts when they make a booking (Note: only the passenger who makes the booking).
6. The system records the booking date and which passenger makes the booking in each booking instance.
7. Each booking must include at least one bus ticket up to many bus tickets. For example, John can make a booking of three bus tickets for his family.
8. The system assigns a unique ticket number for each bus ticket and records which booking and route trip are related to it, so the system knows which booking has the ticket and which route trip is assigned to the ticket. With the route trip data related to a bus ticket, the system can retrieve the route, trip date, and trip price and show them on a bus ticket.
9. For each bus ticket, the system can also record the passenger’s name and his/her special need (e.g., no special need, wheelchair, hearing impaired, sight impaired). These special needs can be predefined in the system and assigned to tickets.
10. Here are some more essential rules.
    • A bus can be assigned to many route trips, whereas a route trip must have only
    one bus assigned to it
    • A route can be assigned to many route trips, whereas a route trip must have only
    one route. For example, the same SF-LA route (depart at 9 AM) can be used in
    different route trips (5/01, 5/10)
    • A driver can be assigned to many route trips, whereas a route trip can also have
    multiple drivers. It is common to have two drivers who take turns to drive a
    long-distance trip). Hint: an associative entity is needed between a Many-to-
    Many relationship
11. Visit www.greyhound.com to experience how a ticket booking works, although the
    scope and assumptions of this project are different.
    Tasks
    Based on the background described above, complete the following tasks.
12. Create an entity-relationship diagram (ERD) that models the CB ticket booking system
    a. Use LucidChart or a similar tool to create the ERD
    b. Use proper notations on the ERD
    a) Entities (each entity must have a name)
    b) Attributes (use the camel style, e.g., PassengerID, RouteTripNum)
    c) Primary keys (use underline)
    d) Relationships with cardinalities
13. Use your ERD as a blueprint and create a database in Microsoft Access
    a. Create all tables and relationships
14. Populate some sample data in your database tables
    a. Create at least three passengers
    b. Create at least five bookings
    c. Create at least five bus tickets
    d. Create at least five route trips
    e. Create at least five routes
    f. Create at least three buses
    g. Create at least three drivers