Escreva os comandos SQL correspondentes para resolver as consultas e aes solicitadas a seguir

Fatec Sorocaba Curso de Anlise e Desenvolvimento de Sistemas

Projeto da Disciplina de Laboratrio de Banco de Dados - 1/2015 Prof. M.Anglica

Tema: Base de dados de contas correntes de um banco.Instrues: Grupo de 4 alunosData de entrega: 08/06/2015 durante a aula de LBDForma de entrega: Documento impresso contendo:

Nome dos integrantes, Modelo E-R, scripts das tabelas com as constraints, comandos SQL, Stored Procedures e Triggers solicitados.

Tarefas:1- Desenvolver um modelo E-R corresponde ao modelo lgico abaixo.

2- Crie as tabelas necessrias definido nome para as constraints

3- Para cada tabela incluir dados significativos. Pelo menos 5 linhas por tabela.

Escreva os comandos SQL correspondentes para resolver as consultas e aes solicitadas a seguir.

CREATE TABLE Movimento

Obs: Tb_cliente a coluna TipoCli pode assumir os valores: "Especial","Ouro",Padro

Tb_contacorrente a coluna SituaoConta pode assumir os valores:

"N"- Saldo negativo e "E" - Conta encerrada.

Comandos SQL1- Alterar o contedo da coluna tipocli para Especial para o cliente com identificao = 045;

2- Excluir os movimentos onde a data do movimento(DtMov) seja do ano passado (usar a data do sistema).

3- Listar o nmero da conta, data de abertura e situao para todas as contas onde o saldo estiver entre 1.000,00 e 5.000,00 reais.

4- Listar o nome dos clientes, endereo e cidade para todos os clientes que moram em Campinas ou Sorocaba ordenado pela cidade e depois pelo nome do cliente.

5- Exibir a soma dos valor dos movimentos do tipo C efetuados para a conta de numero 565 no ms de maio de 2015.

6- Listar o nmero da conta, saldo da conta e o tipo e valor de todos os movimentos associados ela.

7- Listar o nome do cliente , nmero da conta, o saldo e o limite do cheque especial de cada conta do cliente de cdigo 10;

8- Exibir o id cliente e a quantidade de contas que ele possui.

9- Exibir o id e o nome do cliente que tem mais de 3 contas no banco.

10- Exibir o nome do cliente que tem o maior valor de saldo (calculado pela soma do saldo de todas as suas contas).

11- Excluir todos os movimentos das contas em que a situao seja 'E' - encerrada.10- Stored Procedures a- Escreva uma Stored Procedure que receba como parmetro um nmero de conta corrente e verifique se ela est "estourada". A conta est estourada quando o saldo for negativo e maior que o limite do cheque especial. Neste caso, gravar mensagem na tabela de LOG: 'Limite do cheque especial excedido - avisar cliente' concatenado com o nome do cliente e telefone do mesmo. Tratar a exceo caso o cdigo da conta no exista.

Tabela de LOG (datalog date, mensagem varchar2(100))

b-Escreva uma Stored Procedure que receba como parmetro um cdigo de cliente e some o valor total do saldo de todas as suas contas. Se o total for maior que 50.000 reais atualizar o campo tipoCLI da tabela de cliente movendo Cliente ESPECIAL para este campo. Caso contrrio, atualizar o campo TipoCli movendo Cliente PADRO.

11- Funo a- Escreva uma funo que receba como parmetro o Nmero da Conta Corrente.

Testar: se a data de abertura estiver dentro dos ltimos 2 meses (usar a data do sistema) e LimiteCheqEspecial igual a zero, devolver o texto Cliente Novo verificar cheque especial. Indique um comando para testar esta funo.

12- Triggers

a- Criar um trigger que ao ser includo um novo cliente, substitua a data inserida no campo DataCadastro pela data do sistema, e disponha o texto Cliente Novo no campo TipoCli.

b- Criar um trigger que ao ser atualizado o campo ValorMov da TB_Movimento gravar em uma Tabela de LOG (script abaixo) as informaes NrConta, Saldo da Conta, TipoMov, ValorMov antigo, ValorMov novo e o usurio que disparou a ao.

Create table TABLOG

( Nrlog identity, NrConta number, Saldo Number(12,2) , TipoMov char(1), ValorAntigo number(12,2) ValorNovo number(12,2), usurio varchar2(15));

obs: Nrlog uma sequence.TB_ContaCorrente

NrConta NUMBER PRIMARY KEY, DtAbertura DATE NOT NULL,

SituacaoConta CHAR(3) check ('N','E')

SaldoConta NUMBER (12,2),

EmitirTalo CHAR(1),

LimiteCheqEspecial NUMBER(12,2)

DataEncerramento DATE NOT NULL

TB_CliConta

IdCliente INT REFERENCES Cliente,

NrConta INTREFERENCES Conta,

PRIMARY KEY (CdCliente, NrConta) )

Tb_Cliente

IdCliente NUMBER PRIMARY KEY,

NomeCliente VARCHAR2(40) ,

EndCliente VARCHAR2(50) ,

Cidade VARCHAR2 (20),

Telefone Varchar2(20)

TipoCli VARCHAR2 (20),

DataCadastro DATETIME

TipoCliente

TB_Movimento

NrMov NUMBERPRIMARY KEY,

NrConta NUMBERNOT NULL,

DtMov DATENOT NULL,

TipoMov CHAR(1)NOT NULL

CHECK ( TpMov IN( 'C', 'D' ) ),

ValorMov NUMBER(12,2) NOT NULL

DsMov VARCHAR2(20) NOT NULL ;