• Author / Uploaded
• Jimmy Borja Chagua

Citation preview

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 de Bases de Datos

Pag. 1

Programa: Programador .NET

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

Programa: Programador .NET 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

Posibles tablas

Universidad

Docente, alumno, carrera profesional, turno, semestre, horario, aula, asignatura, etc.

Farmacia

Medicamento, categoría, vendedor, cliente, etc.

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

Pag. 3

Programa: Programador .NET 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

Programa: Programador .NET 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.

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

Pag. 5

Programa: Programador .NET 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

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

Pag. 6

Programa: Programador .NET 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.

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

Pag. 7

Programa: Programador .NET

SESIÓN

Modelo Relacional

2

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

 

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

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

Pag. 8

Programa: Programador .NET

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.

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

Pag. 9

Programa: Programador .NET 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

Paciente

Atributos

Tipo de datos

DNI

Texto

Nombres

Texto

Apellidos

Texto

Edad

Numérico

Sexo

Texto

Número de teléfono

Texto

Estatura

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:

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

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

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

Pag. 11

Programa: Programador .NET o

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, por 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.

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

Pag. 12

Programa: Programador .NET

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.

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

Pag. 13

Programa: Programador .NET

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.

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

Pag. 14

Programa: Programador .NET

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

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

Pag. 15

Programa: Programador .NET 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

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

Pag. 16

Programa: Programador .NET 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.

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

Pag. 17

Programa: Programador .NET

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.

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

Pag. 18

Programa: Programador .NET

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.

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

Pag. 19

Programa: Programador .NET

SESIÓN

Desarrollo de caso empresarial

3

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



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

TEMAS:



Caso

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

Pag. 20

Programa: Programador .NET

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.

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

Pag. 21

Programa: Programador .NET 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

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

Pag. 22

Programa: Programador .NET 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.

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

Pag. 23

Programa: Programador .NET

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

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

Pag. 24

Programa: Programador .NET 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

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

Pag. 25

Programa: Programador .NET 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

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

Pag. 26

Programa: Programador .NET

SESIÓN

Ejercicios Propuestos

4

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

 

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

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

Pag. 27

Programa: Programador .NET

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

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

Pag. 28

Programa: Programador .NET 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.

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

Pag. 29

Programa: Programador .NET

SESIÓN

Microsoft SQL Server 2012

5

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

 

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

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

Pag. 30

Programa: Programador .NET

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.

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

Pag. 31

Programa: Programador .NET

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.

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

Pag. 32

Programa: Programador .NET 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

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

Pag. 33

Programa: Programador .NET 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.

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

Muestra todos los objetos disponibles en el servidor.

Panel propiedades

Muestra un conjunto de características del objeto seleccionado. Tabla N° 3

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

Pag. 34

Programa: Programador .NET

SESIÓN

Planificación de base de datos

6

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

 

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

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

Pag. 35

Programa: Programador .NET

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.

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

Pag. 36

Programa: Programador .NET

Figura N° 31

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

Figura N° 32

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

Pag. 37

Programa: Programador .NET 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

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

Pag. 38

Programa: Programador .NET 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 habilitar el Autogrowth, ya que si 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.

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

Pag. 39

Programa: Programador .NET 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.

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

Pag. 40

Programa: Programador .NET

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

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

Pag. 41

Programa: Programador .NET

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

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

Pag. 42

Programa: Programador .NET 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

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

Pag. 43

Programa: Programador .NET 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

922,337,203,685.477,5808

de a

dato

money

almacena

922,337,203,685.477,5807.

valores Ambos

desde 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

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

Pag. 44

Programa: Programador .NET 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.

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

Pag. 45

Programa: Programador .NET

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

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

Pag. 46

Programa: Programador .NET

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.

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

Pag. 47

Programa: Programador .NET

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.

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

Pag. 48

Programa: Programador .NET

SESIÓN

Relaciones entre Tablas

8

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

 

Identificar los tipos de relaciones entre tablas utilizando reglas del negocio. Establecer relaciones entre tablas.

Temas:

  

Visión general Identificando las relaciones Ejercicio

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

Pag. 49

Programa: Programador .NET

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

varchar(20)

Llave primaria

Nombre

varchar(100)

Precio

smallmoney

StockActual

tinyInt

StockMinimo

tinyint

CodigoCategoria

tinyint

Nombre

varchar(50)

Descripcion

varchar(250)

CodigoProveedor

varchar(20)

Nombre

varchar(150)

Representante

varchar(150)

Telefono

varchar(10)

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

Descripción

Rango

tinyint

0 a 255

smallint

-32768 a 32767

int

Números enteros

-2147483648 a 2147483647

bigint decimal Números de punto flotante

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

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

Pag. 50

Programa: Programador .NET

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

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

Pag. 51

Programa: Programador .NET 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.

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

Pag. 52

Programa: Programador .NET 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.

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

Pag. 53

Programa: Programador .NET

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.

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

Pag. 54

Programa: Programador .NET

Figura N° 55

Ahora vamos a analizar la relación entre producto y proveedor, un producto 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.

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

Pag. 55

Programa: Programador .NET

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

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

Pag. 56

Programa: Programador .NET 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.

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

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.

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

Pag. 58

Programa: Programador .NET

SESIÓN

Lenguaje Estructurado de Consulta

9

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

 

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

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

Pag. 59

Programa: Programador .NET

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:

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

Pag. 60

Programa: Programador .NET

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:

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

Pag. 61

Programa: Programador .NET 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

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

Pag. 62

Programa: Programador .NET 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

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

Pag. 63

Programa: Programador .NET 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 distintas: Primera forma: SELECT * FROM tblLibro WHERE cantidadPaginas >=10 AND cantidadPaginas 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

Programa: Programador .NET 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 (SELECT AVG(precio) FROM tblProducto ) ORDER BY nombre

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

Pag. 84

Programa: Programador .NET

Figura N° 81

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

Pag. 85

Programa: Programador .NET

SESIÓN

Vistas

14

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

 

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

Temas:

 

Vistas Ejercicios resueltos

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

Pag. 86

Programa: Programador .NET

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

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

Pag. 87

Programa: Programador .NET 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

Programa: Programador .NET

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

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

Pag. 89

Programa: Programador .NET 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

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

Pag. 90

Programa: Programador .NET 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.

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

Pag. 91

Programa: Programador .NET

SESIÓN

Procedimientos Almacenados

12

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



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

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

Pag. 92

Programa: Programador .NET

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

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

Pag. 99

Programa: Programador .NET o

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.

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

Pag. 100

Programa: Programador .NET

SESIÓN

Ejercicios

17

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

 

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

Temas:



Ejercicios propuestos

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

Pag. 101

Programa: Programador .NET

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

Figura N° 104

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

Pag. 102

Programa: Programador .NET 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.

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

Pag. 103

Programa: Programador .NET

SESIÓN

Triggers

18

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

 

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

Temas:



Triggers

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

Pag. 104

Programa: Programador .NET

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)

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

Pag. 105

Programa: Programador .NET 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'

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

Pag. 106

Programa: Programador .NET 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

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

Pag. 107

Programa: Programador .NET

SESIÓN

Tablas Temporales

19

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

 

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

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

Pag. 108

Programa: Programador .NET

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 (

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

Pag. 109

Programa: Programador .NET DNI NombreCompleto Edad

CHAR(8) VARCHAR(150) TINYINT

PRIMARY 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','Amy Lee',23) INTO #tblParticipante ('88999987','Sabrina Castro',19) INTO #tblParticipante ('88990091','Darlene Casas',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,nombre,precio,stock INTO #tblProductos FROM tblproducto

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

Pag. 110

Programa: Programador .NET 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

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

Pag. 111

Programa: Programador .NET 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.

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

Pag. 112

Programa: Programador .NET

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.

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

Pag. 113

Programa: Programador .NET

SESIÓN

Cursores

20

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

 

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

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

Pag. 114

Programa: Programador .NET

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.

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

Pag. 115

Programa: Programador .NET

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

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

Pag. 116

Programa: Programador .NET 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 alojará 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.

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

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

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

Pag. 118

Programa: Programador .NET

SESIÓN

Respaldo de Información

21

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



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

Temas:

 

Visión general Copias de seguridad

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

Pag. 119

Programa: Programador .NET

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

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

Pag. 120

Programa: Programador .NET 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

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

Pag. 121

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

Figura N° 123

Figura N° 124

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

Pag. 122

Programa: Programador .NET 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

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

Pag. 123

Programa: Programador .NET 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

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

Pag. 124

Programa: Programador .NET 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

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

Pag. 125

Programa: Programador .NET 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.

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

Pag. 126

Programa: Programador .NET

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, que es aquel archivo con extensión ldf que almacena todos los cambios y operaciones que se realizaron sobre la base de datos.

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

Pag. 127

Programa: Programador .NET

SESIÓN

Usuarios (Parte I)

22

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

 

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

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

Pag. 128

Programa: Programador .NET

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.

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

Pag. 129

Programa: Programador .NET

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

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

Pag. 130

Programa: Programador .NET 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

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

Pag. 131

Programa: Programador .NET 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

Programa: Programador .NET

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

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

Pag. 133

Programa: Programador .NET 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

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

Pag. 134

Programa: Programador .NET 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.

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

Pag. 135

Programa: Programador .NET

SESIÓN

Usuarios (Parte II)

23

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

 

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

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

Pag. 136

Programa: Programador .NET 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

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

Pag. 137

Programa: Programador .NET 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

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

Pag. 138

Programa: Programador .NET 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.

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

Pag. 139

Programa: Programador .NET

SESIÓN

Casos

24

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

 

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

Temas:



Casos

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

Pag. 140

Programa: Programador .NET

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

Anita Susana

Casas López Castro Linares

Fecha de nacimiento 05/05/1992 04/08/1985

AL03

Candy

Huamán Nazca

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

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

Pag. 141

Programa: Programador .NET 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. Fecha de registro 201302-12 07:34

Código

Título

Autor

Resumen

Cantidad de hojas

Editorial

Estado

COD01

El retoño

Julián Huanay

Historia de aventura

70

Megacentro

Nuevo

David Fishman

Ninguno

80

Lobitos

Regular

201302-12 07:34

100

…

…

…

…

…

…

…

El camino del líder …

COD02 … 

Categoría 102

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.

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

Pag. 142

Programa: Programador .NET 

En la nueva estructura de tablas propuesta por Candy, todos los libros que no tuvieron resumen inicialmente, se registra como ninguno (Observar las marcas amarillas de la parte superior).



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

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

Pag. 143