Ediciones ENI Editorial | líder en informática
La definición de los datos (LDD)
Los tipos de datos
En esta sección nos ocuparemos de los tipos de datos más utilizados para la descripción de las columnas de una tabla. Existen tres grandes familias de datos: numéricos, caracteres (o alfanumérico) y cronológico (fechas y horas).
Cada SGBDR ha creado tipos específicos para necesidades precisas, como los tipos geográficos o por problemas de almacenamiento. El tipo elegido dependerá de la precisión buscada, teniendo en cuenta el tamaño necesario para almacenar datos. Una buena decisión permite acceder rápidamente al valor.
1. Numéricos
Los tipos numéricos permiten definir si deseamos un entero, un decimal o un número con coma flotante.
Los números enteros:
|
Tipo |
Precisión |
Almacenamiento (bytes) |
BDD |
|
TINYINT |
0 a 255 |
1 |
SQL Server |
|
TINYINT |
0 a 255 o -128 a 127 |
1 |
MySQL |
|
SMALLINT |
-32 768 a 32 768 o 0 a 65 535 |
2 |
SQL Server, PostgreSQL |
|
SMALLINT |
-32 768 a 32 768 o 0 a 65 535 para MySQL |
2 |
MySQL |
|
SMALLSERIAL |
1 a 32 767. Entero de incremento automático |
2 |
PostgreSQL |
|
MEDIUMINT |
-8 388 608 a 8 388 607 o 0 a 16 777 215 |
3 |
MySQL |
|
INT o INTEGER |
-2 147 483 648 a 2 147 483 647 o 0 a 4 294 967 295 |
4 |
SQL Server, PostgreSQL |
|
INT(p) |
-2 147 483 648 a 2 147 483 647 o 0 a 4 294 967 295, donde p designa el número máximo de cifras |
4 |
MySQL, Oracle |
|
SERIAL |
1 a 2 147 483 647. Entero de incremento automático o 1 a 9 223 372 036 854 775 807 para MySQL |
4 |
PostgreSQL, MySQL |
|
BIGINT |
-9 223 372 036 854 775 808 a 9 223 372 036 854 775 807 o 0 a 18 446 744 073 709 551 615 para MySQL |
8 |
SQL Server, PostgreSQL, MySQL |
|
BIGSERIAL |
1 a 9 223 372 036 854 775 807. Entero de incremento automático |
8 |
PostgreSQL |
|
BIT |
1, 0 o NULL |
1 |
SQL Server |
Los números decimales y con coma flotante:
|
DECIMAL(p[,s]) o NUMERIC(p[,s]) |
-10^38+1 a 10^38-1. p representa la precisión, es decir, el número total y máximo de cifras a la izquierda y a la derecha de la coma. La precisión por defecto es 18. s representa la escala, es decir, el número de cifras máximo antes de la coma. La escala es opcional. El valor... |
La creación de las tablas
En esta sección, vamos a ver cómo crear una tabla, agregar o eliminar columnas, poner comentarios en las columnas y las tablas, e igualmente, el método para copiar una tabla a otra y cómo asignar un sinónimo a un nombre de tabla.
1. El comando CREATE
CREATE es el comando base del lenguaje SQL para crear un elemento. Se utiliza para crear una tabla, un índice, una vista o un sinónimo. En función del elemento que demos de alta, la sintaxis es diferente.
En esta sección, vamos a tratar la creación de tablas. Una tabla se define principalmente por las columnas que la componen y las reglas que se aplican a estas columnas.
No abordaremos los aspectos del almacenamiento físico de la tabla. De hecho, cada SGBDR tiene su propia sintaxis en este tema. En la mayoría de los casos son los DBA (Database Administrator - administrador de la base de datos) los que especifican las normas de almacenamiento y las opciones a aplicar en las tablas. El almacenamiento de las tablas es un elemento determinante en términos de rendimiento y de seguridad de la base de datos. Por tanto es muy recomendable preguntar a un administrador antes de lanzarse a la creación de una tabla.
Para eliminar una tabla, se utiliza el comando DROP TABLE.
Antes de crear una tabla, es preferible hacerse algunas preguntas y respetar algunas reglas.
Trate de dar nombres que identifiquen bien las tablas y las columnas para luego encontrar fácilmente estos datos. Los DBA definen casi siempre las reglas de nomenclatura de las tablas y de las columnas. A veces proporcionan scripts estándar para crear una tabla. Es pues una obligación, consultar las normas en vigor en la empresa antes de cualquier creación.
Las reglas mínimas a respetar en la nomenclatura de una tabla no son muy numerosas y se resumen en: un nombre de tabla debe comenzar por una letra, es único y no puede pasar de 256 caracteres.
Cuando una columna tiene el mismo significado en diferentes tablas, se aconseja conservar el mismo nombre. De hecho, si la columna se encuentra en varias tablas y se ha respetado el modelo relacional, esto significa que la columna es la clave de una tabla. Conservando el mismo nombre se simplifican las uniones entre las dos tablas, ya que las columnas clave se identificarán fácilmente. Esto...
La eliminación de tablas
La eliminación de tablas es una operación simple pero hay que hacerlo con prudencia. La eliminación es definitiva y no habrá ninguna posibilidad de recuperar los datos de la tabla una vez se ha ejecutado la orden.
1. El comando DROP
El comando DROP permite eliminar definitivamente una tabla. Se elimina la tabla y su contenido. La cláusula DROP también se utiliza sobre otros objetos de la base de datos, como las vistas o una base.
A menudo se utiliza el comando DROP justo antes de la creación de una tabla. Así se evitan los errores con una tabla existente.
El comando destruye automáticamente los índices y restricciones de la tabla así como los comentarios. Por el contrario, el comando no elimina los sinónimos.
Si se trata de una tabla sensible, es preferible guardarla previamente con un CREATE ... AS SELECT … por ejemplo.
Atención: la tabla no puede estar siendo utilizada por otra persona.
En el caso de un error no se podrá recuperar la tabla (ROLLBACK). Algunas versiones de SGBDR permiten la recuperación después de un DROP (Oracle a partir de la versión 10g por ejemplo).
En MySQL y SQL Server, los comandos de manipulación de tablas comportan una confirmación automática (COMMIT). Así pues, es imposible recuperar una tabla después de un DROP.
Sintaxis:
DROP TABLE nombre_de_tabla; Ejemplo:...
Comprobar la existencia de un objeto
Un objeto es un componente de la definición de datos (LDD). Por tanto, puede ser una tabla, una columna, una vista, un índice, etc.
Cuando trabajamos en la definición de datos, normalmente probamos el objeto en el que estamos trabajando. Esto es parte de una buena práctica para evitar errores. Por ejemplo, comprobamos que existe una tabla antes de borrarla o que no existe antes de crearla.
Hay varias maneras de comprobar la existencia de un objeto.
IF OBJECT_ID ... IS NULL
Syntaxis (SQL Server)
IF OBJECT_ID('nombre_objeto') IS NULL CREATE...;
IF OBJECT_ID('nombre_objeto') IS NOT NULL DROP...; Ejemplo
IF OBJECT_ID('Hoteles') IS NULL
CREATE TABLE Hoteles (idHotel INTEGER,
Etiqueta VARCHAR(50),
Estrella VARCHAR(5));
IF OBJECT_ID('Hoteles') IS NOT NULL DROP TABLE Hoteles IF EXISTS
Syntaxis (SQL Server y PostgreSQL)
DROP TABLE IF EXISTS nombre_objeto; Ejemplo
DROP TABLE IF EXISTS Hoteles; IF EXISTS (SELECT...)
Syntaxe (SQL Server)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'nombre_objeto'°; Ejemplo
IF EXISTS...La modificación de una tabla
Una vez se han creado las tablas, pueden evolucionar en el tiempo y podrá ser necesario añadir, eliminar columnas o modificar restricciones de columnas.
En algunos casos, se podrá renombrar una tabla. Todo esto es lo que vamos a detallar en las siguientes secciones.
1. El comando ALTER
El comando ALTER se utiliza para realizar diferentes acciones. Se puede utilizar para eliminar o añadir una columna de una tabla. También para añadir o eliminar una restricción o añadir un valor por defecto a una columna.
Atención: No se permite cambiar el nombre de una columna ni tampoco su tipo o atributos NULL o NOT NULL.
Algunos SGBDR aceptan el comando MODIFY y permiten modificar el tipo de una columna.
Atención no obstante al contenido de esta columna. Al pasar un tipo VARCHAR a INTEGER la conversión automática realizada por el SGBDR modificará el contenido de los datos.
Existe el riesgo de perder información o de tornar incompatibles algunos datos con su utilización. De manera general, se desaconseja modificar el tipo de dato de una columna, para evitar problemas, habría que vaciar la tabla antes de cambiar el tipo de dato.
Sintaxis:
ALTER TABLE nombre_de_tabla [ADD nombre_de_columna tipo_columna]
[,DROP COLUMN nombre_de_columna] ...Vaciar una tabla
1. La cláusula TRUNCATE
La cláusula TRUNCATE se utiliza para eliminar todas las ocurrencias de una tabla, sin restricción. Esta cláusula ocupará una única línea en el diario. En caso de vuelta atrás, se recuperarán todas las ocurrencias. No será posible recuperar una parte de los registros. El interés de la cláusula TRUNCATE es que libera el espacio disponible eliminando filas en el archivo físico de la base. Otra ventaja de esta cláusula es que reinicializa el auto-incremento si esta opción se aplica a una columna.
Los triggers no se ejecutan.
Esta cláusula normalmente se utiliza por los administradores de base de datos.
Sintaxis
TRUNCATE TABLE <nombre tabla> Ejemplo
TRUNCATE TABLE Habitaciones; Las vistas
En esta sección vamos a ver cómo crear o eliminar vistas. Las vistas son elementos muy útiles en la programación SQL. Permiten principalmente crear tablas « virtuales » específicas para un dominio o para un tipo de usuarios.
1. ¿Por qué utilizar vistas?
En una base de datos, hay tablas permanentes que se han definido después de un análisis de las necesidades y un modelo en forma de tabla.
Si se respeta el modelo relacional, no hay datos redundantes a excepción de las claves que se utilizan para las uniones. Por contra, los usuarios o los desarrolladores tienen necesidad de extracciones específicas de datos. Estas extracciones se materializan en forma de consultas ejecutadas manualmente o incluso en los programas.
Si estas peticiones son repetitivas o comunes a muchos usuarios, puede ser necesario crear una vista. La vista es una representación lógica de la base de datos resultante de una consulta para una necesidad concreta y repetitiva. A diferencia de una tabla, no está almacenada en disco (salvo que se especifique) sino en memoria.
La vista también permite simplificar la base de datos para el usuario, quien no tiene que conocer la totalidad del esquema sino simplemente algunos elementos específicos útiles para su trabajo.
Si sus tablas tienen información confidencial, la vista permite ocultar ciertas columnas. Así el usuario solo ve lo que le queramos mostrar.
La creación de una vista sigue el mismo mecanismo que el CREATE TABLE … AS SELECT … explicado en las secciones anteriores. De hecho, la vista es una suma de columnas que provienen de una o más tablas.
La principal ventaja de una vista es que está permanentemente actualizada. De hecho, una vista se actualiza automáticamente cuando se modifica alguna de las tablas a las que hace referencia. Por contra, una vista no es un objeto propiamente dicho, sino un resultado de una consulta, por lo que no podemos actualizar datos sobre una vista. Los datos pertenecen a las tablas de SELECT.
Una vista representa en un instante dado la imagen de las tablas que utiliza.
2. La creación de vistas
La creación se realiza con el comando CREATE VIEW y a continuación un comando SELECT recupera las columnas que se quieren extraer de las tablas.
Igual que en el CREATE TABLE...
Los índices
En esta sección abordaremos un concepto importante: los índices. Todas las bases de datos utilizan índices. La implementación física de estos difiere de un SGBDR a otro.
Existen varios tipos de índices y varios métodos para tratarlos. Veremos cómo crear y eliminar estos índices y porqué utilizar un tipo de índice u otro en función de las necesidades existentes.
1. Los índices y la norma SQL
En primer lugar hay que indicar que los índices no forman parte de la norma SQL. De hecho, el índice se utiliza para acelerar una búsqueda en una tabla y se basa en los ficheros físicos que se crean cuando se crea el índice.
Se trata pues, de una implementación física y en la norma SQL igual que ocurre con las tablas, no se trata la parte física. Cada SGBDR lo implementa a su manera.
Por contra, los índices son prácticamente indispensables en una base de datos relacional. El tiempo de acceso a los datos es un parámetro muy importante para todos los usuarios y desarrolladores, la utilización o no de un índice puede aumentar el tiempo de respuesta de forma exponencial.
En el caso de tablas con millones de filas, el acceso concreto a un dato puede durar varias horas sin índice, o algunos segundos con índice.
Sin índice, se recorrerá toda la tabla hasta encontrar el registro que se quiere consultar.
Es el SGBDR el que genera los ficheros de índice, el usuario no puede intervenir en cómo se almacenan.
No obstante, tenga en cuenta no crear índices para todas las columnas. Se debe crear un índice según el uso que los programas y usuarios hagan de la tabla. Los índices ralentizan los procesos de actualización, ya que el SGBDR debe recalcular las claves después de cada inserción, eliminación o modificación de filas.
Hay que fijarse en las columnas, analizar las actualizaciones de una tabla y basarse en un análisis funcional de los datos, y a continuación consultar con los DBA las normas de la empresa y el método que utiliza por defecto el SGBDR.
2. Los diferentes métodos de organización de los índices
Existen cinco métodos principales de gestión de los índices:...
La integridad de los datos
Las restricciones de integridad permiten mantener la coherencia de la base de datos. Se confía al SGBDR las tareas de control de la validez de los datos que se insertan.
Las restricciones sustituyen a los controles realizados por un programa.
Existen varios tipos de controles. Es posible indicar al SGBDR:
-
qué valor por defecto se va a asignar a una columna (DEFAULT),
-
que una columna no pueda ser null (NOT NULL),
-
que una columna deba ser única (UNIQUE),
-
o codificar un control en una columna (CHECK).
Existen igualmente dos restricciones particulares que son la clave primaria y la clave extranjera. Vamos a detallar sus funciones.
1. La clave primaria (PRIMARY KEY)
Por definición, la clave primaria es la clave principal de una tabla. El SGBDR controlará en cada inserción o modificación que la clave sea única en la tabla. En caso contrario, rechaza la petición de modificación con un mensaje de error de tipo: «Violation constraint …».
La clave primaria siempre es una clave única. Se compone de una o varias columnas en función del método de creación; lo más importante es que no puede haber dos filas de la tabla con la misma clave.
A menudo se trata de un número que se incrementa de uno en uno en cada inserción de una fila en la tabla.
También se pueden utilizar datos de empresa, como los números de la seguridad social o del permiso de conducir, pero es necesario que todas las filas de la tabla tengan un valor para esta o estas columnas. De hecho, una clave primaria no puede tomar el valor NULL.
La creación de una clave primaria genera en la mayor parte de los SGBDR la creación automática de un índice en esta columna.
Hay dos métodos para declarar una clave primaria. Si la clave corresponde a una sola columna, hay que utilizar esta sintaxis:
Ejemplo: declaración de una clave primaria con una columna
CREATE TABLE Habitaciones (idHabitacion INTEGER PRIMARY KEY,
Hotel INTEGER,
TipoHabitacion INTEGER,
...Ejercicios
Primer ejercicio
A partir del contenido de la siguiente tabla, escriba la sintaxis de creación de la tabla PELICULAS.
Cree una clave primaria con la columna IDENT_PELICULA y un índice no único sobre la columna GENERO 1 vinculado a PAIS.
|
Ident_ PELI CULA |
TITULO |
GENERO1 |
RECAUDA CION |
FECHA_ ESTRENO |
PAIS |
NUM_ ENTRA DAS |
Fecha y hora de alta |
Sinopsis |
|
1 |
SUBWAY |
POLICIACA |
390 659,52 |
10/04/85 |
1 |
2 917 562 |
25/05/11 11:31 |
Cuenta las aventuras de la población subterránea en los túneles del metro de París. |
|
2 |
NIKITA |
DRAMA |
5 017 971,00 |
21/02/90 |
1 |
3 787 845 |
15/04/11 09:30 |
Nikita, condenada a cadena perpetua, es obligada a trabajar en secreto para el gobierno como agente muy cualificada de los servicios secretos. |
|
3 |
STAR WARS 6 - EL RETOR NO DEL JEDI |
ACCIÓN |
191 648 000,00 |
19/10/83 |
2 |
4 263 000 |
01/01/10 08:00 |
El imperio galáctico es más poderoso que nunca: la construcción de la nueva arma, la Estrella de la Muerte, amenaza a todo el universo. |
Segundo ejercicio
Añadir una columna llamada NUM_REAL.
Esta columna es una clave extranjera en la tabla REALIZADOR.
Añadir un valor por defecto en la columna RECAUDACION con el valor 0.
Poner las columnas TITULO y PAIS como NOT NULL.
Eliminar la restricción.
Tercer ejercicio
Crear una vista PELICULAS2 a partir de la tabla PELICULAS que contenga las cuatro primeras columnas...
Soluciones de los ejercicios
Primer ejercicio
Consulta en formato estándar:
DROP TABLE PELICULAS;
CREATE TABLE PELICULAS (IDENT_PELICULA INTEGER PRIMARY KEY,
TITULO VARCHAR(50),
GENERO1 VARCHAR(20),
RECAUDACION DECIMAL(15,2),
FECHA_ESTRENO DATE,
PAIS SMALLINT,
NUM_ENTRADAS INTEGER,
SINOPSIS VARCHAR(2000),
FECHA_ALTA TIMESTAMP
); Descripción Oracle de tabla:
DESC PELICULAS
Nombre ...