everything provided below

AFTER ACCEPTING I WILL PROVIDE MORE INFO AND I MAY ASK FOR CHANGES BASED ON WHAT I SEE FIT Context Description:

In this project, you are going to design a relational database system for a hotel reservation management system.

The hotel manages multiple rooms of different types (e.g., single, double, suite), guests, and staff members. Guests can make reservations for specific dates, and staff are responsible for handling bookings, check-ins, check-outs, and cancellations.

The hotel manager plans to expand the business by adding more rooms and services (such as spa and dining).

Guests often pay a deposit at the time of reservation and the remaining balance upon check-out. The system must support multiple payment methods (Credit Card, Cash, Loyalty Points) and handle partial refunds for cancellations.

You are required to design the database solution, implement it in your Database Management System, and develop a Python application that connects to the database you created..

Requirments:

You can work in a**** group of 3**** to complete the requirements below.

1. Data Modeling:

a. Based on the problem description, assume you are going to meet the hotel manager and ask one question. State the question, explain why it is necessary, and what data you aim to collect.

b. Identify tables, column names, primary keys, foreign keys, and sample data for all the tables.

c. Make sure all your tables are normalized up to the third normal form.

d. Provide an Entity Relationship Diagram (ERD) that captures the tables, attributes, relationships, participation types, participation degrees, and deletion rules. Justify your choices.

2. Database Creation:

a. Based on your database design, use your database management system (XAMPP/ MYSQL server) to create your database: Write the SQL statement that builds each table in the database. Make sure you add the appropriate datatypes, sizes, constraints and deletion rules. Provide a screenshot of the output after successfully executing the CREATE TABLE statement for every table in the DBMS.

b. Write SQL statements that populate the database with realistic data for 5 to 10 records. Ensure that the data is realistic. Take screenshots for every table populated in the DBMS.

3. Simple Queries:

Answer the following simple queries by writing the corresponding SQL statement and showing the resultant DBMS output, and add a simple explanation. Note that these queries do not require JOIN.

a. Write a query that analyzes room usage and presents the results in a meaningful way that helps classify rooms based on their reservation activity. Your output should include Room-related information and a derived classification that reflects usage level.

b. Write a query to retrieve guests whose names match specific letters of your choice, who booked a suite or deluxe room, and who made at least one reservation before.

Note: Please Do NOT use syntax or keywords that we did not introduce in the classes.

4. Advanced queries:

Answer the following advanced queries by writing the corresponding SQL statement and showing the resultant DBMS output and add a simple explanation for each. Ensure all your queries are optimized and that the corresponding SQL statements are justified.

a. Which room types generate the highest total revenue, and how do the types rank against each other in overall performance?

b. Can you provide a list of the guest stays and the staff involved in managing those reservations for the previous year?

c. Which reservations have overlapping times or resource conflicts that violate the reservation rules?

d. Create a view that shares the monthly financial and reservation data.

e. A staff member is attempting to create a room reservation, but the system must ensure that no two staff can book the same room for overlapping dates; implement a transaction to prevent such conflicts while ensuring ACID properties.

f. How can we restructure the Reservations table to improve data retrieval speed as it grows over several years by organizing it into yearly segments?

5. Database application:

Build a Python-based database application that connects with the implemented database. Your application can be either menu-driven or a graphical user interface (your instructor will tell you which is preferred). The application should allow the following:

a. Insert new records into a selected table from your database (for instance, you could allow the user to enter guest information).

b. Update data in another table from your database (for instance, you could allow the updating of reservations).

c. Display data from a single table, and from related tables of your choice (you could show reservations with the rooms and guests’ details).

d. Include a search or filter feature that retrieves specific data based on user input.

e. You should include in your report screenshots of the database connection code sections, as well as each of the requirements listed in points a-d above, and the result output when running the code.

Submission

1. A PDFfile that contains the following: Answers to all the questions with all required screenshots- submitted as a primarysubmission file.
2. A Python file (.py) that contains the code for the database application.
3. Submit a single sql file containing all code needed to recreate and populate your database (exported from XAMPP or saved as a text script from MYSQL server).
4. Add the two files (python and sql files) into a zipped folder and submit it as a secondary sumission file.
5. There will be **a technical interview **for the project, where you are expected to answer questions related to the project.

Notes

1. If you have used ChatGPT or other AI tools, please provide screenshots of the tool usage. Also, please add screenshots of all outputs in an organized manner.
2. Answers with advanced concepts not covered in the course will get a score of 0.
3. You are responsible for understanding every single task in your project and not partially. Please be prepared to explain your work when you are called for the technical interview to explain your answers.
4. Make sure that you submit your own original work. Suspected plagiarism cases will be treated as possible academic misconduct and will be reported to the College Academic Integrity Committee for formal investigation.
5. Your instructor will grade your submission based on what you submitted on the Forum within the allowed deadlines. Do not submit your assignment via email. Failure to submit an assignment or submitting an assignment for another student from the same class or another class will result in a 0 grade without the opportunity to resubmit.
6. If you used any references to complete your tasks, please include your references in APA style.

At the end of your assignment, include this

Academic Integrity disclaimer:

I hereby confirm that the work submitted for the assignment is entirely my own. I affirm that I have not used any artificial intelligence (AI) tools or any other unauthorized means to generate answers or complete any part of this assignment. The work presented reflects my own ideas, research, and understanding of the subject matter. I understand the importance of academic integrity and the consequences of submitting work that is not my own. I acknowledge that any violation of academic honesty policies may result in disciplinary action, including but not limited to a failing grade for the assignment or the entire course.

By submitting this assignment, I declare that I have complied with the academic integrity standards set forth by CIS/ZU. I am aware of the ethical implications of using external assistance and have adhered to the principles of honesty and integrity throughout the completion of this assignment.

Student 1 Signature: ________________________________________

Student 2 Signature: ________________________________________

WRITE MY PAPER