hi all,

i saw an explanation of junction table here:-
http://megocode3.wordpress.com/2008/...unction-table/
how correct is this explanation ?

but SELECT StudentName, RoomNumber
FROM StudentClassroom in this query

SELECT StudentName, RoomNumber
FROM StudentClassroom
JOIN Students ON Students.StudentID = StudentClassroom.StudentID
JOIN Classrooms ON Classrooms.ClassroomID = StudentClassroom.ClassroomID

doesn't seem to be correct is n't it?

"annalissa" <aarklon (AT) gmail (DOT) com> wrote

It is correct; however, it doesn't sufficiently address the claim that
junction tables prevent the addition of duplicate entries, nor the
theoretical basis for such a design.

If you try to stuff everything into one table, call it BadStudentClassrooms

CREATE TABLE BadStudentClassrooms
(
StudentName nchar(50) NOT NULL,
RoomNumber int NOT NULL,
PRIMARY KEY (StudentName, RoomNumber)
)

Then you can't insert a student without inserting a classroom and you can't
insert a classroom without inserting a student. These are known as insert
anomalies. The issue is that it is possible for a student to have been
assigned one or more classrooms but it is not necessary and that it is
possible for a classroom to have been assigned one or more students but it
is not necessary. If students could not exist outside of classrooms and
classrooms could not exist without students, then BadStudentClassrooms would
not be bad, even though there is a many to many relationship between
students and classrooms. It is the zeros in the multiplicities on the ends
of the relationship, 0..m:0..n, that indicates that a junction table is
required. In fact, for a 1..m:1..n relationship, a junction table is
problematic because as far as I know, SQL implementations don't support the
declaration of generalized inclusion dependencies (in SQL, what is
referenced must be the columns in a primary key or unique constraint), so
they must be enforced through the use of triggers.

It is. Neither StudentName nor RoomNumber is ambiguous in the query, so
they don't need to be qualified in the query, unlike StudentID and
ClassroomID. And neither StudentID nor ClassroomID need to be included in
the select list.