CpS3740Spring 2017 Homework 1 Name Dr Huang Due date 1159pm

CpS3740/Spring 2017 Homework #1, Name: Dr. Huang Due date: 11:59pm on February 16, 2017 Please implement your answers in the database CPS3740_2017 on server imc.kean.edu. You have to refer to the tables - Hotel, Room, Booking, Guest in database dream home on imc.kean.edu server. Your view name must be EXACTLY the same as the requirement, xxxx is your Kean Email ID. Please view your answers online at Create a view vHWl_l_xxxx for implementing the following relational algebra operations: Product_hoteI No (sigma price >40 (ROOm)) Create a view vHWl_2_xxxx for implementing the following relational algebra operations: Product_hotelName (Hot _Hotel, hotel No = Room. hotelNo (sigma_price

Solution

1. Create or Replace view VHW1_1_xxxx as select hotelNo from dreamhome.Room where price >40;

2. Create or Replace view VHW1_2_xxxx as select hotelName from dreamhome.Hotel inner join dreamhome.Room on dreamhouse.Hotel.hotelNo = dreamhouse.Room.hotelNo where dreamhome.Room.price <60;

3. Create or Replace view VHW1_3_xxxx as select name,address from dreamhouse.Guest where address like %London% order by name;

4. Create or Replace view VHW1_4_xxxx as select Hotel.hotelname,Room.roomType ,Room.price from dreamhouse.Hotel inner join dreamhouse.Room on dreamhouse.Hotel.hotelNo = dreamhouse.Room.hotelNo where dreamhouse.Room.price = (Select max(price) from dreamhouse.Room));

5. Create or Replace view VHW1_5_xxxx as select roomType from dreamhouse.Room where roomType = \'double\' or roomType = \'family\' and price < 50 order by price;

6. Create or Replace view VHW1_6_xxxx as select name from dreamhouse.Guest inner join dreamhouse.Booking on dreamhouse.Booking.bookingNo = dreamhouse.Guest.bookingNo where Booking.dateFrom = \'May 1,2004\' and Booking.dateTo = \'July 31,2004\';

7. Create or Replace view VHW1_7_xxxx as select Hotel.hotelName,count(Room.*) from dreamhouse.Room inner join dreamhouse.Hotel on dreamhouse.Hotel.hotelNo = dreamhouse.Room.hotelNo group by Hotel.hotelNo;

8. Create or Replace view VHW1_8_xxxx as select Hotel.hotelName, room.type , count(*) NumberOfBookings from Booking inner join Hotel on Booking.HotelNo = Hotel.HotelNo inner join Room on Booking.HotelNo = Room.HotelNo AND Booking.RoomNo = Room.RoomNo group by Booking.HotelNo, Room.Type order by count(*) desc;

9. Create table Customers_xxxx

(

id int NOT NULL,

name varchar(50) NOT NULL,

balance decimal(7,2) NOT NULL,

zipcode varchar(10) NOT NULL,

PRIMARY KEY(id),

FOREIGN KEY(zipcode) REFERENCES Zipcode(zipcode)

);

10.

insert into Customers_xxxx values(1001,\"Smith Jones\",1600.67,\'1555688\');

insert into Customers_xxxx values(1002,\"John \",1000.27,\'1600055\');

insert into Customers_xxxx values(1003,\"Steve Bills\",2345.00,\'1686767\');

insert into Customers_xxxx values(1004,\"Adam Johnson\",1455.78,\'111122236\');

10.