TRAIN DATABASE

You will create a database to store metro stations, lines, and schedules. Please carefully read the following assumptions:

• There are multiple lines and multiple train stations.
• Each line has multiple stations and each station can be on multiple lines. So, we use a line_station table to keep track of which station is on which line. To specify the order of stations, each row in line_station has a sequence number. For example, if station A has sequence number 1 on line B, it means station A is the first station of line B. Sequence number 2 means the second station and so on.
• Each line has multiple schedules. You can think of a schedule as a row in a time table in the real world (a sample time table is given at the bottom of the page).
• The Schedule_station table stores the scheduled arrival time of a train at a station on a given schedule. For example, in schedule 1 the train may arrive at station 1 at 7:30 am and arrive at station 2 at 7:40 am, and so on.
• To represent time during the day such as 7:30 am, we use interval day to second data type (i.e., count the gap from 12 am). For example, 7:30 am is represented as interval ‘7:30:00.00’ hour to second.
• A passenger can transfer to a different line at a station that is on multiple lines

*use attached word document (which is in pl/sql code) as the database from which to complete tasks

Part 1 – Write ONE SQL statement to implement each of the following tasks.

You can ONLY use conditions listed in each task. Your statements need to work regardless of what data are in the database. E.g., if we look for a station with a certain name, you cannot manually look up the station’s ID.

Task 1: Return the number of stations on the green line.

Task 2: Update the status of Chinatown station to open

Task 3: Return the number of lines each station is on, along with station id.

Task 4: Return the names of stations on the green line.

Task 5: Return names of stations that are on multiple lines. Please return both station name and count of lines the station is on.

Task 6: Return all schedules passing Chinatown station. Please include the line name, scheduled arrival time at Chinatown station, and direction of each schedule.

Task 7: Update a line’s status to 3 if some of the stations on the line are closed.

Hint: use a subquery/nested query.

Part 2 – Write an anonymous PL/SQL program to print out the name of the third station (i.e., the sequence number is 3) on the green line. Please use an implicit cursor and handle the exception. In case of exception (no match is found), print out a message ‘No such station’.

Part 3 – Suppose a passenger wants to leave at greenbelt station between 7:30 am to 8:30 am (including 7:30 and 8:30) and take the green line train in the direction of increasing sequence number. Please write an anonymous PL/SQL program to print out schedule IDs and scheduled arrival time of such schedules at greenbelt station.