InventoryMgmt/handley.sql at main · pampon22/InventoryMgmt · 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
/*******************************************************************************
   Handley Database - Version 1.0
   Script: Handley_db.sql
   Description: Creates the Handley database for our inventory management app.
   DB Server: MySql
   Author: Philip Ampong
********************************************************************************/

/*******************************************************************************
   Drop database if it exists
********************************************************************************/
DROP DATABASE IF EXISTS `Handley`;

/*******************************************************************************
   Create database
********************************************************************************/
CREATE DATABASE `Handley`;

USE `Handley`;

/*******************************************************************************
   Create Tables
********************************************************************************/
CREATE TABLE `warehouse` (
  `warehouse_id` int unsigned NOT NULL AUTO_INCREMENT,
  `state` char(2) NOT NULL,
  `capacity` int unsigned DEFAULT 0,
  PRIMARY KEY (`warehouse_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `brand` (
  `brand_id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `shoe` (
  `shoe_id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `fk_location_id` int unsigned DEFAULT 0,
  `color` varchar(45) NOT NULL,
  `brand` varchar(45) NOT NULL,
  `quantity` int unsigned DEFAULT 0 NOT NULL,
  PRIMARY KEY (`shoe_id`),
  KEY `location_id_idx` (`fk_location_id`),
  CONSTRAINT `fk_location_id` FOREIGN KEY (`fk_location_id`) REFERENCES `warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `postal_code` (
  `postal_code` int unsigned NOT NULL,
  `state` char(2) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`postal_code`),
  UNIQUE KEY `postal_code_UNIQUE` (`postal_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*******************************************************************************
	End of Table creations
********************************************************************************/


/**********
	Population with data
***********/
INSERT INTO `warehouse` (`state`, `capacity`) VALUES ('CA', 2);
INSERT INTO `warehouse` (`state`, `capacity`) VALUES ('NV', 40);
INSERT INTO `warehouse` (`state`) VALUES ('OH');
INSERT INTO `warehouse` (`state`) VALUES ('MA');

SELECT * FROM warehouse;

INSERT INTO `brand` (`name`) VALUES ('Nike');

INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Lebron soldier',		11,	'Nike',			'red', 10);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Air force 1',		 11,	'Nike', 		'white', 15);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Under Armour Curry 6',	12,	'UnderArmor',	'off-white', 2);
INSERT INTO `shoe` (`name`, `brand`, `color`, `fk_location_id`) VALUES ("Disco Goldfish Platforms", "The Internet", "multi-color", 11);
INSERT INTO `shoe` (`name`, `brand`, `color`, `fk_location_id`) VALUES ("The Sandwich Flip Flops", "McDonalds", "brown", 11);

INSERT INTO `shoe` (`name`, `brand`, `color`, `fk_location_id`) VALUES ("Vans 147", "Vans", "White", 11);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Reebok Kamikaze',	12,	'Reebok', 'blue+black', 2);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Converse Anarchy',	13,	'Converse', 'grey', 4);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Reebok Allstar',	12,	'Reebok', 'purple', 2);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('KD 5',	12,	'Nike',	'green', 2);
INSERT INTO `shoe` (`name`, `fk_location_id`, `brand`, `color`, `quantity`) VALUES ('Jordan 1s', 12, 'Air Jordan', 'Black', 2);

SELECT * FROM shoe;
DESCRIBE shoe;