apunte 5

Vista previa del material en texto

Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 1 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
4.4. SQL: UN LENGUAJE DE CONSULTA RELACIONAL. 
A partir de la especificación de las operaciones definidas en el Álgebra Relacional, estamos en 
condiciones de entender los tipos de solicitudes que podemos efectuar en una base de datos relacional. 
La especificación de las operaciones del Álgebra Relacional implica el orden o secuencia en que éstas 
deben realizarse. La mayoría de los motores de bases de datos relacionales cuentan con una interfaz de 
lenguaje declarativo de alto nivel, de modo que el usuario solo indica cuál es el resultado deseado 
dejando que el motor se encargue de la optimización y de las decisiones sobre cómo se efectuará la 
consulta. 
De entre los lenguajes declarativos señalados, el más utilizado es SQL. Hemos hablado anteriormente 
de este lenguaje y de sus sublenguajes. SQL es un lenguaje completo: cuenta con enunciados para 
definición, consulta y actualización de los datos, mecanismos para definición de vistas, para la creación y 
borrado de índices, creación de stored procedures, triggers y para la incorporación de enunciados SQL 
en lenguajes de programación de alto nivel. 
CREACIÓN DE LA BASE DE DATOS. 
La definición de la base de datos implica la asignación de un nombre a la base de datos y, 
eventualmente, la identificación del usuario que es propietario de la base de datos. 
Para poder crear una base de datos, el usuario debe contar con los privilegios necesarios. Es decir, no 
cualquier usuario está autorizado para crear una base de Datos. 
La instrucción para la creación de la base de datos tiene la siguiente sintaxis: 
CREATE DATABASE Nombre; 
 
Por ejemplo: 
CREATE DATABASE Alumnos; 
Existen otros parámetros que dependerán del motor a utilizar, veamos el caso de Postgres: 
CREATE DATABASE name 
 [ [ WITH ] [ OWNER [=] user_name ] 
 [ TEMPLATE [=] template ] 
 [ ENCODING [=] encoding ] 
 [ LC_COLLATE [=] lc_collate ] 
 [ LC_CTYPE [=] lc_ctype ] 
 [ TABLESPACE [=] tablespace ] 
 [ CONNECTION LIMIT [=] connlimit ] ] 
Parámetros 
name: El nombre de la base de datos a crear. 
user_name: El nombre del usuario que sera dueño de la nueva base de datos 
template: El nombre de la plantilla desde la cual se creará la base de datos. Por defecto se utilizará 
template1. 
encoding: Conjunto de caracteres a utilizar, se especifica a través de una cadena de caracteres o 
número de codificación, ver tabla de conjuntos en la documentación de postgres por defecto se utilizará 
el seteo de la plantilla. 
lc_collate: Order de colación a usar, afecta a los ordenamientos de caracteres. Por ejemplo para los 
ORDER BY de los SELECT 
lc_ctype: Clasificación de caracteres. Afecta a la categorización de caracteres por ejemplo mayúsculas, 
minúsculas o dígitos. 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 2 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
tablespace: Nombre del tablespace asociado a la base de datos. Por defecto se utilizará el de la 
plantilla. Ver CREATE TABLESPACE en la documentación de postgres para mas información. 
connlimit: Usuarios concurrentes aceptados para la base de datos. -1 es el valor por defecto que quiere 
decir: ilimitado. 
CREACIÓN DE TABLAS. 
Para crear una tabla que representa una relación, debemos especificar su nombre, sus atributos y sus 
restricciones. 
Los atributos se especifican primero, a cada uno se les da un nombre y para cada uno se define el 
dominio y, si la tiene, la restricción de dominio, dentro de las cuales se tiene en cuenta la restricción de 
integridad de las entidades. Posteriormente se especifican las restricciones de clave y luego las de 
integridad referencial. 
La instrucción para la creación de una tabla tiene la siguiente sintaxis (simplificada) 
CREATE TABLE nombre_tabla ( 
 nombre_columna tipo_de_dato [ DEFAULT expr ] [ restricciones_de_columna ], 
 nombre_columna tipo_de_dato [ DEFAULT expr ] [ restricciones_de_columna ], 
 ... 
 ... 
 nombre_columna tipo_de_dato [ DEFAULT expr ] [ restricciones de columna ], 
 [ restricciones_de_tabla] 
) 
 
SELECCIÓN DEL TIPO DE DATOS. 
Cada motor de base de datos maneja diferentes tipos de datos pero en general son: 
 Enteros 
 Reales 
 Alfanuméricos de longitud variable 
 Alfanuméricos de longitud fija 
 Fecha y Hora 
En los anexos 1 y 2 se especifican los tipos de datos manejados por Sql Server y por Postgres 
respectivamente 
El dominio de cada uno de los atributos puede especificarse directamente indicando el tipo de datos. 
Otra alternativa es declarar un dominio y luego utilizar su nombre. Esto hace más fácil cambiar el tipo de 
datos de un dominio y es sumamente útil cuando varios atributos tienen ese dominio. 
La creación de un dominio de hace a través de la instrucción CREATE DOMAIN, cuya sintaxis es la 
siguiente: 
CREATE DOMAIN nombre_dominio AS tipo_de_datos 
Por ejemplo: 
CREATE DOMAIN documento AS VARCHAR (8) 
CREACIÓN Y UTILIZACIÓN DE RESTRICCIONES DE COLUMNA Y DE TABLA. 
 
Restricción de clave. 
Como sabemos, no puede haber relaciones sin claves primarias. Por lo tanto, utilizaremos la cláusula 
PRIMARY KEY, con la siguiente sintaxis: 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 3 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
A nivel de columna: 
nombre_columna tipo_de_dato PRIMARY KEY, 
A nivel de tabla: 
PRIMARY KEY (Lista de atributos) 
Las claves candidatas también tienen que tener definida su condición de unicidad. Para ello utilizamos la 
cláusula UNIQUE a nivel de tabla, con la siguiente sintaxis: 
UNIQUE (Lista de atributos) 
Restricción de integridad de las entidades. (Restricciones a nivel de columna) 
En algunos casos los atributos pueden tener restricciones de dominio. El caso más claro es el del 
atributo o conjunto de atributos que se define como clave primaria de la relación que estamos 
representando en la tabla. 
La restricción de integridad de las entidades expresa, como sabemos, que ningún valor de la clave 
primaria puede ser nulo. La forma de indicar esto es hacer la especificación de esta prohibición utilizando 
la cláusula NOT NULL como parte de la restricción del dominio del atributo o conjunto de atributos. 
nombre_columna tipo_de_dato NOT NULL, 
Este condicionamiento de NOT NULL no solo se utiliza para declarar la restricción de integridad de las 
entidades, sino también para hacer la especificación para cualquier otro atributo que no deba contener 
valores nulos como, por ejemplo, un atributo o conjunto de atributos que sea clave candidata en la 
relación representada por la tabla. En la mayoría de los motores de bases actuales, no es necesaria la 
declaración NOT NULL para claves primarias 
Es posible definir también un valor por omisión que contendrá un atributo. Esto se hace anexando la 
cláusula DEFAULT de la forma: 
nombre_columna tipo_de_dato DAFAULT expresión, 
Así, cada vez que se agregue una tupla y no se consigne un valor determinado para ese atributo, el 
motor automáticamente colocará el valor por omisión. Pero cuidado: ante la ausencia de un valor por 
omisión y de la especificación de NOT NULL, se asignará el valor nulo al atributo. 
Restricción de integridad referencial. 
En una base de datos relacional, la integridad referencial se asegura utilizando las claves foráneas. Por 
lo tanto, debemos definir el atributo o conjunto de atributos que en una relación es clave foránea y, 
además, indicar con qué otra relación define el vínculo. 
Para ello utilizamos la cláusula FOREIGN KEY, con la siguiente sintaxis: 
FOREIGN KEY <(Lista de atributos 1)> REFERENCES <Nombre de la relación [(Lista de 
atributos 2)]> 
Lista de atributos 1 contiene elconjunto de atributos que es clave foránea y Lista de atributos 2 contiene 
el conjunto de atributos que es clave primaria en la relación referenciada. Notemos que no es necesario 
que los atributos que son clave foránea en una relación tengan el mismo nombre que tienen como clave 
primaria en la relación referenciada. Si es así, no es necesario especificar Lista de atributos 2. 
Uno de los efectos de la restricción de integridad referencial es el de asegurar la propagación de las 
actualizaciones. Es decir, el motor debe saber qué hacer cuando se pretende modificar el valor de un 
atributo que es clave primaria en una tupla referenciada o cuando se pretende borrar tal tupla 
referenciada. 
Es por ello que, cuando se especifica la declaración de restricción de integridad referencial, se suele 
indicar la acción a seguir para cada uno de estos casos, la que se denomina acción disparada por 
integridad referencial (la sintaxis y las opciones pueden diferir en los distintos motores de bases de 
datos). Las opciones son: 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 4 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
antes que nada debe especificarse el evento disparador: 
 al eliminar: ON DELETE 
 al modificar: ON UPDATE. 
 
y luego se indica la acción: 
 poner nulo: SET NULL, 
 propagar: CASCADE, 
 poner por omisión: SET DEFAULT 
 no hacer nada: NO ACTION (Directamente no permite la actualización) 
La ausencia de una indicación concreta sobre cómo proceder al eliminar y al modificar, hará que el motor 
no permita ni el borrado de una tupla ni la modificación de valores de clave primaria de una tupla cuando 
al tupla esté referenciada. 
Veamos como ejemplo la creación de la base de datos Cursado, que contendrá las tablas Alumnos, 
Localidades e Inscripciones: 
CREATE DATABASE Cursado WITH OWNER = postgres ; 
 
CREATE DOMAIN nombre AS varchar(60); 
 
CREATE TABLE localidades( 
 Cp varchar(10) Not Null, 
 nombre nombre, 
 PRIMARY KEY (cp)); 
 
CREATE TABLE alumnos( 
 lu integer Not Null, 
 nombre nombre, 
 cp varchar(10), 
 PRIMARY KEY (lu), 
 FOREIGN KEY (cp) REFERENCES localidades 
 ON DELETE Set Null 
 ON UPDATE Cascade); 
 
CREATE TABLE materias 
 codmat integer Primary key, 
 anio integer Not Null, 
 cuatr integer, 
 nombre Nombre Not Null); 
 
CREATE TABLE inscripciones( 
 lu integer Not Null, 
 codmat integer Not Null, 
 anio integer Not Null, 
 condicion varchar(2), 
 PRIMARY KEY (lu, codmat, anio), 
 FOREIGN KEY (codmat) REFERENCES materias 
 ON DELETE NO ACTION 
 ON UPDATE Cascade, 
 FOREIGN KEY (lu) REFERENCES alumnos 
 ON DELETE Set Null 
 ON UPDATE Cascade ); 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 5 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
MODIFICACIONES DE LA ESTRUCTURA DE LA BASE DE DATOS 
Una vez que hemos creado la base de datos y definido las tablas, podemos proceder a la creación del 
conjunto de índices que actuarán sobra cada una de las tablas base. 
Los índices se crean a través de la instrucción CREATE INDEX, que tiene la siguiente sintaxis general: 
CREATE [ UNIQUE ] INDEX < Nombre del índice > 
ON TABLE < Nombre de la tabla base > 
( < expr1 > [ orden ] [< expr2 > [ orden ] . . . ); 
La condición UNIQUE se especifica cuando el índice es creado sobre cualquier conjunto de atributos que 
sea clave candidata, no siendo necesaria la creación de un índice por el atributo o conjunto de atributos 
que definen la clave primaria de una relación. 
Expr puede ser un nombre de atributo o una expresión válida en base a un atributo 
Por ejemplo: 
CREATE INDEX PorNombre 
ON TABLE Alumnos (Nombre asc); 
Es posible eliminar una base de datos completa con la siguiente sentencia SQL: 
DROP DATABASE < Nombre de la base de datos >; 
Igualmente sucede si deseamos eliminar una tabla base que pertenece a un esquema de una base de 
datos. La instrucción DROP TABLE se usa en este caso con alguna de las cláusulas RESTRICT O 
CASCADE. 
DROP TABLE < Nombre de la tabla > [CASCADE | RESTRICT]; 
Si utilizamos la cláusula RESTRICT, la tabla solo se eliminará si no está referenciada en ninguna 
restricción de integridad referencial. 
De manera similar podemos eliminar índices de una tabla base. En esta caso, no es necesaria la 
especificación ni de CASCADE ni de RESTRICT, porque la eliminación de los índices no tienen ninguna 
influencia en el mantenimiento de la integridad. Por ejemplo: 
DROP INDEX PorNombre; 
Es posible que una tabla deba sufrir modificaciones de agregado, modificación o eliminación de sus 
atributos. Estas modificaciones se hacen utilizando la instrucción ALTER TABLE: 
ALTER TABLE <tabla> ADD <atributo> <Dominio> [<Restricción>]; 
ALTER TABLE <tabla> DROP <atributo> <Opción de eliminación>; 
ALTER TABLE <tabla> ALTER COLUMN <atributo> TYPE <dominio>; 
 
Por ejemplo: 
ALTER TABLE Cursado.Alumnos ADD Domicilio Char(60); 
ALTER TABLE Cursado.Alumnos DROP Domicilio CASCADE; 
ALTER TABLE inscripciones ALTER COLUMN condicion TYPE varchar(3); 
 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 6 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
Anexo 1: 
Especificaciones particulares para Transact SQL (Sybase y MS SQL Server) 
NUMERICOS 
bigint: enteros desde -2^63 (-9223372036854775808) hasta 2^63-1 (9223372036854775807). 
int: enteros desde -2^31 (-2,147,483,648) hasta 2^31 - 1 (2,147,483,647). 
smallint: enteros desde 2^15 (-32,768) hasta 2^15 - 1 (32,767). 
tinyint: enteros positivos desde 0 a 255. 
bit: 1 o 0. 
decimal: Decimales de presción fija desde -10^38 +1 hasta 10^38 –1. (el tipo de datos numeric es 
equivalente) 
money: Valores monetarios desde -2^63 (-922,337,203,685,477.5808) hasta 2^63 - 1 
(+922,337,203,685,477.5807), con una exactitud de una diez milésima parte de una unidad monetaria. 
smallmoney: Valores monetarios desde -214,748.3648 hasta +214,748.3647, con exactitud a una diez 
milésima parte de una unidad monetaria. 
float: Números de punto flotante desde -1.79E + 308 hasta 1.79E + 308. 
real: Números de punto flotante desde -3.40E + 38 hasta 3.40E + 38. 
FECHAS 
datetime: Fecha y hora desde 1 de enero de 1753, hasta 31 de diciembre de 9999, con una exactitud 
de tres-centésimo de un segundo, o 3.33 milisegundos. 
smalldatetime: Fecha y hora desde 1 de Enero de 1900 hasta el 6 de junio de 2079, con exactitud de 
un minuto. 
CADENAS DE CARACTERES 
char: Longitud fija de caracteres non-Unicode cuya longitud máxima es de 8000 caracteres. 
varchar: Longitud variable de caracteres non-Unicode cuya longitud máxima es de 8000 caracteres. 
text: Longitud variable de caracteres non-Unicode cuya longitud máxima es de 
2^31 - 1 (2,147,483,647) caracteres. 
CADENAS ESPECIALES UNICODE 
nchar: Longitud fija de caracteres Unicode con un máximo de 4000 caracteres. 
nvarchar: Longitud variable de caracteres Unicode con un máximo de 4000 caracteres. 
ntext: Longitud variable de caracteres Unicode con un máximo de of 2^30 - 1 (1,073,741,823) 
caracteres. 
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ia-iz_3ss4.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ia-iz_3ss4.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ia-iz_3ss4.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ia-iz_3ss4.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ba-bz_2it0.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_de-dz_3grn.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ma-mz_49q1.htmmk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ma-mz_49q1.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_fa-fz_6r3g.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_fa-fz_6r3g.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_da-db_9xut.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_da-db_9xut.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ca-co_7tpu.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ca-co_7tpu.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ta-tz_2838.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_na-nop_9msy.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_na-nop_9msy.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_nos-nz_0lyd.htm
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 7 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
CADENAS BINARIAS 
binary:Datos binarios de longitud fija cuyo límite es de 8,000 bytes. 
varbinary: Datos binarios de longitud variable cuyo límite es de 8,000 bytes. 
image: Datos binarios de longitud variable cuyo límite es de 2^31 - 1 (2,147,483,647) bytes. 
 
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ba-bz_75yx.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ba-bz_75yx.htm
mk:@MSITStore:C:/Archivos%20de%20programa/Microsoft%20SQL%20Server/80/Tools/Books/tsqlref.chm::/ts_ia-iz_9rfp.htm
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 8 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
Anexo 2 
Especificaciones particulares para Postgresql. 
 
Fuente: http://www.postgresql.org/docs/9.1/static/datatype.html 
NUMERICOS 
Tipo Almacena Descripción Rango 
smallint 2 bytes Enteros de rango pequeño -32768 a +32767 
integer 4 bytes Opción típica para enteros -2147483648 a +2147483647 
bigint 8 bytes Enteros de rango ampliado -9223372036854775808 a 9223372036854775807 
decimal o 
numeric 
variable 
Precisión especificada por el 
usuario 
Hasta 131072 dígitos antes del punto decimal; hasta 
16383 dígitos después del punto decimal. 
real 4 bytes Precisión variable 6 decimales. 
double precision 8 bytes Precisión variable 15 decimales. 
serial 4 bytes Entero autoincremental 1 a 2147483647 
bigserial 8 bytes Entero largo autoincremental 1 a 9223372036854775807 
FECHAS 
Tipo Almacena Descripción Desde Hasta Resolución 
timestamp [without 
time zone] 
8 bytes 
Fecha y hora sin zona 
horaria 
4713 AC 294276 DC 
1 microsecond / 14 
digits 
timestamp with 
time zone 
8 bytes 
Fecha y hora con zona 
horaria 
4713 AC 294276 DC 
1 microsecond / 14 
digits 
Date 4 bytes Solo fecha 4713 AC 5874897 DC 1 day 
time [without time 
zone] 
8 bytes Hora sin zona horaria 00:00:00 24:00:00 
1 microsecond / 14 
digits 
time with time 
zone 
12 bytes Hora con zona horaria 00:00:00+1459 
24:00:00-
1459 
1 microsecond / 14 
digits 
interval [fields] 12 bytes Intervalo de tiempo 
-178000000 
years 
178000000 
years 
1 microsecond / 14 
digits 
CADENAS DE CARACTERES 
Tipo Descripción 
character varying(n), varchar(n) Longitud variable con límite superior. 
character(n), char(n) Longitud específica completada con blancos. 
text Longitud variable ilimitada. 
 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. LAS 
BASES DE DATOS I Página 9 
 
 
Lic. Patricia Mac Gaul - Lic. Martín Díaz - Lic. Guillermo Villanueva 
CADENAS BINARIAS 
Tipo Almacena Descripción 
bytea 1 o 4 bytes mas la cadena binaria actual. Cadena binaria de longitud variable. 
CADENAS BINARIAS 
Tipo Almacena Descripción 
boolean 1 byte 
Verdadero o falso: 
TRUE 't' 'true' 'y' 'yes' 'on' '1' 
FALSE 'f' 'false' 'n' 'no' 'off' '0'