PDF de programación - Tema 8 SQL (cont.) ODBC

1.264 Tema 8

SQL (cont.)

ODBC

Subconsultas

• Las subconsultas de SQL nos permiten

utilizar los resultados de una consulta
como parte de otra. Subconsultas:
– A menudo, constituyen un modo natural

de escribir una instrucción.

– Permiten dividir una consulta en partes y

reconstruirla.

– Permite realizar algunas consultas que de

otro modo no se podrían llevar a cabo.

Subconsultas

• Obtener un listado de las oficinas cuyo cupo

de ventas excede la suma de los cupos
individuales de los vendedores:
– SELECT Ciudad FROM Oficinas WHERE Objetivo >

???

– ??? es “la suma de los cupos de los vendedores"

o:
• SELECT SUM(Cupo) FROM RepVentas WHERE

OfiRep = NumOfi

• Al combnarlas se obtiene lo siguiente:

– SELECT Ciudad FROM Oficinas WHERE Objetivo >

(SELECT SUM(Cupo) FROM RepVentas WHERE

OfiRep = NumOfi);

Subconsultas

• Las subconsultas siempre figuran como parte de

las cláusulas WHERE (o HAVING).

• Una subconsulta sólo puede dar lugar a una columna

de datos como resultado:
– En la subconsulta SELECT sólo puede haber un campo.

• No permite ORDER BY; no tendría sentido.
• Tampoco admite UNION; sólo se permite un SELECT.
• A menudo, hace referencia al nombre de una columna

de una tabla principal de la subconsulta:
– Esto determina la fila de la tabla principal para la que se está

ejecutando la subconsulta. Se conoce como referencia exterior.

– En nuestro ejemplo, es OfiRep= NumOfi de la tabla Oficinas.

• NO hay distintas copias de los datos; hacen

referencia a los datos de las tablas subyacentes.

• La base de datos almacena la definición de las

vistas. Los datos se actualizan cuando se
actualizan las tablas subyacentes.

• Ventajas:

Vistas

• Tablas virtuales que muestran los datos a los

usuarios de forma no normalizada.

– Diseñadas para cubrir necesidades específicas de los usuarios.
– Las consultas son mucho más simples para los usuarios en

vistas diseñadas para ellos.

– Seguridad: sólo permite acceder a los datos de las vistas.
– Independencia: evita que el usuario o el programa modifiquen

las tablas subyacentes.

Vistas

• CREATE VIEW PedidosClientes AS SELECT

NumClien, Empresa, Nombre, NumPedido, Prod,
Cant, Im porte FROM Clientes, RepVentas,
Pedidos WHERE RepClien = NumRep AND
(SQL estándar)
NumClien = Clien

NumPedido Clien

Pedidos

Cant

Prod
211 ABAC
522 CDE

88
99

Importe
7 31.000$
4.000$
2

Clientes

NumClien Empresa RepClien LimitCredit
89 50.000$
89 40.000$

211 QGG Co
322 DBO Co

PedidosClientes

NumClien Empresa Nombre NumPedido Prod Cant Importe
7 31.000$
2 4.000$

211 QGC Co
322 DBO Co

Jen Smith
Jen Smith

88 ABAC
99 CDE

RepVentas
NumRep Nombre OfiRep

53 Bill Smith
89 Jen Smith

Cupo
Ventas
0$
22 100.000$
44 50.000$ 130.000$

Detalles de las vistas

• Posibilidad de modificar las vistas e invalidarlas:

– P. ej.: vista de libros de menos de 5 dólares.
– ¿Qué sucede si actualizamos el precio de un libro

a 5,99 dólares a través de la vista? ¡Desaparece!
– Esto se evita añadiendo: WITH CHECK OPTION

• No se pueden actualizar todas las vistas. Una

vista es de sólo lectura si:
– DISTINCT forma parte de la instrucción SELECT.
– Contiene expresiones (medias, totales, etc.).
– Hace referencia a vistas que no son actualizables.
– Contiene claúsulas GROUP BY o HAVING.
– En ocasiones: hace referencia a más de una tabla

(fracaso del objetivo).

Vistas en MS Access

• Las vistas se construyen y almacenan como

consultas:
– Construir la instrucción SELECT, incluyendo las

relaciones (JOINS) entre las tablas en caso necesario.

– Guardar la consulta, dándole un nombre.
– A continuación, se puede realizar una consulta sobre
la consulta anterior (vista), así como utilizarla como
fuente de datos de páginas Web, a modo de tabla.
– Utilizaremos las vistas (consultas) en FrontPage,
dado que presenta algunos errores con respecto a
las relaciones (JOINS).

Ejercicios de repaso

• Mostrar todos los clientes con pedidos o

límites de crédito > 50.000 dólares.
– ¿Es posible listar al cliente una sola vez?

• Eliminar a los representantes de las oficinas
de ventas de Nueva York (NY) con cupos de
más de 40.000 dólares.
– Consejo: recuerde que debe eliminarlos a partir de

(FROM) una sola tabla. Utilice una subconsulta.

Soluciones

• Mostrar todos los clientes con pedidos o límites de

crédito > 50.000 dólares:
– ¿Es posible listar al cliente una sola vez, con los límites de
crédito y los importes de los pedidos? Sí, pero no es fácil.

– SELECT DISTINCT NumClien FROM Clientes, Pedidos
WHERE NumClien = Clien AND (LimitCredit>50000 OR
Importe>50000);

• Eliminar a los representantes de las oficinas de ventas

de Nueva York (NY) con cupos de más de 40.000$:
– Consejo: recuerde que debe eliminarlos a partir de (FROM)

una sola tabla. Utilice una subconsulta.

– DELETE * FROM RepVentas WHERE NumRep IN

(SELECT NumRep FROM RepVentas, Oficinas WHERE
NumOfi = OfiRep AND Cupo>40000 AND
Estado="NY“);

Índices

• Un índice es un objeto de datos independiente presente
en la base de datos que ordena las filas de la tabla para
facilitar una vista rápida:
– Cada índice de cada tabla es un objeto independiente.
– Las claves primarias y secundarias se indexan automáticamente.

• Acceso rápido a columnas indexadas:

– Cada vez que se actualiza una fila, se actualiza el índice, por lo que el uso

de índices ralentiza las operaciones de modificación, inserción y eliminación.

– En la práctica, se deben utilizar un máximo de 3 ó 4 índices por tabla. En
caso de necesitar más, se deben añadir y eliminar según sea necesario.

– Si la base de datos se usa, en su mayoría, para consulta (lectura), es
recomendable utilizar muchos índices, pues se agiliza el rendimiento.

– Si la base de datos se actualiza con frecuencia, conviene añadir el menor

número de índices posible.

• Índices agrupados (clustered):

– Ordenan las filas físicamente según un solo índice con el fin de

maximizar la velocidad de acceso al disco.

Ejemplo de índices

• Base de datos de clientes:

– El ID de cliente es clave primaria.
– También queremos realizar búsquedas por:

• Nombre del cliente (último, primero).
• Ciudad/Estado.
• Código postal.
• Dirección.

– Indexar los campos nombre, ciudad/estado, CP

y dirección:
• Cuatro índices: ralentiza la inserción, actualización y eliminación

de datos, pero agiliza la consulta de la base de datos.

• Si la base de datos de clientes es bastante estable, está bien.
– Seguir la misma lógica para el catálogo de piezas,
• Los motores de búsqueda de Internet utilizan

la facturación de materiales, etc.

"motores de recuperación de texto":
– Indexan todas las palabras de la base de datos; cuentan

las coincidencias y filas iguales. Los últimos avances
(frecuencia de los enlaces, uso…) posibilitan esto.

• Opciones de seguridad:

Seguridad

– Utilizar el login/contraseña del sistema operativo para identificar

al usuario (menor seguridad):

• El usuario puede acceder a todas las bases de datos y a todas las tablas.
– Utilizar un login/contraseña para la base de datos (mayor seguridad):

• Se restringe el acceso a otras bases de datos y tablas, pero permite
– Seguridad a nivel de aplicación (aún más seguro, pero difícil de

utilizar todas las aplicaciones.

administrar):

• Las aplicaciones deben consultar una base de datos común para ver si el
– Seguridad a nivel de red (lo más seguro, apenas viable hoy en día):

usuario está o no autorizado; es más centralizado que de BD a BD.

• Utiliza una infraestructura de clave pública (PKI) y de directorios, que

consiste en la encriptación.

• Es un elemento de vanguardia: caro y complicado.

• Clases de usuarios: superusuario (DBA), propietario

y usuario.

• Asignación de privilegios sobre la base de datos (permisos):

– GRANT y REVOKE. P. ej.:

• GRANT ALL ON NombreTabla TO PUBLIC WITH GRANT OPTION
• En las instrucciones GRANT y REVOKE es importante el orden.

El último es el que domina.
– MS Access no soporta esto.

Transacciones

• Un grupo de operaciones se debe tratar a

menudo como una unidad atómica:
– Iniciar la transacción:

• Insertar CabeceraPedido.
• Mientras hay otros DetallesPedido (elementos de la línea):

– Seleccionar la pieza.
– Actualizar el inventario de las piezas.
– Insertar la fila DetallesPedido.

– Completar la transacción.

• Propiedades de la transacción (ACID):
– A – atómica- todo o nada (recuperación).
– C – coherencia – de un estado de coherencia a otro

(integridad).

– I – aislamiento – no permite que otra transacción

visualice los cambios no realizados (concurrencia).

– D – perdurable – una vez realizados, los cambios son

permanentes.

Transacciones

• Las bases de datos multiusuario tienen otros

problemas relacionados con las transacciones.

• Dos acciones de la base de datos entran en

conflicto si una o ambas son operaciones escritas.
– Actualizaciones perdidas:
• Hay 7 piezas en el inventario.
• Las transacciones 1 y 2 leen simultáneamente 7 como la

• La transacción 1 acaba primero, añade 3 piezas y escribe

• La transacción 2 acaba después, elimina 5 piezas y escribe

cantidad actual.

10 en la cantidad.

2 en la cantidad.

– Cambios no realizados:

• La transacción 1 añade 3 piezas y escribe 10 en la cantidad.
• La transacción 2 lee 10 como cantidad.
• La transacción 1 se cancela (vuelve atrás) y deja a la

transacción 2 con datos erróneos.

Transacciones

• Las bases de datos utilizan protecciones para las

concurrencias. Un esquema sencillo es el siguiente:
– Las escrituras obtienen protección exclusiva sobre un registro,

evitando otras lecturas o escrituras.

– Las lecturas obtienen protecciones no exclusivas, que permiten otras

lecturas, pero evitan que una escritura obtenga protección exclusiva.

• Las bases de datos utilizan archivos log para la

recuperación:
– Además de realizarse todos los cambios en la base d