Criar Banco de Dados Imobiliária Aluguel

Vamos Criar um “Bando de Dados” para uma imobiliária fazer o controle dos Imóveis para Aluguel. 
Será necessário:

•Guardar informações do Proprietário :

Nome, endereço, telefone e e-mail.

•Guardar informações do Corretor responsável :

Nome, endereço, telefone e e-mail.

•Guardar informações do inquilino:

Nome, endereço, telefone e e-mail.

•Guardar informações do imóvel:

Descrição do Imóvel, valor do aluguel, endereço, bairro, condição se alugado ou disponível.

No  Modelo Entidade-Relacionamento (MER) anterior temos as seguintes entidades e relacionamentos:

•Proprietário ,”contata”, Corretor (um proprietário pode contatar vários corretores e um corretor pode ser contatado por vários proprietários).

•Corretor, ”atende”, Inquilino (um corretor pode atender vários inquilinos e um inquilino pode ser atendido por vários corretores).

•Inquilino, ”aluga”, Imóvel (um inquilino aluga um imóvel e um imóvel pode ser alugado por vários inquilinos).

•Proprietário, ”possui”, Imóvel (um proprietário possui vários imóveis e um imóvel pertence a apenas um proprietário).

Modelo Relacional Aluguel

•tabela proprietário(codprop, nome, ender,tel,email), codprop é chave primária.

•tabela corretor(codcor, nome, ender,tel,email), codcor é chave primária.

•tabela inquilino(codinq, nome, ender,tel,email), onde codinq é chave primária.

•tabela imovel(codimov, descricao, valor, ender, bairo, condicao), ond codimov é chave primária

•tabela aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim), ond codalug é chave primaria e;

     codinc é chave estrangeira da tabela inquilino, 

     codimov é chave estrangeira da tabela imovel,

     codprop é chave estrangeira da tabela proprietario,

     codcor é chave estrangeira da tabela corretor.

/* Criar Banco de Dados Imobiliária Aluguel*/
create database DB_Aluguel

/* Altera o contexto de banco de dados, selcionar para ser utilizado  DB_Imobiliaria_Aluguél*/
use DB_Aluguel

/* criar tabela proprietário(codprop, nome, ender,tel,email), onde codprop é chave primária*/
create table proprietario(
codprop char(3) primary key,
nome varchar(50) not null,
ender varchar(130)not null,
tel char(14),
email varchar(100))

drop table proprietario

/* criar tabela corretor(codcor, nome, ender,tel,email), onde codcor é chave primária*/
create table corretor(
codcor char(3) primary key,
nome varchar(50) not null,
ender varchar(130)not null,
tel char(14),
email varchar(100))

drop table corretor

/* criar tabela inquilino(codinq, nome, ender,tel,email), onde codinq é chave primária*/
create table inquilino(
codinq char(3) primary key,
nome varchar(50) not null,
ender varchar(130)not null,
tel char(14),
email varchar(100))

drop table inquilino

/* criar tabela imovel(codimov,descricao,valor,ender,bairo,condicao), ond codimov é chave primária*/
create table imovel(
codimov char(3) primary key,
descricao varchar(100),
valor money,
ender varchar(120),
bairo varchar(50),
condicao varchar(30))

drop table imovel

/* criar tabela aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim), ond codalug é chave primaria e
codinc é chave estrangeira da tabela inquilino,
codimov é chave estrangeira da tabela imovel,
codprop é chave estrangeira da tabela proprietario,
codcor é chave estrangeira da tabela corretor. */
create table aluguel(
codalug varchar(4) primary key,
codinc char(3)FOREIGN KEY REFERENCES inquilino(codinq),
codimov char(3)FOREIGN KEY REFERENCES imovel(codimov),
codprop char(3)FOREIGN KEY REFERENCES proprietario(codprop),
codcor char(3)FOREIGN KEY REFERENCES corretor(codcor),
dataini date,
datafim date)

drop table aluguel

