Insertion anomaly in database design

Gaurav Gupta

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:

  1. A student should be a registered student (Satisfied by regId).
  2. Marks should be inserted for valid(existing) exam (satified by examId).
  3. A student who studies particular subject in a class, then marks should be inserted for those subjects only. This is where i am facing issue.

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.

Mohsen Heydari

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:

enter image description here 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.

edited at
0

Comments

0 comments
Login to comment

Related