Re: many to many relationship

From: Larry Coon <larry_at_assist.org>
Date: Wed, 05 Mar 2003 15:49:30 -0800
Message-ID: <3E668D0A.20EB_at_assist.org>

john wrote:  

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

Yep.

> but how would this schema then work?

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

create table enrollment (
  student_id int not null,
  department varchar(5) not null,
  course_no int not null,
  foreign key (student_id)
    references student (student_id),
  foreign key (department, course_no)
    references course (department, course_no),   primary key (student_id, 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)

insert into enrollment values(1, "MATH", 101)
insert into enrollment values(1, "MUSIC", 110)
insert into enrollment values(1, "MATH", 101)
insert into enrollment values(1, "GEOL", 200)
insert into enrollment values(1, "MUSIC", 110)
insert into enrollment values(1, "GEOL", 200)

The enrollment table has a one-to-many relationship with both student and course, essentially representing the many-to-many relationship between students and courses.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Thu Mar 06 2003 - 00:49:30 CET