bases de datos cap vi:sql: sql-definición de datos

ESCUELA:

NOMBRES:

BASE DE DATOS I

CICLO

Ciencias de la Computación

Ing. Juan Carlos Morocho

OCTUBRE 2009 – FEBRERO 2010

1

BIMESTRE: I BIMESTRE

Capítulo 6

SQL: definición de datos

© Pearson Education Limited 1995, 2005

3

Tipos de datos SQL de ISO

© Pearson Education Limited 1995, 2005

Tipo de datos Declaraciones

booleano BOOLEAN

carácter CHAR VARCHAR

bit BIT BIT VARYING

numérico exacto NUMERIC DECIMAL INTEGER SMALLINT

numérico aproximado FLOAT RAEL DOUBLE PRECISION

fecha y hora DATE TIME TIMESTAMP

intervalo INTERVAL

objetos de gran tamaño CHARACTER LARGE OBJECT BINARY LARGE OBJECT

Tabla 6.1.Tipos de datos SQL

4

Características de mejora de integridad

Tengamos en cuenta cuatro tipos de restricciones de integridad:

Datos requeridos Restricciones de dominio Integridad de entidades Integridad referencial Restricciones generales.

© Pearson Education Limited 1995, 2005

Características de mejora de la integridad

Datos requeridosposition VARCHAR(10) NOT NULL

Restricciones de dominio(a) CHECK

sex CHAR NOT NULL CHECK (sex IN (‘M’, ‘F’))

Integridad de Entidades La clave principal de una tabla debe contener un

valor unívoco y no nulo en cada fila. El estándar ISO soporta las restricciones de integridad

de entidades mediante la cláusula FOREIGN KEY en las instrucciones CREATE y ALTER TABLE:

PRIMARY KEY(staffNo)PRIMARY KEY(clientNo, propertyNo)

La cláusula PRIMARY KEY sólo puede especificarse una vez por tabla. Sigue siendo posible garantizar la unicidad para cualesquiera claves alternativas que tenga la tabla utilizando la palabra clave UNIQUE:

UNIQUE(telNo)

Integridad referencial Una clave externa en una columna o conjunto de

columnas que enlazan cada fila de la tabla hijo que contiene la clave externa con la fila de la clave padre que contiene el valor correspondiente de clave candidata.

La integridad referencial significa que, si la clave externa contiene un valor, dicho valor debe hacer referencia a una fila existente y válida dentro de la tabla padre.

El estándar ISO soporta la definición de claves externas mediante la cláusula FOREIGN KEY en las instrucciones CREATE y ALTER TABLE:

FOREIGN KEY(branchNo) REFERENCES Branch

Integridad referencial Cualquier intento de efectuar una operación

INSERT o UPDATE mediante la que se trate de crear un valor de clave externa en una tabla hijo que no tenga un valor correspondiente de clave candidata en la tabla padre será rechazada.

La acción que SQL toma para cualquier operación UPDATE y DELETE que intente actualizar o borrar un valor de clave candidata de la tabla padre que tenga una o más filas correspondientes en la tabla hijo depende de la acción referencial especificada mediante las subcláusulas ON UPDATE y ON DELETE:

CASCADE - SET NULL SET DEFAULT - NO ACTION

Integridad referencialCASCADE: Borra la fila de la tabla padre y borra automáticamente las filas correspondientes en la tabla hijo, y así sucesivamente en cascada.SET NULL: Se borra la fila de la tabla padre y se asigna el valor NULL a los valores de clave externa en la tabla hijo. Sólo es válida si no se ha especificado el cuantificador NOT NULL para las columnas de clave externa.SET DEFAULT: Borra la fila de la tabla padre y asigna a cada componente la clave externa de la tabla hijo del valor predeterminado especificado. Sólo es válido si se ha especificado un valor DEFAULT para las columnas de clave externa.NO ACTION: Se rechaza la operación de borrado de la tabla padre. Operación predeterminada.

Integridad referencial

FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL

FOREIGN KEY (ownerNo) REFERENCES Owner ON UPDATE CASCADE

Restricciones generales

Podemos usar CHECK/UNIQUE de las instrucciones CREATE y ALTER TABLE.

Definición de datos El lenguaje DDL permite crear y destruir objetos de la base

de datos tales como esquemas, dominios, vistas e índices. Las principales instrucciones del DDL son:

CREATE SCHEMA DROP SCHEMA

CREATE/ALTER DOMAIN DROP DOMAIN

CREATE/ALTER TABLE DROP TABLE

CREATE vista DROP vista

Muchos SGBD también proporcionan:

CREATE INDEX DROP INDEX

Creación de una tablaCreación de una tabla NombreTabla {(NombreColumna tipoDatos [NOT NULL] [UNIQUE][DEFAULT opciónPredeterminada][CHECK condiciónBúsqueda] [,...]}[PRIMARY KEY (listaDeColumnas),]{[UNIQUE (listaDeColumnas),] […,]}{[FOREIGN KEY (listaColumnasClaveExterna) REFERENCES NombreTablaPadre

[(listaColumnasClaveCandidatas)], [ON UPDATE acciónReferencial] [ON DELETE acciónReferencial ]] [,…]} {[CHECK (condiciónBúsqueda)] [,…] })

13

Ejemplo 6.1 - Creación de una tabla

CREATE DOMAIN OwnerNumber AS VARCHAR(5)

CHECK (VALUE IN (SELECT ownerNo FROM PrivateOwner));

CREATE DOMAIN StaffNumber AS VARCHAR(5)

CHECK (VALUE IN (SELECT staffNo FROM Staff));

CREATE DOMAIN PNumber AS VARCHAR(5);

CREATE DOMAIN PRooms AS SMALLINT;

CHECK(VALUE BETWEEN 1 y 15);

CREATE DOMAIN PRent AS DECIMAL(6,2)

CHECK(VALUE BETWEEN 0 y 9999.99);

© Pearson Education Limited 1995, 2005

Ejemplo 6.1 - Creación de una tabla

Crear la tabla PropertyForRent propertyNoPNumber NOT NULL, ….rooms PRooms NOT NULL DEFAULT 4, rent PRent NOT NULL, DEFAULT 600, ownerNo OwnerNumber NOT NULL, staffNo StaffNumber

Constraint StaffNotHylingTooMuch ….branchNo BranchNumberNOT NULL,PRIMARY KEY (propertyNo),FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL ON UPDATE CASCADE ….);

Modificación de la definición de una tabla (ALTER TABLE)

Añadir una nueva columna a una tabla. Eliminar una columna de una tabla. Añadir una nueva restricción de una tabla. Eliminar una restricción de una tabla. Asignar un valor predeterminado a una

columna. Eliminar el valor predeterminado para una

columna.

Ejemplo 6.2(a) - ALTER TABLE

Cambiar la tabla Staff eliminando el valor predeterminado «Assistant» para la columna y asignando como valor predeterminado para la columna sex el sexo femenino (« F »).

ALTER TABLE StaffALTER position DROP DEFAULT;

ALTER TABLE StaffALTER sex SET DEFAULT ‘F’;

Ejemplo 6.2(b) - ALTER TABLE

Cambiar la tabla PropertyForRent eliminando la restricción de que los empleados no pueden gestionar más de 100 inmuebles al mismo tiempo. Añadir una nueva columna a la tabla Client.

ALTER TABLE PropertyForRentDROP CONSTRAINT

StaffNotHylingTooMuch;ALTER TABLE Client

ADD prefNoRooms PRooms;

Eliminación de una tabla (DROP TABLE)

DROP TABLE NombreTabla [RESTRICT | CASCADE]

E.j. DROP TABLE PropertyForRent;

Elimina la tabla designada y todas las filas en ella contenidas.

Con el comando RESTRICT se rechaza la operación DROP si hay otros objetos cuya existencia depende de que continúe existiendo la tabla que se puede eliminar.

Con el comando CASCADE la operación DROP se lleva a cabo y SQL elimina automáticamente todos los objetos dependientes (y también los objetos que dependan de otros objetos).

Vistas

Vista

Resultado dinámico de una o más operaciones relacionales que operan sobre las relaciones base para producir otra relación.

Relación virtual que no tiene por qué existir necesariamente en la base de datos sino que puede producirse cuando se solicite por parte de un usuario concreto, generándosela en el momento de la solicitud.

Creación de una vista (CREATE VIEW)

CREATE VIEW NombreVista [ (nuevoNombreColumna [,...]) ]

AS subselección

[WITH [CASCADED | LOCAL] CHECK OPTION]

Si se especifica una lista de nombres de columna, debe tener el mismo número de elementos que el número de columnas generadas por la subselección

Si se omite la lista de nombres de columnas, cada una de las columnas de la vista tomará el nombre de la columna correspondiente en la instrucción de subselección.

Ejemplo 6.3 – Creación de una vista horizontalCrear una vista de modo que el gerente de la sucursal B003 sólo pueda ver los detalles referidos a los empleados que trabajen en su sucursal.

CREATE vista Manager3StaffASSELECT *

FROM StaffWHERE branchNo = ‘B003’;

Tabla 6.3 Datos correspondientes a la vista Manager3Staff

Ejemplo 6.4 – Creación de una vista verticalCrear una vista de los detalles de los empleados de la sucursal B003 excluyendo la información salarial.

AS SELECT staffNo, fName, lName, position, sex

FROM Staff

WHERE branchNo = ‘B003’;Tabla 6.4 Datos correspondientes a la vista Staff3

23

Ejemplo 6.5 – Vistas agrupadas y combinadas

Crear una vista de los empleados que gestionan inmuebles para alquilar en la que se incluya el número de la sucursal en la que trabaja el empleado, el número del empleado y el número de inmuebles que gestiona.

CREATE view StaffPropCnt (branchNo, staffNo, cnt)AS SELECT s.branchNo, s.staffNo, COUNT(*)

FROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.branchNo, s.staffNo;

© Pearson Education Limited 1995, 2005

Ejemplo 6.3 – Vistas agrupadas y combinadas

Tabla 6.5 Datos para la vista StaffPropCnt

Eliminación de una vista (DROP vista)

DROP VIEW NombreVista [RESTRICT | CASCADE]

Hace que se elimine de la base de datos la definición de la vista.

Por Ejemplo:

DROP vista Manager3Staff;

DROP VIEW

Si se especifica la opción CASCADE, se borrarán todos los objetos dependientes relacionados, es decir, todos los objetos que hagan referencia a la vista.

Si se especifica la opción RESTRICT (predeterminado), y hay algún objeto cuya existencia dependa de que continúe existiendo la vista que se pretende eliminar, el comando no se ejecutará.

Resolución de vistas

Contar el número de inmuebles gestionados por cada empleado de la sucursal.

SELECT staffNo, cntFROM StaffPropCntWHERE branchNo = ‘B003’ORDER BY staffNo;

Resolución de vistas

(a) Los nombres de columna de la vista contenidos en la lista SELECT se traducen a sus correspondientes nombres de columna en la consulta de definición:

SELECT s.staffNo As staffNo, COUNT(*) As cnt

(b) Los nombres de vistas en la cláusula FROM se sustituyen por las correspondientes listas FROM de la consulta de definición y:

FROM Staff s, PropertyForRent p

Resolución de las vistas(c) La cláusula WHERE de la consulta del usuario se combina

con la cláusula WHERE de la consulta de definición utilizando el operador lógico AND:

WHERE s.staffNo = p.staffNo y branchNo = ‘B003’

(d) Las cláusulas GROUP BY y HAVING se copian de la columna de definición:

GROUP BY s.branchNo, s.staffNo

(e) La cláusula ORDER BY se copia de la consulta del usuario traduciendo el nombre de columna de la vista a su correspondiente nombre de columna de la consulta de definición.

ORDER BY s.staffNo

Resolución de las vistas(f) La cláusula combinada final será:

SELECT s.staffNo AS staffNo, COUNT(*) AS cntFROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNo y branchNo = ‘B003’GROUP BY s.branchNo, s.staffNoORDER BY s.staffNo;

Restricciones de las vistas

SQL impone diversas restricciones a la creación y utilización de vistas.

(a) Si una columna de la vista está basada en una función de agregación sobre la columna cnt: Dicha columna sólo puede aparecer en las

cláusulas SELECT y ORDER BY de las consultas que accedan a la vista.

Dicha columna no podrá ser utilizada en una cláusula WHERE y no puede emplearse como argumento para una función de agregación en ninguna consulta basada en la vista.

Restricciones de las vistas

Por Ejemplo, la siguiente consulta no sería legal:

SELECT COUNT(cnt)

FROM StaffPropCnt;

De igual forma, la siguiente consulta también fallaría:

SELECT *

FROM StaffPropCnt

WHERE cnt > 2;

Restricciones de las vistas

(b) Una vista agrupada nunca puede combinarse con una tabla base o con otra vista.

Por ejemplo, la vista StaffPropCnt es una vista agrupada por lo que cualquier intento de combinar esta vista con otra tabla o vista fallará.

Actualización de vistas

Todas las actualizaciones de la tabla base se ven inmediatamente reflejadas en todas las vistas definidas sobre dicha tabla base.

De igual forma, cabría esperar que si se actualiza una vista las tablas base reflejen los cambios realizados.

Actualización de vistas Sin embargo, considere de nuevo la vista

StaffPropCnt. Si tratáramos de insertar un registro que indicara

que, en la sucursal B003, el empleado SG5 gestiona dos propiedades:

INSERT INTO StaffPropCntVALUES (‘B003’, ‘SG5’, 2);

Tendremos que insertar dos registros en la tabla PropertyForRent que indiquen qué inmuebles son. Sin embargo, no sabemos qué inmuebles son, es decir, no conocemos los valores de la clave principal!

Actualización de vistas

Si cambiamos la definición de vista, y sustituimos el valor que indica el número de inmuebles por los números de inmuebles reales:

CREATE VIEW StaffPropList (branchNo, staffNo, propertyNo)

AS SELECT s.branchNo, s.staffNo, p.propertyNo

FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo;

Actualización de vistas

Ahora intentaremos insertar el registro:

INSERT INTO StaffPropList

VALUES (‘B003’, ‘SG5’, ‘PG19’);

Todavía continúa existiendo un problema con esta inserción, porque hemos especificado en la definición de la tabla PropertyForRent que todas las columnas excepto postcode y staffNo no pueden contener valores nulos.

Sin embargo, no tenemos forma de proporcionar valores a las restantes columnas no nulas.

Ventajas de las vistas

Independencia de datos Valores actualizados Mayor seguridad Menor complejidad Comodidad Personalización Integridad de los datos

Desventajas de las vistas

Restricciones de actualización Restricciones de estructura Rendimiento

Materialización de vistas El mecanismo para llevar a cabo la resolución de la

vista puede ser lento, especialmente si se accede a esta vista de manera frecuente.

La materialización de vistas almacena las vistas de forma temporal dentro de la base de datos cuando la vista se consulta por primera vez.

A partir de ahí, las consultas basadas en la vista materializada pueden ser mucho más rápidas que el proceso de recalcular la vista cada vez.

La dificultad consiste en mantener la vista actualizada a medida que se modifican las tablas base.

Mantenimiento de las vistas

EL mantenimiento de las vistas tiene por objetivo mantener actualizada la vista.

Tengamos en cuenta la siguiente vista:CREATE vista StaffPropRent(staffNo)AS SELECT DISTINCT staffNo

FROM PropertyForRentWHERE branchNo = ‘B003’ y

rent > 400;

Tabla 6.8 Datos para la vista StaffPropRent

Transacciones El estándar SQL define un modelo de transacciones

basado en dos instrucciones SQL: COMMIT y ROLLBACK.

Una transacción es una unidad lógica de trabajo compuesta por una o más instrucciones SQL garantizándose que esa unidad lógica sea atómica con respecto a la recuperación.

