O Agostinho MapaMental

-- Agostinho Astori, Matheus Jacobsen

create table cliente

(

matricula integer primary key,

nome varchar(45) not null,

e_mail varchar(45) not null,

celular varchar(14) not null,

telefone varchar(14) not null,

dt_nascimento date not null,

sexo char not null,

cpf varchar(15) not null

);

create table posto

(

idposto integer primary key,

nome_posto varchar(50) not null,

logradouro varchar(70) not null,

numero varchar(10) not null,

complemento varchar(45) not null,

cidade varchar(60) not null,

uf varchar(2) not null

);

create table plano_saude

(

idplano integer primary key,

nome_plano varchar(45) not null,

percentual_cliente numeric not null

);

create table material

(

idmaterial char(3) primary key,

nome_material varchar(45) not null

);

create table exame

(

idexame integer primary key,

nome_exame varchar(45) not null,

material_idmaterial char(3) references material(idmaterial)

);

create table exame_autorizado

(

necessidade_autorizacao_prev integer not null,

ch numeric not null,

plano_saude_idplano integer references plano_saude(idplano),

exame_idexame integer references exame(idexame),

primary key(plano_saude_idplano, exame_idexame)

);

create table us

(

dt_inicio_vigencia date not null,

dt_fim_vigencia date not null,

valor numeric not null,

plano_saude_idplano integer references plano_saude(idplano),

primary key(plano_saude_idplano)

);

create table fatura

(

nr_fatura integer primary key,

dt_emissao_fatura date not null,

dt_prevista_pagamento date not null,

dt_pagamento date not null,

plano_saude_idplano integer references plano_saude(idplano)

);

create table pedido

(

nr_pedido integer primary key,

dt_pedido date not null,

valor_pago_cliente numeric not null,

posto_saude_idposto integer references posto(idposto),

cliente_matricula integer references cliente(matricula),

plano_saude_idplano integer references plano_saude(idplano)

);

create table exame_pedido

(

idExame_pedido integer primary key,

dt_coleta date not null,

valor numeric not null,

autorizacao_plano bigint not null,

fatura_nr_fatura integer not null,

pedido_nr_pedido integer references pedido(nr_pedido),

exame_idexame integer references exame(idexame)

);

#########################################################################

1

create or replace procedure novo_pedido(in numPed integer, in dataPed date, in idPosto integer, in cmat integer, in idPlan integer)

language pLpgsql as $$

declare

begin

insert into pedido (cliente_matricula, dt_pedido, nr_pedido, plano_saude_idplano, posto_saude_idposto, valor_pago_cliente)

values (cmat, dataPed, numPed, idPlan, idPosto, 0);

end

$$

#########################################################################

2

create or replace procedure