Diseño e Implementación de Bases de datos Relacionales Conversión del MER a MR Trabajando con SQL. Profesor: Msc. MIGUEL ANGEL NIÑO ZAMBRANO

(1)

Profesor: Msc. MIGUEL ANGEL NIÑO ZAMBRANO

Capitulo 5

Diseño e Implementación de

Bases de datos Relacionales

Conversión del MER a MR Trabajando con SQL

(2)

Modelado de Bases de Datos – Diseño e Implementación de BD 5-2

Conversión del MER al MR

(3)

5-3 Especialización en Desarrollo de Soluciones Informáticas

1 - Diseño Lógico Estándar

Etapas de Diseño:

Esquema lógico Estándar: A partir del esquema conceptual (EER) y teniendo en cuenta los requisitos de proceso y entorno, se elabora un esquema lógico estándar - ELS , que se apoya en un modelo lógico estándar - MLS (MR) el cual será el mismo modelo de datos soportado por el SGBD que se vaya a utilizar, pero sin las restricciones ligadas a ningún producto comercial. Diseño lógico Específico: Con el ELS y teniendo en

cuenta el modelo lógico específico – MLE propio del SGBD (INGRES, SYBASE, DB2, ORACLE, INFORMIX, INTERBASE, etc.) se elabora un esquema lógico especifico ELE que será descrito en el lenguaje de definición de datos LDD del producto comercial que estemos utilizando.

Introducción al Diseño Lógico de BD

En el diseño lógico se deben coordinar exigencias casi siempre encontradas, como

son eliminar redundancias, conseguir la máxima simplicidad y evitar cargas

suplementarias de programación, obteniendo una estructura lógica adecuada que

venga a establecer el debido equilibrio entre las exigencias de los usuarios y la

eficiencia.

(4)

2 - Conversión del MER a MR

REGLAS DE TRANSFORMACION

GENERICAS:

Todo

tipo entidad

se convierte en una relación.

Todo Tipo relación

N:M

se convierte en una

relación.

Para todo tipo de interrelación

1:N

se realiza lo

que se denomina Propagación de Claves (regla

general), o bien se crea una nueva relación.

La perdida de

semántica

del la conversión se

puede integrar con la construcción de

restricciones del MR.

Reglas de Transformación de MER a MR

Estas reglas, deben aplicarse con cuidado, ya que no todas las interrelaciones tienen su

mejor solución aplicándolas. En ocasiones es necesario establecer otro tipo de manejo para

evitar ineficiencia en las tablas resultantes.

Para las claves Ajenas se maneja lo siguiente:

•La clave se coloca en la relación que tiene la cardinalidad de Interrelación (M) y viaja de la

relación que tiene la cardinalidad de Interrelación 1.

•Cuando es una cardinalidad de interrelación 1:1 se elige cualquiera de las claves, aunque

depende cual es la relación más importante en el modelo de datos.

(5)

3 - Reglas de Transformación

Específicas – Dominios y Atributos

E_CIVIL

ME/R

MR

DOMINIO E_CIVIL(‘S’,’C’,’V’,’D’).

En SQL2 es:

CRETAE DOMAIN Estados_Civiles AS CHAR(1)

CHECK (VALUE IN (‘S’,’C’,’V’,’D’))

TRANSFORMACION DE DOMINIOS:

Si es de valores que pueden cambiar en el tiempo, se crea una entidad

agregando un campo que se convierte en llave primaria de la misma (una

secuencia). En caso contrario, se convierte en una condición CHECK

(Restricción de Verificación).

ATRIBUTOS COMPUESTOS UNIVALUADO:

Se convierten en campos adicionales a la relación los componentes del

atributo.

ATRIBUTOS COMPUESTOS MULTIVALUADOS:

Se crea una nueva relación con los atributos correspondientes si es compuesto.

Se le adiciona un campo que es la llave primaria de la relación a la que

pertenece.

(6)

3 - Reglas de Transformación

Específicas - Entidades

ME/R

MR

PROFESOR(Cód_prof, Nombre, DNI, Dirección, _{Teléfono, Materia).}

En SQL2 es:

CRETAE TABLE Profesor( Cód_Profesor Códigos, Nombre Nombres NOT NULL, DNI DNIS NOT NULL, DIrección Lugares,

Telefono Nos_Telefonicos NOT NULL, Materia Materias NOT NULL,

PRIMARY KEY (Cód_Profesor), UNIQUE (DNI))

PROFESOR

Cód_prof Nombre DNI

Dirección Teléfono Materia

TRANSFORMACION DE ENTIDADES

