Estrutura do banco

Prévia do material em texto

Estrutura do banco:
Create database locadora;
Create table tblcidade (
cidadeid INT,
cidade VARCHAR (100),
ufid INT,
PRIMARY KEY (cidadeid) )
create table tbluf (
ufid INT,
uf varchar (45),
PRIMARY KEY (ufid) )
create table tbltipocliente (
tipoclienteid INT,
tipocliente varchar (45),
PRIMARY KEY (tipoclienteid) )
create table tblcliente (
clienteid INT,
cliente varchar(100),
endereco varchar(200),
cidadeid INT,
tipoclienteid INT,
PRIMARY KEY (clienteid))
create table tblocacao (
clienteid INT,
filmeid INT,
datalocacao DATE,
dtadevolucao DATE,
valorcobrado FLOAT,
valorpago FLOAT,
PRIMARY KEY (clienteid, filmeid))
create table tblfilme (
filmeid INT,
filme varchar(45),
valor float,
diasaluguel int,
PRIMARY KEY (filmeid) )
CHAVES PRIMÁRIAS
ALTER TABLE tblcidade ADD FOREIGN KEY (ufid)
REFERENCES tbluf (ufid);
ALTER TABLE tblcliente ADD FOREIGN KEY (cidadeid)
REFERENCES tblcidade (cidadeid);
ALTER TABLE tblcliente ADD FOREIGN KEY (tipoclienteid)
REFERENCES tbltipocliente (tipoclienteid);
ALTER TABLE tbllocacao ADD FOREIGN KEY (clienteid)
REFERENCES tblcliente (clienteid);
ALTER TABLE tbllocacao ADD FOREIGN KEY (filmeid)
REFERENCES tblfilme (filmeid);
CRIANDO INDICES
create index ind_cidade on tblcidade (cidade);
create index ind_ufid on tblcidade (ufid);
create index ind_uf on tbluf (uf);
create index ind_tipocliente on tbltipocliente (tipocliente);
create index ind_cliente on tblcliente (cliente);
create index ind_cidade on tblcliente (cidade);
create index ind_tipoclienteid on tblcliente (tipoclienteid);
create index fkfilmeid_idx on tbllocacao (filmeid);
create index fkcliente_idx on tbllocacao (clienteid);
create index ind_datalocacao on tbllocacao (datalocacao);
create index ind_dtadevolucao on tbllocacao (dtadevolucao);
create index ind_valorcobrado on tbllocacao (valorcobrado);
create index ind_valorpago on tbllocacao (valorpago);
create index ind_filme on tblfilme (filme);
create index ind_diasaluguel on tblfilme (diasaluguel);
INSERINDO DADOS
insert into tblcidade (cidadeid, cidade, ufid) values (01, ‘São Luis’, 0101);
insert into tbluf (ufid, uf) values (0101, MA);
insert into tbltipocliente (tipoclienteid, tipocliente) values (234, ‘Pessoa Física’);
insert into tblcliente (clienteid, cliente, endereço, cidadeid, tipocliente) values (7890, ‘Genésio Amaral Santos’, ‘Rua do Apeadouro, Nº01, João Paulo’, 234);
insert into tbllocacao (clienteid, filmeid, datalocacao, dtadevolucao, valorcobrado, valorpago) values (7890, 1001, ’05-11-2014’, ’05-13-2014’, 7.00, 7.00);
insert into tblfilme (filmeid, filme, valor, diasaluguel) values (1001, ‘X-Men’, 7.00, 2);
CRIANDO TABELA DE LOG
create table tblcliente_log (
clienteid INT,
cliente varchar(100),
endereco varchar(200),
cidadeid INT,
tipoclienteid INT,
data date,
autor varchar(20),
alteracão varchar(6) )
create table tblfilme_log (
filmeid INT,
filme varchar(45),
valor float,
diasaluguel int,
data date,
autor varchar(20),
alteracão varchar(6) )
TRIGGER
Trigger da tebela “tblcliente”
create function gera_log () returns trigger as
$$
Begin
insert into tblcliente_log select NEW.*, now(), user, TG_OP;
return new;
end;
$$
language plpgsql;
create trigger tr_gera_log after insert or update or delete 
on tblcliente
for each row execute procedure 
gera_log();
Trigger da tebela “tblfilme”
create function gera_log2 () returns trigger as
$$
Begin
insert into tblcliente_log select NEW.*, now(), user, TG_OP;
return new;
end;
$$
language plpgsql;
create trigger tr_gera_log2 after insert or update or delete 
on tblfilme
for each row execute procedure 
gera_log2();
VALOR DA LOCAÇÃO COM BASEADA NA QUANTIDADE DE DIAS ALUGADOS
create function "valor_locacao"(IN DATE, IN float) returns tblocacao as
$$
Begin
select ((dtadevolucao - datalocacao) * 3.50) 
from tblocacao;
end;
$$
language plpgsql;
Obs: ao utilizar somente o select, um resultado foi retornado ao final da operação. Após criar a função o resultado não foi impresso na tela.
CADASTRAR INCLUIR DADOS NAS TABELAS
CREATE OR REPLACE FUNCTION inserir()
 RETURNS tblocacao AS
$$
begin 
insert into tblocacao values (123, 1001, '2014-06-07', '2014-06-09', 7.00, 7.00);
end;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION inserir2()
 RETURNS tblcliente AS
$$
begin 
insert into tblcliente values (234, ‘Genésio’, ‘Turu’, 1234, 01);
end;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION inserir()
 RETURNS tblfilme AS
$$
begin 
insert into tblfilme values (1001, ‘X-Men’, 7.00, 2,);
end;
$$
LANGUAGE plpgsql;
RETORNA O NOME DO FILME, QUANTIDADE DE LOCAÇÕES E O VALOR QUE O FILME JÁ FATUROU.