Must we also create separate tables?

#11

<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?

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.

#12

<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?

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.

#13

<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?

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.

#14

<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?

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.

#15

<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?

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.

#16

<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?

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.

#17

<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?

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.

#18

<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?

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.

#19

<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?

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.

#20

Srubys (AT) gmail (DOT) com wrote:
....

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)

....

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".