lunes, 4 de noviembre de 2013

Taller 3

Este último entregable recoge la aplicación de cuatro temas básicos que se han desarrollado a lo largo del curso, estos son:

  1. Ciclos de vida de los sistemas de información.
  2. Diseño conceptual de Bases de datos.
  3. Álgebra y Calculo Relacional.
  4. Lenguaje Estructurado de Consulta SQL.

De acuerdo a estos temas,se planteó un requerimiento inicial para desarrollar un sistema de información para gestión de usuarios web para la empresa de desarrollo humano integral. Con la fase de levantamiento de requerimientos lista, procedimos a desarrollar el diagrama entidad relación. El resultado fue el siguiente:


Diagrama ER


El diagrama se encuentra normalizado en 3FN como requisito fundamental de la primer etapa de desarrollo. Luego de validar el modelo, se procede a implementarlo, es decir a correr tanto instrucciones DDL para la creación de la estructura como DML para el ingreso de datos. Conozcamos un poco sobre estos dos términos:

DDL: Su sigla en inglés "Data definition Lenguaje" o Lenguaje de definición de datos. Es el lenguaje utilizado para definir la estructura de una base de datos. También se usa para definir funciones, procedimientos o inclusive vistas para consultar los datos que pueden ser almacenados en dichas estructuras, y adicionalmente provee ciertas funciones para administrar los otorgar o revocar permisos en las diferentes instrucciones DML o CRUD(create, read, uptade and delete) de las estructuras definidas. Este lenguaje se expresa bajo sintaxis SQL, por lo que en ocasiones se considera inclusive un subconjunto del mismo. Un ejemplo de lenguaje DML puede ser la instrucción CREATE que usamos para definir una nueva tabla en nuestro modelo:

CREATE TABLE <table name> ( 
        <attribute name 1> <data type 1>,
        ...
        <attribute name n> <data type n>);


Las instrucciones más destacadas en el DDL son:
    

  • CREATE: utilizada para crear nuevas estructuras en la base de datos. Entre las posibildades están:
    • 

      

    • Base de datos.
      • 

    • Tablas.
      • 

    • Funciones.
      • 

    • Procedimientos.
      • 

    • Vistas.
      • 

    • Disparadores.
      • 

    • Índices (cuando son de tipo clustered o secuencias como lo administra PostgreSql).+
      • 

    • Usuarios de base de datos.
      • 

    

  • ALTER: para realizar alteraciones en la tabla, es usado principalmente para especificar claves primarias o foráneas en la tabla. También puede usarse para realizar otro tipo de modificaciones en las tablas, como agregar o quitar campos, agregar o quitar relaciones,etc.
    • 

  • DROP: usado para borrar estructuras de la base de datos e inclusive para borrar la misma base de datos. No se usa para borrado de datos, si bien el DROP podría borrar los datos de una tabla al borrarla completamente, existen dentro del DML instrucciones para borrado de datos.
    • 

  • GRANT: utilizada para otorgar privilegios sobre la base de datos y sus estructuras a los usuarios de base de datos.
    • 

  • REVOKE: utilizada para remover o quitar permisos ya otorgados a los usuarios en la base de datos.
    • 


A continuación compartimos el enlace con el DDL completo del modelo de base de datos planteado en el diagrama ER inicial:


DDL Desarrolo Humano.







DML: Su sigla en inglés "Data manipulation Lenguaje" o Lenguaje de manipulación de datos. Es el lenguaje utilizado para manipular los datos contenidos en una base de datos ya sea a modo de consulta o de modificación de datos. Como se menciona en la definición de DDL podemos enfocar el DML en el CRUD, que se refiere a las tareas de lectura, inserción, actualización y borrado de datos. Las instrucciones a destacar son:








    

  • INSERT:  utilizado para registrar nuevos datos en una entidad.  Su sintaxis: 
    • 








INSERT INTO <table name>







VALUES (<value 1>, ... <value n>);






    

  • UPDATE: utilizado para actualizar registros existentes en una entidad. Su sintaxis: 
    • 








UPDATE <table name>







SET <attribute> = <expression>







WHERE <condition>;






    

  • DELETE: utilizado para eliminar registros de una entidad. Su sintáxis:
    • 








DELETE FROM <table name>







WHERE <condition>;










