Make a Conceptual Data Model (using Draw.io or Visio), containing entities and their relationships as they exist
Question:
Make a Conceptual Data Model (using Draw.io or Visio), containing entities and their relationships as they exist in the problem domain (including any M:M relationships)
2. Please make a Physical (Logical) Data Model using MySQL Workbench® based on the conceptual model.
Project Synopsis:
Your team has been hired by ABC Bus Transit Company to create a database that would store information about their buses, drivers, routes, trips, passengers, and company staff etc.
The business case description for ABC Bus Transit ® Company follows:
ABC Bus Transit Company offers trips to the public between major Canadian cities. Registered Passengers can book tickets online, by paying online either with a credit card or PayPal, for their upcoming bus trips from ABC's website. Passengers can register by creating an account free of cost by visiting ABC's website. Passenger can login and can see their past and future trips with ticket prices. Passengers can also cancel a trip up to 2 hours before the departure. Passengers can also buy tickets from major bus stops either through vending machines or from the ticket booths at some major stops (big cities, terminal stations etc.)
Comp1168-Group Project Page 2 of 9
ABC has 27 different routes with a start and end city for each route. Each route is identified by a unique Route Id (i.e. Route No 12) and has a start and end stop with a number of intermediate stops. The frequency of buses that operate on these routes is different. Some routes only have one or two buses each day whereas some popular routes have multiple buses operating at different times. There are some routes that are either seasonal (only operate in summer months for example) or operate on weekends only.
The scheduling department, headed by a manager, is responsible for assigning a bus (vehicle) and a company driver to a trip. A trip is defined as a bus operating between any two stops on a specific route. Each route has a number of intermediate stops and the database would store the street address for each stop.
The buses are also sent to a company workshop for routine maintenance/oil changes after running for 5000 Kms or once a month (whichever comes first) and stay in the workshop for a day (unless the workshop Manager decides to keep the bus for a longer time due to some defect identification). If a bus ever breaks down (due to some defect) on the road, the driver would inform his/her supervisor and the supervisor then contacts the workshop manager who sends a mobile team of mechanics to fix it on spot or take it to the nearest workshop. The database would keep a track of buses' history in terms of their unique bus ids, license plates, year of manufacture, operational history (what route, distance the bus covered on each day) , any down time (the time a bus remains idle at a terminal stop)
Comp1168-Group Project Page 3 of 9
Drivers should be able to see their schedule for the next 4 weeks online and also can requests their preferred shifts. The supervisor may accommodate their request but it is not guaranteed. The supervisor should be able to generate reports for each route, buses, and drivers.
Workshop Manager can also schedule mechanics for shifts and mechanics should be able to see their schedules online. Each bus entering or leaving the workshop is checked in and checked out. Workshop staff should be able to add the type of work performed on each bus such as oil change, tire replacement, engine overhaul etc. Workshop Manager should be able to generate reports such as mechanical work performed on each bus or each month (for example).
Project Requirements:
Please make reasonable and educated assumptions about missing/ ambiguous information and properly document (in a few words/sentences only) your assumptions and the rationale behind those assumptions.
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George