----------------------------------------------

-- Name: Jonas Wik

-- Date: 04-16-2001

-- Assignment 8

----------------------------------------------

drop table Booking;

drop table Guest;

drop table Room;

drop table Hotel;

create table Hotel

(Hotel_No char(3) Primary Key,

Name VarChar2(15) Not Null,

Address VarChar2(30));

create table Room

(Room_No char(4),

Hotel_No char(3) References Hotel,

Type VarChar2(10),

Price number not null,

Constraint PK_Room

Primary Key (Room_No, Hotel_No),

Constraint TypeCheck

check (Type in('Single','Double','Family')),

Constraint PriceCheck

check (Price between 30 and 200));

create table Guest

(Guest_No char(6) Primary Key,

Guest_Name VarChar2(30) Not Null,

Address VarChar2(30));

create table Booking

(Hotel_No char(3) references Hotel,

Guest_No char(6) references Guest,

Date_From Date Not null,

Date_To Date,

Room_No char(4),

Constraint PK_Booking

Primary Key (Hotel_No, Guest_No, Date_From),

Foreign Key (Hotel_No, Room_No) References Room (Hotel_No, Room_No));

Insert into Hotel

Values('H01', 'Grosvenor', 'London');

Insert into Hotel

Values('H05', 'Glasgow', 'London');

Insert into Hotel

Values('H07', 'Aberdeen', 'London');

Insert into Hotel

Values('H12', 'London', 'Glasgow');

Insert into Hotel

Values('H16', 'Aberdeen', 'Glasgow');

Insert into Hotel

Values('H24', 'London', 'Aberdeen');

Insert into Hotel

Values('H28', 'Glasgow', 'Aberdeen');

Insert into Room

Values('R001', 'H01', 'Single', '30');

Insert into Room

Values('R002', 'H01', 'Single', '35');

Insert into Room

Values('R003', 'H05', 'Single', '40');

Insert into Room

Values('R101', 'H05', 'Double', '35');

Insert into Room

Values('R103', 'H01', 'Double', '40');

Insert into Room

Values('R105', 'H12', 'Double', '45');

Insert into Room

Values('R201', 'H12', 'Family', '80');

Insert into Room

Values('R209', 'H01', 'Family', '150');

Insert into Guest

Values('G01003', 'John White', '6 Lawrence Street, Glasgow');

Insert into Guest

Values('G01011', 'Mary Tregear', '5 tarbot Rd, Aberdeen');

Insert into Guest

Values('G02003', 'Aline Stewart', '64 Fern Dr, London');

Insert into Guest

Values('G02005', 'Mike Ritchie', '18 Tain St, London');

Insert into Guest

Values('G02007', 'Joe Keogh', '6 Achray St, Aberdeen');

Insert into Booking

Values('H01', 'G01003', '25-Apr-99', '14-May-99', 'R001');

Insert into Booking

Values('H01', 'G02003', '24-Apr-99', '26-Apr-99', 'R103');

Insert into Booking

Values('H01', 'G01011', '25-Apr-99', Null, 'R209');