Students to Assessment Relationship

[JamesT]

Hi,

I'm trying to create a student assessment database to replace an older flat-file 'database'. Though I'm having trouble relating assessments to both subjects and students. I'm sure this could be resolved by more TOs but I'm struggling to get it to work.

I'm looking to have a students grades kept for individual assessments. Assessments are related to Subjects. If someone is even able to point me in a direction I could investigate, or a similar 3-4 level relationship it would be greatly appreciated.

This is the relationship graph of a mock up I designed which replicates the actual database. This is a greatly simplified version as the full version contains additional ESS tables and calculation logic.

Thankfully,

JamesT

[comment]

A few questions:

1. How many grades per assessment?

2. Can a student enroll into more than one tutorial in the same subject (e.g. repeating a course)?

[JamesT]

1. Only 1 grade per assessment. Comments are kept where further details could be noted.

2. A student repeating a subject would actually be sitting a different subject. The subjects only record a single semester duration. This is because subjects may have different assessment components between iterations.

Thanks for looking at this.

[comment]

1. Only 1 grade per assessment.

Well, then why not make grade a field in the Assessments table?

As for the rest, it's a matter of convenience: you could make Assessments a child of Enrollments, or a join table between Students and Subjects; it comes down to the same thing, namely that an assessment must belong to one student and one subject. This can be achieved by having either an EnrollmentID or a StudentID and a SubjectID in the Assessments table.

[JamesT]

If grade was a field in the Assessments table would that restrict me from having a list of assessments per subject? I don't want the tutors to have to enter the assessments per student, but rather have a list of all the assessments for the subject and then each student has a mark per assessment, and the tutors simply enter the marks.

Sorry I think I should have written only 1 grade per assessment per student. Each assessment has an individual grade for each student enrolled in the subject.

[comment]

If there are many grades per assessment (one for each student), then Grades must be a join between Assessments and Students. As you have surmised, you'll need (at least) another TO of Students to implement this at the graph level.

Note, however, that there are a few assumptions being made here. I am not sure what exactly these objects - tutorials, subjects, assessments - represent in real life, but your graph above suggests that students enroll into only one tutorial in each subject (for example). If that's not true, then assessments in the same subject but different tutorials will be mixed together with no hope of telling them apart.

[JamesT]

That's true, a student enrolls in 1 tutorial per subject. Subjects are iterations of themselves, in that, they are only for a single sememster, not even a single year. So each time the subject is run there is a new version of it. This has to do with some of the systems that interact with the DB. There are many tutorials per subject, but a student can only be a member of a single tutorial per subject. And a student can attend multiple subjects at any given time.

Thanks for the tips. I'll implement into the mock today and see how I go. Thanks for all your help.

[JamesT]

Fantastic it works. Thank you comment for your help with this. I made Grade come off Enrollment because it made more sense given the DB intention and current structure.

Is there any way to have the Grade records autofill with the assessments or am I restricted to users having to select from a related drop down for the available assessments?

[comment]

Is there any way to have the Grade records autofill with the assessments

If I understand the question correctly, then the answer is no - because a subject can have many assessments.

Assessments >- Subjects -< Tutorials -< Enrollments -< Grades

[JamesT]

Yes that's right. I wanted the Enrollment records to have a portal showing the Assessments for that Enrollment. Thought it might be possible because the Enrollment effectively only has 1 Subject attached to it, but couldnt get the portal to work.

note: studentID on Grade is misnamed