numbers 4-10. Need help. Page 1 of 1 CPS3740 Spring 2017 Homework 1, Name: Dr. H

Question

numbers 4-10. Need help.

Page 1 of 1 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 imekeanedu. You have to refer to the tables Room, Booking, Guest in database dreamhome on imcikean edu Your view name must be EXACTLY the same as the requirement.xxx is your Kean Email ID. Please view your answers online at http limc.kean.edu/students 1. 0 pts Create a view vHwi-l xooks for implementing the following relational algebra operations: IlhoteNo (o price >40 (Room) 2. 0 pts) Create a view vHw1 2 xxx for implementing the following relational algebra operations: IThotel Name (Hotel Hotel hoteINo-Rom hoteNo(aprice (Room)) 3 0 pts) Create a view vHwi 3 xxxx listing the names and addresses of all guests in London. alphabetically ordered by name. 4. (10 pts) Create a view vHWi 4 xxx showing the hotel name, room types and the price for the hotel that has the most expensive room. 5. (10 pts) Create a view vHwi-S xxx listing all double or family rooms with a price below 50.00 per night, in ascending order of price. 6. (10 pts) Create a view vHwi-6 xxx listing all guests staying at the Grosvenor Hotel from May lst to July 31st, 2004. 7. 0 pts) Create a view vHw 7 xxxx listing the number ofrooms in each hotel. You need to show hotename, NOT hotelno. 8 0 pts) Create a view v xxxx showing the most commonly booked room type for each hotel in Hwi-8 London. You need to show hotelname, NOT hotelno. 9. 0 pts) Create a table Customers xxx 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 zipcode is a foreign key references to the zipcode field in CSP3740-Zipcode table. 10. (10 pts) Please insert 4 records into your customers xox table.

Explanation / Answer

Here are the views for 4, 5, 6, 7 questions:

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)

4. Create a view vHW1_4xxxx showing hotel name, room type, and the price for the hotel
that has the most expensive room.
CREATE VIEW vHW1_4xxxx AS
SELECT hotelName, type, price
FROM Hotel H, Room R
WHERE H.hotelNo = R.hotelNo AND R.price = (SELECT DISTINCT max(price) FROM Room)

5. Create a view vHW1_5xxxx listing all double or family rooms with a price below 50.00
per night, in ascending order of price.
CREATE VIEW vHW1_5xxxx AS
SELECT hotelNo, hotelName, type, price
FROM Hotel H, Room R
WHERE H.hotelNo = R.hotelNo AND price < 50 AND (type = 'Double' OR type = 'Family')
ORDER BY price

6. Create a view vHW_1_6xxxx listing all guests staying at the Grosvernor Hotel from
May 1st to July 31st, 2004.
CREATE VIEW vHW1_6xxxx AS
SELECT guestNo, guestName, guestAddress
FROM Guest G, Booking B, Room R, Hotel H
WHERE H.hotelNo = R.hotelNo AND R.hotelNo = B.hotelNo AND B.guestNo = G.guestNo
AND H.hotelName = 'Grosvernor' AND B.dateFrom >= '20040501' AND B.dateTo <= '20040731'

7. Create a view vHW1_7xxxx listing the number of rooms in each hotel. You need to show
hotelname, NOT hotelno.
CREATE VIEW vHW1_7xxxx AS
SELECT hotelname, COUNT(roomNo)
FROM Hotel H, Room R
WHERE H.hotelNo = R.hotelNo
GROUP BY H.hotelNo;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.