SET DEFINE OFF;
SET SCAN OFF;
DROP TABLE boats cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE reserves cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE sailors cascade constraints;

PURGE RECYCLEBIN;
CREATE TABLE boats(
	bid	integer,
	bname	char(30),
	color	char(30));

INSERT INTO boats(bid,bname,color) VALUES ('101','Interlake','blue');
COMMIT;
INSERT INTO boats(bid,bname,color) VALUES ('102','Interlake','red');
INSERT INTO boats(bid,bname,color) VALUES ('103','Clipper','green');
INSERT INTO boats(bid,bname,color) VALUES ('104','Marine','red');


CREATE TABLE reserves(
	sid	integer,
	bid	integer,
	day	date);

INSERT INTO reserves(sid,bid,day) VALUES ('22','101',TO_DATE('1998-10-10','YYYY-MM-DD'));
COMMIT;
INSERT INTO reserves(sid,bid,day) VALUES ('22','102',TO_DATE('1998-10-10','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('22','103',TO_DATE('1998-10-08','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('22','104',TO_DATE('1998-10-07','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('31','102',TO_DATE('1998-11-10','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('31','103',TO_DATE('1998-11-06','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('31','104',TO_DATE('1998-11-12','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('64','101',TO_DATE('1998-09-05','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('64','102',TO_DATE('1998-09-08','YYYY-MM-DD'));
INSERT INTO reserves(sid,bid,day) VALUES ('74','103',TO_DATE('1998-09-08','YYYY-MM-DD'));


CREATE TABLE sailors(
	sid	integer,
	sname	char(30),
	rating	numeric,
	age	numeric);

INSERT INTO sailors(sid,sname,rating,age) VALUES ('22','Dustin','7','45.0');
COMMIT;
INSERT INTO sailors(sid,sname,rating,age) VALUES ('29','Brutus','1','33.0');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('31','Lubber','8','55.5');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('32','Andy','8','25.5');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('58','Rusty','10','35.0');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('64','Horatio','7','35.0');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('71','Zorba','10','16.0');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('74','Horatio','9','35.0');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('85','Art','3','25.5');
INSERT INTO sailors(sid,sname,rating,age) VALUES ('95','Bob','3','63.5');


ALTER TABLE boats ADD PRIMARY KEY (bid);
ALTER TABLE reserves ADD PRIMARY KEY (sid,bid,day);
ALTER TABLE sailors ADD PRIMARY KEY (sid);
ALTER TABLE reserves ADD CONSTRAINT reserves_bid_fkey FOREIGN KEY (bid) REFERENCES boats(bid);
ALTER TABLE reserves ADD CONSTRAINT reserves_sid_fkey FOREIGN KEY (sid) REFERENCES sailors(sid);
