OBJETIVOS:

Jim Melton, Alan R.<br><br> Simon, Morgan Kaufmann Publisher, 1993. A Guide to SQL Standard (fourth edition). Date, C.J.<br><br> and Darwen, H. Addison-Wesley, 1997. SQL.<br><br> Manual de Referencia del Programador. Freeze, W.S. Paraninfo, 1998.<br><br> DEP. TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 3 Definición de Datos SQL: El lenguaje de definición de datos (DDL) El lenguaje de definición de datos permite: " Definir y crear una nueva tabla.<br><br> " Suprimir una tabla que ya no se necesita. " Cambiar la definición de una tabla existente. " Definir una tabla virtual (o vista) de datos.<br><br> " Construir un índice para hacer más rápido el acceso a una tabla. " Controlar el almacenamiento físico de los datos por parte del SGBD. Sentencias sobre Tablas Sintaxis General de la sentencia CREATE TABLE: CREATE TABLE <nombre de tabla> (nombre_columna1 tipo [ restricción de columna ], ........<br><br> nombre_columnaN tipo [ restricción de columna ], [ restricción_de_tabla ]); La sentencia CREATE TABLE se utiliza para crear una tabla dentro de la cual habrá columnas que contienen datos y restricciones DEP. TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 4 Más en detalle& CREATE TABLE <nombre tabla> ( <nombre columna> <tipo de dato> [NOT NULL] [UNIQUE] [CONSTRAINT <nombre restricción>][PRIMARY KEY] [REFERENCES][DEFAULT][CHECK] | [PRIMARY KEY (<lista columnas>)] | [FOREIGN KEY (<lista columnas>) REFERENCES (nombretabla)] | [UNIQUE (<lista columnas>)] [CONSTRAINT <nombre restricción>],[,...] ) | [CHECK (condición de búsqueda)] Tipos de datos Tipo de dato Descripción Char(tamaño) Almacena datos de tipo carácter de longitud fija, con un máximo de 2000 caracteres) varchar2(tamaño) Almacena datos de tipo carácter de longitud variable, con un tamaño máximo de 4000 Varchar Actualmente es igual que char Long Almacena datos de tipo carácter de longitud variable, hasta 2 gigabytes.<br><br> Solo se permite un Long por tabla. Una columna de tipo Long no puede utilizarse como parte de un índice. Una función almacenada no puede devolver un Long.<br><br> Las cláusulas Where, Group By, Order By, Unique, o Connect By no pueden referenciar a una columna Long. Blob Es un objeto binario de gran tamaño, siendo el tamaño máximo 4 GB (gigabytes). Normalmente un blob se utiliza para almacenar una imagen, datos de voz, o cualquier otro bloque de datos grande no estructurado.<br><br> Date Almacena fechas desde el 1 de enero del 4712 a.C. hasta el 31 de diciembre del 4712 d.C. Integer Un número entero que no tiene parte fraccionaria.<br><br> Normalmente un Integer será un valor de 32 bits con un rango de 32147483648 a +2147483647 Smallint Representa un número entero que no contiene parte fraccionaria. Su precisión nunca será mayor que la de un Integer. Es un valor de 16 bits entre 332768 y +32767 Number(1,d) Almacena datos de tipo numérico, siendo cl d la longitud y cd d el número de dígitos decimales Raw(tamaño) Datos binarios puros con una longitud máxima de 2000 bytes.<br><br> Sirven para almacenar datos de tipo binario como sonido e imágenes digitalizadas. DEP. TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1.<br><br> Definición de Datos & Consultas I 5 Restricciones de columnas NOT NULL. La columna no permitirá valores nulos. CONSTRAINT.<br><br> Permite asociar un nombre a una restricción. DEFAULT valor. La columna tendrá un valor por defecto.<br><br> El SBGD utiliza este valor cuando no se especifica un valor para dicha columna. PRIMARY KEY. Permite indicar que esta columna es la clave primaria.<br><br> REFERENCES. Es la manera de indicar que este campo, es clave ajena y hace referencia a una clave candidata de otra tabla. Esta foreign key es sólo de una columna.<br><br> UNIQUE. Obliga a que los valores de una columna tomen valores únicos (no puede haber dos filas con igual valor). Se implementa creando un índice para dicha(s) columna(s).<br><br> CHECK (condición). Permite indicar una condición que debe de cumplir esa columna. Restricciones de tablas PRIMARY KEY (columna1, columna2...).<br><br> Permite indicar las columnas que forman la clave primaria. FOREIGN KEY (columna1, columna2....) REFERENCES NombreTabla. Indica las columnas que son clave ajena referenciando a una clave candidata de otra tabla.<br><br> UNIQUE (columna1, columna2...). El valor combinado de una o varias columnas es único. CHECK (condición).<br><br> Permite indicar una condición que deben cumplir las filas de la tabla. Puede afectar a varias columnas. DEP.<br><br> TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 6 La cláusula Foreign Key tiene unas opciones que se explican a continuación (no soportadas en su totalidad por Oracle): 2 Tratamiento de nulos: Se puede indicar cómo debe tratar el SGBD un valor NULL en una o más columnas de la clave ajena, cuando lo compare con las filas de la tabla padre. 2 Modo de borrado: Para determinar la acción que se debe realizar cuando se elimina una fila referenciada, se debe utilizar una regla de supresión opcional para la relación (CASCADE, SET NULL, SET DEFAULT, NO ACTION).<br><br> 2 Modo de modificación: Una regla de actualización para la relación, que determina la acción que se debe realizar cuando se modifica la clave candidata de la fila referenciada (CASCADE, SET NULL, SET DEFAULT, NO ACTION). EJEMPLOS areas(codigo , nombre, departamento) (código es la clave primaria) departamentos(codigo_dpto , nombre) (código_dpto es la clave primaria) La tabla areas tiene una clave ajena areas.departamento departamentos CREATE TABLE areas ( codigo char(3) not null, nombre char(55) not null, departamento char(3) not null, Primary key(codigo), Foreign key(departamento) REFERENCES departamentos ON DELETE SET NULL ON UPDATE CASCADE); ON DELETE Set Null Significa que si se borra algún departamento de la tabla departamentos el campo departamento de las filas de la tabla areas que le reverenciaban se pone como Null. ON UPDATE CASCADE Significa que si se modifica el código_dpto de una fila de la tabla DEP.<br><br> TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 7 departamento, también se modificara en las filas de la tabla áreas que le referencian. CREATE TABLE departamentos ( codigo_dpto char(3) not null, nombre char(40) not null, Primary key(codigo_dpto) ); Renombrar una tabla RENAME TABLE <nombre tabla existente> TO <nuevo nombre tabla> Eliminar una tabla de la base de datos DROP TABLE <nombre tabla> [CASCADE, RESTRICT] Ejemplos: 3 DROP TABLE DEPARTAMENTOS CASCADE (La tabla se borra, así como las posibles restricciones relativas a esta tabla) 3 DROP TABLE DEPARTAMENTOS RESTRICT (La tabla se borra sólo si no se hace referencia a ella en ninguna restricción, p.e.<br><br> en la definición de claves ajenas) Modificar una tabla ALTER TABLE <nombre tabla> La sentencia Alter Table se utiliza para cambiar una tabla existente. Dentro de la tabla podemos Add (añadir) o Drop (borrar) columnas y restricciones (PRIMARY KEY, FORING KEY, UNIQUE, CHECK CONSTRAINT). { ADD <nombre columna nueva> <tipo de dato> [NOT NULL] MODIFY <nombre columna> [DEFAULT | DROP DEFAULT] valor DROP <nombre columna> [CASCADE | RESTRICT] ADD [PRIMARY KEY (nombre columna) | FOREIGN KEY (nombre columna) REFERENCES nombre_tabla | UNIQUE (nombre columna) | CHECK (condición) DEP.<br><br> TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 8 DROP CONSTRAINT nombre-restricción [CASCADE| RESTRICT] EJEMPLOS Agregar a la tabla areas el campo Responsable de tipo char(30) alter table areas ADD responsable char(30) not null; Modificar el campo nombre de la tabla departamentos a char(50) alter table departamentos MODIFY nombre char(50); Sentencias sobre Sinónimos Un sinónimo es un nombre que puede utilizarse como sustituto o alias del nombre real de una tabla. Puede ser útil para simplificar algunas expresiones.<br><br> Crear un sinónimo CREATE SYNONYM <nombre sinónimo> FOR <nombre tabla> create synonym are for areas; create synonym dep for departamentos; Borrar un sinónimo DROP SYNONYM <nombre sinónimo> Drop synonym are DEP. TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 9 Gestión de Dominios (SI en SQL 3 NO en ORACLE) Los dominios se usan como tipos de datos en la definición de columnas al crear tablas.<br><br> Permiten definir los valores aceptados en las columnas, así como la definición de valores por defecto. Creación de Dominios: CREATE DOMAIN <Nombre Dominio> AS <Tipo Datos> [DEFAULT <valor defecto>] [{[CONSTRAINT <nombre restricción>] CHECK (<condición check>)}...]; Ejemplos: CREATE DOMAIN SeisDigitos AS CHAR(6) DEFAULT '000000'; CREATE DOMAIN SeisDigitos AS CHAR(6) DEFAULT '000000' CHECK (VALUE IS NOT NULL) CHECK (CHAR_LENGTH(TRIM(VALUE)) = 6 AND VALUE BETWEEN '000000' AND 8500000'); Borrado de Dominios: DROP DOMAIN <Nombre Dominio> Modificado de Dominios: ALTER DOMAIN <Nombre Dominio> { SET DEFAULT <Valor Defecto | DROP DEFAULT } ALTER DOMAIN <Nombre Dominio> ADD <Restricción Dominio> ALTER DOMAIN <Nombre Dominio> DROP CONSTRAINT <Nombre Restricción> [RESTRICT | CASCADE ] DEP. TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1.<br><br> Definición de Datos & Consultas I 10 Introducción al Lenguaje de Consultas Sintaxis. La sintaxis de la orden SELECT consta básicamente de las cláusulas SELECT y FROM como obligatorias y de otras varias cláusulas opcionales: <cláusula SELECT> <cláusula FROM> [ <cláusula WHERE> ] [ <cláusula GROUP BY> [ <cláusula HAVING> ] ] [ <cláusula ORDER BY>] Las cláusulas ORDER BY, GROUP BY y HAVING se verán en otra práctica. El formato detallado es: SELECT [ALL | DISTINCT | UNIQUE ] <lista-de selecciones> FROM <nombre de tabla> [alias de tabla] [,...] [WHERE <condición>] [GROUP BY <lista-de columnas> [HAVING <condición>] ] [ORDER BY <nombre de columna> [ASC | DESC] [,...] ] Cláusula SELECT.<br><br> Permite indicar los datos (columnas o expresiones) que queremos obtener. SELECT [ALL | DISTINCT | UNIQUE ] <lista-de selecciones> DISTINCT: Elimina las filas duplicadas en el resultado de la consulta. UNIQUE es igual que DISTINCT.<br><br> <lista-de selecciones>: Lista de nombre de columnas o expresiones separadas por comas. NOTAS: - Las columnas ambiguas se preceden del nombre de la tabla: <tabla>.<columna> - * refiere a todas las columnas de todas las tablas. DEP.<br><br> TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 11 Cláusula FROM. Permite indicar las tablas que contienen los datos.<br><br> FROM { <nombre de tabla> [alias de tabla] } [,...] <alias de tabla>: Es un sinónimo activo sólo en el ámbito de la sentencia SELECT. Cláusula WHERE. Sirve para indicar la condición que deben cumplir las filas resultantes.<br><br> WHERE <condición> Una condición está formada por una o varias expresiones condicionales conectadas por los operadores lógicos AND, OR y NOT. Una expresión condicional tiene una de las formas siguientes: <expresión1> <operador relacional> <expresión2> Verifica si las dos expresiones satisfacen la comparación. < expresión1> [NOT] BETWEEN <expresión2> AND <expresión3> Verifica si la expresión1 tiene un valor comprendido entre los valores de la expresión2 y la expresión3.<br><br> <expresión> [NOT] IN (<lista-de valores>) Verifica si la expresión tiene un valor de los indicados en la lista de valores. <nombre de columna> [NOT] LIKE "<string>" [ESCAPE "<carácter de escape>"] Verifica si el valor de la columna se adapta al patrón de búsqueda (string). Se admiten caracteres comodín ( "%" representa cero o más caracteres, "_" representa un único carácter).<br><br> El carácter de escape 8/ 9 permite referirse a los caracteres comodín como caracteres y no como comodines. <nombre de columna> IS [NOT] NULL Verifica si el valor de la columna es nulo. DEP.<br><br> TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1. Definición de Datos & Consultas I 12 Diccionario de Datos Conjunto de tablas que contiene las definiciones de los elementos de las bases de datos creadas por el usuario. Estas tablas son creadas y actualizadas directamente por el SGBD.<br><br> Las tablas del diccionario de datos se pueden consultar con SELECTs SELECT TABLE_NAME FROM USER_TABLES Obtenemos el nombre de las tablas existente en la base de datos. DESCRIBE nombre_tabla Ofrece la descripción completa de una tabla existente en la base de datos. EJERCICIOS (definición de datos) 2 Utilizando SQL*Plus crear las tablas correspondientes al siguiente esquema de base de dato relacional relativo a la gestión de los préstamos de una biblioteca: Libro (signatura , autor, titulo, editor, clase) Usuario (carnet , nombre, direccion) Clase (clave , tiempo_de_prestamo) Prestamo (signatura, carnet , fecha_inicio, fecha_fin) Con las siguientes claves ajenas: Libro.clase ?<br><br> Clase Prestamo.signatura ? Libro Prestamo.carnet ? Usuario 2 Agregar a la tabla Usuario el campo Fecha_Ingreso (que sea obligatorio) 2 Agregar a la tabla Libro el campo Prestado (que sea obligatorio), y asignarle por defecto el valor 1.<br><br> 2 Definir el dominio cTIPO_EDAD d, como un entero entre 0 y 120, y añadir el atributo Edad del usuario 2 Al finalizar borrar las tablas creadas. DEP. TECNOLOGÍAS Y SISTEMAS DE INFORMACIÓN BASES DE DATOS PRACTICA 1.<br><br> Definición de Datos & Consultas I 13 EJERCICIOS (consultas) Los ejercicios de consultas se realizarán en la segunda sesión, después de explicar el lenguaje de manipulación de datos, pero antes de realizar los ejercicios correspondientes. Aquí se pueden practicar el SELECT TABLE_NAME FROM USER_TABLES y el DESCRIBE nombre_tabla

less