Must we also create separate tables?

wrote in message

> 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?
>
> 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 )
>
> I realize the first option is much better, but my book gives an
> impression as if separate tables must also be created for related
> repeating groups of data in order for table to be 1NF compliant. Which
> doesn't make sense.
>
I have seen both treatments in books. I think you are right, although I
haven't looked it over thoroughly. Once you begin to normal;ize to 2NF,
3NF, etc.
you will decompose the table(s) several times, and end up in the same
place. Still, the alternative you present might be easier for students to
grasp.

BTW, there is a kind of "decomposition" going on in the transition from the
0NF form to the single table 1NF you present. It's just not a table
decomposition.
What's happening is that a row containing repeating groups is decomposed
into multiple rows, each free from repeating groups.
 >> Stay informed about: Must we also create separate tables? 

wrote:
....
>
> 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)
>
....


Heh, just to muddy the waters a little or even a lot, the conventional
normalization discipline is really just a way to help determine a simple
structure that avoids a lot of constraint verbiage. From what I gather
of the typical SQL product it is effectively a sop that allows them to
cop-out and not give very full constraint support.


Suppose that SUBJECT# determines SUBJECT_NAME. Then if you wanted to
store the version of STUDENT-SUBJECT above, you'd want to enforce that
constraint. If I had a dbms that let me state that constraint, there
might be performance reasons for doing it. (I mean logical performance,
trading off more logical "update" work for less logical query work.)
But I gather that most dbms'es don't make that easy to do. Further, I
might even want that stored "table" to be a view, which I gather they
don't allow at all. What's more, I might want one of the base tables of
that view to be SUBJECT (SUBJECT#, SUBJECT_NAME) but not to store it!


As CJ Date says, normalization doesn't tell you what's right, it only
gives hints as to what might be wrong. A lot depends on purpose as well
as the "business rules".
 >> Stay informed about: Must we also create separate tables? 

"paul c" wrote in message

> wrote:
> ...
>>
>> 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)
>>
> ...
>
>
> Heh, just to muddy the waters a little or even a lot, the conventional
> normalization discipline is really just a way to help determine a simple
> structure that avoids a lot of constraint verbiage. From what I gather of
> the typical SQL product it is effectively a sop that allows them to
> cop-out and not give very full constraint support.
>

Nah!
 >> Stay informed about: Must we also create separate tables? 

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), 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
 >> Stay informed about: Must we also create separate tables? 

Walter Mitty wrote:
> "paul c" wrote in message
....
>> Heh, just to muddy the waters a little or even a lot, the conventional
>> normalization discipline is really just a way to help determine a simple
>> structure that avoids a lot of constraint verbiage. From what I gather of
>> the typical SQL product it is effectively a sop that allows them to
>> cop-out and not give very full constraint support.
>>
>
> Nah!
>

Ha! Guessing that brief riposte was to do with the second sentence.
Maybe it was a little inflammatory but those sql people probably deserve
that for some reason or other. Can't help wonder what Thurber would
have thought. As for Codd, I can't prove this either, but I am pretty
sure, given his crude audiennce in the early days, maybe now too, that
he was going out of his way to avoid having to advocate more subtle /
less opaque structures than his "normal" form.
 >> Stay informed about: Must we also create separate tables? 

Greetings

I apologize for not replying sooner, but for some reason my post
didn’t appear for quite some time, so I assumed…


Anyways, if I understood your replies correctly, then not only is

STUDENT ( STUDENT#, STUDENT_NAME, SUBJECT, SUBJECT_NAME, TEACHER )

also in 1NF, but perhaps it is even more advisable ( at least until I
get more experienced at it ) to do it this way and not to create
separate tables for each group of related data when putting the table
in 1NF ( since separate tables for repeating groups of data will still
get created when I got through 2NF and 3NF )?


thank you
 >> Stay informed about: Must we also create separate tables? 

On Tue, 28 Oct 2008 17:27:15 -0700 (PDT), wrote:

>Greetings
>
>I apologize for not replying sooner, but for some reason my post
>didn’t appear for quite some time, so I assumed…
>
>
>Anyways, if I understood your replies correctly, then not only is
>
>STUDENT ( STUDENT#, STUDENT_NAME, SUBJECT, SUBJECT_NAME, TEACHER )
>
> also in 1NF, but perhaps it is even more advisable ( at least until I
>get more experienced at it ) to do it this way and not to create
>separate tables for each group of related data when putting the table
>in 1NF ( since separate tables for repeating groups of data will still
>get created when I got through 2NF and 3NF )?

Hi Srubys,

Yes, that is exactly the advice I was trying to give you.

Best, Hugo
 >> Stay informed about: Must we also create separate tables? 

Thank you kindly for helping me out

cheers
 >> Stay informed about: Must we also create separate tables? 

On Oct 26, 4:00 pm, 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?
>
> 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 )
>
> I realize the first option is much better, but my book gives an
> impression as if separate tables must also be created for related
> repeating groups of data in order for table to be 1NF compliant. Which
> doesn't make sense.
>
> thank you


Its probably more dependent on your instructor / boss than it is being
a real technical issue. Repeating groups are starting to be
implemented by even mainstream rdbms's. Often trying to be strictly
compliant to relational theory will actually cost you a bit when
trying to actually get work done. Just an opinion tho.
 >> Stay informed about: Must we also create separate tables?