7102ICT- Case Study 2814ICT & 7003ICT - Assignment Part 2 Page 1 2814ICT – DATA MANAGEMENT 7003ICT – DATABASE MANAGEMENT School of Information & Communication Technology Trimester 1, 2021 Assignment...

sql_assignment/Task1/tables.txt-- 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 bigM
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema bigM
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `bigM` DEFAULT CHARACTER SET utf8 ;
USE `bigM` ;
-- -----------------------------------------------------
-- Table `bigM`.`CUSTOMER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`CUSTOMER` (
`cust_number` INT NOT NULL AUTO_INCREMENT,
`cust_fname` VARCHAR(30) NULL,
`cust_lname` VARCHAR(30) NULL,
`cust_phone` CHAR(10) NULL,
PRIMARY KEY (`cust_number`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`CUSTOMERORDER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`CUSTOMERORDER` (
`custord_id` INT NOT NULL AUTO_INCREMENT,
`custord_date` DATE NULL,
`cust_number` INT NULL,
`str_num` INT NULL,
PRIMARY KEY (`custord_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`orderline`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`orderline` (
`custord_id` INT NOT NULL AUTO_INCREMENT,
`prod_num` INT NULL,
`ordin_datearrived` DATE NULL,
`ordin_datepicked` DATE NULL,
`ordin_qnty` INT NULL,
PRIMARY KEY (`custord_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`product` (
`prod_num` INT NOT NULL AUTO_INCREMENT,
`prod_desc` VARCHAR(50) NULL,
`prod_size` VARCHAR(10) NULL,
`prod_price` DECIMAL(4,2) NULL,
PRIMARY KEY (`prod_num`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`inventory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`inventory` (
`productnum` INT NOT NULL AUTO_INCREMENT,
`str_num` INT NULL,
`inv_qntyonhand` INT NULL,
`inv_qtyordered` INT NULL,
PRIMARY KEY (`productnum`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`store`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`store` (
`str_num` INT NOT NULL AUTO_INCREMENT,
`str_name` VARCHAR(50) NULL,
`str_phone` CHAR(10) NULL,
`str_fax` CHAR(10) NULL,
`str_email` VARCHAR(40) NULL,
`storemanagerid` INT NULL,
`supstore_num` INT NULL,
PRIMARY KEY (`str_num`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`storedepartment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`storedepartment` (
`strdept_id` INT NOT NULL AUTO_INCREMENT,
`strdept_phone` CHAR(10) NULL,
`strdept_email` VARCHAR(40) NULL,
`deptsupervisorid` INT NULL,
`str_num` INT NULL,
`dept_id` INT NULL,
PRIMARY KEY (`strdept_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`department`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`department` (
`dept_id` INT NOT NULL AUTO_INCREMENT,
`dept_name` VARCHAR(40) NULL,
PRIMARY KEY (`dept_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`employee` (
`emp_id` INT NOT NULL AUTO_INCREMENT,
`emp_fname` VARCHAR(30) NULL,
`emp_lname` VARCHAR(30) NULL,
`emp_phone` CHAR(10) NULL,
`emp_dob` DATE NULL,
`emp_startdate` DATE NULL,
`emp_taxfnum` CHAR(15) NULL,
`emp_hourlysalary` DECIMAL(10,2) NULL,
`strdept_id` INT NULL,
`supervisorid` INT NULL,
PRIMARY KEY (`emp_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigM`.`payslip`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigM`.`payslip` (
`pay_id` INT NOT NULL AUTO_INCREMENT,
`pay_date` DATE NULL,
`pay_num_of_hours` DECIMAL(4,2) NULL,
`pay_amount_gross` DECIMAL(6,2) NULL,
`emp_id` INT NULL,
`str_num` INT NULL,
PRIMARY KEY (`pay_id`))
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Constraints
ALTER TABLE `bigm`.`employee`
CHANGE COLUMN `supervisorid` `supervisorid` INT NULL DEFAULT NULL ,
ADD INDEX `supervises_idx` (`supervisorid` ASC) VISIBLE;
;
ALTER TABLE `bigm`.`employee`
ADD CONSTRAINT `supervises`
FOREIGN KEY (`supervisorid`)
REFERENCES `bigm`.`employee` (`emp_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
UPDATE `bigm`.`store`
SET
`supstore_num` = 200
WHERE `supstore_num` >0;
ALTER TABLE `bigm`.`store`
ADD INDEX `supervises_idx2` (`supstore_num` ASC) VISIBLE;
;
ALTER TABLE `bigm`.`store`
ADD CONSTRAINT `supervises2`
FOREIGN KEY (`supstore_num`)
REFERENCES `bigm`.`store` (`str_num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `bigm`.`customerorder`
ADD INDEX `storNum_idx` (`str_num` ASC) VISIBLE,
ADD INDEX `custNo_idx` (`cust_number` ASC) VISIBLE;
;
ALTER TABLE `bigm`.`customerorder`
ADD CONSTRAINT `storeNum`
FOREIGN KEY (`str_num`)
REFERENCES `bigm`.`store` (`str_num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `custNo`
FOREIGN KEY (`cust_number`)
REFERENCES `bigm`.`customer` (`cust_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `bigm`.`orderline`
ADD INDEX `prodNum_idx` (`prod_num` ASC) VISIBLE;
;
ALTER TABLE `bigm`.`orderline`
ADD CONSTRAINT `cust_order`
FOREIGN KEY (`custord_id`)
REFERENCES `bigm`.`customerorder` (`custord_id`)
ON DELETE...