Are these tables in the third normal form?

Question

I have decomposed a relationship that tracks employee's and the hours they have spent working at hotels. The original relationship is as follows

R(national insurance number, contract Number, hours, eName, hotel Number,     hotel Location)

rewritten as

R(A, B, C, D, E, F)

I have found the Functional dependencies

F:(A->D, E->F, AB->C, B->E, BA->E)

From this I have created the following 3 tables

1.
Employees:
national insurance number(A)
eName(D)
PRIMARY KEY(A)


2.
Works at:
contract Number(B)
Hours(C)
national insurance number(A)
PRIMARY KEY(B, AND A)

3.
Hotel:
contract Number(B)
hotel Number(E)
hotel Location(F)
PRIMARY KEY(B) 

In my third table, I have a primary key that can determine both the number, and location of the hotel. But the hotel number can also determine the hotel location. Should I move the hotel location to a new table, with only hotel number? That would use more space, but is it necessary to reach the 3RD normal form?

Answer 1

When assuming that the FDs you derived are correct and complete, to achieve 3rd normal form in your setting, it is in deed necessary to split Hotel(B,E,F) { B->E, E->F } into HotelContract(B,E) { B->E } and Hotel(E,F) { E->F }. Formally, in { B->E, E->F }, B is the (sole) key, E is a "non prime" attribute (i.e. is not part of any key), and therefore F depends transitively through a non prime attribute from a key. This violates 3rd normal form.

With a schema Hotel(B,E,F) { B->E, E->F }, which violates 3NF, you get a "deletion anomaly", i.e. you loose more information than necessary when deleting a tuple from Hotel. Suppose the following extension of Hotel:

Hotel
 B | E | F
---|---|---
b1 | e1| f1
b2 | e2| f2

When you delete tuple (b2,e2,f2), then you loose the information that hotel e2 is located in f2, although you just wanted to delete the contract.

Even worse, when you translate the scheme into

Hotel:
contract Number(B)
hotel Number(E)
hotel Location(F)
PRIMARY KEY(B)

then you actually omit FD E->F, which would permit that the same hotel e1 gets two different locations, for example f1 and f2:

Hotel
 B | E | F
---|---|---
b1 | e1| f1
b2 | e1| f2  -> permitted by your scheme, but not intended!

Hence, split the table as recommended in the intro.

Answer 2

It seems like you are right. Hotel needs to be in separate table.

Also I guess there is a possibility to employee to work on more than one contract in more than one hotel, and one hotel may have more than one contract for employee.

so, to me normalized form will look like:

1. Employees:
   national insurance number(A)
   eName(D)
   PRIMARY KEY(A)

2. Hotels:
     hotel Number(E)
     hotel Location(F)
     PRIMARY KEY(E) 

3. Contracts:
    contract Number(B)
    hotel Number(E)
    PRIMARY KEY(B)
    FOREIGN KEY(Hotels.E)

4. Works at:
   work at(G),
   national insurance number(A)
   contract Number(B)
   Hours(C)
   PRIMARY KEY(G)
   FOREIGN KEY(Employees.A) 
   FOREIGN KEY(Contracts.B)