1- Install MySQL on your system if it is not already installed. https://dev.mysql.com/downloads/workbench/

2-	SET SESSION sql_mode='ANSI';

This command sets the MySQL session to use the ANSI SQL standard mode. This means that MySQL will follow the ANSI SQL standard rules for handling queries, which may differ from the default MySQL behavior.

3-	DROP DATABASE IF EXISTS IU_Airbnb;

This command drops the database named IU_Airbnb, if it exists. If the database does not exist, this command does nothing.

4-	CREATE DATABASE IU_Airbnb;

This command creates a new database named IU_Airbnb.

5-	USE IU_Airbnb;

This command switches to the IU_Airbnb database. All subsequent queries will be executed in the context of this database. Installation and Configuration:

6- Run the CREATE TABLE statements for each table in the database, in the following order:

1. guest_profile
2. host_profile
3. login_guest
4. login_host
5. guest_about
6. guest_card_details
7. identity_verification
8. emergency_contact
9. host_place
10. rooms_spaces
11. place_location
12. place_highlight
13. property_guests
14. place_offer
15. reservation
16. guest_payment_type
17. setup_payouts
18. transaction_approval
19. host_payment_type
20. payout_methods
21. price_calculator
22. review_guest
23. review_host

7- Once all tables are created, run the INSERT INTO statements to populate the tables with sample data.

Welcome to the documentation manual for the MySQL database schema used in our Airbnb-like platform. This manual is designed to provide a comprehensive overview of the database schema, including its tables, columns, relationships, and constraints. It is intended for developers, database administrators, and other stakeholders who need to understand how the database schema works and how to use it effectively. The manual includes detailed descriptions of each table, column, and constraint in the schema, as well as examples of how to use the database schema to perform common tasks. We hope this manual will serve as a helpful resource for anyone working with our reservation and payment processing platform.

3.1. Table name: guest_profile The table stores information about guests who have signed up on the platform. The table has the following columns: • guest_id: Unique ID for each guest, auto-generated by the system. • last_name: Guest's last name. • first_name: Guest's first name. • phone_number: Guest's phone number. • profile_picture: Guest's profile picture in binary format. • join_date: Date when the guest signed up on the platform. • work_email: Guest's work email address (optional). • gov_id_verification: A boolean flag indicating whether the guest's government ID has been verified (optional).

3.2. Table name: host_profile The table stores information about hosts who have signed up on the platform. The table has the following columns: • host_id: Unique ID for each host, auto-generated by the system. • first_name: Host's first name. • last_name: Host's last name. • profile_pic: Host's profile picture in binary format. • location: Host's location.

3.3. Table name: login_guest The table stores login credentials for guests. The table has the following columns: • login_guest_id: Unique ID for each login credential, auto-generated by the system. • username: Guest's username for login. • password: Guest's password for login (stored in hashed format). • guest_id: Foreign key reference to the guest_profile table, to link the login credential to the corresponding guest.

3.4. Table name: login_host The table stores login credentials for hosts. The table has the following columns: • login_host_id: Unique ID for each login credential, auto-generated by the system. • username: Host's username for login. • password: Host's password for login (stored in hashed format). • host_id: Foreign key reference to the host_profile table, to link the login credential to the corresponding host.

3.5. Table name: guest_about The table stores additional information about the guest's profile, such as their work and language preferences. The table has the following columns: • guest_about_id: Unique ID for each guest's additional profile information, auto-generated by the system. • guest_id: Foreign key reference to the guest_profile table, to link the additional information to the corresponding guest. • location: Guest's location. • work: Guest's work information (optional). • language: Guest's language preferences (optional).

3.6. Table name: guest_card_details The table stores the guest's credit card details for payment processing. The table has the following columns: • guest_card_id: Unique ID for each guest's credit card information, auto-generated by the system. • card_number: Guest's credit card number. • expiration_date: Guest's credit card expiration date. • cvv: Guest's credit card cvv. • zip_code: Guest's zip code. • guest_id: Foreign key reference to the guest_profile table, to link the credit card information to the corresponding guest.

3.7. Table name: identity_verification The table stores the guest's government ID verification information. The table has the following columns: • identity_verification_id: Unique ID for each identity verification, auto-generated by the system. • issuing_country: Country where the government ID was issued. • driver's_license: A boolean flag indicating whether the guest's driver's license has been verified. • passport: A boolean flag indicating whether the guest's passport has been verified. • identity_card: A boolean flag indicating whether the guest's identity card has been verified. • id_image: Guest's government ID image in binary format. • guest_id: Foreign key reference to the guest_profile table, to link the verification information to the corresponding guest.

