14 Replies Latest reply on Jun 27, 2012 4:50 PM by jamest

    Student - Assessment Relationship


      I'm developing a database to allow tutors and staff to record individual assment marks and comments per subject as this functionality isn't in a larger system. I've managed to setup a basic test case of how this will work, but I am stonewalling on some simple relationship issues.


      I want to be able to view a student either directly or through another table, and add a Grade::mark to each assessment they complete. Each student will complete multiple assessments per each subject they attend, but each assessment they complete will only have 1 Grade::mark recorded. In my current setup this sort of works, but through portals I have to add each assessment by first adding a grade then selecting the assessment from a drop-down. As I'm developing this for others I would like to stream-line the whole approach so that there is a list of all the assessments already populated for a given subject for a given student that a tutor can fill in easily. I would prefer this to be a portal but it doesn't need to be as long as the process is easy. A student can only complete a subject once. If they repeat a subject it will be a new occurance of the subject and tutorials. Assessments are also liable to change each semester.


      I have a feeling this can be solved by adding another series of table occurences where the tutors enter the grade::mark results, but I have tried several ideas and they haven't seemed to work.


      This is a mockup of the database just to test my relationships, I understand its basic look and minimalistic fields.


      Any help with this would be greatly appreciated.

        • 1. Re: Student - Assessment Relationship

          Hello, James.


          Presumably, Enrollment represents a join table between Tutorial and Student, yes? If so, I believe your difficulty may be coming from tying the Enrollment ID to Grade, but I'm not certain because I'm not sure about your intent with the data model. Can you elaborate a bit on your intent with the data model? Specifically, what does each table represent and how do you populate the records therein?



          • 2. Re: Student - Assessment Relationship

            Hi Mike,


            Yes you are correct, Enrollment is a join table for Tutorial to Student. Any thoughts on the Enrollment/Grade relationship would be greatly appreciated, just can't seem to get this thing to work. Didn't think it was a terribly difficult relationship. It's beyond my limited abilities though.


            N.B. Population by ESS will probably be copied in from ESS rather than directly displayed. This is due to feed ESS availability and access. Saying populated from ESS will mean data set in FileMaker from ESS but performed by script.



            • Assessment - Name of each assignment, report/essay students are required to complete for a subject. Assessments have a weight which is the percentage of their final mark they are worth. A student can only complete a single assessment once, or if there were extenuating circumstances a comment would be kept.

                 Manually entered for each subject by and admin each semester. No way around this as most assessments change from semester to semester and no system stores details of it.


            • Subject - A student can only complete a subject once as a subject only covers a single point at which it is run. It doesn't cover multiple semesters or years. Each semester a new subject will be created to hold that periods students.

                 Probably populated from an ESS feed or manual entry. Only contains Subject Title, Code, and Lecturer Name.


            • Tutorial - Each subject has at least 1 tutorial. Each student attends a single tutorial session for each subject they attend. A tutorial is simply a class of students. Each tutorial has a single assigned tutor. This tutor will be entering the assignment marks and comments.

                 It doesn't need to be stored where it is and the data for tutorials may be coming from an ESS so a simple set of scripted finds may be able to move the TO position.

                 Similarly populated by ESS if available (ESS has not been completely sorted out yet though).


            • Tutor - Just the name of the person responsible for taking the Tutorial / Class. I may include a start and end date for finds so that searches only include current tutors.

                 Manual entry of Tutor names and assigned to Tutorial classes. There are ~60 tutors for ~80 different subject to match. This will need to be done manually by an admin, as the tutors are unreliable and no other system matches their tutorials that they teach with the ones the students attend.


            • Enrollment - Like you uncovered Mike, a join table for Student to Tutorial to resolve the many to many relationship. Will probably house calculations of final marks, subject comments and tutorial participation marks.

                 Populated by script/ESS of students relationship to tutorial.


            • Grade - To hold the marks and comments for individual Assessment pieces. It needs to be able to relate a student to a subjects' assessments.

                 Would like to be automatic as in have a portal list of all the Assessments for a Student's Enrollment record and have users simply click in an Assessment::Mark field to fill in. The tutors receive training in how to use the system but they use infrequently and sometimes long after they receive the training, making mistakes more prone.


            • Assessment 2 - TO of Assessment allow Grade and Enrollment to read Assessment::Title

                 Data not entered into, only used to relate.


            • Student - Name, student number, contact details, etc.

                 Populated from ESS.



            I hope that helps. Thank you very much for looking into this Mike.



            • 3. Re: Student - Assessment Relationship

              Given all that, would you not have a Grade for each Assessment? In other words, why isn't Grade a data element of Assessment instead of a separate table?



              • 4. Re: Student - Assessment Relationship

                Not sure if this will help, but I had to do something very similar lately. In my case the student enrol for a program and all the student enrolled to this program will follow the same subjects but not necessarily at the same time.


                I added a notion of semester in the tutorial table and enrolment table which avoids having to recreate the subjects everytime. The students are basically enrolled to a specific subject but only for a specific semester.


                As far as the grades are concerned, I had it a bit easier as I don't keep records of the assesements, so I actually link the grade table directly the tutorial table (I have a subjectID field in the grade Table).


                To make things a bit easier as well, I decided that I wanted only one grade record per subject (per student), so my mark field is multi-valuated to allow multiple assesement grades for each subject. I keep the weightings in the tutorial table (as a multi-valuated field as well). I have a result field in the grades table that will then calculate this (really simplified example of course): ( Tutorial::Weight[1] * Mark[1] + Tutorial::Weight[2] * Mark[2] et...)  / Sum (Tutorial::Weight).


                Of course depending on the view I want to have, I link the TOs differently (student View with all his subjects and grades, tutorial view with all the linked student and their gradesetc..).

                1 of 1 people found this helpful
                • 5. Re: Student - Assessment Relationship

                  A Grade for each Assessment as an element would mean that it's a single Grade across all the students performing that Assessment wouldn't it?


                  ie. if Grade were an element of Assessment wouldn't that mean that I could only store 1 Grade value per Assessment and that there would need to be an Assessment record created for each relationship to a student.


                  If I'm right with your line of thinking that would work, but the tutors would need to enter in each individual Assessment piece per student before assigning them a mark. I was hoping that I could have a list of Assessments that are populated once that allow a Tutor to enter the mark of the assessment per student.


                  I'm not sure if I've got it right though, so I could be very wrong.



                  • 6. Re: Student - Assessment Relationship

                    Okay, I think I see your problem.


                    You want an Assessment to be a record of an "assignment" or "test" or "evaluation". Each student takes that assessment - which means you have one record for each student for each assessment - which means there's a many to many relationship between Student and Assessment - which means Grade is really a join table between Student and Assessment, yes?



                    • 7. Re: Student - Assessment Relationship

                      Hi Thomas,


                      In terms of Semester that would be something I should look at. Given that the data comes from an SQL database it is most likely already structured similarly to what you suggest. Unfortunately for me they don't track individual assessment pieces, only a single end of semester grade, hence incomes FileMaker.


                      I will try today linking the Grade table to Tutorial today rather than Enrollment and see if I get closer or expected results. Thank you for this.


                      I have thought about adding a repeating field to Subject containing each of the assessment data and I've wanted to avoid this. I may not be able to because I may not be able to get an auto-filling portal the way I want (at least easily within my capabilities). Thank you for your details on the calculation.


                      I was thinking perhaps I needed a separate unrelated set of TOs to simply link Student -< Grade >- Assessment. I did try this before but thinking about it now, I believe I may have missed something.



                      • 8. Re: Student - Assessment Relationship

                        Yes that sounds exactly right.


                        Will that mean I should create a separate set of TOs for this or should I just add this link to the main tree?


                        I'll have a go at working out a few ideas into my test solution. Thanks Mike.

                        • 9. Re: Student - Assessment Relationship

                          Hi James & others,


                          Just an opinion piece....


                          As a trained teacher and former special educator, it frustrates me that this wheel keeps getting reinvented without the level of detail that makes it a valid student performance measurement and reporting tool.


                          'The system' is at fault because beauracrats and budgets run education and deem a series of assessment events to be an adequate measure of achievement and all notion of ongoing continuous assessment is artificially boxed into these assessment events.


                          My solution is pretty old now (2002) but what it does is sucks in an entire syllabus then tracks and reports student performance based on the following heirachy:




                          KLA (key learning area / subject)

                          Grade / stage

                          Outcome (this is probably the level you are using as asessment)




                          At each level you can comment and score... and you can report instantly with statistical summary at the desired level of detail required.


                          Anecdotes are important but they are not measurable and are inevidably subjective. Similarly assessment events cherry-pick the syllabus rather than measure it's entireity which inevidably disadvantages some students and advantages others who perform well under testing conditions.


                          I wish all educational achievement were measured with more objective data-based tools.


                          - Lyndsay

                          • 10. Re: Student - Assessment Relationship

                            Hi Lyndsay,


                            Thank you for your imput. I can't comment on what management would say to your suggestion. I haven't stated this until now, because I've been treating it like a school, but I actually work for a University. Each of the assessment pieces could be reports, oral presentations, etc. Class participation is also tracked and monitored. This doesn't directly affect their grade (except in one or two cases), but it is used in incidents where a student may perform sub-expectation in an assessment or overall. I have come from a background similar to yourself and can see the merits of a system such as you suggest, the only issue would be standardising it. Unfortunately at the University level though I'm not sure it would work, especially given the differences in tutor markings, and in many cases, lack of real teaching ability.


                            But thankyou for your input, if I fall into a different role it will definately provide me something to think about.



                            • 11. Re: Student - Assessment Relationship

                              Hey, James.


                              That's a question of graph management, which isn't an exact science. There are different schools of thought on how you manage the graph (and some of them approach religious fervor).   


                              Ray Cologon wrote a great white paper some time back on different approaches to graph modeling. I've attached it to this post. Basic considerations:


                              1) The graph defines the context of your layouts and calculations. The more complex it is, the more you have to mind your context.

                              2) The graph has to be something you understand.

                              3) The graph can be organized in different ways - there's no single "right" way to do it. Each method has advantages and disadvantages.





                              1 of 1 people found this helpful
                              • 12. Re: Student - Assessment Relationship

                                Hi James,


                                I did suspect you were dealing with adult ed. Special ed is notionally at the other end of the continum but I think the same tool set could be applied whichever. In fact, I built my system so that with a click of a radio button the jargon used throughout changes from "school" to "business"...


                                You infact point out the biggest problem with educational measurement – "given the differences in tutor markings, and in many cases, lack of real teaching ability". How do we compare a degree from one institution to another when the measurements of achievement are poles apart.


                                Resistance of the staff to actually use such a system in real-time is enormous and I do not doubt that is because the record entry process in such systems is often time consuming and tedious. This often leads to entry from memory and concocted data. The reality is therefore that such a system would be rejected in many environments. That doesn't stop me believing it is right. Because incremental changes in special ed are so small it is actually quite exciting when you deploy such a system... you can see that learning has actually taken place and you can prove it.


                                I am not avocating standardising to such an extent that we damage diversity or narrow content taught. I am advocating the use of systematic tools for dealing with the instructional content and achievement of it.


                                When you change your role... call me.

                                - Lyndsay

                                • 13. Re: Student - Assessment Relationship

                                  Hi Mike,


                                  Thanks for that article, very extensive indeed. I've been going through testing various different TOs to suit, nothing that works yet, but I will keep working on it, hopefully I'll find something to match the task soon. I have the base table set down and that produces a portal I can select the Assignments from, but only after I've entered a mark to create the relationship records. If I can get the Assignment list to populate with related records and then adding a mark will create the Grade record that will be perfect.



                                  • 14. Re: Student - Assessment Relationship

                                    I received a private message regarding this question from someone who had a similar situation. She explained their solution to me and it seems that an FM robot or similar scheduled task may be the only way to get this working the way I wanted. I'd like to thank

                                    https://fmdev.filemaker.com/people/kiwikaty for providing the details to me.


                                    Thanks to all those who helped me along the way with various different solutions and ideas. Your ideas will make this a better application.