WRITE THE FUNCTIONAL IN PL/ASQ:

 

 

1.The system should allow employees to add, edit, and delete properties, as well as view property details such as the address, number of bedrooms and bathrooms, and amenities.

 

2.The system should allow employees to create, edit, and delete rental listings, as well as view listing details such as the property owner, price, description, and photos.

 

3.The system should allow employees to add, edit, and delete tenants, as well as view tenant details such as the tenant's name, phone number, and email.

 

4.The system should allow employees to create, edit, and delete rental agreements, as well as view rental details such as the start and end dates of the rental, the rent amount, and the associated rental listing and tenant.

 

5.The system should allow tenants to submit maintenance requests for their rented properties.

 

6.The system should allow employees/property owners to view and manage maintenance requests, as well as assign maintenance tasks to their maintenance staff.

 

THIS THE TABELS:

CREATE TABLE prj_properties (

property_id NUMBER PRIMARY KEY ,

address VARCHAR2(255) NOT NULL,

num_bedrooms NUMBER NOT NULL,

num_bathrooms NUMBER NOT NULL,

amenities VARCHAR2(255)

);

 

CREATE TABLE prj_property_owners (

property_owner_id NUMBER PRIMARY KEY,

name VARCHAR2(255) NOT NULL,

email VARCHAR2(255) NOT NULL,

phone_number VARCHAR2(20) NOT NULL

);

 

CREATE TABLE prj_rental_listings (

rental_listing_id NUMBER PRIMARY KEY ,

property_id NUMBER NOT NULL,

property_owner_id NUMBER NOT NULL,

price NUMBER(10, 2) NOT NULL,

description VARCHAR2(255),

photos VARCHAR2(255),

FOREIGN KEY (property_id) REFERENCES prj_properties(property_id),

FOREIGN KEY (property_owner_id) REFERENCES prj_property_owners(property_owner_id)

);

 

CREATE TABLE prj_tenants (

tenant_id NUMBER PRIMARY KEY ,

name VARCHAR2(255) NOT NULL,

phone_number VARCHAR2(20) NOT NULL,

email VARCHAR2(255) NOT NULL,

employment_status VARCHAR2(255),

income NUMBER(10, 2)

);

 

CREATE TABLE prj_rental_agreements (

rental_agreement_id NUMBER PRIMARY KEY ,

rental_listing_id NUMBER NOT NULL,

tenant_id NUMBER NOT NULL,

start_date DATE NOT NULL,

end_date DATE NOT NULL,

rent NUMBER(10, 2) NOT NULL,

FOREIGN KEY (rental_listing_id) REFERENCES prj_rental_listings(rental_listing_id),

FOREIGN KEY (tenant_id) REFERENCES prj_tenants(tenant_id)

);

 

CREATE TABLE prj_maintenance_requests (

maintenance_request_id NUMBER PRIMARY KEY ,

rental_listing_id NUMBER NOT NULL,

tenant_id NUMBER NOT NULL,

description VARCHAR2(255) NOT NULL,

request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

status ENUM('Open', 'In Progress', 'Completed') DEFAULT 'Open',

completion_date TIMESTAMP,

FOREIGN KEY (rental_listing_id) REFERENCES prj_rental_listings(rental_listing_id),

FOREIGN KEY (tenant_id) REFERENCES prj_tenants(tenant_id)

);

 

CREATE TABLE prj_payments (

payment_id NUMBER PRIMARY KEY ,

rental_agreement_id NUMBER NOT NULL,

amount NUMBER(10, 2) NOT NULL,

payment_date DATE NOT NULL,

FOREIGN KEY (rental_agreement_id) REFERENCES prj_rental_agreements(rental_agreement_id)

);