2 Manual SQL (1)

2 Programador

.NET 2012

AUMENTA TU PRODUCTIVIDAD

Centro de Educación Continua Universidad Continental Material para fines de Estudio del Programa: Programador .NET Elaborado por: Ing. Rolando R. Zapata Maraví

Queda prohibida la reproducción total o parcial del contenido de este documento en cualquiera de sus formas, sean electrónicas o mecánicas, sin el consentimiento previo y por escrito del autor y el Centro de Educación Continua.

Programa: Programador .NET

SESIÓN

Introducción a Base de Datos

1

AL FINALIZAR LA PRESENTE SESIÓN, EL PARTICIPANTE ESTARÁ EN LA CAPACIDAD DE:

Reconocer y describir los elementos básicos relacionados a base de datos. Determinar y analizar la importancia de las base de datos en las organizaciones.

TEMAS:

Dato e información Base de datos Servidores Sistema Gestor de Base de Datos (SGBD) Herramientas de software para base de datos

Modulo: Implementación y Administración Administraci ón de Bases de Datos

Pag. 1


Visión general A menudo establecemos contacto con diversas organizaciones de diversos rubros, por ejemplo, cuando adquirimos un medicamento en alguna farmacia podemos observar que la persona encargada de atender al público tiene contacto con una lista de medicamentos disponibles (en medios físicos como un papel o en medios digitales por medio de un ordenador) con su respectivo precio y stock, de esa manera se nos brinda información de acuerdo a las necesidades que tenemos. Otro ejemplo es la inclusión de su persona al presente curso, al momento de inscribirse la persona encargada de brindar informes le hizo conocimiento de los horarios disponibles y el costo respetivo. En cualquiera de ambos casos se necesitó de información para realizar con normalidad el proceso de negocio, esto nos indica que cualquier organización de cualquier rubro requiere información para realizar sus operaciones del día a día. a continuación se muestran algunos tipos de negocio acompañados del tipo de información que manejan. Tipo de organización

Banco Colegio Clínica Supermercado

Información necesaria

Datos del cliente, tipos de cuentas de ahorro, tipos de créditos, tipos de tarjetas de crédito, datos de los asesores de negocio, movimientos por cliente, etc. Datos del alumno, datos de los docentes, cursos por grado, secciones disponibles, alumnos destacados, etc. Datos del paciente, datos de los médicos, horarios de atención, disponibilidad de las salas de operaciones, etc. Datos de los productos, vendedores, clientes, etc. Tabla N° 1

Cada organización requiere de algún tipo de información para subsistir y realizar con éxito sus procesos, es por eso que se hace importante contar con información automatizada, actualizada, confiable, íntegra y disponible . Para gestionar dicha información podemos

hacer uso de medios físicos como cuadernos o papales, sin embargo este tipo de medios tiene la principal desventaja de que se deterioran con el tiempo y que en caso de contar con abundante información, podría tomar tiempo ubicar detalles específicos de algún elemento, por ejemplo, imaginemos que llevamos el control de alquiler autos en un cuaderno por más de 10 años, ¿Se imagina ubicar las fechas en la que el cliente Ana Huamán Soriano alquiló un auto de marca Mercedes?, definitivamente esta operación tomaría demasiado tiempo en completarse. Es por eso que lo más recomendable es mantener información en un medio digital a través del algún equipo electrónico como un computador por ejemplo, de ésta manera obtener información tardaría pocos segundos o milisegundos, y ahorraríamos tiempo y dinero.

Modulo: Implementación y Administración de Bases de Datos

Pag. 2


Actualmente con el avance de la tecnología existen diversos componentes y elementos para digitalizar información y hacerla confiable, disponible e íntegra. Dicha información se aloja en lo comúnmente se denomina base de datos. Lo que pretende este segundo paquete es enseñar al lector a diseñar e implementar una base de datos, para luego aplicar algunos principios básicos de administración de la misma.

Dato e información

El dato viene a ser un símbolo descriptivo, por ejemplo en el caso de un alumno podemos percibir los siguientes datos: Nombres, apellidos, edad, sexo, correo electrónico, apoderado, etc. Los datos por sí solos no pueden brindar demasiado conocimiento, sin embargo si tenemos un conjunto de datos y los ordenamos, organizamos y procesamos, entonces se convierte en información, por ejemplo si tenemos datos de 100 alumnos, podemos obtener información del porcentaje de varones y mujeres inscritos, o quizás la edad promedio de los alumnos o el porcentaje de alumnos que usan cuentas de correo en Gmail o Hotmail, etc. Como podemos apreciar, para obtener información necesitamos primero capturar datos, éste proceso a nivel empresarial generalmente se realiza mediante un sistema de información que captura datos de acuerdo a ciertas reglas de validación para luego almacenarlos.

Base de datos

Es un conjunto de datos que guardan relación entre sí y están enfocados hacia un mismo contexto . Dentro de las organizaciones la base de datos juega un rol protagónico ya que permite alojar información de sus procesos; debemos comprender que los usuarios informáticos no ingresan directamente contenido a la base de datos, sino que interactúan con ella a través de un sistema de información. Éste interactúa con la base de datos para procesar su contenido y mostrar información al usuario de acuerdo a sus necesidades y requerimientos. En un ámbito informático, la forma más común de almacenar el contenido de una base de datos es en formato de tablas, veamos el siguiente cuadro que muestra el nombre de algunas bases de datos y sus posibles tablas: Base de datos

Universidad Farmacia

Posibles tablas

Docente, alumno, carrera profesional, turno, semestre, horario, aula, asignatura, etc. Medicamento, categoría, vendedor, cliente, etc.


Pag. 3


Clínica

Paciente, doctor, horario de atención, turno, consultorio, historia clínica, receta médica, etc.

Asistencia del personal

Trabajador, horario, cargo, asistencia, etc. Tabla N° 2

Para poder crear una base de datos, generalmente se realizan los siguientes pasos: 

Toma de requerimientos

Es la fase inicial donde se realizan entrevistas a los usuarios potenciales o interesados, se aplican cuestionarios, se revisan documentos relacionados al negocio, etc. Todo esto con el fin de conocer cómo funciona el negocio o escenario empresarial que estamos abordando o enterarse de que se necesita almacenar en la futura base de datos. 

Implementación de base de datos o

Modelo conceptual

Es un diseño básico que contiene los conceptos relacionados a la base de datos que pensamos implementar. Por ejemplo: Si vamos a implementar una base de datos que guardará información relacionada a las ventas de una compañía de automóviles, nuestro modelo conceptual sería una lista con los posibles elementos que se quiere almacenar, como automóvil, repuesto, vendedor, cliente, etc. Ésta fase es independiente de la plataforma de hardware y software donde pensamos implementar la base de datos. o

Modelo lógico

El modelo lógico toma como referencia lo que se generó en el modelo conceptual, pero esta vez se aplica un paradigma de diseño de base de datos. Uno de los paradigmas para la creación de base de datos más utilizados y conocidos es el Modelo Relacional, a través de su diagrama Entidad/Relación permite la representación de diversos escenarios empresariales de baja, mediana o alta complejidad. En la siguiente sesión abordaremos con más detalle el modelo relacional

o

Modelo físico

El modelo físico traslada el modelo lógico de base de datos a un software especializado para gestionar la operatividad de la base de datos denominado Sistema Gestor de Base de Datos SGBD, así como Microsoft Word 2010 permite Modulo: Implementación y Administración de Bases de Datos

Pag. 4


crear, editar y procesar texto, un SGBD permite aplicar una serie de procesos (crear tablas para almacenar datos, generar copias de seguridad, crear cuentas de usuario para el ingreso a SQL Server 2012, etc.) en una base de datos. 

Administración de base de datos

Es un proceso natural que se da luego de que la base de datos esté funcionando en un servidor. Consiste en realizar las tareas suficientes para garantizar la disponibilidad, integridad, confiabilidad y rendimiento de la base de datos . En esta fase se realizan tareas como: Elaborar planes para recuperación en caso de desastres, establecer políticas de copias de seguridad, realizar mantenimiento de índices de base de datos, etc.

Los elementos mostrados anteriormente están resumidos para brindar un panorama al lector. En realidad el diseño e implementación de base de datos es un proceso más complejo, en el presente material de estudio abordaremos únicamente los elementos principales.

Servidores

A diario utilizamos ordenadores para realizar tareas comunes como redactar informes, elaborar cuadres de presupuestos, etc. Estos ordenadores generalmente tienen instalado un sistema operativo de usuario como Windows 8, Windows 7, Windows Vista, Windows XP, Ubuntu, Mandriva, etc, y poseen una estructura de hardware básica como por ejemplo 2 procesadores, 2 GB de memoria RAM y un disco duro de 500 GB, estos recursos lógicos y físicos son suficientes para ayudar al usuario a desarrollar sus actividades del día a día. Un servidor no es un equipo destinado a procesar tareas cotidianas, por el contrario, realiza tareas específicas de mediana o alta complejidad como por ejemplo atender las peticiones de diversos ordenadores (que pueden ser decenas o cientos), para dichas tareas se hace necesario que un servidor posea características más fuertes a nivel de hardware y software que un ordenador común, por ejemplo, en un servidor podemos encontrar 8 procesadores con 16 GB de RAM y una capacidad en disco duro en Terabytes acompañado de un sistema operativo de servidor como Windows Server o Ubuntu Server. El SGBD que gestiona una base de datos generalmente es instalado en un servidor que es enlazado en red con diversos equipos, de tal manera que los equipos cliente se pueden conectar al servidor central para realizar consultas a la base de datos. Uno de los factores que determinan la velocidad de respuesta del servidor es las características de hardware del mismo, en grandes organizaciones los servidores poseen un arquitectura de hardware generosa ya que deben atender peticiones de decenas de clientes en tiempo real.


Pag. 5


Importancia de la base de datos a nivel organizacional

La base de datos viene a ser un componente fundamental a nivel organizacional, ya que almacena todos los datos concernientes a la naturaleza del negocio, este contenido es procesado por un sistema informático para cubrir las diferentes necesidades de información, por ejemplo en el caso de la universidad, el personal de caja necesita información sobre el calendario de pensiones de un determinado alumno, mientras que un docente necesitará la relación de sus asignaturas y un tutor sus record de asistencias. Como vemos, la base de datos es fundamental para el desarrollo normal de los procesos del día a día de cualquier empresa. Más adelante detallaremos las medidas de protección de datos así como las buenas prácticas a considerar para garantizar la confiabilidad, integridad y disponibilidad de los datos.

Sistema Gestor de Base de Datos (SGBD)

Viene a ser un software que permite gestionar la funcionalidad de una base de datos, en el mercado informático los Sistemas Gestores de Base de Datos líderes son ORACLE, Microsoft SQL Server, MySQL y DB2. Cada uno de ellos cuenta con sus propias características, ventajas y desventajas. Cada SGBD contiene herramientas para implementar y administrar bases de datos, no olvidemos que la implementación contiene el modelo físico que se originó a partir del modelo lógico de base de datos. Por otro lado, la administración se da cuando ya se ha implementado la base de datos. Para el presente módulo usaremos Microsoft SQL Server 2012 como SGBD principal, ya que es muy comercial a nivel nacional e internacional.

Visual Studio 2012 y SQL Server 2012 forman una sólida pareja para crear diversos tipos de aplicaciones, ambos se complementan muy bien por pertenecer a la misma compañía, sin embargo debemos destacar que Visual Studio 2012 permite trabajar con bases de datos en ORACLE o MySQL, lo mismo sucede con el lenguaje de programación Java que permite trabajar con bases de datos implementadas en SQL Server.

Herramientas de Software para base de datos

Los SGBD incluyen el modelo físico de la base de datos, es decir, contienen los archivos físicos reales de la base de datos que se irán trabajando en el día a día por medio de los sistemas de


Pag. 6


información. Sin embargo, para realizar el modelo lógico también existen algunas herramientas como Erwin, Visio, Rational Rose, etc. Por otro lado, si elaboramos nuestro modelo lógico con papel y lápiz también es perfectamente válido, lo que pretenden algunas de las herramientas listadas en el párrafo anterior es reemplazar el papel por un software. La elección de utilizar cualquier de ellos es una cuestión de comodidad por la parte de la persona encargada de diseñar la base de datos.

Para elaborar nuestros modelos lógicos de base de datos usaremos Erwin, que es una herramienta muy comercial para el diseño de base de datos.

Asignación domiciliaria

1. Elaborar una lista de diferencias entre las principales SGBD líderes del mercado (ORACLE, SQL Server y MySQL). 2. Elaborar una línea de tiempo acerca de las versiones de SQL Server. 3. Describir brevemente algunas de las Certificaciones Microsoft relacionadas a SQL Server (Preferentemente sobre las últimas versiones). 4. Elaborar un breve resumen sobre las principales características de cada edición de SQL Server 2012.


Pag. 7


SESIÓN

Modelo Relacional

2


Analizar situaciones cotidianas en términos de base de datos. Conocer de manera teórica y práctica el diagrama Entidad/Relación.

TEMAS:

El modelo relacional Partes fundamentales del diagrama Entidad/Relación Demostración


Pag. 8


El modelo relacional El modelo relacional es una manera muy popular de representar bases de datos, es el más utilizado actualmente ya que provee facilidad para representar situaciones de la realidad en términos de base de datos. Se basa fundamentalmente en 2 conceptos, Entidad y Relación que revisaremos más adelante. El modelo de base de datos almacena información en forma de entidades (en forma lógica) que luego se convertirán en tablas (en forma física), el modelo sugiere que las entidades no pueden estar aisladas una de otra, sino que de alguna forma se relacionan por el mismo hecho de pertenecer al mismo giro de negocio. Debemos comprender que no la única manera de diseñar una base de datos, ya que existen algunos modelos adicionales como el Orientado a Objetos, Jerárquico, de Red, entre otros.

Partes fundamentales Entidad/Relación

del

diagrama

El diagrama entidad relación se compone principalmente de 2 elementos: 

Entidad

Representa algún elemento de la realidad, por ejemplo un libro, un comprobante de pago, un docente, un alumno, un producto, etc. Las entidades poseen atributos o campos para expresar los datos que debe almacenar, a continuación se listan una serie de entidades cada una de ellas con algunos atributos básicos: o

Libro (título, autor, editorial, año de publicación, resumen)

o

Computadora (número de serie, marca, modelo, capacidad de disco duro,

cantidad de memoria RAM) o

Paciente (DNI, nombres, apellidos, edad, sexo, tipo de sangre, número de

teléfono, estatura)

Llave primaria

Adicionalmente, se recomienda que las entidades posean una llave primaria, ésta viene a ser aquel campo que no se repite y que hace a cada registro único, por ejemplo, la entidad Paciente podría tomar como llave primaria el DNI, ya que no pueden existir pacientes con el mismo número de DNI.


Pag. 9


Tipos de datos

Cada atributo que posea una entidad debe tener asignado un tipo de dato, que expresa la naturaleza del valor que se desea almacenar en dicho atributo. Esto es muy similar a los tipos de datos que se aplican a una variable en Visual Basic .Net, sólo que esta vez lo estamos aplicando a base de datos. La siguiente tabla muestra la entidad Paciente con un conjunto de atributos y sus respectivos tipos de datos. Entidad

Atributos

Tipo de datos

Paciente

DNI Nombres Apellidos Edad Sexo Número de teléfono Estatura

Texto Texto Texto Numérico Texto Texto Numérico Tabla N° 3

