Diccionario de datos

Diccionario de Datos –

Durante la creación de la base de datos, el Oracle Server crea estructuras de datos adicionales junto con los data files. Tabla de diccionario de datos Tabla de funcionamiento dinámico.

1.       

o    El diccionario de datos es un conjunto de tablas de solo lectura y vistas que registran, verifican y proveen información.

o    El diccionario de datos describe la base de datos y sus objetos.

o    El diccionario incluye 2 tipos de objetos.

o    Tablas de Base de Datos.

o    es un almacén de la descripción de la base

o    Vistas del Diccionario de Datos.

o    creadas usando el script catalog.sql

2.       

o    El diccionario de datos provee información acerca de lo siguiente:

o    El nombre de los usuarios de Oracle.

o    Privilegios

o    Roles.

o    Defectos por valor para las columnas.

o    Auditoria.

o    Integridad en la información.

o    La definición de todos los esquemas de objetos en la base de datos.

o    Estructura física y lógica de la base de datos.

3.       

o    El diccionario de datos consiste de 3 principales vistas estáticas:

o    DBA: Accede a todos los objetos de la base

o    ALL: Objetos accesibles para usuarios comunes

o    USER: Objetos propios para usuarios comunes.

o    Para obtener un listado de las vistas se usa la consulta: SELECT * FROM V$FIXED_TABLE;

4.       

o    Las vistas de funcionamiento dinámico registran el flujo de la actividad de la BD.

o    Las vistas son continuamente actualizadas con la BD en operación.

o    La información es accedida desde:

o    Memoria y Control files.

o    DBA usa vistas dinámicas para monitorear y afinar la BD

o    Vistas dinámicas son propias del usuario SYS

o    DML no es permitido.

5.       

o    Se debe contestar los siguiente:

o    Esta el objeto en línea y habilitado?

o    Esta el objeto abierto?

o    Que campos del objeto deben ser bloqueados?

o    Esta la sesión activa?

6.       

o    Para las vistas del diccionario de datos, que son vistas estáticas se debe contestar:

o    Fue el objeto alguna ves creado?

o    Que parte del objeto fue creada?

o    Quien es el dueño del objeto?

o    Que privilegios tienen los usuarios?

o    Que restricciones tienen los objetos?

7.       

o    Hay que recordar que a través de las vistas dinámicas, el Oracle Server puede almacenar el flujo de actividad en la base de datos, por tanto las vistas virtuales vienen a ser como un conjunto de tablas virtuales.

o    Este conjunto de tablas virtuales existen solo en memoria cuando la base datos esta corriendo, y reflejan en tiempo real las condiciones de las operaciones de la base.

o    Estas tablas virtuales son el actual de la fuente de información en memoria y archivos de control.

8.       

o    Consultas al Diccionario de Datos y Vistas Dinámicas.

El diccionario de datos y las vistas dinámicas pueden ser requeridas para información como: Listar las vistas disponibles, que pueden ser recuperadas por consultas a la vista del diccionario. Listar las columnas y sus contenidos para que puedan ser accedidos usando el DESCRIBE y SELECT.

9.       

o    Para obtener una vista general:

o    DICTIONARY, DICT_COLUMNS

o    Para obtener un diagrama de objetos:

o    DBA_TABLES, DBA_INDEXES,

o    DBA_TAB_COLUMNS, DBA_CONSTRAINTS

o    Para obtener la estructura de la base de datos:

o    DBA_TABLESPACES, DBA_DATA_FILES

                                                    V       I        D      E        O http://www.youtube.com/watch?v=B2IRvns4kVw

                    

Restricciones

Una restricción es una condición que obliga el cumplimiento de ciertas condiciones en la base de datos. Algunas no son determinadas por los usuarios, sino que son inherentemente definidas por el simple hecho de que la base de datos sea relacional. Algunas otras restricciones las puede definir el usuario, por ejemplo, usar un campo con valores enteros entre 1 y 10.

Las restricciones proveen un método de implementar reglas en la base de datos. Las restricciones restringen los datos que pueden ser almacenados en las tablas. Usualmente se definen usando expresiones que dan como resultado un valor booleano, indicando si los datos satisfacen la restricción o no.

Las restricciones no son parte formal del modelo relacional, pero son incluidas porque juegan el rol de organizar mejor los datos. Las restricciones son muy discutidas junto con los conceptos relacionales.

Dominios

Un dominio describe un conjunto de posibles valores para cierto atributo. Como un dominio restringe los valores del atributo, puede ser considerado como una restricción. Matemáticamente, atribuir un dominio a un atributo significa "todos los valores de este atributo deben de ser elementos del conjunto especificado".

Distintos tipos de dominios son: enteros, cadenas de texto, fecha,no procedurales etc.

Clave única

Cada tabla puede tener uno o más campos cuyos valores identifican de forma única cada registro de dicha tabla, es decir, no pueden existir dos o más registros diferentes cuyos valores en dichos campos sean idénticos. Este conjunto de campos se llama clave única.

Pueden existir varias claves únicas en una determinada tabla, y a cada una de éstas suele llamársele candidata a clave primaria.

