Implementar Una Base de Datos SQL Server 2008

ADMINISTRACIÓN DE BASE DE DATOS - I Implementación de una Base de Datos SQL SERVER 2008

Material de Trabajo Elaborado por: Ing. Oscar Alberto Barnett Contreras

UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS Facultad de Ingeniería Industrial - CEUPS Informática

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.

Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 2 de 72


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, (2, (3, (4, ...

Oscar Barnett , 50, 14000, 123881OBCNT1) Judith Jiménez, 30, 15000, 265341JJMNG1) Blanca Yaya , 40, 15000, 157183BYGMZ2) 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).


Página 3 de 72


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 OFICINAEMPLEADO. 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: Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 4 de 72


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) Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 5 de 72


 

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.


Página 6 de 72


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.


Página 7 de 72


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 dependiente funcional del valor de la clave principal (PK) compuesta CódigoFamilia+CódigoProducto (A).


Página 8 de 72


Ejemplo: En la entidad PAQUETE_CONFECCION, el valor de Cantidad (B) es dependiente funcional del valor de la clave principal (PK) 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

ID_CLIENTE

NOM_CLIENTE

ESTADO

NUM_ITEM

DESC_ITEM

2301

FECHA

23/2/10

101

BARNETT

CA

3786

RED

CANT

3

PRECIO

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. Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 9 de 72


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

ID_CLIENTE

NOM_CLIENTE

ESTADO

NUM_ITEM

DESC_ITEM

2301

FECHA

23/2/10

101

BARNETT

CA

3786

RED

CANT

3

PRECIO

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

2301 2302 2303

23/2/10 25/2/10 27/2/10

101 107 110

NOM_CLIENTE

BARNETT JIMENEZ YAYA

ESTADO

CA WI MI

- ARTICULOS_ORDENES ID_ORDEN

NUM_ITEM

DESC_ITEM

CANT

PRECIO

2301 2301 2301 2302 2303 2303

3786 4011 9132 5794 4011 3141

RED RAQUETA PAQ-3 PAQ-6 RAQUETA FUNDA

3 6 8 4 2 2

35.00 65.00 4.75 5.00 65.00 10.00


Página 10 de 72


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 2301 2301 2302 2303 2303

3786 4011 9132 5794 4011 3141

3 6 8 4 2 2

- ARTICULOS NUM_ITEM

DESC_ITEM

PRECIO

3786 4011 9132 5794 4011 3141

RED RAQUETA PAQ-3 PAQ-6 RAQUETA FUNDA

35.00 65.00 4.75 5.00 65.00 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.


Página 11 de 72


- ORDENES ID_ORDEN

FECHA

ID_CLIENTE

2301 2302 2303

23/2/10 25/2/10 27/2/10

101 107 110

- CLIENTES ID_CLIENTE

NOM_CLIENTE

ESTADO

101 107 110

BARNETT JIMENEZ YAYA

CA WI 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


Página 12 de 72


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:


Página 13 de 72


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

Barra de Titulo Menú Principal Toolbar estándar

Ventana de Diagrama Explorador de Objetos

Panel de Registro


Panel de Avisos

Página 14 de 72


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:

Entidad

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:


Página 15 de 72


Entonces se muestra la siguiente ventana donde se puede establecer el dominio, las restricciones y el icono de cada atributo: Restricciones

Dominio Icono

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:


Página 16 de 72


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:


Página 17 de 72


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’:


Página 18 de 72


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:


Página 19 de 72


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.


Página 20 de 72




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.


Página 21 de 72


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:

Se debe hacer click sobre el boton Import para importar o seleccionar un Icono

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.


Página 22 de 72




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


Página 23 de 72


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.


Página 24 de 72


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.


Página 25 de 72


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.


Página 26 de 72


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 >


Página 27 de 72


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 componentes se conecten entre sí, luego pulsar en el botón Next >

dos

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


Página 28 de 72


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


Página 29 de 72


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.


Página 30 de 72


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:


Página 31 de 72


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.


Página 32 de 72


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


Página 33 de 72


Ahora, en el Menú Tools\Forward Engineer\Schema Generation… se muestra la siguiente ventana de diálogo:

Pulsa el botón Preview… instrucciones pertinentes:

para


completar

el

Script

con

las

Página 34 de 72


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 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 creando un

para guardar el archivo con el Script completo, 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.


Página 35 de 72


Ahora debe iniciar Microsoft SQL Server Management Studio y conectarse a su servidor instalado:

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


Página 36 de 72


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


Página 37 de 72


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


Página 38 de 72


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.


Página 39 de 72


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. Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 40 de 72


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)


Página 41 de 72


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


Página 42 de 72


Cada una de ellas representa una búsqueda de texto en SQL Server.

parte en la arquitectura para

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


Página 43 de 72


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;


Página 44 de 72


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 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} Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 45 de 72


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


Página 46 de 72


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 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 

Drop Database

Esta instrucción permite eliminar una base de datos Formato: Drop Database


Página 47 de 72




Create Table

Esta instrucción permite crear una tabla CREATE TABLE NombreBaseDatos.[propietario].nombreTabla () 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


Página 48 de 72




Drop Table

Permite eliminar una tabla Formato: Drop Table 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 (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


Página 49 de 72




Update

Esta instrucción permite actualizar y modificar uno o un conjunto de registros. Formato: Update 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 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" Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 50 de 72


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 FROM WHERE ORDER BY GROUP BY HAVING PARA DOS TABLAS / VISTAS SELECT FROM WHERE ORDER BY GROUP BY HAVING 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 4. MAX, valor mayor

2. COUNT, contar 5. MIN, valor menor


3. AVG, promedio

Página 51 de 72


Formato: SELECT [ ALL | DISTINCT ] [ TOP n [PERCENT] [ WITH TIES] ] ::= * | { 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 APELLIDEN 'Perez

LOS

CAMPOS

DE

LOS

CLIENTES

QUE

SE

SELECT * FROM Clientes 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' Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 52 de 72


--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 SELECT * FROM Clientes 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 SELECT * FROM Clientes WHERE LEFT(Apellido,1) = '[!aeiou]?n*'


Página 53 de 72


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


Página 54 de 72


--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 Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 55 de 72


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


Página 56 de 72


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


Página 57 de 72


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


Página 58 de 72


--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’


Página 59 de 72


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 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 as instrucción de selección 

DROP VIEW

Esta instrucción permite eliminar una vista ya creada Formato: Drop View


Página 60 de 72


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;


Página 61 de 72


-- 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;


Página 62 de 72


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; Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 63 de 72


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 Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 64 de 72


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 -- O EXECUTE -- O EXECUTE -- O EXECUTE

au_info2 au_info2 'Wh%' au_info2 @firstname = 'A%' au_info2 '[CK]ars[OE]n'


Página 65 de 72


-- 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) 2. if exists(SELECT name FROM sysobjects 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 5.

if exists(SELECT name FROM sysobjects 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 Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 66 de 72


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 AS BEGIN … Instrucciones SQL ... RETURN 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


Página 67 de 72


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


Página 68 de 72


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

GO

FROM PRODUCTOS, INSERTED WHERE PRODUCTOS.ID_FAB = INSERTED.FAB AND PRODUCTOS.ID_PRODUCTO=INSERTED.PRODUCTO

--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 -- ACI 41001 Articulo tipo 1

Precio existencias Cant_Min 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


Página 69 de 72


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 Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 70 de 72


--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='TR_INS_DIST')

NAME

FROM

SYSOBJECTS

WHERE

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;


Página 71 de 72


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

*=*=*=*=*=* Curso: Administración de Base de Datos - I Docente: Ing. Oscar Alberto Barnett Contreras – [email protected]

Página 72 de 72

Implementar Una Base de Datos SQL Server 2008

Recommend Documents