Code review database design for storing college assessments & enrolments
Just a few thoughts.   

I would not use ENUMs. I hate to say "always" (open to correction on this) but I would "always" use a FOREIGN KEY. Consider gender - 25 years ago, you could have had an ENUM ('M', 'F') - now you can have:  


Unknown 
Intersex 
Non-binary 
Decline-to-answer 
Don't know 
Don't care :-)


&amp;c., &amp;c...

With a FOREIGN KEY things are far easier to change into the future. Furthermore, you make the point that you want your database to be well-structured, extensible, and "intuitive" for future maintainers(*) - FOREIGN KEYs will do this by making your system portable to another RDBMS! After an FK, I'd go for a CHECK CONSTRAINT!

(*) upvoting for this alone! If only all system designers were so thoughtful - some of my  sleepless nights as a programmer might have been avoided! :-)

Check out this thread on SO (both question and accepted answer from the same poster). It tries to go through all options (but misses one - see below!) and the "consensus" (if such a thing can be said to exist on these sites) is, FKs, followed by CCs. Check out this - there's a reason that poster has a rep > 300,000 (not to mention a shedload here too)!

Gotchas. MySQL (amazingly!) doesn't support CKs! Plus, another factor mitigating against CCs is that, frequently, DBAs and/or sysadmins are the only operators allowed to change system tables (which is what modifying CCs involves) whereas ordinary programmers/users can change ordinary tables. 

A corollary to this is that if you want tight control over disk activity, you might want the smallest possible amount of it on your system tables - every operation involving a CC involves querying them, so the overhead of querying the FK tables is not such a big deal after all.

A factor in favour of CCs over ENUMs is that you can use the LIKE and other operators on them - you can't with the latter. Finally, take a look at the horrible syntax here to compare values in ENUMs to other values (remember they're different types!). And if you're doing lookups of values for, say, dropdowns, you'd have to use something like this:

select n.nspname as enum_schema,  
       t.typname as enum_name,  
       e.enumlabel as enum_value
from pg_type t 
   join pg_enum e on t.oid = e.enumtypid  
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace


as opposed to:

select my_field from my_table;


All in all, I would strongly urge you to use FKs or CCs to enforce Declarative Referential Integrity (DRI) on your tables.

There are (mentioned only for completeness) ways of restricting values in tables - you could use DOMAINs or TRIGGERs. The former seem superior to ENUMs, whereas TRIGGERs (IMHO) should only be used for complex business logic!

Finally, to answer the question (phew...).

Your schema seems fine - it's pretty difficult to judge these things by eye. What I would do if I were you, is to start using/developing against it and see if you come up against problems. Get back to us here with specific issues if you do - your question is a bit broad! p.s. welcome to the forum!