1.Create the bookingdb environment

2.Review the base tables that is created. Code SQL to create a VIEW for each base table. The viewname should begin with 'v'+basetablename. The CREATE VIEW statement should code all the existing columns in the SQL portion of the statement.

3.Review the lesson that created the guestmessage table and create a guestmessage base table in this bookingdb.

4.Create a VIEW for the guestmessage table.

--Below is the bookdb sql----

CREATE TABLE HOTEL ( hotelNo numeric , name varchar(40) , address varchar(40) , city char(200) , PRIMARY KEY (hotelNo) );

CREATE TABLE ROOM ( roomNo numeric , hotelNo numeric , type char(20) , price numeric , PRIMARY KEY (roomNo,hotelNo) , constraint FOREIGN KEY (hotelNo) REFERENCES hotel (hotelNo) );

CREATE TABLE GUEST ( guestNo numeric , name varchar(40) , address varchar(40) , PRIMARY KEY (guestNo) );

CREATE TABLE BOOKING ( hotelNo numeric , guestNo numeric , dateFrom date NOT NULL , dateTo date , roomNo numeric , PRIMARY KEY (hotelNo,guestNo,roomNo,dateFrom) , CONSTRAINT FOREIGN KEY (hotelNo) REFERENCES hotel (hotelNo) , CONSTRAINT Foreign key (guestNo) references guest (guestNo) , CONSTRAINT foreign key (roomNo) references room (roomNo) );

INSERT INTO HOTEL VALUES (1,'Hilton Hotel','123 Main st','New Jersey'); INSERT INTO HOTEL VALUES (2,'Holiday Inn','50 3rd Ave.', 'New York'); INSERT INTO HOTEL VALUES (3,'Governor Hotel','8243 Berkley Sq.','London'); INSERT INTO HOTEL VALUES (4,'New World Hotel','30 East River Rd', 'Hong Kong'); INSERT INTO HOTEL VALUES (5,'Santos Pension House','15 Main Rd', 'Philippines');

insert into room values (101,1,'Family',200); insert into room values (102,1,'Family',200); insert into room values (103,1,'Single',100); insert into room values (104,1,'Single',100); insert into room values (101,2,'Family',80); insert into room values (102,2,'Family',85); insert into room values (103,2,'Family',80); insert into room values (101,3,'Single',35); insert into room values (102,3,'Double',115); insert into room values (103,3,'Double',115); insert into room values (104,3,'Family',150); insert into room values (101,4,'Single',30); insert into room values (102,4,'Single',30); insert into room values (103,4,'Single',30); insert into room values (104,4,'Double',90); insert into room values (105,4,'Double',90); insert into room values (106,4,'Double',90); insert into room values (107,4,'Double',90); insert into room values (108,4,'Family',110); insert into room values (109,4,'Family',110); insert into room values (101,5,'Double',38); insert into room values (102,5,'Double',38); insert into room values (103,5,'Single',32); insert into room values (104,5,'Single',32);

insert into guest values (1,"John Doe","Los Angeles"); insert into guest values (2,"Mary Jane","New York"); insert into guest values (3,"Hank Kim","London"); insert into guest values (4,"Bob Jones","London"); insert into guest values (5,"Paul John","New York"); insert into guest values (6,"Ann Smith","New York"); insert into guest values (7,"James Mann","London"); insert into guest values (8,"Mary Mann","London"); insert into guest values (9,"Jim Jones","New York"); insert into guest values (10,"Tom Jones","Los Angeles"); insert into guest values (11,"Jimmy Johnson","Boston"); insert into guest values (12,"Harry Coleman","Dallas"); insert into guest values (13,"Bob James","Los Angeles"); insert into guest values (18,"John Mary","London"); insert into guest values (21,"Jeff Bridges","London"); insert into guest values (23,"Larry Cohen","New York");

ALTER TABLE booking ADD COLUMN last_mod_ts timestamp default current_timestamp();

insert into booking values(1,1,'2022-09-05','2022-09-10',101,current_timestamp()); insert into booking values(1,2,'2022-09-25','2022-10-04',102,current_timestamp()); insert into booking values(1,3,'2022-10-01','2022-10-10',103,current_timestamp()); insert into booking values(1,4,'2022-11-05','2022-11-20',104,current_timestamp()); insert into booking values(2,5,'2022-11-05','2022-11-07',105,current_timestamp()); insert into booking values(2,6,'2022-11-22','2022-11-29',106,current_timestamp()); insert into booking values(3,7,'2022-09-08','2022-09-18',109,current_timestamp()); insert into booking values(3,8,'2022-10-01','2022-10-10',101,current_timestamp()); insert into booking values(4,9,'2022-09-05','2022-09-17',103,current_timestamp()); insert into booking values(4,10,'2022-10-11','2022-10-18',108,current_timestamp()); insert into booking values(5,11,'2022-11-11','2022-11-29',102,current_timestamp()); insert into booking values(5,12,'2022-09-10','2022-09-11',104,current_timestamp());