Trabalho universitário de Sql server

Project overview

Preciso de ddesenvolver este trabalho pratico ate ao mais tradar esta noite. Pretendo que o codigo esteja simples e que funcione corretamente. O codigo escrito até agora é o seguinbte : --trabalho prático-- create table dbo.DIM_TEMPO( Id_tempo int identity(1,1) not null, data date not null, ano smallint not null, mes smallint not null, dia smallint not null, dia_semana smallint not null, dia_ano smallint not null, ano_bissexto char(1) not null, dia_util char(1) not null, fim_semana char(1) not null, feriado char(1) not null, pre_feriado char(1) not null, pos_feriado char(1) not null, nome_feriado varchar(30) null, nome_dia_semana varchar(15) not null, nome_dia_semana_abrev char(3) not null, nome_mes varchar(15) not null, nome_mes_abrev char(3) not null, quinzena smallint not null, bimestre smallint not null, trimestre smallint not null, semestre smallint not null, nr_semana_mes smallint not null, nr_semana_ano smallint not null, estacao_ano varchar(15) not null, data_por_extenso varchar(50) not null, evento varchar(50) null ); -- bloco de código em t-sql que definindo "@datainicial" e a "@datafinal" -- popula a dim_tempo para os valores indicados declare @datainicial date, @datafinal date, @data date, @ano smallint, @mes smallint, @dia smallint, @diasemana smallint, @diautil char(1), @fimsemana char(1), @feriado char(1), @preferiado char(1), @posferiado char(1), @nomeferiado varchar(30), @nomediasemana varchar(15), @nomediasemanaabrev char(3), @nomemes varchar(15), @nomemesabrev char(3), @bimestre smallint, @trimestre smallint, @nrsemanames smallint, @estacaoano varchar(15), @dataporextenso varchar(50) --defina aqui o período para o qual deseja criar os dados set @datainicial = '01/jan/2020' set @datafinal = '31/dec/2020' delete from dim_tempo while @datainicial <= @datafinal begin set @data = @datainicial set @ano = year(@data) set @mes = month(@data) set @dia = day(@data) set @diasemana = datepart(weekday,@data) if @diasemana in (1,7) set @fimsemana = 's' else set @fimsemana = 'n' /* feriados locais/regionais e aqueles que não possuem data fixa (carnaval, páscoa e corpus cristis) tb devem ser adicionados aqui */ if (@mes = 1 and @dia in (1,2)) or (@mes = 12 and @dia = 31) --feriado universal set @nomeferiado = 'feriado universal' else if (@mes = 4 and @dia in (25)) --dia da liberdade set @nomeferiado = 'dia da liberdade' else if (@mes = 5 and @dia in (1)) --dia do trabalhador set @nomeferiado = 'dia do trabalhador' else if (@mes = 6 and @dia in (10)) --dia de portugal set @nomeferiado = 'dia de portugal' else if (@mes = 8 and @dia in (15)) --dia nossa senhora assuncao set @nomeferiado = 'dia nossa senhora assuncao' else if (@mes = 10 and @dia in (5)) --implantacao republica set @nomeferiado = 'implantacao republica' else if (@mes = 11 and @dia in (1)) --dia de finados set @nomeferiado = 'dia de finados' else if (@mes = 12 and @dia in (1)) --dia da restaurcao da independencia set @nomeferiado = 'proclamação da república' else if (@mes = 12 and @dia in (8)) --imaculada conceicao set @nomeferiado = 'imaculada conceicao' else if (@mes = 12 and @dia in (24,25)) --natal set @nomeferiado = 'natal' else set @nomeferiado = null if (@mes = 12 and @dia = 31) or --feriado universal (@mes = 4 and @dia = 24) or --dia da liberdade (@mes = 4 and @dia = 30) or --dia do trabalhador (@mes = 6 and @dia = 9) or --dia de portugal (@mes = 8 and @dia = 14) or --dia nossa senhora assuncao (@mes = 10 and @dia = 4) or --implantacao republica (@mes = 10 and @dia = 31) or --dia de finados (@mes = 11 and @dia = 30) or --dia da restaurcao da independencia (@mes = 12 and @dia = 7) or --imaculada conceicao (@mes = 12 and @dia = 24) --natal set @preferiado = 's' else set @preferiado = 'n' if (@mes = 1 and @dia = 1) or --feriado universal (@mes = 4 and @dia = 25) or --dia da liberdade (@mes = 5 and @dia = 1) or --dia do trabalhador (@mes = 6 and @dia = 10) or --dia de portugal (@mes = 8 and @dia = 15) or --dia nossa senhora assuncao (@mes = 10 and @dia = 5) or --implantacao republica (@mes = 11 and @dia = 1) or --dia de finados (@mes = 12 and @dia = 1) or --dia da restaurcao da independencia (@mes = 12 and @dia = 8) or --imaculada conceicao (@mes = 12 and @dia = 25) --natal set @feriado = 's' else set @feriado = 'n' if (@mes = 1 and @dia = 2) or --feriado universal (@mes = 4 and @dia = 26) or --dia da liberdade (@mes = 5 and @dia = 2) or --dia do trabalhador (@mes = 6 and @dia = 11) or --dia de portugal (@mes = 8 and @dia = 16) or --dia nossa senhora assuncao (@mes = 10 and @dia = 6) or --implantacao republica (@mes = 11 and @dia = 2) or --dia de finados (@mes = 12 and @dia = 2) or --dia da restaurcao da independencia (@mes = 12 and @dia = 9) or --imaculada conceicao (@mes = 12 and @dia = 26) --natal set @posferiado = 's' else set @posferiado = 'n' set @nomemes = case when @mes = 1 then 'janeiro' when @mes = 2 then 'fevereiro' when @mes = 3 then 'março' when @mes = 4 then 'abril' when @mes = 5 then 'maio' when @mes = 6 then 'junho' when @mes = 7 then 'julho' when @mes = 8 then 'agosto' when @mes = 9 then 'setembro' when @mes = 10 then 'outubro' when @mes = 11 then 'novembro' when @mes = 12 then 'dezembro' end set @nomemesabrev = case when @mes = 1 then 'jan' when @mes = 2 then 'fev' when @mes = 3 then 'mar' when @mes = 4 then 'abr' when @mes = 5 then 'mai' when @mes = 6 then 'jun' when @mes = 7 then 'jul' when @mes = 8 then 'ago' when @mes = 9 then 'set' when @mes = 10 then 'out' when @mes = 11 then 'nov' when @mes = 12 then 'dez' end if @fimsemana = 's' or @feriado = 's' set @diautil = 'n' else set @diautil = 's' set @nomediasemana = case when @diasemana = 1 then 'domingo' when @diasemana = 2 then 'segunda-feira' when @diasemana = 3 then 'terça-feira' when @diasemana = 4 then 'quarta-feira' when @diasemana = 5 then 'quinta-feira' when @diasemana = 6 then 'sexta-feira' else 'sábado' end set @nomediasemanaabrev = case when @diasemana = 1 then 'dom' when @diasemana = 2 then 'seg' when @diasemana = 3 then 'ter' when @diasemana = 4 then 'qua' when @diasemana = 5 then 'qui' when @diasemana = 6 then 'sex' else 'sáb' end set @bimestre = case when @mes in (1,2) then 1 when @mes in (3,4) then 2 when @mes in (5,6) then 3 when @mes in (7,8) then 4 when @mes in (9,10) then 5 else 6 end set @trimestre = case when @mes in (1,2,3) then 1 when @mes in (4,5,6) then 2 when @mes in (7,8,9) then 3 else 4 end set @nrsemanames = case when @dia < 8 then 1 when @dia < 15 then 2 when @dia < 22 then 3 when @dia < 29 then 4 else 5 end if @data between cast('23/sep/'+convert(char(4),@ano) as date) and cast('20/dec/'+convert(char(4),@ano) as date) set @estacaoano = 'outono' else if @data between cast('21/mar/'+convert(char(4),@ano) as date) and cast('20/jun/'+convert(char(4),@ano) as date) set @estacaoano = 'primavera' else if @data between cast('21/jun/'+convert(char(4),@ano) as date) and cast('22/sep/'+convert(char(4),@ano) as date) set @estacaoano = 'verao' else -- @data between 21/12 e 20/03 set @estacaoano = 'inverno' insert into dbo.DIM_TEMPO SELECT @data ,@ano ,@mes ,@dia ,@diaSemana ,datepart(dayofyear,@data) --DIA_ANO ,case when (@ano % 4) = 0 then 'S' else 'N' end -- ANO_BISSEXTO ,@diaUtil ,@fimSemana ,@feriado ,@preFeriado ,@posFeriado ,@nomeFeriado ,@nomeDiaSemana ,@nomeDiaSemanaAbrev ,@nomeMes ,@Nomemesabrev ,case when @dia < 16 then 1 else 2 end -- quinzena ,@bimestre ,@trimestre ,case when @mes < 7 then 1 else 2 end -- semestre ,@nrsemanames ,datepart(wk,@data)--nr_semana_ano, smallint ,@estacaoano ,lower(@nomediasemana + ', ' + cast(@dia as varchar) + ' de ' + @nomemes + ' de ' + cast(@ano as varchar)) ,null--evento, varchar(50)) set @datainicial = dateadd(day,1,@datainicial) end ------2------ create table dbo.FATO_CHUVA ( Id_chuva int identity(1,1) not null, -- id autoincrementável, não precisa de primary key aqui id_tempo int not null, id_tipo_chuva int not null, id_freguesia int not null, duracao int not null, -- duração em minutos quantidade_precipitacao decimal(10, 2) not null, -- quantidade em mm constraint pk_fato_chuva primary key (id_chuva), -- definindo a primary key com constraint foreign key (id_tipo_chuva) references dbo.DIM_TIPO_CHUVA(Id_tipo_chuva), foreign key (id_freguesia) references dbo.DIM_FREGUESIA(Id_freguesia) ); create table dbo.DIM_TIPO_CHUVA ( Id_tipo_chuva int identity(1,1) not null, nome_tipo_chuva varchar(50) not null, constraint pk_dim_tipo_chuva primary key (id_tipo_chuva) ); create table dbo.DIM_FREGUESIA ( Id_freguesia int identity(1,1) not null, nome_freguesia varchar(100) not null, distrito varchar(100) not null, constraint pk_dim_freguesia primary key (id_freguesia) ); -----selects das tabelas----- select * from dbo.Fato_chuva; select * from dbo.Dim_tipo_chuva; select * from dbo.Dim_freguesia; select * from dbo.Dim_tempo; -----drops das tabelas----- drop table dbo.Fato_chuva; drop table dbo.Dim_freguesia; drop table dbo.Dim_tipo_chuva; drop table dbo.DIM_TEMPO; ------3--------- -- Populando a tabela Dim_tipo_chuva insert into dbo.DIM_TIPO_CHUVA (NOME_TIPO_CHUVA) VALUES ('Fraca'), ('Moderada'), ('Forte'); -- Populando a tabela Dim_freguesia com mais 30 freguesias e distritos insert into dbo.DIM_FREGUESIA (Nome_freguesia, distrito) values ('lisboa', 'lisboa'), ('porto', 'porto'), ('coimbra', 'coimbra'), ('funchal', 'madeira'), ('braga', 'braga'), ('albufeira', 'faro'), ('cascais', 'lisboa'), ('aveiro', 'aveiro'), ('viseu', 'viseu'), ('sintra', 'lisboa'), ('amadora', 'lisboa'), ('oeiras', 'lisboa'), ('guimarães', 'braga'), ('leiria', 'leiria'), ('ponta delgada', 'açores'), ('portimão', 'faro'), ('évora', 'évora'), ('viana do castelo', 'viana do castelo'), ('fafe', 'braga'), ('bragança', 'bragança'), ('vila real', 'vila real'), ('celorico de basto', 'braga'), ('caldas da rainha', 'leiria'), ('castelo branco', 'castelo branco'), ('loulé', 'faro'), ('figueira da foz', 'coimbra'), ('tomar', 'santarém'), ('peniche', 'leiria'), ('torres vedras', 'lisboa'), ('barcelos', 'braga'), ('marinha grande', 'leiria'); -- populando a tabela fato_chuva insert into dbo.FATO_CHUVA (ID_TEMPO, ID_TIPO_CHUVA, ID_FREGUESIA, DURACAO, QUANTIDADE_PRECIPITACAO) VALUES (1, 1, 1, 30, 5.0), -- Exemplo: 30 minutos de chuva fraca em Lisboa (2, 2, 2, 45, 10.0), -- Exemplo: 45 minutos de chuva moderada no Porto (3, 3, 3, 60, 20.0), -- Exemplo: 60 minutos de chuva forte em Coimbra (4, 4, 4, 120, 50.0), -- Exemplo: 120 minutos de chuva intensa em Funchal (5, 5, 5, 90, 100.0), -- Exemplo: 90 minutos de chuva torrencial em Braga (6, 1, 6, 30, 3.0), -- Exemplo: 30 minutos de chuva fraca em Albufeira (7, 2, 7, 45, 8.0), -- Exemplo: 45 minutos de chuva moderada em Cascais (8, 3, 8, 60, 15.0), -- Exemplo: 60 minutos de chuva forte em Aveiro (9, 4, 9, 120, 40.0), -- Exemplo: 120 minutos de chuva intensa em Viseu (10, 5, 10, 90, 90.0); -- Exemplo: 90 minutos de chuva torrencial em Sintra --1. Número de ocorrências de precipitação, por dia de semana, por tipo de chuva: SELECT dt.NOME_DIA_SEMANA, tc.NOME_TIPO_CHUVA, COUNT(fc.ID_CHUVA) AS NUM_OCORRENCIAS FROM dbo.FATO_CHUVA fc JOIN dbo.DIM_TEMPO dt ON fc.ID_TEMPO = dt.ID_TEMPO JOIN dbo.DIM_TIPO_CHUVA tc ON fc.ID_TIPO_CHUVA = tc.Id_tipo_chuva group by dt.NOME_DIA_SEMANA, tc.Nome_tipo_chuva order by dt.NOME_DIA_SEMANA, tc.NOME_TIPO_CHUVA; --2. Quantidade total, por tipo de pluviosidade, por mês: Select dt.mes, sum(case when tc.NOME_TIPO_CHUVA = 'Fraca' THEN fc.Quantidade_precipitacao else 0 end) as fraca, sum(case when tc.NOME_TIPO_CHUVA = 'Moderada' THEN fc.Quantidade_precipitacao else 0 end) as moderada, sum(case when tc.NOME_TIPO_CHUVA = 'Forte' THEN fc.Quantidade_precipitacao else 0 end) as forte from dbo.FATO_CHUVA fc JOIN dbo.DIM_TEMPO dt ON fc.ID_TEMPO = dt.ID_TEMPO JOIN dbo.DIM_TIPO_CHUVA tc ON fc.ID_TIPO_CHUVA = tc.Id_tipo_chuva group by dt.Mes order by dt.MES; --3.Tempo médio de duração e quantidade, por tipo de pluviosidade: SELECT tc.NOME_TIPO_CHUVA, Avg(fc.duracao) as duracao, avg(fc.QUANTIDADE_PRECIPITACAO) AS Quantidade FROM dbo.FATO_CHUVA fc JOIN dbo.DIM_TIPO_CHUVA tc ON fc.ID_TIPO_CHUVA = tc.Id_tipo_chuva group by tc.Nome_tipo_chuva order by tc.NOME_TIPO_CHUVA; --4.Valores médios, por tipo de precipitação, por distrito: Select fg.distrito, sum(case when tc.NOME_TIPO_CHUVA = 'Fraca' THEN fc.Quantidade_precipitacao else 0 end) as fraca, sum(case when tc.NOME_TIPO_CHUVA = 'Moderada' THEN fc.Quantidade_precipitacao else 0 end) as moderada, sum(case when tc.NOME_TIPO_CHUVA = 'Forte' THEN fc.Quantidade_precipitacao else 0 end) as forte from dbo.FATO_CHUVA fc JOIN dbo.DIM_FREGUESIA fg ON fc.ID_FREGUESIA = fg.ID_FREGUESIA JOIN dbo.DIM_TIPO_CHUVA tc ON fc.ID_TIPO_CHUVA = tc.Id_tipo_chuva group by fg.Distrito order by fg.DISTRITO;

Category Writing & Translation
Subcategory Other
Project size Small
Is this a project or a position? Project
Required availability As needed

Delivery term: December 22, 2024

Skills needed