Creación de la Base de Datos

Preparación de la estructura

Creamos una base de datos SQLite

Leer diccionario de datos

dicc_ganaderos <- readxl::read_excel(here::here("db/dicc_gps.xlsx"), sheet = "dicc_ganaderos")
dicc_dispositivos <- readxl::read_excel(here::here("db/dicc_gps.xlsx"), sheet = "dicc_dispositivos")
dicc_explotaciones <- readxl::read_excel(here::here("db/dicc_gps.xlsx"), sheet = "dicc_explotaciones")

Crear base de datos y tablas

con <- dbConnect(RSQLite::SQLite(), 
                 dbname = here::here("db/db_gps.db"))

Diccionario Ganaderos

Creo la tabla dicc_ganaderos y especifico el campo id_ganadero como KEY. Ojo, no utilizo el nombre del ganadero por Ley Protección de Datos.

# %s(%s en la siguiente función hace referencia a cada uno de los argumentos posteriores

sql_statement <- sprintf("CREATE TABLE %s(%s, PRIMARY KEY(%s))", "dicc_ganaderos",
        paste(names(dicc_ganaderos), collapse =", "), 
        names(dicc_ganaderos)[1])

dbExecute(conn=con, statement=sql_statement)
dbWriteTable(con, "dicc_ganaderos", dicc_ganaderos, append = TRUE, row.names = FALSE)

Diccionario Explotaciones

Creo la tabla dicc_explotaciones y especifico el campo id_ganadero como FOREING KEY de la tabla dicc_ganaderos.

sql_statement <- sprintf("CREATE TABLE %s(%s, FOREIGN KEY(%s) REFERENCES %s(%s))", "dicc_explotaciones",
        paste(names(dicc_explotaciones), collapse =", "), 
        names(dicc_explotaciones)[1], "dicc_ganaderos", 
        names(dicc_ganaderos)[1])

dbExecute(conn=con, statement=sql_statement)
dbWriteTable(con, "dicc_explotaciones", dicc_explotaciones, append = TRUE, row.names = FALSE)

Diccionario Dispositivos

Creo la tabla dicc_dispositivos y especifico el campo codigo_gps como KEY. Incluyo el campo id_ganadero como FOREING KEY de la tabla dicc_ganaderos

sql_statement <- sprintf("CREATE TABLE %s(%s, PRIMARY KEY (%s) FOREIGN KEY(%s) REFERENCES %s(%s))",
                         "dicc_dispositivos",
                         paste(names(dicc_dispositivos), collapse =", "), 
                         names(dicc_dispositivos)[4], 
                         names(dicc_dispositivos)[2],
                         "dicc_ganaderos", 
                         names(dicc_ganaderos)[1])

dbExecute(conn=con, statement=sql_statement)
dbWriteTable(con, "dicc_dispositivos", dicc_dispositivos, append = TRUE, row.names = FALSE)

Tabla datos_gps

Creo la tabla datos_gps. Incluyo el campo codigo_gps como FOREIGN KEY de la tabla dicc_dispositivos

sql_statement <- "CREATE TABLE datos_gps(
id INTEGER PRIMARY KEY AUTOINCREMENT,codigo_gps,
                      id_itm INTEGER, 
                      id_user INTEGER, 
                      lat,
                      lng, 
                      time_stamp DATETIME,
                      month INTEGER, FOREIGN KEY(codigo_gps) REFERENCES dicc_dispositivos(codigo_gps))"


dbExecute(conn=con, statement=sql_statement)

Estructura de la base de datos

La estructura de la base de datos se puede ver en Figure 1

# Para crear este esquema:
# 1. Exporto la estructura de la base de datos desde DB Browser Lite (ver /db/schema_db_gps.sql)
# 2. Edito el archivo y guardo como /db/schema_db_gps_edited.txt 
# 3. Genero png en https://dbdiagram.io/