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

-- Name: Jonas Wik

-- Date: 04-29-2001

-- Assignment 10

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

drop table Booking;

drop table Guest;

drop table Room;

drop table Hotel;

drop View RoomInH01;

drop synonym Staff;

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 Room

Values('R115', 'H07', 'Family', '39');

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

Insert into Booking

Values('H01', 'G02003', '20-Aug-99', '29-Aug-99', 'R103');

Insert into Booking

Values('H07', 'G02003', '12-Aug-99', '15-Aug-99', 'R115');

Insert into Booking

Values('H01', 'G02003', '11-Apr-01', '02-May-01', 'R103');

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

-- 1. Creates a view named RoomInH01 which contains all records

-- in table Room that are in hotel with Hotel_No = 'H01'

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

Create View RoomInH01 as

Select *

From Room

Where Hotel_No = 'H01';

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

-- 2. Increase the price by 10% for all double rooms in RoomInH01

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

Update RoomInH01

Set Price = Price * 1.1

where Type = 'Double';

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

-- 3. Grants all rights on RoomInH01 to all Users

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

Grant All on RoomInH01 to Public;

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

-- 4. Grant update rights to user YangQ

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

Grant Update on RoomINH01 to YangQ;

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

-- 5. Creates a synonym Staff for the table Yangq.Staff

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

Create synonym Staff for Yangq.Staff;

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

-- Insert one record into Staff using my name, etc.

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

Insert Into Staff

values('WIKJ', 'Jonas', 'Wik', '515 West Main', '348-7487',

'Student', 'M', '27-Feb-77', '50000', '999023466', 'cs');