sql-data definition language (ddl) lenguaje de definición de datos

4.1

SQL-Data Definition Language (DDL)SQL-Data Definition Language (DDL)Lenguaje de Definición de DatosLenguaje 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.2

Tipos de Datos en SQLTipos de Datos en SQL(volveremos sobre ellos)(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 usuariocreate domain nombre-persona char(20) not null

(typedef in C)

4.3

Ejemplo de DominioEjemplo de Dominiohacerhacer

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

Día y Hora en SQL (Cont.)Día y Hora en SQL (Cont.)practica fecha creacionpractica 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.5

Creación de Tablas Creación de Tablas ejemploejemplo

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

Destrucción de TablasDestrucció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.7

Columnas Auxiliares Creadas por PostgeSQL Columnas Auxiliares Creadas por PostgeSQL ejemplo, pgaccesejemplo, 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.8

Valores por Defecto Valores por Defecto ejemploejemplo

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

Valores por Defecto Valores por Defecto ejemploejemplo

CREATE SEQUENCE producto_no_seq;


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

RestriccionesRestricciones

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

CHECK CHECK ejemploejemplo

Permite especificar que los valores de una columna deben satisfacer una expresión. Por ejemplo ser positivo.


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

CHECK ejemplo

Permite especificar que los valores de una columna deben satisfacer una expresión. Por ejemplo ser positivo.



nombre text,

precio numeric(10,2) CONSTRAINT precio_positivo CHECK (precio > 0)

);

CONSTRAINT nombre_ligadura puede usarse con las otras restricciones

4.13

CHECKCHECK

Las restricciones pueden involucrar varias columnas pero no varias tablas.



nombre text,

precio numeric CHECK (price > 0),

precio_rebajado numeric CHECK

(precio_rebajado > 0),

CHECK (precio > precio_rebajado )

);

4.14

CHECK, AdvertenciaCHECK, Advertencia

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

4.15

NOT NULL NOT NULL ejemploejemplo

Indica que el atributo no puede valer “NULL”


producto_no integer NOT NULL,

nombre text NOT NULL,

precio numeric(10,2)

);

4.16

NOT NULLNOT NULL

Pueden existir varias restricciones referidas al mismo atributo, el order no importa.


producto_no integer NOT NULL,

nombre text NOT NULL,

precio numeric(10,2) NOT NULL CHECK (precio > 0)

);

4.17

UNIQUE UNIQUE ejemploejemplo

Asegura que un determinado valor no esta repetido en una columna.


producto_no integer UNIQUE,

nombre text,

precio numeric(10,2)

);

4.18

UNIQUE UNIQUE ejemploejemplo

Alternativamente se puede usar la sintaxis



nombre text,

precio numeric,

UNIQUE(producto_no,nombre)

);

Pregunta: ¿producto_no y nombre deben ser únicos o la pareja debe ser única?

4.19

UNIQUE ejemplo

¿Esta tabla es equivalente a la anterior?


producto_no integer UNIQUE,

nombre text UNIQUE,

precio numeric(10.2)

);



nombre text,

precio numeric(10.2),

UNIQUE(producto_no,nombre)

);

4.20

UNIQUE vs CHECKUNIQUE 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)

);

4.21

Clave Primaria Clave Primaria ejemploejemplo



nombre text,

precio numeric

);

CREATE TABLE ejemplo (

a integer,

b integer,

c integer,

PRIMARY KEY (a, c)

);

4.22

Clave Primaria: ¿Es esto equivalente? ejemplo


a integer PRIMARY KEY,

b text PRIMARY KEY,

c numeric

);

CREATE TABLE ejemplo (

a integer,

b integer,

c integer,

PRIMARY KEY (a, c)

);

4.23

Pregunta:Pregunta:

¿Es equivalente UNIQUE a PRIMARY KEY?

4.24

Clave Extranjera Clave Extranjera ejemplo,triggerejemplo,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.25

Triggers (una interrupción) Triggers (una interrupción) ejemploejemplo

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

TriggersTriggerstemplate1,plpgsql-sqltemplate1,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 INSERTON tomateFOR EACH ROW EXECUTE PROCEDURE modificacion();

4.27

Clave ExtranjeraClave Extranjera

¿Qué pasa si se borra el producto_no en la tabla productos?



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

Poblar las tablas anterioresPoblar 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.29

Clave Extranjera Clave Extranjera ejemploejemplo

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

4.30

Clave primaria y Clave ExtranjeraClave 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.31

Modificar tablas:“Drop” y “Alter” Modificar tablas:“Drop” y “Alter” ejemploejemplo

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

Añadir/Modificar una restricción Añadir/Modificar una restricción ejemploejemplo

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

Herencia Herencia ejemploejemplo

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

Etc…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…

Ejemplo BancoEjemplo Banco

4.36

Ejemplo Banco IEjemplo 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.37

Ejemplo Banco II Ejemplo Banco II error/ejemploerror/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

4.38

Ejemplo Banco IIIEjemplo 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)

);

4.39

Última preguntaÚ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

FINFIN

sql-data definition language (ddl) lenguaje de definición de datos

Documents