Suppose that six hotel chains, each one with hotels in more than 20 locations in North and South America combined, have decided to cooperate and develop an application that will allow their customers to easily book rooms in their hotels, seeing room availability in real time, etc. This will be the application domain considered for this project. You are required to develop, as part of a new information systems project, a new database to allow this.
For each parent hotel brand you will need to store the information regarding: the central offices location (Main office location) and physical address, email addresses, phone numbers, the total number of hotels of the chain. For each hotel chain we need to know: the star category (e.g. 1-star up to 5-star), the number of rooms, the address of the hotel and contact email and phone numbers for this hotel. For the rooms in a hotel, we need to know their price, all amenities (e.g. TV, air condition, fridge, etc), the room capacity (e.g. single, double etc), if they have sea view or mountain view and if they can be extended (e.g. adding one more bed). For customers we need to store their full name, address and SIN number and the date of their registration into the system. Also, store the booking information details of the customer (e.g. room type booked, total number of occupants of the room etc). For employees of the hotels, we need to store their full name, address, salary and SIN (Any more personal details which you might feel relevant to add). The employees may have various roles/positions in a hotel such as concierge, housekeeper, manager, etc. Every hotel needs to have a manager. The customers can search for and book rooms for specific dates. When they check-in the hotel, their room booking is transformed to renting and they can also pay for this renting. The employee that does the check-in for a customer is responsible for transforming the room booking to renting. A customer may present himself physically at a hotel without a booking and directly ask to rent a room. In this case the employee at the hotel can do the renting of the room right away without prior booking. We need to store in the database the history of both bookings and rentings (archives), but we do not need to store the history of payments. Information about an old (archived) room booking/renting must exist in the database, even if information about the room itself does not exist in the database anymore. We should be able to add and to delete from our database hotel chains, hotels and rooms. If the database has information about a room then, the information about the corresponding hotel should also exist. The same applies to hotels and corresponding hotel chains 1. Create the ER diagram that corresponds to the above description. Be sure to identify the primary keys, relationship cardinalities, etc. 2. Create the Relational model that corresponds to your ER model. Provide both the relational model and the schema diagram. 3. Define the necessary constraints that will ensure the correctness of the database to be created according to your Relational model. These are primary keys, referential integrity constraints, domain constraints and user-defined constraints. Please include a brief justification for each constraint. For each parent hotel brand you will need to store the information regarding: the central offices location (Main office location) and physical address, email addresses, phone numbers, the total number of hotels of the chain. For each hotel chain we need to know: the star category (e.g. 1-star up to 5-star), the number of rooms, the address of the hotel and contact email and phone numbers for this hotel. For the rooms in a hotel, we need to know their price, all amenities (e.g. TV, air condition, fridge, etc), the room capacity (e.g. single, double etc), if they have sea view or mountain view and if they can be extended (e.g. adding one more bed). For customers we need to store their full name, address and SIN number and the date of their registration into the system. Also, store the booking information details of the customer (e.g. room type booked, total number of occupants of the room etc). For employees of the hotels, we need to store their full name, address, salary and SIN (Any more personal details which you might feel relevant to add). The employees may have various roles/positions in a hotel such as concierge, housekeeper, manager, etc. Every hotel needs to have a manager. The customers can search for and book rooms for specific dates. When they check-in the hotel, their room booking is transformed to renting and they can also pay for this renting. The employee that does the check-in for a customer is responsible for transforming the room booking to renting. A customer may present himself physically at a hotel without a booking and directly ask to rent a room. In this case the employee at the hotel can do the renting of the room right away without prior booking. We need to store in the database the history of both bookings and rentings (archives), but we do not need to store the history of payments. Information about an old (archived) room booking/renting must exist in the database, even if information about the room itself does not exist in the database anymore. We should be able to add and to delete from our database hotel chains, hotels and rooms. If the database has information about a room then, the information about the corresponding hotel should also exist. The same applies to hotels and corresponding hotel chains 1. Create the ER diagram that corresponds to the above description. Be sure to identify the primary keys, relationship cardinalities, etc. 2. Create the Relational model that corresponds to your ER model. Provide both the relational model and the schema diagram. 3. Define the necessary constraints that will ensure the correctness of the database to be created according to your Relational model. These are primary keys, referential integrity constraints, domain constraints and user-defined constraints. Please include a brief justification for each constraint
