projectPart5 (1)

pdf

School

University of North Texas *

*We aren’t endorsed by this school

Course

4460

Subject

Communications

Date

Apr 3, 2024

Type

pdf

Pages

43

Uploaded by AmbassadorFlyPerson1011

Report
Stored Functions 1) calculate_hotel_revenue calculates the total revenue made by a hotel within a given date range. It takes the hotel ID, start date, and end date as parameters, queries the Reservations table to sum up the total_price of all reservations for the given hotel ID within the specified date range, and returns the calculated revenue. CREATE OR REPLACE FUNCTION calculate_hotel_revenue( p_hotel_id VARCHAR2, p_start_date DATE, p_end_date DATE ) RETURN NUMBER IS l_revenue NUMBER := 0; BEGIN SELECT SUM(total_price) INTO l_revenue FROM Reservations WHERE hotel_id = p_hotel_id AND checkout_date BETWEEN p_start_date AND p_end_date; RETURN l_revenue; END calculate_hotel_revenue; /
Execution: DECLARE l_hotel_id VARCHAR2(20) := 'HTL02'; -- Replace with the desired hotel ID string l_start_date DATE := DATE '2023-11-28'; l_end_date DATE := DATE '2023-11-30'; l_revenue NUMBER; BEGIN l_revenue := calculate_hotel_revenue(l_hotel_id, l_start_date, l_end_date); DBMS_OUTPUT.PUT_LINE('Total revenue for hotel ' || l_hotel_id || ' from ' || l_start_date || ' to ' || l_end_date || ' is ' || l_revenue); END; /
2) GetGuestStayDuration accepts a reservation ID as input. It calculates the duration of the guest's stay by subtracting the checkin_date from the checkout_date and adding 1 (to include the checkout date). The function returns the calculated stay duration in days. It utilizes the below execution code to display the stay duration of the guest. CREATE OR REPLACE FUNCTION GetGuestStayDuration( p_reservation_id IN VARCHAR2 ) RETURN NUMBER IS v_duration NUMBER; BEGIN SELECT (checkout_date - checkin_date) + 1 INTO v_duration FROM Reservations WHERE reservation_id = p_reservation_id; RETURN v_duration; END GetGuestStayDuration; / Execution:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
DECLARE v_reservation_id VARCHAR2(10) := 'Resv1'; v_stay_duration NUMBER; BEGIN v_stay_duration := GetGuestStayDuration(v_reservation_id); DBMS_OUTPUT.PUT_LINE('Reservation ID: ' || v_reservation_id); DBMS_OUTPUT.PUT_LINE('Stay Duration (in days): ' || v_stay_duration); END; / 3) CalculateTotalPayroll takes a hotel ID and payroll date as parameters. It first retrieves the employee IDs of all employees working at the given hotel by joining the Employees and Payroll tables. It then calculates the total payroll by summing the salaries of those employees for the specified payroll date. CREATE OR REPLACE FUNCTION CalculateTotalPayroll( p_hotel_id IN VARCHAR2, p_payroll_date IN DATE ) RETURN NUMBER IS v_total_payroll NUMBER := 0;
BEGIN SELECT SUM(salary) INTO v_total_payroll FROM Payroll WHERE employee_id IN ( SELECT employee_id FROM Employees WHERE hotel_id = p_hotel_id ) AND payroll_date = p_payroll_date; RETURN v_total_payroll; END CalculateTotalPayroll; / Execution: DECLARE v_hotel_id VARCHAR2(10) := 'HTL01'; -- Replace with the actual hotel ID
v_payroll_date DATE := TO_DATE('2023-03-04', 'YYYY-MM-DD'); -- Replace with the desired payroll date v_total_payroll NUMBER; BEGIN v_total_payroll := CalculateTotalPayroll(v_hotel_id, v_payroll_date); DBMS_OUTPUT.PUT_LINE('Hotel ID: ' || v_hotel_id); DBMS_OUTPUT.PUT_LINE('Payroll Date: ' || v_payroll_date); DBMS_OUTPUT.PUT_LINE('Total Payroll: ' || v_total_payroll); END; / 4) GetRoomAmenities accepts a room ID as input. It retrieves the amenity names for the hotel associated with the given room ID from the Amenities table. The function concatenates all the amenity names into a comma-separated string and returns it. CREATE OR REPLACE FUNCTION GetRoomAmenities( p_room_id IN NUMBER ) RETURN VARCHAR2 IS v_amenities VARCHAR2(500);
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
v_amenity_name VARCHAR2(50); BEGIN v_amenities := ''; FOR amenity_rec IN ( SELECT amenity_name FROM Amenities WHERE hotel_id = ( SELECT hotel_id FROM Rooms WHERE room_id = p_room_id ) ) LOOP v_amenity_name := amenity_rec.amenity_name; v_amenities := v_amenities || v_amenity_name || ', '; END LOOP; RETURN RTRIM(v_amenities, ', '); END GetRoomAmenities; /
Execution: DECLARE v_room_id NUMBER := 101; -- Replace with the actual room ID v_room_amenities VARCHAR2(500); BEGIN v_room_amenities := GetRoomAmenities(v_room_id); DBMS_OUTPUT.PUT_LINE('Room ID: ' || v_room_id); DBMS_OUTPUT.PUT_LINE('Room Amenities: ' || v_room_amenities); END; /
5) GetEmployeeDetails takes a hotel ID and department as input parameters. It returns a cursor that fetches employee details (employee ID, name, age, job title, qualification, and certification) for the specified hotel and department from the Employees table. CREATE OR REPLACE FUNCTION GetEmployeeDetails( p_hotel_id IN VARCHAR2, p_department IN VARCHAR2 ) RETURN SYS_REFCURSOR IS v_employee_details SYS_REFCURSOR; BEGIN OPEN v_employee_details FOR SELECT employee_id, ename, age, job_title, qualification, certification FROM Employees WHERE hotel_id = p_hotel_id AND department = p_department; RETURN v_employee_details; END GetEmployeeDetails; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Execution: DECLARE v_hotel_id VARCHAR2(10) := 'HTL03'; -- Replace with the actual hotel ID v_department VARCHAR2(50) := 'Housekeeping'; -- Replace with the desired department v_employee_details SYS_REFCURSOR; v_employee_id VARCHAR2(5); v_ename VARCHAR2(50); v_age NUMBER; v_job_title VARCHAR2(50); v_qualification VARCHAR2(100); v_certification VARCHAR2(50); BEGIN v_employee_details := GetEmployeeDetails(v_hotel_id, v_department); LOOP FETCH v_employee_details INTO v_employee_id, v_ename, v_age, v_job_title, v_qualification, v_certification; EXIT WHEN v_employee_details%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id); DBMS_OUTPUT.PUT_LINE('Name: ' || v_ename); DBMS_OUTPUT.PUT_LINE('Age: ' || v_age); DBMS_OUTPUT.PUT_LINE('Job Title: ' || v_job_title); DBMS_OUTPUT.PUT_LINE('Qualification: ' || v_qualification); DBMS_OUTPUT.PUT_LINE('Certification: ' || v_certification); DBMS_OUTPUT.PUT_LINE('-------------------'); END LOOP; CLOSE v_employee_details; END; / Stored Procedures 1)CalculateHotelAverageRating takes a hotel ID as input and two output parameters: p_avg_rating and p_total_reviews. It calculates the average overall rating and the total number of reviews for the given hotel by querying the Reviews table and assigns the calculated values to the output parameters. CREATE OR REPLACE PROCEDURE CalculateHotelAverageRating(
p_hotel_id IN VARCHAR2, p_avg_rating OUT NUMBER, p_total_reviews OUT NUMBER ) AS BEGIN SELECT AVG(overall_rating), COUNT(*) INTO p_avg_rating, p_total_reviews FROM Reviews WHERE hotel_id = p_hotel_id; END CalculateHotelAverageRating; / Execution: DECLARE v_hotel_id VARCHAR2(10) := 'HTL01'; -- Replace with the desired hotel ID v_avg_rating NUMBER; v_total_reviews NUMBER; BEGIN CalculateHotelAverageRating(v_hotel_id, v_avg_rating, v_total_reviews);
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
DBMS_OUTPUT.PUT_LINE('Hotel ID: ' || v_hotel_id); DBMS_OUTPUT.PUT_LINE('Average Rating: ' || v_avg_rating); DBMS_OUTPUT.PUT_LINE('Total Reviews: ' || v_total_reviews); END; / 2)SearchInventoryItem accepts a hotel ID, item name, and two output parameters: p_item_found (a boolean flag) and p_quantity. It checks if the given item exists in the Inventory table for the specified hotel. If found, it sets p_item_found to TRUE and assigns the item quantity to p_quantity. Otherwise, it sets p_item_found to FALSE and p_quantity to 0. CREATE OR REPLACE PROCEDURE SearchInventoryItem( p_hotel_id IN VARCHAR2, p_item_name IN VARCHAR2, p_item_found OUT BOOLEAN, p_quantity OUT NUMBER ) AS v_count NUMBER; BEGIN -- Check if the item exists in the inventory for the given hotel
SELECT COUNT(*) INTO v_count FROM Inventory WHERE hotel_id = p_hotel_id AND item_name = p_item_name; IF v_count > 0 THEN -- Item found, retrieve the quantity SELECT quantity INTO p_quantity FROM Inventory WHERE hotel_id = p_hotel_id AND item_name = p_item_name; p_item_found := TRUE; ELSE -- Item not found p_item_found := FALSE; p_quantity := 0; END IF; END SearchInventoryItem;
/ Execution: DECLARE v_hotel_id VARCHAR2(10) := 'HTL01'; v_item_name VARCHAR2(100) := 'Towels'; v_item_found BOOLEAN; v_quantity NUMBER; BEGIN SearchInventoryItem(v_hotel_id, v_item_name, v_item_found, v_quantity); IF v_item_found THEN DBMS_OUTPUT.PUT_LINE('Item found in inventory: ' || v_item_name); DBMS_OUTPUT.PUT_LINE('Quantity: ' || v_quantity); ELSE DBMS_OUTPUT.PUT_LINE('Item not found in inventory: ' || v_item_name); END IF; END;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
/ 3) UpdateRoomSize takes a hotel ID, room ID, and new room size as input. It first checks if the given hotel ID and room ID combination exists in the Rooms table. If found, it updates the room_size for the specified room with the new value. If not found, it displays an error message. CREATE OR REPLACE PROCEDURE UpdateRoomSize( p_hotel_id IN VARCHAR2, p_room_id IN NUMBER, p_new_room_size IN NUMBER ) AS v_room_exists NUMBER; BEGIN -- Check if the provided hotel_id and room_id are valid SELECT COUNT(*) INTO v_room_exists FROM Rooms WHERE hotel_id = p_hotel_id AND room_id = p_room_id;
IF v_room_exists > 0 THEN -- Update the room size for the specified room UPDATE Rooms SET room_size = p_new_room_size WHERE hotel_id = p_hotel_id AND room_id = p_room_id; COMMIT; DBMS_OUTPUT.PUT_LINE('Room size updated successfully.'); ELSE DBMS_OUTPUT.PUT_LINE('Invalid hotel ID or room ID.'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); ROLLBACK; END UpdateRoomSize; /
Before Updating Execution: BEGIN UpdateRoomSize(p_hotel_id => 'HTL02', p_room_id => 103, p_new_room_size => 450); END;/
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
After Updating: 4)AddHotelReview accepts a hotel ID, customer ID, review text, and various rating values (overall, cleanliness, service, and amenities) as input parameters. It generates a new review ID, inserts the new review into the Reviews table with the provided details, commits the transaction, and displays a success message with the new review ID. CREATE OR REPLACE PROCEDURE AddHotelReview( p_hotel_id IN VARCHAR2, p_customer_id IN VARCHAR2, p_review_text IN VARCHAR2, p_overall_rating IN NUMBER, p_cleanliness_rating IN NUMBER, p_service_rating IN NUMBER, p_amenities_rating IN NUMBER ) AS v_review_id VARCHAR2(10); BEGIN -- Generate a new review ID SELECT 'REV' || LPAD(TO_CHAR(Reviews_seq.NEXTVAL), 2, '0') INTO v_review_id
FROM DUAL; -- Insert the new review INSERT INTO Reviews (review_id, hotel_id, customer_id, review_date, review_text, overall_rating, cleanliness_rating, service_rating, amenities_rating) VALUES (v_review_id, p_hotel_id, p_customer_id, SYSDATE, p_review_text, p_overall_rating, p_cleanliness_rating, p_service_rating, p_amenities_rating); -- Commit the transaction COMMIT; DBMS_OUTPUT.PUT_LINE('New review added with Review ID: ' || v_review_id); END AddHotelReview; / Execution: DECLARE v_review_id VARCHAR2(10); BEGIN AddHotelReview( p_hotel_id => 'HTL02',
p_customer_id => 'G2', p_review_text => 'Excellent hotel with great service.', p_overall_rating => 5, p_cleanliness_rating => 4, p_service_rating => 5, p_amenities_rating => 4 ); END; / Select * from reviews;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5)SearchAvailableRooms takes a check-in date, number of beds, and hotel ID as input parameters. It retrieves a list of available rooms based on the check-in date, number of beds, and hotel ID by joining the Rooms and Reservations tables. It displays the details (room ID, size, number of beds, internet availability, and thermostat) of the available rooms. CREATE OR REPLACE PROCEDURE SearchAvailableRooms( p_checkin_date IN DATE, p_num_beds IN NUMBER, p_hotel_id IN VARCHAR2 ) AS v_available_rooms SYS_REFCURSOR; v_room_id NUMBER; v_room_size NUMBER; v_number_beds NUMBER; v_internet_availability VARCHAR2(3); v_thermostat NUMBER; BEGIN OPEN v_available_rooms FOR SELECT r.room_id, r.room_size, r.number_beds, r.internet_availability, r.thermostat
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
FROM Rooms r LEFT JOIN Reservations rs ON r.room_id = rs.room_id AND ( rs.checkin_date < p_checkin_date AND rs.checkout_date >= p_checkin_date ) WHERE r.hotel_id = p_hotel_id AND r.number_beds >= p_num_beds AND rs.reservation_id IS NULL; LOOP FETCH v_available_rooms INTO v_room_id, v_room_size, v_number_beds, v_internet_availability, v_thermostat; EXIT WHEN v_available_rooms%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Room ID: ' || v_room_id); DBMS_OUTPUT.PUT_LINE('Room Size: ' || v_room_size); DBMS_OUTPUT.PUT_LINE('Number of Beds: ' || v_number_beds); DBMS_OUTPUT.PUT_LINE('Internet Availability: ' || v_internet_availability); DBMS_OUTPUT.PUT_LINE('Thermostat: ' || v_thermostat); DBMS_OUTPUT.PUT_LINE('-------------------'); END LOOP; CLOSE v_available_rooms; END SearchAvailableRooms; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Execution: DECLARE v_checkin_date DATE := TO_DATE('2023-06-01', 'YYYY-MM-DD'); v_num_beds NUMBER := 2; v_hotel_id VARCHAR2(10) := 'HTL12'; BEGIN SearchAvailableRooms( p_checkin_date => v_checkin_date, p_num_beds => v_num_beds, p_hotel_id => v_hotel_id ); END; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Triggers 1) CheckGuestAge is a trigger that runs before inserting or updating a row in the Guests table. It calculates the guest's age based on the date of birth and checks if the guest meets the minimum age requirement (set to 18 years in the provided code). If the guest's age is less than the minimum, it raises an application error. CREATE OR REPLACE TRIGGER CheckGuestAge BEFORE INSERT OR UPDATE ON Guests FOR EACH ROW DECLARE v_age NUMBER; v_min_age NUMBER := 18; -- Set the minimum age requirement here BEGIN -- Calculate the age based on the DOB v_age := TRUNC(MONTHS_BETWEEN(SYSDATE, :NEW.dob) / 12); -- Check if the guest meets the minimum age requirement IF v_age < v_min_age THEN
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
RAISE_APPLICATION_ERROR(-20001, 'Guest must be at least ' || v_min_age || ' years old.'); END IF; END; / -- data inserted with guest age >18 INSERT INTO Guests VALUES ('G11', 'Chris Jane', '556-567-8901', 'jane@example.com', '890 Cedar St', TO_DATE('1995-11-30', 'YYYY-MM-DD')); --data inserted with guest age<18 INSERT INTO Guests VALUES ('G12', 'Mears Taylor', '555-678-9012', 'mears@example.com', '234 Elm St', TO_DATE('2006-07-05', 'YYYY-MM-DD'));
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2)check_room_availability is a trigger that runs before inserting a new row into the Reservations table. It checks if there are any overlapping reservations for the same hotel and room based on the check-in and check-out dates. If overlapping reservations are found, it raises an application error indicating that the requested room is not available for the specified dates. CREATE OR REPLACE TRIGGER check_room_availability BEFORE INSERT ON Reservations FOR EACH ROW DECLARE v_overlapping_reservations NUMBER; BEGIN -- Check if there are any overlapping reservations for the same hotel and room SELECT COUNT(*) INTO v_overlapping_reservations FROM Reservations WHERE hotel_id = :NEW.hotel_id AND room_id = :NEW.room_id AND (:NEW.checkin_date BETWEEN checkin_date AND checkout_date OR :NEW.checkout_date BETWEEN checkin_date AND checkout_date);
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
IF v_overlapping_reservations > 0 THEN RAISE_APPLICATION_ERROR(-20002, 'The requested room is not available for the specified dates.'); END IF; END; / --Before inserting the Reservation details in Reservation table it checks whether the requested room in the hotel is available or not for the mentioned dates. INSERT INTO Reservations VALUES ('Resv21', 'G4', 104, TO_DATE('2024-03-18', 'YYYY- MM-DD'), TO_DATE('2024-03-19', 'YYYY-MM-DD'), 200.00, 'HTL04');
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3)update_inventory_for_new_hotel is a trigger that runs after inserting a new row into the Hotels table. It automatically inserts default inventory items (towels, pillows, and sheets) with predefined quantities into the Inventory table for the newly added hotel. CREATE OR REPLACE TRIGGER update_inventory_for_new_hotel AFTER INSERT ON Hotels FOR EACH ROW BEGIN INSERT INTO Inventory (hotel_id, item_name, quantity) VALUES (:NEW.hotel_id, 'Towels', 100); INSERT INTO Inventory (hotel_id, item_name, quantity) VALUES (:NEW.hotel_id, 'Pillows', 200); INSERT INTO Inventory (hotel_id, item_name, quantity) VALUES (:NEW.hotel_id, 'Sheets', 300); END; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Before adding new Hotel the Inventory table: Inserted new rows in hotels table
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Inventory table: Package Package Specification: Created a package GuestManagementPackage to include various procedures and functions for managing the Guests like Adding Guest details, Updating Guest information, to check if a guest has any active reservations, and get the reviews given by a specified guest, and calculate the total revenue made by a guest.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CREATE OR REPLACE PACKAGE GuestManagementPackage AS -- Procedure to add a new guest PROCEDURE AddGuest( p_guest_id IN VARCHAR2, p_name IN VARCHAR2, p_phone IN VARCHAR2, p_email IN VARCHAR2, p_address IN VARCHAR2, p_dob IN DATE ); -- Procedure to update guest information PROCEDURE UpdateGuest( p_guest_id IN VARCHAR2, p_name IN VARCHAR2 DEFAULT NULL, p_phone IN VARCHAR2 DEFAULT NULL, p_email IN VARCHAR2 DEFAULT NULL, p_address IN VARCHAR2 DEFAULT NULL, p_dob IN DATE DEFAULT NULL ); -- Function to check if a guest has any active reservations FUNCTION HasActiveReservations( p_guest_id IN VARCHAR2 ) RETURN BOOLEAN; -- Function to get the total revenue generated by a guest FUNCTION GetTotalRevenueByGuest( p_guest_id IN VARCHAR2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
) RETURN NUMBER; -- Function to get all reviews written by a guest FUNCTION GetGuestReviews( p_guest_id IN VARCHAR2 ) RETURN SYS_REFCURSOR; END GuestManagementPackage; / Package Body: The implementation of all the subprograms are defined in the package body and the provided code demonstrates how to use the packages functions and procedures to perform various tasks performed by guests like adding new guests, updating the guest details, checking for active reservations, calculating total revenue generated by a guest, and retrieving guest reviews. CREATE OR REPLACE PACKAGE BODY GuestManagementPackage AS PROCEDURE AddGuest( p_guest_id IN VARCHAR2, p_name IN VARCHAR2, p_phone IN VARCHAR2,
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
p_email IN VARCHAR2, p_address IN VARCHAR2, p_dob IN DATE ) IS BEGIN INSERT INTO Guests (guest_id, gname, phno, g_email, address, dob) VALUES (p_guest_id, p_name, p_phone, p_email, p_address, p_dob); COMMIT; END AddGuest; PROCEDURE UpdateGuest( p_guest_id IN VARCHAR2, p_name IN VARCHAR2 DEFAULT NULL, p_phone IN VARCHAR2 DEFAULT NULL, p_email IN VARCHAR2 DEFAULT NULL, p_address IN VARCHAR2 DEFAULT NULL, p_dob IN DATE DEFAULT NULL ) IS BEGIN UPDATE Guests SET gname = NVL(p_name, gname), phno = NVL(p_phone, phno), g_email = NVL(p_email, g_email), address = NVL(p_address, address), dob = NVL(p_dob, dob) WHERE guest_id = p_guest_id; COMMIT; END UpdateGuest;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
FUNCTION HasActiveReservations( p_guest_id IN VARCHAR2 ) RETURN BOOLEAN IS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM Reservations WHERE guest_id = p_guest_id AND checkout_date >= SYSDATE; RETURN v_count > 0; END HasActiveReservations; FUNCTION GetTotalRevenueByGuest( p_guest_id IN VARCHAR2 ) RETURN NUMBER IS v_total_revenue NUMBER := 0; BEGIN SELECT SUM(total_price) INTO v_total_revenue FROM Reservations WHERE guest_id = p_guest_id; RETURN v_total_revenue; END GetTotalRevenueByGuest;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
FUNCTION GetGuestReviews( p_guest_id IN VARCHAR2 ) RETURN SYS_REFCURSOR IS v_guest_reviews SYS_REFCURSOR; BEGIN OPEN v_guest_reviews FOR SELECT review_id, hotel_id, review_date, review_text, overall_rating, cleanliness_rating, service_rating, amenities_rating FROM Reviews WHERE customer_id = p_guest_id; RETURN v_guest_reviews; END GetGuestReviews; END GuestManagementPackage; / Execution: -- Adding a new guest
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
BEGIN GuestManagementPackage.AddGuest( p_guest_id => 'G001', p_name => 'John Doe', p_phone => '555-1234', p_email => 'john.doe@example.com', p_address => '123 Main St, Anytown USA', p_dob => TO_DATE('1990-05-15', 'YYYY-MM-DD') ); END; / After adding new guest details:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
-- Checking if a guest has active reservations DECLARE v_has_active_reservations BOOLEAN; BEGIN v_has_active_reservations := GuestManagementPackage.HasActiveReservations('G001'); IF v_has_active_reservations THEN DBMS_OUTPUT.PUT_LINE('Guest G001 has active reservations.'); ELSE DBMS_OUTPUT.PUT_LINE('Guest G001 does not have any active reservations.'); END IF; END; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
When the Guest hasn’t any reservations currently: When the Guest have any reservations currently: -- Get total revenue by guest DECLARE v_total_revenue NUMBER; BEGIN
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
v_total_revenue := GuestManagementPackage.GetTotalRevenueByGuest('G1'); DBMS_OUTPUT.PUT_LINE('Total revenue by guest G1: ' || v_total_revenue); END; / -- Update guest information BEGIN GuestManagementPackage.UpdateGuest( p_guest_id => 'G001', p_name => 'John Doe', p_phone => '555-234-1234', p_email => 'john.doe@example.com' ); END; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
After Updating the guest table: -- Get guest reviews DECLARE v_review_cursor SYS_REFCURSOR; v_review_id VARCHAR2(10); v_hotel_id VARCHAR2(10);
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
v_review_date DATE; v_review_text VARCHAR2(500); v_overall_rating NUMBER; v_cleanliness_rating NUMBER; v_service_rating NUMBER; v_amenities_rating NUMBER; BEGIN v_review_cursor := GuestManagementPackage.GetGuestReviews('G1'); LOOP FETCH v_review_cursor INTO v_review_id, v_hotel_id, v_review_date, v_review_text, v_overall_rating, v_cleanliness_rating, v_service_rating, v_amenities_rating; EXIT WHEN v_review_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Review ID: ' || v_review_id || ', Hotel ID: ' || v_hotel_id || ', Review Date: ' || v_review_date || ', Review Text: ' || v_review_text || ', Overall Rating: ' || v_overall_rating || ', Cleanliness Rating: ' || v_cleanliness_rating || ', Service Rating: ' || v_service_rating || ', Amenities Rating: ' || v_amenities_rating); END LOOP; CLOSE v_review_cursor; END; /
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
My Contributions: In this part, I have worked on implementing two stored procedures and three stored functions within the provided package GuestManagementPackage. I have made a comprehensive approach to managing guest-related tasks within the hospitality management system. By implementing both stored procedures and functions, I have ensured efficient data manipulation and retrieval while maintaining the integrity of the database schema.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

About Self-Esteem.docx
Speech 2 outlines.docx
DB Week #6.docx
SUCCESS Worksheet-2.pdf
LIB495 BELEN Rough Draft.docx
11 (9).docx
Literacy Article Analysis.docx
22_ Culture Repair and Villages (1).docx
CHCDIS009_AE_Kn_1of2.docx
early-educator-physical-disability.pdf
4-2 Module Four Short Responses.docx
Moduel 1.docx