Developing database schemas for Spring 2022 release

[CharlieMahana]

As we prepare for the internal release of the API, we need to finalize our schemas in order to enable other projects to give other projects an adequate amount of time to integrate with our data before the March 21, 2022 release. With this in mind, I would like for our schemas to be completed by March 1, 2022.

I recommend consulting CourseBook, Galaxy, and the UTD Website for ideas.

Please use the space below to discuss how you think we should design our schemas. I will be actively updating this post with the current revisions of our schemas as they come to fruition below.

[jpahm]

I have a number of revisions to our current schema plans I'd like to suggest:

General

- Better leveraging of TypeScript via type aliases instead of raw primitives for properties
i.e. the type of the .class_level property of a course could be defined as follows:

type classLevel = "Undergraduate" | "Graduate"

I believe this would make our internal API infrastructure more coherent and strongly-typed later on, actually taking advantage of the fact that we're using TypeScript over raw JS.

Courses

• Switching the requisite-courses list properties to collections of course IDs rather than objects
  I see this as the most straightforward solution to the issue of recursion within the requisite listings. There's no need to store entire course objects anywhere, and this will encourage a simple system of reference IDs instead. With this system, verifying whether a student meets the requisites of a course will be as simple as iterating over collections of IDs, rather than recursively climbing entire object structures. This should also drastically decrease network load when the API is in use, as full object structures will instead be queried on a case-by-case basis instead. API methods could be implemented to make working with these IDs as painless as possible.

• Removing the .postrequisite_courses property
  I can't see a scenario where this is necessary alongside corequisite_courses and prerequisite_courses, it seems to be the least useful of the three and is actively encouraging clutter/overlap within the requisite framework.

Collections

• Overall, I like the idea of collection objects for managing requisites. My only suggestion would be, in order to fit with the changes proposed above, that options[] is instead an array of course IDs rather than entire course objects.

Sections

• As per suggestions above, section_corequisites should probably consist of ID references to other sections rather than actual section objects.

• Why is the syllabus_reference a number? I think this would be best left as a simple hyperlink to Coursebook's copy of the syllabus, unless we have further plans for this.

• A standard schema or at least type alias should probably be developed for meetings that can be used throughout various other schemas and internal API methods.

• We should probably establish whether attributes property is actually necessary or not. Cutting out disorganized misc. data would probably be beneficial if possible.

Professors

• Pretty happy with this layout, perhaps something listing their office hours for different classes would be nice? Open to suggestions as to how to best implement such a thing.

[CharlieMahana]

I completely agree with you about leveraging type aliases to more strongly type our infrastructure. Regarding the points you made about courses, imposing a reference based system is probably the clearest and most efficient way of managing our data. Additionally, I agree about removing the post-requisite property as it really beyond our scope. It was originally thought up as a way to make development for planner easier. It's probably better that we implement this in the client library instead.

I've already addressed my stance on using references instead of storing objects above so I'll ignore any other points concerning that issue. Regarding syllabi references, I would agree that we change the reference to a hyperlink. We did have some plans for using the references differently earlier on, but for the time being, hyperlinks will do the job just fine. Additionally, the attributes property was there merely to serve as a place we could put data that our schema didn't have places for if we found an edge case in production and still wanted to wholly represent a section or course.

Finally, regarding professors, I like the idea of listing office hours. However, since their hours can change from semester to semester, we would have to somehow extract this data from the existing section objects or syllabi of the current semester. This is clearly a non-trivial task so ideas from others would be appreciated. Fortunately, we do not need to flesh out the professor data type by March 21st.

[jmyrick02]

We could possibly use the meeting schema you suggested to store office hours as well. I could use a little clarification on how office hours work. Does a professor have 1 general office hours for all his/her classes or are there different office hours for each section/class? The answer to this question would change our implementation of this.

[jmyrick02]

I would like to bring up some things I noticed were potentially lacking in our schema while initially implementing the kickoff demo.

A good way to illustrate the possible shortcomings of the current schema is the course CS 2305 (Discrete Math I). From the coursebook: "ALEKS score required or MATH 2312 with a grade of C or better or (Data Science major and MATH 2413). (Same as CE 2305)". This will be referenced in points 1-4.

1. Our schema does not support prerequisites that are test-based, only those that are class-based? We need to support the ALEKS test and AP tests. Perhaps we could create a separate object in the schema for these types of tests.

2. Should our system handle the minimum grade required for a course to satisfy the prerequisites? Some classes may require a C while others may require a C- while others may not require a specific grade at all.

3. We need a way to handle major based requisites. As you can see in the CS 2305 requirements, a Data Science major satisfies the ALEKS or Math 2312 requirement. I also saw this Data Science prerequisite in some other courses. I'm not sure if it's prevalent in other majors.

4. Many classes are equivalent to others, like how CS 2305 is equivalent to CE 2305. Should we track these equivalences in the course schema?

5. I found an interesting prerequisite for CS 4485 that required "at least three CS 43XX classes." We could either have a collection of all the CS 43XX classes for this specific case or we could perhaps make a more general system that matches classes by properties, similar to regex, including subject, credit hours, and class numbers.

6. It's also worth mentioning that within MongoDB, ids must be 24 byte strings (24 characters).

[jpahm]

I think you've brought up some great points, and I'd like to talk further about each of them below.

1. As far as test-based and various other misc requisites, I think the Collection schema we already have is sufficient or could be made sufficient for representing these. The current schema has a requisite_type property that could be extended to have an ALEKS type and various other types to support this kind of behavior.

2. I think this fits well into the point above. We could add a "grade" requisite type to handle these instances, though we would need to determine where and how to best access previously earned grades (especially considering we may need the user to input them manually, at least initially)

3. I believe this is already set up to be handled by the current Collection model.

4. Definitely something to look further into, though considering Coursebook seems to treat them distinctly I think that it may be best that we do as well.

5. I think a general system would be best here. Perhaps after some experimentation a "match" requisite type could be added that determines whether certain properties of courses meet certain filters?

Overall, what I've been thinking about while writing this all out is that the Collection schema really ought to be something like Requisite instead. I think the name is a lot clearer for what its intended purpose seems to be, and leans it further towards being a general representation of requisites rather than just a collection of misc items.

It also seems to me that it would be beneficial for us to make either an internal function or an API endpoint that takes in these "Collection" objects and then, based on their requisite_type, can determine whether the requisite is met or not. This code could be made heavily generic and would make our requisite framework very powerful albeit simple to use.

[CharlieMahana]

You brought up some really great points here, Jack.

Regarding point 1, I think it may be a good idea to create a separate schema for credit bearing examinations such as AP/IB, CLEP, ALEKS, and any others we could think of. These exams objects could perhaps reference the courses they provide credit for relative to the score achieved on the exam. For example, a score of 3 on AP US History yields credit for HIST 1301 whereas a 4 or a 5 yield credit for HIST 1301 and HIST 1302. From an OOP perspective, we may want to design all of the various exams as derived classes of a general parent exam class.

Regarding point 2, considering the grade required for a prerequisite to be valid is certainly something we should incorporate. I expect this to be a bit of a complex task as there are certain edge cases which make prerequisites yielded from credits awkward to handle. For example, the AP Physics C: E&M exams yield credit for PHYS 2326 and PHYS 2126 and enables a student to take PHYS 3416 but they only retain the credit for PHYS 2326 and PHYS 2126 if they pass PHYS 3416 with a C or higher.

Regarding point 4, I think that all major requirements and course equivalencies should be stored in the context of a degree. For example, certain courses are only equivalent if you're a certain major like CS where CE 2305 and CS 2305 are not equivalent classes.

Regarding point 5, I think that clarifying that a CS 43XX exists is the scope of our work for time being. Our job is to simply store the data in a useful format. All validation will be done on the client side. Keep these sort of issues in mind for later when we ultimately write some client libraries.

EDIT: I've included a link to the UTD AP credit page.

[jmyrick02]

Given the need to handle minimum grades for prerequisites, I have designed a solution as an addition to the collection schema. I came to this solution because it is relatively non-invasive (easily ignored by end-users of this API if they don't want to worry about it) but still relatively robust from how I'm looking at it right now at least.

I propose we add a field minimum_grade of type string to the Collection schema that specifies the minimum grade that must be satisfied for a course to fulfill a collection. If multiple different grades are required for different courses, we solve this problem with collection composition. An empty string in this field specifies no restriction.

I saw Charlie's comment on the unique PHYS 3416 pre-req grades situation, but that seems to be something that should be handled in the test/exam schema rather than the collections schema since that policy is only apparent on the AP credit page.

Please give your feedback on this idea as I'd like to hear other approaches or critiques.