At the moment i have two tables:
SUBJECT GRADE BOUNDARIES(*Subject referance, A, B, C, D, E, U)
RE-MARK SCRIPT(*Candidate number, *Centre number, Title, Surname, Fore Name, Original Mark, Original Grade,Re-mark Mark, Re-mark Grade, Return Script, Date Submitted, Subject Referance, Re-marked Yet?, Grade Changed?, Re-mark Date)
I have two questions:
1. Do I need to spit the Re-mark table? Is it in 1st 2nd or 3rd normal form?
2. I want the original grade to fill in auto matically depending on the subject referance and original mark. Different subject have different grade boundaries. How would i do this automatically?
1. This is difficult to answer for sure without knowing your data. If the same Candidate number appears in many RE-MARK records, then you might want to move Title, Surname, Forename into a separate Candidate table.
Also, a lot of the columns look very derivable, e.g.:
- original grade (see 2)
- remark grade
- remarked yet? (if there is a remark mark then the answer is "yes"!)
- grade changed? (if the grades are different, "yes"!)
The fact that the table is named RE-MARK SCRIPT is suspicious - it suggests that ther may be another table called ORIGINAL MARK SCRIPT or whatever that contains the original mark - in which case, you don't want to repeat the original mark in this table.
2. If the GRADE BOUNDARIES are static, then there is no requirement to store Original Grade in the RE-MARK table at all - that would be a denormalisation. You can just derive the grade via a join.
Assuming there were a need to do this, you could use a database trigger along the following lines (this is Oracle's syntax):
CREATE TRIGGER t BEFORE INSERT ON remark_grade
SELECT grade INTO :NEW.original_grade
WHERE :NEW.original_mark BETWEEN ???;