Subject Area as a Table

Here is the courses table again:

Look at the subject area column. Every row says “Computer science.” Nothing so far has forced me to split that off. It is not like a professor’s email, which a requirement says an administrator can update. It is not like an offering’s room, which changes from one instance of the course to the next. As far as I know right now, the subject area of a course does not change.

But suppose a new course arrives from a different department, and the administrator entering it types “Comp Sci” instead of “Computer science.” The database does not catch that mismatch. Now if you ask “how many courses are in Computer science,” you have to hope every administrator typed the subject area the same way in every row, the same problem I ran into with degree requirement names in the last section.

So subject area gets its own table, with the same invented ID I gave professors and requirements:

Now the courses table points at that table by ID instead of repeating the text:

Subject area is now a foreign key, so the database can check it against the subject areas table to enforce referential integrity.