For number 1, the hotel will be able to create a number of different room types such as Queen Suite, 2 Bedroom Suite, Kitchenette, etc.

• For number 1, the hotel will be able to create a number of different room types such as Queen Suite, 2 Bedroom Suite, Kitchenette, etc.
• For number 2, the hotel will be able to associate the price a guest would pay for each room type they have created in function 1 and set those prices based on date ranges (for example, 2 Bedroom Suite at $149/night from May 1 - June 30, that same room at $99/night from November 1 - February 28, etc.).
• For number 3, the hotel will be able to create an entry for each room that is in the hotel and associate it with a room type. Data for rooms includes not only the room's type but also its room number and a method to allow for notes to include something that is unique in regard to the room. Also, the hotel will need to keep track of how much each room "costs" the hotel per night whether it is used or not and store that cost associated with each room (assume a single flat cost for each room, but it is dependent on the room). Think of this cost as the amount it takes to maintain the room per night (electricity, water, maintenance, employee costs, etc.). This will be used to help create the earnings report for the year.
• For number 4, customers would be able to register on say the hotel's web site and then reserve a particular room type (if available) for a given date range. They will be able to select a room of a given type, choose a beginning and ending date for their stay and indicate how many guests will be staying in the room. A reservation is separate than a stay and the hotel does not make any money from reservations, only if people actually stay. NOTE: You will need the system to reserve a particular room when a user selects room type to avoid overbooking and ensure room availability.
• For number 5, the hotel will be able to track the customers who have reserved and stayed in rooms. Information that should be collected about a customer includes their name, email address, mailing address, and payment method used (must use a CC, and you can assume a customer only ever uses a single CC for this site).
• For number 6, once a customer arrives at the hotel and converts their reservation to a stay, the application (or database) should provide a way to track the actual room stayed in, dates of the stay (start date, number of nights), and how much they were actually charged per night for the room based on the pricing associated with the dates of their stay. NOTE: You can assume for this class that no reservation overlaps into 2 or more pricing zones.

All of the above should provide management with information such as % of reservations that become stays, profit on a given date, set of dates, hotel utilization, etc. This information will come from queries that you generate using the data collected above.

The main purpose of the database is to keep track of all aspects of a person's reservation so that the hotel can track data, including the percentage of capacity in terms of rooms used on any given date, earning potential for a given night, % of customers who reserve vs. actually stay, % of customers who are repeat visitors, etc. These are all important aspects of the hotel�s main purpose for implementing such an application and should be considered a basis for the important "reports" (aka, queries) that are to be written as part of project deliverable 3.

Note that not all of the information above should be stored in the database (hint: think derived data for some items) and not all of the information should come from a single entity.

To support such a database and its transactions, you will need to design a database that would be used to keep track of all of the interactions described above as well as have the end goal being that of generating a full report showing how much the hotel earned in a given year. There are undoubtedly other features of such an application, but these are the main features that you must model within your deliverables and to prove you have learned what you should regarding database design and implementation from this course.

CIT365 - Project Deliverable #2

To support DIYHotelManager's database, you are to design a database that would be used to keep track of the transactions that will be made according to your design/needs submitted within Project Deliverable #1, and also based on comments supplied from my evaluation of your Project 1 Deliverable.  Projects that are not "fixed" from project deliverable 1's comments will automatically lose 25 points.

For this submission, you will submit a report containing the ERD diagram (Visio-based recommended, no hand-drawn/scanned ERDs please) for your database, and an explanation/justification of the diagram, all within a single PDF document. Please note this... a single document is to be submitted. Think on how to integrate your ERD within your PDF document with your typed comments. For the justification, you will need to explain why you selected specific entities (if changed since the submission of project 1) and why the relationships are the way they appear in the ER diagram (1:M, M:N broken down, mandatory, strong, weak, optional, etc.). The report/explanation and ERD should not be more than 4 pages single-spaced. Be sure to state any assumptions you make. Also, be sure to identify the keys and non-key attributes (meaning all attributes for each entity must be present) in the entity-relationship diagram. 

Please submit as a single PDF.