Normalization issues

Question

I was normalizing a Grades table which contains column of RollNumber, TotalMarks, and Status.

RollNumber is foreign key from student table.

Status column is dependent on TotalMarks as if student with greater than or equal 33 percent marks will have a status of 'PASS' and less than 33 percent will assign status 'FAIL'.

So to remove this transitive dependency (because Status is dependent on TotalMarks), I have to create new table with TotalMarks and Status.

The problem is that RollNumber 1 to 10000 is assign to class 9th with total marks of 500. So the passing condition in that case would be greater than or equal to 165 marks.

And for RollNumber 10000 to 100000 is assign to class 10th with total marks of 1000. So the passing marks in that case would be 330.

So in this newly created table, there would be discrepancy if I create only two column of TotalMarks and Status, because for some 165 TotalMarka, a student would be pass while for other 165 TotalMarks, a student would be fail.

So to tackle this, I have to create my own new ID which would be unique and assign each RollNumber this new unique ID and link it to this newly created table, but this would take a lot of memory because of data repetition.

So how to efficiently tackle this situation?

Note: This Marks distribution of 500 and 1000 marks are dummy, there are more than 8 classes in my DB with different passing and failing criteria.

Answer 1

TotalMarks alone is not a determinant for Status because Status is also dependent on RollNumber. The functional dependency in this case is {RollNumber,TotalMarks} → {Status} which is not a transitive dependency. It follows that {RollNumber,TotalMarks} must be a superkey and that being the case it follows that your Grades table satisfies Fifth Normal Form. There appears to be no normalization issue here that would require the creation of a new table.

As stated in answer to your previous question, normalization never requires the creation of new attributes.