zoo.sql

-- MySQL Script generated by MySQL Workbench
-- Sat Apr  3 18:44:55 2021
-- 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 Zoo
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema Zoo
-- -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS `Zoo` DEFAULT CHARACTER SET utf8 ;
USE `Zoo` ;

-- -----------------------------------------------------
-- Table `Zoo`.`GiftShop`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`GiftShop` (
  `ShopID` INT NOT NULL,
  PRIMARY KEY (`ShopID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`ItemType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`ItemType` (
  `ID` INT NOT NULL,
  `InStock` INT NULL,
  `Name` LONGTEXT NULL,
  `ItemType_ID` INT NOT NULL,
  `ShopID` INT NOT NULL,
  `GiftShop_ShopID` INT NOT NULL,
  PRIMARY KEY (`ID`, `ShopID`, `GiftShop_ShopID`),
  INDEX `fk_ItemType_GiftShop1_idx` (`GiftShop_ShopID` ASC),
  CONSTRAINT `fk_ItemType_GiftShop1`
    FOREIGN KEY (`GiftShop_ShopID`)
    REFERENCES `Zoo`.`GiftShop` (`ShopID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Employee` (
  `FName` VARCHAR(45) NOT NULL,
  `MInitial` VARCHAR(1) NULL,
  `LName` VARCHAR(45) NOT NULL,
  `ID` INT NOT NULL,
  `StartDate` DATE NOT NULL,
  `Addr` TINYTEXT NOT NULL,
  `Phone` TEXT(13) NOT NULL,
  `Sex` VARCHAR(1) NULL,
  `DOB` DATE NOT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Attraction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Attraction` (
  `Name` VARCHAR(45) NOT NULL,
  `ManagerID` VARCHAR(45) NOT NULL,
  `AnimalID` VARCHAR(45) NOT NULL,
  `Price` DOUBLE NULL,
  `UpkeepCost` DOUBLE NULL,
  PRIMARY KEY (`Name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Animal`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Animal` (
  `Name` VARCHAR(45) NULL,
  `ID` INT NOT NULL,
  `Breed` VARCHAR(45) NOT NULL,
  `ArrivalDate` DATE NOT NULL,
  `Species` VARCHAR(45) NOT NULL,
  `Diet` LONGTEXT NOT NULL,
  `Sex` VARCHAR(1) NOT NULL,
  `DeceasedDate` DATE NULL,
  `DOB` DATE NULL,
  `CarerID` INT NOT NULL,
  `Attraction_Name` VARCHAR(45) NOT NULL,
  `Employee_ID` INT NOT NULL,
  PRIMARY KEY (`ID`, `CarerID`),
  INDEX `fk_Animal_Attraction1_idx` (`Attraction_Name` ASC),
  INDEX `fk_Animal_Employee1_idx` (`Employee_ID` ASC),
  CONSTRAINT `fk_Animal_Attraction1`
    FOREIGN KEY (`Attraction_Name`)
    REFERENCES `Zoo`.`Attraction` (`Name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Animal_Employee1`
    FOREIGN KEY (`Employee_ID`)
    REFERENCES `Zoo`.`Employee` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Ticket Booth`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Ticket Booth` (
  `BoothID` INT NOT NULL,
  PRIMARY KEY (`BoothID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Member` (
  `Email` VARCHAR(80) NOT NULL,
  `FName` VARCHAR(45) NULL,
  `MInitial` VARCHAR(1) NULL,
  `LName` VARCHAR(45) NULL,
  `StartDate` DATE NULL,
  PRIMARY KEY (`Email`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Ticket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Ticket` (
  `TicketID` BIGINT(255) NOT NULL,
  `Price` DOUBLE NOT NULL,
  `Date` DATE NOT NULL,
  `MEmail` VARCHAR(80) NULL,
  `BoothID` INT NOT NULL,
  `Ticket Booth_BoothID` INT NOT NULL,
  `Member_Email` VARCHAR(80) NOT NULL,
  PRIMARY KEY (`TicketID`, `BoothID`, `Ticket Booth_BoothID`),
  INDEX `fk_Ticket_Ticket Booth1_idx` (`Ticket Booth_BoothID` ASC),
  INDEX `fk_Ticket_Member1_idx` (`Member_Email` ASC),
  CONSTRAINT `fk_Ticket_Ticket Booth1`
    FOREIGN KEY (`Ticket Booth_BoothID`)
    REFERENCES `Zoo`.`Ticket Booth` (`BoothID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Ticket_Member1`
    FOREIGN KEY (`Member_Email`)
    REFERENCES `Zoo`.`Member` (`Email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`GiftShop_has_Employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`GiftShop_has_Employee` (
  `GiftShop_ShopID` INT NOT NULL,
  `Employee_ID` INT NOT NULL,
  `Ticket Booth_BoothID` INT NOT NULL,
  `Attraction_Name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Employee_ID`),
  INDEX `fk_GiftShop_has_Employee_Employee1_idx` (`Employee_ID` ASC),
  INDEX `fk_GiftShop_has_Employee_GiftShop1_idx` (`GiftShop_ShopID` ASC),
  INDEX `fk_GiftShop_has_Employee_Ticket Booth1_idx` (`Ticket Booth_BoothID` ASC),
  INDEX `fk_GiftShop_has_Employee_Attraction1_idx` (`Attraction_Name` ASC),
  CONSTRAINT `fk_GiftShop_has_Employee_GiftShop1`
    FOREIGN KEY (`GiftShop_ShopID`)
    REFERENCES `Zoo`.`GiftShop` (`ShopID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GiftShop_has_Employee_Employee1`
    FOREIGN KEY (`Employee_ID`)
    REFERENCES `Zoo`.`Employee` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GiftShop_has_Employee_Ticket Booth1`
    FOREIGN KEY (`Ticket Booth_BoothID`)
    REFERENCES `Zoo`.`Ticket Booth` (`BoothID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GiftShop_has_Employee_Attraction1`
    FOREIGN KEY (`Attraction_Name`)
    REFERENCES `Zoo`.`Attraction` (`Name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Zoo`.`Notifications`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Zoo`.`Notifications` (
  `sent` TINYINT NULL,
  `Name` VARCHAR(45) NOT NULL,
  `Type` VARCHAR(45) NULL,
  PRIMARY KEY (`Name`))
ENGINE = InnoDB;

USE `Zoo`;

DELIMITER $$
USE `Zoo`$$
CREATE DEFINER = CURRENT_USER TRIGGER `Zoo`.`ItemType_AFTER_UPDATE` AFTER UPDATE ON `ItemType` FOR EACH ROW
BEGIN
	IF (NEW.InStock < 10) THEN
		INSERT INTO Notifications(sent, Name, Type)
        VALUES(0, OLD.Name, 1);
    END IF;
END$$


DELIMITER ;

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