implementar una base de datos sql server 2008

ADMINISTRACIÓN DE BASE DE DATOS - I

Material de Trabajo Elaborado por:

Ing. Oscar Alberto Barnett Contreras

Implementación de una Base de Datos SQL SERVER 2008

UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS

Facultad de Ingeniería Industrial - CEUPS Informática

Curso: Administración de Base de Datos - I

Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 2 de 72

Capítulo I - PRINCIPIOS DE LAS BASE DE DATOS

RELACIONALES

Modelo Lógico

Bajo el enfoque E/R, desde el punto de vista lógico (conceptual)

existen los siguientes conceptos básicos:

Entidad.- Representa una abstracción de la realidad con existencia independiente, es decir, que se diferencia unívocamente de cualquier otro

objeto o cosa que sea, incluso, del mismo tipo. Puede ser de tipo concreto

(tangible) como una persona, un documento, un objeto, un edificio; ó

puede ser abstracta (conceptual) como una transacción bancaria, un curso,

una cuenta de aportes individual, un proceso de matrícula, la asistencia al

trabajo de un personal, etc.

Ejemplo de entidades concretas:

EMPLEADO, ALUMNO, SOCIO, CLIENTE, etc.

BOLETA_PAGO, CARNÉ, CUENTA_INDIVIDUAL, RECETA_ MEDICA.

COMPUTADOR, LIBRO, PUESTO_COMERCIAL, MEDICAMENTO, etc.

OFICINA, AULA, COOPERATIVA, FARMACIA, etc.

Ejemplo de entidades abstractas:

ASISTENCIA, MATRICULA, APORTE, VENTA, etc.

RESERVA_VUELO, ALQUILER_VIDEO, COMPRA_VENTA_MONEDA, etc.

Conjunto de entidades.- Es una colección de entidades que comparten los

mismos atributos o características (¡pero no los mismos valores en esos atributos!).

Ejemplos:

Todos los empleados que laboran en una empresa, comparten sus

atributos: Nombre, Número de Seguro Social, Edad, Cargo...

Todas las oficinas sucursales de una empresa, comparten los atributos: Ciudad, Director, Área, Región…

Todas las boletas de pago de los empleados, comparten sus

atributos: Nro_Boleta, Período_Pago, Descuento, Bonificación,

MontoBruto…

Todos los productos de un almacén, comparten los atributos: Código

de Familia, Código de Producto, Precio Unitario, Existencias…

Atributo.- Representa una característica significativa de la Entidad que

puede valorarse. Una entidad dentro de un conjunto de entidades, tiene

valores específicos asignados para cada uno de sus atributos, de esta

forma, es posible su identificación unívoca.





Ejemplo:

A la colección de entidades EMPLEADO, con el siguiente conjunto de

atributos en común (Num_Empl, Nombre, Edad, Cuota, CUSPP), pertenecen

las entidades:

(1, Oscar Barnett , 50, 14000, 123881OBCNT1)

(2, Judith Jiménez, 30, 15000, 265341JJMNG1) (3, Blanca Yaya , 40, 15000, 157183BYGMZ2)

(4, William León , 50, 19000, 114768WLVLQ3)

...

Cada una de las entidades pertenecientes a este conjunto se diferencia de

las demás por el valor de sus atributos. Nótese que dos o más entidades

diferentes pueden tener los mismos valores en algunos atributos, pero nunca en todos a la vez.

Atributos identificativos.- Son aquellos que permiten diferenciar a una

instancia de la entidad de otra distinta. Por ejemplo, el atributo

identificativo que distingue a un empleado de otro es su número de

empleado: Num_Empl. También podría ser el atributo CUSPP.

Dominio de atributo.- Hace referencia al tipo de dato que será

almacenado o a restricciones en los valores que el atributo puede tomar

(Cadenas de caracteres, números, solo dos letras, solo números mayores

que cero, solo números enteros...).

Indicador NULO (NULL).- Se usa cuando una entidad no tiene valor para un atributo dado, bien sea porque el valor no se conoce, no existe o no se

sabe nada respecto del mismo.

Ejemplo de atributos de diversas entidades (identificadores en negrita):

CLIENTE: Num_Clie, Nombre, Estado, RUC, etc.

SOCIO: ID_Socio, Apellido, Nombre, Giro, Nro_Puesto, etc. ORDEN_COMPRA: Num_Orden, Fecha_Orden, Fecha_Entrega,

Importe, etc.

MEDICAMENTO: Codigo, Nombre, Nro_Lote, Fecha_Vencimiento, etc.

OFICINA: ID_Oficina, Ciudad, Region, Objetivo, etc.

ASISTENCIA: Num_Empl, Hora_Ingreso, Hora_Salida, etc.

MATRICULA: ID, Fecha, ID_Alumno, Grado, Seccion, Turno.

PRODUCTO: ID_Fab, ID_Producto, Nombre, Precio, Existencia, etc. APORTE: ID_Socio, Fecha, Importe, Observacion, etc.

Relación.- Describe cierta dependencia entre entidades o permite la

asociación de las mismas.

Ejemplo:

Dadas dos entidades "Oficina.11" y "Empleado.Oscar Barnett", es posible relacionar que en la oficina 11 se encuentra laborando el empleado

de nombre Oscar Barnett.

Una Relación tiene sentido al expresar las entidades que relaciona.

En el ejemplo anterior, un EMPLEADO (entidad), labora en (relación) una

OFICINA (entidad).





Conjunto de Relaciones.- Consiste en una colección, o conjunto, de

relaciones de la misma naturaleza.

Ejemplo:

Dados los conjuntos de entidades "OFICINA" y "EMPLEADO", todas

las relaciones de la forma OFICINA-EMPLEADO, permiten obtener la

información de las oficinas y sus respectivos empleados.

La dependencia o asociación entre los conjuntos de entidades es llamada

PARTICIPACIÓN. En el ejemplo anterior los conjuntos de entidades

"OFICINA" y "EMPLEADO" participan en el conjunto de relaciones OFICINA-

EMPLEADO.

Se llama GRADO del conjunto de relaciones a la cantidad de conjuntos de

entidades participantes en la relación.

Restricciones.- Son reglas que deben mantener los datos almacenados en

la base de datos, con el objetivo de mantener la coherencia y consistencia.

Cardinalidad.- Dado un conjunto de relaciones en el que participan dos o

más conjuntos de entidades, la correspondencia de cardinalidad indica el

número de entidades con las que puede estar relacionada una entidad dada. Dado un conjunto de relaciones binarias y los conjuntos de entidades A y B,

la correspondencia de cardinalidades puede ser:

Uno a uno: Una entidad de A se relaciona únicamente con una

entidad en B y viceversa.

Uno a varios: Una entidad en A se relaciona con cero o muchas

entidades en B. Pero una entidad en B se relaciona con una única entidad en A.

Varios a varios: Una entidad en A se puede relacionar con 0 o

muchas entidades en B y viceversa.

Claves.- Es un subconjunto del conjunto de atributos comunes en una

colección de entidades, que permite identificar unívocamente cada una de

las entidades pertenecientes a dicha colección. Asimismo, permiten distinguir entre sí las relaciones de un conjunto de relaciones.

Dentro de los conjuntos de entidades existen los siguientes tipos de claves:

Superclave: Es un subconjunto de atributos que permite distinguir

unívocamente cada una de las entidades de un conjunto de

entidades. Si se añade un atributo al anterior subconjunto, el

resultado seguirá siendo una superclave.

Clave candidata: Dada una superclave, si ésta deja de serlo quitando únicamente uno de los atributos que la componen, entonces

ésta es una clave candidata.

Clave primaria: Es una clave candidata, elegida por el diseñador de

la base de datos, para identificar unívocamente las entidades en un

conjunto de entidades.

Los valores de los atributos de una clave, no pueden ser todos iguales para dos o más entidades.

Conjunto de Relaciones R

Para poder distinguir unívocamente las relaciones en un conjunto de

relaciones R, se deben considerar dos casos:





R NO tiene atributos asociados: En este caso, se usa como clave

primaria de R la unión de las claves primarias de todos los conjuntos

de entidades participantes.

R tiene atributos asociados: En este caso, se usa como clave

primaria de R la unión de los atributos asociados y las claves

primarias de todos los conjuntos de entidades participantes.

Si el conjunto de relaciones, R, sobre las que se pretende determinar la clave primaria está compuesto de relaciones binarias, con los conjuntos de

entidades participantes A y B, se consideran los siguientes casos, según sus

cardinalidades:

R es de uno a uno de A a B entonces se toma cualquiera de las dos

claves primarias (A ó B), como clave primaria de R.

R es de uno a muchos de A a B entonces se toma sólo la clave primaria del lado uno, como clave primaria de R.

R es de muchos a muchos de A a B entonces se toma la unión de

los atributos que conforman las claves primarias de A y de B, como

clave primaria de R.

Modelo Físico

Desde el punto de vista físico (implementación), una base de datos estará constituida, entre otros elementos por:

Tabla.- Es la implementación de un conjunto de entidades.

Ejemplos:

Tabla CLIENTE

Tabla SOCIO

Tabla ORDENES Tabla MEDICAMENTO

Tabla OFICINA

Tabla MATRICULA

Columna.- Es la implementación de los atributos de un conjunto de

entidades. Ejemplos en diversos conjuntos de entidades:

CLIENTE : (ID_Cliente, Nombre, Estado)

SOCIO : (Num_Socio, Apellidos, Nombre, Giro, Nro_Puesto)

ORDENES : (ID_Orden, Fecha, Importe)

MEDICAMENTO: (Codigo, Nombre, Nro_Lote, Fecha_Vencimiento)

OFICINA : (Oficina, Ciudad, Region, Objetivo)

MATRICULA : (ID, Fecha, Id_Alumno, Grado, Sección, Turno)

Fila.- Denominada también REGISTRO es la implementación de una

entidad específica (instancia de entidad).

Ejemplos de fila en diversas tablas:

CLIENTE : (21, “BARNETT”, “CA”) SOCIO : (“001”, “BARNETT”, “OSCAR”, “INFORMÁTICA”, 35)

ORDENES : (2301, 2/23/08, 104.75)

MEDICAMENTO: (101, SALBUTAMOL, “001435”, 12/31/11)





OFICINA : (11, “Atlanta”, “Este”, 650000)

MATRICULA : (1, 02/25/10, “A175”, “6to”, “A”, “DIURNO”)

Por otro lado, existen conceptos de funcionalidad que se asignan a las

columnas de una tabla, con la finalidad de permitir un manejo eficiente de

los datos almacenados en ellas:

Clave Principal.- Denominada también “Clave primaria (PK)”. Es aquella

columna cuyo valor es único y obligatorio para cada fila de la tabla. Una

tabla sólo puede tener una clave principal.

Ejemplo: En la tabla ORDENES_DE_COMPRA, la columna ID_ORDEN (Nro.

de la Orden de Compra). Ejemplo: En la tabla CLIENTES, la columna ID_CLIENTE (código asignado al

cliente).

Clave Alterna.- Denominada también “Clave Candidata (AK)”. Es aquella

columna cuyo valor es único y obligatorio para cada fila de la tabla. Es

similar a la clave principal y puede ser empleada como tal, si fuera

necesario.

Ejemplo: En la tabla CLIENTES, la columna RUC (código de contribuyente

del cliente).

Ejemplo: En la tabla EMPLEADOS, la columna CUSPP (código AFP) del

empleado.

Clave Externa.- Denominada también “Clave Foránea (FK)”. Es aquella

columna cuyo valor corresponde con el dominio (conjunto de valores) de la

clave principal de otra tabla (denominada tabla “padre”). Esta clave permite

relacionar las filas de dos tablas (la tabla “padre” y la tabla “hijo”).

Ejemplo: Entre las tablas CLIENTES (“padre”) y ORDENES (“hijo”), se puede establecer una relación mediante las columnas

CLIENTES.ID_CLIENTE y ORDENES.ID_CLIENTE.

Ejemplo: Entre las tablas EMPLEADOS (“padre”) y CLIENTES (“hijo”), se

puede establecer la relación mediante las columnas

EMPLEADOS.ID_EMPLEADO y CLIENTES.ID_EMPLEADO.

Clave de Índice.- denominada también “Entrada Inversa (IR)”. Es aquella

columna cuyos valores serán usados para permitir eficiencia en

ordenamientos virtuales de las filas en la tabla ó también para el acceso

rápido a dichas filas.

Ejemplo: En la tabla EMPLEADOS, un índice en la columna Apellidos puede permitir ordenar alfabéticamente las filas de datos, para una

consulta o un listado impreso.

Ejemplo: En la tabla ORDENES, un índice en la columna Fecha, permitiría

ubicar rápidamente las filas de datos que correspondan a una

cierta fecha.





Proceso de Normalización

La tarea de un Diseñador de Base de Datos consiste en estructurar

los datos de forma que se eliminen duplicaciones innecesarias y se

proporcione una búsqueda rápida para la información necesaria. El proceso

de perfeccionar tablas, columnas, claves y relaciones para crear una base

de datos eficaz se denomina Normalización.

Es un proceso complejo formado por reglas específicas y distintos niveles de intensidad. La definición completa de Normalización es: “El

proceso de descartar la repetición de grupos, minimizar la

redundancia, eliminar claves compuestas para la dependencia

parcial y separar los atributos que no sean de la clave”. En términos

generales, las reglas de normalización se pueden resumir en una sola frase:

"Cada atributo (columna) debe ser una realidad de la clave, toda la clave y

nada más que la clave".

Cada tabla debe describir sólo un tipo de entidad (como una persona, un

lugar, un pedido de cliente o un producto).

Grados de normalización

Existen básicamente tres niveles de normalización: Primera Forma

Normal (1NF), Segunda Forma Normal (2NF) y Tercera Forma Normal

(3NF). Cada una de estas formas tiene sus propias reglas. Cuando una base de datos se conforma a un nivel, se considera

normalizada a esa forma de normalización. No siempre es una buena

idea tener una base de datos conformada en el nivel más alto de

normalización, puede llevar a un nivel de complejidad que pudiera ser

evitado si estuviera en un nivel más bajo de normalización.

En la tabla siguiente se describe brevemente en qué consiste cada una de las reglas, y posteriormente se explican con más detalle.

Regla Descripción

Primera Forma Normal (1FN) Incluye la eliminación de todos los

grupos repetidos.

Segunda Forma Normal (2FN)

Asegura que todas las columnas que

no son clave sean completamente

dependientes de la clave primaria

(PK).

Tercera Forma Normal (3FN)

Elimina cualquier dependencia

transitiva. Una dependencia

transitiva es aquella en la cual las

columnas que no son clave son dependientes de otras columnas que

tampoco son clave.





Dependencia Funcional (DF)

La idea de Dependencia Funcional como una RESTRICCIÓN es

intuitivamente atrayente. Por ejemplo, parece sensato decir que su Sueldo

depende de su Rendimiento o que su Modo de ser depende de su

Educación. La dependencia puede ser de dos formas, una se refiere a los

atributos de una misma entidad y la otra se refiere a las asociaciones entre

entidades. Cuando modelamos, el problema de la elección de las entidades se

origina en el hecho de que el valor de un atributo de la entidad puede

determinar completamente el valor de otros atributos. Tales dependencias

nos obligan a evitar determinadas agrupaciones de atributos.

Definición Un atributo B tiene dependencia funcional de otro atributo A si, cada

vez que se repite un valor de A; B necesariamente toma el mismo valor. Se representa A B y se lee “A define a B”

En otras palabras si se conoce el valor de A entonces, el valor de B

queda determinado.

Ejemplo: En la entidad EMPLEADO, el valor del Apellido (B) es dependiente

funcional del valor de Código (A).

Ejemplo: En la entidad FACTURA, el valor del Importe (B) es dependiente

funcional del valor de Nro_Factura (A).

Ejemplo: En la entidad SOCIO, el valor del Giro (B) es dependiente

funcional del valor de ID_SOCIO (A).

Ejemplo: En la entidad CLIENTE, el valor de Dirección (B) es dependiente

funcional del valor de RUC (A).

Ejemplo: En la entidad EMPLEADO, el valor de Remuneracion (B) es

dependiente funcional del valor de COD_SEGURO (A).

Definición

Cuando una entidad tiene una clave primaria compuesta (por 2 o

más atributos) y los demás atributos dependen de la clave como conjunto y

no tan solo de alguno de los atributos de la clave, se dice que el atributo tiene dependencia funcional completa (DFC) de la clave primaria

compuesta.

Ejemplo: En la entidad DETALLE_FACTURA, el valor de Cantidad (B) es

dependiente funcional del valor de la clave principal (PK)

compuesta Nro_Orden+Código_Producto (A).

Ejemplo: En la entidad TRANSACCION_CUENTA, el valor de Fecha (B) es dependiente funcional del valor de la clave principal (PK)

compuesta Nro_Cuenta+Nro_Transacción (A).

Ejemplo: En la entidad PRODUCTO, el valor de PrecioUnitario (B) es


compuesta CódigoFamilia+CódigoProducto (A).





Ejemplo: En la entidad PAQUETE_CONFECCION, el valor de Cantidad (B) es


compuesta Nro_OrdenTrabajo+Código_Paquete (A).

Dependencia Transitiva

Si existe una DF de A en B (“A define a B”) y de B en C (“B define a

C”), entonces, existe una dependencia funcional de A en C (“A define a C”)

que se llamará Dependencia Transitiva y se representa así:

Si A B y B C, entonces A C

Ejemplo: En la entidad FACTURA, si el valor de Direccion_Cliente (C)

depende del valor de Codigo_Cliente (B), el valor de

Codigo_Cliente (B) depende del valor de Nro_Factura (A); y

además el valor de Dirección_Cliente (C) depende del valor de

Nro_Factura (A).

Entonces existe Dependencia Transitiva de Dirección_Cliente

respecto a Nro_Factura, por intermedio de Codigo_Cliente.

Aplicando La Normalización

Primera Forma Normal

La regla de la Primera Forma Normal establece que las columnas

repetidas deben eliminarse y colocarse en tablas separadas. Procesar la base de datos según la Primera Forma Normal resuelve el problema de los

encabezados de columna múltiples.

Posiblemente para almacenar datos de Órdenes de Compra, los

diseñadores de bases de datos inexpertos crearán algo similar a la siguiente

tabla:

ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO NUM_ITEM DESC_ITEM CANT PRECIO

2301 23/2/10 101 BARNETT CA 3786 RED 3 35.00

2301 23/2/10 101 BARNETT CA 4011 RAQUETA 6 65.00

2301 23/2/10 101 BARNETT CA 9132 PAQ-3 8 4.75

2302 25/2/10 107 JIMENEZ WI 5794 PAQ-6 4 5.00

2303 27/2/10 110 YAYA MI 4011 RAQUETA 2 65.00

2303 27/2/10 110 YAYA MI 3141 FUNDA 2 10.00

Una y otra vez, crearán columnas que representen los mismos

datos. La Normalización ayuda a clarificar la base de datos y a organizarla en partes más pequeñas y más fáciles de entender. En lugar de tener que

entender una tabla gigantesca y monolítica que tiene diferentes aspectos,

sólo tenemos que entender los objetos pequeños y más tangibles, así como

las relaciones que guardan con otros objetos también pequeños.

Segunda Forma Normal

La regla de la Segunda Forma Normal establece que todas las dependencias funcionales parciales se deben eliminar y separar dentro de

sus propias tablas. Una dependencia funcional parcial es un término que

describe a aquellos datos que no dependen de toda la clave primaria

compuesta de la tabla para ser identificados.

Una vez alcanzado el nivel de la Segunda Forma Normal, se

controlan la mayoría de los problemas de lógica. Podemos insertar un

registro sin un exceso de datos en la mayoría de las tablas.





Tercera Forma Normal

Una tabla está normalizada en esta forma si todas las columnas que

no son clave son funcionalmente dependientes por completo de la clave

primaria y no hay dependencias funcionales transitivas. Como ya se explicó

anteriormente, una dependencia transitiva es aquella en la cual existen

columnas que no son clave que dependen de otras columnas que tampoco

son clave.

Cuando las tablas están en la Tercera Forma Normal se previenen

errores de lógica cuando se insertan o borran registros. Cada columna en

una tabla está identificada de manera única por la clave primaria, y no debe

haber datos repetidos. Esto provee un esquema limpio y elegante, que es

fácil de trabajar y expandir.

Ejemplo

Un dato sin normalizar no cumple con ninguna regla de normalización. Para

explicar con un ejemplo en qué consiste cada una de las reglas, vamos a

considerar los datos de la siguiente tabla.

ORDENES

ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO NUM_ITEM DESC_ITEM CANT PRECIO

2301 23/2/10 101 BARNETT CA 3786 RED 3 35.00

2301 23/2/10 101 BARNETT CA 4011 RAQUETA 6 65.00

2301 23/2/10 101 BARNETT CA 9132 PAQ-3 8 4.75

2302 25/2/10 107 JIMENEZ WI 5794 PAQ-6 4 5.00

2303 27/2/10 110 YAYA MI 4011 RAQUETA 2 65.00

2303 27/2/10 110 YAYA MI 3141 FUNDA 2 10.00

Al examinar estos registros, podemos darnos cuenta que contienen un

grupo repetido para NUM_ITEM, DESC_ITEM, CANT y PRECIO. La 1FN prohíbe los grupos repetidos, por lo tanto tenemos que convertir a la

primera forma normal. Los pasos a seguir son:

1. Tenemos que eliminar los grupos repetidos.

2. Tenemos que crear una nueva tabla con la PK de la tabla base y el

grupo repetido.

Los registros quedan ahora conformados en dos tablas que llamaremos ORDENES y ARTICULOS_ORDENES

- ORDENES

ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO

2301 23/2/10 101 BARNETT CA

2302 25/2/10 107 JIMENEZ WI

2303 27/2/10 110 YAYA MI

- ARTICULOS_ORDENES

ID_ORDEN NUM_ITEM DESC_ITEM CANT PRECIO

2301 3786 RED 3 35.00

2301 4011 RAQUETA 6 65.00

2301 9132 PAQ-3 8 4.75

2302 5794 PAQ-6 4 5.00

2303 4011 RAQUETA 2 65.00

2303 3141 FUNDA 2 10.00





Ahora procederemos a aplicar la segunda formal normal, es decir,

tenemos que eliminar cualquier columna no clave que no dependa de la

clave primaria de la tabla. Los pasos a seguir son:

1. Determinar cuáles columnas que no son clave no dependen de la clave

primaria de la tabla.

2. Eliminar esas columnas de la tabla base.

3. Crear una segunda tabla con esas columnas y la(s) columna(s) de la PK de la cual dependen.

La tabla ORDENES está en 2FN. Cualquier valor único de ID_ORDEN

determina un sólo valor para cada columna. Por lo tanto, todas las

columnas son dependientes de la clave primaria ID_ORDEN.

Por su parte, la tabla ARTICULOS_ORDENES no se encuentra en 2FN

ya que las columnas PRECIO y DESC_ITEM son dependientes de NUM_ITEM, pero no son dependientes de ID_ORDEN. Lo que hacemos a continuación es

eliminar estas columnas de la tabla ARTICULOS_ORDENES y crear una tabla

ARTICULOS con dichas columnas y la clave primaria de la que dependen.

Las tablas quedan ahora de la siguiente manera.

- ARTICULOS_ORDENES ID_ORDEN NUM_ITEM CANT

2301 3786 3

2301 4011 6

2301 9132 8

2302 5794 4

2303 4011 2

2303 3141 2

- ARTICULOS NUM_ITEM DESC_ITEM PRECIO

3786 RED 35.00

4011 RAQUETA 65.00

9132 PAQ-3 4.75

5794 PAQ-6 5.00

4011 RAQUETA 65.00

3141 FUNDA 10.00

La tercera forma normal nos dice que tenemos que eliminar

cualquier columna no clave que sea dependiente de otra columna no clave.

Los pasos a seguir son:

1. Determinar las columnas que son dependientes de otra columna no

clave. 2. Eliminar esas columnas de la tabla base.

3. Crear una segunda tabla con esas columnas y con la columna no clave

de la cual son dependientes.

Al observar las tablas que hemos creado, nos damos cuenta que

tanto la tabla ARTICULOS, como la tabla ARTICULOS_ORDENES se

encuentran en 3FN. Sin embargo la tabla ORDENES no lo está, ya que

NOM_CLIENTE y ESTADO son dependientes de ID_CLIENTE, y esta columna no es la clave primaria.

Para normalizar esta tabla, moveremos las columnas no clave y la

columna clave de la cual dependen dentro de una nueva tabla CLIENTES.

Las nuevas tablas CLIENTES y ORDENES se muestran a continuación.





- ORDENES ID_ORDEN FECHA ID_CLIENTE

2301 23/2/10 101

2302 25/2/10 107

2303 27/2/10 110

- CLIENTES

ID_CLIENTE NOM_CLIENTE ESTADO

101 BARNETT CA

107 JIMENEZ WI

110 YAYA MI

Práctica de Normalización

Partiendo del siguiente archivo plano no normalizado, debe realizar el

proceso de normalización para una pequeña biblioteca:

CodLibro Titulo Autor Editorial NombreLector FechaDev

1001 Oracle 11g Oscar Barnett UNMSM Pérez Gómez, Juan 15/04/2010

1004 SPSS V17 Blanca Yaya MACRO Ríos Terán, Ana 17/04/2010

1005 SQL Server 2010 Oscar Barnett UNMSM Roca, René 16/04/2010

1006 Oracle 11g Judith Jiménez Oracle Corp. García Roque, Luis 20/04/2010

1007 VB NET 2010 Julio Córdova UNMSM Pérez Gómez, Juan 18/04/2010





Uso Del CASE Erwin Para Diseñar Base De Datos

AllFusion Erwin 7 es una herramienta CASE (Ingeniería de Información Asistida por Computadora), que permite modelar información de

manera inteligente. Las siglas Erwin significan Entidad Relación para Windows.

Básicamente con Erwin podemos modelar, refinar, transformar, gestionar y documentar plataformas contenedoras de datos.

Para poder ingresar al software debemos realizar lo siguiente:





La interface principal del CASE Erwin se muestra a continuación:

Barra de Titulo

Menú Principal Toolbar estándar

Panel de Registro Panel de Avisos

Ventana de Diagrama

Explorador de Objetos





Diseño de Entidades

En el Toolbox de Erwin se debe hacer clic sobre el icono Entity y luego en el área en blanco. Aparecerá un objeto que representa un conjunto de

entidades y donde se digita el nombre del mismo, tal como se muestra a continuación:

A continuación se debe digitar sus correspondientes

atributos. La PK se inserta en el primer recuadro, los

atributos no claves en el recuadro inferior, como se muestra en la imagen del lado:

Posteriormente se puede editar el objeto entidad para establecer propiedades de

cada elemento, tal como se muestra en la imagen del lado:

Entidad





Entonces se muestra la siguiente ventana donde se puede establecer el

dominio, las restricciones y el icono de cada atributo:

Para establecer el tipo de letra,

tamaño, color, color de línea y color de relleno para la entidad se debe hacer clic con

el botón derecho sobre el objeto entidad y seleccionar Object Font & Color como se

muestra:

Restricciones

Dominio

Icono





Relacionar Entidades

Para relacionar dos o más entidades debemos de ubicar en el Erwin Toolbox los

botones de relación (RelationShip) como se muestra al lado:

Identifying relationship, relación de identificación.

Many-To-Many relationship, relación muchos a muchos.

Non-Identifying relationship, relación de no identificación.

Para poder establecer la relación entre dos entidades se debe hacer

clic sobre el botón de relación pertinente, un siguiente clic en la tabla padre y luego un clic en la tabla hijo, por ejemplo:

Avanzando con el ejemplo, se tiene definido el modelo lógico:





Para establecer el nombre del diagrama del modelo, sobre el área en blanco debemos hacer clic

derecho, seleccionar la opción Stored Display Settings… como se muestra en las imágenes:

Luego de establecer los parámetros deseados en las fichas de la

ventana anterior, se pulsa el botón OK. En el ejemplo se muestra el diagrama con el nombre establecido como ‘BarnettLogico’:





La opción New sirve para adicionar una nueva ficha y la opción

Rename sirve para renombrar la ficha.

En el ejemplo, se ha agregado una ficha Display2 y se ha renombrado como ‘BarnettFísico’, tal como se muestra a continuación:

Para poder establecer y o modificar la cardinalidad de las tablas

relacionadas se hace clic en el Menú Model \ RelationShip…, o clic derecho

en la línea de relación, seleccionar RelationShip Properties y aparece la

ventana que se muestra a continuación:





Notación Lógica y Física

Primero se debe hacer clic sobre el Menú Model \ Model Properties,

clic sobre la ficha Notation encontramos tres metodologías:

Integration Definition for Information Modeling IDEF1X, Definición

de Integración para un Modelo de Información.

Information Engineering (IE), Ingeniería de la Información.





Dimencional Modeling (DM), Modelo Dimensional.

Propiedades de un objeto Entidad

En el modelo Lógico, para establecer la definición de un objeto

entidad se debe hacer clic derecho sobre el objeto y seleccionar la opción

Entity Properties… tal como se

muestra:

Luego aparecerá la siguiente ventana:

En la ficha Definition

se puede digitar una definición asociada a la

entidad, de tal manera que cada tabla podría tener una

definición pre-establecida.





Para establecer un Icono a un determinado objeto entidad se puede

hacer clic derecho sobre el objeto, seleccionar la opción Entity Properties… y luego la ficha Icon tal como se muestra:

Niveles de Visualización de las Entidades

En el Menú Format \ Display Level, se encuentran las opciones de niveles de visualización:

Entity.- Muestra solo los títulos de los objetos entidad.

Atribute.- Muestra los objetos entidad con sus atributos.

Se debe hacer click sobre el boton Import

para importar o seleccionar un Icono





Primary Key.- Muestra los objetos entidad con sus Claves Primarias y

Foráneas (Externas).

Definition.- Muestra las definiciones de los objetos entidad.

Icon.- Muestra los iconos de los objetos entidad





INSTALACIÓN BÁSICA DE UNA INSTANCIA SQL

SERVER 2008

Luego de haber decidido la Edición a instalar y de verificar los

requerimientos de Hardware y Software de acuerdo a lo indicado en el Web Site de Microsoft, inserte el DVD de instalación en la Unidad Lectora.

A continuación se mostrará, en forma sintetizada, la secuencia de instalación para la Edición Estándar de SQL Server 2008 (Inglés) en una

plataforma Windows Server 2003 Release 2, dentro de una red LAN sin dominio:

1. Se carga SQL Server Installation Center, con la ficha Planning donde debe revisar la información acerca de requerimientos para la instalación,

recomendaciones de seguridad y

realizar un chequeo de la

configuración del sistema. Haga

clic en el link

“System Configuration

Checker”

2. Verificar que todo

es correcto y

pulsar el botón OK para retornar

al Installation Center.





3. Haga clic en la ficha

Installation, luego en el link New SQL

Server stand-alone Installation, para

iniciar el proceso.

4. Aguarde unos segundos mientras se completa la inicialización.

5. Se inicia una

comprobación de

errores para el proceso de copia de

los archivos de soporte. No debe

ocurrir ninguna falla para poder

continuar. Al terminar pulsar el

botón OK.





6. Debe digitar la clave

del producto y pulsar

el botón Next >

7. Ahora acepte los

términos de licencia, activando el

CheckBox I accept

the licence terms. Pulsar botón Next >

8. En el cuadro de

instalación de archivos de soporte,

pulsar el botón Install.





9. Debe seleccionar las características de

SQL server 2008 que desea instalar.

Asegúrese de instalar

los servicios que en algún momento vaya

a utilizar, si está totalmente seguro

que no va a usar un servicio específico

cómo Analysis

Services, limpie la casilla de verificación

junto a él, y pulsar el botón Next >

10. Determine si la instancia que va instalar es una instancia por default o

nombrada, en el segundo caso tendrá

que asignar a ésta un nombre con el

cual la reconocerá en el futuro; si la

instancia es por

default, la forma de conectarse a ésta

desde servidores o equipos clientes

remotos, será por medio del Nombre

de la PC o de la dirección IP de la

misma. Pulsar el

botón Next >

11. Verifique si tiene el espacio libre en el

disco donde se instalará el producto,

luego pulsar en el

botón Next >





12. Configurar las cuentas con las cuales se ejecutará el servicio; la

recomendación es utilizar diferentes cuentas, sin embargo, en la imagen puede observar cómo

una cuenta es utilizada para ejecutar más de un

servicio, con el botón

intermedio podría seleccionar la opción

para utilizar la misma cuenta para todos los

servicios, en cuyo caso solamente tendrá que

escribir credenciales una vez, pero no estará

cumpliendo con buenas

prácticas de seguridad. Después de configurar

las cuentas pulsar en la pestaña Collation.

13. En Collation, observe

los métodos de

ordenamiento que van a ser utilizados tanto para

SQL Server cómo para Analysis Services; es

importante que tenga un cuenta si existen

regulaciones en su organización acerca del

tipo de ordenamiento a

utilizar, y de no ser así, busque que tanto las

bases de datos, cómo Analysis Services

tengan modelos de ordenamiento similares para evitar problemas cuando estos dos

componentes se conecten entre sí, luego pulsar en el botón Next >

14. Ahora configure el

Modo de autenticación para el inicio de sesión:

Windows ó Mixto. En el segundo caso, es

recomendable que establezca una

contraseña inicial para

el login sa. Asimismo agregue las cuentas de

usuario, al menos la cuenta de usuario

Windows actual, como





administrador. Luego pulsar el botón Next >

15. Finalmente se mostrará un cuadro resumen de todas las opciones y parámetros seleccionados y configurados. Pulsar el botón Install para que

la instalación inicie. Al terminar, verifique el resumen final de la instalación y pulsar el botón Close para cerrar.

Cargar Microsoft SQL Server Management Studio 2008

Ahora debe especificar las Credenciales para solicitar un inicio de sesión, empleando una cuenta configurada en la instalación del producto, por

ejemplo Autenticación Mixta; Server=SERVIDOR; User=sa; Password=123; y pulsar el botón Connect





Si todo es correcto, se muestra la siguiente pantalla de una sesión

iniciada con el servidor conectado:

Puede empezar a trabajar empleando el Explorador de Objetos o sino

Cerrar la Ventana de aplicación para salir de SQL Server. Recuerde que posiblemente algunos servicios continúan iniciados (según haya configurado

en la instalación) por lo que, si desea detenerlos puede emplear las

Herramientas Administrativas de Windows, como lo haría con cualquier otro servicio.





Generación de una base de datos SQL Server

2008

Completar el Modelo Físico

Para nombrar un objeto Relationship, se hace clic derecho sobre

la relación y selecciona Relationship Properties…

Es importante definir el nombre de las restricciones de clave externa

(FK) para el ulterior proceso de generación de la base de datos. Para ello, en la ficha General se digita el nombre en la casilla correspondiente Foreign Key

Constraint Name:





Ahora es necesario completar el modelamiento a nivel físico, de

acuerdo a los parámetros siguientes:

Además, se desea establecer algunas restricciones (constraint) en el modelo físico:

1. La fecha de una orden siempre

es menor a la fecha actual.





2. El precio de un artículo siempre

debe ser positivo.

3. La cantidad de un artículo en una orden siempre debe ser positiva.

4. El cliente más frecuente es

‘Barnett’.





Ahora, en el Menú Tools\Forward Engineer\Schema Generation…

se muestra la siguiente ventana de diálogo:

Pulsa el botón Preview… para completar el Script con las

instrucciones pertinentes:





A continuación, de acuerdo a la versión de SQL Server disponible,

seguir una de las siguientes secuencias:

Versión 2005 de SQL Server

Pulsar el botón Generate... para establecer la conexión correcta con el servidor SQL Server que se desea, para ello debe digitar los parámetros

correctos de conexión:

User Name sa (Para autenticación Mixta)

Password <*******>

Database Master

Server Name <Nombre del Servidor>

Pulsar el botón Connect… para que se genere la base de datos en el

servidor SQL Server 2005, al finalizar cerrar la ventana de diálogo y verificar en el servidor la existencia de la base de datos creada. Luego puede salir de

ERWIN.

Versión 2008 de SQL Server

Pulsa en el botón para guardar el archivo con el Script completo, creando un archivo de extensión .sql

Luego pulsa el botón Close y a continuación el botón OK para cerrar

la ventana de diálogo.





Ahora debe iniciar Microsoft SQL Server Management Studio y

conectarse a su servidor instalado:

Pulsar el botón Connect para completar la conexión:





Ahora en el Menú File \ Open \ File… seleccionar el archivo de script

guardado anteriormente (EjemBarnett.sql) y pulsar el botón Open:

Ahora que el script está cargado, debe pulsar el botón Execute





La base de datos será creada de acuerdo a las instrucciones del script

generado por Erwin:

Verificar la BD para asegurase que contiene todos los objetos que se

diseñaron con ERWIN:

La base de datos BARNETT ha sido creada





Es conveniente crear un objeto Diagrama en SQL Server para

comprobar las relaciones entre las tablas:

Ahora que todo es correcto, puede salir de Erwin.

Práctica de Laboratorio – Modelamiento BD 1. Elabore un modelo en Erwin el cual esté asociado a un proceso de

planillas, considerando solo objetos Entidad. (Claves y tipos de datos para los atributos)

2. Relacione usted los objetos Entidad. 3. Represente las relaciones utilizando la metodología Ingeniería de la

Información (IE). 4. Establezca una definición para cada objeto Entidad

5. Establezca un Icono para cada objeto Entidad

6. Muestre usted el modelo con los cinco niveles de visualización ya explicados.

7. Genere desde ERWIN la base de datos para un servidor SQL Server 2008.

8. Elabore nuevamente los siete puntos anteriores, pero esta vez para un proceso productivo.

Contexto del Caso De Estudio

La empresa “Comercial BARNETT SAC” comercializa productos del rubro Ferretería a través de una serie de oficinas de ventas distribuidas en

diferentes regiones geográficas del país (una sola oficina por ciudad en cada

región), donde cada oficina es identificada por un Nro. de oficina (número entero). Cada oficina está a cargo de un Director el cual, por política de la

empresa, es siempre un empleado del Área de Ventas y que, ocasionalmente, puede tener a su cargo más de una oficina. Este Director es responsable de

que la oficina cumpla con el Objetivo de ventas mensual que se haya establecido para dicha oficina. Cabe señalar que por política de la empresa,

todas las transacciones monetarias se expresan en dólares USA.





Asimismo, cada oficina dispone de empleados convocados mediante

contrato por tiempo indefinido, (cuya fecha determina su Tiempo de Servicio),

y cumplen labor como personal de ventas. Cada empleado es identificado por un Nro. de empleado (número entero) y cuenta con su propia Cartera de

Clientes a los cuales atiende registrando sus pedidos; de esa manera los empleados pueden cumplir con una cuota de ventas mensual que se les

asigna. Un empleado del área de Ventas puede tener los siguientes Cargos ó Títulos: Representante de Ventas, Director de Ventas, Vicepresidente de

Ventas.

Los clientes son identificados mediante un Nro. de cliente (número entero) y a cada uno se le asigna un monto límite para ventas al crédito,

este monto no puede ser excedido bajo responsabilidad del empleado que lo atiende. Cada cliente tiene un personal para contacto con quien los

empleados de venta realizan tratos comerciales, por lo que es importante disponer del número telefónico de tales contactos para comunicarse con

ellos. Los productos son suministrados por varios proveedores los cuales,

muchas veces, utilizan códigos similares para sus productos por lo que, para

identificarlos se emplea un primer código de familia (3 caracteres) y además un código de producto (5 caracteres). Es importante conocer en todo

momento la existencia de los productos para atender los pedidos de los clientes, así como controlar la cantidad mínima de existencias para procesar

la reposición de productos cuando sea necesario, siguiendo la política de inventarios de la empresa.

REQUERIMIENTOS DE INFORMACION

Se necesita mantener actualizada la información sobre las oficinas, empleados, clientes y productos, así como registrar los pedidos de los

clientes atendidos por los empleados de manera que se pueda suministrar información sobre las actividades relacionadas con el área de Ventas:

Listar las oficinas existentes ordenadas por su Objetivo y/o Ventas.

Listar los empleados en orden alfabético por nombre.

Listar los clientes ordenados por Límite de crédito.

Listar los productos ordenados por familia y/o precio.

Listar las oficinas de una determinada región y/o ciudad.

Listar las oficinas cuya Venta es menor a un cierto monto.

Listar los empleados que tienen un cierto título.

Listar los empleados asignados a una cierta oficina.

Listar los empleados que han sido contratados en un período cualquiera.

Listar al Director de una oficina determinada.

Listar las oficinas que cumplen o no cumplen con su objetivo.

Listar los empleados que cumplen o no cumplen con su cuota.

Listar los clientes que no han realizado pedidos hasta la fecha.

Listar los productos que deben ser reabastecidos.

Listar los productos clasificados por rangos de precio.

Listar los pedidos realizados por cada cliente.

Listar los pedidos atendidos por cada empleado.

Calcular las comisiones por venta a cada empleado.

Calcular descuentos a los clientes por volumen de pedido.

Calcular los precios límite de los productos.

etc., etc.

NOTA: Cada pedido solo consta de un único producto.





Objetos De Un Servidor Sql Server 2008

Existen cuatro partes posibles en el nombre de un objeto:

Nombre del servidor

Nombre de la BD

Nombre del propietario del objeto

Identificador del objeto

Por lo que el nombre completo ó absoluto de un objeto tabla “Ventas”

que pertenece al propietario “dbo” en la base de datos “Ventas” del servidor “SERVIDOR”, sería: SERVIDOR.Ventas.dbo.Oficinas

Ejemplos Los siguientes nombres son válidos en diversas circunstancias y tipos

de objeto:

SERVIDOR.Ventas...Oficinas SERVIDOR...dbo.Oficinas

SERVIDOR…Oficinas Ventas.dbo.Oficinas

Ventas...Oficinas

dbo.Oficinas Oficinas

Ejemplo: --CONOCE EL NOMBRE DEL SERVIDOR

SELECT * FROM sys.servers; --DIVERSAS CONSULTAS DE SELECCIÓN

SELECT * FROM SERVIDOR.Ventas.dbo.Oficinas; SELECT * FROM Ventas.dbo.Oficinas;

SELECT * FROM Ventas..Oficinas;

SELECT * FROM Oficinas

Objetos De Una Base De Datos Sql Server

DATABASE DIAGRAMAS (DIAGRAMAS DE BASE DE DATOS)

Son representaciones gráficas de la base de datos que pueden contener: tablas, vistas; en las cuales se pueden realizar también operaciones

con ellas. Para crear un diagrama basta con seleccionar la opción New

Database Diagrams.. del menú contextual de la carpeta “Database Diagrams”

TABLES (TABLAS) Es el objeto de almacenamiento permanente de datos. Tiene una

estructura tabular (filas y columnas) y se crea mediante la opción New Table.. del menú contextual de la carpeta “Tables”

VIEWS (VISTAS) La vista constituye el elemento de consulta básico en la base de datos,

pues tiene la capacidad de mostrar datos provenientes de las tablas y/o vistas existentes no solo en la base de datos actual sino también de otras bases de

datos. Para crear una vista basta con seleccionar la opción New View.. del menú contextual de la carpeta “Views”

SYNONYMS (SINONIMOS)





Son nombres alternativos para tablas, vistas, procedimientos

almacenados y otros objetos, que permite referirse a uno de estos objetos con

más de un nombre. Ejemplo.- Si en el servidor “SERVIDOR” existe una BD “Ventas” en la que

existe una tabla “Clientes” y se desea crear un nombre sinónimo para dicha tabla:

Se elige la opción New Synonym.. en el menú contextual del objeto Synonims de la BD Ventas.

Se configura el sinónimo de manera adecuada:

SERVICES BROKER (SERVICIO INTERMEDIARIO) Proporciona colas y mensajería como parte del motor de base de datos,

de tal manera que se puedan crear aplicaciones distribuidas entre servidores con mayor grado de confiabilidad. Los programadores de aplicaciones que

usan Service Broker pueden distribuir las cargas de trabajo de datos en varias

bases de datos sin tener que programar complejas funciones internas de comunicación y mensajería. Se compone de siete subcarpetas:

Messages Type, Tipos de Mensaje

Contracts, Contratos

Queues, Colas

Services, Servicios

Routes, Rutas

Remote Service Binding, Enlace de servicio remoto

Priority Broker, Nivel Prioritario

Las opciones de trabajo están incorporadas en el menú contextual de

cada una de las subcarpetas.

STORAGE (ALMACENAMIENTO)

El “almacenamiento” permite emplear el servicio de búsqueda, filtro e indización de texto completo como parte de las tareas del motor de base de

datos.

Esta carpeta formada por cuatro subcarpetas:

Full Text Catalogs, Catálogos de texto

Partition Schemes, Esquema de partición

Partition Functions, Funciones de partición

Full Text StopLists, Lista de texto irrelevante





Cada una de ellas representa una parte en la arquitectura para

búsqueda de texto en SQL Server.

SECURITY (SEGURIDAD)

Aquí se encuentran los objetos relacionados al control de acceso y permisos en una base de datos. Para ello existen siete subcarpetas:

Users, Usuarios

Roles, Funciones (permisos)

Schemas, Esquemas

Asymetric Keys, Claves Asimétricas

Certificates, Certificados

Symetric Keys, Claves Simétricas

Database Audit Specifications, Especificaciones de Auditoría de base

de datos

En cada una de ellas se pueden crear objetos para controlar la actividad dentro de la base de datos, como un complemento a la seguridad del

servidor.

PROGRAMMABILITY (PROGRAMACION)

Aquí se encuentran varias subcarpetas referidas a los objetos de programación:

Stored Procedures, Procedimientos Almacenados (código que

automatiza una tarea)

Functions, Funciones (código que calcula y retorna valor)

Databases Triggers, Desencadenantes (PA automatizados)

Assemblies, Ensamblados NET (incorporados con CREATE ASSEMBLY)

Types, tipos (datos de sistema, de usuario, Colecciones esquema XML)

Rules, reglas (restricción de valor). Se prefiere constraint CHECK

Defaults, Valores predeterminados. Se prefiere constraint DEFAULT

Plan Guides, Plan

Configuración del Servidor con instrucciones Set

Permite configurar diversos comportamientos en el servidor, por ejemplo para adaptarlo al estándar SQL 92. Algunas de estas opciones son las

siguientes:

SET ANSI_WARNINGS {ON|OFF}, Maneja advertencias requeridos por el estándar ANSI, por ejemplo:

CREATE TABLE PruebaWarning(Col1 int NULL) INSERT INTO PruebaWarning(Col1)VALUES(5)

INSERT INTO PruebaWarning(Col1)VALUES(Null)

INSERT INTO PruebaWarning(Col1)VALUES(7) PRINT 'warnings on'

SET ANSI_WARNINGS ON SELECT SUM (Col1) FROM PruebaWarning

PRINT 'warnings off' SET ANSI_WARNINGS OFF

SELECT SUM (Col1) FROM PruebaWarning





Resultado de ejecución:

warnings on

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected) warnings off

(1 row(s) affected)

SET ANSI_PADDING {ON|OFF}, controla el efecto de los espacios en blanco o

ceros finales cuando se insertan valores en columnas de longitud fija o variable. El tipo específico de dato determina el comportamiento final.

Ejemplo: Char(n) NOT NULL; ON|OFF rellena con espacios en blanco hasta

completar el tamaño de la columna. Char(n) NULL, ON rellena con espacios en blanco hasta completar el

tamaño de la columna; OFF recorta todos los espacios en blanco a la

derecha. Varchar(n), ON; No recorta ni rellena valores; OFF recorta espacios en

blanco a la derecha pero no rellena Ejemplo:

--PRUEBA COMANDO SET_PADDING PRINT 'Prueba con ANSI_PADDING ON'

SET ANSI_PADDING ON; CREATE TABLE tabla1 (

ColChar CHAR(18) NULL,

ColVarchar VARCHAR(18) NULL, ColVarbinary VARBINARY(8));

INSERT INTO tabla1 VALUES ('No blancos', 'No blancos', 0x00ee); INSERT INTO tabla1 VALUES ('Rellena blanco ', 'Rellena blanco ', 0x00ee00);

SELECT 'CHAR' = '>' + ColChar + '<', 'VARCHAR'='>' + ColVarchar +

'<',ColVarbinary

FROM tabla1; PRINT 'Prueba con ANSI_PADDING OFF';

SET ANSI_PADDING OFF;

CREATE TABLE tabla2 ( ColChar CHAR(18) NULL,

ColVarchar VARCHAR(18) NULL, ColVarbinary VARBINARY(8));

INSERT INTO tabla2 VALUES ('No blancos', 'No blancos', 0x00ee); INSERT INTO tabla2 VALUES ('Rellena blanco ', 'Rellena blanco ', 0x00ee00);

SELECT 'CHAR' = '>' + ColChar + '<', 'VARCHAR'='>' + ColVarchar + '<',ColVarbinary

FROM tabla2;





DROP TABLE tabla1

DROP TABLE tabla2

Resultado de la ejecución:

Asimismo hay más comandos de configuración tales como:

SET ANSI_NULLS {ON|OFF}, controla si se puede utilizar el operador de

igualdad con NULL, por ejemplo: WHERE Col1= NULL para determinar si la

columna tiene un contenido NULL.

SET IMPLICIT_TRANSACTIONS {ON|OFF}, determina si las instrucciones CREATE, SELECT, INSERT y UPDATE inician transacciones automáticamente

cada vez que se ejecuten, en cuyo caso se debe confirmar (COMMIT) o revertir (ROLLBACK) explícitamente la instrucción.

SET ANSI_DEFAULTS {ON|OFF}, permite establecer una compatibilidad

completa con ANSI, pues combina una serie de instrucciones:

SET ANSI_NULLS ON

SET ANSI_NULL_DDFLT_ON ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET CURSOR_CLOSE_ON_COMMIT ON

SET IMPLICIT_TRANSACTIONS ON

SET QUOTED_IDENTIFIER ON

Configuración De Una Base De Datos

Cuando desee modificar la configuración de una BD puede emplear el

comando ALTER TABLE:

ALTER TABLE <NombreBD> SET

{SINGLE_USER|RESTRICTED_USER|MULTI_USER} | {READ_ONLY | READ_WRITE} |

AUTO_SHRINK {ON|OFF} ANSI_NULLS {ON|OFF} |

ANSI_WARNINGS {ON|OFF} | QUOTED_IDENTIFIERS {ON|OFF} |

RECURSIVE _TRIGGERS {ON|OFF} |

RECOVERY {FULL|BULK_LOGGED|SIMPLE}





Y otras opciones más adicionales, cada una de las cuales se establece

con carácter permanente.

También puede emplear el procedimiento almacenado sp_dboption. Ejemplo:

--MODIFICA CONFIGURACION DE UNA BD -- MUESTRA OPCIONES DISPONIBLES

exec sp_dboption 'Ventas' --USANDO PARAMETROS POR NOMBRE

exec sp_dboption @dbname='Ventas',@optname='ANSI Nulls',

@optvalue='TRUE' --USANDO PARAMETROS POR POSICION

exec sp_dboption 'Ventas','ANSI Warnings','TRUE'

También es posible que en una BD que ha sido migrada desde versiones anteriores, necesite mantener compatibilidad en la nueva plataforma

SQL Server 2008. Para ello puede emplear el procedimiento almacenado

sp_dbcmmptlevel. Por ejemplo: -- AJUSTA COMPATIBILIDAD CON DIVERSAS VERSIONES

-- sql server 2000 exec sp_dbcmptlevel 'Ventas', 80

-- sql server 2005 exec sp_dbcmptlevel 'Adventure Works',90

-- sql server 2008 exec sp_dbcmptlevel 'NeoBARNETT',100





Capítulo II - COMANDOS TRANSACT SQL

COMANDOS DDL

Sirven para manipular los objetos de la base de datos

Create Database

Esta instrucción permite crear una nueva base de datos

Formato:

Create database <nombre base_de_datos>

Ejemplo:

USE master

GO

CREATE DATABASE Sales ON

( NAME = Sales_dat, FILENAME = 'c:\mssql7\data\saledat.mdf',

SIZE = 10, MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = 'Sales_log', FILENAME = 'c:\mssql7\data\salelog.ldf',

SIZE = 5MB, MAXSIZE = 25MB,

FILEGROWTH = 5MB ) GO

Alter database

Esta instrucción permite modificar una base de datos .

Formato:

Alter Database <nombre base_datos>

Drop Database

Esta instrucción permite eliminar una base de datos

Formato:

Drop Database <nombre base_datos>





Create Table

Esta instrucción permite crear una tabla

CREATE TABLE NombreBaseDatos.[propietario].nombreTabla (<Definición_Columnas>)

<Restricción_Tabla> Ejemplo:

CREATE TABLE publishers

(

pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED

CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'),

pub_name varchar(40) NULL, city varchar(20) NULL,

state char(2) NULL,

country varchar(30) NULL DEFAULT('USA')

)

Alter Table

Permite modificar una tabla.

Ejemplo:

CREATE TABLE doc_exa ( column_a INT) GO

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL GO

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)

GO

ALTER TABLE doc_exe ADD column_b INT IDENTITY

CONSTRAINT column_b_pk PRIMARY KEY, column_c INT NULL

CONSTRAINT column_c_fk REFERENCES doc_exe(column_a),

column_d VARCHAR(16) NULL CONSTRAINT column_d_chk

CHECK

(column_d IS NULL OR column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR

column_d LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

column_e DECIMAL(3,3) CONSTRAINT column_e_default

DEFAULT .081

GO





Drop Table

Permite eliminar una tabla

Formato:

Drop Table <nombre tabla>

Ejemplo: DROP TABLE doc_exe

COMANDOS DML Se emplean para manipular los datos almacenados en la base de datos.

Insert Into

Esta instrucción permite insertar un registro o fila en una tabla o un conjunto

de tablas.

Formato:

Insert Into <Tabla> (columnas) Values (Lista de Valores)

Ejemplos:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'T1')

DROP TABLE T1 GO

CREATE TABLE T1

( column_1 int identity, column_2 varchar(30)

CONSTRAINT default_name DEFAULT ('column default'), column_3 int NULL,

column_4 varchar(40)

) GO

INSERT INTO T1 (column_4) VALUES ('Explicit value')

GO INSERT INTO T1 (column_2,column_4)

VALUES ('Explicit value', 'Explicit value')

GO INSERT INTO T1 (column_2,column_3,column_4)

VALUES ('Explicit value',-44,'Explicit value') GO

SELECT * FROM T1





Update

Esta instrucción permite actualizar y modificar uno o un conjunto de

registros. Formato:

Update <tabla> Set (lista de campos) Where [condición)

Ejemplos:

CREATE TABLE s (ColA INT, ColB DECIMAL(10,3)) GO

CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3)) GO

INSERT INTO s VALUES(1, 10.0) INSERT INTO s VALUES(1, 20.0)

INSERT INTO t VALUES(1, 0.0)

GO UPDATE t

SET t.ColB = t.ColB + s.ColB FROM t INNER JOIN s ON (t.ColA = s.ColA)

GO

--MODIFICA EL IDENTIFICADOR DE VENDEDOR PARA TODOS LOS --CLIENTES QUE TENGAN EL VENDEDOR PEREZ.

UPDATE Clientes SET Vendedor="GUTI"

WHERE Vendedor="PEREZ"

--AUMENTA EL PRECIO AL POR MENOR DE TODOS LOS ARTICULOS EN 5%

UPDATE Articulos SET Minorista=Minorista*1.05

Delete

Esta instrucción permite eliminar uno o un conjunto de registros contenidos

en una tabla.

Formato:

Delete from <tabla> where [condición]

Ejemplos:

--ELIMINA AL AUTOR DE APELLIDO McBadden

USE pubs DELETE FROM authors

WHERE au_lname = 'McBadden'

--ELIMINA TODOS LOS CLIENTES QUE VIVAN EN SEVILLA

DELETE FROM Clientes WHERE Provincia = "Se"





COMANDO SELECT Para implementar las consultas se emplea la Ventana de Consulta (New

Query) En ella se puede digitar el código T-SQL

deseado, generando un texto “esquematizado”, al estilo de la

plataforma de desarrollo NET.

SELECT Es el comando que permite seleccionar datos de las tablas y/o consultas de la base de datos. Para ello dispone de una extensa sintaxis, de la

cual se muestra algunas de sus clausulas principales:

PARA UNA SOLA TABLA / VISTA SELECT <Lista de Columnas a Mostrar>

FROM <Nombre Tabla /Vista> WHERE <Expresión Lógica Filtro de Filas>

ORDER BY <Expresión Para Ordenar Filas> GROUP BY <Expresión de Agrupamiento>

HAVING <Expresión Lógica Filtro de Grupos>

PARA DOS TABLAS / VISTAS

SELECT <Lista de Alias.Columnas a Mostrar> FROM <Tabla1 [Alias1] {INNER|LEFT|RIGHT|FULL} JOIN Tabla2

[Alias2] ON <Expresión de Enlace de Tablas>

WHERE <Expresión Lógica Filtro de Filas> ORDER BY <Expresión Para Ordenar Filas>

GROUP BY <Expresión de Agrupamiento>

HAVING <Expresión Lógica Filtro de Grupos>

CLAUSULA LIKE.- Para encontrar coincidencias entre cadenas de texto. CLAUSULA DISTINCT.- Para omitir registros que contienen valores duplicados

en las columnas seleccionadas. CLAUSULA IN.- Permite seleccionar ítems dentro de un rango.

SELECT DE FECHAS.- Se emplea la comilla como delimitador de fechas. Además se dispone de cinco funciones para operaciones matemáticas y

estadísticas en la BD:

1. SUM, sumar 2. COUNT, contar 3. AVG, promedio

4. MAX, valor mayor 5. MIN, valor menor





Formato:

SELECT [ ALL | DISTINCT ] [ TOP n [PERCENT] [ WITH TIES] ]

<listaSelección> <listaSelección> ::=

* | { nombreTabla | nombreVista | aliasTabla }.*

| { nombreColumna | expresión | IDENTITYCOL |

ROWGUIDCOL }

[ [AS] aliasColumna ]

| aliasColumna = expresión } [,...n]

Ejemplos:

--SELECCIONAR TODOS LOS REGISTROS DE UNA TABLA CON TODOS SUS --CAMPOS

SELECT *

FROM Ventas

--SELECCIONAR TODOS LOS REGISTROS, PERO SOLO UNOS CAMPOS

SELECT Articulo, Descripcion, [Precio Venta]

FROM Ventas

--CREA UNA CONSULTA CON EL APELLIDO Y EL NOMBRE DE LOS --CLIENTES, SEPARADOS POR UNA COMA, EN UN SOLO CAMPO

SELECT Apellido + ', ' + Nombre

FROM Clientes

--SELECCIONA LOS CLIENTES ORDENANDOLOS POR APELLIDO (DE

FORMA 'DESCENDENTE) Y POR EL NOMBRE (DE FORMA ASCENDENTE)

SELECT * FROM Clientes

ORDER BY Apellido DESC, Nombre

--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE SE

APELLIDEN 'Perez


WHERE Apellido = ‘Perez’

--SELECCIONA TODOS LOS CAMPOS DE LOS ARTÍCULOS CON FECHA --MAYOR QUE 15/12/09

SELECT * FROM Articulos

WHERE Fecha > '15/12/09'





--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE SEAN --

GALLEGOS

SELECT *

FROM Clientes WHERE Provincia IN ('C', 'Lu', 'Or', 'Po')

--SELECCIONA LOS CLIENTES CON FECHA DE ENERO DE 2009


WHERE Fecha BETWEEN '1/01/09' AND '31/01/09'

--SELECCIONA LOS CLIENTES CON APELLIDOS QUE NO ESTÉN ENTRE LA -- M Y LA P

SELECT *

FROM Clientes

WHERE Apellido NOT BETWEEN 'M' AND 'P'

--SELECCIONA LOS CLIENTES QUE SE APELLIDEN MARTINEZ DE LAS PROVINCIAS DE PONTEVEDRA Y ORENSE

SELECT *

FROM Clientes

WHERE Apellido = 'Martinez' AND Provincia IN ('Po', 'OR')

FUNCIONES DE AGREGACIÓN Y ESTÁNDAR

--CREA UN RECORDSET CON UN ÚNICO REGISTRO CON EL STOCK --MÍNIMO, 'EL MÁXIMO Y EL MEDIO DE LOS ARTÍCULOS ASÍ COMO EL

--COSTE TOTAL 'DE LAS EXISTENCIAS

SELECT Min(Stock), Max(Stock), Avg(Stock), Sum(Stock * Precio) FROM Articulos

--CREA UNA CONSULTA COMPUESTA DE DOS CAMPOS, UNA CANTIDAD Y --LA RAIZ CUADRADA DE ESA CANTIDAD. A ESTOS CAMPOS LES ASIGNA

--UN 'NOMBRE ALTERNATIVO

SELECT Cantidad AS Cant1, SQRT(Cantidad) AS Cant2 FROM Ventas

--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE TENGAN UN

--APELLIDO QUE NO EMPIECE POR UNA VOCAL Y QUE LA TERCERA LETRA

--SEA UNA n


WHERE LEFT(Apellido,1) = '[!aeiou]?n*'





--CREA UN IDENTIFICADOR DE CLIENTE UTILIZANDO LAS 3 PRIMERAS

--LETRAS DE LOS CAMPOS APELLIDOS Y NOMBRE Y PONE TODO EN

--MAYUSCULAS

SELECT UPPER(LEFT(Apellidos, 1, 3)) + UPPER(LEFT(Nombre, 1, 3)) FROM Clientes

--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE TENGAN

--COMO 'SEGUNDA LETRA DEL APELLIDO, UNA LETRA A, B O C

SELECT *

FROM Clientes WHERE SUBSTRING(Apellido, 2, 1) LIKE '[a-c]'

CONSULTAS CON GRUPOS Y FILTROS HAVING --OBTIENE EL TOTAL DE VENTAS DE LAS PROVINCIAS

SELECT Provincia, SUM(VentasTotales)

FROM Clientes

GROUP BY Provincia

--OBTIENE EL TOTAL DE VENTAS DE LAS PROVINCIAS QUE TENGAN MAS --DE 10 CLIENTES Y LA VENTA MAXIMA A UN SOLO CLIENTE NO SEA

--SUPERIOR A 1000

SELECT Provincia, SUM(VentasTotales)

FROM Clientes GROUP BY Provincia

HAVING COUNT(*) > 10 AND MAX(VentasTotales) < 1000

COMBINACION DE TABLAS – CONSULTAS MULTITABLA

--SELECCIONAR TODOS LOS CAMPOS DE LA TABLA VENTAS Y LOS -- CAMPOS ‘DESCRIPCION Y MINORISTA DE LA TABLA DE ARTICULOS,

--RELACIONANDO AMBAS TABLAS POR EL CODIGO DEL ARTICULO.

SELECT Ventas.*, Articulos.Descripcion, Articulos.Minorista

FROM Ventas, Articulos WHERE Ventas.Articulo = Articulos.Articulo

--CALCULAR EL PRECIO TOTAL DE LOS ARTICULOS. ESTA CONSULTA

-- CREA UN CAMPO CON EL RESULTADO DE LA MULTIPLICACION

SELECT Articulos.Minorista * Ventas.Cantidad FROM Articulos, Ventas

WHERE Ventas.Articulo = Articulos.Articulo





--OBTIENE UNA NUEVA TABLA, CORREOS, CON LAS DIRECCIONES DE LOS

--CLIENTES

SELECT CS.Apellido + ', ' + CS.Nombre AS Nombre,

CS.Direccion, CP.Ciudad,

CP.Provincia, CS.[Codigo Postal]

INTO Correos

FROM Clientes CS, [Codigo Postal] CP WHERE CS.[Codigo Postal] = CP.Codigo

--NO SE SELECCIONAN LOS REGISTROS QUE TENGAN IGUALES TODOS

-- LOS CAMPOS EN AMBAS TABLAS

SELECT DISTINCTROW Articulo FROM Articulos, Ventas

WHERE Articulos.Articulo = Ventas.Articulo

--SE GENERA UN RECORDSET CON TODOS LOS REGISTROS DE LA TABLA

--CLIENTES, EXISTA O NO EL VENDEDOR ASOCIADO, EN LA QUE NO --SALDRÁN REFLEJADOS LOS VENDEDORES QUE NO TIENEN NINGÚN

--CLIENTE ASIGNADO

SELECT CS.Apellido, CS.Nombre, VN.Apellido, VN.Nombre

FROM Clientes CS LEFT JOIN Vendedores VN ON CS.Vendedor = VN.Vendedor

EJEMPLOS DE CONSULTAS EN LA BD VENTAS

--MUESTRA NRO DE CLIENTE, EMPRESA Y CIUDAD DE TODOS LOS -- CLIENTES

SELECT Num_Clie, Empresa, Ciudad

FROM CLIENTES

--MUESTRA TODOS LOS DATOS DE LOS 10 PRIMEROS CLIENTES

SELECT TOP 10 *

FROM CLIENTES

--MUESTRA LOS 10 PRIMEROS CLIENTES, ORDENANDO POR LIMITE -- DE CRÉDITO

SELECT TOP 10 *

FROM CLIENTES

ORDER BY Limite_Credito

--MUESTRA TODOS LOS DATOS DE LOS CLIENTES CUYO LIMITE -- DE CRÉDITO ES MAYOR A 50000

SELECT *

FROM CLIENTES WHERE Limite_Credito > 50000





--MUESTRA CÓDIGO, PRECIO E IMPUESTO IGV DE TODOS

-- LOS PRODUCTOS

SELECT Id_Fab, Id_Producto, Precio, Precio * 19/100 AS ‘IGV’

FROM Productos

--MUESTRA TODOS LOS DATOS DE CLIENTES CUYA EMPRESA --INICIA CON LETRA C

SELECT * FROM clientes

WHERE empresa LIKE 'C%'

--MUESTRA LOS CLIENTES CUYA EMPRESA TIENE C COMO -- SEGUNDA LETRA

SELECT *

FROM clientes

WHERE empresa LIKE '_c%'

--MUESTRA PRODUCTOS CUYA DESCRIPCIÓN TIENE --LETRA C EN CUALQUIER POSICIÓN

SELECT *

FROM Productos

WHERE Descripcion LIKE '%c%'

--MUESTRA PRODUCTOS CUYO CÓDIGO DE FABRICA ES 'REI' Ó 'FEA'

SELECT * FROM Productos

WHERE Id_Fab IN ('FEA','REI')

--MUESTRA EL TOTAL PEDIDO EL 12 DE OCTUBRE DEL 2006

SELECT SUM(Importe) AS 'Total Importe'

FROM Pedidos WHERE (Fecha_pedido = '10-12-2006')

--MUESTRA CANTIDAD DE PEDIDOS AGOTADOS

SELECT COUNT(Id_Fab) AS 'Nro. Agotados'

FROM Productos

WHERE Existencias=0;

--MUESTRA EL PRECIO PROMEDIO DE LOS PRODUCTOS --CON EXISTENCIAS MAYORES AL MINIMO

SELECT AVG(Precio)

FROM Productos

WHERE Existencias > Cant_Min





--MUESTRA EL VALOR DE INVENTARIO DE TODOS LOS PRODUCTOS

SELECT SUM(Existencias*Precio) AS 'Inventario' FROM Productos;

--MUESTRA EL VALOR DE INVENTARIO DE TODOS LOS

--PRODUCTOS AGRUPADOS POR CODIGO DE FABRICA

SELECT Id_Fab, SUM(Existencias*Precio) AS 'Inventario Parcial'

FROM Productos GROUP BY Id_Fab;

--MUESTRA EL VALOR DE INVENTARIO DE TODOS LOS

--PRODUCTOS AGRUPADOS POR CODIGO DE FABRICA CUYO --VALOR SEA MAYOR A 100000

SELECT Id_Fab, SUM(Existencias*Precio) AS 'Inventario Parcial'

FROM Productos

GROUP BY Id_Fab HAVING SUM(Existencias*Precio) > 100000;

--MUESTRA LOS TITULOS QUE PUEDEN TENER LOS EMPLEADOS

SELECT DISTINCT titulo

FROM repventas

GROUP BY titulo;

--CUENTA LOS TITULOS QUE PUEDEN TENER LOS EMPLEADOS

SELECT COUNT (DISTINCT Titulo) FROM RepVentas

--MUESTRA PEDIDOS REALIZADOS ENTRE 15 DE ENERO Y

--15 DE FEBRERO 2007

SELECT Num_pedido, convert(varchar,Fecha_pedido,103) AS 'Fecha'

FROM Pedidos WHERE Fecha_pedido BETWEEN '01-15-2007' AND '02-15-2007'

SUBCONSULTAS

Una subconsulta viene a ser una consulta embebida dentro de otra, a

la cual se denomina consulta principal ó consulta primaria. Normalmente se encuentran en las cláusulas de filtro (WHERE ó HAVING).

Se debe insertar entre paréntesis y debe cumplir algunas reglas: 1. No debe retornar más de una columna, pero sí varias filas

2. No debe emplear cláusula UNION

3. Puede emplear columnas de la tabla de la consulta principal.

Muchas veces una subconsulta puede ser reemplazada por consultas multitabla.

Ejemplos (EMPLEANDO LA BD VENTAS DE CLASE)





--MUESTRA EL NOMBRE DE LOS EMPLEADOS CUYA CUOTA

--ES MAYOR O IGUAL AL MONTO DEL OBJETIVO DE LA

--CIUDAD DE ATLANTA

SELECT nombre FROM repventas

WHERE cuota>=(SELECT objetivo FROM oficinas

WHERE ciudad='Atlanta')

--MUESTRA LOS CLIENTES QUE SON ATENDIDOS POR

--EL EMPLEADO 'Bill Adams'

SELECT empresa FROM clientes

WHERE rep_clie=(SELECT num_empl FROM repventas

WHERE nombre='Bill Adams')

--MUESTRA LOS EMPLEADOS DE LAS OFICINAS QUE HAN

--CUMPLIDO SU OBJETIVO

SELECT nombre FROM repventas

WHERE oficina_rep in (SELECT oficina

FROM oficinas WHERE ventas>objetivo)

--MUESTRA LOS CLIENTES QUE HAN PEDIDO UN DETREMINADO

--PRODUCTO DENTRO DE UN CIERTO PERIODO

SELECT empresa FROM clientes

WHERE num_clie in (SELECT distinct clie

FROM pedidos WHERE fab='ACI'

and producto like '4100%' and fecha_pedido between '01-oct-2006' and '31-oct-2006')

--MUESTRA LOS PRODUCTOS QUE SE ENCUENTRAN EN

--PEDIDO DE AL MENOS 25000

SELECT distinct descripcion

FROM productos WHERE exists(SELECT num_pedido

FROM pedidos WHERE producto=id_producto

and fab=id_fab and importe>=25000)





--MUESTRA EMPLEADOS CON PEDIDO REGISTRADO DE AL MENOS

--10% DE SU CUOTA

SELECT nombre

FROM repventas WHERE (0.10*cuota)< any (SELECT importe

FROM pedidos WHERE rep=num_empl)

--EJEMPLO EN LA BD NORTHWIND DE SQL SERVER --MUESTRA PRODUCTOS CUYO PRECIO DE VENTA EN UN

--PEDIDO ES MENOR AL 50% DE SU PRECIO EN INVENTARIO

SELECT productid, productname FROM products p

WHERE unitprice < any (SELECT od.unitprice FROM [order details] od

WHERE od.productid=0.5*p.productid)

--MUESTRA LAS OFICINAS DONDE TODOS LOS EMPLEADOS

--HAN VENDIDO POR MONTO MAYOR AL 50% DEL OBJETIVO

SELECT ciudad, objetivo FROM oficinas

WHERE (0.50*objetivo)< all (SELECT ventas

FROM repventas WHERE oficina_rep=oficina)

EJERCICIOS CON LA BD VENTAS 1.- Muestre los pedidos y sus respectivos clientes

2.- Obtenga

a) Pedidos sin clientes

