Database: University Database Example

A common example in most database books is a university database. This is used because students understand the business rules a university uses (a student may not be enrolled more than once simultaneously in the same section, a course may have many sections, ...).

Relational Schema

univeristy schema

Tables

TablePurpose
Student Describes each student. Not all students may be currently enrolled.
EnrollmentBridge table for M:N Student-Section enrollments for the current term. Grade recorded here.
Section The particular instance of a course being offered at a specific time, place, etc.
Course The abstract course. Each crs_code is like IFSM410. Corresponds to catalog entry.
Location The loc_code has values like RAMST. Country codes are 2 char internet suffixes (eg, 'de').
InstructorEach instructor. Not all instructors may currently be teaching a Section. Each instructor reports to one department.
Qualified Bridge table specifying which courses an instructor is qualified to teach.
DepartmentAn administrative unit of the University.
PrerequisiteDefines which courses are prerequisites to other courses.

SQL to create the tables

The following SQL can be used to define the tables. The order of some table definitions may be important so that the REFERENCES constraint only references previously defined tables. Comments that start with "--position" may be ignored as these are produced by the schema drawing program. 

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 

CREATE TABLE Student ( --position 15 87,
    stu_id    char(9)   PRIMARY KEY,
    stu_fname char(20)  NOT NULL,
    stu_lname char(20)  NOT NULL
);

CREATE TABLE Department ( --position 281 371,
    dep_code char(4)  PRIMARY KEY,
    dep_name char(40) NOT NULL UNIQUE
);

CREATE TABLE Instructor ( --position 108 267,
    ins_id    char(9)  PRIMARY KEY,
    ins_fname char(20) NOT NULL,
    ins_lname char(20) NOT NULL,
    dep_code  char(4)  NOT NULL REFERENCES Department(dep_code)
);

CREATE TABLE Location ( --position 128 153,
    loc_code    char(5)  PRIMARY KEY,
    loc_name    char(40) NOT NULL,
    loc_country char(2)  NOT NULL
);

CREATE TABLE Course ( --position 498 159,
    crs_code        char(10)     PRIMARY KEY,
    crs_title       varchar(100) NOT NULL,
    crs_credits     tinyint      NOT NULL,
    dep_code        char(4)      NOT NULL REFERENCES Department(dep_code),
    crs_description varchar(255) NOT NULL
);

CREATE TABLE Section ( --position 294 110,
    sec_id   int      PRIMARY KEY,
    sec_term char(8)  NOT NULL,
    sec_bldg char(6),
    sec_room char(4),
    sec_time char(10),
    crs_code char(10) NOT NULL REFERENCES Course(crs_code),
    loc_code char(5)  NOT NULL REFERENCES Location(loc_code),
    ins_id   char(9)  REFERENCES Instructor(ins_id)
);

CREATE TABLE Enrollment ( --position 151 36,
    stu_id     char(9)  REFERENCES Student(stu_id),
    sec_id     int      REFERENCES Section(sec_id),
    grade_code char(2),
    PRIMARY KEY (stu_id, sec_id)
);

CREATE TABLE Prerequisite ( --position 283 21,
    crs_code     char(10) REFERENCES Course(crs_code),
    crs_requires char(10) REFERENCES Course(crs_code),
    PRIMARY KEY (crs_code, crs_requires)
);

CREATE TABLE Qualified ( --position 283 303,
    ins_id   char(9)  REFERENCES Instructor(ins_id),
    crs_code char(10) REFERENCES Course(crs_code),
    PRIMARY KEY (ins_id, crs_code)
);