· 5 years ago · Oct 15, 2020, 02:00 AM
1CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
2USE `mydb` ;
3
4-- -----------------------------------------------------
5-- Table `mydb`.`Supplier`
6-- -----------------------------------------------------
7CREATE TABLE IF NOT EXISTS `mydb`.`Supplier` (
8 `Company` VARCHAR(45) NOT NULL,
9 `Phone` VARCHAR(45) NOT NULL,
10 `INN` INT NOT NULL,
11 PRIMARY KEY (`Company`))
12ENGINE = InnoDB;
13
14
15-- -----------------------------------------------------
16-- Table `mydb`.`Stock`
17-- -----------------------------------------------------
18CREATE TABLE IF NOT EXISTS `mydb`.`Stock` (
19 `Number` INT NOT NULL,
20 `Type` INT NOT NULL DEFAULT 0,
21 `Volume` VARCHAR(45) NOT NULL,
22 `Workers_Name` VARCHAR(45) NOT NULL,
23 PRIMARY KEY (`Number`, `Workers_Name`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `mydb`.`Order`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
31 `Number` INT NOT NULL,
32 `Type` INT NOT NULL,
33 `Price` INT NOT NULL,
34 `Volume` INT NOT NULL,
35 `Workers_Name` VARCHAR(45) NOT NULL,
36 `Stock_Number` INT NOT NULL,
37 `Stock_Workers_Name` VARCHAR(45) NOT NULL,
38 PRIMARY KEY (`Number`),
39 FOREIGN KEY (`Stock_Number` , `Stock_Workers_Name`)
40 REFERENCES `mydb`.`Stock` (`Number` , `Workers_Name`)
41 ON DELETE NO ACTION
42 ON UPDATE NO ACTION)
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `mydb`.`Animals`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `mydb`.`Animals` (
50 `Name` VARCHAR(45) NOT NULL,
51 `Category` VARCHAR(45) NOT NULL,
52 `Quantity` INT NOT NULL,
53 `Consuming` INT NOT NULL,
54 PRIMARY KEY (`Name`))
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `mydb`.`Workers`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `mydb`.`Workers` (
62 `Name` VARCHAR(45) NOT NULL,
63 `Position` VARCHAR(45) NOT NULL,
64 PRIMARY KEY (`Name`))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `mydb`.`Foods`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `mydb`.`Foods` (
72 `Number` INT NOT NULL,
73 `Name` VARCHAR(45) NOT NULL,
74 `Volume` INT NOT NULL,
75 PRIMARY KEY (`Number`))
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`SupplierOrder`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `mydb`.`SupplierOrder` (
83 `Supplier_Company` VARCHAR(45) NOT NULL,
84 `Order_Number` INT NOT NULL,
85 PRIMARY KEY (`Supplier_Company`, `Order_Number`),
86 INDEX `fk_SupplierOrder_Order1_idx` (`Order_Number` ASC) VISIBLE,
87 CONSTRAINT `fk_SupplierOrder_Order1`
88 FOREIGN KEY (`Order_Number`)
89 REFERENCES `mydb`.`Order` (`Number`)
90 ON DELETE NO ACTION
91 ON UPDATE NO ACTION)
92ENGINE = InnoDB;
93
94
95-- -----------------------------------------------------
96-- Table `mydb`.`WorkersAnimals`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `mydb`.`WorkersAnimals` (
99 `Workers_Name` VARCHAR(45) NOT NULL,
100 `Animals_Name` VARCHAR(45) NOT NULL,
101 PRIMARY KEY (`Workers_Name`, `Animals_Name`),
102 INDEX `fk_WorkersAnimals_Animals1_idx` (`Animals_Name` ASC) VISIBLE,
103 CONSTRAINT `fk_WorkersAnimals_Animals1`
104 FOREIGN KEY (`Animals_Name`)
105 REFERENCES `mydb`.`Animals` (`Name`)
106 ON DELETE NO ACTION
107 ON UPDATE NO ACTION)
108ENGINE = InnoDB;
109
110
111-- -----------------------------------------------------
112-- Table `mydb`.`StockFoods`
113-- -----------------------------------------------------
114CREATE TABLE IF NOT EXISTS `mydb`.`StockFoods` (
115 `Stock_Number` INT NOT NULL,
116 `Foods_Number` INT NOT NULL,
117 PRIMARY KEY (`Stock_Number`, `Foods_Number`),
118 INDEX `fk_StockFoods_Foods1_idx` (`Foods_Number` ASC) VISIBLE,
119 CONSTRAINT `fk_StockFoods_Foods1`
120 FOREIGN KEY (`Foods_Number`)
121 REFERENCES `mydb`.`Foods` (`Number`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127SET SQL_MODE=@OLD_SQL_MODE;
128SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
129SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
130
131INSERT INTO Supplier VALUES
132('Apples', '344466', '111111111'),
133('Beef', '112333', '123123'),
134('Mosquitoes', '88005553535', '11');
135
136
137INSERT INTO Stock VALUES
138(1, 1, 10000, 'Ivan'),
139(2, 1, 15000, 'Ivan'),
140(3, 2, 15500, 'Ivan');
141
142INSERT INTO Animals VALUES
143('Rabbit', 'Mammal', 10, 4),
144('Lion', 'Predator', 4,10),
145('Frog', 'Amphibian', 8, 35);
146
147
148INSERT INTO Workers VALUES
149('Ivan', 'Stock Watcher'),
150('Igor', 'Lion Feeder'),
151('Tanya', 'Order constructor')