COSC457/SqlCode.sql at master · hgrad12/COSC457 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
-- MySQL Script generated by MySQL Workbench
-- 04/25/16 13:27:36
-- 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 gkoutr2db
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema gkoutr2db
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `gkoutr2db` DEFAULT CHARACTER SET latin1 ;
USE `gkoutr2db` ;

-- -----------------------------------------------------
-- Table `gkoutr2db`.`Employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Employee` (
  `employee_id` CHAR(8) NOT NULL,
  `first_name` VARCHAR(45) NULL,
  `Minit` CHAR(1) NULL,
  `last_name` VARCHAR(45) NULL,
  `dob` DATE NULL,
  `gender` CHAR(1) NULL,
  `address` VARCHAR(45) NULL,
  `Super_ID` CHAR(8) NULL,
  `SSN` CHAR(9) NULL,
  PRIMARY KEY (`employee_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gkoutr2db`.`Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Customer` (
  `Customer_ID` CHAR(8) NOT NULL,
  `fname` VARCHAR(45) NULL,
  `Minit` CHAR(1) NULL,
  `lname` VARCHAR(45) NULL,
  `user_name` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  PRIMARY KEY (`Customer_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gkoutr2db`.`Inventory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Inventory` (
  `Item_ID` CHAR(5) NOT NULL,
  `item_name` VARCHAR(45) NULL,
  `brand` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  `quanitity` INT NULL,
  `store_id` CHAR(7) NULL,
  `warehouse_id` CHAR(7) NULL,
  PRIMARY KEY (`Item_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gkoutr2db`.`Store`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Store` (
  `store_id` CHAR(7) NOT NULL,
  `item_id` CHAR(5) NULL,
  `super_id` CHAR(8) NULL,
  `location` VARCHAR(45) NULL,
  PRIMARY KEY (`store_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gkoutr2db`.`Warehouse`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Warehouse` (
  `warehouse_id` CHAR(7) NOT NULL,
  `item_id` CHAR(5) NULL,
  `super_id` CHAR(8) NULL,
  `location` VARCHAR(45) NULL,
  PRIMARY KEY (`warehouse_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gkoutr2db`.`Cart`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Cart` (
  `cust_id` CHAR(8) NOT NULL,
  `Item_ID` VARCHAR(45) NOT NULL,
  `Quantity` INT NULL,
  PRIMARY KEY (`cust_id`, `Item_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gkoutr2db`.`Orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gkoutr2db`.`Orders` (
  `Cust_id` CHAR(8) NOT NULL,
  `item_id` CHAR(5) NOT NULL,
  `Quantity` INT NULL,
  `Timestamp` DATE NULL,
  `Transaction_num` CHAR(10) NULL,
  PRIMARY KEY (`Cust_id`, `item_id`))
ENGINE = InnoDB;


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