b) Clientes sin pedidos

3.- Muestre

a) Límites de importes de pedidos

b) Límites de importes de pedidos 2007

c) Cantidad pedidos con importe menor al importe

promedio de pedidos

d) Clientes cuya empresa inicia con a

e) Muestre el impuesto igv promedio de pedidos de clientes

4.- Pedidos que corresponden al día y mes actuales

5.- Crear tablas y llenar con pedidos de importe > importe promedio y con

pedidos de importe < al importe promedio

6.-Mostrar el importe incrementado en 10% para los pedidos correspondientes

al primer semestre y en 5% para los pedidos del segundo semestre del año

2007

7.- Muestra el total de importe de pedidos por cada año

8.- Reasignar los pedidos del cliente ‘Zetacorp’ al cliente ‘Orion corp.’

9.- Eliminar al cliente ‘Zetacorp’





Capítulo III - MANEJO DE VISTAS DE DATOS

Creación de Vistas

Una vista es una estructura lógica la cual muestra información de

varias tablas relacionadas, a este objeto también se le denomina “Tabla Virtual”.

CREATE VIEW

Esta instrucción permite crear una vista estructura la cual contendrá

registros de varias tablas relacionadas, la data se obtiene con una instrucción de selección.

Create View <nombre> as instrucción de selección

Ejemplo:

USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME = 'accounts') DROP VIEW accounts

GO

CREATE VIEW accounts (title, advance, amt_due)

WITH ENCRYPTION AS

SELECT title, advance, price * royalty * ytd_sales FROM titles

WHERE price > 5 GO

ALTER VIEW

Esta instrucción permite modificar la estructura de una vista ya creada

Formato:

Alter View <nombre_vista> as instrucción de selección

DROP VIEW

Esta instrucción permite eliminar una vista ya creada

Formato:

Drop View <nombre_vista>





Ejemplos: (EMPLEAR LA BD NEPTUNO)

--VISTA CON SUBCONSULTA

--VISTA DE LOS CLIENTES CON PEDIDOS REALIZADOS EN 1996

CREATE VIEW ClientesConPedidos1996 AS

SELECT clientes.NombreCompañía, Clientes.Teléfono

FROM Clientes WHERE exists(SELECT * from pedidos

WHERE pedidos.Idcliente=clientes.Idcliente AND Year(FechaPedido)=1996);

--PRUEBA LA VISTA

SELECT * FROM ClientesConPedidos1996;

--OTRA PRUEBA

SELECT NombreCompañía FROM ClientesConPedidos1996:

--VISTA DE TABLAS COMBINADAS

-- VISTA Pedidos1996 DE LOS PEDIDOS DEL AÑO 1996 UTILIZANDO LAS -- TABLAS PEDIDOS, CLIENTES y EMPLEADOS

CREATE VIEW Pedidos1996 AS

SELECT Idpedido, FechaPedido, pedidos.Cargo, Nombrecompañía AS ‘clientes’,

Nombre+Apellidos AS ‘empleado’, FechaEnvío,

FechaEntrega FROM pedidos

INNER JOIN clientes ON pedidos.Idcliente=clientes.Idcliente

INNER JOIN empleados ON pedidos.Idempleado=empleados.Idempleado WHERE year(FechaPedido)=1996;

--PRUEBA LA VISTA

SELECT * FROM pedidos1996;

--OTRA PRUEBA --VISUALIZA LOS CAMPOS IDPEDIDO, CARGO, CLIENTES

--DE LA VISTA PEDIDOS1996

SELECT Idpedido, Cargo, Clientes FROM pedidos1996;





-- CREAR UNA VISTA CON EL NOMBRE ProdCatProv

--QUE PERMITA VISUALIZAR LOS CAMPOS O COLUMNAS

--NombreProducto y PrecioUnidad DE LA TABLA PRODUCTOS --NombreCategoria DE LA TABLA CATEGORIAS

--NombreCompañia DE LA TABLA PROVEEDORES

CREATE VIEW ProdCatProv AS

SELECT NombreProducto,

PrecioUnidad, categorías.NombreCategoría,

proveedores.NombreCompañía FROM productos

INNER JOIN categorías ON productos.idcategoría = categorías.idcategoría INNER JOIN proveedores ON productos.idproveedor =

proveedores.idproveedor;

--PRUEBA LA VISTA SELECT * from ProdCatProv;

--CREAR UNA VISTA EmpleadoPedido QUE MUESTRE

--DATOS DEL PEDIDO Y EL EMPLEADO CORRESPONDIENTE

CREATE VIEW EmpleadoPedido

AS SELECT pedidos.idpedido, apellidos+nombre AS Emple, empleados.cargo

FROM empleados INNER JOIN pedidos on empleados.idempleado=pedidos.idempleado;

--PRUEBA LA VISTA

SELECT * from empleadopedido;

--EJEMPLO DE UNA VISTA QUE UNE TRES TABLAS

CREATE VIEW CurPorCic( Id, Ciclo, Curso, Profesor, Horario, V_M, Estado

) AS

SELECT CP.IdCursoProg,

CP.IdCiclo, C.NomCurso,

P.ApeProfesor + Space(1) + P.NomProfesor, CP.Horario, Convert(VarChar(3),CP.Vacantes) + '/' + Convert(VarChar(3), CP.Matriculados),

Case CP.Activo

when 0 then 'Cancelado'

when 1 then 'Activo' End

FROM Profesor P INNER JOIN CursoProgramado CP ON P.IdProfesor = CP.IdProfesor INNER JOIN Curso C

ON CP.IdCurso = C.IdCurso;

--PRUEBA LA VISTA

SELECT * FROM CurPorCic;





Capítulo IV - OBJETOS DE PROGRAMACIÓN

PROCEDIMIENTOS ALMACENADOS

Un procedimiento almacenado es un conjunto de instrucciones transact sql las cuales se ejecutan siguiendo una secuencia lógica, los

procedimientos almacenados pueden recibir y devolver parámetros.

Formato:

CREATE PROC[EDURE] nombreProcedimiento

@parámetro tipoDatos} [VARYING] [= predeterminado] [OUTPUT] AS

instrucciónSQL [...n]

Ejemplos: (DEBE DISPONER DE LA BD VENTAS IMPLEMENTADA EN CLASE)

--PROCEDIMIENTO ALMACENADO SIN PARAMETROS

--pa_infopedidos QUE MUESTRA NUMERO DE PEDIDO, NOMBRE DEL --CLIENTE Y NOMBRE DEL EMPLEADO, DE TODOS LOS PEDIDOS

CREATE PROCEDURE pa_infopedidos

AS SELECT num_pedido, empresa, Nombre

FROM (clientes INNER JOIN pedidos ON clientes.num_clie = pedidos.clie) INNER JOIN repventas

ON pedidos.rep = repventas.num_empl

GO EXECUTE pa_infopedidos;

--PROCEDIMIENTO ALMACENADO CON PARAMETRO DE ENTRADA

--pa_infocliente QUE ACEPTA UN NUMERO DE CLIENTE --Y MUESTRA SU NOMBRE Y LIMITE DE CREDITO

CREATE PROCEDURE pa_infocliente @num_clie int AS

SELECT empresa, limite_credito

FROM clientes WHERE num_clie=@num_clie;

EXECUTE pa_infocliente 2123;

--PROCEDIMIENTO ALMACENADO CON PARAMETRO DEFAULT --pa_infoejecutivos QUE ACEPTA UN TITULO PARA

--MOSTRAR LOS DATOS DE LOS EJECUTIVOS CORRESPONDIENTES

-- O SINO MUESTRA DATOS DE LOS DIRECTORES

CREATE PROCEDURE pa_infoejecutivos @titulo varchar(10) = 'Dir%' AS

SELECT num_empl, nombre, Titulo FROM repventas

WHERE Titulo like @titulo; EXECUTE pa_infoejecutivos;





EXECUTE pa_infoejecutivos 'Rep Ventas';

--PROCEDIMIENTO ALMACENADO CON DOS PARAMETROS DE ENTRADA --EMPLEANDO LA BD PUBS DE SQL SERVER

--pa_infoautor QUE ACEPTA EL NOMBRE Y APELLIDOS DE UN AUTOR

--PARA MOSTRAR EL TITULO Y EDITOR DE CADA UNO DE LOS --LIBROS QUE EL AUTOR HA PUBLICADO

CREATE PROCEDURE pa_infoautor @ape varchar(40), @nom varchar(20) AS

SELECT au_lname, au_fname, title, pub_name FROM authors, titles, publishers, titleauthor

WHERE au_fname=@nom AND au_lname=@ape

AND authors.au_id=titleauthor.au_id AND titles.title_id=titleauthor.title_id

AND titles.pub_id=publishers.pub_id;

--PRUEBA EL PA EXECUTE pa_infoautor 'Karsen', 'Livia';

--PROCEDIMIENTO ALMACENADO CON PARAMETRO DEFAULT

--pa_infoautor2 QUE ACEPTA EL NOMBRE DE UN EDITOR --PARA MOSTRAR EL NOMBRE DE LOS AUTORES QUE HAN

--PUBLICADO UN LIBRO CON ESE EDITOR

--O SINO MUESTRA LOS AUTORES DEL EDITOR 'Algodata Infosystems'

CREATE PROCEDURE pa_infoautor2 @editor varchar(40)='Algodata Infosystems'

AS SELECT au_lname, au_fname, pub_name

FROM authors a, titles t, publishers p, titleauthor ta WHERE p.pub_name=@editor

AND a.au_id=ta.au_id

AND t.title_id=ta.title_id AND t.pub_id=p.pub_id

GO EXECUTE pa_infoautor2 'Binnet & Hardley';

--PROCEDIMIENTO ALMACENADO QUE MUESTRA INFORMACION

--SOBRE LOS LIBROS DE UN DETERMINADO AUTOR IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info' AND type = 'P')

DROP PROCEDURE au_info GO

CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20)

AS SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id WHERE au_fname = @firstname





AND au_lname = @lastname;

--EL PROCEDIMIENTO ALMACENADO au_info SE PUEDE EJECUTAR DE --ESTAS FORMAS:

EXECUTE au_info 'Dull', 'Ann' -- O

EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- O

EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'

-- O EXEC au_info 'Dull', 'Ann'

-- O EXEC au_info @lastname = 'Dull', @firstname = 'Ann'

-- O EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

--O SI ESTE PROCEDIMIENTO ES LA PRIMERA INSTRUCCIÓN DEL -- PROCESO POR LOTES:

au_info 'Dull', 'Ann'

-- O au_info @lastname = 'Dull', @firstname = 'Ann'

-- O au_info @firstname = 'Ann', @lastname = 'Dull'

--PROCEDIMIENTO ALMACENADO QUE MUESTRA INFORMACION

--SOBRE LOS LIBROS Y EDITOR DE UN DETERMINADO AUTOR

--CUYO APELLIDO INICIA CON LETRA D IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2

GO CREATE PROCEDURE au_info2

@lastname varchar(30) = 'D%', @firstname varchar(18) = '%'

AS

SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname

AND au_lname LIKE @lastname GO

--EL PROCEDIMIENTO ALMACENADO au_info2 SE PUEDE EJECUTAR --EN MUCHAS COMBINACIONES. AQUÍ SE MUESTRAN SÓLO ALGUNAS

--COMBINACIONES:

EXECUTE au_info2 -- O

EXECUTE au_info2 'Wh%'

-- O EXECUTE au_info2 @firstname = 'A%'

-- O EXECUTE au_info2 '[CK]ars[OE]n'





-- O

EXECUTE au_info2 'Hunter', 'Sheryl'

-- O EXECUTE au_info2 'H%', 'S%'

--OTROS EJEMPLOS (EMPLEA LA BD MATRICULA):

