DROP TABLE ENROLLMENT;
DROP TABLE STUDENT;
DROP TABLE CLASS;
DROP TABLE DEPARTMENT;

CREATE TABLE DEPARTMENT (
    Dep_no  SERIAL          CONSTRAINT PK_DEPARTMENT PRIMARY KEY,
    Name    VARCHAR(100)    NOT NULL UNIQUE
);

CREATE TABLE CLASS (
    Classcode   VARCHAR(5)      CONSTRAINT PK_CLASS     PRIMARY KEY,
    Dep_no      INTEGER         CONSTRAINT REF_DEP_NO   REFERENCES DEPARTMENT(Dep_no) NOT NULL,
    Title       VARCHAR(100)    NOT NULL,
    Max_Number  NUMERIC         NOT NULL
);

CREATE TABLE STUDENT (
    Reg_no      SERIAL          CONSTRAINT PK_STUDENT PRIMARY KEY,
    Firstname   varchar(100)    NOT NULL,
    Lastname    varchar(100)    NOT NULL,
    DOB         DATE            NOT NULL,
    Dep_no      INTEGER         CONSTRAINT REF_DEP_NO   REFERENCES DEPARTMENT(Dep_no) NOT NULL
);

CREATE TABLE ENROLLMENT (
    Enrollment_id   SERIAL      CONSTRAINT PK_ENROLLMENT    PRIMARY KEY,
    Reg_no          INTEGER     CONSTRAINT REF_REG_NO       REFERENCES STUDENT(Reg_no) NOT NULL,
    Classcode       VARCHAR(5)  CONSTRAINT REF_CLASS_CODE   REFERENCES CLASS(Classcode) NOT NULL
);
ALTER TABLE ENROLLMENT ADD CONSTRAINT Enroll_Max_Once UNIQUE (Reg_no, Classcode);