db.sql

-- ===============================================
-- MySQL Syntax
-- ===============================================
SET GLOBAL time_zone = 'Asia/Muscat';

CREATE TABLE IF NOT EXISTS `EMPLOYEES` (
    `EMP_USERNAME` VARCHAR(255) NOT NULL,
    `EMP_PASSWORD` VARCHAR(500) NOT NULL,
    `EMP_NAME` VARCHAR(255) NOT NULL,
    `EMP_EMAIL` VARCHAR(255) NOT NULL,
    `EMP_DEPR` VARCHAR(255) NOT NULL,
    `EMP_CREATED_AT` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(`EMP_USERNAME`)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `EVENT_MST` (
    `MST_ID` INT NOT NULL AUTO_INCREMENT,
    `MST_TITLE` VARCHAR(255) NOT NULL,
    `MST_DESC` TEXT,
    `MST_FILE` VARCHAR(300),
    `MST_START` DATETIME NOT NULL,
    `MST_END` DATETIME NOT NULL,
    `MST_ALLDAY` TINYINT(1) NOT NULL DEFAULT 1,
    `MST_STATUS` TINYINT(1) NOT NULL DEFAULT 1,
    `MST_READ` TINYINT(1) NOT NULL DEFAULT 1,
    `MST_DELETE` TINYINT(1) NOT NULL DEFAULT 1,
    `MST_UPDATE` TINYINT(1) NOT NULL DEFAULT 1,
    `MST_CREATED_AT` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `EMP_USERNAME` VARCHAR(255) NOT NULL,
    PRIMARY KEY(`MST_ID`),
    CONSTRAINT `FK_MST_EMP_USERNAME` FOREIGN KEY(`EMP_USERNAME`) REFERENCES `EMPLOYEES`(`EMP_USERNAME`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

-- ===============================================
-- Old MySQL Syntax
-- ===============================================
-- CREATE TABLE IF NOT EXISTS `EMPLOYEES` (
--     `EMP_USERNAME` VARCHAR(255) NOT NULL,
--     `EMP_NAME` VARCHAR(255) NOT NULL,
--     `EMP_EMAIL` VARCHAR(255) NOT NULL,
--     `EMP_DEPR` VARCHAR(255) NOT NULL,
--     PRIMARY KEY(`EMP_USERNAME`)
-- ) ENGINE=INNODB;

-- CREATE TABLE IF NOT EXISTS `EVENT_CAT` (
--     `CAT_ID` INT NOT NULL AUTO_INCREMENT,
--     `CAT_NAME` VARCHAR(255) NOT NULL,
--     PRIMARY KEY(`CAT_ID`)
-- ) ENGINE=INNODB;

-- CREATE TABLE IF NOT EXISTS `EVENT_MST` (
--     `MST_ID` INT NOT NULL AUTO_INCREMENT,
--     `MST_TITLE` VARCHAR(255) NOT NULL,
--     `MST_DESC` TEXT,
--     `MST_FILE` VARCHAR(300),
--     `MST_START` DATETIME NOT NULL,
--     `MST_END` DATETIME NOT NULL,
--     `MST_ALLDAY` TINYINT(1) NOT NULL DEFAULT 1,
--     `MST_STATUS` TINYINT(1) NOT NULL DEFAULT 1,
--     `EMP_USERNAME` VARCHAR(255) NOT NULL,
--     `CAT_ID` INT NOT NULL,
--     PRIMARY KEY(`MST_ID`),
--     CONSTRAINT `FK_EMP_USERNAME` FOREIGN KEY(`EMP_USERNAME`) REFERENCES `EMPLOYEES`(`EMP_USERNAME`) ON UPDATE CASCADE ON DELETE CASCADE,
--     CONSTRAINT `FK_CAT_ID` FOREIGN KEY(`CAT_ID`) REFERENCES `EVENT_CAT`(`CAT_ID`) ON UPDATE CASCADE ON DELETE CASCADE
-- ) ENGINE=INNODB;

-- ===============================================================
-- SQL Server Syntax
-- ===============================================================
-- CREATE TABLE EMPLOYEES (
--     EMP_USERNAME VARCHAR(255) NOT NULL,
--     EMP_PASSWORD VARCHAR(255) NOT NULL,
--     EMP_NAME VARCHAR(255) NOT NULL,
--     EMP_EMAIL VARCHAR(255) NOT NULL,
--     EMP_DEPR VARCHAR(255) NOT NULL,
--     PRIMARY KEY(EMP_USERNAME)
-- );

-- CREATE TABLE EVENT_CAT (
--     CAT_ID INT NOT NULL IDENTITY(1, 1),
--     CAT_NAME VARCHAR(255) NOT NULL,
--     PRIMARY KEY(CAT_ID)
-- );

-- CREATE TABLE EVENT_MST (
--     MST_ID INT NOT NULL IDENTITY(1, 1),
--     MST_TITLE VARCHAR(255) NOT NULL,
--     MST_DESC TEXT,
--     MST_FILE VARCHAR(300),
--     MST_START DATETIME NOT NULL,
--     MST_END DATETIME NOT NULL,
--     MST_STATUS TINYINT NOT NULL DEFAULT 1,
--     EMP_USERNAME VARCHAR(255) NOT NULL,
--     CAT_ID INT NOT NULL,
--     PRIMARY KEY(MST_ID),
--     CONSTRAINT FK_EMP_USERNAME FOREIGN KEY(EMP_USERNAME) REFERENCES EMPLOYEES(EMP_USERNAME) ON UPDATE CASCADE ON DELETE CASCADE,
--     CONSTRAINT FK_CAT_ID FOREIGN KEY(CAT_ID) REFERENCES EVENT_CAT(CAT_ID) ON UPDATE CASCADE ON DELETE CASCADE
-- );