| |
 |
|
| Computers Forum Index » Computer - Databases » Junction table... |
|
Page 1 of 1 |
|
| Author |
Message |
| annalissa... |
 Posted: Sun Jul 05, 2009 9:30 am |
|
|
|
Guest
|
Hi all,
i saw an explanation of junction table here:-
http://megocode3.wordpress.com/2008/01/04/understanding-a-sql-junction-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? |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Sun Jul 05, 2009 12:29 pm |
|
|
|
Guest
|
On Jul 5, 12:18 am, annalissa <aark... at (no spam) gmail.com> wrote:
Quote: Hi all,
i saw an explanation of junction table here:-http://megocode3.wordpress.com/2008/01/04/understanding-a-sql-junctio...
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 isn't it?
This write up is wrong on several points.
1) SQL has relationship tables. "Junction" is a term from network
databases. You follow the usual rules about naming a thing for what
it is, not for its location, content,etc. Relationships have names,
too. His "StudentClassroom" should have been something like
"ClassRoster"; would you have named a "Marriages" table
"BoyGirlChurch"?
2) He gave no DDL which is rude and makes the posting useless. Here
is my guess with the required DRI.
CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY,
student_name CHAR(30) NOT NULL,
etc);
CREATE TABLE Classrooms
(room_nbr INTEGER NOT NULL PRIMARY KEY,
room_size INTEGER NOT NULL,
etc);
CREATE TABLE ClassRoster
(student_id INTEGER NOT NULL
REFERENCES Students(student_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
room_nbr INTEGER NOT NULL
REFERENCES Classrooms (room_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (student_id, room_nbr),
etc);
3) room_nbr would be part of the key of the roster. so his query is
going to produce redundant rows. This should a have been:
SELECT S.student_name, R.room_nbr
FROM ClassRoster AS R, Students AS S
WHERE S.student_id = R.student_id;
What he was probably trying to say was:
SELECT S.student_name, R.room_nbr, C.room_size
FROM ClassRoster AS R, Students AS S, Classrooms AS C
WHERE R.student_id = S.student_id
AND R.classroom_nbr = C.classroom_nbr; |
|
|
| Back to top |
|
|
|
| George Neuner... |
| Posted: Mon Jul 06, 2009 10:33 pm |
|
|
|
Guest
|
On Sun, 5 Jul 2009 05:29:34 -0700 (PDT), --CELKO--
<jcelko212 at (no spam) earthlink.net> wrote:
Quote: 1) SQL has relationship tables. "Junction" is a term from network
databases. You follow the usual rules about naming a thing for what
it is, not for its location, content,etc. Relationships have names,
too. His "StudentClassroom" should have been something like
"ClassRoster"; would you have named a "Marriages" table
"BoyGirlChurch"?
I'm not sure when "junction" terminology began (or even if it is part
of the current standard), but it has become fashionable to refer to a
table that defines a many-to-many relation and whose key is composed
of the foreign keys from the related tables as a "junction".
George |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sun Nov 22, 2009 1:27 am
|
|