Best option for prediction fields

[MikeKD]

Hi folks,

I have a database for my school music department.

It includes these tables:

Pupils (things like DoB, name etc - also baseline test results and previous school)

Marks (linked to Assessment and Class tables too)

Instruments

Instrumental Exam Results

 

What I'd like to do now is:

• Add GCSE, A level and A2 results
• Use previous baseline tests, Marks and Instrumental Exam Results to generate predictions so I can see which pupils are likely to under and over achieve.

My two questions are:

1. How do I go about thinking of the maths (I'm a musician!!!)
   • The GCSE, AS and A2 exams all have 3 papers, each with different maximum marks. Obviously all could be converted into a %.
   • I'd somehow like the calculation to take into account historical accuracy - i.e, if last years predictions were too high, next years will be lower.
2. How do I organise the database to add this, I see 4 options:
   • Add GCSE, AS and A2 result fields to the Pupil table.
   • Add a new Academic Exam table.
   • Use the Instrumental Exam table, but add new fields.
   • Use the Marks table.

Many thanks,

Mike

 

[Agnes Riley]

Hi Mike,

First of all you want to add the exams to an EXAM table. It only needs the basic fields (ID, creation, modification dates/accounts) and the date of the exam, the paper required and the maximum mark (which can be scripted or calculated from the proper year's results). Add the exams for this year. Add their papers and maximum marks. So if you have 3 exams and 3 papers you'll end up with 9 records. Alternatively you can create a sub-table for the papers, but if you only have a few, it's not really worth the effort.

Then create another table called PUPIL_EXAM, which will be a join table between the pupils and the exams they take.

Make sure to add the Pupil ID and the Exam ID, and have its own ID. Then you will need a field, which is the mark they received. You can certainly add other fields, too. This is just your minimum.

Then you can add a portal (from the join table) on the pupils layout where you can add each exam they took (or auto generate the ones they have to take) and add their mark for it.

The script your statistics when you add the new exams taken with their marks. And script your predictions based on this, at the same time.

Hope this helps,

Agi

[MikeKD]

Many thanks Agnes - I have a think about whether I need another table for Papers - I'm pretty undecided at the moment.

Without; if 3 pupils did GCSE they'd each have 1 EXAMMARK record with three marks on it - one for each paper.

With: if three pupils did GCSE they'f each have 3 EXAMPAPER records, with one mark on it.

Regarding scripting, would the first one be easier?

Your immediate reaction was that I wanted another new table for Exams though, so that's where I think I'm heading!

Cheers!

Mike

[Agnes Riley]

Rule of thumb: everything that is a separate noun needs to be put in its own table, if you can have many of them.

No matter what you do, each pupil will have 3 records for one exam if they have 3 papers. :-) I honestly don't think you need the EXAM_PAPER table unless you want to see reporting for all the papers for one exam, whether from a student's perspective or just the exams. Keep it simple but logical.

Edited August 18, 2015 by Agnes Riley

[MikeKD]

So the join (ExamMarks) table would have

ID

Pupil ID

Exam ID

Paper Name

Mark

The Exam table would have

ID

Date

Title / level etc

Total marks for each paper - or I could include that in the Join Table.

 

Thanks!!!

[Agnes Riley]

No, the paper name goes with the Exam, because it's the child of the exam. Or as I said it could go in its own table.

The total_marks for each paper goes in the Exam table, as well.

[MikeKD]

Yep, that makes more sense!!

[jbante]

Agnes has covered the data normalization. Now let's talk forecasting.

What are you doing with the forecasts? I believe we talked about this before on a different thread, but it's worth re-iterating that there's no value in a forecast if you aren't actually going to do different things in response to different forecasts. How you eventually act on forecasts may influence how the forecasting is done, how accurate or precise the forecast needs to be to be useful, whether or not certain biases are acceptable (or even preferable!), etc. How you intervene may also interfere with your assessment of the accuracy of the forecasts: if a student with poor marks gets extra tutoring, then performs better than forecast, we can't necessarily say the forecast was wrong.

What exactly are you forecasting? Each student's mark on a particular exam? Each student's overall marks on everything going forward? Each student's ranking going forward (as opposed to marks)?

What analyses, if any, have you already done into the relationship between past marks and whatever outcome it is that you're forecasting? Does each student's future just look like their own past, or is something else at work?

[MikeKD]

All I've done so far is this:

Used pupils existing baseline assessments to give them a percentile based an all known present and past students at the school. This is accessible via a tooltip by their name in the class register.

This has been very useful in my interactions with younger students - if a pupil is constantly asking for help I can check the tooltip to see if their ability is lower than their peers.

With older pupils this data is not as useful, as it doesn't reflect their musical ability very well.  Traditional baseline test are notoriously inaccurate at forecasting music exam grades.  At school, we have extra relevant information though - instrumental exam results, music class test results etc.

This would be useful to:

• Advise pupils whether they should opt to do music at a higher level.
• Advise teachers what grades they should be aiming for with each pupil.
• With more accurate predictions, pupils would be more likely to get to their optimum university.

Cheers!

Mike

[jbante]

For advising pupils whether or not to pursue higher-level music, do you have marks for students who already went on to do music at a higher level? Are there barriers to entry (e.g. admission tests, application review process, especially grade reviews, etc.)?

For advising teachers, is that basically the same scenario as what you explain you've already done?

How do more accurate predictions help pupils get in to their optimum university? Are students to use their predictions to apply to universities most likely to accept them? What data do you have or can you get relating marks at your school's level with university admission decisions?

[MikeKD]

For advising pupils whether or not to pursue higher-level music, do you have marks for students who already went on to do music at a higher level? Are there barriers to entry (e.g. admission tests, application review process, especially grade reviews, etc.)?

We do have some marks for the previous students - the GCSE, AS and A2 grades.  For the most recent we also have our own internal marks and school prediction data.

 For advising teachers, is that basically the same scenario as what you explain you've already done?

Yes - at the moment we have the school predictions based on baseline test administered by a national agency.  For us though, they're very unreliable hence my wish to improve them (the baseline tests don't include anything that tests musical ability).

 How do more accurate predictions help pupils get in to their optimum university? Are students to use their predictions to apply to universities most likely to accept them? What data do you have or can you get relating marks at your school's level with university admission decisions?

It's all to do with the prediction of accurate AS and A2 grades. Once the students have their AS results we have a better idea of how they will do for A2. This prediction is crucial to them getting a university place.

If we predict too high and they aim for universities at that level, there's a good chance of them not getting the grades they need to get there.

If we predict too low, they aim too low.

 

All this can be especially crucial when students join the school older (which happens frequently). E.g. We've taught one this year who came to do AS and who I believe has underachieved because I didn't spot until too late that they were likely to be very weak in one paper. The student & parents had emphasised a different paper as an historical weakness so more effort was focussed there.

 

I'm starting to get my head round the process of data normalisation / input.  I think I will need a table of papers so I'm able to focus the predictions more. Each paper is based on one of the skills of performing, composing and musical analysis / listening. If I use a check list of these skills in class assessments, then I can focus on the ones relevant to each paper in the predictions.

When adding a record for each paper, I can then run the script to create a prediction for that paper.

 

Cheers!

Mike