CIT365 - Spring 2018 Project Description This semester, you will be creating a mock database that will be used to help a hotel owner manage a hotel's...

CIT365 - Spring 2018 Project Description

This semester, you will be creating a mock database that will be used to help a hotel owner manage a hotel's reservation and guest stay system. Note now and often... this database will not be a fully functioning system, nor will you fully develop the system that contains data entry forms, guest entry, etc. but you will be creating a database that would sit behind such a system, designed with the requirements below. For your database, you will need to come up ll of the entities, attributes, and relationships necessary for your simulated system, called the DIYHotelManager Suite, to function.

This application will be responsible for tracking only a portion of the hotel management process, and in particular, room and reservation management.  The application would allow hotel management to track their reservations and actual "stays", track income (profit/loss), room availability, etc.

Specifically, the application will provide the ability to store in the database and keep track of the following data:

1.     Listing of Room types available in the hotel

2.     How much each room type can be reserved for (price) by a guest based on a particualr set of dates

3.     Listing of all rooms available in the hotel

4.     Way to reserve a particular type of room on a set of dates

5.     Track customers

6.     Way to track what reservations actually become "stays"

For number 1, the hotel will be able to make 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 dates of the year (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 make 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. 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.

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 (must use a CC).

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 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.

All of the above should provide management with information such as % of reserversations 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 #1 

For this deliverable, you will submit a data dictionary containing all of your tables (entities), their attributes, and the attributes' characteristics to be used in the design of your database. A sample data dictionary can be found on page 92 of your textbook. You will need to ensure that the data has been normalized (data dependency diagrams may help). Be sure to state any assumptions you make while designing your structures. You will submit a complete written report, including the data dictionary, and a justification of why you chose to create the specific table/attribute assignment/creation structures that you did so that there are no assumptions while grading. The entire submission should not be comprised of more than ( max for the data dictionary itself and ... for an explanation of your design). Please submit as a single PDF.

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 .... 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.

CIT365 - Project Deliverable #3

For this deliverable, you are to create the corresponding relational data model by implementing your database design for DIYHotelManager within the course's mySQL server site, linked under the Course Content section within Blackboard. Be sure to populate your tables with dummy data, providing me with the SQL scripts used to create the tables and insert the data into the actual database within a PDF document. Please do not use an interface that creates and inserts data automatically for you such as phpmyAdmin or mySQL Workbench. A portion of the grade is based on your SQL statements. You need only to have enough data so that your queries (explained below) can be processed for correctness and completeness, but please note, this means more than one or two records per entity. The database does not have to be a complete product or fully developed and populated system.

For this deliverable, you will also create 10 important SELECT queries that you need for the day to day management and maintenance of the operation of the system, which will turn raw data into useful information (see hints throughout the product explanation above). You will be required to not only create the queries, but also include a write-up (sentence) for each query indicating what it is supposed to be doing and why you feel it is important to the management of the application.  ALSO: One of these 10 selects MUST be a complex query that will generate an earnings report for the hotel.  In other words, it should calculate the earnings from stays and subtract from that the overall cost of having the rooms that are contained in the hotel.  This report should output a single monetary value (positive for profit and negative for loss).  Questions, ask.  Run the queries against your own database to ensure the results are correct and complete. Justify why each of the queries you created are important to the application in a written report. Be sure that you make the best use of the data that you can.

I will look for at least two major areas in your implementation. (a) First, I will check the structure of the tables - has entity and referential integrity been enforced and does the structure of your relational database match the ER diagram you submitted as deliverable #2 (b) most importantly, do you have queries that support the transactions associated with DIYHotelManager. Remember that I will be looking for and grading 10 queries, but at the same time, I grade on a difficulty scale. Points will be awarded based on complexity, meaning I am looking for aggregate information, joins, etc. and not just statements such as select * from customers, etc. As an example, you may have 10 queries, but if one is very simple and not a very useful query, it may get only 1 point (select * fromtable1) or 3 points (select * from table1 where ....). Queries with aggregate functions, joins, complex where clauses, etc. earn up to 10 points each while your large earnings report query is worth 20 points. The remaining 40 points are assigned based on the database's structural integrity and sample data - see first paragraph). Also, make sure no query or report produces a null output. I must see data in the output for all queries/reports and the data must represent what you are intending the query to do. Remember, and I reiterate, grading for the queries/reports will be based on the complexity of the queries. Simple queries are allowed, but are awarded very few points.

Submit everything as a single PDF.

OKAY! So now that you've read what I have to do, I need major help with deliverable 1,2,3 (basically the whole project) I have no idea how to start it and am in desperate need of help with these three parts because none of this makes any sense to me. So I don't even know how to approach this project because it makes absolutely no sense to me.