Existen dentro del DML unas instrucciones avanzadas que normalmente se utilizan cuando se realizan transacciones muy complejas o que requieren puntos de control en la base de datos bien sea desde funciones o procedimientos e inclusive desde un lenguaje de programación que se este comunicando con la base de datos. Estas instrucciones son BEGIN, COMMIT Y ROLLBACK, las cuales nos permiten realizar operaciones del CRUD de forma temporal o transitoria, y solo hasta que se dé una aprobación o una no aprobación de las mismas, van o no a afectar nuestros datos (Más acerca de transacciones).




A continuación compartimos el enlace con el DML de los datos que se ingresaron ininicialmente al modelo ER planteado:




DML Desarrollo Humano



Dejamos un video de apoyo que en palabras cortas pero muy claras nos muestra ambos conceptos:











Entendiendo lo anterior, aplicamos los conceptos a nuestro modelo inicial como podemos ver en los siguientes ejemplos:





DDL:









Enunciado
Consulta







Definición de la entidad para registrar usuarios de sistema.
CREATE TABLE `USUARIO` (
  `idUsuario` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Consecutivo numérico autogenerado para identificar inequivocamente a cada usuario',
  `cedula` varchar(15) NOT NULL COMMENT 'Cédula del usuario',
  `nombres` varchar(60) NOT NULL COMMENT 'Nombre el usuario',
  `apellidos` varchar(60) NOT NULL COMMENT 'Apellidos del usuario',
  `email` varchar(120) NOT NULL COMMENT 'Email principal del usuario',
  `nombreUsuario` varchar(10) NOT NULL COMMENT 'Nombre de usuario. Este es el log in para los usuario.',
  `clave` varchar(32) NOT NULL COMMENT 'Clave del usuario para el acceso en el sisema. Encriptada en md5 (128 bits con longitud de 32 caracteres)',
  `telefonoFijo` varchar(8) NOT NULL COMMENT 'Teléfono del usuario. Enmascarado con el indicadtivo de la ciudad correspondiente',
  `telefonoCelular` varchar(10) NOT NULL COMMENT 'Teléfono celular del usuario',
  `direccion` varchar(50) DEFAULT NULL COMMENT 'Dirección física del usuario',
  `emailAuxiliar` varchar(120) DEFAULT NULL COMMENT 'Email auxiliar o de contacto',
  `idEstadoCivil` int(11) DEFAULT NULL COMMENT 'Código del estado civil del usuario',
  `nroHijos` int(11) DEFAULT NULL COMMENT 'Indica el número de hijos del usuario',
  `idRol` int(11) NOT NULL COMMENT 'Código del rol relacionado al usuario',
  `idTipoAfiliacion` int(11) DEFAULT NULL COMMENT 'Tipo de afiliación relacionado al usuario.',
  `empresaNombre` varchar(255) DEFAULT NULL COMMENT 'Nombre de la empresa a la que pertenece el usuario',
  `empresaArea` varchar(50) DEFAULT NULL COMMENT 'Área de la empresa en la que labora el usuario ',
  `empresaCargo` varchar(50) DEFAULT NULL COMMENT 'Cargo en el que se desenvuelve el usuario en le empresa en la que labora',
  `colegioNombre` varchar(255) DEFAULT NULL COMMENT 'Nombre del colegio al que peretence el usuario',
  `colegioGrado` int(2) DEFAULT NULL COMMENT 'Grado que cursa en el colegio al que pertenece el usuario',
  `colegioGrupo` varchar(5) DEFAULT NULL COMMENT 'Grupo en el que cursa el grado el usuario.',
  PRIMARY KEY (`idUsuario`),
  UNIQUE KEY `idUsuario` (`idUsuario`),
  UNIQUE KEY `cedula` (`cedula`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `nombreUsuario` (`nombreUsuario`),
  KEY `idTipoAfiliacion` (`idTipoAfiliacion`),
  KEY `idEstadoCivil` (`idEstadoCivil`),
  KEY `fk_USUARIO_ROL_1` (`idRol`),
  CONSTRAINT `fk_USUARIO_ROL_1` FOREIGN KEY (`idRol`) REFERENCES `ROL` (`idRol`),
  CONSTRAINT `USUARIO_ibfk_1` FOREIGN KEY (`idEstadoCivil`) REFERENCES `ESTADO CIVIL` (`idEstadoCivil`),
  CONSTRAINT `USUARIO_ibfk_2` FOREIGN KEY (`idTipoAfiliacion`) REFERENCES `TIPO AFILIACION` (`idTipoAfiliacion`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;






Definición de la tabla de Programas
CREATE TABLE `PROGRAMA` (
  `idPrograma` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Consecutivo numérico autogenerado para identificar unívocamente los registros',
  `nombre` varchar(255) NOT NULL COMMENT 'Nombre del programa',
  `descripcion` text COMMENT 'Descripción del programa',
  `esPago` bit(1) NOT NULL COMMENT 'Booleano que determina si el programa tiene o no costo.',
  `valor` double DEFAULT NULL COMMENT 'Si el programa tiene costo, en este campo se específica el valor del mismo.',
  `fechaInicio` date DEFAULT NULL COMMENT 'Fecha en que inicia el programa.',
  `fechaFinalizacion` date DEFAULT NULL COMMENT 'fecha en que debe finalizar el programa.',
  PRIMARY KEY (`idPrograma`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;





Creación del usuario bd2 en el host local. Se otorgan permisos como super usuario sobre todas las operaciones DDL en la base de datos de desarrollo_humano. 
CREATE USER 'bd2'@'localhsot' IDENTIFIED BY 'bd2013';



GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `desarrollo\_humano`.* TO 'bd2'@'localhsot';







Se revocan permisos al usuario bd2 sobre la operación de crear procedimientos almacenados.
REVOKE CREATE ROUTINE ON `desarrollo\_humano`.* FROM 'bd2'@'localhost';










Creación de una nueva vista que permite ver las versiones de un programa.
CREATE

VIEW `NewView`AS

SELECT PROGRAMA.nombre, PROGRAMA.descripcion, VERSION.nroVersion, VERSION.activa FROM PROGRAMA INNER JOIN VERSION ON VERSION.idPrograma = PROGRAMA.idPrograma ;		
















DML:

















Enunciado
Consulta






Borrar el seguimiento con
id=1 
DELETE FROM SEGUIMIENTO WHERE
 idSeguimiento='1';




Próximos Programas. Muestra el nombre, la descripción y la fecha de inicio de los programas existentes.
SELECT PROGRAMA.nombre, PROGRAMA.descripcion, PROGRAMA.fechaInicio FROM PROGRAMA




Versiones de un Programa. Muestra las versiones que tiene cada programa y si está activa o no esa versión del programa.
SELECT PROGRAMA.nombre, PROGRAMA.descripcion, VERSION.nroVersion, VERSION.activa FROM PROGRAMA INNER JOIN VERSION ON VERSION.idPrograma = PROGRAMA.idPrograma




Inserción de un nuevo usuario (teniendo en cuenta referencias a otras tablas)
INSERT INTO `USUARIO` (`cedula`, `nombres`, `apellidos`, `email`, `nombreUsuario`, `clave`, `telefonoFijo`, `telefonoCelular`, `direccion`, `emailAuxiliar`, `idEstadoCivil`, `nroHijos`, `idRol`, `idTipoAfiliacion`) VALUES ('1128422950', 'Juan', 'Quintero', 'juan@gmail.com', 'jquintero', 'b86d15b7fbd81d03b1ff2c1fb890b92a', '24447417', '3012569987', 'Calle 1A N D SUR 44', 'juan@hotmail.com', '1', '1', '3', '1')




Modificación de la fecha de
finalización del programa con id =3 hasta el 20 de Diciembre de 2013.
INSERT INTO `PROGRAMA` (`nombre`, `descripcion`, `esPago`, `valor`, `fechaInicio`, `fechaFinalizacion`) VALUES ('Programa Personas', 'Programa diseñado para las personas de la comuna, no es pago', '\0', '0', '2013-10-01', '2013-12-31');













Concluimos entonces acerca de toda la teoría y la práctica de los contenidos en este curso:








    

  • Las tres formas normales (3FN) con un fundamento clave en desarrollo de bases de datos. Determinan la calidad de la información, la efectividad de las instrucciones DML, la escalabilidad del sistema de información y otra serie de aspectos que construyen un alto porcentaje del "corazón" del sistema a desarrollar.
    • 

  • Si bien las formas normales son ideales, en ocasiones los requerimientos del sistema nos obligan a violar una u otra, esto pues requerimos que algunos proceso se optimicen. Por ende, las formas normales siendo un patrón ideal, no siempre van a representar la solución mas óptima para los requerimientos.
    • 

  • Tanto el DDL como el DML son herramientas claves e indispensables para el desarrollo de bases de datos relacionales. Si bien los DBMS actuales automatizan en gran medida la composición de estas consultas, es importante tener una base de conocimiento sólida sobre estos dos conceptos, pues es ideal tener claridad sobre todo lo que hay detrás de esta automatización.
    • 














REFERENCIA BIBLIOGRÁFICA:















Publicado por



en





No hay comentarios:
  


































        

      









Suscribirse a:
Entradas (Atom)