IS420 Database Application Development Spring 2022 Hotel Management SystemThe actual tasks for the...

Question

IS420 Database Application Development Spring 2022 Hotel Management System The actual tasks for the hotel management system are given below. Tasks : Some tasks are simpler than others. · Task...

1 answer below »

IS420 Database Application Development

Spring 2022

Hotel Management System

The actual tasks for the hotel management system are given below.

Tasks: Some tasks are simpler than others.

· Task 1:

1. [10 Pst] Add a new hotel: Create a new hotel with appropriate information about the hotel as input parameters.

2. [10 Pst] Find a hotel: Provide as input the address of the hotel and return its hotel ID

3. [10 Pst] Delete a hotel by ID

4. [10 Pst] Display hotel info: Given a hotel ID, display all information about that hotel (Includes room, rates and availability)

· Task 2:

5. [10 Pst] Make a reservation: Input parameters: Hotel ID, guest’s name, start date, end date, room type, date of reservation, etc. Output: reservation ID (this is called confirmation code in real-life). NOTE: Only one guest per reservation. However, the same guest can make multiple reservations.

6. [10 Pst] Find a reservation: Input is guest’s name and date, hotel ID. Output is reservation ID

7. [10 Pst] Cancel a reservation: Input the reservationID and mark the reservation as cancelled (do NOT delete it)

· Task 3:

8. [10 Pst] 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

· Task 4:

9. [10 Pst] Display a bill: Input parameters: reservation ID. Print on the console a bill for the guest. Include guest name, hotel Name and ID, start date, end date, room type, date of reservation, room number, charge per day, rate per day and total amount.

10. [10 Pst] Income By State Report:

a. Input is state. Print total income from all sources of all hotels by room type.

b. Input is hotel ID. Print total income of hotel by room type.

· GUI: There is no Graphical User Interface (GUI) for this project. You need to create PL/SQL procedures and functions that carry out the tasks identified above. Each task will be a separate PL/SQL stored procedure or function.

· Input/output: For tasks that require input parameters, you need to call the corresponding PL/SQL procedure or function and pass to it the input parameters. This means that you need to have another program that calls your procedures and functions.

· How to speed up your work: First start by writing and completing the simple tasks. Make sure that you are DEBUGGING your code:

1. First thing to do in each procedure/function is to print out the values of the input parameters. This way you know that they were passed correctly, before you start working on the main part of the procedure/function.

2. Occasionally within the procedure/function print out the values of variables, just to make sure your procedure is progressing correctly.

3. Always use EXCEPTIONs to explain what went wrong. This will definitely speed up the implementation time. In addition, EXCEPTIONS ARE REQUIRED for every single procedure and function. There will be points taken off for missing exceptions.

Grading:

is420-project-sp-2022-1-0n0kveua.docx

Answered Same DayMay 14, 2022

Bikram · Accepted Answer

Database Design HGI hotels
CREATE TABLE Hotel(
    HotelID int not null,
    HotelName varchar2(50),
    City varchar2(50),
    State varchar2(50),
    Address varchar2(50),
    Phone varchar2(50),
  Country varchar2(50)
);
CREATE TABLE RoomType(
    RoomTypeID int not null,
    RoomType varchar2(50),
    MaxPerson int
);
CREATE TABLE Room(
    RoomID int not null,
    HotelID int,
    RoomTypeID int,
    RoomDescription varchar2(50),
    BreakFastIncluded CHAR(1),
    RoomLocked CHAR(1)
);
CREATE TABLE Price(
    PriceID int not null,
    RoomID int,
    OffseasonPrice int,
    OnseasonPrice int
);
CREATE TABLE Customer(
    CustomerID int not null,
    Name varchar2(50),
    Phone varchar2(50),
    Address varchar2(50),
    Email varchar2(50),
    CreaditCardNo varchar2(50)
);
CREATE TABLE Reservation(
    ReservationID int not null,
    CustomerID int ,
    RoomID int,
    Arrival Date,
    Checkout Date,
    Nights int,
    BookingDate Date,
  NoOFGuest Int
);
CREATE TABLE Cancellation(
    ReservationID int,
    CustomerID int ,
    RoomID int,
    Arrival Date,
    Checkout Date,
    Nights int,
    BookingDate Date,
    CancellationDate Date
);
CREATE TABLE Invoice(
    InvoiceID int not null,
    ReservationID int,
    Amoint int,
    Paid CHAR(1),
    Cancelled CHAR(1)
);
1. Add/delete a hotel
Add Hotel 
CREATE OR REPLACE PROCEDURE AddHotel (  
p_HotelName Hotel.HotelName%TYPE,  
p_City Hotel.City%TYPE,  
p_State Hotel.State%TYPE,
P_Address Hotel.Address%TYPE,
p_Phone Hotel.Phone%TYPE
)    
IS 
    tableid int;
    BEGIN  
    select nvl(max(hotelid),0)+1 into tableid from Hotel; 
    INSERT INTO Hotel (HotelID, HotelName, City,State,Address,Phone)  
    VALUES (tableid,p_HotelName, p_City,p_State,p_Address,P_Phone);  
    COMMIT;  
END;  
Find a hotel: 
 CREATE OR REPLACE PROCEDURE FindHotel (  
P_Address IN Hotel.Address%TYPE,
O_HotelID Out NUMBER
)    
IS 
BEGIN     
    Select HOTELID Into O_HotelID from HOtel where ADDRESS=P_Address;
   
END;
Delete Hotel
CREATE OR REPLACE PROCEDURE DeleteHotel (  p_HotelID Hotel.HotelID%TYPE)
IS
BEGIN
 DELETE FROM Hotel WHERE HotelID=p_HotelID;
 COMMIT;
END;
Function to return Room Price based on Date.
CREATE OR REPLACE Function GetRoomPrice
   ( inRoomId IN Int, ArrivalDate IN Date )
   RETURN number
IS
   roomPrice number;
BEGIN
 select case when  to_char(ArrivalDate,'MON') in ('SEP','OCT','NOV','DEC','JAN','FEB','MAR',

IS420 Database Application Development Spring 2022 Hotel Management System The actual tasks for the hotel management system are given below. Tasks : Some tasks are simpler than others. · Task...

Answer To: IS420 Database Application Development Spring 2022 Hotel Management System The actual tasks for...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment