I am having a tough time writing a procedure/function, in SQL Oracle, for the question below.

Change a reservationDate: Input the reservation ID and change reservation start and end date, if there is availability in the same room type for the new date interval.

Below is my ER diagram, NOTE: I made a minor change to it, in the rooms table, I dropped the customer_id column and added a reservation_number column that refers to the reservation table. :

I created a procedure and a function for it, listed below. The issue that I am having is that a hotel can have several of the same room types. For example there can be more than one 'Single-bed' room type in a hotel, and the problem is that I don't know how to have my function take that into consideration. If I have two 'single bed' room types in one hotel, and one of them is reserved from Nov 20, 2017 to Nov 30, 2017, if I want to change the date to Nov 20, 2017 to Nov 30, 2017 for another reservation for that same room type, it won't see that there is another 'single bed' room type that has not been reserved in that hotel and it won't update. Any help would be appreciated.

FUNCTION:

create or replace function RoomCheck ( start_date IN DATE, end_date IN DATE, roomty in varchar2) return number is resnum reservation.reservation_number%type; begin select count(*) into resnum from reservation where check_in_date = start_date and CHECK_OUT_DATE = end_date and room_type = roomty; return resnum; end;

PROCEDURE:

create or replace PROCEDURE ChangeReservationDate (r_number IN NUMBER, start_date IN DATE, end_date IN DATE, roomty in varchar2 ) is resvfunc int; BEGIN resvfunc := RoomCheck(start_date, end_date, roomty); if resvfunc = 0 then UPDATE reservation SET check_in_date = start_date, check_out_date=end_date where RESERVATION_NUMBER = r_number; dbms_output.Put_line('Reservation successfully updated. Your confirmation number is ' || r_number || ' Thank you!'); else dbms_output.Put_line('Sorry, no rooms available during the date interval provided' ); end if; END;

Rooms oom Number Room Type Rates Room location Customer ID Hotel_ID Hotels Services Service ID Service_name Service cost Hotel ID

Room charge Misc charges Credit card No Payment Date Customer ID Reservation_Number stomer ID Check in date Check out date Status Guest_fname Guest Iname Room Type Number of guests Reservation date Hotel ID Service ID Room Number Hotel ID Customer Customer ID Last Name Phone number First Name City State Zip code