Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    3

    Question Unanswered: Normalisation headache

    Hey guys,

    I've been trying to create a student records database and am not so confident about its design and am fairly stuck with one particular section.

    First up, yes this is for a student assignment, which is apparently ok in the forum rules, so if you don't feel comfortable assisting me please abandon thread now

    The student record system should have students, who can do many courses. Each course may have several modules.

    Attached is my attempt at an implementation of the many to many relationships required. Now I admit I'm fairly rough at database design so I'm not sure how well I've done it, however assuming it's not totally broken I'll present my problem :

    I can't figure out how to relate grades for individual units


    Any hints or tips?

    Thank you in advance
    Attached Thumbnails Attached Thumbnails design.jpg  
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends enitrely on how your academic institution handles things
    bear in mind any assessed mark may go to a review board, an overall year mark can also go to a review board and be adjusted up or down
    a student can object to an assessment and request a remarking.
    some instituions apply penalties for late, too little / too much (as if!) coursework.
    some courses carry forward marks formt he previuous year into this years or final years mark.

    So an individual piece of coursework that is handed in and marked may contribute to the end of year assessment
    it may attract a penalty of being late, or too short
    it may attract a credit because the student is dyslexic, or otherwise non standard
    the course work may be mandatory (ie the student has to complete (or attempt or pass it) some courses actually do have mandatory coursework which has to be attneded, but dont' require a pass
    the module may or may not be examined, it may or may not have an element of coursework (that element of coursework may comprise several pieces of coursework
    some coursework may be laboratory practical, some may be essays / reserach.

    some modules may be common to many courses
    some modules may be worht more than other modules to the final year assessment.
    some students can take more modules than they are actually required to take. say a student requires 120 credits to complete the year, its possible that they may study 120...180 credits you need to decide how to handle the amrks for these over acievers (ie do you take the average of all units, do you take a weighted average, do you take the best 120 credits worth and ignire the rest....)
    some students may be registered for more than one course in any one acadmeic year - so you need to be able to handle that (ie make sure that you only pull thre right results for the right course.
    some course have specific pass / fail / grade thresholds some don't.
    how do you propose to handle resits (bearing in mnind that a student may resit a course, a module(s) both in the summer, the autumn, the following exam year. a student may have to resit exams, coursework or the whole module. if they are in resit then you may have to apply a penalty to their following grade

    so for each module, in an ideal world you need to know the types of assessed (marked) work and how much they contribute to the final year mark. (eg the exam(s) are worht 60% of the final year mark, and the total of the coursework is worth 40% - but you need to know if that is 4 x 10%, or 15%,15%,5% & 5%)
    some moduels are optional, some are mandatory, some are required for a specific sub option
    that means you need a table identifying coursework being undertaken in that year for that course
    you need another table identfying what assessed pieces of work that student did (it should map directly back to the student and the pieces of coursework

    its optional if you want to store a moduel assessment (probably a good idea to do so)
    its optional if you want to store a course assessment (probably a good idea to do so)

    you need to build in the variuous review stages and decisions made at the exam board (eg pass, fail, grade, adjustment and probably include reasons.
    you need to have a clear track record of what happenened to the original mark

    eg student "wrist" is taking a dbdesign module as part of a degree
    that moduel has a piece of assessed coursework (say to design a student record system

    wrist hands in the work late with too many tables
    the coursework is marked as 55%
    however the work was handed in late - penalty - 10%
    and had too many tables (the academic couldn't be bothered to look in detail at the design) - 10%
    meaning the the mark is now 35% a borderline fail
    this brings the year average low so the exam board may review the marks and decide to rescind the penalties. however another academic believes the piece of work is copied (ie isn't the students work (say he copied it from dbforums.com) and the board elects to zero the mark instead.

    student fails the year, is called before the exam board - paints a clever picture as to how its all his own work and dbforums wasn't that relevent or helpfull, mark reinstated. the exam board finding that their are too few passes inthis particular course and therefore decides to adjust the results for all / most students to ensure that they achieve government and universtiy targets and ass 10% to all student marks to make sure that the course isn't cut next year and that the academics will have to go out and get a proper job.

    bear in mind that not all modules may run in any one year (ie it is possible a module smay exist, but no one is doing it this year, equally its possible that the same module may appear in more than one course, and in more than one academic year (ie module xxx can be taken in year 1, 2 or 3) I presume your model is going to say student enrollment is a function another external system - if thats not the case ruight now, then do so enrollment is fiendishly complex).

    so
    a course comprises several modules, some of which are optional
    some modules may be offered in differnt courses (and different years within the course)
    a module may comprise many pieces of assessed work
    a student registers for a course and modules
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    3

    Red face

    And verily the great hand came down from the heavens and did smite him, like he had never been smote before.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, for homework questions, we generally don't give answers, but we sometimes do give pointed questions. Like this one: Is a unique grade supposed to be determined by a combination of student and course, or student and module?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Healdem, from now on, please submit all your responses in the forum of Haiku.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by wrist
    I can't figure out how to relate grades for individual units.
    What's a unit? I don't see that on your diagram.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unit probably equates to a module
    a modules assessment is probably comprised of a mix of coursework and exam (but may also include lab or practiacl or fieldwork)

    as you may have guessed I was involved with a copmplex project handling student assessments. the rules are legion and conflicting. at times it seemed like anything goes, providiiong of courtse you get the "correct" number of passes (and grade dustribution) per course by module. The stories I could tell....
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2006
    Posts
    3

    Post

    Sorry, yes units=modules.

    Basically, despite the potential complexity healdem has described, for the purpose of this exercise each module should have a mark (lets say 0 - 100 ).
    Each module an enrolled student has taken should contain a mark.

    Would creating another table say called studentsModules and linking an enrolled student to the coursesModules be advised?


    Attached updated image.
    Attached Thumbnails Attached Thumbnails design.jpg  

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Wrist
    enclosed is a bowlderised version of a students assesment model.

    I (We) have only included the keys. It al depends how you map out your model. This one was set up to separately identify each piece of work a student did within a sepcific module. It doesn't cater for a resit per se (the resit was handled in a totally cackhanded manner so its been removed)

    MarkComments contains codes that identify what the mark is. ferisntace it would include things like "original mark", "Late Submission", "Work To big/small", "Plagarism", "n%allowance for special needs" etc......
    AssessmentDef defines what pieces of work are expected for the specified unit (ie the following pieces of work (essays, research, lab, practical, exam etc...) are planned for this unit
    AssessmentType identifies what type of assessed work is expected int he definition.
    StudentAssessment is the container that registers the students submission of assessedwork (eg took exam on xx/xxx/xxxx, handed in on.... etc)
    StudentMarks is the tabel that actaully carries the marks (and any adjustments assigned to that piece of work.

    The problem here is how to handle rework / resists especially as soem exam boards expect to see the current and any previous mark.


    HTH
    Attached Thumbnails Attached Thumbnails StuAssBasic.png  
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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