Partes: 1, 2, 3

Para borrar una columna basta con utilizar la cláusula DROP COLUMN (COLUMN es opcional) y el nombre de la columna que queremos borrar, se perderán todos los datos almacenados en la columna.

Ejemplo: ALTER TABLE tab1 DROP COLUMN col3 También podemos escribir: ALTER TABLE tab1 DROP col3 El resultado es el mismo, la columna col3 desaparece de la tabla tab1.

Para borrar una restricción basta con utilizar la cláusula DROP CONSTRAINT y el nombre de la restricción que queremos borrar, en este caso sólo se elimina la definición de la restricción pero los datos almacenados no se modifican ni se pierden.

Ejemplo: ALTER TABLE tab1 DROP CONSTRAINT c1 Con esta sentencia borramos el índice c1 creado anteriormente pero los datos de la columna col3 no se ven afectados por el cambio.

DROP TABLE

La sentencia DROP TABLE sirve para eliminar una tabla. No se puede eliminar una tabla si está abierta, tampoco la podemos eliminar si el borrado infringe las reglas de integridad referencial (si interviene como tabla padre en una relación y tiene registros relacionados). La sintaxis es la siguiente:

Ejemplo: DROP TABLE tab1 Elimina de la base de datos la tabla tab1.

CREATE INDEX

La sentencia CREATE INDEX sirve para crear un índice sobre una o varias columnas de una tabla. Si quieres repasar conceptos básicos sobre índices haz clic aquí La sintaxis es la siguiente:

nbindi: nombre del índice que estamos definiendo. En una tabla no pueden haber dos índices con el mismo nombre de lo contrario da error. nbtabla: nombre de la tabla donde definimos el índice. A continuación entre paréntesis se indica la composición del índice (las columnas que lo forman). nbcol: nombre de la columna que indexamos. Después del nombre de la columna podemos indicar cómo queremos que se ordenen las filas según el índice mediante las cláusulas ASC/DESC. ASC: la cláusula ASC es la que se asume por defecto e indica que el orden elegido para el índice es ascendente (en orden alfabético si la columna es de tipo texto, de menor a mayor si es de tipo numérico, en orden cronológico si es de tipo fecha). DESC: indica orden descendente, es decir el orden inverso al ascendente. Podemos formar un índice basado en varias columnas, en este caso después de indicar la primera columna con su orden, se escribe una coma y la segunda columna también con su orden, así sucesivamente hasta indicar todas las columnas que forman el índice. Opcionalmente se pueden indicar las cláusulas: WITH PRIMARY indica que el índice define la clave principal de la tabla, si la tabla ya tiene una clave principal, la sentencia CREATE INDEX dará error. WITH DISALLOW NULL indica que no permite valores nulos en las columnas que forman el índice. WITH IGNORE NULL indica que las filas que tengan valores nulos en las columnas que forman el índice se ignoran, no aparecen cuando recuperamos las filas de la tabla utilizando ese índice.

Ejemplo: CREATE UNIQUE INDEX ind1 ON clientes (provincia, poblacion ASC, fecha_nacimiento DESC) Crea un índice llamado ind1 sobre la tabla clientes formado por las columnas provincia, población y fecha_nacimiento. Este índice permite tener ordenadas las filas de la tabla clientes de forma que aparezcan los clientes ordenados por provincia, dentro de la misma provincia por población y dentro de la misma población por edad y del más joven al más mayor. Al añadir la cláusula UNIQUE el índice no permitirá duplicados por lo que no podría tener dos clientes con la misma fecha de nacimiento en la misma población y misma provincia, para evitar el poblema sería mejor utilizar: CREATE INDEX ind1 ON clientes (provincia, poblacion ASC, fecha_nacimiento DESC)

DROP INDEX

La sentencia DROP INDEX sirve para eliminar un índice de una tabla. Se elimina el índice pero no las columnas que lo forman. La sintaxis es la siguiente:

Ejemplo: DROP INDEX ind1 ON clientes Elimina el índice que habíamos creado en el ejemplo anterior.

SUM (Transact-SQL)

Devuelve la suma de todos los valores o sólo de los valores DISTINCT de la expresión. SUM sólo puede utilizarse con columnas numéricas. Los valores Null se pasan por alto. Puede ir seguida de la cláusula OVER.

Convenciones de sintaxis de Transact-SQL

Sintaxis

SUM ( [ ALL | DISTINCT ] expression )

Argumentos

ALL

Aplica la función de agregado a todos los valores. ALL es el valor predeterminado.

DISTINCT

Especifica que SUM devuelve la suma de los valores únicos.

expression

Es una constante, columna o función y cualquier combinación de operadores aritméticos, bit a bit y de cadena. expression es una expresión de la categoría del tipo de datos numérico exacto o numérico aproximado, excepto para el tipo de datos bit. No se permiten funciones de agregado ni subconsultas.

