April 6, 2011 at 4:05 pm
Not sure this is the right forum but I have a couple of questions with a database I created for a class, we were supposed to create a sort of mock system for hotel reservations and processes that a made up hotel could go through, complete with multiple tables.
I THINK I've created all the relavant tables and put in some mock data into it so that I can test it out and so far so good, however one of the guidelines was to create multiple queries of our choosing that we think would be relevant to the process.
So my though process was that a query for:
1. If a guest wanted to reserve a certain type of room to see if that type of room is even available or if all are occupied?
2. Maybe another one could be to take the time the guest wishes to stay and multiply it by the room rate to get a total for their entire stay?
So for the first query I have a Guest table, a Room type table, then for each type of room I have separate tables for them (3), a guest reservation table, and a reservation table.
April 7, 2011 at 11:06 am
For the 1st query I think I'd have to use 3 table; the room table, reservation table and rental table:
Room Table: Room_ID, Room Type
Reservation Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_Type
Rental Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_Type
Basically I'd like to figure out a query to find out if a specific room type is available during certain days.
So perhaps an output showing a list of all the rooms (by room_ID) from a certain room_type available between particular check_in and check_out days?
For the 2nd query I think I'd have to use 3 tables again; the room table, price table and reservation table:
Room Table: Room_ID, Room_Type, Price_Plan
Price Table: Price_Plan, Rate
Reservation Table: Check_In_Date, Check_Out_Date, Room_Type, Confirmation_No
Basically I'd like to figure out a query to give a total cost of stay by Confirmation_No.
So perhaps an output showing a list of confirmation numbers and then the check_out_date minus the check_in_date to give a duration of stay and then that column multiplied by the corresponding rate and a last column showing a total price?
Any ideas/suggestions?
April 8, 2011 at 6:52 am
Most hotels that I've stayed in have different rates, depending on the day of the week, events in town during that time period, etc. You might end up needing a calendar table with the rate / type of room / day to support this. This would also mean adding the rate, instead of multiplying by the # of days staying.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply