bases de datos segunda entrega juanse

FUNDAMENTOS DE BASE DE DATOS

I VOTE

ELABORADO POR:

JOHN ALEXANDER CONTRERAS

LIZ GIL SÁNCHEZ

LAURA JOHANA SCARPETTA FARÍAS

JUAN SEBASTIAN TIRADO PERTUZ

PRESENTADO A:

HUGO ORTIZ BARRERO

UNIVERSIDAD POLITECNICO GRANCOLOMBIANO

FACULTAD DE POSTGRADOS VIRTUALES

ESPECIALIZACION GERENCIA DE PROYECTOS E INTELIGENCIA DE NEGOCIOS

BOGOTÁ DC.

2015

TABLA DE CONTENIDO

1. INTRODUCCIÓN2. PLANTEAMIENTO DEL PROBLEMA Y JUSTIFICACIÓN

3. OBJETIVOS3.1 Objetivo General3.2 Objetivo específico

4. MARCO TEORICO4.1 Definición. Base de datos

4.2 Sistema de gestión de base de datos (SGDB)

5. ESTRUCTURA DE DESCOMPOSICIÓN DEL TRABAJO (WBS)

6. CRONOGRAMA DE ACTIVIDADES

7. SISTEMA PROPUESTO

7.1 Panorama (Universo del Discurso)

7.2 Requerimiento funcional 7.3 Requerimientos no funcionales

8. MODELOS DEL SISTEMA8.1 Diseño conceptual de la base de datos8.2 Normalización de las bases de datos8.3 Diseño lógico de las bases de datos

9. CUESTIONES DE CONTROL DE INTEGRIDAD REFERENCIAL

10.BIBLIOGRAFIA

1. INTRODUCCION

Este proyecto se elabora bajo el marco del módulo Fundamentos de Bases de datos de la especialización en Gerencia de Proyectos de Inteligencia de NegociosEste proyecto mostrará cómo se desarrolla un sistema de información con herramientas de software que permitirá administrar, analizar, utilizar y explotar la información contenida en una base de datos. Para la construcción de la Base de datos se usara el modelo diseñado a partir de los requisitos exigidos por la compañía. El sistema desarrollado permitirá tener el control completo de diferentes tipos de elecciones, controlando los usuarios, los candidatos y la programación, y el resultado de las votaciones.

2. PLANTEAMIENTO DEL PROBLEMA Y JUSTIFICACION

Partiendo del hecho que los modelos democráticos tanto de los países como de las organizaciones hoy por hoy buscan la participación de todos es preciso crear métodos que permitan esta participación, y que mejor forma que hacerlo que a través de la herramienta de mayor difusión como lo es la tecnología; ya que está al alcance de todos.

Por esta razón se hace necesario el desarrollo de programas que genere confianza y les brinde a todos los implicados sentir que su opinión es tomada en cuenta

Estos sistemas ayudarán a acelerar el conteo de los votos, proveer de una mejor accesibilidad a los votantes, además ofrece un medio de respaldo para los procesos que se llevan a cabo, porque puede presentar informes detallados del estado de la votación en momentos cruciales.

Otro aspecto a tomar en consideración es el miedo a que exista fraude, usos de medios ilícitos, compra de votantes, alteraciones en el sistema actual para el conteo de votos, etc. Por ello se hace más latente la necesidad de implementar la tecnología con el fin de dar trasparencia al proceso.

3. OBJETIVOS

3.1 Objetivo general

Crear un sistema de bases de datos que permita administrar la información de un sistema de votación electrónica a través de Internet (i--‐vote).

3.2 Objetivos Específicos

• Plantear una base de datos implementada sobre el sistema de gestión de bases de datos MY SQL

• Emplear un software para diseñar una base de datos basado en un modelo entidad - relación.

• Diseñar consultas en SQL que respondan a requerimientos específicos.

• Plantear una solución para la gestión de la información y apoyo a la toma de decisiones de las organizaciones.

4. MARCO TEORICO

En nuestros días lo que más se mueve es la información ya sea en medios de comunicación o simplemente como parte de nuestro diario vivir en las empresas y organización del hogar, es por ello que diariamente se genera una cantidad ilimitada e indeterminada de información que según su uso e importancia es indispensable guardar, organizar y procesar; a partir de esta necesidad surge un espacio de almacenamiento llamado Bases de Datos.

