Table in 3rd normal form, yet with obvious redundancies

Question

Assume the following Reservations table. Reservation_Number is the only candidate key:

Reservation_Number  Guest_Name  
------------------  ------------
1                   john smith  
2                   john smith  
3                   john smith  
4                   jane doe  
5                   bob anderson  

Is this table in 3rd normal form?

1. It doesn't violate 1st normal form. A table is in 1NF when it has a primary key and the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. Reservation_Number is the primary key and both Reservation_Number and Guest_Name meet the atomic criteria.
2. It doesn't violate 2nd normal form. A table is in 2NF when it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key. Guest_Name is the only non-prime attribute and it is fully dependent on the only candidate key, Reservation_Number.
3. It doesn't violate 3rd normal form. A table is in 3NF when it is in 2NF and every non-prime attribute of the table is non-transitively dependent on every superkey of the table. The only non-prime attribute, Guest_Name, is non-transitively dependent on the only superkey, Reservation_Number.

Yet there is redundancy in this table. I could remove these redundancies by:

1. creating a Guest table with Guest_Id and Guest_Name.
2. replacing Guest_Name in the Reservations table with the Guest_Id FK.

How could the original Reservations table be in 3rd normal form yet have redundancies in Guest_Name? If it is violating the normal forms, then how and why?

Answer 1

Your example will not violate the 2nd and 3rd normal form, but what if you add a column Guest_Telephone for example. Then the 3rd normal form would be violated because you have a functional dependency between Guest_Name and Guest_Telephone.

You can remember:

If the relation is in the 2nd normal form and there is only one additional attribute among the key, then it is automatically in the 3rd normal form.

In your example you could simply modify the name of the attribute Guest_Name to Reservation_Description and the indication of a redundancy would no longer be that obvious.

Also try to split the Guest_Name into two attributes Guest_Firstname and Guest_Lastname, what would happen?

The indication of redundancy alone does not mean that you vialoate one of the first 3 normal forms, but as you said correctly it would be better to tear apart the reservations and the guests.

One last thing to be mentioned

Your attribute Guest_Name could be considered a key, there is no requirement that a key has to be a number. Consider your hotel (or whatever your reservation table is for) accomodates "numbers" instead of persons (ok, I hope you have a powerful imagination...). Would it make sense to have a schema like that:

Reservation_PK Guest_FK
1              1
2              2
3              1

Guest_PK Guest_Name
1        34
2        57

Of course not, you would leave the Guest_Name where it is:

Reservation_PK Guest_Name
1              34
2              57
3              34

So if you don't want to save any other attributes than the name of your guests, I would leave your reservation table as it is (and it even perfectly meets all the requirements of normal form 3).