-- MySQL Script generated by MySQL Workbench
-- 11/30/17 13:08:41
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema ns_Pepperoni
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema ns_Pepperoni
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `ns_Pepperoni` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `ns_Pepperoni` ;

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Students`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Students` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Students` (
  `studentID` INT NOT NULL,
  `email` VARCHAR(45) NULL,
  `ugamyid` VARCHAR(8) NULL,
  `yeartitle` VARCHAR(12) NULL,
  PRIMARY KEY (`studentID`))
ENGINE = InnoDB;

INSERT INTO Students VALUES (8442486591, "ske56525@uga.edu", "ske56525", "junior");
INSERT INTO Students VALUES (8124514598, "kke55225@uga.edu", "kke55225", "freshman");
INSERT INTO Students VALUES (8445249595, "ske45225@uga.edu", "ske45225", "freshman");
INSERT INTO Students VALUES (8125486598, "kuh35825@uga.edu", "kuh35825", "sophmore");
INSERT INTO Students VALUES (8442458257, "jke98515@uga.edu", "jke98515", "senior");
INSERT INTO Students VALUES (8142468598, "ukt25495@uga.edu", "ukt25495", "junior");
INSERT INTO Students VALUES (8144587569, "gth56254@uga.edu", "gth56254", "senior");

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`ClassTimes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`ClassTimes` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`ClassTimes` (
  `timeslotID` VARCHAR(45) NOT NULL,
  `days` VARCHAR(35) NULL,
  `starttime` TIMESTAMP(4) NULL,
  `durationminutes` INT NULL,
  PRIMARY KEY (`timeslotID`))
ENGINE = InnoDB;

INSERT INTO ClassTimes VALUES (1, MWF, "10:10", 50);
INSERT INTO ClassTimes VALUES (6, TR, "11:00", 75);
INSERT INTO ClassTimes VALUES (2, MWF, "9:05", 50);
INSERT INTO ClassTimes VALUES (5, TR, "8:00", 75);
INSERT INTO ClassTimes VALUES (4, TR, "3:30", 75);
INSERT INTO ClassTimes VALUES (3, MWF, "12:05", 50);
-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Classes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Classes` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Classes` (
  `CRN` INT NOT NULL,
  `coursetag` VARCHAR(4) NULL,
  `Class_times_days` VARCHAR(35) NOT NULL,
  PRIMARY KEY (`CRN`),
  INDEX `fk_Classes_Class_times_idx` (`Class_times_days` ASC),
  CONSTRAINT `fk_Classes_Class_times`
    FOREIGN KEY (`Class_times_days`)
    REFERENCES `ns_Pepperoni`.`ClassTimes` (`days`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO Classes VALUES (425474, MIST, "2090_11:00_TR");
INSERT INTO Classes VALUES (585474, MIST, "4610_3:30_TR");
INSERT INTO Classes VALUES (548674, MIST, "5710_12:05_MWF");
INSERT INTO Classes VALUES (158474, MIST, "4620_8:00_TR");
INSERT INTO Classes VALUES (786544, MIST, "5720_9:05_MWF");
INSERT INTO Classes VALUES (445842, MIST, "5510_10:10_MWF");

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Professors`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Professors` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Professors` (
  `profID` INT NOT NULL,
  `prof_fname` VARCHAR(20) NULL,
  `prof_lname` VARCHAR(20) NULL,
  `dateofhire` DATE NULL,
  `tenure` VARCHAR(5) NULL,
  `ugaID` INT NULL,
  PRIMARY KEY (`profID`))
ENGINE = InnoDB;

INSERT INTO Professors VALUES (0001,"Nikhil", "Shriny", 10/21/2002, "10", 5856244125);
INSERT INTO Professors VALUES (0006,"Timothy", "Chesters", 9/11/2003, "10", 2545851325);
INSERT INTO Professors VALUES (0085,"Margret", "Christ", 8/7/2005, "10", 8758934125);
INSERT INTO Professors VALUES (0951,"Robert", "Roseteck", 3/17/2006, "10", 9658522125);

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`OverallGrade`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`OverallGrade` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`OverallGrade` (
  `ClassCRN` INT NOT NULL,
  `gradeavg` INT NULL,
  `gpaWeight` INT NULL,
  PRIMARY KEY (`ClassCRN`),
  INDEX `fk_Grades_Classes1_idx` (`ClassCRN` ASC),
  CONSTRAINT `fk_Grades_Classes1`
    FOREIGN KEY (`ClassCRN`)
    REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO OverallGrade VALUES (425474, 92, 3.7);
INSERT INTO OverallGrade VALUES (585474, 89, 3.2);
INSERT INTO OverallGrade VALUES (548674, 72, 2.3);
INSERT INTO OverallGrade VALUES (158474, 60, 1.0);
INSERT INTO OverallGrade VALUES (786544, 95, 3.7);
INSERT INTO OverallGrade VALUES (445842, 100, 4.0);

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Announcements`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Announcements` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Announcements` (
  `announcementID` INT NOT NULL,
  `content` VARCHAR(512) NULL,
  `date` DATE NULL,
  `CRN` INT NOT NULL,
  PRIMARY KEY (`announcementID`),
  INDEX `fk_Announcements_Classes1_idx` (`CRN` ASC),
  CONSTRAINT `fk_Announcements_Classes1`
    FOREIGN KEY (`CRN`)
    REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO Announcements VALUES (002,"Do Your HW", 10/20/2017, 425474);
INSERT INTO Announcements VALUES (212,"Do Your HW Now", 6/17/2017, 585474);
INSERT INTO Announcements VALUES (852,"Do Your HW Tomorow", 5/14/2017, 548674);
INSERT INTO Announcements VALUES (692,"Do Your HW Next Week", 2/9/2017, 158474);
INSERT INTO Announcements VALUES (032,"Do Your HW Next Month", 8/29/2017, 786544);
INSERT INTO Announcements VALUES (842,"Do Your HW Next Year", 7/25/2017, 445842);

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Chat`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Chat` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Chat` (
  `conversationID` INT NOT NULL,
  `chatpartner` VARCHAR(50) NULL,
  `chatpartnerUGAid` INT NULL,
  `totalmessages` INT NULL,
  `studentID` INT NOT NULL,
  PRIMARY KEY (`conversationID`),
  INDEX `fk_Chat_Students1_idx` (`studentID` ASC),
  CONSTRAINT `fk_Chat_Students1`
    FOREIGN KEY (`studentID`)
    REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO Chat VALUES (1, "Sam", "sam12451", 5, 4512147854);
INSERT INTO Chat VALUES (2, "James", "jam14521", 6, 6584327854);
INSERT INTO Chat VALUES (4, "Cardinal", "car45221", 17, 8759654854);
INSERT INTO Chat VALUES (6, "John", "jon78651", 52, 7895467854);
INSERT INTO Chat VALUES (8, "Snow", "sno95651", 4, 3214587854);
INSERT INTO Chat VALUES (10, "Bob", "bob64521", 9, 9587462154);

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Assignments`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Assignments` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Assignments` (
  `assignmentID` INT NOT NULL,
  `assigned` DATE NULL,
  `due` DATE NULL,
  `submissions` INT NULL,
  `score` INT NULL,
  `assignmentTitle` VARCHAR(45) NULL,
  `ClassCRN` INT NOT NULL,
  `studentID` INT NOT NULL,
  PRIMARY KEY (`assignmentID`),
  INDEX `fk_Assignments_Classes1_idx` (`ClassCRN` ASC),
  INDEX `fk_Assignments_Students1_idx` (`studentID` ASC),
  CONSTRAINT `fk_Assignments_Classes1`
    FOREIGN KEY (`ClassCRN`)
    REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Assignments_Students1`
    FOREIGN KEY (`studentID`)
    REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO Assignments VALUES (046, 10/25/2017, 11/25/2017, 3, 92, "HW 1", 425474, 4512147854);
INSERT INTO Assignments VALUES (982, 6/19/2017, 7/19/2017, 2, 85, "HW 5", 585474, 6584327854);
INSERT INTO Assignments VALUES (983, 5/19/2017, 6/19/2017, 2, 68, "HW 6", 548674, 8759654854);
INSERT INTO Assignments VALUES (942, 2/12/2017, 3/12/2017, 5, 98, "HW 9", 158474, 7895467854);
INSERT INTO Assignments VALUES (325, 8/30/2017, 9/30/2017, 6, 75, "HW 7", 786544, 3214587854);
INSERT INTO Assignments VALUES (865, 7/29/2017, 8/29/2017, 9, 87, "HW 4", 445842, 9587462154);

 -- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Calendar`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Calendar` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Calendar` (
  `Date` DATE NOT NULL,
  PRIMARY KEY (`Date`))
ENGINE = InnoDB;

INSERT INTO Calendar VALUES (10/01/2017);
INSERT INTO Calendar VALUES (11/01/2017);
INSERT INTO Calendar VALUES (12/01/2017);
INSERT INTO Calendar VALUES (1/01/2017);
INSERT INTO Calendar VALUES (2/01/2017);
INSERT INTO Calendar VALUES (3/01/2017);
INSERT INTO Calendar VALUES (4/01/2017);
INSERT INTO Calendar VALUES (5/01/2017);

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`inbox`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`inbox` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`inbox` (
  `InboxID` INT NOT NULL,
  `studentID` INT NOT NULL,
  `address` VARCHAR(45) NULL,
  PRIMARY KEY (`InboxID`),
  INDEX `fk_email_Students1_idx` (`studentID` ASC),
  CONSTRAINT `fk_email_Students1`
    FOREIGN KEY (`studentID`)
    REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO inbox VALUES (046, 4512147854, "123 6th St. Melbourne, FL 32904");
INSERT INTO inbox VALUES (982, 6584327854, "4 Goldfield Rd. Honolulu, HI 96815");
INSERT INTO inbox VALUES (983, 8759654854, "71 Pilgrim Avenue Chevy Chase, MD 20815");
INSERT INTO inbox VALUES (942, 7895467854, "44 Shirley Ave. West Chicago, IL 60185");
INSERT INTO inbox VALUES (325, 3214587854, "70 Bowman St. South Windsor, CT 06074");
INSERT INTO inbox VALUES (865, 9587462154, "514 S. Magnolia St. Orlando, FL 32806");


-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`emails`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`emails` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`emails` (
  `emailID` INT NOT NULL,
  `senderaddress` VARCHAR(100) NULL,
  `subject` VARCHAR(25) NULL,
  `datereceived` DATE NULL,
  `InboxID` INT NOT NULL,
  PRIMARY KEY (`emailID`),
  INDEX `fk_emails_email1_idx` (`InboxID` ASC),
  CONSTRAINT `fk_emails_email1`
    FOREIGN KEY (`InboxID`)
    REFERENCES `ns_Pepperoni`.`inbox` (`InboxID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO emails VALUES ("ske56525@uga.edu", "kds56525@uga.edu", "abs", 6/19/2017, 001);
INSERT INTO emails VALUES ("kke55225@uga.edu", "mdj55225@uga.edu", "biceps", 6/19/2017, 032);
INSERT INTO emails VALUES ("ske45225@uga.edu", "lks45225@uga.edu", "arms", 8/30/2017, 562);
INSERT INTO emails VALUES ("kuh35825@uga.edu", "kdi35825@uga.edu", "legs", 3/12/2017, 458);
INSERT INTO emails VALUES ("jke98515@uga.edu", "oss98515@uga.edu", "thighs", 11/25/2017, 284);
INSERT INTO emails VALUES ("ukt25495@uga.edu", "ijs25495@uga.edu", "feet", 5/19/2017, 251);
INSERT INTO emails VALUES ("gth56254@uga.edu", "los56254@uga.edu", "hands", 9/30/2017, 958);


-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`ClassRoster`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`ClassRoster` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`ClassRoster` (
  `studentID` INT NOT NULL,
  `CRN` INT NOT NULL,
  PRIMARY KEY (`studentID`, `CRN`),
  INDEX `fk_Students_has_Classes_Classes1_idx` (`CRN` ASC),
  INDEX `fk_Students_has_Classes_Students1_idx` (`studentID` ASC),
  CONSTRAINT `fk_Students_has_Classes_Students1`
    FOREIGN KEY (`studentID`)
    REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Students_has_Classes_Classes1`
    FOREIGN KEY (`CRN`)
    REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO ClassRoster VALUES (4512147854, 425474);
INSERT INTO ClassRoster VALUES (6584327854, 585474);
INSERT INTO ClassRoster VALUES (8759654854, 548674);
INSERT INTO ClassRoster VALUES (7895467854, 158474);
INSERT INTO ClassRoster VALUES (3214587854, 786544);
INSERT INTO ClassRoster VALUES (8958746215, 445842);

-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Classes_has_Professors`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Classes_has_Professors` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Classes_has_Professors` (
  `CRN` INT NOT NULL,
  `profID` INT NOT NULL,
  PRIMARY KEY (`CRN`, `profID`),
  INDEX `fk_Classes_has_Professors_Professors1_idx` (`profID` ASC),
  INDEX `fk_Classes_has_Professors_Classes1_idx` (`CRN` ASC),
  CONSTRAINT `fk_Classes_has_Professors_Classes1`
    FOREIGN KEY (`CRN`)
    REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Classes_has_Professors_Professors1`
    FOREIGN KEY (`profID`)
    REFERENCES `ns_Pepperoni`.`Professors` (`profID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO Classes_has_Professors VALUES (425474, 0001);
INSERT INTO Classes_has_Professors VALUES (585474, 0006);
INSERT INTO Classes_has_Professors VALUES (548674, 0085);
INSERT INTO Classes_has_Professors VALUES (158474, 0951);
INSERT INTO Classes_has_Professors VALUES (786544, 0006);
INSERT INTO Classes_has_Professors VALUES (445842, 0951);


-- -----------------------------------------------------
-- Table `ns_Pepperoni`.`Content`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ns_Pepperoni`.`Content` ;

CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Content` (
  `contentID` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `filesize(MB)` INT NULL,
  `CRN` INT NOT NULL,
  PRIMARY KEY (`contentID`),
  INDEX `fk_Content_Classes1_idx` (`CRN` ASC),
  CONSTRAINT `fk_Content_Classes1`
    FOREIGN KEY (`CRN`)
    REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO Content VALUES (0021, "One thing", 20, 425474);
INSERT INTO Content VALUES (0621, "Two thing", 65, 585474);
INSERT INTO Content VALUES (0584, "Four thing", 52, 548674);
INSERT INTO Content VALUES (0745, "Three thing", 85, 158474);
INSERT INTO Content VALUES (4582, "Six thing", 45, 786544);
INSERT INTO Content VALUES (9856, "Five thing", 36, 445842);

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