Re: many to many relationship

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)

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 Wed Mar 05 2003 - 23:53:38 CET