You work at the Amtrak HQ in Washington DC, and have been tasked to design a database for train schedules. Trains can run on various tracks as the infrastructure between stations, and a daily schedule is needed to handle operations.

A Train is either a (D)iesel, (E)lectric, or (L)ocomotive, with consist specifying the number of cars. Each train configuration also has a name (for instance the Palmetto Limited, the City of New Orleans, the Sunshine Special etc.), date of last service, and whether in/active service. Trains run on tracks, which are an (acquired) infrastructure between stations, so each track specifies a departing station and arriving station. Tracks include a distance, and whether the track is in/active service. Only one train can be active on a track.

Stations have address information, and are always open and can service any number of trains. Amtrak's daily train Schedule lists the trains scheduled to run on tracks, with date, departure time, arrival time, and ticket price.

Create an amtrak.sql script to implement this railroad.

1. (10 pts) Create an AMTRAK user, with necessary privileges.

2. (32 pts) Code the TYPEs and DDL for AMTRAK user's data (eg. four tables).

3. Create an amtrak package with functions/procedures to: (28 pts) load at least five stations, seven tracks, five trains, and seven scheduled departures; and (20 pts) display report of your Amtrak RR schedule. For at most (20 pts) extra credit, simulate an interactive Amtrak RR transaction.

4. (10 pts) Use a PL/SQL block similar to the following to test.
declare
begin
amtrak.loadRR(); -- only once!
amtrak.showRR();
end;
/

ALL USING SQL