AeRMHNU5

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema test
9-- -----------------------------------------------------
10-- Final Project DB. Created by Forward Engineering an Entity Relationship Diagram
11
12-- -----------------------------------------------------
13-- Schema test
14--
15-- Final Project DB. Created by Forward Engineering an Entity Relationship Diagram
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 ;
18USE `test` ;
19
20-- -----------------------------------------------------
21-- Table `test`.`CUSTOMER`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `test`.`CUSTOMER` (
24  `Customer_ID` INT NOT NULL AUTO_INCREMENT,
25  `FirstName` VARCHAR(45) NOT NULL,
26  `LastName` VARCHAR(75) NOT NULL,
27  `EmailAddress` NVARCHAR(254) NULL,
28  `PhoneNumber` CHAR(10) NULL,
29  `StreetAddress` VARCHAR(100) NOT NULL,
30  `City` VARCHAR(45) NOT NULL,
31  `State` CHAR(2) NOT NULL,
32  `ZipCode` CHAR(10) NOT NULL,
33  PRIMARY KEY (`Customer_ID`),
34  UNIQUE INDEX `Customer_ID_UNIQUE` (`Customer_ID` ASC))
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `test`.`PET`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `test`.`PET` (
42  `Pet_ID` INT NOT NULL AUTO_INCREMENT,
43  `Customer_ID` INT NOT NULL,
44  `PetName` VARCHAR(75) NOT NULL,
45  `PetType` ENUM('Dog', 'Cat', 'Bird', 'Fish') NOT NULL,
46  `Gender` ENUM('M', 'F') NOT NULL,
47  `Birthday` DATE NOT NULL,
48  PRIMARY KEY (`Pet_ID`, `Customer_ID`),
49  INDEX `fk_PET_DATA_CUSTOMER_DATA_idx` (`Customer_ID` ASC),
50  CONSTRAINT `fk_PET_DATA_CUSTOMER_DATA`
51    FOREIGN KEY (`Customer_ID`)
52    REFERENCES `test`.`CUSTOMER` (`Customer_ID`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `test`.`INVENTORY`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `test`.`INVENTORY` (
62  `Item_ID` INT NOT NULL,
63  `ItemName` VARCHAR(256) NOT NULL,
64  `AvailableUnits` INT NOT NULL,
65  `Price` DECIMAL(13,2) NOT NULL,
66  PRIMARY KEY (`Item_ID`))
67ENGINE = InnoDB;
68
69
70-- -----------------------------------------------------
71-- Table `test`.`ORDERS`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS `test`.`ORDERS` (
74  `Order_ID` INT NOT NULL AUTO_INCREMENT,
75  `Customer_ID` INT NOT NULL,
76  `Pet_ID` INT NOT NULL,
77  `Item_ID` INT NOT NULL,
78  `ItemQuantity` INT NOT NULL,
79  `OrderTotal` DECIMAL(13,2) NOT NULL,
80  `OrderDate` DATE NOT NULL,
81  `OrderStatus` ENUM('Packing', 'Shipping', 'Closed', 'Initiating', 'Payment Pending', 'Filling') NOT NULL,
82  `PaymentType` ENUM('Credit', 'Check', 'PayPal') NOT NULL,
83  PRIMARY KEY (`Order_ID`, `Customer_ID`, `Pet_ID`, `Item_ID`),
84  INDEX `fk_ORDER_INVENTORY1_idx` (`Item_ID` ASC),
85  INDEX `fk_ORDER_CUSTOMER1_idx` (`Customer_ID` ASC),
86  INDEX `fk_ORDER_PET1_idx` (`Pet_ID` ASC),
87  CONSTRAINT `fk_ORDER_INVENTORY1`
88    FOREIGN KEY (`Item_ID`)
89    REFERENCES `test`.`INVENTORY` (`Item_ID`)
90    ON DELETE NO ACTION
91    ON UPDATE NO ACTION,
92  CONSTRAINT `fk_ORDER_CUSTOMER1`
93    FOREIGN KEY (`Customer_ID`)
94    REFERENCES `test`.`CUSTOMER` (`Customer_ID`)
95    ON DELETE NO ACTION
96    ON UPDATE NO ACTION,
97  CONSTRAINT `fk_ORDER_PET1`
98    FOREIGN KEY (`Pet_ID`)
99    REFERENCES `test`.`PET` (`Pet_ID`)
100    ON DELETE NO ACTION
101    ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104USE `test` ;
105
106-- -----------------------------------------------------
107--  routine1
108-- -----------------------------------------------------
109
110DELIMITER $$
111USE `test`$$
112$$
113
114DELIMITER ;
115
116SET SQL_MODE=@OLD_SQL_MODE;
117SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
118SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;