Conceitos

  • Dado: fatos que podem ser armazenados (nomes,endereços,telefones,…)
  • Base de Dados: coleçao de dados que se relacionam logicamente
  • Sistema de Gerência de Base de Dados (SGBD): coleção de programas que permite a criação e gerência de base de dados
  • Banco de Dados: nome genérico para softwares SGBD e Base de Dados

Função básica do SGBD

  • Simplificar o desenvolvimento de aplicações caracterizadas por uso intensivo de dados
  • Prover serviços que diminuem o tempo de desenvolvimento
  • Permitir a entrada, consultas e manipulação de dados de acordo com usuário

Exemplos de SGBD

  • MySQL
  • PostgreSQL
  • MongoDB
  • MariaDB

Níveis Modelos de Dados

  • Nível Semântico
  • Nível Lógico
  • Nível Físico

Modelagem de Dados

  • Objetivo:
  • Modelos:
  • Entidade-Relacionamento

Modelo Entidade-Relacionamento

  • Entidade:
  • Relacionamento:
  • Cardinalidade:

Diagrama Entidade-Relacionamento

DER.

DER.

UML
UML.

UML.

SQL

Explicar o que é o SQL

Instalação MySQL e Workbench

Digitar no terminal:

  • sudo apt-get update
  • sudo apt-get install mysql-server
  • definir usuário root
  • sudo apt-get install mysql-workbench

Criando usuários

  • Usuários utilizam o SGBD com proprósitos diferentes
  • Necessário determinar privilégios a usuários de acordo com suas necessidades

R e MySQL

Pacotes Necessários

  • DBI: utilizado para conectar-se a determinada Base de Dados, realizar consultas, modificar …
  • RMySQL: utilizado para auxiliar o pacote DBI em conexões com SGBD em MySQL

Instalando e Iniciando Pacotes

## Verificando se Pacotes estao instalados ##
pacotes <- c("DBI","RMySQL","stringr")
#install.packages(pacotes)
## Iniciando Pacotes ##
library(DBI)
library(RMySQL)
library(stringr)
rm(pacotes)

Criando e Manipulando Base de Dados

## data frame with 0 columns and 0 rows
## [1] TRUE
  1. Criar Base de Dados “testes” utilizando MySQL Workbench ou similar
  2. Modelar a Base de Dados “testes”
  3. Criar usuário “Teste_Aula_BD” com todos os privilégios para o BD “testes”
  4. Conectar-se a Base de Dados “testes” utilizando R
  5. Criar Tabelas Jornal, Responsavel e Noticia
  6. Inserir dados na Tabela a partir de arquivo csv
  7. Realizar consultas na tabela Noticia onde a data for maior que “2017-07-01” e o jornal for “Folha” de mídia “Online”
  8. Criar Views

Importante: liberar IP

Modelando Base de Dados

A base da dados “testes” está sendo criada para armazenar notícias de diferentes jornais que foram extraídas para serem analisadas e calcular o IIEBR. Cada notícia possui link,manchete,data,conteúdo(noticia), pertencem a um ou mais jornais e foram colocadas na BD por um responsável Cada jornal possui nome e pertence a uma mídia, podendo ser impresso ou online, e pode possuir várias notícias, sendo que jornais com o mesmo nome e mídias diferentes podem possuir a mesma notícia O nome da pessoa responsável deve ser armazenado na BD assim como o horário da operação de inserção das notícias
Modelagem_Dados_2.

Modelagem_Dados_2.

Fazendo conexao com BD “testes”

### Parametros ###
dbname="henriqu2_testes"
user <- "henriqu2_iibre"
password <- "123123iibre"
host='77.104.156.92'
port=3306
### Conexão com BD ###
db <- dbConnect(drv = MySQL(),
                host= host,
                port= port,
                dbname= dbname,
                user= user,
                pass= password)
### Verificando Tabelas no BD ###
dbListTables(conn = db)
## character(0)

Criando Tabelas Jornal,Responsavel e Noticia

### Criando Tabela 'Jornal'

query <- "CREATE TABLE Jornal(
            id INTEGER NOT NULL AUTO_INCREMENT,
            nome CHARACTER(60) NOT NULL,
            midia CHARACTER(60) NOT NULL,
            CONSTRAINT PK_Jornal PRIMARY KEY (id,nome,midia)
          )"
