drop table B_TYPE cascade constraints;
drop table BOAT cascade constraints;
drop table SAILOR cascade constraints;
drop table CUSTOMER cascade constraints;
drop table REG_LINE cascade constraints;
drop table REG_LINE_REL cascade constraints;
drop table REG_LINE_REL_COVER cascade constraints;
drop table RESERVATION cascade constraints;
drop table SAIL_WITH_GUIDE cascade constraints;

create table B_TYPE (
	TYPE  VARCHAR2(20),
	C_CAPACITY   NUMBER(4),
	CAPACITY  NUMBER(4),
	M_SPEED  NUMBER(4),
	constraint B_TYPE_PK primary key (TYPE));

create table BOAT (
	BID  NUMBER(3),
	BNAME  VARCHAR2(20) not null,
	BTYPE  VARCHAR2(20),
	COLOR  VARCHAR2(20),
	constraint BOAT_PK primary key (BID),
	constraint BOAT_UK unique (BNAME),
	constraint BOAT_FK foreign key (BTYPE) references B_TYPE(TYPE),
	constraint BOAT_CK check (COLOR in ('yellow', 'blue', 'pink', 'brown', 'black', 'green')));

create table SAILOR (
	SID  NUMBER(3),
	SNAME  VARCHAR2(20) not null,
	AGE  NUMBER(3),
	RATE  NUMBER(2),
	constraint SAILOR_PK primary key (SID),
	constraint SAILOR_UK unique (SNAME),
	constraint SAILOR_CK check (AGE between 14 and 700));

create table CUSTOMER (
	CID  NUMBER(3),
	CNAME  VARCHAR2(20),
	constraint CUSTOMER_PK primary key (CID));

create table REG_LINE (
	LID  NUMBER(3),
	STRT  VARCHAR2(20),
	DEST  VARCHAR2(20),
	constraint REG_LINE_PK primary key (LID));

create table REG_LINE_REL (
	LID  NUMBER(3) ,
	RID  NUMBER(3),
	LTIME DATE,
	constraint REG_LINE_REL_PK primary key (LID, RID),
	constraint REG_LINE_REL_FK foreign key (LID) references REG_LINE(LID));

create table REG_LINE_REL_COVER (
	LID  NUMBER(3),
	RID  NUMBER(3),
	COV_DATE  DATE,
	BID  NUMBER(3),
	SID  NUMBER(3),
	constraint REG_LINE_REL_COVER_PK primary key (LID, RID, COV_DATE, BID, SID),
	constraint REG_LINE_REL_COVER_FK1 foreign key (LID, RID)
		references REG_LINE_REL (LID, RID),
	constraint REG_LINE_REL_COVER_FK2 foreign key (BID) references BOAT(BID),
	constraint REG_LINE_REL_COVER_FK3 foreign key (SID) references SAILOR(SID));

create table RESERVATION (
	BID  NUMBER(3),
	SID  NUMBER(3),
	RES_DATE  DATE,
	constraint RESERVATION_PK primary key (BID, SID, RES_DATE),
	constraint RESERVATION_FK1 foreign key (BID) references BOAT(BID),
	constraint RESERVATION_FK2 foreign key (SID) references SAILOR(SID));

create table SAIL_WITH_GUIDE (
	CID  NUMBER(3),
	BID  NUMBER(3),
	SID  NUMBER(3),
	SAIL_DATE  DATE,
	constraint SAIL_WITH_GUIDE_PK primary key (CID,BID,SID,SAIL_DATE),
	constraint SAIL_WITH_GUIDE_FK1 foreign key (CID) references CUSTOMER(CID),
	constraint SAIL_WITH_GUIDE_FK2 foreign key (BID) references BOAT(BID),
	constraint SAIL_WITH_GUIDE_FK3 foreign key (SID) references SAILOR(SID));