Tipos de valor devueltos

Devuelve la suma de todos los valores de expression con el tipo de datos expression más preciso.

Resultado de la expresión		Valor devuelto
Categoría integer		int
Categoría decimal (p, s)		decimal(38, s)
Categoría money y smallmoney		money
Categoría float y real		float
Importante:
No se admiten agregados Distinct, por ejemplo AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name) y SUM(DISTINCT column_name) cuando se utilizan CUBE o ROLLUP. Si se utilizan, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) devuelve un mensaje de error y cancela la consulta.

Ejemplos

A. Utilizar SUM para agregados y agregados de filas

En estos ejemplos se muestran las diferencias entre las funciones de agregado y las funciones de agregado de filas. En el primero se muestran funciones de agregado que sólo ofrecen datos de resumen y en el segundo, funciones de agregado de filas que ofrecen datos de resumen y de detalle.

USE AdventureWorks;

GO

SELECT Color, SUM(ListPrice), SUM(StandardCost)

FROM Production.Product

WHERE Color IS NOT NULL AND ListPrice != 0.00 AND Name LIKE 'Mountain%'

GROUP BY Color

ORDER BY Color;

GO

USE AdventureWorks;

GO

SELECT Color, ListPrice, StandardCost

FROM Production.Product

WHERE Color IS NOT NULL AND ListPrice != 0.00 AND Name LIKE 'Mountain%'

ORDER BY Color

COMPUTE SUM(ListPrice), SUM(StandardCost) BY Color;

GO

COUNT (Transact-SQL)

Devuelve el número de elementos de un grupo. COUNT funciona como COUNT_BIG. La única diferencia entre ambas funciones está en los valores devueltos. COUNT siempre devuelve un valor de tipo de datos int. COUNT_BIG siempre devuelve un valor de tipo de datos bigint. Puede ir seguida de la cláusula OVER.

Convenciones de sintaxis de Transact-SQL

Sintaxis

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

Argumentos

ALL

Aplica la función de agregado a todos los valores. ALL es el valor predeterminado.

DISTINCT

Especifica que COUNT devuelva el número de valores únicos no NULL.

expression

Es una expression de cualquier tipo excepto text, image o ntext. No se permite utilizar funciones de agregado ni subconsultas.

*

Especifica que se deben contar todas las filas para devolver el número total de filas de una tabla. COUNT(*) no acepta parámetros y no se puede utilizar con DISTINCT. COUNT(*) no requiere un parámetro expression porque, por definición, no utiliza información sobre ninguna columna específica. COUNT(*) devuelve el número de filas de una tabla especificada sin deshacerse de las duplicadas. Cuenta cada fila por separado. Se incluyen las filas que contienen valores NULL.

Importante:

No se admiten agregados Distinct, por ejemplo AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name) y SUM(DISTINCT column_name) cuando se utilizan CUBE o ROLLUP. Si se utilizan, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) de Microsoft devuelve un mensaje de error y cancela la consulta.

Tipos de valor devueltos

int

Notas

COUNT (*) devuelve el número de elementos de un grupo. Se incluyen valores NULL y duplicados.

COUNT (ALL expression) evalúa expression en todas las filas del grupo y devuelve el número de valores no NULL.

COUNT (DISTINCT expression) evalúa expression en todas las filas del grupo y devuelve el número de valores no NULL únicos.

Si los valores devueltos son superiores a 2^31-1, COUNT genera un error. En su lugar, utilice COUNT_BIG.

Ejemplos

A. Usar COUNT y DISTINCT

En el ejemplo siguiente se muestra el número de cargos diferentes que puede tener un empleado que trabaja en Adventure Works Cycles.

USE AdventureWorks;

GO

SELECT COUNT(DISTINCT Title)

FROM HumanResources.Employee;

GO

B. Usar COUNT (*)

USE AdventureWorks;

GO

SELECT COUNT(*)

FROM HumanResources.Employee;

GO

C. Usar COUNT (*) con otros agregados

USE AdventureWorks;

GO

SELECT COUNT (*), AVG (Bonus)

FROM Sales.SalesPerson

WHERE SalesQuota > 25000;

GO

AVG (Transact-SQL)

Devuelve el promedio de los valores de un grupo. Los valores NULL se pasan por alto. Puede ir seguida de la cláusula OVER.

Convenciones de sintaxis de Transact-SQL

Sintaxis

AVG ([ALL | DISTINCT] expression)

Argumentos

ALL

Aplica la función de agregado a todos los valores. ALL es el valor predeterminado.

DISTINCT

Especifica que AVG se ejecute sólo en cada instancia única de un valor, sin importar el número de veces que aparezca el valor.

expression

Es una expresión de la categoría de tipo de datos numérico exacto o numérico aproximado, excepto para el tipo de datos bit. No se permite utilizar funciones de agregado ni subconsultas.

Tipos de valor devueltos

El tipo de valor devuelto viene determinado por el tipo del resultado evaluado de expression.

Resultado de la expresión	Tipo de valor devuelto
Categoría integer	int
Categoría decimal (p, s)	decimal(38, s) dividido por decimal(10, 0)
Categoría money y smallmoney	money
Categoría float y real	float

Notas

Si el tipo de datos de expression es un tipo de datos de alias, el tipo de valor devuelto es también del tipo de datos de alias. No obstante, si se asciende el tipo de datos base del tipo de datos de alias, por ejemplo, de tinyint a int, el valor devuelto es del tipo de datos ascendido, no del tipo de datos de alias.

Ejemplos

A. Utilizar las funciones SUM y AVG para los cálculos

En el ejemplo siguiente se calcula el promedio de horas de vacaciones y la suma de horas de baja por enfermedad que han utilizado los vicepresidentes de Adventure Works Cycles. Cada una de estas funciones de agregado produce un valor único de resumen para todas las filas recuperadas.

USE AdventureWorks;

GO

SELECT AVG(VacationHours)as 'Average vacation hours',

SUM (SickLeaveHours) as 'Total sick leave hours'

FROM HumanResources.Employee

WHERE Title LIKE 'Vice President%';

B. Utilizar las funciones SUM y AVG con una cláusula GROUP BY

Cuando se utiliza con una cláusula GROUP BY, cada función de agregado produce un solo valor para cada grupo, en vez de para toda la tabla. En el ejemplo siguiente se obtienen valores de resumen para cada territorio de ventas. El resumen muestra el promedio de bonificaciones recibidas por los vendedores de cada territorio y la suma de las ventas realizadas hasta la fecha en cada territorio.

USE AdventureWorks;

GO

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) 'YTD sales'

FROM Sales.SalesPerson

GROUP BY TerritoryID;

GO

C. Utilizar AVG con DISTINCT

En la instrucción siguiente se devuelve el precio de venta promedio de los productos.

USE AdventureWorks;

GO

SELECT AVG(DISTINCT ListPrice)

FROM Production.Product;

D. Utilizar AVG sin DISTINCT

Sin DISTINCT, la función AVG busca el precio de venta promedio de todos los productos de la tabla Product.

Copiar código

USE AdventureWorks;

GO

SELECT AVG(ListPrice)

FROM Production.Product;

MAX (Transact-SQL)

Devuelve el valor máximo de la expresión. Puede ir seguida de la cláusula OVER.

Convenciones de sintaxis de Transact-SQL

Sintaxis

MAX ( [ ALL | DISTINCT ] expression )

Argumentos

ALL

Aplica la función de agregado a todos los valores. ALL es el valor predeterminado.

DISTINCT

Especifica que se tiene en cuenta cada valor único. DISTINCT no tiene ningún significado con MAX y sólo se incluye para la compatibilidad con SQL-92.

expression

Se trata de una constante, nombre de columna o función y cualquier combinación de operadores aritméticos, bit a bit y de cadena. MAX se puede usar con columnas numéricas, de caracteres y de datetime, pero no con columnas de bit. No se permiten funciones de agregado ni subconsultas.

Tipos de valor devueltos

Notas

MAX pasa por alto los valores NULL.

Para las columnas de caracteres, MAX busca el valor más alto de la secuencia de intercalación.

Ejemplos

En el siguiente ejemplo se devuelve el tipo impositivo mayor (máximo).

USE AdventureWorks;

GO

SELECT MAX (TaxRate)

FROM Sales.SalesTaxRate;

GO

MIN (Transact-SQL)

Devuelve el valor mínimo de la expresión. Puede ir seguida de la cláusula OVER.

Convenciones de sintaxis de Transact-SQL

Sintaxis

MIN ( [ ALL | DISTINCT ] expression )

Argumentos

ALL

Aplica la función de agregado a todos los valores. ALL es el valor predeterminado.

DISTINCT

Especifica que se tiene en cuenta cada valor único. DISTINCT no tiene ningún significado con MIN y está disponible sólo por compatibilidad con SQL-92.

expression

Se trata de una constante, nombre de columna o función, y cualquier combinación de operadores aritméticos, bit a bit y de cadena. MIN se puede utilizar con columnas de tipo numérico, char, varchar o datetime, pero no con columnas de tipo bit. No se permite utilizar funciones de agregado ni subconsultas.

Tipos de valor devueltos

Devuelve un valor igual a expression.

MIN pasa por alto los valores NULL.

En el caso de columnas de datos de caracteres, MIN busca el valor más bajo en la secuencia de ordenación.

Ejemplos

En el ejemplo siguiente se devuelve la tasa de impuestos más baja (mínima).

USE AdventureWorks;

GO

SELECT MIN(TaxRate)

FROM Sales.SalesTaxRate;

GO

El operador UNION

El operador UNION sirve para obtener a partir de dos tablas con las mismas columnas, una nueva tabla con las filas de la primera y las filas de la segunda. La sintaxis es la siguiente:

Consulta puede ser un nombre de tabla, un nombre de consulta (en estos dos casos el nombre debe estar precedido de la palabra TABLE), o una sentencia SELECT completa (en este caso no se puede poner TABLE). La sentencia SELECT puede ser cualquier sentencia SELECT con la única restricción de que no puede contener la cláusula ORDER BY. Después de la primera consulta viene la palabra UNION y a continuación la segunda consulta. La segunda consulta sigue las mismas reglas que la primera consulta. Las dos consultas deben tener el mismo número de columnas pero las columnas pueden llamarse de diferente forma y ser de tipos de datos distintos. Las columnas del resultado se llaman como las de la primera consulta. Por defecto la unión no incluye filas repetidas, si alguna fila está en las dos tablas, sólo aparece una vez en el resultado. Si queremos que aparezcan todas las filas incluso las repeticiones de filas, incluimos la palabra ALL (todo en inglés). El empleo de ALL tienen una ventaja, la consulta se ejecutará más rapidamente. Puede que la diferencia no se note con tablas pequeñas, pero si tenemos tablas con muchos registros (filas) la diferencia puede ser notable. Se puede unir más de dos tablas, para ello después de la segunda consulta repetimos la palabra UNION... y así sucesivamente. También podemos indicar que queremos el resultado ordenado por algún criterio, en este caso se incluye la cláusula ORDER BY que ya vimos en el tema anterior. La cláusula ORDER BY se escribe después de la última consulta, al final de la sentencia; para indicar las columnas de ordenación podemos utilizar su número de orden o el nombre de la columna, en este último caso se deben de utilizar los nombres de columna de la primera consulta ya que son los que se van a utilizar para nombrar las columnas del resultado.

Para ilustrar la operación, vamos a obtener los códigos de los productos que tienen existencias iguales a cero o que aparezcan en pedidos del año 90.

SELECT idfab,idproductoFROM productosWHERE existencias = 0UNION ALL SELECT fab,productoFROM pedidosWHERE year(fechapedido) = 1990ORDER BY idproducto			o bien	TABLE [existencias cero]UNION ALLTABLE [pedidos 90]ORDER BY idproducto
Se ha incluido la cláusula ALL porque no nos importa que salgan filas repetidas. Se ha incluido ORDER BY para que el resultado salga ordenado por idproducto, observar que hemos utilizado el nombre de la columna de la primera SELECT, también podíamos haber puesto ORDER BY 2 pero no ORDER BY producto (es el nombre de la columna de la segunda tabla). Para el 2º caso hemos creado una consulta llamada existencias cero con la primera SELECT, y una consulta llamada pedidos 90 con la segunda SELECT. Observar que los nombres de las consultas están entre corchetes porque contienen espacios en blanco, y que en este caso hay que utilizar TABLE.

