use ict5103;

drop table if exists reserves cascade;
drop table if exists sailors cascade;
drop table if exists boats cascade;

create table sailors(
	sid integer primary key,
	sname varchar(30),
	rating integer,
	age real
);

create table boats(
	bid integer primary key,
    bname varchar(30),
    color varchar(30)
);

create table reserves(
	sid integer,
    bid integer,
    rday date,
    primary key (sid, bid, rday),
    foreign key (sid) references sailors(sid),
    foreign key (bid) references boats(bid)
);

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

insert into boats(bid, bname, color) values
(101, 'Interlake', 'blue'),
(102, 'Interlake', 'red'),
(103, 'Clipper', 'green'),
(104, 'Marine', 'red');

insert into reserves(sid, bid, rday) values
(22,101,'98-10-10'),
(22,102,'98-10-10'),
(22,103,'98-08-10'),
(22,104,'98-07-10'),
(31,102,'98-10-11'),
(31,103,'98-06-11'),
(31,104,'98-12-11'),
(64,101,'98-05-09'),
(64,102,'98-08-09'),
(74,103,'98-08-09');