Clave primaria

Una clave primaria es una clave única elegida entre todas las candidatas que define unívocamente a todos los demás atributos de la tabla, para especificar los datos que serán relacionados con las demás tablas. La forma de hacer esto es por medio de claves foráneas.

Sólo puede existir una clave primaria por tabla y ningún campo de dicha clave puede contener valores NULL.

Clave foránea

Una clave foránea es una referencia a una clave en otra tabla. Las claves foráneas no necesitan ser claves únicas en la tabla donde están y sí a donde están referenciadas.

Por ejemplo, el código de departamento puede ser una clave foránea en la tabla de empleados, obviamente se permite que haya varios empleados en un mismo departamento, pero existirá sólo un departamento.

V I  D  E O http://www.youtube.com/watch?v=rR1dyMYp2TU

Teoría de consultas

En bases de datos, una consulta es el método para acceder a los datos en las bases de datos. Con las consultas se puede modificar, borrar, mostrar y agregar datos en una base de datos. Para esto se utiliza un lenguaje de consultas. El lenguaje de consultas a base de datos más utilizado es el SQL.

Técnicamente hablando, las consultas a la base de datos se realizan a través de un lenguaje de manipulación de datos (DML – Data Manipulation Language). SQL es un lenguaje DML, pero además posee otras características de otros lenguajes. Por ejemplo, permite también crear bases de datos.

La consulta básica en SQL es llamada select-from-where.

Multitablas

Multitabla

En este tema vamos a estudiar las consultas multitabla llamadas así porque están basadas en más de una tabla.

El SQL de Microsoft Jet 4.x soporta dos grupos de consultas multitabla:

- la unión de tablas

- la composición de tablas

La unión de tablas

Esta operación se utiliza cuando tenemos dos tablas con las mismas columnas y queremos obtener una nueva tabla con las filas de la primera y las filas de la segunda. En este caso la tabla resultante tiene las mismas columnas que la primera tabla (que son las mismas que las de la segunda tabla).

Por ejemplo tenemos una tabla de libros nuevos y una tabla de libros antiguos y queremos una lista con todos los libros que tenemos. En este caso las dos tablas tienen las mismas columnas, lo único que varía son las filas, además queremos obtener una lista de libros (las columnas de una de las tablas) con las filas que están tanto en libros nuevos como las que están en libros antiguos, en este caso utilizaremos este tipo de operación.

Cuando hablamos de tablas pueden ser tablas reales almacenadas en la base de datos o tablas lógicas (resultados de una consulta), esto nos permite utilizar la operación con más frecuencia ya que pocas veces tenemos en una base de datos tablas idénticas en cuanto a columnas. El resultado es siempre una tabla lógica.

Por ejemplo queremos en un sólo listado los productos cuyas existencias sean iguales a cero y también los productos que aparecen en pedidos del año 90. En este caso tenemos unos productos en la tabla de productos y los otros en la tabla de pedidos, las tablas no tienen las mismas columnas no se puede hacer una union de ellas pero lo que interesa realmente es el identificador del producto (idfab,idproducto), luego por una parte sacamos los códigos de los productos con existencias cero (con una consulta), por otra parte los códigos de los productos que aparecen en pedidos del año 90 (con otra consulta), y luego unimos estas dos tablas lógicas.

El operador que permite realizar esta operación es el operador UNION.

La composición de tablas

La composición de tablas consiste en concatenar filas de una tabla con filas de otra. En este caso obtenemos una tabla con las columnas de la primera tabla unidas a las columnas de la segunda tabla, y las filas de la tabla resultante son concatenaciones de filas de la primera tabla con filas de la segunda tabla.

El ejemplo anterior quedaría de la siguiente forma con la composición

A diferencia de la unión la composición permite obtener una fila con datos de las dos tablas, esto es muy útil cuando queremos visualizar filas cuyos datos se encuentran en dos tablas

Por ejemplo queremos listar los pedidos con el nombre del representante que ha hecho el pedido, pues los datos del pedido los tenemos en la tabla de pedidos pero el nombre del representante está en la tabla de empleados y además queremos que aparezcan en la misma línea; en este caso necesitamos componer las dos tablas (Nota: en el ejemplo expuesto a continuación, hemos seleccionado las filas que nos interesan).

Existen distintos tipos de composición, aprenderemos a utilizarlos todos y a elegir el tipo más apropiado a cada caso.

Los tipos de composición de tablas son:

. El producto cartesiano

. El INNER JOIN

. El LEFT / RIGHT JOIN

subconsultas

Definiciones

Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT que llamaremos consulta principal.

Se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HAVING de la consulta principal.

Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis, no puede contener la cláusula ORDER BY, ni puede ser la UNION de varias sentencias SELECT, además tiene algunas restricciones en cuanto a número de columnas según el lugar donde aparece en la consulta principal. Estas restricciones las iremos describiendo en cada caso.

Cuando se ejecuta una consulta que contiene una subconsulta, la subconsulta se ejecuta por cada fila de la consulta principal.

