In this project, you are expected to design and develop a hotel management system using MySQL database involving querying the database via Java JDBC. You should implement relevant entities and endpoints for a server back-end Java application initialized with the Spring framework. You should implement your database design by considering the functional dependencies, and your database should be 3NF. Remember that since this is a database course, you are expected to solve as many challenges as you can using SQL. Hacky-Java Based solutions will not be graded!! The project consists of two phases. In the first phase, you will form your project groups and design and implement the database for this application. You have to form groups of 2 for this project. Your report should include an ER Diagram of your Database, necessary DML and DDL statements, and a brief explanation of your design decisions. In the second part, you will implement the Web back-end application. Your application should connect and access the database. Please note that you should do your search on the hotel management system and add new features to this system. The design report requires the following tasks: - Finding out the information requirements of the Hotel Management System. Determine the constraints and domains. Determine the entities and relations. - Identifying the properties of the entities and their domains. - Determining the identifier of each entity (i.e., find the primary key). - Drawing the Entity-Relationship (E-R) diagram. - Deciding what the base relations are. - Drawing the Functional Dependency diagrams. - Making sure your relations are in 3NF. - Deciding what the referential integrity constraints are (identify foreign keys). - Deciding which deletion integrity rules to use (restrict, set to NULL, or cascade). - Considering frequent access, come up with a physical database schema. - Create the database on a DBMS. - Specifying DDL statements in SQL (internal, external, conceptual level). - Specifying the queries (needed for the transactions you determined in step 1) using SQL.Write SQL insertion, deletion, modification and select statements (DML statements). In this project, you should create a new schema/database in your MySQL server and implement the entities and endpoints for a server back-end Java application initialized with the Spring framework. The system should be implemented as the backend for a Hotel Management System website. You are responsible for designing a Hotel Management System. This system contains users, rooms, bookings, and housekeeping schedules. Each of these components has unique semantics, and part of these semantics are described below: The mandatory requirement of the hotel management system: There must be separate user types. At least four user types: guest, administrator, receptionist, housekeeping. Only Administrators can create, delete or modify a room with its features Each room should have a unique name and may have different types (e.g., one bed, two bed, family-sized). Your design should allow us to create different rooms. Guests should be able to book rooms in any time frame for any amount of people depending on the availability of rooms. For example, the user can make a reservation for four people and he can book two double-bed rooms or one room for four people if it is available. Guests can check in if it is confirmed by a receptionist, and check out if they complete their payment. Note that they can make the payment in advance or while checking out. Receptionists can list the availability of the rooms in the system and the bookings requested by the guests. Receptionists can confirm room bookings or deny them according to booking requirements and available rooms. The same room should not be overbooked. That means during the customer's stay; there is no other reservation in that room. Administrators can cancel any booking they want if the user hasn't made any payment. Administrator accounts must already be present in the DB, or a DB Manager should only be able to add them - in other words, you cannot create an Administrator account from the end-points. Housekeeping can only view room availability but nothing about Guest information. Housekeeping is scheduled by Receptionists and kept as a schedule Housekeeping cleans the rooms after the bookings and keeps track of cleaned rooms. (Secret check: Only book clean rooms) Submission Guideline: 1) Please compress all your files and submit a single zip file. 2) The name of your zip file should look like the following: 'Group-X-Project-Part-1.zip' 3) Your submission should have the following structure: a) Group-X-Project-Part-1.zip i) Report.pdf ii) DDL.sql (CREATE TABLE statements) iii) DML.sql (INSERT statements) 4) Your Database design must comply with 3NF. 5) Write a report detailing your design decisions. 6) Your report MUST include an ER diagram representing your database design, your functional dependencies, and a description of your design decisions and why you made them. Your report should also include the names of the group members. 7) Failing to comply with these guidelines will result in a penalty. 8) Your submission must include a DDL file with relevant CREATE TABLE statements. In this project, you are expected to design and develop a hotel management system using MySQL database involving querying the database via Java JDBC. You should implement relevant entities and endpoints for a server back-end Java application initialized with the Spring framework. You should implement your database design by considering the functional dependencies, and your database should be 3NF. Remember that since this is a database course, you are expected to solve as many challenges as you can using SQL. Hacky-Java Based solutions will not be graded!! The project consists of two phases. In the first phase, you will form your project groups and design and implement the database for this application. You have to form groups of 2 for this project. Your report should include an ER Diagram of your Database, necessary DML and DDL statements, and a brief explanation of your design decisions. In the second part, you will implement the Web back-end application. Your application should connect and access the database. Please note that you should do your search on the hotel management system and add new features to this system. The design report requires the following tasks: - Finding out the information requirements of the Hotel Management System. Determine the constraints and domains. Determine the entities and relations. - Identifying the properties of the entities and their domains. - Determining the identifier of each entity (i.e., find the primary key). - Drawing the Entity-Relationship (E-R) diagram. - Deciding what the base relations are. - Drawing the Functional Dependency diagrams. - Making sure your relations are in 3NF. - Deciding what the referential integrity constraints are (identify foreign keys). - Deciding which deletion integrity rules to use (restrict, set to NULL, or cascade). - Considering frequent access, come up with a physical database schema. - Create the database on a DBMS. - Specifying DDL statements in SQL (internal, external, conceptual level). - Specifying the queries (needed for the transactions you determined in step 1) using SQL.Write SQL insertion, deletion, modification and select statements (DML statements). In this project, you should create a new schema/database in your MySQL server and implement the entities and endpoints for a server back-end Java application initialized with the Spring framework. The system should be implemented as the backend for a Hotel Management System website. You are responsible for designing a Hotel Management System. This system contains users, rooms, bookings, and housekeeping schedules. Each of these components has unique semantics, and part of these semantics are described below: The mandatory requirement of the hotel management system: There must be separate user types. At least four user types: guest, administrator, receptionist, housekeeping. Only Administrators can create, delete or modify a room with its features Each room should have a unique name and may have different types (e.g., one bed, two bed, family-sized). Your design should allow us to create different rooms. Guests should be able to book rooms in any time frame for any amount of people depending on the availability of rooms. For example, the user can make a reservation for four people and he can book two double-bed rooms or one room for four people if it is available. Guests can check in if it is confirmed by a receptionist, and check out if they complete their payment. Note that they can make the payment in advance or while checking out. Receptionists can list the availability of the rooms in the system and the bookings requested by the guests. Receptionists can confirm room bookings or deny them according to booking requirements and available rooms. The same room should not be overbooked. That means during the customer's stay; there is no other reservation in that room. Administrators can cancel any booking they want if the user hasn't made any payment. Administrator accounts must already be present in the DB, or a DB Manager should only be able to add them - in other words, you cannot create an Administrator account from the end-points. Housekeeping can only view room availability but nothing about Guest information. Housekeeping is scheduled by Receptionists and kept as a schedule Housekeeping cleans the rooms after the bookings and keeps track of cleaned rooms. (Secret check: Only book clean rooms) Submission Guideline: 1) Please compress all your files and submit a single zip file. 2) The name of your zip file should look like the following: 'Group-X-Project-Part-1.zip' 3) Your submission should have the following structure: a) Group-X-Project-Part-1.zip i) Report.pdf ii) DDL.sql (CREATE TABLE statements) iii) DML.sql (INSERT statements) 4) Your Database design must comply with 3NF. 5) Write a report detailing your design decisions. 6) Your report MUST include an ER diagram representing your database design, your functional dependencies, and a description of your design decisions and why you made them. Your report should also include the names of the group members. 7) Failing to comply with these guidelines will result in a penalty. 8) Your submission must include a DDL file with relevant CREATE TABLE statements