Modelagem-Fisica-Clinica-Pesquisa-Completa-OLTP-SQL

1 Faculdades Metropolitanas Unidas Departamento de Ciências Exatas e Gerenciais MODELO FÍSICO CLÍNICA PESQUISA COMPLETA NA CLÍNICA USANDO OLTP PARA O RH AMBIENTE SQL SELECT FROM MEDICO AS M INNER JOIN MEDICOESPECIALIDADE AS ME ON MCDMEDICOMECDMEDICO INNER JOIN ESPECIALIDADE AS E ON ECDESPECIALIDADEMECDESPECIALIDADE INNER JOIN TELEFONE AS T ON MCDMEDICOTCDMEDICO INNER JOIN MEDICOPACIENTE AS MP ON MCDMEDICOMPCDMEDICO INNER JOIN PACIENTE AS P ON PCDPACIENTEMPCDPACIENTE INNER JOIN TIPOSANGUINEO AS TS ON TSCDTIPOSANGUINEOPCDTIPOSANGUINEO REFATORAR O COMANDO TIRANDO O E COLOCANDO OS DADOS QUE FORAM SOLICITADOS PELO USUÁRIO SELECT NMESPECIALIDADENMMEDICONRCRMTNRDDDTNRTELEFONE DSDATADSHORANMPACIENTEPNRDDDPNRTELEFONEDSTIPODSFATOR FROM MEDICO AS M INNER JOIN MEDICOESPECIALIDADE AS ME ON MCDMEDICOMECDMEDICO INNER JOIN ESPECIALIDADE AS E ON ECDESPECIALIDADEMECDESPECIALIDADE INNER JOIN TELEFONE AS T ON MCDMEDICOTCDMEDICO INNER JOIN MEDICOPACIENTE AS MP ON MCDMEDICOMPCDMEDICO INNER JOIN PACIENTE AS P ON PCDPACIENTEMPCDPACIENTE INNER JOIN TIPOSANGUINEO AS TS ON TSCDTIPOSANGUINEOPCDTIPOSANGUINEO ORDER BY NMESPECIALIDADENMMEDICODSDATADSHORANMPACIENTE PARA MELHORAR A PERFORMANCE VAMOS CRIAR A VIEW MEDICO CDMEDICO int Não NMMEDICO varchar50 Não DTADMISSAO date Sim NRCRM int Não VLSALARIO numeric18 2 Sim Nome da Coluna Tipo Condensado Permite Valor Nulo MEDICOPACIENTE DSDATA date Não DSHORA time7 Não CDPACIENTE int Não CDMEDICO int Não Nome da Coluna Tipo Condensado Permite Valor Nulo PACIENTE CDPACIENTE int Não NMPACIENTE varchar50 Não DSLOGRADOURO varchar30 Sim DSNUMERO varchar10 Sim DSCOMPLEMENTO varchar10 Sim DSBAIRRO varchar50 Sim DSCEP char8 Sim NRDDD int Sim NRTELEFONE int Sim CDTIPOSANGUINEO int Não Nome da Coluna Tipo Condensado Permite Valor Nulo TIPOSANGUINEO CDTIPOSANGUINEO int Não DSTIPO varchar2 Não DSFATOR char1 Não Nome da Coluna Tipo Condensado Permite Valor Nulo MEDICOESPECIALIDADE CDMEDICO int Não CDESPECIALIDADE int Não Nome da Coluna Tipo Condensado Permite Valor Nulo TELEFONE CDTELEFONE int Não NRDDD int Não NRTELEFONE int Não CDMEDICO int Não Nome da Coluna Tipo Condensado Permite Valor Nulo ESPECIALIDADE CDESPECIALIDADE int Não NMESPECIALIDADE varchar50 Não Nome da Coluna Tipo Condensado Permite Valor Nulo 2 Faculdades Metropolitanas Unidas Departamento de Ciências Exatas e Gerenciais SEM ORDER BY E SEM CAMPOS REPETIDOS CREATE VIEW VWCLINICA AS SELECT NMESPECIALIDADENMMEDICONRCRMTNRDDDTNRTELEFONE DSDATADSHORANMPACIENTEPNRDDD AS DDDPNRTELEFONE AS TELEFONE DSTIPODSFATOR FROM MEDICO AS M INNER JOIN MEDICOESPECIALIDADE AS ME ON MCDMEDICOMECDMEDICO INNER JOIN ESPECIALIDADE AS E ON ECDESPECIALIDADEMECDESPECIALIDADE INNER JOIN TELEFONE AS T ON MCDMEDICOTCDMEDICO INNER JOIN MEDICOPACIENTE AS MP ON MCDMEDICOMPCDMEDICO INNER JOIN PACIENTE AS P ON PCDPACIENTEMPCDPACIENTE INNER JOIN TIPOSANGUINEO AS TS ON TSCDTIPOSANGUINEOPCDTIPOSANGUINEO CRIAR A PROCEDURE COM A VIEW E COM O ORDER BY CREATE PROCEDURE SPCLINICA AS SELECT FROM VWCLINICA ORDER BY NMESPECIALIDADENMMEDICODSDATADSHORANMPACIENTE EXECUTAR A PROCEDURE EXECUTE SPCLINICA EXEC SPCLINICA SPCLINICA 3 Faculdades Metropolitanas Unidas Departamento de Ciências Exatas e Gerenciais MODELO FÍSICO PROJETO RH OLTP PARA A PESQUISA NO RH SELECT FROM FUNCIONARIO AS F INNER JOIN DEPTO AS D ON DDEPTOIDFDEPTOID INNER JOIN CIDADE AS C ON CCIDADEIDFCIDADEID INNER JOIN ESTADO AS E ON EESTADOIDCESTADOID REFATORAR SELECT UFNMESTADONMCIDADENMDEPTONMFUNCIONARIOSEXODATAADMISSAOSALARIO FROM FUNCIONARIO AS F INNER JOIN DEPTO AS D ON DDEPTOIDFDEPTOID INNER JOIN CIDADE AS C ON CCIDADEIDFCIDADEID INNER JOIN ESTADO AS E ON EESTADOIDCESTADOID CRIAR O DATAWAREHOUSE CREATE DATABASE DWHM COLOCAR EM USO USE DWHM UFNMESTADONMCIDADENMDEPTONMFUNCIONARIOSEXODATAADMISSAOSALARIO CREATE TABLE DWH CDDWH INT IDENTITY11 NOT NULL UF CHAR2 NULL CIDADE VARCHAR60 NULL CIDADE CIDADEID int Não NMCIDADE nvarchar60 Não ESTADOID int Não Nome da Coluna Tipo Condensado Permite Valor Nulo DEPTO DEPTOID int Não NMDEPTO nvarchar50 Não Nome da Coluna Tipo Condensado Permite Valor Nulo ESTADO ESTADOID int Não NMESTADO nvarchar30 Não UF nvarchar2 Não Nome da Coluna Tipo Condensado Permite Valor Nulo FUNCIONARIO FUNCIONARIOID int Não NMFUNCIONARIO nvarchar80 Sim SEXO nvarchar1 Sim DATAADMISSAO date Sim CIDADEID int Não DEPTOID int Não CEP nvarchar8 Sim SALARIO numeric18 2 Sim VLIR numeric18 2 Sim Nome da Coluna Tipo Condensado Permite Valor Nulo 4 Faculdades Metropolitanas Unidas Departamento de Ciências Exatas e Gerenciais DEPTO VARCHAR50 NULL FUNCIONARIO VARCHAR80 NULL SEXO CHAR1 NULL ADMISSAO DATE NULL SALARIO NUMERIC182 NULL CONSTRAINT PKDWH PRIMARY KEYCDDWH PESQUISA EM UMA TABELA DO SISTEMA TABELA QUE ESTÁ NO MASTER TEM TODOS OS COMPONENTES DO NOSSO BANCO SELECT FROM SYSOBJECTS SELECT FROM SYSOBJECTS WHERE XTYPE U USUÁRIO PESQUISAR PELO NOME DA TABELA SELECT FROM SYSOBJECTS WHERE NAME DWH CRIAR A PROCEDURE DE CARGA DO DATAWAREHOUSE CREATE PROCEDURE DWHCARGA AS IF NOT EXISTSSELECT FROM SYSOBJECTS WHERE NAME DWH BEGIN CREATE TABLE DWH CDDWH INT IDENTITY11 NOT NULL UF CHAR2 NULL CIDADE VARCHAR60 NULL DEPTO VARCHAR50 NULL FUNCIONARIO VARCHAR80 NULL SEXO CHAR1 NULL ADMISSAO DATE NULL SALARIO NUMERIC182 NULL CONSTRAINT PKDWH PRIMARY KEYCDDWH END ELSE BEGIN TRUNCATE TABLE DWH END EXECUTAR A CARGA DE DADOS EXECUTE DWHCARGA DADOS QUE PRECISO PARA DAR CARGA NA TABELA COLOCAR OS DOIS PONTOS ANTES DO NOME DA TABELA PARA INDICAR QUAL BANCO PERTENCE SELECT UFNMESTADONMCIDADENMDEPTONMFUNCIONARIOSEXODATAADMISSAOSALARIO FROM DATAWAREHOUSEFUNCIONARIO AS F INNER JOIN DATAWAREHOUSEDEPTO AS D ON DDEPTOIDFDEPTOID INNER JOIN DATAWAREHOUSECIDADE AS C ON CCIDADEIDFCIDADEID INNER JOIN DATAWAREHOUSEESTADO AS E ON EESTADOIDCESTADOID 5 Faculdades Metropolitanas Unidas Departamento de Ciências Exatas e Gerenciais ALTERAR A PROCEDURE PARA COLOCAR A CÓPIA DE DADOS DO BANCO DATAWAREHOUSE PARA O NOSSO DWHM ALTER PROCEDURE DWHCARGA AS IF NOT EXISTSSELECT FROM SYSOBJECTS WHERE NAME DWH BEGIN CREATE TABLE DWH CDDWH INT IDENTITY11 NOT NULL UF CHAR2 NULL ESTADO VARCHAR30 NULL CIDADE VARCHAR60 NULL DEPTO VARCHAR50 NULL FUNCIONARIO VARCHAR80 NULL SEXO CHAR1 NULL ADMISSAO DATE NULL SALARIO NUMERIC182 NULL CONSTRAINT PKDWH PRIMARY KEYCDDWH END ELSE BEGIN TRUNCATE TABLE DWH END INSERT INTO DWH SELECT UFNMESTADONMCIDADENMDEPTONMFUNCIONARIOSEXODATAADMISSAOSALARIO FROM DATAWAREHOUSEFUNCIONARIO AS F INNER JOIN DATAWAREHOUSEDEPTO AS D ON DDEPTOIDFDEPTOID INNER JOIN DATAWAREHOUSECIDADE AS C ON CCIDADEIDFCIDADEID INNER JOIN DATAWAREHOUSEESTADO AS E ON EESTADOIDCESTADOID EXECUTAR A CARGA DA TABELA EXECUTE DWHCARGA PESQUISAR NA TABELA SELECT FROM DWH NMDEPTONMFUNCIONARIOSALARIO CREATE TABLE DATAMARTSOMASALARIO CDMART INT IDENTITY11 NOT NULL DEPTO VARCHAR50 NULL FUNCIONARIO VARCHAR80 NULL SALARIO NUMERIC182 NULL CONSTRAINT PKDATAMARTSOMASALARIO PRIMARY KEYCDMART CRIAR A PROCEDURE DE CARGA DO DATA MART CREATE PROCEDURE SPDATAMARTCARGA AS 6 Faculdades Metropolitanas Unidas Departamento de Ciências Exatas e Gerenciais IF NOT EXISTSSELECT FROM SYSOBJECTS WHERE NAME DATAMARTSOMASALARIO BEGIN CREATE TABLE DATAMARTSOMASALARIO CDMART INT IDENTITY11 NOT NULL DEPTO VARCHAR50 NULL FUNCIONARIO VARCHAR80 NULL SALARIO NUMERIC182 NULL CONSTRAINT PKDATAMARTSOMASALARIO PRIMARY KEYCDMART END ELSE BEGIN TRUNCATE TABLE DATAMARTSOMASALARIO END INSERT INTO DATAMARTSOMASALARIO SELECT DEPTOFUNCIONARIOSALARIO FROM DWH EXECUTAR EXECUTE SPDATAMARTCARGA PESQUISA SELECT FROM DATAMARTSOMASALARIO CRIAR UMA VIEW PARA O ETL CREATE VIEW FATOSOMA AS SELECT DEPTOSUMSALARIO AS SOMA FROM DATAMARTSOMASALARIO GROUP BY DEPTO SEGUINDO O ROTEIRO ACIMA 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 SE CONSEGUIR MONTAR ATÉ 2503 TENDO DÚVIDA ME ESCREVA ADEMIRAVILAFMUBR