DISEÑO DE BASES DE DATOS
1. CONCEPTOS
- Base de Datos.- Cualquier conjunto de datos organizados para su respectivo almacenamiento en la memoria de un ordenador o computador, diseñado para facilitar su mantenimiento y acceso de forma estándar. Los datos suelen aparecer en forma de texto, números o gráficos. Hay cuatro modelos principales de bases de datos: el modelo jerárquico, el modelo en
Red, el modelo relacional (el más extendido hoy en día).
- Base de Datos Relacional.- Tipo de base de datos o sistema de
administración de bases de datos, que almacena información estrictamente en tablas de valores (filas y columnas de datos) y realiza búsquedas utilizando los datos de columnas especificadas de una tabla para encontrar datos adicionales en otra tabla, en donde todas las operaciones de la base de datos operan sobre estas mismas tablas.
Estas bases de datos, son percibidas por los usuarios como una colección de relaciones normalizadas de diversos grados que varían con el tiempo.
2. MODELO DE DATOS
Es un conjunto de herramientas conceptuales para describir los datos, las relaciones entre ellos, su semántica y sus limitantes.
Clases de Modelos de Datos:
Modelos Lógicos Basados en Objetos.- Son aquellos que nos permiten una definición clara y concisa de los esquemas conceptuales y de visión. Su característica principal es que permiten definir en forma detallada las limitantes de los datos.
Modelos Lógicos Basados en Registros.- Operan sobre niveles conceptuales y de visión. Se caracterizan principalmente porque permiten una descripción más
amplia de la implantación, pero no son capaces de especificar con claridad las limitantes de los datos.
Modelos Físicos de Datos.- Describen los datos en el nivel más bajo y permiten identificar algunos detalles de implantación para el manejo del hardware de almacenamiento.
3. SISTEMAS DE GESTIÓN DE BASES DE DATOS
Arquitectura de Red.- La infraestructurade comunicación por medio de redes. La red más sencilla es una conexión directa entre dos computadoras. Usando la terminología de redes, una relación es llamada conjunto, y cada conjunto está compuesto por al menos dos tipos de registros:
- Un registro propietario, que es el equivalente al padre en el modelo jerárquico.
- Un registro miembro, equivalente a un registro hijo en el modelo jerárquico.
La diferencia entre el modelo jerárquico y el de red es que este último incluye una condición en la cual un miembro puede aparecer en más de un conjunto. Es decir, un miembro puede tener muchos propietarios.
Lo que distingue a este modelo de los demás es que, en este todos los registros mantienen una interconexión entre si, y también pueden existir relaciones de muchos a muchos.
Arquitectura Jerárquica.- La estructura de base de datos jerárquica puede ser representada como un árbol hacia abajo. El usuario percibe la base de datos como una jerarquía de segmentos. Un segmento, también llamado nodo, es el equivalente al tipo registro de un sistema de archivos, es decir que es una colección de registros que se perciben organizados para conformar la estructura de un árbol. Jerárquicamente, el nivel superior (la raíz) es percibido como el
abajo de otros segmentos son hijos de los segmentos que se encuentran arriba de ellos. Una de sus características principales es que un segmento padre puede tener varios segmentos hijos. Y al mismo tiempo se dan problemas como la redundancia de información, que es superado por otros modelos.
Arquitectura Relacional.- El gran salto en la administración de bases de datos se produce con el modelo relacional, basado en la proposición de Edgar Codd, en 1970. La proposición del diagrama Entidad-Relación hizo que los diseñadores y programadores prefirieran la implantación de este nuevo modelo. Entendemos al diagrama entidad-relación como una propuesta de ver los datos como objetos del mundo real, diferenciables unos de otros por sus características. Un objeto o entidad es describible por la colección de características que tienen y, a la vez, es diferenciable de otros objetos por las mismas características o atributos.
Entonces, se tiene una serie de entidades que comparten elementos característicos comunes, o un conjunto de entidades. Estos conjuntos de entidades pueden ser ilimitados, pero los atributos que los diferencian son limitables. Actualmente este modelo es el más utilizado en la práctica, esto es debido a la facilidad de entendimiento y de manipulación por parte de diseñadores y administradores.
Para entender este modelo de base de datos hay que tener claros ciertos conceptos sencillos que son importantes a la hora de diseñar la base de datos y sus relaciones.
Entidad: es una persona, cosa o transacción de la cual se puede guardar información. Las entidades, en la fase de diseño (diagrama E-R), llegarán a convertirse posteriormente en las tablas de la base de datos.
Atributos: son las características que se desean almacenar de las entidades. Relación: una relación es una asociación entre dos entidades.
Conectividad: Se utiliza para clasificar el tipo de relación que une a las dos entidades (uno a uno, un a muchos ó muchos a muchos).
Especifica el número específico de ocurrencias de la entidad asociadas con una ocurrencia de la entidad relacionada.
4. ADMINISTRADOR DE LA BASE DE DATOS (DBA)
El administrador de datos (DA) es la persona identificable que tendrá la
responsabilidad central (de gestionar y controlar todas las actividades que tienen que ver con los datos de la empresa y con la base de datos). Ya que los datos son uno de los activos más valiosos de la empresa, es imperativo que exista una persona que los entienda junto con las necesidades de la empresa con respecto a esos datos, a un nivel de administración superior. Por lo tanto, es labor del administrador decidir en primer lugar qué datos deben ser almacenados en la base de datos y establecer políticas para mantener y manejar esos datos una vez almacenados.
El administrador de base de datos (DBA) es el técnico responsable de implementar las decisiones del administrador de datos. Por lo tanto, debe ser un profesional en IT. El trabajo del DBA consiste en crear la base de datos real e implementar los controles técnicos necesarios para hacer cumplir las diversas decisiones de las políticas hechas por el DA. El DBA también es responsable de asegurar que el sistema opere con el rendimiento adecuado y de proporcionar una variedad de otros servicios técnicos.
El administrador de datos juega un papel más importante que el administrador de la base de datos en las siguientes etapas del ciclo de vida: planificación de la base de datos, definición del sistema, recolección y análisis de los requisitos, diseño conceptual y diseño lógico de la base de datos. En el resto de las etapas es donde el administrador de la base de datos tiene el papel más importante: selección del SGBD, diseño de las aplicaciones, diseño físico, prototipo, implementación, conversión y carga de datos, prueba y mantenimiento.
5. DICCIONARIO DE DATOS
El diccionario de datos guarda y organiza los detalles del Diagrama de Flujo de Datos (DFD). Es el segundo componente del análisis estructurado. También se conoce como "Data Repository". Incluye el contenido de los data flow (flujos de datos), los "data store", las entidades externas y los procesos.
Data elements (elementos de datos).- Es la parte más pequeña de los datos que tiene significado en el sistema de información. Se combinan varios elementos de datos para hacer los registros o "data structures". Ejemplo: Nombre, Dirección, Dni.
Las características que se describen en el diccionario de datos son:
Name.- Es el nombre del elemento de datos; debe ser significativo.
Alias.- Cualquier otro nombre que se pueda usar para referirse al elemento de datos. Por ejemplo, el nombre de un elemento de datos puede ser Balance actual, y el alias puede ser Deuda. Solo se incluye el alias si realmente es necesario utilizarlo.
Type y Size.- se refiere a si el elemento de datos contiene valor numérico, caracteres o alfabético. Size o tamaño se refiere al máximo de caracteres o de dígitos que puede tener el elemento de datos.
Formato de salida o mascara de edición.- Indica cómo se presenta el dato al mostrarse en pantalla o al imprimirse en un reporte. Por ejemplo, el número de teléfono del cliente se puede guardar en el disco usando solo números 7878889999, pero presentarse editado en la pantalla o en el reporte (787) 888-9999.
Default value.- Es el valor que el elemento de datos tiene si no se cambia entrando otro valor.
Prompt, column header or field caption.- Es el nombre que se presenta en la pantalla o el título del dato en el reporte.
Source (fuente).- De dónde se origina el valor del elemento de datos. Puede ser una forma, un departamento, otro sistema, etc.
Security.- Identifica los individuos o departamentos que pueden modificar el elemento de datos. Por ejemplo, la línea de crédito puede ser cambiada por el gerente de crédito.
Responsible user(s).- Identifica el (los) usuarios responsables de entrar o cambiar los valores del elemento de datos.
Acceptable Data and Data validation.- Se especifica el dominio o valores permitidos. Pueden ser valores específicos, una lista de valores, los valores que
se encuentren en otro archivo, etc. El valor puede tener reglas de validación; por ejemplo, el salario debe estar entre lo permitido para la posición que el empleado ocupa.
Derivation formula.- Si el valor es el resultado de un cálculo, se muestra la fórmula que se utiliza.
Description or comments.- Para proveer información adicional, notas o descripciones.
Data Structure (Estructura de datos)
También se conocen como record. Es la combinación de elementos de datos relacionados que se incluye en un flujo de datos o se retiene en un "data store". Data flows (Flujo de datos) - Las características que se describen en el flujo de datos son:
Name – El nombre del flujo de datos tal y como aparece en el DFD. Alias – Otro nombre con que se conozca el flujo de datos.
Abbreviation or ID – Código que provee acceso rápido al flujo de datos en un diccionario de datos automatizado.
Description – Describe el flujo de datos y su propósito.
Origin – De donde sale (la fuente) el flujo de datos. Puede ser un proceso, un “data store” o una entidad.
Destination – El punto final del flujo de datos en el DFD. Puede ser un proceso, un “data store” o una entidad.
Record – Cada flujo de datos representa un grupo de elementos de datos relacionados, o un record. Los records y los flujos de datos se definen por separado para que más de un flujo de datos o “data store” pueda hacer referencia al mismo record.
Volume and frequency – Describe el número esperado de ocurrencias para el flujo de datos por unidad de tiempo.
“Data store” – Las características que se describen en el “data store” son: Name – El nombre del “data store” según aparece en el DFD.
Abbreviation or ID – Código que provee un acceso rápido al “data store” en un diccionario de datos automatizado.
Description – Describe el “data store” y su propósito.
Input data flows – Los nombres de los flujos de datos que entran al “data store”. Output data flows – Los nombres de los flujos de datos que salen del “data store”. Record – El nombre del record en el diccionario de datos para el “data store”. Volume and Frequency – El número estimado de records guardados en el “data store”, al igual que el aumento o cambio esperado.
Proceso – Se documenta cada función primitiva. Se incluye:
Process name or label – El nombre del proceso como aparece en el DFD.
Purpose or description – Un resumen del propósito general del proceso. Los detalles se documentan en el Process Description.
Process number – Número de referencia que identifica el proceso y su relación con los niveles del sistema.
Input data flows – Los nombres de los flujos de datos que entran al proceso. Output data flows – Los nombres de los flujos de datos que salen del proceso. Process Description – Se explican los detalles del proceso.
Entidades Externas – Las características que se describen son: Name
Alias
Description – Describe a la entidad y su propósito. Input data flow
Output data flow
Record (Registro) – Se describe lo siguiente: Record name
Alias
Description
Record content or composition – Una lista de todos los elementos de datos incluidos en el record. Se identifica cualquier “LLAVE” primaria, o sea un elemento de datos en el record que identifica en forma única al record.
DISEÑO LOGICO DE BASE DE DATOS
El objetivo del diseño lógico es convertir los esquemas conceptuales locales en un esquema lógico global que se ajuste al modelo de SGBD sobre el que se va a implementar el sistema. Mientras que el objetivo fundamental del diseño conceptual es la compleción y expresividad de los esquemas conceptuales locales, el objetivo del diseño lógico es obtener una representación que use de modo más eficiente posible, los recursos que el modelo de SGBD posee para estructurar los datos y para modelar las restricciones.
Los modelos de bases de datos más extendidos son el modelo relacional, el modelo de red y el modelo jerárquico. El modelo orientado a objetos es también muy popular, pero no existe un modelo estándar orientado a objetos.
En un primer paso en la fase del diseño lógico consistirá en la conversión de esos mecanismos de representación de alto nivel en términos de las estructuras de bajo nivel disponibles en el modelo relacional.
Metodología de diseño lógico en el modelo relacional
La metodología que se va a seguir para el diseño lógico en el modelo relacional consta de dos fases, cada una de ellas compuesta por varios pasos que se detallan a continuación.
1. Convertir los esquemas conceptuales locales en esquemas lógicos locales
En este paso, se eliminan de cada esquema conceptual las estructuras de datos que los sistemas relacionales no modelan directamente:
(a) Eliminar las relaciones de muchos a muchos, sustituyendo cada una de ellas por una nueva entidad intermedia y dos relaciones de uno a muchos de esta nueva entidad con las entidades originales. La nueva entidad será débil, ya que sus ocurrencias dependen de la existencia de ocurrencias en las entidades originales.
las entidades originales. La cardinalidad de estas nuevas relaciones binarias dependerá de su significado.
(c) Eliminar las relaciones recursivas, sustituyendo cada una de ellas por una nueva entidad (débil) y dos relaciones binarias de esta nueva entidad con la entidad original. La cardinalidad de estas relaciones dependerá de su significado.
(d)Eliminar las relaciones con atributos, sustituyendo cada una de ellas por una nueva entidad (débil) y las relaciones binarias correspondientes de esta nueva entidad con las entidades originales. La cardinalidad de estas relaciones dependerá del tipo de la relación original y de su significado.
(e)Eliminar los atributos multievaluados, sustituyendo cada uno de ellos por una nueva entidad (débil) y una relación binaria de uno a muchos con la entidad original.
(f) Revisar las relaciones de uno a uno, ya que es posible que se hayan identificado dos entidades que representen el mismo objeto (sinónimos). Si así fuera, ambas entidades deben integrarse en una sola.
(g)Eliminar las relaciones redundantes. Una relación es redundante cuando se puede obtener la misma información que ella aporta mediante otras relaciones. El hecho de que haya dos caminos diferentes entre dos entidades no implica que uno de los caminos corresponda a una relación redundante, eso dependerá del significado de cada relación.
Una vez finalizado este paso, es más correcto referirse a los esquemas conceptuales locales refinados como esquemas lógicos locales, ya que se adaptan al modelo de base de datos que soporta el SGBD escogido.
2. Derivar un conjunto de relaciones (tablas) para cada esquema lógico local
En este paso, se obtiene un conjunto de relaciones (tablas) para cada uno de los esquemas lógicos locales en donde se representen las entidades y relaciones entre entidades, que se describen en cada una de las vistas que los usuarios tienen de la empresa. Cada relación de la base de datos tendrá un nombre, y el
nombre de sus atributos aparecerá, a continuación, entre paréntesis. El atributo o atributos que forman la clave primaria se subrayan. Las claves ajenas, mecanismo que se utiliza para representar las relaciones entre entidades en el modelo relacional, se especifican aparte indicando la relación (tabla) a la que hacen referencia.
A continuación, se describe cómo las relaciones (tablas) del modelo relacional representan las entidades y relaciones que pueden aparecer en los esquemas lógicos.
(a) Entidades fuertes. Crear una relación para cada entidad fuerte que incluya todos sus atributos simples. De los atributos compuestos incluir sólo sus componentes.
Cada uno de los identificadores de la entidad será una clave candidata. De entre las claves candidatas hay que escoger la clave primaria; el resto serán claves alternativas. Para escoger la clave primaria entre las claves candidatas se pueden seguir estas indicaciones:
- Escoger la clave candidata que tenga menos atributos.
- Escoger la clave candidata cuyos valores no tengan probabilidad de cambiar en el futuro.
- Escoger la clave candidata cuyos valores no tengan probabilidad de perder la unicidad en el futuro.
- Escoger la clave candidata con el mínimo número de caracteres (si es de tipo texto).
- Escoger la clave candidata más fácil de utilizar desde el punto de vista de los usuarios.
(b) Entidades débiles. Crear una relación para cada entidad débil incluyendo todos sus atributos simples. De los atributos compuestos incluir sólo sus componentes. Añadir una clave ajena a la entidad de la que depende. Para ello, se incluye la clave primaria de la relación que representa a la entidad padre en la nueva relación creada para la entidad débil. A continuación, determinar la clave
(c) Relaciones binarias de uno a uno. Para cada relación binaria se incluyen los atributos de la clave primaria de la entidad padre en la relación (tabla) que representa a la entidad hijo, para actuar como una clave ajena. La entidad hijo es la que participa de forma total (obligatoria) en la relación, mientras que la entidad padre es la que participa de forma parcial (opcional). Si las dos entidades participan de forma total o parcial en la relación, la elección de padre e hijo es arbitraria. Además, en caso de que ambas entidades participen de forma total en la relación, se tiene la opción de integrar las dos entidades en una sola relación (tabla). Esto se suele hacer si una de las entidades no participa en ninguna otra relación.
(d) Relaciones binarias de uno a muchos. Como en las relaciones de uno a uno, se incluyen los atributos de la clave primaria de la entidad padre en la relación (tabla) que representa a la entidad hijo, para actuar como una clave ajena. Pero ahora, la entidad padre es la de "la parte del muchos" (cada padre tiene muchos hijos), mientras que la entidad hijo es la de "la parte del uno" (cada hijo tiene un solo padre).
(e) Jerarquías de generalización. En las jerarquías, se denomina entidad padre a la entidad genérica y entidades hijo a las sub-entidades. Hay tres opciones distintas para representar las jerarquías. La elección de la más adecuada se hará en función de su tipo (total/parcial, exclusiva/superpuesta).q
- Crear una relación por cada entidad. Las relaciones de las entidades hijo heredan como clave primaria la de la entidad padre. Por lo tanto, la clave primaria de las entidades hijo es también una clave ajena al padre. Esta opción sirve para cualquier tipo de jerarquía, total o parcial y exclusiva o superpuesta.
- Crear una relación por cada entidad hijo, heredando los atributos de la entidad padre. Esta opción sólo sirve para jerarquías totales y exclusivas. - Integrar todas las entidades en una relación, incluyendo en ella los atributos
de la entidad padre, los atributos de todos los hijos y un atributo discriminativo para indicar el caso al cual pertenece la entidad en consideración. Esta opción sirve para cualquier tipo de jerarquía. Si la jerarquía es superpuesta, el atributo discriminativo será multievaluado1.
Una vez obtenidas las relaciones con sus atributos, claves primarias y claves ajenas, sólo queda actualizar el diccionario de datos con los nuevos atributos que se hayan identificado en este paso.
3. Validar cada esquema mediante la normalización
La normalización se utiliza para mejorar el esquema lógico, de modo que satisfaga ciertas restricciones que eviten la duplicidad de datos. La normalización garantiza que el esquema resultante se encuentra más próximo al modelo de la empresa, que es consistente y que tiene la mínima redundancia y la máxima estabilidad.
La normalización es un proceso que permite decidir a qué entidad pertenece cada atributo. Uno de los conceptos básicos del modelo relacional es que los atributos se agrupan en relaciones (tablas) porque están relacionados a nivel lógico. En la mayoría de las ocasiones, una base de datos normalizada no proporciona la máxima eficiencia, sin embargo, el objetivo ahora es conseguir una base de datos normalizada por las siguientes razones:
- Un esquema normalizado organiza los datos de acuerdo a sus dependencias funcionales, es decir, de acuerdo a sus relaciones lógicas.
- El esquema lógico no tiene porqué ser el esquema final. Debe representar lo que el diseñador entiende sobre la naturaleza y el significado de los datos de la empresa. Si se establecen unos objetivos en cuanto a prestaciones, el diseño físico cambiará el esquema lógico de modo adecuado. Una posibilidad es que algunas relaciones normalizadas se normalicen. Pero la des-normalización no implica que se haya malgastado tiempo normalizando, ya que mediante este proceso el diseñador aprende más sobre el significado de los datos. De hecho, la normalización obliga a entender completamente cada uno de los atributos que se han de representar en la base de datos.
- Un esquema normalizado es robusto y carece de redundancias, por lo que está libre de ciertas anomalías que éstas pueden provocar cuando se actualiza la base de datos.
- Los equipos informáticos de hoy en día son mucho más potentes, por lo que en ocasiones es más razonable implementar bases de datos fáciles de manejar (las normalizadas), a costa de un tiempo adicional de proceso.
- La normalización produce bases de datos con esquemas flexibles que pueden extenderse con facilidad.
El objetivo de este paso es obtener un conjunto de relaciones que se encuentren en la forma normal de Boyce-Codd. Para ello, hay que pasar por la primera, segunda y tercera formas normales.
4. Validar cada esquema frente a las transacciones del usuario
El objetivo de este paso es validar cada esquema lógico local para garantizar que puede soportar las transacciones requeridas por los correspondientes usuarios. Estas transacciones se encontrarán en las especificaciones de requisitos de usuario. Lo que se debe hacer es tratar de realizar las transacciones de forma manual utilizando el diagrama entidad-relación, el diccionario de datos y las conexiones que establecen las claves ajenas de las relaciones (tablas). Si todas las transacciones se pueden realizar, el esquema queda validado. Pero si alguna transacción no se puede realizar, seguramente será porque alguna entidad, relación o atributo no se ha incluido en el esquema.
5. Dibujar el diagrama entidad-relación
En este momento, se puede dibujar el diagrama entidad-relación final para cada vista de usuario que recoja la representación lógica de los datos desde su punto de vista. Este diagrama habrá sido validado mediante la normalización y frente a las transacciones de los usuarios.
6. Definir las restricciones de integridad
Las restricciones de integridad son reglas que se quieren imponer para proteger la base de datos, de modo que no pueda llegar a un estado inconsistente. Hay cinco tipos de restricciones de integridad.
(a) Datos requeridos. Algunos atributos deben contener valores en todo momento, es decir, no admiten nulos.
(b) Restricciones de dominios. Todos los atributos tienen un dominio asociado, que es el conjunto los valores que cada atributo puede tomar.
(c) Integridad de entidades. El identificador de una entidad no puede ser nulo, por lo tanto, las claves primarias de las relaciones (tablas) no admiten nulos.
(d)Integridad referencial. Una clave ajena enlaza cada tupla de la relación hijo con la tupla de la relación padre que tiene el mismo valor en su clave primaria. La integridad referencial dice que si una clave ajena tiene un valor (si es no nula), ese valor debe ser uno de los valores de la clave primaria a la que referencia. Hay varios aspectos a tener en cuenta sobre las claves ajenas para lograr que se cumpla la integridad referencial.
- ¿Admite nulos la clave ajena? Cada clave ajena expresa una relación. Si la participación de la entidad hijo en la relación es total, entonces la clave ajena no admite nulos; si es parcial, la clave ajena debe aceptar nulos.
- ¿Qué hacer cuando se quiere borrar una ocurrencia de la entidad padre que tiene algún hijo? O lo que es lo mismo, ¿qué hacer cuando se quiere borrar una tupla que está siendo referenciada por otra tupla a través de una clave ajena? Hay varias respuestas posibles:
o Restringir: no se pueden borrar tuplas que están siendo referenciadas por otras tuplas.
o Propagar: se borra la tupla deseada y se propaga el borrado a todas las tuplas que le hacen referencia.
o Anular: se borra la tupla deseada y todas las referencias que tenía se ponen, automáticamente, a nulo (esta respuesta sólo es válida si la clave ajena acepta nulos).
o Valor por defecto: se borra la tupla deseada y todas las referencias toman, automáticamente, el valor por defecto (esta respuesta sólo es válida si se ha especificado un valor por defecto para la clave ajena).
o No comprobar: se borra la tupla deseada y no se hace nada para garantizar que se sigue cumpliendo la integridad referencial.
- ¿Qué hacer cuando se quiere modificar la clave primaria de una tupla que está siendo referenciada por otra tupla a través de una clave ajena? Las respuestas posibles son las mismas que en el caso anterior. Cuando se escoge propagar, se actualiza la clave primaria en la tupla deseada y se propaga el cambio a los valores de clave ajena que le hacían referencia.
(e) Reglas de negocio. Cualquier operación que se realice sobre los datos debe cumplir las restricciones que impone el funcionamiento de la empresa.
Todas las restricciones de integridad establecidas en este paso se deben reflejar en el diccionario de datos para que puedan ser tenidas en cuenta durante la fase del diseño físico.
7. Revisar cada esquema lógico local con el usuario correspondiente
Para garantizar que cada esquema lógico local es una fiel representación de la vista del usuario lo que se debe hacer es comprobar con él que lo reflejado en el esquema y en la documentación es correcto y está completo.
Relación entre el esquema lógico y los diagramas de flujo de datos
El esquema lógico refleja la estructura de los datos a almacenar que maneja la empresa. Un diagrama de flujo de datos muestra cómo se mueven los datos en la empresa y los almacenes en donde se guardan. Si se han utilizado diagramas de flujo de datos para modelar las especificaciones de requisitos de usuario, se pueden utilizar para comprobar la consistencia y completitud del esquema lógico desarrollado. Para ello:
- Cada almacén de datos debe corresponder con una o varias entidades completas.
- Los atributos en los flujos de datos deben corresponder a alguna entidad.
Los esquemas lógicos locales obtenidos hasta este momento se integrarán en un solo esquema lógico global en la siguiente fase para modelar los datos de toda la empresa.
8. Mezclar los esquemas lógicos locales en un esquema lógico global
En este paso, se deben integrar todos los esquemas locales en un solo esquema global. En un sistema pequeño, con dos o tres vistas de usuario y unas pocas entidades y relaciones, es relativamente sencillo comparar los esquemas locales, mezclarlos y resolver cualquier tipo de diferencia que pueda existir. Pero en los
sistemas grandes, se debe seguir un proceso más sistemático para llevar a cabo este paso con éxito:
- Revisar los nombres de las entidades y sus claves primarias. - Revisar los nombres de las relaciones.
- Mezclar las entidades de las vistas locales.
- Incluir (sin mezclar) las entidades que pertenecen a una sola vista de usuario.
- Mezclar las relaciones de las vistas locales.
- Incluir (sin mezclar) las relaciones que pertenecen a una sola vista de usuario.
- Comprobar que no se ha omitido ninguna entidad ni relación. - Comprobar las claves ajenas.
- Comprobar las restricciones de integridad. - Dibujar el esquema lógico global.
- Actualizar la documentación.
9. Validar el esquema lógico global
Este proceso de validación se realiza, de nuevo, mediante la normalización y mediante la prueba frente a las transacciones de los usuarios. Pero ahora sólo hay que normalizar las relaciones que hayan cambiado al mezclar los esquemas lógicos locales y sólo hay que probar las transacciones que requieran acceso a áreas que hayan sufrido algún cambio.
10.Estudiar el crecimiento futuro
En este paso, se trata de comprobar que el esquema obtenido puede acomodar los futuros cambios en los requisitos con un impacto mínimo. Si el esquema lógico se puede extender fácilmente, cualquiera de los cambios previstos se podrá incorporar al mismo con un efecto mínimo sobre los usuarios existentes.
11.Dibujar el diagrama entidad-relación final
Una vez validado el esquema lógico global, ya se puede dibujar el diagrama entidad-relación que representa el modelo de los datos de la empresa que son de
interés. La documentación que describe este modelo (incluyendo el esquema relacional y el diccionario de datos) se debe actualizar y completar.
12.Revisar el esquema lógico global con los usuarios
Una vez más, se debe revisar con los usuarios el esquema global y la documentación obtenida para asegurarse de que son una fiel representación de la empresa.
NORMALIZACION
La normalización es una técnica para diseñar la estructura lógica de los datos de un sistema de información en el modelo relacional, desarrollada por E. F. Codd en 1972. Es una estrategia de diseño de abajo a arriba: se parte de los atributos y éstos se van agrupando en relaciones (tablas) según su afinidad. Aquí no se utilizará la normalización como una técnica de diseño de bases de datos, sino como una etapa posterior a la correspondencia entre el esquema conceptual y el esquema lógico, que elimine las dependencias entre atributos no deseadas. Las ventajas de la normalización son las siguientes:
- Evita anomalías en inserciones, modificaciones y borrados. - Mejora la independencia de datos.
- No establece restricciones artificiales en la estructura de los datos.
Uno de los conceptos fundamentales en la normalización es el de dependencia funcional. Una dependencia funcional es una relación entre atributos de una misma relación (tabla).
La dependencia funcional es una noción semántica. Si hay o no dependencias funcionales entre atributos no lo determina una serie abstracta de reglas, sino, más bien, los modelos mentales del usuario y las reglas de negocio de la organización o empresa para la que se desarrolla el sistema de información. Cada dependencia funcional es una clase especial de regla de integridad y representa una relación de uno a muchos.
En el proceso de normalización se debe ir comprobando que cada relación (tabla) cumple una serie de reglas que se basan en la clave primaria y las dependencias funcionales. Cada regla que se cumple aumenta el grado de normalización. Si una regla no se cumple, la relación se debe descomponer en varias relaciones que sí la cumplan.
La normalización se lleva a cabo en una serie pasos. Cada paso corresponde a una forma normal que tiene unas propiedades. Conforme se va avanzando en la normalización, las relaciones tienen un formato más estricto (más fuerte) y, por lo tanto, son menos vulnerables a las anomalías de actualización. El modelo relacional
formas normales son opcionales. Sin embargo, para evitar las anomalías de actualización, es recomendable llegar al menos a la tercera forma normal.
Primera forma normal (1FN)
Una relación está en primera forma normal si, y sólo si, todos los dominios de la misma contienen valores atómicos, es decir, no hay grupos repetitivos. Si se ve la relación gráficamente como una tabla, estará en 1FN si tiene un solo valor en la intersección de cada fila con cada columna.
Si una relación no está en 1FN, hay que eliminar de ella los grupos repetitivos. Un grupo repetitivo será el atributo o grupo de atributos que tiene múltiples valores para cada tupla de la relación. Hay dos formas de eliminar los grupos repetitivos. En la primera, se repiten los atributos con un solo valor para cada valor del grupo repetitivo. De este modo, se introducen redundancias ya que se duplican valores, pero estas redundancias se eliminarán después mediante las restantes formas normales. La segunda forma de eliminar los grupos repetitivos consiste en poner cada uno de ellos en una relación aparte, heredando la clave primaria de la relación en la que se encontraban.
Un conjunto de relaciones se encuentra en 1FN si ninguna de ellas tiene grupos repetitivos.
Segunda forma normal (2FN)
Una relación está en segunda forma normal si, y sólo si, está en 1FN y, además, cada atributo que no está en la clave primaria es completamente dependiente de la clave primaria.
La 2FN se aplica a las relaciones que tienen claves primarias compuestas por dos o más atributos. Si una relación está en 1FN y su clave primaria es simple (tiene un solo atributo), entonces también está en 2FN. Las relaciones que no están en 2FN pueden sufrir anomalías cuando se realizan actualizaciones.
Para pasar una relación en 1FN a 2FN hay que eliminar las dependencias parciales de la clave primaria. Para ello, se eliminan los atributos que son funcionalmente dependientes y se ponen en una nueva relación con una copia de su determinante (los atributos de la clave primaria de los que dependen).
Tercera forma normal (3FN)
Una relación está en tercera forma normal si, y sólo si, está en 2FN y, además, cada atributo que no está en la clave primaria no depende transitivamente de la clave primaria. La dependencia es transitiva si existen las dependencias , , siendo , , atributos o conjuntos de atributos de una misma relación.
Aunque las relaciones en 2FN tienen menos redundancias que las relaciones en 1FN, todavía pueden sufrir anomalías frente a las actualizaciones. Para pasar una relación de 2FN a 3FN hay que eliminar las dependencias transitivas. Para ello, se eliminan los atributos que dependen transitivamente y se ponen en una nueva relación con una copia de su determinante (el atributo o atributos no clave de los que dependen).
Forma normal de Boyce-Codd (BCFN)
Una relación está en la forma normal de Boyce-Codd si, y sólo si, todo determinante es una clave candidata.
La 2FN y la 3FN eliminan las dependencias parciales y las dependencias transitivas de la clave primaria. Pero este tipo de dependencias todavía pueden existir sobre otras claves candidatas, si éstas existen. La BCFN es más fuerte que la 3FN, por lo tanto, toda relación en BCFN está en 3FN.
La violación de la BCFN es poco frecuente ya que se da bajo ciertas condiciones que raramente se presentan. Se debe comprobar si una relación viola la BCFN si tiene dos o más claves candidatas compuestas que tienen al menos un atributo en común.
Resumen
El diseño de bases de datos consta de tres etapas: diseño conceptual, lógico y físico. El diseño lógico es el proceso mediante el que se construye un esquema que representa la información que maneja una empresa, basándose en un modelo lógico determinado, pero independientemente del SGBD concreto que se vaya a utilizar para implementar la base de datos e independientemente de cualquier otra consideración física.
Las dos fases de que consta el diseño lógico son la construcción y validación de los esquemas lógicos locales para cada vista de usuario, y la construcción y validación de un esquema lógico global. Cada una de estas fases consta de una serie de pasos.
Un paso importante es la conversión del esquema conceptual a un esquema lógico adecuado al modelo relacional. Para ello, se deben hacer algunas transformaciones: eliminar las relaciones de muchos a muchos, eliminar las relaciones complejas, eliminar las relaciones recursivas, eliminar las relaciones con atributos, eliminar los atributos multievaluados, reconsiderar las relaciones de uno a uno y eliminar las relaciones redundantes.
Los esquemas lógicos se pueden validar mediante la normalización y frente a las transacciones de los usuarios. La normalización se utiliza para mejorar el esquema, de modo que éste satisface ciertas restricciones que evitan la duplicidad de datos. La normalización garantiza que el esquema resultante está más próximo al modelo de la empresa, es consistente, tiene la mínima redundancia y la máxima estabilidad.
Las restricciones de integridad son las restricciones que se imponen para que la base de datos nunca llegue a un estado inconsistente. Hay cinco tipos de restricciones de integridad: datos requeridos, restricciones de dominio, integridad de entidades, integridad referencial y reglas de negocio.
Para garantizar la integridad referencial se debe especificar el comportamiento de las claves ajenas: si aceptan nulos y qué hacer cuando se borra la tupla a la que se hace referencia, o cuando se modifica el valor de su clave primaria.
6. SISTEMA DE GESTION DE BASE DE DATOS
Para plasmar los niveles en el enfoque o modelo de datos seleccionado, es necesario disponer de una aplicación que actúe de interfaz entre el usuario, los modelos y el sistema físico. Esta es la función que desempeñan los SGBD (Sistemas de Gestión de Base de Datos), y que pueden definirse como un paquete generalizado de software, que se ejecuta en un sistema computacional anfitrión, centralizando los accesos a los datos y actuando de interfaz entre los datos físicos y el usuario. Las principales funciones que debe cumplir un SGBD
se relacionan con la creación y mantenimiento de la base de datos, el control de accesos, manipulación de datos de acuerdo a las necesidades del usuario, cumplimiento de las normas de tratamiento de datos, evitar redundancias e inconsistencias y mantener la integridad. Se han señalado como componentes básicos los siguientes:
1. Un lenguaje de definición de esquema conceptual. 2. Un sistema de diccionario de datos.
3. Un lenguaje de especificación de paquetes de entrada/salida. 4. Un lenguaje de definición de esquemas de base de datos. 5. Una estructura simétrica de almacenamiento de datos. 6. Un módulo de transformación lógica a física.
7. Un subsistema de privacidad de propósito general. 8. Un subsistema de integridad de propósito general
9. Un subsistema de reserva y recuperación de propósito general. 10. Un generador de programas de aplicación.
11. Un generador de programas de informes. 12. Un lenguaje de consulta de propósito general.
El SGBD incorpora como herramienta fundamental 2 lenguajes:
1. El lenguaje de definición de datos (DDL, Data Definition Language) provee de medios necesarios para definir los datos con precisión, especificando las distintas estructuras. De acuerdo con el modelo de arquitectura de tres niveles, habrá un lenguaje de definición de la estructura lógica global, otro para la definición de la estructura interna, y un tercero para la definición de las estructuras externas.
2. El lenguaje de manipulación de datos (DML, Data Manipulation/ Management Language), es el encargado de facilitar a los usuarios el acceso y manipulación de los datos. Pueden diferenciarse en procedimentales (aquellos que requieren qué datos se necesitan y cómo obtenerlos) y no procedimentales (que datos se necesitan, sin especificar como obtenerlos), y
se encargan de recuperar los datos almacenados, de la inserción y supresión de datos en la base de datos, y de la modificación de los existentes.
Fig.1. Arquitectura de un Sistema de Bases de Datos.
7. VENTAJAS DE LAS BASES DE DATOS
Todas las ventajas que se obtienen a causa de la implantación de una base de datos son enormemente importantes para una empresa u organización y vienen a
contribuir en el desarrollo y la funcionalidad de estas mismas.
A continuación se mencionan las más importantes y sobresalientes:
• Se puede compartir toda la información necesaria.
• La información puede ser globalizada dentro de una empresa. • Se mantiene la integridad en la información, es decir, que elimina la
información redundante y la información inconsistente.
• Existe independencia de datos (ventaja mas notable), no es necesario cambiar programas o aplicaciones a la hora de cambiar datos.
OPERACIÓN DE LAS SENTENCIAS DE SQL
A continuación se indican las principales operaciones que se implementan a través de sentencias SQL.
a. Creación de tablas
La sentencia CREATE TABLE permite definir una nueva tabla y prepararla para aceptar datos. Su sintaxis es la siguiente:
CREATE TABLE nombre de tabla <definición de columnas>
Las columnas de la tabla recién creada se definen en el cuerpo de la sentencia CREATE TABLE. Las definiciones de columnas aparecen en una lista separada por comas y encerrada entre paréntesis. El orden de las definiciones de las columnas determina el orden de izquierda a derecha de las columnas en la tabla. Cada definición especifica el nombre de la columna y el tipo de datos que la columna almacena.
b. Inserción de datos en una tabla
Crea una nueva fila de datos se añade a una base de datos relacional cuando una nueva entidad representada por una fila aparece en el mundo exterior. Una fila es la unidad de datos más pequeña que puede añadirse a una tabla.
La sentencia INSERT permite añadir una nueva fila a una tabla. Su sintaxis es:
INSERT INTO nombre tabla <Lista de columnas> VALUES >Lista de valores> La cláusula INTO especifica la tabla que recibirá la nueva fila y las columnas que almacenarán los datos Insertados. La cláusula VALUES especifica los valores de los datos que la nueva fila contendrá. Los nombres de columna y los valores de los datos se separan utilizando comas.
Los valores de los datos almacenados en una base de datos se modifican cuando se producen cambios correspondientes, de tal forma que en todo momento la información almacenada en la base de datos sea un modelo exacto del mundo real. La unidad más pequeña que puede modificarse es una columna de una fila.
La sentencia UPDATE permite modificar los valores de una o más columnas de las filas seleccionadas de una tabla. Debe respetarse la siguiente sintaxis:
UPDATE nombre de tabla SET nombre de columna =expresión WHERE condición
El nombre de tabla identifica la tabla en que se realizará la actualización. La condición que acompaña a la cláusula WHERE sirve para seleccionar las filas de la tabla que serán modificadas. La cláusula SET es una lista de asignaciones separadas por comas. Cada asignación identifica una columna destino a actualizar y especifica cómo calcular el nuevo valor para dicha columna.
d. Eliminación de registros y datos
Normalmente, una fila de datos se suprime de una tabla cuando la entidad representada por la fila desaparece del mundo exterior. La unidad más pequeña de datos que puede ser suprimida es una única fila.
La sentencia DELETE permite eliminar filas seleccionadas de una sola tabla. La sintaxis de la sentencia DELETE es como sigue:
DELETE FROM nombre de tabla WHERE condición
La cláusula FROM especifica el nombre de la tabla de la que se eliminarán filas. La condición de la cláusula WHERE selecciona las filas que serán suprimidas. Obviamente si ninguna de las filas satisface dicha condición, ninguna de ellas será eliminada.
Las consultas de selección se utilizan para obtener información de las bases de datos, esta información es devuelta en forma de conjunto de registros.
La sintaxis básica de una consulta de selección es la siguiente:
SELECT Lista de campos FROM Nombre de tabla WHERE Condicion
En donde Lista de Campos es la relación de campos que se desean recuperar y Nombre de Tabla identifica a la tabla, origen de los mismos.
El resultado de una consulta SQL es siempre una tabla de datos, semejante a las tablas almacenadas en la base de datos. Generalmente los resultados de la consulta generarán una tabla con varías columnas y varias filas. Las consultas más sencillas solicitan columnas de datos de una única tabla en la base de datos.
f. Eliminación de tablas
Cuando se quiere destruir una tabla existente que ya no es necesaria se debe utilizar la sentencia DROP TABLE cuya sintaxis es:
DROPTABLE nombre de tabla
TIPOS DE DATOS
El tipo de datos de integridad es la definición del tipo de datos que cada columna de la tabla (entidad) permite almacenar. Los siguientes tipos de datos son los que proporciona el MBD SQL Server
Tipo de datos Tamaño de
almacenamiento Intervalo
Byte 1 byte 0 a 255
Boolean 2 bytes True o False Integer 2 bytes -32,768 a 32,767 Long (entero largo) 4 bytes -2,147,483,648 a 2,147,483,647 Single (coma flotante/ precisión simple)
4 bytes -3,402823E38 a –1,401298E-45 para valores negativos; 1,401298E-45 a 3,402823E38 para valores positivos
Double
(coma flotante/ precisión doble)
8 bytes -1.79769313486231E308 a
-4,94065645841247E-324 para valores negativos; 4,94065645841247E-324 a 1,79769313486232E308 para valores positivos
Currency
(entero a escala)
8 bytes -922.337.203.685.477,5808 a 922.337.203.685.477,5807
Decimal 14 bytes +/-79.228.162.514.264.337.593.543.950.335 sin punto decimal;
+/-7,9228162514264337593543950335 con 28 posiciones a la derecha del signo decimal; el número más pequeño distinto de cero es +/-0,0000000000000000000000000001
Datetime 8 bytes 1 de enero de 100 a 31 de diciembre de 9999
Varchar (longitud variable) 10 bytes + longitud de la cadena Desde 0 a 2.000 millones Char (longitud fija) Longitud de la cadena Desde 1 a 65.400 aproximadamente Definido por el usuario Número requerido por los elementos
El intervalo de cada elemento es el mismo que el intervalo de su tipo de datos.
(utilizando Type)
OPERADORES EN SQL Operadores Lógicos
Operador Uso
AND Es el “y” lógico. Evalúa dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas.
OR Es el “o” lógico. Evalúa dos condiciones y devuelve un valor de verdad si alguna de las dos es cierta.
NOT Negación lógica. Devuelve el valor contrario de la expresión. 1. Operadores de Comparación
Operador Uso
< Menor que
> Mayor que
<> Distinto de
<= Menor ó Igual que
>= Mayor ó Igual que
BETWEEN Utilizado para especificar un intervalo de valores.
LIKE Utilizado en la comparación de un modelo
In Utilizado para especificar registros de una base de datos
2. Funciones de Agregado
Las funciones de agregado se usan dentro de una cláusula SELECT
en grupos de registros para devolver un único valor que se aplica a un grupo de registros.
Comando Descripción
AVG Utilizada para calcular el promedio de los valores de un campo determinado
COUNT Utilizada para devolver el número de registros de la selección
SUM Utilizada para devolver la suma de todos los valores de un campo
determinado
MAX Utilizada para devolver el valor más alto de un campo
especificado
MIN Utilizada para devolver el valor más bajo de un campo
especificado
EJEMPLOS Lógicos:
SELECT *
FROM Empleados
WHERE Edad > 25 AND Edad < 50
SELECT *
FROM Empleados
WHERE (Edad > 25 AND Edad < 50) OR Sueldo = 100
SELECT *
FROM Empleados
WHERE NOT Estado = 'Soltero'
SELECT *
FROM Empleados
WHERE (Sueldo > 100 AND Sueldo < 500) OR (Provincia = 'Trujillo' AND Estado = 'Casado')
OTROS OPERADORES El operador Like
Se utiliza para comparar una expresión de cadena con un modelo en una expresión SQL. Su sintaxis es: expresión Like modelo
En donde expresión es una cadena modelo o campo contra el que se compara expresión. Se puede utilizar el operador Like para encontrar valores en los campos que coincidan con el modelo especificado. Por modelo puede especificar un valor completo (Ana María), o se puede utilizar una cadena de caracteres comodín como los reconocidos por el sistema operativo para encontrar un rango de valores (Like An %).
El operador Like se puede utilizar en una expresión para comparar un valor de un campo con una expresión de cadena. Por ejemplo, si introduce Like C% en una consulta SQL, la consulta devuelve todos los valores de campo que comiencen por la letra C.
Ejemplos:
SELECT * FROM EMPLEADOS WHERE NOMBRES LIKE 'A%' Todo lo que comience por A
SELECT * FROM EMPLEADOS WHERE NOMBRES LIKE '%NG' Todo lo que comience por cualquier carácter y luego siga NG SELECT * FROM EMPLEADOS WHERE NOMBRES LIKE '[AF]%' Todo lo que comience por A ó F SELECT * FROM EMPLEADOS WHERE NOMBRES LIKE '[A-F]%' Todo lo que comience por cualquier letra comprendida entre la A y la F SELECT * FROM EMPLEADOS WHERE NOMBRES LIKE '[A^B]%' Todo lo que comience por A y la segunda letra no sea una B
El Operador In
Este operador devuelve aquellos registros cuyo campo indicado coincide con alguno de los en una lista.
Su sintaxis es: expresión [Not] In(valor1, valor2, . . .)
SELECT * FROM Pedidos
La cláusula WHERE
La cláusula WHERE puede usarse para determinar qué registros de las tablas enumeradas en la cláusula FROM aparecerán en los resultados de la instrucción
SELECT. Después de escribir esta cláusula se deben especificar las condiciones expuestas en los apartados anteriores. Si no se emplea esta cláusula, la consulta devolverá todas las filas de la tabla. WHERE es opcional, pero cuando aparece debe ir a continuación de FROM.
SELECT Apellidos, Salario FROM Empleados
WHERE Salario = 21000
SELECT IdProducto, Existencias FROM Productos
FUNCIONES
Existen en SQL muchas funciones que pueden complementar el manejo de los datos en las consultas. Se utilizan dentro de las expresiones y actuan con los valores de las columnas, variables o constantes.
Se pueden incluir en las clásulas SELECT, WHERE y ORDER BY.
Pueden anidarse funciones dentro de funciones. Y existe una gran variedad de funciones para cada tipo de datos:
Funciones Aritméticas
Función Cometido Ejemplo Resultado
ABS(n) Calcula el valor absoluto de n. select abs(-15) from
dual; 15
CEIL(n) Calcula el valor entero
inmediatamente superior o igual a n.
select ceil(15.7) from
dual; 16
FLOOR(n) Calcula el valor entero
inmediatamante inferior o igual a n.
select floor(15.7) from
dual; 15
MOD(m,n) Calcula el resto resultante de dividir m entre n.
select mod(11,4) from
dual; 3
POWER(m,n) Calcula la potencia n-esima de m. select power(3,2)
from dual; 9
ROUND(m,n)
Calcula el redondeo de m a n decimales. Si n<0 el redondeo se efectua a por la izquierda del punto decimal.
select round(123.456,1)
from dual;
123.5
SQRT(n) Calcula la raíz cuadrada de n. select sqrt(4) from
dual; 2
TRUNC(m,n) Calcula m truncado a n decimales (n puede ser negativo).
select
trunc(123.456,1) from dual;
123.4
SIGN(n) Calcula el signo de n, devolviendo -1 si n<0, 0 si n=0 y 1 si n>0.
select sign(-12) from
dual; -1
Funciones de Cadenas de Caracteres
CHR(n) Devuelve el carácter cuyo valor codificado es n.
select chr(65) from
dual; A
ASCII(cad) Devuelve el valor ascii de cad.
select ascii('A') from
dual; 65
CONCAT(cad1,cad2)
Devuelve cad1
concatenada con cad2. Esta función es
esquivalente al operador ||. select
concat(concat(nombre,' es '),oficio) from emp;
Cano es
Presidente, etc.
LOWER(cad)
Devuelve la cadena cad con todas sus letras convertidas a minúsculas. select lower('MinUsCulAs') from dual; minusculas UPPER(cad)
Devuelve la cadena cad con todas sus letras convertidas a mayúsculas.
select
upper('maYuSCulAs') from dual;
MAYUSCULAS
INITCAP(cad) Devuelve cad con el primer caracter en mayúsculas.
select initcap('isabel')
from dual; Isabel
LPAD(cad1,n,cad2)
Devuelve cad1 con longitud n, y ajustada a la derecha, rellenando por la izquierda con cad2.
select lpad('P',5,'*') from
dual; ****P
RPAD(cad1,n,cad2)
Devuelve cad1 con longitud n, y ajustada a la izquierda, rellenando por la derecha con cad2.
select rpad('P',5,'*')
from dual; P****
REPLACE(cad,ant,nue)
Devuelve cad en la que cada ocurrencia de la cadena ant ha sido sustituida por la cadena nue. select replace('digo','i','ie') from dual; diego SUBSTR(cad,m,n) Devuelve la sudcadena de cad compuesta por n caracteres a partir de la posicion m. select substr('ABCDEFG',3,2) from dual; CD
LENGTH(cad) Devuelve la longitud de cad.
select length('cadena') from dual; 6
Función Cometido Ejemplo Resultado
SYSDATE Devuelve la fecha y
hora actuales. select sysdate from dual; 14-MAR-97 ADD_MONTHS(d,n)
Devuelve la fecha d incrementada en n meses.
select add_months(sysdate,4)
from dual; 14-JUL-97
LAST_DAY(d)
Devuelve la fecha del último día del mes de d.
select last_day(sysdate) from
dual; 31-MAR-97
MONTHS_BETWEEN(d1, d2)
Devuelve la
diferencia en meses entre las fechas d1 y d2.
select
months_between(sysdate,'01-JAN-97') from dual;
2.43409424
NEXT_DAY(d,cad)
Devuelve la fecha del primer día de la semana cad
después de la fecha d.
select next_day(sysdate,
'sunday') from dual; 16-MAR-97
Funciones de Conversión de Tipos
Función Cometido Ejemplo Resultado
TO_NUMBER(cad,fmto)
Convierte la cadena cad a un número, opcionalmente de acuerdo con el formato fmto.
select
to_number('12345') from dual;
124345
TO_CHAR(d, fmto)
Convierte la fecha d a una cadena de caracteres, opcionalmente de acuerdo con el formato fmto.
select
to_char(sysdate) from dual;
'14-MAR-97'
TO_DATE(cad,fmto)
Convierte la cadena cad de tipo varchar2 a fecha, opcionalmente de acuerdo con el formato fmto.
select
to_date('1-JAN-97') from dual; 01-JAN-97
Con las fechas pueden utilizarse varios formatos. Estos formatos permiten modificar la presentación de una fecha. En la siguiente tabla se presentan algunos formatos de fecha y el resultado que generan.
Formato Cometido Ejemplo Resultado
cc ó scc Valor del siglo. select to_char(sysdate,'cc') from
dual; 20
y,yyy ó sy,yyy Año con coma, con o sin signo.
select to_char(sysdate,'y,yyy')
from dual; 1,997
yyyy ó yyy ó yy ó y
Año sin signo con cuatro, tres, dos o un dígitos.
select to_char(sysdate,'yyyy')
from dual; 1997
q Trimestre. select to_char(sysdate,'q') from
dual; 1
ww ó w Número de la semana del año o del mes.
select to_char(sysdate,'ww') from
dual; 11
mm Número del mes. select to_char(sysdate,'mm')
from dual; 03
ddd ó dd ó d Número del día del año, del mes o de la semana.
select to_char(sysdate,'ddd')
from dual; 073
hh ó hh12 ó hh24 La hora en formato 12h. o 24h.
select to_char(sysdate,'hh') from
dual; 12
mi Los minutos de la hora. select to_char(sysdate,'mi') from
dual; 15
ss ó sssss Los segundos dentro del minuto, o desde las 0 horas.
select to_char(sysdate,'sssss')
from dual; 44159
Máscaras de Formato de Caracteres
Formato Cometido Ejemplo Resultado
syear ó year Año en Inglés select to_char(sysdate,'syear) from dual;
nineteen ninety-seven
month o mon
Nombre del mes o su abreviatura de tres letras.
select to_char(sysdate,'month')
from dual; march
day ó dy
Nombre del día de la
semana o su abreviatura de tres letras.
select to_char(sysdate,'day')
from dual; friday a.m. ó p.m. El espacio del día. select to_char(sysdate,'a.m.')
from dual; p.m.
b.c. ó a.d. Indicador del año respecto al del nacimiento de Cristo.
select to_char(sysdate,'b.c.')
Función Cometido Ejemplo Resultado
DECODE(var, val1, cod1, val2, cod2, ..., defecto)
Convierte el valor de var, de acuerdo con la codificación. select decode(oficio, 'Presidente', 'P', 'Director', 'D', 'X') from emp; P, D, X, ... GREATEST(exp1, exp2, ...) Devuelve el mayor
valor de una lista. sin ejemplo. sin ejemplo. LEAST(cad,fmto) Devuelve el menor
valor de una lista. sin ejemplo. sin ejemplo.
NVL(val, exp)
Devuelve la
expresión exp si val es NULL, y val si en otro caso. select salario+nvl(comision,0) from emp; 450000, 350000, ...
CONSULTAS Y SUBCONSULTAS Definición de subconsultas.
Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia
SELECT. Normalmente se utilizan para filtrar una cláusula WHERE o HAVING con el conjunto de resultados de la subconsulta, aunque también pueden utilizarse en la lista de selección.
Por ejemplo podríamos consultar el alquiler último de un cliente.
SELECT COD_CLIENTE, NOMBRE, MARCA, MODELO FROM ALQUILERES
WHERE COD_CLIENTE = 1
AND FECHA_ALQUILER = (SELECTMAX(FECHA_ALQUILER)
FROM ALQUILERES
WHERE COD_CLIENTE = 1)
En este caso, la subconsulta se ejecuta en primer lugar, obteniendo el valor de la máxima fecha de alquiler, y posteriormente se obtienen los datos de la consulta principal.
Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis.
La subconsulta se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HAVING de la consulta principal.
Tiene las siguientes restricciones:
• No puede contener la cláusula ORDER BY
• No puede ser la UNION de varias sentencias SELECT
• Si la subconsulta aparece en la lista de selección, o esta asociada a un operador igual "=" solo puede devolver un único registro.
Referencias externas
A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna de la fila actual en la consulta principal, ese nombre de columna se denomina referencia externa.
Cuando se ejecuta una consulta que contiene una subconsulta con referencias externas, la subconsulta se ejecuta por cada fila de la consulta principal.
En este ejemplo la subconsulta aparece en la lista de selección, ejecutándose una vez por cada fila que devuelve la consulta principal.
SELECT COD_EMPLEADO,
NOMBRE,
(SELECT MIN(FECHA_NOMINA) FROM NOMINAS
WHERE COD_EMPLEADO = EMPLEADOS.COD_EMPLEADO) PRIMERA_NOMINA FROM EMPLEADOS;
Anidar subconsultas
Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula WHERE (por ejemplo) de otra subconsulta que a su vez forma parte de otra consulta principal.
SELECT COD_EMPLEADO, EMPLEADOS
FROM EMPLEADOS
WHERE COD_EMPLEADO IN (SELECT COD_EMPLEADO FROM NOMINAS
WHERE ESTADO IN ( SELECT ESTADO FROM ESTADOS_NOMINAS WHERE EMITIDO = 'S' AND PAGADO = 'N') )
Los resultados que se obtienen con subconsultas normalmente pueden conseguirse a través de consultas combinadas ( JOIN ).
SELECT COD_EMPLEADO,
NOMBRE
FROM EMPLEADOS
WHERE ESTADO IN (SELECT ESTADO FROM ESTADOS WHERE ACTIVO = 'S') Podrá escribirse como:
NOMBRE
FROM EMPLEADOS, ESTADOS
WHERE EMPLEADOS.ESTADO = ESTADOS.ESTADO AND ESTADOS.ACTIVO = 'S'
Normalmente es más rápido utilizar un JOIN en lugar de una subconsulta, aunque esto depende sobre todo del diseño de la base de datos y del volumen de datos que tenga.
Utilización de subconsultas con UPDATE
Podemos utilizar subconsultas también en consultas de actualización conjuntamente con UPDATE. Normalmente se utilizan para "copiar" el valor de otra tabla.
UPDATE EMPLEADOS
SET SALARIO_BRUTO = (SELECT SUM(SALARIO_BRUTO) FROM NOMINAS
WHERE NOMINAS.COD_EMPLEADO = EMPLEADOS.COD_EMPLEADO) WHERE SALARIO_BRUTO IS NULL
La función EXISTS
EXISTS es una función SQL que devuelve verdadero cuando una subconsulta retorna al menos una fila.
SELECT COD_CLIENTE, NOMBRE FROM CLIENTES
WHERE EXISTS ( SELECT *
FROM MOROSOS
WHERE COD_CLIENTE = CLIENTES.COD_CLIENTE AND PAGADO = 'N')
La función EXISTS puede ser utilizada en cualquier sentencia SQL válida, SELECT, UPDATE,INSERTo DELETE.
CONSULTAS DE SELECCION Uso de Índices de las tablas
Si deseamos que la sentecia SQL utilice un índice para mostrar los resultados se puede utilizar la palabra reservada INDEX de la siguiente forma:
SELECT ... FROM Tabla (INDEX=Indice) ...
Normalmente los motores de las bases de datos deciden que índice se debe utilizar para la consulta, para ello utilizan criterios de rendimiento y sobre todo los campos de búsqueda especificados en la cláusula WHERE. Si se desea forzar a no utilizar ningún índice utilizaremos la siguiente sintaxis:
SELECT ... FROM Tabla (INDEX=0) ...
Consultas con Predicado
El predicado se incluye entre la cláusula y el primer nombre del campo a recuperar, los posibles predicados son:
Predicado Descripción
ALL Devuelve todos los campos de la tabla
TOP Devuelve un determinado número de registros de la tabla
DISTINCT Omite los registros cuyos campos seleccionados coincidan totalmente DISTINCTOW Omite los registros duplicados basándose en la totalidad del registro y
no sólo en los campos seleccionados.
ALL
Si no se incluye ninguno de los predicados se asume ALL. El Motor de base de datos selecciona todos los registros que cumplen las condiciones de la instrucción SQL y devuelve todos y cada uno de sus campos. No es conveniente abusar de este predicado ya que obligamos al motor de la base de datos a analizar la estructura de la tabla para averiguar los campos que contiene, es mucho más rápido indicar el listado de campos deseados.
SELECT ALL FROM Empleados
SELECT *
FROM Empleados
TOP
Devuelve un cierto número de registros que entran entre al principio o al final de un rango especificado por una cláusula ORDER BY. Supongamos que queremos recuperar los nombres de los 25 primeros estudiantes del curso 1994:
SELECT TOP 25 Nombre, Apellido FROM Estudiantes
ORDER BY Nota DESC
Si no se incluye la cláusula ORDER BY, la consulta devolverá un conjunto arbitrario de 25 registros de la tabla de Estudiantes. El predicado TOP no elige entre valores iguales. En el ejemplo anterior, si la nota media número 25 y la 26 son iguales, la consulta devolverá 26 registros. Se puede utilizar la palabra reservada PERCENT para devolver un cierto porcentaje de registros que caen al principio o al final de un rango especificado por la cláusula ORDER BY. Supongamos que en lugar de los 25 primeros estudiantes deseamos el 10 por ciento del curso:
SELECT TOP 10 PERCENT Nombre, Apellido FROM Estudiantes
ORDER BY Nota DESC
El valor que va a continuación de TOP debe ser un entero sin signo. TOP no afecta a la posible actualización de la consulta.
DISTINCT
Omite los registros que contienen datos duplicados en los campos seleccionados. Para que los valores de cada campo listado en la instrucción SELECT se incluyan en la consulta deben ser únicos. Por ejemplo, varios empleados listados en la tabla
Empleados pueden tener el mismo apellido. Si dos registros contienen López en el campo Apellido, la siguiente instrucción SQL devuelve un único registro:
SELECT DISTINCT Apellido FROM
Empleados
Con otras palabras el predicado DISTINCT devuelve aquellos registros cuyos campos indicados en la cláusula SELECT posean un contenido diferente. El resultado de una consulta que utiliza DISTINCT no es actualizable y no refleja los cambios subsiguientes realizados por otros usuarios.
Insertar cambios en tablas
USE BdEjemplo_02 GO
/* Tabla CATEGORIA */
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'cons_15') ALTER TABLE Categoria
DROP CONSTRAINT cons_15 GO
ALTER TABLE Categoria
ADD IdCategoria Int IDENTITY(1,1)
CONSTRAINT cons_15 PRIMARY KEY (IdCategoria) GO
Uso del Inner Join
USE Nwind GO
SELECT P.IdProducto,P. NombreProducto, C.NombreCategoría FROM Productos AS P INNER JOIN Categorías AS C
