I want to design a DB for school, where marks for students will be scored. But i got stuck at a point with my current design where i want to insert student marks. My current DB design at broader level is something like:
------------------- ------------------- ------------------
STUDENTINFO | | EXAMS | | taught_in |
------------------- ------------------- ------------------
reg_id | | examID | | t_cisId |
cis_Id | | examname | | subjectcode |
$otherAttributes | | | | t_id(auto-inc)|
__________________| |_________________| |________________|
------------------- ----------------- ------------------
|ClassInSchool | | Subjects | |Result |
------------------- ----------------- ------------------
| classes | | subjectcode | | regId |
| section | | subjectname | | examID |
| cis_id | |_______________| | t_id |
|_________________| |__scoredmarks___|
Now Issue is : In result table i want only those records to be inserted in table for which following condition satisfies:
E.g, In sec A of class 7, English is taught. then For a student studying in 7A only marks for English can be inserted.
In my design if in sec B of 7 class, Maths is taught, then i can insert marks for a student for Maths
who is studying in 7 A
.
I want to handle this behaviour at database level only, otherwise i have to handle this in Java side.
Note: regId, examID, t_id are F.K. I have used similar names here for F.K to make it understand pictorially.
I hope I have understood the problem correctly.
It seems you design may need some improvements.
In the current design nothing could prevent a student to have participate in an exam twice.
Exam have no subject so an exam could be result in multiple subjects... etc
(correct me if i am wrong)
BTW the source of the problem in the current design is that you have used surrogate key identifier as the primary key of association tables. It is the side effect of using surrogate key , more info
BTW the draft design bellow may come in handy:
Note that:
taught_in PK = subjectPK + ClassInScholePK
STUDENTINFO Pk = subjectPK + ClassInScholePK + studentpk
Exam pk = sequence + subjectPK
result pk = (STUDENTINFO Pk) + (Exam pk) =
(subjectPK_1 + ClassInScholePK + studentpk_1) + (sequence + subjectPK_2)
Having unique constraint on student_PK + sequence + subjectPK
will result non duplicate student results per exam.
Having check constraint on subjectPK_1 = subjectPK_2
will result subject specific results.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments