The primary key—a set of attributes that uniquely identity the row—for the hotel is RoomId, HotelId, and Date. In the preceding table, we can see that there is a lot of repetition of data. For example, HotelDescription only depends on HotelId and not on other attributes of the reservation. The problem with this repetition is that any mistake/change in the description would need to change in a lot of places.
To avoid this redundancy, Codd's second constraint states that, "Each attribute must be dependent on the entire primary key."
To enable this, the table must be refactored into two tables, as follows:
- Reservations:
Room Id |
HotelId |
Date |
Userid |
1 |
12321 |
01/01/2018 |
abc |
1 |
12321 |
01/02/2018 |
pqr |
2 |
12321 |
01/01/2018 |
xyz |
1 |
456 |
01/01/2018 |
zzz |
- Hotels:
HotelId |
HotelName |
HotelDescription |
Phone |
Star rating |
FreeCancel |
12321 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
12321 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
12321 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
456 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123456 |
4 |
Yes |
456 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123789 |
4 |
Yes |
Now, with this schema, the reservations table has much less redundant information and all the non-key fields are dependent on the primary key.