Transformación de entidades:

Cada tipo entidad se convierte en un relación. La relación

toma el nombre del tipo entidad que proviene y no se pierde semántica.

Transformación de atributos de entidades: Cada atributo de una entidad se transforma en

una columna de la relación a la que ha dado lugar la entidad. Se debe tener en cuenta los

identificadores principales y los identificadores alternativos, éstos se manejan así:

•Atributos identificadores (AIP): Se convierten en Llave primaria en el LLS se

maneja con PRIMARY KEY.

•Atributos identificadores Alternativos (AIA): Se convierten el Llaves secundarias

se maneja con UNIQUE.

•Atributos no identificadores: Son columnas los cuales pueden tomar valores nulos a

no ser que se indique lo contrario NOT NULL.

ENTIDADES FUERTES:

Una entidad E con N atributos, se convierte en una relación E con N campos (Columnas).

ENTIDADES DEBILES:

Se crea una relación a la cual se le adicionan los campos que forman la llave primaria de la

entidad de la cual pertenece.

(7)

3 - Reglas de Transformación

Específicas – Interrelaciones (N:M)

MR

PROFESOR(Cód_prof). CURSO(Cód_Curso). RELPROFCURSOIMP(Cód_Curso, Cód_prof).

ME/R

PROFESOR

Cód_prof Imparte

CURSO

Cód_Curso 1:N 1:N N:M

TRANSFORMACION DE INTERRELACIONES N:M

Se transforma en una relación cuya clave primaria será los dos AIP de los tipo entidad que asocia. La semántica de las relaciones asociadas se debe almacenar adecuadamente. Las claves se convierten en claves

ajenas la cual el LLS (SQL2) maneja con la cláusula FOREING KEY. Además hay que tener en cuenta las

acciones a realizar en las modificaciones a los datos. (NO ACTION, SET NULL, SET DEFAULT, CASCADE)

En SQL2 la conversión de interrelación N:M sería: CREATE TABLE RELPROFCURSOIMP( Cód_prof Codigos_P,

Cod_Curso Codigos_C, ...,

PRIMARY KEY (Cód_prof,Cód_Curso),

FOREING KEY (Cód_prof) REFERENCES Profesor ON DELETE CASCADE ON UPDATE CASCADE,

FOREING KEY (Cód_Curso) REFERENCES Curso ON DELETE CASCADE ON UPDATE CASCADE)

La cardinalidad mínima se puede manejar con la implementación de restricciones como Ej. CREATE ASSERTION Profesor_Curso

CHECK NOT EXIST (SELECT COUNT(*) FROM RELPROFCURSOIMP GROUP BY COD_CURSO HAVING COUNT(*)>=4)

(8)

3 - Reglas de Transformación

Específicas – Interrelaciones (1:N)

MR

PROFESOR(Cód_prof, ..., Cod_Dep). DEPARTAMENTO(Cód_Dep, ...).

ME/R

PROFESOR Cód_prof Pertenece DEPARTAMENTO Cód_Dep 1:N 1:1 N:1 EJEMPLO DE SOLUCION 1

TRANSFORMACION DE INTERRELACIONES 1:N

Existen dos soluciones:

1. Propagar los AIP de tipo de entidad que tiene la cardinalidad máxima 1 a la que

tienen N, es decir en el sentido de la flecha, desapareciendo el nombre de la

interrelación.

2. Transformarlo en una relación, como si se tratara de una interrelación N:M. La

clave primaria de la relación creada es la clave de la tabla que le corresponde la

cardinalidad N.

En SQL2 la conversión de interrelación 1:N sería parecido a los caso anteriores,

dependiendo de la opción de conversión tomada.

(9)

3 - Reglas de Transformación

Específicas – Interrelaciones (1:N)

MR

Solución a: TEMA(Cód_Tema, ..., Cod_TemaSuperior).

(Borrado: puesta a nulos;Modificación: cascada)

Solución b:

TEMA(Cód_Tema, ...).

CONSTA(Cód_Tema, Cod_TemaSuperior...).

(Borrado y Modificación: cascada)

Nulos no permitidos para Cod_TemaSuperior

ME/R

TEMA Cód_Tema Consta N:1 EJEMPLO DE SOLUCION 2

TRANSFORMACION DE INTERRELACIONES 1:N

Existen dos soluciones:

1. Propagar los AIP de tipo de entidad que tiene la cardinalidad máxima 1 a la que

tienen N, es decir en el sentido de la flecha, desapareciendo el nombre de la

interrelación.

2. Transformarlo en una relación, como si se tratara de una interrelación N:M. La

clave primaria de la relación creada es la clave de la tabla que le corresponde la

cardinalidad N.

En SQL2 la conversión de interrelación 1:N sería parecido a los caso anteriores,

dependiendo de la opción de conversión tomada.

(10)

3 - Reglas de Transformación

Específicas – Interrelaciones (1:1)

MR

HOMBRE(Cód_hombre, ...). MUJER(Cód_mujer, ...). MATRIMONIO(Cód_hombre, Cód_mujer).

(UNIQUE, NOT NULL)

ME/R

0:1 HOMBRE Cód_hombre Matrimonio MUJER Cód_Mujer 0:1 1:1 PROFESOR Cód_prof Pertenece DEPTO Cód_depto 0:1 1:1 PROFESOR(Cód_prof, ...). DEPTO(Cód_depto, ..., Cód_prof).

ME/R

Ej. 1 Ej. 2

TRANSFORMACION DE INTERRELACIONES 1:1

Existen dos soluciones:

1. Propagar los AIP de cualquiera de los tipo entidad a su correspondiente en la

relación, desapareciendo el nombre de la interrelación.

2. Transformarlo en una relación, como si se tratara de una interrelación N:M,

dónde se trasladan los códigos de las entidades que intervienen en la relación.

Los criterios de aplicación de estos métodos dependen de cardinalidades mínimas y recoger

(11)

3 - Reglas de Transformación Específicas –

Atributos de Interrelaciones

MR

PROFESOR(Cód_prof). CURSO(Cód_Curso). RELPROFCURSOIMP(Cód_Curso, Cód_prof, Num_Horas).

ME/R

PROFESOR

Cód_prof Imparte

CURSO

Cód_Curso 1:N 1:N N:M Num_Horas

TRANSFORMACION DE ATRIBUTOS DE INTERRELACIONES

• Si la interrelación se transforma en una relación, todos sus atributos pasan a ser

columnas de la relación.

• En caso que se utilice la propagación de claves, sus atributos migran junto a la

clave a la relación que corresponda. Aunque es recomendable conformar una

relación para mantener la semántica.

(12)

3 - Reglas de Transformación

Específicas – Restricciones

Ej1.

CRETAE DOMAIN Números_Naturales AS CHAR(1)

CHECK (VALUE BETWEEN 0 AND 9 )

Ej2.

CRETAE DOMAIN Colores AS VARCHAR(8)

CHECK (VALUE IN (‘Amarillo’,’Azul’,’Rojo’))

Otra posibilidad es utilizar la cláusula CHECK: Ej.

CREATE TABLE Curso( Cód_Curso Cursos,

Nombre Nombres NOT NULL, Num_Horas Horas NOT NULL, Fecha_Inicio Fechas NOT NULL, Fecha_Fin Fechas NOT NULL PRIMARY KEY (Cód_Curso),

CHECK(Fecha_Inicio < Fecha_Fin ));

TRANSFORMACION DE RESTRICCIONES

• Si la interrelación se transforma en una relación, todos sus atributos pasan a ser

columnas de la relación.

• En caso que se utilice la propagación de claves, sus atributos migran junto a la

clave a la relación que corresponda. Aunque es recomendable conformar una

relación para mantener la semántica.

(13)

3 - Reglas de Transformación Específicas –

Dependencias de Identificación

CURSO Cod_Curso Pertenece EDICION Cod_Edición 1:N 1:1 N:1 ID identificador (Cód_Curso + Cod_Edición) EDICION(Cód_Edición, Cód_Curso...). CURSO(Cód_Curso, ...).

Clave Ajena: ON DELETE CASCADE ON UPDATE CASCADE

TRANSFORMACION DE DEPENDENCIAS DE IDENTIFICACION

Se realiza a través de Propagación de claves con manejo de integridad

referencial.

En SQL92:

CREATE TABLE Curso(

Cód_Curso Codigos_C,

...,

PRIMARY KEY (Cód_Curso));

CREATE TABLE Edición(

Cód_Curso Codigos_C,

Cód_Edición Codigos_E,

...,

PRIMARY KEY (Cód_Curso, Cód_Edición),

FOREING KEY (Cód_Edición) REFERENCES Curso ON DELETE CASCADE

ON UPDATE CASCADE);

(14)

3 - Reglas de Transformación

Específicas – MER Extendido

AGREGACION:

Se toma la llave primaria de las entidades compuestas y la llave primaria de la relación de la entidades componentes y se agregan a una nueva relación. GENERALIZACIÓN:

Si es Solapada y Parcial: Se crea una nueva relación para el supertipo y su llave primaria para a los subtipos. Si es total: Se crean relaciones de las entidades suptipo, donde cada una tiene los atributos del supertipo. No se crea una relación para el supertipo.

AGREGACION:

Se toma la llave primaria de las entidades compuestas y la llave primaria de la relación de la entidades componentes y se agregan a una nueva relación. INTERRELACIONES RECURSIVAS:

Se adiciona un nuevo campo con la llave primaria de la entidad que participa en la interrelación. Se debe de cambiar el nombre del campo de acuerdo al rol correspondiente.

INTERRELACIONES N-ARIAS:

Se crea una nueva relación con el nombre de la interrelación con todas las llaves primarias de todas las entidades participantes y los atributos de la interrelación.

TRANSFORMACION MER EXTENDIDO

Las demás restricciones del MER Extendido, como la Inclusión y la Exclusión

se maneja con restricciones a nivel de Aserciones o Disparadores que

controlen la semántica colocada.

(15)

Trabajando con SQL

(16)

Definición

Es un lenguaje de BD, el cual permite

definir, manipular y obtener datos de

manera sencilla a través de consultas

etructuradas fáciles de utilizar.

El Objetivo principal de SQL es la

realización de consultas y cálculos con los

datos de una o varias tablas.

Esta compuesto por un conjunto reducido

de clausulas, pero muy potentes.

Es un estándar extendido en muchos

(17)

Características Generales

Posibilidad de recuperar

conjuntos de datos con diversas

operaciones.

Lenguaje declarativo

Necesidad de Optimizar las

consultas.

Fácil de aprender y utilizar.

Características de SQL

•El SQL es un lenguaje de acceso a bases de datos que explota la flexibilidad y

potencia de los sistemas relacionales permitiendo gran variedad de operaciones

sobre los mismos.

•Es un lenguaje declarativo de "alto nivel" o "de no procedimiento“. permitiendo

una alta productividad en codificación y la orientación a objetos.

•El orden de ejecución interno de una sentencia puede afectar gravemente a la

eficiencia del SGBD, por lo que se hace necesario que éste lleve a cabo una

optimización antes de la ejecución de la misma.

(18)

Historia

ISO/IEC 9075-14:2006 Define las maneras en las cuales el SQL se puede utilizar conjuntamente con XML. SQL-2006

2006

Introduce algunas características de XML, cambios en las funciones, estandarización del objeto sequence y de las columnas auto numéricas.

SQL-2003 2003

Se agregaron expresiones regulares, consultas recursivas (para relaciones jerárquicas), triggers y algunas características orientadas a objetos. SQL-1999 o SQL3 1999 Revisión mayor SQL-92 o SQL2 1992 Revisión menor. SQL-89 1989

Primera publicación hecha por ANSI. Confirmada por ISOen 1987. SQL-86 o SQL1 1986 Comentarios Nombre Año Definición de SQL

El Lenguaje de consulta estructurado (SQL en inglés Structured Query Language) es un lenguaje declarativode acceso a bases de datosrelacionales que permite especificar diversos tipos de operaciones sobre las mismas. Una de sus características es el manejo del álgebra y el cálculo relacional permitiendo lanzar

consultascon el fin de recuperar -de una forma sencilla-informaciónde interés de una base de datos, así como también hacer cambios sobre la misma. Es un lenguajede cuarta generación (4GL).

Orígenes y Evolución

Los orígenes del SQL están ligados a los de las bases de datos relacionales. En 1970 E. F. Coddpropone el

modelo relacionaly asociado a este un sublenguaje de acceso a los datosbasado en el cálculo de predicados. Basándose en estas ideas, los laboratorios de IBMdefinen el lenguaje SEQUEL(Structured English QUEry Language) que más tarde sería ampliamente implementado por el SGBD (Sistemas Gestores de Bases de Datos) experimental System R, desarrollado en 1977 también por IBM. Sin embargo, fue Oracle quien lo introdujo por primera vez en 1979en un programa comercial.

El SEQUEL terminaría siendo el predecesor de SQL, siendo éste una versión evolucionada del primero. El SQL pasa a ser el lenguaje por excelencia de los diversos SGBDrelacionales surgidos en los años siguientes y es por fin estandarizado en 1986por el ANSI, dando lugar a la primera versión estándar de este lenguaje, el "SQL-86" o "SQL1". Al año siguiente este estándar es también adoptado por la ISO.

