GraphDB with Patrick and Alan

📅 Tue May 31th, 2022
🔗 https://us06web.zoom.us/j/89910098699?pwd=RjFCR0t3dEhpRXFLcXVIMXkwM3J1Zz09

1. Call to Order

• Patrick Guo - guopatrick.comping@gmail.com

• Alan Manewitz - alan.manewitz@ai-strategies.net

2. Main Topics

Data Model EER Diagram

Neo4j

SQL schema

-- MySQL Script generated by MySQL Workbench
-- Tue May 31 15:18:34 2022
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema daanmatch_datamodel
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema daanmatch_datamodel
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `daanmatch_datamodel` DEFAULT CHARACTER SET utf8 ;
USE `daanmatch_datamodel` ;

-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`daanmatch_ngo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`daanmatch_ngo` (
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `name` VARCHAR(255) NULL,
  `description` TEXT NULL,
  `type` VARCHAR(64) NULL,
  PRIMARY KEY (`daanmatch_ngo_id`),
  UNIQUE INDEX `id_UNIQUE` (`daanmatch_ngo_id` ASC) VISIBLE);


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`sector`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`sector` (
  `id` VARCHAR(45) NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`partnership`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`partnership` (
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `partner_id` VARCHAR(128) NOT NULL,
  `relationship` TEXT NULL,
  `sector_id` VARCHAR(45) NULL,
  PRIMARY KEY (`daanmatch_ngo_id`),
  INDEX `fk_partnership_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  INDEX `fk_partnership_daanmatch_ngo2_idx` (`partner_id` ASC) VISIBLE,
  INDEX `fk_partnership_sector1_idx` (`sector_id` ASC) VISIBLE,
  CONSTRAINT `fk_partnership_daanmatch_ngo1`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_partnership_daanmatch_ngo2`
    FOREIGN KEY (`partner_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_partnership_sector`
    FOREIGN KEY (`sector_id`)
    REFERENCES `daanmatch_datamodel`.`sector` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`member` (
  `member_id` VARCHAR(128) NOT NULL,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  PRIMARY KEY (`member_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`people`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`people` (
  `member_id` VARCHAR(128) NOT NULL,
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `designation` VARCHAR(45) NULL,
  PRIMARY KEY (`member_id`),
  INDEX `fk_people_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  CONSTRAINT `fk_people_member`
    FOREIGN KEY (`member_id`)
    REFERENCES `daanmatch_datamodel`.`member` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_people_daanmatch_ngo`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`field_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`field_data` (
  `field_id` VARCHAR(128) NOT NULL,
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `recorded_by` VARCHAR(128) NOT NULL,
  `date` DATETIME NULL,
  `description` TEXT NULL,
  `audio` VARCHAR(2083) NULL,
  `photo` VARCHAR(2083) NULL,
  `video` VARCHAR(2083) NULL,
  `sector_id` VARCHAR(45) NULL,
  PRIMARY KEY (`field_id`),
  UNIQUE INDEX `id_UNIQUE` (`field_id` ASC) VISIBLE,
  INDEX `fk_field_data_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  INDEX `fk_field_data_people1_idx` (`recorded_by` ASC) VISIBLE,
  INDEX `fk_field_data_sector1_idx` (`sector_id` ASC) VISIBLE,
  CONSTRAINT `fk_field_data_daanmatch_ngo`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_field_data_people`
    FOREIGN KEY (`recorded_by`)
    REFERENCES `daanmatch_datamodel`.`people` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_field_data_sector`
    FOREIGN KEY (`sector_id`)
    REFERENCES `daanmatch_datamodel`.`sector` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`reg_num`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`reg_num` (
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `type` VARCHAR(45) NULL,
  `num` VARCHAR(45) NULL,
  `date` DATE NULL,
  PRIMARY KEY (`daanmatch_ngo_id`),
  INDEX `fk_registration_number_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  CONSTRAINT `fk_registration_number_daanmatch_ngo`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`finance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`finance` (
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `year` YEAR NULL,
  `annual_expenditure` FLOAT NULL,
  `total_funding_gap` FLOAT NULL,
  `no_donors` INT NULL,
  PRIMARY KEY (`daanmatch_ngo_id`),
  INDEX `fk_finance_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  CONSTRAINT `fk_finance_daanmatch_ngo`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`reg_office`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`reg_office` (
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `registration_with` VARCHAR(45) NULL,
  `date` DATE NULL,
  PRIMARY KEY (`daanmatch_ngo_id`),
  CONSTRAINT `fk_reg_office_daanmatch_ngo`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`contact_ngo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`contact_ngo` (
  `contact_id` VARCHAR(128) NOT NULL,
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `type` VARCHAR(45) NULL,
  `field` VARCHAR(2083) NULL,
  INDEX `fk_ngo_contact_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  PRIMARY KEY (`contact_id`),
  CONSTRAINT `fk_ngo_contact_daanmatch_ngo`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`contact_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`contact_member` (
  `contact_id` VARCHAR(128) NOT NULL,
  `member_id` VARCHAR(128) NOT NULL,
  `type` VARCHAR(45) NULL,
  `field` VARCHAR(2083) NULL,
  PRIMARY KEY (`contact_id`),
  INDEX `fk_member_contact_member1_idx` (`member_id` ASC) VISIBLE,
  CONSTRAINT `fk_member_contact_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `daanmatch_datamodel`.`member` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`addr_ngo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`addr_ngo` (
  `addr_id` VARCHAR(128) NOT NULL,
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `addr_line_1` VARCHAR(500) NULL,
  `addr_line_2` VARCHAR(500) NULL,
  `city` VARCHAR(45) NULL,
  `state` VARCHAR(45) NULL,
  `pin_code` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  `latitude` DECIMAL NULL,
  `longitude` DECIMAL NULL,
  PRIMARY KEY (`addr_id`),
  INDEX `fk_address_reg_ngo_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  CONSTRAINT `fk_address_reg_ngo_daanmatch_ngo1`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`addr_field_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`addr_field_data` (
  `addr_id` VARCHAR(128) NOT NULL,
  `field_data_id` VARCHAR(128) NOT NULL,
  `addr_line_1` VARCHAR(500) NULL,
  `addr_line_2` VARCHAR(500) NULL,
  `city` VARCHAR(45) NULL,
  `state` VARCHAR(45) NULL,
  `pin_code` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  `latitude` DECIMAL NULL,
  `longitude` DECIMAL NULL,
  PRIMARY KEY (`addr_id`),
  INDEX `fk_address_reg_ngo_copy1_field_data1_idx` (`field_data_id` ASC) VISIBLE,
  CONSTRAINT `fk_address_reg_ngo_copy1_field_data1`
    FOREIGN KEY (`field_data_id`)
    REFERENCES `daanmatch_datamodel`.`field_data` (`field_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `daanmatch_datamodel`.`issue_working`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daanmatch_datamodel`.`issue_working` (
  `daanmatch_ngo_id` VARCHAR(128) NOT NULL,
  `issue` VARCHAR(128) NULL,
  INDEX `fk_issue_working_daanmatch_ngo1_idx` (`daanmatch_ngo_id` ASC) VISIBLE,
  CONSTRAINT `fk_issue_working_daanmatch_ngo1`
    FOREIGN KEY (`daanmatch_ngo_id`)
    REFERENCES `daanmatch_datamodel`.`daanmatch_ngo` (`daanmatch_ngo_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Database connection

Hostname: daanmatch-mysql.cjbwmakklxd2.us-west-1.rds.amazonaws.com

Port: 3306

Username: alan

Password: sent on slack

Data as CSVs

Ideally uploaded to MySQL, but there’s some issues with certain columns that breaks the import wizard.

Design Thinking Process

Mural

Other

Gamification Resources

• AWS for Games

• Cloud Game Development

• The Unique Architecture behind Amazon Games’ Seamless MMO New World

• AWS for Games – Helping developers create new gaming experiences with purpose-built services, solutions, and partners

Ethical AI

Trusted AI 101: A Guide to Building Trustworthy and Ethical AI Systems