Database - Trying to solidify understanding of normalization, how normalized is this?

Question

I drew an example diagram in Access.

Example SqlFiddle

The relationships and tables fit 3NF:

• a Projects table with fields ProjNumber(PK), ProjName, and ProjDesc
• an Assignments table with compound key consisting of EmpID and ProjNumber, with fields HourlyBillingRate, NumOfHours, and TeamNum
• a Teams table, with fields TeamNum(PK), TeamName, ProjNumber

ProjNumber from Assignments and Teams are foreign keys to Projects and TeamNum from Assignments is a foreign key to the Teams primary key. I don't know if it's necessary to directly reference Teams if I have the ProjNumber foreign key because that project would have an associated TeamNum.

There is a project, a team associated with carrying that out, and employees that are on that team which are paid an hourly billing rate for that project.

I use a compound key to answer "What is the employee works on multiple projects?", so I couldn't make EmpID the sole primary key, thus I chose to make it a compound key because even if the employee works on multiple projects, the combination of the two will always be unique. Each field is necessary and relevant to referenced primary key.

Why does this or doesn't this fulfill 3NF?

Answer 1

It depends. Your diagram and discussion appear to assume that the primary key is the only candidate key in each of the tables. That appears not to be the case.

In the Assignments table, it looks as though EmpID and TeamNumber is another candidate key, provided that TeamNumber may not be NULL.

If we look at this table with EmpId, TeamNumber as the key, then it is not in 2NF. ProjNumber is determined by TeamNumber, which is not the whole key.

So now the answer to your question turns on whether FDs are analyzed with respect to all candidate keys or just the declared primary key. I have seen tutorials on on normalization that go both ways. I follow the one that considers all candidate keys, so the table is not in 2NF.

Unless I've misconstrued the FDs in your case, or Assignment.TeamNumber can be NULL.

HOWEVER, your SQL Fiddle presentation is different. Now, if there are several teams on one project, and an employee is assigned to one project for a few hours, there isn't any way to tell what team the employee was on. The FDs are not the same in the SQL Fiddle example and in the implications I take from your diagram.