lenguajes de sqlarantxa.ii.uam.es/~rcobos/teaching/esp/si1/tema4-ddl.pdf · 2010. 1. 12. · este...

1

2009/2010Tema 4: DDL (Lenguaje de Definición de Datos) - SQL 1

Tema 4 – SQL, Data Definition Language (DDL)Lenguaje de Definición de Datos

SQL: DDL y DML

Esquema de cada relación.

Dominios de cada atributo.

Crear, destruir y modificar tablas.

Restricciones de integridad.

Introducción a los Triggers.

Tema 4: DDL (Lenguaje de Definición de Datos) - SQL 2

Lenguajes de SQL

DDL Lenguaje de definición de datos (Data Definition Language, DDL).

– Definición de esquemas de relación

– Borrado de relaciones

– Creación de índices

– Modificación de esquemas de relación

– Órdenes para la definición de vistas

– Órdenes para especificar las restricciones de integridad que deben cumplir los datos almacenados en la base de datos

– Órdenes para especificar derechos de acceso para las relaciones y vistas

DML Lenguaje interactivo de manipulación de datos (Data Manipulation Language, DML).

– Incluye un lenguaje de consultas, basado en el álgebra relacional (y en el cálculo de tuplas)

– Incluye órdenes para insertar, borrar y modificar tuplas de la base de datos.

2


Tipos de Datos en SQL

char(n). Cadena de longitud fija. La longitud es n caracteres.

varchar(n). Cadena de longitud variable. La longitud máxima es n caracteres.

int/integer. Entero.

smallint. Entero corto.

numeric(p,d). Numero en formato de coma fija, con precisión de pdí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

(define/typedef in C)


Día y Hora en SQL (Cont.)

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)

3


Creación de Tablas

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.


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


Columnas Auxiliares Creadas por PostgeSQL

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


Valores por Defecto

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

);

5


Secuencia de valores

CREATE SEQUENCE producto_no_seq;


producto_no integer NOT NULL DEFAULT

nextval('producto_no_seq'),

nombre text,

precio numeric(10,2) DEFAULT 9.99

);

-- Además se puede crear un índice sobre la secuencia

CREATE UNIQUE INDEX producto_no ON productos

( producto_no );


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

6


CHECK

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


CHECK

El mismo ejemplo...



nombre text,

precio numeric(10,2) CONSTRAINT

precio_positivo CHECK (precio > 0)

);

CONSTRAINT nombre_restriccion : dar nombre a una restricción.

7


CHECK

Las restricciones pueden involucrar varias columnas.CREATE TABLE productos (


nombre text,

precio numeric CHECK (precio > 0),

precio_rebajado numeric

CHECK (precio_rebajado > 0),

CHECK (precio > precio_rebajado )

);

prueba=> INSERT into productos VALUES (1,’prod’,20,20);

ERROR: new row for relation “productos” violates check constraint …

prueba=> INSERT into productos VALUES (1,’prod’,20,10);INSERT 12506 1


CHECK, Advertencia

“CHECK” se satisface si la expresion evaluada es

“TRUE” o “NULL”. Recordad que la mayor parte de las

expresiones en las que se utiliza “NULL” dan como

resultado NULL

8


Not NULL

Indica que el atributo no puede valer “NULL”


producto_no integer NOT NULL,

nombre text NOT NULL,

precio numeric(10,2)

);


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

);

9


Not NULL

prueba=> INSERT into productos VALUES (10,'text',4);

INSERT 17481 1

prueba=> INSERT into productos VALUES (10,'text');

ERROR: ExecInsert: Fail to add null value in not null attribute precio

prueba=> INSERT into productos VALUES (10,'text',-4);

ERROR: ExecInsert: rejected due to CHECK constraint

"productos_precio" on "productos"

prueba=> select * from productos;

producto_no | nombre | precio

-------------+--------+--------

10 | text | 4.00

(1 row)


UNIQUE

Asegura que un determinado valor no esta repetido en

una columna.


producto_no integer UNIQUE,

nombre text,


);

10


UNIQUE

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?


UNIQUE

¿Son equivalentes estas tablas?


producto_no integer UNIQUE,

nombre text UNIQUE,


);

CREATE TABLE productos2 (


nombre text,

precio numeric(10,2),

UNIQUE(producto_no,nombre)

);

11


UNIQUE

prueba=> INSERT into productos VALUES (10,'text',4);

INSERT 17469 1

prueba=> INSERT into productos VALUES (10,'text2',4);

ERROR: Cannot insert a duplicate key into unique index

productos_producto_no_key



-------------+--------+--------

10 | text | 4.00

(1 row)


UNIQUE

prueba=> INSERT into productos2 VALUES (10,'text',4);

INSERT 17471 1

prueba=> INSERT into productos2 VALUES (10,'text2',4);

INSERT 17472 1

prueba=> INSERT into productos2 VALUES (10,'text2',4);

ERROR: Cannot insert a duplicate key into unique index

productos2_producto_no_key

prueba=> select * from productos2;


-------------+--------+--------

10 | text | 4.00

10 | text2 | 4.00

(2 rows)

12


Más ejemplos con UNIQUE o CHECK

CREATE TABLE example (

a integer,

b integer,

c integer,

UNIQUE (a, c)

);

CREATE TABLE example2 (

a integer,

b integer,

c integer,

CHECK (a > 0 AND b > 0)

);prueba=> INSERT into example VALUES (1,2,3);

INSERT 17505 1

prueba=> INSERT into example2 VALUES (1,2,3);

INSERT 17506 1

prueba=> INSERT into example VALUES (0,2,3);

INSERT 17507 1


ERROR: ExecInsert: rejected due to CHECK constraint "$1" on "example2"

prueba=> INSERT into example VALUES (1,2,3);

ERROR: Cannot insert a duplicate key into unique index example_a_key


INSERT 17509 1


Clave Primaria



nombre text,

precio numeric

);

CREATE TABLE ejemplo (

a integer,

b integer,

c integer,

PRIMARY KEY (a, c)

);

13


Clave Primaria: ¿Es esto equivalente?

prueba=> CREATE TABLE productos (

prueba(> a integer PRIMARY KEY,

prueba(> b text PRIMARY KEY,

prueba(> c numeric

prueba(> );

ERROR: CREATE TABLE / PRIMARY KEY multiple

primary keys for table 'productos' are not allowed


a integer PRIMARY KEY,

b text PRIMARY KEY,

c numeric

);

CREATE TABLE ejemplo (

a integer,

b integer,

c integer,

PRIMARY KEY (a, c)

);


Pregunta:

¿Es equivalente UNIQUE a PRIMARY KEY?

14


Clave Extranjera 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



nombre text,

precio numeric);

CREATE TABLE pedidos (

pedido_no integer PRIMARY KEY,

producto_no integer REFERENCES productos(producto_no),

cantidad integer);

PostgeSQL no nos dejara crear pedidos sobre productos que

no existan


Clave Extranjera

Otra forma de definir pedidos:CREATE TABLE pedidos (



cantidad integer,

foreign key (producto_no) REFERENCES productos(producto_no)

);

prueba=> insert into productos values (1,'prod1', 19);

INSERT 17560 1

prueba=> insert into pedidos values (11,1,100);

INSERT 17561 1


ERROR: $1 violaciÃÂ³n de la integridad refrencial - referencia a la clave desde

pedidos no se encuentra en productos

15


Clave Extranjera

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

productos?

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

pedidos?



-------------+--------+--------

1 | prod1 | 19

(1 row)

prueba=> select * from pedidos;

pedido_no | producto_no | cantidad

-----------+-------------+----------

11 | 1 | 100

(1 row)

prueba=> delete from productos where producto_no =1;

ERROR: $1 referential integrity violation - key in

productos still referenced from pedidos



-------------+--------+--------

1 | prod1 | 19

(1 row)

prueba=> delete from pedidos where producto_no =1;

DELETE 1

prueba=> select * from pedidos;


-----------+-------------+----------

(0 rows)


Clave Extranjera



nombre text,

precio numeric);

CREATE TABLE pedidos (


producto_no integer REFERENCES productos(producto_no),

cantidad integer);

CREATE TABLE producto_pedido (

producto_no integer REFERENCES productos ON DELETE RESTRICT,

pedido_no integer REFERENCES pedidos ON DELETE CASCADE ON UPDATE CASCADE,

cantidad integer,

PRIMARY KEY (producto_no, pedido_no)

);

16


Clave Extranjera

prueba=> insert into productos values (1,'prod1', 19);

INSERT 17560 1


INSERT 25701 1

prueba=> insert into producto_pedido values (1,11,500);

INSERT 25702 1



-------------+--------+--------

1 | prod1 | 19

(1 row)

prueba=> select * from pedidos ;


-----------+-------------+----------

11 | 1 | 100

(1 row)

prueba=> select * from producto_pedido ;

producto_no | pedido_no | cantidad

-------------+-----------+----------

1 | 11 | 500

(1 row)

prueba=> delete from productos where producto_no =1;

ERROR: $1 referential integrity violation - key in

productos still referenced from pedidos

prueba=> delete from pedidos where producto_no =1;

DELETE 1

prueba=> select * from pedidos ;


-----------+-------------+----------

(0 rows)

prueba=> select * from producto_pedido;

producto_no | pedido_no | cantidad

-------------+-----------+----------

(0 rows)


Clave primaria y Clave Extranjera

La clave primaria y extranjera pueden usar los mismos

atributos/campos

CREATE TABLE order_items (

product_no integer REFERENCES products,

order_id integer REFERENCES orders,

quantity integer,

PRIMARY KEY (product_no, order_id)

);

17


Triggers (una interrupción)

Un “trigger” (disparador) es código SQL que se ejecuta automáticamente como efecto secundario de una modificación en la base de datos.

SQL 99 (subset)

Para diseñar un mecanismo disparador:– Especificar condiciones en las que se va a ejecutar el

disparador. Compuesto de:

• Evento que causa la comprobación del disparador.

• Condición que se debe cumplir para la ejecución del disparador

– Especificar las acciones que se van a realizar cuando se ejecute el disparador.

Modelo: evento-condición-acción.

Por ejemplo: Para guardar la fecha de actualización:


Triggers

CREATE TABLE ejemplo( ej_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 ejemplo;

CREATE TRIGGER t_modificacion

BEFORE INSERT

ON ejemplo

FOR EACH ROW EXECUTE PROCEDURE modificacion();

18


Modificar tablas:“Drop” y “Alter”

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


Añadir/Modificar una restricción

ALTER TABLE productos ADD CHECK (nombre '');

ALTER TABLE productos ADD CONSTRAINT some_name

UNIQUE (producto_no);

ALTER TABLE productos ADD FOREIGN KEY

(producto_group_id) REFERENCES grupo_productos;

ALTER TABLE productos ALTER COLUMN producto_no SET

NOT NULL;

ALTER TABLE productos ALTER COLUMN precio SET

DEFAULT 7.77;

ALTER TABLE productos RENAME COLUMN producto_no TO

product_number;

19


Añadir/Modificar una restricción

prueba=> \d productos;

Table "public.productos"

Column | Type | Modifiers

-------------+---------+-----------

producto_no | integer | not null

nombre | text |

precio | numeric |

Indexes: productos_pkey primary key btree (producto_no)

prueba=> ALTER TABLE productos ADD CHECK (nombre '');

ALTER TABLE

prueba=> ALTER TABLE productos ALTER COLUMN precio SET DEFAULT 7.77;

ALTER TABLE

prueba=> \d productos;

Table "public.productos"


-------------+---------+--------------

producto_no | integer | not null

nombre | text |

precio | numeric | default 7.77

Indexes: productos_pkey primary key btree (producto_no)

Check constraints: "$1" (nombre ''::text)


Herencia

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 fila de capitales contiene TODOS los atributos de ciudades.

¿Qué pasa con capitales si modifico ciudades?

20


Herencia

prueba=> alter table ciudades add column varios varchar(20);

ALTER TABLE

prueba=> \d ciudades

Table "public.ciudades"


-----------+-----------------------+-----------

nombre | character(30) |

poblacion | double precision |

altura | integer |

varios | character varying(20) |

prueba=> \d capitales

Table "public.capitales"


-----------+-----------------------+-----------



altura | integer |

pais | character(30) |

varios | character varying(20) |

prueba=> CREATE TABLE ciudades (

prueba(> nombre char(30),

prueba(> poblacion float,

prueba(> altura int -- en metros

prueba(> );

CREATE TABLE

prueba=>

prueba=> CREATE TABLE capitales (

prueba(> pais char(30)

prueba(> ) INHERITS (ciudades);

CREATE TABLE

prueba=> \d ciudades

Table "public.ciudades"


-----------+------------------+-----------



altura | integer |

prueba=> \d capitales

Table "public.capitales"


-----------+------------------+-----------



altura | integer |

pais | character(30) |


Esquemas

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, operadores

– Triggers

– etc…

21


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


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

)

CREATE TABLE cliente_cuenta (

nombre_cliente char(30) REFERENCES cliente,

número_cuenta numeric(25) REFERENCES cuenta,

PRIMARY KEY (nombre_cliente, número_cuenta)

)

CREATE TABLE cliente_prestamo (

nombre_cliente char(30) REFERENCES cliente,

número_prestamo numeric(25) REFERENCES prestamo,

PRIMARY KEY (nombre_cliente, número_prestamo)

)

CREATE TABLE cuenta(

numero-cuenta numeric(25),


saldo numeric(15,2) NOT NULL,

PRIMARY KEY (numero-cuenta )

)

CREATE TABLE prestamo (

numero-prestamo numeric(25),


cantidad numeric (15,2) NOT NULL,

PRIMARY KEY (numero-cuenta )

)

Hay al menos tres errores en estas ordenes:

lenguajes de sqlarantxa.ii.uam.es/~rcobos/teaching/esp/si1/tema4-ddl.pdf · 2010. 1. 12. · este...

Documents