Check out some similar questions!

lawal201 · May 22, 2008, 06:20 AM

Please I have these tables below, could somebody help me with the normalisation in the third normal form.

1. Employee: Username, and Password

2. Guest: Guest No, Name, Address, Telephone Number, confirm

3. Room Details: Room No, Room Type, Room Rate, Status

4. Reservation: Reservation NO, Reservation Date, Expiry Date, Arrival Date, Departure Date

5. Report: Billing No, Reservation Date, Arrival Date, Departure Date, Total Amount

6. Allocation/Registration: Allocation No, Allocation Date, Arrival Date, Departure Date, comment

7. Billing: Billing No, Facility Type, Billing Rate, Total

It is about computerisation of front office of hotel, where by customer make reservation through telephone or by going to the hotel, how to allocate rooms, billings and reports.

Thanks

ScottGem · May 22, 2008, 06:36 AM

First, let me say that a reservation system, especially for a hotel, is probably the hardest database to design. If this is for a real life situation I would recommend buying a software package already setup for this task.

The main thing I see wrong with your structure is you have no relations set up. It should look more like this:

1. tblEmployees: EmployeeID (P Autonumber), Username, Password

2. tblGuests: GuestID (PK Autonumber), FirstName, LastName, Address, Telephone Number, confirm

3. tblRooms: RoomID (PK Autonumber), RoomNo, RoomTypeID, RoomRate

4. tblReservations: ReservationID (PK Autonumber), GuestID (FK), ReservationDate, ExpiryDate, ArrivalDate, LengthofStay, CheckInTime, DepartureDate, RoomID (FK)

5. Report: Billing No, Reservation Date, Arrival Date, Departure Date, Total Amount (not sure what this is, doesn't seem right)

6. Allocation/Registration: Allocation No, Allocation Date, Arrival Date, Departure Date, comment (unnecessary)

7. Billing: Billing No, Facility Type, Billing Rate, Total (don't understand this)

8. tluRoomtYpes: RoomTypeID (PK Autonumber), RoomType

Your Reservations table should be the central table. You need to have the GuestID as a foreign key. You would turn a reservation into a stay, by entering a CheckinTime and assigning a Room number.

ScottGem · May 22, 2008, 04:40 PM

Please don't use e-mails for follow-up. Posts your response to this thread.

I think you missed my point. I don't see the purpose of those three tables. So I don't know how they fit in.