Insertion anomaly in database design

Question

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:

A student should be a registered student (Satisfied by regId).
Marks should be inserted for valid(existing) exam (satified by examId).
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.

I can not find the association between student and class! How we can detect that 'a student who studies particular subject in a class'? — Mohsen Heydari, Mar 15 '14 at 15:29
@MohsenHeydari `STUDENTINFO` has F.K s_cisID that refers to `cis_Id` od `ClassInSchool`. I will add it to in this picture. — Gaurav Gupta, Mar 15 '14 at 15:36

score 1 · Answer 1 · answered Mar 15 '14 at 13:55

All the tables that describe the static objects are there. These are look up tables. To get to your answer a transaction table that pulls items from the lookup tables is needed. The transaction will describe the particular student in a particular class. Maybe the transaction will include the exam they are signed up for if there is always just one exam for a class for a student.

Once the transaction describing the student in a class exists, you can say I have an exam for a class - is this student in the class. There are lots of ways to put this together. I'm assuming your student info table just holds students although the key id hints that maybe it needs to be split. One way could be:

Registration Transaction                 Exam Assignment Transaction     Exam Result Transaction
registration id  unique                  exam assignmt id                result id
student id                               exam id                         student id
class id                                 class id                        exam assignmt id
registration date                        exam date                       scores, etc

If a student is in the registration table and the exam is assigned to the class, create a row in the result table

I think it need a bit more to learn the concept of lookup and transaction table. Can you refer to some useful resource to study. — Gaurav Gupta, Mar 15 '14 at 14:10
The answers below are very good. If you want to dig in a little further you can also check out on-line tutorials or books as suggested below. Here are a couple more links I dug up: http://www.sqlcourse.com/index.html Database Journal http://stackoverflow.com/questions/377375/a-beginners-guide-to-sql-database-design StackOverflow http://msdn.microsoft.com/en-us/library/ms167593(v=sql.105).aspx Microsoft SQL Tutorials — Ramoth, Mar 16 '14 at 12:51

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

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.

edited May 23 '17 at 10:25

Community

1
1

answered Mar 15 '14 at 16:28

Mohsen Heydari

7,256
4
31
46

What if there are two exam for the same subject, say, mid-term exam and end-term exam for Maths. – Gaurav Gupta Mar 15 '14 at 17:33
Exam pk = sequence + subjectPK – Mohsen Heydari Mar 15 '14 at 17:35
what is `sequence` here. – Gaurav Gupta Mar 15 '14 at 17:37
A sequence may be generated by incremental function, identity or GUID or MAX + 1 per subject in exam table, the compilation with subject pk will be the composite pk of exam. – Mohsen Heydari Mar 15 '14 at 17:38
Sir, same subjects are read by whole class (specially speaking class + section), so as per your design aren't you duplicating same data many times. Say, there are 50 classes with every class having 50 students, so we need to store only subjects read in 50 classes(not in subjects read by every student). If a student is in class 5 and section A, then we can get the subjects from that class. That will help us in removing same data (i am not sure but possibly this is redundant data ). Please share your views on this – Gaurav Gupta Mar 18 '14 at 06:33
Actually STUDENTINFO Pk = taught_in FK + student Fk = subject_PK + ClassInSchole_PK + student_Pk , having subject_PK inside STUDENTINFO does not mean a direct relation to SUBJECT table. it is part of taught_in foreign key. – Mohsen Heydari Mar 18 '14 at 06:50
i understand that, but my point is, if there are 3000 entries in Student table then to map student from some subjects we are making another 3000 entries in `studentinfo`. Can these `another` entries be eliminated/reduced since `num Of students` can be large number. – Gaurav Gupta Mar 18 '14 at 07:04
You mean students table is redundant and just studentinfo table will be efficient? – Mohsen Heydari Mar 18 '14 at 07:21
I am thinking of a way to eliminate entries from studentInfo table. It is just a raw thought. Possibly my thinking leads to `no way out` situation. But, i gave a thought for this too. Otherwise backing off and getting on correct path is always an open option – Gaurav Gupta Mar 18 '14 at 07:25
1

studentInfo is the place where registration of a student to a subject in a specific class happens. It is a valuable data, you need to persist the registration some where. think about having 3000 student 50 subject and 20 classes. We will have 3000 student record(we have them in reality) 50 subject record and 20 class record then we will have registration info, in most pessimistic condition 3000*50*20 records in studentinfo, that will not happen since not all subjects will be taught in all classes where all students will register for all subjects in all classes! – Mohsen Heydari Mar 18 '14 at 07:35

score 1 · Answer 3 · edited May 23 '17 at 12:13

1

You need to use identifying relationships, analogous to what was described here, here, here and here. You may also be interested in relative pros and cons of surrogate and natural keys.

I'll leave the exact implementation for this case as an exercise for the reader ;)

edited May 23 '17 at 12:13

Community

1
1

answered Mar 15 '14 at 18:35

Branko Dimitrijevic

50,809
10
93
167

Insertion anomaly in database design

3 Answers3