Sin embargo este primer estándar no cubre todas las necesidades de los desarrolladores e incluye funcionalidades de definición de almacenamiento que se consideraron suprimir. Así que en 1992se lanza un nuevo estándar ampliado y revisado del SQL llamado "SQL-92" o "SQL2".

En la actualidad el SQL es el estándar de facto de la inmensa mayoría de los SGBD comerciales. Y, aunque la diversidad de añadidos particulares que incluyen las distintas implementaciones comerciales del lenguaje es amplia, el soporte al estándar SQL-92 es general y muy amplio.

El ANSI SQL sufrió varias revisiones y agregados a lo largo del tiempo (ver tabla de la diapositiva) * Tomado de:Wikipedia (http://es.wikipedia.org/wiki/Celda_activa).

(19)

Comandos SQL

Cláusulas DLL DML

Comandos SQL

Existen dos tipos de comandos SQL:

•Los DLL que permiten crear y definir nuevas bases de datos, campos e índices.

•Los DML que permiten generar consultas para ordenar, filtrar y extraer datos de la base de

datos.

Cláusulas

Las cláusulas son condiciones de modificación utilizadas para definir los datos que desea

seleccionar o manipular.

(20)

Comandos SQL

Operadores lógicos

Operadores de Información

Comandos SQL

(21)

Comandos SQL

Funciones de Agregado Predicados

Comandos SQL

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.

Consultas con Predicados

(22)

Creación de consultas simples

Proyección (SELECT).

Pertenencia (FROM)

Selección (WHERE)

Comparación de cadenas (LIKE)

Comparación de fechas y horas

(DATE ‘1948-05-14’, TIME ’15:00:02.5’)

Ordenación de la salida (ORDER BY

<lista_de_atributos>).

Consultas Simples (seleccionar de donde).

Esta es la forma en que se presentan las consultas de SQL, nos permite tomar el producto de

varias relaciones (la cláusula FROM), aplicar una condición a las tuplas del resultado (la

cláusula WHERE) y producir componentes deseados (cláusula SELECT).

(23)

Creación de consultas simples

- Ejemplos

Consultas Básicas

SELECT Nombre, Telefono FROM Clientes;

Devolver Literales

SELECT Empleados.Nombre, 'Tarifa semanal: ', Empleados.TarifaHora * 40 FROM Empleados

WHERE Empleados.Cargo = 'Electricista‘;

Ordenar los Registros

SELECT CodigoPostal, Nombre, Telefono FROM Clientes

ORDER BY Nombre;

SELECT CodigoPostal, Nombre, Telefono FROM Clientes

ORDER BY CodigoPostal, Nombre; SELECT CodigoPostal, Nombre, Telefono FROM Clientes

ORDER BY CodigoPostal DESC , Nombre ASC;

Devolver Literales

En determinadas ocasiones nos puede interesar incluir una columna con un texto fijo en una

consulta de selección, por ejemplo, supongamos que tenemos una tabla de empleados y

deseamos recuperar las tarifas semanales de los electricistas.

Ordenar Registros

Adicionalmente se puede especificar el orden en que se desean recuperar los registros de las

tablas mediante la claúsula ORDER BY Lista de Campos. En donde Lista de campos

representa los campos a ordenar.

(24)

Creación de consultas simples

- Ejemplos

SELECT ALL

FROM Empleados;

SELECT *

FROM Empleados;

SELECT TOP 25 Nombre, Apellido

FROM Estudiantes

ORDER BY Nota DESC;

SELECT DISTINCT Apellido

FROM Empleados;

SELECT DISTINCTROW Apellido

FROM Empleados;

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. No se 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.

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. Si no se incluye la cláusula ORDER BY, la consulta devolverá un conjunto arbitrario de 25 registros de la tabla 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. (ver ejemplo)

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.

DISTINCTROW

Este predicado no es compatible con ANSI. Devuelve los registros diferentes de una tabla; a diferencia del predicado anterior que sólo se fijaba en el contenido de los campos seleccionados, éste lo hace en el contenido del registro completo independientemente de los campo indicados en la cláusula SELECT. Si la tabla empleados contiene dos registros: Antonio López y Marta López, el ejemplo del predicado DISTINCT devuleve un único registro con el valor López en el campo Apellido ya que busca no duplicados en dicho campo. Este último ejemplo devuelve dos registros con el valor López en el apellido ya que se buscan no duplicados en el registro completo.

(25)

Creación de consultas simples

- Ejemplos

SELECT DISTINCTROW Apellido AS Empleado

FROM Empleados;

SELECT Apellido AS "Empleado"

FROM Empleados;

SELECT Apellido AS Empleado

FROM Empleados AS Trabajadores;

SELECT Trabajadores.Apellido AS Empleado

FROM Empleados Trabajadores;

SELECT Apellido

FROM Servidor1.BaseDatos1.dbo.Empleados

Alias

En determinadas circunstancias es necesario asignar un nombre a alguna columna determinada de un conjunto devuelto, otras veces por simple capricho o por otras circunstancias. Para resolver todas ellas tenemos la palabra reservada AS que se encarga de asignar el nombre que deseamos a la columna deseada. Tomado como referencia el ejemplo anterior podemos hacer que la columna devuelta por la consulta, en lugar de llamarse apellido (igual que el campo devuelto) se llame Empleado.

AS no es una palabra reservada de ANSI, existen diferentes sistemas de asignar los alias en función del motor de bases de datos. En ORACLE para asignar un alias a un campo hay que hacerlo con comillas dobles. También podemos asignar alias a las tablas dentro de la consulta de selección, en esta caso hay que tener en

cuenta que en todas las referencias que deseemos hacer a dicha tabla se ha de utilizar el alias en lugar del nombre. Esta técnica será de gran utilidad más adelante cuando se estudien las vinculaciones entre tablas. Para asignar alias a las tablas en ORACLE y SQL-SERVER los alias se asignan escribiendo el nombre de la

tabla, dejando un espacio en blanco y escribiendo el Alias (se asignan dentro de la cláusula FROM). Esta nomenclatura [Tabla].[Campo] se debe utilizar cuando se está recuperando un campo cuyo nombre se

repite en varias de las tablas que se utilizan en la sentencia. No obstante cuando en la sentencia se emplean varias tablas es aconsejable utilizar esta nomenclatura para evitar el trabajo que supone al motor de datos averiguar en que tabla está cada uno de los campos indicados en la cláusula SELECT.

en otros sistemas como SQL-SERVER u ORACLE, la cosa es más complicada la tener que existir relaciones de confianza entre los servidores o al ser necesaria la vinculación entre las bases de datos.

(26)

Creación de consultas simples

- Ejemplos

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 = 'Madrid' AND Estado = 'Casado');