El término de bases de datos fue escuchado por primera vez en 1963, en un simposio celebrado en California, USA. Una base de datos se puede definir como un conjunto de información relacionada que se encuentra agrupada o estructurada. Desde el punto de vista informático, la base de datos es un sistema formado por un conjunto de datos almacenados en discos que permiten el acceso directo a ellos y un conjunto de programas que manipulen ese conjunto de datos.

Los orígenes de las bases de datos se remontan a la Antigüedad donde ya existían bibliotecas y toda clase de registros. Además también se utilizaban para recoger información sobre las cosechas y censos. Sin embargo, su búsqueda era lenta y poco eficaz y no se contaba con la ayuda de máquinas que pudiesen reemplazar el trabajo manual.

Posteriormente, el uso de las bases de datos se desarrolló a partir de las necesidades de almacenar grandes cantidades de información o datos. Sobre todo, desde la aparición de las primeras computadoras, el concepto de bases de datos ha estado siempre ligado a la informática.

En 1884 Herman Hollerith creó la máquina automática de tarjetas perforadas, siendo nombrado así el primer ingeniero estadístico de la historia. En esta época, los censos se realizaban de forma manual.Ante esta situación, Hollerith comenzó a trabajar en el diseño de una maquina tabuladora o con sensor, basada en tarjetas perforadas.

Posteriormente, en la década de los cincuenta se da origen a las cintas magnéticas, para automatizar la información y hacer respaldos. Esto sirvió para suplir las necesidades de información de las nuevas industrias. Y a través de este mecanismo se empezaron a automatizar información, con la desventaja de que solo se podía hacer de forma secuencial.

Posteriormente en la época de los sesenta, las computadoras bajaron los precios para que las compañías privadas las pudiesen adquirir; dando paso a que se popularizara el uso de los discos, cosa que fue un adelanto muy efectivo en la época, debido a que a partir de este soporte se podía consultar la información directamente, sin tener que saber la ubicación exacta de los datos.

En esta misma época se dio inicio a las primeras generaciones de bases de datos de red y las bases de datos jerárquicas, ya que era posible guardar estructuras de datos en listas y árboles.Por lo que respecta a la década de los setenta, Edgar Frank Codd, científico informático ingles conocido por sus aportaciones a la teoría de bases de datos relacionales, definió el modelo relacional a la par que publicó una serie de reglas para los sistemas de datos relacionales a través de su artículo “Un modelo relacional de datos para grandes bancos de datos compartidos”.

Este hecho dio paso al nacimiento de la segunda generación de los Sistemas Gestores de Bases de Datos.

Como consecuencia de esto, durante la década de 1970, Lawrence J. Ellison, más conocido como Larry Ellison, a partir del trabajo de Edgar F. Codd sobre los sistemas de bases de datos relacionales, desarrolló el Relational Software System, o lo que es lo mismo, lo que actualmente se conoce como Oracle Corporation, desarrollando así un sistema de gestión de bases de datos relacional con el mismo nombre que dicha compañía.1

Posteriormente en la época de los ochenta también se desarrollará el SQL (Structured Query Language) o lo que es lo mismo un lenguaje de consultas o lenguaje declarativo de acceso a bases de datos relacionales que permite efectuar consultas con el fin de recuperar información de interés de una base de datos y hacer cambios sobre la base de datos de forma sencilla

En la década de 1990 la investigación en bases de datos giró en torno a las bases de datos orientadas a objetos. Las cuales han tenido bastante éxito a la hora de gestionar datos complejos en los campos donde las bases de datos relacionales no han podido desarrollarse de forma eficiente.

En la actualidad, las tres grandes compañías que dominan el mercado de las bases de datos son IBM, Microsoft y Oracle. Por su parte, en el campo de internet, la compañía que genera gran cantidad de información es Google. Aunque existe una gran variedad de software que permiten crear y manejar bases de datos con gran facilidad.

Definición de Base de datos: Se define una base de datos como una serie de datos organizados y relacionados entre sí, los cuales son recolectados y explotados por los sistemas de información de una empresa o negocio en particular.

Características

Entre las principales características de los sistemas de base de datos podemos mencionar:

Independencia lógica y física de los datos. Redundancia mínima.

1 http://histinf.blogs.upv.es/2011/01/04/historia-de-las-bases-de-datos/

Acceso concurrente por parte de múltiples usuarios. Integridad de los datos. Consultas complejas optimizadas. Seguridad de acceso y auditoría. Respaldo y recuperación. Acceso a través de lenguajes de programación estándar.

Sistema de gestión de bases de datosEs el software que permite la utilización y/o la actualización de los datos almacenados en una (o varias) base(s) de datos por uno o varios usuarios desde diferentes puntos de vista y a la vez.El objetivo fundamental de un SGBD consiste en suministrar al usuario las herramientas que le permitan manipular, en términos abstractos, los datos, o sea, de forma que no le sea necesario conocer el modo de almacenamiento de los datos en la computadora, ni el método de acceso empleado.2

Características de los SGBD

Algunos ejemplos de SGBD son Oracle, DB2, PostgreSQL, MySQL, MS SQL Server, etc.Un SGBD debe permitir:

Definir una base de datos: especificar tipos, estructuras y restricciones de datos. Construir la base de datos: guardar los datos en algún medio controlado por el mismo

SGBD Manipular la base de datos: realizar consultas, actualizarla, generar informes. Crear y gestionar base de datos de forma fácil, cómoda y rápida. Ofrece un ambiente agradable dado por su interfaz gráfica.

Las características de un Sistema Gestor de Base de Datos SGBD3 son:

• Abstracción de la información: Los SGBD ahorran a los usuarios detalles acerca del almacenamiento físico de los datos. Da lo mismo si una base de datos ocupa uno o cientos de archivos, este hecho se hace transparente al usuario: Así, se definen varios niveles de abstracción.

• Independencia. La independencia de los datos consiste en la capacidad de modificar el esquema (físico-lógico) de una base de datos sin tener que realizar cambios en las aplicaciones que se sirven de ella.

• Redundancia mínima: Un buen diseño de una base de datos logrará evitar la aparición de información repetida o redundante: De entrada, lo ideal es lograr una redundancia nula; no

2 http://www.maestrosdelweb.com/que-son-las-bases-de-datos/3 http://indira-informatica.blogspot.com/2007/09/qu-es-un-sistema-de-gestin-de-base-de.html

obstante, en algunos casos la complejidad de los cálculos hace necesaria la aparición de redundancias.

• Consistencia: En aquellos casos en los que no se ha logrado esta redundancia nula, será necesario vigilar que aquella información que aparece repetida se actualice de forma coherente, es decir, que todos los datos repetidos se actualicen de forma simultánea.

• Seguridad: La información almacenada en una base de datos puede llegar a tener un gran valor. Los SGBD deben garantizar que esta información se encuentra segurizada frente a usuarios malintencionados, que intenten leer información privilegiada; frente a ataques que deseen manipular o destruir la información; o simplemente ante las torpezas de algún usuario autorizado pero despistado. Normalmente, los SGBD disponen de un complejo sistema de permisos a usuarios y grupos de usuarios, que permiten otorgar diversas categorías de permisos.

• Integridad: Se trata de adoptar las medidas necesarias para garantizar la validez de los datos almacenados. Es decir, se trata de proteger los datos ante fallos de hardware, datos introducidos por usuarios descuidados, o cualquier otra circunstancia capaz de corromper la información almacenada.

• Respaldo y recuperación: Los SGBD deben proporcionar una forma eficiente de realizar copias de respaldo de la información almacenada en ellos, y de restaurar a partir de estas copias los datos que se hayan podido perder.

• Control de la concurrencia: En la mayoría de entornos (excepto quizás el doméstico), lo más habitual es que sean muchas las personas que acceden a una base de datos, bien para recuperar información, bien para almacenarla. Y es también frecuente que dichos accesos se realicen de forma simultánea. Así pues, un SGBD debe controlar este acceso concurrente a la información, que podría derivar en inconsistencias.

5. ESTRUCTURA D DESCOMPOSICION DEL TRABAJO (WBS)

6. CRONOGRAMA DE ACTIVIDADES

Planteamiento del Problema

Análisis del sistema que se va a utilizar

Diseño del sistema elegido

Control de integridad referencial

Programación del sistema

Implementación del Sistema

Identificación de errores

Ajustes y Finalización

7. SISTEMA PROPUESTO

7.1 PANORAMA (UNIVERSO DEL DISCURSO)

Debido al crecimiento de las tecnologías de información y al gran uso de la tecnología informática que hoy día facilita la administración de todas las empresas, se vio la necesidad de actualizarse e implementar sistemas de colaboración con los procesos de gestión empresarial, con el fin de mejorar la productividad y rendimiento en los procesos generales y repetitivos. Para brindar ventajas y ampliar posibilidades, para ser realmente competitivos y eficaces es necesario la implementación de sistemas que ofrezcan múltiples posibilidades, que permitan acceder a todos los datos, datos relevantes de manera oportuna y cada vez que se necesiten.

Dependerá de la manera de manejar e implementar sistemas, de los procesos y almacenamiento de datos, la eficiencia y evolución de las empresas, obligándolas a crear procesos para cada actividad, que permitan el almacenamiento y creación de recursos disponibles para formular preguntas, responder inquietudes y organizar de manera eficiente la gestión pasada, actual y futura.

Para lograr un éxito general, siempre se deberá revisar la información, los datos relativos de todo proceso de una empresa, que es lo que permite la toma asertiva de decisiones y cambios, logrando una evolución a partir de los sistemas de información; es así como nos percatamos que la información ya no se trata de datos al azar, todo lo contrario, ya hace parte importante de las empresas, se volvió un recurso corporativo.

7.2 REQUERIMIENTOS FUNCIONALES

Registro de usuarios

El sistema deberá permitir el ingreso o registro de cada usuario, se deberá ingresar información completa que contenga: Nombre completo, número de identificación, género, fecha de nacimiento y contraseña para el acceso al sistema. La contraseña deberá ser entregada al usuario vía correo certificado.

Candidatos

El sistema deberá permitir el registro y generación de listas de los candidatos los cuales deben ser registrados con los datos completos de: Nombre completo, número de identificación, género, imagen, fecha de nacimiento y número de lista del candidato.

Elecciones

El sistema deberá permitir el registro y generación de listas con las elecciones que se realizaran, estas deben contener los datos: Nombre de la elección (tipo de elección, asamblea, presidente, etc.), fecha de inicio de la elección, fecha de fin, hora de inicio y hora de finalización de la elección.

Listas electorales

El sistema deberá permitir al usuario la visualización de las listas electorales en curso a la fecha, deberá permitir que el usuario emita su voto, lo cancele de ser necesario y la confirmación final del voto para luego proceder a el almacenamiento del mismo.

Votación

El sistema debe restringir al usuario de realizar más de un voto para la misma elección, no se puede permitir que exista más de un voto del mismo usuario puesto que sería un fraude.

El sistema verificara la elección en la cual se almaceno la votación del usuario, la última votación realizada, realizar este proceso sobre las elecciones realizadas y así restringir la doble votación.

El usuario solo podrá votar por uno y solo un candidato u opción de las listas de cada elección, la única opción adicional es el voto en blanco cuando el usuario no está de acuerdo con ninguno de los candidatos postulados en la elección.

Un candidato solo podrá postularse en una elección por ello no deberá existir duplicidad de candidatos en ninguna de las listas.

El sistema deberá contabilizar los votos almacenados, permitiendo ver cada uno de ellos y guardando un registro de los votos cancelados.

Final de las elecciones

Al finalizar las elecciones el sistema deberá realizar el conteo total de los votos, deberá permitir conocer el ganador de cada elección y almacenar estos datos para posteriormente tener los datos históricos de cada una de las elecciones.

Requerimientos del sistema

Para lograr implementar el sistema de votación electrónica y que este funcione correctamente se deberá contar con:

Software: MySQL, sistema de gestión de bases de datos relacional, multihilo y multiusuario

Hardware:

Descripción Mínimo RecomendadoProcesador 64 bits a 1,4 GHz 64 bits a 2,8 GHzRAM 512 MB 6-10 GBDisco 80 GB 800 GB

7.3 REQUERIMIENTOS NO FUNCIONALES

USABILIDAD

Código Descripción

RNF01El sistema deberá mostrar mensajes de error describiendo eventos que sucedan eventualmente y que sean por excepciones presentadas.

RNF02En el momento de ingreso del usuario el sistema deberá mostrar las elecciones activas de la fecha.

RNF03El sistema deberá contar con tips de ayuda a los usuarios para evitar que estos comentan errores.

RNF04El sistema deberá pedir confirmación de parte del usuario para guardar registros o cambios en el sistema.

CONFIABILIDAD


RNF05El sistema deberá almacenar los errores de las excepciones ocurridas en el sistema

SOPORTE


RNF06 El sistema deberá ser compatible con MySQL

RNF07La estación Cliente deberá disponer de un visualizador de archivos PDF.

RNF08

