Capítulo 3 CLASE 3

3.1 Creación de tablas en SQL

SQL respalda tres tipos de tablas:

  • Tablas base: son objetos de esquema que tienen los datos de SQL.
  • Tablas derivadas: son los resultados que se observan cuando se solicitan (Query) datos de una base de datos.
  • Tablas vistas.Serán vistas mas adelante

Aca se presentarán las Tablas base. Tipos de tabla base:

  • Tablas base persistentes: Un objeto de esquema nombrado definido por la definición de una tabla en la instrucción CREATE TABLE. Las tablas base persistentes tienen los datos de SQL que se almacenan en la base de datos. Éste es el tipo más común de tabla base y es a menudo a lo que se refiere la gente cuando menciona tablas base o tablas. Una tabla base persistente existe desde que la definición de tabla existe, y se puede llamar desde cualquier sesión de SQL.

  • Tablas temporales globales: Un objeto de esquema nombrado definido por una definición de tabla en la instrucción CREATE GLOBAL TEMPORARY TABLE. Aunque la definición de la tabla es parte del esquema, la tabla actual existe sólo cuando se hace referencia dentro del contexto de la sesión SQL en la cual se creó. Cuando la sesión termina, la tabla ya no exis- te. No se puede acceder a una tabla temporal global creada en una sesión desde otra sesión de SQL. Los contenidos son distintos en cada sesión de SQL.

  • Tablas temporales locales: creadas Un objeto de esquema nombrado definido por una defi- nición de tabla en la instrucción CREATE LOCAL TEMPORARY TABLE. Al igual que una tabla temporal global, sólo se puede hacer referencia a una tabla temporal local creada dentro del contexto de la sesión de SQL en la cual se creó, y no se puede acceder desde otra sesión de SQL. Sin embargo, se puede acceder a una tabla global desde cualquier lugar dentro de una sesión asociada de SQL, mientras que en una tabla temporal local sólo se podrá acceder dentro del módulo asociado. Los contenidos son distintos dentro de ese módulo.

  • Tablas temporales locales declaradas: Una tabla declarada como parte de un procedimiento en un módulo. La definición de la tabla no se incluye en el esquema y no existe hasta que ese procedimiento se ejecuta. Al igual que otras tablas temporales, sólo se hace referencia a una tabla temporal local declarada dentro del contexto de la sesión SQL en la cual se creó.

Como puede ver, se puede utilizar una forma de la instrucción CREATE TABLE para crear todos los tipos de tablas, excepto las tablas temporales locales declaradas. A lo largo del resto del capítulo, se analizarán en primer lugar las tablas temporales locales persistentes, aunque se tocará el tema de las tablas temporales en los capítulos subsecuentes. Mientras tanto, veamos la sintaxis en la instrucción CREATE TABLE:

CREATE [ {GLOBAL | LOCAL} TEMPORARY ]
TABLE < nombre de la tabla >
( < elemento de la tabla > [ {, < elemento de la tabla > }… ] )
[ ON COMMIT { PRESERVE | DELETE } ROWS ]

En la primera línea de la sintaxis se designa si la tabla es temporal y se proporciona un nombre a la tabla; por lo tanto, tiene tres opciones:

  • CREATE TABLE < nombre de la tabla >
  • CREATE GLOBAL TEMPORARY TABLE < nombre de la tabla >
  • CREATE LOCAL TEMPORARY TABLE < nombre de la tabla >

La segunda línea de la sintaxis permite especificar las partes que componen la tabla, tales como columnas.

La tercera línea de la sintaxis aplica sólo si se crea una tabla temporal. La cláusula permite especificar si la tabla se debe vaciar o no cuando la instrucción COMMIT se ejecuta. La instrucción COMMIT se usa en una transacción al hacer cam- bios en la base de datos (Lo veremos después.)

En su forma más básica, la instrucción CREATE TABLE podría verse como la siguiente instrucción:

CREATE TABLE ARTISTAS
( ID_ARTISTA INTEGER,
NOMBRE_ARTISTA CHARACTER (60) );

En esta instrucción se creó una tabla llamada ARTISTAS, una columna llamada ID_ARTISTA y una columna llamada NOMBRE_ARTISTA. La columna ID_ARTISTA se asocia con el tipo de dato INTEGER, y la columna NOMBREA_ARTISTA se asocia con el tipo de datos CHARACTER. Observe que las definiciones de las dos columnas se separan por una coma. También observe que se colocaron las definiciones de las dos columnas en líneas separadas y se alinearon los tipos de datos agregando espacios extra (cuando se procesan las instrucciones SQL, los espacios extra y las líneas nuevas simplemente se ignoran).

3.2 Tipos de datos en una columna

SQL respalda tres formas de tipos de datos:

  • Predefinido: Los tipos de datos predefinidos son los más comunes. Cada tipo de datos predefinido es un elemento nombrado (utilizando una palabra clave en SQL) que limita valores a las restricciones definidas por esa base de datos. SQL incluye cinco formas de tipos de datos predefinidos: cadena, numérico, fecha y hora, intervalo y booleano.

  • Construido: Los tipos de datos construidos también se denominan elementos, pero tienden a ser más complejos que los tipos de datos predefinidos ya que pueden contener múltiples valores.

  • Definido por el usuario: Los tipos de datos definidos por el usuario se basan en los tipos predefinidos o definiciones de atributos, y se agregan como objetos de esquema al entorno SQL. SQL respalda dos formas de tipos de datos definidos por el usuario: distinto y estructurado.

3.2.1 Tipos de datos de cadena

SQL define cuatro formas de tipos de datos en cadena:

  • Cadenas de caracteres: Los valores permitidos se deben extraer de un conjunto de caracteres, ya sea de un conjunto predeterminado o de un conjunto definido en el momento que la columna se define. Los tipos de datos en la cadena de caracteres incluyen CHARACTER, CHARACTER VARYING y CHARACTER LARGE OBJECT.

  • Cadenas de carácter nacional: Los valores permitidos son similares a los de las cadenas de caracteres, salvo que el conjunto de caracteres asociados con estos tipos de datos se definen por la aplicación. Como resultado, cuando una cadena de carácter nacional se especifica, los valores asociados con ese tipo de datos deben basarse en el conjunto de caracteres especificado por el sistema de gestión de base de datos relacional (RDBMS) para las cadenas de carácter nacional. Éstas son útiles para almacenar cadenas de caracteres en varios lenguajes en la mis- ma base de datos. Los tipos de datos en la cadena de carácter nacional incluyen NATIONAL CHARACTER, NATIONAL CHARACTER VARYING y NATIONAL CHARACTER LAR- GE OBJECT.

  • Cadenas de bits: Los valores permitidos se basan en bits de datos (dígitos binarios), lo que significa que estos tipos de datos permiten sólo valores de 0 o 1. SQL respalda dos formas de tipos de datos de cadena de bits: BIT y BIT VA- RYING.

  • Cadenas binarias: Los valores permitidos son similares a las cadenas de bits, excepto que se basan en bytes (denominado como octetos en SQL:2006), en lugar de bits. Como resultado, ningún conjunto de caracteres se relacionan con ellas. (Un byte es igual a 8 bis, la razón por la cual el estándar SQL utiliza el término octeto.) SQL sólo respalda un tipo de datos en una cadena binaria: BINARY LARGE OBJECT. Este tipo es útil para almacenar datos binarios puros tales como clips de sonido o imágenes en la base de datos.

3.2.2 Tipos de datos numéricos

Todos los tipos de datos numéricos tienen una precisión, y algunos tienen una escala. La precisión se refiere al número de dígitos (dentro de un valor numérico específico) que se pueden almacenar. La escala se refiere al número de dígitos en la parte fraccional de ese valor (los dígitos a la derecha del punto decimal). Por ejemplo, el número 435.27 tiene una precisión de 5 y una escala de 2. Una escala de 0 indica que el número es un número entero y no contiene ningún compo- nente fraccional. SQL define dos formas de tipos de datos numéricos:

  • Numéricos exactos: Los valores permitidos tienen precisión y escala. Los tipos de datos numéricos exactos incluyen NUMERIC, DECIMAL, INTEGER y SMALLINT.

  • Numéricos aproximados: Los valores permitidos tienen precisión pero no escala. Como resultado, el punto decimal puede flotar. Un número de punto flotante es aquel que contiene un punto decimal, pero el punto decimal se puede localizar en cualquier lugar dentro del número, por lo que se dice que un numérico aproximado no tiene escala. Los tipos de datos numéricos aproximados incluyen REAL, DOUBLE PRECISION y FLOAT.

3.2.3 Tipos de datos de fecha y hora

3.2.4 Tipo de datos de intervalo

El tipo de datos de intervalo está estrechamente relacionado con los tipos de datos de fecha y hora. El valor de un tipo de datos de intervalo representa la diferencia entre dos valores de fecha y hora. SQL respalda dos tipos de intervalos:

  • Intervalos de año-mes El tipo de datos de intervalo especifica intervalos entre años, meses, o ambos. Se pueden utilizar sólo los campos de AÑO y MES en un intervalo de año-mes.

  • Intervalos de día-hora El tipo de datos de intervalo especifica intervalos entre cualquiera de los siguientes valores: días, horas, minutos o segundos. Se pueden utilizar sólo los campos de DAY, HOUR, MINUTE y SECOND en un intervalo de día-tiempo.

No se puede mezclar un tipo de intervalo con los demás. Por ejemplo, no se puede definir un tipo datos de intervalo que utilice el campo YEAR y el campo HOUR.

El tipo de datos de intervalo utiliza la palabra clave INTERVAL seguido por una cláusula de . La cláusula es una serie de reglas complejas que describen cómo se puede definir el tipo de datos de INTERVAL para expresar la participación de los intervalos de años, meses, días, horas, minutos o segundos.

Para los intervalos de año-mes, se puede especificar uno de los siguientes tipos de datos de intervalo:

  • INTERVAL YEAR
  • INTERVAL YEAR(p)
  • INTERVAL MONTH
  • INTERVAL MONTH(p)
  • INTERVAL YEAR TO MONTH
  • INTERVAL YEAR(p) TO MONTH
  • INTERVAL DAY
  • INTERVAL DAY(p)
  • INTERVAL DAY TO HOUR
  • INTERVAL DAY(p) TO HOUR
  • INTERVAL DAY TO MINUTE
  • INTERVAL DAY(p) TO MINUTE
  • INTERVAL DAY TO SECOND
  • INTERVAL DAY(p) TO SECOND
  • INTERVAL DAY TO SECOND(x)
  • INTERVAL DAY(p) TO SECOND(x)

Si fuera a utilizarse un tipo de datos de intervalo en una definición de columna, se vería algo como lo siguiente:

RANGO_FECHA INTERVAL YEAR(4) TO MONTH

En este ejemplo, un valor en esa columna incluiría cuatro dígitos para el año, un guión, y luego dos dígitos para el mes, tal como 1999-08. Si la precisión no se especifica para el año, el rango de año puede incluir sólo dos dígitos (00 hasta 99).

3.2.5 Tipo de dato booleano

El tipo de datos booleano (a diferencia de los tipos de datos de intervalo) es muy sencillo y fácil de aplicar. El tipo de dato respalda una construcción de verdadero/falso que permite sólo tres valores: verdadero, falso o desconocido. Un valor nulo se evalúa como desconocido.

Las comparaciones en el tipo de datos booleano siguen una lógica específica: - Verdadero es mayor que falso. - Una comparación con un valor desconocido (nulo) devolverá un resultado desconocido. - Un valor desconocido se puede asignar a una columna sólo si admite valores nulos.

Para utilizar el tipo de datos booleano, debe utilizar la palabra clave BOOLEAN sin parámetros, como se muestra en el siguiente ejemplo:

ARTISTAS_CON_AGENTE BOOLEAN

La columna ARTISTAS_CON_AGENTE sólo aceptará valores de verdadero, falso y desconocido.

3.3 Utilice tipos de datos SQL

Veamos la instrucción CREATE TABLE, que define a una tabla con columnas que usan diferentes tipos de datos. En el siguiente ejemplo, la instrucción crea una tabla llamada ARTISTAS que incluye cuatro columnas:

CREATE TABLE ARTISTAS
( ID_ARTISTA INT,
NOMBRE_ARTISTA VARCHAR(60),
FDN_ARTISTA DATE,
POSTER_EN_EXISTENCIA BOOLEAN );

