Must we also create separate tables?

#41

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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

#42

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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

#43

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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

#44

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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

#45

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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

#46

On Sun, 26 Oct 2008 14:00:53 -0700 (PDT), Srubys (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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

#47

Walter Mitty wrote:

Quote:

"paul c" <toledobythesea (AT) oohay (DOT) ac> 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.

#48

Walter Mitty wrote:

Quote:

"paul c" <toledobythesea (AT) oohay (DOT) ac> 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.

#49

Walter Mitty wrote:

Quote:

"paul c" <toledobythesea (AT) oohay (DOT) ac> 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.

#50

Walter Mitty wrote:

Quote:

"paul c" <toledobythesea (AT) oohay (DOT) ac> 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.