Re: Must we also create separate tables?

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys_at_gmail.com wrote:

>greetings
>
>My book claims that for table to be in 1NF, we must:
>
>* Eliminate remove repeating groups of data
>* Create separate tables for each group of related data and identify
>each row with a unique column ( the primary key )
>
>
>I know that for table to be in 1NF, we must remove repeating groups
>of data. But is it ( for relation to be in 1NF ) also required to
>create separate tables for each group of related data or is that just
>recommended and thus optional?

Hi Srubys,

The short answer is that your book is wrong.

The slightly longer answer is that the author probably expects you to be able to "see" (in some magical way) what data belongs logically together. If you do that, and if you're good at it, you might find your data already in 3NF after this step. But on the other hand, if you mess up you might never notice...

I dislike the advice. The reason is simply that, of all the steps taken in database design, normalization is the one step where there are actually clear rules on what's correct and what's not. Given the functional dependencies, everyone who knows the rules can check your design and explain exactly why it is or is not properly normalised (in whatever normal form you're shooting for). This advice throws that huge advantage of the normalization process out of the window.

Don't worry. As you normalize more often, you will get experienced, and you will get to the point where you "see" the proper design and jot it down immediately - and where you can still always fall back on the individual steps for hard problems or if you feel a need to doublecheck. Skipping this part now is like skipping the long division practice in primary school because the questions are so simple that you can write down the answer straight away - you'll regret missing the practice once you run into the harder problems in high school.

>Say we have non-normalized table STUDENT:
>
>STUDENT ( STUDENT#, STUDENT_NAME, SUBJECT1#, SUBJECT2#, SUBJECT1_NAME,
>SUBJECT2_NAME, TEACHER1_NAME, TEACHER2_NAME )
>
>
>I realize best thing to do would be to create two tables:
>
>STUDENT-SUBJECT ( STUDENT#, SUBJECT#, SUBJECT_NAME, TEACHER_NAME )
>
>STUDENT ( STUDENT#, STUDENT_NAME)
>
>
>
>But would the following table also be considered in 1NF:
>
> STUDENT ( STUDENT#, STUDENT_NAME, SUBJECT, SUBJECT_NAME, TEACHER )
Yes, this table is in 1NF. (It's not in 3NF of course, but I think you're already aware of that).

Best, Hugo Received on Mon Oct 27 2008 - 23:24:36 CET