dbGetQuery(conn = db,
           statement = query)
## data frame with 0 columns and 0 rows
### Criando Tabela 'Responsavel'

query <- "CREATE TABLE Responsavel(
            id INTEGER NOT NULL AUTO_INCREMENT,
            nome CHARACTER(60) NOT NULL,
            op_time DATETIME NOT NULL,
            PRIMARY KEY(id)
          )"
dbGetQuery(conn = db,
           statement = query)
## data frame with 0 columns and 0 rows
### Criando Tabela 'Noticia'
query <- "CREATE TABLE Noticia(
            link CHARACTER(60) NOT NULL,
            manchete TEXT NOT NULL,
            noticia TEXT NOT NULL,
            categoria CHARACTER(60) NOT NULL,
            date DATE NOT NULL,
            Jornal_id INTEGER NOT NULL,
            Responsavel_id INTEGER NOT NULL,
            CONSTRAINT PK_Noticia PRIMARY KEY (link,Jornal_id),
            FOREIGN KEY(Jornal_id) REFERENCES Jornal(id) ON DELETE CASCADE,
            FOREIGN KEY(Responsavel_id) REFERENCES Responsavel(id) ON DELETE CASCADE
          )"
dbGetQuery(conn = db,
           statement = query)
## data frame with 0 columns and 0 rows
### Verificando Tabelas ###
dbListTables(conn = db)
## [1] "Jornal"      "Noticia"     "Responsavel"
### Fechando Conexão ###
dbDisconnect(conn = db)
## [1] TRUE

Inserindo dados na Base de Dados

  • Inserção de dados a partir de arquivo csv previamente preenchido
  • OBS: dados obtidos através da leitura dos arquivos de notícia do IIEBR

Tratamento dos dados

### Lendo arquivos ###
arquivos <- Sys.glob(paths = "./arquivos/*")
df_noticia <- read.csv2(file = arquivos[1],
                        stringsAsFactors = FALSE,
                        nrows = 10)
for(arquivo in arquivos){
  df_noticia_aux <- read.csv2(file = arquivo,
                              stringsAsFactors = FALSE,
                              nrows = 10)
  if(!isTRUE(all(df_noticia_aux == df_noticia))||
     df_noticia_aux$jornal != df_noticia$jornal){
    df_noticia <- rbind(df_noticia,df_noticia_aux)
  }
  rm(df_noticia_aux)
}
str(df_noticia)
## 'data.frame':    20 obs. of  6 variables:
##  $ link     : chr  "http://www1.folha.uol.com.br/poder/2017/04/1878932-fachin-ironiza-decisao-do-stf-que-soltou-presos-da-lava-jato.shtml" "http://www1.folha.uol.com.br/ciencia/2017/04/1878740-cientistas-descobrem-vestigios-humanos-mais-antigos-das-americas.shtml" "http://www1.folha.uol.com.br/mercado/2017/04/1878731-e-melhor-reduzir-10-da-jornada-que-demitir-10-diz-ceo-da-votorantim.shtml" "http://www1.folha.uol.com.br/mercado/2017/04/1878721-bancarios-e-metroviarios-saiba-quais-categorias-anunciaram-adesao-a-greve."| __truncated__ ...
##  $ date     : chr  "2017-05-26" "2017-05-26" "2017-05-26" "2017-05-26" ...
##  $ manchete : chr  "777.\n                                                \n                                                Folha de S.Paulo  - Pod"| __truncated__ "778.\n                                                \n                                                Folha de S.Paulo  - Ciê"| __truncated__ "779.\n                                                \n                                                Folha de S.Paulo  - Mer"| __truncated__ "780.\n                                                \n                                                Folha de S.Paulo  - Mer"| __truncated__ ...
##  $ categoria: chr  "Ilustrada" "Mundo" "Poder" "Mundo" ...
##  $ noticia  : chr  "Literatura de Moacyr Scliar vai além do judaísmo, dizem escritores\nFredy Vieira/Folhapress\nO escritor Moacyr Scliar em 2008\n"| __truncated__ "Concessão de vistos a cidadãos de países islâmicos cai 55% nos EUA\nISABEL FLECK\nRSS\nPublicidade\nEnquanto o decreto prevendo"| __truncated__ "Governo do PA devolve corpos em estado de putrefação aos parentes\nAvener Prado/Folhapress\nCerimônia de enterro de sem-terra m"| __truncated__ "Enchentes e deslizamentos de terra no Sri Lanka deixam ao menos 91 mortos\nDinuka Liyanawatte/Reuters\nHomem participa de opera"| __truncated__ ...
##  $ jornal   : chr  "Folha Impresso" "Folha Impresso" "Folha Impresso" "Folha Impresso" ...
### Pre Tratamento dos Dados ###
jornais <- c("Folha","Valor")
midias <- c("Impresso","Online")
df_noticia$responsavel <- "Teste_Aula"
midia <- data.frame(link=character(),
                    midia=character())
for(i in 1:length(midias)){
  df_noticia_aux <- df_noticia[which(str_detect(string = df_noticia$jornal,
                                                  pattern = midias[i]) == TRUE),]
  tryCatch({
    for(j in 1:nrow(df_noticia_aux)){
      if(str_detect(string = df_noticia_aux$jornal[j],
                    pattern = midias[i])){
        v_midia <- str_extract(string = df_noticia_aux$jornal[j],
                               pattern = midias[i])
        midia_row <- data.frame(link=df_noticia_aux$link[j],
                                midia=v_midia)
        midia <- rbind(midia,midia_row)
      }
    }
  },error=function(cond){
    message(cond)
  },finally = {
    rm(df_noticia_aux)
    rm(midia_row)
    rm(v_midia)
  })
}
df_noticia <- merge(df_noticia,midia)
rm(midia)
df_jornal <- data.frame(jornal=character(),
                          link=character())
for(i in 1:length(jornais)){
  df_noticia_aux <- df_noticia[which(str_detect(string = df_noticia$jornal,
                                                pattern = jornais[i]) == TRUE),]
  if(nrow(df_noticia_aux)>0){
    tryCatch({
      for(j in 1:nrow(df_noticia_aux)){
        if(str_detect(string = df_noticia_aux$jornal[j],
                      pattern = jornais[i])){
          v_jornal <- str_extract(string = df_noticia_aux$jornal[j],
                                  pattern = jornais[i])
          jornal_row <- data.frame(jornal=v_jornal,
                                   link=df_noticia_aux$link[j])
          df_jornal <- rbind(df_jornal,jornal_row)
        }
      }
    },error=function(cond){
      message(cond)
    })
  rm(v_jornal)
  rm(jornal_row)
  rm(df_noticia_aux)
  }
}
for(i in 1:length(jornais)){
  for(j in 1:nrow(df_noticia)){
    if(str_detect(string = df_noticia$jornal[j],
                  pattern = jornais[i])){
      df_noticia$jornal[j] <- str_extract(string = df_noticia$jornal[j],
                                          pattern = jornais[i])
    }
  }
}
df_noticia <- merge(df_noticia,df_jornal)
rm(df_jornal)
unique(df_noticia$jornal)
## [1] "Folha"
unique(df_noticia$midia)
## [1] Impresso Online  
## Levels: Impresso Online

Inserção dos dados

Tabela Jornal
### Inserindo Dados no BD ###
db <- dbConnect(drv = MySQL(),
                host= host,
                port= port,
                dbname= dbname,
                user= user,
                pass= password)
### Inserindo na Tabela Jornal ###
query_select <- paste0("SELECT * FROM Jornal WHERE nome = '",unique(df_noticia$jornal),
                       "' AND midia = '",unique(df_noticia$midia),"'")
