LuxComfort Bus (LCB) operates a bus company specializing in freeway travel between major cities in California, Oregon, Washington, Utah, Nevada, and Arizona. The firm needs to create a bus ticket reservation system. Background The owner of LCB 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 to support the system. In an interview with the owner, you found the following information about the expected system. 1. LCB currently has a fleet of 100 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. LCB operates 10 routes now (e.g., San Francisco-to-Los Angeles), but it can add more routes if its business succeeds. 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 expiration 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 10/01 at 9 AM) on a specific date and time based on various factors (e.g., supply and demand). For example, the bus 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. 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 book 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 (Route trip: a bus trip on a specific route). 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 assigned to it. For example, the same SF-LA route (depart at 9 AM) can be assigned to different route trips (10/31, 11/10) A driver can be assigned to many route trips, whereas a route trip can 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 or www.flixbus.com to experience how ticket booking works, although the scope and assumptions of this project are different. Tasks Based on the background described above, complete the following tasks. 1. Create an entity-relationship diagram (ERD) that models the LCB 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 2. Use your ERD as a blueprint and create a database in Microsoft Access a. Create all tables and relationships 3. 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