Transcript Chapter 4: SQL

SQL-Data Definition Language (DDL)
Lenguaje de Definición de Datos
 Esquema de cada relación.
 Dominios de cada atributo.
 Restricciones de integridad.
 Restricciones de seguridad para cada relación.
4.1
Tipos de Datos en SQL
(volveremos sobre ellos)
 char(n). Cadena de longitud fija. La longitud es n caracteres.
 varchar(n). Cadena de longitud variable. La longitud máxima es n







caracteres. (text)
int/integer. Entero.
smallint. Entero corto.
numeric(p,d). Numero en formato de coma fija, con precisión de p dígitos,
con d dígitos a la derecha de la coma decimal. (1-> 0.9999)
real, double precision. numero en coma flotante y número en coma
flotante con doble precisión.
float(n). Número en coma flotante con una precisión no menor de n
dígitos.
El valor NULL esta permitido para todos los atributos a menos que se
prohíba explícitamente. not null prohíbe el uso del valor NULL.
La construcción create domain en SQL-92 crea tipos de datos definidos
por el usuario
create domain nombre-persona char(20) not null
(typedef in C)
4.2
Ejemplo de Dominio
hacer
--drop function domain_username_constraint_check(text) ;
create or replace function
domain_username_constraint_check (text)
returns boolean as '
select
case
when (length($1) >= 6) then true
else false
end
;
' language 'sql';
--drop domain username;
create domain username as text
check (domain_username_constraint_check(value));
create TABLE midominio2( nombre username);
--version equivalente
create TABLE midominio2( nombre text check (length(nombre) >=
6));
4.3
Día y Hora en SQL (Cont.)practica fecha creacion
 date. Fecha (día del año), año (4 dígitos), mes y dia
 Ej. date ‘2001-7-27’
 time. hora del día, en horas, minutos y segundos.
 E.j. time ’09:00:30’
time ’09:00:30.75’
 timestamp: día y hora
 E.j. timestamp ‘2001-7-27 09:00:30.75’
 Interval: periodo de tiempo
 E.j. Interval ‘1’ day
 la diferencia entre “date/time/timestamp” da un “interval”
 “Interval” se puede sumar a “date/time/timestamp”
 Se pueden extraer valores independientes de
“date/time/timestamp”
 E.j. extract (year from r.comienzo)
4.4
Creación de Tablas

ejemplo
Para crear una tabla se usa la orden CREATE TABLE. Es
necesario especificar (al menos) el nombre de la tabla, los
nombres de las columnas y el tipo de dato. Por ejemplo:
CREATE TABLE tablita (
nombre1 char(20), -- hola
nombre2 integer
-- que tal
);
 Este comando crea una tabla llamada tablita con dos
columnas llamadas nombre1 y nombre2.
4.5
Destrucción de Tablas

Cuando las tablas no sean necesarias se pueden borrar con la
orden DROP TABLE. Por ejemplo:
DROP TABLE tablita;
 Todos los comandos CREATE tienen una pareja DROP
4.6
Columnas Auxiliares Creadas por PostgeSQL
ejemplo, pgacces
 Cada tabla tienen varias columnas definidas por la
base de datos. Los nombres de estas columnas
auxiliares no se pueden usar como nombres de las
columnas definidas por el usuario.
oid
tableoid
xmin
cmin
xmax
cmax
ctid
4.7
Valores por Defecto
ejemplo
 Se le puede asignar un valor por defecto a una columna.
Cuando se crea la tupla si no se le asigna ningún valor a a
alguna de las columnas está cogerá su valor por defecto. (si no
se declara explícitamente el valor por defecto es NULL)
 A la hora de definir la tabla los valores por defecto van tras la
declaración de la columna. Esto es:
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text, --soy un comentario
precio numeric(10,2) DEFAULT 9.99
);
4.8
Valores por Defecto
ejemplo
CREATE SEQUENCE producto_no_seq;
CREATE TABLE productos (
producto_no
integer PRIMARY KEY DEFAULT
nextval('producto_no_seq'),
nombre text,
precio numeric(10,2) DEFAULT 9.99
);
CREATE UNIQUE INDEX producto_no ON productos (
producto_no );
4.9
Restricciones

“ Check”, Restriccion arbitraria

“ Not-Null”, El atributo no acepta valores nulos

“ Unique”, El atributo no acepta valores repetidos

“ Primary Keys”, El atributo es clave primaria

“ Foreign Keys”, El atributo es clave extranjera
4.10
CHECK
ejemplo
 Permite especificar que los valores de una columna deben
satisfacer una expresión. Por ejemplo ser positivo.
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric(10.2) CHECK (precio > 0)
);
 La restricción debe definirse DESPUES del tipo de dato. Se
define usando la palabra CHECK seguida de una expresión
entre paréntesis
4.11
CHECK
ejemplo
 Permite especificar que los valores de una columna deben
