Entity Relationship Diagram for Grade Calculator

Question

I was wondering if anyone would be able to review my Grade Calculator for a course ERD? I'm looking for ways to improve the current design (if any improvements need to be made that is) and if anyone could review the current MySQL query which is there. In order for this to be reviewed I was told I needed an SQL query to accompany it.

SELECT
    `yearGroup`.`id` AS `year_group_id`,
    `yearGroup`.`year` AS `year_group`,
    CONCAT(
    TRUNCATE
        (
            COALESCE(
                SUM(
                    `assignment`.`percentage_achieved` *(
                        `assignment`.`percentage_weighting` / 100
                    )
                ),
                0
            ) /(
            SELECT
                COUNT(*)
            FROM
                `unit`
            WHERE
                `unit`.`year_group_id` = `yearGroup`.`id`
        ),
        2
        ),
        '%'
) AS `grade`
FROM
    `assignment`
RIGHT JOIN `unit` ON `unit`.`id` = `assignment`.`unit_id`
RIGHT JOIN `yearGroup` ON `yearGroup`.`id` = `unit`.`year_group_id`
WHERE
    `yearGroup`.`course_id` = 1
GROUP BY
    `yearGroup`.`id`

Answer 1

I've got a few thoughts:

1. The user table is related to the course table in a funny way. At first when I was looking at the diagram I assumed users were students, but after staring for a while, that seemed less and less likely. I'm now under the impression that the user is the owner of the course, and I think that having the user_id in the course table is a little funny. I would use a separate mapping table like so:

2. The lookup tables seem fairly useless as you are using the actual value in the base tables instead of a lookup id. Changing it to look like this will add usefulness:

3. Your naming is a little strange. I would change the course table to be named courseInfo, and change yearGroup to be named course. This might be a little nitpicky as this is a generally well done diagram. :P

Notes in response to comments:

• If you want to share a course between users, you will need to change your schema to be similar to mine. If, as suggested in the comments, only one user will ever use a course, then you're probably okay with the schema your using. I just don't generally think of a user as an attribute of a course. It's a little ambiguous: is that user a teacher, student, or what have you.
• One thing to think about for lookup tables is that if you're not using an id, then the value is duplicated. If you ever update the lookup table, you have to change all values in the other table as well.
• I do definitely think yearGroup is a misnomer. Where I come from, courses are offered by semester or some other frequency besides yearly.