/* Remove tables if they are already here */
drop table if exists Reserves;
drop table if exists Sailors;
drop table if exists Boats;


/* Turn on some useful features: foreign constraint checking, and pretty-printing */
.header on
.mode column
PRAGMA foreign_keys = ON; -- turns on checking for foreign keys constraints


/* Create the relations */
CREATE TABLE Sailors (sid INTEGER, sname CHAR(20),rating INTEGER, age REAL,PRIMARY KEY (sid));
CREATE TABLE Boats (bid INTEGER, bname CHAR (20), color CHAR(10),PRIMARY KEY (bid));
CREATE TABLE Reserves (sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, day), FOREIGN KEY(sid) REFERENCES Sailors, FOREIGN KEY(bid) REFERENCES Boats);

/* Add some data */
INSERT INTO Sailors (sid,sname,rating,age) VALUES (22,'Dustin',7,45.0); 
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);

INSERT INTO Boats (bid,bname,color) VALUES (101,'Interlake','blue'); 
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'); 

INSERT INTO Reserves (sid,bid,day) VALUES (22,101,'2014-10-10'); 
INSERT INTO Reserves (sid,bid,day) VALUES (22,102,'2014-10-10'); 
INSERT INTO Reserves (sid,bid,day) VALUES (22,103,'2014-10-08'); 
INSERT INTO Reserves (sid,bid,day) VALUES (22,104,'2014-10-07'); 
INSERT INTO Reserves (sid,bid,day) VALUES (31,102,'2014-11-10'); 
INSERT INTO Reserves (sid,bid,day) VALUES (31,103,'2014-11-06'); 
INSERT INTO Reserves (sid,bid,day) VALUES (31,104,'2014-11-12'); 
INSERT INTO Reserves (sid,bid,day) VALUES (64,101,'2014-9-05'); 
INSERT INTO Reserves (sid,bid,day) VALUES (64,102,'2014-9-08'); 
INSERT INTO Reserves (sid,bid,day) VALUES (74,103,'2014-9-08'); 


