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 da