Structured Querying Language

Everything so far has been design, not implementation. When it comes time to implement a relational database, SQL is the language you use. SQL, short for Structured Query Language, is expressive enough to state the design decisions directly. So here is the same schema again, this time as code the database can run. Like the ERD in the last section, you are not expected to know SQL for this course. It is included here for completeness:

CREATE TABLE subject_areas (
  subject_area_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE courses (
  course_number TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  credits INTEGER NOT NULL,
  subject_area_id INTEGER NOT NULL,
  FOREIGN KEY (subject_area_id) REFERENCES subject_areas(subject_area_id)
);

CREATE TABLE professors (
  professor_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE offerings (
  offering_id INTEGER PRIMARY KEY,
  course_number TEXT NOT NULL,
  professor_id INTEGER NOT NULL,
  term TEXT NOT NULL,
  meeting_time TEXT NOT NULL,
  room TEXT NOT NULL,
  FOREIGN KEY (course_number) REFERENCES courses(course_number),
  FOREIGN KEY (professor_id) REFERENCES professors(professor_id)
);

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE requirements (
  requirement_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE course_requirements (
  course_number TEXT,
  requirement_id INTEGER,
  PRIMARY KEY (course_number, requirement_id),
  FOREIGN KEY (course_number) REFERENCES courses(course_number),
  FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id)
);

CREATE TABLE history_entries (
  student_id INTEGER,
  offering_id INTEGER,
  PRIMARY KEY (student_id, offering_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (offering_id) REFERENCES offerings(offering_id)
);

Every keyword here is something you already know by a different name. PRIMARY KEY marks the column, or pair of columns, that uniquely identifies a row. FOREIGN KEY ... REFERENCES is the line the ERD drew between two boxes. NOT NULL is the constraint that refuses a row missing that value. UNIQUE is the constraint on the professor’s email, separate from the primary key.

Look at course_requirements and history_entries. Their primary key is not attached to a single column the way subject_area_id INTEGER PRIMARY KEY is. It sits on its own line, PRIMARY KEY (course_number, requirement_id), naming the pair. That is the syntax for a composite primary key, and it is the same composite key from the sections on junction tables, written in the language the database actually reads.

None of this is a new decision. It is the same schema, stated a third way.