Quizás se estén preguntando, ¿Por qué el DNI viene a ser de tipo Texto si contiene únicamente números al igual que el número de teléfono?, bueno la respuesta es que según las buenas prácticas de implementación de base de datos, únicamente se debe establecer un tipo de dato numérico a aquellos campos con los cuales vamos a realizar operaciones, por ejemplo es muy extraño sumar los número de DNI de 2 pacientes, o quizás obtener el promedio de todos los números telefónicos de mis pacientes, como estas operaciones no se van a realizar, entonces lo más adecuado es establecer un tipo de dato texto. Por otro lado, es más común obtener el promedio de edades de mis pacientes o quizás obtener el paciente con edad mayor, como estas operaciones son más cotidianas entonces es necesario asignar un tipo de dato numérico al atributo edad. Los tipos de datos texto, numérico, fecha o lógico (Verdadero o Falso) vienen a ser tipos de datos genéricos que se pueden aplicar a nuestro modelo lógico, sin embargo, en el momento de trasladar nuestro modelo lógico a un modelo físico, los tipos de datos vienen a convertirse en específicos, y esto es dependiente del Sistema Gestor de Base de Datos que vayamos a utilizar. Por ejemplo en Microsoft SQL Server 2012, para especificar un tipo de dato texto podemos utilizar un Char, Varchar, nChar, nVarchar y algunos otros, para representar números podemos usar un tinyint, int, decimal, entre otros. 

Relación

Generalmente las entidades no pueden quedar aisladas unas de otras, ellas mantienen vínculos que se representan por medio de una relación. Básicamente existen 3 tipos de relaciones:


Pag. 10

Programa: Programador .NET o

Uno a Uno

Se produce cuando a un elemento de una entidad le corresponde un elemento de otra entidad, por ejemplo si vamos a implementar una base de datos para un colegio, podemos listar a las entidades docente y alumno. Docente DNI Nombres Apellidos FechaDeNacimiento Sexo Profesión Especialidad

Alumno DNI Nombres Apellidos FechaDeNacimiento Sexo CódigoMatricula FechaDeInscripcion Ambas entidades tienen datos en común, por lo que podemos agruparlos en una tercera entidad denominada Persona, y luego crear una relación de uno a uno de Persona a Alumno y de Persona a Docente, ya que a un docente le corresponde una persona y a un alumno le corresponde una persona. Aquellos datos que no son comunes entre ambos se ubicarían en su respectiva entidad. Persona DNI Nombres Apellidos FechaDeNacimiento Sexo

Alumno CódigoMatricula FechaDeInscripcion

Docente Profesión Especialidad


Pag. 11


Uno a Muchos

Este tipo de relación se manifiesta cuando a un elemento de la primera entidad le corresponden muchos elementos de una segunda entidad y a un elemento de la segunda entidad le corresponde un único elemento de la primera entidad. Algunos ejemplos son: 

TipoDeDocente y Docente

En alguna universidad de puede manejar dos tipos de docentes, p or horas y a tiempo completo. Cada docente tiene asignado un solo tipo, y un tipo de docente podría incluir a un conjunto de docentes. 

Producto y categoría

Algunas bodegas y tiendas de abarrotes poseen una gran cantidad de productos agrupados por categorías (lácteos, condimentos, primera necesidad, limpieza, embutidos, etc). Cada producto tiene una única categoría mientras que una categoría podría abarcar a varios productos. o

Muchos a muchos

Se produce cuando a un elemento de la primera entidad le corresponden una serie de elementos de una segunda entidad y viceversa. 

Docente y Curso

Un docente puede dictar muchos cursos y un curso puede ser dictado por muchos docentes. 

Boleta y Producto

En una venta una boleta podría incluir la descripción de múltiples productos, y un mismo producto podría ser incluido en múltiples boletas o comprobantes. Cuando identificamos una relación de muchos a muchos, debemos romper dicha relación utilizando una tabla intermedia, que lleva las llaves principales de las entidades que se relacionan de muchos a muchos. Como recomendación se debe tener en cuenta que el nombre de la nueva tabla puede ser la combinación de ambas tablas de origen.


Pag. 12


Demostración Para crear entidades y relaciones, vamos a utilizar una herramienta muy popular en el diseño de base de dato denominada ERwin. Esta herramienta nos va a permitir elaborar prototipos de modelos lógicos que luego se convertirán en modelo físico utilizando Microsoft SQL Server 2012. Para abrir ERwin, hacemos click en el botón inicio, ubicamos el cursor sobre All Programs, y ubicamos la carpeta Computer Associates, luego seleccionamos la opción ERwin Data Modeler.

Figura N° 1

Una vez que inicie el programa, vamos dar click en el menú File, opción New, para crear un nuevo modelo lógico.

Figura N° 2

En la siguiente pantalla vamos a seleccionar la opción Logical, y hacemos click en el botón OK.


Pag. 13


Figura N° 3

ERwin nos muestra el área de trabajo en blanco donde comenzaremos a agregar entidades a través de la siguiente barra:

Figura N° 4

Damos un click en la segunda herramienta empezando del lado izquierdo, y hacemos un click en una zona vacía del área de trabajo, obtendremos el siguiente resultado:

Figura N° 5

Escribimos Paciente en el primer recuadro que pertenece al nombre de la entidad, el segundo recuadro pertenece a la zona de llave(s) primaria(s) y el tercero a los atributos comunes. Para trasladarnos de recuadro presionamos la tecla Intro.


Pag. 14


Figura N° 6

La llave primaria de una entidad puede ser simple o compuesta, se dice que es simple en el caso de que la llave primaria está conformada por un solo campo, y es compuesta si la llave primaria se forma por 2 o más campos.

Vamos a modificar la entidad para mostrar el siguiente resultado:

Figura N° 7

Para establecer los tipos de datos a cada atributo, hacemos doble click en la entidad y se nos mostrará la siguiente figura:

Figura N° 8


Pag. 15


Seleccionamos DNI y en el panel derecho seleccionamos el tipo de dato String, que representa datos de tipo texto.

Figura N° 9

Otorgamos los tipos de datos adecuados y siguiendo los pasos anteriores, vamos a crear otra entidad llamada Tipo que representará al tipo de paciente de una clínica, tal y como muestra la siguiente figura:

Figura N° 10

Ambas entidades tienen una relación de muchos a uno, ya que una persona tiene únicamente un tipo de paciente y un tipo de paciente puede incluir varias personas. Para realizar la representación gráfica de la relación, vamos a dar un click en la siguiente herramienta:

Figura N° 11


Pag. 16


La primera herramienta marcada en círculo empezando de la izquierda, pertenece a una relación de uno a muchos de tipo identificada, y la primera herramienta marcada iniciando desde la derecha corresponde a una relación no identificada. Cuando se establece una relación de uno a muchos identificada, la llave primaria de la entidad independiente pasa a formar parte de la llave primaria de la entidad dependiente. Por otro lado, si aplicamos una relación no identificada, la llave primaria de la entidad independiente pasa a formar parte de los campos de la entidad dependiente. Para éste utilizaremos una relación no identificada, ya que se supone que no debe existir pacientes con dos números de DNI iguales.

Figura N° 12

Si estableceríamos una relación identificada, la llave de la entidad Paciente se formaría por la combinación del DNI del mismo con el tipo de cliente, lo cual indicaría podrían existir 2 números de DNI iguales.

El modelo que estamos elaborando, tiene por defecto la notación IDEF1X, vamos a cambiar esa notación a un tipo más conocido como la notación IE (Information Engineering), para ello vamos a dar click en el menú Tools y seleccionamos la opción Model Properties.


Pag. 17


Figura N° 13

En la ficha Notation vamos a seleccionar la opción IE (Information Engineering), luego hacemos click en el botón OK.

Figura N° 14

De esa manera el modelo cambia un poco su formato, veamos el resultado.


Pag. 18


Figura N° 15

Hemos elaborado un modelo lógico bastante simple que nos sirve de ejemplo para aplicar los conceptos vistos anteriormente y conocer el manejo básico de ERwin como herramienta de elaboración de modelo lógico de base de datos.


Pag. 19


SESIÓN

Desarrollo de caso empresarial

3


Desarrollar modelos básicos de base de datos a nivel lógico.

TEMAS:

Caso


Pag. 20


Caso Enunciado Yiyi es una empresa dedicada a la venta de abarrotes, ofrece al público diversos tipos de

productos como leche, mermelada, crema de afeitar, etc. Cada producto que se ofrece pertenece a un única categoría (lácteos, embutidos, belleza, etc.), pero una categoría puede involucrar una seria de productos. Cada fin de mes o cuando se agota el stock de un producto, el administrador se contacta con sus proveedores (por medio de un número telefónico que incluye el proveedor) para incrementar el stock de aquellos productos que no tienen stock disponible o que superaron el stock mínimo permitido. Al momento que un proveedor hace entrega de los productos, se lleva una pequeña ficha donde figura el nombre del producto con su respectivo precio de compra, la fecha en que se realiza la entrega el código del vendedor que hace la recepción. Al momento de realizar la venta, se emite un recibo que incluye el nombre y apellido del cliente, su número de DNI, la fecha de la compra, la lista de productos que incluye la venta incluyendo el subtotal (precio unitario del producto por la cantidad) y el total.

Resolución

Lo primero que haremos es crear un nuevo modelo lógico en ERwin y lo guardaremos con el nombre Demostracion01. Luego vamos a crear la entidad producto, que almacenará todos los productos disponibles que la empresa, por lo que se puede apreciar en el caso, los atributos serían: Nombre del producto, precio sugerido de venta, vstock actual, stock mínimo y adicionalmente agregaremos un código que nos servirá como llave primaria.

Figura N° 16

El caso también nos menciona que cada producto pertenece a una única categoría, así que crearemos una entidad Categoría y ya que el ejercicio no nos indica demasiado sobre sus atributos, asumiremos los atributos codigoCategoria, nombre y descripción.


Pag. 21


Es recomendable no asignar tildes y ni usar la letra “ñ” en los nombres de las entidades y en los nombres de los campos, esto se debe a que la mayoría de software destinado al trabajo con base de datos son elaborados en países que tienen como lengua materno al inglés, y en dicho idioma no se usa estos caracteres..

Figura N° 17

Con respecto al proveedor, guardaremos los siguientes datos: Código de proveedor, nombre del proveedor, nombre del representante y un teléfono de contacto.

Figura N° 18


Pag. 22


En el caso de hace referencia al cliente, del cual se solicita su número de DNI y su nombre completo, vamos a utilizar el DNI como llave primaria ya que es un dato que por su naturaleza no se repite.

Figura N° 19

Ahora vamos a representar el proceso de venta, el enunciado menciona que una venta incluye el DNI del cliente junto con su nombre y apellido, adicionalmente se incluye la fecha de compra y la lista de productos. El siguiente paso es crear la entidad Venta, y como esta incluye los datos del cliente, entonces vamos a identificar qué tipo de relación se debe aplicar entre cliente y venta. Veamos:

Un CLIENTE puede participar en muchas VENTAS Una VENTA puede incluir muchos CLIENTES

SI NO, solamente uno por cada VENTA

Por lo tanto, concluimos que se trata de una relación de uno a muchos (de Cliente hacia venta). Es hora de averiguar si se trata de una relación identificada o no identificada, para ello debemos analizar si es necesario que la llave primaria de ventas sea simple o compuesta. A cada venta le vamos a establecer un código numérico distinto, por lo cual no necesitaría la llave principal del cliente para formar su llave primaria, por lo tanto vamos a utilizar una relación no identificada para vincular ambas entidades.


Pag. 23


Figura N° 20

Una venta se compone un solo producto o de varios, y un producto puede incluirse en varias ventas, por lo tanto entre la entidad Venta y Producto tenemos una relación de muchos a muchos, no olvidemos que para estos casos debemos crear una tabla intermedia que rompa la relación de muchos a muchos. En esta nueva tabla vamos a incluir la cantidad de productos que se vendieron, incluyendo su precio de venta. Observemos que no incluimos el subtotal porque viene a ser un dato calculado por medio de la multiplicación de la cantidad de unidades del producto por el precio de venta; de igual manera sucede con el total, que vendría a ser la suma de todos los subtotales.

Figura N° 21


Pag. 24


Vamos a relacionar de una vez la entidad Producto con Categoria, recordemos que el enunciado nos da a entender que un producto únicamente puede pertenecer a una categoría, mientras que una categoría puede involucrar una serie de productos.

Figura N° 22

Podemos observar que la entidad Proveedor se encuentra suelta por el momento, según el caso menciona, un proveedor brinda una serie de productos a Yiyi. Analicemos el tipo de relación entre ambos, un Proveedor puede brindar varios productos, y un producto puede ser brindado por varios proveedores, por lo tanto se crea otra relación de muchos a muchos que debemos romper con una tabla intermedia.

Figura N° 23


Pag. 25


Notemos que en el modelo se muestran 3 tipos de precios, un precio base que es el precio normal de un producto, un precio de venta que es el precio real al que se vende un producto (que no necesariamente es igual al precio base en todos los casos) y un precio de compra que es el precio adquirimos con el proveedor. Por otro lado, hemos utilizado una relación de uno a muchos identificada para indicar que no puede existir el mismo código del producto con el mismo código del proveedor 2 o más veces . Finalmente vamos a agregar la entidad vendedor con algunos datos básico:

Figura N° 24


Pag. 26


SESIÓN

Ejercicios Propuestos

4


Aplicar los conceptos principales de diseño de base de datos en escenarios empresariales reales. Identificar los escenarios comunes de aplicación de buenas prácticas de diseño de base de datos.

TEMAS:

Ejercicios propuestos


Pag. 27


Ejercicios propuestos A continuación se muestra una lista de ejercicios propuestos para aplicar lo desarrollado en sesiones anteriores, elabore el Diagrama Entidad/Relación de cada uno de ellos teniendo en cuenta las buenas prácticas de diseño de base de datos y normalización. Considere que se pueden realizar suposiciones en caso de que exista información incompleta.

1. Una tienda de comunicaciones desea almacenar información acerca de sus equipos celulares y accesorios que ofrece al público en general, cada equipo cuenta con marca, modelo, color (podrían existir combinaciones, por ejemplo azul y gris), número de serie, dimensiones (largo por ancho por altura), precio base y fabricante. Por el lado de los accesorios se tienen 2 categorías: Internos (batería, memoria, etc.) y Externos (auriculares, estuche, parlantes, cargador, cable de datos, etc). Actualmente la tienda pega un sticker de color amarillo a aquellos celulares que se encuentran de moda y un sticker de color rojo a aquellos modelos desfasados. Considere que cada producto tiene un empleado registrador, aquel que ingresa los datos del celular o accesorio; y un empleado de salida, que es aquel que solicita un producto para realizar alguna venta. Sobre los empleados se necesita almacenar los nombres, apellidos (paterno y materno), sexo, correo electrónico, fecha de inicio de labores, DNI, sueldo base (entre 1000.00 y 9999.99 nuevos soles). 2. La empresa en desarrollo “Candy’s Bell” desea digitalizar el proceso de control de asistencia del personal, básicamente desean conocer los horarios de entrada y salida de cada trabajador. Considere las siguientes reglas de negocio: 

Un trabajador puede tener asignado 2 turnos como máximo en un sol o día.



Existen horarios rotativos, es decir, un trabajador puede laborar los días lunes, martes y miércoles de 7:00 A.M. a 03:00 P.M., y los días jueves y viernes de 09:00 A.M. a 05:00 P.M.



Cada trabajador tiene un cargo específico, y se aplica un descuento mensual de acuerdo al mismo.



El tercer día de cada mes se generan el reporte de asistencia de cada trabajador, dicho informe muestra las horas y/o minutos de tardanza, cantidad de faltas, cantidad de veces que se retiró antes de terminar su horario de trabajo y la cantidad de marcas que hizo en el mes acompañado de la fecha y horario respectivo.


Pag. 28


3. Una empresa dedicada al rubro de transportes desea informatizar sus principales procesos, todos los días se envían buses desde su terminal central ubicado cerca del mercado Central. Cada salida consta de un bus, origen, destino, fecha y hora de salida, piloto, copiloto y auxiliar; por cada bus se desea saber el número de placa, cantidad de asientos, código interno, cantidad de pisos, marca y modelo. Por otro lado los pilotos y los copilotos comparten los mismos datos, estos son: Nombres, apellidos, sexo, número de celular, brevete, fecha de inicio de labores, fecha de nacimiento y nivel de conocimiento de mecánica automotriz (nula, básica, intermedia o avanzada). Sobre los auxiliares se desea saber sus nombres, apellidos, sexo, fecha de inscripción y número de celular. En cada viaje se puede tener como máximo una cantidad de pasajeros que no supere la cantidad de asientes del bus respectivo, además, cada pasajero debe viajar con su boleto respectivo que contiene su nombre completo, edad, DNI, origen de viaje, destino de viaje, fecha y hora de salida, valor del boleto (en números y letras), número de asiento y el nombre de usuario del vendedor que lo atendió. El Gerente sugiere que cada vendedor debe tener un nombre de usuario asignado con su respectiva clave de acceso (que debe ser encriptada para brindar mayor seguridad), adicionalmente se debe guardar el nombre, apellido paterno, apellido materno, fecha de nacimiento, fecha de inicio de labores, teléfono fijo, número de celular (indicando el operador y si es ordinario, RPM o RPC), observaciones, estado civil, sexo y correo electrónico. Adicionalmente considere que los vendedores pueden realizar una reserva vía teléfono o de manera presencial indicando el número de su DNI y su nombre completo, recuerde que las reservas en caso de que no sean confirmadas se deben eliminar automáticamente 1 hora antes de la salida del viaje.


Pag. 29


SESIÓN

Microsoft SQL Server 2012

5


Identificar correctamente las partes principales de Microsoft SQL Server 2012. Conocer los principales componentes de Microsoft SQL Server 2012.

TEMAS:

Conociendo Microsoft SQL Server 2012 Modos de autenticación Elementos principales del entorno de trabajo


Pag. 30


Conociendo Microsoft SQL Server 2012 Microsoft SQL Server 2012 es la última versión del Sistema Gestor de Base de Datos más popular de la compañía Microsoft, posee una serie de características que hacen de ella una herramienta muy competitiva en un mercado con duros competidores como ORACLE por ejemplo.

Figura N° 25

Modos de autenticación El modo de autenticación viene a ser la forma de identificación que usamos para ingresar a Microsoft SQL Server 2012, por defecto manejamos 2 tipos: 

Autenticación Windows

Es una autenticación basada en un usuario de Windows. Normalmente Microsoft SQL Server 2012 es instalado sobre un servidor con Windows Server, en este último se pueden crear diversas credenciales con diversos permisos específicos para cada tipo de escenario, Microsoft SQL Server puede utilizar una de esas credenciales para permitir el ingreso. 

Autenticación SQL Server

Este tipo de autenticación utiliza credenciales de Microsoft SQL Server independientemente del sistema operativo. Microsoft SQL Server 2012 administra internamente sus propias credenciales para realizar diversas tareas con ciertos permisos. Para conocer aplicar lo explicado anteriormente vamos ingresar a Microsoft SQL Server 2012, para ellos hacemos click en el botón inicio – All Programs, ubicamos la carpeta Microsoft SQL Server 2012 y seleccionamos la opción SQL Server Management Studio.


Pag. 31


Figura N° 26

Las buenas prácticas recomiendan utilizar Autenticación Windows siempre y cuando Microsoft SQL Server 2012 esté instalado sobre Windows Server.

Figura N° 27

En la sección Server Name vamos a ingresar el nombre del servidor al cual deseamos conectarnos, si estamos trabajando con Microsoft SQL Server instalado en nuestro propio equipo entonces el servidor es local, y lo podemos representar escribiendo un punto “.” o sino a través de la palabra localhost.


Pag. 32


Debajo del nombre del servidor, en la sección Autenticación vamos a seleccionar Windows Authentication, ya que al momento de la instalación se creó un usuario en Windows con el nombre alumno, y que fue asignado como un administrador de Microsoft SQL Server 2012. Finalmente hacemos click en el botón conectar y luego de unos segundos se nos mostrará una pantalla similar a la siguiente:

Figura N° 28

Para conocer los datos de nuestra conexión, podemos observar la parte superior del panel Object Explorer ubicado en la izquierda de la pantalla:

Figura N° 29


Pag. 33


El panel izquierdo muestra el nombre del servidor, que es WE09-2 seguido entre paréntesis de la versión Microsoft SQL Server 2012 que estamos usando y del nombre del usuario conectado (Alumno para este caso).

Elementos principales del entorno de trabajo La interfaz principal de trabajo de Microsoft SQL Server 2012 es similar a la de Visual Studio 2012, veamos las partes principales:

Figura N° 30

Número

1 2 3 4 5 6

Nombre

Descripción

Barra de título

Muestra el título del programa, en este caso, Microsoft SQL Server Management Studio Contiene los comandos disponibles en SQL Server 2012 Contiene los comandos más cotidianos de SQL Server 2012 Área donde se realiza consultas, diseña tablas, inserta registros, etc. Muestra todos los objetos disponibles en el servidor. Muestra un conjunto de características del objeto seleccionado.

Barra de menú Barra de herramientas Zona de trabajo Object Explorer Panel propiedades

Tabla N° 3


Pag. 34


SESIÓN

Planificación de base de datos

6


Crear adecuadamente una base de datos. Aplicar buenas prácticas de implementación de base de datos de acuerdo al escenario empresarial.

TEMAS:

Creación de base de datos Recomendaciones generales


Pag. 35


Creación de base de datos Una base de datos no se crea todos los días (al menos a nivel empresarial), es por ello que se debe hacer de la manera más correcta y aplicando en todo momento buenas prácticas de implementación, de esta forma obtendremos una base de datos con alta disponibilidad y de gran rendimiento. Tipos de archivo de base de datos

Una base de datos en Microsoft SQL Server se compone de 3 tipos de archivos: 

MDF

Es el archivo principal de la base de datos, contiene la configuración de la misma así como las ubicaciones de los otros archivos. Además de que permite el almacenamiento de registros de datos. 

NDF

De manera similar que el anterior, también permite el almacenamiento de datos con la característica de que se puede utilizar para desplegar la base de datos en diferentes ubicaciones. 

LDF

También conocido como registro de transacciones, se encarga de almacenar todas las transacciones que realicemos sobre la base de datos. Consideremos que una base de datos de Microsoft SQL Server 2012 debe contener como mínimo un archivo .MDF y .LDF

Para visualizar los tipos de archivos, vamos a crear una base de datos con el nombre RRHH, para ello hacemos click derecho en la carpeta Databases ubicada en el Object Explorer, y seleccionamos la opción New Database.


Pag. 36


Figura N° 31

A continuación se mostrará una ventana donde ingresaremos las características principales de nuestra base de datos.

Figura N° 32


Pag. 37


En la sección Database Name vamos a ingresar RRHH, ya que vamos a crear una base de datos sencilla de recursos humanos. En la parte inferior podemos visualizar un botón con el texto Add, ese botón nos permite agregar más archivos a nuestra base de datos, al momento de presionar el botón se agrega un nuevo elemento en la lista de archivos que componen la base datos.

Figura N° 33

En la segunda columna de la lista, titulada File Type, podemos seleccionar el tipo de archivo que deseamos agregar, recordemos que una base de datos en SQL Server 2012 se compone de 3 tipos de archivos .MDF, .LDF y .NDF. Los archivos con el tipo Rows Data vienen a ser archivos .MDF o .NDF, pero ¿Cómo los diferenciamos?, bueno el primer archivo Rows Data es el archivo principal .MDF y el resto automáticamente se convierten en .NDF, por otro lado los archivos con el tipo Log vienen a pertenecer al tipo registro de transacciones. Otro dato importante es el ingresado en la columna Initial Size, que asigna el tamaño inicial de un archivo de base de datos, como podemos apreciar para los archivos de tipo .MDF o .NDF el tamaño inicial por defecto es 5 MB y para el registro de transacciones de 1 MB. Se recomienda asignar un espacio generoso (considerar el recurso tecnológico con el que contamos) ya que la base de datos por su naturaleza tiende a crecer, y si establecemos un tamaño inicial de 5 MB, podría llenarse en poco tiempo. En caso de que se llene el espacio asignado inicialmente, se produce un evento denominado Autogrowth, que quiere decir


Pag. 38


crecimiento automático. Notemos que esta configuración se muestra en la quinta columna, para apreciar con mayor detalle las características que nos brinda Microsoft SQL Server 2012, vamos a dar un click en el botón de los 3 puntos que aparece en esta columna.

Figura N° 34

Si habilitamos el Autogrowth, cuando la base de datos se quede sin espacio, entonces ocurrirá un autoincremento que puede ser en Porcentaje o en Megabytes, adicionalmente SQL Server 2012 nos permite establecer un tope o tamaño máximo de la base de datos por medio de la sección Maximun File Size . Debemos tener en cuenta que no es del todo recomendable re comendable habilitar el Autogrowth, ya que si la la base de datos es altamente concurrida y se produce el Autogrowth, el servidor destinará algunos de sus recursos a redimensionar la base de datos, lo cual podría implicar que la base de datos reduciría su rendimiento. Es importante realizar una buena planificación de la base de datos, de esa manera podemos crear una base de datos con proyección al futuro, que sea fácil de mantener y sobretodo que posea un buen rendimiento.

Recomendaciones generales A continuación se lista una serie de recomendaciones a tener en cuenta al momento de crear una base de datos: 1. Ubicar el registro de transacciones en un disco duro separado. 2. Particionar la base de datos en diversos discos duros para mejorar el rendimiento, esto se puede lograr utilizando RAID.


Pag. 39


3. De preferencia no habilitar el autogrowth, pero el administrador de base de datos debe realizar un seguimiento periódico sobre los recursos de almacenamiento de la base de datos. 4. En caso de que se habilite el autogrowth se recomienda establecer un tope máximo de tamaño.


Pag. 40


SESIÓN

Creación de Tablas

7

Al finalizar la presente sesión, el participante estará en la capacidad de:

Crear tablas para almacenar datos. Conocer los tipos de datos que ofrece Microsoft SQL Server 2012 para el trabajo con datos. Temas : Creación de tablas Ingreso de datos Ejercicio


Pag. 41


Creación de tablas Los datos incluidos en una base de datos se guardan en formato de tablas, una tabla representa a una entidad del modelo lógico. Para crear una base de datos con tablas en Microsoft SQL Server 2012 vamos a seguir las siguientes indicaciones: 1. Vamos a crear una base de datos con el nombre RecursosHumanos, que tiene como propósito gestionar información relacionada al personal que labora de una empresa. Una vez creada, hacemos click derecho con en la carpeta Tables que está incluida en la base de datos RecursosHumanos, y luego seleccionamos la opción New Table.

Figura N° 35

2. Inmediatamente se apertura el diseñador de tablas, en este caso vamos a crear una tabla con el nombre tblEmpleado con una serie de atributos (también denominado columnas o campos) que se ingresarán en la primera columna (Column Name) tal y como muestra la Figura N° 36.

Figura N° 36


Pag. 42


3. El siguiente paso es establecer los tipos de datos respectivos, para dicho propósito utilizaremos la columna con el nombre Data Type, ubicada a la derecha del nombre de la columna. Al igual que las variables en Visual Basic .Net, los campos o atributos de una tabla deben tener asignado un tipo de dato que guarde relación con lo que se desea almacenar. Para establecer datos de tipo cadena principalmente podemos utilizar varchar y char y nchar, cada uno de ellos incluyen un valor numérico entre paréntesis que indica la longitud del campo. Veamos un ejemplo: Si utilizamos un tipo de dato varchar(15) y almacenamos “Amy Lee”

en su interior,

solamente se usan 7 espacios y el resto se elimina, mientras que si almacenamos el mismo nombre en un char(15), siempre se almacenan 15 espacios, es decir, se guardará “Amy Lee” seguido de 8 espacios en blanco, que sumados con los 7 caracteres del nombre suman

un total de 15 caracteres. El tipo de datos varchar se usa generalmente para cadenas que tener longitud variable en su contenido, mientras que el tipo de dato char es recomendable para cadenas de longitud fija. Teniendo como premisa el párrafo anterior, vamos a empezar a otorgar los tipos de datos respectivos en nuestra tabla. El DNI es un tipo de dato que almacena siempre 8 números, por lo tanto podemos utilizar un tipo de dato char con longitud 8 . En ocasiones es inevitable hacernos la siguiente pregunta: ¿Por qué otorgar un tipo de dato cadena a un DNI que almacena sólo números?, bueno en realidad las buenas prácticas de base de datos indican que es recomendable utilizar un tipo de dato numérico siempre y cuando se realizará operaciones con el dato, por ejemplo, en el caso de la edad de un alumno, es muy probable que se requiera obtener el promedio de edades de un salón de clases o quizás quién es el alumno con mayor edad, u operaciones similares. Sin embargo, en el caso del DNI es poco común obtener el promedio de todos los números de DNI de un conjunto de personas, o quizás obtener el número de DNI mayor. Asignamos los siguientes tipos de datos para nuestra tabla:

Figura N° 37

4. Los demás atributos son analizados a continuación: a. Para la fecha de nacimiento usaremos el tipo de dato date, que tiene un intervalo de fechas desde el 01 de enero del año 1 hasta el 31 de diciembre del año 9999. El


Pag. 43


correo electrónico por ser un dato con longitud variable se le asignará el tipo de dato varchar(50), con respecto al sexo guardaremos M o F según sea el caso, es por eso que le aplicaremos el tipo de dato char(1). b. El tipo de datos lógico (Verdadero o Falso) viene representado por tipo de dato bit, el cual lo aplicaremos al campo habilitado que indica si el empleado sigue trabajando o no en la organización. c. Con respecto al estado civil, debemos recordar que únicamente existen formalmente el estado civil soltero o casado, por lo tanto a lo mucho se almacenarán 7 caracteres , es por eso que utilizaremos el tipo de dato

varchar(7). d. La fecha de registro indica la fecha y hora en la cual el empleado ingresa a la base de datos corporativa, el tipo de dato datetime se acopla perfectamente a guardar este tipo de registro. e. Para representar valores monetarios, se puede utilizar un tipo de dato llamado money o smallmoney, la diferencia entre ellos es el rango de valores que pueden

almacenar, mientras que un smallmoney almacena valores desde - 214.748,3648 a 214.748,3647, el tipo de dato money almacena valores desde 922,337,203,685.477,5808 a 922,337,203,685.477,5807. Ambos tienen una precisión de una diezmilésima de las unidades monetarias que representan, aplicaremos el tipo de dato smallmoney para el campos sueldo establecido. f. Las fotografías se pueden almacenar en un tipo de dato image que es especializado para el trabajo con imágenes.

Figura N° 38


Pag. 44


5. Para guardar nuestra tabla vamos a hacer click en el botón guardar ubicado en la barra de herramientas, el nombre de nuestra tabla será tblEmpleado.

Figura N° 39

6. Ahora vamos indicar que campos son opcionales en nuestra tabla, para ello utilizaremos la tercera columna que lleva el título Allow Nulls, aquellos campos que consideremos opcionales vamos a marcarlos con un check.

Figura N° 40

7. Ahora vamos a establecer la llave primaria de la tabla, recordemos que la llave primaria es aquel campo que no se repite y que sirve para identificar a cada registro. En este caso el campo más adecuado es el DNI ya que no pueden existir 2 o más empleados con número de DNI idéntico. Para establecer la llave primaria, hacemos un click derecho sobre el nombre del campo DNI y luego seleccionamos la primera opción con el texto Set Primary Key.


Pag. 45


Figura N° 41

8. Para verificar que nuestra se creó correctamente, podemos desplegar la carpeta Tables incluida dentro de la base de datos RecursosHumanos que acabamos de crear.Incluso si desplegamos la carpeta Columns de nuestra tabla, vamos a poder apreciar los atributos que hemos ingresado con su respectivo tipo de dato.

Figura N° 42


Pag. 46


Ingreso de datos El ingreso de datos a una base de datos es un proceso que generalmente se realiza desde un sistema de información (recomendado para usuarios finales), sin embargo, se puede realizar el ingreso respectivo desde el Sistema Gestor de Base de Datos que estemos utilizando. Para abrir la tabla e iniciar el ingreso de datos vamos a hacer un click derecho en la misma y seleccionamos la opción Edit Top 200 rows.

Figura N° 43

Inmediatamente se mostrará una grilla con los nombres de las columnas para realizar el ingreso de datos respectivo.

Figura N° 44

Ahora procederemos a ingresar registros fila por fila, debemos considerar que debemos respetar los datos de cada columna para evitar posibles mensajes de error. Como ejercicio vamos a ingresar datos aleatorios de 10 personas. Recuerde que si desea dejar vacío el campo correoElectronico o fotografía, simplemente digite NULL, que representa el valor vacío en Microsoft SQL Server 2012, recuerde que estos datos son opcionales ya que les dejamos un check en la columna Allow nulls durante el diseño de la tabla.


Pag. 47


Figura N° 45

Vamos a dejar vacío todos los datos incluidos en el campo fotografía, ya que lo más recomendables guardar las fotografías desde un sistema de información.

Ejercicio A manera de ejercicio, cree una nueva base de datos con el nombre Ventas1, y agregue las tablas de la Figura N° 19. No olvide asignar los tipos de datos más adecuados así como también establecer la llave primaria.


Pag. 48


SESIÓN

Relaciones entre Tablas

8


Identificar los tipos de relaciones entre tablas utilizando reglas del negocio. Establecer relaciones entre tablas. Temas: Visión general Identificando las relaciones Ejercicio


Pag. 49


Visión general La mayor parte de tablas incluidas en una base de datos deben quedar relacionadas, estas relaciones son las mismas que vimos anteriormente, es decir, uno a uno, uno a muchos y muchos a muchos.

Vamos a crear una base de datos llamada Ventas con las siguientes tablas: Tabla

tblProducto

tblCategoria

tblProveedor

Atributos

Tipo de dato

Notas

CodigoProducto Nombre Precio StockActual StockMinimo CodigoCategoria Nombre Descripcion CodigoProveedor Nombre Representante Telefono

varchar(20) varchar(100) smallmoney tinyInt tinyint tinyint varchar(50) varchar(250) varchar(20) varchar(150) varchar(150) varchar(10)

Llave primaria

Llave primaria

Llave primaria

Tabla N° 4

El tipo de dato tinyint se utiliza para representar valores numéricos enteros, debemos considerar que su rango es idéntico al tipo de dato Byte de Visual Basic, es decir, de 0 a 255. Para los tipos de datos numéricos podemos utilizar los siguientes: Tipo de dato

tinyint smallint int bigint

Descripción

Rango

Números enteros

decimal Números de punto flotante

0 a 255 -32768 a 32767 -2147483648 a 2147483647 -9223372036854775808 a 9223372036854775807 Cuando se utiliza la precisión máxima, los valores válido se sitúan entre -10^38 y 10^38 - 1

Tabla N° 5

Una vez que creamos las tablas anteriormente mencionadas, la carpeta donde se ubican las tablas debe quedar similar a la siguiente figura:


Pag. 50


Figura N° 46

Para relacionar las tablas anteriormente creadas, vamos a crear un diagrama, el diagrama de base de datos nos permite visualizar de manera más amigable las tablas incluidas en nuestra base de datos. Vamos a hacer click derecho sobre la carpeta Database Diagrams ubicada dentro de la base de datos que estamos trabajando (Ventas en este caso) y seleccionamos la opción New Database Diagram .

Figura N° 47

A continuación seleccionamos todas las tablas que hemos creado y hacemos click en el botón agregar.

Figura N° 48


Pag. 51


Luego vamos a obtener el siguiente resultado:

Figura N° 49

Ahora vamos a guardar el diagrama con el nombre diagrama01.

Figura N° 50

Identificando las relaciones Ahora vamos a realizar el análisis respectivo para identificar las relaciones en nuestro modelo: Una categoría puede contener muchos productos, pero un producto puede pertenecer únicamente a una categoría, por lo tanto, tenemos una relación uno a muchos iniciando en categoría.


Pag. 52


La teoría menciona que si identificamos una relación de uno a muchos, debemos copiar la llave primaria de la tabla independiente (Categoría) a la tabla dependiente (Producto). Para agregar un campo a la tabla producto con las características de la llave primaria (codigoCategoria) vamos a dar un click derecho a la tabla producto y seleccionamos Vista de Tabla , opción Estándar .

Figura N° 51

De esa manera observaremos los campos y sus tipos de datos respectivos en el diagrama que hemos creado, realizamos la misma operación anterior para las demás tablas, el resultado debe mostrarse de manera similar a la siguiente figura:

Figura N° 52

Ahora agregamos el campo CodigoCategoria con el tipo de dato tinyint en la tabla tblProducto.


Pag. 53


Figura N° 53

Para establecer la relación, arrastramos a partir de la llave primaria de la tabla independiente (tblCategoria) hacia el atributo CodigoCategoria de la tabla dependiente (tblProducto). Inmediatamente después se mostrará una ventana indicándonos los campos mediante los cuales queremos relacionar. En ella verificamos que corresponda al campo CodigoCategoria de ambas tablas.

Figura N° 54

Hacemos click en Aceptar, y en la siguiente ventana también aplicamos el mismo procedimiento, luego el diagrama mostrará ambas tablas relacionadas.


Pag. 54


Figura N° 55

Ahora vamos a analizar la relación entre producto y proveedor, un pr oducto puede ser brindado por muchos proveedores, y un proveedor puede abastecer muchos productos, por lo tanto tenemos una relación de muchos a muchos. En este caso tenemos que romper la relación a través de una tabla intermedia que generalmente recibe el nombre de ambas tablas involucradas y tiene como atributos sus respectivas llaves primarias.

Figura N° 56

Luego relacionamos tblProveedor y tblProducto con tblProductoProveedor.


Pag. 55


Figura N° 57

Finalmente, vamos a aplicar una llave primaria a tblProductoProveedor, esta vendría a ser compuesta. Una llave primaria compuesta es aquella que se forma por la combinación de 2 o más atributos, para aplicar este concepto a tblProductoProveedor vamos a seleccionar los dos únicos campos y vamos a hacer un click derecho dentro de cualquiera de los campos y seleccionamos la opción Set Primary Key.

Figura N° 58


Pag. 56


Finalmente obtenemos el siguiente resultado:

Figura N° 59

Ejercicio A manera de ejercicio, transforme en un modelo físico el diagrama presentado en la figura N° 24.

El diagrama se debe realizar una nueva base de datos con el nombre Ventas, considerando las siguientes indicaciones: 

Establecer correctamente los nombres de tablas.


Pag. 57

Programa: Programador .NET 

Asignar el tipo de dato más correcto para cada una de las columnas de las diferentes tablas.



Antes de relacionar verifique si la relación es identificada o no identificada.



Por cuestiones de prueba y aprendizaje, inserte por lo menos 5 registros en cada una de las tablas que componen el modelo.


Pag. 58


SESIÓN

Lenguaje Estructurado de Consulta

9


Conocer el Lenguaje Estructurado de Consulta y diferenciarlo con T-SQL y PL-SQL. Elaborar consultas básicas para extraer información. Temas: Lenguaje Estructurado de Consulta


Pag. 59


Lenguaje Estructurado de Consulta Es un lenguaje para extraer información en bases de datos relacionales, también se utilizara para modificar objetos de la misma. Su abreviatura viene a ser SQL que hace referencia a su nombre en inglés Structured Query Language. A lo largo del presente módulo usaremos una extensión del Lenguaje Estructurado de Consulta (SQL) denominado Transact – SQL de Microsoft y Sybase. Debemos recalcar que también existe el término PL-SQL que viene a ser un lenguaje incrustado por ORACLE, ambos lenguajes tienen como base al Lenguaje Estructurado de Consulta (SQL). Muchas veces se suele confundir SQL con SQL Server, el primero de ellos viene a ser en lenguaje de consulta explicado en el párrafo anterior, mientras que SQL Server es el software para el trabajo con base de datos que ofrece Microsoft

Tipos de sentencias

Dentro del Lenguaje Estructurado de Consulta existen dos tipos de sentencias: Sentencias DDL (Lenguaje de Definición de Datos), y sentencias DML (Lenguaje de Manipulación de Datos). Las sentencias DML permiten insertar, actualizar, eliminar y seleccionar registros de una base de datos, las principales son: SELECT

Permite seleccionar columnas específicas de una o más tablas, así como también limitar la cantidad de filas. Se utiliza para extraer información personalizada de una base de datos.

INSERT

Se utilizar para agregar registros a una tabla.

DELETE

Eliminar los registros de una tabla de acuerdo a ciertos parámetros.

UPDATE

Permite actualizar una o más filas de una tabla de acuerdo a las columnas seleccionadas. Tabla N° 6

Para utilizar de manera práctica lo visto anteriormente, vamos a crear una base de datos con el nombre Biblioteca, donde crearemos la siguiente tabla:


Pag. 60


Figura N° 60

Una vez creada la tabla, vamos a agregar los siguientes registros:

Figura N° 61

Ahora procederemos a utilizar la sentencia SELECT, que nos permite extraer información personalizada de una o más tablas, para conseguir el propósito debemos acompañar a la sentencia SELECT con una clausula FROM, que se utiliza para especificar las tablas de las cuales queremos obtener información. Para empezar a escribir Transact – SQL, vamos a dar un click en el botón New Query ubicado en la parte superior izquierda de la pantalla principal de Microsoft SQL Server.

Figura N° 62

Luego Microsoft SQL Server mostrará una hoja en blanco similar a un bloc de notas donde iniciaremos con algunas consultas básicas. Veamos algunos ejemplos:


Pag. 61


a. Mostrar todos los libros registrados

Para mostrar todos los registros de una tabla, simplemente especificamos en la sentencia SELECT las columnas que queremos mostrar y luego a través de FROM indicaremos el

nombre de la respectiva tabla. SELECT codigo,titulo,autor,cantidadPaginas, fechaDePublicacion ,resumen,editorial,fecharDeInscripcion FROM tblLibro

Para probar nuestra consulta simplemente presionamos F5 o hacemos click en el botón Execute ubicado en la barra de herramientas.

Figura N° 63

Luego se mostrarán los resultados en la parte inferior del programa.

Figura N° 64

El comodín * permite indicar todas las columnas, por lo tanto, si reemplazamos todos los atributos por un asterisco, el resultado es el mismo. SELECT * FROM tblLibro

Para aplicar comentarios en T-SQL podemos utilizar dos guiones, por ejemplo, si quisiéramos agregar una breve descripción al código anteriormente mostrado, podemos escribir lo siguiente: --Ejercicio 1: --Mostrar todos los libros registrados SELECT * FROM tblLibro

Los comentarios incluidos en el código T-SQL se muestran en color verde por defecto.

b. Mostrar todos los libros registrados ordenados por título. --Ejercicio 2: --Mostrar todos los libros registrados ordenados por título SELECT * FROM tblLibro ORDER BY titulo


Pag. 62


c. Mostrar el título y la cantidad de hojas de cada libro, iniciando por el libro con mayor cantidad de hojas SELECT titulo,cantidadPaginas FROM tblLibro ORDER BY cantidadPaginas DESC

DESC indica que el ordenamiento se aplicará de manera descendente, y ASC indica ascendente, pero si no incluimos ni uno ni otro se asume que el ordenamiento es ascendente.

d. Mostrar los libros cuya cantidad de hojas sea mayor a 100 .

Para éste ejercicio usaremos WHERE, que especifica una condición (Similar a la estructura IF de Visual Basic .Net) al resultado de la consulta, en este caso la condición es que la cantidad de hojas sea mayor a 100. SELECT titulo,cantidadPaginas FROM tblLibro WHERE cantidadPaginas >100

No olvidemos que ORDER BY generalmente se ubica después de WHERE, y este se ubica después de FROM. No podemos ubicar cada comando en cualquier parte, sino que se sigue un orden establecido.

e. Mostrar los libros que inicien con la letra “E”

Para éste ejercicio utilizaremos LIKE que tiene la capacidad de buscar coincidencias parecidas en base a algunos parámetros, adicionalmente usaremos el comodín % que quiere decir “cualquier cosa” (ya sea número, cadena símbolo, espacio en blanco, etc.). SELECT titulo,cantidadPaginas FROM tblLibro WHERE titulo LIKE 'E%' ORDER BY cantidadPaginas

Recordemos que las cadenas en T-SQL se deben ubicar entre comillas simples.

f.

Mostrar los libros que inicien con la letra “E” y tienen más de 100 hojas SELECT titulo,cantidadPaginas FROM tblLibro WHERE titulo LIKE 'E%' AND cantidadPaginas >100 ORDER BY cantidadPaginas


Pag. 63


Como podemos apreciar, también podemos utilizar AND y OR (similar a Visual Basic .Net) en condiciones indicadas en WHERE

g. Mostrar los libros que no tienen resumen. SELECT * FROM tblLibro WHERE resumen IS NULL ORDER BY titulo

NULL indica vacío, y si queremos hacer referencia al contenido NULL debemos utilizar IS y no el signo =. h. Mostrar los libros cuya cantidad de hojas se encuentre en el rango comprendido entre 10 y 100.

Este ejercicio se puede solucionar de 2 maneras distint as: Primera forma: SELECT * FROM tblLibro WHERE cantidadPaginas >=10 AND cantidadPaginas <=100 ORDER BY titulo

Segunda forma:

Utilizando BETWEEN que en español quiere decir entre. SELECT * FROM tblLibro WHERE cantidadPaginas BETWEEN 10 AND 100 ORDER BY titulo

Como podemos apreciar, una consulta se puede resolver de varias maneras, pero siempre debemos de tratar de usar aquella forma que sea la más eficiente. Abordaremos las sentencias de tipo DDL en la siguiente sesión, mientras que las sentencias INSERT, DELETE y UPDATE las abordaremos más adelante.


Pag. 64


Creación de objetos de base de datos con TransactSQL

SESIÓN

10


Aplicar técnicas alternativas de creación de objetos de base de datos. Identificar las ventajas y desventajas de la creación de objetos mediante interfaz gráfica y código puro. Temas:

Creación de tablas Modificar la estructura de una tabla Ejercicio


Pag. 65


Creación de tablas Ahora vamos a aplicar las sentencias DML para crear una la base de datos biblioteca con la tabla tblLibro utilizados en la sesión anterior. Veamos los detalles:

Figura N° 65

Si ya cuenta con una base de datos con el mismo nombre, primero debe eliminarla para obtener el resultado correcto.

Las principales sentencias DML son: CREATE

Se usa para crear objetos, como bases de datos, tablas, usuarios, procedimientos almacenados, etc.

ALTER

Modifica la estructura de un objeto de base de datos.

DROP

Elimina un objeto de la base de datos. Tabla N° 7

Vamos a utilizar CREATE para crear la base de datos Biblioteca, iniciamos haciendo click en el botón New Query ubicado en la barra de herramientas y escribimos la siguiente línea: CREATE DATABASE BIBLIOTECA

Luego de presionar F5, notaremos que el Object Explorer muestra una base de datos con el nombre biblioteca.


Pag. 66


Figura N° 66

Si no se mostrara la base de datos debemos hacer un click derecho en la carpeta Databases y luego seleccionar la opción Refresh. De esta manera actualizamos el contenido de la carpeta.

Ahora procederemos a crear la tabla libro, para ello vamos a escribir el siguiente script: USE BIBLIOTECA CREATE TABLE tblLibro ( codigo titulo autor cantidadPaginas fechaDePublicacion resumen editorial fechaDeInscripcion )

VARCHAR(10) VARCHAR(250) VARCHAR(250) SMALLINT DATE VARCHAR(500) VARCHAR(20) DATETIME

PRIMARY KEY, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NULL, NOT NULL, NOT NULL

Use se utiliza para indicar la base de datos con la que se desea trabajar, en este caso se supone que vamos a crear la tabla tblLibro dentro de la base de datos Biblioteca , es por eso que agregamos USE Biblioteca antes de iniciar con la creación de la misma. Ejecutamos el script con F5 y luego visualizamos la carpeta tables dentro de la base de datos biblioteca que creamos. Debemos entender que la mayoría de acciones que podemos realizar vía interfaz gráfica, también lo podemos desarrollar utilizando T-SQL. La ventaja de tener el script de creación de la base de datos es que es un archivo portable con un tamaño bastante pequeño. Ahora vamos a unificar en un solo script la creación de base de datos y la creación de la tabla respectiva, lo único que vamos a cambiar es el nombre de la base de datos de Biblioteca a


Pag. 67


Biblioteca2, esto con el propósito de que el script no cause errores, ya que no pueden existir

2 bases de datos con el mismo nombre. Abrimos una nueva ventana de consulta y escribimos lo siguiente: CREATE DATABASE BIBLIOTECA2 GO USE BIBLIOTECA2 CREATE TABLE tblLibro ( codigo VARCHAR(10) titulo VARCHAR(250) autor VARCHAR(250) cantidadPaginas SMALLINT fechaDePublicacion DATE resumen VARCHAR(500) editorial VARCHAR(20) fechaDeInscripcion DATETIME )

PRIMARY KEY, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NULL, NOT NULL, NOT NULL

La única diferencia con los scripts anteriores que se ha agregado un GO luego de crear la base de datos, ¿Por qué? Simplemente para ejecutar el script por partes, es decir, primero creamos la base de datos y luego creamos la tabla correspondiente al libro.

Modificar la estructura de una tabla Si lo que deseamos es agregar una nueva columna a nuestra tabla utilizando T-SQL, podemos hacerlo aplicando ALTER. Por ejemplo, si queremos agregar a la tabla tblLibro de la base de datos Biblioteca2 una nueva columna llamada observaciones con tipo de dato varchar(500) y que sea opcional, podemos escribir el siguiente script: ALTER TABLE tblLibro ADD observaciones varchar(500) NULL

Presionamos F5 para ejecutar el script y si luego realizamos un SELECT * FROM tblLibro notaremos que efectivamente se ha agregado una nueva columna a nuestra tabla.

Figura N° 67

Recordemos que es muy importante realizar el modelo lógico de la mejor manera para así no tener que realizar cambios en el modelo físico.

Si lo que queremos es modificar el tipo de dato de una columna podemos hacerlo de la siguiente manera: ALTER TABLE tblLibro ALTER COLUMN observaciones varchar(50) NULL


Pag. 68


En la línea anterior, se cambia la longitud de la columna observaciones, de 500 a 50. Por último, si deseamos eliminar una columna debemos utilizar DROP. La siguiente línea elimina la columna observaciones agregada recientemente. ALTER TABLE tblLibro DROP COLUMN observaciones

Ejercicio A manera de práctica, incluya dentro de la base de datos biblioteca una tabla con el nombre tblAsistente con los siguientes atributos: Columna

Tipo de dato

Opcional

DNI nombreCompleto edad fechaDeNacimiento sexo ocupación observaciones

varchar(8) varchar(150) tinyint date char(1) varchar(40) varchar(250)

NO además es llave principal NO NO NO NO NO SI Tabla N° 8


Pag. 69


SESIÓN

Consultas (Parte I)

11


Crear adecuadamente una base de datos. Aplicar buenas prácticas de implementación de base de datos de acuerdo al escenario empresarial. Temas: Ejercicios resueltos


Pag. 70


Ejercicios resueltos Para aplicar lo que hemos aprendido hasta el momento vamos a realizar los siguientes ejercicios: Primera parte

Crear una base de datos con el nombre Bodega y luego crear 2 tablas (Categoría y Producto) tal como se indica en el siguiente gráfico:

Figura N° 68

Debemos considerar que para realizar una relación entre tablas no es necesario que los campos por los cuales se quiera aplicar la relación utilicen el mismo nombre, el nombre puede variar, pero el tipo de dato tiene que ser el mismo.

Luego de crear las respectivas tablas, vamos a ingresar por lo menos 4 categorías y luego 10 productos como mínimo. Esto con el fin de que las consultas que realicemos nos retornen datos. Otra manera de crear la misma base de datos con las mismas tablas es utilizando el siguiente script: CREATE DATABASE BODEGA GO USE BODEGA CREATE TABLE tblCategoria ( nombre VARCHAR(20) PRIMARY KEY, descripcion VARCHAR(500) NULL ) GO CREATE TABLE tblProducto ( serie VARCHAR(15) PRIMARY KEY, nombre VARCHAR(100) NOT NULL, precioVenta SMALLMONEY NOT NULL, precioCompra SMALLMONEY NOT NULL, stockActual TINYINT NOT NULL,


Pag. 71

Programa: Programador .NET stockMinimo TINYINT NOT NULL, descripcion VARCHAR(500) NULL, observaciones VARCHAR(500) NULL, categoria VARCHAR(20) NOT NULL ) GO ALTER TABLE tblProducto ADD CONSTRAINT FK_tblCategoria_tblProducto FOREIGN KEY (categoria) REFERENCES tblCategoria

Segunda parte

Ahora vamos a realizar los siguientes ejercicios de consultas: a. Mostrar los productos cuyo nombre incluya la palabra “chi” en su nombre, y su precio de compra sea mayor a 20. SELECT * FROM tblProducto WHERE nombre LIKE '%chi%' AND precioCompra > 20

b. Mostrar el número de serie y el nombre de los 5 productos con el precio de venta más elevado. SELECT TOP 5 serie, nombre FROM tblProducto ORDER BY precioVenta DESC

TOP se utiliza para establecer límites de filas devueltas, en el ejemplo anterior nos

solicitan los 5 productos más caros, por lo tanto ordenando los productos de manera descendente por precio, y recuperando los 5 primeros de esa lista, obtendremos el resultado deseado. c. Mostrar las categorías que incluyan una descripción, muestre el resultado ordenado por nombre SELECT nombre,descripcion FROM tblCategoria WHERE NOT descripcion IS NULL ORDER BY 1

En ORDER BY no solamente se puede especificar el nombre de los campos que queremos que participen en el ordenamiento de resultados, sino también se puede incluir números, en este caso si lo que queremos es ordenar por nombre, podemos indicar 1 que hace referencia a la primera columna especificada en SELECT. Por otro lado el operador NOT (que también funciona en Visual Basic .NET) sirve para negar una condición, en este caso la condición (descripcion

IS NULL)

indica que la descripción es NULA, pero si agregamos un NOT al inicio se niega la condición. d. Mostrar la cantidad de productos cuyo stock actual sea inferior a 20. Modulo: Implementación y Administración de Bases de Datos

Pag. 72


En este caso el ejercicio nos solicita una CANTIDAD escalar, lo que debemos hacer es contar la cantidad de productos cuyo stock actual sea inferior a 20, para ello utilizaremos COUNT, que viene a ser una función de agregado y se utiliza para contar la cantidad de filas de una determinada columna. SELECT COUNT(SERIE) FROM tblProducto WHERE stockActual <20

Recordemos que COUNT omite las filas con contenido NULL

Notemos que el resultado nuestra la cantidad de fila correctamente, pero no tiene un título asignado, para establecer un título personalizado simplemente se escribe el titulo luego del nombre del campo. SELECT COUNT(SERIE) TotalProductos FROM tblProducto WHERE stockActual <20

De esta manera el resultado se muestra con un títlo de columna personalizado. e. Mostrar los nombres de los productos, con su respectivo nombre de categoría y descripción. Para esta consulta tenemos que seleccionar campos de 2 tablas diferentes, es decir, de la tabla producto debemos seleccionar nombre (de producto) y categoría (nombre de la categoría), y de la tabla categoría debemos seleccionar el campo descripción. Cuando debemos realizar consultas con múltiples tablas se utiliza INNER JOIN para enlazar las tablas y ON para especificar el campo enlace entre las tablas, veamos la solución: SELECT tblProducto .nombre,tblProducto.categoria,tblcategoria .descrip cion FROM tblProducto INNER JOIN tblCategoria ON tblproducto .categoria = tblCategoria .nombre ORDER BY tblProducto .nombre

Como podemos apreciar, en la sección de FROM utilizamos INNER JOIN para indicar que vamos a utilizar más de una tabla para la consulta, luego debemos especificar a través de ON cual es el campo de cada tabla por el cual deseamos realizar la comunicación, generalmente se usa los campos involucrados en la relación (para este caso nombre de tblCategoria y categoría de tblProducto).


Pag. 73


Observemos adicionalmente que SELECT muestra los nombres de las columnas indicando en forma de prefijo las tablas a las que pertenecen, esto es recomendable cuando existen campos con el mismo nombre en las tablas que se quiere comunicar, para el ejemplo el campo nombre se repite tanto en la tabla tblProducto

como

en tblCategoria, es por eso que se le asigno tblproducto.nombre para indicar que queremos obtener el nombre de la tabla tblproducto. Sin embargo, como es el único campo que se repite, pudimos haber omitido el prefijo para la columna categoría ya que no se repite en ambas tablas. SELECT tblProducto.nombre,categoria,tblcategoria .descripcion FROM tblProducto INNER JOIN tblCategoria ON tblproducto .categoria = tblCategoria .nombre ORDER BY tblProducto .nombre


Pag. 74


SESIÓN

Ejercicios

12


Elaborar consultas para obtener información en base a ciertos parámetros. Temas:



Pag. 75


Ejercicios propuestos Primera parte

Escriba un script utilizando T-SQL para crear una base de datos con el nombre ControlAsistencia con las siguientes tablas:

Figura N° 69

Cree la base de datos por medio de interfaz gráfica y a la vez genere un script que realice el mismo trabajo.

Segunda parte

Elabore las siguientes consultas: 

Mostrar los nombres, apellidos y DNI de los docentes cuyo horario culmine antes del mediodía.



Mostrar los nombres y apellidos (en una sola columna) de los docentes que trabajan el día martes.



Mostrar el promedio de edades de los docentes solteros.



Mostrar los nombres, apellidos y DNI de los docentes varones y cuyo deporte favorito sea el vóley.



Mostrar los horarios con su respectiva descripción iniciando desde aquel que culmina más tarde.


Pag. 76


Mostrar la cantidad de números celulares que inician con 954 o 964.



Mostrar los nombres y apellidos de los docentes casados y que laboren hasta más de las 10 de la noche.



Mostrar los nombres, apellidos, DNI y fecha de nacimiento de los docentes menores de 25 años y que sean casados.



Mostrar los nombres, apellidos, fecha de nacimiento y edad de los docentes que sean menores de 30 años y laboren los días viernes y sábado.



Mostrar los docentes que no tienen teléfono móvil y que su número de teléfono fijo inicie con 2.



Mostrar el porcentaje de docentes varones y damas.



Mostrar los nombres, apellidos y día de nacimiento de aquellos docentes que cumplan años en el mes actual.



Mostrar los nombres y apellidos de los docentes que sean hermanos, el resultado debe listarse ordenado por apellido paterno y materno.


Pag. 77


SESIÓN

Consultas (Parte II)

13


Elaborar consultas que muestren información resumida. Aplicar subconsultas para simplificar consultas complejas. Temas:

Cláusula GROUP BY Ejercicios resueltos Subconsultas


Pag. 78


Cláusula GROUP BY Se utiliza para agrupar filas y mostrar información resumida de acuerdo a funciones de agregado que podemos incluir en la lista de columnas presentadas en SELECT. Debemos comprender que GROUP BY generalmente va acompañado de una función de agregado como COUNT, SUM, AVG, MAX, MIN..

Vamos a crear una base de datos con el nombre Ventas y vamos a crear una tabla con el nombre tblProducto donde agregaremos los siguientes atributos: Columna

Tipo de dato

Opcional

codigo nombre precio stock Descripción categoría

varchar(10) varchar(100) smallmoney tinyint varchar(250) tinyint

Llave principal NO NO NO SI NO Tabla N° 9

Adicionalmente vamos a agregar una tabla con el nombre tblCategoria con los siguientes atributos: Columna

Tipo de dato

Opcional

codigo nombre

tinyint varchar(150)

Llave principal NO Tabla N° 9

Esta vez vamos a hacer que el código de la categoría se genere de manera automática conforme vamos ingresando registros a dicha tabla, en términos técnicos, vamos a hacer que código sea un campo identificado. Para hacer el campo identificado vamos a seleccionar el campo y en las propiedades del mismo vamos a ubicar la propiedad Identity Specification y cambiaremos su valor a Yes, en la parte inferior debemos especificar el valor inicial (Identity Seed) y el incremento (Identity Increment) , estableceremos los valores 100 y 1 respectivamente que indica que el valor inicial para el código de la primera categoría será 100 y el siguiente 101, luego 102 y así sucesivamente.


Pag. 79


Figura N° 70

Luego debemos relacionar ambas tablas tal y como muestra la siguiente figura:

Figura N° 71

Anteriormente creamos la base de datos y las tablas utilizando la interfaz gráfica, si queremos hacer el mismo procedimiento con un script con código T-SQL podemos escribir lo siguiente: CREATE DATABASE Ventas GO USE VENTAS CREATE TABLE tblProducto ( codigo VARCHAR(10) nombre VARCHAR(100) precio SMALLMONEY stock TINYINT descripcion VARCHAR(250) categoria TINYINT ) GO

PRIMARY KEY, NOT NULL, NOT NULL, NOT NULL, NULL, NOT NULL


Pag. 80

Programa: Programador .NET CREATE TABLE tblCategoria ( codigo TINYINT PRIMARY KEY IDENTITY(100,1), nombre VARCHAR(150) NOT NULL ) GO ALTER TABLE tblProducto ADD CONSTRAINT FK_tblCategoria_tblProducto FOREIGN KEY (categoria) REFERENCES tblCategoria

Bien, ahora ingresaremos datos en la tabla tblCategoria, recordemos que el código es identificado, es decir que se genera automáticamente, lo que indica que únicamente debemos ingresar el nombre de la categoría .

Figura N° 72

El siguiente paso es ingresar algunos productos, tratemos de que sean productos de diversas categorías.

Figura N° 73

Listo, ahora tenemos datos de prueba disponibles para realizar algunas consultas resumen.

Ejercicios resueltos A continuación se muestran algunos ejercicios resueltos utilizando GROUP BY. 

Mostrar la cantidad de productos que existen por categoría. Para resolver esta consulta primero mostraremos todas las categorías con sus respectivos nombres de productos.


Pag. 81

Programa: Programador .NET SELECT tblCategoria.nombre,tblProducto.nombre FROM tblCategoria INNER JOIN tblproducto ON tblCategoria.codigo = tblProducto.categoria ORDER BY tblCategoria.nombre

Observemos los resultados, la categoría con el nombre Gaseosas tiene 2 productos, la categoría Lácteos tiene 3 productos y la categoría Embutidos solamente 1 producto.

Figura N° 74

Para conseguir ese resultado tenemos que agrupar categorías y contar productos, entonces utilizaremos GROUP BY en combinación con COUNT. SELECT tblCategoria.nombre,COUNT(tblProducto.nombre) FROM tblCategoria INNER JOIN tblproducto ON tblCategoria.codigo = tblProducto.categoria GROUP BY tblCategoria.nombre ORDER BY tblCategoria.nombre

Figura N° 75

Adicionalmente, podemos asignar un nombre adecuado a la columna que muestra las cantidades por categoría. SELECT tblCategoria.nombre,COUNT(tblProducto.nombre) Total FROM tblCategoria INNER JOIN tblproducto ON tblCategoria.codigo = tblProducto.categoria GROUP BY tblCategoria.nombre ORDER BY tblCategoria.nombre

El resultado final es el siguiente:

Figura N° 76


Pag. 82


Mostrar el precio más elevado por categoría. Éste ejercicio es similar al anterior, con la diferencia de que en vez de utilizar un conteo, vamos a utilizar MAX , que determina el valor mayor de un conjunto de valores (lo contrario a MIN que retorna el menor valor). SELECT tblCategoria.nombre,MAX(tblProducto.precio) PrecioMayor FROM tblCategoria INNER JOIN tblproducto ON tblCategoria.codigo = tblProducto.categoria GROUP BY tblCategoria.nombre ORDER BY tblCategoria.nombre

Veamos el resultado:

Figura N° 77 

Mostrar la cantidad de productos que existen por categoría, pero únicamente incluir aquellos que superen las 10 unidades. Tenemos el mismo enunciado que el primer ejercicio, con la diferencia que esta vez nos solicitan mostrar únicamente aquellas categorías que superen 2 unidades. Para establecer condiciones a las funciones de agregado que trabajan con GROUP BY, se utiliza HAVING. SELECT tblCategoria.nombre,COUNT(tblProducto.precio) Total FROM tblCategoria INNER JOIN tblproducto ON tblCategoria.codigo = tblProducto.categoria GROUP BY tblCategoria.nombre HAVING COUNT(tblProducto.precio)>2 ORDER BY tblCategoria.nombre

Figura N° 78

Recordemos que WHERE se usa para expresar condiciones de los campos o columnas que se ubican luego de SELECT, pero HAVING aplica condiciones a las funciones de agregado que trabajan con GROUP BY.

Adicionalmente recordemos que HAVING debe ubicarse luego de GROUP BY, algo similar a lo que sucede con WHERE y SELECT.


Pag. 83


Mostrar los nombres de las categorías incluyen el precio promedio de los productos que contiene, así como también la suma de todos ellos. Cuando utilizamos GROUP BY podemos incluir más de una función de agregado, en el enunciado del ejercicio nos solicitan el promedio (calculado con AVG) y la suma (calculado con SUM). SELECT tblCategoria.nombre,AVG(tblProducto.precio) PrecioPromedio,SUM(tblProducto.precio) SumaTotal FROM tblCategoria INNER JOIN tblproducto ON tblCategoria.codigo = tblProducto.categoria GROUP BY tblCategoria.nombre ORDER BY tblCategoria.nombre

Figura N° 79

Subconsultas Una subconsulta es una consulta dentro de otra consulta. Se pueden utilizar para simplificar consultas complejas y mejorar la lectura del código T-SQL. Veamos un ejemplo: 

Mostrar los productos cuyo precio supere el precio promedio general de todos los productos. En este caso podemos utilizar AVG para calcular el precio promedio de todos los productos. SELECT AVG(precio) FROM tblProducto

Figura N° 80

Si queremos mostrar los productos que superen el precio mostrado anteriormente, simplemente encerramos la consulta anterior entre paréntesis. SELECT nombre,precio,stock FROM tblProducto WHERE precio > (SELECT AVG(precio) FROM tblProducto ) ORDER BY nombre


Pag. 84


Figura N° 81


Pag. 85


SESIÓN

Vistas

14


Crear vistas para agrupar información o resultados parciales. Identificar las ventajas del uso de vistas. Temas:

Vistas Ejercicios resueltos


Pag. 86


Vistas Cuando realizamos consultas a diario a una base de datos, es común que reutilicemos algún código T-SQL que hemos elaborado anteriormente. Una solución para no escribir el código nuevamente sería guardando el script y si se desea utilizar luego simplemente abrir el archivo y ejecutarlo nuevamente. Sin embargo, existe una manera más eficiente de realizar dicha tarea, la respuesta es utilizando vistas, estas nos permiten guardar código T-SQL que podemos reutilizar más adelante, lo interesante de esto es que una vista se comporta como tabla , lo que quiere decir que se le puede realizar consultas a una vista utilizando SELECT, FROM, WHERE, TOP, funciones de agregado, etc. De esta manera podemos reducir la complejidad de una consulta que requiera de varias tablas. Para crear una vista con código, podemos utilizar CREATE VIEW. Vamos a trabajar con la base de datos Ventas creada en la sesión anterior, y vamos a escribir el siguiente código T-SQL sobre la base de datos mencionada. CREATE VIEW vw_Lista_Productos AS SELECT codigo,nombre,precio,stock,descripcion,categoria FROM tblproducto WHERE stock >10

La vista almacena todos los productos cuyo stock supere las 10 unidades, una vez que ejecutamos el código anterior, se mostrará el siguiente mensaje:

Figura N° 82

Vamos a verificar si se creó correctamente la vista, para ello expandimos la carpeta Views de nuestra base de datos.

Figura N° 83


Pag. 87


Crearemos una nueva consulta donde usaremos SELECT aplicado a una vista, escribimos las siguientes líneas y las ejecutamos. SELECT * FROM vw_Lista_Productos ORDER BY nombre

Ahora observemos el resultado:

Figura N° 84

Como podemos apreciar, una vista tiene la característica de comportarse como tabla, incluso se pueden crear vistas basadas en otras vistas .

Otra alternativa para crear vistas

Ahora vamos a crear vistas utilizando una herramienta gráfica denominada Query Builder, el cual nos permitirá de una manera sencilla crear vistas simplemente haciendo clicks. Para ello hacemos click derecho en la carpeta Views ubicada en nuestra base de datos, luego seleccionamos la opción New View.

Figura N° 85

Se mostrará el Query Builder que nos brindará una manera diferente de crear vistas, lo primero que haremos es agregar tablas para nuestra vista. Modulo: Implementación y Administración de Bases de Datos

Pag. 88


Figura N° 86

Seleccionamos ambas tablas y hacemos click en el botón Add

Figura N° 87

Como podemos apreciar, automáticamente Microsoft SQL Server 2012 detecta que ambas tablas están relacionadas. El siguiente paso es seleccionar los campos que deseamos incluir en


Pag. 89


nuestra consulta, para ello nos ubicamos en el segundo panel y empezamos a seleccionar las columnas que deseamos, en este cas el nombre del producto, nombre de la categoría, precio y stock.

Figura N° 88

Vamos a especificar la condición que filtre los productos con un stock mayor a 10 unidades y un criterio de ordenamiento ascendente en el mismo campo.

Figura N° 89


Pag. 90


Veamos adicionalmente que mientras vamos modificando el segundo panel, automáticamente se actualiza la consulta mostrada en el tercer panel.

Figura N° 90

Para obtener una vista previa presionamos el botón con signo de admiración ubicado en la barra de herramientas ubicada parte superior del programa.

Figura N° 91

Inmediatamente después se mostrará los resultados parciales de la vista.

Figura N° 92

Finalmente grabamos la vista a través del botón guardar de la barra de herramientas, y le asignamos un nombre.

Figura N° 93

Listo, ahora ya tenemos la vista creada de manera gráfica, para poder verificarlo desplegamos la carpeta Views de nuestra base de datos (en caso de que no se visualice, debemos hacer una actualización de la carpeta). Podemos crear vistas utilizando código T-SQL o también por medio del Query Builder, en ambos casos el resultado es el mismo.


Pag. 91


SESIÓN

Procedimientos Almacenados

12


Implementar procedimientos almacenados para empaquetar código T-SQL de manera eficiente. Comprender las ventajas de utilizar procedimientos almacenados a nivel de base de datos e identificar su relación con los sistemas de información. Temas:

Procedimientos almacenados Parámetros Procedimientos almacenados y vistas


Pag. 92


Procedimientos almacenados Un procedimiento almacenado nos brinda la posibilidad de empaquetar código T-SQL para ser reutilizado posteriormente, es muy similar a los procedimientos que abordamos en el primer paquete del presente módulo Fundamentos de Programación. La gran ventaja de utilizar procedimientos almacenados es que se pre compilan en servidor, de esta manera, a partir de la segunda vez que son invocados su funcionamiento es más veloz. Es por ello que cuando se desarrolla software generalmente se utilizan procedimientos almacenados para acceder a la base de datos. Vamos a crear un procedimiento almacenado que permita mostrar aquellos productos cuyo stock sea menor a 10 unidades, para ello vamos a escribir las siguientes líneas de código TSQL: CREATE PROCEDURE usp_Listar_Productos AS SELECT codigo,nombre,precio,stock,descripcion,categoria FROM tblproducto WHERE stock<10 ORDER BY stock DESC

Presionamos F5 y el procedimiento almacenado estará creado, podemos verificarlo desplegando la carpeta Stored Procedures ubicada en la carpeta Programmability de nuestra base de datos Ventas.

Figura N° 94

Para llamar al procedimiento almacenado que acabamos de crear, simplemente utilizamos su nombre directamente. Abrimos una nueva ventana de consulta y escribimos lo siguiente: usp_Listar_Productos

Ejecutamos el código e inmediatamente el procedimiento almacenado nos muestra los productos cuyo stock sea menor a 10 unidades.


Pag. 93


Figura N° 95

Generalmente a los procedimientos almacenados que crea el usuario se les otorga un prefijo con las letras “usp” que proviene de User Stored Procedure

Parámetros En muchas ocasiones un procedimiento almacenado requiere datos para cumplir su objetivo, estos datos reciben el nombre de parámetros. Los parámetros se especifican luego de indicar el nombre del procedimiento almacenado, veamos algunos ejemplos: Procedimiento almacenado de inserción de datos

Vamos a crear un procedimiento almacenado que permite insertar datos en tblProducto, para ello utilizaremos INSERT INTO el cual permite insertar datos en una tabla, abrimos una nueva ventana de consulta y escribimos las siguientes líneas de código: CREATE PROCEDURE usp_Insertar_Producto @codigo VARCHAR(10),@nombre VARCHAR(100),@precio SMALLMONEY,@stock TINYINT, @descripcion VARCHAR(250),@categoria TINYINT AS INSERT INTO tblProducto (codigo,nombre,precio,stock,descripcion,categoria) VALUES (@codigo ,@nombre ,@precio ,@stock ,@descripcion ,@categoria )

Ejecutamos el código anterior y ya tenemos creado el procedimiento almacenado para insertar en tblProducto. Debemos comprender que luego de escribir INSERT INTO se especifica el nombre de la tabla y luego entre paréntesis se debe indicar los campos en donde queremos

insertar. A través de VALUES especificamos los valores que vamos a insertar a cada campo indicado luego del nombre de la tabla. Recordemos que se debe respetar el orden entre los campos y los parámetros que se especifican en INSERT INTO, observemos la siguiente imagen que pertenece al código del procedimiento almacenado creado anteriormente:

Figura N° 96


Pag. 94


Aunque también pudimos haberlo realizado de la siguiente manera:

Figura N° 97

En conclusión, no importa el orden de los campos de la tabla, sino importa el orden que se indica luego de especificar el nombre de la tabla. Para probar nuestro procedimiento almacenado vamos a abrir una nueva ventana de consulta e invocamos a usp_Insertar_Producto seguido de los parámetros que necesita, los parámetros que son de tipo numérico se incluyen normalmente, mientras que los parámetros de tipo cadena se deben indicar entre comillas simples. usp_Insertar_Producto 'PROD07','Pepsi Personal',1.20,17,'Pepsi personal',100

Ejecutamos la línea de código anterior y se nos muestra el siguiente mensaje:

Figura N° 98

El mensaje indica que una fila fue afectada, en este caso indica que se insertó una nueva fila. Para verificar el resultado podemos realizar una consulta a la tabla tblProducto: SELECT * FROM tblProducto

E inmediatamente visualizaremos todos los productos registrados.

Figura N° 99

Procedimiento almacenado para eliminar registros

Ahora vamos a crear un procedimiento almacenado que permita eliminar un producto. Generalmente cuando se elimina datos se realiza por medio de la llave primaria, ya que es el campo que identifica a un registro específico.


Pag. 95


Vamos a escribir las siguientes líneas de código: CREATE PROCEDURE usp_Eliminar_Producto @codigoProducto VARCHAR(10) AS DELETE FROM tblproducto WHERE codigo=@codigoProducto

Ejecutamos el código anterior, y luego en una nueva ventana de consulta invocamos a nuestro procedimiento almacenado con el código del producto que queremos eliminar. usp_Eliminar_Producto 'PROD07'

Ahora hacemos una consulta a la tabla tblProducto y observemos el resultado:

Figura N° 100

Toda variable dentro de T-SQL se crea utilizando “@” al inicio de la misma.

Procedimientos almacenados y vistas Si deseamos mostrar una lista de productos registrados en tblProducto, podemos usar una vista o un procedimiento almacenado. Sin embargo, tenemos que recordar que una vista tiene la capacidad de comportarse como tabla mientras que un procedimiento almacenado no. Por otro lado un procedimiento almacenado puede recibir parámetros y una vista no. En conclusión, para utilizar una vista o un procedimiento almacenado debemos analizar muy bien el escenario que estamos abordando para aplicar la solución más eficiente.


Pag. 96


SESIÓN

Funciones

16


Crear funciones personalizadas para agrupar tareas comunes. Complementar procedimientos almacenados con funciones. Temas:

Funciones personalizadas


Pag. 97


Funciones personalizadas Una función permite agrupar un fragmento de código T-SQL, es ese sentido es similar a un procedimiento almacenado, la diferencia es que una función retorna un valor o varios valores.

En este tema abordaremos principalmente 3 tipos de funciones: o

Funciones escalares

Una función escalar retorna un único valor que puede ser de tipo varchar, char, int, smallmoney, etc. Vamos a crear un función que convierta la primera letra de una cadena en mayúscula y el resto de letras en minúscula. CREATE FUNCTION fn_Convertir_PrimerLetraMayuscula ( @Cadena VARCHAR(100) ) RETURNS VARCHAR(100) AS BEGIN DECLARE @Resultado VARCHAR(100) SET @Resultado = UPPER(SUBSTRING(@Cadena,1,1)) + LOWER(SUBSTRING(@Cadena,2,LEN(@Cadena)-1)) RETURN @Resultado END

Ahora abrimos una nueva consulta y llamamos a la función creada, consideremos que los parámetros de una función se deben incluir entre paréntesis. PRINT dbo.fn_Convertir_PrimerLetraMayuscula('ruby')

Figura N° 101

Como podemos apreciar la función convierte el primer carácter de una cadena en mayúscula, incluso podríamos aplicarlo en un SELECT, veamos un ejempl: SELECt codigo,dbo.fn_Convertir_PrimerLetraMayuscula(nombre),precio FROM tblProducto

El resultado sería el siguiente:


Pag. 98


Figura N° 102

Debemos tener en cuenta que dentro del contenido de la función podemos utilizer SELECT, FROM, WHERE, etc, si fuera necesario. o

Funciones en línea

Este tipo de función devuelve un conjunto de registros de acuerdo a un SELECT. Vamos a crear una función que muestre los productos cuyo stock supere una cantidad determinada. CREATE FUNCTION fn_Productos_ConStock ( @stockBase TINYINT ) RETURNS TABLE AS RETURN ( SELECT codigo,nombre,precio,stock,descripcion,categoria FROm tblProducto WHERE stock>@stockBase )

Ahora invocaremos a la función con un SELECT: SELECT * FROM fn_Productos_ConStock(10)

Figura N° 103

Incluso podemos utilizar la función anterior en una consulta que involucra INNER JOIN.


Pag. 99


Funciones en línea con múltiples sentencias

Muy similar a la anterior con la diferencia de que se puede incluir múltiples sentencias de tipo SELECT.


Pag. 100


SESIÓN

Ejercicios

17


Aplicar correctamente código T-SQL para obtener información de una base de datos. Diferenciar el uso de vistas y procedimientos almacenados. Temas:



Pag. 101


Ejercicios propuestos Primera parte

Implemente la siguiente base de datos utilizando únicamente un script con código T-SQL.

Figura N° 104


Pag. 102


Segunda parte

Desarrolle los siguientes ejercicios teniendo como referencia la base de datos creada anteriormente. 

Mostrar el registro de asistencia de una persona en base a un rango de fechas determinado.



Mostrar las 5 personas que más faltaron en los últimos 5 meses.



Mostrar los horarios de ingreso y salida con su respectiva cantidad de trabajadores.



Mostrar las personas que cumplan años en el mes actual.



Mostrar las personas que laboran a partir de las 7 de la noche los días viernes y sábado.



Mostrar el registro de asistencia de un determinado día.



Mostrar todos los registros de asistencia de un determinado mes que incluyan alguna observación.



Mostrar todas las personas que incluyan una determinada palabra en sus apellidos paterno o materno.



Mostrar el registro de asistencia por cargo.



Mostrar las 10 personas más puntuales del último año.


Pag. 103


SESIÓN

Triggers

18


Implementar triggers a nivel de tablas. Reconocer las diversas ventajas de implementar triggers. Temas:

Triggers


Pag. 104


Triggers Reciben el nombre alternativo de desencadenadores, ya que su objetivo es realizar una acción cuando se produce un suceso, que generalmente es insertar, eliminar o actualizar registros en una tabla. Los triggers son muy similares a los eventos de controles que utilizamos en el primer paquete Fundamentos de programación, con la diferencia de que en vez de aplicarlos a controles, los aplicamos a tablas.

Por ejemplo, podemos utilizar un trigger para algo tan sencillo como mostrar un mensaje luego de insertar un registro en tblProducto. Abrimos una nueva ventana de consulta y escribimos el siguiente código: CREATE TRIGGER tg_Producto_Insertado ON tblProducto AFTER INSERT AS PRINT 'Ha insertado un nuevo producto'

Para observar el funcionamiento del trigger, vamos a insertar un nuevo registro utilizando INSERT INTO : INSERT INTO tblProducto VALUES ('PROD07','Inka Cola Personal',1.50,27,'Tamaño personal',100)

Ejecutamos el código e inmediatamente el panel de mensajes muestra 1 row(s) affected acompañado del mensaje que incluimos en el trigger.

Figura N° 105

Ahora vamos a crear un procedimiento almacenado que permita registrar una categoría. CREATE PROC usp_Insertar_Categoria @nombre VARCHAR(150) AS INSERT INTO tblCategoria (nombre) VALUES (@nombre)


Pag. 105


Notemos que estamos utilizando PROC en vez de PROCEDURE, en realidad es sólo una simple abreviatura. Adicionalmente debemos recordar que únicamente indicamos un parámetro que hace referencia al nombre de la categoría, ya que el código de la misma es identificado y eso hace que se genere automáticamente. Ahora vamos a crear un trigger que permite ingresar como máximo 6 registros en tblCategoria. Para ello debemos crear una variable que almacene la cantidad de registros que existen en la tabla y luego aplicar un condicional que evalúe la cantidad de registros ingresados. CREATE TRIGGER tg_ValidarCantidad_Categoria ON tblCategoria AFTER INSERT AS DECLARE @CantidadRegistros TINYINT SET @CantidadRegistros =(SELECT COUNT(codigo) FROM tblCategoria) IF @CantidadRegistros = 7 BEGIN PRINT 'No se puede insertar más de 6 categorías.' ROLLBACK END

Rollback sirve para abortar la consulta, es algo similar a un deshacer en Windows.

Para probar el trigger vamos a llamar a usp_Insertar_Categoria varias veces, pero antes de ello vamos a visualizar la cantidad de registros que tenemos en tblCategoria.

Figura N° 106

Abrimos una nueva consulta e insertamos una nueva categoría con el procedimiento almacenado creado anteriormente.

Figura N° 107

Intentamos nuevamente registrar otra categoría. usp_insertar_categoria 'Belleza'


Pag. 106


El resultado es correcto, sigamos intentando. usp_insertar_categoria 'Limpieza'

El resultado es correcto, sigamos intentando. usp_insertar_categoria 'Hogar'

Hasta ahora todos los resultados son correctos, si verificamos la cantidad de registros que tiene la tabla categoría, notaremos que ya alcanzamos 6 en total. Ahora trataremos de ingresar otro registro. usp_insertar_categoria 'Cocina'

Inmediatamente se nos muestra un mensaje de error.

Figura N° 108


Pag. 107


SESIÓN

Tablas Temporales

19


Conocer las diversas formas de almacenar datos temporales. Utilizar correctamente una tabla temporal o una variable tipo tabla de acuerdo al escenario propuesto. Temas :

Datos temporales Tablas temporales Variables tipo tabla


Pag. 108


Datos temporales En ocasiones es necesario almacenar datos de manera temporal, ya sea por una cuestión de facilidad en la consulta (por ejemplo cuando se trabaja con una consulta que involucra muchas tablas) o porque necesitamos repositorios temporales de información. Para estos escenarios, Microsoft SQL Server 2012 provee 2 tipos de objetos: Tablas temporales y Variables tipo tabla, veamos la descripción de cada uno:

Tablas temporales Una tabla temporal posee una estructura y modo de funcionamiento similar a una tabla común de una base de datos, una de las diferencias es que las tablas temporales se crean en la base de datos tempdb (una de las bases de datos del sistema).

Figura N° 109

Existen 2 tipos de tablas temporales: Tablas temporales locales

Se crean ubicando el símbolo # antes del nombre de la tabla. Debemos recordar que este tipo de tablas únicamente están disponibles para la conexión actual , lo cual indicar que 2 usuarios conectados al servidor de base de datos podrían crear cada uno una tabla temporal con el mismo nombre y no existiría conflicto ya que se están usando 2 conexiones distintas, adicionalmente debemos saber que las tablas temporales se eliminan automáticamente cuando el usuario cierra su conexión. Si queremos eliminar la tabla manualmente, podemos utilizar DROP.

Abrimos una nueva consulta y vamos a crear una tabla temporal para almacenar los datos principales de un participante: CREATE TABLE #tblParticipante (


Pag. 109

Programa: Programador .NET DNI NombreCompleto Edad

CHAR(8) CHAR( VARCHAR( VARCHAR (150) 150) TINYINT

PRIMARY KEY KEY, , NOT NULL, NOT NULL

)

Ejecutamos el script y ya tenemos creada nuestra primera tabla temporal, podemos visualizarlo en la carpeta TemporaryTables de la base de datos tempdb.

Figura N° 110

Incluso podemos realizar inserciones en la tabla temporal usando INSERT INTO : INSERT VALUES INSERT VALUES INSERT VALUES

INTO #tblParticipante ('78887760' '78887760', ,'Amy Lee', Lee',23) 23) INTO #tblParticipante ('88999987' '88999987', ,'Sabrina Castro', Castro',19) 19) INTO #tblParticipante ('88990091' '88990091', ,'Darlene Casas', Casas',22) 22)

Luego hacemos la consulta respectiva. SELECT * FROM #tblParticipante

Y obtendremos los siguientes resultados:

Figura N° 111

Otra manera de crear tablas temporales es directamente en una instrucción SELECT, por ejemplo, si queremos almacenar los productos cuyo stock supere las 10 unidades de la base de datos ventas en una tabla temporal podemos realizar lo siguiente: SELECT codigo codigo, ,nombre nombre, ,precio precio, ,stock INTO #tblProductos FROM tblproducto


Pag. 110


Inmediatamente se crea otra tabla temporal en la base de datos tempdb.

Figura N° 112

Si queremos eliminar la tabla temporal tblProductos simplemente escribimos lo siguiente: DROP TABLE #tblProductos

Si actualizamos la carpeta Temporary Tables notaremos que nuestra tabla ha sido eliminada, recordemos que si nos desconectamos totalmente de SQL Server y luego volvemos a ingresar, nuestras tablas temporales se eliminarán automáticamente. Para desconectarnos podemos utilizar el botón Disconnect ubicado en la parte superior de Object Explorer.

Figura N° 113


Pag. 111


Tablas temporales globales

En funcionamiento y estructura son similares a las tablas temporales locales, con la diferencia de que el alcance de una tabla temporal global podría cubrir a diversos usuarios conectados. Debemos tener cuidado con este tipo de tablas ya que podrían ocasionar

conflictos con tablas del mismo nombre (algo que no sucede con una tabla temporal local). Para crear tablas temporales globales simplemente utilizamos doble numeral (##) antes del nombre de la tabla, veamos un ejemplo: CREATE TABLE ##tblCargos ( nombre VARCHAR(100) PRIMARY KEY, descripcion VARCHAR(500) NOT NULL ) INSERT VALUES INSERT VALUES INSERT VALUES

INTO ##tblCargos ('GERENTE GENERAL','MANEJA LA EMPRESA') INTO ##tblCargos ('ASISTENTE DE GERENCIA','APOYO PARA LA GERENCIA GENERAL') INTO ##tblCargos ('MANTENIMIENTO','PERSONAL DE SERVICIO')

La tabla ##tblCargos puede ser accedida por cualquier usuario, es decir, tiene un alcance global. Al igual que las tablas temporales locales se pueden eliminar utilizando DROP TABLE, pero si el usuario creador de la tabla temporal global se desconecta, automáticamente se ésta eliminará.

Variables tipo tabla Las variables tipo tabla pueden ser utilizados en procedimientos almacenados, funciones o en un lote de código T-SQL, y únicamente tienen vida durante la ejecución del procedimiento almacenado, función o lote donde fueron creados. Se recomienda crear variables tipo tabla como una alternativa a las tablas temporales siempre y cuando la cantidad de datos no sea muy grande. Su creación es muy similar a una tabla temporal, con la diferencia de que se utiliza DECLARE y una arroba antes del nombre de la tabla, veamos un ejemplo: DECLARE @ListaPrecios TABLE ( precio SMALLMONEY NOT NULL ) INSERT INTO @ListaPrecios SELECT DISTINCT precio FROM tblProducto ORDER BY precio

Luego de ejecutar el código anteriormente mostrado, notaremos que la tabla @ListaPrecios recibió 5 registros.


Pag. 112


Figura N° 114

Si posteriormente queremos realizar una consulta a @ListaPrecios se nos mostrará el siguiente mensaje de error: SELECT * FROM @ListaPrecios

Figura N° 115

Esto indicar que la tabla se creó, cumplió su objetivo y luego se eliminó automáticamente, es decir, solamente tuvo vida en el bloque de código escrito anteriormente.


Pag. 113


SESIÓN

Cursores

20


Elaborar cursores para diversos propósitos. Conocer las principales ventajas del uso de cursores, así como también sus aplicaciones más comunes. Temas:

Visión general Cursores


Pag. 114


Visión General En algunas ocasiones necesitamos realizar acciones sobre los registros de una o más tablas, por ejemplo si deseamos ejecutar un procedimiento almacenado o una función sobre cada uno de los empleados cuya edad sea mayor a 25 años; para dicho propósito podemos utilizar un cursor que recorra empleado por empleado y aplique el procedimiento o función de acuerdo a la condición. Otro escenario donde se puede utilizar un cursor es al momento de realizar migración de datos, por ejemplo, en ocasiones necesitamos extraer datos con ciertos parámetros de una tabla para insertarlos en otra, el cursor nos permitiría recorrer fila a fila y así realizar una migración personalizada.

Cursores Los cursores nos permiten recorrer cada registro obtenido a partir de una consulta, con cada uno de ellos podemos realizar diversas acciones según el escenario que abordemos. A continuación se crea un cursor que traslada los nombres de los productos y su respectiva descripción a una nueva tabla llamada tblProductosSeleccionados, pero solo de aquellos que superen las 10 unidades en stock, adicionalmente, los nombres se convertirán a mayúscula y aquellos productos que no posean descripción se trasladarán con el texto “Ninguno” . Los productos registrados son los siguientes:

Figura N° 116

Primero crearemos la nueva tabla tblProductosSeleccionados: CREATE TABLE tblProductosSeleccionados ( nombre VARCHAR(100) NOT NUlL, descripcion VARCHAR(250) NULL )

Ejecutamos el código anterior para crear la nueva tabla.


Pag. 115


Figura N° 117

Ahora crearemos el cursor que nos permitirá trasladar los datos de la tabla principal: DECLARE @nombreProducto VARCHAR(100) DECLARE @descripcionProducto VARCHAR(250) DECLARE misProductos CURSOR FOR SELECT nombre,descripcion FROM tblProducto WHERE stock>10 OPEN misProductos FETCH misProductos INTO @nombreProducto,@descripcionProducto WHILE (@@FETCH_STATUS =0) BEGIN IF @descripcionProducto IS NULL INSERT INTO tblProductosSeleccionados VALUES (UPPER(@nombreProducto),'Ninguno') ELSE INSERT INTO tblProductosSeleccionados VALUES (UPPER(@nombreProducto),@descripcionProducto) FETCH misProductos INTO @nombreProducto,@descripcionProducto END CLOSE misProductos DEALLOCATE misProductos

Una vez ejecutado el código, se mostrará el siguiente mensaje:

Figura N° 118


Pag. 116


Ahora realizamos una consulta para verificar los datos: SELECT * FROM tblProductosSeleccionados

Figura N° 119

Al inicio del cursor declaramos 2 variables para almacenar el nombre y descripción de cada uno de los registros a recorrer, recordemos que T-SQL permite crear variables de manera similar a Visual Basic .Net. DECLARE @nombreProducto VARCHAR(100) DECLARE @descripcionProducto VARCHAR(250)

La creación del cursor se hace de una manera muy similar a una variable, con la diferencia de que necesitamos establecer la consulta necesaria para indicar los datos que alo jará el cursor. DECLARE misProductos CURSOR FOR SELECT nombre,descripcion FROM tblProducto WHERE stock>10

Una vez creado el cursor, debemos abrirlo para iniciar el recorrido. OPEN misProductos

La siguiente recorre el primer registro del cursor, y lo almacena dentro de ambas variables creadas al inicio. FETCH misProductos INTO @nombreProducto,@descripcionProducto

Para seguir recorriendo el cursor hasta el final utilizamos un bucle WHILE. WHILE (@@FETCH_STATUS =0) BEGIN IF @descripcionProducto IS NULL INSERT INTO tblProductosSeleccionados VALUES (UPPER(@nombreProducto),'Ninguno') ELSE INSERT INTO tblProductosSeleccionados VALUES (UPPER(@nombreProducto),@descripcionProducto) FETCH misProductos INTO @nombreProducto,@descripcionProducto END

Mientras recorremos registro a registro preguntaremos si la descripción es nula, en caso de que sea verdadero, vamos a insertar la cadena “Ninguno”, en caso contrario insertamos la

descripción normalmente.


Pag. 117

Programa: Programador .NET IF @descripcionProducto IS NULL INSERT INTO tblProductosSeleccionados VALUES (UPPER(@nombreProducto),'Ninguno') ELSE INSERT INTO tblProductosSeleccionados VALUES (UPPER(@nombreProducto),@descripcionProducto) FETCH misProductos INTO @nombreProducto,@descripcionProducto

Al final del recorrido, cerramos el cursor y liberamos los recursos. CLOSE misProductos DEALLOCATE misProductos


Pag. 118


SESIÓN

Respaldo de Información

21


Conocer y diferenciar los principales tipos de copias de seguridad que proporciona Microsoft SQL Server 2012. Temas:

Visión general Copias de seguridad


Pag. 119


Visión general La base de datos es un contenedor de información, como tal, almacena todos los datos que maneja y procesa cualquier organización. Por este motivo es que se deben de crear copias de seguridad que nos garanticen un respaldo de información en caso de que ocurra algún desastre, como por ejemplo: Un ataque de virus, problemas de hardware, desastres naturales, entre otros. Imaginemos que una empresa pierda completamente su base de datos ¿Qué sucedería?, pues simplemente la empresa perdería todo control de información y se encontraría en serios problemas. Posiblemente ha oído sobre amigos o conocidos que perdieron sus dispositivos de almacenamiento USB, muchos de ellos no consideran muy grave la pérdida del dispositivo en sí, sino de la información que mantienen dentro del dispositivo (que en realidad es lo más importante), es por ello que se recomienda realizar copias periódicas de los archivos de los dispositivos USB a nuestro computador personal. De igual manera debemos realizar copias periódicas del contenido de una base de datos para evitar pérdidas y posibles problemas.

Copias de seguridad Una copia de seguridad nos permite crear un archivo de respaldo de información de nuestra base de datos. Microsoft SQL Server 2012 permite generar 3 tipos de copias de seguridad: Copia completa

Es una copia de seguridad que involucra todos los objetos y registros de una base de datos, vamos a tomar la base de datos Ventas creada anteriormente para mostrar cómo generar una copia de este tipo.

Figura N° 120


Pag. 120


Lo primero que haremos es hacer click derecho sobre nuestra base de datos Ventas e inmediatamente seleccionamos Tasks y luego la opción Back Up.

Figura N° 121

Luego se mostrará la siguiente ventana:

Figura N° 122


Pag. 121


A través del botón Add vamos a agregar una ruta para generar la copia de seguridad.

Figura N° 123

Figura N° 124


Pag. 122


Una vez que obtenemos la Figura N° 124, ubicamos una ruta en el disco duro e ingresamos el nombre de la copia de seguridad.

Figura N° 125

Ahora presionamos OK hasta llegar a la primera ventana, y luego Microsoft SQL Server 2012 mostrará un mensaje de confirmación.

Figura N° 126


Pag. 123


Bien, ya hemos generado una copia de seguridad completa, es decir, todos las tablas, diagramas, registros, procedimientos almacenados, y otros están guardados en el archivo generado. Ahora veremos cuál es el proceso a seguir para restaurar una base de datos por medio de una copia de seguridad completa. Imaginemos que queremos restaurar la base de datos en otro equipo con Microsoft SQL Server 2012, para ello podemos trasladar la copia de seguridad generada hace un momento y seguimos los siguientes pasos: Primero hacemos un click derecho en la carpeta Databases ubicada en el Object Explorer, y seleccionamos la opción Restore Database .

Figura N° 127

En la siguiente ventana, seleccionamos la opción Device.

Figura N° 128


Pag. 124


Con el botón de los 3 puntos seleccionamos el archivo de copia de seguridad que generamos anteriormente.

Figura N° 129

Presionamos el botón OK hasta llegar a la primera ventana y Microsoft SQL Server 2012 mostrará un mensaje de confirmación.

Figura N° 130


Pag. 125


Finalmente presionamos el botón OK y obtenemos como resultado la base de datos Ventas.

Figura N° 131

Figura N° 132

Copias de seguridad adicionales Además de la copia de seguridad completa, existe la copia de seguridad diferencial y copia de seguridad del registro de transacciones, para generar alguna de estas copias de seguridad simplemente debemos cambiar la opción BackupType en la ventana de generación de copias de seguridad.


Pag. 126


Figura N° 133

Una backup diferencial únicamente guarda aquellos cambios que se realizaron desde el último backup full, uno de los objetivos de la copia de seguridad diferencial es reducir el tiempo de espera que se da al momento de generar una copia de seguridad completa (en el caso que la base de datos posea un tamaño considerable), una estrategia básica seria generar backups diferenciales en periodos cortos de tiempo cortos para salvaguardar la información de la base de datos y reducir considerablemente la pérdida de datos en caso de algún desastre. Un backup del transaction log realiza una copia de seguridad del registro de transacciones transacciones , que es aquel archivo con extensión ldf que almacena todos los cambios y operaciones que se realizaron sobre la base de datos.


Pag. 127


SESIÓN

Usuarios (Parte I)

22


Crear usuarios para aplicar políticas de seguridad comunes. Conocer la importancia de brindar un marco de seguridad adecuado para una base de datos. Temas:

Seguridad de base de datos Modos de autenticación Usuarios


Pag. 128


Seguridad de base de datos Definitivamente una base de datos contiene información de suma importancia para cualquier empresa, muy aparte de que se deben establecer políticas de copias de seguridad, también se deben establecer políticas de seguridad a nivel de acceso a la base de datos, es decir, únicamente las personas autorizadas pueden realizar consultas a la base de datos. Además debemos recordar que pueden existir jerarquías entre los usuarios, esto indica que pueden existir usuarios que puedan tener un total acceso a la base de datos (como el DBA por ejemplo), pero existen algunos que quizás deban tener permisos limitados (como practicantes o empleados que recién inician labores) para evitar cualquier fuga de información.

Modos de autenticación Microsoft SQL Server 2012 presenta 2 modos de autenticación: Autenticación Windows y Autenticación SQL Server. Podemos observar a ambos al momento de iniciar el programa.

Figura N° 134

La autenticación Windows utiliza un usuario del sistema operativo Windows para acceder a SQL Server. En un ambiente ideal, Microsoft SQL Server 2012 debe quedar instalado sobre un sistema operativo de servidor como Windows Server, en este escenario es recomendable utilizar autenticación Windows, ya que con eso delegamos la seguridad a un sistema operativo especializado como Windows Server. Por otro lado, la autenticación SQL Server utiliza usuarios creados en SQL server para acceder al programa.


Pag. 129


Usuarios Vamos a crear usuarios para diferenciar con más claridad los modos de autenticación vistos anteriormente. Lo primero que haremos es desplegar la carpeta Security en el Object Explorer y luego hacemos un click derecho en la carpeta Logins para luego seleccionar la opción New Login.

Figura N° 135

Inmediatamente Microsoft SQL Server 2012 muestra la siguiente ventana:

Figura N° 136


Pag. 130


Primero vamos a crear un usuario basado en Autenticación Windows, como este tipo de autenticación se basa en las cuentas de usuario de Windows, vamos a crear un nuevo usuario Windows con el nombre “Anita” .

Figura N° 137

Ahora volvemos a la ventana de creación de usuarios de Microsoft SQL Server 2012 y presionamos el botón Search para ubicar al usuario “Anita”.

Figura N° 138


Pag. 131


Hacemos click en el botón Aceptar y luego seleccionamos la base de datos Ventas en la sección Default Database , esto lo hacemos para que cuando se creen consultas con el usuario Anita, automáticamente se va a direccionar a la base de datos Ventas.

Figura N° 139

Ahora vamos a seleccionar la opción User Mapping

Figura N° 140

En esta ventana vamos a otorgar algunos permisos básicos para Anita en la base de datos Ventas. Vamos a habilitar la opción db_datareader que habilita el permiso de lectura sobre

los registros de la base de datos, adicionalmente vamos a marcar la opción db_denydatawriter para restringir el permiso de escritura en la base de datos. Modulo: Implementación y Administración de Bases de Datos

Pag. 132


Figura N° 141

Ahora vamos a crear un usuario en la base de datos Ventas que esté vinculado con el login Anita que acabamos de crear, para ello hacemos un click derecho en la carpeta Users ubicada

en la carpeta Security de la base de datos Ventas y seleccionamos la opción New User.

Figura N° 142


Pag. 133


En la siguiente ventana vamos a seleccionar Windows User para ubicar al usuario de Windows Anita.

Figura N° 143

A través de la sección User name ubicamos al usuario Anita.

Figura N° 144

Presionamos el botón Aceptar y luego el botón OK .

Figura N° 145


Pag. 134


Como podemos apreciar el usuario Anita de Windows ahora pertenece a la base de datos Ventas.

Cerramos sesión en Windows e ingresamos con el usuario Anita, luego de ello ingresamos a Microsoft SQL Server 2012 y notaremos que el inicio de sesión fue exitoso.

Figura N° 146

Ahora vamos a realizar una consulta de información. SELECT * FROM tblCategoria

Y apreciaremos un resultado parecido al siguiente:

Figura N° 147

Ahora intentaremos agregar una nueva categoría. INSERT INTO tblCategoria (nombre) VALUES ('Bebidas')

Y visualizaremos el siguiente mensaje de error:

Figura N° 148

Esto se da porque el permiso de escritura ha sido denegado.


Pag. 135


SESIÓN

Usuarios (Parte II)

23


Crear usuarios para aplicar políticas de seguridad comunes. Conocer la importancia de brindar un marco de seguridad adecuado para una base de datos. Temas:

Seguridad de base de datos Modos de autenticación Usuarios


Pag. 136


Ahora vamos a crear un usuario de basado en Autenticación SQL Server, para ello seguimos un proceso similar al anterior, solo que esta vez seleccionaremos SQL server authentication y en el nombre del usuario vamos a escribir July, y en la parte inferior vamos a deshabilitar la opción Enforce password policy. Esta opción activada fuerza a que se ingrese letras mayúsculas, letras minúsculas, números y símbolos en el password, sin embargo por una cuestión de facilidad vamos a quitar la marca correspondiente.

Figura N° 149

De manera similar a la sección anterior, podemos establecer permisos adicionales utilizando User Mapping.

Figura N° 150


Pag. 137


Ahora para realizar una prueba con el usuario que acabamos de crear, vamos a desconectar nuestra sesión en Microsoft SQL Server 2012 y a continuación intentamos conectar pero esta vez haciendo uso de SQL Server authenticacion.

Figura N° 151

Ingresamos las credenciales:

Figura N° 152


Pag. 138


Como podemos apreciar el ingreso resultó exitoso.

Figura N° 152

Recordemos queal igual que con los usuarios Windows, puedo asignar permisos a un usuario de Microsoft SQL Server 2012 determinado.


Pag. 139


SESIÓN

Casos

24


Aplicar los diversos conceptos y técnicas abordados durante el curso. Identificar escenarios empresariales y proponer soluciones eficientes. Temas:

Casos


Pag. 140


Casos Resuelva los siguientes casos de la manera más eficiente: Caso 1

Una base de datos consta de la siguiente tabla: tblAlumno Código

Nombres

Apellidos

AL01 AL02 AL03

Anita Susana Candy

Casas López Castro Linares Huamán Nazca

Fecha de nacimiento 05/05/1992 04/08/1985 12/11/1993

…

…

…

…

800 alumnos en total

Se solicita migrar los datos a la siguiente tabla: tblAlumnosClasificados Fecha Hora Registro

Código

Nombre completo

1 2

Anita Casas López Candy Huamán Nazca

…

…

MesAnioNacimiento 05-1992 11-1993

…

…

Notas: 

La columna “Fecha de registro” muestra la fecha y hora en que se insertó al alumno en la base de datos.



Migrar únicamente a los alumnos que nacieron luego del año 1990.

Caso 2

Resuelva de la manera más eficiente el siguiente caso: Ruby es una empresa dedicada al rubro de ventas de libros, desde hace 7 años guarda

información acerca de toda su colección de libros en una base de datos que tiene la siguiente estructura: Código

Título

Autor

Resumen

Cantidad de hojas

Editorial

Estado

Categoría

COD01

El retoño

Julián Huanay

Historia de aventura

70

Megacentro

Nuevo

Aventura

COD02

El

David

80

Lobitos

Regular

Autoayuda


Pag. 141


…

camino del líder

Fishman

…

…

…

…

…

…

…

800 libros en total

Una observación que se encontró en la tabla libro es que los títulos están escritos de manera heterogénea, es decir, algunos inician con mayúsculas, otros están escritos totalmente en mayúsculas, otros totalmente en minúsculas, etc. Hace unas semanas, Candy inició sus labores en la empresa Ruby, analizó la base de datos actual y decidió restructurarla para ofrecer una mejor performance. Su nuevo modelo incluye las siguientes tablas: 

Libro que incluye los atributos código, título, autor, resumen, cantidad de hojas, editorial,

estado (desgastado, defectuoso, regular, bueno y nuevo) y código de la categoría a la cual pertenece. Resumen Cantidad de hojas

Código

Título

Autor

COD01

El retoño

Julián Huanay

COD02

El camino del líder

David Fishman

Ninguno

80

Lobitos

…

…

…

…

…

…



Historia de aventura

70

Editorial

Estado

Megacentro

Nuevo

Fecha de registro 201302-12 07:34

Categoría 102

Regular

201302-12 07:34

100

…

…

…

Categoría que incluye los campos código (que inicia en 100 y avanza de dos en dos), nombre

y descripción. Código 100 102 104

Título Autoayuda Aventura Drama

…

…

Se le solicita crear el script de la nueva estructura de base de datos, así como también elaborar el script necesario para trasladar todos los datos la base de datos antigua a la nueva base de datos. Notas: 

No olvide migrar todos los títulos en mayúsculas.


Pag. 142

2 Manual SQL (1)

Recommend Documents