Degree Scheduler

I’ve been tasked with creating an online degree scheduler interface for the Grand Lodge of WI. Here’s the working DB Schema DDL that I just ran in the development environment:

-- DATABASE CREATION SCRIPT FOR DEGREE SCHEDULER v0.1 --
-- UNCLAS --

CREATE TABLE BROTHER (
        ID              INT             UNSIGNED        NOT NULL        AUTO_INCREMENT,
        FIRSTNAME       VARCHAR(31),
        LASTNAME        VARCHAR(31),
        PHONE           VARCHAR(14),
        EMAIL           VARCHAR(255),
        HOMELODGE       VARCHAR(31),
        NOTES           TEXT,
        PRIMARY KEY                     (ID),
                KEY IX_BROTHER_NAME     (LASTNAME, FIRSTNAME)
) ENGINE=InnoDB;

CREATE TABLE DEGREE (
        ID              INT             UNSIGNED        NOT NULL,
        NAME            VARCHAR(18)                     NOT NULL,
        SYMBOL          CHAR(2)                         NOT NULL,
        NOTES           TEXT,
        PRIMARY KEY                     (ID),
        UNIQUE  KEY UX_DEGREE_NAME      (NAME),
        UNIQUE  KEY UX_DEGREE_SYMBOL    (SYMBOL)
) ENGINE=InnoDB;

CREATE TABLE SECTION (
        ID              INT             UNSIGNED        NOT NULL        AUTO_INCREMENT,
        `INDEX`         TINYINT         UNSIGNED        NOT NULL,
        NAME            VARCHAR(31),
        DEGREE          INT             UNSIGNED        NOT NULL,
        NOTES           TEXT,
        PRIMARY KEY                             (ID),
        UNIQUE  KEY UX_SECTION_INDEXDEGREE      (`INDEX`, DEGREE),
        UNIQUE  KEY UX_SECTION_NAMEDEGREE       (NAME, DEGREE),
        FOREIGN KEY FK_SECTION_DEGREE           (DEGREE)
                REFERENCES DEGREE               (ID)
) ENGINE=InnoDB;

CREATE TABLE PART (
        ID              INT             UNSIGNED        NOT NULL        AUTO_INCREMENT,
        NAME            VARCHAR(31)                     NOT NULL,
        SYMBOL          CHAR(2),
        SECTION         INT             UNSIGNED        NOT NULL,
        DIFFICULTY      TINYINT         UNSIGNED        NOT NULL,
        NOTES           TEXT,
        PRIMARY KEY                             (ID),
        UNIQUE  KEY UX_PART_NAMESECTION         (NAME, SECTION),
        UNIQUE  KEY UX_PART_SYMBOLSECTION       (SYMBOL, SECTION),
        FOREIGN KEY FK_PART_SECTION             (SECTION)
                REFERENCES SECTION              (ID)
) ENGINE=InnoDB;

CREATE TABLE EVENT (
        ID              INT             UNSIGNED        NOT NULL        AUTO_INCREMENT,
        `DATE`          DATE                            NOT NULL,
        STARTTIME       TIME,
        LOCATION        VARCHAR(63),
        CANDIDATE       VARCHAR(63),
        DEGREE          INT             UNSIGNED        NOT NULL,
        NOTES           TEXT,
        PRIMARY KEY                     (ID),
                KEY IX_EVENT_DATE       (DATE),
        FOREIGN KEY FK_EVENT_DEGREE     (DEGREE)
                REFERENCES DEGREE       (ID)
) ENGINE=InnoDB;

CREATE TABLE ASSIGNMENT (
        ID              INT             UNSIGNED        NOT NULL        AUTO_INCREMENT,
        EVENT           INT             UNSIGNED        NOT NULL,
        PART            INT             UNSIGNED        NOT NULL,
        BROTHER         INT             UNSIGNED        NOT NULL,
        NOTES           TEXT,
        PRIMARY KEY                             (ID),
        UNIQUE  KEY UX_ASSIGNMENT_EPB           (EVENT, PART, BROTHER),
        FOREIGN KEY FK_ASSIGNMENT_EVENT         (EVENT)
                REFERENCES EVENT                (ID),
        FOREIGN KEY FK_ASSIGNMENT_PART          (PART)
                REFERENCES PART                 (ID),
        FOREIGN KEY FK_ASSIGNMENT_BROTHER       (BROTHER)
                REFERENCES BROTHER              (ID)
) ENGINE=InnoDB;

CREATE TABLE CAPABILITY (
        ID              INT             UNSIGNED        NOT NULL        AUTO_INCREMENT,
        PART            INT             UNSIGNED        NOT NULL,
        BROTHER         INT             UNSIGNED        NOT NULL,
        NOTES           TEXT,
        PRIMARY KEY                             (ID),
        UNIQUE  KEY UX_CAPABILITY_PART_BROTHER  (PART, BROTHER),
        FOREIGN KEY FK_CAPABILITY_PART          (PART)
                REFERENCES PART                 (ID),
        FOREIGN KEY FK_CAPABILITY_BROTHER       (BROTHER)
                REFERENCES BROTHER              (ID)
) ENGINE=InnoDB;

-- UNCLAS --

Here’s the unclassified version of the base data insert code for Blue Lodges:

-- BASE DATA (BLUE LODGE) FOR DEGREE SCHEDULER v0.1 --
-- -CONFIDENTIAL- UNCLAS//GL-WI//RIT//-ORCON- --

INSERT INTO DEGREE      (ID,    NAME,                   SYMBOL)
VALUES                  (1,     "Entered Apprentice",   "EA"),
                        (2,     "Fellowcraft",          "FC"),
                        (3,     "Master Mason",         "MM")
;

INSERT INTO SECTION     (`INDEX`,       NAME,                   DEGREE)
VALUES                  (1,             "First Section",        1),
                        (2,             "Second Section",       1),
                        (3,             "Third Section",        1),
/* -UBS-RIT- */         U REDACTED
                        (1,             "First Section",        2),
                        (2,             "Second Section",       2),
/* -UBS-RIT- */         U REDACTED
                        (1,             "First Section",        3),
                        (2,             "Second Section",       3),
/* -UBS-RIT- */         U REDACTED
                        (4,             "Third Section",        3),
/* -UBS-RIT- */         U REDACTED
/* -UBS-RIT- */         U REDACTED
;

INSERT INTO PART        (NAME,                  SYMBOL, SECTION,        DIFFICULTY)
-- NAME IS -C-RIT- UNCLAS, SYMBOL IS -UBS-RIT- UNCLAS --
VALUES                  (REDACTED,              REDACTED,   1,              8),
                        (REDACTED,              REDACTED,   1,              2),
                        (REDACTED,              REDACTED,   1,              6),
                        (REDACTED,              REDACTED,   1,              5),
                        (REDACTED,              REDACTED,   1,              2),
                        (REDACTED,              REDACTED,   1,              4),
                        (REDACTED,              REDACTED,   1,              5),
                        (REDACTED,              REDACTED,   1,              1),
                        (REDACTED,              REDACTED,   1,              1),
                        (REDACTED,              REDACTED,   2,              9),
                        (REDACTED,              REDACTED,   3,              9),
                        (REDACTED,              REDACTED,   4,              6),
                        (REDACTED,              REDACTED,   5,              8),
                        (REDACTED,              REDACTED,   5,              2),
                        (REDACTED,              REDACTED,   5,              4),
                        (REDACTED,              REDACTED,   5,              6),
                        (REDACTED,              REDACTED,   5,              2),
                        (REDACTED,              REDACTED,   5,              4),
                        (REDACTED,              REDACTED,   5,              5),
                        (REDACTED,              REDACTED,   5,              1),
                        (REDACTED,              REDACTED,   5,              1),
                        (REDACTED,              REDACTED,   6,              8),
                        (REDACTED,              REDACTED,   6,              10),
                        (REDACTED,              REDACTED,   6,              4),
                        (REDACTED,              REDACTED,   6,              2),
                        (REDACTED,              REDACTED,   6,              1),
                        (REDACTED,              REDACTED,   7,              6),
                        (REDACTED,              REDACTED,   8,              8),
                        (REDACTED,              REDACTED,   8,              2),
                        (REDACTED,              REDACTED,   8,              5),
                        (REDACTED,              REDACTED,   8,              1),
                        (REDACTED,              REDACTED,   8,              1),
                        (REDACTED,              REDACTED,   8,              6),
                        (REDACTED,              REDACTED,   8,              3),
                        (REDACTED,              REDACTED,   8,              4),
                        (REDACTED,              REDACTED,   8,              5),
                        (REDACTED,              REDACTED,   9,              8),
                        (REDACTED,              REDACTED,   9,              5),
                        (REDACTED,              REDACTED,   9,              7),
                        (REDACTED,              REDACTED,   9,              9),
                        (REDACTED,              REDACTED,   9,              9),
                        (REDACTED,              REDACTED,   9,              10),
                        (REDACTED,              REDACTED,   9,              5),
                        (REDACTED,              REDACTED,   9,              8),
                        (REDACTED,              REDACTED,   9,              7),
                        (REDACTED,              REDACTED,   9,              3),
                        (REDACTED,              REDACTED,   9,              9),
                        (REDACTED,              REDACTED,   9,              7),
                        (REDACTED,              REDACTED,   9,              4),
                        (REDACTED,              REDACTED,   10,             9),
                        (REDACTED,              REDACTED,   11,             8),
                        (REDACTED,              REDACTED,   12,             6),
                        (REDACTED,              REDACTED,   12,             1),
                        (REDACTED,              REDACTED,   13,             9)
);

-- -END C-RIT- --
-- -CONFIDENTIAL- UNCLAS//GL-WI//RIT//-ORCON- --
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: