· 9 years ago · Nov 20, 2016, 08:10 PM
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 mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`country`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`country` (
21 `country_id` INT NOT NULL AUTO_INCREMENT,
22 `country_name` VARCHAR(45) NULL,
23 PRIMARY KEY (`country_id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `mydb`.`city`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mydb`.`city` (
31 `city_id` INT NOT NULL AUTO_INCREMENT,
32 `country_id` INT NOT NULL,
33 `city_name` VARCHAR(45) NULL,
34 PRIMARY KEY (`city_id`),
35 INDEX `fk_city_country1_idx` (`country_id` ASC),
36 CONSTRAINT `fk_city_country1`
37 FOREIGN KEY (`country_id`)
38 REFERENCES `mydb`.`country` (`country_id`)
39 ON DELETE NO ACTION
40 ON UPDATE NO ACTION)
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `mydb`.`users`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `mydb`.`users` (
48 `id` INT NOT NULL AUTO_INCREMENT,
49 `username` VARCHAR(25) NOT NULL,
50 `password` VARCHAR(64) NOT NULL,
51 `salt` VARCHAR(16) NOT NULL,
52 PRIMARY KEY (`id`),
53 UNIQUE INDEX `username_UNIQUE` (`username` ASC))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `mydb`.`temperature`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `mydb`.`temperature` (
61 `date` DATETIME NOT NULL,
62 `city_id` INT NOT NULL,
63 `T` DOUBLE NULL,
64 `Tn` DOUBLE NULL,
65 `Tx` DOUBLE NULL,
66 `Td` DOUBLE NULL,
67 `Tg` DOUBLE NULL,
68 PRIMARY KEY (`city_id`, `date`),
69 INDEX `fk_temperature_city1_idx` (`city_id` ASC),
70 CONSTRAINT `fk_temperature_city1`
71 FOREIGN KEY (`city_id`)
72 REFERENCES `mydb`.`city` (`city_id`)
73 ON DELETE NO ACTION
74 ON UPDATE NO ACTION)
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`other_weather_data`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `mydb`.`other_weather_data` (
82 `date` DATETIME NOT NULL,
83 `city_id` INT NOT NULL,
84 `U` INT NULL,
85 `VV` DOUBLE NULL,
86 `RRR` VARCHAR(45) NULL,
87 `tR` INT NULL,
88 `E` VARCHAR(45) NULL,
89 `E_` VARCHAR(45) NULL,
90 `sss` VARCHAR(45) NULL,
91 PRIMARY KEY (`city_id`, `date`),
92 INDEX `fk_other_weather_data_city2_idx` (`city_id` ASC),
93 CONSTRAINT `fk_other_weather_data_city2`
94 FOREIGN KEY (`city_id`)
95 REFERENCES `mydb`.`city` (`city_id`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION)
98ENGINE = InnoDB;
99
100
101-- -----------------------------------------------------
102-- Table `mydb`.`pressure`
103-- -----------------------------------------------------
104CREATE TABLE IF NOT EXISTS `mydb`.`pressure` (
105 `date` DATETIME NOT NULL,
106 `city_id` INT NOT NULL,
107 `P0` DOUBLE NULL,
108 `P` DOUBLE NULL,
109 `Pa` DOUBLE NULL,
110 PRIMARY KEY (`city_id`, `date`),
111 INDEX `fk_pressure_city1_idx` (`city_id` ASC),
112 CONSTRAINT `fk_pressure_city1`
113 FOREIGN KEY (`city_id`)
114 REFERENCES `mydb`.`city` (`city_id`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION)
117ENGINE = InnoDB;
118
119
120-- -----------------------------------------------------
121-- Table `mydb`.`weather`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `mydb`.`weather` (
124 `date` DATETIME NOT NULL,
125 `city_id` INT NOT NULL,
126 `WW` VARCHAR(45) NULL,
127 `W1` VARCHAR(45) NULL,
128 `W2` VARCHAR(45) NULL,
129 PRIMARY KEY (`city_id`, `date`),
130 INDEX `fk_weather_city1_idx` (`city_id` ASC),
131 CONSTRAINT `fk_weather_city1`
132 FOREIGN KEY (`city_id`)
133 REFERENCES `mydb`.`city` (`city_id`)
134 ON DELETE NO ACTION
135 ON UPDATE NO ACTION)
136ENGINE = InnoDB;
137
138
139-- -----------------------------------------------------
140-- Table `mydb`.`clouds`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `mydb`.`clouds` (
143 `date` DATETIME NOT NULL,
144 `city_id` INT NOT NULL,
145 `N` INT NULL,
146 `Cl` VARCHAR(45) NULL,
147 `Nh` INT NULL,
148 `H` VARCHAR(45) NULL,
149 `Cm` VARCHAR(45) NULL,
150 `Ch` VARCHAR(45) NULL,
151 PRIMARY KEY (`city_id`, `date`),
152 INDEX `fk_clouds_city1_idx` (`city_id` ASC),
153 CONSTRAINT `fk_clouds_city1`
154 FOREIGN KEY (`city_id`)
155 REFERENCES `mydb`.`city` (`city_id`)
156 ON DELETE NO ACTION
157 ON UPDATE NO ACTION)
158ENGINE = InnoDB;
159
160
161-- -----------------------------------------------------
162-- Table `mydb`.`wind`
163-- -----------------------------------------------------
164CREATE TABLE IF NOT EXISTS `mydb`.`wind` (
165 `date` DATETIME NOT NULL,
166 `city_id` INT NOT NULL,
167 `DD` VARCHAR(45) NULL,
168 `Ff` VARCHAR(45) NULL,
169 `ff10` DOUBLE NULL,
170 `ff3` DOUBLE NULL,
171 PRIMARY KEY (`city_id`, `date`),
172 INDEX `fk_wind_city1_idx` (`city_id` ASC),
173 CONSTRAINT `fk_wind_city1`
174 FOREIGN KEY (`city_id`)
175 REFERENCES `mydb`.`city` (`city_id`)
176 ON DELETE NO ACTION
177 ON UPDATE NO ACTION)
178ENGINE = InnoDB;
179
180
181SET SQL_MODE=@OLD_SQL_MODE;
182SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
183SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;