for(query_select in query_select){
  result <- dbGetQuery(conn = db,
                       statement = query_select)
  if(nrow(result)==0){
    query <- paste0("INSERT INTO Jornal(nome,midia)
                    VALUES('",unique(df_noticia$jornal),
                    "','",unique(df_noticia$midia),"')")
    for(q in query){
      tryCatch({
        dbGetQuery(conn = db,
                   statement = q)
      },error=function(cond){
        message(cond)
      })
    }
  }
}
rm(query_select)
jornais <- dbReadTable(conn = db,
                       name = "Jornal")
str(jornais)
## 'data.frame':    2 obs. of  3 variables:
##  $ id   : int  1 2
##  $ nome : chr  "Folha" "Folha"
##  $ midia: chr  "Impresso" "Online"
Tabela Responsavel
### Inserindo na Tabela Responsavel ###
query_select <- paste0("SELECT * FROM Responsavel WHERE nome ='",
                       unique(df_noticia$responsavel),"'") 
for(query_select in query_select){
  result <- dbGetQuery(conn = db,
                       statement = query_select)
  if(nrow(result)==0){
   ## Pegando data e hora da operação ##
   time <- str_sub(string = Sys.time(),
                   start = 0,
                   end = -1)
   time <- sub(x = time,
               pattern = "'",
               replacement = " ")
   query <- paste0("INSERT INTO Responsavel(nome,op_time) VALUES ('",unique(df_noticia$responsavel),
                  "','",time,"')")
  for(q in query){
     tryCatch({
       dbGetQuery(conn = db,
                  statement = q)
     },error=function(cond){
       message(cond)
     })
    }
  }
}
rm(query_select)
responsavel <- dbReadTable(conn = db,
                           name = "Responsavel")
str(responsavel)
## 'data.frame':    1 obs. of  3 variables:
##  $ id     : int 1
##  $ nome   : chr "Teste_Aula"
##  $ op_time: chr "2017-07-21 14:06:22"
Tabela Noticia
df_noticia$responsavel_id <- responsavel$id
df_noticia$jornal_id <- jornais$id
df_noticia_aux <- df_noticia
### Inserindo na Tabela Noticia ###
query_select <- paste0("SELECT * FROM Noticia WHERE link ='",
                       df_noticia$link,"'")
df_noticia$manchete <- gsub(x = df_noticia$manchete,
                                pattern = "'",
                                replacement = " ")
df_noticia$noticia <- gsub(x = df_noticia$noticia,
                           pattern = "'",
                           replacement = " ")
Encoding(df_noticia$manchete) <- "latin1"
df_noticia$manchete <- iconv(x = df_noticia$manchete,
                            from = "latin1",
                            to = "UTF-8")
Encoding(df_noticia$noticia) <- "latin1"
df_noticia$manchete <- iconv(x = df_noticia$noticia,
                             from = "latin1",
                             to = "UTF-8")
for(i in 1:length(query_select)){
  result <- dbGetQuery(conn = db,
                       statement = query_select[i])
  if(nrow(result)==0){
    query <- paste0("INSERT INTO Noticia(link,manchete,noticia,categoria,date,Jornal_id,Responsavel_id) VALUES('",
      df_noticia$link[i],
      "','",df_noticia$manchete[i],
      "','",df_noticia$noticia[i],
      "','",df_noticia$categoria[i],
      "','",df_noticia$date[i],
      "','",df_noticia$jornal_id[i],
      "','",df_noticia$responsavel_id[i],"')")
      tryCatch({
        dbGetQuery(conn = db,
                   statement = query)
      })
  }
}
rm(query_select)
noticia <- dbReadTable(conn=db,
                       name = "Noticia")
str(noticia)
## 'data.frame':    20 obs. of  7 variables:
##  $ link          : chr  "http://www1.folha.uol.com.br/ciencia/2017/04/1878740-cientis" "http://www1.folha.uol.com.br/colunas/julioabramczyk/2017/07/" "http://www1.folha.uol.com.br/colunas/marcelocoelho/2017/04/1" "http://www1.folha.uol.com.br/colunas/reinaldo/2017/04/187860" ...
##  $ manchete      : chr  "Concessão de vistos a cidadãos de países islâmicos cai 55% nos EUA\nISABEL FLECK\nRSS\nPublicidade\nEnquanto o decreto prev"| __truncated__ "Médico formado pela Escola Paulista de Medicina/Unifesp, faz parte do corpo clínico do Hospital Santa Catarina, onde foi dire"| __truncated__ " Velozes e Furiosos 8  é 30º filme a arrecadar US$ 1 bilhão\nDivulgação\nReceita do sucesso:  Velozes e Furiosos 8 , o tri"| __truncated__ "Emicida e Rael lançam álbum com os rappers portugueses Capicua e Valete\nDivulgação\nDa esq. para dir., os rappers Rael, Em"| __truncated__ ...
##  $ noticia       : chr  "Concessão de vistos a cidadãos de países islâmicos cai 55% nos EUA\nISABEL FLECK\nRSS\nPublicidade\nEnquanto o decreto prev"| __truncated__ "Médico formado pela Escola Paulista de Medicina/Unifesp, faz parte do corpo clínico do Hospital Santa Catarina, onde foi dire"| __truncated__ " Velozes e Furiosos 8  é 30º filme a arrecadar US$ 1 bilhão\nDivulgação\nReceita do sucesso:  Velozes e Furiosos 8 , o tri"| __truncated__ "Emicida e Rael lançam álbum com os rappers portugueses Capicua e Valete\nDivulgação\nDa esq. para dir., os rappers Rael, Em"| __truncated__ ...
##  $ categoria     : chr  "Mundo" "Colunistas" "Ilustrada" "Ilustrada" ...
##  $ date          : chr  "2017-05-26" "2017-07-15" "2017-05-26" "2017-05-26" ...
##  $ Jornal_id     : int  1 2 1 2 1 2 1 2 1 2 ...
##  $ Responsavel_id: int  1 1 1 1 1 1 1 1 1 1 ...
Encoding(x = noticia$noticia) <- "UTF-8"

Consultando dados

noticia$noticia <- gsub(x = noticia$noticia,
                        pattern = "\n",
                        replacement = " ")
noticia$noticia[1]
## [1] "Concessão de vistos a cidadãos de países islâmicos cai 55% nos EUA ISABEL FLECK RSS Publicidade Enquanto o decreto prevendo o veto a cidadãos de seis países de maioria muçulmana segue suspenso pela Justiça , o governo de Donald Trump pode ter estabelecido uma forma mais sutil de frear a entrada do grupo no país: reduzir a emissão de vistos. Segundo dados do Departamento de Estado, em abril, o número de vistos de visitantes para pessoas nascidas em todos os países da lista -Síria, Libia, Somália, Sudão, Irã e Iêmen- caiu 21% em relação a março deste ano e 55% à média mensal de 2016. O total de vistos de visitantes para todos os países também diminuiu, em abril, em relação a 2016 -mas 15%. Para brasileiros, caiu 4% em relação à média de 2016. A maior queda se deu entre os iemenitas, 72%: de uma média de 434 vistos de não imigrante emitidos por mês em 2016 para 122 em abril. Para os sírios, o governo americano havia concedido 758 vistos ao mês em 2016. No último mês, foram 265. O decreto de Trump , que usa como justificativa a segurança nacional para barrar temporariamente cidadãos dos seis países, foi suspenso em decisões de três tribunais federais americanos. Na última quinta (25), teve nova derrota numa corte de apelação . Ainda há uma decisão pendente na mesma corte de apelação que manteve suspenso o seu primeiro decreto sobre o tema. O governo não confirmou se pretende levar o caso à Suprema Corte. Um levantamento feito pelo site especializado Politico mostra ainda que houve queda de quase 20% quando analisados quase 50 países de maioria muçulmanos. Para Stephen Pattison, um advogado de imigração que trabalhou por quase 30 anos como agente consular em postos americanos, é possível que tenha havido uma redução na procura por visto por parte dos cidadãos desses países diante das ameaças de Trump de veto. Ele, contudo, acredita que houve também uma mudança na hora da entrevista e da emissão. \"Algumas pessoas podem ter cancelados suas viagens\", disse Pattison ao Politico. \"Mas é ingênuo pensar que o que está acontecendo em Washington não pesa nas decisões consulares.\" PORTA FECHADA - Vistos de não imigrantes emitidos CÍCLICA O porta-voz do Escritório de Assuntos Consulares do Departamento de Estado, William Cocks, relativizou os números, dizendo que a demanda por vistos é \"cíclica\": \"Os vistos tendem a crescer nas altas temporadas de viagem, como o verão e os feriados de fim de ano\". Em março, a imprensa americana revelou telegramas em que o secretário de Estado americano, Rex Tillerson, ordenava um aumento no rigor de entrevistas para concessão de vistos a determinados grupos de viajantes. Nos documentos, ele orienta os postos a identificar \"populações que justifiquem maior análise\", sobre as quais serão adotadas medidas mais rígidas, como um questionamento detalhado sobre o histórico do solicitante de visto. Entre as informações a cobrar nesse caso estão o histórico de viagens, trabalho e endereços da pessoa nos últimos 15 anos, além de dados de telefone, e-mail e redes sociais nos últimos cinco anos. As regras poupam 38 países cujos cidadãos têm entrada facilitada pelo programa de isenção de vistos, como a maioria das nações europeias, Austrália, Nova Zelândia, Japão e Coreia do Sul.  "
dbDisconnect(conn = db)
## [1] TRUE