1. Creating the 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;

2. 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;

3. 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;

4. 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;

5. 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;