Database - w3¶

📅 Team Meeting - 24 Feb 2022 6:45pm PST ¶

Agenda¶

Short EDA presentation
Introduction to AWS RDS and MySQL WorkBench
Create Model Assignment
Questions

This Week’s Objective¶

Get familiar with AWS RDS and MySQL.

What is AWS RDS?

Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks, such as hardware provisioning, database setup, patching, and backups.

What is MySQL?

MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold the vast amounts of information in a corporate network.

DaanMatch’s Data Model¶

Fig. 2 DaanMatch’s Data Model visualized using DrawSQL. Last updated Feb 21 2022.
Changed id datatype from int to uuid. Redefined relationships. Added new field to reflect most used social media platforms in India.¶

What is UUID?

Universally Unique Identifiers, or UUIDS, are 128 bit numbers, composed of 16 octets and represented as 32 base-16 characters, that can be used to identify information across a computer system.

Model Assignment¶

✅ TODO: Create a Model

Will require domain knowledge of what each data field represents. Either web search or ask Cara Arellano for more information.

Arthi: registration_number
Emily: registration_office, finance
Gabriel: people, member
Apoorv: sector, partnership, field_data (will need to work with Emily to connect field_data with member)

✅ TODO: Export EER Script and .mwb file and onto data-model repository in folder shpg-2 (example in folder). You will be presenting on this in the next meeting.

Example¶

Fig. 3 EER diagram of daanmatch_ngo on MySQL Workbench¶

-- MySQL Script generated by MySQL Workbench
-- Mon Feb 21 02:38:50 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 mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`daanmatch_ngo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`daanmatch_ngo` (
  `id` VARCHAR(128) GENERATED ALWAYS AS (UUID()),
  `name` VARCHAR(255) NULL,
  `description` TEXT NULL,
  `type` VARCHAR(64) NULL,
  `last_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `address` TEXT NULL,
  `mobile` VARCHAR(20) NULL,
  `email` VARCHAR(50) NULL,
  `fax` VARCHAR(20) NULL,
  `website` VARCHAR(2083) NULL,
  `facebook` VARCHAR(2083) NULL,
  `instagram` VARCHAR(2083) NULL,
  `twitter` VARCHAR(2083) NULL,
  `fulltime_staff` INT NULL,
  `partime_staff` INT NULL,
  `volunteers` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE);


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

Monthly 1-1 Meetings¶

✅ TODO: Schedule monthly 1-1 meeting with Patrick.

DaanMatch