0

Please consider from an academic view not practical engineering view. This is about 1NF and 1NF only.

Considering the unnormalized form below, The primary key is {trainingDateTime, employeeNumber}, how would you make it to first normal form?

If we separate course, instructor and employee tables out as separate tables, it will automatically become 3NF.

UNF!

If i split into different rows, it would be something like:

enter image description here

But problem here is obvious - the primary key is no longer valid.

Changing primary key now to {trainingDateTime, employeeNumber, employeeSkill} doesn't seems to be a sensible solution.

user2001850
  • 477
  • 4
  • 18
  • Agreed. And thats where you would need to use 2NF and 3NF. – Pratik Apr 26 '15 at 10:56
  • Why do you want your table normalized only to 1NF? – Pratik Apr 26 '15 at 10:58
  • this is more of an academic demo, showing how each normal form is derived. in practice i can't imagine anyone would start from a complete unormalized form. – user2001850 Apr 26 '15 at 11:01
  • You can, for a complete novice, who doesn't know about normalization, he can start from a completely non normalized schema. But as you get more practice, your default schema is generally normalized to 3NF – Pratik Apr 26 '15 at 11:03

2 Answers2

0

Just to make it satisfy 1NF, you need to have seperate rows for the individual teaching skills. But you should be ensuring that the higher normal forms are also satisfied by splitting tables. So one row should have teaching skill as Advanced PHP and second row as advanced Java and third row as Advanced SQL and so on for the same employee.

Pratik
  • 1,122
  • 8
  • 26
  • but how about primary key? you want to have primary key changed from {trainingDateTime, employeeNumber} to something like {trainingDateTime, employeeNumber, employeeSkill}? – user2001850 Apr 26 '15 at 10:55
  • yes. But a better solution is to make your schema compatible with higher normal forms. – Pratik Apr 26 '15 at 10:57
  • are you saying in this example, the only way is to normalize straight to higher form? can this on a step-by-step basis? – user2001850 Apr 26 '15 at 10:58
  • When you normalize your table to 1NF, you would notice that it no longer satisfies 2NF. Thats when you realize that you need to normalize to 2NF. If after doing that, you can see that it does not satisfy 3NF, you normalize to 3NF. Thats how it works – Pratik Apr 26 '15 at 11:00
  • yeah. obviously. but how to resolve the problem (broken primary key after splitting rows) without going to higher normal form? – user2001850 Apr 26 '15 at 11:04
  • The only way is changing the PK. The PK should always be on a unique combination, and hence you need to change it to something unique. If your PK was for performance, remove that and put in a non clustered non unique index. Not the same performance but still a better performance than a general query – Pratik Apr 26 '15 at 11:08
  • this is purely about normalization. nothing to do with OLAP/OLTP/denormalization/sharding or anything like that. did you mean the primary key should now be something like {trainingDateTime, employeeNumber, employeeSkill} ? – user2001850 Apr 26 '15 at 11:15
  • yes, the PK is supposed to be that now.. And yes, this is just purely based on normalization. – Pratik Apr 26 '15 at 11:32
  • i am afraid your answers didn't help. Having employeeSkill as part of primary key doesn't make sense. – user2001850 Apr 26 '15 at 14:39
  • 1
    yup agreed. It didn't help. But it doesn't make sense to have a table just first normalized. – Pratik Apr 26 '15 at 19:14
  • yeah, I guess what we want is {trainingDateTime, employeeNumber} and {employeeNumber, employeeSkill} two primary keys. But in which normalization {trainingDateTime, employeeNumber, employeeSkill} can be converted to these? I can't think of any – user2001850 Apr 27 '15 at 05:58
0

Together with your other question database normalization - merge/combine tables it seems you are looking for an answer to a question you did not ask.

With regards to your comment "in practice i can't imagine anyone would start from a complete unormalized form." I would think your question is more, why do we need those normalization rules in the way they are formulated in order to produce normalization efficiently. Something like that. I guess your real motivation/question plays a role here.

Normalization is typically perceived as a process or a methodology. And there is no harm to this. However the formulation of those normalization rules also allows for a checklist like usage. So you can doublecheck an arbitrary set of tables with an arbitrary size against normalization rules and confirm or reject normalization compliance. So even if you can find probably thousands of examples where any of those normalization rules confirm normalization compliance from the very first natural schema version you could also find thousands of other examples that would fail normalization compliance on those same rules.

In fact trying to squeeze in multiple somehow coupled information in a historically grown collection of MS Excel tables accross several sheets usually is a extraordinary source for conflicting any set of normalization rules. (e.g. render a business case and connect that with planning aspects and ressource planning)...

Community
  • 1
  • 1
Quicker
  • 1,247
  • 8
  • 16