RECENT ASSIGNMENT

Overview of business case:
Drive-n-Drop is a transport company that provides bus services to the public in the Sydney metropolitan area. The company employs a number of bus drivers and owns a fleet of over 90 busses. Drive-n-Drop has been using an Excel spreadsheet to track their business activities; however, this has caused many redundancies and inefficiency.
With view to their expanding business and under the new management, they decided to investigate the option of implementing a database system. Accordingly, they hired you to design a relational database to better track and record their operational data.

Your task:
Create an ERD for each of the following statements that describe the current business rules for Drive-n-Drop. For each new statement being the business rule, extend the ERD to include the additional business rule in the ERD (created in previous point for example; B will include A&B, C will include A, B & C, etc.)
Use Crows Foot notations and include all of the following in each part:
• All entities with Primary key and any foreign key attributes;
• The relationships between entities;
• The cardinality and optionality of each relationship;
• Any M:N relationships are be resolved to 1:M relationships
Drive-n-Drop discovered business rules for your ERD:
1. Each driver is assigned to one shift (morning, afternoon, night), and each shift includes a number of drivers. (2 marks)
2. A bus is assigned to a driver for each shift, with the bus number appearing on the driver’s shift. That’s is how the driver knows what bus to pick up from the depot at the start of his/her shift. (2 marks)
3. Each shift covers multiple routes; that is, with some shifts there are up to 6 routes that are covered, depending on the length of the route and the number of stops pertaining to the route. (2 marks)
4. Each route is of different length and includes multiple sections. A route is covered multiple times in each shift depending on the time of the day and peak period: for example Route 305 takes 1.5 hours to be covered in a whole shift, but is serviced every 15 minutes. (4 marks)
Rationale
This assessment item is designed to test your understanding of basic database design and modelling concepts, and your ability to interpret business rules into an Entity Relationship Diagram.
Additionally, your ability to apply correct relationships, constraints, cardinalities, optionalities, and the use Crows Foot modelling notations are tested.