What you need to do

Task $1$

Below is a description of the state$$s public transport. The following paragraphs describe the UoD.

The state has $3$ modes of transportation for public passengers, including buses, trains, and ferries for this scenario. A passenger can pay for their trip by using a Go card over all the $3$ modes of transport within the state.

$$ What is a trip on public transport?

A trip refers to a journey that a passenger makes using the public transportation system. This can involve boarding a vehicle $($such as a bus, train, or ferry$)$ at a specific location $($pick$-$up point$)$ and disembarking at another location $($drop$-$off point$).$ A trip typically involves moving from one point to another within the transportation network.

A vehicle $($bus$,$ train, and ferry$)$ can be used over multiple daily routes throughout the day and each vehicle has a unique vehicle registration number. Below is the description of the vehicles table which are used as public transport.

Vehicle Registration Number Vehicle Colour Capacity Model Year Mode

ABC$123$ Red $302019$ Bus

DEF$456$ Blue $1002018$ Train

GHI$789$ Green $252023$ Ferry

JKL$012$ Silver $342019$ Bus

MNO$345$ Black $1202018$ Train

Below is the sample of details of the driver$$s table. Each driver has a unique Driver ID within the public transport network. A driver can only drive one mode of transport which includes either a bus, train or ferry but can't drive more than $1$ i$.$e$.$ A driver can$$t drive a train and a ferry. A driver may or may not have a contact number. Null values are represented by a question mark $(?)$ in the table below.

Driver ID Driver Name Contact number Address Date of Birth Driver license number Driver license type

$1001$ John Smith $555-123-4567123$ Main St$,$ Calamvale $1985-07-15$ DL$123456789$ Bus

$1002$ Jane Doe $?456$ Elm Rd$,$ Townsville $1992-03-22$ DL$987654321$ Train

$1003$ Michael Lee $555-555-1212789$ Oak Ave, Forest Lake $1978-11-10$ DL$456789123$ Ferry

$1004$ Emily White $555-333-5678234$ Pine Ln$,$ Upper Mount Gravatt $2000-09-03$ DL$654321987$ Bus

$1005$ David Brown $?567$ Maple Dr$,$ Tingalpa $1996-05-18$ DL$789123456$ Train

$1006$ Chris Ward $555-171-383934$ Tea tree St$,$ Richlands $1991-02-01$ DL$534695951$ Ferry

Below is a sample of details of passengers who are using a Go card for their trips on public transport. Passengers must provide their name, address, and phone number on an online web portal during the purchase of a Go card if they want to use it on public transport.

Go Card Number Passenger$$s Name Passenger$$s Address Passenger$$s Contact Number

$123456789$ Hew Smith $123$ Main St$,$ East Brisbane $555-133-3567$

$987654321$ Chris Doe $456$ Elm Rd$,$ Stafford $555-987-6543$

$456789123$ Drew Johnson $789$ Oak Ave, West End $555-583-1215$

$789123456$ Emily Williams $234$ Pine Ln$,$ St Lucia $555-333-1278$

$654321987$ David Brown $567$ Maple Dr$,$ Sherwood $555-777-7420$

$353641927$ James Ward $43$ Nottingham Rd$,$ Springfield $555-143-8567$

Below is the sample of the $$Scheduled trips$$ table that is followed for the whole week within the state. These scheduled trips are managed by the state government and are publicly available for passengers so that they can plan their trips accordingly. Each vehicle $($bus$,$ train, and ferry$)$ trip is allocated a unique Schedule ID number within the $$Scheduled trip$$ below. The Route ID starts with either B$,$ T or F which represents Bus, Train, and Ferry respectively.

Vehicle Registration Number Schedule ID Route ID Driver ID Start Time End Time

ABC$1231$ B$101100108$:$00$ AM $09$:$30$ AM

DEF$4562$ T$118100508$:$15$ AM $9$:$45$ AM

GHI$7893$ F$125100610$:$30$ AM $11$:$30$ PM

JKL$0124$ B$101100411$:$45$ AM $01$:$30$ PM

MNO$3455$ T$105100201$:$00$ PM $03$:$00$ PM

DEF$4566$ T$101100510$:$00$ AM $12$:$00$ PM

GHI$7897$ F$12510031$:$00$ PM $2$:$00$ PM

JKL$0128$ B$10210019$:$45$ AM $10$:$45$ AM

Below is the sample of the $$Transaction$$s table$$ for all trips which are made by passengers on public transport. $$Operations date$$ refers to the date on which the vehicle was running in service on any specific route as per the scheduled trips table. A passenger can make more than $1$ trip across different modes of transport. Each trip made on the public transport network is allocated a unique TransactionID.

Transaction ID Go card Number Cost Schedule ID Operations date Pick Up Address Drop Off Address Boarding Date$-$Time Alighting Date$-$Time

$5001123456789$ $$5\mathrm{.}0012023-08-1445$ Blueberry St$,$ Algester $456$ Elm Rd$,$ Brisbane City $2023-08-1408$:$302023-08-1409$:$15$

$5002987654321$ $$3\mathrm{.}5022023-08-11456$ Elm Rd$,$ West End $789$ Oak Ave, Norman Park $2023-08-1109$:$152023-08-119$:$30$

$5003987654321$ $$2\mathrm{.}2082023-08-1239$ Oak Ave, Everton Park $234$ Pine Ln$,$ The Gap $2023-08-1210$:$002023-08-1210$:$23$

$5004353641927$ $$4\mathrm{.}7572023-08-0523$ Leon St$,$ Tingalpa $567$ Maple Dr$,$ Carina $2023-08-0513$:$242023-08-0513$:$40$

$5005654321987$ $$6\mathrm{.}8062023-08-06$ Richland Train station Central Station $2023-08-0610$:$152023-08-0611$:$30$

$5006654321987$ $$3\mathrm{.}5042023-08-06$ Creek St$,$ Brisbane City $45$ Alice St$,$ Brisbane City $2023-08-0612$:$152023-08-0612$:$45$

$50077891$