Una transacción SQL comienza automáticamente con una instrucción SQL iniciadora de transacción(por ejemplo SELECT, INSERT).

Los cambios realizados por una transacción no son visibles para otras transacciones que se estén ejecutando concurrentemente hasta que la transacción se complete.

Transacciones

Toda transacción puede completarse de cuatro formas distintas:- La instrucción COMMIT hace que la transacción termine con éxito, con lo que los cambios realizados en la base de datos serán permanentes. - La instrucción ROLLBACK aborta la transacción, deshaciendo cualquier cambio que se está ejecutando concurrentemente hasta que la transacción se complete.

Transacciones

- Para el SQL programático la terminación satisfactoria de un programa hace que termine con éxito la transacción final, aún cuando no se haya ejecutado una instrucción COMMIT.- Para el SQL programático, una terminación anormal del programa hará que se aborte la transacción.

Transacciones

Una nueva transacción comienza con la siguiente instrucción de comienzo de transacción.

Las transacciones SQL no pueden anidarse. La instrucción SET TRANSACTION permite al

usuario configurar ciertos aspectos de la transacción:

SET TRANSACTION

[READ ONLY | READ WRITE] |

[ISOLATION LEVEL READ UNCOMMITTED |

READ COMMITTED|REPEATABLE READ |SERIALIZABLE ]

Privilegios

Las acciones que se permite al usuario llevar a cabo sobre una determinada tabla base o vista:

SELECTExtraer datos de una tabla.

INSERT Insertar nuevas filas en una tabla.

UPDATE Modificar filas de datos en una tabla.

DELETEBorrar filas de datos de una tabla.

REFERENCES Hacer referencia a columnas de una tabla especificada dentro de las restricciones de integridad.

USAGE Utilizar dominios, intercalaciones, conjuntos de caracteres y traducciones.

Privilegios

Pueden restringirse a columnas específicas de la tabla, lo que permite que se efectúen cambios en dichas columnas pero impidiendo la modificación de las columnas restantes.

El propietario de una base de datos debe proporcionar acceso a los restantes usuarios mediante la instrucción GRANT.

Para crear una vista el usuario debe disponer del privilegio SELECT sobre todas las tablas que forman la lista y del privilegio PREFERENCES sobre las columnas especificadas de la vista.

GRANT

GRANT {ListaPrivilegios | ALL PRIVILEGES}

ON NombreObjeto TO {ListaAutorización | PUBLIC} [WITH GRANT OPTION]

ListaPrivilegios está compuesta por uno o más de los privilegios arriba indicados separados por comas.

ALL PRIVILEGES da todos los privilegios a un usuario.

Ejemplo 6.7/8 - GRANT

Proporcionar al usuario con identificador de autorización Manager todos los privilegios mediante GRANT.

GRANT ALL PRIVILEGESON StaffTO Manager WITH GRANT OPTION;

Conceder a los usuarios Personnel y Director los privilegios SELECT y UPDATE sobre la columna salary de la tabla Staff.

GRANT SELECT, UPDATE (salary)ON StaffTO Personnel, Director;

Ejemplo 6.9 – Concesión de privilegios específicos a PUBLIC

mediante GRANTConceder a todos los usuarios el privilegio SELECT sobre la tabla Branch.

GRANT SELECTON BranchTO PUBLIC;

REVOKE REVOKE se utiliza para quitar privilegios concedidos

con la instrucción GRANT.

REVOKE [GRANT OPTION FOR] {ListaPrivilegios | ALL PRIVILEGES}

ON NombreObjetoFROM {ListaIdentificadorAutorización | PUBLIC}

[RESTRICT | CASCADE]

La palabra clave ALL PRIVILEGES hace referencia a todos los privilegios concedidos a un usuario por el usuario que está revocándolo.

Vista 6.10/11 – Revocación de privilegios específicos

Revocar el privilegio SELECT a todos los usuarios sobre la tabla Branch.

REVOKE SELECT

ON Branch

FROM PUBLIC;

Revocar todos los privilegios dados a Director sobre la tabla Staff.

REVOKE ALL PRIVILEGES

ON Staff

FROM Director;