El servidor que alojará la base de datos deberá cumplir con los siguientes requerimientos mínimos: 1 procesador Intel Xeon de 3.46 GHZ dual core o superior, 8 GB memoria RAM, conexión de red de 100 Mbps, 3 HD 80 GB en RAID 5 para los archivos de la base de datos y 2 HD 80 GB en RAID 1 para el sistema operativo y el motor de BD

RENDIMIENTO


RNF9El tiempo de respuesta promedio del sistema para las operaciones involucradas con los reportes es de 30 segundos.

RNF10El tiempo promedio de las transacciones en el sistema no debe exceder los 15 segundos.

RESTRICCIONES DE DISEÑO


RNF11El sistema debe operar en cualquier computador personal con procesador doble núcleo o superior, 512 MB de memoria RAM como mínimo y disco duro de 40 GB.

RNF12 El motor de base de datos deberá SER mySQL

INTERFACE


RNF13 No se incorporarán animaciones en el sistema.

RNF14 Los reportes mostrarán el logo y nombre de la empresa.

RNF15 La pantalla principal mostrara el logo de la empresa

RNF16La aplicación debe funcionar en pantallas con una resolución de 1024 x 768 pixeles o superior.

RNF17 La aplicación deberá utilizar como fuente de letra calibri 11

8. MODELOS DEL SISTEMA

8.1. DISENO CONCEPTUAL DE LAS BASES DE DATOS (MER Modelo Entidad Relación)

8.2 NORMALIZACION DE LA BASE DE DATOS

Primera forma normal 1FN

CandidatoId_CandidatoNombreApellidosImagenGéneroNacimientoNum_ListaCod_Eleccion

UsuarioId_UsuarioNombreApellidosGéneroFecha de nacimientoContraseña

ElecciónCod_EleccionFecha_InicioFecha_FinHora_InicioHora_FinEstamentoActivo

VotaciónFechaHoraId_UsuarioId_CandidatoCod_Eleccion

Segunda forma normal 2FNDebido a que todas las claves primarias contienen un solo atributo, entonces las tablas cumplen con la 2FN

Tercera forma normal 3FNNo se presentan dependencias transitivas en las tablas por lo tanto cumplen con 3FN.

10.3. DISENO LOGICO DE LAS BASES DE DATOS

9. CUESTIONES DE CONTROL DE INTEGRIDAD REFERENCIAL

Cuando se da una relación entre tablas, la tabla principal es aquella que contiene un registro único que se relaciona con múltiples registros que se encuentran en la tabla secundaria. Por ejemplo: en el diseño descrito en la sección anterior se tiene una relación de uno a varios entre a tabla Elección y la tabla Candidato. Cada registro que se encuentra en la tabla Elección es único y el código de la elección jamás se repetirá por ser esta llave primaria. Ahora en la tabla Candidato cada registro también es único y el código del candidato jamás se repetirá por ser esta llave primaria, pero además esta tabla tiene un campo donde se relaciona el Cod_Elección (llave foránea) al cual el candidato se postuló. En esta última tabla puede tener varios candidatos con el mismo código de elección.

Ahora bien, como en el diseño de esta base de datos de este proyecto se tienen tablas relacionadas y para mantener la integridad referencial entre los registros de estas tablas, se definirán las siguientes reglas en los campos llaves foráneas que están en las tablas secundarias o hijas:

Actualizar en cascada => Esto indica que cuando se actualiza un registro de la tabla principal se actualizarán los registros relacionados que se encuentren en la tabla secundaria.

Eliminar restringida => Esto indica que a la hora de eliminar un registro de la tabla principal si este está relacionado en la tabla secundaria entonces se registren la eliminación.

10. BIBLIOGRAFIA

http://www.maestrosdelweb.com/que-son-las-bases-de-datos/

http://histinf.blogs.upv.es/2011/01/04/historia-de-las-bases-de-datos/

http://indira-informatica.blogspot.com/2007/09/qu-es-un-sistema-de-gestin-de-base-de.html

http://www.cavsi.com/preguntasrespuestas/que-es-un-sistema-gestor-de-bases-de-datos-o-sgbd/

1. Pruebas con álgebra relacional y cálculo relacional Algebra relacional

SELECCION

select identificacion from usuario where identificacion='1212'

σ identificacion=1212 (usuario);

selection:=select identificacion=1212 (usuario);

PROYECCION

select nombres, apellidos, genero from usuario

Π<nombres, apellidos, genero> (usuario);

projection:=Project nombres, apellidos, genero (usuario);

