Re: many to many relationship

From: Larry Coon <>
Date: Wed, 05 Mar 2003 14:53:38 -0800
Message-ID: <>

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

Original text of this message