-
Notifications
You must be signed in to change notification settings - Fork 0
/
Meteo.sql
67 lines (53 loc) · 2.1 KB
/
Meteo.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
CREATE TABLE `users` (
`id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`location_id` INT,
`login` VARCHAR(32) UNIQUE NOT NULL,
`password` VARCHAR(128) NOT NULL,
`group_id` INT
);
CREATE TABLE `groups` (
`group_id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`group_name` VARCHAR(20) UNIQUE NOT NULL,
`group_level` INT NOT NULL
);
CREATE TABLE `locations` (
`location_id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`location_name` VARCHAR(128)
);
CREATE TABLE `stations` (
`station_id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`location_id` INT NOT NULL,
`temperature` INT,
`humidity` INT,
`pressure` INT,
`wind_speed` INT,
`wind_direction_id` INT,
`precipitation_id` INT
);
CREATE TABLE `indications_history` (
`indication_id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`station_id` INT NOT NULL,
`time` TIMESTAMP NOT NULL,
`temperature` INT,
`humidity` INT,
`pressure` INT,
`wind speed` INT,
`wind_direction_id` INT,
`precipitation_id` INT
);
CREATE TABLE `precipitations` (
`precipitation_id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`precipitation` VARHCAR(64)
);
CREATE TABLE `wind_directions` (
`wind_direction_id` INT UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
`wind_direction` VARCHAR(32)
);
ALTER TABLE `users` ADD FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`);
ALTER TABLE `users` ADD FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`);
ALTER TABLE `stations` ADD FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`);
ALTER TABLE `indications_history` ADD FOREIGN KEY (`wind_direction_id`) REFERENCES `wind_directions` (`wind_direction_id`);
ALTER TABLE `stations` ADD FOREIGN KEY (`wind_direction_id`) REFERENCES `wind_directions` (`wind_direction_id`);
ALTER TABLE `indications_history` ADD FOREIGN KEY (`precipitation_id`) REFERENCES `precipitations` (`precipitation_id`);
ALTER TABLE `stations` ADD FOREIGN KEY (`precipitation_id`) REFERENCES `precipitations` (`precipitation_id`);
ALTER TABLE `indications_history` ADD FOREIGN KEY (`station_id`) REFERENCES `stations` (`station_id`);