Re: many to many relationship

I think the point you are trying to make is, its impossible, am i right in saying you would need a third table say enrollment?

but how would this schema then work?

"Larry Coon" <larry_at_assist.org> wrote in message news:3E667FF2.86B_at_assist.org...
> john wrote:
>
> > hi,
> >
> > why is it that a many to many relationship should be changed so that a
one
> > to many table links the two?
> >
> > i.e.
> >
> > table M_________M table
> >
> > becomes:
> >
> > table M________1 table 1__________M table
> >
> > i never understood this during my college course, and just wanted to
clarify
>
> Becuse there's no way to represent the relationship without violating
> first normal form.
>
> Let me contrive an example for you to work on, which hopefully will
> illustrate why this is true:
>
> create table student (
> student_id int not null primary key,
> last_name varchar(30) not null,
> first_name varchar(30) not null
> )
>
> create table course (
> department varchar(5) not null,
> course_no int not null,
> course_name varchar(80) not null,
> units int not null,
> primary key (department, course_no)
> )
>
> insert into student values(1, "Smith", "John")
> insert into student values(2, "Jones", "Joe")
> insert into student values(3, "Lee", "Leo")
>
> insert into course values("MATH", 101, "Algebra", 3)
> insert into course values("MUSIC", 110, "Theory", 3)
> insert into course values("GEOL", 200, "Petrology", 3)
>
> Okay, there is a many-to-many relationship between students and
> courses: a student enrolls in many courses, and a course is taken
> by many students.
>
> In this case, let's say student 1 takes MATH 101 and MUSIC 110,
> student 2 takes MATH 101 and GEOL 200, and student 3 takes MUSIC
> 110 and GEOL 200.
>
> Modify the schema to reflect this many-to-many relationship
> (enrollment in courses) without adding a third table. Show the
> create table and insert statements to reflect the above
> enrollments with the above students & courses.
>
>
> Larry Coon
> University of California
> larry_at_assist.org
> and lmcoon_at_home.com
Received on Thu Mar 06 2003 - 00:15:25 CET