I have created the following ERD: Entity Relationship Diagram

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 isCompulsory boolean.)

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 student_id and 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.

Any ideas?

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.



One idea is to add a relation, say student_optimal_no:

CREATE TABLE student_optional_no
( student_id ... not null references student (id)
, optional_no int not null
,     primary key (student_id, optional_subject_no)

If student X can have 4 optional subjects, 4 rows are inserted:

insert into student_optional_no (student_id, optional_no)
values (X,1),(X,2),(X,3),(X,4);

Then add this attribute to student_optional_subject, and make a foreign key to student_optional_no. When adding a student_optional_subject, pick the smallest available number for the student in student_optional_no.


Given the clarifications I would suggest something like:

CREATE TABLE number_of_student_subjects
( student_id ... not null references student (id)
, type_of_rule char(1) not null  -- M=Mandatory, O=Optional
, number_of_subjects int default 0 not null
,     primary key (student_id, type_of_rule)


insert into number_of_student_subjects
    (student_id, type_of_rule, number_of_subjects)
values (X, 'M', 3)
     , (X, 'O', 2);

If the set of Mandatory/Optional subjects for each student has to be defined

CREATE TABLE student_subject_collection
( student_id ... not null references student (id)
, subject_id ... not null references subject (id)
, type_of_rule char(1) not null  -- M=Mandatory, O=Optional
,     primary key (student_id, subject_id, type_of_rule)

What the student enrols in:

CREATE TABLE student_subject_enrolement
( student_id ... not null references student (id)
, subject_id ... not null references subject (id)
,     primary key (student_id, subject_id)

The results

CREATE TABLE student_subject_result
( student_id ... not null 
, subject_id ... not null 
,     foreign key (student_id, subject_id)
          references student_subject_enrolement (student_id, subject_id) 
, ...
,     primary key (student_id, subject_id)

To verify whether a student has the correct number of enrolments you can compare the number of enrolments with the number of mandatory and so forth.

Sorry, I'm in a hurry so that's the best I can do at the moment.

  • My rule was quite ambiguous. I have added a bit of clarification in the question. Can you please take a look and update your answer to reflect the same? Every student gets the same number of subjects to choose. – Aditya Shaw May 7 '20 at 20:40
  • A rule like: Every Student of a Sem X of Dept Y has to choose N subjects out of given M optional subjects. can not be enforced in the case that a student chooses only N-1 subjects. If they must choose all subjects at once, a transaction with all or nothing semantics can be implemented. But in any case. a violation can, of course, be detected and acted upon. I'll see if I get the time during today to come up with something. – Lennart May 8 '20 at 6:10
  • I am ok with forcing students to choose it at once. Sorry, I have again edited the Question to make it more clear and concise. I hope I am not creating trouble. – Aditya Shaw May 8 '20 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.