CPS3740 Spring 2017 Homework Na a1 Page 1 of 1 Dr Huang Due

CPS3740 Spring 2017 Homework Na a1. Page 1 of 1 Dr. Huang Due date: 11:59pm on February 16, 2017 Please implement your answers in the database CPS3740 20167 on server You have to refer to the tables-Hotel. Room, Booking, innc kean edu. imc kean edu Guest in database dreamhome on Your view name must be EXACTLY the same as the requirement. xxxx is your Kean ID. Please view your answers online at keanedu/students Email 1. (10 pts Create a view vHwI 1 xxxx for implementing the following relational algebra operations (oprice 40 (Room) 2. ao pts) Create a view vHw1 2 xxxx for implementing the following relational algebra operations: hotel Name (Hotel HotelhoterNo-Room.hotelNo price 60 (Room))) 3. (10 pts) Create a view vHw1 3 xxxx sting the names and addresses of all guests in London, alphabetically ordered by name. 4. pts) Create a view vHwi 4 xxxx showing the hotel name, room type and the price for the hotel that has the most expensive room. 5. 10 pts) Create a view vHwi 5xxxx sting all double or family rooms with a price below 50.00 per night, in ascending order of price. 6. (0 pts) Create a view vHw1 6 xxxx listing all guests staying at the Grosvenor Hotel from May 1st t July 31st, 2004. 7. (10 pts) Create a view vHwi-7-xxxx sting the number of rooms in each hotel. You need to show 8. do pts) create a view vHw1 8 Kxx showing the most commonly booked room type for each hot London. You need to show hotelname, 9. (0 pts) Create a table Customers xxxx with the following fields and constraints id: int type, name: varchar type, balance: float type, zipcode: varchar type All fields cannot be NULL. id: the primary key CSP3740.Zipcode table. zipcode is a foreign key references to the zipcode field in

Solution

Initially please understand the requirement .Here are the meanings of the symbolic representation
SELECT -> (sigma)
PROJECT -> (pi)
PRODUCT -> ×(times)
JOIN -> |×| (bow-tie)
UNION -> (cup)
INTERSECTION -> (cap)
DIFFERENCE -> - (minus)
RENAME -> (rho)
1.
The question is about selecting the hotelNo from the table Room where column \'price\' value is greater than 40.
Please replace xxxx with ur/user emailID

create or replace view vHW1_1_xxxx as select hotelNo from dreamhome.Room where price >40;
  
2.It all about pulling the hotelname by performing join operation where the price of room are less than 60
create or replace view vHW1_2_xxxx as select h.hotelName from dreamhome.Hotel h,dreamhome.Room r where h.hotelNo=r.hotelNo and r.price<60;  

3.For this we need to have the schema definition,i.e the table and the respective columns present in the tables,so that we can pull the required columns based on conditions and joins to be made.
The query will be something like:

create or replace view vHW1_3_xxxx as select name,address from dreamhome.Guests where <location column similar which will be mentioned in the table def of Guests> location=\'London\' order by name;

similary with remainin also.
9. we are creating keys using constraint keyword and referencing the table in CSP3740 schema zipcode table
create table dreamhome.Customers_xxxx(id number(10) NOT NULL,name varchar(50) NOT NULL,balance number(8,2) NOT NULL,zipcode varchar(10) NOT NULL,CONSTRAINT id_pk PRIMARY KEY (id),CONSTRAINT fk_zipcode
FOREIGN KEY (zipcode)
REFERENCES CSP3740.Zipcode(zipcode);
10.Inserting values ,we have to make sure the zipcodes ae present in the CSP3740.Zipcode table. and ID value should be uique as primary key it will not allow duplicates.
insert into dreamhome.Customers_xxxx(10,\'xxxx\',30000,\'NY21D\');
insert into dreamhome.Customers_xxxx(20,\'xxxx1\',60000,\'NY31D\');
insert into dreamhome.Customers_xxxx(30,\'xxxx2\',30000,\'NY21D\');
insert into dreamhome.Customers_xxxx(40,\'xx2x\',30000,\'NY41D\');