Operadores Lógicos

(27)

Creación de consultas simples

- Ejemplos

SELECT * FROM Pedidos WHERE

CodPostal Between 28000 And

28999;

expresión Like modelo.

(28)

Consultas de mas de una

relación.

Productos y vínculos en SQL

(SELECT ... FROM ... WHERE

Campo1 = Campo2 ...)

Eliminación de las ambigüedades en

los atributos (TABLA.CAMPO)

Variables Tuplas (FROM R AS R).

Unión, Intersección y Diferencia de

las consultas (Compatibilidad de

unión. UNION, INTERSECT, EXCEPT)

(29)

Consultas de mas de una

relación - Ejemplos

SELECT Nombre_Categoría,

NombreProducto

FROM Categorias INNER JOIN Productos

ON Categorias.IDCategoria =

Productos.IDCategoria;

SELECT Facturas., Albaranes. FROM

Facturas INNER JOIN Albaranes ON

Facturas.IdAlbaran = Albaranes.IdAlbaran

WHERE Facturas.IdCliente = 325;

(30)

SubConsultas

Subconsultas que producen valores escalares. (SELECT ... FROM ... WHERE

Campo <operador> (SELECT ... FROM... WHERE...);

Condiciones en que intervienen las relaciones. (Sea R una relación y sea s un valor escalar entonces: EXIST R, s IN R, s > ALL, s > ANY, NOT).

Condiciones en las que intervienen las tuplas. (Sea R una relación y sea t una tupla

entonces: (t1,t2,...) IN R, (t1,t2,...) > ALL, (t1,t2,...) > ANY, NOT).

Subconsultas correlacionadas. (SELECT ... FROM R as RS WHERE T.campo

<operador> (SELECT … FROM T WHERE T.Campo2 <operador> RS.Campo2);.

Subconsultas

Una subconsulta es una instrucción SELECT anidada dentro de una instrucción SELECT, SELECT...INTO, INSERT...INTO, DELETE, o UPDATE o dentro de otra subconsulta.

Puede utilizar tres formas de sintaxis para crear una subconsulta:

comparación [ANY | ALL | SOME] (instrucción sql) expresión [NOT] IN (instrucción sql) [NOT] EXISTS (instrucción sql) En donde:

comparación

•Es una expresión y un operador de comparación que compara la expresión con el resultado de la subconsulta. expresión

•Es una expresión por la que se busca el conjunto resultante de la subconsulta. instrucción sql

•Es una instrucción SELECT, que sigue el mismo formato y reglas que cualquier otra instrucción SELECT. Debe ir entre paréntesis.

Se puede utilizar una subconsulta en lugar de una expresión en la lista de campos de una instrucción SELECT o en una cláusula WHERE o HAVING. En una subconsulta, se utiliza una instrucción SELECT para proporcionar un conjunto de uno o más valores especificados para evaluar en la expresión de la cláusula WHERE o HAVING.

Se puede utilizar el predicado ANY o SOME, los cuales son sinónimos, para recuperar registros de la consulta principal, que satisfagan la comparación con cualquier otro registro recuperado en la subconsulta. El ejemplo siguiente devuelve todos los productos cuyo precio unitario es mayor que el de cualquier producto vendido con un descuento igual o mayor al 25 por ciento:

(31)

Subconsultas - Ejemplos

SELECT *

FROM Productos

WHERE PrecioUnidad > ANY (SELECT PrecioUnidad FROM DetallePedido

WHERE Descuento >= 0 .25); SELECT *

FROM Productos

WHERE IDProducto IN (SELECT IDProducto FROM DetallePedido

WHERE Descuento >= 0.25);

SELECT Clientes.Compañía, Clientes.Teléfono FROM Clientes

WHERE EXISTS (SELECT FROM Pedidos

WHERE Pedidos.IdPedido = Clientes.IdCliente)

Ejemplos de Subconsultas

Se puede utilizar el predicado ANY o SOME, los cuales son sinónimos, para recuperar registros de la consulta principal, que satisfagan la comparación con cualquier otro registro recuperado en la subconsulta. El ejemplo siguiente devuelve todos los productos cuyo precio unitario es mayor que el de cualquier producto vendido con un descuento igual o mayor al 25 por ciento.

El predicado ALL se utiliza para recuperar únicamente aquellos registros de la consulta principal que satisfacen la comparación con todos los registros recuperados en la subconsulta. Si se cambia ANY por ALL en el ejemplo anterior, la consulta devolverá únicamente aquellos productos cuyo precio unitario sea mayor que el de todos los productos vendidos con un descuento igual o mayor al 25 por ciento. Esto es mucho más restrictivo.

El predicado IN se emplea para recuperar únicamente aquellos registros de la consulta principal para los que algunos registros de la subconsulta contienen un valor igual. El ejemplo siguiente devuelve todos los productos vendidos con un descuento igual o mayor al 25 por ciento:

Inversamente se puede utilizar NOT IN para recuperar únicamente aquellos registros de la consulta principal para los que no hay ningún registro de la subconsulta que contenga un valor igual.

El predicado EXISTS (con la palabra reservada NOT opcional) se utiliza en comparaciones de verdad/falso para determinar si la subconsulta devuelve algún registro. Supongamos que deseamos recuperar todos aquellos clientes que hayan realizado al menos un pedido:

(32)

Otros Operadores

Manejo de Duplicados.

(DISCTINCT, UNION ALL,

INTERSECT ALL, EXCEPT ALL).

Funciones de Agregación

(SUM, AVG, MIN, MAX, COUNT

[DISCTINCT], GROUP BY, HAVING)

(33)

Otros Operadores - Ejemplos

SELECT Id_Familia, Sum(Stock)

FROM Productos GROUP BY Id_Familia;

SELECT Id_Familia Sum(Stock) FROM Productos

GROUP BY Id_Familia

HAVING Sum(Stock) > 100 AND NombreProducto Like BOS*; SELECT Avg(Gastos) AS Promedio

FROM Pedidos

WHERE Gastos > 100; SELECT Count(*) AS Total

FROM Pedidos;

SELECT Count(DISTINCT Localidad) AS Total FROM Pedidos;

(34)

Otros Operadores - Ejemplos

SELECT Min(Gastos) AS ElMin

FROM Pedidos

WHERE Pais = 'España';

SELECT Max(Gastos) AS ElMax

FROM Pedidos

WHERE Pais = 'España';

SELECT Sum(PrecioUnidad * Cantidad) AS

Total

FROM DetallePedido;

(35)

Modificación de la Base de

Datos.

Inserción (INSERT INTO R (A1,A2, ..)

VALUES (v1,v2, ..))

Eliminación (DELETE FROM R

WHERE <condición>)

Actualizaciones (UPDATE R SET

<campo = v1, campo=valor2, ....>

WHERE <condición>)

(36)

Definición de un Esquema

Relacional en SQL

Tipos de Datos. (CHAR(n), VARCHAR(n), BIT(n), BIT VARING(n), FLOAT o REAL, DOUBLE PRECISION, DECIMAL(n,d), DATE, TIME) Declaraciones Simples de Tablas. (CREATE TABLE R (Campo1

TIPO, Campo2 TIPO, ...);.

Eliminación de Tablas. (DROP R;).

Modificación de los esquemas relaciónales. (ALTER TABLE R [ADD/DROP] Campo TIPO).

Valores por omisión (predeterminados) (Campo TIPO DEFAULT ‘valor’).

Dominios. (CREATE DOMAIN <nombre> AS <descripción del tipo> [DEFAUL/CHECK]), (ALTER DOMAIN D SET DEFAULT ‘valor’), (DROP DOMAIN D;).

Índices. (CREATE INDEX I ON R(Campo1[,Campo2, ...] );), (DROP INDEX I).

(37)

Definición de Vistas

Declaración de Vistas. (CREATE VIEW V

AS <Definición de la vista>).

Cambio de Nombre (renombramiento) de

los atributos. (CREATE VIEW V

(Nuevo1,Nuevo2, ...) AS <Definición de la

vista>).

Modificación de las Vistas (Sólo en las

(38)

Manejo de Nulos (NULL)

TRUE UNKNOWN FALSE UNKNOWN FALSE UNKNOWN UNKNOWN FALSE FALSE UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN TRUE UNKNOWN TRUE UNKNOWN FALSE TRUE UNKNOWN UNKNOWN TRUE NOT X X OR Y X AND Y Y X

Hay dos reglas importantes a tener en cuenta:

•Cuando operamos sobre un valor NULL y cualquier otro, entre ellos otro NULL usando un operador aritmético como * o +, el resultado será NULL. •Cuando operamos sobre un valor NULL y cualquier otro, entre ellos otro NULL usando un operador de comparación = o >, el resultado es UNKNOWN, el cual es otro valor de verdad como TRUE o FALSE.

Operaciones sobre valores nulos

NULL esta especialmente diseñado para representar un valor desconocido o inexistente. Las

reuniones externas son otra fuente de valores NULL.

(39)

Reuniones de Información

Expresiones de reunión del lenguaje

SQL2. (R CROOS JOIN T, R JOIN T

ON <exppresión similar al WHERE>)

Reuniones Naturales. (R NATURAL

JOIN T)

Reuniones Externas. (LEFT OUTER

JOIN, RIGHT OUTER JOIN, FULL

OUTER JOIN)

(40)

Restricciones y Disparadores

Llaves en SQL. (PRIMARY KEY). Si es una sola llave

se puede definir en la misma línea de definición del

campo Ej. Nombre CHAR(30) PRIMARY KEY, . Si son

uno o mas atributos la llave se puede definir a nivel de la

tabla. Ej. PRIMARY KEY (titulo, año). Otra forma es con

la palabra UNIQUE. A diferencia de PRIMARY KEY

pueden existir en una tabla varios atributos únicos. Otra

variante del uso de UNIQUE sería. Ej. CREATE UNIQUE

INDEX YearIndex ON Movie(year).

Integridad Referencial y Llaves Exteriores. (una forma

es usar REFERENCES R(atributo), otra es anexar a la

lista de atributos de la definición de la tabla FOREING

KEY <atributos> REFERENCES R(atributos).).

(41)

Restricciones y Disparadores

Mantenimiento de la Integridad Referencial. (Política por omisión:

SQL rechaza toda actualización que viole la restricción de integridad referencial. Política en Cascada: Se manejan las eliminaciones o actualizaciones en las relaciones referenciadas. Política de Asignar valor nulo: Coloca nulos a las relaciones referenciadas). Estas se manejan en las definiciones de las llaves externas con (ON DELETE, ON UPDATE, SET NULL o CASCADE).

Restricciones de los valores de atributos. (Se pueden hacer de dos formas: Una restricción del atributo en la definición de su esquema relacional o una restricción de dominio, que se declara después del dominio del atributo en cuestión.).

Restricciones no nulas. (NOT NULL).

Restricciones CHECK basadas en atributos. (CHECK(igual a una condición

WHERE).

Restricción de Dominios. (CREATE DOMAIN D TIPO CHECK(VALUE IN

(42)

Restricciones y Disparadores

Restricciones Globales. (Son restricciones más complejas que

se refieren a las relaciones entre varios atributos o incluso a relaciones diversas).

Restricciones CHECK Basadas en Tuplas. (Se crea como otro

atributo de la relación: Ej. CHECK( gender = ‘F’ OR name NOT LIKE ‘Ms.%’).