Search code examples
database-normalization

How to normalize the following tables to 3NF

So far I have the following tables:

Hotel (ID(pk), NoOfRooms, Phone, Address)
Room (No(pk), HotelID(pk), Rate, Size, Occupied, Loc)
Customer (Num(pk), SSN, Name, CreditCard, Address, StartDate, EndDate, AmtOwing, RoomNo(fk))
CareTaker (ID(pk), HotelID(fk), Name, Address, Phone, Email, Salary)
Manager (ID(pk), HotelID(fk), Name, Address, Phone, Email, Salary)

With the following assumptions:

Each manager can manage more than one hotel but a hotel may have only one manager

Each customer can only stay at one hotel and be given one room

Each caretaker works at only one hotel but a hotel may have many caretakers

Rooms in a particular hotel are unique but may not be unique across several branches of the hotels

Based upon the above assumptions I think there should be the following changes to the tables

Hotel (ID(pk), NoOfRooms, Phone, Address)
Room (No(pk), HotelID(pk), Rate, Size, Occupied, Loc)
Customer (Num(pk), Name, StartDate, EndDate, RoomNo(fk), SSN(fk))
CustomerPaymentInfo (SSN(pk), CreditCard, Address, AmtOwing)
CareTaker (ID(pk), HotelID(fk))
Manager (ID(pk), HotelID(fk))
Employee (ID(pk), Name, Address, Phone, Email, Salary)

Let me know what you think and if you have any suggestions.

Solution

  • Each manager can manage more than one hotel but a hotel may have only one Manager

    => So you need a manager per hotel, not one hotel per manager

    • Hotel (ID(pk), ManagerID(fk), NoOfRooms, Phone, Address)
    • Manager (ID(pk), REMOVE THIS: HotelID(fk), Name, Address, Phone, Email, Salary)

    Rooms in a particular hotel are unique but may not be unique across several branches of the hotels

    => okay

    • Room (No(pk), HotelID(pk), Rate, Size, Occupied, Loc)

    Each customer can only stay at one hotel and be given one room

    => A room is identified by room number plus Hotel:

    • Customer (Num(pk), SSN, Name, CreditCard, Address, StartDate, EndDate, AmtOwing, RoomNo(fk), HotelID(fk))

    Each caretaker works at only one hotel but a hotel may have many caretakers

    => okay

    • CareTaker (ID(pk), HotelID(fk), Name, Address, Phone, Email, Salary)