ionicSoftDB.sql

-- MySQL Script generated by MySQL Workbench
-- Sun Feb 24 16:00:00 2019
-- 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 ionicSoftDB
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Person`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Person` (
  `username` VARCHAR(16) NOT NULL,
  `email` VARCHAR(255) NULL,
  `password` VARCHAR(32) NOT NULL,
  `userID` INT NOT NULL,
  `firstName` VARCHAR(45) NOT NULL,
  `lastName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`userID`));


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Producer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Producer` (
  `companyName` VARCHAR(255) NOT NULL,
  `userID` INT NOT NULL,
  `producerID` INT NOT NULL,
  PRIMARY KEY (`producerID`),
  CONSTRAINT `userID`
    FOREIGN KEY (`userID`)
    REFERENCES `ionicSoftDB`.`Person` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`PaymentInfo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`PaymentInfo` (
  `paymentID` INT NOT NULL,
  `cardName` VARCHAR(255) NOT NULL,
  `cardNum` INT UNSIGNED NOT NULL,
  `CVC` INT NOT NULL,
  `expDate` DATE NOT NULL,
  PRIMARY KEY (`paymentID`));


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Customer` (
  `userID` INT NOT NULL,
  `customerID` INT NOT NULL,
  `paymentID` INT NOT NULL,
  INDEX `PaymentID_idx` (`paymentID` ASC) VISIBLE,
  PRIMARY KEY (`customerID`),
  CONSTRAINT `userID`
    FOREIGN KEY (`userID`)
    REFERENCES `ionicSoftDB`.`Person` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `PaymentID`
    FOREIGN KEY (`paymentID`)
    REFERENCES `ionicSoftDB`.`PaymentInfo` (`paymentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Show`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Show` (
  `showID` INT NOT NULL,
  `showName` VARCHAR(255) NOT NULL,
  `producerID` INT NOT NULL,
  PRIMARY KEY (`showID`),
  INDEX `ProducerID_idx` (`producerID` ASC) VISIBLE,
  CONSTRAINT `ProducerID`
    FOREIGN KEY (`producerID`)
    REFERENCES `ionicSoftDB`.`Producer` (`producerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Subscriptions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Subscriptions` (
  `subscriptionID` INT NOT NULL,
  `customerID` INT NOT NULL,
  `showID` INT NOT NULL,
  `currentEpisode` INT NOT NULL,
  PRIMARY KEY (`subscriptionID`),
  INDEX `SubscriberID_idx` (`customerID` ASC) VISIBLE,
  INDEX `showID_idx` (`showID` ASC) VISIBLE,
  CONSTRAINT `SubscriberID`
    FOREIGN KEY (`customerID`)
    REFERENCES `ionicSoftDB`.`Customer` (`customerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `showID`
    FOREIGN KEY (`showID`)
    REFERENCES `ionicSoftDB`.`Show` (`showID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Video`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Video` (
  `title` VARCHAR(16) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `mainActor` VARCHAR(32) NOT NULL,
  `vidID` INT NOT NULL,
  `genre` VARCHAR(45) NOT NULL,
  `avgRating` INT NULL,
  `vidFilename` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`vidID`));


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Episode`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Episode` (
  `episodeID` INT NOT NULL,
  `seasonNum` INT NOT NULL,
  `showID` INT NOT NULL,
  `relEpNum` INT NOT NULL,
  `absEpNum` INT NOT NULL,
  `vidID` INT NOT NULL,
  PRIMARY KEY (`episodeID`),
  INDEX `showID_idx` (`showID` ASC) VISIBLE,
  INDEX `vidID_idx` (`vidID` ASC) VISIBLE,
  CONSTRAINT `showID`
    FOREIGN KEY (`showID`)
    REFERENCES `ionicSoftDB`.`Show` (`showID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `vidID`
    FOREIGN KEY (`vidID`)
    REFERENCES `ionicSoftDB`.`Video` (`vidID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`logEntry`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`logEntry` (
  `logTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actionPerformed` VARCHAR(255) NOT NULL,
  `logID` INT NOT NULL,
  `CustomerID` INT NOT NULL,
  PRIMARY KEY (`logID`),
  INDEX `CustomerID_idx` (`CustomerID` ASC) VISIBLE,
  CONSTRAINT `CustomerID`
    FOREIGN KEY (`CustomerID`)
    REFERENCES `ionicSoftDB`.`Customer` (`customerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `ionicSoftDB`.`Movie`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ionicSoftDB`.`Movie` (
  `movieID` INT NOT NULL,
  `vidID` INT NOT NULL,
  PRIMARY KEY (`movieID`),
  INDEX `vidID_idx` (`vidID` ASC) VISIBLE,
  CONSTRAINT `vidID`
    FOREIGN KEY (`vidID`)
    REFERENCES `ionicSoftDB`.`Video` (`vidID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE USER 'root' IDENTIFIED BY 'ionicsoft';

GRANT ALL ON `ionicSoftDB`.* TO 'root';

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