satisfacer una expresión. Por ejemplo ser positivo.
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric(10,2) CONSTRAINT
precio_positivo CHECK (precio > 0)
);
 CONSTRAINT nombre_ligadura puede usarse con las otras
restricciones
4.12
CHECK

Las restricciones pueden involucrar varias columnas pero no
varias tablas.
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric CHECK (price > 0),
precio_rebajado
numeric CHECK
(precio_rebajado > 0),
CHECK (precio > precio_rebajado )
);
4.13
CHECK, Advertencia

“CHECK” se satisface si la expresion evaluada es “TRUE” o
“NULL”.
4.14
NOT NULL
ejemplo
 Indica que el atributo no puede valer “NULL”
CREATE TABLE productos (
producto_no
integer NOT NULL,
nombre text NOT NULL,
precio
numeric(10,2)
);
4.15
NOT NULL
 Pueden existir varias restricciones referidas al mismo atributo, el
order no importa.
CREATE TABLE productos (
producto_no integer NOT NULL,
nombre text NOT NULL,
precio numeric(10,2) NOT NULL CHECK (precio
> 0)
);
4.16
UNIQUE
ejemplo
 Asegura que un determinado valor no esta repetido en una
columna.
CREATE TABLE productos (
producto_no integer UNIQUE,
nombre text,
precio numeric(10,2)
);
4.17
UNIQUE
ejemplo
 Alternativamente se puede usar la sintaxis
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric,
UNIQUE(producto_no,nombre)
);
 Pregunta: ¿producto_no y nombre deben ser únicos o la
pareja debe ser única?
4.18
UNIQUE
ejemplo
 ¿Esta tabla es equivalente a la anterior?
CREATE TABLE productos (
producto_no integer UNIQUE,
nombre text UNIQUE,
precio numeric(10.2)
);
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric(10.2),
UNIQUE(producto_no,nombre)
);
4.19
UNIQUE vs CHECK
CREATE TABLE example (
CREATE TABLE example (
a integer,
a integer,
b integer,
b integer,
c integer,
c integer,
UNIQUE (a, c)
);
CHECK (a > 0 AND b > 0)
);
4.20
Clave Primaria
ejemplo
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text,
precio numeric
);
CREATE TABLE ejemplo (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
4.21
Clave Primaria: ¿Es esto equivalente?
CREATE TABLE productos (
a integer PRIMARY KEY,
b text PRIMARY KEY,
c numeric
);
CREATE TABLE ejemplo (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
4.22
ejemplo
Pregunta:
¿Es equivalente UNIQUE a PRIMARY KEY?
4.23
Clave Extranjera

ejemplo,trigger
La restriccion “REFERENCES” asegura que los valores de una
determinada columna debe ser identicos a los valores que
aparecen en otra determinada columna que puede estar en otra
tabla
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text,
precio numeric);
CREATE TABLE pedidos (
pedido_no integer PRIMARY KEY,
producto_no integer REFERENCES productos
(producto_no),
cantida integer);
 PostgeSQL no nos dejara crear pedidos sobre productos que no
existan
4.24
Triggers (una interrupción)
ejemplo
 Un “trigger” es un pedazo de codigo SQL que se ejecuta
automaticamente cuando se invoca una cierta acción
 SQL 99 (subset)
 Por ejemplo: Para guardar la fecha de actualización:
4.25
Triggers
template1,plpgsql-sql
CREATE TABLE tomate( tomate_no int PRIMARY KEY,
color
char(10),
modificadoen timestamp);
DROP FUNCTION modificacion();
CREATE FUNCTION modificacion() RETURNS TRIGGER AS '
BEGIN
NEW.modificadoen := ''now'';
RETURN NEW;
END;'LANGUAGE 'plpgsql' WITH (isstrict);
DROP TRIGGER t_modificacion on tomate;
CREATE TRIGGER t_modificacion
BEFORE INSERT
ON tomate
FOR EACH ROW EXECUTE PROCEDURE modificacion();
4.26
Clave Extranjera
 ¿Qué pasa si se borra el producto_no en la tabla
productos?
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text,
precio numeric);
CREATE TABLE pedidos (
pedido_no integer PRIMARY KEY,
producto_no integer REFERENCES productos
(producto_no),
cantida integer);
 ¿Qué pasa si se borra/cambia el producto_no en la tabla
productos?
4.27
Poblar las tablas anteriores
INSERT into
productos VALUES (1,'aaa',12.1);
INSERT into
productos VALUES (2,'bbb',12.1);
INSERT into
pedidos VALUES (1,1,5);
INSERT into
pedidos VALUES (2,3,7);
4.28
Clave Extranjera
ejemplo
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text,
precio numeric);
CREATE TABLE pedidos (
pedido_no integer PRIMARY KEY,
producto_no integer REFERENCES productos
(producto_no),
cantida integer);
CREATE TABLE producto_pedido (
producto_no integer REFERENCES productos ON DELETE
RESTRICT,
pedido_no integer REFERENCES pedidos ON DELETE
CASCADE,
cantidad integer,
PRIMARY KEY (producto_no, pedido_no)
);
“According to the SQL standard, specifying either RESTRICT or CASCADE is
required. No database system actually implements it that way, but whether the
4.29
default behavior is RESTRICT or CASCADE
varies across systems. “
Clave primaria y Clave Extranjera
 La clave primaria y extranjera pueden usar los mismos atributos
CREATE TABLE producto_pedido (
producto_no integer REFERENCES productos,
pedido_id integer REFERENCES orders,
cantidad integer,
PRIMARY KEY (producto_no, pedido_id)
);
 Importante, una clave extranjera no puede ser clave extranjera
de otra relación.
CREATE TABLE inventario (
product_no integer REFERENCES pedidos
producto_no,
4.30
Modificar tablas:“Drop” y “Alter”
ejemplo
 La orden drop table nombre_de_la_tabla elimina la tabla
nombre_de_la_tabla (y toda información relacionada con
ella) de la base de datos.
 La orden alter table nombre_de_la_tabla se usa para
añadir atributos a una relación.
alter table productos add column A integer
El valor inicial de los atributos es NULL (a menos que se
especifique un valor por defecto).
 La orden alter table se puede usar para borrar atributos de
una tabla
alter table productos drop A
4.31
Añadir/Modificar una restricción ejemplo
 ALTER TABLE productos ADD CHECK (name <> '');
 ALTER TABLE productos ADD CONSTRAINT some_name
UNIQUE (producto_no);
 ALTER TABLE productos ADD FOREIGN KEY
(producto_group_id) REFERENCES product_groups;
 ALTER TABLE productos ALTER COLUMN product_no
SET NOT NULL;
 ALTER TABLE products ALTER COLUMN price SET
DEFAULT 7.77;
 ALTER TABLE products RENAME COLUMN product_no TO
product_number;
4.32
Herencia ejemplo
 Una tabla puede estar basada en otra (ISA)
CREATE TABLE ciudades (
nombre
char(30),
poblacion
float,
altura
int
--
en metros
);
CREATE TABLE capitales (
pais
char(30)
) INHERITS (ciudades);
 Cada tupla de capitales contiene TODOS los atributos de ciudades.
 ¿Qué pasa con capitales si “altero” ciudades?
4.33
Etc…
 Las tablas son los objeto “centrales” en una base de datos pero
no son los únicos objetos que existen en una base de datos:
 Vistas
 Funciones
 Triggers
 etc…
4.34
Ejemplo Banco
Ejemplo Banco I
sucursal (nombre-sucursal, ciudad-sucursal, capital)
cliente (nombre-cliente, calle-cliente, ciudad-cliente)
cuenta (numero-cuenta, nombre-sucursal, saldo)
prestamo (numero-prestamo, nombre-sucursal, cantidad)
cliente-cuenta (nombre-cliente^, número-cuenta^)
cliente-prestamo (nombre-cliente^, numero-prestamo^)
4.36
Ejemplo Banco II
error/ejemplo
CREATE TABLE sucursal(
nombre-sucursal char(30),
ciudad-sucursal char(30),
capital char(30),
PRIMARY KEY (nombre-sucursal)
)
CREATE TABLE cuenta(
numero-cuenta numeric(25),
nombre-sucursal char(30),
saldo numeric(15,2) NOT NULL,
PRIMARY KEY (numero-cuenta )
)
CREATE TABLE cliente(
nombre-cliente char(30),
calle-cliente char(30),
ciudad-cliente char(30),
PRIMARY KEY (nombre-cliente )
)
CREATE TABLE prestamo (
numero-prestamo numeric(25),
nombre-sucursal char(30),
cantidad numeric (15,2) NOT
NULL,
PRIMARY KEY (numero-cuenta )
)
 Debemos poner NOT NULL
a todo?
 Hay al menos tres errores en
estas ordenes:
 ;
 - vs. _
 numero_cuenta
4.37
Ejemplo Banco III
CREATE TABLE cliente_cuenta (
nombre_cliente
char(30) REFERENCES cliente,
numero_cuenta
numeric(25) REFERENCES cuenta,
PRIMARY KEY (nombre_cliente, número_cuenta)
);
CREATE TABLE cliente_prestamo (
nombre_cliente
numero_prestamo
char(30) REFERENCES cliente,
numeric(25) REFERENCES prestamo,
PRIMARY KEY (nombre_cliente, número_prestamo)
);
4.38
Última pregunta
 CREATE TABLE i1 (i int, I int);
 CREATE TABLE i2 ('i' int, 'I' int);
 CREATE TABLE i3("i" int, "I" int);
 comentar pgaccess
4.39
FIN