Results 1 to 4 of 4

Thread: Database design

  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Question More Questions!

    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?

    Thanks for the help.
    Last edited by JamieK; 02-11-03 at 16:32.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Database design

    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
    BEGIN
    SELECT grade INTO :NEW.original_grade
    FROM grade_boundaries
    WHERE :NEW.original_mark BETWEEN ???;
    END;
    /

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    Some more help needed

    If Field A Does not = Field B then Field C = True.


    Also if the subject is x and mark is y then the grade is z. I want field z to automatically fill in as A, B, C etc from the GRADE BOUNDARY table.

    Does anyone know how to do this in access?

    Thanks for the help everyone.

  4. #4
    Join Date
    Feb 2003
    Posts
    3

    More details on tables.

    Cand #1, Martin, Maths, Centre #1
    Cand #2, Martin, Physics, Centre #2
    Cand #1, Martin, Computing, Centre #1

    Candidate numbers are unique within each centre.

    I hope that this helps you help me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •