SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos

1. 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.

2. 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)

3. Ejemplo de Dominiohacer --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. 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)

5. 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.

6. 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

7. 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

8. 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 );

9. 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 );

10. 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

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) 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

12. 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) CONSTRAINTprecio_positivoCHECK (precio > 0) ); • CONSTRAINT nombre_ligadura puede usarse con las otras restricciones

13. 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 ) );

14. CHECK, Advertencia “CHECK” se satisface si la expresion evaluada es “TRUE” o “NULL”.

15. 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) );

16. 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) );

17. 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) );

18. 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?

19. 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) );

20. UNIQUE vs CHECK CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); CREATE TABLE example ( a integer, b integer, c integer, CHECK (a > 0 AND b > 0) );

21. 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) );

22. Clave Primaria: ¿Es esto equivalente? ejemplo 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) );

23. Pregunta: ¿Es equivalente UNIQUE a PRIMARY KEY?

24. 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

25. 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:

26. Triggerstemplate1,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();

27. 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?

28. 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);

29. 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 default behavior is RESTRICT or CASCADE varies across systems. “

30. 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,

31. 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 tablenombre_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

32. 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;

33. 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?

34. 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…

35. Ejemplo Banco

36. 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^)

37. 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 cliente( nombre-cliente char(30), calle-cliente char(30), ciudad-cliente char(30), PRIMARY KEY (nombre-cliente ) ) Debemos poner NOT NULL a todo? CREATE TABLE cuenta( numero-cuenta numeric(25), nombre-sucursal char(30), saldo numeric(15,2) NOT NULL, PRIMARY KEY (numero-cuenta ) ) CREATE TABLE prestamo ( numero-prestamo numeric(25), nombre-sucursal char(30), cantidad numeric (15,2) NOT NULL, PRIMARY KEY (numero-cuenta ) ) Hay al menos tres errores en estas ordenes: ; - vs. _ numero_cuenta

38. 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 char(30) REFERENCES cliente, numero_prestamo numeric(25) REFERENCES prestamo, PRIMARY KEY (nombre_cliente, número_prestamo) );

39. Ú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

40. FIN