Convert table to 2NF - strange table to convert?

Question

I have the following table below and am supposed to convert it to 2NF.

Convert table to 2NF

I have an answer to this where I have gone:

SKILLS: Employee, Skill

LOCATION: Employee, Current Work Location

I have a feeling I'm wrong with this ^above^ though.

Also can someone explain what the differences are between 1NF, 2NF and 3NF. I know 1 comes first and you have to break it all up into smaller tables but would like a really good description to help me understand better. Thanks

NF differences: http://stackoverflow.com/questions/723998/can-someone-please-give-an-example-of-1nf-2nf-and-3nf-in-plain-english — BlackICE, Jun 17 '11 at 15:01
@David thanks, could you tell me whether I'm right with the one above though? — maclunian, Jun 17 '11 at 15:02
It looks like 3NF from what is in the post. That doesn't necessarily mean it's going to do what you want though :) — BlackICE, Jun 17 '11 at 15:07
@David how would I get it so it's in 2NF then? That's the aim of the exercise — maclunian, Jun 17 '11 at 15:08
@David the table was in 1NF and I was meant to turn it into 2NF or so my teacher said... — maclunian, Jun 17 '11 at 15:16
Assuming the key to your new tables is "Employee", your new tables are in 5NF. (And 4NF, and BCNF, and 3NF, and 2NF.) — Mike Sherrill 'Cat Recall', Jun 17 '11 at 16:07
That is the answer wikipedia provides, but to get credit at my university you would need to explain how you arrived at that conclusion. — Woot4Moo, Jun 17 '11 at 17:27
@maclunian: You might find the accepted answer and comments to this SO question helpful: http://stackoverflow.com/questions/5368344/moving-to-second-form — Mike Sherrill 'Cat Recall', Jun 18 '11 at 14:29

score 0 · Answer 1 · answered Feb 08 '15 at 07:21

0

I am new to learning 2NF but I have solved the answer like this. Let me know if this is correct so that I can understand my mistake and practice more.

enter image description here Only two tables. Thanks

answered Feb 08 '15 at 07:21

AbhiSheikhBin

35
9

score -2 · Accepted Answer · answered Jun 17 '11 at 15:27

-2

Employee Table

EmployeeID  |   Name
1       |   Jones
2       |   Bravo
3       |   Ellis
4       |   Harrison


Skills Table

SkillId     |   Skill
1       |   Typing
2       |   Shorthand
3       |   Whittling
4       |   Light Cleaning
5       |   Alchemy
6       |   Juggling

Location Table

LocationId  |   Name
1       |   114 Main Street
2       |   73 Industrial Way

EmployeeSkill Table

EmployeeId  |   LocationId  |   SkillId |   SkillName
1       |   1       |   1   |   Typing
1       |   1       |   2   |   Shorthand
1       |   1       |   3   |   Whittling
2       |   2       |   4   |   Light Cleaning
3       |   2       |   5   |   Alchemy
3       |   2       |   6   |   Juggling
4       |   2       |   4   |   Light Cleaning

In the EmployeeSkill table the primary key would be EmployeeId + LocationId, this gives you the skill they have at that location. Including the SkillName column violates 3NF in this example. This practice is actually used sometimes in database design (and called "denormalization") in order to reduce joins to increase performance reading data that is commonly used together.
Ususally this is only done in tables used for reporting.

answered Jun 17 '11 at 15:27

BlackICE

8,816
3
53
91

1

You're introducing guaranteed-unique keys, which is good practice in general but irrelevant to the assignment, and it isn't clear that assigning a SkillId is necessary. I'm also not fond of joining tables that embody both one-to-one and one-to-many relationships. – David Thornley Jun 17 '11 at 15:57
Yes, it was difficult to tell exactly what the end format would likely be, is a skill dependent upon a location or not? – BlackICE Jun 17 '11 at 16:02
3

-1. In the context of homework and education, students are expected to work with the columns in the assignment. They're not allowed to introduce new columns. One risk is that a wall of numbers obscures what's really happening. Another is that introducing new columns with id numbers seems to suggest to students that "normalizing" means "substitute id numbers for strings". – Mike Sherrill 'Cat Recall' Jun 17 '11 at 16:11
Typically anything that has a primary key meets BCNF (3.5 NF) – Woot4Moo Jun 17 '11 at 17:25
-1. The answer is a bad example. Normalization has nothing to do with inventing new keys for things. – nvogel Jun 17 '11 at 18:53
@Woot4Moo: If your university taught you that anything having a primary key meets BCNF, ask for your money back. They've served you badly. – Mike Sherrill 'Cat Recall' Jun 18 '11 at 14:15
@catcall 99.8% of tables that have primary keys meet bcnf. – Woot4Moo Jun 18 '11 at 14:54
@David: The OP said he needed to convert a table to 2NF. He didn't ask any questions about surrogate ID numbers. – Mike Sherrill 'Cat Recall' Jun 18 '11 at 15:26
and this meets 2NF, nothing was specified about keeping the same columns or not adding anything. – BlackICE Jun 20 '11 at 10:18
@David, The question specifies that you should *convert* [a given set of attributes and related dependencies] to 2NF. Supplying a *different* set of attributes that allegedly satisfies 2NF is not answering the question. E.g. it looks like you assumed the dependency Employee->"Current Work Location" is supposed to be satsified but then you replaced "Employee" with two other attributes and you haven't even said whether either or both of these are keys. Without specifying the keys and dependencies it's not clear that you've answered the question at all, let alone whether it might satisfy 2NF. – nvogel Jun 20 '11 at 13:44

Convert table to 2NF - strange table to convert?

2 Answers2