Reference no: EM13330106

A small, independently runhotel needs to design a portion of a database that will contain information on customers, rooms available and which customershave been booked into rooms. You have been asked to assist them with creating a relational database structure for organizing this information.

The main things that the hotel wants to keep track of in this database are their Customers, along with CustomerID, name, address, phone number and email. The Rooms that are available including the Room number, Number of beds,and the cost of the room, and room description (suite, basic, executive, mountain view, etc).  They also need to know which customershavebeen booked into which rooms and if those rooms have been paid for or not, this information will be stored in the REGISTRATION table. A simple Entity Relationship Diagram is shown below:

You need to do the following:

Part I

1. Create the tables using the table names indicated above, also include fields, data types, and Primary Keys.  The CustomerID and RoomNo fields in the REGISTRATION table should be Look Up fields. DO NOT put the data in before you have linked the tables (see #2 below).The data types for PricePerNight will be Currency, the data types for CheckInDate will be Date/Time, the data type for the data type for NumNights will be number, Paid will be Yes/No, all others will be short text.

2. Create the relationships between the tables using the LookUp Wizard, these should be in the form of 1-M relationships and will link the PKs and FKs, remember you need to Enforce Referential Integrity to do this.The field CustomerID in the REGISTRATION table will be looked-Up from the CustomerID in the CUSTOMER table. The field RoomNo in the REGISTRATION table will be looked-Up from the RoomNoin the ROOMS table.

3. Create a form for data entry for each of the tables

4. Populate the database using the data provided below, the CUSTOMER and ROOMS tables need to be populated before the REGISTRATION table. You MUST add yourself as a customerand register for a room.

Part II

Create and execute the following queries:

1. Design a query tolist all rooms costing over $100.00, format the dollar amount field to have a '$' sign, label the query RoomsOver100.

2. Design a query to list the total cost of room(s) for each customer, in the query result you will have one row for each customer containing the total amount for that customer, this will include everything they owe and/or have paid, label the query TotRoomAmtPerCust.

3. Design a query to list the total amount owed for all rooms (rooms booked but not yet paid for), there should be only one total amount, format the dollar amount field to have a '$' sign, label the query TotOwedRooms.

Part III

1. Create a report that groups the bookings by room. Include the room number, description, Name and address of all customers that have stayed in that room under the room information.  Label the reportRoomBookingList, center the title. Save as Room Booking List.

Data for the Tables

Customer Information

All fields should be of type TEXT except the CustomerID which will be type AutoNumber

CustomerID	CustName	Address	City	ST	Zip	Telephone	Email
1000	Sophie Beranek	145 Oak Ave.	Wilmington	NM	19808	302-475-4477	[email protected]
1001	Justin Taylor	1625 Brook St.	Costa Mesa	CO	92688	714-336-4785	[email protected]
1002	Brad Pitt	9661 King Pl.	Santa Fe	NM	01013	413-572-8292	[email protected]
1003	Jory Red	346 Magee Ave.	Fort Collins	CO	19111	215-780-3953	[email protected]
1004	Adele Adele	419 Basic st	Denver	CO	80920	303-780-4491	[email protected]
1005	Carly Jepson	1884 Unitah	Colorado Springs	CO	80819	719-450-3129	[email protected]
1006	Regina Spektor	776 S. 5th	Castle Rock	CO	82114	303-269-4444	[email protected]
1007	Chris Pine	1408 Creek st	Albuquerque	NM	80808	520-419-2323	[email protected]

Room Information

All fields should be of type TEXT except PricePerNight which will be type Currency.

RoomNo	RoomDescrip	NumBeds	PricePerNight
101	Basic with microwave	1	$85.00
102	Deluxe	2	$100.00
201	Basic	1	$80.00
202	Basic with microwave	1	$85.00
206	Executive, Mountain View	1	$180.00
207	Basic with microwave	2	$85.00
301	Deluxe	1	$100.00
302	One Bedroom suite	1	$120.00
303	Two Bedroom suite, Mountain View	2	$150.00
304	One Bedroom suite, Mountain View	1	$120.00
305	Two Bedroom suite, Mountain View	2	$180.00

Registration Information

RoomRegID will be type AutoNumber, CustomerID will be type Number, RoomNo will be type text CheckIndate will be type Time/Date, NumberNights will be type Number and Paid? will be type Yes/No.

	RoomRegID	CustomerID	RoomNo	CheckInDate	NumerNights	Paid?
	100	1000	101	5/1/2013	3	N
	101	1001	201	11/22/2012	8	Y
	102	1001	102	12/23/2012	3	Y
	103	1002	202	1/30/2013	4	Y
	104	1003	301	3/5/2013	2	N
	105	1004	206	12/1/2012	2	Y
	106	1004	303	4/3/2013	2	Y
	107	1005	305	2/26/2013	2	N
	108	1006	201	1/13/2013	2	Y
	109	1007	202	3/14/2013	1	N

Customers and rooms - this is the list so far of customers and the rooms they are registered in. Be sure to add yourself as a customerin the executive suite for 2 nights.