-- SQL statements for database implementation of Exercise IV

DROP SEQUENCE  route_seq; 
CREATE SEQUENCE  route_seq  START WITH 1 INCREMENT BY 1 CACHE 100; 
DROP TABLE  route; 
CREATE TABLE  route ( 
  rid    INTEGER PRIMARY KEY, 
  begin  INTEGER NOT NULL, 
  end    INTEGER NOT NULL ); 

DROP SEQUENCE  stop_seq; 
CREATE SEQUENCE  stop_seq  START WITH 1 INCREMENT BY 1 CACHE 100; 
DROP TABLE  stop; 
CREATE TABLE  stop ( 
  sid   INTEGER PRIMARY KEY, 
  name  VARCHAR(32) NOT NULL ); 

DROP SEQUENCE  route_stop_seq; 
CREATE SEQUENCE  route_stop_seq  START WITH 1 INCREMENT BY 1 CACHE 100; 
DROP TABLE  route_stop; 
CREATE TABLE  route_stop ( 
  rsid  INTEGER PRIMARY KEY, 
  rid   INTEGER NOT NULL, 
  sid   INTEGER NOT NULL, 
  ord   INTEGER NOT NULL ); 

INSERT INTO  stop 
  SELECT  stop_seq.NEXTVAL, 'Alerus Center'  FROM DUAL; 
INSERT INTO  stop 
  SELECT  stop_seq.NEXTVAL, 'Starbucks'  FROM DUAL; 
INSERT INTO  route 
  SELECT  route_seq.NEXTVAL, 1, 2  FROM DUAL; 
INSERT INTO  route_stop 
  SELECT  route_stop_seq.NEXTVAL, 1, 1, 0 FROM DUAL; 
INSERT INTO  route_stop 
  SELECT  route_stop_seq.NEXTVAL, 1, 2, 8 FROM DUAL; 

SELECT * FROM  stop; 
SELECT * FROM  route; 
SELECT * FROM  route_stop;