{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "6fgU_vTdm2xH" }, "source": [ "#**Control 3**" ] }, { "cell_type": "markdown", "metadata": { "id": "mjADSlwdncPi" }, "source": [ "##Setup" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "sBDirdKpAexk", "outputId": "1715caeb-8a6a-4d7b-e8be-061240005fe7" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: gdown in /usr/local/lib/python3.10/dist-packages (4.6.6)\n", "Collecting gdown\n", " Downloading gdown-4.7.1-py3-none-any.whl (15 kB)\n", "Requirement already satisfied: filelock in /usr/local/lib/python3.10/dist-packages (from gdown) (3.13.1)\n", "Requirement already satisfied: requests[socks] in /usr/local/lib/python3.10/dist-packages (from gdown) (2.31.0)\n", "Requirement already satisfied: six in /usr/local/lib/python3.10/dist-packages (from gdown) (1.16.0)\n", "Requirement already satisfied: tqdm in /usr/local/lib/python3.10/dist-packages (from gdown) (4.66.1)\n", "Requirement already satisfied: beautifulsoup4 in /usr/local/lib/python3.10/dist-packages (from gdown) (4.11.2)\n", "Requirement already satisfied: soupsieve>1.2 in /usr/local/lib/python3.10/dist-packages (from beautifulsoup4->gdown) (2.5)\n", "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests[socks]->gdown) (3.3.2)\n", "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests[socks]->gdown) (3.4)\n", "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests[socks]->gdown) (2.0.7)\n", "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests[socks]->gdown) (2023.7.22)\n", "Requirement already satisfied: PySocks!=1.5.7,>=1.5.6 in /usr/local/lib/python3.10/dist-packages (from requests[socks]->gdown) (1.7.1)\n", "Installing collected packages: gdown\n", " Attempting uninstall: gdown\n", " Found existing installation: gdown 4.6.6\n", " Uninstalling gdown-4.6.6:\n", " Successfully uninstalled gdown-4.6.6\n", "Successfully installed gdown-4.7.1\n", "Downloading...\n", "From (uriginal): https://drive.google.com/uc?id=1mGxfX7NKoKxlpaQxvCZzq6niLpf3Ziaq\n", "From (redirected): https://drive.google.com/uc?id=1mGxfX7NKoKxlpaQxvCZzq6niLpf3Ziaq&confirm=t&uuid=100c82f8-fc77-4792-bc94-8b2a863a0122\n", "To: /content/VW_VOTARON_2020PLEB_Datos_completos.zip\n", "100% 42.0M/42.0M [00:00<00:00, 65.9MB/s]\n" ] } ], "source": [ "!pip install --upgrade --no-cache-dir gdown\n", "!gdown https://drive.google.com/uc?id=1mGxfX7NKoKxlpaQxvCZzq6niLpf3Ziaq" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "7kQZDmJmAkyc", "outputId": "9e1f11d6-f5c7-496d-c968-e37b06a7a060" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Archive: VW_VOTARON_2020PLEB_Datos_completos.zip\n", " inflating: VW_VOTARON_2020PLEB_Datos completos.csv \n" ] } ], "source": [ "!unzip \"VW_VOTARON_2020PLEB_Datos_completos.zip\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "aoNEJ0DJAnWw" }, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "CGop1xFWAp7X" }, "outputs": [], "source": [ "df = pd.read_csv('VW_VOTARON_2020PLEB_Datos completos.csv', sep=';')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 504 }, "id": "_1FUo9TXA4B7", "outputId": "e3d4facb-4a80-418a-fcc8-f3e5837c34f6" }, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CedulaCircunscripcionComunaDVEdadNacionalidadPais DomicilioPais NacimientoPartidoProvinciaRango EdadRegionSexoSufragioVotoExteriorNúmero de registrosVotaron
00CoyhaiqueCoyhaique022chilenaChileChile[130] FEDERACION REGIONALISTA VERDE SOCIALCoyhaique20-24De Aysen Del General Carlos Ibañez Del CampofemeninosufragóNacional11.0
10El PuertoValparaiso089chilenaChileChileSIN PARTIDOValparaiso80 o +De Valparaisofemeninono sufragóNacional1NaN
20IquiqueIquique099chilenaChileChileSIN PARTIDOIquique80 o +De Tarapacamasculinono sufragóNacional1NaN
30Rio TranquiloRio Ibañez022chilenaChileChileSIN PARTIDOGeneral Carrera20-24De Aysen Del General Carlos Ibañez Del CampomasculinosufragóNacional11.0
40El PuertoValparaiso095chilenaChileChileSIN PARTIDOValparaiso80 o +De Valparaisomasculinono sufragóNacional1NaN
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", " \n", "\n", "\n", "\n", " \n", "
\n", "
\n", "
\n" ], "text/plain": [ " Cedula Circunscripcion Comuna DV Edad Nacionalidad Pais Domicilio \\\n", "0 0 Coyhaique Coyhaique 0 22 chilena Chile \n", "1 0 El Puerto Valparaiso 0 89 chilena Chile \n", "2 0 Iquique Iquique 0 99 chilena Chile \n", "3 0 Rio Tranquilo Rio Ibañez 0 22 chilena Chile \n", "4 0 El Puerto Valparaiso 0 95 chilena Chile \n", "\n", " Pais Nacimiento Partido \\\n", "0 Chile [130] FEDERACION REGIONALISTA VERDE SOCIAL \n", "1 Chile SIN PARTIDO \n", "2 Chile SIN PARTIDO \n", "3 Chile SIN PARTIDO \n", "4 Chile SIN PARTIDO \n", "\n", " Provincia Rango Edad Region \\\n", "0 Coyhaique 20-24 De Aysen Del General Carlos Ibañez Del Campo \n", "1 Valparaiso 80 o + De Valparaiso \n", "2 Iquique 80 o + De Tarapaca \n", "3 General Carrera 20-24 De Aysen Del General Carlos Ibañez Del Campo \n", "4 Valparaiso 80 o + De Valparaiso \n", "\n", " Sexo Sufragio VotoExterior Número de registros Votaron \n", "0 femenino sufragó Nacional 1 1.0 \n", "1 femenino no sufragó Nacional 1 NaN \n", "2 masculino no sufragó Nacional 1 NaN \n", "3 masculino sufragó Nacional 1 1.0 \n", "4 masculino no sufragó Nacional 1 NaN " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "markdown", "metadata": { "id": "nvX7ojdvUm_n" }, "source": [ "##Pregunta 1\n", "**Para este control trabajaremos con una base de datos real (anonimizada) del SERVEL acerca del plebiscito, he indica si cada ciudadano voto o no en el plebiscito del 2020.**" ] }, { "cell_type": "markdown", "metadata": { "id": "jDQSGf6eNC8L" }, "source": [ "###a)\n", "Reemplace en la columna 'Votaron' los NaN por 0." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "4a-bsuqRcTaN" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "acFzjOCIBzsa" }, "source": [ "###b)\n", "Calcule el tamaño del padrón electoral y de los votantes por primera vez." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "4JEIZZUgcTvT" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "_Fl7cmNEBBug" }, "source": [ "###c)\n", "¿Cuántas personas votaron y no votaron por rango de edad?\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "C1tiOBye5-04" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "-GYRxO34EYRa" }, "source": [ "###d)\n", "¿Cuántas personas por partido votaron vs los que no votaron? (incluya los SIN PARTIDO)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "JcIS_HdB6HPi" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "s6meu0wfHgwD" }, "source": [ "###e)\n", "¿Cuantos chilenos del padrón nacieron en chile?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "pPugx-276sfl" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "bzVjtryAA3b0" }, "source": [ "###f)\n", "¿Cuantas comunas y circunscripciones hay en Chile?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "KInyGCuR--Vd" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "_jvy3iaD-sqc" }, "source": [ "###g)\n", "Entregue un gráfico (matplotlib.pyplot) que indique el número de personas que votaron y los que no por percentil de edad" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "PW2NEn8k_IZr" }, "outputs": [], "source": [ "# Escriba aquí su respuesta" ] }, { "cell_type": "markdown", "metadata": { "id": "yxfYmJWOIQ1Z" }, "source": [ "##Pregunta 2: Índices, algoritmos y transacciones\n", "\n", "Para esta pregunta considere la tabla Persona(id int, nombre VARCHAR(100), edad int)" ] }, { "cell_type": "markdown", "metadata": { "id": "3Sxe278IIUGO" }, "source": [ "###a\\)" ] }, { "cell_type": "markdown", "metadata": { "id": "iQ6eHZP7IZLw" }, "source": [ "Es natural pensar que la edad pueda tener valores repetidos para distintas personas.\n", "\n", "\n", "* Explique cómo se puede hacer que un B+Tree pueda indexar el atributo edad de la relación Persona.\n", "* Mencione cómo cambian los costos de busqueda para consultas de igualdad y rango.\n", "\n", "\n", "\n", "Hint: piense en las overflow pages del Hash." ] }, { "cell_type": "markdown", "metadata": { "id": "fQWY63yuIyqf" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "e8eegb0RJD7I" }, "source": [ "###b)" ] }, { "cell_type": "markdown", "metadata": { "id": "tlp9dlzxJHdl" }, "source": [ "Suponga que en esta relación la primary key está indexada con un B+Tree unclustered, mientras que la edad está indexada con un Hash Index clustered. La relación tiene 1 millón de tuplas y la columna id posee los valores del 0 al 999999. En cada página caben 50 tuplas y 200 punteros. El árbol es de altura h y las hojas están ocupadas al 75%. El Hash Index tiene una página por bucket (i.e. es suficientemente grande y no tiene overflow pages), y cada edad va a parar a un bucket distinto.\n", "\n", "Indique el costo en I/O de las siguientes consultas:" ] }, { "cell_type": "markdown", "metadata": { "id": "iIhcByWXJYNc" }, "source": [ "####1.\tSELECT * FROM Persona WHERE id=5555" ] }, { "cell_type": "markdown", "metadata": { "id": "oTiAHGRbJf2n" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "U1d694IzJkWJ" }, "source": [ "####2.\tSELECT * FROM Persona WHERE nombre=’John Doe’" ] }, { "cell_type": "markdown", "metadata": { "id": "ltcgnMAdJkWK" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "EMRIIGL7JkgF" }, "source": [ "####3.\tSELECT * FROM Persona WHERE edad=55" ] }, { "cell_type": "markdown", "metadata": { "id": "LKbdvM9EJkgF" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "1jNwM4t5JlKX" }, "source": [ "####4.\tSELECT * FROM Persona WHERE id < 100000" ] }, { "cell_type": "markdown", "metadata": { "id": "8Bkhy1axJlKm" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "GJpGAWruJlNW" }, "source": [ "####5.\tSELECT * FROM Persona WHERE edad>=25 and edad < 55" ] }, { "cell_type": "markdown", "metadata": { "id": "LM-riW6FJlNW" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "wqXG-sUvJ8qs" }, "source": [ "##Pregunta 3: Transacciones y logs" ] }, { "cell_type": "markdown", "metadata": { "id": "i-4mVmf4cfOO" }, "source": [ "###a) Redo Logging\n", "Suponga que su sistema tuvo una falla. Al reiniciar el sistema, el sistema se encuentra con el log file que se muestra a continuación, en la tabla “Log Redo”. Suponiendo que la política de recovery es la de Redo Logging, indique:" ] }, { "cell_type": "markdown", "metadata": { "id": "fdRQhnLZeNvW" }, "source": [ "| Log Redo |\n", "| ---------------------- |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\ |\n", "| \\   |\n", "| \\   |\n", "| \\   |\n", "| \\   |\n", "| \\   |\n" ] }, { "cell_type": "markdown", "metadata": { "id": "6WvoOsrFcuZE" }, "source": [ "####i.\tDesde qué parte del log debo comenzar el proceso de redo.\n" ] }, { "cell_type": "markdown", "metadata": { "id": "GTqKNr8wdQiq" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "MXAA_I7ScxxJ" }, "source": [ "####ii.\tQué variables deben rehacer sus cambios y cuál es el valor con el que quedarán." ] }, { "cell_type": "markdown", "metadata": { "id": "suXd8hD-dQYd" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "s7r4HRg3c2nO" }, "source": [ "####iii.\tQué variables (de las que aparecen en el log) no son cambiadas en el proceso." ] }, { "cell_type": "markdown", "metadata": { "id": "z4WgHOzudQC5" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "5g2I8X7Rc45i" }, "source": [ "####iv.\tSi no hubiesemos encontrado la línea , ¿desde qué parte del log debería comenzar el proceso de redo?. Justifique su respuesta explicando qué significa no haber encontrado un \\." ] }, { "cell_type": "markdown", "metadata": { "id": "aZyueqN6dKA0" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "apHXhTOIlOYn" }, "source": [ "###b)\n", "A continuación hay dos transacciones T1 y T2 en las cuales A, B y C son tres valores distintos en la base de datos." ] }, { "cell_type": "markdown", "metadata": { "id": "BCZ9jJWqlZ6G" }, "source": [ "| T1 | T2 |\n", "| ----------- | --------- |\n", "| R(A) | R(A) |\n", "| W(B, A*2) | W(A, A-5) |\n", "| R(C) | R(C) |\n", "| W(C, C-100) | W(C, C+A) |\n", "\n", "\n", "Donde 'R(A)' representa la lectura del valor 'A', y 'W(A,B)' representa la escritura de 'A' con el valor 'B' (i.e. escribir 'B' en 'A')." ] }, { "cell_type": "markdown", "metadata": { "id": "Bxx34YUFmX6a" }, "source": [ "Para los siguientes tipos de conflictos indique si es posible que ocurra:" ] }, { "cell_type": "markdown", "metadata": { "id": "MAYqc4Uhmbk1" }, "source": [ "####Escritura-lectura (WR)\n" ] }, { "cell_type": "markdown", "metadata": { "id": "mzdXDiigmbk_" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "8fuOoGW-mblA" }, "source": [ "####Lectura-escritura (RW)" ] }, { "cell_type": "markdown", "metadata": { "id": "PQPVVBimmblA" }, "source": [ "*Ingrese aquí su respuesta*" ] }, { "cell_type": "markdown", "metadata": { "id": "juUfWi3TmblA" }, "source": [ "\n", "####Escritura-escritura (WW)" ] }, { "cell_type": "markdown", "metadata": { "id": "ox5TAYW_mblA" }, "source": [ "*Ingrese aquí su respuesta*" ] } ], "metadata": { "colab": { "collapsed_sections": [ "3Sxe278IIUGO" ], "provenance": [] }, "kernelspec": { "display_name": "Python 3", "name": "python3" } }, "nbformat": 4, "nbformat_minor": 0 }