The course_student table

There seems to be much confusion and questions surrounding the course_student table. So here is my take on it. The table structure allows a many-to-many relationship but you don't need to implement it fully.

There are a few ways of connecting entities within a relational system such as SQL. These are called relationships.

These are some links for more information.
Link 1
Link 2

The relationships are based on real life entity relationships.

eg:
A car has one owner.
An owner has zero or more cars.
A student has (zero) one or more courses.
A course has zero or more students.

These are examples of relationships. They are broken down into the following categories

1. One to One Relationships
2. One to Many and Many to One Relationships
3. Many to Many Relationships
4. Self Referencing Relationships

I am not going to discuss the last one (Self Referencing Relationships) as this is rather complex, but powerful, and is used to create hierarchies of objects.

Before we can link any entities we need to be able to identify them. This is usually done by an assigned unique number. Every entity has a number assigned to them by the system that is unique for all entities of that type, For our project
Course has cid, and student has sno.

One to One Relationships:
This one is fairly easy. Each object is linked to one other object. A common example of this is customer addresses, or student addresses.
We have a business rule that states that each customer needs one delivery address. So we have a Customer table and an Address Table.

Customer Table Address Table
customerID addressID
name line1
surname line2
addressID line3
etc. etc.

Here we have the addressID stores in the customer table. So we can see that each customer has one address which we identify by the addressID filed which occurs in both tables. The customer is said to "own" the relationship.


One to Many and Many to One Relationships
This one is a bit more complicated, but bear with me. Each object can be linked to one(zero) or more of another object.
For example: We have a business rule that states that each customer can have more than credit card. so we have the customer and the credit card tables
Customer Credit Card
customerID cardID
name cardNumber
surname expiryDate
addressID customerID
etc. etc.

Here we have the customerID stored in the Credit Card Table. This means that each credit card (many) is linked to a customer(one). So this is a many to one relationship, or a one to many depending on which way you read it. It comes down to the fact that for one customer, there can be many credit cards sharing the same customerID.


Many to Many Relationships
This is the one that does confuse a lot of people, but you can actually break it up into a one-to-many and a many-to-one relationship.
For this relationship we need two business rules.

1. Each Course can have one or more Students
2. Each Student can subscribe to one or more courses

So we have the following tables that should be familiar to you.
Student Course
sno cid
sname cname
etc, etc.

Now we need to somehow link them together. We know how to identify them as I mentioned earlier in the post. Students are identified by their sno and courses by their cid.
Step one we need to join the courses to the students. Each course can have many students, so we need to somehow store the student identifier, sno, under the course.
Step two we need to join the student table to the courses. Each student can have many courses, so we need to somehow store the course identifier, cid, under the student.

This is where the course_student table comes in.
course_student
cid
sno
year
fmark

example content
cid | sno | year | fmark
1 | 10 | 0 | 0
1 | 11 | 0 | 0
1 | 13 | 0 | 0
2 | 10 | 0 | 0
2 | 14 | 0 | 0


As you can see the cid shows that the course which has cid is linked to student with the student number as shown by the sno.
Course 1 has three students (10, 11, and 13)
Course 2 has two students (10 and 14)
At the same time
Student 10 is subscribed to 2 courses (1 and 2)
Student 11 is subscribed to 1 course (1)
Student 13 is subscribed to 1 course (1)
Student 14 is subscribed to 1 course (2)

The year and fmark fields are there to add additional information, so I am going to ignore them for this discussion.
So to populate this table you need to know the student number sno and the course id cid. Once you know both of these you insert a record into the table. This is typically done on the student screen (you have the sno) having a select which lists the courses cid. When the form is submitted you have both entities and now can populate the linking table.

--

Student Number: 7298-786-3