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
- One to One Relationships
- One to Many and Many to One Relationships
- Many to Many Relationships
- 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.
- Each Course can have one or more Students
- 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