· 5 years ago · Mar 24, 2020, 05:04 AM
1DROP DATABASE If EXISTS Lab1;
2CREATE DATABASE IF NOT EXISTS Lab1;
3USE Lab1;
4
5SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
6SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
7SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
8
9-- -----------------------------------------------------
10-- Schema mydb
11-- -----------------------------------------------------
12
13-- -----------------------------------------------------
14-- Table `Customer`
15-- -----------------------------------------------------
16CREATE TABLE IF NOT EXISTS `Customer` (
17 `CustNo` CHAR(8) NOT NULL,
18 `FirstName` VARCHAR(45) NULL,
19 `LastName` VARCHAR(45) NULL,
20 `Street` VARCHAR(45) NULL,
21 `City` VARCHAR(45) NULL,
22 `State` VARCHAR(45) NULL,
23 `PhoneNo` VARCHAR(45) NULL,
24 `CustBal` VARCHAR(45) NULL,
25 PRIMARY KEY (`CustNo`))
26ENGINE = InnoDB;
27
28
29-- -----------------------------------------------------
30-- Table `Employee`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `Employee` (
33 `EmpNo` CHAR(8) NOT NULL,
34 `FirstName` VARCHAR(45) NULL,
35 `LastName` VARCHAR(45) NULL,
36 `PhoneNo` VARCHAR(45) NULL,
37 `Email` VARCHAR(45) NULL,
38 PRIMARY KEY (`EmpNo`))
39ENGINE = InnoDB;
40
41
42-- -----------------------------------------------------
43-- Table `Order`
44-- -----------------------------------------------------
45CREATE TABLE IF NOT EXISTS `Order` (
46 `OrderNo` CHAR(8) NOT NULL,
47 `OrderDate` VARCHAR(45) NULL,
48 `Customer_CustNo` CHAR(8) NOT NULL,
49 `Employee_EmpNo` CHAR(8) NULL,
50 `CustName` VARCHAR(45) NULL,
51 `Street` VARCHAR(45) NULL,
52 `City` VARCHAR(45) NULL,
53 `State` VARCHAR(45) NULL,
54 `PhoneNo` VARCHAR(45) NULL,
55 PRIMARY KEY (`OrderNo`),
56 INDEX `fk_Order_Customer1_idx` (`Customer_CustNo` ASC),
57 INDEX `fk_Order_Employee1_idx` (`Employee_EmpNo` ASC),
58 CONSTRAINT `fk_Order_Customer1`
59 FOREIGN KEY (`Customer_CustNo`)
60 REFERENCES `Customer` (`CustNo`)
61 ON DELETE NO ACTION
62 ON UPDATE NO ACTION,
63 CONSTRAINT `fk_Order_Employee1`
64 FOREIGN KEY (`Employee_EmpNo`)
65 REFERENCES `Employee` (`EmpNo`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION)
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `Product`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `Product` (
75 `ProdNo` CHAR(8) NOT NULL,
76 `ProdName` VARCHAR(45) NULL,
77 `Mfg` VARCHAR(45) NULL,
78 `Stock` INT NULL,
79 `Price` VARCHAR(45) NULL,
80 PRIMARY KEY (`ProdNo`))
81ENGINE = InnoDB;
82
83
84-- -----------------------------------------------------
85-- Table `ProductInOrder`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS `ProductInOrder` (
88 `Order_OrderNo` CHAR(8) NOT NULL,
89 `Product_ProdNo` CHAR(8) NOT NULL,
90 `Qty` INT NOT NULL,
91 INDEX `fk_ProductInOrder_Order1_idx` (`Order_OrderNo` ASC),
92 PRIMARY KEY (`Product_ProdNo`, `Order_OrderNo`),
93 CONSTRAINT `fk_ProductInOrder_Product1`
94 FOREIGN KEY (`Product_ProdNo`)
95 REFERENCES `Product` (`ProdNo`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION,
98 CONSTRAINT `fk_ProductInOrder_Order1`
99 FOREIGN KEY (`Order_OrderNo`)
100 REFERENCES `Order` (`OrderNo`)
101 ON DELETE NO ACTION
102 ON UPDATE NO ACTION)
103ENGINE = InnoDB;
104
105
106SET SQL_MODE=@OLD_SQL_MODE;
107SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
108SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;