This is my ERD diagram. I am required to write these 4 procedures for this. I ha

Question

This is my ERD diagram. I am required to write these 4 procedures for this. I have tried writing the procedures, but I am keep getting compilation errors. Help me out.

1. Create a new hotel with appropriate information about the hotel as input parameters.

2. Change a reservationRoomType: Input the reservation ID and change reservation room type if there is availability for that room type during the reservation’s date interval

3. ShowCancelations: Print all canceled reservations in the hotel management system. Show reservation ID, hotel name, location, guest name, room type, dates

4. TotalClariottStateReport: Input is state. Print total income from all sources of all hotels by room type and service type in the given state. Include discounts.

IS420 2617 PALYO1 RESERVATION RESERVATION I GUEST ID NUMBER NUMBER NUMBER DATE DATE S42D 2617 PALYO1.GUEST ROOM D 15420 2617 PALYO1.SERVICE LINE GUEST I GUEST NAME VARCHAR2 (50 BYTE START_DATE END_DATE RESERVATION DATE DATE NUMBE SERVICE ID SERVICE DATE SERVICE TYPE ID NUMBER RESERVATION ID SERVICE_PK(SERVMCE_ID) NUMBER DATE NUM_ROOMS NUMBER GUEST_PK (GUEST_ID) GUEST-PK (GUEST-D) IS CANCELED VARCHAR2 (6 BYTE) NUMBER (9,2) NUMBER (9.2) NUMBER RATE_PER DAY DISCOUNT ? RESERVATION PK(RESERVATION ID) RESERVATION PK (RESERVATION ID) SERVICE-PK (SERVICE-ID) S420_2617_PALYO1 HOTEL HOTEL ID HOTEL NAME HOTEL ADDRESS VARCHAR2 (60 BYTE) HOTEL CITY HOTEL STATE HOTEL_ZIP NUMBER VARCHAR2 (5D BYTE) 15420 2617 PALYO1 SERVICES NUMBER NUMBER ROOM ID HOTEL ID ROOM NUMBER VARCHAR2 (50 BYTE) ROOM TYPE ROOM_PK(ROOMID) ROOM-PK (ROOM-ID) SERVICE DESC VARCHAR2 (50 BYTE) CHAR (2 BYTE) VARCHAR2 (20 BYTE) VARCHAR2 (6 BYTE) VARCHAR2 (6D BYTE) NUMBER (,2) SERVMICE_COST ARCHAR2 (0 BYTE) SERVICE-DESC-UQ (SERVICE-DESC) SERVICE TYPE PK(SERVICE TYPE ID) IS SOLD HOTEL_PK (HOTEL_D) HOTEL PK (HOTELID) ? SERVICE TYPE_PK (SERVICE_TYPE ID) SERVICE-DESC-UQ (SERVICE-DESC)

Explanation / Answer

1. Select h.HOTEL_NAME, h.HOTEL_ADDRESS + ' City: ' + h.HOTEL_CITY + ' State and Zip Code: '

+ h.HOTEL_STATE + h.HOTEL_ZIP as [HOTEL ADDRESS], r.ROOM_TYPE, rs.RATE_PER_DAY from Hotel h Inner join Room r ON h.Hotel_ID = r.Hotel_ID Inner join Reservation rs ON

rs.Room_ID=r.Room_ID

2. Update Room

SET room_type = 'Something Else'

Where Reservation_Id = 'Input to be given by end user'

3. Select Reservation_Id , Hotel_Name, Hotel_Address, Guest_Name, Room_Type, Reservation_Date, Start_Date, End_Date from Hotel h Inner join Room r On h.Hotel_id=r.Hotel_id

Inner join Reservation rs ON rs.Room_ID=r.Room_Id Inner join Guest g ON g.Guest_id = rs.Guest_id

Where rs.Is_cancelled=true

4. Select SUM(Service_Cost), Room_type, Service_Type, Hotel_Name

From Room r INner join Hotel h ON h.Hotel_id=r.Hotel_Id Inner join Reservation rs ON

r.Room_Id=rs.Room_Id Inner join Service_Line sl ON sl.Reservation_id = rs.Reservation_Id

Inner join Services s ON s.Service_Type_id = sl.Service_Type_id

WHere h.Hotel_State = ' Input to be provided by enduser'

Group by Hotel_Name, Room_type, Service_Type

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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