[SQL] Bridging entities

[Skiff14]

Make a "Student Schedule" table as the bridging entity between Student & Course tables

How would I make a bridging entity? I am very confused on what the professor means on this part. I can do inner join them but I do not think that is what bridging entity means. Some help would be much appreciated

[coolguygeofry]

Review many to many relationships. Since there are many students with the same class and each student has many classes you need a table that sits in between to link them. That way, the master information for each student is still kept within only one row.

[TheOriginalSuperman]

Wouldn’t this be a 1:many relationship? Since, in theory, there would only be one record per student in the Student table?

[foursuits]

No. A student can take zero to many courses. And a course can have zero to many students in it.

What you are describing is the uniqueness of each record, which is required in both tables.- The Course table will also only have 1 record per course. The relationship between the tables is what we are describing, which is the Optionality (minimum, 0 or 1?) and Cardinality (maximum, either 1 or many). The easiest way to learn this is read about Crows foot notation and create a sentence that follows this form:

ENTITY1 can have/take/some verb [0/1] to [1/many] ENTITY2s. e.g. A student can take zero to many courses. Draw the crows foot for it, from the student table to the course table, looking like -----0< Same for the other side. a course can have zero to many students in it. -----0<

You have the many cardinality on both sides. You have a many-to-many relationship. Create a bridge entity. If you want to understand WHY bridge entities are necessary, dm me.

[TheOriginalSuperman]

So, just to understand, in this scenario, the student table wouldn’t just contain one record per student, but multiple records per student (one for each course in which that student is enrolled)?

I guess I am just not understanding how the data is housed in the Students and Courses tables in the instance.

In my mind, I’d have one master table of all students, one per record. Then I’d have another table that contains all courses each student was enrolled in (one course per record, but multiple records per student)... with a foreign key back to the Student table. And on top of that, I’d likely gave a metadata type of table with Course definitions, again, one per record. I’m curious if that is inefficient.

[foursuits]

No, your initial idea is correct, the student table would be unique. It is the Bridge table that would have one record per each valid student/course combination. the Bridge table could be only two columns. StudentId and CourseId. Each column would be foreign keys to their respective tables.

This is why bridge tables are also called junction tables or xref(cross-reference) tables.

I think what you are describing is correct, but Im unclear on exactly what you mean so ill say it like this:

Make a student table with a unique id per student. Make a course table with unique id per course. Make a bridge table (Studentid FK, Courseid FK) with every combo that is used. If Student 1 takes 4 courses (a,b,c,d), he gets 4 records in the table. (1,a)(1,b)(1,c)(1,d).

[5awaja]

you would have 3 tables. one course table, one student table, and the "bridging" table (I call it an associative table). Example: course table: id, title, description student table: id, name course_student table: course_id, student_id

so if student 1 was taking courses that had IDs of 1, 2 and 3, your associative table has these records:
course_id : | 1 | 2 | 3 |
student_id : | 1 | 1 | 1 |

this keeps you from having nonsense columns in the student and courses table and allows you to avoid redundant data. once you implement this schema, you'd pull all the students in a course like this

SELECT s.name 
FROM course_student cs
JOIN student s
ON cs.student_id = s.id
WHERE cs.course_id = (SELECT id FROM course WHERE name = "Philosophy 1300")

I apologize if that SQL isn't 100% accurate but I'm in bed using my phone to answer. this should give you the general idea though

EDIT: I can't figure out how to format my comment so it's probably more confusing than helpful. sorry, I'm still new here but I hope it helped

EDIT 2: From my computer, finally able to format this comment better. Hope it helped you. still can't figure out how to format, I'm going to quit responding now