I want to have a database which contains each student, the courses he/she is attending and the grades he/she has for each of them.
My current model is:
STUDENT:
first_name
last_name
student_id PK
COURSE:
course_name
course_id PK
GRADES
grade
student_id FK
course_id FK
The problem is that I can't join STUDENT and COURSE. Would it be ok to add student_id as a foreign key in COURSE? What would the best modelling be?
Any two tables can be meaningfully joined. It just apparently isn't going to give you the rows you want. Please explain what rows you want: rows where student_id ids a student and course_id ids a course and ..."? In fact you need to give such a predicate for every base table or the database can't be used. You seem to be expecting us to know what those are from common sense. But it's not clear what rows you want that that join isn't giving you.
A FK (foreign key) just says values for some column set are also values for some other column set. That, like every constraint, follows from what rows you want put in each table in a situation and what situations can arise. So decide what rows you want to be able to ask for and if they aren't returned by some query using the base tables you have then add appropriate base tables. Then declare consequent constraints, including FKs, PKs (primary keys) & UNIQUE, so the DBMS can prevent impossible updates. But note that constraints are not needed to query. A query expression just uses base table names and the rows returned satisfy predicates built from base table predicates.
Presumably you want rows where "student student_id takes class class_id. Grades is presumably rows where "student student_id made grade in class class_id". Projecting out grade from Grades gives rows where "student student_id made some grade in class class_id". But those are only the rows you want if all students enrolled in a class have a grade for it.
You could add Enrollment(student_id, class_id) with predicate "student student_id takes class class_id. Notice that then a row in Grade must be a subrow in Enrollment, so we declare a FK; that just reflects the fact that "if student student_id made some grade in class class_id then student_id takes class class_id".
Or you could instead have Grade's predicate be something like "student student_id made grade in class class_id or student student_id takes class class_id and grade = 'n/a'". Then a projection of Enrollment gives you the rows you want. Notice how that predicate is relatively complex. This is one of the difficulties with using NULL in SQL or other special values.
Re querying see this answer and/or this one (including their links).
