/* MySQL database for 3nd Code School - University of Crete */
/* Example */

/* create table hotel */
create table hotel(name varchar(100),
address varchar(100),
phone varchar(50),
stars int,
primary key(name)
)DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

/* create table for employees */
create table employees(code varchar(100),
fname varchar(50), 
lname varchar(50),    
age int,  
salary double,
primary key(code)
)
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

create table works_on
(code varchar(100),	
name varchar(100),
foreign key(code) references employees(code) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(name) references hotel(name) ON DELETE CASCADE ON UPDATE CASCADE,
primary key(code,name))
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

/* create table for reservations */
create table reservations(code varchar(100),
startDate varchar(50),
endDate varchar(50),
cost double,
primary key(code))
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

/* INSERT DATA INTO HOTEL */
insert into hotel values('Hotel Beach','Chersonissos-Malia','0030-123456',5);
insert into hotel values('Hotel Apartments','Stalida','0030-12345678',3);
insert into hotel values('Hotel Sunshine','Gouves','0030-12345678910',4);

/* INSERT DATA INTO EMPLOYEES */
insert into employees values('A1','Emmanouil','Papadakis',30,2200.62);
insert into employees values('A2','Mary','Papadaki',50,1350.07);
insert into employees values('A3','Sofia','Papadopoulou',42,500);

/* INSERT DATA INTO RESERVATIONS */
insert into reservations values('Res0912','10/08/2015','18/08/2015',1200);
insert into reservations values('Res0092','10/07/2015','18/07/2015',1258.56);
insert into reservations values('Res897','10/09/2015','15/09/2015',560.07	);

/* INSERT DATA INTO WORKS_ON */
insert into works_on values('A1','Hotel Beach');
insert into works_on values('A3','Hotel Apartments');
insert into works_on values('A2','Hotel Sunshine');