6vTpnjfy

1CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
2USE `mydb` ;
3
4-- -----------------------------------------------------
5-- Table `mydb`.`employees`
6-- -----------------------------------------------------
7CREATE TABLE IF NOT EXISTS `mydb`.`employees` (
8  `eno` INT NOT NULL,
9  `ename` VARCHAR(45) NOT NULL,
10  `zip` VARCHAR(45) NOT NULL,
11  `hdate` DATE NOT NULL,
12  PRIMARY KEY (`eno`))
13ENGINE = InnoDB;
14
15
16-- -----------------------------------------------------
17-- Table `mydb`.`parts`
18-- -----------------------------------------------------
19CREATE TABLE IF NOT EXISTS `mydb`.`parts` (
20  `pno` INT NOT NULL,
21  `pname` VARCHAR(45) NOT NULL,
22  `qoh` INT NULL,
23  `price` INT NULL,
24  `olevel` INT NULL,
25  PRIMARY KEY (`pno`))
26ENGINE = InnoDB;
27
28
29-- -----------------------------------------------------
30-- Table `mydb`.`customers`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `mydb`.`customers` (
33  `cno` INT NOT NULL,
34  `cname` VARCHAR(45) NULL,
35  `street` VARCHAR(45) NULL,
36  `zip` VARCHAR(45) NULL,
37  `phone` VARCHAR(45) NULL,
38  PRIMARY KEY (`cno`))
39ENGINE = InnoDB;
40
41
42-- -----------------------------------------------------
43-- Table `mydb`.`orders`
44-- -----------------------------------------------------
45CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
46  `ono` INT NOT NULL,
47  `cno` INT NOT NULL,
48  `eno` INT NOT NULL,
49  `received` INT NOT NULL,
50  `shipped` INT NOT NULL,
51  PRIMARY KEY (`ono`),
52  INDEX `fk_orders_customers_idx` (`cno` ASC),
53  INDEX `fk_orders_employees1_idx` (`eno` ASC),
54  CONSTRAINT `fk_orders_customers`
55    FOREIGN KEY (`cno`)
56    REFERENCES `mydb`.`customers` (`cno`)
57    ON DELETE NO ACTION
58    ON UPDATE NO ACTION,
59  CONSTRAINT `fk_orders_employees1`
60    FOREIGN KEY (`eno`)
61    REFERENCES `mydb`.`employees` (`eno`)
62    ON DELETE NO ACTION
63    ON UPDATE NO ACTION)
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `mydb`.`odetails`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `mydb`.`odetails` (
71  `ono` INT NOT NULL,
72  `pno` INT NOT NULL,
73  `qty` INT NULL,
74  INDEX `fk_odetails_orders1_idx` (`ono` ASC),
75  PRIMARY KEY (`ono`),
76  INDEX `fk_odetails_parts1_idx` (`pno` ASC),
77  CONSTRAINT `fk_odetails_orders1`
78    FOREIGN KEY (`ono`)
79    REFERENCES `mydb`.`orders` (`ono`)
80    ON DELETE NO ACTION
81    ON UPDATE NO ACTION,
82  CONSTRAINT `fk_odetails_parts1`
83    FOREIGN KEY (`pno`)
84    REFERENCES `mydb`.`parts` (`pno`)
85    ON DELETE NO ACTION
86    ON UPDATE NO ACTION)
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `mydb`.`zipcodes`
92-- -----------------------------------------------------
93CREATE TABLE IF NOT EXISTS `mydb`.`zipcodes` (
94  `zip` VARCHAR(45) NULL,
95  `city` VARCHAR(45) NULL)
96ENGINE = InnoDB;