Database schema

Database Schema

Below is our schema for the Stanford MySQL database, with some little annotations added by me.

Currently, you guys have read/write access on every table on the Stanford MySQL server table. When I enable remote access to the cs198 server's database (which has the same schema), you have read access to everything, and write access to only AssignmentFiles and AssignmentComments. So I would recommend writing only to these 2 tables in PHP, but you can mess with the data on the Stanford MySQL database all you want--it's just a copy we can play with. Use the mysql console to mess with the data to create edge cases, such as the following:

• reassign Eric's student's FacePamphlet submission to be graded by Jeremy and make sure it shows up for Jeremy

• have Jeremy teach 2 sections of CS106A

• have Jeremy teach a section of 106A and a section of 106B (this already happens with 106L which stores its section/grade data on CS198, even though L doesn't have a similar submission system yet)

Annotations:

* means a foreign key, meaning it refers to a row in another table using the ID field (it's like a pointer!)

@ means string, otherwise the column is an integer

# means the field might be null, so don't depend on it being present

All ID fields are primary keys, non-null, and auto-increment on insert.

STUFF THAT RARELY CHANGES
State(DefaultQuarter*)
Quarters(ID, Quarter, Year)
Courses(ID, Name@)
--
PEOPLE/SECTION-RELATED TABLES
People(ID, SUNetID@, FirstName@, LastName@, DisplayName@, Email@, NickName@#)
CourseRelations(ID, Person*, Quarter*, Class*, Position*)
Sections(ID, Quarter*, Class*, SectionLeader*#)
SectionAssignments(ID, Person*, Quarter*, Section*, Class*)
--
ASSIGNMENT-RELATED TABLES
Assignments(ID, Title@, Class*)
Criteria(ID, Class*, Quarter*, Assignment*, AssignmentNumber#)
GradedAssignments(ID, Student*, Grader*, Criteria*, Quarter*)
--
NEW TABLES FOR PAPERLESS
AssignmentFiles(ID, GradedAssignment*, File@)
AssignmentComments(ID, AssignmentFile*, StartLine, EndLine, CommentText@)