Hired by the City of Metropolis to design a database for the Metropolis Transit Authority (META), which runs the city's subway system. The system consists of a number of subway stations, each with a name, and a number of subway lines that connect various stations. Each line runs in two directions, called uptown and downtown. Of course, every day there are many trains on each line. For simplicity, assume that each train has some time when it departs from the first station, and that if the following train starts say 6 minutes later, it will arrive 6 minutes later than the previous train on every station that is on this line. Thus, to store the schedule, you only have to store the starting time of each train, and the times in takes between the stations on the line (which is the same for all trains.)
Customers have to purchase a transit card called METACard to use the subway, and the card can be refilled with credits as needed. Assume that customers have to swipe this card whenever they enter or exit a station, and that the system then deducts from their card the cost of the trip, which depends on where they enter and leave the subway. For example, a trip from "Luthor Avenue" to "Lois Lane" might cost $2, while a trip from "Metro Square" to "Park Avenue" costs $2.50. So the price might be different between any two stations. METACards are identified by an ID, but do not have a customer name associated with them since they can be bought at any newsstand without giving a name. The balance for each card has to be maintained in your database, so that the actual card only stores an ID that is read and transmitted to the database system when the card is swiped.
a) Design an ER diagram that can model the above scenario. Identify any weak entities, suitable keys, and the cardinalities of the relationships. Discuss any assumptions that you are making in your design.
b) Write statements in SQL for the following queries. Note that if your schema does not allow you to answer a query, you may have to go back and change your design.
1. List the names of station where the train departed within the last hour.
2. Assuming that there is a direct train going from "Metro Square" to "Broadway", output the travel time and cost of the trip.
3. For each subway station, output how many people swipe in and out between 8pm and 10pm on Feb 16.
4. Check if there is a way to go from "Metro Square" to "Lois Lane" by changing at most once.
5. Output the customer who took the train from same station at least twice on the same day.