Skip to content

SeedingMariaDB.md

Latest commit

 

History

History
311 lines (220 loc) · 11.8 KB

SeedingMariaDB.md

File metadata and controls

311 lines (220 loc) · 11.8 KB
Raw

Seeding MariaDB in IntelliJ

Using the IntelliJ DB Console

Slide 1

1. Recreate application user and Charity DB

Create 1-CREATE-CHARITY-DB.SQL Script

        DROP DATABASE IF EXISTS charityDB;
        CREATE DATABASE charityDB;
        SHOW DATABASES;

Create 1-A-CREATE-APP-USER.SQL Script

        DROP USER springjdbcapp@localhost;
        CREATE USER springjdbcapp@localhost IDENTIFIED BY 'password1';
        SELECT user FROM mysql.user;
        GRANT ALL PRIVILEGES ON charitydb.* TO springjdbcapp@localhost;
        FLUSH PRIVILEGES;
        SHOW GRANTS FOR springjdbcapp@localhost;

2. Create Charity Table

Create 1-B-CREATE-CHARITY-TABLE.SQL Script

        ALTER TABLE charitydb.CHARITY DROP INDEX IF EXISTS `PK_CHARITY`;

        DELETE FROM charitydb.CHARITY;
        
        DROP TABLE IF EXISTS charitydb.CHARITY;
        
        CREATE TABLE charitydb.CHARITY
        (
        `CHARITY_ID`               MEDIUMINT    NOT NULL AUTO_INCREMENT,
        `CHARITY_TAX_ID`           VARCHAR(250) NOT NULL UNIQUE , -- https://tax.co.za/what-is-my-tax-number/
        `CHARITY_NAME`             VARCHAR(250),
        `CHARITY_MISSION`          VARCHAR(250),
        `CHARITY_WEB_ADDRESS`      VARCHAR(250),          -- Need a separate ADDRESS Table
        `CHARITY_FACEBOOK_ADDRESS` VARCHAR(250),          -- Need a separate ADDRESS Table
        `CHARITY_TWITTER_ADDRESS`  VARCHAR(250),          -- Need a separate ADDRESS Table
        PRIMARY KEY (`CHARITY_ID`)
        ) ENGINE = InnoDB;
        
        CREATE UNIQUE INDEX `PK_CHARITY` ON charitydb.CHARITY (`CHARITY_ID` ASC);

3. Create Charity Category Table and Relationship Tables

Create 2-CREATE-CATEGOTY-TABLE.SQL Script

    ALTER TABLE charitydb.`CATEGORY` DROP INDEX IF EXISTS `IDX_CATEGORY`;

    ALTER TABLE CATEGORY DROP FOREIGN KEY IF EXISTS `FK_CHARITY_CATEGORY_ID`;
    
    DROP TABLE IF EXISTS charitydb.`CATEGORY`;
    
    CREATE TABLE charitydb.`CATEGORY`
    (
    `CATEGORY_ID`   MEDIUMINT NOT NULL AUTO_INCREMENT,
    `CATEGORY_NAME` VARCHAR(50),
    CONSTRAINT `CATEGORY_PK` PRIMARY KEY (`CATEGORY_ID`)
    ) ENGINE = InnoDB;

    CREATE UNIQUE INDEX `IDX_CATEGORY` ON charitydb.`CATEGORY` (`CATEGORY_ID` ASC);
    
    ALTER TABLE `CATEGORY`
    ADD CONSTRAINT `FK_CHARITY_CATEGORY_ID` FOREIGN KEY (`CATEGORY_ID`)
    REFERENCES `charity_category` (`CATEGORY_ID`) #
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

CREATE 2-A-CHARITY-CATEGORY-RELATIONSHIP.SQL Script

    ALTER TABLE CHARITY_CATEGORY DROP FOREIGN KEY IF EXISTS `FK_CATEGORY_ID`;

    ALTER TABLE CHARITY_CATEGORY DROP FOREIGN KEY IF EXISTS `FK_CHARITY_ID`;
    
    DROP TABLE IF EXISTS charitydb.`CHARITY_CATEGORY`;
    
    CREATE TABLE charitydb.`CHARITY_CATEGORY`
    (
    `CHARITY_ID`  MEDIUMINT NOT NULL,
    `CATEGORY_ID` MEDIUMINT NOT NULL
    ) ENGINE = InnoDB;
    
    ALTER TABLE `CHARITY_CATEGORY`
    ADD CONSTRAINT `FK_CHARITY_ID`
    FOREIGN KEY (CHARITY_ID) REFERENCES charity(CHARITY_ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;
    
    ALTER TABLE `CHARITY_CATEGORY`
    ADD CONSTRAINT `FK_CATEGORY_ID`
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

Populate Categories with scripts

2-A-B-INSERT-CATEGORY-ROWS.SQL Script

    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('ANIMALS');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('ARTS,CULTURE,HUMANITIES');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('COMMUNITY DEVELOPMENT');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('EDUCATION');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('ENVIRONMENT');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('HEALTH');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('HUMAN SERVICES');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('HUMAN AND CIVIL RIGHTS');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('RELIGION');
    INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('RESEARCH AND PUBLIC POLICY');

4. Create Charity Program Table and Relationship Tables

CREATE 3-CREATE-PROGRAM.SQL Script

    ALTER TABLE charitydb.`PROGRAM` DROP INDEX IF EXISTS `IDX_PROGRAM`;

    ALTER TABLE PROGRAM DROP FOREIGN KEY IF EXISTS `FK_CHARITY_PROGRAM_ID`;
    
    DROP TABLE IF EXISTS charitydb.`PROGRAM`;
    
    CREATE TABLE charitydb.`PROGRAM`
    (
    `PROGRAM_ID`   MEDIUMINT NOT NULL AUTO_INCREMENT,
    `PROGRAM_DESCRIPTION` VARCHAR(250),
    CONSTRAINT `PROGRAM_PK` PRIMARY KEY (`PROGRAM_ID`)
    ) ENGINE = InnoDB;;
    
    CREATE UNIQUE INDEX `IDX_PROGRAM` ON charitydb.`PROGRAM` (`PROGRAM_ID` ASC);
    
    ALTER TABLE `PROGRAM`
    ADD CONSTRAINT `FK_CHARITY_PROGRAM_ID` FOREIGN KEY (`PROGRAM_ID`)
    REFERENCES `charity_program` (`PROGRAM_ID`) #
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

Populate PROGRAM table with scripts

CREATE 3-A-INSERT-PROGRAMS.SQL Script

    INSERT INTO PROGRAM (PROGRAM_DESCRIPTION) VALUES ('Residential facility');
    INSERT INTO PROGRAM (PROGRAM_DESCRIPTION) VALUES ('Social Work offices for psycho-social services');
    INSERT INTO PROGRAM (PROGRAM_DESCRIPTION) VALUES ('Protective workshops');
    INSERT INTO PROGRAM (PROGRAM_DESCRIPTION) VALUES ('Home based care');
    INSERT INTO PROGRAM (PROGRAM_DESCRIPTION) VALUES ('Pre school');
    INSERT INTO PROGRAM (PROGRAM_DESCRIPTION) VALUES ('Orphanage');

CREATE 3-B-CREATE-CHARITY-PROGRAMS.SQL Script

    ALTER TABLE CHARITY_PROGRAM DROP FOREIGN KEY IF EXISTS `FK_CHARITY_PROGRAM_ID`;
    ALTER TABLE CHARITY_PROGRAM DROP FOREIGN KEY IF EXISTS `FK_PROGRAM_ID`;
    
    DROP TABLE IF EXISTS charitydb.`CHARITY_PROGRAM`;
    
    CREATE TABLE charitydb.`CHARITY_PROGRAM`
    (
    `CHARITY_ID`  MEDIUMINT NOT NULL,
    `PROGRAM_ID` MEDIUMINT NOT NULL
    );
    
    ALTER TABLE `CHARITY_PROGRAM`
    ADD CONSTRAINT `FK_CHARITY_PROGRAM_ID`
    FOREIGN KEY (CHARITY_ID) REFERENCES charity(CHARITY_ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;
    
    ALTER TABLE `CHARITY_PROGRAM`
    ADD CONSTRAINT `FK_PROGRAM_ID`
    FOREIGN KEY (PROGRAM_ID) REFERENCES program(PROGRAM_ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

5. Create Charity Associate Table and Relationship tables

CREATE 4-CREATE-CHARITY-ASSOCIATE-TABLE.SQL Script

    ALTER TABLE CHARITY_ASSOCIATE DROP INDEX IF EXISTS CHARITY_ASSOCIATE_PK;
    
    DROP TABLE IF EXISTS CHARITY_ASSOCIATE;
    
    CREATE TABLE CHARITY_ASSOCIATE
    (
    CHARITY_ASSOCIATE_ID      MEDIUMINT   NOT NULL AUTO_INCREMENT,
    CHARITY_ID                MEDIUMINT, -- FOREIGN KEY
    CHARITY_ASSOCIATE_TYPE    MEDIUMINT,
    CHARITY_ASSOCIATE_NAME    VARCHAR(80) NOT NULL,
    CHARITY_ASSOCIATE_SURNAME VARCHAR(80) NOT NULL,
    CHARITY_ASSOCIATE_DOB     DATE        NOT NULL,
    PRIMARY KEY (CHARITY_ASSOCIATE_ID)
    );
    
    CREATE UNIQUE INDEX CHARITY_ASSOCIATE_PK ON CHARITY_ASSOCIATE (CHARITY_ASSOCIATE_ID);
    
    ALTER TABLE `CHARITY_ASSOCIATE`
    ADD CONSTRAINT `FK_CHARITY_ID_ASSOCIATE` FOREIGN KEY (`CHARITY_ID`)
    REFERENCES `CHARITY` (`CHARITY_ID`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

Slide 2

1. Create Charity Associate Address Table

CREATE 4-A-CREATE-CHARITY-ASSOCIATE-ADDRESS.SQL Script

    ALTER TABLE `CHARITY_ASSOCIATE_ADDRESS` DROP FOREIGN KEY IF EXISTS `FK_CHARITY_ASSOCIATE_ADDRESS`;
    
    DROP TABLE IF EXISTS `CHARITY_ASSOCIATE_ADDRESS`;
    
    CREATE TABLE `CHARITY_ASSOCIATE_ADDRESS`
    (
    `CHARITY_ASSOCIATE_ADDRESS_ID`                           MEDIUMINT NOT NULL AUTO_INCREMENT,
    `CHARITY_ASSOCIATE_ID`                                   MEDIUMINT NOT NULL,
    `CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_LINE1`      VARCHAR(60),
    `CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_LINE2`      VARCHAR(60),
    `CHARITY_ASSOCIATE_CITY`                                 VARCHAR(60),
    `CHARITY_ASSOCIATE_POSTAL_CODE`                          VARCHAR(10),
    `CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_START_DATE` DATE,
    `CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_END_DATE`   DATE,
    PRIMARY KEY (`CHARITY_ASSOCIATE_ADDRESS_ID`)
    );
    
    CREATE UNIQUE INDEX CHARITY_ASSOCIATE_ADDRESS_PK ON CHARITY_ASSOCIATE_ADDRESS (CHARITY_ASSOCIATE_ADDRESS_ID);
    
    ALTER TABLE `CHARITY_ASSOCIATE_ADDRESS`
    ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_ADDRESS` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
    REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

2. Create Charity Associate Email Table

CREATE 4-B-CREATE-CHARITY-ASSOCIATE-EMAIL.SQL Script

ALTER TABLE CHARITY_ASSOCIATE_EMAIL DROP INDEX IF EXISTS CHARITY_ASSOCIATE_EMAIL_PK;

DROP TABLE IF EXISTS CHARITY_ASSOCIATE_EMAIL;

CREATE TABLE CHARITY_ASSOCIATE_EMAIL
(
CHARITY_ASSOCIATE_EMAIL_ID       MEDIUMINT    NOT NULL AUTO_INCREMENT,
CHARITY_ASSOCIATE_ID             MEDIUMINT, -- FOREIGN KEY
CHARITY_ASSOCIATE_EMAIL_PRIORITY MEDIUMINT,
CHARITY_ASSOCIATE_EMAIL_EMAIL    VARCHAR(250) NOT NULL,
PRIMARY KEY (CHARITY_ASSOCIATE_EMAIL_ID)
);

CREATE UNIQUE INDEX CHARITY_ASSOCIATE_EMAIL_PK ON CHARITY_ASSOCIATE_EMAIL (CHARITY_ASSOCIATE_EMAIL_ID);

ALTER TABLE `CHARITY_ASSOCIATE_EMAIL`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_ID_EMAIL` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;

3. Create Charity Associate Phone Table

CREATE 4-C-CREATE-CHARITY-ASSOCIATE-PHONE.SQL Script

ALTER TABLE CHARITY_ASSOCIATE_PHONE DROP INDEX IF EXISTS CHARITY_ASSOCIATE_PHONE_PK;

DROP TABLE IF EXISTS CHARITY_ASSOCIATE_PHONE;

CREATE TABLE CHARITY_ASSOCIATE_PHONE
(
CHARITY_ASSOCIATE_PHONE_ID        MEDIUMINT   NOT NULL AUTO_INCREMENT,
CHARITY_ASSOCIATE_ID              MEDIUMINT, -- FOREIGN KEY
CHARITY_ASSOCIATE_PHONE_PRIORITY  MEDIUMINT,
CHARITY_ASSOCIATE_PHONE_CELLPHONE VARCHAR(20) NOT NULL,
PRIMARY KEY (CHARITY_ASSOCIATE_PHONE_ID)
);

CREATE UNIQUE INDEX CHARITY_ASSOCIATE_PHONE_PK ON CHARITY_ASSOCIATE_PHONE (CHARITY_ASSOCIATE_PHONE_ID);

ALTER TABLE `CHARITY_ASSOCIATE_PHONE`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_ID_PHONE` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;

4. Create Charity Associate Type Table

CREATE 4-D-CREATE-CHARITY-ASSOCIATE-TYPE.SQL Script

ALTER TABLE CHARITYDB.`CHARITY_ASSOCIATE_TYPES` DROP INDEX IF EXISTS `IDX_CHARITY_ASSOCIATE_TYPE`;

DROP TABLE IF EXISTS CHARITYDB.`CHARITY_ASSOCIATE_TYPES`;

CREATE TABLE CHARITYDB.`CHARITY_ASSOCIATE_TYPES`
(
`CHARITY_ASSOCIATE_TYPE_ID`   MEDIUMINT NOT NULL AUTO_INCREMENT,
`CHARITY_ASSOCIATE_ID`        MEDIUMINT NOT NULL, -- FOREIGN KEY
`CHARITY_ASSOCIATE_TYPE_NAME` VARCHAR(50),
CONSTRAINT `CHARITY_ASSOCIATE_TYPE_PK` PRIMARY KEY (`CHARITY_ASSOCIATE_TYPE_ID`)
);

CREATE UNIQUE INDEX `IDX_CHARITY_ASSOCIATE_TYPE` ON CHARITYDB.`CHARITY_ASSOCIATE_TYPES` (`CHARITY_ASSOCIATE_TYPE_ID` ASC);

ALTER TABLE `CHARITY_ASSOCIATE_TYPES`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_TYPES` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;