REM Oracle Schema file for the hotel booking database

Drop table booking;
drop table room;
drop table hotel;
drop table guest;

create table hotel (
hotelNo char(3),
hotelName varchar2(20) not null,
city char(20) not null,
constraint PK_hotel primary key (hotelNo));

create table room (
roomNo char(3),
hotelNo char(3),
type char(2) not null,
price number(3) not null,
constraint PK_room primary key(roomNo, hotelNo),
constraint FK_room foreign key (hotelNo) references hotel(hotelNo),
constraint check_roomtype check (type in ('BS','BD','BF','S','D','F','LS','LD','LF')),
constraint check_roomprice check (price between 50 and 350),
constraint check_price check (price<=300 or type='LF')
);

create table guest (
guestNo Number(8),
guestName varchar2(30) not null,
guestAddress varchar2(30),
constraint pk_guest primary key (guestNo)
);

create table booking (
hotelNo char(3),
guestNo number(8),
dateFrom date not null,
dateTo date not null,
roomNo char(3) not null,
constraint PK_booking primary key (hotelNo, guestNo, dateFrom),
constraint FK1_booking foreign key (guestNo) references guest (guestNo),
constraint FK2_booking foreign Key (roomNo, hotelNo) references room (roomNo, hotelNo),
constraint ChecK_date check (dateFrom <= dateTo)
);

 REM inserting data

 Insert into hotel values ('H01', 'Grosvenor Hotel',  'Sydney');
 Insert into hotel values ('H02', 'ANA Hotel',  'Sydney');
 Insert into hotel values ('H03', 'Great Mansion Hotel',  'Brisbane');
 Insert into hotel values ('H04', 'Grand Hotel',  'Brisbane');
 Insert into hotel values ('H05', 'Central Hotel',  'Sydney');
 Insert into hotel values ('H06', 'Airport Hotel',  'Sydney');


 Insert into guest values (1, 'David Goodman',  '21 mains rd,Sydney');
 Insert into guest values (2, 'Lisa Bourke', Null);
 Insert into guest values (3, 'Lawrence Dooley',  '12 Sydney street, Brisbane');
 Insert into guest values (4, 'Ray Maher',  '10 Churchill Court, Brisbane');
 Insert into guest values (5, 'David Smith',  '101 San_Lurry St, Brisbane');
 Insert into guest values (6, 'John Wang',  '1 newfound Rd, Gold Coast');


 Insert into room values ('001', 'H01',  'F', 120);
 Insert into room values ('002', 'H01',  'F', 120);
 Insert into room values ('003', 'H01',  'LD', 150);
 Insert into room values ('004', 'H01',  'D', 100);
 Insert into room values ('005', 'H01',  'S', 80);
 Insert into room values ('006', 'H01',  'S', 80);
 Insert into room values ('001', 'H02',  'LS', 100);
 Insert into room values ('002', 'H02',  'S', 78);
 Insert into room values ('003', 'H02',  'S', 78);
 Insert into room values ('004', 'H02',  'BF', 75);


 Insert into booking values ('H01', 1,  '01-Apr-2012', '10-apr-2012', '001');
 Insert into booking values ('H01', 2,  sysdate-1, sysdate+5, '002');
 Insert into booking values ('H01', 3,  '01-Apr-2012', '10-apr-2012', '006');
 Insert into booking values ('H02', 2,  sysdate-1, sysdate+5, '004');
 Insert into booking values ('H01', 4,  '01-feb-2012', '10-feb-2012', '001');
 Insert into booking values ('H01', 2,  '20-feb-2012', '01-mar-2012', '002');
 Insert into booking values ('H01', 3,  '10-feb-2012', '15-feb-2012', '006');
