Re: Must we also create separate tables?

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

Yes, you need three tables, but not as you outlined them: STUDENT ( STUDENT#, STUDENT_NAME)
SUBJECT ( SUBJECT#, SUBJECT_NAME, TEACHER_NAME ) STUDENT-SUBJECT ( STUDENT#, SUBJECT# ) would be a preferred set of tables, though most schools use something like:

STUDENT ( STUDENT#,  STUDENT_NAME)
SUBJECT ( SUBJECT#,  SUBJECT_NAME, DESCRIPTION)
SECTION ( SECTION#,  SUBJECT#, TEACHER_ID, TIME_AND_DAYS_CODE,...)