This sample application uses four tables to track all university activity. The DEPT table is used to track department offerings within the university. The CLASS table is used to track class offerings within each department. The STUDENT table is used to track each student within the university. The GRADE table is used to track the enrollments of each student within each class.
In this sample application, social security number (ssn) is used as the primary key for the STUDENT table. The DEPT table uses department code (dept) as the primary key, and course code (ccode) is used as the primary key for the CLASS table. The combination of social security number (ssn) and course code (ccode) makes up the primary key for the GRADE table.
The column major is defined as a foreign key in the STUDENT table. When selecting a major, the student must choose a valid department code (dept) from the DEPT table. The department (dept) column in the CLASS table is also defined as a foreign key. When a course is inserted into this table, it must be associated with a valid department (dept) from the DEPT table.
The GRADE table has two foreign keys. When enrolling a student in a class, the social security number (ssn) must exist in the STUDENT table, and the course code (ccode) must exist in the CLASS table. This ensures that students are not enrolled in nonexistent classes, and that classes are not filled with nonexistent students.