PL SQL CODE

PL . SQL CODE

PL SQL CODE

GIVEN THE BELOW TABLES :

create table room_info (

Reservation_ID int,

room_number number not null,

hotel_code number,

room_type varchar (200),

room_rate varchar (50),

room_status varchar (50),

hotel_city varchar (150),

hotel_state varchar (150),

Primary Key (room_number));

create table Resident (

Customer_ID Int not null,

Reservation_ID int not null,

Customer_Name Varchar (200),

Room_Number number,

Phone_number Varchar (200),

email_address varchar (200),

Primary Key (Customer_ID),

Foreign Key (Room_number) references room_info(room_number));

create table hotel_info (

Hotel_code int not null,

Hotel_Name varchar (200),

Hotel_Phone_Number Varchar (200),

Hotel_address varchar (200),

Hotel_city varchar (100),

Hotel_State varchar (10),

Hotel_zip_code varchar (200),

Hotel_Status varchar (100),

Total_Single_Bed_Rooms number,

Total_Double_Bed_Rooms number,

Total_rooms number,

Primary Key (hotel_code));

create table Reservation (

Reservation_ID int not null,

Customer_ID int not null,

room_number number not null,

Hotel_code number not null,

reservation_date Date,

Check_in_date Date,

check_out_date Date,

Reservation_status varchar (200),

Total_balance Varchar (200),

Primary Key (Reservation_ID),

Foreign Key (Room_number) references room_info(room_number),

Foreign Key (Customer_ID) references resident (customer_id),

Foreign Key (Hotel_code) references hotel_info(Hotel_code)

);

Create table Payment_info (

Customer_Id int not null,

Reservation_ID int not null,

Payment_id Varchar (200),

Payment_type varchar (100),

Total_balance Varchar (200),

Balance_Due Varchar (200),

Discount varchar (150),

Services_total varchar (150),

Primary Key (Payment_ID),

Foreign key (Customer_ID) references resident(Customer_ID),

Foreign key (reservation_ID) references reservation(reservation_ID));

Create table Services (

Service_number int not null,

Customer_ID int not null,

Reservation_ID int not null,

Movie_services Varchar (200),

Laundary_services Varchar (200),

Resturant_services Varchar (200),

Service_Total Varchar (200),

primary key (service_number),

Foreign key (customer_id) references resident(customer_id),

Foreign key (reservation_ID) references reservation(reservation_ID));

WRITE A PL SQL CODE FOR

Add a service to a reservation: Input: ReservationID, specific service. Add the service to the reservation for a particular date. Multiple services are allowed on a reservation for the same date.