APS II SQL (Projeto Banco de Dados Para Uma Imobiliária)

Prévia do material em texto

Ministério da Educação
UNIVERSIDADE TECNOLÓGICA FEDERAL DO PARANÁ
Campus Pato Branco
COADS – Coordenação de Tecnologia em Análise e Desenvolvimento de Sistemas
Projeto Banco de Dados
 APS II
Pato Branco- PR
Ministério da Educação
UNIVERSIDADE TECNOLÓGICA FEDERAL DO PARANÁ
Campus Pato Branco
COADS – Coordenação de Tecnologia em Análise e Desenvolvimento de Sistemas
Projeto Banco de Dados Para Uma Agência 
de Viagens
Giovani Tognon
Luis Henrique Stormoski
 Felipe Figueiredo Gabriel
Projeto realizado para APS de Banco de 
Dados, com intuito de exercitar as aulas
 de SQL e Comandos DDL, ministrada 
pelo Professor Marcelo Teixeira
Pato Branco - PR
Laboratório de Informática V107 – Comandos SQL DDL 
 
Luis Henrique Stormoski – Turma 2S – e-mail: stormoski@alunos.utfpr.edu.br
Giovani Tognon – Turma 2S – e-mail: giovani123@gmail.com
Felipe Figueiredo Gabriel – Turma 2S – e-mail: felipefg.93@gmail.com
 
Banco de Dados – Departamento de Análise e Desenvolvimento de Sistemas 
Universidade Tecnológica Federal do Paraná – Pato Branco 
1. Banco de Dados de uma Imobiliária
Este banco de dados tem por objetivo estruturar de forma simplifica e simples de como
seria um banco de dados de uma empresa imobiliária, lembrando-se de este banco é um
rascunho de como poderia se dar inicio a um banco de dados.
 Corretor: esta entidade tem por objetivo armazenar os dados do corretor, esta entidade
serve de apoio na hora de uma transação armazenando dados do corretor em uma venda
ou locação.
 Cliente: esta tem por objetivo armazenar e guardar os dados dos clientes, bem como
usa-los em outras entidades em forma de relacionamentos.
 Localização Cliente: nesta entidade ficam armazenados os dados de endereços dos
clientes, esta entidade serve de apoio para não poluir e pesar tanto a tabela clientes.
 Tipo de Transação: serve para armazenar as operações feitas na imobiliária, bem como
quem as fez.
 Imóvel: armazenar os dados dos Imóveis.
 Localização Imóvel: idem da mesma função da localização clientes.
 Tipo Imóvel: guarda dados do tipo de imóvel, bem como tipo, estrutura,proprietário.
Modelo DER para a representação do Banco de Dados da
Empresa Agencia de Viagens
Modelo Lógico
Comandos DDL de Criação e Pesquisa
CREATE TABLE CORRETOR(
PK_CODCORRETOR INTEGER NOT NULL,
NOME VARCHAR(50),
ENDERECO VARCHAR(60),
CIDADE VARCHAR(20),
TELEFONE VARCHAR(22),
CONSTRAINT CONST_PK_CODCORRETOR PRIMARY KEY (PK_CODCORRETOR)
);
CREATE TABLE LOCALIZACAO_CLIENTE(
PK_CODLOC INTEGER NOT NULL,
ENDERECO VARCHAR(60),
CIDADE VARCHAR(20),
BAIRRO VARCHAR(15),
ESTADO VARCHAR(20),
CONSTRAINT CONST_PK_CODLOC PRIMARY KEY (PK_CODLOC)
);
CREATE TABLE LOCALIZACAO_IMOVEL(
PK_CODLOC INTEGER NOT NULL,
ENDERECO VARCHAR(20),
CIDADE VARCHAR(20),
BAIRRO VARCHAR(40),
ESTADO VARCHAR(20),
CONSTRAINT CONST_PK_ICODLOC PRIMARY KEY (PK_CODLOC)
);
CREATE TABLE TIPO_IMOVEL(
PK_CODIMOVEL INTEGER NOT NULL,
TIPO_IMOVEL VARCHAR(20),
ANO_CONSTRUCAO DATE,
CONSTRAINT CONST_PK_CODTIMOVEL PRIMARY KEY (PK_CODIMOVEL)
);
CREATE TABLE CLIENTE(
PK_CODCLIENTE INTEGER NOT NULL,
FK_LOCALIZACAO INTEGER NOT NULL,
NOME VARCHAR(50),
IDADE CHAR(2),
CPF CHAR(11),
PROFISSAO VARCHAR(20),
CONSTRAINT CONST_PK_CODCLIENTE PRIMARY KEY (PK_CODCLIENTE),
CONSTRAINT CONST_FK_LOCALIZACAO FOREIGN KEY (FK_LOCALIZACAO) REFERENCES 
LOCALIZACAO_CLIENTE (PK_CODLOC)
);
CREATE TABLE IMOVEL(
PK_CODIMOVEL INTEGER NOT NULL,
FK_CODLOC INTEGER NOT NULL,
FK_CODTIPO INTEGER NOT NULL,
STATUS VARCHAR(20),
PROPRIETARIO VARCHAR(60),
CONSTRAINT CONST_PK_CODIMOVEL PRIMARY KEY (PK_CODIMOVEL),
CONSTRAINT CONST_FK_CODLOC FOREIGN KEY (FK_CODLOC) REFERENCES 
LOCALIZACAO_IMOVEL (PK_CODLOC),
CONSTRAINT CONST_FK_CODTIPO FOREIGN KEY (FK_CODTIPO) REFERENCES TIPO_IMOVEL 
(PK_CODIMOVEL)
);
CREATE TABLE TIPO_TRANSACAO(
PK_CODTRANS INTEGER NOT NULL,
FK_CODCORRETOR INTEGER NOT NULL,
FK_CODCLIENTE INTEGER NOT NULL,
TIPO_TRANSACAO VARCHAR(20),
FORMA_PAGAMENTO VARCHAR(20),
VALOR NUMERIC(8, 2),
CONSTRAINT CONST_PK_CODTRANS PRIMARY KEY (PK_CODTRANS),
CONSTRAINT CONST_FK_CODCORRETOR FOREIGN KEY (FK_CODCORRETOR) REFERENCES 
CORRETOR (PK_CODCORRETOR),
CONSTRAINT CONST_FK_CODCLIENTE FOREIGN KEY (FK_CODCLIENTE) REFERENCES CLIENTE 
(PK_CODCLIENTE)
);
LOC CLIENTES
insert into Localizacao_CLIENTE VALUES (1, 'AVENIDA PARANA','PATO BRANCO','CENTRO','PR');
insert into Localizacao_CLIENTE VALUES (2, 'AVENIDA PANAMA','PATO BRAGADO','CENTRO','RS');
insert into Localizacao_CLIENTE VALUES (3, 'AVENIDA GUARANI','PALMAS','CENTRO','RS');
insert into Localizacao_CLIENTE VALUES (4, 'RUA DEP TOBIAS','VIDEIRA','VIDA','SC');
insert into Localizacao_CLIENTE VALUES (5, 'RUA TOCANTINS','PATO BRANCO','CENTRO','PR');
insert into Localizacao_CLIENTE VALUES (6, 'AVENIDA IPIRANGA','BENTO GONCALVES','CENTRO','RS');
insert into Localizacao_CLIENTE VALUES (7, 'RUA PARA','SAO PAULO','ZONA LESTE','SP');
insert into Localizacao_CLIENTE VALUES (8, 'RUA FORTALEZA','PATO BRANCO','PINHEIROS','PR');
LOC IMOVEL
insert into Localizacao_IMOVEL VALUES (1, 'AVENIDA PARANA','PATO BRANCO','CENTRO','PR');
insert into Localizacao_IMOVEL VALUES (2, 'AVENIDA PANAMA','PATO BRAGADO','CENTRO','RS');
insert into Localizacao_IMOVEL VALUES (3, 'AVENIDA GUARANI','PALMAS','CENTRO','RS');
insert into Localizacao_IMOVEL VALUES (4, 'RUA DEP TOBIAS','VIDEIRA','VIDA','SC');
insert into Localizacao_IMOVEL VALUES (5, 'RUA TOCANTINS','PATO BRANCO','CENTRO','PR');
insert into Localizacao_IMOVEL VALUES (6, 'AVENIDA IPIRANGA','BENTO GONCALVES','CENTRO','RS');
insert into Localizacao_IMOVEL VALUES (7, 'RUA PARA','SAO PAULO','ZONA LESTE','SP');
insert into Localizacao_IMOVEL VALUES (8, 'RUA FORTALEZA','PATO BRANCO','PINHEIROS','PR');
TIPO IMOVEL
insert into TIPO_IMOVEL VALUES (1, 'CASA MADEIRA','20.05.1987');
insert into TIPO_IMOVEL VALUES (2, 'CASA ALVENARIA','20.11.1999');
insert into TIPO_IMOVEL VALUES (3, 'APARTAMENTO','02.08.2012');
insert into TIPO_IMOVEL VALUES (4, 'CASA 2 PISOS','22.05.1997');
insert into TIPO_IMOVEL VALUES (5, 'CASA 3 PISOS','01.12.2017');
insert into TIPO_IMOVEL VALUES (6, 'APT COM SACADA','06.01.1990');
insert into TIPO_IMOVEL VALUES (7, 'CASA MADEIRA','28.02.1966');
insert into TIPO_IMOVEL VALUES (8, 'CASA ALVENARIA','10.08.1987');
TIPO TRANSAÇÃO
insert into TIPO_TRANSACAO VALUES (1,1,1,'FINANCIAMENTO','FINANCIAMENTO','15000000');
insert into TIPO_TRANSACAO VALUES (2,2,2,'COMPRA','TRANSFERENCIA','98000');
insert into TIPO_TRANSACAO VALUES (3,3,3,'ALUGA','BOLETO MENSAL','560000');
insert into TIPO_TRANSACAO VALUES (4,4,4,'ALUGA','BOLETO MENSAL','780000');
insert into TIPO_TRANSACAO VALUES (5,5,5,'ALUGA','BOLETO MENSAL','630000');
insert into TIPO_TRANSACAO VALUES (6,6,6,'COMPRA','CHEQUE','9600000');
insert into TIPO_TRANSACAO VALUES (7,7,7,'COMPRA','A VISTA','1000000');
insert into TIPO_TRANSACAO VALUES (8,8,8,'ALUGA','BOLETO MENSAL','400000');
CLIENTE
insert into CLIENTE VALUES (1,1, 'CELIA LAPA','30','12365898547','EMPRESARIA');
insert into CLIENTE VALUES (2,2, 'LUIZ ANTONIO','45','96589874587','APOSENTADO');
insert into CLIENTE VALUES (3,3, 'HUDSON DOS SANTOS','27','89963258741','ENGENHEIRO');
insert into CLIENTE VALUES (4,4, 'BRUNO WYLLIAN','28','11125896325','MEDICO');
insert into CLIENTE VALUES (5,5, 'EDNILSON FRANCISCO','30','78969836987','GERENTE BANCO');
insert into CLIENTE VALUES (6,6, 'EDSON CANALLI','28','88899966633','CONTADOR');
insert into CLIENTE VALUES (7,7, 'LUIZ HENRRIQUE','21','78552563214','ESTUDANTE');
insert into CLIENTE VALUES (8,8, 'MARCELO PROFESSOR','29','22223333965','PROFESSOR');
CORRETOR
insert into CORRETOR VALUES (1,'LAPA LUANDO','RUA TOCANTINS','PATO BRANCO','99002610');
insert into CORRETOR VALUES (2,'ANTONIO SILVEIRO','RUA ALAGOAS','PATO BRANCO','46-30251984');
insert into CORRETOR VALUES (3,'SANTOS CAMARGO','AVBRASIL','PATO BRANCO','99656558');
insert into CORRETOR VALUES (4,'WYLLIAN CHARLES','RUA MACEIO','PATO BRANCO','46-30265958');
insert into CORRETOR VALUES (5,'FRANCISCO SANTOS','RUA DAS FLORES','PATO 
BRANCO','91565587');
insert into CORRETOR VALUES (6,'CANALLI PACHECO','RUA BENTO SILVA','PATO 
BRANCO','966936521');
insert into CORRETOR VALUES (7,'HENRRIQUE CARLOS','RUA ARAGBOIA','PATO BRANCO','46-
9632525');
insert into CORRETOR VALUES (8,'SILVA JUNIOR','29','AV TUPI','99165378');
IMOVEL
insert into IMOVEL VALUES (1,1,1,'CONSIGNACAO','CARLOS SILVA');
insert into IMOVEL VALUES (2,2,2,'A VENDA','4LUIZ DOMINGUES');
insert into IMOVEL VALUES (3,3,3,'ALUGA-SE','CLEO PIRES');
insert into IMOVEL VALUES (4,4,4,'ALUGA-SE','MANOEL TOBIAS');
insert into IMOVEL VALUES (5,5,5,'ALUGA-SE','DANIEL SILVA');
insert into IMOVEL VALUES (6,6,6,'A VENDA','LUCIANE FOZ');
insert into IMOVEL VALUES (7,7,7,'VENDIDO','ARI ALMEIDA');
insert into IMOVEL VALUES (8,8,8,'ALUGA-SE','FABRICIO OLIVEIRA');
Consultas
Numero de Tuplas
select count(Cliente.pk_codcliente) as Numero_de_Tuplas from cliente, tipo_transacao
Corretor com seu respectivo cliente
select c.nome as Nome_Corretor, cl.nome as Nome_Cliente from corretor c, cliente cl
where pk_codcorretor=pk_codcliente
Corretor que possuir imóvel igual ou superior a 1000000
select c.nome as Nome_Corretor, sum(tt.valor) as Valor_Imovel from tipo_transacao tt, corretor c
where pk_codcorretor=fk_codcorretor
group by c.nome having sum(tt.valor) >= 1000000
Cidade de todos os clientes e o estado dos imoveis. Excluindo da consulta os 
registros em que as cidades sejam inferiores a 2
select lc.cidade as Cidade_Estado from localizacao_cliente lc
group by lc.cidade having count(lc.cidade) > 2
union all
select li.estado from localizacao_imovel li
Cidade de todos os clientes e o estado dos imoveis. Excluindo da consulta os 
registros em que os estados sejam inferiores a 1
select lc.cidade as Cidade_Estado from localizacao_cliente lc
union
select li.estado from localizacao_imovel li
group by li.estado having count(lc.cidade) > 1
Lista as cidades de em que os imóveis estão localizados e exclui da lista da 
localização do cliente que estão na cidade de Curitiba
select li.cidade from localizacao_imovel li
where not exists (select lc.cidade from localizacao_cliente lc where upper(lc.cidade) = 'CURITIBA')
Seleciona o nome do cliente a cidade e o bairro
select c.nome, l.cidade, l.bairro from cliente c
inner join localizacao_cliente l
on c.fk_localizacao=l.pk_codloc
Seleciona o nome do cliente e a cidade, ordenada por cidade.
select c.nome, l.cidade from cliente c
left join localizacao_cliente l
on c.fk_localizacao = l.pk_codloc order by l.cidade
Seleciona o nome do cliente e a cidade dos clientes com mais de 27 anos
select c.nome, l.cidade from cliente c
right join localizacao_cliente l
on c.fk_localizacao = l.pk_codloc where c.idade > 27
Nome do corrector com os valores dos imoveis
select cr.nome, tt.valor from corretor cr
full join tipo_transacao tt
on fk_codcorretor = pk_codcorretor