Designing database table and relationships

[George]

Good Evening.
I want to design a database for an educational instution environment. My problem is knowing what tables to create and how to link them. I know for example that an instructor can teach more than 1 course and a course can be taught by more than 1 Instructor. This makes it a many to many relationship which is not remommended. I know that there has to be another table to bridge these 2 tables. I want to know what is a suitable name for this 3rd table and what example fields it should contain besides the the Keys form table 1 and 2?

Now here is the over all problem:

1. some instructors teaches more than 1 subject
2. some subjects are taught by more than 1 Instructor
3. some Classes are taught by more than 1 instructor
4. some instructors teach more than1 class
5. Some classes do more than 1 subject
6. Some subjects are done by more than 1 Class
7. Some classes are done at more than 1 location

This is what is needed:

1. To be able to enter student grades and attendances by class

The marks sheet should be entered in a subform displaying the full student names list for a particular class at a time.




2. To be able to display the marks and attendance of students as a class list.

[orange]

You have identified the many to many between Instructor and Course.
Perhaps the table between these to remove the M:M and to make two 1:M relationships could be called -- something like -- InstructorCourse or a similar one of your choice.

If you are interested here are free video tutorials that you may find helpful.
http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.