3.8. Table name: emergency_contact The table stores the guest's emergency contact information. The table has the following columns: • emergency_contact_id: Unique ID for each emergency contact, auto-generated by the system. • name: Emergency contact's name. • relationship: Emergency contact's relationship to the guest. • email: Emergency contact's email address (optional). • phone_number: Emergency contact's phone number. • prefered_language: Emergency contact's preferred language (optional). • guest_id: Foreign key reference to the guest_profile table, to link the emergency contact to the corresponding guest.

3.9. Table name: host_place The table stores information about the places that hosts have listed on the platform. The table has the following columns: • place_id: Unique ID for each place, auto-generated by the system. • title: Title of the place. • description: Description of the place. • pic_1: First picture of the place in binary format. • pic_2: Second picture of the place in binary format. • pic_3: Third picture of the place in binary format. • pic_4: Fourth picture of the place in binary format. • pic_5: Fifth picture of the place in binary format. • host_id: Foreign key reference to the host_profile table, to link the place to the corresponding host.

3.10. Table name: rooms_spaces The table stores information about the rooms and spaces in the place listed by the host. The table has the following columns: • rooms_spaces_id: Unique ID for each room/space, auto-generated by the system. • bedroom: Number of bedrooms in the place. • full_bathroom: Number of full bathrooms in the place. • living_room: Number of living rooms in the place. • exterior: Number of exterior spaces in the place. • place_id: Foreign key reference to the host_place table, to link the rooms/spaces to the corresponding place.

3.11. Table name: place_location The table stores the location information for the place listed by the host. The table has the following columns: • place_location_id: Unique ID for each place's location information, auto-generated by the system. • address: Address of the place. • longitude: Longitude of the place's location. • latitude: Latitude of the place's location. • place_id: Foreign key reference to the host_place table, to link the location information to the corresponding place.

3.12. Table name: place_highlight The table stores the highlights or unique features of the place listed by the host. The table has the following columns: • place_highlight_id: Unique ID for each place's highlight, auto-generated by the system. • peaceful: A boolean flag indicating whether the place is peaceful. • unique: A boolean flag indicating whether the place is unique. • family_friendly: A boolean flag indicating whether the place is family-friendly. • stylish: A boolean flag indicating whether the place is stylish. • central: A boolean flag indicating whether the place is central. • spacious: A boolean flag indicating whether the place is spacious. • place_id: Foreign key reference to the host_place table, to link the highlights to the corresponding place.

