ATIVIDADE - PRÁTICAS DE BANCO DE DADOS

Prévia do material em texto

CENTRO UNIVERSITÁRIO DAS FACULDADES METROPOLITANAS UNIDAS 
 
Elma Nayara Soares 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ATIVIDADE - BANCO DE DADOS 
Elaboração de um Data Mart 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
São Paulo - SP 
2022 
 
ELMA NAYARA SOARES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ATIVIDADE - BANCO DE DADOS 
Elaboração de um Data Mart 
 
 
 
 
 
 
 
Atividade apresentada ao Centro Universitário das 
Faculdades Metropolitanas Unidas, como parte das 
exigências para a obtenção de nota referente à N1 
da disciplina Práticas de Banco de Dados. 
 
 
 
Professor: Ademir Avila 
 
 
 
 
 
 
 
 
São Paulo - SP 
2022 
 
 
ATIVIDADE 
 
Seguindo o roteiro passado pelo professor, elaborar um Data Mart para ter os dados 
dos funcionários, cidade e estado em que moram, e a partir dele elaborar o fato da 
quantidade de funcionários por UF. 
 
 
 
 
 
 
Observação: Devido a um problema no momento de importar os dados do Excel 
para o SQL Server, inseri alguns dados manualmente para que fosse possível realizar 
a atividade. 
4 
--CRIAR BANCO DE DADOS 
CREATE DATABASE ATIVIDADE_DATABASE 
 
--COLOCAR O BANCO DE DADOS EM USO 
USE ATIVIDADE_DATABASE 
 
--CRIAR A TABELA ESTADO 
CREATE TABLE ESTADO 
(ESTADOID INT IDENTITY(1,1) NOT NULL, 
NM_ESTADO VARCHAR(30) NOT NULL, 
UF CHAR(2) NOT NULL, 
CONSTRAINT PK_ESTADO PRIMARY KEY(ESTADOID)) 
 
--CRIAR A TABELA DEPTO 
CREATE TABLE DEPTO 
(DEPTOID INT IDENTITY(1,1) NOT NULL, 
NM_DEPTO VARCHAR(50) NOT NULL, 
CONSTRAINT PK_DEPTO PRIMARY KEY(DEPTOID)) 
 
--CRIAR A TABELA CIDADE 
CREATE TABLE CIDADE 
(CIDADEID INT IDENTITY(1,1) NOT NULL, 
NM_CIDADE VARCHAR(60) NOT NULL, 
ESTADOID INT NOT NULL, 
CONSTRAINT PK_CIDADE PRIMARY KEY(CIDADEID), 
CONSTRAINT FK_CIDADE FOREIGN KEY(ESTADOID) 
REFERENCES ESTADO(ESTADOID)) 
 
--CRIAR TABELA FUNCIONARIO 
CREATE TABLE FUNCIONARIO 
(FUNCIONARIOID INT IDENTITY(1,1) NOT NULL, 
NM_FUNCIONARIO VARCHAR(80) NOT NULL, 
SEXO CHAR(1) NULL, DATA_ADMISSAO DATE 
NULL, SALARIO NUMERIC(18,2) NULL, CEP 
CHAR(8) NULL, 
CIDADEID INT NOT NULL, DEPTOID INT NOT 
NULL, 
CONSTRAINT PK_FUNCIONARIO PRIMARY KEY(FUNCIONARIOID), 
CONSTRAINT FK_FUNCIONARIO_CIDADE FOREIGN KEY(CIDADEID) 
REFERENCES CIDADE(CIDADEID), 
CONSTRAINT FK_FUNCIONARIO_DEPTO FOREIGN KEY(DEPTOID) 
REFERENCES DEPTO(DEPTOID)) 
 
--INSERÇÃO DOS ESTADOS 
INSERT INTO ESTADO VALUES ('MINAS GERAIS','MG') 
INSERT INTO ESTADO VALUES ('SÃO PAULO','SP') 
INSERT INTO ESTADO VALUES ('RIO DE JANEIRO','RG') 
INSERT INTO ESTADO VALUES ('ESPÍRITO SANTO','ES') 
 
 
--INSERÇÃO DOS DEPTOS 
5 
INSERT INTO DEPTO VALUES ('DIRETORIA') 
INSERT INTO DEPTO VALUES ('RH') 
INSERT INTO DEPTO VALUES ('VENDAS') 
INSERT INTO DEPTO VALUES ('SUPORTE') 
INSERT INTO DEPTO VALUES ('MARKETING') 
 
--INSERÇÃO DAS CIDADES 
INSERT INTO CIDADE VALUES ('DOM SILVÉRIO',1) 
INSERT INTO CIDADE VALUES ('ALVINÓPOLIS',1) 
INSERT INTO CIDADE VALUES ('BELO HORIZONTE',1) 
INSERT INTO CIDADE VALUES ('DIADEMA',2) 
INSERT INTO CIDADE VALUES ('SANTOS',2) 
INSERT INTO CIDADE VALUES ('SALTO',2) 
INSERT INTO CIDADE VALUES ('NITERÓI',3) 
INSERT INTO CIDADE VALUES ('DUQUE DE CAXIAS',3) 
INSERT INTO CIDADE VALUES ('PETRÓPOLIS',3) 
INSERT INTO CIDADE VALUES ('OSASCO',2) 
INSERT INTO CIDADE VALUES ('JOÃO MONLEVADE',1) 
INSERT INTO CIDADE VALUES ('NOVA IGUAÇU',3) 
INSERT INTO CIDADE VALUES ('VITÓRIA',4) 
INSERT INTO CIDADE VALUES ('GUARAPARI',4) 
INSERT INTO CIDADE VALUES ('SERRA',4) 
INSERT INTO CIDADE VALUES ('VILA VELHA',4) 
INSERT INTO CIDADE VALUES ('SÃO CAETANO DO SUL',2) 
INSERT INTO CIDADE VALUES('OURO PRETO',1) 
INSERT INTO CIDADE VALUES ('MAUÁ',2) 
INSERT INTO CIDADE VALUES ('UBERLÂNDIA',1) 
 
--INSERÇÃO DOS FUNCIONÁRIOS 
INSERT INTO FUNCIONARIO VALUES ('MATHEUS SILVA', 
'M','2021-12-10',1900.00,09914030,4,4) 
INSERT INTO FUNCIONARIO VALUES ('GABRIEL FERREIRA, 
'M','2022-01-04',2700.00,35440000,1,1) 
INSERT INTO FUNCIONARIO VALUES ('JENIFER MELO', 
'F','2005-06-25',2300.00,29010002,13,3) 
INSERT INTO FUNCIONARIO VALUES ('VINÍCIUS PONTEL', 
'M','2011-11-11',2000.00,13970190,12,2) 
INSERT INTO FUNCIONARIO VALUES ('RICARDO OLIVEIRA',' 
M','2006-01-28',1150.00,04063002,17,4) 
INSERT INTO FUNCIONARIO VALUES ('AMANDA GOLÇALVES', 
'F','2012-12-15',2000.00,11010071,5,3) 
INSERT INTO FUNCIONARIO VALUES ('MARCOS SILVA','M', 
'2020-05-12',2100.00,24020003,7,2) 
INSERT INTO FUNCIONARIO VALUES ('KARINA SOUZA', 
'F','2008-04-23',2700.00,29010130,15,3) 
INSERT INTO FUNCIONARIO VALUES ('LUCAS BRAGA', 
'M','2019-10-30',1250.00,13301616,6,4) 
INSERT INTO FUNCIONARIO VALUES ('RÓGER CAMPOS, 
'M','2020-07-29',1350.00,04949515,10,4) 
6 
INSERT INTO FUNCIONARIO VALUES ('MILENE FERNANDES', 
'F','2014-05-31',1700.00,04195010,17,3) 
INSERT INTO FUNCIONARIO VALUES ('BÁRBARA VIEIRA', 
'F','2010-03-31',1350.00,3440000,1,4) 
INSERT INTO FUNCIONARIO VALUES ('FERNANDA OLIVEIRA', 
'F','2017-02-28',1750.00,15076610,20,5) 
INSERT INTO FUNCIONARIO VALUES ('ELMA NAYARA', 
'F','2019-07-10',1750.00,01011100,10,5) 
INSERT INTO FUNCIONARIO VALUES ('SIMONE SOUZA', 
'F','2015-02-20',1650.00,29010035,13,2) 
INSERT INTO FUNCIONARIO VALUES ('LETÍCIA TRINDADE', 
'F','2009-02-12',1400.00,30110005,3,4) 
INSERT INTO FUNCIONARIO VALUES ('KELLY KEY', 
'F','2009-02-03',2700.00,13301590,6,1) 
INSERT INTO FUNCIONARIO VALUES ('VALENTINA COSTA', 
'F','2008-12-13',3500.00,38204056,20,1) 
INSERT INTO FUNCIONARIO VALUES ('CECÍLIA CAMPOS', 
'F','2011-11-25',2400.00,35930008,11,4) 
INSERT INTO FUNCIONARIO VALUES ('MARIA JOAQUINA', 
'F','2010-10-26',2000.00,29129304,14,1) 
INSERT INTO FUNCIONARIO VALUES ('RONI MOURA', 
'M','2010-05-12',1950.00,29010080,16,4) 
INSERT INTO FUNCIONARIO VALUES ('JOÃO SILVA', 
'M','2012-04-08',1550.00,29010050,13,4) 
INSERT INTO FUNCIONARIO VALUES ('JÚNIOR MORAES', 
'M','2020-07-24',1200.00,07262130,3,4) 
INSERT INTO FUNCIONARIO VALUES ('IVAN ANDRADE, 
'M','2021-06-11',1500.00,15612162,20,2) 
INSERT INTO FUNCIONARIO VALUES ('RAÚL LUCAS', 
'M','2021-01-22',2100,03973060,19,3) 
INSERT INTO FUNCIONARIO VALUES ('ADSON SOARES', 
'M','2007-10-17',2850.00,35400000,18,2) 
INSERT INTO FUNCIONARIO VALUES ('GUSTAVO MANTUAN', 
'M','2009-06-30',1500.00,11010095,5,4) 
INSERT INTO FUNCIONARIO VALUES ('LUCAS PITON', 
'M','2006-09-09',3000.00,25610041,9,4) 
INSERT INTO FUNCIONARIO VALUES ('GIULIANO DE SOUZA', 
'M','2007-09-28',2100.00,24020000,7,3) 
INSERT INTO FUNCIONARIO VALUES ('JOÃO ASSIS', 
'M','2008-08-19',2750.00,35950000,2,3) 
INSERT INTO FUNCIONARIO VALUES ('LUAN SILVA', 
'M','2009-05-05',2350.00,04417210,4,2) 
INSERT INTO FUNCIONARIO VALUES ('PAULO BEZERRA', 
'M','2010-04-20',3000.00,09910040,4,1) 
INSERT INTO FUNCIONARIO VALUES ('GUSTAVO SILVA', 
'M','2011-06-07',2000.00,29100011,16,2) 
INSERT INTO FUNCIONARIO VALUES ('EDUARDO QUEIROZ', 
'M','2013-01-16',1300.00,22711240,12,5) 
7 
INSERT INTO FUNCIONARIO VALUES ('LUCAS SANTOS', 
'M','2014-08-23',1050.00,35950000,2,4) 
INSERT INTO FUNCIONARIO VALUES ('FELIPE LEMOS', 
'M','2018-06-14',1650.00,20761170,8,5) 
INSERT INTO FUNCIONARIO VALUES ('JOÃO VICTOR', 
'M','2017-09-27',1800.00,35930004,11,4) 
INSERT INTO FUNCIONARIO VALUES ('CÁSSIO RAMOS', 
'M','2016-12-18',1950.00,22710325,9,4) 
INSERT INTO FUNCIONARIO VALUES ('RÉGIS AUGUSTO', 
'M','2015-05-06',2050.00,04849515,10,2) 
INSERT INTO FUNCIONARIO VALUES ('WILLIAN BORGES', 
'M','2005-01-15',4000.00,30360740,11,1) 
 
--CRIAR PESQUISA 
SELECT 
NM_FUNCIONARIO, SEXO, NM_DEPTO, 
DATA_ADMISSAO, SALARIO, 
NM_ESTADO, UF, NM_CIDADE,CEP 
FROM FUNCIONARIO AS F 
INNER JOIN DEPTO AS D ON D.DEPTOID=F.DEPTOID 
INNER JOIN CIDADE AS C ON C.CIDADEID=F.CIDADEID 
INNER JOIN ESTADO AS E ON E.ESTADOID=C.ESTADOID 
 
--CRIAR O DWH CREATE DATABASE 
DWH 
 
--COLOCAR O DATAWAREHOUSE EM USO 
USE DWH 
--UF,NM_ESTADO,NM_CIDADE,NM_DEPTO,NM_FUNCIONARIO,SEXO,DATA_ADMISSAO,SALARIO 
CREATE TABLE DWH 
(CD_DWH INT IDENTITY(1,1) NOT NULL, 
UF CHAR(2) NULL, 
CIDADE VARCHAR(60) NULL, 
DEPTO VARCHAR(50) NULL, 
FUNCIONARIO VARCHAR(80)NULL, 
SEXO CHAR(1) NULL, 
ADMISSAO DATE NULL, 
SALARIO NUMERIC(18,2) NULL, 
CONSTRAINT PK_DWH PRIMARY KEY(CD_DWH)) 
 
--CRIAR A PROCEDURE DE CARGA DO DATA MART (GERAL) 
CREATE PROCEDURE DATA_MART_CARGA 
AS 
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 
'DATA_MART_FUNCIONARIOS_UF') 
BEGIN 
CREATE TABLE DATA_MART_FUNCIONARIOS_UF 
(CD_MART INT IDENTITY(1,1) NOT NULL, 
8 
 
 
 
 
 
 
 
 
 
 
END 
FUNCIONARIO VARCHAR(80) NULL, 
SEXO CHAR(2) NULL, 
DEPTO VARCHAR(50) NULL, 
ADMISSAO DATE NULL, 
SALARIO NUMERIC(18,2) NULL, 
ESTADO VARCHAR(30) NULL, 
UF CHAR(2) NULL, 
CIDADE VARCHAR(60) NULL, 
CEP CHAR(8) NULL, 
CONSTRAINT PK_DATA_MART_FUNCIONARIOS_UF PRIMARY KEY(CD_MART)) 
 
ELSE 
BEGIN 
TRUNCATE TABLE DATA_MART_FUNCIONARIOS_UF 
END 
 
INSERT INTO DATA_MART_FUNCIONARIOS_UF 
SELECT 
NM_FUNCIONARIO, SEXO, NM_DEPTO, 
DATA_ADMISSAO, SALARIO, NM_ESTADO, 
UF, NM_CIDADE, CEP 
FROM ATIVIDADE_DATABASE..FUNCIONARIO AS F 
INNER JOIN ATIVIDADE_DATABASE..DEPTO AS D ON D.DEPTOID=F.DEPTOID 
INNER JOIN ATIVIDADE_DATABASE..CIDADE AS C ON C.CIDADEID=F.CIDADEID 
INNER JOIN ATIVIDADE_DATABASE..ESTADO AS E ON E.ESTADOID=C.ESTADOID 
 
--EXECUTAR 
EXECUTE DATA_MART_CARGA 
 
--CRIAR A PROCEDURE DE CARGA DO DATA MART (APENAS OS DADOS NECESSÁRIOS PARA O 
FATO_FUNCIONARIOS_UF) 
CREATE PROCEDURE DATA_MART_CARGA_1 
AS 
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 
'DATA_MART_FUNCIONARIOS_UF_1') 
BEGIN 
CREATE TABLE DATA_MART_FUNCIONARIOS_UF_1 
(CD_MART_1 INT IDENTITY(1,1) NOT NULL, 
FUNCIONARIO VARCHAR(80) NULL, 
ESTADO VARCHAR(30) NULL, 
UF CHAR(2) NULL, 
CIDADE VARCHAR(60) NULL, 
CONSTRAINT PK_DATA_MART_FUNCIONARIOS_UF_1 PRIMARY KEY(CD_MART_1)) END 
ELSE 
BEGIN 
TRUNCATE TABLE DATA_MART_FUNCIONARIOS_UF END 
 
INSERT INTO DATA_MART_FUNCIONARIOS_UF_1 
SELECT FUNCIONARIO,ESTADO,UF,CIDADE 
FROM DATA_MART_FUNCIONARIOS_UF 
9 
 
--EXECUTAR 
EXECUTE DATA_MART_CARGA_1 
 
--CRIAÇÃO DA VIEW 
CREATE VIEW FATO_FUNCIONARIOS_UF 
AS 
SELECT ESTADO,COUNT(UF) AS 'QUANTIDADE' 
FROM DATA_MART_FUNCIONARIOS_UF_1 
GROUP BY ESTADO 
 
--EXECUÇÃO DA VIEW 
SELECT * FROM FATO_FUNCIONARIOS_UFORDER BY 
QUANTIDADE DESC