Case Study $-$ Transportation Authority

$1.$ The Transportation Authority would like you to design a database consistent with the following. You will need to create a complete ERD diagram using notations and such learned from the prerequisite Database Design I course.

$-$ A station has a unique name and an address and is either an express station or a local station.

$-$ A train has a unique number and an engineer and is either an express train or a local train.

$-$ A local train can stop at any station.

$-$ An express train only stops at express stations.

$-$ A train can stop at a station for any number of times during a day.

$-$ Train schedules can vary every day.

$2.$ Create a full table structure with PK$\text{'}$s and FK$\text{'}$s$.$ Define your database instance using Management Studio. Show me your table structure. This should match your ERD diagram.

$3.$ Populate your database which you created with some sample data $-$ You will need at least $10$ rows in each table. Show all your data by querying the database and showing the results in your report.

$4.$ A transportation authority worker may be able to query the data for all trains and include associated information about every train in the database. However, we want a potential traveler to use the same database but to only be able to view train information but without being able to see the engineer's name or information.

Design and implement a database construct to achieve this in the simplest way possible and which you learnt from this course. Explain and show your results.

$5.$ On a nightly basis, the transportation authority needs to examine the database to make sure that engineers are not overloaded by having too many train assignments. A simple way to do this would be to query the database to see how many train assignments the engineers have during a given week. However, week by week, the demand for engineers could differ based on seasonality, time of the year, and other thresholds. The transportation authority manager will want to query this based on different parameters at any time. For example, during holiday weeks, they may want to identify engineers who have more than $3$ routes in a given week, while in slow times, the threshold could be $2$ routes.

Design and implement a database construct to achieve this in the simplest way possible and which you learnt from this course. Explain and show your results.

$6.$ In some instances, the transportation authorities may need to reassign trains to other engineers as a backend process if they determine some engineers are overloaded with their assignments in any given week. However, they want to automate this process so that a reassignment takes place only if the other engineer being assigned is also not overloaded. If the engineer is overloaded, then all bets are off, and the update needs to be cancelled or otherwise reverted in some way and the process needs to start again to ensure an acceptable result.

Design and implement a database construct to achieve this in the simplest way possible and which you learnt from this course. Explain and show your results.