Each student is under one department.
Each department has fixed number of semesters.(semester is not a table, as of now.)
Each semester has many subjects.(One subject may exist in more than 1 dept. This is currently not depicted in the diagram.)
Some subjects are compulsory, some are optional (or elective).(decided by
There is a restriction on the optional subject allotment.
Say, there are 3 optional subjects OptSubA, OptSubB, OptSubC in dept X in sem Z. Now, it may happen that a student can choose only 1 of them. It may also happen that the student can choose any 2 of them. Striked out as this example was highly ambiguous. What I want to say is that the rule is not pre-decided but the Admin should be able to add the rule.
Edit: The rule is something like this
Every Student of a Sem X of Dept Y has to choose exactly 1 subject out of given M optional subjects AND 1 subject out of Q other optional subjects and so on. (i.e., optional subjects are grouped together). Every student must choose exactly 1 Subject out of each group.
I will be happy if I am able to design even this highly simplified rule (though above rule is my ultimate goal) :
Every Student of a Sem X of Dept Y has to choose N subjects out of given M optional subjects.
Edit 2 : Example : Consider, Sem Y of DEPT X. The optional subject in a group has similar codes. E.g.,
PE 501 A ,
PE 501 B. There may be yet another group having Subject codes like
PE 502 A,
PE 502 B,
PE 503 C,
PE 503 D. The student needs to choose 1 Subject from first group and 1 Subject from second group. The number of such groups is not known beforehand. Admin should be able to add/remove/modify the groups.
I have created a table
student_optional_subject which stores
subject_code. This table is capable of storing which subjects are chosen by the student but I cannot understand how to store the rule and impose the same.
Besides, this is my first question on this site, so please tell me if I have posted the question in wrong subdomain.
Sorry for editing it so many times. First time asking a DB Related Question.