3NF Normalization and Decomposition

Question

I am currently in a DB class and working through Normalization, and am running into some trouble. Am hoping I can get some assistance working through this. I have searched for the last 30 min and haven't found anything that helps solve my question, but hopefully I'm not searching for the wrong things.

The question is as follows:

Considering the universal Relation

EMPLOYEE (ID, First, Last, Team, Dept, Salary)

With the follow set F of functional dependencies

ID -> First
ID -> Last
First, Last -> ID
Last -> Team
ID -> Dept
ID -> Salary
Salary -> Dept

Identify the candidate keys and construction a decomposition of Employee into relations in 3NF that preserve Dependencies.

For the candidate keys, I am struggling because when doing an edge diagram, there are incoming dependencies for every single attribute. There are no attributes that do not appear on the RHS of the dependencies. What I think may be confusing me is that while ID does determine everything, First, Last determines ID. So would ID and First, Last both be a candidate key?

I know for the deconstruction, Last -> Team and Salary -> Dept are transitive, but ID has a direct dependency ID -> Dept and ID-> Salary already given.

Does that mean I only need two tables, (ID, First, Last, Salary) and (Last, Team)?

Or based on the candidate keys question above, do I need (ID, First, Last) (ID, Salary, Dept) (Last, Team)

Let me know if any additional info is needed. Thank you.

Your "I have these FDs" doesn't make sense. "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. "These are some FDs that hold"?--Question can't be answered. Find out what a *cover* is & what the exact conditions are to apply a particular definition/rule/algorithm. To determine CKs & NFs we must be given FDs that form a cover. Sometimes a minimal/irreducible cover. And the set of all attributes must given. [See this answer.](https://stackoverflow.com/a/53386492/3404097) — philipxy, Nov 09 '20 at 18:26
Right now you are effectively asking for us to rewrite your textbook with a bespoke tutorial. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. Quote the definitions, theorems, algorithms & heuristics you are relying on. All the steps are also SO faqs. PS That includes explaining "edge diagram". Also you seem to mean "decomposition" not "deconstruction". — philipxy, Nov 09 '20 at 18:34

score 2 · Accepted Answer · edited Nov 08 '20 at 21:23

So would ID and First, Last both be a candidate key?

ID is a candidate key and Last, First is probably a composite index. It's too common for people to have the same name.

The third normal form can be summed up in one sentence. "The columns in the table depend on the key, the whole key, and nothing but the key, so help me Codd."

So, let's take a look at your original description.

EMPLOYEE (ID, First, Last, Team, Dept, Salary)

First, Last, and Salary would be based on the employee id. One of your dependencies implies that everyone in the department gets the same salary. I don't agree, but whatever.

An employee is on one team, and one team can have one or more employees. This is a one to many relationship, which implies a foreign key to a Team table from the Employee table.

The same holds for the employee / department relationship. Another foreign key to a Department table from the Employee table.

There doesn't seem to be any relationship between the Team table and the Department table.

Salary is a weird field. I'd say it belongs in the Employee table, but the Salary -> Dept relationship is confusing me.

3NF Normalization and Decomposition

1 Answers1