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 not null primary key,
	sname varchar(30) not null,
	rating integer,
	age real,
    check (rating >= 1 and rating <= 10)
);

-- create domain ratingval integer default 1 check (value >= 1 and value <= 10); not supported by mysql

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

create table reserves(
	sid integer not null,
    bid integer not null,
    rday date not null,
    foreign key (sid) references sailors(sid) on delete cascade on update cascade, -- restrict, no action, set null
    foreign key (bid) references boats(bid) on delete cascade on update cascade -- restrict, no action, 
);

alter table reserves add primary key (sid, bid, rday);

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');

-- copy table
create table sailorscopy
select * from sailors;