A little bit of domain investigation reveals that the FreeCancellation column depends on the StartRating one—five-star hotels don't offer free cancellation, but others do. The fact that the free cancellation option is mentioned in the database as opposed to hardcoding in the code is good design—it makes changes to this policy easier, without touching/releasing code. However, the repetition of information does introduce redundancy. It can also lead to consistency issues—say that four-star hotels also stop offering free cancellations; it's much better to model this relationship separately.
Codd's third constraint states that, "Each attribute must be dependent only on the primary key." To enable this, we refactor the hotels table further, as follows:
- Hotels:
HotelId |
HotelName |
HotelDescription |
Phone |
Star rating |
12321 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
12321 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
12321 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
456 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123456 |
4 |
456 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123789 |
4 |
- Cancellation policy:
Star rating |
FreeCancel |
5 |
No |
4 |
Yes |
3 |
Yes |
2 |
Yes |
1 |
Yes |