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

Room Information

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

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.

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.