Bases De Datos I. Tema Vi. Lenguaje De Consulta Estructurado 2da Parte

Tema VI. Lenguaje de Consulta

Estructurado SQL

::Universidad del Mar::

::Campus Puerto Escondido ::

Bases de Datos I MTI Remedios Fabián Velasco Ver. 1.1 Cuarto Semestre Licenciatura en Informática

Temario 6.5 Las subconsultas. 6.5.1 Definiciones. 6.5.2 Referencias externas. 6.5.3 Anidar subconsultas. 6.5.4 Subconsulta en la lista de selección. 6.5.5 Subconsulta en la cláusula FROM. 6.5.6 Subconsulta en las cláusulas WHERE y HAVING. 6.5.7 Condiciones de selección con subconsultas. 6.6 Actualización de datos. 6.6.1 Insertar una fila INSERT INTO VALUES. 6.6.2 Insertar varias filas INSERT INTO SELECT. 6.6.3 Insertar filas en una tabla nueva SELECT INTO. 6.6.4 Modificar el contenido de las filas UPDATE. 6.6.5 Borrar fila DELETE. 6.6.6 Conceptos básicos de integridad referencial. 6.7 Tablas de referencias cruzadas. 6.7.1 La sentencia TRANSFORM. 6.7.2 Las columnas dinámicas. 6.7.3 Las columnas fijas. 6.8 El DDL (lenguaje de definición de datos). 6.8.1 La sentencia CREATE TABLE. 6.8.2 La sentencia ALTER TABLE. 6.8.3 La sentencia DROP TABLE. 6.8.4 La sentencia CREATE INDEX. 6.8.5 La sentencia DROP INDEX. 6.8.6 Integridad referencial. 6.8.7 Los índices. 6.8.8 Tipos de datos. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5 Las subconsultas. SQL proporciona un mecanismo para las subconsultas anidadas. Una subconsulta es una expresión select from where que se anida dentro de otra consulta. Un uso común de subconsultas es llevar a cabo comprobaciones sobre pertenencia a conjuntos, comparación de conjuntos y cardinalidad de conjuntos. Pertenencia a conjuntos SQL utiliza el cálculo relacional para las operaciones que permiten comprobar la pertenencia de una tupla a una relación. La conectiva in comprueba la pertenencia a un conjunto, donde el conjunto es la colección de valores resultado de una cláusula select. La conectiva

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5 Las subconsultas. Las operaciones de intersección (intersect) y diferencia (except) se puede realizar utilizando consultas anidadas, como se muestra a continuación: r1  r 2 Intersección: SELECT * FROM r1 WHERE (A,C,B) IN (SELECT * FROM r1 r 2 r2)

Diferencia: SELECT * FROM r1 WHERE (A,C,B) NOT IN (SELECT * FROM r2)

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.1 Definiciones. Los resultados de la instrucción interna (subconsulta) se utilizan en la instrucción externa como ayuda para determinar el contenido de la tabla de resultados final. Puede utilizarse una subselección en las cláusulas SELECT y HAVING de una instrucción SELECT externa, en cuyo caso se denomina esa instrucción SELECT interna subconsulta o consulta anidada. Las subselecciones pueden aparecer también dentro de las instrucciones INSERT, UPDATE, y DELETE. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.1 Definiciones. Existen tres tipos de subconsultas: Escalar: Devuelve una única columna y una única fila, es decir, un único valor. En principio puede utilizarse una subconsulta escalar en cualquier lugar donde haga falta un único valor. Generar un listado con todos los empleados de la sucursal ubicada en ‘163 Main St’ SELECT noPersonal, pNombre, pApellido, puesto FROM Personal WHERE noSucursal=(SELECT noSucursal FROM Sucursal WHERE calle = ‘163 Main St’ ); Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.1 Definiciones. Subconsulta de fila: Devuelve múltiples columnas, pero de nuevo de una fila única. Puede utilizarse una subconsulta de fila siempre que se necesite un constructor para generar un valor de fila, lo que normalmente sucede en los predicados. Generar un listado de todos los empleados cuyo salario sea superior al salario medio, indicando cuál es la diferencia en cada caso con respeto al salario medio. SELECT noPersonal, pNombre, pApellido, puesto, salario – (SELECT AVG(salario) FROM Personal) AS diferencia FROM Personal Bases de Datos I

WHERE salario >Universidad (SELECT AVG(salario) FROM Personal); del Mar 07/2008 MTI Remedios Fabián Velasco

6.5.1 Definiciones. Subconsulta de tabla: Devuelve una o más columnas y múltiples filas. Puede utilizarse una subconsulta de tabla en cualquier lugar donde se necesite una tabla, como por ejemplo para operando del predicado IN. Generar un listado con los inmuebles gestionados por los empleados que trabajan en la sucursal situada en ‘163 Main St’ SELECT noPropiedad, pCalle, pCiudad, pCp, pTipo, pCuartos, pRenta FROM Propiedades WHERE noPersonal=(SELECT noPersonal FROM Personal WHERE calle = ‘163 Main St’ ); Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.2 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 Universidad del Mar 07/2008 Bases de Datos MTI Remedios Fabián Velasco unI campo de la tabla empleados (origen de la consulta principal).

6.5.3 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. Ejemplo: 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 línea 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). Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.4 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 SQL’s 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. Por ejemplo: SELECT numemp, nombre, MIN(fechapedido) FROM empleados LEFT JOIN pedidos empleados.numemp = pedidos.rep GROUP BY numemp, nombre Bases de Datos I

Universidad del Mar 07/2008

ON

MTI Remedios Fabián Velasco

6.5.5 Subconsulta 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. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.6 Subconsulta en las cláusulas WHERE y HAVING. Se suele utilizar subconsultas en las cláusulas WHERE o HAVING cuando los datos que queremos visualizar están en una tabla pero para seleccionar las filas de esa tabla necesitamos un dato que está en otra tabla. Ejemplo: SELECT numemp, nombre FROM empleados WHERE contrato = (SELECT MIN(fechapedido) FROM pedidos)

En este ejemplo listamos el número y nombre de los empleados cuya fecha de contrato sea igual a la primera fecha de todos los pedidos de la empresa. En una cláusula WHERE / HAVING tenemos siempre una condición y la subconsulta actúa de

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.5.7 Condiciones de selección con subconsultas. Las condiciones de selección son las condiciones que pueden aparecer en la cláusula WHERE o HAVING. En SQL tenemos cuatro condiciones: Test Test Test Test

Bases de Datos I

de de de de

comparación con subconsulta comparación cuantificada pertenencia a un conjunto existencia

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6 Actualización de datos. La actualización de datos, como insertar nuevas filas, borrar filas o cambiar el contenido de las filas de una tabla, son operaciones que modifican los datos almacenados en las tablas pero no su estructura, ni su definición. Existen comando para: Insertar nuevas filas -> INSERT INTO Insertar y crear -> SELECT... INTO Borrar filas de una tabla -> DELETE Modificar el contenido de las filas -> UPDATE

Nota: Todas estas operaciones se podrán realizar siempre que se tengan los permisos correspondientes. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.1 Insertar una fila INSERT INTO VALUES. Para insertar datos en una relación, o bien se especifica la tupla que se desea insertar o se formula una consulta cuyo resultado sea el conjunto de tuplas que se desean insertar. Los valores de los atributos de la tuplas que se inserten deben pertenecer al dominio de los atributos y deberán ser de la aridad correcta. INSERT INTO cuenta VALUES (‘C-9732’, ‘Navacerrada’, 1200)

SQL permite que los atributos se especifiquen en la cláusula INSERT. Así, el siguiente ejemplo tiene una función idéntica al anterior: INSERT INTO cuenta (nomSucursal, noCuenta, saldo) VALUES (‘Navacerrada’, ‘C-9732’, 1200) Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.2 Insertar varias filas INSERT INTO SELECT. Insertar las tuplas que resultan de una consulta. Por ejemplo, si a todos los clientes tenedores de préstamos en la sucursal Navacerrada se les quisiera regalar, como gratificación, una cuenta de ahorro con $200 por cada cuenta de préstamo que tienen, se podría escribir: INSERT INTO cuenta SELECT nomSucursal, noPrestamo, 200 FROM prestamo WHERE nomSucursal = ‘Navacerrada’ Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.3 Insertar filas en una tabla nueva SELECT INTO. Esta sentencia inserta filas creando en ese momento la tabla donde se insertan las filas. Se suele utilizar para guardar en una tabla el resultado de una SELECT. Las columnas de la nueva tabla tendrán el mismo tipo y tamaño que las columnas origen, y se llamarán con el nombre de alias de la columna origen o en su defecto con el nombre de la columna origen, pero no se transfiere ninguna otra propiedad del campo o de la tabla como por ejemplo las claves e índices. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.3 Insertar filas en una tabla nueva SELECT INTO. La sentencia SELECT puede ser cualquier sentencia SELECT sin ninguna restricción, puede ser una consulta multitabla, una consulta de resumen, una UNION ... Ejemplo: SELECT * INTO t2 FROM t1 Esta sentencia genera una nueva tabla t2 con todas las filas de la tabla t1. Las columnas se llamarán igual que en t1 pero t2 no será una copia exacta de t1 ya no tendrá clave principal ni relaciones con las otras tablas, ni índices si los tuviese t1 Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.4 Modificar el contenido de las filas UPDATE. En determinadas situaciones puede ser deseable cambiar un valor dentro de una tupla, sin cambiar todos los valores de la misma. Para este tipo de situaciones se utiliza la instrucción update. Al igual que ocurre con insert y delete, se pueden elegir las tuplas que van a ser actualizadas mediante una consulta.

Por ejemplo, si hubiera que realizar el pago de intereses anuales y todos los saldos se incrementasen en un 5 %, habría que formular la siguiente actualización: UPDATE cuenta SET saldo = saldo * 1.05 Esta actualización se aplica una vez a cada tupla de la relación cuenta.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.4 Modificar el contenido de las filas UPDATE. Si se paga el interés sólo a las cuentas con un saldo de $1,000 o superior, se puede escribir update cuenta set saldo = saldo * 1.05 where saldo >= 1000

Pagar un interés del 5% a las cuentas cuyo saldo sea mayor que la media» como sigue: update cuenta set saldo = saldo * 1.05 where (saldo > select avg(saldo) from cuenta)

SQL ofrece una constructora case, que se puede usar para formular las dos instrucciones de actualización anteriores en una única instrucción de actualización, evitando el problema del orden de actualización. update cuenta set saldo = case when saldo <= 10000 then saldo * 1.05 else saldo * 1.06 end

La forma general de la instrucción case es la siguiente:

case when pred1 then result1 … when predn then resultn else result0 Universidad del Mar 07/2008 Bases de Datos I end

MTI Remedios Fabián Velasco

6.6.5 Borrar fila DELETE. Un borrado se expresa igual que una consulta. Se pueden borrar sólo tuplas completas, es decir, no se pueden borrar valores de atributos concretos. Un borrado se expresa en SQL del modo siguiente: DELETE FROM r

P donde P representa un predicado y r representa una relación. La declaración DELETE selecciona primero todas las tuplas t en r para las que P (t) es cierto y a continuación las borra de r. La cláusula WHERE se puede omitir, en cuyo caso se borran todas las tuplas de r. Hay que señalar que una orden DELETE opera sólo sobre una relación. Si se desea borrar tuplas de varias relaciones, se deberá utilizar una orden DELETE por cada relación. WHERE

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.5 Borrar fila DELETE. El predicado de la cláusula where puede ser tan complicado como el where de cualquier cláusula select, o tan simple como una cláusula where vacía. La consulta delete from préstamo borra todas las tuplas de la relación préstamo (los sistemas bien diseñados requieren una confirmación del usuario antes de ejecutar una consulta tan devastadora). Borrar todas las cuentas de la sucursal Navacerrada.

DELETE FROM cuenta WHERE nomSucursal = ‘Navacerrada’ Borrar las cuentas de todas las sucursales de Navacerrada. DELETE FROM cuenta WHERE nomSucursal in (SELECT nomSucursal FROM sucursal WHERE ciudadSucursal = ‘Navacerrada’)

El borrado anterior selecciona primero todas las sucursales con sede en Navacerrada y a continuación borra todas las tuplas cuenta pertenecientes a esas sucursales. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.6.6 Conceptos básicos de integridad referencial. Una llave externa es un columna o conjunto de columnas que enlaza cada fila de la tabla hijo que contiene la tabla externa con la fila de la llave padre que contiene el valor correspondiente de la llave candidata. La integridad referencial quiere decir que, si la llave externa contiene un valor, dicho valor debe hacer referencia a una fila existente y válida dentro de una tabla padre. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.7 Tablas de referencias cruzadas. Cuando queremos representar una consulta sumaria con dos columnas de agrupación como una tabla de doble entrada en la que cada una de las columnas de agrupación es una entrada de la tabla utilizaremos una consulta de tabla de referencias cruzadas. Por ejemplo queremos obtener las ventas mensuales de nuestros empleados. Tenemos que diseñar una consulta sumaria calculando la suma de los importes de los pedidos agrupando por empleado y mes de la venta.

La consulta sería:

Bases de Datos I

SELECT rep AS empleado, MONTH(fechapedido) as mes, SUM(importe) as vendido FROM pedidos Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.7 Tablas de referencias cruzadas. El resultado: La consulta quedaría mucho más elegante y clara presentando los datos en un formato más compacto como el siguiente:

Éste último resultado se obtiene mediante una consulta de referencias cruzadas. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.7.1 La sentencia TRANSFORM. Las consultas de referencias cruzadas se pueden crear directamente en SQL mediante la sentencia TRANSFORM. La sintaxis es la siguiente:

En SELECT la columnafija es la columna que define el encabezado de filas, el origen que indicamos en la cláusula FROM es la tabla (o tablas) de donde sacamos la información, y en la cláusula GROUP BY ponemos la columna que va a definir las filas del resultado. SELECT puede contener una cláusula WHERE para seleccionar la filas que se utilizan para calcular el resultado, puede contener subconsultas pero no la cláusula HAVING. En la cláusula PIVOT indicamos la columna cuyos valores van a definir columnas dinámicas del resultado a esta columna la llamaremos pivote. Universidad delel Mar conjunto 07/2008 Bases de Datos MTI Remedios Fabián Velasco LaI cláusula IN permite definir de valores que

6.7.1 La sentencia TRANSFORM. La sentencia del ejemplo, utilizando TRANSFORM, quedaría de la siguiente forma: TRANSFORM SUM(importe) SELECT rep AS empleado FROM pedidos GROUP BY rep PIVOT MONTH(fechapedido);

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.7.2 Las columnas dinámicas. Las columnas dinámicas son las que se generan según los valores almacenados en la columna pivote (PIVOT). Se genera una columna dinámica por cada valor que se encuentre en la columna pivote del origen de datos. Cuando los posibles valores que puede tomar la columna pivote son conocidos y queremos definir cuales y el orden en que aparezcan, algunos o todos incluso, se utiliza la cláusula IN. En la cláusula IN se ponen entre paréntesis todos los posibles valores, o los que se requiera visualizar, así como el orden en el resultado. Por ejemplo sólo interesan los meses de febrero, mayo y diciembre: 

Bases de Datos I

TRANSFORM SUM(importe) SELECT rep AS empleado FROM pedidos GROUP BY rep Universidad del Mar 07/2008 PIVOT month(fechapedido) IN (2,5,12);

//

MTI Remedios Fabián Velasco

6.7.3 Las columnas fijas. Las columnas fijas son las que aparecen delante de las columnas dinámicas. Son fijas porque se genera una sola columna en el resultado por cada columna indicada en la lista de columnas fijas. Las columnas fijas se indican en la lista de la sentencia SELECT, una columna fija que siempre debe incluirse es la que sirve de encabezado de fila. Además podemos incluir otras columnas, por ejemplo, columnas de resumen de cada fila, sin que se tenga en cuenta la agrupación por la columna pivote. Por ejemplo queremos saber para cada empleado cuánto ha vendido en total y cuál ha sido el importe mayor vendido en un pedido:

Bases de Datos I

TRANSFORM SUM(importe) AS Suma SELECT rep AS empleado, SUM(importe) AS [Total vendido],MAX(importe) AS mayor FROM pedidos Universidad del Mar 07/2008 MTI Remedios Fabián Velasco GROUP BY rep

6.8 El DDL (lenguaje de definición de datos). Un esquema de base de datos se especifica mediante un conjunto de definiciones expresadas mediante un lenguaje especial llamado lenguaje de definición de datos (LDD). DDL (Data Description Language), lenguaje de definición de datos, incluye órdenes para definir, modificar o borrar las tablas en las que se almacenan los datos y de las relaciones entre estas. (Es el que más varia de un sistema a otro) Por ejemplo, la siguiente instrucción en el lenguaje SQL define la tabla cuenta: create table cuenta (noCuenta char(10), saldo integer)

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.1 La sentencia CREATE TABLE.

Un esquema de relación se define utilizando la orden create table: create table r (A1D1, A2D2, … AnDn,) (restricción-integridad1) … (restricción-integridadk) donde r es el nombre de la relación, cada Ai es el nombre de un atributo del esquema de relación r y Di es el dominio de los valores del atributo Ai. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.1 La sentencia CREATE TABLE. Las restricciones de integridad válidas incluyen: Primary Key (Aj1, Aj2,…,Ajm): la especificación de llave primaria dice que los atributos Aj1, Aj2,…,Ajm forman la llave primaria de la relación. Los atributos llave primaria deben ser no nulos y únicos; es decir, ninguna tupla puede tener un valor nulo para un atributo de la llave primaria y ningún par de tuplas de la relación pueden ser iguales en todos los atributos llave primaria. Aunque la especificación de clave primaria es opcional, es generalmente buena idea especificar una llave primaria para cada relación. Check (P): la cláusula check especifica un predicado P que debe satisfacer cada tupla de la relación.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.1 La sentencia CREATE TABLE. create table sucursal (nomSucursal char (15), ciudadSucursal char (30), activo integer, primary key (nomSucursal), check (activo >= 0)); create table estudiante (nombre char (15) not null, idEstudiante char (10) not null, nivelEstudios char (15) not null, primary key (idEstudiante), check (nivelEstudios in (‘Graduado’, ‘Licenciado’,‘Doctorado’ )));

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.2 La sentencia ALTER TABLE. La instrucción alter table se utiliza para añadir atributos a una relación existente. La sintaxis de la instrucción es la siguiente: alter table r add A D

donde r es el nombre de una relación existente, A es el nombre del atributo que se desea añadir y D es el dominio del atributo A. Se pueden eliminar atributos de una relación utilizando la orden Ejemplo: alter table cuentas add saldo integer(10); alter table r drop A

donde r es el nombre de una relación existente y A es el nombre de un atributo de la relación. alter table cuentas drop saldo; Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.3 La sentencia DROP TABLE. Para borrar una relación de una base de datos SQL, se utiliza la orden drop table. Dicha orden borra de la base de datos toda la información sobre la relación eliminada. La instrucción drop table r tiene una repercusión más drástica que delete from r La última conserva la relación r, pero borra todas sus tuplas. La primera, no sólo borra todas las tuplas de la relación r, sino también borra su esquema. Después de que r se elimine no se puede insertar ninguna tupla en dicha relación, a menos que su esquema se vuelva a crear utilizando la instrucción create table. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.4 La sentencia CREATE INDEX. Los índices no se necesitan para la corrección, ya que son estructuras de datos redundantes. Sin embargo, los índices son importantes para el procesamiento eficiente de las transacciones, incluyendo las transacciones de actualización y consulta. Los índices son también importantes para un cumplimiento eficiente de las ligaduras de integridad. Por ejemplo, las implementaciones típicas obligan a declarar una clave, mediante la creación de un índice con la clave declarada como la clave de búsqueda del índice.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.4 La sentencia CREATE INDEX. Un índice se crea mediante la orden create index, la cual tiene la forma:

CREATE INDEX <nombre-índice> ON <nombre-relación> (<listaatributos>)

lista-atributos es la lista de atributos de la relación que constituye la clave de búsqueda del índice. Para definir un índice llamado índices de la relación sucursal con la clave de búsqueda nombreSucursal, se escribe CREATE INDEX indices ON sucursal (nomSucursal)

Para declarar que la llave de búsqueda es una llave candidata, hay que añadir el atributo unique. Con esto, la orden: create unique index índice-s on sucursal (nombre_sucursal)

declara nombre-sucursal como una llave candidata de sucursal. Si cuando se introduce la orden create unique index, nombre-sucursal no es una llave candidata, se mostrará un mensaje de error y el intento de crear un índice fallará. Por otro lado, si el intento de crear el índice ha tenido éxito, cualquier intento de insertar una tupla que viole la declaración de llave fallará. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

NOTA: Hay que observar que el carácter unique es redundante si la

6.8.5 La sentencia DROP INDEX. El nombre de índice especificado con el index se necesita para hacer posible la eliminación (drop) de índices. La orden drop index tiene la forma drop index <nombre-índice>

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.6 Integridad referencial. Existe la cláusula FOREING KEY en las instrucciones CREATE y ALTER TABLE, para definir la llave externa en la tabla hija. SQL rechazará cualquier intento de agregar una fila en la tabla hija que no tenga un valor válido en la llave candidata de la tabla padre. Ejemplo: Definir la llave foránea noSucursal situada en la tabla Sucursal. FOREING KEY (noSucursal) REFERENCES Sucursal;

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.6 Integridad referencial. Foreing key puede aplicar la integridad referencial al utilizar la cláusula update o delete, especificando las subcláusulas ON UPDATE y ON DELETE. Además soporta cuatro opciones relativas a la acción que hay que tomar: 1. CASCADE. Borra la fila de a tabla padre y borra automáticamente las filas correspondientes en la tabla hija, si a su vez tienen una clave candidata que es llave foránea en otra también la elimina. 2. SET NULL. Se borra la fila de la tabla padre y se asigna el valor NULL a los valores de llave externa en la tabla hijo. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.6 Integridad referencial. 3. SET DEFAULT. Borra la fila de la tabla padre y asigna a cada componente de la llave externa de la tabla hija el valor predeterminado especificado. 4. NO ACTION. Rechaza la operación de borrado de la tabla padre. Esta es la acción predeterminada si no se especifica ON DELETE.

Ejemplos: FOREING KEY (noPersonal) REFERENCES Personal ON DELETE SET NULL FOREING KEY (noPropietario) REFERENCES Propiedad ON UPDATE CASCADE Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.7 Los índices. Un índice para un archivo del sistema funciona como el índice de un libro. Si se va a buscar un tema (especificado por una palabra o una frase) en el libro, se puede buscar en el índice al final del libro, encontrar las páginas en las que aparece y después leer esas páginas para encontrar la información que estamos buscando. Los índices de los sistemas de bases de datos juegan el mismo papel que los índices de los libros o los catálogos de fichas de las bibliotecas. Por ejemplo, para recuperar un registro cuenta dado su número de cuenta, el sistema de bases de datos buscaría en un índice para encontrar el bloque de disco en que se encuentra el registro correspondiente, y entonces extraería ese bloque de disco para obtener el registro cuenta. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.8.7 Los índices. Hay dos tipos básicos de índices: Índices ordenados. Estos índices están basados en una disposición ordenada de los valores. Índices asociativos (hash indices). Estos índices están basados en una distribución uniforme de los valores a través de una serie de cajones (buckets). El valor asignado a cada cajón está determinado por una función, llamada función de asociación. Un archivo puede tener varios índices según diferentes claves de búsqueda. Si el archivo que contiene los registros está ordenado secuencialmente, el índice cuya clave de búsqueda especifica el orden secuencial del archivo es el índice primario. (El término índice primario se emplea algunas veces para hacer alusión a un índice según una clave primaria. Sin embargo, tal uso no es normal y debería evitarse.) Los índices primarios también se llaman índices con agrupación (clustering indices.) La clave de búsqueda de un índice primario es normalmente la clave primaria, aunque no es así necesariamente. Los índices cuyas claves de búsqueda especifican un orden diferente del orden secuencial del archivo se llaman índices secundarios o índicesUniversidad sin agrupación (non clustering indices ). del Mar 07/2008 Bases de Datos I MTI Remedios Fabián Velasco

6.8.8 Tipos de datos. Tipos de datos definidos en SQL: Booleano: BOOLEAN Carácter: CHAR | VARVHAR Bit: BIT | BIT VARYING Numérico exacto: NUMERIC | DECIMAL | INTEGER | SAMLLINT Numérico aproximado: FLOAT | REAL Fecha y Hora: DATE | TIME | TIMESTAMP Intervalo: INTERVAL

Definición de dominios: CREATE DOMAIN tipoSexo AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘M’,’F’)

Bases de Datos I

Se puede utilizar el nombre de dominio tipoSexo en lugar de char en CREATE TABLE Sexo tipoSexo NOTUniversidad NULL, del Mar 07/2008 MTI Remedios Fabián Velasco

Bibliografía utilizada Libros: Fundamentos de Bases de Datos. Abraham Silberschatz, Henry F. Korth, S. Sudarshan Manual MySQL incorporado en la aplicación

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco