-- uw.sql
-- DDL for UW-wide useful information
-- ogsas-dev@cscf.cs.uaterloo.ca

-- Data types that are useful everywhere
CREATE DOMAIN SIN_TYPE AS CHAR(9)
  CHECK (VALUE ~ E'^\\d{9}$' or VALUE = '');

-- Data types that are useful everywhere at UW
CREATE DOMAIN GROUP_TYPE AS VARCHAR;
CREATE DOMAIN DEPT_TYPE AS VARCHAR;
CREATE DOMAIN PLAN_TYPE AS VARCHAR;
CREATE DOMAIN TERM_TYPE AS CHAR(4)
  CHECK (VALUE ~ E'^\\d{3}[159]$');
CREATE DOMAIN UWUSERID_TYPE AS VARCHAR(8)
  CHECK (VALUE ~ '^[-_[:alnum:]]{1,8}$');
CREATE DOMAIN GENDER_TYPE AS CHAR(1)
  CHECK (VALUE IN ('M', 'F'));
CREATE DOMAIN UWID_TYPE AS CHAR(8)
  CHECK (VALUE ~ E'^\\d{8}$');

CREATE TABLE uw_term (
  term                  TERM_TYPE,
  PRIMARY KEY (term),
  
  lectureStart          DATE,
  lectureEnd            DATE,
  examsStart            DATE,
  examsEnd              DATE
);

-- "Group" is the official database name for faculties.
-- The differences takes into account the existence of entries like
-- VPA which is used for inter-faculty programs.
CREATE TABLE uw_group (
  groupCode             GROUP_TYPE,
  PRIMARY KEY (groupCode),
  
  description           VARCHAR UNIQUE
);