3.4 Creación de tipos definidos por el usuario

El tipo definido por el usuario es una forma de tipo de datos (almacenada como un objeto de esquema) que está en parte definida por el programador y en parte basada en uno o más tipos de datos. SQL apoya dos formas de tipos definidos por el usuario:

  • Tipos estructurados: Estos tipos se forman por uno o más atributos, cada uno de los cuales está basado en otro tipo de datos, incluyendo tipos predefinidos, tipos construidos, y otros tipos estructurados. Un tipo estructurado puede incluir métodos en su definición.

Un método es un tipo de función que se asocia con un tipo definido por el usuario. Una función es el nombre de una operación que realiza tareas predefinidas que no se podrían realizar normalmente mediante el uso de sólo instrucciones SQL. Es un tipo de ruta que toma parámetros de entrada (que a menudo son opcionales) y devuelve un valor único basado en esos parámetros.

  • Tipos distintos: Estos tipos se basan simplemente en tipos de datos predefinidos, y cualquier tipo de parámetro se define para ese tipo de datos si los parámetros son necesarios o deseados.

SQL proporciona la instrucción CREATE TYPE para definir los tipos definidos por el usuario. En la siguiente instrucción se creó un tipo definido por el usuario que se basa en el tipo de datos NUMERIC:

CREATE TYPE SALARIO AS NUMERIC(8,2)
FINAL;

Cuando se especifica FINAL, le dice a SQL que no se definen subtipos para este tipo. La alternativa es especificar NOT FINAL, lo que significa que se pueden definir los subtipos para este tipo. Una vez creado el tipo, se puede utilizar en la definición de una columna como si fuera un tipo de datos predefinido:

CREATE TABLE EMPLEADO
( ID_EMPLEADO INTEGER,
SALARIO_EMPLEADO SALARIO );

Cualquier valor que se agregue a la columna SALARIO_EMPLEADO se ajustará a las especificaciones del tipo de datos NUMERIC con una precisión de 8 y una escala de 2. Como resultado, el valor puede ser cualquiera desde –999999.99 hasta 999999.99. La mejor parte es que se puede utilizar el tipo definido por el usuario SALARIO en cualquier otro tipo de tablas que requieren valores similares.

3.5 Especificación de los valores predeterminados en una columna

Otra característica valiosa que SQL respalda es la habilidad de especificar un valor predeterminado para una columna cuando se utilice la instrucción CREATE TABLE para crear una tabla. La sintaxis para la definición de una columna con un valor predeterminado tendría este aspecto:

< nombre de columna > < tipo de datos > DEFAULT < valor predeterminado >

En el siguiente ejemplo se utiliza la instrucción CREATE TABLE para definir a una tabla llamada ARTISTAS, que contiene tres columnas:

CREATE TABLE ARTISTAS
( ID_ARTISTA INT,
NOMBRE_ARTISTA VARCHAR(60),
LUGAR_DE_NACIMIENTO VARCHAR(60) DEFAULT ‘Desconocido’ );

Observe que la columna LUGAR_DE_NACIMIENTO incluye el valor predeterminado ‘Des- conocido.’ El valor es aceptable, ya que se ajusta a los requisitos del tipo de datos VARCHAR(60). También observe que el valor predeterminado se encierra en comillas simples. Debe utilizar comillas simples para valores de cadena de caracteres. La Figura muestra cómo puede verse la tabla si se llena con filas de datos:

Si se insertan nuevas filas en esta tabla y no se sabe el lugar de nacimiento de un artista, el sistema automáticamente insertará el valor Desconocido.

3.6 PASO A PASO: Creación de tablas

  1. Abra la aplicación de clientes de su RDBMS y conecte la base de datos INVENTARIO. Todos los objetos se crean dentro de esa base de datos.

  2. La primera tabla que se crea es la tabla DISCOS_COMPACTOS. Observe que incluye tres columnas, dos de las cuales tienen un tipo de datos INT y una que tiene un tipo de datos VAR- CHAR(60). Esta tabla tiene información acerca de los discos compactos en el inventario. La co- lumna ID_DISCO_COMPACTO contiene números que identifican únicamente a cada CD. La columna TITULO_CD contiene los nombres actuales de los CD. La columna ID_DISQUERA contiene números que identifican a las compañías que editan los CD. Introduzca la siguiente instrucción SQL en la ventana de entrada de la aplicación clientes:

CREATE TABLE TIPOS_DISCO_COMPACTO ( ID_DISCO_COMPACTO INT, TITULO_CD VARCHAR(60), ID_TIPO INT );

  1. Verifique que se introdujo la información correcta y ejecute la instrucción. Debe recibir un mensaje de confirmación de que la instrucción se ejecutó exitosamente.

  1. La próxima tabla que se crea es la tabla DISQUERAS_CD. La tabla incluye la columna ID_ DISQUERA, que identifica únicamente a cada compañía que edita los CD, y la columna NOM- BRE_COMPAÑIA, que enumera los nombres actuales de las compañías. Introduzca y ejecute el siguiente código:

CREATE TABLE DISQUERAS_CD
( ID_DISQUERA INT,
NOMBRE_COMPAÑIA VARCHAR(60) );

  1. La última tabla que se crea es la tabla TIPOS_MUSICA. La tabla incluye la columna ID_TIPO, que identifica únicamente cada categoría de música, y la columna NOMBRE_TIPO, que enumera los nombres actuales de las categorías de música (por ejemplo, blues o jazz). Introduzca y ejecute el siguiente código:

CREATE TABLE TIPO_MUSICA ( ID_TIPO INT PRIMARY KEY, NOMBRE_TIPO VARCHAR (20) NOT NULL, CONSTRAINT un_NOMBRE_TIPO UNIQUE (NOMBRE_TIPO));

La base de datos ahora contiene tres tablas nuevas. Estas tablas sirven como base para otros ejercicios. A medida que progrese mediante estos ejercicios, podrá modificar esas tablas, crear tablas adicionales, insertar datos en las tablas, y después hacer consultas y manipular esos datos. Para cuando complete todos los ejercicios, creará y completará una pequeña base de datos que almacene datos sobre un inventario de discos compactos.

3.7 Modificación de tablas en SQL

En su forma más básica, la sintaxis para la instrucción ALTER TABLE se representa:

ALTER TABLE < nombre de la tabla >
ADD [COLUMN] < definición de columna >
| ALTER [COLUMN] < nombre de columna >
{ SET DEFAULT < valor predeterminado > | DROP DEFAULT }
| DROP [COLUMN] < nombre columna > { CASCADE | RESTRICT }

La instrucción permite tomar tres diferentes acciones: añadir columnas, modificar columnas o eliminar columnas.

La instrucción ALTER TABLE también permite añadir o eliminar limitaciones en una tabla.

Una limitación en una tabla es una regla que restringe qué datos se introducen en la tabla. La limitación forma parte de la definición de la tabla, pero no forma parte de cualquiera de las definiciones de una columna específica.

Por ejemplo, se puede utilizar la siguiente instrucción para modificar la tabla ARTISTAS de modo que incluya la columna FDN_ARTISTA:

   ALTER TABLE ARTISTAS
     ADD COLUMN FDN_ARTISTA DATE;

A diferencia de la cláusula ADD [COLUMN], la cláusula ALTER [COLUMN] se limita a dos acciones: establecer una predeterminación o eliminar una predeterminación (aunque existen aplicaciones de productos que permiten cambiar otras propiedades, tal como el tipo de datos o la precisión y la escala). Por ejemplo, suponga que la tabla ARTISTS incluye la columna LUGAR_ DE_NACIMIENTO, pero no se define ninguna predeterminación para esa columna. Puede agregar una predeterminación utilizando la siguiente instrucción:

   ALTER TABLE ARTISTAS
  ALTER COLUMN LUGAR_DE_NACIMIENTO SET DEFAULT 'Desconocido';

También puede eliminar la predeterminación utilizando la siguiente instrucción:

   ALTER TABLE ARTISTAS 
   ALTER COLUMN LUGAR_DE_NACIMIENTO DROP DEFAULT;

La cláusula final en la sintaxis (DROP[COLUMN]) proporciona dos opciones para eliminar una columna y los datos de la tabla: las palabras clave CASCADE y RESTRICT.

Si se especifica la opción CASCADE, la columna y los datos dentro de la columna se eliminan inde- pendientemente de si otros objetos hacen referencia a esa columna. Todas las vistas, restricciones, rutas o activadores que hacen referencia a la columna también se eliminan. Si se utiliza la opción RESTRICT, la columna se elimina sólo si no hay vistas, restricciones, rutas o activadores que hagan referencia a la columna.

Por ejemplo, la siguiente instrucción elimina la columna LUGAR_DE_NACIMIENTO y los datos almacenados en la columna, independientemente de las dependencias:

   ALTER TABLE ARTISTAS 
    DROP COLUMN LUGAR_DE_NACIMIENTO CASCADE;

3.8 Eliminación de tablas en SQL

Como puede imaginar, el proceso de eliminación de una tabla y sus datos almacenados es muy sencillo. La siguiente sintaxis muestra lo fácil que resulta este proceso:

DROP TABLE { CASCADE | RESTRICT }

La única decisión que necesita tomar cuando se elimina una tabla es si debe escoger la opción CASCADE o RESTRICT.

3.9 PASO A PASO: Creación de tablas

  1. Abra la aplicación de cliente del RDBMS y conéctese a la base de datos INVENTARIO

CREATE DATABASE INVENTARIO; USE INVENTARIO;

  1. Cree las siguientes tabla llamada TIPOS_DISCO_COMPACTO. La tabla incluye la columna ID_DIS- CO_COMPACTO y la columna ID_TIPO. Ambas columnas se asignarán a un tipo de datos INT. Introduzca y ejecute el siguiente código:

CREATE TABLE TIPOS_DISCO_COMPACTO ( ID_DISCO_COMPACTO INT, ID_TIPO INT );

  1. Ahora elimine la tabla de la base de datos. Introduzca y ejecute el siguiente código:

DROP TABLE TIPOS_DISCO_COMPACTO CASCADE;

  1. Ahora se volverá a crear las tablas

CREATE TABLE CDLabels ( ID_DISQUERA INT, CompanyName VARCHAR (60) DEFAULT ‘Independent’ NOT NULL, CONSTRAINT pk_CDLabels PRIMARY KEY (ID_DISQUERA) ) ;

CREATE TABLE DISCOS_COMPACTOS ( ID_DISCO_COMPACTO INT PRIMARY KEY, TITULO_CD VARCHAR (60) NOT NULL, ID_DISQUERA INT NOT NULL, EN_EXISTENCIA INT NOT NULL, FOREIGN KEY fk_ID_DISQUERA (ID_DISQUERA) REFERENCES CDLabels (ID_DISQUERA) );

CREATE TABLE ARTISTAS ( ID_ARTISTA INT, NOMBRE_ARTISTA VARCHAR (60) NOT NULL, LUGAR_DE_NACIMIENTO VARCHAR (60) DEFAULT ‘Unknown’ NOT NULL, CONSTRAINT pk_ARTISTAS PRIMARY KEY (ID_ARTISTA) ) ;

CREATE TABLE CD_ARTISTAS ( ID_ARTISTA INT, ID_DISCO_COMPACTO INT, CONSTRAINT pk_CD_ARTISTAS PRIMARY KEY ( ID_ARTISTA, ID_DISCO_COMPACTO ), FOREIGN KEY fk_ID_ARTISTA (ID_ARTISTA) REFERENCES ARTISTAS (ID_ARTISTA), FOREIGN KEY fk_ID_DISCO_COMPACTO_02 (ID_DISCO_COMPACTO) REFERENCES DISCOS_COMPACTOS (ID_DISCO_COMPACTO) ) ;

CREATE VIEW CDsInStock AS SELECT TITULO_CD, EN_EXISTENCIA FROM DISCOS_COMPACTOS WHERE EN_EXISTENCIA > 10 WITH CHECK OPTION ;

CREATE VIEW CDPublishers ( TITULO_CD, Publisher ) AS SELECT DISCOS_COMPACTOS.TITULO_CD, CDLabels.CompanyName FROM DISCOS_COMPACTOS, CDLabels WHERE DISCOS_COMPACTOS.ID_DISQUERA = CDLabels.ID_DISQUERA ;

La base de datos INVENTARIO ahora debe contener cuatro tablas: