bd/create.sql at main · GSNimbus/bd · GitHub

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
create table t_nimbus_alerta (
   id_alerta      number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL ,
   ds_risco       varchar2(20) not null,
   ds_tipo        varchar2(20) not null,
   ds_mensagem    varchar2(250) not null,
   horario_alerta date not null,
   id_bairro      number(9) not null
);

alter table t_nimbus_alerta add constraint t_nimbus_alerta_pk primary key ( id_alerta );

create table t_nimbus_localizacao (
   id_localizacao number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nr_longitude   number(8, 5) not null,
   nr_latitude    number(7, 5) not null
);


alter table t_nimbus_localizacao add constraint t_nimbus_localizacao_pk primary key ( id_localizacao );

create table t_nimbus_bairro (
   id_bairro      number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_bairro  varchar2(100) not null,
   id_cidade      number(9) not null,
   id_localizacao number(9) not null
);


alter table t_nimbus_bairro add constraint t_nimbus_bairro_pk primary key ( id_bairro );
-- alter table t_nimbus_bairro add constraint t_nimbus_bairro_fk foreign key (id_localizacao) references t_nimbus_localizacao(id_localizacao);

create table t_nimbus_cidade (
   id_cidade number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_cidade varchar2(100) not null,
   id_estado number(9) not null
);

alter table t_nimbus_cidade add constraint t_nimbus_cidade_pk primary key ( id_cidade );

create table t_nimbus_endereco (
   id_endereco number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nr_cep      char(8) not null,
   id_bairro   number(9) not null,
   nm_logradouro  varchar2(100) not null,
   nr_logradouro   number(5)
);

alter table t_nimbus_endereco add constraint t_nimbus_endereco_pk primary key ( id_endereco );

alter table t_nimbus_endereco
   add constraint ck_cep check ( length(nr_cep) = 8 );

create table t_nimbus_estado (
   id_estado number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_estado varchar2(50) not null,
   id_pais   number(9) not null
);

alter table t_nimbus_estado add constraint t_nimbus_estado_pk primary key ( id_estado );

create table t_nimbus_gp_endereco (
   id_gp_endereco number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_grupo       varchar2(100) not null,
   id_usuario     number(9) not null,
   id_endereco    number(9) not null
);

alter table t_nimbus_gp_endereco add constraint t_nimbus_gp_endereco_pk primary key ( id_gp_endereco );


create table t_nimbus_pais (
   id_pais number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_pais varchar2(50) not null
);

alter table t_nimbus_pais add constraint t_nimbus_pais_pk primary key ( id_pais );

create table t_nimbus_previsao (
   id_previsao         number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_hora             varchar2(50) not null,
   nr_temperatura      number(20) not null,
   nr_chuva            number(20) not null,
   nr_codigo_chuva     number(20) not null,
   nr_velocidade_vento number(20) not null,
   nr_rajada_vento     number(20) not null,
   nr_umidade          number(20) not null,
   id_bairro           number(9) not null
);

alter table t_nimbus_previsao add constraint t_nimbus_previsao_pk primary key ( id_previsao );

create table t_nimbus_usuario (
   id_usuario number(9) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
   nm_usuario varchar2(50) not null,
   ds_email   varchar2(50) not null,
   senha      varchar2(12) not null
);

alter table t_nimbus_usuario add constraint t_nimbus_usuario_pk primary key ( id_usuario );

alter table t_nimbus_estado
   add constraint relation_1 foreign key ( id_pais )
      references t_nimbus_pais ( id_pais );

alter table t_nimbus_gp_endereco
   add constraint relation_10 foreign key ( id_endereco )
      references t_nimbus_endereco ( id_endereco );

alter table t_nimbus_cidade
   add constraint relation_2 foreign key ( id_estado )
      references t_nimbus_estado ( id_estado );

alter table t_nimbus_previsao
   add constraint relation_23 foreign key ( id_bairro )
      references t_nimbus_bairro ( id_bairro );

alter table t_nimbus_alerta
   add constraint relation_24 foreign key ( id_bairro )
      references t_nimbus_bairro ( id_bairro );

alter table t_nimbus_bairro
   add constraint relation_3 foreign key ( id_cidade )
      references t_nimbus_cidade ( id_cidade );

alter table t_nimbus_endereco
   add constraint relation_4 foreign key ( id_bairro )
      references t_nimbus_bairro ( id_bairro );

alter table t_nimbus_bairro
   add constraint relation_7 foreign key ( id_localizacao )
      references t_nimbus_localizacao ( id_localizacao );

alter table t_nimbus_gp_endereco
   add constraint relation_9 foreign key ( id_usuario )
      references t_nimbus_usuario ( id_usuario );