UNION

select identificacion,nombres , apellidos from usuariounion select identificacion,nombres , apellidos from candidato

Union=:( Π<identificacion, nombres, apellidos> (usuario)) U (Π<identificacion, nombres, apellidos> (candidato))

Union=:(Project <identificacion, nombres, apellidos> (usuario)) U (Project <identificacion, nombres, apellidos> (candidato))

DIFERENCIA

select nombres from usuario,votacion where identificacion='454545' and votacion.id_usuario=usuario.identificacion

Difference=: (Π<nombres>( σ identificacion=454545(usuario))) - (Π<nombres>(votos)

Difference=: project nombres ( select identificacion=454545 (usuario))) - (Π<nombres>(votos)

INTERSECCION

select usuario.identificacion from usuario,candidato where usuario.identificacion = candidato.identificacion

intersection=:( Π<identificación> (usuario)) ∩ (Π<identificacion> (candidato))

intersection =:(Project <identificacion> (usuario)) ∩ (Project <identificacion> (candidato))

PRUEBAS y refinaciamiento

2. DICCIONARIO DE DATOS

DISENO FISICO DE LA BASE DE DATOS

3. Documentación de la implementación y ajustes al sistema de bases de datos.

4. CONSULTAS SQL Y CODIGO SQL QUE RESPONDE A LOS REQUERIMIENTOS DEL SISTEMA, IMPLEMENTACIÓN DE CURSORES, IMPLEMENTACIÓN DE PROCEDIMIENTOS ALMACENADOS (STORED PROCEDURES) E IMPLEMENTACIÓN DE DISPARADORES (TRIGGERS)

CREACIÓN DE TABLAS

Usuarios: CREATE TABLE ùsuario` (ìdentificacion` VARCHAR(15) NOT NULL,`nombres` VARCHAR(60) NOT NULL,àpellidos` VARCHAR(60) NOT NULL,`genero` VARCHAR(15) NOT NULL,`nacimiento` DATE NOT NULL,`contrasena` VARCHAR(60) NOT NULL,PRIMARY KEY (ìdentificacion`))COLLATE='utf8_general_ci'ENGINE=InnoDB;

Candidatos:CREATE TABLE `candidato` (ìdentificacion` VARCHAR(15) NOT NULL,`nombres` VARCHAR(60) NOT NULL,àpellidos` VARCHAR(60) NOT NULL,`genero` VARCHAR(15) NOT NULL,`nacimiento` DATE NOT NULL,ìmagen` VARCHAR(60) NOT NULL,`num_lista` INT(11) NOT NULL,`Cod_Eleccion` INT(11) NOT NULL,PRIMARY KEY (ìdentificacion`),INDEX `FK_candidato_eleccion` (`Cod_Eleccion`),CONSTRAINT `FK_candidato_eleccion` FOREIGN KEY (`Cod_Eleccion`) REFERENCES èleccion` (`Cod_Eleccion`))COLLATE='utf8_general_ci'ENGINE=InnoDB;

Elección: CREATE TABLE èleccion` (`Cod_Eleccion` INT(11) NOT NULL AUTO_INCREMENT,`Fecha_Inicio` DATE NOT NULL,`Fecha_Fin` DATE NOT NULL,`Hora_Inicio` TIME NOT NULL,`Hora_Fin` TIME NOT NULL,Èstamento` VARCHAR(60) NOT NULL,àctivo` INT(11) NOT NULL DEFAULT '0',PRIMARY KEY (`Cod_Eleccion`))COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=3;