3.13. Table Name: property_guests The table stores information about the property guests. The table has the following columns: • property_guest_id: Unique identifier for each property guest (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • place_type: The type of place (e.g. apartment, house, villa, etc.) (VARCHAR(255), NOT NULL). • property_type: The type of property (e.g. entire place, private room, shared room, etc.) (VARCHAR(255), NOT NULL). • room_type: The type of room (e.g. bedroom, living room, etc.) (VARCHAR(255), NULL). • year_built: The year the property was built (DATE, NULL). • property_size: The size of the property in square feet (INT, NULL). • number_of_guests: The maximum number of guests allowed in the property (INT, NOT NULL). • place_id: The unique identifier of the property (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_place(place_id) ON DELETE CASCADE ON UPDATE CASCADE). • PRIMARY KEY: property_guest_id

3.14. Table Name: place_offer The table stores information about the amenities offered by the property. The table has the following columns: • place_offer_id: Unique identifier for each place offer (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • wifi: Whether the property has Wi-Fi (BOOLEAN, NOT NULL). • tv: Whether the property has TV (BOOLEAN, NOT NULL). • kitchen: Whether the property has a kitchen (BOOLEAN, NOT NULL). • washer: Whether the property has a washer (BOOLEAN, NOT NULL). • free_parking: Whether the property has free parking (BOOLEAN, NOT NULL). • paid_parking: Whether the property has paid parking (BOOLEAN, NOT NULL). • ac: Whether the property has air conditioning (BOOLEAN, NOT NULL). • dedicated_workplace: Whether the property has a dedicated workplace (BOOLEAN, NOT NULL). • pool: Whether the property has a pool (BOOLEAN, NOT NULL). • hot_tub: Whether the property has a hot tub (BOOLEAN, NOT NULL). • patio: Whether the property has a patio (BOOLEAN, NOT NULL). • bbq_grill: Whether the property has a BBQ grill (BOOLEAN, NOT NULL). • outdoor_dining_area: Whether the property has an outdoor dining area (BOOLEAN, NOT NULL). • fire_pit: Whether the property has a fire pit (BOOLEAN, NOT NULL). • pool_table: Whether the property has a pool table (BOOLEAN, NOT NULL). • indoor_fireplace: Whether the property has an indoor fireplace (BOOLEAN, NOT NULL). • piano: Whether the property has a piano (BOOLEAN, NOT NULL). • exercise_equipment: Whether the property has exercise equipment (BOOLEAN, NOT NULL). • lake_access: Whether the property has access to a lake (BOOLEAN, NOT NULL). • beach_access: Whether the property has access to a beach (BOOLEAN, NOT NULL). • ski-in_ski-out: Whether the property offers ski-in/ski-out access (BOOLEAN, NOT NULL). • outdoor_shower: Whether the property has an outdoor shower (BOOLEAN, NOT NULL). • smoke_alarm: Whether the property has a smoke alarm (BOOLEAN, NOT NULL). • first_aid_kit: Whether the property has a first aid kit (BOOLEAN, NOT NULL). • fire_extinguisher: Whether the property has a fire extinguisher (BOOLEAN, NOT NULL). • carbon_monoxide_alarm: Whether the property has a carbon monoxide alarm (BOOLEAN, NOT NULL). • place_id: The unique identifier of the property (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_place(place_id) ON DELETE CASCADE ON UPDATE CASCADE). • PRIMARY KEY: place_offer_id

3.15. Table Name: reservation The table stores information about the reservations made by guests. The table has the following columns: • reservation_id: Unique identifier for each reservation (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • guest_id: The unique identifier of the guest making the reservation (INT, UNSIGNED, NOT NULL, FOREIGN KEY to guest_profile(guest_id) ON DELETE CASCADE ON UPDATE CASCADE). • host_id: The unique identifier of the host whose property is being reserved (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_profile(host_id) ON DELETE CASCADE ON UPDATE CASCADE). • place_id: The unique identifier of the property being reserved (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_place(place_id) ON DELETE CASCADE ON UPDATE CASCADE). • check_in: The date and time of the guest's check-in (DATETIME, NOT NULL). • check_out: The date and time of the guest's check-out (DATETIME, NOT NULL). • guest_number: The number of guests included in the reservation (INT, NOT NULL). • nights_number: The number of nights for which the reservation is made (INT, NOT NULL, INDEX NIGHTS_NUMBER_INDEX). • work_trip: Whether the trip is a work trip or not (BOOLEAN, NOT NULL). • PRIMARY KEY: reservation_id

3.16. Table Name: guest_payment_type The table stores information about the payment methods available to the guest. The table has the following columns: • guest_payment_type_id: Unique identifier for each payment type used by the guest (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • credit_debt_card: Whether the guest can pay using a credit/debit card or not (BOOLEAN, NOT NULL). • paypal: Whether the guest can pay using PayPal or not (BOOLEAN, NOT NULL). • coupon: Whether the guest can pay using a coupon or not (BOOLEAN, NULL). • guest_id: The unique identifier of the guest who can use the payment method (INT, UNSIGNED, NOT NULL, FOREIGN KEY to guest_profile(guest_id) ON DELETE CASCADE ON UPDATE CASCADE). • PRIMARY KEY: guest_payment_type_id

3.17. Table Name: setup_payouts The table stores information about the payout setup for the host. The table has the following columns: • setup_payouts_id: Unique identifier for each payout setup (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • host_id: The unique identifier of the host whose payout setup is being managed (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_profile(host_id) ON DELETE CASCADE ON UPDATE CASCADE). • account_holder_name: The name of the account holder (VARCHAR(255), NOT NULL). • street_address: The street address of the account holder (VARCHAR(255), NOT NULL). • apt_suite_bldg: The apartment/suite/building number of the account holder (VARCHAR(255), NULL). • city: The city of the account holder (VARCHAR(255), NOT NULL). • state: The state of the account holder (VARCHAR(255), NOT NULL). • zip_code: The zip code of the account holder (INT, NOT NULL). • country: The country of the account holder (VARCHAR(255), NOT NULL). • commission_host: The commission charged to the host for the payout (INT, NOT NULL). • PRIMARY KEY: setup_payouts_id

3.18. Table Name: transaction_approval The table stores information about the approval of a transaction by the host. The table has the following columns: • transaction_id: Unique identifier for each transaction (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • reservation_id: The unique identifier of the reservation for which the transaction is being approved (INT, UNSIGNED, NOT NULL, FOREIGN KEY to reservation(reservation_id) ON DELETE CASCADE ON UPDATE CASCADE). • arrival_date: The date and time of the guest's arrival (DATETIME, NOT NULL). • 24_hrs_passed: Whether 24 hours have passed since the guest's check-in (BOOLEAN, NOT NULL, INDEX 24_hrs_passed_INDEX). • host_id: The unique identifier of the host approving the transaction (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_profile(host_id) ON DELETE CASCADE ON UPDATE CASCADE). • guest_id: The unique identifier of the guest making the reservation (INT, UNSIGNED, NOT NULL, FOREIGN KEY to guest_profile(guest_id) ON DELETE CASCADE ON UPDATE CASCADE). • guest_card_id: The unique identifier of the card used by the guest for payment (INT, UNSIGNED, NOT NULL, FOREIGN KEY to guest_card_details(guest_card_id) ON DELETE CASCADE ON UPDATE CASCADE). • commission_guest: The commission charged to the guest for the transaction (INT, NOT NULL). • PRIMARY KEY: transaction_id

3.19. Table Name: host_payment_type The table stores information about the payment methods available to the host. The table has the following columns: • host_payment_type_id: Unique identifier for each payment type used by the host (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • wire_transfer: Whether the host can receive payment via wire transfer or not (BOOLEAN, NOT NULL). • payoneer: Whether the host can receive payment via Payoneer or not (BOOLEAN, NOT NULL). • host_id: The unique identifier of the host who can use the payment method (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_profile(host_id) ON DELETE CASCADE ON UPDATE CASCADE). • PRIMARY KEY: host_payment_type_id

3.20. Table Name: payout_methods The table stores information about the payout methods for a transaction. The table has the following columns: • payout_methods_id: Unique identifier for each payout method used for a transaction (INT, UNSIGNED, NOT NULL, AUTO_INCREMENT). • host_id: The unique identifier of the host receiving the payout (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_profile(host_id) ON DELETE CASCADE ON UPDATE CASCADE). • setup_payouts_id: The unique identifier of the payout setup being used for the payout (INT, UNSIGNED, NOT NULL, FOREIGN KEY to setup_payouts(setup_payouts_id) ON DELETE CASCADE ON UPDATE CASCADE). • host_payment_type_id: The unique identifier of the payment type being used by the host for the payout (INT, UNSIGNED, NOT NULL, FOREIGN KEY to host_payment_type(host_payment_type_id) ON DELETE CASCADE ON UPDATE CASCADE). • final_payout: The final amount of the payout (INT, NOT NULL). • payout_date: The date and time of the payout (DATETIME, NOT NULL). • 24_hrs_passed: Whether 24 hours have passed since the guest's check-in (BOOLEAN, NOT NULL, FOREIGN KEY to transaction_approval(24_hrs_passed) ON DELETE CASCADE ON UPDATE CASCADE). • PRIMARY KEY: payout_methods_id

3.21. Table: price_calculator The table stores the calculated prices for a reservation. The table has the following columns: • price_id: unique identifier for each price calculation. • reservation_id: a foreign key referencing the reservation table's reservation_id. • nights_number: number of nights the reservation will last. • night_price: the price per night for the reservation. • cleaning_fees: the fees charged for cleaning the place after the guest leaves. • service_fees: the fees charged for using the platform's services. • taxes: the taxes charged on the reservation. • total_USD: the total amount in US dollars for the reservation. • The table has a primary key on price_id and foreign keys on reservation_id and nights_number referencing reservation table's reservation_id and nights_number, respectively. • The foreign keys are set to cascade on delete and update.

3.22. Table: review_guest The table stores the reviews made by guests after their stay. The table has the following columns: • review_guest_id: unique identifier for each guest review. • host_id: a foreign key referencing the host_profile table's host_id. • guest_id: a foreign key referencing the guest_profile table's guest_id. • stars: the rating given by the guest on a scale of 1 to 5 stars. • comment: the written comments made by the guest about their stay. • The table has a primary key on review_guest_id and foreign keys on host_id and guest_id referencing host_profile and guest_profile tables' host_id and guest_id, respectively. • The foreign keys are set to cascade on delete and update.

3.23. Table: review_host The table stores the reviews made by hosts after a guest's stay. The table has the following columns: • review_host_id: unique identifier for each host review. • guest_id: a foreign key referencing the guest_profile table's guest_id. • host_id: a foreign key referencing the host_profile table's host_id. • reservation_id: a foreign key referencing the reservation table's reservation_id. • stars: the rating given by the host on a scale of 1 to 5 stars. • comment: the written comments made by the host about the guest's stay. • The table has a primary key on review_host_id and foreign keys on guest_id, host_id, and reservation_id referencing guest_profile, host_profile, and reservation tables' guest_id, host_id, and reservation_id respectively. • The foreign keys are set to cascade on delete and update.