/* inserir dados na tabela proprietário(codprop, nome, ender,tel,email), onde codprop é chave primária*/
insert into proprietario(codprop, nome, ender,tel,email)
values
('P01','Antonio Mauel','Rua Maria Alzira Leal 123','1999345245','antonio@gmail.com'),
('P02','Milena Rafaela Nunes','AV Paulista 100','1993543765','milnunes@uol.com.br'),
('P03','Neide Alpino','Rua das Amelias 32','194433345','neidealpino@ig.com.br'),
('P04','Patricia Rufino','Rua Jose Bonifacio 1050','1997899923','patruf@rh.com.br'),
('P05','Jose Pascoal','Rua Curitiba 503','1997845637','jp@all.com'),
('P06','Felipe Maia','Rua 10 23','1997895534','fmaia@ig.com.br'),
('P07','Joao Santo','Rua 9 de julho 34','1999345245','jsanto@gmail.com'),
('P08','Walter Lopes','Rua das Rosas','1993543765','wlopes@uol.com.br'),
('P09','Cristiano Sulato','Rua Primavera 121','194433345','crissulato@ig.com.br'),
('P10','Maria Silveira','Rua Maracana 64','1997899923','msilveira@rh.com.br'),
('P11','Plino Milano','Rua Constantino 234','1997845637','plinim@all.com'),
('P12','Rosangela Bagers','Av Brasil 1000','1997895534','robarges@ig.com.br')


/* inserir dados na tabela corretor(codcor, nome, ender,tel,email), onde codcor é chave primária*/
insert into corretor(codcor, nome, ender,tel,email)
values
('100', 'Almir Lopez', 'Rua das Rosas 123','199399242','amlopez@hotmail.com'),
('200', 'Sebastiao Goncalo', 'Rua Pernambuco 344','1996453399','gonsalo@imobiliaria.com'),
('300', 'Beatriz Maria', 'AV Sao Joao 1232','9833823342','biam@imobiliaria.com')


/* inserir dados na tabela inquilino(codinq, nome, ender,tel,email), onde codinq é chave primária*/
insert into inquilino(codinq, nome, ender,tel,email)
values
('I01', 'Julio Simao', 'Rua Pitanga 34','199938456','julioS@fds.com'),
('I02', 'Milton Soares', 'Av Laguna 543','198790534','milsoares@ere.com'),
('I03', 'Marcia Rosatto', 'Rua Jose de Alencar 45','198876433','marciarosato@gmail.com'),
('I04', 'Paulo Gomes', 'Rua Joao Lima 894','195453332','paulog@all.com.br'),
('I05', 'Josefa Fernandes', 'Rua Araguari 76','198890432','josefaf@outlook.com'),
('I06', 'Gilmar Mendes', 'Rua Monte Videl 89','1988845321','gilmendes@gmailcom'),
('I07', 'Cristina Faustino', 'Rua Cantinga 122','1978363221','cris@gmail.com'),
('I08', 'Sivio Castilho', 'Rua Guanabara 110','198893421','silvioc@sp.com.br'),
('I09', 'Carlos Justino', 'Rua Marinara','19889789','carlosjus@ig.com.br'),
('I10', 'Samuel Souza', 'Rua Araguari 114','1978363221','samuels@gmail.com'),
('I11', 'Otavio Brandino', 'Rua Sao Pedro 20','198893421','otbr@sp.com.br'),
('I12', 'Naiara Lima', 'Av 15 de Novembro 10','19889789','nailima@ig.com.br')

/* inserir dados na tabela imovel(codimov,descricao,valor,ender,bairo,condicao, codprop), ond codimov é chave primária */
insert into imovel(codimov,descricao,valor,ender,bairo,condicao)
values
('C01','Apartamento 2 quartos','780','Rua Jurunas 455','Vl Maria','Disponivel'),
('C02','Casa 2 quartos 1 suite','1250','Rua Silveira Bueno 53','Jd Das Rosas','Disponivel'),
('C03','Casa 1 quartos 1 suite','950','AV Brasil 44','Centro','Disponivel'),
('C04','Edicula 1 quarto sem garagem','500','Rua Bahia 21','Jd Das Rosas','Disponivel'),
('C05','Apartamento 1 suite','780','Rua Constantino 231','Vila Sao Judas','Disponivel'),
('C06','Casa 3 quartos','890','Rua Santa Cruz 331','Condomino Azul','Disponivel'),
('C07','Casa 2 quartos','690','Av Guimaraes','Jd Brasilia','Disponivel'),
('C08','Casa 3 quartos 1 suite','1300','Rua Lindor Silveira 544','Vila Cascata','Disponivel'),
('C09','Casa 1 quartos 1 suite','780','Rua 22 15','Jd Paulista','Disponivel'),
('C10','Edicula 2 quarto ','750','Rua Laranjeiras 98','Pq Sao Luis','Disponivel'),
('C11','Apartamento 2 quartos 1 suite','1100','Rua Constantino 231','Vila Sao Judas','Disponivel'),
('C12','Casa 2 quartos','700','Rua Palmital 44','Vila Boa Esperanca','Disponivel')