Votación: CREATE TABLE `votacion` (

`fecha` DATE NOT NULL,`hora` TIME NOT NULL,ìd_usuario` VARCHAR(15) NOT NULL,ìd_candidato` VARCHAR(15) NOT NULL,`cod_eleccion` INT(11) NOT NULL,INDEX `FK_votacion_usuario` (ìd_usuario`),INDEX `FK_votacion_candidato` (ìd_candidato`),INDEX `FK_votacion_eleccion` (`cod_eleccion`),CONSTRAINT `FK_votacion_usuario` FOREIGN KEY (ìd_usuario`) REFERENCES ùsuario` (ìdentificacion`),CONSTRAINT `FK_votacion_candidato` FOREIGN KEY (ìd_candidato`) REFERENCES `candidato` (ìdentificacion`),CONSTRAINT `FK_votacion_eleccion` FOREIGN KEY (`cod_eleccion`) REFERENCES èleccion` (`Cod_Eleccion`))COLLATE='utf8_general_ci'ENGINE=InnoDB;

INSERTAR USUARIOS, CANDIDATOS Y ELECCIONES

Insertar candidato:

INSERT INTO ìvote`.`candidato` (ìdentificacion` ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,ìmagen` ,`num_lista` ,`Cod_Eleccion`)VALUES ('104491546', 'pedro luis', 'serna girado', 'Masculino', '1990-05-10', '104491546.jpg', '1', '2');

INSERT INTO ìvote`.`candidato` (ìdentificacion` ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,ìmagen` ,`num_lista` ,`Cod_Eleccion`)VALUES ('45565452', 'maria ', 'cabarcas suarez', 'Femenina', '1980-05-10', '45565452.jpg', '1', '2');INSERT INTO ìvote`.`candidato` (ìdentificacion` ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,ìmagen` ,`num_lista` ,`Cod_Eleccion`)VALUES ('10455566', 'esteban ', 'salgado silva', 'Masculina', '1990-05-10', '10455566.jpg', '1', '2');

INSERT INTO ìvote`.`candidato` (ìdentificacion` ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,ìmagen` ,`num_lista` ,`Cod_Eleccion`)VALUES ('45584278', 'marcela ', 'ramirez puello', 'Femenina', '1984-05-10', '45584278.jpg', '1', '2');

Insertar las votaciones

INSERT INTO ìvote`.èleccion` ( `Fecha_Inicio` ,`Fecha_Fin` ,`Hora_Inicio` ,`Hora_Fin` ,Èstamento` ,àctivo`)VALUES ('2015-05-29', '2015-05-29', '8:00', '4:00', 'senado', '0');

INSERT INTO ìvote`.èleccion` ( `Fecha_Inicio` ,`Fecha_Fin` ,`Hora_Inicio` ,`Hora_Fin` ,Èstamento` ,àctivo`)VALUES ('2015-05-29', '2015-05-29', '8:00', '4:00', 'vicepresidente', '0');

Insertamos los usuarios

INSERT INTO ìvote`.usuario (identificacion ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,`contrasena` )VALUES ('454545', 'julio ', 'ramirez perez', 'Masculino', '1991-05-10', md5('454545'));INSERT INTO ìvote`.usuario (identificacion ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,`contrasena` )VALUES ('55555', 'moises ', 'bolano rojas', 'Masculino', '1991-05-10', md5('55555'));

INSERT INTO ìvote`.usuario (identificacion ,`nombres` ,àpellidos` ,`genero` ,`nacimiento` ,`contrasena` )VALUES ('2222', 'juan ', 'puello aguirre', 'Masculino', '1991-05-10', md5('2222'));

NOTA: MD5() permite encriptar las contraseñas

ACTIVAR EL PROCESO DE ELECCIÓN QUE SE VA A REALIZAR

update eleccion set eleccion.activo='1' where eleccion.Cod_Eleccion='2'

LOGEO DE USUARIO

select * from usuario where usuario.identificacion='454545' and usuario.contrasena=md5('454545')

GENERACIÓN DE LISTADO DE CANDIDATOS QUE VE EL USUARIO PARA VOTAR

select c.identificacion,c.nombres,c.imagen,c.Cod_Eleccion,c.num_lista,e.Estamento from candidato c inner join eleccion e on e.Cod_Eleccion=c.Cod_Eleccion and e.activo='1'

REGISTRO DE VOTOS POR PARTE DEL USUARIO

INSERT INTO ìvote`.`votacion` (`fecha` ,`hora` ,ìd_usuario` ,ìd_candidato` ,`cod_eleccion` )VALUES ('2015-05-29', '17:37:28', '454545', '45584278','2');

ORDENAR LOS CANDIDATOS SEGÚN PORCENTAJE DE VOTACIÓN

select count(v.id_candidato) as votos,v.id_candidato ,concat(trim(nombres),' ',trim(apellidos)) as candidato from votacion v inner join candidato c onc.identificacion=v.id_candidato group by id_candidato order by votos desc

CREAR UN PROCEDIMIENTO ALMACENADO PARA AGREGAR USUARIOS

create procedure insertar_nuevo_usuario(nuevo_identificacion VARCHAR(15),nuevo_nombres VARCHAR(60) ,nuevo_apellidos VARCHAR(60) ,nuevo_genero VARCHAR(15) ,nuevo_nacimiento DATE ,nuevo_contrasena VARCHAR(60))

iNSERT INTO usuario(identificacion,nombres,apellidos,genero,nacimiento,contrasena)VALUES(nuevo_identificacion,nuevo_nombres,nuevo_apellidos,nuevo_genero,nuevo_nacimiento,nuevo_contrasena);

LLAMAR EL PROCEDIMIENTO ALMACENADO PARA REGISTRAR UN USUARIO

call insertar_nuevo_usuario('1212','maicol jose','peña','masculino','1995-12-10',md5('1212'))

CREAR UN PROCEDIMIENTO ALMACENADO Y HACER USO DE LOS CURSORES PARA CONSULTAR UNA LISTA DE LOS CANDIDATOS CON SUS RESPECTIVOS VOTOS

DELIMITER $$

CREATE PROCEDURE build_votos_list (INOUT votos_list varchar(4000))BEGIN

DECLARE v_finished INTEGER DEFAULT 0;DECLARE v_candidato varchar(100) DEFAULT "";DECLARE v_votos int;declare v_id_candidato varchar(60) default "";

-- declare cursor for employee emailDEClARE votos_cursor CURSOR FORselect count(v.id_candidato) as votos,v.id_candidato ,concat(trim(nombres),' ',trim(apellidos)) as candidato from votacion v inner join candidato c onc.identificacion=v.id_candidato group by id_candidato order by votos asc;

-- declare NOT FOUND handlerDECLARE CONTINUE HANDLERFOR NOT FOUND SET v_finished = 1;

OPEN votos_cursor;

get_votos: LOOP

FETCH votos_cursor INTO v_votos,v_id_candidato,v_candidato;

IF v_finished = 1 THENLEAVE get_votos;END IF;

-- build email listSET votos_list = CONCAT("(",v_id_candidato,")",v_candidato,": ",v_votos," || ",votos_list);

END LOOP get_votos;

CLOSE votos_cursor;

END$$

DELIMITER ;

EJECUTAR EL PROCEDIMIENTO

SET @votos_list = "";CALL build_votos_list(@votos_list);SELECT @votos_list as lista;

PARA CREAR UN TRIGGER SE CREA UNA TABLA LLAMADA AUDITORIA QUE ES DONDE SE GUARDARAN POSTERIORMENTE UN REGISTRO CADA VEZ QUE SE EJECUTE UN TRIGGER.

CREATE TABLE àuditoria` (ìd` INT(11) NOT NULL AUTO_INCREMENT,ìd_candidato` VARCHAR(15) NULL DEFAULT '0',`tipo_accion` ENUM('NUEVO REGISTRO','EDITAR REGISTRO','ELIMINAR') NULL DEFAULT NULL,`hora` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX `Índice 1` (ìd`),INDEX `FK_auditoria_candidato` (ìd_candidato`),CONSTRAINT `FK_auditoria_candidato` FOREIGN KEY (ìd_candidato`) REFERENCES `candidato` (ìdentificacion`))COMMENT='tabla de auditoria'COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=4;

CREACIÓN DE TRIGGER Q SE VA A DISPARAR CADA VEZ QUE SE HAGA

DELIMITER $$

CREATETRIGGER `candidadto_after_update` BEFORE UPDATEON `candidato`FOR EACH ROW BEGIN

SET @estado = 'EDITAR REGISTRO';

INSERT INTO auditoria (id_candidato, tipo_accion) VALUES (NEW.identificacion, @estado);

END$$

DELIMITER ;

DESPUÉS DE CREAR EL TRIGGER PROBAMOS QUE CADA VEZ QUE SE REALIZA UN ACTUALIZAR DE CUALQUIER CAMPO DE LA TABLA CANDIDATO SE GUARDA AUTOMÁTICAMENTE UN REGISTRO EN LA TABLE AUDITORIA.

CREAR TRIGERR PARA LA TABLA CANDIDATO CADA VEZ QUE SE CREE UN NUEVO REGISTRO

DELIMITER $$CREATETRIGGER `candidato_after_insert` AFTER INSERTON `candidato`FOR EACH ROW BEGINSET @estado = 'NUEVO REGISTRO';INSERT INTO auditoria (id_candidato, tipo_accion) VALUES (NEW.identificacion, @estado); END$$

5. Cuestiones de seguridad de la base de datos.

6. Documentación del proceso de refinamiento y pruebas de performance de la base de datos.

bases de datos segunda entrega juanse

Documents