1. if exists(SELECT name FROM sysobjects

WHERE name='sp_ins_alu' and type ='p')

begin DROP PROCEDURE sp_ins_alu

end CREATE PROCEDURE sp_ins_alu @id char(8), @ape char(30),@nom char(30)

AS INSERT INTO alumno(idalumno,apealumno,nomalumno)

VALUES (@id,@ape,@nom)


WHERE name='sp_list_alu' and type ='p') begin

DROP PROCEDURE sp_list_alu end

CREATE PROCEDURE sp_list_alu AS

SELECT apealumno, fecmatricula

FROM alumno a inner join matricula m ON a.idalumno=m.idalumno

3. CREATE PROCEDURE sp_Busca_alu @id Char(8)

AS SELECT idalumno, apealumno, nomalumno

FROM alumno WHERE (Idalumno = @id)

4. if exists(SELECT name FROM sysobjects WHERE name='sp_act_alu' and type ='p')

begin DROP PROCEDURE sp_act_alu

end CREATE PROCEDURE sp_act_alu @id char(8), @ape char(30),@nom char(30)

AS UPDATE alumno SET idalumno = @id ,

apealumno = @ape ,

nomalumno = @nom WHERE idalumno = @id


WHERE name='sp_eli_alu' and type ='p') begin

DROP PROCEDURE sp_eli_alu

end CREATE PROCEDURE sp_eli_alu @id char(8)

AS DELETE FROM alumnos





WHERE idalumno=@id

FUNCIONES DEFINIDAS POR EL USUARIO (FDU)

Es un conjunto de instrucciones parecido al procedimiento

almacenado, con la diferencia que puede retornar un valor calculado como resultado de su ejecución.

También puede recibir parámetros de trabajo para definir su proceso de cálculo. El valor final que se retorna, debe ser indicado mediante una

instrucción RETURN necesariamente.

CREATE FUNC[TION] NombreFuncionUsuario (@parámetro TipoDato) RETURNS <TipoDatoRetorno>

AS

BEGIN …

Instrucciones SQL ...

RETURN <ExpRetorno> END

Ejemplos (EN LA BD VENTAS DE CLASE)

--FDU QUE RETORNA VALOR ESCALAR --CREA UNA FDU FunEmail() QUE GENERA UNA CUENTA DE CORREO

--ELECTRONICO PARA UN REP DE VENTAS EN hotmail.com

CREATE FUNCTION FunEmail (@Num_Empl int) RETURNS varchar(60)

AS

BEGIN DECLARE @cuenta varchar(60)

SELECT @cuenta = Nombre + ' @hotmail.com' FROM REPVENTAS

WHERE Num_Empl=@Num_Empl RETURN @cuenta

END

GO

--PRUEBA FDU FunEmail() SELECT dbo.FunEmail(102) as 'Cuenta Email'

GO

--OTRA PRUEBA DECLARE @C varchar(60)

SET @C = dbo.FunEmail(102)

SELECT @C AS 'Cuenta Email' GO

--ELIMINA FDU Funemail

DROP FUNCTION FunEmail GO





--EJEMPLO DE FDU QUE RETORNA VALORES TABLE

--CREA FDU FunRepNY QUE LISTA LOS REP VENTAS DE --UNA DETERMINADA OFICINA

CREATE FUNCTION FunRepNY (@ofi int)

RETURNS table AS

RETURN (SELECT Num_Empl, Nombre FROM REPVENTAS

where Oficina_Rep=@Ofi) GO

--PRUEBA FDU FunRepNY()

SELECT * FROM dbo.FunRepNY(11)

GO --OTRA PRUEBA

SELECT *

FROM dbo.FunRepNY(11) WHERE Nombre like 'S%'

GO

--EJEMPLO DE FDU QUE SE EMPLEA PARA CREAR --COLUMNA CALCULADA EN UNA TABLA

CREATE FUNCTION FunJornal(@HL int, @TH MONEY) RETURNS MONEY

AS BEGIN

RETURN (@HL * @TH) END

GO

--PRUEBA FDU FunJornal()

--CREA TABLA PLANILLA CON COLUMNA CALCULADA CREATE TABLE PLANILLA

(cod int,hor_lab int, tar_hor money, Jornal as dbo.FunJornal(hor_lab,tar_hor)) GO

--AGREGA FILA EN PLANILLA

INSERT INTO Planilla VALUES (1,12,15.00)

GO

--MUESTRA DATOS DE FILA SELECT *

FROM PLANILLA GO





DESENCADENADORES (TRIGGERS) EN SQL SERVER

Un desencadenador es un conjunto de instrucciones que se ejecuta automáticamente al momento de realizar una inserción, eliminación o

actualización en una tabla. A diferencia de los procedimientos

almacenados y funciones de usuario, no admiten parámetros de trabajo, ni retornan resultado alguno

Sentencias:

Create Trigger.- Crea un desencadenador

Alter Trigger.- Modifica un desencadenador

Drop Trigger.- Elimina un desencadenador

Ejemplos (EMPLEANDO LA BD VENTAS IMPLEMENTADA EN CLASE)

--TRIGGER DE ACTUALIZACIÓN

--CUANDO SE AGREGA UN NUEVO PEDIDO A LA TABLA PEDIDOS --ESTOS DOS CAMBIOS DEBEN TENER LUGAR:

-- 1.- LA COLUMNA VENTAS DEL VENDEDOR QUE ATENDIO

-- EL PEDIDO DEBE AUMENTAR EN EL IMPORTE DEL PEDIDO -- 2.- EL VALOR DE EXISTENCIAS PARA EL PRODUCTO ORDENADO

-- DEBERIA DISMINUIR EN LA CANTIDAD SOLICITADA

CREATE TRIGGER TR_NUEVOPEDIDO ON PEDIDOS

AFTER INSERT AS

UPDATE REPVENTAS SET VENTAS=VENTAS + INSERTED.IMPORTE

FROM REPVENTAS, INSERTED WHERE REPVENTAS.NUM_EMPL=INSERTED.REP UPDATE PRODUCTOS SET EXISTENCIAS = EXISTENCIAS - INSERTED.CANT

FROM PRODUCTOS, INSERTED

WHERE PRODUCTOS.ID_FAB = INSERTED.FAB

AND PRODUCTOS.ID_PRODUCTO=INSERTED.PRODUCTO GO

--DATO ACTUAL EN REP_VENTAS

SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas

--101 Dan Roberts 45 12 Rep Ventas 1996-10-20 104 300000 305673

--DATO ACTUAL EN PRODUCTOS SELECT * FROM PRODUCTOS

-- Id_Fab Id_Producto Descripcion Precio existencias Cant_Min -- ACI 41001 Articulo tipo 1 55.00 277 0

--PEDIDO A REGISTRAR EN TABLA PEDIDO

--EMPLEADO 101, PROD 'ACI' + '41001' CANTIDAD 7 IMPORTE 385.00 INSERT INTO PEDIDOS

VALUES (113071, getdate(), 2101, 101, 'ACI', '41001', 7, 385.00)

GO --VERIFICA PEDIDO INSERTADO





SELECT * FROM PEDIDOS

GO

--SE ESPERA QUE LOS NUEVOS DATOS DE REP_VENTAS 101 SEAN --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas

-- 101 Dan Roberts 45 12 Rep Ventas 1996-10-20 104 300000 306058 SELECT * FROM REPVENTAS GO

--SE ESPERA QUE LOS NUEVOS DATOS DE PRODUCTOS SEAN

-- Id_Fab Id_Producto Descripcion Precio existencias Cant_Min -- ACI 41001 Articulo tipo 1 55.00 270 0

SELECT * FROM PRODUCTOS GO

--EJEMPLO DE INTEGRIDAD REFERENCIAL PARA LA RELACION

--ENTRE TABLAS OFICINAS / REPVENTAS, MOSTRANDO UN MENSAJE --CUANDO UNA ACTUALIZACION DE LA TABLA REPVENTAS FALLA

CREATE TRIGGER TR_ACTUALIZA_REPVENTAS ON REPVENTAS

AFTER INSERT,UPDATE AS

IF((SELECT OFICINAS.VENTAS FROM OFICINAS, INSERTED

WHERE OFICINAS.OFICINA=INSERTED.OFICINA_REP)=0) BEGIN

PRINT('Numero de oficina inválido')

ROLLBACK TRANSACTION END

--DATO ACTUAL EN OFICINAS

SELECT * FROM OFICINAS --Oficina Ciudad Region Dir Objetivo Ventas

-- 11 575000 692637

-- 12 800000 735042 -- 13 350000 367911

-- 21 725000 835915 -- 22 300000 186042

--DATO ACTUAL EN REP_VENTAS

SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas

-- 102 Sue Smith 48 21 Rep Ventas 1996-10-12 108 350000 474050

--SE MODIFICARA A LA EMPLEADA SUE SMITH ASIGNANDOLA --A LA OFICINA 15

UPDATE REPVENTAS SET Oficina_Rep= 21

WHERE Num_Empl = 102 GO

-- VERIFICA LA ACTUALIZACIÓN SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas

-- 102 Sue Smith 48 21 Rep Ventas 1996-10-12 108 350000 474050 --SE HA VERIFICADO QUE NO SE REALIZÓ LA ACTUALIZACION





--AHORA SE MODIFICARÁ A LA EMPLEADA SUE SMITH A LA OFICINA 13 UPDATE REPVENTAS

SET Oficina_Rep= 13 WHERE Num_Empl = 102

GO SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas

-- 102 Sue Smith 48 13 Rep Ventas 1996-10-12 108 350000 474050 --SI SE REALIZÓ LA ACTUALIZACIÓN

--OTROS EJEMPLOS

1. CREATE TRIGGER tr_act_detaven

ON detafact

FOR insert

AS

UPDATE detafact SET subtot=pre_venta * cant

2. CREATE TRIGGER tr_act_fact

ON detafact

FOR insert

AS

UPDATE facturas SET subtotal=(SELECT sum(subtot)

FROM detafact

WHERE facturas.idfact=detafact.idfact)

UPDATE facturas SET igv =subtotal* 0.19

UPDATE facturas SET total =subtotal + igv

3. IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE

NAME='TR_INS_DIST')

BEGIN

DROP TRIGGER TR_INS_DIST

END

CREATE TRIGGER TR_INS_DIST

ON DISTRITO

FOR INSERT

AS

IF @@ROWCOUNT=0 RETURN

PRINT 'REGISTRO INSERTADO CON EXITO'

ALTER TABLE DISTRITO

DISABLE TRIGGER TR_INS_DIST;





4. IF EXISTS(SELECT NAME

FROM MASTER.DBO.SYSOBJECTS

WHERE NAME='TR_TABLA_PROT' AND TYPE='TR')

BEGIN

DROP TRIGGER TR_TABLA_PROT

END

CREATE TRIGGER TR_TABLA_PROT

ON DISTRITO

FOR INSERT, UPDATE, DELETE

AS

RAISERROR ('Mensaje de trigger: tabla protegida', 16, 1)

ROLLBACK TRANSACTION;

5. –EMPLEAR LA BD PUBS DE SQL SERVER

--CREA TRIGGER DE EJECUCION CONDICIONAL

--SI ACTUALIZA LAS COLUMNAS stor_id ó ord_num

-- DE LA TABLA SALES, SE INTERRUMPE LA TRANSACCION

CREATE TRIGGER TR_PRUEBA

ON SALES

AFTER INSERT,UPDATE

AS

IF UPDATE(stor_id) or UPDATE(ord_num)

BEGIN

PRINT ('ACTUALIZACION NO VALIDA...DESCARTANDO...')

ROLLBACK TRANSACTION

END

GO

--DATOS ACTUALES TABLA SALES

SELECT * from sales WHERE stor_id='6380'

-- PRUEBA TRIGGER

INSERT INTO SALES(stor_id,ord_num,ord_date,qty,payterms,title_id)

VALUES('6380','eet','12/12/2007',2,'wer','BU1032')

--DATOS ACTUALES TABLA SALES

SELECT * from sales WHERE stor_id='6380'

--NO SE REALIZÓ LA ACTUALIZACIÓN

*=*=*=*=*=*

implementar una base de datos sql server 2008

Documents