-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreate.sql
213 lines (177 loc) · 11 KB
/
create.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
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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
create table orcamento ( id integer primary key
, valor numeric(10, 2) not null
, datahoraorcamento timestamp without time zone not null default agora()
);
create table material ( id integer primary key
, brevedescricao varchar(60) not null check (brevedescricao <> '')
);
create table orcamentosmateriais ( id integer primary key
, quantidade integer not null check (quantidade > 0)
, preco numeric(10, 2) not null check (preco > 0)
, codorc integer
, codmat integer
);
-- codorc references orcamento (id)
-- alter table orcamentosmateriais add constraint codorc_fkey foreign key (codorc) references orcamento (id);
-- codmat references material (id)
-- alter table orcamentosmateriais add constraint codmat_fkey foreign key (codmat) references material (id);
create table homologado ( id integer primary key
, codass integer
, codaloc integer
, codmat integer
, codcoord integer
);
-- codass references assinatura (id)
-- alter table homologado add constraint codass_fkey foreign key (codass) references assinatura (id);
-- codaloc references alocacao (id)
-- alter table homologado add constraint codaloc_fkey foreign key (codaloc) references alocacao (id);
-- codmat references material (id)
-- alter table homologado add constraint codmat_fkey foreign key (codmat) references material (id);
-- codcoord references coordenador (id)
-- alter table homologado add constraint codcoord_fkey foreign key (codcoord) references coordenador (id);
create table local ( id integer primary key
, andar integer not null
, setor varchar(2) not null check (setor <> '')
, sala integer not null
);
-- alter table local add constraint localizacaounica unique(andar)
-- alter table local add constraint localizacaounica unique(setor)
-- alter table local add constraint localizacaounica unique(sala)
create table alocacao ( id integer primary key
, datahoraalocacao timestamp without time zone not null default agora()
, codlocal integer
);
-- codlocal references local (id)
-- alter table alocacao add constraint codlocal_fkey foreign key (codlocal) references local (id);
-- codhom references homologado (id)
-- alter table alocacao add constraint codhom_fkey foreign key (codhom) references homologado (id);
create table assinatura ( id integer primary key
, datahoraassinatura timestamp without time zone not null default agora()
, codfun integer
);
-- codfun references funcionario (id)
-- alter table assinatura add constraint codfun_fkey foreign key (codfun) references funcionario (id);
-- codhomo references homologado (id)
-- alter table assinatura add constraint codfun_fkey foreign key (codhomo) references homologado (id);
create table funcionario ( id integer primary key
, nome varchar(60) not null check (nome <> '')
, idade integer not null
, salario numeric(10,2) not null
, datahoraingresso timestamp without time zone not null default agora()
, datahoraegresso timestamp without time zone
);
-- create table alteracoes_funcionario ( id integer primary key
-- , ultima_vez_data timestamp
-- , ultima_vez_usuario varchar(60)
-- );
-- alter table alteracoes_funcionario add constraint logfun_fkey foreign key (logfunfk) references funcionario (id);
create table funcionariolocal ( id integer primary key
, codfun integer
, codlocal integer
);
-- codfun references funcionario (id)
-- alter table funcionariolocal add constraint codfun_fkey foreign key (codfun) references funcionario (id);
-- codlocal references local (id)
-- alter table funcionariolocal add constraint codlocal_fkey foreign key (codlocal) references local (id);
create table projeto ( id integer primary key
, brevedescricao varchar(60) not null
, datahorainicio timestamp without time zone not null default agora()
, datahorafim timestamp without time zone not null
);
create table participacaoprojeto ( id integer primary key
, datahorainicio timestamp without time zone not null default agora()
, datahorafim timestamp without time zone not null
, codfun integer
, codproj integer
);
-- codfun references funcionario (id)
-- alter table participacaoprojeto add constraint codfun_fkey foreign key (codfun) references funcionario (id);
-- codproj references projeto (id)
-- alter table participacaoprojeto add constraint codproj_fkey foreign key (codproj) references projeto (id);
create table tarefa ( id integer primary key
, brevedescricao varchar(60)
, estado varchar(60) check (estado in ( 'AGUARDANDO DESENVOLVIMENTO'
, 'EM DESENVOLVIMENTO'
, 'AGUARDANDO ANALISE'
, 'EM ANALISE'
))
, prioridade varchar(60) check (prioridade in ( 'BAIXA'
, 'MEDIA'
, 'ALTA'
))
, datahorainicial timestamp without time zone not null default agora()
, datahorafinal timestamp without time zone not null
, codsubtar integer
, codproj integer
, codfundono integer
);
-- tarefa weak entity, must have the id of project identifying tarefa
-- codfun references funcionario (id)
-- alter table tarefa add constraint codfun_fkey foreign key (codfun) references projeto (id);
-- codproj references projeto (id)
-- alter table tarefa add constraint codproj_fkey foreign key (codproj) references projeto (id);
-- codsubtar references tarefa (id)
-- alter table tarefa add constraint codsubtar_fkey foreign key (codsubtar) references tarefa (id);
-- codfundono references funcionario (id)
-- alter table tarefa add constraint codfundono_fkey foreign key (codfundono) references funcionario (id);
create table participacaotarefa ( id integer primary key
, codfun integer
, codtar integer
, codsubtar integer
);
-- codsubtar references tarefa (id)
-- alter table participacaotarefa add constraint codsubtar_fkey foreign key (codsubtar) references tarefa (id);
-- codfund references funcionario (id)
-- alter table participacaotarefa add constraint codfun_fkey foreign key (codfun) references funcionario (id);
-- codtar references tarefa (id)
-- alter table participacaotarefa add constraint codsubtar_fkey foreign key (codsubtar) references tarefa (id);
create table coordenador ( id integer primary key
, designacao varchar(60) not null check (designacao in ( 'D1'
, 'D2'
, 'D3'
, 'D4'
, 'D5'
))
, codfun integer
);
-- codfun references funcionario (id)
-- alter table coordenador add constraint codfun_fkey foreign key (codfun) references funcionario (id);
create table coordenadorprojeto ( id integer primary key
, codcoord integer
, codproj integer
);
-- codcoord references coordenador (id)
-- alter table coordenadorprojeto add constraint codcoord_fkey foreign key (codcoord) references coordenador (id);
-- codproj references projeto (id)
-- alter table coordenadorprojeto add constraint codproj_fkey foreign key (codproj) references projeto (id);
create table programador ( id integer primary key
, rank varchar(60) not null check (rank in ( 'R1'
, 'R2'
, 'R3'
))
, codfun integer
);
-- codfun references funcionario (id)
-- alter table programador add constraint codfun_fkey foreign key (codfun) references funcionario (id);
create table linguagem ( id integer primary key
, nome varchar(60) not null check (nome in ( 'C'
, 'CPP'
, 'PERL'
, 'RUBY'
, 'JAVA'
, 'SHELL'
, 'PYTHON'
, 'GROOVY'
, 'CLOJURE'
, 'JAVASCRIPT'
))
);
create table programadorlinguagem ( id integer primary key
, dominio varchar(60) not null check (dominio <> '')
, codprog integer
, codling integer
);
-- codprog references programador (id)
-- alter table programadorlinguagem add constraint codprog_fkey foreign key (codprog) references programador (id);
-- codling references linguagem (id)
-- alter table programadorlinguagem add constraint codling_fkey foreign key (codling) references linguagem (id);