Desafio08

##SQLite

O SQLite é uma biblioteca embutida em um processo único que é autônomo, não necessita de servidor e exige zero de configuração. O código é de domínio público e seu uso é gratuito em qualquer tipo de utilização. Trata-se de um sistema SQL completo, capaz de utilizar múltiplas tabelas, índices, gatilhos e visões. Ele funciona, virtualmente, em qualquer plataforme (incluindo móveis) e pesa cerca de 600KB.

##Objetivos

Ao fim deste laboratório, você deverá ser capaz de:

Conectar-se a um banco de dados do tipo SQLite utilizando o R como interface; Explorar quais são as tabelas disponíveis no referido banco de dados; Identificar quais são as colunas de uma dada tabela existente no banco de dados; Realizar pesquisas simples; Extrair registros do banco de dados e armazená-las em objetos do R; Realizar algumas pesquisas complexas, utilizando WHERE, INNER JOIN. Criar novas tabelas no banco de dados. Fonte do problema: Estes dados são de uma iniciativa de publicidade de desempenho de alunos e professores nas Universidades Americanas. A UW-Madison disponibilizou os seus dados online (no Office of the Registrar: https://registrar.wisc.edu/), e alguém trouxe os dados para uma competição no Kaggle (https://www.kaggle.com/Madgrades/uw-madison-courses). Nesta atividades, examinaremos disciplinas oferecidas no assunto de Estatística pela referida Universidade. O esquema do banco de dados é apresentado na figura abaixo.

##Observações

Esta tarefa não deve ser realizada no site jupyter.ime.unicamp.br ; Você deve realizar o download dos dados para o computador que estiver utilizando e, então, iniciar a atividade; Recomenda-se a utilização dos seguintes pacotes: RSQLite Toda a atividade deve ser realizada utilizando-se apenas de SQLite (i.e., não utilize dbplyr);

##Atividade

1. Baixe o arquivo database.sqlite3. Conecte-se a ele usando o pacote RSQLite, armazenando a conexão em uma variável conn.

# Instalar pacote se necessário
# install.packages("RSQLite")

# Carregar o pacote
library(RSQLite)

# Conectar ao banco de dados
conn <- dbConnect(RSQLite::SQLite(), "database.sqlite3")

# Verificar se a conexão foi bem sucedida
dbListTables(conn)

 [1] "course_offerings"    "courses"             "grade_distributions"
 [4] "instructors"         "rooms"               "schedules"          
 [7] "sections"            "subject_memberships" "subjects"           
[10] "teachings"          

2. Quem são e quantos são os professores que lecionaram disciplinas cujo tópico era estatística (subjects.abbreviation=‘STAT’);

# Query corrigida baseada na estrutura real
query_professores <- "
SELECT DISTINCT i.name, COUNT(DISTINCT co.uuid) as num_disciplinas
FROM instructors i
INNER JOIN teachings t ON i.id = t.instructor_id
INNER JOIN sections s ON t.section_uuid = s.uuid
INNER JOIN course_offerings co ON s.course_offering_uuid = co.uuid
INNER JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
INNER JOIN subjects sub ON sm.subject_code = sub.code
WHERE sub.abbreviation = 'STAT'
GROUP BY i.name
ORDER BY num_disciplinas DESC;"

professores_stat <- dbGetQuery(conn, query_professores)

3. O GPA americano é definido numa escala de 0 a 4, em que A = 4, AB = 3.5, B = 3, BC = 2.5, C = 2, D = 1 e F = 0. Determinando a nota média de cada oferecimento pela ponderação da quantidade de alunos em cada extrato com os valores numéricos de cada conceito, indique (no que se referente a disciplinas no assunto de estatística):

3.1. Quem é o professor mais difícil? 3.2. Quem é o professor mais fácil?

# Query para dados dos professores
query_gpa_professores <- "
SELECT 
    i.name as professor,
    co.name as disciplina,
    -- Converter contagens de texto para números
    CAST(gd.a_count AS INTEGER) as a_count,
    CAST(gd.ab_count AS INTEGER) as ab_count,
    CAST(gd.b_count AS INTEGER) as b_count,
    CAST(gd.bc_count AS INTEGER) as bc_count,
    CAST(gd.c_count AS INTEGER) as c_count,
    CAST(gd.d_count AS INTEGER) as d_count,
    CAST(gd.f_count AS INTEGER) as f_count
FROM grade_distributions gd
INNER JOIN course_offerings co ON gd.course_offering_uuid = co.uuid
INNER JOIN sections s ON co.uuid = s.course_offering_uuid
INNER JOIN teachings t ON s.uuid = t.section_uuid
INNER JOIN instructors i ON t.instructor_id = i.id
INNER JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
INNER JOIN subjects sub ON sm.subject_code = sub.code
WHERE sub.abbreviation = 'STAT';"

dados_gpa <- dbGetQuery(conn, query_gpa_professores)

# Função para calcular GPA a partir da estrutura de colunas separadas
calcular_gpa_from_columns <- function(a, ab, b, bc, c, d, f) {
  # Valores do GPA para cada conceito
  valores_gpa <- c(A = 4.0, AB = 3.5, B = 3.0, BC = 2.5, C = 2.0, D = 1.0, F = 0.0)
  
  # Calcular totais
  total_a <- sum(a, na.rm = TRUE)
  total_ab <- sum(ab, na.rm = TRUE)
  total_b <- sum(b, na.rm = TRUE)
  total_bc <- sum(bc, na.rm = TRUE)
  total_c <- sum(c, na.rm = TRUE)
  total_d <- sum(d, na.rm = TRUE)
  total_f <- sum(f, na.rm = TRUE)
  
  total_alunos <- total_a + total_ab + total_b + total_bc + total_c + total_d + total_f
  
  if (total_alunos == 0) return(NA)
  
  # Calcular GPA ponderado
  gpa_ponderado <- (total_a * 4.0 + total_ab * 3.5 + total_b * 3.0 + 
                     total_bc * 2.5 + total_c * 2.0 + total_d * 1.0 + total_f * 0.0) / total_alunos
  
  return(gpa_ponderado)
}

# Calcular GPA por professor
gpa_por_professor <- aggregate(cbind(a_count, ab_count, b_count, bc_count, c_count, d_count, f_count) ~ professor, 
                              dados_gpa, 
                              FUN = function(x) sum(as.numeric(x), na.rm = TRUE))

# Aplicar função de cálculo de GPA
gpa_por_professor$gpa_medio <- mapply(calcular_gpa_from_columns,
                                     gpa_por_professor$a_count,
                                     gpa_por_professor$ab_count,
                                     gpa_por_professor$b_count,
                                     gpa_por_professor$bc_count,
                                     gpa_por_professor$c_count,
                                     gpa_por_professor$d_count,
                                     gpa_por_professor$f_count)

# Remover NAs e ordenar
resultado_professores <- gpa_por_professor[!is.na(gpa_por_professor$gpa_medio), c("professor", "gpa_medio")]
resultado_professores <- resultado_professores[order(resultado_professores$gpa_medio), ]

cat("Professor mais difícil (menor GPA):", resultado_professores$professor[1], 
    "- GPA:", round(resultado_professores$gpa_medio[1], 3), "\n")

Professor mais difícil (menor GPA): JAMES D KUELBS - GPA: 2.608 

cat("Professor mais fácil (maior GPA):", 
    resultado_professores$professor[nrow(resultado_professores)], 
    "- GPA:", round(resultado_professores$gpa_medio[nrow(resultado_professores)], 3), "\n")

Professor mais fácil (maior GPA): YAJUAN SI - GPA: 4 

3.3. Qual é a disciplina mais difícil? 3.4. Qual é a disciplina mais fácil?

# Query para dados das disciplinas
query_gpa_disciplinas <- "
SELECT 
    co.name as disciplina,
    CAST(gd.a_count AS INTEGER) as a_count,
    CAST(gd.ab_count AS INTEGER) as ab_count,
    CAST(gd.b_count AS INTEGER) as b_count,
    CAST(gd.bc_count AS INTEGER) as bc_count,
    CAST(gd.c_count AS INTEGER) as c_count,
    CAST(gd.d_count AS INTEGER) as d_count,
    CAST(gd.f_count AS INTEGER) as f_count
FROM grade_distributions gd
INNER JOIN course_offerings co ON gd.course_offering_uuid = co.uuid
INNER JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
INNER JOIN subjects sub ON sm.subject_code = sub.code
WHERE sub.abbreviation = 'STAT';"

dados_disciplinas <- dbGetQuery(conn, query_gpa_disciplinas)

# Calcular GPA por disciplina
gpa_por_disciplina <- aggregate(cbind(a_count, ab_count, b_count, bc_count, c_count, d_count, f_count) ~ disciplina, 
                               dados_disciplinas, 
                               FUN = function(x) sum(as.numeric(x), na.rm = TRUE))

# Aplicar função de cálculo de GPA
gpa_por_disciplina$gpa_medio <- mapply(calcular_gpa_from_columns,
                                      gpa_por_disciplina$a_count,
                                      gpa_por_disciplina$ab_count,
                                      gpa_por_disciplina$b_count,
                                      gpa_por_disciplina$bc_count,
                                      gpa_por_disciplina$c_count,
                                      gpa_por_disciplina$d_count,
                                      gpa_por_disciplina$f_count)

# Remover NAs e ordenar
resultado_disciplinas <- gpa_por_disciplina[!is.na(gpa_por_disciplina$gpa_medio), c("disciplina", "gpa_medio")]
resultado_disciplinas <- resultado_disciplinas[order(resultado_disciplinas$gpa_medio), ]

cat("Disciplina mais difícil (menor GPA):", resultado_disciplinas$disciplina[1], 
    "- GPA:", round(resultado_disciplinas$gpa_medio[1], 3), "\n")

Disciplina mais difícil (menor GPA): Intro-Theory of Probability - GPA: 2.886 

cat("Disciplina mais fácil (maior GPA):", 
    resultado_disciplinas$disciplina[nrow(resultado_disciplinas)], 
    "- GPA:", round(resultado_disciplinas$gpa_medio[nrow(resultado_disciplinas)], 3), "\n")

Disciplina mais fácil (maior GPA): Sample Survey Theory&Method - GPA: 4 

4. Desconecte do banco de dados.

# Desconectar do banco de dados
dbDisconnect(conn)