El INNER JOIN
El INNER JOIN es otro tipo de composición de tablas, permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado. La sintaxis es la siguiente:
Ejemplo: SELECT *FROM pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie tabla1 y tabla2 son especificaciones de tabla (nombre de tabla con alias o no, nombre de consulta guardada), de las tablas cuyos registros se van a combinar. Pueden ser las dos la misma tabla, en este caso es obligatorio definir al menos un alias de tabla. col1, col2 son las columnas de emparejamiento. Observar que dentro de la cláusula ON los nombres de columna deben ser nombres cualificados (llevan delante el nombre de la tabla y un punto). Las columnas de emparejamiento deben contener la misma clase de datos, las dos de tipo texto, de tipo fecha etc... los campos numéricos deben ser de tipos similares. Por ejemplo, se puede combinar campos AutoNumérico y Long puesto que son tipos similares, sin embargo, no se puede combinar campos de tipo Simple y Doble. Además las columnas no pueden ser de tipo Memo ni OLE. comp representa cualquier operador de comparación ( =, <, >, <=, >=, o <> ) y se utiliza para establecer la condición de emparejamiento. Se pueden definir varias condiciones de emparejamiento unidas por los operadores AND y OR poniendo cada condición entre paréntesis. Ejemplo:SELECT * FROM pedidos INNER JOIN productos ON (pedidos.fab = productos.idfab) AND (pedidos.producto = productos.idproducto) Se pueden combinar más de dos tablas En este caso hay que sustituir en la sintaxis una tabla por un INNER JOIN completo. Por ejemplo: SELECT *FROM (pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie) INNER JOIN empleados ON pedidos.rep = empleados.numemp En vez de tabla1 hemos escrito un INNER JOIN completo, también podemos escribir: SELECT *FROM clientes INNER JOIN (pedidos INNER JOIN empleados ON pedidos.rep = empleados.numemp) ON pedidos.clie = clientes.numclie
CREATE TABLE
La sentencia CREATE TABLE sirve para crear la estructura de una tabla no para rellenarla con datos, nos permite definir las columnas que tiene y ciertas restricciones que deben cumplir esas columnas. La sintaxis es la siguiente:
nbtabla: nombre de la tabla que estamos definiendo nbcol: nombre de la columna que estamos definiendo tipo: tipo de dato de la columna, todos los datos almacenados en la columna deberán ser de ese tipo. Para ver qué tipos de datos se pueden Una restricción consiste en la definición de una característica adicional que tiene una columna o una combinación de columnas, suelen ser características como valores no nulos (campo requerido), definición de índice sin duplicados, definición de clave principal y definición de clave foránea (clave ajena o externa, campo que sirve para relacionar dos tablas entre sí). restricción1: una restricción de tipo 1 es una restricción que aparece dentro de la definición de la columna después del tipo de dato y afecta a una columna, la que se está definiendo. restricción2: una restricción de tipo 2 es una restricción que se define después de definir todas las columnas de la tabla y afecta a una columna o a una combinación de columnas. Para escribir una sentencia CREATE TABLE se empieza por indicar el nombre de la tabla que queremos crear y a continuación entre paréntesis indicamos separadas por comas las definiciones de cada columna de la tabla, la definición de una columna consta de su nombre, el tipo de dato que tiene y podemos añadir si queremos una serie de especificaciones que deberán cumplir los datos almacenados en la columna, después de definir cada una de las columnas que compone la tabla se pueden añadir una serie de restricciones, esas restricciones son las mismas que se pueden indicar para cada columna pero ahora pueden afectar a más de una columna por eso tienen una sintaxis ligeramente diferente.
Una restricción de tipo 1 se utiliza para indicar una característica de la columna que estamos definiendo, tiene la siguiente sintaxis:
La cláusula NOT NULL indica que la columna no podrá contener un valor nulo, es decir que se deberá rellenar obligatoriamente y con un valor válido (equivale a la propiedad requerido Sí de las propiedades del campo). La cláusula CONSTRAINT sirve para definir una restricción que se podrá eliminar cuando queramos sin tener que borrar la columna. A cada restricción se le asigna un nombre que se utiliza para identificarla y para poder eliminarla cuando se quiera. Como restricciones tenemos la de clave primaria (clave principal), la de índice único (sin duplicados), la de valor no nulo, y la de clave foránea. La cláusula PRIMARY KEY se utiliza para definir la columna como clave principal de la tabla. Esto supone que la columna no puede contener valores nulos ni pueden haber valores duplicados en esa columna, es decir que dos filas no pueden tener el mismo valor en esa columna. En una tabla no pueden haber varias claves principales, por lo que no podemos incluir la cláusula PRIMARY KEY más de una vez, en caso contrario la sentencia da un error. No hay que confundir la definición de varias claves principales con la definición de una clave principal compuesta por varias columnas, esto último sí está permitido y se define con una restricción de tipo 2. La cláusula UNIQUE sirve para definir un índice único sobre la columna. Un índice único es un índice que no permite valores duplicados, es decir que si una columna tiene definida un restricción de UNIQUE no podrán haber dos filas con el mismo valor en esa columna. Se suele emplear para que el sistema compruebe el mismo que no se añaden valores que ya existen, por ejemplo si en una tabla de clientes queremos asegurarnos que dos clientes no puedan tener el mismo D.N.I. y la tabla tiene como clave principal un código de cliente, definiremos la columna dni con la restricción de UNIQUE. La cláusula NOT NULL indica que la columna no puede contener valores nulos, cuando queremos indicar que una columna no puede contener el valor nulo lo podemos hacer sin poner la cláusula CONSTRAINT, o utilizando una cláusula CONSTRAINT. La última restricción que podemos definir sobre una columna es la de clave foránea, una clave foránea es una columna o conjunto de columnas que contiene un valor que hace referencia a una fila de otra tabla, en una restricción de tipo 1 se puede definir con la cláusula REFERENCES, después de la palabra reservada indicamos a qué tabla hace referencia, opcionalmente podemos indicar entre paréntesis el nombre de la columna donde tiene que buscar el valor de referencia, por defecto coge la clave principal de la tabla2, si el valor que tiene que buscar se encuentra en otra columna de tabla2, entonces debemos inidicar el nombre de esta columna entre paréntesis, además sólo podemos utilizar una columna que esté definida con una restricción de UNIQUE, si la columna2 que indicamos no está definida sin duplicados, la sentencia CREATE nos dará un error. Si quieres repasar conceptos de clave foránea e integridad referencial
Ejemplo: CREATE TABLE tab1 (col1 INTEGER CONSTRAINT pk PRIMARY KEY,col2 CHAR(25) NOT NULL,col3 CHAR(10) CONSTRAINT uni1 UNIQUE,col4 INTEGER,col5 INT CONSTRAINT fk5 REFERENCES tab2 ); Con este ejemplo estamos creando la tabla tab1 compuesta por: una columna llamada col1 de tipo entero definida como clave principal, una columna col2 que puede almacenar hasta 25 caracteres alfanuméricos y no puede contener valores nulos, una columna col3 de hasta 10 caracteres que no podrá contener valores repetidos, una columna col4 de tipo entero sin ninguna restricción, y una columna col5 de tipo entero clave foránea que hace referencia a valores de la clave principal de la tabla tab2.

Trigger (base de datos)

Un trigger o un disparador en una Base de datos es un evento que se ejecuta cuando se cumple una condición establecida al realizar una operación de inserción (INSERT), actualización (UPDATE) o borrado (DELETE).

Son usados para mejorar la administración de la Base de datos, sin necesidad de contar con el usuario que ejecute la sentencia de SQL.

Además, pueden generar valores de columnas, previene errores de datos, sincroniza tablas, modifica valores de una vista, etc.

Permite implementar programas basados en paradigma lógico (sistemas expertos, deducción).

La estructura básica de un trigger es:

Llamada de activación: es la sentencia que permite "disparar" el código a ejecutar.

Restricción: es la condición necesaria para realizar el código. Esta restricción puede ser de tipo condicional o de tipo nulidad.

Acción a ejecutar: es la secuencia de instrucciones a ejecutar una vez que se han cumplido las condiciones iniciales.

Existen dos tipos de triggers, que se clasifican según la cantidad de ejecuciones a realizar:

Row Triggers (o Triggers de fila): son aquellos que se ejecutaran n-veces si se llama n-veces desde la tabla asociada al trigger

Statement Triggers (o Triggers de secuencia): son áquellos que sin importar la cantidad de veces que se cumpla con la condición, su ejecución es única.

Pueden ser de sesión y almacenados

Un sencillo ejemplo sería crear un trigger para insertar un pedido de algún producto cuando la cantidad de éste en nuestro almacén sea inferior a un valor dado.

BEFORE UPDATE ON tabla_almacen

FOR ALL records

IF: NEW.producto < 100 THEN

INSERT INTO tabla_pedidos(producto) VALUES ('1000');

END IF;

END;

PROTECCION

Los datos guardados en una base de datos deben estar protegidos contra los accesos no autorizados y ante destrucción y/o alteración malintencionadas

Accesos No Autorizados

Lectura no autorizada de datos

Modificación no autorizada de datos

Destrucción no autorizada de datos

SEGURIDAD

La Seguridad de las Bases de Datos se refiere a la protección frente a accesos malintencionados

La protección absoluta no es posible La idea que habitualmente se emplea es elevar el coste para quién lo realiza ?? Medida disuasoria en la mayor parte de los casos

Privilegios

Por razones de seguridad, los sistemas SQL2 permiten obtener muchas clases de privilegios sobre los elementos de la base de datos; Entre ellos se encuentran: el derecho de seleccionar (leer), insertar, eliminar o actualizar relaciones y el derecho de referenciar relaciones (referirse a ellas en una restricción); Los privilegios de insertar, actualizar y referenciar también pueden conseguirse en determinadas columnas de una relación

NIVELES DE SEGURIDAD

Sistema de Bases de Datos: Algunos usuarios del sistema de base de datos sólo estén autorizados a tener acceso a una parte limitada de la base de datos (es responsabilidad del sistema de bases de datos asegurarse de que no se violan las restricciones de autorización)

Sistema Operativo: La debilidad de la seguridad del sistema operativo puede servir de medio para el acceso no autorizado a la base de datos [contraseñas de acceso, aislamiento de procesos,…]

Red: Dado que casi todos los sistemas de bases de datos permiten el acceso remoto mediante redes, la seguridad en el nivel del software de red, tanto en Internet como en las redes privadas, es muy importante [encriptación,…]

Física: Los sitios que contienen los sistemas informáticos deben estar protegidos físicamente contra la entrada de intrusos [seguridad física]

Humanos: Los usuarios deben ser autorizados cuidadosamente para reducir la posibilidad de que alguno de ellos dé acceso a intrusos a cambios de favores […]

AUTORIZACIONES

Los usuarios pueden tener varios tipos de privilegios o autorización de acceso para diferentes usos de la base de datos:

??Autorización de Lectura (SELECT): permite la lectura de datos pero no la modificación de datos existentes

??Autorización de Inserción (INSERT): permite la inserción de nuevos datos pero no la modificación de datos existentes