Se aconseja no utilizar campos calculados en las subconsultas, ralentizan la consulta.

Las consultas que utilizan subconsultas suelen ser más fáciles de interpretar por el usuario.

Referencias externas

A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna en la fila actual de la consulta principal, ese nombre de columna se denomina referencia externa.
Una referencia externa es un nombre de columna que estando en la subconsulta, no se refiere a ninguna columna de las tablas designadas en la FROM de la subconsulta sino a una columna de las tablas designadas en la FROM de la consulta principal. Como la subconsulta se ejecuta por cada fila de la consulta principal, el valor de la referencia externa irá cambiando.

Ejemplo:
SELECT numemp, nombre, (SELECT MIN(fechapedido) FROM pedidos WHERE rep = numemp)
FROM empleados;

En este ejemplo la consulta principal es SELECT... FROM empleados.
La subconsulta es ( SELECT MIN(fechapedido) FROM pedidos WHERE rep = numemp ).
En esta subconsulta tenemos una referencia externa ( numemp ) es un campo de la tabla empleados (origen de la consulta principal).

¿Qué pasa cuando se ejecuta la consulta principal?

- se coge el primer empleado y se calcula la subconsulta sustituyendo numemp por el valor que tiene en el primer empleado. La subconsulta obtiene la fecha más antigua en los pedidos del rep = 101,
- se coge el segundo empleado y se calcula la subconsulta con numemp = 102 (numemp del segundo empleado)... y así sucesivamente hasta llegar al último empleado.
Al final obtenemos una lista con el número, nombre y fecha del primer pedido de cada empleado.

Si quitamos la cláusula WHERE de la subconsulta obtenemos la fecha del primer pedido de todos los pedidos no del empleado correspondiente.

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. En la práctica, una consulta consume mucho más tiempo y memoria cuando se incrementa el número de niveles de anidamiento. La consulta resulta también más difícil de leer , comprender y mantener cuando contiene más de uno o dos niveles de subconsultas.

SELECT numemp, nombre
FROM empleados
WHERE numemp = (SELECT rep FROM pedidos WHERE clie = (SELECT numclie FROM clientes WHERE nombre = 'Julia Antequera'))

En este ejemplo, por cada linea de pedido se calcula la subconsulta de clientes, y esto se repite por cada empleado, en el caso de tener 10 filas de empleados y 200 filas de pedidos (tablas realmente pequeñas), la subconsulta más interna se ejecutaría 2000 veces (10 x 200).

Subconsulta en la lista de selección

Cuando la subconsulta aparece en la lista de selección de la consulta principal, en este caso la subconsulta, no puede devolver varias filas ni varias columnas, de lo contrario se da un mensaje de error.

Muchos SQLs no permiten que una subconsulta aparezca en la lista de selección de la consulta principal pero eso no es ningún problema ya que normalmente se puede obtener lo mismo utilizando como origen de datos las dos tablas. El ejemplo anterior se puede obtener de la siguiente forma

SELECT numemp, nombre, MIN(fechapedido)
FROM empleados LEFT JOIN pedidos ON empleados.numemp = pedidos.rep
GROUP BY numemp, nombre

En la cláusula FROM

En la cláusula FROM se puede encontrar una sentencia SELECT encerrada entre paréntesis pero más que subconsulta sería una consulta ya que no se ejecuta para cada fila de la tabla origen sino que se ejecuta una sola vez al principio, su resultado se combina con las filas de la otra tabla para formar las filas origen de la SELECT primera y no admite referencias externas

En la cláusula FROM vimos que se podía poner un nombre de tabla o un nombre de consulta, pues en vez de poner un nombre de consulta se puede poner directamente la sentencia SELECT correspondiente a esa consulta encerrada entre paréntesis.

operaciones de conjuntos

SQL Server 2005 permite tres tipos de operaciones con conjuntos:

·         UNION, disponible en todas las versiones de SQL Server.

·         EXCEPT, nuevo en SQL Server 2005.

·         INTERSECT, nuevo en SQL Server 2005.

Para utilizar operaciones de conjuntos debemos cumplir una serie de normas.

·         Las consultas a unir deben tener el mismo número campos, y además los campos deben ser del mismo tipo.

·         Sólo puede haber una única clausula ORDER BY al final de la sentencia SELECT.

UNION

UNION devuelve la suma de dos o más conjuntos de resultados. El conjunto obtenido como resultado de UNION tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de UNION

Cuando realizamos una consulta con UNION internamente se realiza una operacion DISTINCT sobre el conjunto de resultados final. Si queremos obtener todos los valores debemos utiliza UNION ALL.

EXCEPT devuelve la diferencia (resta) de dos o más conjuntos de resultados. El conjunto obtenido como resultado de EXCEPT tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de EXCEPT

El uso de EXCEPT, como norma general, es mucho más rápido que utilizar condiciones NOT IN o EXISTS en la clausula WHERE.

INTERSECT

Devuelve la intersección entre dos o más conjuntos de resultados en uno. El conjunto obtenido como resultado de INTERSECT tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de INTERSECT