Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
UNIVERSIDAD NACIONAL AUTÓNOMA DE MÉXICO FACULTAD DE ESTUDIOS SUPERIORES ARAGÓN T R A B A J O E S C R I T O EN LA MODALIDAD DE SEMINARIOS Y CURSOS DE ACTUALIZACIÓN Y C A P A C I T A C I Ó N P R O F E S I O N A L QUE PARA OBTENER EL TÍTULO DE: INGENIERO EN COMPUTACIÓN P R E S E N T A : M I N E R V A P A R E D E S D U R A N ASESOR: M. EN C. MARCELO PÉREZ MEDEL “ACTUALIZACIÓN EN TÓPICOS DE ADMINISTRACIÓN DE BASES DE DATOS” MÉXICO, 2009. UNAM – Dirección General de Bibliotecas Tesis Digitales Restricciones de uso DERECHOS RESERVADOS © PROHIBIDA SU REPRODUCCIÓN TOTAL O PARCIAL Todo el material contenido en esta tesis esta protegido por la Ley Federal del Derecho de Autor (LFDA) de los Estados Unidos Mexicanos (México). El uso de imágenes, fragmentos de videos, y demás material que sea objeto de protección de los derechos de autor, será exclusivamente para fines educativos e informativos y deberá citar la fuente donde la obtuvo mencionando el autor o autores. Cualquier uso distinto como el lucro, reproducción, edición o modificación, será perseguido y sancionado por el respectivo titular de los Derechos de Autor. Dedicatoria A Dios por cuidar mis pasos y permitirme llegar hasta este momento. A mi amado esposo por su amor y apoyo incondicional. A mi pequeño Gabriel por ser la inspiración más grande de mi vida. A mis padres por darme la vida y entregarme su mayor esfuerzo. A mi hermano Daniel, porque siempre estás en mi corazón. A mi hermano Julio, porque sé que juntos llegaremos muy lejos... Cuentas conmigo. A mi tía Socorrito y prima July por su cariño y buen ejemplo. Agradecimientos En primer lugar a mi asesor el M. en C. Marcelo Pérez Medel, por todo su tiempo, apoyo y compresión, ya que sin él no hubiera sido posible cumplir esta meta. Al Mat. Luis Ramírez Flores, por todos aquellos consejos que contribuyeron en mi crecimiento personal y profesional. A los Ing. Silvia Vega Muytoy, Juan Gastaldi Pérez y Gabriel Ortiz Cordero, por su valioso tiempo, observaciones y sugerencias para el mejoramiento de este trabajo. A la UNAM y todos mis profesores que contribuyeron en mi desarrollo profesional. Índice Antecedentes............................................................................................................ 1 1 Modelo de Datos Relacional y Sistemas Manejadores de Bases de Datos Relacionales ........................................................................................................ 3 1.1 Introducción a las Bases de Datos.................................................... 4 1.1.1Dato................................................................................................... 4 1.1.2Información........................................................................................ 4 1.2 Base de Datos ..................................................................................... 5 1.2.1Tipos de Bases de Datos .................................................................. 5 1.2.2Arquitectura de las Bases de Datos .................................................. 6 1.2.3Independencia de datos .................................................................... 7 1.2.4Arquitectura Cliente-Servidor............................................................. 7 1.2.5Características de las Bases de Datos.............................................. 8 1.3 Modelo de Datos ................................................................................. 9 1.3.1Modelos lógicos basados en objetos............................................... 10 1.3.2Modelos lógicos basados en registros............................................. 11 1.3.3Modelos físicos de datos ................................................................. 12 1.4 Sistema Manejador de Bases de Datos Relacionales (RDBMS) ... 13 1.4.1Reglas de Codd............................................................................... 13 1.4.2Características del RDBMS............................................................. 14 1.4.3Arquitectura del RDBMS ................................................................. 15 1.4.4Esquema de seguridad del RDBMS................................................ 16 1.4.5Clasificación de los RDBMS............................................................ 16 2 Lenguaje SQL..................................................................................................... 17 2.1 Estándares SQL ANSI 89, 92 y 99.................................................... 18 2.1.1SQL ANSI 89 ................................................................................... 18 2.1.2SQL ANSI 92 ................................................................................... 19 2.1.3SQL ANSI 99 ................................................................................... 19 2.2 Definición de datos........................................................................... 20 2.2.1Tipos de datos del sistema.............................................................. 21 2.2.2El Valor Nulo.................................................................................... 21 2.3 Tablas ................................................................................................ 21 2.3.1Convención de nombres.................................................................. 22 2.3.2Creación de tablas........................................................................... 22 2.3.3Modificación de tablas ..................................................................... 23 2.3.4Eliminación de tablas....................................................................... 23 2.4 Reglas ................................................................................................ 24 2.5 Defaults.............................................................................................. 25 2.6 Llaves e Índices ................................................................................ 26 2.6.1Llaves primarias .............................................................................. 26 2.6.2Llaves foráneas ............................................................................... 26 2.6.3Índices ............................................................................................. 28 2.7 Manipulación de datos ..................................................................... 29 2.7.1Algebra relacional............................................................................ 29 2.7.2Selección de datos .......................................................................... 30 2.7.3Inserción de datos ........................................................................... 35 2.7.4Eliminación de datos ....................................................................... 36 2.7.5Actualización de datos..................................................................... 36 2.8 Vistas ................................................................................................. 37 2.9 Transacciones................................................................................... 38 2.9.1Commit y Rollback........................................................................... 38 2.10 Procedimientos almacenados...................................................... 39 3 Administración de Bases de Datos .................................................................. 41 3.1 Instalación y actualización de ASE ................................................. 42 3.2 Ambiente de Trabajo ........................................................................ 42 3.2.1Bases de Datos desistema............................................................. 44 3.2.2Log de Bases de Datos ................................................................... 45 3.3 Dispositivo de Base de Datos.......................................................... 46 3.4 Base de Datos en ASE...................................................................... 48 3.5 Control de Acceso ............................................................................ 52 3.5.1Privilegios del administrador de sistema sa..................................... 52 3.5.2Roles de sistema............................................................................. 53 3.5.3Dueños de objetos........................................................................... 54 3.5.4Login................................................................................................ 54 3.5.5Usuarios de Bases de Datos ........................................................... 56 3.5.6Permisos en los objetos de Bases de Datos ................................... 57 3.5.7Grupos............................................................................................. 60 3.5.8Roles ............................................................................................... 61 3.6 Respaldos y Recuperación .............................................................. 62 3.6.1¿Qué es un servidor de Respaldos ASE? ....................................... 63 3.6.2Respaldo de Base de Datos............................................................ 64 3.6.3Respaldo del log de transacciones.................................................. 66 3.6.4Recuperación .................................................................................. 68 4 Tópicos Avanzados de Bases de Datos........................................................... 70 4.1 Data Warehouse................................................................................ 71 4.1.1Data Warehouse y Bases de Datos Transaccionales...................... 71 4.1.2ROLAP y MOLAP............................................................................ 72 4.1.3Data Mart......................................................................................... 72 4.1.4Arquitectura del Data Warehouse.................................................... 73 4.1.5Utilidades y herramientas Back End................................................ 73 4.1.6Metodología de implementación de un Data Warehouse ................ 75 4.1.7Modelo de datos Multidimensional .................................................. 76 4.1.8Metodología de diseño .................................................................... 77 Conclusiones.......................................................................................................... 78 Bibliografía.............................................................................................................. 80 Antecedentes Hoy en día la información es uno de los activos más importantes para las organizaciones ya sean estas de carácter público o privado, por lo cual requieren administrarla y asegurarla ante todo tipo de amenaza que ponga en riesgo las características que le dan valor. La implementación de Bases de Datos permite a dichas organizaciones administrar la información de forma segura y eficiente. El objetivo principal del presente trabajo consiste en proporcionar los conocimientos necesarios para una adecuada administración de las Bases de Datos; Así como mostrar una de las principales tendencias en cuanto al tema. En el capítulo I, Modelo de Datos Relacional y Sistemas Manejadores de Bases de Datos Relacionales, se explican los conceptos fundamentales de las Bases de Datos, entre ellos encontramos la definición de Bases de Datos, así como los elementos que las conforman; Otro tema que también será tratado en este capítulo es el Sistema Manejador de Bases de Datos, su función, componentes y características. En el capítulo II, Lenguaje SQL, se describirán los elementos básicos para el desarrollo de consultas a la Base de Datos utilizando el lenguaje SQL para el manejo de datos (insertar, modificar, eliminar y extraer la información requerida). En el capítulo III, Administración de Bases de Datos, se describen las tareas básicas de administración en SYBASE Adaptive Server Enterprise 15.0. En el capítulo IV, Tópicos Avanzados de Bases de Datos, se describe brevemente el concepto y características del Data Warehouse, el cual es una de las principales tendencias de las Bases de Datos, debido a que es una valiosa herramienta para la toma de decisiones. 2 Modelo de Datos Relacional y Sistemas Manejadores de Bases de Datos Relacionales 1.1 Introducción a las Bases de Datos Anteriormente el administrar la información representaba una labor lenta y complicada; Conforme fue pasando el tiempo, fueron evolucionando los sistemas para administrar la información, desde los Sistemas de Gestión de Archivos1 hasta lo que hoy conocemos como las Bases de Datos. Para poder explotar toda la funcionalidad que ofrecen las Bases de Datos es necesario contar con un software especial que es el Sistema Manejador de Bases de Datos el cual permite administrar de forma segura y eficiente una Base de Datos. Ahora bien para poder entender la importancia de las Bases de Datos en cuanto a la administración de la información tenemos que conocer los conceptos básicos que son desarrollados en este capítulo. 1.1.1 Dato Un dato es la unidad mínima de información, de forma genérica se dice que un dato se puede definir como un hecho aislado y en bruto que por sí sólo no tiene significado ni valor. Relacionando el concepto dato a las Bases de Datos, se puede decir que un dato es la representación simbólica (numérica, alfabética, etc.) de un atributo o característica de un objeto o entidad. 1.1.2 Información Se define como un conjunto de datos relacionados entre sí y que dentro de un contexto determinado tienen un valor y significado; Es posible realizar una toma de decisiones a partir de la información. La obtención de información formal genera gastos, su valor sólo puede ser comparado con el valor que tendrá para el receptor final. El costo de producir información es tangible, se puede medir gracias a algunos dispositivos y medios utilizados, pero la información es conceptual por naturaleza y no tiene características tangibles únicamente representaciones simbólicas. Para dar valor a la información se utilizan las siguientes características: • Accesible: Facilidad y rapidez con que se obtiene la información • Clara: Entendimiento de la información sin ambigüedades • Precisa: Lo más exacta posible • Propia: Relación entre el usuario y lo solicitado por el mismo 1 Sistema que consistía en almacenar la información en varios archivos de texto, dichos archivos no tenían relación entre ellos, lo que resultaban en información redundante. 4 • Oportuna: Disponible cuando se requiera • Flexible: Adaptable a la toma de decisiones • Verificable: Permite el Análisis • Imparcial: Modificación por el dueño de la información únicamente • Cuantificable: Cualquier dato procesado produce información 1.2 Base de Datos Una Base de Datos es un conjunto de datos interrelacionados, que tienen un objetivo en común y una estructura basada en las relaciones y restricciones reales existentes entre cada dato. Una Bases de Datos puede verse como un único repositorio central de información al cual tienen acceso múltiples usuarios al mismo tiempo. El objetivo de las Bases de Datos es almacenar y administrar la información de las organizaciones. 1.2.1 Tipos de Bases de Datos Una Base de Datos puede clasificarse ya sea por su contenido o bien por la variabilidad desu contenido, a continuación enlisto los tipos de Base de Datos según su criterio de clasificación: Según la variabilidad de los datos almacenados: • Bases de Datos estáticas: su contenido no puede ser modificado ya que son Bases de Datos solo de consulta, por lo general almacenan datos históricos que sirven como fuente de información para realizar una toma de decisiones o bien crear modelos de comportamiento • Bases de Datos dinámicas: su contenido puede ser modificado a través del tiempo agregando, modificando o eliminando datos, dentro de esta categoría se encuentran las Bases de Datos Transaccionales, las cuales almacenan la información generada en el día a día de una organización Según su contenido: • Bibliográficas • Numéricas • Directorios • Bancos de imágenes, audio, video, multimedia, etc. 5 1.2.2 Arquitectura de las Bases de Datos Con la finalidad de separar la Base de Datos física de los programas de aplicación que acceden a ella, las Bases de Datos se alinean con la arquitectura definida por el grupo ANSI/SPARC2 la cual divide a la Base de Datos en tres niveles: externo, conceptual e interno. • Nivel interno: En este nivel se define como se almacenará físicamente la información, así como los métodos para acceder a ella. También es conocido como Nivel físico • Nivel conceptual: Cada organización cuenta con diferentes áreas que soportan su operación diaria, cada uno de estas áreas tiene ciertos requerimientos de información y aplicaciones, por lo cual la arquitectura de Bases de Datos debe abstraer todos estos requerimientos y plasmarlos en la Base de Datos, el nivel conceptual hace que esto sea posible gracias a que representa todos estos requerimientos incluyendo la definición de los datos y las relaciones entre ellos • Nivel externo: Los usuarios y aplicaciones pueden acceder a la información contenida en la Base de Datos por medio de este nivel • Las correspondencias: Se pueden definir como una asociación de distintas representaciones para un mismo dato Fig. 1-1 Arquitectura ANSI/SPARC 2 ANSI/SPARC es un grupo de estudio del Standard Planning and Requirements Committee (SPARC) del ANSI (American National Standards Institute), que se ocupa de todo lo relacionado a computadoras e informática 6 1.2.3 Independencia de datos Partiendo de la arquitectura de tres niveles se puede conceptualizar a la independencia de datos como aquella que hace posible modificar el esquema en un nivel sin necesidad de modificar el esquema del nivel superior inmediato. Existen dos tipos de independencia de datos: • Independencia lógica: al modificar una Base de Datos ya sea al agregar o eliminar una entidad, se dice que se está modificando el esquema en un nivel conceptual, y gracias a la independencia lógica es posible realizar esta modificación sin tener que actualizar los esquemas del nivel externo que no tengan relación con dicha entidad • Independencia física: es posible modificar el esquema interno sin tener que modificar el esquema en el nivel conceptual o externo. Por ejemplo, para mejorar el rendimiento al ejecutar una consulta o actualización de datos, puede ser necesario modificar la ubicación de los archivos físicos, la independencia física permita realizar esta modificación sin tener que realizar modificaciones adicionales a los niveles superiores 1.2.4 Arquitectura Cliente-Servidor La arquitectura Cliente-Servidor se define como la relación entre una máquina llamada “cliente” quien realiza una solicitud a otra máquina llamada “servidor” que será la encargada de realizar las tareas necesarias para responder a la solicitud del cliente y así enviarle un resultado. Para que esta relación sea posible ambas máquinas deben estar conectadas a la misma red. Gracias a la arquitectura Cliente-Servidor se optimiza el rendimiento, ya que el procesamiento de una solicitud se divide entre el cliente y servidor. Hablando de Bases de Datos, se observa que esta arquitectura es de gran utilidad ya que el cliente es el encargado de la interface con el usuario (pantallas, reportes, etc.), dejando al servidor de Bases de Datos a cargo de la ejecución de consultas a la Base de Datos, el almacenamiento e integridad de los datos. Durante el procesamiento de una consulta o actualización a la Base de Datos se pueden identificar los siguientes pasos: 1. El usuario crea solicitud ya sea de consulta o actualización. 2. El cliente adecua la solicitud para que pueda ser entendida por el servidor de Base de Datos, y se la envía a través de la red. 3. El servidor de Base de Datos procesa la solicitud, el resultado obtenido lo envía al cliente a través de la red. 4. El cliente recibe el resultado y lo despliega al usuario. 7 Fig. 1-2 Arquitectura Cliente-Servidor 1.2.5 Características de las Bases de Datos Redundancia La redundancia de datos se refiere a la existencia de información duplicada en diferentes tablas dentro de una Base de Datos, lo cual requiere múltiples procedimientos de entrada y actualización, así mismo conduce a muchos problemas que tienen que ver con la integridad y consistencia de los datos. Dentro de una Base de Datos la redundancia debe ser mínima y controlada, en ocasiones existirán motivos válidos de negocios o técnicos para mantener redundancia. Consistencia Decimos que la información es consistente cuando su valor es idéntico en todos los lugares en donde se hace referencia a ella, frecuentemente los problemas de consistencia de información se deben a la redundancia, es muy probable que surjan incongruencias al almacenar la misma información en más de un lugar; ya que al modificarla, eliminarla o agregarla se debe repetir el mismo proceso para cada uno de los campos en donde es almacenada con el riesgo de no realizarlo en su totalidad, generando en este caso la inconsistencia. Integridad La integridad de una Base de Datos se refiere no sólo a que los datos sean consistentes dentro de la Base de Datos, sino que además, los valores que posean los datos sean válidos de acuerdo a las dependencias funcionales entre tablas y de acuerdo a las políticas de negocio. Seguridad La seguridad de una Base de Datos se refiere principalmente al control de acceso, modificación y definición, tanto de los datos como de la estructura de la Base de Datos por parte de los diferentes usuarios que la utilizan. 8 Además de las características anteriores una Base de Datos debe cumplir las siguientes condiciones: • Los datos han de estar almacenadas juntos • Tanto los usuarios finales como los programas de aplicación no necesitan conocer los detalles de las estructuras de almacenamiento ya que lo importante para estos es la información contenida • Los datos son compartidos por diferentes usuarios y programas de aplicación; por lo tanto debe existir un mecanismo común para inserción, actualización, borrado y consulta de datos • Tanto datos como procedimientos pueden ser transportables conceptualmente a través de diferentes sistemas manejadores de Base de Datos 1.3 Modelo de Datos Los modelos son utilizados para representar la realidad, ya sean hechos, comportamientos o simplemente objetos o conceptos. Para poder crear una Base de Datos, es necesario contar con una representación grafica que describa las entidades y las relaciones existentes entre ellas, a esta representación se le conoce como “Modelo de Datos”. Podemos encontrar diferentes definiciones de Modelo de Datos, pero opino que la definición de Jeffrey Ullman describe mejor el concepto: “Un modelo de datos es un sistema formal y abstracto que permite describir los datos de acuerdo con reglas y convenios predefinidos. Es formal pues los objetos del sistema se manipulan siguiendo reglas perfectamente definidas y utilizando exclusivamente los operadoresdefinidos en el sistema, independientemente de lo que estos objetos y operadores puedan significar.” Clasificación de Modelos Los modelos de datos se dividen en tres grupos: • Modelos lógicos basados en objetos • Modelos lógicos basados en registros • Modelos físicos de datos 9 1.3.1 Modelos lógicos basados en objetos Como se definió anteriormente la abstracción del mundo real la podemos representar en los niveles conceptual y externo, los modelos lógicos basados en objetos son utilizados para realizar esta abstracción, son fáciles de estructurar ya que permiten representar los datos, sus relaciones y restricciones tal como los captamos en el mundo real. Existen diferentes modelos de este tipo, pero el más utilizado es el modelo Entidad- Relación por su fácil estructuración. Modelo E-R (Entidad-Relación) Las entidades son objetos con características propias llamadas atributos, las entidades se diferencian de otras gracias a sus atributos y relaciones con otras entidades. Con el modelo E-R podemos representar a estas entidades y sus relaciones. Los símbolos utilizados en el modelo E-R son los siguientes: Fig. 1-3 Simbología Entidad-Relación Para entender mejor el concepto expondré el siguiente ejemplo: En una librería, los vendedores ganan una comisión extra por cada venta realizada; utilizando el modelo E-R identificamos como entidades al vendedor y el libro, dejando la venta como la relación existente entre estas 2 entidades; gráficamente se ilustra: Fig. 1-4 Modelo Entidad-Relación 10 1.3.2 Modelos lógicos basados en registros Para representar los niveles conceptual e interno se utilizan este tipo de modelos; que para representar la realidad emplean registros e instancias, así como ligas o apuntadores que representan las relaciones existentes entre estos registros. Son utilizados para especificar la estructura lógica de la Base Datos. Los tres modelos lógicos basados en registros más comunes son: • Modelo relacional • Modelo de red • Modelo jerárquico Modelo relacional Para representar los datos y sus relaciones, este tipo de modelo utiliza tablas, en donde los registros son representados por los renglones (tuplas) y las características (atributos) de estos registros corresponden a las columnas dentro de las tablas. RFC Nombre Dirección Salario MADG771003 Gabriel Macías Duran Graciela #38 5,000 MESC790523 Carlos Mena Suárez Oniquina #13 5,000 Tabla 1-1 Vendedores Clave Editorial Nombre Costo A1124 Trillas Cómo aprovechar el tiempo 135 B0032 Planeta El beso de la virreina 250 Tabla 1-2 Libros Para representar las relaciones en este modelo, primero debemos entender el concepto de llave primaria, que se define como el campo que representa un atributo principal y que permite identificar de forma única a una entidad. Existen dos formas de representar las relaciones en este modelo: 1.2.1 Crear una tabla con cada una de las llaves primarias de las entidades para las que se pretende representar una relación. RFC Clave MADG771003 A1124 MESC790523 B0032 Tabla 1-3 Relación tipo a 2.2.1 Incluir en una de las tablas (entidades) involucradas, la llave primaria de la otra tabla. RFC Nombre Dirección Salario Clave MADG771003 Gabriel Macías Duran Graciela #38 5,000 A1124 MESC790523 Carlos Mena Suárez Oniquina #13 5,000 B0032 Tabla 1-4 Relación tipo b 11 Modelo de red Para representar los datos el modelo de red utiliza registros, y las ligas o enlaces representan las relaciones entre los registros. Las ligas o enlaces pueden verse como punteros. Una Base de Datos de red, está formada por un grupo de registros, y sus respectivos enlaces. Un registro es un conjunto de campos (atributos), cada campo tiene almacenado un valor, el enlace es la relación existente entre dos registros. Fig. 1-5 Modelo de red Modelo jerárquico Este tipo de modelo representa al igual que el modelo de red a los datos y sus relaciones por medio de registros y ligas. A diferencia del modelo de red, el modelo jerárquico organiza los datos y sus relaciones en un conjunto de árboles y no de graficas arbitrarias. Utiliza un nodo ficticio para representar la raíz del árbol. Por lo tanto una Base de Datos jerárquica será un grupo de árboles del siguiente tipo. Fig. 1-6 Modelo jerárquico 1.3.3 Modelos físicos de datos Se usan para representar el nivel interno o físico, existen pocos modelos de este tipo. Describen la forma en que será almacenada la Base de Datos a nivel hardware (discos). Se clasificación en dos tipos: • Modelo unificador • Memoria de elementos 12 1.4 Sistema Manejador de Bases de Datos Relacionales (RDBMS) Para administrar el acceso a los datos almacenados en una Base de Datos, es utilizado un software especial llamado Sistema Manejador de Bases de Datos o Data Base Management System (DBMS). El cuál es la interface entre los datos físicos (es decir, los datos tal y como están almacenados en el hardware) y los usuarios y/o aplicaciones de la Base de Datos. Ahora bien un RDBMS es un Sistema Administrador de Bases de Datos Relacionales o Relational Data Base Management System (RDBMS), el cual administra las Bases de Datos y las relaciones entre cada una de las tablas contenidas en ella. 1.4.1 Reglas de Codd Existen doce reglas para definir si un DBMS es relacional o no; estas reglas son el pilar de los RDBMS y fueron públicas por el Dr. Edgar Frank Codd en 1985. Regla 1: Regla de la información Toda la información en una Base de Datos Relacional se representa explícitamente en el nivel lógico exactamente de una manera, como valores en una tabla. Regla 2: Regla del acceso garantizado Para todos y cada uno de los datos (valores atómicos) de una Base de Datos Relacional se garantiza que son accesibles a nivel lógico utilizando una combinación de nombre de tabla, valor de clave primaria y nombre de columna. Regla 3: Tratamiento sistemático de valores nulos Los valores nulos (que son distintos de la cadena vacía, blancos y 0) se soportan en los DBMS totalmente relacionales para representar información desconocida o no aplicable de manera sistemática, independientemente del tipo de datos. Regla 4: Catalogo dinámico en línea basado en el modelo relacional La descripción de la Base de Datos se representa a nivel lógico de la misma manera que los datos normales, de modo que los usuarios autorizados pueden aplicar el mismo lenguaje relacional a su consulta, igual que lo aplican a los datos normales. Regla 5: Regla del sub-lenguaje de datos completo Se debe contar con un sub-lenguaje que contemple la definición de datos, la definición de vistas, la manipulación de datos, las restricciones de integridad, la autorización, el inicio y fin de una transacción. Regla 6: Regla de actualización de vistas Todas las vistas que son teóricamente actualizables se pueden actualizar por el sistema. Regla 7: Inserción, actualización y borrado de alto nivel La capacidad de manejar una relación base o derivada como un solo operando se aplica no sólo a la recuperación de los datos (consultas), sino también a la inserción, actualización y borrado de datos. 13 Regla 8: Independencia física de datos Los programas de aplicación y actividades en terminales permanecen inalterados a nivel físico cuando quiera que se realicen cambios en las representaciones de almacenamiento o métodos de acceso. Regla 9: Independencia lógica de datos Los programas de aplicación y actividades en terminales permanecen inalterados a nivel lógico cuandoquiera que se realicen cambios a las tablas base que preserven la información Regla 10: Independencia de integridad Los limitantes de integridad específicos para una determinada Base de Datos relacional deben poder ser definidos en el sublenguaje de datos relacional, y almacenables en el catálogo, no en los programasde aplicación. Regla 11: Independencia de distribución Debe existir un sub-lenguaje de datos que pueda soportar Bases de Datos distribuidas sin alterar los programas de aplicación cuando se distribuyan los datos por primera vez o se redistribuyan éstos posteriormente. Regla 12: Regla de la no subversión Si un sistema relacional tiene un lenguaje de bajo nivel (un sólo registro cada vez), ese bajo nivel no puede ser utilizado para suprimir las reglas de integridad y las restricciones expresadas en el lenguaje relacional de nivel superior (múltiples registros a la vez). 1.4.2 Características del RDBMS Los RDBMS deben facilitar la integridad, seguridad y acceso a los datos; así como reducir la redundancia en su almacenamiento. Deben mantener las aplicaciones independientes del almacenamiento físico de los datos. Un RDBMS debe permitir las siguientes condiciones en una Base de Datos: • Los datos han de estar almacenados juntos • Tanto los usuarios finales como los programas de aplicación no necesitan conocer los detalles de las estructuras de almacenamiento • Los datos son compartidos por diferentes usuarios y programas de aplicación, por tal motivo debe existir un mecanismo común para la inserción, actualización, borrado y consulta de los datos • Tanto datos como procedimientos pueden ser transportables conceptualmente a través de diferentes RDBMS 14 1.4.3 Arquitectura del RDBMS Los componentes de un RDBMS son ilustrados con la siguiente figura: Fig. 1-7 Arquitectura del RDBMS Lenguaje de Definición de Datos o Data Definition Language (DDL) Permite crear, modificar y eliminar estructuras de datos como: tablas, Bases de Datos, índices, etc.; es decir, permite definir la estructura de la Base de Datos mediante comandos como Create (crear), Drop (eliminar), o Alter (modificar). Lenguaje de Manipulación de Datos o Data Manipulation Language (DML) Se utiliza para realizar consultas y edición de la información contenida en la Base de Datos, esto implica: seleccionar (select), insertar (insert), borrar (delete) y modificar (update). Lenguaje de Control de Datos o Data Control Language (DCL) Se utiliza para la definición de los privilegios de control de acceso y edición a los elementos que componen la Base de Datos (seguridad), es decir, permitir (grant) o revocar (revoke) el acceso. Los permisos a nivel Base de Datos pueden otorgarse a usuarios para ejecutar ciertos comandos dentro de la base o para que puedan manipular objetos como roles y los datos que puedan contener estos. Diccionario de Datos o Data Dictionary (DD) El diccionario de datos contiene la definición de los objetos almacenados en la Base de Datos, a esta definición se le conoce como metadatos, que por lo general se describen como “datos acerca de los datos”. En ocasiones el diccionario de datos puede ser muy completo, ya que puede incluir las referencias que describen los datos utilizados por un programa o bien los informes o reportes que requiere cada área dentro de una organización, etc. 15 1.4.4 Esquema de seguridad del RDBMS El acceso al RDBMS se administra por 3 niveles: • Seguridad a nivel Servidor: El usuario final debe tener una cuenta válida dentro de la capa del servidor RDBMS • Seguridad a nivel de Base de Datos: El usuario final debe tener una cuenta valida dentro de una Base de Datos • Seguridad a nivel de Permisos sobre Objetos y Comandos: El usuario final debe tener privilegios sobre los objetos de la Base de Datos y comandos Fig. 1-8 Esquema de seguridad del RDBMS 1.4.5 Clasificación de los RDBMS Los RDBMS pueden ser clasificados por ámbito o volumen de información que pueden almacenar. Las siguientes tablas muestran los RDBMS más populares y su clasificación: Ámbito Comerciales Software libre • SQL Server • Sybase • Oracle • Informix • DB2 • PostgresSQL • Mysql • Sybase(Linux) Tabla 1-5 RDBMS por ámbito Volumen de Información Corporativo Departamental • Oracle • Informix • Sybase DB2 • PostgreSQL • SQL Server • Mysql Tabla 1-6 RDBMS por volumen de información 16 Lenguaje SQL El Lenguaje de Consunta Estructurado o Structured Query Language (SQL) es un lenguaje de consulta para Bases de Datos, fácil de entender ya que su estructura utiliza palabras en inglés, lo que lo hace fácil de aprender y utilizar, las instrucciones se enfocan a qué buscar, dejando al RDBMS la tarea de cómo recuperar la información. La ventaja de la adopción del ANSI SQL, es que los diversos RDBMS tienen que acoplarse al estándar, permitiendo así una mayor compatibilidad entre ellos. Esto implica que conociendo una variante del lenguaje SQL, se tienen los conocimientos necesarios para poder utilizarlo en todos los RDBMS (MS SQL Server, Oracle, Sybase, MySQL, PostgreSQL, DB2, entre otros). Aunque los distintos fabricantes tratan de acoplarse al estándar ANSI SQL, es cierto que cada uno implementa funcionalidades extra que le dan un valor agregado a su producto pero sacrificando un poco la compatibilidad, por lo cual se podrán notar ciertas diferencias entre distintos RDBMS. 2.1 Estándares SQL ANSI 89, 92 y 99 El lenguaje SQL, fue adoptado como estándar de la industria en 1986. Desde entonces se han realizado revisiones al estándar para incorporar nueva funcionalidad conforme la industria de las Bases de Datos lo va requiriendo. Una de las revisiones más importantes fue la de 1992, conocida como ANSI SQL92, actualmente la versión soportada por la mayoría de los RDBMS es el ANSI SQL99 también conocido como SQL3. A continuación enlisto algunas de las características de las diferentes versiones de SQL: 2.1.1 SQL ANSI 89 • Agregan la capacidad conocida como integridad referencial y la descripción de todo el modelo relacional • Se definió que el lenguaje SQL está compuesto por comandos, cláusulas, operadores y funciones de agregado • Estos elementos se combinan para definir y manipular la Base de Datos • Se establecen los elementos de un DBMS (DDL, DML y DCL), así como las instrucciones y sintaxis relacionadas con cada uno de ellos • Establecimiento de las cláusulas del comando SELECT, las cuales son: FROM, WHERE, GROUP BY, HAVING, ORDER BY • Definición de los operadores lógicos: AND, OR y NOT y de comparación • Se determinan las funciones de agregado, tales como: AVG, COUNT, SUM, MAX, MIN 18 2.1.2 SQL ANSI 92 • Toma todas características definidas en el estándar ANSI SQL 89 • Permite la definición de esquemas • Permite la definición de dominios por parte de los usuarios, es decir, tipos de datos definidos por el usuario • Menciona las consideraciones para realizar consultas sencillas, multi-tablas y sub-consultas • Incluye los operadores EXISTS y NOT EXISTS • Contempla el uso de la palabra DISTINCT en una consulta • Menciona algunas consideraciones para el uso de las cláusulas GROUP BY y HAVING • Especifica la definición de vistas en una Base de Datos 2.1.3 SQL ANSI 99 • Toma todas características definidas en los estándares ANSI SQL 89 y 92 • Incluye nuevos tipos de datos escalares: BOOLEAN, CLOB (objeto de caracteres largo) y BLOB (objeto binario grande) • Presenta dos nuevos operadores de totales: EVER y ANY • Incorpora generadores de tipo de dato: REF, ARRAY y ROW • Incluye los operadores EXISTS y NOT EXISTS • Contempla el uso de la palabra DISTINCT en una consulta • Soporta una opción LIKE en CREATE TABLE, lo cual permite que todas o algunas definiciones de columna de una nueva tabla sean copiadas a partir de otra ya existente • Incluye la cláusula WITH para introducir nombres abreviados para determinadas expresiones • Incorpora una nueva expresión de condición IS DISTINCT para la cláusula FROM 19 2.2 Definición de datos Antes de comenzar a trabajarcon SQL, es necesario conocer los elementos que intervienen en la definición de la información en una Base de Datos, para poder manipularla de manera adecuada. La tabla es el elemento fundamental de una Base de Datos Relacional, la cual consiste de una serie de renglones (registros) que representan la información. Cada renglón está dividido en columnas (campos) los cuales deben de tener un tipo de dato establecido. 2.2.1 Tipos de datos del sistema Cada columna dentro de una tabla debe tener asociado un tipo de dato, siendo la labor del diseñador de la Base de Datos, el encontrar el mejor tipo de dato que satisfaga las necesidades de almacenamiento y recuperación de cierta información. Los tipos de datos que se manejan en una base, pueden variar ligeramente entre diferentes RDBMS, sin embargo el estándar ANSI, asegura que cierto tipo de datos estará presente en cualquier RDBMS asegurando así la compatibilidad. Algunos RDBMS implementan sinónimos para los tipos de datos, de manera que puedan cumplir con el ANSI SQL99, sin embargo internamente son convertidos a un tipo de dato que si esté soportado. Por ejemplo MS SQL Server acepta el tipo de dato DOUBLE PRECISION pero lo convierte y maneja como un FLOAT. En la siguiente tabla se muestra en la primera columna, el tipo de dato como se especifica en el lenguaje ANSI SQL y en las demás columnas se indica el tipo de datos equivalente por RDBMS que cumple con dicho estándar. Tipo en SQL99 MySQL PostgreSQL Oracle Sybase Ms SQL Server Descripción smallint smallint smallint smallint (lo convierte a number) smallint smallint Entero con signo de 2 bytes int, integer int,integer integer int (lo convierte a number) int int Entero con signo de 4 bytes float() float float() float float Número de punto flotante double double double precision double precision (lo convierte a float) double precision double precision (lo convierte en float) Número doble real real real (lo convierte a float) real real Número real numeric(p,d) numeric(p,d) numeric(p,d) number(p,d) numeric(p,d) decimal(p,d) Numérico con precisión p y d decimales character varying(n) varchar(n) varchar(n) varchar2(n) varchar(n) varchar(n) Carácter de longitud variable char, character(n) char(n) char(n) char(n) char(n) char(n) Cadena de caracteres de longitud fija date date date Fecha sin hora del día time time time Hora del día timestamp timestamp timestamp date datetime datetime Fecha y hora del día boolean boolean bit bit Valor booleano blob blob bytea blob image image Binary large object clob text text clob text text Character large object interval interval Intervalo de tiempo Tabla 2-1 Tipo de datos 20 2.2.2 El Valor Nulo Es importante conocer el concepto de valor nulo, en el contexto de una Base de Datos, debido a que frecuentemente un valor nulo es confundido con un valor numérico de 0 o una cadena vacía. Un valor nulo se representa en SQL con la cláusula NULL y representa la ausencia de información. Por ejemplo si en un listado de empleados algunos aparecen en el dato de comisión como NULL, esto indica que no se tiene dicha información aunque erróneamente se podría interpretar que estos empleados tienen comisión del 0%. Ahora, suponiendo que los datos que aparecen como NULL corresponden a la edad, indican que el dato no fue proporcionado y por lo tanto se carece de dicha información y claramente se observa que no es lo mismo que una edad de 0 años. Hasta este momento se ha hablado únicamente de tipos de datos numéricos, aunque los valores nulos también se utilizan en cualquier otro tipo de dato, por ejemplo en texto: No es lo mismo una cadena vacía que un valor nulo. 2.3 Tablas Las tablas son el elemento fundamental que compone a una Base de Datos relacional porque todo gira en torno a ellas. Las tablas son estructuras de almacenamiento que albergan la información en forma de registros (renglones) que deben de ser identificados de manera única y esto se logra a través de una llave primaria. Atributo1 con tipo de dato especificado Tabla Atributo2 con tipo de dato especificado AtributoN con tipo de dato especificado... Valor del atributo1 Valor del atributo2 Valor del atributoN... R eg is tr os Valor del atributo1 Valor del atributo2 Valor del atributoN... Valor del atributo1 Valor del atributo2 Valor del atributoN... . . . Valor del atributo1 Valor del atributo2 Valor del atributoN... . . . . . . Fig. 2-1 La Tabla 21 2.3.1 Convención de nombres Los nombres que se le asignan a los objetos de una Base de Datos, están sujetos a ciertas reglas que varían entre RDBMS, incluso de una versión a otra del mismo. Algunas de las reglas que deben observarse se muestran en la siguiente tabla: RDBMS Reglas Todos • No pueden usarse palabras reservadas del servidor SQL. • Es aconsejable usar nombres descriptivos. • No pueden existir dos objetos (aunque sean de distinto tipo) con el mismo nombre para un usuario en particular Sybase • Deben empezar con una letra. • Pueden contener letras: A-Z, a-z, números: 0-9, _, #, $. Oracle • Deben empezar con una letra. • Pueden contener letras: A-Z, a-z, números: 0-9, _, #, $. MySQL • Puede empezar con los símbolos _, $, letras o números • Pueden contener letras: A-Z, a-z, números: 0-9, _, $. PostgreSQL • Puede empezar con el símbolo _, y letras • Pueden contener letras: A-Z, a-z, números: 0-9, _, $ Ms SQL Server • Puede empezar con el símbolo #,_, letras • Pueden contener letras: A-Z, a-z, números: 0-9, _, $ Tabla 2-2 Convención de Nombres 2.3.2 Creación de tablas La sintaxis básica para la creación de una tabla es la siguiente: Sintaxis CREATE TABLE <nombre_tabla> ( <nombre_campo1> <tipo_de_dato> [NULL | NOT NULL] [DEFAULT <val_predeterminado>], <nombre_campo2> <tipo_de_dato> [NULL | NOT NULL] [DEFAULT <val_predeterminado>], … <nombre_campoN> <tipo_de_dato> [NULL | NOT NULL] [DEFAULT <val_predeterminado>] ) Ejemplo CREATE TABLE pais ( id_pais numeric(3) NOT NULL, nombre varchar(100) NOT NULL ) Tabla 2-3 Sintaxis creación de tablas 22 2.3.3 Modificación de tablas Dependiendo del RDBMS esta modificación de estructura de la tabla ofrece mayor o menor flexibilidad. Por ejemplo, MySQL es de las más flexibles, porque permite cambiar incluso el tipo de dato de una columna mientras que en Sybase o MS SQL Server es necesario eliminar y volver a crear la tabla con la estructura deseada. Características soportadas: MySQL PostgreSQL Oracle Sybase MS SQL Server Agregar campo Si Si Si Si Si Eliminar campo Si Si Si No Si Renombrar campo Si Si Si No No Cambiar el tipo de dato del campo Si No Si No Si Definir llave primaria Si Si Si Si Si Definir llave foránea Si Si Si Si Si Renombrar tabla Si Si Si No No Tabla 2-4 Los RDBMS y el soporte para modificación de tablas La sintaxis varía de un RDBMS, debido a las diferentes características soportadas, sin embargo todos ellos utilizan la cláusula ALTER TABLE para realizar las modificaciones posibles, a una tabla. La sintaxis general para agregar un campo es la siguiente: Sintaxis ALTER TABLE <nombre_tabla> add <nombre_campo> <tipo_dato> [DEFAULT <val_predeterminado>] [NOT NULL | NULL] Ejemplo ALTER TABLE pais add superficie numeric(6) NULL Tabla 2-5 Sintaxis modificación de tablas Cabe señalar que siempre que se agregue un campo a una tabla, éste debe permitir valores NULOS. 2.3.4 Eliminación de tablas Para eliminar una tabla y los datos que contiene, se utiliza la siguiente instrucción: Sintaxis DROP TABLE <nombre_tabla> Ejemplo DROP TABLE pais Tabla 2-6 Sintaxis eliminación de tablas 23 2.4 Reglas Las reglas dentro de la Base de Datos permiten definir condiciones que debe cumplir la información para que sea válida. Por ejemplo se puede definir una regla que especifiqueque los ingresos de un empleado no sobrepasen los $10,000 pesos. En la práctica, muchas de estas reglas no se definen en la Base de Datos sino mediante la lógica de una aplicación desarrollada en algún lenguaje, que tiene acceso a la información, ya que el exceso de reglas puede disminuir el rendimiento del RDBMS en los procesos de inserción y modificación de información. En Sybase y MS SQL Server las reglas se pueden crear como objetos independientes que se vinculan a distintas tablas. En cambio en Oracle y PostgreSQL no son objetos independientes y sólo pueden ser definidas como restricciones que afecta a una sola tabla. MySQL PostgreSQL Oracle Sybase MS SQL Server Soporta definición de reglas No Si Si Si Si Tabla 2-7 Los RDBMS y el soporte para reglas En las siguientes tablas describo la sintaxis para crear reglas dependiendo el RDBMS: Sintaxis para Sybase y MS SQL Server CREATE RULE <nombre_regla> AS <condicion> Ejemplo CREATE RULE regla_salario AS @sueldo <= 10000 Tabla 2-8 Sintaxis creación de reglas Sybase y Ms SQL Server Sintaxis para Oracle y PostgreSQL ALTER TABLE <nombre_tabla> ADD CONSTRAINT <nombre_restriccion> CHECK <condicion> Ejemplo ALTER TABLE empleado ADD CONSTRAINT regla_salario CHECK @sueldo <= 10000 Tabla 2-9 Sintaxis creación de reglas Oracle y PostgreSQL 24 2.5 Defaults Los defaults establecen que valor será registrado de manera predeterminada para una columna, en caso de que no se especifique al momento de introducir los datos. En algunos RDBMS los defaults son objetos que se pueden emplear en diferentes tablas, mientras que en los demás, están ligados a la definición de la tabla. Al igual que con las reglas, la funcionalidad de los defaults pueden implementarse utilizando la lógica de la aplicación. Nuevamente en Sybase y MS SQL Server es posible definir un DEFAULT como un objeto independiente que se puede vincular a varias campos de una o más tablas, mientras que en Oracle, MySQL y PostgreSQL los defaults están ligados a un solo campo. MySQL PostgreSQL Oracle Sybase MS SQL Server Soporta definición de defaults Si Si Si Si Si Tabla 2-7 Los RDBMS y el soporte para defaults En Todos los RDBMS, los valores predeterminados se pueden especificar al momento de generar una tabla, o en algunos casos es posible modificar la tabla para agregar estos valores predeterminados: Sintaxis <nombre_campo> <tipo_dato> DEFAULT <val_predeterminado> Ejemplo al crear una tabla CREATE TABLE cliente (id_cliente NUMERIC(10),fecha_afiliacion DATE DEFAULT SYSDATE) Ejemplo al modificar una tabla ALTER TABLE cliente add fecha_afiliacion DATE DEFAULT SYSDATE Tabla 2-8 Sintaxis definición de defaults 2.6 Llaves e Índices Un índice es una estructura de almacenamiento físico que permiten recuperar datos de una manera muy eficiente. En un esquema relacional, cada registro dentro de una tabla debe de ser identificado de manera única, y esto se logra a través de una llave primaria, a la cual se le genera de manera automática un índice, que ayuda a ser más eficiente el proceso de consulta de la información. También existen las llaves foráneas, que son las columnas que hacen referencia a la llave primaria de otra tabla. A través de ellas se establecen relaciones entre tablas. Es un error frecuente confundir entre índices y llaves, quizá sea el hecho de que al crear una llave primaria se genera un índice, lo cual no sucede para una llave foránea. Hay que tener presente que los índices tienen como función acelerar el proceso de recuperación de la información. Tanto una llave primaria como una foránea establecen restricciones sobre el valor que pueda tener una columna. Las restricciones pueden tener un nombre asignado por el usuario o si este no se especifica, entonces el RDBMS será el encargado de asignarle un nombre interno a dicha restricción. 25 2.6.1 Llaves primarias Una llave primaria permite identificar de manera única un registro dentro de una tabla. Al crear una llave de este tipo se genera automáticamente un índice de valores únicos, por lo que los valores de los campos que involucra la llave primaria no se pueden repetir ni ser nulos. Existen varias formas de declarar una llave primaria: Sintaxis CONSTRAINT <nombre_llaveprimaria> PRIMARY KEY (<nombre_campo1>,<nombre_campo2>,…,<nombre_campon>) Ejemplo al crear una tabla con un campo como llave primaria CREATE TABLE pais ( id_pais numeric(3) NOT NULL PRIMARY KEY, nombre varchar(100) ) Ejemplo al crear una tabla con dos campos como llave primaria CREATE TABLE pedido ( id_cliente numeric(10) NOT NULL, id_producto numeric(10) NOT NULL, fecha date, CONSTRAINT pedido_pk PRIMARY KEY (id_cliente, id_producto) ) Ejemplo al modificar una tabla ALTER TABLE pedido ADD CONSTRAINT pedido_pk PRIMARY KEY (id_cliente, id_producto) Tabla 2-9 Sintaxis creación de llave primaria 2.6.2 Llaves foráneas Las llaves foráneas son atributos de una tabla que hacen referencia a la llave primaria de otra tabla. Estas llaves foráneas permiten establecer relaciones entre las distintas tablas que existen dentro de la Base de Datos. La mayoría de los RDBMS implementan restricciones (constraints) cuando se genera una llave foránea, de este modo asegura la integridad de la información almacenada en la Base de Datos. Las versiones 3 y anteriores de MySQL no implementaban estas restricciones. Actualmente se puede decidir entre manejarlas o no manejarlas. Quizá en un futuro de manera predeterminada se manejara el soporte para llaves foráneas. Lo que implica la falta de este tipo de restricciones puede ser un problema de integridad en la información, pero por otro lado las restricciones se pueden implementar con lógica de aplicación en caso de que exista (Por ejemplo una aplicación escrita en Java puede realizar verificación de integridad) liberando así a la Base de Datos de esta tarea permitiendo que MySQL trabaje más rápido que cualquier otro RDBMS. 26 Dependiendo del tipo de información y de aplicación a desarrollar se puede sacrificar la característica a cambio de rapidez en respuesta. Las llaves foráneas se pueden crear dentro de la definición de la tabla, o una vez que esta ya existe, se puede utilizar la cláusula ALTER TABLE para agregar esta restricción. A diferencia de las llaves primarias, las llaves foráneas no generan un índice, por lo que de ser necesario se deberá crear con la cláusula CREATE INDEX. Sintaxis CONSTRAINT <nombre_llaveforanea> FOREIGN KEY (<nombre_campo>) REFERENCES <nombre_tabla_referenciada>(<nombre_llaveprimaria_referenciada>) Ejemplo al crear una tabla CREATE TABLE resultado ( id_pais1 NUMBER(3) NOT NULL, id_pais2 NUMBER(3) NOT NULL, resultado VARCHAR2(15) NOT NULL, CONSTRAINT pais_01_fk FOREIGN KEY (id_pais1) REFERENCES pais(id_pais), CONSTRAINT pais_02_fk FOREIGN KEY (id_pais2) REFERENCES pais(id_pais) ) Ejemplo al modificar una tabla ALTER TABLE resultado ADD CONSTRAINT pais_01_fk FOREIGN KEY (id_pais1) REFERENCES pais(id_pais); ALTER TABLE resultado ADD CONSTRAINT pais_02_fk FOREIGN KEY (id_pais2) REFERENCES pais(id_pais); Tabla 2-10 Sintaxis creación llave foránea 27 2.6.3 Índices Un índice es una estructura de almacenamiento físico que ocupa un espacio. Los índices ayudan al Servidor SQL a localizar datos y son transparentes para el usuario. El propósito principal de un índice es proporcionar un acceso más rápido a los datos, aunque en algunos casos su propósito es asegurar que el contenido de un campo sea único. Abusar del empleo de índices puede llevar a que se degrade el tiempo de respuesta del servidor en lugar de mejorarlo, esto se debe a que en operaciones que involucran inserción, modificación o eliminación de datos, los índices deben de seractualizados lo cual puede consumir un tiempo considerable. Por lo general se suele crear un índice únicamente cuando una columna es empleada frecuentemente en una cláusula WHERE y la tabla tiene un tamaño considerable. La sintaxis para creación y eliminación de índices se muestra en las siguientes tablas: Sintaxis CREATE [UNIQUE] INDEX <nombre_índice> Ejemplo CREATE INDEX idx_empleado ON empleado(fecha _ contratación) Tabla 2-11 Sintaxis creación de índices Sintaxis DROP INDEX <nombre_índice> Ejemplo DROP INDEX idx_empleado Tabla 2-12 Sintaxis eliminación de índices 28 2.7 Manipulación de datos La mayor parte del trabajo con SQL gira en torno a cuatro comandos: • SELECT: Permite seleccionar (recuperar) información de una tabla • INSERT: Permite agregar información a una tabla • DELETE: Permite eliminar información de una tabla • UPDATE: Permite actualizar información que existe en una tabla Para el empleo de cualquiera de los comandos mencionados anteriormente es indispensable tomar en cuenta dos puntos: • Para expresar un valor de tipo alfanumérico o fecha, es requisito entrecomillarlo con comillas simples • Todo valor que no se especifique entre comillas simples, será interpretado como tipo de dato numérico 2.7.1 Algebra relacional SQL se basa en el álgebra relacional, por ello es importante conocer las operaciones del álgebra relacional y su relación con SQL. Fig. 2-2 Algebra relacional 29 Selección (Restricción) La operación de selección genera un subconjunto de los renglones de una tabla, con base en un criterio (restricción) establecido. Esta operación del álgebra relacional es realizada por la cláusula WHERE de SQL. Proyección La proyección selecciona y genera un subconjunto con los atributos (columnas) indicados de una tabla. También es conocida como operación vertical. Esta operación es realizada por la cláusula SELECT de SQL. Unión La operación unión realiza la misma acción que en el álgebra de conjuntos, es decir {1,4,5,10} U {1,4,3,9} = {1,3,4,5,9,10}. Esta operación se realiza con la cláusula UNION de SQL. Producto Cartesiano El producto cartesiano es el producto cruz entre 2 tablas: {a, b} X {1, 2} = {a1, a2, b1, b2} El resultado es la combinación de cada renglón de una tabla con cada renglón de la otra tabla. En SQL esta operación se lleva a cabo cuando se ocupa la cláusula FROM especificando 2 o más tablas, y no se especifica una restricción que indique la relación entre las tablas. Join La operación join es en esencia un producto cartesiano, donde se selecciona los renglones que satisfagan las condiciones indicadas que establecen la relación entre las tablas involucradas. Esta es una operación muy común en las Bases de Datos relacionales. 2.7.2 Selección de datos Las tablas dentro de una Base de Datos son las estructuras que tienen almacenada la información en forma de registros. Para poder recuperar esa información almacenada, se requiere del comando SELECT de SQL. El comando SELECT es sumamente útil, ya que a través de él es posible realizar desde una consulta simple que sólo involucra una tabla, hasta una consulta compleja donde intervienen dos o más tablas, varias condiciones, agrupaciones de datos y ordenamientos. 30 Sintaxis SELECT {* | [DISTINCT] <campo>, <campo> … } FROM <nombre_tabla>, [ <nombre_tabla> ] [WHERE <condición> ] [GROUP BY <campo>, <campo>,… ] [HAVING <condición> ] [ORDER BY <campo> [ASC|DESC], <campo> [ASC|DESC], … ] Seleccionar el nombre del empleado de la tabla empleado y el nombre de departamento de la tabla departamento. SELECT empleado.nombre, departamento.nombre FROM empleado, departamento WHERE departamento.id_departamento = empleado.id_departamento Seleccionar todos los campos de la tabla empleado. SELECT * FROM empleado Seleccionar todos los campos de la tabla empleado y el nombre del departamento de la tabla departamento. SELECT empleado.*, departamento.nombre FROM empleado, departamento WHERE departamento.id_departamento = empleado.id_departamento Tabla 2-13 Sintaxis selección de datos Lo que se puede apreciar en la estructura de la instrucción SELECT, es que nunca debe faltar ni la palabra SELECT, ni FROM. Todos los demás elementos son opcionales. Comando SELECT Este comando indica que la instrucción a ejecutar es una consulta a la Base de Datos. SELECT permite indicar el nombre de los campos que queremos mostrar en la consulta. En caso de querer mostrar todos los campos de una tabla se emplea el comodín asterisco: *. Cuando se realiza una consulta que involucra dos o más tablas, al nombre de cada campo se le antepone el de la tabla a la que pertenece. Sintaxis <nombre_tabla>.<nombre_campo> Ejemplo SELECT empleado.nombre, departamento.nombre FROM empleado, departamento Tabla 2-14 Sintaxis selección de datos multi-tablas Uso de alias para columnas Es posible utilizar seudónimos (alias) para cambiar el nombre de las columnas mostradas en una consulta, esto puede servir para hacer más legible los resultados mostrados, o porque así lo requiere alguna aplicación. Para colocar los seudónimos es necesario especificarlo mediante la cláusula AS. Sintaxis <nombre_campo> AS <alias> Ejemplo SELECT nombre AS nombre_empleado, FROM empleado Tabla 2-15 Sintaxis selección de datos empleando alias para columnas 31 Uso de alias para tablas Cuando una consulta involucra más de una tabla, es necesario especificar de cual tabla es el campo que se desea mostrar. Esto lo podemos ver en el ejemplo c), que involucra a las tablas empleado y departamento. Es frecuente el uso de seudónimos para las tablas, con la finalidad de simplificar la escritura de la consulta. El seudónimo se coloca inmediatamente después del nombre de la tabla. Sintaxis <tabla> [seudónimo] Ejemplo SELECT e.nombre, d.nombre FROM empleado e, departamento d Tabla 2-16 Sintaxis selección de datos empleando alias para tablas Cláusula FROM La cláusula FROM sirve para indicar las tablas de las cuales se desea mostrar la información. Cláusula WHERE La cláusula WHERE permite delimitar los registros que serán mostrados en la consulta, a través de criterios o condiciones. Es posible utilizar los operadores lógicos: OR, AND y NOT para combinar expresiones y refinar el criterio de consulta. Comparación El valor NULL es un valor especial por lo cual se debe tener sumo cuidado cuando se desee utilizar condiciones con NULL. La única forma de comparar contra un valor NULL es utilizar el operador IS o IS NOT. SELECT * FROM empleado WHERE comision = NULL; --incorrecto SELECT * FROM empleado WHERE comision is NULL; --correcto Las expresiones más frecuentes son las que involucran una comparación entre dos elementos, a continuación se enlistan los operadores utilizados para realizar dicha comparación. Comparación Operador Ejemplo Igualdad = empleado.id_departamento = departamento.id_departamento Desigualdad <> ó ¡= nombre_cargo ¡= ‘Director’ Mayor que > sueldo < 15000 Menor que < edad > 35 Mayor o igual que >= sueldo >=15000 Menor o igual que <= edad <= 35 Similar a LIKE nombre_empleado like ‘Al%’ (% es un comodín) Es IS edad IS NULL No es IS NOT edad IS NOT NULL Tabla 2-17 Operadores de comparación 32 La comparación de similitud que se hace mediante el uso de la cláusula LIKE, requiere de incluir comodines, que sustituyan uno o varios caracteres. El signo “%“ representa cero o más caracteres y el signo “-“ representa un carácter. En SQL es posible abreviar la forma de escribir ciertas condiciones: Expresión Operador Quedando de la siguiente manera: Sueldo >= 10000 AND sueldo <=15000 BETWEEN Sueldo between 10000 and 15000 Nombre_cargo = ‘Gerente’ OR nombre_cargo = ‘Presidente’ OR nombre_cargo = ‘Vicepresidente’ OR nombre_cargo = ‘Director’ INnombre_cargo in (‘Gerente’, ‘Presidente’, ‘Vicepresidente’, ‘Director’) Nombre_cargo ¡= ‘Jefe de departamento’ AND nombre_cargo ¡= ‘Vendedor’ NOT IN nombre_cargo not in (‘Jefe de departamento’, ‘Vendedor’) Tabla 2-18 Operadores de condición Cláusula GROUP BY En la cláusula GROUP BY se indica el o los campos por los cuales se desea agrupar un conjunto de registros. Comúnmente esta agrupación va acompañada con una serie de funciones que realizan ciertas operaciones sobre el valor de los campos indicados. Estas funciones son conocidas como funciones de agregado o agrupación: Función Acción COUNT(*) Regresa el número de registros encontrados COUNT(<campo>) Regresa el número de registros cuyo valor del campo especificado no es nulo SUM(<campo>) Suma los valores de la columna especificada AVG(<campo>) Promedia los valores del campo especificado MIN(<campo>) Regresa el valor mínimo del campo especificado MAX(<campo>) Regresa el valor máximo del campo especificado Tabla 2-19 Funciones de agregado Algunos ejemplos de la utilización de las funciones de agregados serian: Descripción Sentencia Mostrar la clave de departamento y el dinero total empleado para pagar a los empleados de dicho departamento. SELECT id_departamento, SUM(sueldo) FROM empleado GROUP BY id_departamento Mostrar la edad promedio por cada clave de departamento. SELECT id_departamento, AVG(edad) FROM empleado GROUP BY id_departamento Mostrar el nombre de departamento y el dinero total empleado para pagar a los empleados de dicho departamento. SELECT d.nombre, SUM(e.sueldo) FROM empleado e, departamento d WHERE e.id_departamento = d.id_departamento GROUP BY d.nombre Tabla 2-20 Uso de funciones de agregado 33 Cláusula HAVING Esta cláusula es el equivalente a la cláusula WHERE, es decir, especifica un criterio o condición, pero la diferencia radica en que se ocupa únicamente cuando se desea especificar una función de agregado en la condición. Ejemplo Sentencia Mostrar la clave de departamento y sueldo promedio de sus empleados, de aquellos departamentos cuyo salario promedio sea mayor que 12000 SELECT id_departamento, AVG(sueldo) FROM empleado GROUP BY id_departamento HAVING AVG(sueldo)>12000 Tabla 2-21 Uso cláusula HAVING Cláusula ORDER BY Esta cláusula permite indicar los campos por los cuales se desea ordenar la información mostrada. Es posible indicar si el orden es descendente o ascendente, de manera predeterminada es ascendente. Algunos RDBMS permiten realizar este ordenamiento especificando, en lugar del nombre del campo, la posición de este. Ejemplos Sentencia Mostrar todos los datos de los empleados ordenados por nombre de manera descendente. SELECT * FROM empleado ORDER BY nombre DESC Mostrar clave, nombre del empleado y salario ordenados por salario. SELECT id_empleado, nombre, sueldo FROM empleado ORDER BY 3 Tabla 2-22 Uso cláusula ORDER BY Uso de Joins Un join se emplea para definir la relación que existe entre dos tablas. El INNER JOIN es una cláusula que permite definir estas relaciones. Este tipo de join puede expresarse de otra manera utilizando restricciones en la sección del WHERE en una consulta. Ejemplo con INNER JOIN SELECT e.nombre, d.nombre FROM empleado e INNER JOIN departamento d ON e.id_departamento = d.id_departamento Ejemplo con WHERE SELECT e.nombre, d.nombre FROM empleado e, departamento d WHERE e.id_departamento = d.id_departamento Tabla 2-23 Uso de Joins El resultado de las dos consultas mostradas es el mismo. Lo único que varía es la forma de especificar la relación entre las tablas. 34 Uso de la Unión Permite realizar la unión del resultado de dos consultas. El proceso de UNION elimina los registros duplicados, aunque algunos RDBMS proporcionan una cláusula que permite mostrar todos los registros resultantes de la unión. Sintaxis <instrucción SELECT_1> UNION <instrucción SELECT_2> Ejemplo SELECT MAX(sueldo) FROM empleado UNION SELECT MIN(sueldo) FROM empleado Tabla 2-24 Uso de la Unión 2.7.3 Inserción de datos A través del comando INSERT de SQL, se introduce información a una tabla. Sintaxis INSERT INTO <tabla> [(<nombreCampo1>, <nombreCampo2>, <nombreCampo3> …)] {VALUES (<valorCampo1>, <valorCampo2>, <valorCampo3> … ) | <Expresión select> } Ejemplo INSERT INTO empleado (id_empleado id_departamento, id_cargo, nombre, edad, sexo, sueldo, fecha_contratacion) VALUES (20, 2, 1, ‘Lorena Aguilar’, ‘M’, 7000.00, ‘2002-04-26’) Tabla 2-25 Sintaxis inserción de datos Comando INSERT Esta cláusula permite indicar que la operación a realizar es la inserción de un registro. Cláusula INTO Esta cláusula permite indicar la tabla en donde se realizará dicha inserción. Únicamente se puede especificar una tabla a la vez. Después del nombre de la tabla puede o no ir el nombre de los campos donde se va insertar información, esto es opcional, pero es muy recomendable no omitirlos, porque le resta legibilidad a la instrucción. Si no se especifica el nombre de los campos que se van a insertar, el DBMS identifica que se desea insertar información en cada uno de los campos, en el orden definido por la estructura de la tabla. Cláusula VALUES Esta cláusula permite especificar los valores a insertar para cada uno de los campos involucrados en la sentencia. 35 2.7.4 Eliminación de datos El comando DELETE elimina registros de la tabla indicada con la posibilidad de indicar un criterio, en caso de omitirlo, se eliminan todos los registros de la tabla. Sintaxis DELETE FROM <nombre_tabla>[ WHERE <condición> ] Ejemplo DELETE FROM empleados WHERE id_empleado=7 Tabla 2-26 Sintaxis eliminación de datos Cláusula DELETE La cláusula DELETE permite indicar que la operación a realizar es una eliminación de registros Cláusula FROM La cláusula FROM permite especificar la tabla de donde se desea eliminar registros. Nota: En algunos RDBMS, la cláusula FROM se puede emplear cuando es necesario especificar varias tablas que se encuentran involucradas en la condición de borrado. Cláusula WHERE La cláusula WHERE permite delimitar el conjunto de registros a eliminar, si no se especifica esta condición, se eliminaran todos los registros que contenga la tabla especificada. Para mayor detalle de las condiciones consulte el contenido de la sección 2.7.2. 2.7.5 Actualización de datos Para modificar o actualizar los valores de los registros de una tabla se utiliza el comando UPDATE. Si no se especifica una condición con la cláusula WHERE, todos los registros que existan en la tabla serán actualizados. Sintaxis UPDATE <nombre_tabla> SET <campo1> = <valor1>, <campo2> = <valor2>, …. [ WHERE <condición> ] Ejemplo Actualizar el salario a 27,000 pesos y edad a 27 años del empleado con clave 12. UPDATE empleado SET sueldo = 27000, edad = 27 WHERE id_empleado = 12 Tabla 2-27 Sintaxis actualización de datos Cláusula UPDATE La cláusula UPDATE es la que indica que la operación a ejecutar es una actualización. Después de la cláusula se especifica el nombre de la tabla en donde se encuentra la información que deseamos modificar. Sólo se puede especificar una tabla a la vez. 36 Cláusula SET Esta cláusula permite especificar los campos que se desean modificar y su nuevo valor. La cláusula se coloca una sola vez aunque sean varios campos los que se deseen modificar. Cláusula WHERE Esta cláusula permite especificar un criterio para delimitar el conjunto de registros a modificar. Para mayor detalle de las condiciones consulte el contenido de la sección 2.7.2. 2.8 Vistas Una vista es una tabla qué no ocupa espacio de almacenamiento para la información que contiene, porque su estructura e información está definida a través de la ejecución de una instrucción SELECT. Las vistas tiene dos usos: el primero es parasimplificar el acceso a datos que se ocupan frecuentemente y que requieren una sentencia de SQL muy compleja para dicho acceso; y el segundo es con fines de seguridad, que permitan mantener ocultas ciertas columnas. Aunque las vistas forman parte del estándar, algunos RDBMS como MySQL no las implementan actualmente. Sintaxis CREATE VIEW <nombre_vista> AS <instrucción SELECT> Ejemplo CREATE VIEW empleados_h AS SELECT * FROM empleados WHERE sexo = ‘H’ Tabla 2-28 Sintaxis creación de vistas Sintaxis DROP VIEW <nombre_vista> Ejemplo DROP VIEW empleados_h Tabla 2-29 Sintaxis eliminación de vistas Las vistas pueden ser utilizadas como si fueran tablas, pero con ciertas restricciones Algunas consideraciones que se deben tomar en cuenta al implementar vistas son: La instrucción SELECT que define una vista puede: • Incluir criterios de selección (cláusula WHERE) • Usar funciones de agrupamiento (COUNT, AVG, SUM,…) • Usar columnas calculadas • Consultar más de una tabla • Consultar otra vista La instrucción SELECT que define una vista NO puede utilizar la cláusula ORDER BY. Sin embargo, la cláusula ORDER BY se puede aplicar a la vista una vez creada, siendo consultada como cualquier tabla. 37 2.9 Transacciones Los RDBMS deben de implementar un mecanismo a través del cual, los cambios a realizar en la información, a través de una instrucción INSERT, DELETE o UPDATE, no son efectuados hasta que el usuario lo indique explícitamente. Una transacción puede comprender una o más instrucciones SQL. MySQL PostgreSQL Oracle Sybase Ms SQL Server Soporta transacciones Si Si Si Si Si Tabla 2-30 Los RDBMS y el soporte para transacciones Una transacción es atómica, porque todas las instrucciones de SQL deben completarse con éxito, o ninguna de ellas. Una vez que el RDBMS determina que la transacción fue exitosa, es necesario almacenar la información de manera permanente. Una Base de Datos transaccional garantiza que todas las operaciones realizadas en una transacción sean guardadas en almacenamiento permanente antes de que ésta sea reportada como completada, previniendo así, perdida de información por fallas del equipo, por ejemplo en un corte del suministro de energía. Cuando múltiples usuarios realizan transacciones de manera concurrente, cada uno de ellos no debe ver los cambios incompletos realizados por los demás. En el momento que transacción finaliza adecuadamente y es almacenada permanentemente, los cambios se vuelven visibles para todos los demás usuarios. 2.9.1 Commit y Rollback El comando commit permite indicar que los cambios a realizar dentro de una transacción sean llevados a cabo de manera permanente, y el comando rollback permite deshacer los cambios. El RDBMS reserva un espacio de almacenamiento, donde registra todas las instrucciones de SQL que se deben de ejecutar. De esta manera es posible deshacer los cambios realizados por operaciones UPDATE, DELETE o INSERT. Las transacciones se inician de distinta manera, entre los manejadores. Por ejemplo en PostgreSQL, una transacción es iniciada mediante la cláusula BEGIN y en Sybase se emplea BEGIN TRANSACTION, seguida de las instrucciones de SQL a realizar y finalmente se emplea la cláusula COMMIT para indicar que las modificaciones deben realizarse de manera permanente o en caso que se desee cancelar la operación, se ocupa la cláusula ROLLBACK. Ejemplo BEGIN; UPDATE cuenta SET saldo = saldo - 1000.00 WHERE cliente = 'Fernanda'; UPDATE cuenta SET saldo = saldo + 1000.00 WHERE cliente = 'Alfredo'; COMMIT; Tabla 2-31 Uso de transacciones 38 En el ejemplo anterior, se muestra las operaciones necesarias para realizar una transacción monetaria de $1,000 pesos de la cuenta de Fernanda a la cuenta de Alfredo. Si por alguna razón alguna de las dos instrucciones fallara todo el proceso sería cancelado. La cláusula COMMIT, realiza los cambios de manera permanente. Si se diera el caso de requerir cancelar la operación, en lugar de emplear la cláusula COMMIT se debería emplear la cláusula ROLLBACK. 2.10 Procedimientos almacenados Un procedimiento almacenado es un conjunto de comandos de SQL que pueden ser compilados y almacenados en el servidor. Una vez realizado esto, los clientes no necesitan volver a teclear todas las instrucciones sino únicamente hacer referencia al procedimiento. Esto mejora el rendimiento del servidor, ya que la instrucción de SQL solamente es revisada una sola vez y menos información debe ser enviada entre el cliente y el servidor. Cada RDBMS tiene procedimientos almacenados predeterminados, para simplificar las tareas de administración de Base de Datos, la nomenclatura para identificarlos en la mayoría de los RBDMS es “sp_<nombre_de funcion>”, sp del acrónimo en inglés store procedure. MySQL PostgreSQL Oracle Sybase Ms SQL Server Soporta Procedimientos Almacenados No Si Si Si Si Tabla 2-32 Los RDBMS y el soporte para procedimientos almacenados El lenguaje que se emplea para programar los procedimientos almacenados, varía de un RDBMS a otro, y existen algunos que permiten programar en más de un lenguaje. Para crear procedimientos almacenados se utilizan las siguientes sentencias: RDBMS Sintaxis Ejemplo Oracle CREATE PROCEDURE <nombre_procedimiento> [(<nombre_parámetro> [IN | OUT] <tipo_de_dato>) ] AS BEGIN <instrucciones SQL> END CREATE PROCEDURE nombre_empleados AS BEGIN SELECT nombre FROM empleados ORDER BY nombre END Sybase y MS SQL Server CREATE PROCEDURE <nombre_procedimiento> [(@<nombre_parámetro> <tipo_de_dato>)] AS <instrucciones SQL> RETURN CREATE PROCEDURE nombres_empleados AS SELECT nombre FROM empleado ORDER BY nombre RETURN Tabla 2-33 Sintaxis creación de procedimientos almacenados 39 Para ejecutar los procedimientos almacenados la sintaxis es la siguiente: RDBMS Sintaxis Ejemplo Oracle CALL <nombre_procedimiento> [<parámetro>,…] CALL nombre_empleados Sybase y MS SQL Server EXEC <nombre_procedimiento> [<parámetro>, …] EXEC nombre_empleados Tabla 2-34 Sintaxis ejecución de procedimientos almacenados Para eliminar un procedimiento almacenado, se dispone de la instrucción DROP PROCEDURE. Sintaxis DROP PROCEDURE <nombre_procedimiento> Ejemplo DROP PROCEDURE nombre_empleados Tabla 2-35 Sintaxis eliminación de procedimientos almacenados El uso de parámetros incrementa la flexibilidad de un procedimiento almacenado. Estos se definen desde la creación del procedimiento y deben ser proporcionados por el usuario al momento de ejecutarlo. RDBMS Creación Ejecución Oracle CREATE PROCEDURE sueldo_empleado (sueldo IN number(8,2)) AS BEGIN SELECT nombre, sueldo FROM empleado WHERE sueldo <= sueldo ORDER BY nombre; END; call sueldo_empleado 10000 Sybase y MS SQL Server CREATE PROCEDURE sueldo_empleado @sueldo numeric(8,2) AS SELECT nombre, sueldo FROM empleado WHERE sueldo <= @sueldo ORDER BY nombre RETURN exec sueldo_empleado 10000 Tabla 2-36 El uso de parámetros en los procedimientos almacenados 40 Administración de Bases de Datos Los procedimientos de administración de las Base de Datos dependen del fabricante y la versión del RDBMS por tal motivo en este capítulo solo me enfocaré al manejador de Base de Datos SYBASE Adaptive Server Enterprise 15.0 (en adelante ASE). Con los temas de este capítulo trato de describir las funciones básicas del Administrador de Base de Datos las cuales incluyen: • Instalar, configurar y actualizar el RDBMS • Decidir la estructura de almacenamiento: Monitorea constantemente el crecimiento de la Base de Datos y administra los medios de almacenamiento de la Base de Datos • Brindar servicio tanto a los usuarios finales como a los programadores
Compartir