??Autorización de Actualización (UPDATE): permite la modificación de datos pero no su borrado

??Autorización de Borrado (DELETE): Permite el borrado de datos

Los usuarios pueden tener varios tipos de privilegios o autorización de modificación de esquema de la base de datos:

?? Autorización de Índices: permite la creación y borrado de índices

?? Autorización de Recursos: permite la creación de nuevas relaciones

?? Autorización de Alteración: permite el añadido y borrado de atributos de una relación

?? Autorización de Eliminación: Permite el borrado de relaciones

SOBRE AUTORIZACIONES

Las autorizaciones de eliminación y borrado se diferencian en que la autorización de borrado elimina sólo las tuplas mientras que la autorización de eliminación elimina la relación.

El usuario con autorización de recursos, y por tanto con la capacidad de crear nuevas relaciones, recibe automáticamente todos los privilegios sobre ella

La autorización de índices permite regular los recursos (los usuarios que realizan actualizaciones estarían tentados a eliminar índices y los que realizan consultas a crearlos)

La forma superior de privilegio o autoridad es la concedida al administrador de la base de datos

AUTORIZACIONES Y VISTAS

Puede ocurrir que sea necesario autorizar a un usuario el acceso parcial a una relación (ver unos atributos y otros no) Esto se puede conseguir mediante una combinación de seguridad en el nivel relacional y en el nivel de las vistas

La creación de vistas no necesita la autorización de recursos El usuario que crea una vista no recibe necesariamente todos los privilegios sobre la misma (sólo recibe los que no proporcionen autorizaciones adicionales a las que ya posee)

AUTORIZACION EN SQL

La norma SQL incluye los privilegios SELECT (autorización de lectura), INSERT (autorización de inserción), UPDATE (autorización de actualización) y DELETE (autorización de borrado), para la definición de autorizaciones

SQL incluye el privilegio REFERENCES para permitir a un usuario o papel declarar claves externas al crear una relación (para referenciar atributos)

CONCESION Y PREVILEGIOS EN SQL

La cláusula GRANT se usa para conceder o retirar privilegios en SQL

GRANT lista_privilegios ON nombre_relación TO lista_usuarios/papeles

GRANT SELECT ON sucursal TO A, B, C

GRANT UPDATE (importe) ON prestamo TO A, B

GRANT REFERENCES (nombre_sucursal) ON sucursal TO A

ALL PRIVILEGES concede todos los privilegios,

PUBLIC hace referencia a todos los usuarios

PRIVILEGIOS EN PAPELES EN SQL

SQL permite asociar privilegios a los papeles definidos con la cláusula

CREATE ROLE

CREATE ROLE cajero

GRANT SELECT ON cuenta TO cajero

GRANT cajero TO juan

CREATE ROLE gestor

GRANT cajero TO gestor

GRANT gestor TO maría

Privilegio Conceder Privilegios

Por defecto, un usuario o papel que recibe un privilegio no está autorizado a concedérselo a otros usuarios y/o papeles GRANT SELECT ON sucursal TO A WITH GRANT OPTION

Diagramas Concesión Privilegios

Dada la intrincada red de concesiones de privilegios que pueden surgir, se hace necesaria una representación gráfica denominada diagrama de concesiones

Nodos: Usuario y Privilegio

Arcos: Concesiones de Privilegios

Diagrama de Concesiones

Los propietarios pueden otorgar privilegios a otros usuarios o al usuario general PUBLIC; si los privilegios se otorgan con la opción de concesión, entonces pueden ser transmitidos a otros; Los privilegios también pueden revocarse; El diagrama de concesiones es un medio muy útil de recordar lo suficiente sobre la historia de las concesiones y revocaciones, a fin de llevar un control sobre los privilegios dados a los usuarios y sobre su origen

Ejemplo Diagrama Privilegios

Privilegio Retirar Privilegios

Para retirar un privilegio se emplea la cláusula REVOKE

REVOKE lista_privilegios ON nombre_relación

FROM lista_usuarios/papeles

[RESTRICT|CASCADE]

REVOKE SELECT ON sucursal FROM A, B, C

REVOKE UPDATE (importe) ON prestamo FROM A, B

REVOKE REFERENCES (nombre_sucursal) ON sucursal FROM A

REVOKE GRANT OPTION FOR SE

Privilegio Retirar Privilegios

Para retirar un privilegio se emplea la cláusula REVOKE

REVOKE lista_privilegios ON nombre_relación

FROM lista_usuarios/papeles

[RESTRICT|CASCADE]

REVOKE SELECT ON sucursal FROM A, B, C

REVOKE UPDATE (importe) ON prestamo FROM A, B

REVOKE REFERENCES (nombre_sucursal) ON

sucursal FROM A

REVOKE GRANT OPTION FOR SELECT

