Please I need help with the Part 3 and 4 Questions of this exercise. Thank you.

Exercise: Hotel Schema Overview Hotel Software Guild is a resort hotel on the West Arm of Lake Minnetonka. It is a relatively small hotel, with three floors of rooms. The top floor includes two large suites, and each of the other floors has eight standard-sized hotel rooms. The ground floor includes the customer service counter, a lounge area, a small exercise room, and a restaurant. The hotel staff currently uses Excel spreadsheets to keep track of the rooms, guests, and reservations, and they have hired you to convert the existing data into a more flexible relational database. You will build a database that satisfies at least second normal form (2NF) using the data provided here. While you are given the data itself, you are expected to identify the tables, fields, and relationships that will house this data. This exercise uses realistic data, but not necessarily real data. Some options (like extra beds or room service) that a real hotel is likely to include have been excluded to simplify the exercise. Hotel Data Rooms Room details are listed below: Rooms 201-204 and 301-304 . Each of these rooms is a Double room with two queen beds and can sleep up to 4 people. Each room includes a refrigerator or a microwave, but not both. Rooms 205-208 and 305-308 Each of these rooms is a Single room with a single king bed and can sleep up to 2 people. Each room includes both a microwave and a refrigerator. Rooms 401 and 402 . Each suite includes two separate bedrooms: one with two queen-sized beds and the other with one king-sized bed. Each suite also includes a shared living room space with a sleeper sofa. Each suite can sleep up to 8 people. Each suite includes a full kitchen, with a refrigerator, an electric stove and oven, and a microwave oven. Each suite is ADA accessible. Base Room Prices The base prices for the rooms are the following: Double: $174.99ight for 2 adults, with an additional $10 for each additional adult per night. Single: $149.99ight for 2 adults. Suite: $399.99ight for 3 adults, with an additional $20 for each additional adult per night. A jacuzzi adds $25 to the base price. . Additional Data All even-numbered rooms on the second and third floor are ADA accessible. All odd-numbered rooms on the second and third floor have a jacuzzi bath. Children (under age 18) do not incur additional charges if there is at least one adult per room on the reservation. Room Data The room data is as follows: Room Type Amenities ADA Standard Accessible Occupancy Maximum Occupancy Base Price Extra Person 201 No 4 $10 $199.99 $174.99 202 Yes 4 $10 203 No NNNNN 4 $199.99 $10 204 Yes 4 $10 $174.99 $174.99 205 No 2 NA 206 Yes N 2 $149.99 NA 207 No 2 2 $174.99 NA 208 Yes 2 2 $149.99 NA 301 No 4 $199.99 $10 302 Yes 4 $174.99 $10 Double Microwave, Jacuzzi Double Refrigerator Double Microwave, Jacuzzi Double Refrigerator Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator Double Microwave, Jacuzzi Double Refrigerator Double Microwave, Jacuzzi Double Refrigerator Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator, Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator Suite Microwave, Refrigerator, Oven Suite Microwave, Refrigerator, Oven N N N N 303 No 4 $199.99 $10 304 Yes 4 $174.99 $10 305 No 2 2 $174.99 NA 306 Yes 2 2 $149.99 NA 307 No 2 2 $174.99 NA 308 Yes 2 2 $149.99 NA 401 Yes 3 8 $399.99 $20 402 Yes 3 8 $399.99 $20 Guests Guest information is collected only when a guest makes a reservation, so all guests have at least one reservation in the system. Guests who have made reservations include the following: Name Address City State Zip Phone Your Name Your Address City State Zip Phone IA Council Bluffs Wasilla AK TX Mack Simmer Bettyann Seery Duane Cullison Karie Yang Aurore Lipton Zachery Luechtefeld Jeremiah Pendergrass Walter Holaway Wilfred Vise 379 Old Shore Street 750 Wintergreen Dr. 9662 Foxrun Lane 9378 W. Augusta Ave. 762 Wild Rose Street 7 Poplar Dr. 70 Oakwood St. NJ Harlingen West Deptford Saginaw Arvada MI CO 51501 (291) 553-0508 99654 (478) 277-9632 78552 (308) 494-0198 08096 (214) 730-0298 48601 (377) 507-0974 80003 (814) 485-2615 60099 (279) 491-0960 02864 (446) 396-6785 13126 (834) 727-1001 22015 (446) 351-6860 19026 (231) 893-2755 Zion IL 7556 Arrowhead St. Cumberland RI NY 77 West Surrey Street 939 Linda Rd. Oswego Burke Maritza Tilton VA Joleen Tison 87 Queen St. Drexel Hill PA Reservations The following reservations appear in the current reservation system: Room Number Name Adults Children Start Date End Date Total Room Cost 308 Mack Simmer 1 0 2/2/2023 2/4/2023 $299.98 203 2 1 2/5/2023 2/10/2023 $999.95 Bettyann Seery Duane Cullison 305 2 0 2/22/2023 2/24/2023 $349.98 201 Karie Yang 2 2 3/6/2023 3/7/2023 $199.99 307 Your Name 1 1 3/17/2023 3/20/2023 $524.97 302 Aurore Lipton 3 0 3/18/2023 3/23/2023 $924.95 202 Zachery Luechtefeld 2 2 3/31/2023 $349.98 3/29/2023 3/31/2023 304 2 0 4/5/2023 $874.95 Jeremiah Pendergrass Walter Holaway 301 1 0 4/9/2023 4/13/2023 $799.96 207 Wilfred Vise 1 1 4/23/2023 4/24/2023 $174.99 401 Maritza Tilton 2 4 5/30/2023 6/2/2023 $1,199.97 206 Joleen Tison 2 0 6/10/2023 6/14/2023 $599.96 208 Joleen Tison 1 0 6/10/2023 $599.96 6/14/2023 6/18/2023 304 Aurore Lipton 3 0 6/17/2023 $184.99 205 Your Name 2 0 7/2/2023 $699.96 6/28/2023 7/13/2023 204 Walter Holaway 3 1 7/14/2023 $184.99 401 Wilfred Vise 4 2 7/18/2023 7/21/2023 $1,259.97 303 Bettyann Seery 2 1 7/28/2023 7/29/2023 $199.99 305 Bettyann Seery 1 0 8/30/2023 9/1/2023 $349.98 208 Mack Simmer 2 0 9/17/2023 $149.99 9/16/2023 9/13/2023 203 Karie Yang 2 2 $399.98 401 Duane Cullison 2 2 11/22/2023 9/15/2023 11/25/2023 11/25/2023 $1,199.97 206 Mack Simmer 2 0 11/22/2023 $449.97 301 Mack Simmer 2 2 11/22/2023 11/25/2023 $599.97 302 Maritza Tilton 2 0 12/24/2023 12/28/2023 $699.96 Comments: In the database, a reservation can include multiple rooms reserved by the same guest and for the same dates. There are two examples in the table above. Adults are guests whose age is 18 or older. Children are guests under the age of 18. Start date represents the first evening the guest will stay at the hotel. End date represents the day the guest plans to check out. Assessment Instructions Part 1: ERD Using the data presented above, create an ERD that represents the database. Specifications for the ERD: The ERD should be created separately from the RDBMS. (E.g., Draw.io; shapes in Google Docs, Word, or PowerPoint; or paper and pencil) The design should be in second normal form. All tables and columns should be named appropriately so that it is easy to map data to the database. The ERD should specify the data type and field size (where appropriate) for each column. The ERD should indicate the nullability of all non-key fields. The ERD should identify primary keys and foreign keys in each table. The ERD should identify all relationships between tables. Save the ERD as a file named YourName-HotelERD.jpg. If you choose to use paper and pencil, submit a clear scan or image that is easy to read. When you have completed the ERD, submit it to your instructor for review, make any necessary changes based on feedback, and resubmit. Once the instructor has approved the ERD, you are ready to move to the next part. Part 2: Define the Database Using your ERD as an example, create a SQL script that performs the following steps: Drops the existing database, if it exists, so that the script can rebuild a database with the same name. Creates the tables with all appropriate fields, data types, and keys, using the structure indicated in the ERD o Tip: Create the primary tables first, followed by the tables with foreign keys. The script should run from beginning to end without errors. Name the file YourName-HotelDB.sql. Part 3: Manage the Data Create a separate SQL script to manage data in the tables. First, using the data provided in the tables above, create the required SQL statements to populate the tables with the data above. Include your name, address, and phone number in the first record of the table for guests. You may use a fictitious address and phone number, as long as you use your own name. As with the tables, add data to the primary tables before adding data to the tables with foreign keys. Second, after adding all of the data above, create SQL statements that will delete Jeremiah Pendergrass and his reservations from the database. Deleting data should start with records that reference Jeremiah Pendergrass using a foreign key and then delete the record from the guest table as the last step. The scripts should only delete records related to Jeremiah Pendergrass and his reservations. They should not delete any room data. The script should run from beginning to end without errors. Name the file YourName-HotelData.sql. Part 4: Query the Database Create a third SQL script that includes queries to retrieve the following data from the database. 1. Write a query that returns a list of reservations that end in July 2023, including the name of the guest, the room number(s), and the reservation dates. 2. Write a query that returns a list of all reservations for rooms with a jacuzzi, displaying the guest's name, the room number, and the dates of the reservation. 3. Write a query that returns all the rooms reserved for a specific guest, including the guest's name, the room(s) reserved, the starting date of the reservation, and how many people were included in the reservation. (Choose a guest's name from the existing data.) 4. Write a query that returns a list of rooms, reservation ID, and per-room cost for each reservation. The results should include all rooms, whether or not there is a reservation associated with the room. 5. Write a query that returns all the rooms accommodating at least three guests and that are reserved on any date in April 2023. 6. Write a query that returns a list of all guest names and the number of reservations per guest, sorted starting with the guest with the most reservations and then by the guest's last name. 7. Write a query that displays the name, address, and phone number of a guest based on their phone number. (Choose a phone number from the existing data.) For each query, include: The request from this assignment as a comment above the query, including the number The query itself The results of the query in a comment under the query Name the file YourName-HotelQueries.sql. Exercise: Hotel Schema Overview Hotel Software Guild is a resort hotel on the West Arm of Lake Minnetonka. It is a relatively small hotel, with three floors of rooms. The top floor includes two large suites, and each of the other floors has eight standard-sized hotel rooms. The ground floor includes the customer service counter, a lounge area, a small exercise room, and a restaurant. The hotel staff currently uses Excel spreadsheets to keep track of the rooms, guests, and reservations, and they have hired you to convert the existing data into a more flexible relational database. You will build a database that satisfies at least second normal form (2NF) using the data provided here. While you are given the data itself, you are expected to identify the tables, fields, and relationships that will house this data. This exercise uses realistic data, but not necessarily real data. Some options (like extra beds or room service) that a real hotel is likely to include have been excluded to simplify the exercise. Hotel Data Rooms Room details are listed below: Rooms 201-204 and 301-304 . Each of these rooms is a Double room with two queen beds and can sleep up to 4 people. Each room includes a refrigerator or a microwave, but not both. Rooms 205-208 and 305-308 Each of these rooms is a Single room with a single king bed and can sleep up to 2 people. Each room includes both a microwave and a refrigerator. Rooms 401 and 402 . Each suite includes two separate bedrooms: one with two queen-sized beds and the other with one king-sized bed. Each suite also includes a shared living room space with a sleeper sofa. Each suite can sleep up to 8 people. Each suite includes a full kitchen, with a refrigerator, an electric stove and oven, and a microwave oven. Each suite is ADA accessible. Base Room Prices The base prices for the rooms are the following: Double: $174.99ight for 2 adults, with an additional $10 for each additional adult per night. Single: $149.99ight for 2 adults. Suite: $399.99ight for 3 adults, with an additional $20 for each additional adult per night. A jacuzzi adds $25 to the base price. . Additional Data All even-numbered rooms on the second and third floor are ADA accessible. All odd-numbered rooms on the second and third floor have a jacuzzi bath. Children (under age 18) do not incur additional charges if there is at least one adult per room on the reservation. Room Data The room data is as follows: Room Type Amenities ADA Standard Accessible Occupancy Maximum Occupancy Base Price Extra Person 201 No 4 $10 $199.99 $174.99 202 Yes 4 $10 203 No NNNNN 4 $199.99 $10 204 Yes 4 $10 $174.99 $174.99 205 No 2 NA 206 Yes N 2 $149.99 NA 207 No 2 2 $174.99 NA 208 Yes 2 2 $149.99 NA 301 No 4 $199.99 $10 302 Yes 4 $174.99 $10 Double Microwave, Jacuzzi Double Refrigerator Double Microwave, Jacuzzi Double Refrigerator Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator Double Microwave, Jacuzzi Double Refrigerator Double Microwave, Jacuzzi Double Refrigerator Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator, Single Microwave, Refrigerator, Jacuzzi Single Microwave, Refrigerator Suite Microwave, Refrigerator, Oven Suite Microwave, Refrigerator, Oven N N N N 303 No 4 $199.99 $10 304 Yes 4 $174.99 $10 305 No 2 2 $174.99 NA 306 Yes 2 2 $149.99 NA 307 No 2 2 $174.99 NA 308 Yes 2 2 $149.99 NA 401 Yes 3 8 $399.99 $20 402 Yes 3 8 $399.99 $20 Guests Guest information is collected only when a guest makes a reservation, so all guests have at least one reservation in the system. Guests who have made reservations include the following: Name Address City State Zip Phone Your Name Your Address City State Zip Phone IA Council Bluffs Wasilla AK TX Mack Simmer Bettyann Seery Duane Cullison Karie Yang Aurore Lipton Zachery Luechtefeld Jeremiah Pendergrass Walter Holaway Wilfred Vise 379 Old Shore Street 750 Wintergreen Dr. 9662 Foxrun Lane 9378 W. Augusta Ave. 762 Wild Rose Street 7 Poplar Dr. 70 Oakwood St. NJ Harlingen West Deptford Saginaw Arvada MI CO 51501 (291) 553-0508 99654 (478) 277-9632 78552 (308) 494-0198 08096 (214) 730-0298 48601 (377) 507-0974 80003 (814) 485-2615 60099 (279) 491-0960 02864 (446) 396-6785 13126 (834) 727-1001 22015 (446) 351-6860 19026 (231) 893-2755 Zion IL 7556 Arrowhead St. Cumberland RI NY 77 West Surrey Street 939 Linda Rd. Oswego Burke Maritza Tilton VA Joleen Tison 87 Queen St. Drexel Hill PA Reservations The following reservations appear in the current reservation system: Room Number Name Adults Children Start Date End Date Total Room Cost 308 Mack Simmer 1 0 2/2/2023 2/4/2023 $299.98 203 2 1 2/5/2023 2/10/2023 $999.95 Bettyann Seery Duane Cullison 305 2 0 2/22/2023 2/24/2023 $349.98 201 Karie Yang 2 2 3/6/2023 3/7/2023 $199.99 307 Your Name 1 1 3/17/2023 3/20/2023 $524.97 302 Aurore Lipton 3 0 3/18/2023 3/23/2023 $924.95 202 Zachery Luechtefeld 2 2 3/31/2023 $349.98 3/29/2023 3/31/2023 304 2 0 4/5/2023 $874.95 Jeremiah Pendergrass Walter Holaway 301 1 0 4/9/2023 4/13/2023 $799.96 207 Wilfred Vise 1 1 4/23/2023 4/24/2023 $174.99 401 Maritza Tilton 2 4 5/30/2023 6/2/2023 $1,199.97 206 Joleen Tison 2 0 6/10/2023 6/14/2023 $599.96 208 Joleen Tison 1 0 6/10/2023 $599.96 6/14/2023 6/18/2023 304 Aurore Lipton 3 0 6/17/2023 $184.99 205 Your Name 2 0 7/2/2023 $699.96 6/28/2023 7/13/2023 204 Walter Holaway 3 1 7/14/2023 $184.99 401 Wilfred Vise 4 2 7/18/2023 7/21/2023 $1,259.97 303 Bettyann Seery 2 1 7/28/2023 7/29/2023 $199.99 305 Bettyann Seery 1 0 8/30/2023 9/1/2023 $349.98 208 Mack Simmer 2 0 9/17/2023 $149.99 9/16/2023 9/13/2023 203 Karie Yang 2 2 $399.98 401 Duane Cullison 2 2 11/22/2023 9/15/2023 11/25/2023 11/25/2023 $1,199.97 206 Mack Simmer 2 0 11/22/2023 $449.97 301 Mack Simmer 2 2 11/22/2023 11/25/2023 $599.97 302 Maritza Tilton 2 0 12/24/2023 12/28/2023 $699.96 Comments: In the database, a reservation can include multiple rooms reserved by the same guest and for the same dates. There are two examples in the table above. Adults are guests whose age is 18 or older. Children are guests under the age of 18. Start date represents the first evening the guest will stay at the hotel. End date represents the day the guest plans to check out. Assessment Instructions Part 1: ERD Using the data presented above, create an ERD that represents the database. Specifications for the ERD: The ERD should be created separately from the RDBMS. (E.g., Draw.io; shapes in Google Docs, Word, or PowerPoint; or paper and pencil) The design should be in second normal form. All tables and columns should be named appropriately so that it is easy to map data to the database. The ERD should specify the data type and field size (where appropriate) for each column. The ERD should indicate the nullability of all non-key fields. The ERD should identify primary keys and foreign keys in each table. The ERD should identify all relationships between tables. Save the ERD as a file named YourName-HotelERD.jpg. If you choose to use paper and pencil, submit a clear scan or image that is easy to read. When you have completed the ERD, submit it to your instructor for review, make any necessary changes based on feedback, and resubmit. Once the instructor has approved the ERD, you are ready to move to the next part. Part 2: Define the Database Using your ERD as an example, create a SQL script that performs the following steps: Drops the existing database, if it exists, so that the script can rebuild a database with the same name. Creates the tables with all appropriate fields, data types, and keys, using the structure indicated in the ERD o Tip: Create the primary tables first, followed by the tables with foreign keys. The script should run from beginning to end without errors. Name the file YourName-HotelDB.sql. Part 3: Manage the Data Create a separate SQL script to manage data in the tables. First, using the data provided in the tables above, create the required SQL statements to populate the tables with the data above. Include your name, address, and phone number in the first record of the table for guests. You may use a fictitious address and phone number, as long as you use your own name. As with the tables, add data to the primary tables before adding data to the tables with foreign keys. Second, after adding all of the data above, create SQL statements that will delete Jeremiah Pendergrass and his reservations from the database. Deleting data should start with records that reference Jeremiah Pendergrass using a foreign key and then delete the record from the guest table as the last step. The scripts should only delete records related to Jeremiah Pendergrass and his reservations. They should not delete any room data. The script should run from beginning to end without errors. Name the file YourName-HotelData.sql. Part 4: Query the Database Create a third SQL script that includes queries to retrieve the following data from the database. 1. Write a query that returns a list of reservations that end in July 2023, including the name of the guest, the room number(s), and the reservation dates. 2. Write a query that returns a list of all reservations for rooms with a jacuzzi, displaying the guest's name, the room number, and the dates of the reservation. 3. Write a query that returns all the rooms reserved for a specific guest, including the guest's name, the room(s) reserved, the starting date of the reservation, and how many people were included in the reservation. (Choose a guest's name from the existing data.) 4. Write a query that returns a list of rooms, reservation ID, and per-room cost for each reservation. The results should include all rooms, whether or not there is a reservation associated with the room. 5. Write a query that returns all the rooms accommodating at least three guests and that are reserved on any date in April 2023. 6. Write a query that returns a list of all guest names and the number of reservations per guest, sorted starting with the guest with the most reservations and then by the guest's last name. 7. Write a query that displays the name, address, and phone number of a guest based on their phone number. (Choose a phone number from the existing data.) For each query, include: The request from this assignment as a comment above the query, including the number The query itself The results of the query in a comment under the query Name the file YourName-HotelQueries.sql