/* inserir dados na tabela aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim), ond codalug é chave primaria e
codinc é chave estrangeira da tabela inquilino,
codimov é chave estrangeira da tabela imovel,
codprop é chave estrangeira da tabela proprietario,
codcor é chave estrangeira da tabela corretor. */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
/*('AA01',’I01',’C01', ’P01','100','20150812','20170810'),
('AA02',’I02',’C02', ’P02',’200','20161025','20171025'),
('AA03',’I03',’C03', ’P03',’300','20161204','20171204'),
('AA04',’I04',’C04', ’P04',’100','20160722','20170722'),
('AA05',’I05',’C05', ’P05','200','20160915','20170915') */


/* Criar uma View onde contenha Descrição do imovel, bairro do imovel, valor do aluguél dos imóveis
Disponivel para Alugar */
create view vw_Disponivel as
select  I.descricao, I.bairo, I.valor, I.condicao
from imovel I
where condicao = 'Disponivel'

select * from vw_Disponivel
drop view vw_Disponivel

/* Criar uma View com nomes e telefones de contatos do Corretor, do Inquilino
e do Proprietário, o bairro onde fica o Imoveis que estão alugados */
create view vw_Alugado as
select C.nome as [Nome Corretor], C.tel as [Tel Corretor],
I.nome as [Nome Inquilino], I.tel as [Tel Inquilino], P.nome as [Nome Propietario], P.tel as [Tel Proprietario],
M.bairo, M.condicao
from aluguel A
inner join corretor C
on C.codcor = A.codcor
inner join proprietario P
on P.codprop = A.codprop
inner join  inquilino I
on I.codinq = A.codinc
inner join  imovel M
on M.codimov = A.codimov

select * from vw_Alugado
drop view vw_Alugado

/* Criar uma View que calcula quantidade e valor dos Imoveis Alugados
por nome do Corretor e calcular 10% da comissao */
create view vw_CorrAluga as
select C.nome as[Corretor],
count(I.valor) as [Quantidade de Aluguel] ,
sum(I.valor) as [Soma dos Alugueis R$],
str(sum(I.valor * 0.10),6,2) as [Comissao R$]
from corretor C
inner join aluguel A
on C.codcor = A.codcor
inner join imovel I
on I.codimov = A.codimov
group by C.nome

select * from vw_CorrAluga
drop view vw_CorrAluga

/* Trigger de Insert que dispara a condição do Imovel "Disponível" para "Alugado" */
create trigger trg_Aluga
on aluguel
for insert as
begin
declare
 @condicao varchar(30),
 @codimov  char(3);
    select @codimov = codimov from inserted
select @condicao = condicao from  imovel
update imovel
    set condicao = 'Alugado'
where  @codimov = codimov;

print 'Imovel Alugado !!!'
end;

drop trigger trg_Aluga

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA01','I01','C01', 'P01','100','20150812','20170810')

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA02','I02','C02', 'P02','200','20161025','20171025')

select * from vw_Disponivel
select * from vw_Alugado
select * from vw_CorrAluga


/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA03','I03','C03', 'P03','300','20161204','20171204')

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA04','I04','C04', 'P04','100','20160722','20170722')

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA05','I05','C05', 'P05','200','20160915','20170915')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA06','I06','C06', 'P06','100','20150812','20170810')

select * from vw_Disponivel
select * from vw_Alugado
select * from vw_CorrAluga

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA07','I07','C07', 'P07','200','20161025','20171025')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA08','I08','C08', 'P08','300','20161204','20171204')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA09','I09','C09', 'P09','100','20160722','20170722')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA10','I10','C10', 'P10','200','20160915','20170915')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA11','I11','C11', 'P11','100','20160722','20170722')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA12','I12','C12', 'P12','200','20160915','20170915')

select * from vw_Disponivel
select * from vw_Alugado
select * from vw_CorrAluga