ON sucursal FROM A

Conclusión

La finalidad de este trabajo, es dar una inducción en el tema de Diseño de Bases de Datos, a personas ajenas al tema. De manera que por ello los temas se presentan de una manera sencilla y sin tanta terminología.

Nos muestra la gran importancia que para cualquier entidad, ya sea una empresa grande o chica, para el gobierno, hasta para la vida cotidiana de una persona tienen las bases de datos.

Todo gira alrededor de ellas, todos los procesos del mundo están registrados en ellas, de ahí la importancia de llevar a cabo un diseño eficiente y libre de errores de las mismas.

Siempre que una persona escucha hablar de bases de datos y de toda la terminología que las acompaña piensa que es un tema excesivamente complicado, y no es así, todo tiene un porque y lógica, es cosa de familiarizarse un poco con ellas (bases de datos).

Cuando se ven en realidad todas las ventajas que tienen, es mas sencillo el proceso de aprendizaje, ya que siente que el aprender a manejarlas se vera recompensado.

Además de los sencillas que son, es muy fácil acceder a información, manuales y cursos relacionados a ellas, todo esta a la mano, con la facilidad de poner este tema en un buscador de la red y aparecerán infinidad de temas, unos mas complejos que otros, pero siempre uno que se adecue a las capacidades de aprendizaje de cada persona.

Otro punto muy importante es que la mayoría son gratis.

Bibliografía

• 1. MANUAL DE BASE DE DATOS BASICOS ING. MANUEL CHANG ESCUELA SUPERIOR POLITECNICA DEL LITORAL (ESPOL)

• 2. MANUAL DEL PROGRAMADOR CON EL USO DE BASES DE DATOS ING. ALFREDO ALVAREZ. ESCUELA SUPERIOR POLITECNICA DEL LITORAL. ESCUELA DE SISTEMAS.

• 3. BASES DE DATOS RELACIONALES, EDICIONES MG GRILL. ING. JORGE SOTOMAYOR PEREZ.

• 4. DISEÑO DE BASES DE DATOS. EDICIONES MULTILIBROS.

• 5. 

• 6. WWW.LAWEBDELPROGRAMADOR.COM

• 7.  WWW.PROGRAMACIÓN.COM

• 8.  WWW.SOLOCURSOS.COM

• 9. WWW.MONOGRAFIAS.COM

• 10. MANUAL BASES DE DATOS, TABLAS, FORMULARIOS, CONSULTAS TNLG. GERARDO LOOR LINDAO, ACADEMIA NAVAL ALMIRANTE ILLINGWORTH

• 11. JERARQUIA DE LAS BASES DE DATOS, ING. MILTON ORELLANA, ACADEMIA NAVAL ALMIRANTE ILLINGWORTH

• 12. CURSO SOBRE BASES DE DATOS. WWW.EMAGISTER.COM

AGRADECIMIENTO

QUIERO DEJAR EN CONSTANCIA DE MIS MAS SINCEROS AGRADECIMIENTOS DE ORGULLO Y LEALTAD, ALA UNIVERSIDAD AGRARIA DEL ECUADOR, A US AUTORIDADES, PERSONAL DOCENTE, ADMINISTRATIVO, QUIEN EN ESTOS TRES AÑOS DE LABORES SE HA HECHO COMO UNA FAMILIA DENTRO DE LA COMUNIDAD EDUCATIVA.

ALAS INSTITUCIONES PUBLICAS Y PRIVADAS DEL CANTON PALESTINA, EN CUYA OFICINAS SE PERMITIERON ELABORAR LAS PASANTIAS, EMPRESARIALES, PREVIO A LA OBTENCION DEL TITULO DE TECNOLOGO EN COMPUTACÓN E INFORMATICA.

JAIRON CASTRO MACIAS

DEDICATORIA

QUIERO DEDICAR ESTE TRABAJO DE INVESTIGACION TIPO MONOGRAFICO, PRIMERAMENTE A DIOS POR DARME LA CONFIANZA ESPIRITUAL E INTELECTUAL PARA CUMPLIR CON EFICACIA LA META PROPUESTA, EN SEGUNDO LUGAR A MI FAMILIA CUYO APOYO INCONDICIONAL MORAL, ECONOMICO HA SIDO FACTIBLE PARA PODER CULMINAR MIS ESTUDIOS SUPERIORES.

JAIRON CASTRO MACIAS

 

 

 

 

 

 

Autor:

Jairon Temistocles Castro Macias

PROFESOR GUIA: LCDO. ALFREDO LEON ALVARADO

PALESTINA – GUAYAS - ECUADOR

UNIVERSIDAD AGRARIA DEL ECUADOR

FACULTA DE COMPUTACIÓN E INFORMATICA

PROGRAMA REGIONAL DE ENSEÑANZA

PALESTINA

2008