Database design help required

I need help in building simple database structure. Kind of stuck.

Here's what I'm trying.

Class means Department+Semester. Eg, Electronics Engineering SemI, Electrical Engg Sem II.. etc..

Student is in class; Class has a defined group of subjects; Group consists of multiple subjects.

Class and Subjects, 2 entities depend on same SubjectGroup entity. So isn't this a ternary relationship ?

I've made this image to 开发者_JAVA百科make picture clear. [ P = Primary key; F = Foreign Key; Arrows do not mean anything of One-to-many/many to many etc.. they are just showing what referenced where]

I am stuck because I can not refer to GroupId two times.

How should I modify this structure ?

- Thanks

Entities

• Student.
• Class.
• Subject.

Relationships

• Student to Class. One to Many. One class relates to many students.
• Subject to Class. One to Many. One class relates to many subjects.

Tables

Student

Columns:

• studentId - primary key of the student table.
• stuff about sutdent (some number of columns).
• classId - foreign key into the class table.

Subject

Columns:

• subjectId - primary key of the subject table.
• stuff about subject (some number of columns).
• classId - foreign key into the class table.

Class

• classId - primary key of the class table.
• stuff about a class. nothing about student. nothing about subject. no foreign key to student. no foreign key to subject.

Queries

Students in a class

select
    s.studentName
from
    class c
    inner join student s on s.classId = c.classId
where
    c.classId = 'desired class id'

Subjects Student is Studying

select
    sub.subjectName
from
    student stu
    inner join subject sub on sub.classId = stu.classId
where
    stu.studentId = 'desired student id'

I think you need to introduce a classSubject table. Is this an accurate example of rows?

student
beth
john
mark

class
beths_and_johns_class bethssubject, johnssubject
marks_class markssubject

subject
bethssubject
johnssubject
markssubject