MMF by microsoft

www.pdftron.com

AM ÉRI

Microsoft® Ofﬁcial Academic Course

Fundamentos de bases de datos EXAMEN 98-364

ETC

IBE RO

www.pdftron.com

CA AM ÉRI

Créditos

EDITOR DIRECTOR DE VENTAS DIRECTOR DE MERCADOTECNIA GERENTE DE RELACIONES ESTRATÉGICAS DE MICROSOFT ASISTENTE EDITORIAL GERENTE DE PRODUCCIÓN EDITOR PRINCIPAL DE PRODUCCIÓN DIRECTOR CREATIVO DISEÑADOR DE PORTADA TECNOLOGÍA Y MEDIOS DE COMUNICACIÓN

Bryan Gambrel Mitchell Beaton Chris Ruel Merrick Van Dongen de Microsoft Learning Jennifer Lartz Micheline Frederick Kerry Weinstein Harry Nolan Jim O’Shea Tom Kulesa/Wendy Ashenberg

www.pdftron.com

IBE RO

Este libro fue escrito en Garamond por Aptara, Inc. e impreso y encuadernado por Bind Rite Graphics. La portada fue impresa por Phoenix Color.

Copyright © 2011 por John Wiley & Sons, Inc. Todos los derechos reservados. Queda prohibida la reproducción de esta publicación, el almacenamiento en un sistema de recuperación o su transmisión en alguna forma o medio, ya sea electrónico, mecánico, por fotocopia, grabación, digitalización o de otra manera, excepto de lo que permite la Sección 107 o 108 de la Ley de Derechos de autor de los Estados Unidos de 1978, sin el permiso escrito de la Editorial o sin la autorización a través del pago correspondiente de los derechos de la copia a Copyright Clearance Center, Inc. 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Las solicitudes para obtener el permiso de la Editorial deben enviarse a Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, (201) 748-6011, fax (201) 748-6008. Para ordenar libros o para obtener servicio al cliente, llame al 1-800-CALL WILEY (225-5945). Microsoft, ActiveX, Excel, InfoPath, Microsoft Press, MSDN, OneNote, Outlook, PivotChart, PivoTable, PowerPoint, SharePoint, SQL Server, Visio, VisualBasic, Visual C#, Visual Studio, Windows, Windows 7, Windows Mobile, Windows Server y Windows Vista son marcas registradas o comerciales que pertenecen a Microsoft Corporation en los Estados Unidos y/u otros países. Otros nombres de productos o compañías que aquí se mencionan pueden ser marcas registradas que pertenecen a sus respectivos propietarios.

ETC

Las compañías, organizaciones, productos, nombres de dominios, direcciones de correo electrónico, logotipos, personas, lugares y acontecimientos utilizados en los ejemplos son ﬁcticios. No se pretende indicar ni debe deducirse ninguna asociación con empresas, organizaciones, productos, nombres de dominios, direcciones de correo electrónico, logotipos, personas, lugares o acontecimientos. El libro muestra los puntos de vista y las opiniones del autor. El contenido de la información de este libro se proporciona sin ninguna garantía expresa, legal o implícita. Ninguno de los autores, ni John Wiley & Sons, Inc., ni Microsoft Corporation o sus revendedores o distribuidores serán legalmente responsables de cualquier daño que se cause o pueda causar de manera directa o indirecta por el uso de este libro.

Prólogo del Editor

AM ÉRI

La visión de Wiley para la serie de Microsoft Official Academic Course es proporcionar a los alumnos y docentes las habilidades y conocimientos que necesitan para utilizar la tecnología de Microsoft de manera eficaz en todos los aspectos de su vida personal y profesional. La enseñanza de calidad es necesaria para ayudar a docentes y alumnos a obtener lo mejor de las herramientas de software de Microsoft y para ser más productivos. Por lo tanto, nuestra misión es hacer que nuestros programas de enseñanza sean confiables compañeros educativos para toda la vida. Para lograr esta misión, Wiley y Microsoft se han asociado para desarrollar programas educativos de la más alta calidad orientados a trabajadores informáticos, profesionales en las tecnologías de la información y desarrolladores. Los materiales creados por esta asociación llevarán el nombre de “Microsoft Official Academic Course” y garantiza a docentes y alumnos que el contenido de estos libros de texto se encuentra avalado completamente por Microsoft y que en sus productos se proporciona información y enseñanza de la más alta calidad. Los libros de texto de Microsoft Official Academic Course son “Oficiales” en más de un sentido, ya que son cursos autorizados de manera oficial por miembros de la Academia IT de Microsoft. La serie de Microsoft Official Academic Course se enfoca en el desarrollo de la fuerza laboral. Estos programas van dirigidos a aquellos alumnos que buscan ser parte de la fuerza laboral, cambiar de trabajo o cursar una carrera como trabajadores de informática, profesionales en las tecnologías de la información y desarrolladores. Los programas de Microsoft Official Academic Course se ajustan a sus necesidades al hacer énfasis en situaciones auténticas de trabajo e implementando diversos proyectos, ejercicios, casos y evaluaciones.

www.pdftron.com

IBE RO

Microsoft Ofﬁcial Academic Course se encuentra asociado a la investigación exhaustiva de Microsoft y al análisis de tareas de trabajo, la misma investigación y análisis que se utilizó para crear los exámenes de Microsoft Technology Associate (MTA) y Microsoft Certiﬁed Information Technology Professional (MCITP). Los libros de texto se enfocan en habilidades reales para trabajos reales. A medida que los alumnos trabajan en los proyectos y en los ejercicios de los libros de texto, mejoran su nivel de conocimiento y su habilidad para aplicar la tecnología más reciente de Microsoft a las tareas de cada día. Estos alumnos también obtienen experiencia que pueden incluir en su currículum lo cual les puede ayudar a encontrar un trabajo, mantener el actual o complementar su educación.

Actualmente el concepto de formación continua es una gran necesidad. Los roles de trabajo e incluso todas las categorías de trabajo cambian continuamente y por ello ninguna persona puede ser competente o productiva sin actualizar continuamente sus habilidades y capacidades. Microsoft Official Academic Course ofrece y se enfoca en el examen de preparación para la certificación de Microsoft por lo cual proporciona medios para que las personas adquieran y actualicen de manera eficaz sus habilidades y conocimientos. Wiley apoya a los alumnos en este esfuerzo a través del desarrollo y distribución de estos cursos como editor académico oficial de Microsoft.

ETC

Actualmente las publicaciones educativas requieren de atención para proporcionar material impreso y contenido electrónico de buena calidad. Al integrar los productos de Microsoft Official Academic Course, WileyPLUS y las certificaciones de Microsoft, estamos mejor capacitados para ofrecer soluciones de aprendizaje eficaces para alumnos y docentes. Bonnie Lieberman Director general y Vicepresidente

Visite la página www.wiley.com/college/microsoft o llame al número gratuito de MOAC: 1+ (888) 764-7001 (Únicamente Estados Unidos y Canadá)

Prefacio

AM ÉRI

Bienvenido al programa Microsoft Ofﬁcial Academic Course (MOAC) para el curso de Fundamentos de Bases de Datos MTA. MOAC representa la colaboración entre Microsoft Learning y la compañía publicitaria John Wiley & Sons, Inc. Microsoft y Wiley se asociaron para crear una serie de libros de texto que contienen soluciones de aprendizaje innovadoras para que docentes y alumnos adquieran experiencias de aprendizaje superiores. Estos libros de texto maximizan la transferencia de habilidades en poco tiempo. Los alumnos se ven desaﬁados a lograr maximizar su potencial al utilizar sus nuevas habilidades técnicas como miembros altamente productivos de la fuerza laboral.

Debido a que esta base de conocimientos proviene directamente de Microsoft y el creador de los exámenes de Microsoft Technology Associate, Microsoft Certified IT Professional y Microsoft Certified Professional (www.microsoft.com/learning/mcp/mcitp) le garantizan que recibirá la cobertura de los temas que son más relevantes para el éxito personal y profesional de los alumnos. La participación directa de Microsoft no únicamente garantiza que el contenido de los libros de texto MOAC es preciso y actual, sino que también significa que los alumnos recibirán la mejor enseñanza posible para lograr que tengan éxito en los exámenes de certificación y en su trabajo.

www.pdftron.com

ETC

IBE RO

El Programa de Microsoft Ofﬁcial Academic Course

Prefacio

AM ÉRI

La serie de Microsoft Ofﬁcial Academic Course es un programa completo para docentes e instituciones que desean preparar e impartir cursos sobre las tecnologías del software de Microsoft. Con MOAC, reconocemos que, debido a la rápida evolución de la tecnología y al plan de estudios desarrollado por Microsoft, existen necesidades actuales que van más allá de las herramientas de enseñanza que se utilizan en el salón de clases y por ello, el docente debe estar preparado para saber cómo impartir el curso. El programa MOAC intenta proporcionar soluciones para todas esas necesidades de manera sistemática y garantizar una experiencia del curso exitosa y gratiﬁcante tanto para el docente como para el alumno; también ofrece una capacitación técnica sobre el plan de estudios para que el docente esté listo para utilizar los nuevos productos de software. El software lo pueden utilizar los alumnos en sus hogares para adquirir habilidades, evaluarlas y validar su desarrollo, asimismo, el software cuenta con una diversidad de herramientas para aplicarlas en la enseñanza en el salón de clases y laboratorio de computación. Todas las herramientas son importantes para la impartición de un curso interesante sobre el software de Microsoft y todas las proporciona el programa MOAC. Se considera que el siguiente modelo es un indicador que garantiza que nosotros lo avalamos completamente en su propósito de impartir un gran curso. A medida que evalúe las opciones de materiales educativos, deseará utilizar un modelo de comparación para todos los productos que se encuentran disponibles.

ETC

IBE RO

www.pdftron.com

Características pedagógicas

Tour ilustrado del libro

AM ÉRI

Los libros de texto MOAC para los Fundamentos de Bases de Datos se diseñaron para cubrir todos los objetivos de aprendizaje del examen MTA 98-364, en el cual se hace referencia a su “dominio de objetivos”. Los objetivos del examen de Microsoft Certified Information Technology Professional (MCITP) se encuentran destacados en los libros de texto. Muchas de las características pedagógicas se han desarrollado especialmente para los programas de Microsoft Official Academic Course. La presentación de la extensa información de procedimientos y los conceptos técnicos que se presentan en los libros de texto aumentan los desafíos tanto para alumnos como para docentes. El siguiente Tour ilustrado del libro proporciona una guía para las características multimedia que contribuyen al plan pedagógico del programa Microsoft Official Academic Course. A continuación se encuentra una lista de las características clave en cada lección que fueron diseñadas para preparar a los alumnos a que tengan éxito a medida que continúan con su educación en las tecnologías de la información, en los exámenes de certificación y en su trabajo:

www.pdftron.com

IBE RO

• Cada lección inicia con una Matriz de habilidades de la lección (Dominio de objetivos). Más que una lista de objetivos de aprendizaje, la Matriz del dominio correlaciona cada habilidad del software que se explicó en la lección con el dominio de objetivos especíﬁcos del examen. • Las clases concisas y detalladas enseñan a los alumnos nuevas características y les da la oportunidad de practicar. Existen diversos pasos detallados que ayudan a los alumnos a aprender más habilidades de software. Los pasos también muestran los resultados y las imágenes en pantalla para saber lo que los alumnos deberán ver en sus computadoras.

• Ilustraciones: Las imágenes en pantalla proporcionan una retroalimentación visual a medida que los alumnos trabajan en los ejercicios. Las imágenes refuerzan los conceptos clave, proporcionan pistas visuales sobre los pasos y le permite a los alumnos revisar su progreso. • Términos clave: El vocabulario técnico importante se encuentra en una lista al inicio de la lección. Cuando estos términos se utilizan posteriormente en la lección, aparecen con formato en itálica y negrita y se les deﬁne. El Glosario contiene todos los términos clave así como sus deﬁniciones.

ETC

• Los puntos de uso interesante en los Asistentes de lectura se encuentran en todas las lecciones y les indica a los alumnos por qué ese tema es relevante (En resumen), proporciona pistas útiles (Tome nota) o muestra diversas alternativas para realizar una tarea (Otra manera). Los Asistentes de lectura también proporcionan información relevante o una referencia que le agrega valor a la lección. • Las características de Listo para la certificación en el texto indica a los alumnos en qué parte se cubre un objetivo específico de la certificación, dándoles la oportunidad a los alumnos de verificar su entendimiento de los objetivos de ese examen en particular y, de ser necesario, proporciona un repaso a la sección de la lección en donde se cubrió el objetivo. • La evaluación del conocimiento proporciona de manera progresiva más actividades desafiantes al término de cada una de las lecciones.

Tour ilustrado del libro

Lección 1

Tabla Padre

Tabla 1-1 Diseño de base de datos jerárquica mostrando tablas padre e hijo

NumEmp 100 101 102 103

Nombre Paul Jane Jim Ed

Apellido Baker Smith Tate Rosen

NumDept 101 101 101 102

VII

Tabla Hijo

NumSerial 30032334 4323452 342342 234322

Tipo Computadora Laptop Monitor Impresora

NumEmp 100 101 100 100

AM ÉRI

La tabla padre mantiene los datos del empleado. Cada ﬁla o registro se reﬁere a la información del usuario incluyendo el número de empleado del usuario (NumEmp). La tabla hijo mantiene los datos del equipo de cómputo. La columna NumEmp enlaza a cada registro a la tabla padre. Es importante notar que cada pieza de equipo debe ser introducida separadamente. Ya que estamos utilizando una base de datos jerárquica, podemos asignar múltiples dispositivos de cómputo a cada empleado.

f Comprender las bases de datos relacionales

El último aún más importante tipo de base de datos es una base de datos relacional. Una base de datos relacional es similar a la base de datos jerárquica en la que los datos son almacenados en tablas y cualquier información nueva es añadida automáticamente en la tabla sin la necesidad de reorganizar la propia tabla. A diferencia de la base de datos jerárquica, una tabla en una base de datos relacional puede tener múltiples padres.

; Listo para la certiﬁcación

Alertas de objetivo de certiﬁcación MTA

¿Cómo son diferentes las bases de datos relacionales a las bases de datos planas y bases de datos jerárquicas? —1.2

Podría ya haber trabajado con productos de base de datos similares (tal como Access) y si es así, está familiarizado con las distintas tablas que constituyen muchos de los reportes de salida. Utilizando la información proporcionada en la Tabla 1-1, al jefe de la oficina le gustaría tener un registro de cuales empleados reciben qué tipo de equipo. Como creador de la base de datos, necesita mirar ambas tablas para recuperar la información relevante de cada tabla con el fin de compilar el reporte para el jefe de la oficina. Primero debe encontrar la clave que se encuentra en ambas tablas. Utilizando esta clave, puede filtrar la información redundante o irrelevante almacenada. Básicamente, una base de datos está hecha de muchas tablas. Justo como un libro de Excel puede estar hecho de varias hojas de cálculo – albergando miles de filas y columnas de información. Esto es el porqué es más fácil trabajar con una base de datos cuando está tratando de ir a través de varias hojas de cálculo para encontrar la información que necesita. Un ejemplo de una base de datos relacional, es mostrado en la Tabla 1-2. En la primera tabla Padre, tiene una tabla que muestra a los vendedores de una compañía. En la segunda tabla padre, tiene varios modelos que están a la venta. En la tabla hijo, tiene clientes quienes han comprado modelos de su compañía, lo cual esta enlazado a la primer tabla padre con el NumEmp y a la segunda tabla padre con el Modelo.

IBE RO

www.pdftron.com Comprender los conceptos básicos de bases de datos

• El Lenguaje de Deﬁnición de Datos (DDL) es un subconjunto del lenguaje Transact-SQL, trata con la creación de objetos de la base de datos como tablas, restricciones y procedimientos almacenados.

» Evaluación de Conocimientos

Llene el Espacio en Blanco

2. Tablas creadas utilizando la sentencia ____________ son utilizadas para almacenar información. 3. Las restricciones pueden deﬁnir relaciones de entidad entre tablas en una forma continua. También son conocidos como restricciones ____________.

4. Con el ﬁn de utilizar el objeto vistas para ver un conjunto de datos, debe utilizar la sentencia de Transact-SQL ____________ para mostrar datos de tablas subyacentes. 5. DDL manipula ____________, mientras que ____________ manipula la información almacenada en tablas.

6. ____________ son compilados. Las sentencias Transact-SQL tienen la intención de desempeñar acciones particulares y puede aceptar parámetros y retornar valores, pero ____________ siempre retorna un valor y nunca actualizan datos.

ETC

7. Las sentencias DDL centrales son ____________ , ____________ y ____________. 8. Las sentencias DML centrales son ____________ , ____________ , ____________ y ____________. 9. Las vistas del sistema pertenecen a ____________. 10. La restricción de CLAVE EXTERNA es un identiﬁcador de ____________. Elección Múltiple

Encierra en un círculo la letra que corresponda a la mejor respuesta. 1. ¿Cuál de las siguientes no es una sentencia DDL? a. b. c. d.

CREATE MERGE ALTER DROP

2. ¿Cuál de las siguientes no es una restricción de columna? a. b. c. d.

DEFAULT CHECK RANGE UNIQUE

Preguntas de evaluación de conocimiento

VIII

Comprender los conceptos básicos de bases de datos

» Evaluación de Competencias

Estudio de casos

Recién ha sido contratado como DBA (Administrador de base de datos) para una compañía internacional que es una sociedad ﬁnanciera para muchas otras compañías. Su primera tarea es diseñar una nueva infraestructura de base de datos. Por lo tanto, se le ha dicho que piense en sus actividades de las últimas semanas. Enliste al menos una base de datos que ha utilizado directamente o indirectamente y describa como la base de datos probablemente será presentada. Ejercicio 1-2: Diseñando una Base de Datos Relacional Se le ha dado una tarea de diseñar una base de datos relacional para soportar un negocio de venta de autos. Necesita almacenar información de sus empleados, el inventario que posee y las ventas hechas. Se debe notar que cada vendedor se le ha dado un porcentaje diferente de comisión. ¿Qué tablas y columnas crearía y como enlazaría las tablas?

AM ÉRI

Ejercicio 1-3: Utilizando la Ayuda de SQL Server 2008

Ejercicio 1-1: Buscando una Base de Datos Relacional

Recién se ha graduado de la escuela y fue contratado como un DBA junior. Una cosa que ha aprendido en los últimos meses es que no tendrá todas las respuestas. Por lo tanto, necesita aprender cuando y como obtener ayuda cuando sea necesario. SQL Server 2008 tiene un sistema intensivo de ayuda y ejemplos. Desde que es el nuevo DBA, le gustaría visualizar la ayuda en la sentencia CREATE de forma que pueda crear una tabla. Por lo tanto, ¿Cuáles son los pasos que utilizaría para encontrar esa información? Ejercicio 1-4: Creando bases de datos utilizando la Interfaz Gráﬁca de SSMS

Su compañía, AdventureWorks, decidió expandirse a un viaje interestelar. Por lo tanto, lo han contratado como un Nuevo administrador de base de datos para crear una nueva base de datos llamada Planets en el Microsoft SQL server utilizando la interfaz gráﬁca del SQL Server Management Service. Por lo tanto, ¿Qué pasos utilizaría para crear una base de datos llamada Planets?

» Respuestas para las peguntas de la Evaluación del Conocimiento Llene el Espacio en Blanco 1. Almacenamiento, programación 2. Create 3. Clave externa 4. Select 5. Datos, DML 6. Procedimientos Almacenados, Funciones

www.pdftron.com

ETC

IBE RO

7. Create, alter, drop

Convenciones y características que se utilizan en el libro

AM ÉRI

Este libro utiliza fuentes, símbolos y convenciones de encabezados especíﬁcos para destacar la información que es importante o para dirigir la atención hacia pasos determinados. Para más información sobre las características en cada lección, diríjase a la sección Tour ilustrado del libro.

Convención

Significado

È EN RESUMEN

Esta característica proporciona un resumen breve del material que se cubrirá en las siguientes secciones.

; Listo para la Certiﬁcación

Esta característica muestra el punto específico en el texto en donde se cubrió un objetivo de la certificación. Da la oportunidad de verificar el entendimiento de dicho objetivo MCTS en particular y, de ser necesario, da un repaso a la sección de la lección en donde se cubrió el objetivo.

º Tome Nota

Los recursos del lector aparecen en cuadros sombreados que se encuentran en el texto. TOME NOTA proporciona pistas útiles relacionadas a tareas o temas en particular.

OTRA MANERA

OTRA MANERA proporciona un procedimiento alterno para realizar una tarea en particular.

X-Ref

Estas notas indican en donde se encuentra la información que se explicó en alguna parte del libro de texto o describen características interesantes del Servidor SQL que no se mencionan directamente en el tema actual o en el ejercicio.

Una impresora compartida la pueden utilizar diversas personas en una red.

Los términos clave aparecen en formato itálica y negrita.

ETC

IBE RO

www.pdftron.com

Programa de soporte para el docente

AM ÉRI

Los programas de Microsoft Ofﬁcial Academic Course vienen acompañados de un conjunto de recursos multimedia que incorporan los visuales del libro de texto para conformar un paquete pedagógico cohesivo. Estos recursos proporcionan todos los materiales que el docente necesita para implementarlos en el curso. Los recursos que se encuentran disponibles en línea para descargar son los siguientes:

• La Guía del docente contiene Soluciones para todos los ejercicios del libro de texto así como resumen de cada capítulo y notas para el lector. La Guía del docente y los Programas de estudio para diversos términos se encuentran disponibles en el sitio de la compañía del libro (www.wiley.com/college/microsoft). • El Banco de pruebas contiene cientos de preguntas de opción múltiple, verdadero-falso, respuestas cortas y formatos de ensayo y se encuentran disponibles para descargarlos del sitio de la compañía del libro del docente (www.wiley.com/college/microsoft). Se proporcionan todas las respuestas de las preguntas. • Presentaciones en PowerPoint e imágenes: Se encuentra disponible un conjunto de presentaciones de PowerPoint en el sitio de la compañía del libro del docente (www.wiley. com/college/microsoft) que sirven para mejorar las presentaciones en el salón de clases. Se ajustan a los temas que se cubren en el libro y a la Matriz de habilidades ya que estas presentaciones se diseñaron para transmitir los conceptos clave que se mencionan en el texto.

IBE RO

www.pdftron.com Todas las imágenes del texto se encuentran en el sitio de la compañía del libro del docente (www. wiley.com/college/microsoft). Puede incluirlas en las presentaciones de PowerPoint o crear acetatos y documentos para distribuir. Al utilizar estos visuales en la clase, puede ayudar a que los alumnos se enfoquen en los elementos clave de las tecnologías que se cubren en el libro y les ayuda a entender cómo utilizarlos de manera efectiva en el trabajo.

ETC

• Cuando se desea mejorar las experiencias del salón de clases, no existe una mejor fuente de ideas y de inspiración que los compañeros de trabajo. La Red de Facultades Wiley conecta a maestros que cuentan con tecnología, facilita el intercambio de las mejores prácticas y les ayuda a mejorar su eﬁcacia como docentes. Las actividades de la Red de Facultades incluyen la capacitación de tecnología y tutoriales, seminarios virtuales, intercambio de experiencias e ideas entre colegas, consultas personales y compartir recursos. Para más detalles visite el sitio www.WhereFacultyConnect.com.

Programa de soporte para el docente

ASOCIACIÓN ACADÉMICA MSDN—MEMBRESÍA GRATUITA POR 3 AÑOS DISPONIBLE PARA USUARIOS CALIFICADOS.

AM ÉRI

Microsoft Developer Network Academic Alliance (MSDN AA) se diseñó para proporcionar a las universidades la forma más fácil y económica de hacer disponibles las herramientas de desarrollo de Microsoft, productos y tecnologías más recientes en los laboratorios, salones de clases y en las PC de los alumnos. MSDN AA es un programa de membresía anual para los departamentos que enseñan cursos de Ciencias, Tecnología, Ingeniería y Matemáticas (STEM). La membresía proporciona una solución completa para mantener a los laboratorios académicos, facultades y alumnos a la vanguardia de la tecnología. El software que se encuentra disponible en el programa MSDN AA se proporciona sin costo alguno a los departamentos usuarios a través de la asociación de publicación de Wiley y Microsoft. Como un extra a esta oferta gratuita, se introducirá a la facultad a la Conexión de Facultades de Microsoft y al Centro de Recursos Académicos. Se requiere de tiempo y preparación para lograr que los alumnos se involucren mientras se les da un entendimiento básico de la teoría y la Conexión de Facultades de Microsoft se diseñó para ayudar a los docentes de STEM con esta preparación al proporcionarles artículos, planes de estudio y herramientas que pueden utilizar para involucrar a los alumnos y animarlos a utilizar la tecnología. Para más detalles, contacte a su representante de Wiley.

Para obtener más información sobre el programa de la Asociación Académica MSDN, visite el sitio:

www.pdftron.com msdn.microsoft.com/academic/

IBE RO

Nota: Se puede descargar Microsoft Visual Studio 2008 de MSDN AA para utilizarlo con los alumnos en este curso.

Direcciones web y números telefónicos importantes Para localizar al Representante de Educación Superior de Wiley en su área, visite la siguiente dirección web y haga clic en el vínculo “Who’s My Rep?” que se encuentra en la parte superior de la página www.wiley.com/college o llame al número gratuito de MOAC: 1 + (888) 764-7001 (Únicamente Estados Unidos y Canadá).

ETC

Para aprender más sobre cómo convertirse en un Profesional Certiﬁcado de Microsoft y revisar la disponibilidad, visite el sitio www.microsoft.com/learning/mcp.

Programa de soporte para el alumno

Sitio web de la compañía del libro (www.wiley.com/college/microsoft)

AM ÉRI

El sitio de la compañía del libro del alumno para la serie de MOAC incluye cualquier recurso, ejercicio y vínculo web que se utilizará junto con este curso.

Ediciones digitales Wiley

Las ediciones digitales MOAC de Wiley son versiones electrónicas innovadoras de los libros de texto impresos. En Estados Unidos, los alumnos compran la versión digital al 50% menos del precio del texto impreso y obtiene el valor agregado de permanencia y portabilidad. Las ediciones digitales Wiley proporcionan a los alumnos diversos beneﬁcios adicionales que no se encuentran disponibles en otras soluciones de texto electrónico. Las ediciones digitales Wiley NO son suscripciones; los alumnos descargan dicha edición a sus computadoras. Los alumnos son propietarios del contenido que compran y lo pueden tener en su poder el tiempo que quieran. Después de que se descarga la edición digital Wiley al equipo, los alumnos tienen acceso instantáneo a todo el contenido sin navegar en Internet. Los alumnos también pueden imprimir cualquier sección que preﬁeran leer en papel. También pueden tener acceso a los recursos que se encuentran en la edición digital Wiley. De subrayar su texto electrónico a tomar y compartir notas, los alumnos fácilmente personalizan sus ediciones digitales Wiley al leer o al continuar en la clase.

ETC

IBE RO

www.pdftron.com

Certiﬁcación Microsoft Technology Associate (MTA)

Preparando a la fuerza laboral para la tecnología del mañana.

AM ÉRI

La tecnología juega un papel importante prácticamente en cada uno de los negocios alrededor del mundo. Poseer el conocimiento básico de cómo funciona la tecnología y entender su impacto en los entornos académicos y laborales de la actualidad es de fundamental importancia, sobre todo para los alumnos que están interesados en explorar las profesiones relacionadas con la tecnología. Esta es la razón por la cual Microsoft creó la certificación Microsoft Technology Associate (MTA) la cual es una nueva credencial de entrada que valida el conocimiento tecnológico básico que deben poseer los alumnos que quieren tener una carrera en la tecnología. La certificación Microsoft Technology Associate (MTA) es el camino ideal y preferido para los programas de certificación de tecnología renovada del mundo de Microsoft, como lo son Microsoft Certified Technology Specialist (MCTS) y Microsoft Certified IT Professional (MCITP). MTA se encuentra posicionado para llegar a ser una de las credenciales principales para las personas que buscan explorar y seguir una carrera en tecnología o para expandir cualquier otra posibilidad relacionada como lo son los negocios o cualquier otro campo en donde predomine la tecnología.

www.pdftron.com

IBE RO

Perﬁl del candidato a MTA.

El programa de certificación MTA se diseñó específicamente para alumnos de secundaria y preparatoria que están interesados en explorar las opciones académicas y profesionales en un campo de la tecnología. Ofrece a los alumnos una certificación en las tecnologías de la información básica y en desarrollo. A medida que se implementa el nuevo punto de entrada recomendado para las certificaciones en tecnología de Microsoft, MTA se encuentra diseñado especialmente para los alumnos que son nuevos en IT y en desarrollo de software. Se encuentra disponible de manera exclusiva en ambientes educativos y se integra fácilmente al plan de estudios de las clases de computación existentes.

MTA faculta a los docentes y motiva a los alumnos.

ETC

MTA proporciona un nuevo estándar para medir y validar el conocimiento tecnológico básico en el salón de clases mientras mantiene intacto su presupuesto y los recursos de enseñanza. MTA ayuda a las instituciones a que se destaquen como proveedores innovadores de credenciales en la industria de alta demanda y se puede implementar con facilidad con un paquete sencillo, conveniente y costeable de exámenes de certificación en tecnología de nivel introductorio. MTA permite a los alumnos explorar rutas de formación profesional en la tecnología sin requerir de una gran inversión de tiempo y recursos, y a su vez, proporciona una base profesional y la confianza para tener éxito en estudios avanzados y en futuros escenarios profesionales. Además de proporcionar a los alumnos una certificación de nivel introductorio a Microsoft, MTA se diseñó para ser un escalón hacia otras certificaciones en tecnología de Microsoft más avanzadas, por ejemplo la certificación de Microsoft Certified Technology Specialist (MCTS).

XIV

Presentación de exámenes MTA: La Licencia Campus de MTA.

AM ÉRI

Implementar un nuevo programa de certiﬁcación en el salón de clases nunca antes había sido tan fácil, sobre todo si se cuenta con la Licencia Campus de MTA. Basta con comprarla una sola vez por un periodo de un año para obtener 1,000 exámenes de la Licencia Campus de MTA y no habrá necesidad de realizar otro presupuesto adecuado y volver a comprar vouchers para los exámenes. En la actualidad, puede realizar el presupuesto a un bajo costo para todo el año para posteriormente administrar los exámenes MTA con los alumnos y otras facultades en todo el campus cuando y donde lo desee. La Licencia Campus de MTA proporciona un paquete conveniente y costeable de certiﬁcaciones de tecnología de nivel introductorio que se diseñó para facultar a docentes y motivar a los alumnos a medida que construyen la base para sus carreras. La Licencia Campus de MTA se encuentra administrada por Certiport, proveedor exclusivo de exámenes de MTA de Microsoft.Para obtener más información sobre Microsoft Technology Associate y de la disponibilidad de exámenes, visite el sitio www.microsoft.com/learning/mta.

www.pdftron.com

IBE RO

Examen de prueba sobre Fundamentos de Bases de Datos: Examen MTA 98-364

ETC

Al comprar este libro de texto MOAC MTA, los alumnos pueden realizar una copia del examen de prueba GMetrix para el Examen MTA 98-364. Los exámenes de práctica de GMetrix son una de las herramientas más efectivas para evaluar la preparación de cada persona para el examen de certiﬁcación de MTA.

Revisores de los Fundamentos Tecnológicos de MOAC MTA

AM ÉRI

Agradecemos a todos los revisores que estudiaron detalladamente el documento y proporcionaron retroalimentación valiosa en el servicio de calidad de los materiales educativos: Yuke Wang, Universidad de Texas en Dallas. Palaniappan Vairavan, Bellevue College Harold “Buz” Lamson, Instituto Técnico ITT Colin Archibald, Valencia Community College Catherine Bradﬁeld, Universidad en línea DeVry Robert Nelson, Blinn College Kalpana Viswanathan, Bellevue College Bob Becker, Vatterott College Carol Torkko, Bellevue College Bharat Kandel, Tecnológico Missouri Linda Cohen, Forsyth Technical Community College Candice Lambert, Centros Tecnológicos Metro Susan Mahon, Collin College Mark Aruda, Hillsborough Community College Claude Russo, Brevard Community College David Koppy, Baker College Sharon Moran, Hillsborough Community College Keith Hoell, Briarcliffe College and Queens College-CUNY Mark Hufnagel, Distrito Escolar del Condado de Lee Rachelle Hall, Glendale Community College Scott Elliott, Christie Digital Systems, Inc. Gralan Gilliam, Kaplan Steve Strom, Butler Community College John Crowley, Bucks County Community College Margaret Leary, Northern Virginia Community College Sue Miner, Lehigh Carbon Community College Gary Rollinson, Cabrillo College Al Kelly, Universidad de Tecnología Avanzada Katherine James, Seneca College

ETC

IBE RO

www.pdftron.com

Índice

1. Comprender los conceptos básicos de bases de datos 2. Crear objetos de bases de datos

AM ÉRI

3. Manipulación de datos

4. Comprender el Almacenamiento de Datos 5. Administración de una Base de Datos

105

ETC

IBE RO

www.pdftron.com

Contenido

Lección 2 Crear objetos de bases de datos

Comprender los Conceptos de Bases de Datos

Comprender las bases de datos de tipo plano Comprender las bases de datos jerárquicas

Comprender las bases de datos relacionales

Deﬁnición de tipos de datos 26

Usar tipos de datos predeﬁnidos

Tipos de datos más utilizados

Uso de tipos de datos numéricos exactos

Comprender los fundamentos de base de datos 5

Comparar hojas de cálculo con tablas de bases de 5 datos Comprender los valores calculados 6

Comprender conceptos de bases de datos relacionales 7 Introducir elementos de lenguaje

AM ÉRI

Lección 1 Comprender los conceptos básicos de 1 bases de datos

Uso de tipos de datos numéricos aproximados 30 Uso de tipos de datos de fecha y hora 30

Tipos de datos más utilizados

Comprender conversiones implícitas

Uso de cadenas de carácter regulares

Comprender las cadenas de caracteres Unicode 34

www.pdftron.com Utilizar la interfaz del SQL Server Management Studio (SSMS) 10

Comprender las inyecciones SQL

IBE RO

Comprender el lenguaje de manipulación de datos (DML) 14 Comprender el lenguaje de deﬁnición de datos 14 (DDL)

Creación y uso de tablas 34 Creación de vistas 38 Creación de procedimientos almacenados

Utilizar sentencias DDL USE

ALTER DROP

Prácticas recomendadas

Usar el procedimiento almacenado Insert

Usar el procedimiento almacenado Delete

CREATE GO

Usar el procedimiento seleccionado Select Usar el procedimiento almacenado Update

Resumen de Habilidades 47 Evaluación de Conocimientos 48 Evaluación de aptitud 50 Evaluación de destreza 51

TRUNCATE y DELETE 18 Tablas del Sistema 19

ETC

Resumen de Habilidades 20 Evaluación de Conocimientos Evaluación de Competencias

21 23

Lección 3 Manipulación de datos

Uso de consultas para seleccionar datos Combinación de condiciones

Uso de la cláusula BETWEEN

Uso de la cláusula NOT 57 Uso de la cláusula UNION

Uso de las cláusulas EXCEPT e INTERSECT 58 Uso de la cláusula JOIN

Uso de consultas para insertar datos

XVIII

Evaluación de Competencia 101

Actualizar datos y bases de datos

Uso de la sentencia UPDATE

Lección 5 Servicios Esenciales 105

Eliminar datos 67 Uso de la sentencia DELETE

Truncar una tabla con TRUNCATE TABLE

Eliminar una tabla con DROP TABLE 68 Uso de la integridad referencial

Insertar datos

Seguridad de bases de datos

106

Seguridad de nivel de servidor

107

Seguridad de Nivel de base de datos 108

AM ÉRI

Resumen de Habilidades 70 Evaluación de Conocimientos 71 Evaluación de aptitud 72 Evaluación de destreza 73

108

Seguridad de Windows

Cuenta de servicio de SQL Server Autenticación de Windows

108

109

Agregar un nuevo Inicio de sesión de Windows 109 Remover un Inicio de sesión de Windows

Lección 4 Comprender el Almacenamiento de Datos 75 Normalizar una Base de Datos

112

Autenticación de SQL 113

Roles de nivel Servidor de base de datos Roles ﬁjos de servidor

113

Rol público 115

Roles deﬁnidos por el Usuario 115

Conceder Acceso a la base de datos

¿Qué es la Normalización? 76

116

www.pdftron.com Primer Forma Normal (1NF) Eliminar Grupos 77 Repetidos

Seguridad de Objeto

Tercera Forma Normal (3NF) Eliminar Columnas no Dependientes a Claves 78

Cómo Normalizar una base de datos a la Tercera Forma Normal 79 Cuarta Forma Normal (4NF) Aislar relaciones múltiplesi ndependientes 80

Quinta Forma Normal (5NF) Aislar las relaciones múltiples relacionadas semánticamente 80 Beneﬁcios de una base de datos digital Tablas, ﬁlas y columnas

Comprender las claves principales, externas y 82 compuestas Claves principales

ETC Claves principales compuestas

Asignar roles de base de datos ﬁjas con Transact-SQL 118 Roles de aplicación

118

Permisos de objetos

118

Conceder permisos de objeto con sentencias de Transact-SQL 119

Administrar roles

120

Administrando roles con sentencias de Transact-SQL 120 Estructura jerárquica de roles

120

Seguridad de objetos y Management Studio Establecer permisos desde la lista de roles

Comprender los índices clustered y non-Clustered 91 Índices clustered Crear una Tabla

117

Asignar roles de base de datos ﬁjas con SQL Server Management Studio 117

121

Establecer permisos desde la lista de usuario 121

Claves externas 86

Índices non-clustered

116

117

Roles de nivel de base de datos

IBE RO

Segunda Forma Normal (2NF) Eliminar los Datos Redundantes 78

Cuentas de inicio de sesión de Invitado

Resumen de Habilidades 98 Evaluación de Conocimiento 101

Cadenas de propiedad

123

Ejemplo de un modelo de seguridad

124

Respaldar y restaurar bases de datos 125 Respaldos de bases de datos Respaldos parciales

126

Respaldos de archivos

126

Dispositivos de respaldo 127 Calendarizar respaldos

127

125

122

Contenido

Restaurar bases de datos Usar el SSMS

XIX

127

128

Uso del comando RESTORE

129

AM ÉRI

Resumen de Habilidades 130 Examen de Conocimientos 131 133 Evaluación de destreza Evaluación de dominio 133

ETC

IBE RO

www.pdftron.com

CA AM ÉRI

ETC

IBE RO

www.pdftron.com

Lección 1

Comprender los conceptos básicos de bases de datos Matriz de Dominio de objetivos

Comprender los conceptos de bases de datos Comprender las bases de datos relacionales

Comprender el lenguaje de manipulación de datos

Comprender el lenguaje de deﬁnición de datos (DDL)

Términos Clave

Descripción del Dominio del objetivo Comprender cómo la información es almacenada en tablas. Comprender los conceptos de bases de datos relacionales. Comprender el lenguaje de manipulación de datos (DML). Comprender el lenguaje de deﬁnición de datos (DDL).

Número del Dominio del objetivo 1.1

AM ÉRI

Habilidades/Conceptos

1.2 1.3

1.4

www.pdftron.com • Sistema de administración de base de datos (DBMS) • Servidor de base de datos • Base de datos plana • Base de datos jerárquica • Índice

IBE RO

• Limitaciones • Base de datos (db) • Lenguaje de Deﬁnición de Datos (DDL) • Lenguaje de manipulación de datos (DML)

• Consulta • Base de datos relacional • SQL Server Management Studio (SSMS) • Tabla • Transact-SQL

ETC

Es contador junior recién contratado en una firma de contadores prestigiosa y se le ha pedido el compilar una perspectiva financiera para uno de sus clientes más grandes, con un tiempo de alrededor de tres semanas. Uno de los socios de la firma siente que la empresa no está obteniendo toda la información financiera relativa utilizando sus métodos actuales de recuperación de información. La firma actualmente utiliza hojas de cálculo de Excel para crear perspectivas financieras y las posiciones financieras actuales para cada uno de sus clientes. Se le ha entregado un total de 15 hojas de cálculo de sus clientes más grandes para reunir la información necesaria, en la cual rápidamente se da cuenta que la información financiera actual es demasiada vasta para completar el proyecto en el tiempo que se le ha asignado.

Lección 1

Comprender los Conceptos de Bases de Datos

È EN RESUMEN

AM ÉRI

Con la tecnología actual, cualquier computadora en la red puede proporcionar o solicitar servicios dependiendo de cómo se conﬁgura la red. Un servidor es una computadora que está destinada a ser un proveedor de servicio dedicado y un cliente es una computadora que solicita servicios. Una red que está constituida por servidores dedicados y clientes se conoce como una red cliente/servidor. Una red basada en servidor es la mejor para compartir recursos y datos, mientras proporciona seguridad de red centralizada para tales recursos y datos. Las redes con Windows Server 2003 y Windows Server 2008 por lo general son redes cliente/servidor.

Una base de datos (bd) es una colección organizada de datos, típicamente almacenada en un formato electrónico. Le permite introducir, organizar y recuperar la información rápidamente. Las bases de datos tradicionales están organizadas por campos, registros y ﬁlas. Microsoft SQL server utiliza tres tipos de archivos para almacenar la base de datos. Archivos de datos principales, con una extensión .mdf, son los primeros archivos creados en una base de datos y puede contener objetos deﬁnidos por el usuario, tales como tablas y vistas, así como también las tablas del sistema que SQL Server requiere para mantener el registro de la base de datos. Si la base de datos se hace muy grande y se queda sin espacio en su primer disco duro, puede crear archivos de datos secundarios, con una extensión .ndf, en discos duros físicos separados para dar más espacio a su base de datos. El tercer tipo de archivo es el archivo de registro de transacciones. Los archivos de registro de transacciones utilizan una extensión .ldf y no contienen ningún objeto tal como tablas o vistas.

IBE RO

www.pdftron.com Un ejemplo de base de datos es el directorio telefónico. Si tiene un directorio telefónico almacenado en disco, el directorio telefónico sería el archivo. Dentro del directorio telefónico, tiene una lista de registros. Cada registro tiene un nombre, dirección y número telefónico. Un campo es una sola pieza de información. Los ejemplos incluyen nombre, número telefónico y dirección.

Ya que una base de datos puede almacenar miles de registros, sería una faena si tuviera que abrir la tabla e ir a través de cada registro, uno a la vez, hasta que encuentre el registro que necesita. Por supuesto, sería aún más difícil si tiene que recuperar registros múltiples. Para recuperar datos dentro de una base de datos, ejecutaría una consulta, lo cual es una investigación en la base de datos con el ﬁn de obtener información desde ahí. En otras palabras, una consulta es utilizada para pedir información desde la base de datos y los datos son regresados.

ETC

Si la base de datos contiene miles de registros con muchos campos por registro, podría tomar tiempo a una computadora rápida el buscar a través de una tabla para recuperar la información. Un índice de base de datos es una estructura de datos que mejora la velocidad de las operaciones de recuperación de datos en una tabla de base de datos. Las desventajas de utilizar un índice es que los índices necesitan ser creados y actualizados, lo cual toma procesamiento y toma espacio en disco. La mayoría de los usuarios no acceden a las bases de datos directamente, en su lugar, los usuarios utilizan un sistema de administración de base de datos (DBMS) para acceder a la base de datos indirectamente. DBMS es una colección de programas que le permiten introducir, organizar y seleccionar datos en una base de datos. Por ejemplo, un agente de viajes puede ejecutar un programa de sistema de ticket en su computadora de escritorio la cual a su vez accede a la base de datos.

Comprender los conceptos básicos de bases de datos

• Bases de datos plana • Bases de datos jerárquicas • Bases de datos relacionales

Hay tres tipos de bases de datos que debería conocer con el ﬁn de hacer la elección apropiada cuando desarrolle sus propias tablas de base de datos. Cada tipo de base de datos tiene sus propias características de diseño importantes:

f Comprender las bases de datos de tipo plano

; Listo para la certiﬁcación ¿Cómo están organizadas las tablas dentro de una base de datos? —1.1

AM ÉRI

Una base de datos plana, es un diseño muy simple. Son más comúnmente utilizadas en formatos de texto plano, ya que su propósito es mantener un registro por línea, haciendo el desempeño de acceso y consultas muy rápidas. Un ejemplo de este tipo de base de datos seria lo que encuentra en un archivo .txt o .ini. Las bases de datos planas son consideradas planas debido a que son tablas de dos dimensiones consistiendo de renglones y columnas. Cada columna podría ser deﬁnida como un campo (tal como el apellido de una persona o el número de ID de un producto) y cada renglón sería un registro (tal como la información de una persona o la información de un producto. El siguiente es un ejemplo de un archivo de base de datos plana simple donde una compañía de suministros ha conﬁgurado a cada cliente con lo que el cliente ordena consistentemente para una fácil recuperación y propósitos de reordenamiento: id 1 2 3 4 5

cliente allen smith dennis alex sloan

orden cuaderno papel plumas cartuchos de tinta impresora

IBE RO

www.pdftron.com f Comprender las bases de datos jerárquicas

Un diseño de base de datos jerárquica es similar a la estructura de un árbol (tal como un árbol familiar). Cada padre puede tener múltiples hijos, pero cada hijo puede tener sólo un padre. Esto es similar al funcionamiento de un diseño de base de datos jerárquico donde hay una relación padre-hijo.

ETC

Un ejemplo de una base de datos jerárquica padre-hijo es mostrada en la Tabla 1-1. Por lo que podría tener un departamento de cuatro empleados para los que la compañía acaba de comprar equipo nuevo. Una tabla podría mantener la información del empleado mientras otra tabla podría mantener los datos del equipo recién comprado. La tabla en la izquierda es deﬁnida como “padre” y la tabla de la derecha es deﬁnida como el “hijo”. Si tiene varias tablas que están enlazadas, las tablas de la base de datos empezaran a formar la estructura de árbol donde tiene un padre, que tiene múltiples tablas hijo y cada uno de esas tablas hijo podría tener sus propias tablas hijo.

Lección 1

Tabla Padre

Diseño de base de datos jerárquica mostrando tablas padre e hijo

NumEmp 100 101 102 103

Tabla Hijo NumSerial 30032334 4323452 342342 234322

Nombre Paul Jane Jim Ed

Apellido Baker Smith Tate Rosen

NumDept 101 101 101 102

Tabla 1-1

AM ÉRI

Tipo Computadora Laptop Monitor Impresora

NumEmp 100 101 100 100

www.pdftron.com

IBE RO

f Comprender las bases de datos relacionales

; Listo para la certiﬁcación

Básicamente, una base de datos está hecha de muchas tablas. Justo como un libro de Excel puede estar hecho de varias hojas de cálculo – albergando miles de ﬁlas y columnas de información. Esto es el porqué es más fácil trabajar con una base de datos cuando está tratando de ir a través de varias hojas de cálculo para encontrar la información que necesita.

ETC

¿Cuál es la diferencia entre las bases de datos relacionales a las bases de datos planas y bases de datos jerárquicas? —1.2

Un ejemplo de base de datos relacional, es mostrado en la Tabla 1-2. En la primera tabla Padre, tiene una tabla que muestra a vendedores de una compañía. En la segunda tabla padre, tiene varios modelos que están a la venta. En la tabla hijo, tiene clientes quienes han comprado modelos de su compañía, lo cual esta enlazado a la primer tabla padre con el NumEmp y a la segunda tabla padre con el Modelo.

Comprender los conceptos básicos de bases de datos

Tabla 1-2

Tabla Padre 1 NumEmp 100 101 102 103

Nombre Paul Jane Jim Ed

Tabla Padre 2 Apellido Baker Smith Tate Rosen

Modelo 2200MX 42CRS 4232DR 2201MX

Costo $75000 $55000 $60000 $80000

Color Red Gray Red Blue

AM ÉRI

Tabla Hijo

Diseño de una base de datos relacional mostrando dos tablas padre y una hijo.

Nombre Pete Jim Omar Louise

Apellido Wilson Cline Salize Peterson

IDNum 1001 1002 1003 1004

Modelo 2200MX 42CRS 4232DR 2201MX

f Comprender los fundamentos de base de datos

Una base de datos simple con una sola tabla es similar a una hoja de cálculo que contiene ﬁlas y columnas. Sin embargo, a diferencia de una hoja de cálculo, una base de datos le permite almacenar miles de ﬁlas y datos y después acceder a esa información más rápidamente de lo que lo puede hacer leyendo una hoja de cálculo.

www.pdftron.com

IBE RO

Una hoja de cálculo a menudo es el punto de inicio para crear una base de datos. Utilizando una hoja de cálculo, es fácil crear encabezados e iniciar a introducir información. Es fácil añadir, eliminar, reordenar y formatear encabezados. También puede fácilmente ordenar la información en uno o más encabezados. Es fácil insertar, eliminar y ﬁltrar renglones que coincidan con uno o más patrones bajo un encabezado. Muchas bases de datos acumularán miles de ﬁlas de datos. Basado en sus necesidades, podría necesitar crear tablas adicionales. En una hoja de cálculo, esto sería igual que añadir hojas de cálculo adicionales, lo cual puede ser utilizado para categorizar la información por una entidad o periodo de tiempo o manteniendo la información adicional.

Las hojas de cálculo están diseñadas y limitadas a miles de filas por hoja de cálculo. Cuando una hoja de cálculo es abierta, el archivo entero es cargado en la memoria de la computadora. Si se almacena suficiente información, el archivo eventualmente podría fallar en el momento de cargarlo dado la memoria insuficiente. De repente, los beneficios de utilizar una hoja de cálculo empiezan a declinar. Esto es cuando el cambiar a una base de datos tiene más sentido.

ETC

Hay tres fundamentos de bases de datos: • Están diseñadas para almacenar billones de ﬁlas de datos. • Están limitadas al espacio de disco duro disponible en la computadora. • Son optimizadas para utilizar toda la memoria disponible de la computadora para mejorar el desempeño.

Comparar hojas de cálculo con tablas de bases de datos Una hoja de cálculo puede contener varias hojas electrónicas que almacenan lógicamente

Lección 1

Figura 1-1

AM ÉRI

Hoja de Cálculo con Múltiples Hojas Electrónicas

información agrupada en un formato tabular. Una hoja de cálculo es comparable a una tabla de base de datos y los encabezados en una hoja de cálculo son comparables a las columnas o campos dentro de una tabla de base de datos. Si una hoja de cálculo tiene múltiples hojas electrónicas, cada hoja electrónica se puede interpretar como una tabla distinta que pertenece a la misma base de datos.

IBE RO

www.pdftron.com Cuando una columna de una hoja de cálculo contiene datos que podrían estar vacíos, un vacío es almacenado como un NULL en la base de datos. Una tabla de base de datos puede estar diseñada para permitir valores NULL o para no permitirlos dentro de una columna.

Comprender los valores calculados

ETC

En una hoja de cálculo, puede utilizar fórmulas para calcular valores desde otra información en la misma ﬁla o columna. Un valor calculado es esencialmente un valor que resulta de alguna ordenación, cálculo – o fórmula – desempeñado en un valor de entrada especíﬁco. Las bases de datos pueden ser utilizadas para hacer la misma cosa dentro de la base de datos o dentro de reportes generados desde la base de datos o dentro de la aplicación que está

Comprender los conceptos básicos de bases de datos

Figura 1-2

accediendo a la base de datos.

AM ÉRI

Una hoja de cálculo con valores calculados

IBE RO

www.pdftron.com f Comprender conceptos de bases de datos relacionales

Antes de que diseñe su primera base de datos relacional, debe comprender los elementos que forman una base de datos y la terminología detrás de la base de datos.

º Tome Nota

ETC

Para comprender los modelos de bases de datos relacionales, piense en cómo las tablas se relacionan a una o más tablas.

Las bases de datos a menudo son encontradas en servidores de bases de datos de forma que pueden ser accedidos por múltiples usuarios y para proporcionar un nivel alto de desempeño. Un servidor de base de datos popular ejecuta Microsoft SQL Server. Los servidores de bases de datos realmente no actúan para albergar programas gráficos, aplicaciones de procesamiento de palabras o cualquier otro tipo de aplicaciones. Estos servidores son optimizados por completo para servir solo a los propósitos de la propia base de datos, regularmente utilizando hardware avanzado para permitir las necesidades de alto procesamiento del servidor de base de datos. Es importante notar que estos servidores no actúan como una estación de trabajo, generalmente están montados en racks ubicados en un centro de datos central y son accedidos sólo a través de un sistema de escritorio del administrador. Una base de datos relacional ayuda a organizar toda su información de las distintas filas y columnas de cada tabla, como se muestra en la Figura 1-5. Cada columna entonces corresponderá a un tipo específico de información que está buscando almacenar en la base de datos. Como observa en la figura, imagine que cada fila corresponde a un registro, una instancia de cada columna y cada tabla podría estar relacionada a uno o más tablas.

Lección 1

Figura 1-3

AM ÉRI

Tabla básica de base de datos

www.pdftron.com

IBE RO

Un modelo de base de datos relacional organiza esa información mostrada en la Figura 1-3 en una tabla de base de datos la cual contiene ﬁlas y columnas y cada columna corresponde a un atributo o un tipo de información que desea almacenar. Cada ﬁla corresponde a un registro o una instancia de cada columna.

Introducir elementos de lenguaje

ETC

Los objetos de bases de datos son divididos jerárquicamente en dos amplias categorías: almacenamiento y programación. Una tabla está estructurada por columnas y filas; cada una de las columnas entonces almacena datos clasificados como tipos de datos. La Figura 1-4 muestra un ejemplo de atributos de columna para una base de datos. Existe una variedad de tipos de datos para elegir ya sea a través de tipos integrados o creando sus propios tipos de datos definidos por el usuario. Los tipos de datos serán discutidos en la Lección 2.

Comprender los conceptos básicos de bases de datos

Figura 1-4

Estructura de base de datos mostrando atributos de columna

AM ÉRI

º Tome Nota Las bases de datos avanzadas, tales como SQL Server, analizan consultas periódicamente y crea índices conforme es necesario para optimizar el desempeño. Puede encontrar evidencia de esto buscando el índice en la base de datos.

IBE RO

www.pdftron.com

ETC

Las Restricciones son limitaciones o reglas ubicadas en un campo o columna para garantizar que los datos considerados inválidos no sean introducidos. Por ejemplo, si desea que alguien introduzca la edad de una persona, el tipo de datos sólo pueden ser números positivos. Una persona no puede tener una edad negativa. Hay una variedad de restricciones las cuales están disponibles con SQL Server 2008, incluyendo las siguientes: • Una restricción única permite al administrador de la base de datos identificar específicamente cuales columnas no deberían contener valores duplicados. • Una restricción de comprobación le permite limitar los tipos de datos que un usuario puede insertar en la base de datos. • Una restricción por default es utilizado para insertar un valor por default en una columna. Si no se especifica otro valor, el valor por default será agregado a todos los nuevos registros. • La restricción no nula (null) garantiza que los datos son introducidos en una celda. En otras palabras, la columna no puede estar en blanco. También significa que no puede insertar un nuevo registro o actualizar un registro sin agregar un valor a este campo. • La restricción de clave principal únicamente identifica a cada registro en una tabla de base de datos. No puede contener valores únicos y no puede obtener valores nulos (NULL). Cada tabla debería tener una clave principal y cada tabla puede tener solo UNA clave principal. • Una restricción de clave externa en una tabla apunta a una clave principal en otra tabla.

Vea la Figura 1-5.

Lección 1

Figura 1-5

AM ÉRI

Una restricción de Base de datos

º Tome Nota Una clave externa también puede ser conocida como auto referencia

IBE RO

www.pdftron.com Las columnas marcadas como clave externa no contienen valores nulos (null). Este no es un estándar deseado en la práctica, debido a que no se pueden veriﬁcar las restricciones si una clave externa consiste de dos o más columnas y contiene valores nulos (null). Esto signiﬁca que no se puede garantizar que la integridad de sus datos esté libre de errores.

Es posible para una restricción de clave externa el hacer referencia a columnas en la misma tabla, también conocido como auto referencia. Cuando se utiliza la auto referencia para consultar una tabla (query), estos arreglos son ahora referencias como un self-join. Como ejemplo de una tabla de auto referencia, tal vez desee crear una tabla de Generaciones que contiene los nombres de personas utilizando columnas llamadas PersonID, PersonName y MotherID. La madre también es una persona almacenada en la tabla Generaciones, de manera que puede crear una relación de clave externa desde el MotherID (la columna de clave externa) referenciando PersonID (la columna de la clave principal).

f Utilizar la interfaz del SQL Server Management Studio (SSMS)

ETC

Cuando instala el Microsoft SQL Server, también instala el SQL Server Management Studio (SSMS), la cual es la herramienta principal para administrar el servidor y sus bases de datos utilizando una interfaz gráﬁca. La característica central del SQL Server Management Studio es el Explorador de Objetos, el cual permite al usuario el navegar, seleccionar y administrar cualquiera de los objetos dentro del servidor. Vea la Figura 1-6. También puede ser utilizado para ver el desempeño de la base de datos y optimizar el desempeño de la base de datos. El SQL Server Management Studio también puede ser utilizado para crear y modiﬁcar la base de datos, tablas e índices.

Comprender los conceptos básicos de bases de datos

Figura 1-6

AM ÉRI

SQL Server Management Studio

IBE RO

www.pdftron.com

ETC

SSMS también incluye el Query Analyzer (Vea la Figura 1-7), el cual proporciona una interfaz basada en GUI para escribir y ejecutar consultas (queries). El Query Analyzer soporta los siguientes XQuery, sqlcmd scripts y Transact-SQL,

• XQuery: Un lenguaje de consulta y programación funcional que está diseñado para consultar colecciones de datos XML. • sqlcmd scripts: SQLCMD es una aplicación de línea de comando que viene con Microsoft SQL Server, y expone las características de administración de SQL Server. Permite que las consultas de SQL sean escritas y ejecutadas desde el símbolo del sistema. También puede actuar como un lenguaje de scripting para crear y ejecutar un conjunto de sentencias SQL como un script. Tales scripts son almacenados como un archivo.sql y son utilizados para la administración de bases de datos o para crear el esquema de base de datos durante el desarrollo de una base de datos. • Transact-SQL: Es el medio principal de programación y administración de SQL Server. Expone palabras clave de forma que puede crear y administrar bases de datos y sus componentes y para monitorear y administrar el propio servidor. Cuando utiliza el SSMS para desempeñar una acción o tarea, que esté ejecutando comandos de Transact-SQL.

Lección 1

Figura 1-7

AM ÉRI

Query Analyzer

www.pdftron.com

IBE RO

Debe tener SQL Server 2008 instalado en su sistema antes de moverse a la siguiente sección.

Æ Cargar la Interfaz SSMS

PREPÁRESE. Antes de iniciar estos pasos, asegúrese de lanzar el SSMS.

1. Haga clic en el botón Inicio y luego en Microsoft SQL Server 2008 para expandir la selección de programa. 2. Haga clic en SQL Server Management Studio. El Management Studio se abre, desplegando el cuadro de diálogo de Conectar con el Servidor.

3. Cambie los detalles de conexión al servidor (si es necesario) y haga clic en Conectar. Después de que conﬁgure las opciones del servidor correctamente, la interfaz del SQL Server Management Studio será visible. Solución de Problemas

ETC

Su computadora podría no tener la interfaz del SQL Server Management Studio instalada como parte del programa SQL Server 2008. Si no puede encontrar la herramienta Management Studio cuando la busca bajo los archivos de programa, podría tener que agregar esto como una actualización de instalación de servidor. Para hacer esto, inserte el CD de instalación, y haga clic en el botón Avanzadas en los Componentes para la ventana de instalación cuando aparezca.

Tome Nota

También puede instalar el SQL Server Management Studio en cualquier sistema operativo de escritorio de Windows de forma que puede conectarse remotamente y administrar un SQL server.

Comprender los conceptos básicos de bases de datos

PAUSA. DEJE el SQL Server Management Studio abierto para el recordatorio del capítulo.

El SQL Server Management Studio puede ser utilizado para desempeñar la mayoría de las actividades que requiere hacer y puede ser considerado como una herramienta de “onestop”.

Æ Crear una base de datos con la interfaz del SSMS

AM ÉRI

PREPÁRESE. Antes que pueda iniciar a administrar las bases de datos, tendrá que crearlas. 1. Abra SQL Server Management Studio. Para hacer esto, haga clic en Inicio después en Todos los programas luego en Microsoft SQL Server 2008 y ﬁnalmente en SQL Server Management Studio. 2. Asegúrese que el Motor de Base de Datos esta seleccionado y haga clic en el botón Conectar. 3. Expanda las bases de datos haciendo clic en el signo de suma (+) al lado de las bases de datos. 4. Haga clic derecho en las bases de datos y seleccione Nueva Base de Datos. 5. En el nombre de la base de datos, teclee el nombre de la base de datos que desea crear y haga clic en el botón Aceptar.

www.pdftron.com

IBE RO

Æ Elimine una base de datos con la Interfaz del SSMS

PREPÁRESE. De vez en cuando, desea eliminar bases de datos que no están en uso.

1. Abra el SQL Server Management Studio. Para hacer eso, haga clic en Inicio después en Todos los Programas, posteriormente en Microsoft SQL Server 2008 y al ﬁnal en SQL Server Management Studio. 2. Asegúrese que el Motor de Bases de Datos esté seleccionado y haga clic en el botón Conectar.

3. Expanda las bases de datos haciendo clic en el signo de suma (+) al lado de las bases de datos. 4. Haga clic derecho en la base de datos que desea eliminar y seleccione Eliminar. 5. Seleccione Cerrar Conexiones Existentes y haga clic en el botón Aceptar.

ETC

Por último, SQL Server tiene un área de ayuda intensiva. Además, cuando instala el SQL server, también tiene la opción de instalar los Libros en línea y los Tutoriales del Servidor. Por lo tanto, si desea encontrar información acerca de una opción o comando, debe comprobar la Ayuda y los Libros en línea. Por supuesto, si aún no puede encontrar lo que está buscando, no tenga miedo de buscar en internet.

Lección 1

Comprender el lenguaje de manipulación de datos (DML)

È EN RESUMEN

Como parte de la funcionalidad de crear bases de datos, es importante comprender elementos del lenguaje pueden hacerse dentro de su estructura de base de datos.

¿Cuáles comandos populares utilizados con SQL son comandos DML? — 1.3

Lenguaje de Manipulación de Datos (DML) es el elemento de lenguaje el cual le permite utilizar las sentencias core INSERT, UPDATE, DELETE, y MERGE para manipular datos en cualquier tabla de SQL Server. Las sentencias core incluyen:

AM ÉRI

; Listo para la certiﬁcación

• SELECT: Recupera filas desde la base de datos y permite la selección de una o varias filas o columnas desde una o varias tablas en SQL Server. • INSERT: Agrega una o más filas nuevas a una tabla o una vista en SQL Server. • UPDATE: Cambia los datos existentes en una o más columnas en una tabla o vista. • DELETE: Elimina filas de una tabla o vista. • MERGE: Desempeña operaciones de insert, update o delete en una tabla objetivo, basado en los resultados de un join (unión) con una tabla de origen. Cuando utiliza sentencias DML tales como INSERT, UPDATE, DELETE o MERGE necesita considerar que son ejecutados como un todo en general, ya sea exitoso o fallido. Lo cual significa, por ejemplo, si fuera a insertar 10,000 registros en una tabla, pero se viola una clave principal o restricción única, todas las 10,000 filas de registros se devolverían inmediatamente y ningún registro seria insertado en la tabla. Similarmente, si una declaración DELETE falla basado en la violación de una restricción de clave externa (aunque sea solo en una fila), nada sería eliminado. Debe consultar la tabla para verificar que las restricciones de clave son cumplidas y que su sintaxis es correcta. La siguiente sección muestra la sintaxis correcta para el trabajo con restricciones.

IBE RO

www.pdftron.com Comprender el lenguaje de deﬁnición de datos (DDL) È EN RESUMEN

Las sentencias del Lenguaje de deﬁnición de datos (DDL) forman parte de la porción de Transact-SQL del SQL Server y puede ser utilizado para crear objetos de bases de datos tales como tablas y vistas.

; Listo para la certiﬁcación

ETC

¿Qué comandos populares de SQL son comandos de DDL? —1.4

El Lenguaje de Deﬁnición de Datos (DDL) es un subconjunto del lenguaje Transact-SQL, trata con la creación de objetos de bases de datos como tablas, restricciones y procedimientos almacenados (stored procedures). La interfaz de usuario utilizada para crear estas sentencias fundamentales de DDL será a través de la interfaz de usuario de SQL Server Management Studio como se muestra en la Figura 1-8.

Comprender los conceptos básicos de bases de datos

Figura 1-8

AM ÉRI

Interfaz de usuario del SQL Server Management Studio

www.pdftron.com

IBE RO

Utilizar sentencias DDL

ETC

La interfaz de usuario del SQL Server Management Studio le permite visualizar el diseño de sus sentencias de DDL. Una tarea de sentencias de script DDL puede ser completado siempre a través de la interfaz de usuario del SQL Server Management Studio pero no todas las opciones que desearía utilizar con el script DDL pueden ser llevadas a cabo a través de la interfaz de usuario. Debe, por lo tanto, familiarizarse con las sentencias DDL de USE, CREATE, ALTER, y DROP en los objetos para crear y administrar tablas, tipos de datos definidos por el usuario, vistas, triggers, funciones y procedimientos almacenados. Mientras más sentencias DDL puedan ser ejecutadas utilizando la interfaz gráfica de SSMS, aun así tiene más poder, flexibilidad y control cuando utiliza sentencias DDL. También puede utilizar sentencias DDL para hacer tareas o actividades de secuencias de comandos, las cuales pueden ser programadas o ejecutadas conforme sea necesario. • USE: Cambia el contexto de base de datos. • CREATE: Crea un objeto de base de datos de SQL Server (tabla, vista o procedimiento almacenado) • ALTER: Cambia un objeto existente

Lección 1

¿Qué comando de DDL utilizaría para cambiar el contexto de la base de datos y cuál comando utilizaría para crear una tabla? -1.4

• DROP: Elimina un objeto de la base de datos Vayamos a través de cada una de estas sentencias clave de DDL con una explicación más a fondo y un ejemplo de cada uno.

; Listo para la certiﬁcación

USE

Un comando de One Transact-SQL que vale la pena mencionar es el comando USE. El comando USE cambia el contexto de base de datos a la base de datos especiﬁcada. En otras palabras, cuando realiza comandos en una base de datos particular, lo más probable es que utilice el comando USE para seleccionar la base de datos primero. Por ejemplo, para seleccionar el TESTDB, ejecutaría el siguiente comando: USE TESTDB

CREATE

AM ÉRI

La sentencia CREATE le permite crear una variedad de objetos de base de datos, incluyendo tablas, vistas y procedimientos almacenados. En el siguiente ejemplo, aprenderá a crear una nueva tabla llamada Shirt y una nueva vista llamada Size. El siguiente ejemplo también utiliza CREATE para crear una nueva tabla.

www.pdftron.com Por ejemplo, para crear la tabla Planets, utilizaría lo siguiente:

USE [AdventureWorks]

IBE RO

CREATE TABLE [dbo].[Planets](

[IndvidualID] [int] NOT NULL,

[PlanetName] [varchar](50) NULL, [PlanetType] [varchar](50) NULL, [Radius] [varchar](50) NULL, [TimeCreated] [datetime] NULL

) ON [PRIMARY]

ETC

El use de [AdventureWorks] cambia el contexto de la base de datos a AdventureWorks. El comando GO ejecuta el conjunto previo de comandos. El comando CREATE TABLE [dbo.[Planets] es utilizado para crear la tabla Planets. IndividualID, PlanetName, PlanetType, Radius y TimeCreated son las columnas dentro de la tabla Planets. IndividualID no puede ser nulo (NULL). Int, varchar y datetime señalan el tipo de dato, lo cual especiﬁca cual puede ser introducido en la columna. Los tipos de datos serán explicados en la Lección 2.

Comprender los conceptos básicos de bases de datos

ALTER

AM ÉRI

-- Añadir una nueva columna

La sentencia ALTER cambia un objeto existente y puede añadir o remover columnas de la tabla. Por ejemplo, puede utilizar ALTER para añadir una columna a una tabla, como se muestra en el siguiente ejemplo. También puede utilizar ALTER para cambiar la definición de una vista, un procedimiento almacenado, un trigger o una función. El siguiente ejemplo redefine la vista para incluir la columna Price. No confunda ALTER con UPDATE, ALTER cambia la definición del objeto y UPDATE cambia los datos en una tabla.

ALTER TABLE Shirt ADD Price Money; GO

-- Utilice la declaración UPDATE para establecer los precios UPDATE Shirt SET Price = 13.50 WHERE ProductID = 1; UPDATE Shirt SET Price = 13.50 WHERE ProductID = 2; UPDATE Shirt SET Price = 10.00 WHERE ProductID = 3;

www.pdftron.com UPDATE Shirt SET Price = 12.00 WHERE ProductID = 4;

IBE RO

-- Redefnir la vista ALTER VIEW Size AS

SELECT ProductID, ProductName, Price FROM Shirt WHERE ProductType = ‘Size’; GO

SELECT * FROM Size -- Results:

-- ProductID

ProductName

-- -----------------

Price

---------------

Red

13.50

-- 2

Blue

13.50

-- 3

Orange

-- 4

Black

ETC

-- 1

10.00 12.00

--------------

Lección 1

DROP

La sentencia DROP realmente elimina un objeto de la base de datos, pero si otros objetos dependen del objeto que intenta eliminar, esta sentencia fallara y aparecerá un error. El siguiente ejemplo le muestra cómo utilizar DROP para eliminar datos de la tabla Shirt, subsecuentemente elimina la vista Size y entonces elimina la tabla Shirt de la Base de Datos. También intentaremos suprimir la tabla Person.Contact, pero se dará cuenta, que esta operación fallará debido a que existen otros objetos dependientes de la tabla Person. Contact. Recuerde no confundir DROP, el cual elimina un objeto de la base de datos, con DELETE, el cual elimina datos dentro de una tabla.

AM ÉRI

DELETE FROM Shirt

Select * FROM Size -- Results: -- ProductID

-- -------------

ProductName

--------------

Price

--------

www.pdftron.com -- (0 row(s) affected)

IBE RO

DROP VIEW Size;

DROP TABLE Shirt; GO

DROP TABLE Person.Contact -- Results:

-- Msg 3726, Level 16, State 1, Line 1

ETC

-- Could not drop object ‘Person.Contact’ because it is referenced by a FOREIGN KEY constraint (no se pudo eliminar el objeto ‘Person.Contact’ debido a que está referenciado por una restricción de clave externa).

TRUNCATE y DELETE

Podría leer acerca de distintas maneras de eliminar datos de una tabla y preguntase cuál de las dos sentencias DDL, TRUNCATE o DELETE, sería más aplicable a sus necesidades. La sentencia DELETE es utilizada para eliminar ﬁlas de una tabla, pero no libera el espacio que contiene la tabla. El comando SQL TRUNCATE es utilizado para eliminar todas las ﬁlas de una tabla y libera el espacio que contiene la tabla.

Comprender los conceptos básicos de bases de datos

Para eliminar todas las ﬁlas de la tabla user, utilizaría el siguiente comando:

Si está eliminando datos de tablas y si es una base de datos grande, utilice truncate, es más eﬁciente. Utilice delete para bases de datos más pequeñas.

TRUNCATE FROM user;

º Tome Nota

Para eliminar a un empleado con la id 200 de la tabla User, utilizaría el siguiente comando:

AM ÉRI

DELETE FROM employee; DELETE FROM user WHERE id = 200;

Tablas del Sistema

Cuando desea consultar las vistas del sistema para veriﬁcar si los objetos que desea eliminar están en las tablas de la base de datos, necesitara saber que tablas son las más útiles. Las vistas del sistema pertenecen al esquema sys. Algunas de estas tablas del sistema incluyen: • • • • • • • • •

sys.Tables

sys.Columns

sys.Databases

sys.Constraints sys.Views

sys.Procedures

www.pdftron.com sys.Indexes

sys.Triggers

IBE RO

sys.Objects

Todos estos nombres de vistas son autoexplicativos. Por ejemplo, la vista sys.Objects contiene una ﬁla para cada objeto en la base de datos con los nombres de las columnas

ETC

clave, object_id, type_desc, type, create_date, y modify_date.

Lección 1

En esta lección aprendió:

Resumen de Habilidades • Una base de datos (bd) es una colección organizada de datos, típicamente almacenada en un formato electrónico. Le permite introducir datos, organizar los datos y recuperar los datos rápidamente. • Microsoft SQL server utiliza tres tipos de archivos para almacenar la base de datos. Archivos de datos principales, con una extensión .mdf, son los primeros archivos creados en una base de datos y pueden contener objetos definidos por el usuario, tales como tablas y vistas, así como también las tablas del sistema que SQL Server requiere para mantener registro de la base de datos. • Si la base de datos se hace muy grande y se queda sin espacio en su primer disco duro, puede crear archivos de datos secundarios, con una extensión .ndf, en discos duros físicos separados para darle más espacio a su base de datos. • El tercer tipo de archivo es un archivo de log de transacciones. Los archivos de log de transacciones utilizan una extensión .ldf y no contiene objetos como tablas o vistas. • Para recuperar datos dentro de una base de datos, usted ejecutaría una consulta de base de datos, lo cual es una investigación en la base de datos con el fin de obtener información de regreso de la base de datos. En otras palabras, una consulta es utilizada para pedir información de la base de datos y los datos son regresados. • Un índice de base de datos es una estructura de datos que mejora la velocidad de las operaciones de recuperación de datos en una tabla de base de datos. • La mayoría de los usuarios no acceden a las bases de datos directamente, en su lugar, los usuarios utilizan un sistema administrador de bases de datos (DBMS) para acceder a la base de datos indirectamente. • Una base de datos plana es muy simple en diseño. Son más comúnmente utilizadas en formatos de texto plano, como su propósito es mantener un registro por línea, haciendo el desempeño de acceso y consultas muy rápido. • Tablas, utilizadas para almacenar datos, son objetos de dos dimensiones consistentes de filas y columnas. • Un diseño de base de datos jerárquica es similar a la estructura de un árbol (tal como un árbol familiar). Cada padre puede tener múltiples hijos, pero cada hijo sólo puede tener un padre. • Una base de datos relacional es similar a una base de datos jerárquica en la que los datos son almacenados en tablas y cualquier información nueva es añadida automáticamente en la tabla sin la necesidad de reorganizar la propia tabla. A diferencia de la base de datos jerárquica, una tabla en una base de datos relacional puede tener múltiples padres. • Las bases de datos a menudo son encontradas en servidores de bases de datos de forma que pueden ser accedidas por múltiples usuarios y para proporcionar un alto nivel de desempeño. Un servidor de bases de datos popular ejecuta Microsoft SQL Server. • Las restricciones son limitaciones o reglas ubicadas en un campo o columna para garantizar que los datos que son considerados inválidos no sean introducidos. • El SQL Server Management Studio (SSMS), es la herramienta principal para administrar el servidor y sus bases de datos utilizando una interfaz gráfica. • El lenguaje de Manipulación de Datos o (DML), es un elemento de lenguaje el cual le permite utilizar las sentencias: INSERT, UPDATE, DELETE, y MERGE para manipular datos en cualquier tabla de SQL Server.

AM ÉRI

ETC

IBE RO

www.pdftron.com

Comprender los conceptos básicos de bases de datos

» Evaluación de Conocimientos

AM ÉRI

Llene el Espacio en Blanco

Complete las siguientes oraciones escribiendo la palabra o palabras correctas en los espacios en blanco proporcionados. 1. Los objetos de bases de datos están divididos en dos categorías: ____________ y ____________. 2. Tablas creadas utilizando la sentencia ____________ son utilizadas para almacenar información. 3. Las restricciones pueden deﬁnir relaciones de entidad entre tablas en una forma continua. También son conocidos como restricciones ____________. 4. Con el ﬁn de utilizar el objeto vistas para ver un conjunto de datos, debe utilizar la sentencia de Transact-SQL ____________ para mostrar datos de tablas subyacentes.

www.pdftron.com 5. DDL manipula ____________, mientras que ____________ manipula la información almacenada en tablas.

IBE RO

8. Las sentencias DML centrales son ____________ , ____________ , ____________ y ____________. 9. Las vistas del sistema pertenecen a ____________. 10. La restricción de CLAVE EXTERNA es un identiﬁcador de ____________. Elección Múltiple

Encierra en un círculo la letra que corresponda a la mejor respuesta.

ETC

1. ¿Cuál de las siguientes no es una sentencia DDL? a. b. c. d.

CREATE MERGE ALTER DROP

2. ¿Cuál de las siguientes no es una restricción de columna? a. b. c. d.

DEFAULT CHECK RANGE UNIQUE

Lección 1

a. b. c. d.

CLAVE PRINCIPAL Índice UNICO Índice CLUSTERED Todos los de arriba

3. ¿Cuál de las siguientes puede ser utilizada para mejorar el desempeño de la consulta?

4. ¿Cuál de los siguientes no es una sentencia DML? a. b. c. d.

REMOVE INSERT DELETE TRUNCATE

AM ÉRI

5. Seleccione todas las sentencias que son verdaderas:

a. Los índices deberían solo ser creados en columnas que son buscadas frecuentemente. b. Una auto-referencia es cuando una restricción de clave externa hace referencia a una columna en la misma tabla. c. Una sola sentencia INSERT puede ser utilizada para agregar ﬁlas a múltiples tablas. d. Múltiples CLAVES PRINCIPALES pueden ser agregadas a una tabla. 6. ¿Cuál de las siguientes no es soportado por ALTER?

www.pdftron.com Agregar una columna nueva a una tabla. Eliminar múltiples columnas de una tabla existente. Modiﬁcar el tipo de dato de una columna existente. Cambiar la restricción de IDENTIDAD de una columna existente.

IBE RO

a. b. c. d.

7. ¿Cuál de los siguientes no es una restricción? a. b. c. d.

NULL UNIQUE CHECK PRIMARY

8. ¿Qué signiﬁca SQL?

a. Lenguaje de Pregunta Estructurada b. Lenguaje de Consulta Estructurada c. Lenguaje de Pregunta Fuerte

9. ¿Cuál sentencia SQL es utilizada para extraer datos de una base de datos? SELECT OPEN EXTRACT GET

ETC

a. b. c. d.

10. ¿Cuál sentencia SQL es utilizada para actualizar datos en una base de datos? a. b. c. d.

SAVE MODIFY SAVE AS UPDATE

Comprender los conceptos básicos de bases de datos

» Evaluación de Competencias

Ejercicio 1-1: Buscando una Base de Datos Relacional

AM ÉRI

Se le ha dado una tarea de diseñar una base de datos relacional para soportar un negocio de venta de autos. Necesita almacenar información de sus empleados, el inventario que posee y las ventas hechas. Se debe notar que cada vendedor se le ha dado un porcentaje diferente de comisión. ¿Qué tablas y columnas crearía y como enlazaría las tablas?

» Evaluación de Competencias

Ejercicio 1-3: Utilizando la Ayuda de SQL Server 2008

www.pdftron.com

IBE RO

ETC

2. Abra el menú de Ayuda y Seleccione Buscar. 3. En el cuadro de búsqueda de texto, teclee y haga clic en el botón Buscar. 4. Haga clic en la opción CREATE TABLE (Transact-SQL).

Lección 1

Ejercicio 1-4: Creando bases de datos utilizando la Interfaz Gráﬁca de SSMS

1. Abra el SQL Server Management Studio. Para hacer eso, haga clic en Inicio seleccione Todos los Programas después Microsoft SQL Server 2008 y ﬁnalmente SQL Server Management Studio. 2. Asegúrese que el Motor de Base de Datos esta seleccionado y haga clic en el botón Conectar. 3. Expanda las bases de datos haciendo clic en el símbolo de suma (+) al lado de las bases de datos.

AM ÉRI

4. Haga clic derecho en las bases de datos y seleccione Nueva Base de Datos.

5. En el nombre de la base de datos, teclee Planets y haga clic en el botón Aceptar. 6. Expanda la base de datos Planets haciendo clic en el símbolo de suma (+) al lado de Planets. 7. Expanda las tablas y haga clic en el símbolo de suma (+) al lado de las tablas.

8. Antes de ir a la siguiente evaluación, dar clic derecho en la base de datos Planets y seleccionado Eliminar. 9. Seleccione Cerrar Conexiones Existentes y haga clic en el botón Aceptar.

ETC

IBE RO

www.pdftron.com

Lección 2

Crear objetos de bases de datos Matriz de Dominio de objetivos

Deﬁnición de tipos de datos Creación y uso de tablas

Descripción del Dominio del objetivo Seleccionar tipos de datos Comprender las tablas y saber cómo crearlas Crear vistas Crear procedimientos almacenados y funciones

Número del Dominio del objetivo 2.1 2.2

AM ÉRI

Habilidades/Conceptos

Creación de vistas Creación de procedimientos almacenados

2.3 2.4

www.pdftron.com Términos Clave

• Inyección SQL • Tablas

IBE RO

• Tipos de datos • Procedimientos almacenados

• Vistas

ETC

Es un diseñador de bases de datos para una empresa importante de importación/exportación. Su jefe le ha hecho una solicitud para eliminar algunos procedimientos obsoletos quitando los pedidos a través del fax. En su lugar, ella quiere que se reciban tanto los pedidos como información relacionada a través de un servidor web o por correo electrónico. Por lo tanto, se espera que diseñe una base de datos que almacene y procese estos pedidos.

Lección 2

Deﬁnición de tipos de datos

È EN RESUMEN

AM ÉRI

En esta sección, aprenderá lo que son los tipos de datos, el por qué son importantes y cómo estos afectan los requerimientos de almacenamiento. Cuando vea los tipos de datos, necesitará entender cómo es el diseño de cada tipo para cada tabla y comprender cómo cada uno de esos tipos funciona de la mejor manera para cada columna, variable local, expresión o parámetro. Deberá elegir el tipo de datos que cumpla con los requerimientos de datos; necesitará asegurarse que independientemente del tipo de datos que decida utilizar, este necesitará ofrecer el almacenamiento y el esquema de consultas más eficientes. Uno de los roles del administrador de una base de datos es asegurarse de contener los datos dentro de cada base de datos sean uniformes entre sí al decidir la mejor manera para que se acoplen a la aplicación con la cual interactúen. Un tipo de datos es un atributo que especifica el tipo de datos que un objeto puede albergar y también especifica cuantos bytes tiene asignados. Por ejemplo, varios tipos de datos definen sólo números enteros, los cuales son recomendables para conteos o para propósitos de identificación. Otros tipos de datos permiten números decimales que resultan prácticos cuando se almacenan valores relacionados con dinero. Otros tipos de datos están diseñados para almacenar cadenas o caracteres múltiples para permitirle definir etiquetas, descripciones y comentarios. Por último, hay otros tipos de datos que pueden almacenar fechas, horas y números binarios que consisten en ceros y unos o que se pueden usar para almacenar imágenes. Como regla general, si tiene dos tipos de datos que son similares y sólo difieren en la cantidad de bytes que cada uno utiliza, se tiene un rango mayor de valores y/o se incrementa la precisión.

www.pdftron.com

IBE RO

f Usar tipos de datos predeﬁnidos

SQL Server tiene una amplia variedad de tipos de datos predeﬁnidos. La mayoría de las bases de datos que elabore o utilice sólo necesitan utilizar estos tipos de datos. Los tipos de datos predeﬁnidos de SQL Server 2008 están organizados en las siguientes categorías generales: Numéricos exactos Numéricos aproximados Fecha y hora Cadenas de caracteres Cadenas de caracteres Unicode Cadenas binarias Otros tipos de datos Tipos de datos CLR Tipos de datos espaciales

ETC

; Listo para la certiﬁcación

• • • • • • • • •

¿Cuál tipo de datos utilizaría para el costo de un sistema y cuál usaría para contar la cantidad de automóviles que tiene en inventario? —2.1

Utilizará algunos de estos tipos de datos predeﬁnidos de forma regular y otro más esporádicamente. De cualquier manera, es importante entender lo que son estos tipos de datos y cómo se utilizan en la base de datos. La Tabla 2-1 y la Tabla 2-2 muestran los tipos de datos más utilizados. Observe que en la Tabla 2-2 el * denota los tipos de datos nuevos en SQL Server 2008.

Crear objetos de bases de datos

Tipo de dato Money (Numérico)

Explicación Este tipo de datos numérico se utiliza en lugares donde quiera involucrar dinero o tipo moneda en su base de datos, pero si tiene la necesidad de calcular columnas de porcentaje, es mejor utilizar un tipo de datos flotante “float”. La diferencia entre un tipo de datos numérico y uno float reside en si decide utilizar un número aproximado o uno de precisión fija. En el caso de money o un tipo de datos numérico, este cae dentro del tipo de datos de precisión fija puesto que debe ser representado con precisión y escala. Los tipos de datos datetime y time se utilizan para almacenar datos de fechas y horas en muchos formatos diferentes pero es el “para qué utiliza los datos almacenados” lo que hace la diferencia para decidir entre el tipo de datos a utilizar, ya sea datetime o datetime2. Si intenta almacenar valores entre las fechas Enero 1, 1753 y Diciembre 31, 9999 con una precisión de 3.33 milisegundos, utilice el tipo de datos datetime. Si busca almacenar valores entre Enero 1, 1900 y Junio 6, 2079 con una precisión de 1 minuto, entonces debería utilizar el tipo de datos datetime2. La segunda diferencia importante entre los dos tipos de datos es que datetime utiliza 8 bytes de almacenamiento y datetime2 sólo utiliza 4 bytes. El tipo de datos numérico int se utiliza para almacenar cómputos matemáticos y se usa cuando no se requiere de un punto decimal. Un ejemplo de un integer sería: 2 o -2. Este tipo de datos de cadena de caracteres se utiliza generalmente en bases de datos en la cual tenga atributos en Inglés, pero si se encuentra manejando varios idiomas, utilice en su lugar el tipo de datos nvarchar puesto que le ayudará a minimizar los problemas que conlleva la conversión de caracteres. Conocido también como un tipo de datos de un bit. Por lo tanto, si va a almacenar columnas de 8 bits o menos en una tabla, estas se almacenarán como 1 byte y si hay de 9 a 16 bits, las columnas se almacenarán como 2 bytes, y así sucesivamente. El tipo de datos Boolean convierte valores de cadena verdaderos y falsos a valores de bit con el Verdadero convertido a 1 y el Falso a 0. Este tipo de datos numérico se utiliza generalmente en la comunidad científica y se considera un tipo de datos de números aproximados. Esto significa que no todos los valores dentro del rango de datos se representarán con exactitud. Cuando utilice este tipo de datos, los rangos de tipos de datos flotantes son diferentes. Para el almacenamiento de 4 bytes, utilizaría el rango de 1-24, por ejemplo: float (24) y para 8 bytes, utilizaría el rango 25-53, por ejemplo: float (53).

Diseño de base de datos jerárquica mostrando tablas padre e hijo

Tipos de datos más utilizados

AM ÉRI

Tabla 2-1

Datetime

www.pdftron.com Integer

IBE RO

Varchar

Boolean

ETC

Float

Lección 2

Tabla 2-2

Tipo de datos Numéricos exactos: Bit

Uso / Descripción

Almacenamiento

Entero con un valor de 1 o 0. Se almacenan de 9 a 12 columnas de bits como 2 bytes. La capacidad de almacenamiento se incrementa a medida que aumenta la cantidad de columnas bit utilizadas. Tinyint Datos enteros de 0 a 255 Smallint Datos enteros de -2^15(-32,768) a 2^15-1(32,767) Int Datos enteros de -2^31(-2,147,483,648) a 2^31-1(2m147m483m647) Bigint Datos enteros de -2^63(-9,223,372,036,854,775,808) a 2^63-1(9,223,372,036,854,775,807). Numeric Precisión fija y escala. Los valores válidos van de -10^38+1 a 10^38-1. Decimal Precisión fija y escala. Los valores válidos van de -10^38+1 a 10^38-1 Smallmoney Valores monetarios que van de -214,748.3648 a 214,748.3647 Money Valores monetarios que van de -922,337,203,685,477.508 a 922,337,203,685,477.5807 Numéricos aproximados: Datetime Define una fecha que se combina con la hora del día con segundos fraccionales que se basan en un reloj de 24 horas. Rango: Enero 1, 1753 a Diciembre 31, 9999. Precisión: Redondeado a incrementos de .000, .003, o .007 segundos Smalldatetime Define una fecha que se combina con la hora del día. El tiempo está basado en un día de 24 horas, con los segundos siempre en cero (:00) y sin segundos fraccionales. Rango: 1900-01-01 a 2079-06-06, Enero 1, 1900, a Junio 6, 2079. Precisión: Un minuto. date* Define una fecha. Rango: 0001-01-01 a 9999-12-31. Enero 1, 1 D.C. a Diciembre 31, 9999. Precisión: Un día. time* Define la hora del día. La hora es sin considerar la hora horaria y está basada en un reloj de 24 horas. Rango: 00:00:00.0000000 a 23:59:59.9999999. Precisión: 100 nanosegundos. datetimeoffset* Define una fecha que resulta de la combinación de una hora del día que considera la zona horaria y está basada en un reloj de 24 horas. Rango: 000101-01 a 9999-12-31, Enero 1, 1 D.C. a Diciembre 31, 9999. Rango de tiempo: 00:00:00 a 23:59:59.9999999. Precisión: 100 nanosegundos. datetime2* Define una fecha que resulta de la combinación de la hora del día en un reloj de 24 horas. Rango: 0001-01-01 a 999-12-31, Enero 1, 1 D.C. a Diciembre 31, 9999 D.C. Rango de la hora: 00:00:00 a 23:59:59.9999999. Precisión: 100 nanosegundos. Cadenas de caracteres: Char Tipo de datos de carácter con longitud fija. Varchar Tipo de datos de carácter con longitud variable.

1 byte

Tipos de datos

AM ÉRI

1 byte 2 bytes 4 bytes 8 bytes Varía Varía 4 bytes 8 bytes

8 bytes

ETC

IBE RO

www.pdftron.com 4 bytes

3 bytes

5 bytes

10 bytes

Varía

Varía Varía

Crear objetos de bases de datos

Este tipo de datos se eliminará en versiones futuras de SQL, por lo tanto, utilice varchar (max) Cadenas de caracteres Unicode: Nchar Tipo de datos de carácter con longitud fija. Nvarchar Tipo de datos de carácter con longitud variable. Ntext Este tipo de datos se eliminará en versiones futuras de SQL, por lo tanto, utilice nvarchar (max) Cadenas binarias: Binary Datos binarios con longitud fija. Varbinary Datos binarios con longitud variable. Image Este tipo de datos se eliminará en versiones futuras de SQL, por lo tanto, utilice varbinary (max). Otros tipos de datos: sql_variant Almacena valores de varios tipos de datos soportados por SQL Server con excepción de text, ntext, image, timestamp and sql_variant. Uniqueidentifier 16-byte GUID. (UUID)

Varía

Varía Varía Varía

AM ÉRI

Text

Varía Varía Varía

Varía

16 bytes

Recuerde que en SQL Server, cada columna, variable local, expresión y parámetro siempre tienen un tipo de datos relacionado que deﬁne las características de almacenamiento de los datos como se puede ver en la Tabla 2-1.

www.pdftron.com º Tome Nota

Ahora que ha comprendido la mayoría de los tipos de dato disponibles, recuerde que cuando dos expresiones tienen diferentes tipos de datos, collation (intercalación), precisión, escala o longitud, las características de los resultados quedarán determinadas por cualquiera de los siguientes puntos:

IBE RO

Cada columna, variable local, expresión y parámetro siempre tiene un tipo de datos relacionado y cada tipo de datos es un atributo.

• Cuando hay dos expresiones (función matemática o función de comparación) con tipos de datos diferentes, hay reglas para la precedencia de los tipos de datos que especifican que el tipo de datos con la precedencia más baja se convierta al tipo de datos con la precedencia más alta. • El Collation se refiere a un conjunto de reglas que determinan la forma en la cual se ordenan y comparan los datos. De manera predeterminada, SQL Server tiene una precedencia de collation predefinida. Si desea modificar la manera en la cual se ordenan los datos, debe utilizar una clausula collation. • La precisión, escala y longitud del resultado dependen de la precisión del mismo, de la expresión de entrada. Es decir, si tiene varios valores diferentes y lleva a cabo una operación matemática con estos valores, la precisión, escala y longitud se basará en dichos valores con los que esté llevando a cabo las operaciones matemáticas.

ETC

Veamos algunas de las descripciones de los tipos de datos para que se familiarice con ellos:

f Uso de tipos de datos numéricos exactos

Los tipos de datos numéricos exactos son los tipos de SQL Server más utilizados para almacenar información numérica. Dependiendo del tipo de datos seleccionado, algunos tipos permiten sólo números enteros, mientras que otros permiten números decimales. Los tipos de datos numéricos exactos incuyen: bigint, bit, decimal, int, money, numeric, smallint, smallmoney y tinyint.

Lección 2

AM ÉRI

• int es el tipo de datos entero principal (número entero). • bigint se utiliza cuando los valores enteros excederán el rango del tipo de datos int. Las funciones sólo regresan bigint si la expresión original es un tipo de datos bigint. SQL Server no convierte otros tipos de datos enteros como tinyint, smallint e int de forma automática a bigint. • bit es un tipo de datos entero Transact-SQL que puede tomar un valor de 1, 0 o NULL y produce las siguientes características: • El motor de base de datos de SQL Server optimizará el almacenamiento de las columnas de bits, lo cual significa que si hay 8 o menos columnas de bits en una tabla, estas se almacenarán como 1 byte. Si hay de 9 a 16 columnas, estas se almacenarán como 2 bytes. Es importante darse cuenta de que 1 byte equivale a 8 bits cuando considere tipos de datos. • Los valores de cadena TRUE y FALSE se pueden convertir a valores bit. Esto significa que TRUE se convierte a 1 y FALSE se convierte a 0. • decimal y numeric son también tipos de datos Transact-SQL que tienen precisión fija y escala. La sintaxis par estos tipos de datos es el siguiente: decimal[(p[,s])] numeric[(p[,s])]

• Precision (p) es el número total de dígitos decimales que se pueden almacenar, tanto a la izquierda como a la derecha del punto decimal; este valor debe ser mínimo de 1 y máximo de 38. El número de precisión predeterminado es 18. • Scale (s) reﬂeja el número máximo de dígitos decimales que se pueden almacenar a la derecha del punto decimal y debe ser un valor de 0 a p, pero sólo se puede especiﬁcar cuándo se utiliza precisión. La escala predeterminada es 0. • money y smallmoney son tipos de datos Transact-SQL que utilizaría para representar valores monetarios. Los dos tipos de datos son precisas en una 10,000a de las unidades monetarias que representen.

IBE RO

www.pdftron.com f Uso de tipos de datos numéricos aproximados

Los tipos de datos numéricos aproximados no son tan utilizados como otros tipos de datos de SQL Server. Si necesita de mayor precisión (más lugares decimales) que los disponibles en el tipo de dato numérico exacto, necesitará utilizar un foat o real. Estos tipos de datos generalmente utilizan un almacenamiento mayor en bytes. Float y real se utilizan en conjunto con datos numéricos de punto flotante. Esto significa que todos los datos flotantes son aproximados, por lo que no todos los valores que se representan con un rango de tipo de datos aproximado se pueden expresar de forma exacta.

ETC

La sintaxis de real es foat(n). (n) es la cantidad de bits que se utilizan para almacenar la mantisa del número float como se representa en notación científica por lo que se puede dictaminar la precisión y el tamaño de almacenamiento al especificar n. El valor debe ser entre 1 y 53 con el valor predeterminado de 53.

Uso de tipos de datos de fecha y hora

Los tipos de datos de fecha y hora incluyen date, datetimeoffset, smalldatetime y time.

datetime2,

datetime,

Date se utiliza para deﬁnir una fecha empezando con Enero 1, 0001 DC a Diciembre 31, 9999 DC. Al igual que con cualquier tipo de datos, el tipo de fecha tiene los descriptores

Crear objetos de bases de datos

Tabla 2-3 Descripciones de date

Tipos de datos más utilizados Propiedad Sintaxis Uso

Valor Date DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date ) YYYY-MM-DD

AM ÉRI

Formato de literal de cadena predeterminado

que aparecen en la Tabla 2.3. Mientras que las fechas en sí no resultan afectadas por el horario de verano, debe utilizar fechas para determinar si la hora de un día en particular está en horario de verano o no.

(utilizado para clientes de bajo nivel) Rango Rangos de elementos

(Esto se puede utilizar para compatibilidad previa para clientes de bajo nivel). 0001-01-01 a 9999-12-31 Enero 1, 1 D.C. a Diciembre 31, 9999 D.C. YYYY son cuatro dígitos del 0001 al 9999 para representar un año MM son dos dígitos del 01 al 12 para representar un mes en un año especíﬁco

DD son dos dígitos del 01 al 31, dependiendo del mes, representan un día en un mes especíﬁco. 10 posiciones 10, 0 3 bytes, ﬁjo Un día 1900-01-01

www.pdftron.com

IBE RO

Longitud de carácter Precisión, escala Tamaño de almacenamiento Precisión Valor predeterminado

Calendario Precisión fraccional de segundos deﬁnida por el usuario Conservación y activación de compensación de zona horaria Activación de horario de verano

º Tome Nota

No No

Datetime deﬁne una fecha que se combina con una hora del día expresada con segundos fraccionales basada en un reloj de 24 horas. Su exactitud es hasta de 0.00333 segundos. Si necesita de mayor precisión, deberá utilizar el tipo de datos datetime2 que tiene una exactitud de hasta 100 nanosegundos. Si no necesita llevar registro de los segundos (lo cual resulta menos preciso), puede ahorrarse algo de espacio de almacenamiento utilizando el tipo de datos smalldatetime. El tipo de datos DateTimeOffset es similar a DateTime pero mantiene registro de las zonas horarias. Por ejemplo, si utiliza dos valores DateTimeOffset con la misma hora UTC en diferentes zonas horarias, los dos valores serán iguales.

ETC

Utilice time, date, datetime2 y los tipos de datos dateoffset para sus nuevos proyectos puesto que se alinean con el Estándar SQL, son más portables y todos con excepción de date cuentan con la mejor precisión para aplicaciones de nanosegundos.

Este valor se utiliza para la parte de la fecha adjunta para la conversión de time a datetime2 o datetimeoffset. Gregoriano No

Lección 2

Comprender conversiones implícitas

Ilustración de tipos de conversiones implícitos y explícitos

SQL Server soporta conversiones implícitas, que se pueden llevar a cabo sin especiﬁcar la función invocada (cast o convert). Las conversiones explícitas de hecho requieren que utilice especíﬁcamente las funciones cast o convert. Tanto las conversiones explícitas como implícitas para tipos de datos de SQL Server se muestran en la Figura 2-1.

AM ÉRI

Figura 1-2

Cast y Convert también juegan un papel importante con cualquier función de tipos de datos ya que la convierte una expresión en de un tipo de datos a otro. Convert era la antigua manera de convertir con cast utilizándolo de la misma manera. La siguiente ﬁgura, cortesía de Microsoft, contiene un análisis a mayor detalle de la conversión implícita entre tipos de datos.

ETC

IBE RO

www.pdftron.com Si quiere crear un conjunto de datos donde la hora cuenta con una zona horaria indicada y basada en un reloj de 24 horas, necesitará utilizar datetimeoffset.

Smalldatetime combina una fecha con una hora del día, con la hora basada en un día de

24 horas, con los segundos siempre en cero (:00) y sin segundos fraccionales.

Time deﬁne la hora del día basada en un reloj de 24 horas y sin considerar la zona horaria.

Crear objetos de bases de datos

Uso de cadenas de carácter regulares

Un carácter regular utiliza un byte de almacenamiento para cada uno, lo cual le permite deﬁnir uno de 256 (hay 8 bits en un byte y 2^8=256) caracteres posibles compatibles con el idioma Inglés y algunos europeos. Un carácter Unicode utiliza dos bytes de almacenamiento por carácter, por lo que puede representar uno de 65,536 (hay 16 bits en 2 bytes y 2^16=65,536 caracteres). El carácter adicional permite almacenar caracteres de casi cualquier idioma, incluyendo el chino, japonés, árabe, etc.

º Tome Nota Cualquier tipo de datos sin el elemento VAR (char, nchar) dentro de su nombre es de longitud ﬁja.

AM ÉRI

A medida que escriba la sintaxis para los diferentes tipos de datos, recuerde que también diﬁeren en la forma en la cual se expresan las literales. Una literal de carácter regular siempre se expresa con comillas sencillas. Por ejemplo: ‘Así se ve una cadena literal de caracteres regulares’

Cuando exprese una literal de carácter Unicode, esta debe tener la letra N (de Nacional) como preﬁjo para las comillas sencillas. Por ejemplo: N’Así se ve una cadena literal de caracteres Unicode’

Cuando utilice un elemento VAR, SQL Server conservará espacio en la ﬁla en la cual reside con base en el tamaño de la columna y no en el número de caracteres que se encuentra en la cadena en sí, además se proporcionan 2 bytes extra para los datos offset. Por ejemplo, si quiere especiﬁcar que una cadena sólo soporta un máximo de 25 caracteres, deberá utilizar

www.pdftron.com º Tome Nota

VARCHAR(25).

El consumo de almacenamiento cuando se utilizan tipos de datos Unicode se reduce en comparación al de los tipos de datos regulares, además de permitir operaciones de lectura más rápidas; sin embargo, el precio que se paga por utilizar este tipo de datos está en la posibilidad para la expansión de filas, lo cual lleva al movimiento de datos fuera de la página actual. Esto significa que cualquier actualización de datos cuando se utilizan tipos de datos de longitud variable puede ser menos eficiente que la de los de longitud fija. Es posible definir el tipo de datos de longitud variable con el especificador MAX en lugar de utilizar un número máximo de caracteres identificados en la cadena. Por ejemplo, cuando una columna se define con el especificador MAX, un valor con un cierto umbral (el valor predeterminado es 8,000) que luego se almacena en línea en la fila. Cuando quiera especificar un valor con un tamaño superior al umbral predeterminado, se almacenará externo a la fila y se identificará como un objeto grande o LOB.

º Tome Nota

Estos son los tipos e datos de carácter más utilizados y son ya sea de longitud fija o variable. Cada uno tiene sus propias características individuales que necesitará considerar cuando decida cual tendrá un efecto positivo en los requerimientos de almacenamiento. Tanto los conjuntos de datos char y varchar necesitan estar definidos o asignados dentro de la definición de los datos y pueden resultar afectados por los límites de almacenamiento máximo.

IBE RO

Cuando no se especifica n en la definición de datos o sentencia de declaración de variables, la longitud predeterminada es 1. Cuando no se especifica n con la función CAST, la longitud predeterminada es 30.

ETC

Utilice nchar cuando los tamaños de las entradas de datos de las columnas sean similares. Utilice nvarchar cuando los tamaños de las entradas de datos de las columnas vayan a variar de forma considerable como en el caso de archivos binarios, archivos de imagen, variante SQL y UUID.

Para el conjunto de datos char, este se identiﬁca como char [(n)] y es de longitud ﬁja, carácter no-Unicode (es decir, carácter regular) y tiene una longitud de n bytes. El valor de n debe ser entre 1 y 8,000, lo que hace que el tamaño de almacenamiento de n bytes. El otro tipo de datos no-Unicode varchar[(n|max)], es un conjunto de datos de longitud variable que puede consistir de 8,000 caracteres. Ahora veremos el otro lado de las cadenas de caracteres: cadenas de caracteres Unicode.

Lección 2

Comprender las cadenas de caracteres Unicode

Hay solamente dos tipos de cadenas soportados por SQL Server: regular y Unicode. Los tipos de datos regulares incluyen a los que se identifican con CHAR and VARCHAR. Los tipos de datos Unicode se identifican con NCHAR and NVARCHAR. ¿Suena simple? Lo es, en lo que se refiere a que las diferencias entre regular y Unicode son los bytes de almacenamiento que utiliza cada uno.

AM ÉRI

Las cadenas de caracteres Unicode nchar yand nvarchar pueden ser ﬁjas o variables como sus cadenas de caracteres regulares; estas utilizan el conjunto de caracteres UNICODE UCS-2.

Creación y uso de tablas È EN RESUMEN

El objetivo es desarrollar y comprender el propósito de las tablas y la creación de las mismas en una base de datos al utilizar la sintaxis ANSI SQL apropiada. El propósito de una tabla es proporcionar una estructura para almacenar datos en una tabla relacional. Sin la estructura, hay una alta probabilidad de que la base de datos falle. En el Capítulo 1 se comentó sobre la comprensión del propósito de las tablas y cómo crearlas. Revisemos rápidamente algunos de los puntos importantes que se deben recordar cuando prosigamos a aprender a cómo crear una tabla en una interfaz de usuario no-gráﬁca. Es importante asegurarse de tomar en cuenta cual es el propósito de una base de datos relacional en la jerarquía de la administración de bases de datos.

IBE RO

www.pdftron.com Una base de datos SQL es el contenedor central, la cual recupera datos de varias tablas y vistas de la base de datos. Usted puede entonces ejecutar estos datos a través de consultas que pueden interactuar con los datos almacenados en la base de datos para obtener los resultados requeridos. Una ventaja que tiene una base de datos sobre una serie de hojas de cálculo es que una base de datos puede analizar el almacenamiento redundante e información obtenida de varias hojas de cálculo relacionales. Similar a la programación, cuando se diseña y crea utilizando bases de datos, puede utilizar fácilmente cientos de objetos que incluyen bases de datos, tablas, columnas, vistas y procedimientos almacenados. Por lo tanto, para facilitar el manejo, su organización debe establecer y utilizar un solo estándar consistente. Por supuesto, esto también signiﬁcaría que tienen estos estándares documentados y que los han distribuido entre las personas que trabajan con las bases de datos.

ETC

Realmente no importa si utiliza mayúsculas o minúsculas, siempre y cuando sea consistente. Las dos convenciones de nombres comunes son PascalCase y camelCase. Ejemplos de PascalCase son por ejemplo nombres como OrdenDetalle y ClienteDireccion. Ejemplos de camelCase son nombres como miDireccion y terminosVenta. A ﬁnal de cuentas, no importa cual estándar utilice, siempre debe utilizar nombres que sean precisos y descriptivos. También debe evitar el uso de espacio ya que pueden involucrar complicaciones que lo harán utilizar comillas. En su lugar, utilice guiones bajos (_) como separador para las palabras. Primero aprendamos a crear una tabla nueva con SQL Server Management Studio antes de continuar con el método de sintaxis.

Crear objetos de bases de datos

¿Cómo crearía una tabla con SSMS y cómo crearía un SSMS con comandos SQL?—2.2

Æ Crear una tabla con Microsoft SQL Server Management Studio PREPÁRESE. Antes de comenzar, asegúrese de abrir la aplicación de SQL Server Management Studio. Asegúrese de que tiene la base de datos correcta expandida en la cual desee crear una nueva tabla.

; Listo para la certiﬁcación

1. Haga clic en la carpeta tablas y seleccione Nueva Tabla, como se muestra a continuación:

Figura 2-2

AM ÉRI

Creando una Nueva Tabla

www.pdftron.com

IBE RO

Mientras tenga esta pantalla abierta, realice las siguientes actividades:

Figura 2-3

2. Utilice la información de la Figura 2-3 para completar los detalles en Nombre de Columna, Tipo de Datos y su longitud como se especiﬁca en los paréntesis y en las columnas Permitir Valores Nulls.

ETC

Nombres de Columna e identiﬁcando información

3. Establezca el Valor predeterminado de la columna TimeCreated a (getdate()) y se insertará la fecha actual en cada registro nuevo para ese campo especíﬁco. Vea la ﬁgura 2-4.

Lección 2

Figura 2-4

AM ÉRI

Estableciendo las propiedades del Diseñador de Tabla

www.pdftron.com 4. Guarde su nueva tabla seleccionado Archivo y después Guardar Tabla_1, como se muestra en la Figura 2-5.

Figura 2-5

ETC

IBE RO

Guardando la tabla nueva

Crear objetos de bases de datos

Figura 2-6

AM ÉRI

Nombrando la tabla

www.pdftron.com Su nueva tabla ahora aparece en la sección Tablas (vea la Figura 2-7).

Figura 2-7

ETC

IBE RO

Tabla recién creada

PAUSA. Deje la interfaz de SQL Server Management Studio interface abierta para el siguiente ejercicio.

Lección 2

Æ Crear una tabla con comandos Transact-SQL

La creación de tablas con SQL Server Management Studio es fácil puesto que es una interfaz gráﬁca fácil de utilizar. Pero ¿qué pasaría si necesita crear sus tablas y no está seguro de cómo utilizar la sintaxis ANSI SQL? La sentencia create table se utiliza para crear una tabla. Para utilizar las convenciones de sintaxis de Transact-SQL de forma apropiada, revise el siguiente ejemplo:

AM ÉRI

CREATE TABLE planets (name varchar(50), diameter varchar(50)) INSERT INTO planets (name, diameter) VALUES (‘earth’, 10000)

Si SQL server no soportara la conversión implícita, se necesitaría lo siguiente: CREATE TABLE planets (name varchar(50), diamerter varchar(50)) INSERT INTO planets (name, diameter) VALUES (‘earth’, CAST (10000 as varchar(50)))

Creación de vistas

www.pdftron.com

IBE RO

È EN RESUMEN

Es importante comprender cuando utilizar vistas y cómo crear una vista utilizando ya sea una sentencia TransactSQL o con un diseñador gráﬁco.

Una vista no es más que una tabla virtual que consiste de diferentes columnas de una o más tablas. A diferencia de una tabla, una vista se almacena en la base de datos como un objeto de consulta; por lo tanto, una vista es un objeto que obtiene sus datos de una o más tablas. Las vistas que se encuentran basadas en esta deﬁnición son conocidas como tablas subyacentes. Una vez que haya deﬁnido su vista, podrá hacer referencia a esta como a cualquier otra tabla en su base de datos.

ETC

Una vista se utiliza para servir al propósito de ser un mecanismo de seguridad. Esto significa que una vista asegura que los usuarios puedan recuperar y modificar sólo los datos que puedan ver con los permisos que se les hayan otorgado, lo cual asegura que los usuarios no puedan ver o acceder al resto de los datos en las tablas subyacentes. Una vista también sirve como mecanismo para simplificar la ejecución de consultas. Las consultas complejas se pueden almacenar en el formulario como una vista y los datos de la vista se pueden extraer a través de sentencias de consulta sencillas. Las vistas aseguran la seguridad de los datos al restringir el acceso a los siguientes datos: • • • • • •

Filas específicas de las tablas. Columnas específicas de las tablas. Filas y columnas específicas de las tablas. Filas obtenidas de las combinaciones. Resumen estadístico de datos en tablas dadas. Subconjuntos de otra vista o un subconjunto de varias vistas y tablas.

Crear objetos de bases de datos

Algunos ejemplos comunes de vistas son: Un subconjunto de ﬁlas o columnas de una tabla base. Una unión de dos o más tablas. Una combinación de dos o más tablas. Un resumen estadístico de tablas base. Un subconjunto de otra vista o una combinación de vistas y una tabla base.

• • • • •

AM ÉRI

Las vistas de las bases de datos están diseñadas para crear una tabla virtual que es representativa de una o varias tablas de una forma alternativa y se encuentran relacionadas con consultas en ejecución. Hay dos razones principales por las cuales se debe utilizar una vista en lugar de dar a los usuarios la capacidad de acceder a las tablas subyacentes en su base de datos. • Las vistas le permiten limitar los datos a los cuales puedan acceder los usuarios. Puede otorgar permisos de visualización de las tablas designadas y también puede negar el permiso de acceso. • Las vistas disminuyen la complejidad para los usuarios ﬁnales para que estos no tengan que aprender a escribir consultas completas en SQL si no desean hacerlo. Usted puede escribir las consultas en su lugar y luego ocultarlas en una vista. Cuando cree una vista, asegúrese de considerar el desempeño de la base de datos en su diseño. Como ya se comentó en la Lección 1, la indexación juega un rol en el tiempo de respuesta de las consultas y uno más importante en las mejoras en el desempeño de la base de datos. Pero tenga cuidado: agregar índices al esquema puede de hecho incrementar la complejidad de la base de datos debido al mantenimiento que se debe dar a estos índices.

www.pdftron.com

IBE RO

Hay dos maneras de crear una vista:

• Utilizando el diseñador gráﬁco SQL Server Management Studio. • Escribiendo una sentencia Transact-SQL.

ETC

Cubriremos los dos procedimientos en esta sección. Para crear una vista utilizando Management Studio, asegúrese que la base de datos a la cual le quiera agregar una vista se encuentre resaltada. Una vez que se encuentre con la base de datos con la cual quiera trabajar, siga estos pasos para crear su vista:

Lección 2

Æ Crear una vista utilizando SQL Server Management Studio

PREPÁRESE. Antes de llevar a cabo estos pasos, asegúrese de que Management Studio se encuentre abierto. 1. Expanda la sección Vistas al hacer clic en el signo de mas (+) junto a Vistas.

Haga clic derecho en la carpeta Vistas como aparece en la Figura 2-8 y seleccione Nueva Vista.

Figura 2-8

AM ÉRI

Creación de una vista nueva

Figura 2-9

IBE RO

www.pdftron.com Se abre el cuadro de diálogo Agregar Tabla (vea la Figura 2-9).

ETC

Cuadro de diálogo Add Table

Crear objetos de bases de datos

Explicaremos un poco sobre lo que le permite hacer este cuadro de diálogo:

Figura 2-10

AM ÉRI

• Para especificar la tabla que será utilizada como la fuente primaria, puede hacer clic en la ficha Tablas del cuadro de diálogo. • Para utilizar otra vista existente, haga clic en la ficha Vistas del cuadro de diálogo para utilizar la vista deseada. • Si quiere generar registros de una función, podrá realizarlo en la ficha Funciones. • Si quiere utilizar más de una fuente, puede hacer clic en las fichas para buscar la tabla, vista o función que quiera agregar a su consulta. • Una vez que haya seleccionado la fuente deseada, sólo haga clic en el botón Agregar para cada una de ellas. • Una vez que haya seleccionado y agregado todas las fuentes que desee, haga clic en el botón Cerrar para cerrar el cuadro de diálogo. 2. Cuando haga clic en Agregar para agregar cada fuente, verá la información que aparece en la Figura 2-10.

Información del cuadro de diálogo Agregar Tabla

IBE RO

www.pdftron.com ; Listo para la certiﬁcación

Después de seleccionar los objetos que desee utilizar, la barra de herramientas Diseñador de Vistas se agregará, en la cual podrá planear las vistas que desee incorporar en su consulta.

ETC

¿Cómo se crea una vista utilizando SSMS?—2.3

También puede crear vistas utilizando Transact-SQL. Una vez que haya agregado las fuentes en el panel de diagrama, la sintaxis para estas fuentes aparece en el panel SQL.

Lección 2

Æ Crear una vista utilizando Transact-SQL

PREPÁRESE. Para crear una vista con sintaxis de Transact-SQL, se podría realizar de la siguiente manera: CREATE VIEW vwCustomer AS SELECT CustomerId, Company Name, Phone

AM ÉRI

FROM Customers

Con esto se crea una vista llamada vwCustomer que se almacenará como un objeto. Los datos que se consultan de las columnas vienen de la tabla Clientes.

Creación de procedimientos almacenados È EN RESUMEN

La razón de crear procedimientos almacenados y funciones es aprender a seleccionar, insertar, actualizar o eliminar datos utilizando sentencias de procedimientos almacenados de SQL.

www.pdftron.com

IBE RO

Hasta ahora, hemos aprendido a utilizar diferentes tipos de datos para crear tablas y vistas con un diseñador gráﬁco y con sentencias de sintaxis Transact-SQL. Ahora toca aprender a almacenar sentencias de procedimientos almacenados utilizando la misma interfaz gráﬁca. Un procedimiento almacenado es una sentencia SQL escrita con anterioridad que se “guardó” o almacenó en la base de datos. Una de las cosas que le ayudarán a ahorrar tiempo cuando ejecute la misma consulta de forma repetida es la creación de un procedimiento almacenado, que se puede ejecutar en el ambiente de comandos de la base de datos. Un ejemplo de la ejecución de un procedimiento almacenado sería como el que se muestra a continuación: exec usp_displayallusers

El nombre del procedimiento almacenado es “usp_displayallusers” y “exec” le dice a SQL Server que ejecute el código en el procedimiento almacenado. Cuando cree su propio procedimiento almacenado, se le designará “usp” al principio, lo que le indica a SQL que se trata de un “procedimiento almacenado creado por el usuario”. Ahora, el procedimiento almacenado que esté ejecutando “displayallusers” debe tener un código sencillo dentro de él, por ejemplo:

ETC

SELECT * FROM USERLIST

Lo que hará esta sentencia “select” es regresar todos los datos encontrados en la tabla USERLIST. Una de las preguntas que se debe plantear ahora es “¿por qué no puedo sólo ejecutar la consulta para que me proporcione los datos que necesito en lugar de tomarme la molestia de crear un procedimiento almacenado?”. El “*” que puede observar en la sentencia signiﬁca que no está deﬁniendo criterios que le gustaría que coincidieran con el resultado. Es decir, está regresando “todos” los registros de la tabla userlist.

Crear objetos de bases de datos

Con base en la sentencia de consulta anterior, podría validar esa pregunta con un “si”, pero hay ocasiones donde una consulta puede ir más allá de una simple sentencia “select * from…”. Tal vez se encuentre trabajando en la construcción de un sitio web con páginas ASP y necesitará llamar un procedimiento almacenado desde esa u otra aplicación como Visual Basic. La utilización de un procedimiento almacenado le permite almacenar toda la lógica dentro de la base de datos para que con un comando sencillo pueda consultar y recuperar toda la información de todas las fuentes. Ahora, veamos cómo crear un procedimiento almacenado.

AM ÉRI

Æ Crear un procedimiento almacenado

Un procedimiento almacenado es una sentencia escrita en SQL que se almacenó en la base de datos y si frecuentemente utiliza la misma sentencia SQL en su base de datos, es más sencillo crear un procedimiento almacenado y utilizarlo en su lugar. Ahora, no resulta muy práctico utilizar los procedimientos almacenados para sustituir sentencias simples, como una sentencia “select”, pero, si se encuentra creando sentencias de consulta complejas, lo mejor es crear un procedimiento almacenado y ejecutarlo en el Query Analyzer con el comando ejecutar (exec). Para crear un procedimiento almacenado utilizando una interfaz de usuario gráﬁco, realice el siguiente procedimiento: 1. Expanda la sección Programación, luego expanda la sección Procedimientos Almacenados.

www.pdftron.com Figura 2-11

2. Haga clic derecho y seleccione Nuevo Procedimiento Almacenado (vea la Figura 2-11).

ETC

IBE RO

Menú de selección de Nuevo Procedimiento Almacenado

Se abre la ventana Text Editor (vea la Figura 2-12), que muestra la sintaxis con colores. La ventana contiene una plantilla de procedimientos “realizada” para que agregue sus propios parámetros.

Lección 2

Figura 2-12

AM ÉRI

Ejemplo de la ventana Text Editor

; Listo para la certiﬁcación ¿Cómo se crea un procedimiento almacenado con SSMS?—2.4

www.pdftron.com f Comprender las inyecciones SQL

IBE RO

Antes de aprender la sintaxis para seleccionar, insertar, actualizar y eliminar datos, necesita comprender lo que es una inyección SQL. Una inyección SQL es un ataque en el cual se coloca código malicioso en cadenas que luego pasan a instancias de SQL Server en espera para su ejecución. Cualquier procedimiento que construya sentencias SQL debería revisarse de forma constante para ver que no contenga vulnerabilidades ya que SQL Server ejecuta todas las consultas sintácticamente validas de cualquier fuente. º Tome Nota

La forma primaria de una inyección SQL es como una inserción directa de código en las variables que ingresa el usuario que están concatenadas con comandos SQL y luego se ejecutan. Un método menos directo de ataque inyecta código malicioso en cadenas que se destinan para el almacenamiento en una tabla o se consideran metadatos. Cuando estas cadenas almacenadas se concatenan subsecuentemente en el comando dinámico de SQL, el código malicioso se ejecuta. La función del proceso de inyección es terminar de forma prematura una cadena de texto y agregar un comando nuevo y puesto que el comando insertado puede tener cadenas adicionales adjuntas al mismo antes de ser ejecutado, el agresor termina la cadena inyectada con la marca de comentario “- -“ lo que hace que se ignore el texto subsecuente durante el tiempo de ejecución.

ETC

Una inyección SQL es un ataque en el cual se inserta código malicioso que se inserta en cadenas que luego pasan cuando se ejecutan las sentencias.

f Usar el procedimiento seleccionado Select

El procedimiento almacenado select recupera filas de una base de datos y habilita la selección de una o varias filas de una o varias tablas. Tal vez quiera ver las columnas id y nombre de una tabla donde el tipo de la columna tiene un valor específico. Podría entonces escribir la sentencia select como se indica a continuación:

Crear objetos de bases de datos

SELECT id, name //columna FROM sysobjects //tabla

f Usar el procedimiento almacenado Insert

WHERE type = “U” //el valor de la columna que quiere consultar

AM ÉRI

El procedimiento almacenado insert agrega una o más ﬁlas nuevas en una tabla o vista.

La sintaxis SQL Transact para insert es como se indica a continuación:

CREATE TABLE Sysobjects (Column1 INT, Column2 VARCHAR(10)) // aquí crea la tabla sysobjects con parámetros de columna INSERT INTO TableName (Column1, Column2) VALUES

(1, ‘test1’), (2, ‘test2’) // aquí carga datos en las columnas identifcadas previamente

www.pdftron.com f Usar el procedimiento almacenado Update

IBE RO

El procedimiento almacenado update cambia los datos existentes en una tabla o vista.

El procedimiento almacenado update se representa como se indica a continuación:

UPDATE sysObject //esta es la tabla a la cual le quiere actualizar datos SET Column1 = ‘Name’ //se asigna el título Name a la columna 1 GO //ejecuta la consulta

f Usar el procedimiento almacenado Delete Tal vez tenga un empleado que necesite borrar de la tabla Empleados puesto que se le despidió recientemente. El procedimiento almacenado delete elimina ﬁlas de una tabla o vista.

ETC

El procedimiento almacenado delete se representa de la siguiente forma: DELETE FROM Employee eliminar datos

//esta es la tabla de la cual requiere

WHERE EmployeeID = 8 //este es el número de la persona que quiere eliminar de la tabla Empleados

Lección 2

f Prácticas recomendadas

Veamos más información algunas de las prácticas recomendadas que reunió Microsoft para la utilización de un procedimiento almacenado o función. Es importante tener este tipo de conocimientos sobre prácticas recomendadas que los administradores de bases de datos o desarrolladores deben seguir en el diseño de las bases de datos. Las siguientes recomendaciones aseguran la calidad en desempeño y mantenimiento:

• No utilice SELECT * en sus consultas, en su lugar, escriba los nombres de columnas requeridos después de la sentencia SELECT, por ejemplo:

AM ÉRI

SELECT IDCliente, ClienteNombre, Ciudad

• Cuando se diseña una base de datos, tenga el “desempeño” en mente (algo que hemos mencionado a lo largo de este capítulo). Necesita empezar primero con el desempeño ya que es casi imposible afinar este detalle después de realizar la base de datos. • Asigne un prefijo a los nombres de las tablas con el nombre del propietario, esto no solo mejora la legibilidad, sino que evita cualquier confusión innecesaria y ayuda tanto en el plan de reutilización y mejora los resultados en desempeño. • Utilice SET NOCOUNT ON al principio de sus lotes de SQL, procedimientos almacenados y triggers en ambientes de producción ya que esto eliminará mensajes como ‘(1 filas afectadas) al ejecutar sentencias INSERT, UPDATE, DELETE y SELECT. Al hacer esto se mejora el desempeño de los procedimientos almacenados al reducir el tráfico en la red. • Utilice cláusulas de ANSI-Standard Join más legibles en lugar de las joins antiguos. Las cláusulas de combinación ANSI como WHERE, se utilizan para filtrar datos, mientras que las anteriores manejan tanto la condición de combinación como el filtrado de datos.

IBE RO

www.pdftron.com

ETC

Muchos de los otros comentarios y recomendaciones mencionados en estos capítulos asegurarán (siempre y cuando tenga el desempeño en mente) que cuando se realice una consulta en una base de datos, pueda obtener resultados más rápidos de tablas y vistas indexadas apropiadamente. Al hacer esto, se facilitará su trabajo.

Crear objetos de bases de datos

En esta lección aprendió:

Resumen de Habilidades

AM ÉRI

• Un tipo de datos es un atributo que especifica el tipo de datos que puede tener un objeto y también especifica cuantos bytes puede tomar cada uno de estos. • Como regla general, si tiene dos tipos de datos similares pero que su única diferencia es la cantidad de bytes que cada tipo utiliza, se tiene un rango mayor de valores y/o se incrementa la precisión. • SQL Server tiene un amplio rango de datos predefinidos conocidos como tipos de datos integrados. La mayoría de las bases de datos que se pueden crear o utilizar necesitan utilizar estos tipos de datos. • Los tipos de datos numéricos exactos son los tipos de datos más comunes utilizados en SQL Server para almacenar información numérica. • Int es el tipo de datos numérico principal (número entero). • La precisión (p) es la cantidad total máxima de dígitos decimales que se pueden almacenar, tanto a la izquierda como a la derecha del punto decimal; este valor debe ser mínimo de 1 y máximo de 38. El número predeterminado de precisión es 18. • Money y smallmoney son tipos de datos Transact SQL que deberá utilizar para representar valores monetarios. Los dos tipos son precisos en una 10,000a de la unidad monetaria que representen. • Los tipos de datos numéricos aproximados no son tan utilizados como otros tipos de datos de SQL. Si necesita de mayor precisión (más posiciones decimales) de los que están disponibles en los tipos de datos numéricos exactos, deberá utilizar un tipo flotante o real. Estos tipos de datos tienen generalmente más bytes de almacenamiento. • Los tipos de datos date y time, obviamente manejan fechas y horas. Estos tipos de datos incluyen date, datetime2, datetime, datetimeoffset, smalldatetime y time. • SQL Server soporta conversiones implícitas, que pueden ocurrir sin especificar la llamada de alguna función (cast o convert). Las conversiones explicitas de hecho requieren que utilice las funciones cast o convert. • Un carácter regular utiliza un byte de almacenamiento para cada carácter, lo cual le permite definir uno de 256 (hay 8 bits en un byte y 2^8=256) caracteres disponibles que son compatibles con el idioma inglés y otras lenguas europeas. • Un carácter Unicode utiliza dos bytes de almacenamiento por carácter para que pueda representar uno de 5,536 (hay 16 bits en 2 bytes y 2^16=65,536 caracteres). El carácter adicional permite almacenar caracteres de casi cualquier idioma. • Cuando utiliza un elemento VAR, SQL Server conservará espacio en la fila en la cual reside con base en el tamaño definido de la columna y no en el número de caracteres que haya en la cadena. • Las cadenas de caracteres Unicode nchar y nvarchar pueden ser ya sea fijas o variables así como sus cadenas de caracteres regulares; estas utilizan el conjunto de caracteres UNICODE UCS-2. • El propósito de una tabla es proporcionar una estructura para el almacenamiento de los datos en una tabla relacional. • Una vista es simplemente una tabla virtual que consiste de diferentes columnas de una o más tablas. A diferencia de una tabla, una vista se almacena en la base de datos como un objeto de consulta; por lo tanto, una vista es un objeto que obtiene sus datos de una o más tablas.

ETC

IBE RO

www.pdftron.com

Lección 2

» Evaluación de Conocimientos

AM ÉRI

Complete la frase

• Un procedimiento almacenado es una sentencia escrita previamente en SQL que se ha “almacenado” o guardado en la base de datos. • Una inyección SQL es un ataque en el cual código malicioso se inserta en cadenas que luego pasan a las instancias de SQL Server que esperan ser ejecutadas.

Complete los siguientes enunciados escribiendo la palabra o palabras correctas en los espacios en blanco proporcionados. 1. Cada ____________, ____________, expresión y ____________ tienen siempre un tipo de datos relacionado. 2. El bit es un tipo de datos entero Transact SQL que puede tomar un _______ de 1, 0 o NULL. 3. ____________ y time son sentencias Transact SQL que tienen: ____________, ____________, smalldatetime y time. 4. Es importante que considere utilizar conjuntos de datos ____________ cuando desarrolle tablas dependientes de los horarios de verano.

www.pdftron.com 5. SQL Server soporta conversiones ____________ sin utilizar el llamado de las funciones cast o convert.

IBE RO

6. Un carácter regular utiliza _______ byte de almacenamiento para cada carácter y un carácter Unicode requiere de ______ bytes de almacenamiento.

7. El conjunto de datos char tiene una longitud ______ y una longitud de __________ bytes. 8. El objetivo de una tabla es proporcionar una _______ para el almacenamiento de datos en una tabla relacional. 9. Cuando decida crear una vista, asegúrese de considerar el ______ en su diseño. 10. Cuando se realiza una consulta en una base de datos, se pueden obtener resultados más rápidos al ______ apropiadamente las tablas y vistas. Opción múltiple

Encierre la letra que corresponda a la respuesta correcta. 1. ¿Cuál de las siguientes opciones no es un tipo de datos? Numéricos exactos Numéricos aproximados Cadena ANSI Espacial

ETC

a. b. c. d.

Crear objetos de bases de datos

a. b. c. d. e.

char text vchar null

2. Cuando se determina un grupo de resultados por las reglas de la precedencia de agrupación, ¿cuál de los siguientes no es un tipo de datos resultante?

3. ¿Cuál de las siguientes opciones no describe un tipo de datos int?

integer bigint se utiliza cuando el valor excederá el rango del tipo de datos Int SQL server no convierte otros tipos de datos int a bigint las funciones solo regresarán bigint si la expresión original es del mismo tipo de datos

AM ÉRI

a. b. c. d.

4. ¿Requieren las conversiones implícitas de invocación: cast o convert? a. Si b. No

5. Seleccione todos los enunciados que sean falsos

IBE RO

www.pdftron.com 6. ¿Cuál de las siguientes no es soportado por ALTER? a. b. c. d.

Agregar una columna nueva a una tabla. Eliminar múltiples columnas de una tabla existente. Modiﬁcar el tipo de dato de una columna existente. Cambiar la restricción de IDENTIDAD de una columna existente.

7. ¿Cuál de los siguientes no es una restricción? a. b. c. d.

NULL UNIQUE CHECK PRIMARY

ETC

8. ¿Qué signiﬁca SQL?

a. Lenguaje de Pregunta Estructurada b. Lenguaje de Consulta Estructurada c. Lenguaje de Pregunta Fuerte

9. ¿Cuál sentencia SQL es utilizada para extraer datos de una base de datos?

Lección 1

a. b. c. d.

SELECT OPEN EXTRACT GET

a. b. c. d.

SAVE MODIFY SAVE AS UPDATE

» Evaluación de aptitud

AM ÉRI

10. ¿Cuál sentencia SQL es utilizada para actualizar datos en una base de datos?

Escenario 2-1: Cálculo del tamaño de una tabla

Como el nuevo administrador de bases de datos, se le ha dicho que necesita crear una base de datos grande de empleados que almacenará la información de 10,000 empleados. Digamos que tiene las siguientes columnas deﬁnidas en la tabla: Nombre

varchar(50)

www.pdftron.com Apellido

varchar(75)

IBE RO

Dirección

varchar(50)

Estado

varchar(2)

varchar(9)

Edad

int

Fechanacimiento

date

¿Cuántos bytes se necesitan para almacenar cada registro? ¿Cuántos bytes utilizaría para almacenar 10,000 registros? Escenario 2-2: Selección de tipos de datos

ETC

Se encuentra diseñando varias bases de datos para un cliente. Mientras diseña las bases de datos, encuentra los elementos de datos enlistados. ¿Cuál tipo de datos utilizaría para almacenar la siguiente información y por qué lo usaría? Radio de un planeta El valor de pi (π) Salario

Fecha de nacimiento de una persona

Crear objetos de bases de datos

La longitud de un tablón

Cantidad vendida de reproductores de música

» Evaluación de destreza

AM ÉRI

Escenario 2-3: Entender las tablas y cómo crearlas

Se le ha contratado como el nuevo administrador de base de datos. Necesita crear una base de datos para los clientes. En este ejercicio, creará una tabla a la vez que deﬁne una restricción de clave principal después de haber creado la tabla. Después de abrir el SSMS y accede a la base de datos AdventureWords, ¿cuál comando de consultas utilizaría para crear una tabla llamada customers. con las siguientes columnas o campos? Unique Customer ID

CompanyID – Hasta 50 caracteres

www.pdftron.com FirstName – Hasta 50 caracteres – Hasta 50 caracteres

IBE RO

LastName

ModifedDate

Escenario 2-4: Extracción de datos de una base de datos

Usted es un administrador de base de datos de la empresa AdventureWorks. Un gerente de servicios le pide que extraiga datos de la base de datos AdventureWorks. Por lo tanto, después de abrir el SSMS y acceder a la base de datos AdventureWorks, especifique las sentencias SELECT que utilizaría para recuperar la información requerida de la tabla ProductsSubcategory. 1. ¿Cuáles comandos regresarían todas las filas y columnas en esa tabla ProductCategory? 2. ¿Cuáles comandos escribiría y ejecutaría para regresar solo las columnas ProductSubcategoryID, ProductCategoryID, Name, y ModifiedDate?

ETC

3. ¿Cuáles comandos escribiría y ejecutaría para regresar las ﬁlas en donde se encontrara la palabra bike en la columna Name? 4. En la ventana de consulta existente, ¿Cuáles comandos utilizaría para agregar un alias de columna a la columna Name para desplegarla como el nombre de la subcategoría?

5. En la ventana de consulta existente, ¿cuál comando utilizaría para ordenar los ORDER BY [Subcategory Name]; resultados anteriores?

CA AM ÉRI

ETC

IBE RO

www.pdftron.com

Lección 3

Manipulación de datos Matriz de Dominio de objetivos

Uso de consultas para seleccionar datos Uso de consultas para insertar datos Actualizar datos y bases de datos Eliminar datos

Términos Clave

Descripción del Dominio del objetivo Seleccionar datos Insertar datos Actualizar datos Eliminar datos

Número del objetivo

AM ÉRI

Habilidades/Conceptos

3.1 3.2 3.3 3.4

www.pdftron.com • Join • Integridad referencial • Select

IBE RO

• Combinación cruzada (cross join) • Delete • Intersect

• Transacción • Union • Update

ETC

Recién lo ha contratado una empresa mediana con pocos empleados y necesita proporcionar listas de registros de los mismos para el recién contratado Vicepresidente de Recursos Humanos. Necesita crear una variedad de listas diferentes utilizando varias sentencias Transact SQL para manipular los datos y así cumplir con las necesidades que se le soliciten.

Lección 3

Aun cuando el título de este capítulo describa la manipulación de datos con el propósito de realizar consultas, también se considera la modiﬁcación de datos a medida que modiﬁque las sentencias de consulta para obtener los resultados deseados. No importando el nombre o descriptor, el propósito de cualquier base de datos es ofrecer al usuario la capacidad de extraer datos de la misma. La amplia mayoría de las sentencias de SQL se diseñaron para recuperar información solicitada por el usuario a través de diversas sentencias de consultas. Veamos las sentencias SQL más importantes que necesitará para entender de mejor manera Transact SQL.

AM ÉRI

Uso de consultas para seleccionar datos È EN RESUMEN

En esta sección, aprenderá a utilizar la consulta SELECT para recuperar o extraer datos de una tabla, a recuperar o extraer datos utilizando combinaciones (joins) y a combinar resultados con UNION e INTERSECT. ; Listo para la certiﬁcación ¿Cuál comando se utiliza para mostrar registros de una tabla?—3.1

El comando SQL para recuperar datos de una base de datos es SELECT. De manera similar a otros comandos de SQL, la redacción es similar a la de enunciados en el idioma inglés. La redacción de una sentencia SELECT es como si se completaran espacios en blanco, como se indica a continuación:

www.pdftron.com

IBE RO

SELECT id, name //columnas FROM sysobjects // tablas WHERE type = “jones” //condiciones de las cuales quiere obtener resultados

Resulta ser una sentencia sencilla, pero proporciona una explicación básica de lo que hace la sentencia SELECT y siempre seguirá el mismo patrón cada vez que utilice una sentencia SELECT para una base de datos.

Hay únicamente tres cosas que debe asegurarse de identiﬁcar en su sentencia para elaborar una sentencia de consulta SELECT correcta: • Columnas a recuperar • Tablas de las cuales recuperar dichas columnas • Condiciones que deben cumplir los datos

ETC

Los constructores anteriores son la estructura para la creación de sentencias de consulta SELECT utilizando la ventana de edición de texto de SQL. Supongamos, por ejemplo que necesita proporcionarle a su jefe una lista de empleados cuyo salario sea superior a $50 mil. Sólo está interesado en recuperar los empleados que cumplan con esos criterios. He aquí como obtendría esos resultados en SQL: SELECT nombre, apellido, salario FROM empleados WHERE salario >= 50,000

Esa consulta produciría los siguientes resultados:

Manipulación de datos

nombre -----------------

apellido -------------------

salario -------------

John Sylvia Julia David

Allan Goddard Smith Thompson

52,000 51,200 55,000 62,900

(4 fla(s) afectadas)

¿Puede ver la línea que informa sobre la cantidad de ﬁlas “afectadas”? Puede desactivar o activar esta opción utilizando la sentencia SET NOCOUNT. De forma predeterminada, se encuentra desactivada —3.1

Si quiere seleccionar una sola columna para su consulta, identifique el nombre de la misma ingresándola entre las palabras select y from en la sentencia de la consulta. Si quiere identificar más de una columna en su consulta, agregue cada nombre de columna y separe sus nombres con una coma. La coma se utiliza en lugar del espacio en blanco para que SQL la utilice como un identificador o palabra de concordancia como “value” o “select”, sin embargo, si necesita utilizar un espacio en blanco en su sentencia, utilice corchetes o comillas dobles en los extremos. Por ejemplo, [select] o “value”.

AM ÉRI

º Tome Nota

Si desea seleccionar todos los nombres de las columnas de una tabla, lo puede hacer si coloca un (*) donde se vaya a identiﬁcar el nombre de la columna. El único componente requerido de la consulta SQL SELECT es la cláusula SELECT… FROM, lo que signiﬁca que puede seleccionar todos los campos disponibles de una tabla si utiliza el siguiente comando:

www.pdftron.com

IBE RO

SELECT * FROM empleados

Con esto se puede producir un resultado muy amplio o pequeño, dependiendo de la cantidad de empleados en la compañía. Utilicemos un ejemplo de una empresa con sólo 6 empleados de los cuales queremos una lista. Nuestro resultado se vería similar al siguiente: Nombre -------

apellido ----------

Jim Francis David Alexandria Peter Antoin

empleado_id -----------

Alexander Drake Thompson Link Link Drake

610001 610002 610003 610004 610005 610006

telefono ---------

sexo ---------

574-555-0001 574-555-0346 574-555-0985 574-555-9087 574-555-7863 574-555-2597

M F M F M M

(6 fla(s) afectadas)

ETC

Ahora cuenta con una compresión básica sobre la sentencia SELECT, así también necesitará ser específico respecto a de “dónde” (WHERE) recuperará los tipos de datos de tablas específicas que haya identificado en su sentencia de consulta SELECT… FROM. La cláusula WHERE se puede utilizar para consultar los empleados que trabajan en el departamento de Paquetería de la empresa como aparece a continuación: SELECT nombre, apellido WHERE departamento = ‘paquetería’

Lección 3

nombre ----------

apellido --------

Jim Francis David

Alexander Drake Thompson

AM ÉRI

(3 fla(s) afectadas)

Esta sentencia regresaría el siguiente resultado:

f Combinación de condiciones

Si cuenta con mayor información de la requerida de una consulta que únicamente tiene un conjunto de parámetros para realizar la búsqueda de la información, puede combinar varias condiciones en una sentencia de consulta que satisfaga sus necesidades. En el ejemplo anterior, realizamos una consulta que localiza los empleados que trabajan en el departamento de Paquetería, pero tal vez quiera ese resultado y además (AND) necesite encontrar los empleados que sean mujeres. En este caso, la condición de la sentencia SQL sería como se menciona a continuación: SELECT nombre, apellido FROM empleados WHERE departamento = ‘paquetería’ AND sexo = ‘F’ AND contratado >= ‘2000-JAN-01’

www.pdftron.com Los resultados serían los siguientes: apellido --------Drake

IBE RO

Nombre ----------Francis

(1 fla(s) afectadas)

El ejemplo anterior muestra a todos los empleados que están en el departamento Paquetería que son mujeres: También se puede utilizar la conjunción OR para hacer que las ﬁlas regresen un resultado que cumpla con una de las condiciones. SELECT nombre, apellido FROM empleados WHERE departamento = ‘paquetería’ OR empleado_id <= 610007

ETC

El resultado sería:

nombre -----------

apellido ---------

James David Francis Alexandria Peter Antoin (6 ﬁla(s) afectadas)

Alexander Thompson Drake Link Link Drake

Manipulación de datos

Uso de la cláusula BETWEEN

En el ejemplo anterior, observará los mismos tres empleados del departamento de Paquetería de la consulta anterior (puesto que cumplen con la primera condición), así como tres ﬁlas nuevas de datos que cumplen con la segunda condición de encontrar el número de empleado que sin embargo no cumple con la otra condición de estar en el departamento de Paquetería. Esto signiﬁca que Alexandria Sinclair, Peter Link and Antoin Jones cumplen con la segunda condición en la sentencia de consulta WHERE.

AM ÉRI

En algunas situaciones tal vez necesite recuperar registros que cumplan con un rango de condiciones donde se contenga también un valor que se encuentre entre un rango de otro valor especíﬁco. Por ejemplo, tal vez necesite recuperar una lista de empleados que cumplan con las condiciones anteriores pero que además dicho listado muestre un rango de fechas de contratación para los empleados que se contrataron entre 1990 y el 2000. Una forma de lograr este resultado es utilizando las dos condiciones combinadas con la conjunción AND. Esto se logra de la siguiente forma: SELECT nombre, apellido, fecha_contratacion FROM empleados WHERE fecha_contratacion >= ‘1-Jan-1990’ AND fecha_ contratacion <= ‘1-Jan-2000’ Estas sentencias producirán los resultados deseados con base en las otras sentencias de consulta. El resultado sería:

www.pdftron.com apellido ---------

IBE RO

nombre -----------

James Francis Peter Antoin

Alexander Drake Link Drake

fecha_contratacion -----------1990-12-10 1998-03-04 1997-07-08 1999-12-31

(4 fla(s) afectadas)

Tal vez se pregunte lo que en realidad haga la consulta con base en las dos condiciones y por qué la sentencia se ve rara en cuanto a su redacción. Puede resolver la cláusula AND reemplazándola con una cláusula BETWEEN. Al hacerlo, podrá especiﬁcar un rango a utilizar “entre x y y” en un formato de consulta mucho más claro. Reescribamos la sentencia anterior para utilizar la condición between:

ETC

SELECT nombre, apellido, fecha_contratacion FROM empleados WHERE fecha_contratacion BETWEEN ‘1-Jan-1990’ AND ‘1Jan-2000’ Recibirá el mismo resultado que la sentencia de consulta anterior.

Uso de la cláusula NOT En algunos casos es más sencillo escribir su consulta para buscar datos en términos de lo que no quiere obtener. Transact SQL le ofrece la cláusula NOT para tales situaciones. Una situación en la cual pueda necesitar esta cláusula es por ejemplo en nuestras sentencias anteriores SELECT. Podría necesitar un resultado que muestre los empleados que no

Lección 3

trabajen en el departamento de Paquetería:

SELECT nombre, apellido FROM empleados WHERE NOT departamento = ‘paquetería’

El uso de operadores, como se muestra en varios de los ejemplos anteriores, pueden ayudar a lograr los mismos resultados en la mayoría de las instancias. Se puede escribir una consulta utilizando < (menor que) o un > (mayor que) en lugar de un signo igual. La sentencia de consulta sería como aparece a continuación:

AM ÉRI

SELECT nombre, apellido FROM empleados WHERE departamento <> ‘paquetería’

Sin importar el sentido en el cual escriba la sintaxis para la sentencia de consulta, esta producirá los mismos resultados.

Uso de la cláusula UNION

La cláusula UNION le permite combinar los resultados de dos o más consultas en un solo conjunto resultante que incluirá todas las ﬁlas que pertenezcan a la consulta en esa unión. La cláusula UNION es completamente diferente a las sentencias JOIN en la cuáles combina columnas de dos tablas diferentes. Hay un par de reglas básicas que necesita seguir al combinar resultados de dos consultas a través del uso de la cláusula UNION:

www.pdftron.com

IBE RO

• La cantidad y el orden de las columnas debe ser el mismo en cada una de las consultas que utilice en esta cláusula. • Los tipos de datos que vaya a utilizar deben ser compatibles.

La consulta para utilizar la cláusula UNION en nuestros ejemplos podría ser el siguiente: SELECT nombre, apellido FROM empleados WHERE departamento = ‘paquetería’ UNION SELECT nombre, apellido FROM empleadosWHERE fecha_contratacion BETWEEN ‘1-Jan1990’ AND ‘1-Jan-2000’

Se produciría un resultado con una lista de todos los empleados en el departamento de Paquetería de los que fueron contratados entre dos fechas especíﬁcas.

ETC

Uso de las cláusulas EXCEPT e INTERSECT

Estas dos cláusulas se encuentran diseñadas para regresar valores distintivos al comparar el resultado de dos consultas. La cláusula EXCEPT regresa cualquiera de esos valores distintivos de la consulta izquierda que no se encuentren en la consulta derecha, mientras que la cláusula INTERSECT regresa cualquiera de los valores distintivos que no se hayan regresado por ninguna de las consultas (izquierda o derecha) de este operando. Las mismas dos reglas básicas para la combinación de los conjuntos de resultados de las dos consultas para cualquiera de las cláusulas aplican aquí de la misma forma que con la cláusula UNION:

Manipulación de datos

• La cantidad y el orden de las columnas debe ser el mismo en todas las consultas. • Los tipos de datos deben ser compatibles.

Un ejemplo de este tipo de sentencia de consulta para EXCEPT e INTERSECT en un ambiente laboral sería en un almacén, donde por ejemplo, le gustaría recuperar una lista de productos que tengan órdenes de trabajo y otra que no las tengan. Podría realizar la consulta como se indica a continuación, primero utilizando la cláusula INTERSECT:

AM ÉRI

SELECT ProductID FROM Production.Product // El nombre de a base de datos es Production y el de la tabla es Product INTERSECT SELECT ProductID FROM Production.WorkOrder; --Resultado: 238 Filas (productos que tienen órdenes de trabajo) La misma consulta, pero utilizando la cláusula EXCEPT:

SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; --Resultado: 266 Filas (productos que no tienen órdenes de trabajo)

IBE RO

www.pdftron.com Uso de la cláusula JOIN

A medida que empiece a adquirir conocimientos más complejos sobre las sentencias de consulta y sobre cómo cada cláusula manipula los datos, descubrirá las capacidades de una base de datos relacional.

La cláusula JOIN le permite combinar datos relacionados de diferentes tablas fuente. Las sentencias JOIN se parecen a las EXCEPT e INTERSECT en que regresan valores de dos tablas separadas. Con base en esto, podemos continuar para aprender cuales datos se pueden obtener a través de las sentencias JOIN. Las sentencias JOIN se pueden especiﬁcar en las cláusulas FROM o WHERE, pero se recomienda que las especiﬁque en la cláusula FROM.

ETC

Hay tres tipos de sentencias JOIN que debe tener en cuenta:

• INNER JOIN le permite relacionar registros tomados de diferentes tablas fuente. • OUTER JOIN puede incluir registros de una o ambas tablas que consulte que no tengan registros correspondientes en la otra tabla. Hay tres tipos de joins externos: LEFT OUTER JOIN, RIGHT OUTER JOIN y FULL OUTER JOIN

• CROSS JOIN regresará todas las ﬁlas de una tabla con todas las ﬁlas de la otra tabla. Se deben incluir siempre condiciones WHERE.

Un ejemplo de las sentencias JOIN más comunes, INNER JOIN, puede ser si quiere recuperar una lista de empleados por sus números ID y relacionarlos con los ID de los supervisores de departamento con los que actualmente se encuentran trabajando. Para realizar este tipo de consultas, tendrá que identiﬁcar la columna de relación en las dos

Lección 3

Tabla empleados

Tabla 3-2 Tabla departamento

tablas que vaya a utilizar en la consulta. En este ejemplo, la clave única o foránea en la Tabla 3-1 se identiﬁca en la columna “departamento_id” y en la tabla 3-2 se identiﬁca como la columna de relación “departamento”: nombre James David Francis Alexandria Peter

apellido Alexander Thompson Drake Link Link

Antoin David

Drake Cruze

empleado_id 610001 620002 610003 610004 620005

departamento 1 1 1 2 2

Tabla 3-1

AM ÉRI

departamento_id 1 2 3

610006 610007

nombre Jane Mitch Paul

3 NULL

apellido Horton Simmons Franklin

Tratar de combinar los datos entre las tablas puede ser una tarea muy pesada, sobre todo si hay miles de ﬁlas de datos de las cuales se deban crear listas especíﬁcas. La sentencia SELECT puede producir listas individuales, pero, toda la información que necesite, será en una lista individual.

IBE RO

www.pdftron.com Las palabras clave INNER JOIN simpliﬁcarán la recuperación de estos datos pero no sólo incluyendo la información de las dos tablas que quiera consultar, sino incluyéndolas en la cláusula FROM utilizando las palabras clave INNER JOIN y especiﬁcando las condiciones requeridas. Por ejemplo, de las dos tablas de ejemplo, tal vez quiera crear una lista que muestre cuales empleados trabajan para cada uno de los diferentes supervisores de departamento. Deberá escribir la sentencia SQL como se indica a continuación: SELECT empleados.nombre, empleados.apellido, departamento.nombre, departamento.apellido FROM empleados INNER JOIN departamento ON empleados.departamento = departamento.departamento_id Los resultados serían:

apellido ---------

nombre ------------

apellido ----------

James David Francis Alexandria Peter Antoin

Alexander Thompson Drake Link Link Drake

Jane Jane Jane Mitch Mitch Paul

Horton Horton Horton Simmons Simmons Franklin

ETC

nombre --------

(6 fla(s) afectadas) ¿Observó que David Cruze no se incluyó en la lista de resultados de los empleados

Manipulación de datos

relacionados con los departamentos? En la columna departamento, su nombre no se identiﬁcó como perteneciente a ningún departamento, aun cuando él es un empleado. Esto puede deberse a una variedad de razones, tal vez porque se le acaba de contratar y no ha empezado a trabajar oﬁcialmente en ningún departamento.

AM ÉRI

Tal vez su jefe quiera una lista de registros de la segunda tabla que de hecho no cumpla con ningunas de las condiciones anteriores. Cualquiera de las sentencias OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN o FULL OUTER JOIN pueden realizar el resultado deseado. Las sentencias OUTER JOIN empiezan donde los resultados de INNER JOIN terminan e incluyen todos los registros en la tabla izquierda junto con los registros coincidentes de la tabla derecha e incluyen cualquiera de los registros no coincidentes. Un ejemplo de la sentencia LEFT OUTER JOIN incluye la sentencia INNER JOIN mostrada con anterioridad e incluye una cláusula de no coincidencia como aparece a continuación: SELECT empleados.nombre, empleados.apellido, departamento.nombre, departamento.apellido FROM empleados LEFT OUTER JOIN departamento ON empleados.departamento = departamento.departamento_id El resultado sería: nombre --------

apellido ---------

nombre ------------

apellido ----------

James David Francis Alexandria Peter Antoin David

Alexander Thompson Drake Link Link Drake Cruze

Jane Jane Jane Mitch Mitch Paul NULL

Horton Horton Horton Simmons Simmons Franklin NULL

IBE RO

www.pdftron.com (7 flas afectadas)

Ahora observará que la única diferencia entre las sentencias INNER JOIN y OUTER JOIN es la inclusión de David Cruze quien, como se mencionó con anterioridad, no se

encuentra asignado a ningún supervisor de departamento, por lo que su nombre muestra un valor NULL en la lista donde las columnas se encuentran identiﬁcadas por cada nombre y apellido de supervisor.

ETC

En algunos casos, tal vez quiera tener una combinación de tabla consigo misma. A esto se le llama autocombinación debido a las instancias cuando quiera comparar registros desde la misma tabla hacia las demás. Este tipo de tablas generalmente se encuentran cuando se crean jerarquías organizacionales y se requiere de resultados de la misma. Por ejemplo, tal vez quiera encontrar cuantos autores viven en la misma ciudad y proporcionar una lista a una casa editorial. Este resultado se obtendría utilizando la siguiente sentencia SELF-JOIN:

USE pubs SELECT autor1.nombre, autor1. apellido, autor2.nombre, autor2.apellido

Lección 3

El resultado sería:

FROM autor1 INNER JOIN autor2 ON autor1.zip = autor2.zip WHERE autor1.ciudad = ‘Pittsburgh’ ORDER BY autor1.nombre ASC, autor1.apellido ASC

nombre --------

apellido ---------

nombre ------------

apellido ----------

David David David Alex Alex Alex Linda Linda Linda Delina Jules

Jones Jones Jones Starr Starr Starr Arrow Arrow Arrow Burris Allan

David Alex Linda David Alex Linda David Alex Linda David Jules

Jones Starr Arrow Jones Starr Arrow Jones Starr Arrow Burris Allan

AM ÉRI

(11 flas afectadas)

www.pdftron.com Si desea eliminar las ﬁlas donde se repita el mismo autor, lo logrará con la sentencia SELF-JOIN:

IBE RO

USE pubs

SELECT autor1.nombre, autor1. apellido, autor2.nombre, autor2.apellido FROM autor1 INNER JOIN autor2 ON autor1.zip = autor2.zip WHERE autor1.ciudad = ‘Pittsburgh’ AND autor1.estado = ‘PA’ AND autor1.autor_id < autor2.autor_id ORDER BY autor1.nombre ASC, autor1.apellido ASC La salida resultante sería:

apellido ---------

nombre ------------

apellido ----------

David David Alex

Jones Jones Starr

Alex Linda Linda

Starr Arrow Arrow

ETC

nombre --------

(3 flas afectadas) De los resultados obtenidos de la sentencia de consulta, David Jones, Alex Starr y Linda

Manipulación de datos

Arrow viven todos en Pittsburgh, PA y tienen los mismos códigos postales (ZIP).

Uso de consultas para insertar datos È EN RESUMEN

; Listo para la certiﬁcación ¿Cuál comando se utiliza para agregar datos a una tabla? —3.2

AM ÉRI

El objetivo es comprender cómo se insertan datos en una base de datos y cómo utilizar las sentencias INSERT.

Hay varias formas diferentes en las cuales SQL Server le permite insertar datos en su base de datos. Hay diferentes herramientas de inserción disponibles que le permitirán unirlos. Si quiere insertar cantidades pequeñas de datos, por ejemplo, agregar nuevas ﬁlas en su base datos, lo puede hacer de dos formas diferentes. La primera es utilizando la interfaz gráﬁca o con la sentencia INSERT.

Insertar datos

Aprendamos primero cómo insertar datos en una tabla utilizando SQL Server Management Studio antes de continuar con el método de sintaxis.

www.pdftron.com Æ Insertar datos con SQL Server Management Studio

IBE RO

PREPÁRESE. Antes de empezar, asegúrese de abrir SQL Server Management Studio y conectarse a la base de datos con la que quiera trabajar.

Figura 3-1

1. Asegúrese de haberse conectado a la base de datos con la que quiera trabajar (vea la Figura 3-1).

ETC

Conectándose a la base de datos

2. Expanda la carpeta de bases de datos al hacer clic en el signo de más (+) junto a la palabra “bases de datos”. 3. Expanda la carpeta de la base de datos con la que va a trabajar y a modiﬁcar.

Lección 3

Figura 3-2

4. Expanda la carpeta Tablas haciendo clic en el signo de más. 5. Haga clic derecho en el nombre de la tabla y seleccione Editar las primeras 200 ﬁlas (vea la Figura 3-2).

AM ÉRI

Editar las primeras 200 ﬁlas

Figura 3-3 Pantalla resultante de Editar las primeras 200 ﬁlas

www.pdftron.com

ETC

IBE RO

La Figura 3-3 muestra la pantalla resultante. Si tiene un valor predeterminado como el campo IntIdentity, no necesitará proporcionar el campo en su sentencia de consulta:

6. Introduzca sus datos en la última fila de la tabla para los datos que se consideren nuevos (insertados). La última fila de la tabla contendrá valores NULL en cada una de las columnas. Si resalta cada uno de los valores con el mouse se insertarán automáticamente datos en la tabla. Vea la figura 3-3.

Manipulación de datos

Conﬁguraciones para las Propiedades del Diseñador de Tablas

Caracter comodín % [a-f] [^a-f] [abc]

AM ÉRI

[^abc]

Descripción Cualquier carácter único Cualquier serie de cero o más caracteres Cualquier carácter único en el rango de a-f Cualquier carácter único que no esté en el rango a-f Cualquier carácter único contenido en la lista (a, b, c) Cualquier carácter único que no esté en la lista (a,b,c)

Tabla 3-3

PAUSA. Deje SQL Server Management Studio abierto para el siguiente ejercicio.

El otro método de utilizar la cláusula insert se realiza al escribir una sentencia SQL en la ventana de edición de texto, lo que le dará el mismo resultado que la interfaz gráﬁca si utiliza la siguiente sintaxis: INSERT INTO <nombre_tabla> (<columnas>) VALUES (<valores>) º Tome Nota Aunque la lista de columnas en la sentencia insert es completamente opcional, se recomienda especiﬁcar cuáles columnas desea utilizar, puesto que SQL asumirá automáticamente que su lista de valores incluirán todas las columnas en el orden correcto.

Las dos cláusulas <columnas> y <valores> tendrían sus listas separadas por comas de los nombres de las columnas en la tabla que desee incluir junto con los valores que quiera insertar.

www.pdftron.com

IBE RO

No tiene que insertar sólo una fila de forma individual en la sentencia insert sino que puede indicar varias filas utilizando una coma para separarlas. Esto es similar a cuando observa un archivo .CSV de Excel donde vería comas que separan columnas de información cuando se importe para utilizarlo en otra base de datos, como Access. Pero, dentro de una sentencia INSERT, cada una de las filas identificadas por comas se encerrará entre paréntesis.

Para escribir una sentencia INSERT (en el siguiente ejemplo, se agregan dos empleados a la tabla empleados), se vería como aparece a continuación: INSERT INTO empleados (nombre, apellido, empleado_id, departamento) VALUES (‘David’, ‘Clark’, 610008, ‘paquetería), (‘Arnold’, ‘Davis’, 610009, ‘contabilidad’) El resultado sería:

ETC

(2 fla(s) afectadas)

Es realmente sencillo aprovechar estas poderosas características para modiﬁcar y administrar una base de datos. Sin embargo, existen otras modiﬁcaciones de datos o manipulaciones que veremos más adelante.

Lección 3

Actualizar datos y bases de datos

È EN RESUMEN

Es importante comprender cómo se actualizan los datos en una base de datos y cómo escribir los datos actualizados en la base de datos utilizando las sentencias UPDATE apropiadas.

¿Cuál comando se utiliza para modiﬁcar los datos existentes en una tabla? —3.3

La función de la sentencia UPDATE es modificar los datos en una tabla o vista. Muy similar a cualquier manipulación o modificación de datos, las cláusulas y sentencias disponibles para utilizar en SQL pueden realizarse con la interfaz gráfica o en la ventana de edición de texto.

AM ÉRI

; Listo para la certiﬁcación

Uso de la sentencia UPDATE

La cláusula UPDATE le permite modiﬁcar los datos que se encuentran almacenados en tablas utilizando atributos de datos como se muestra a continuación: UPDATE <nombre_tabla> SET <atributo> = <valor> WHERE <condiciones>

www.pdftron.com

IBE RO

Esta sentencia se parece a otras que hemos visto con anterioridad. Quiere actualizar una tabla, en la que un identificador de columna refleje un valor específico. Tal vez quiera tener un atributo de un supervisor nuevo (piense en nuestro ejemplo de empleados), Doug Able, siendo asignado a los empleados nuevos con el propósito de capacitación. Ese supervisor podría tener el atributo, si vemos de vuelta nuestra tabla departamento de un ID de 4 y se cumpliría con la cláusula WHERE respecto a la condición NULL de los empleados sin supervisor. Veamos un escenario en el cual la sentencia UPDATE cumpla con el ejemplo anterior. Primero agreguemos un registro en la tabla departamento para agregar el nombre de nuestro nuevo supervisor y la información de ID de departamento con la sentencia INSERT:

INSERT INTO departamento(nombre,apellido,departamento_id) VALUES (‘Doug’, ‘Able’, 4) Nuestra respuesta sería:

(1 fla(s) afectadas)

ETC

º Tome Nota

Si no especiﬁca los registros a actualizar, todos los registros o ﬁlas se actualizarán con el nuevo valor. Esto es potencialmente dañino.

Ahora, necesitamos actualizar nuestra tabla empleados para reﬂejar a los empleados que no tengan asignado un supervisor de departamento. La sentencia UPDATE sería como se indica a continuación: UPDATE empleados SET departamento = 4 WHERE departamento IS NULL

Manipulación de datos

Los resultados aparecen en la Tabla 3-4.

2010-04-

dbo

CREATEVIEW

dbo

vDVMPrep

CREATE

03…. 2010-04-

dbo

CREATE_VIEW

dbo

vTimeSeries

VIEW [… CREATE

03…. 2010-04-

dbo

NULL

03…. NULL

NULL

AM ÉRI

Valores NULL en la columna departamento

Tabla 3-4

CREATE_VIEW

dbo

vTargetMail

VIEW [… CREATE

NULL

VIEW [… NULL

<EVENT_ INSTA…

NULL

Si revisa de vuelta la Tabla 3-1, observará que un empleado, David Cruze, no tenía un ID de departamento asignado a su registro en la tabla empleados. Su valor se representó como NULL en esa columna de departamento. Puesto que agregamos este nuevo supervisor en la tabla departamento en nuestra sentencia insert into ahora el resultado de David Cruze tiene a Doug Able como supervisor asignado. Para actualizar una tabla con la interfaz gráﬁca, necesita seguir los siguientes pasos:

www.pdftron.com

IBE RO

• Abra Server Management Studio • Abra la tabla que quiera actualizar • Localice la ﬁla de la cual quiera actualizar los registros en la vista de tabla desplegada

Eliminar datos È EN RESUMEN

Es importante comprender cómo eliminar datos de una o varias tablas y asegurar los datos y la integridad referencial utilizando transacciones. ; Listo para la certiﬁcación

La sentencia DELETE elimina filas de una tabla o vista. Hay varias formas de llevar a cabo esta tarea. Puede identificar y eliminar filas individuales de una base de datos utilizando la sintaxis delete, eliminar todas las filas con la sentencia truncate table o eliminar toda la tabla con la sentencia drop table. Todo depende de las necesidades que tenga o de la cantidad de datos que necesite eliminar.

ETC

¿Cuáles comandos se utilizan para eliminar datos de una base de datos? —3.4

Uso de la sentencia DELETE

º Tome Nota

Puede utilizar la sentencia DELETE o eliminar una o más ﬁlas en una tabla o vista con la siguiente sentencia SQL:

Las ﬁlas que se eliminarán sólo son de la tabla que es llamada en la cláusula delete (tabla_o_vista)

DELETE FROM <nombre_tabla> WHERE <conditiones>

Lección 3

Puede utilizar la sentencia DELETE en diferentes situaciones, por ejemplo, debido a cambios en la empresa, necesita quitar los empleados del departamento de contabilidad de la tabla empleados. Podría utilizar este departamento utilizando el siguiente comando:

El resultado sería: (1 fla(s)

afectada)

DELETE FROM empleados WHERE departamento = ‘contabilidad’

AM ÉRI

Estos resultados muestran que el empleado que trabajaba en el departamento de contabilidad se ha eliminado de la tabla empleados.

Truncar una tabla con TRUNCATE TABLE

Tal vez quiera eliminar todas las ﬁlas de una tabla en particular, en este caso puede utilizar la sentencia TRUNCATE TABLE, aun cuando podría verse tentado a utilizar DELETE y la condición WHERE. Si lo hace, se produciría el mismo resultado pero podría tomarle mucho más tiempo si elimina ﬁlas de bases de datos muy grandes. La sintaxis TRUNCATE TABLE se realiza de la siguiente manera: TRUNCATE TABLE <nombre_tabla>

www.pdftron.com Cada resultado exitoso de SQL produciría lo siguiente:

IBE RO

Consulta ejecutada correctamente.

La sentencia TRUNCATE TABLE sólo elimina los datos de la tabla pero dejará la estructura para poder utilizarla más adelante.

Eliminar una tabla con DROP TABLE

En caso de que sólo quiera borrar toda la tabla al volverse obsoleta o porque simplemente necesita eliminar una tabla de la base de datos. Para eliminar una tabla entera se realiza lo siguiente: DROP TABLE <nombre_tabla>

Uso de la integridad referencial

ETC

Uno de los pasos más importantes que debe tomar al planear una base de datos, crear tablas, manipular datos, etc., es desarrollar un modelo de seguridad apropiado, donde cosas como la integridad referencial formen parte de ese modelo. Un problema serio de la manipulación y mantenimiento de las bases de datos es que en ocasiones los datos de SQL Server se pierden y se necesita recuperarlos. Si no se cuenta con la protección apropiada y forma parte del proceso de respaldo y recuperación, puede ser un proceso muy preocupante el tratar de recuperar los datos perdidos. Una medida de recuperación de fallos que se puede implementar con respecto a las tablas de una base de datos, es a través de los métodos de integridad referencial. Uno de los errores más comunes de la manipulación de base de datos es la perdida accidental de tablas enteras. La mejor forma de evitar este tipo de situaciones es en primer lugar, asegurarse

Manipulación de datos

que la base de datos utilice la integridad referencial, la cual no permite la eliminación de las tablas a menos que se encuentren al ﬁnal de la relación.

AM ÉRI

Uno de los mejores métodos es utilizar transacciones al actualizar los datos. Es común eliminar, truncar o actualizar accidentalmente los datos durante las tareas regulares de mantenimiento y una de las mejores formas de evitarlo es utilizando transacciones al actualizar los datos. Un buen punto para empezar es insertar un simple begin tran antes de la sentencia SQL y si ha ejecutado de forma correcta, se puede emitir una sentencia COMMIT desde SQL. Si hubiera un error en cualquiera de las sentencias, se emitiría un rollback desde SQL. A continuación se presenta una sentencia de transacción: BEGIN TRAN DELETE FROM <nombre_tabla>

Lo que sucede en este punto es que puede veriﬁcar que lo que hizo realmente se aplicó y luego podría emitir una sentencia COMMIT para guardar los cambios o una ROLLBACK para deshacer los cambios. Aunque en muchas ocasiones suceden errores, si utiliza BEGIN TRAN y COMMIT o ROLLBACK al llevar a cabo las tareas de mantenimiento, podrá evitar accidentes con los datos.

ETC

IBE RO

www.pdftron.com

Lección 3

En esta lección aprendió:

Resumen de Habilidades • Que el comando SQL para recuperar cualquier tipo de datos de una base de datos es SELECT. • Sólo hay tres cosas que necesita asegurarse que se incluyen en una consulta SELECT: columnas a recuperar, tablas de las cuales recuperar información y las condiciones, si es que hay alguna, que los datos deban cumplir. • Una cláusula BETWEEN le permite especificar el rango a utilizar en un formato de consulta “entre x y y”. • La palabra clave NOT se utiliza para buscar datos en términos de lo que no se quiere en los resultados. • La cláusula UNION le permite combinar los resultados, de dos o más consultas en un solo conjunto resultante que incluiría todas las filas que pertenezcan a la consulta en esa unión. • La cláusula EXCEPT regresa cualquiera de los valores distintivos de la consulta izquierda que no se encuentren en la consulta derecha, mientras que INTERSECT regresa los valores que no resulten de las dos consultas (derecha e izquierda). • La cláusula JOIN le permite combinar datos relacionados de varias tablas fuente. • Para insertar datos, por ejemplo, puede utilizar la herramienta de interfaz gráfica o la sentencia INSERT. • La función de la sentencia UPDATE es para cambiar los datos en una tabla o vista. • La sentencia DELETE es para eliminar filas de una tabla o vista. • La sentencia TRUNCATE TABLE sólo elimina los datos de una tabla, pero deja la estructura de la tabla para su uso posterior. • El comando DROP TABLE se utiliza para eliminar una tabla por completo. • La mejor forma de evitar la eliminación accidental de tablas enteras es utilizando la integridad referencial, la que no permite la eliminación de tablas a menos que se encuentren al final de la relación.

AM ÉRI

ETC

IBE RO

www.pdftron.com

Manipulación de datos

Verdadero o Falso

» Evaluación de Conocimientos

Encierre la V si el enunciado es verdadero o F si es falso. F F F F

La sentencia SELECT se limita a consultar datos de una sola tabla. La sentencia INSERT se puede utilizar para insertar varias ﬁlas a la vez. La sentencia TRUNCATE se utiliza para eliminar una tabla Una INNER JOIN y condición son lo mismo que una CROSS JOIN y una condición WHERE. 5. El orden de las columnas en la sentencia SELECT debe corresponder al orden en el que fueron creadas cuando se elaboró la tabla.

1. 2. 3. 4.

AM ÉRI

V V V V

Complete el enunciado

Complete los siguientes enunciados escribiendo la palabra o palabras correctas en los espacios en blanco. 1. La sentencia SELECT se utiliza para consultar y combinar datos de una o más _________.

www.pdftron.com 2. La cláusula WHERE de una sentencia SELECT contiene una o más ________ para ﬁltrar los datos consultados.

IBE RO

3. La forma más eficiente de eliminar todas las filas de una tabla es con la sentencia ______________. 4. Para combinar los resultados de dos consultas, utilice el operador __________. 5. Utilice la sentencia _______ para modificar una o más filas en una tabla.

6. Si utiliza _______________y un _______________ o ____________al llevar a cabo tareas de mantenimiento, podrá evitar muchos accidentes con los datos. 7. Se puede eliminar toda una tabla con la sintaxis __________ y ___________.

8. Si quiere seleccionar una sola columna de su consulta, se identiﬁca el nombre de la columna escribiéndola entre __________ y __________ en la sentencia de consulta. 9. Puede ______ varias condiciones en una sentencia de consulta para satisfacer sus necesidades. 10. La cláusula _______________le permite combinar los resultados de dos o más consultas en un solo conjunto resultante que incluirán todas las ﬁlas que pertenezcan a la consulta.

ETC

Opción múltiple

Encierre la letra que corresponda a la respuesta correcta. 1. ¿Cuál de las siguientes condiciones es inválida? a. b. c. d.

salary <> 50000 salary != 50000 salary NOT EQUAL 50000 NOT salary = 50000

Lección 1

a. b. c. d.

UNION EXCEPT INTERSECT AND

2. ¿Cuál de los siguientes operadores no es compatible al combinar resultados entre sentencias SELECT?

3. ¿Cuál de las siguientes condiciones de rango generaría un error de sintaxis? salary <= 50000 and salary >= 10000 salary between (10000 and 50000) and (60000 and 90000) salary >= 10000 and salary <= 50000 salary between 10000 and 50000

AM ÉRI

a. b. c. d.

4. ¿Cuál de las siguientes sentencias no sería válida en una sentencia SELECT? a. b. c. d.

mass*acceleration as force 3.1415 as PI (select max(age) from employee) as maxage ﬁrstname + secondname as fullname

5. ¿Cuál de las siguientes opciones no es cierta sobre la integridad referencial?

a. Evita la eliminación parcial de los datos durante una transacción b. Evita la eliminación de una ﬁla que contiene un valor al cual se hace referencia a través de una clave foránea en otra tabla c. Requiere que se cree una clave foránea para relacionar dos tablas. d. La clave foránea se debe vincular a una clave primaria o índice único

IBE RO

www.pdftron.com » Evaluación de aptitud

Escenario 3-1: Uso del comando SELECT Recientemente se le ha contratado como Administrador de bases de datos para la empresa AdventureWorks. El administrador de la red quiere saber cómo extraer información de las bases de datos AdventureWorks, por lo que necesita contestar las siguientes preguntas: 1. ¿Cuál comando usaría para mostrar los registros de una tabla? 2. ¿Cuál comando usaría para mostrar FirstName y LastName de la tabla Users? 3. ¿Cuál comando usaría para mostrar todos los registros de la base de datos Member y ordenarlos por la columna Name?

ETC

4. ¿Cuál comando usaría para mostrar todos los registros de la tabla Supliers que tengan la ciudad de Sacramento? 5. ¿Cuál comando usaría para mostrar CompanyName, ContactName y PhoneNumber de la tabla Suppliers que tengan un Supplier ID mayor que 1000? 6. ¿Cuál comando usaría para mostrar CompanyName, Contact y Phone Number de la tabla Customers para las compañías que tengan más de 100 empleados y residan en el estado de CA.

Manipulación de datos

Escenario 3-2: Eliminar datos de tablas Después de que usted y el administrador de la red hayan revisado algunos registros de la base de datos AdventureWorks, deciden eliminar algunos registros antiguos. Conteste las siguientes preguntas. 1. ¿Cuál comando usaría para eliminar todos los registros de la tabla Customer donde Age sea menor que 18?

AM ÉRI

2. ¿Cuál comando usaría para eliminar todos los registros de la tabla Schools con Enrollment de menos de 500?

3. ¿Cuál comando usaría para eliminar todos los registros de la tabla Contact que no tengan un país de USA y que libere el espacio utilizado por tales registros? 4. ¿Cuál comando usaría para eliminar la tabla Temp?

» Evaluación de destreza

www.pdftron.com Escenario 3-3: Manipulación de datos con las sentencias SELECT y JOIN

IBE RO

Es el Administrador de base de datos para la empresa AdventureWorks.

1. Después de abrir SSMS y acceder a la base de datos AdventureWorks, ¿cuál consulta utilizaría para que aparezca ProductSubcategoryID y ProductCategoryID de la tabla Production.ProductSub que contengan la palabra “Bike”? También debe ordenar la información por Subcategory Name. 2. ¿Cuál consulta utilizaría para unir la tabla ProductCategory a la tabla ProductSubcategory con el propósito de recuperar la columna Name de la tabla ProductCategory.

Escenario 3-4: Manipular datos utilizando las sentencias INSERT y UPDATE Como el administrador de la base de datos AdventureWorks, necesita insertar datos en la tabla Credit en la base de datos Sales utilizando diferentes sentencias de datos para actualizar y eliminar datos de la misma tabla Credit.

ETC

1. Por lo tanto, después de abrir SSMS, ¿cuáles comandos escribiría y ejecutaría en la base de datos AdventureWorks para revisar las columnas en la tabla Sales? 2. En la ventana de consultas existente, ¿cuáles comandos escribiría, resaltaría y ejecutaría para agregar la siguiente ﬁla a la tabla Credit especiﬁcando los valores para cada columna con base a su ubicación relativa en el esquema de la tabla utilizando la siguiente sentencia? Patrick, Roberts, 6000, Roberts@telecome.usa

Lección 1

Alex, Hall, 5000 Annie, Smith, 10000

3. En la ventana de consultas existente, ¿cuáles comandos escribiría, resaltaría y ejecutaría para agregar las siguientes ﬁlas a la tabla Credit para ﬁrstname, lastname y credit limit?

4. En la ventana de consultas existente, ¿cuáles comandos utilizaría para actualizar el correo electrónico (Email) para Annie Smith en la tabla Credit utilizando la sentencia Update? 5. En la ventana de consulta existente, ¿cuál comando utilizaría para ordenar los resultados anteriores?

AM ÉRI

ETC

IBE RO

www.pdftron.com

Lección 4

Comprender el Almacenamiento de Datos Matriz de Dominio de objetivos

Normalizar una Base de Datos Comprender las claves principales, externas y compuestas Comprender los índices agrupados y no agrupados

Descripción del Dominio del objetivo Comprender la Normalización. Comprender las claves

Número del objetivo

AM ÉRI

Habilidades/Conceptos

4.1 4.2

principales, externas y

compuestas. Comprender los índices.

4.3

www.pdftron.com Términos Clave

• Restricción de clave externa • Forma • Índice no agrupado • Normalización

IBE RO

• Índice agrupado • Clave compuesta • Primera forma normal • Clave externa

• Clave principal • Datos redundantes • Segunda forma normal • Tercera forma normal

ETC

Necesita trabajar para un gran almacén de una compañía de distribución que proporciona equipo de acampado al aire libre a más de 90 tiendas en Norte América. Ha recibido un memo que informa que la compañía recién ha comprado una segunda tienda en el estado de Washington. Su jefe le pidió agregar una nueva tabla a la base de datos para esta nueva tienda y necesita que el mismo equipo que es vendido en la primera tienda sea ingresado en la nueva tabla de la tienda para garantizar la integridad de las bases de datos así como también para asegurar que la nueva tabla dentro de la base de datos se mantiene normalizada a la tercera forma normal.

Lección 4

Normalizar una Base de Datos

AM ÉRI

È EN RESUMEN

En esta lección, aprenderá sobre la normalización de una base de datos, los cinco niveles más comunes de normalización y el propósito de la normalización en lo que se reﬁere a laintegridad de la base de datos. También aprenderá por qué las claves externas, principales y compuestas juegan un rol integral en la integridad referencial.

El objetivo es comprender las razones de la normalización, los cinco niveles más comunes de normalización y cómo normalizar una base de datos a una tercera forma normal. º Tome Nota La normalización es la eliminación de datos redundantes para ahorrar espacio.

La razón principal para utilizar las técnicas de normalización, con respecto al almacenamiento de datos, fue desarrollado en los días en los que el almacenamiento de datos costaba mucho más de lo que cuesta ahora. La Normalización en pocas palabras, es la eliminación de datos redundantes para ahorrar espacio. Ahora que comprende la deﬁnición en general de esta importante organización de datos, veamos más a fondo los conceptos de normalización y su aplicación cuando se busca un diseño de base de datos en su rutina diaria.

f ¿Qué es la Normalización?

www.pdftron.com La normalización está basada por completo en el diseño de datos y procesos de organización los cuales son derivados de reglas utilizadas cuando se construyen y diseñan bases de datos relacionales. Por lo tanto, su comprensión de qué son las bases de datos relacionales y las buenas metodologías de diseño son extremadamente importantes.

IBE RO

; Listo para la certiﬁcación

Cuando se busca la normalización por definición, este es el proceso de organizar datos con el fin de reducir la redundancia de datos dividiendo una base de datos en dos o más tablas y luego definiendo las relaciones de tabla. El objetivo de esta operación es aislar datos de forma que las adiciones, eliminaciones y modificaciones que ocurren en cada campo pueden ser hechas dentro de una tabla y luego propagarlas a través del resto de la base de datos utilizando estas relaciones definidas. Hay cinco formas de normalización (NFs) y discutiremos los primeros tres: • • • • •

1NF: Elimina los Grupos Repetitivos 2NF: Elimina los Datos Redundantes 3NF: Elimina las Columnas No Dependientes de la llave 4NF: Aísla independientemente las Relaciones Múltiples 5NF: Aísla Semánticamente las Relaciones Múltiples Relacionadas

Ahora miraremos a cada una de estas un poco más a fondo.

ETC

¿Cuáles son las tres primeras formas de no malización y cómo diﬁeren entre sí? —4.1

Tomando una vista detallada de las formas normales este capítulo ofrece un estudio más formal del diseño de una base de datos relacional. Al contrario de la opinión popular, las formas no son una metodología progresiva, pero representan un nivel progresivo de aceptación. Técnicamente, no puede estar en 2NF hasta que se haya cumplido 1NF, por lo tanto, no planee diseñar una entidad y moverla a través de la primer forma normal a la segunda forma normal y así sucesivamente conforme cada forma normal sea simplemente un tipo diferente de integridad de datos con requerimientos cumplidos.

Comprender el Almacenamiento de Datos

Primer Forma Normal (1NF) Eliminar Grupos Repetidos

La primera forma normalizada (1NF) signiﬁca que los datos están en un formato de entidad, lo cual básicamente signiﬁca que se deben cumplir las siguientes tres condiciones:

AM ÉRI

• La tabla no debe tener registros duplicados. Una vez que ha deﬁnido la clave principal para la tabla, ha cumplido el primer criterio de forma normalizada. • La tabla tampoco debe tener atributos multi-valores, lo cual signiﬁca, que no puede combinar valores múltiples que son considerados válidos para una columna en una sola. Por ejemplo de la primera forma normal en acción, considere el enlistado de campamentos base (baseCamp) y paseos (tours) de la base de datos de Cape Hatteras Adventures. La Tabla 4-1 muestra los datos de los campamentos base en un modelo que viola la primera forma normal. El atributo repetido del paseo (tour) no es único. • Las entradas en la columna o atributos son del mismo tipo de datos. Tabla 4-1 Datos de los campamentos base (antes) – Violando la Primera Forma Normal

BaseCamp Ashville

Tour1 Appalachian Trial

Cape Hatteras

Outer Banks

Tour2 Blue Ridge Parkway

Tour3

Hike

Lighthouses Bahamas Dive Amazon Trek Gauley River Rafting

www.pdftron.com

IBE RO

Freeport Ft. Lauderdale West Virginia

Tabla 4-2

Entidad del tour BaseCampID (FK) 1 2 3 4

ETC

Datos de los campamentos base (después) – Conforme a la Primera Forma Normal

Para rediseñar el modelo de datos de forma que cumpla con la primera forma normal, resolver el grupo repetitivo de los atributos de Tour en un solo atributo único, como se muestra en la Tabla 4-2 y luego mueva cualquier valor múltiple a una tabla única. La entidad BaseCamp contiene una tabla única para cada campamento base y la entidad del Tour BaseCampID se reﬁere a la clave principal en la entidad BaseCamp.

Entidad BaseCam BaseCampID (PK)

Name

Appalachian Trail Blue Ridge Parkway Hike Outer Banks Lighthouses Outer Banks Lighthouses

1 2

Ashville Cape Hatteras

3 4

Freeport Ft. Lauderdale

Amazon Trek

Tour

Gauley River Rafting

Lección 4

Segunda Forma Normal (2NF) Eliminar los Datos Redundantes

AM ÉRI

La segunda forma normal (2NF) garantiza que cada atributo en realidad describe a la entidad. Está totalmente basado en dependencia y en los atributos de la entidad en cuestión, los cuales no son parte de una clave candidato, debe ser funcionalmente dependiente de la clave principal. Lo que termina sucediendo en ocasiones, es que las claves principales combinadas pueden algunas veces dar problemas con la segunda forma normal si los atributos no son dependientes en cada atributo en la clave principal. Si un atributo depende en uno de los atributos de la clave principal pero no de otro, entonces se convierte en una dependencia parcial, lo que viola la segunda forma normal. Para comprender mejor cómo puede violar las reglas de la segunda forma normal, la tabla 4-3 muestra un ejemplo de los mismos datos de campamento base. La tabla 4-4 muestra un ejemplo de ésta después de que ha sido conformada. Tabla 4-3 Datos de los Campamentos Base (antes) – Violando la Segunda Forma Normal

PK-BaseCamp Ashville Ashville Cape Hatteras Freeport Ft. Lauderdale West Virginia

PK-Tour Appalachian Trail Blue Ridge Parkway Hike Outer Banks Lighthouses Bahamas Dive Amazon Trek

Base Camp PhoneNumber 828-555-1212 828-555-1212 828-555-1213 828-555-1214 828-555-1215

Gauley River Rafting

828-555-1216

www.pdftron.com Tabla 4-4

Entidad del tour PK-Base Camp Ashville Ashville

Entidad BaseCam PK-Base Camp

PhoneNumber

Ashville Cape Hatteras

828-555-1212 828-555-1213

Cape Hatteras Freeport

Appalachian Trail Blue Ridge Parkway Hike Outer Banks Lighthouses Bahamas Dive

Freeport Ft. Lauderdale

828-555-1214 828-555-1215

Ft. Lauderdale

Amazon Trek

West Virginia

828-555-1216

West Virginia

Gauley River Rafting

PK-Tour

IBE RO

Datos de los Campamentos Base (después) – Conforme a la Segunda Forma Normal

Tercera Forma Normal (3NF) Eliminar Columnas no Dependientes a Claves

ETC

La tercera forma normal (3NF) veriﬁca las dependencias transitivas. Una dependencia transitiva es similar a la dependencia parcial en que ambas se reﬁeren a los atributos que no son totalmente dependientes en una clave principal. Una dependencia es considerada transitoria cuando el atributo 1 es dependiente en el atributo 2, el cual es entonces dependiente en la clave principal. Cuando se busca si hay una violación en la segunda o tercera forma normal, cada atributo es directa o indirectamente vinculado a la clave principal. Por lo tanto, la segunda forma normal es violada cuando un atributo depende en parte de la clave y la tercera forma normal sería violada cuando el atributo dependa en la clave pero también depende de otro atributo que no es clave. La frase clave más importante para recordar cuando se describe la tercer forma normal es que cada atributo “debe proporcionar un hecho acerca de la clave, la clave completa y nada más que la clave”. Justo como la segunda forma normal, la tercera forma normal se resuelve moviendo el atributo no dependiente a una nueva entidad. Para comprender mejor cómo puede violar las reglas de la tercera forma normal, un ejemplo de los mismos datos de campamentos

Comprender el Almacenamiento de Datos

base son mostrados en la Tabla 4-5. La

Tabla 4-6 Datos de los Campamentos Base (después) – Conforme a la Tercera Forma Normal

LeadGuide Jeff Davis Ken Frank Dab Smith Sam Wilson Lauren Jones

DateofHire 5/1/99 4/15/97 7/7/2001 1/1/2002 6/1/2000

Datos de los Campamentos Base (antes) – Violando la Tercera Forma Normal

Entidad de campamentos base BaseCampPK BaseCampPhoneNumber Ashville 1-828-555-1212 Cape Hatteras 1-828-555-1213 Freeport 1-828-555-1215 Ft. Lauderdale 1-828-555-1215 West Virginia 1-828-555-1216

AM ÉRI

Tabla 4-5

Entidad de Tour BaseCampPK Ashville, NC Cape Hatteras Freeport West Virginia

LeadGuide Jeff Davis Ken Frank Dab Smith Lauren Joes

Entidad de LeadGuide LeadGuidePK DateofHire Jeff Davis 5/1/99 Ken Frank 4/15/97 Dab Smith 7/7/2001 Lauren Joes 6/1/2000

Cómo Normalizar una base de datos a la Tercera Forma Normal

www.pdftron.com Hay dos requerimientos básicos para que una base de datos esté en la tercera forma normal:

IBE RO

• Ya cumplen los requerimientos tanto de 1NF y 2NF • Remueve las columnas que no dependen completamente en la clave principal

Con el ﬁn de comprender cómo una base de datos es puesta en la tercera forma normal, veamos un ejemplo de este propósito: Imagine que se tiene una tabla de widget orders que contiene los siguientes atributos: • • • • •

Número de Orden (clave principal) Número de Cliente Precio Unitario Cantidad Total

ETC

Recuerde, nuestro primer requerimiento es que la tabla debe satisfacer los requerimientos de 1NF y 2NF. ¿Existe alguna columna duplicada?, No. ¿Tenemos una clave primaria?, Sí, el número de orden. Por lo tanto, se satisfacen los requerimientos de 1NF. ¿Existe algún subconjunto de datos que apliquen a múltiples ﬁlas?, No, entonces también se satisfacen los requerimientos de 2NF. Ahora, ¿todas las columnas son totalmente dependientes de la clave principal? El número de cliente varía con el número de orden y no parece depender de ninguno de los otros campos. ¿Qué pasa con el precio unitario? Este campo puede ser dependiente al número de cliente en una situación donde le cargamos a cada cliente el precio ﬁjo. Sin embargo, basado en la información proporcionada en los campos de la tabla, podríamos algunas veces cargar distintos precios al mismo cliente. Por lo tanto, el precio unitario es totalmente dependiente del número de orden. La cantidad de artículos también varía de orden en orden, así que está correcto. ¿Qué pasa con el total?, pareciera que tenemos un problema. El total puede ser derivado de multiplicar el precio unitario por la

Lección 4

• • • •

Número de Orden Número de Cliente Precio Unitario Cantidad

cantidad, por lo tanto no depende totalmente de la clave primaria. Debemos eliminarlo de la tabla para cumplir con la tercera forma normal. Tal vez podríamos utilizar los siguientes atributos en lugar de reemplazar nuestros atributos originales:

Ahora nuestra tabla está en 3NF. Pero, podría preguntar, ¿Qué pasa con el total? Este es un campo derivado y es mejor no almacenarlo en la base de datos. Podemos simplemente computarlo “en tiempo de ejecución” cuando desempeñamos consultas en la base de datos. Por ejemplo, podríamos haber utilizado previamente esta consulta para recuperar los números de orden y los totales:

AM ÉRI

SELECT OrderNumber, Total FROM WidgetOrders

Ahora podemos utilizar la siguiente consulta:

SELECT OrderNumber, UnitPrice * Quantity AS Total FROM WidgetOrders

Con el ﬁn de lograr los mismos resultados, sin violar las reglas de normalización.

www.pdftron.com

IBE RO

Cuarta Forma Normal (4NF) Aislar relaciones múltiples independientes

La cuarta forma normal (4NF) involucra dos atributos independientes reunidos para formar una clave primaria junto con un tercer atributo. Pero, si los dos atributos realmente no identiﬁcan únicamente a la entidad sin el tercer atributo, entonces el diseño viola la cuarta forma normal.

Quinta Forma Normal (5NF) Aislar las relaciones múltiples relacionadas semánticamente

ETC

La quinta forma normal (5NF) proporciona el método para diseñar relaciones complejas involucrando múltiples entidades (regularmente tres o más). Es comúnmente discutido entre administradores de bases de datos que satisfaciendo los requerimientos de la tercera forma normal es suﬁciente. Cuando se mira en las cuartas y quintas formas normales, podrían ser complejas, pero violarlas puede causar problemas severos. Es importante mirar el diseño de la base de datos como un todo y no sólo como diseñar algo para llenar la mitad de las necesidades de sus usuarios, empleados, etc. No es necesario, si el número de entidades son utilizadas o no, es una cuestión de alinear apropiadamente los atributos y claves. Cualquier violación de las formas normales puede causar un efecto de cascada con violaciones múltiples y bases de datos ineﬁcientes. La normalización también reduce la contención de bloqueos y mejora el desempeño de usuarios múltiples junto con estas tres ventajas: • Costos de desarrollo: Mientras tomaría más el diseñar una base de datos normalizada, es más fácil trabajar con una base de datos normalizada y así reduce los costos de desarrollo.

Comprender el Almacenamiento de Datos

• Usabilidad: Ubicando columnas en la tabla correcta, es más fácil comprender la base de datos y escribir las consultas correctas. Esto ayuda en la reducción del tiempo de diseño y costos. • Extensibilidad: Una base de datos no normalizada es a menudo más compleja y por lo tanto más difícil de modiﬁcar. Esto lleva a retrasos en el despliegue de nuevas bases de datos a los usuarios e incrementa los costos de desarrollo.

Beneﬁcios de una base de datos digital

AM ÉRI

Con nuestro vasto conocimiento y experiencias ganadas a través de la era de la información y los cambios en las prácticas de diseño del desarrollo de bases de datos relacionales, ha habido algunos beneﬁcios medibles presentados a las organizaciones. Incluyendo: • Consistencia de datos mejorada y mejor cumplimiento de las reglas de negocio • Habilidad mejorada para compartir datos, especialmente a través de largas distancias • Habilidad mejorada para introducir, buscar y recuperar información • Funciones de reporteo mejoradas • Habilidad mejorada para analizar tendencias de datos a través de funcionalidad de reporteo mejorada

www.pdftron.com

IBE RO

Cuando las bases de datos fueron diseñadas por primera vez, el ahorrar tiempo introduciendo datos no era considerada una parte importante del diseño de una base de datos, sino que más bien, las bases de datos fueron diseñadas con propósitos de calidad y velocidad de recuperación de datos. Sin embargo, con el inicio de la necesidad de colección de datos automatizada en tales industria como la fabricación, utilización de código de barras en la venta al por menor, las bases de datos continuamente compartían más datos y los consumidores ubicaban sus propias ordenes de comercio electrónico en Internet, el esfuerzo y tiempo requerido para introducir los datos también se ha convertido en un factor de importancia en el diseño de base de datos.

Tablas, ﬁlas y columnas

ETC

En otras palabras, una base de datos relacional y una cotidiana hoja de cálculo común son realmente bastante similares desde el punto de vista de la teoría de diseño. Una base de datos relacional colecciona datos relacionados o comunes en una sola lista. Por ejemplo, toda la información del producto debe ser enlistada en una tabla y todos los clientes en otra tabla. Una tabla parece similar a una hoja de cálculo y está diseñada con columnas y filas. Esto es realmente donde las similitudes entre una base de datos relacional y una hoja de cálculo común terminan rápidamente. Una aparente ventaja de una hoja de cálculo es su estilo de desarrollo informal, haciéndola fácil de modificar y agrandarla conforme madura el diseño. De hecho, los administradores tienden a almacenar información crítica en hojas de cálculo en lugar de en una base de datos. Pero, muchas bases de datos comenzaron como hojas de cálculo informales y se trasladaron a un diseño de base de datos relacional más fuerte y mucho más amplio. Cuando se mira el diseño de lo que es una hoja de cálculo y una base de datos, es importante darse cuenta que visualmente tanto la hoja de cálculo y la base de datos cada una tiene filas o un ítem y cada columna mantiene una pieza específica de datos referente a ese ítem y cada celda debería contener una sola pieza de datos sobre un solo ítem.

Lección 4

El inconveniente de una hoja de cálculo es la tendencia a ser de flujo libre y a perder su diseño, pero las tablas de la base de datos, por otro lado, son muy consistentes en términos del significado de los datos que residen en una columna. Siendo que tanto la consistencia de fila y columna son muy importantes para una tabla de base de datos, el diseño de la tabla es más crítico.

AM ÉRI

Comprender las claves principales, externas y compuestas È EN RESUMEN

El objetivo es comprender la razón de ser de las claves en una base de datos, eligiendo las claves principales apropiadas, seleccionando los tipos de datos apropiados para las claves, seleccionando los campos adecuados para claves compuestas y comprender la relación entre claves externas y principales. ; Listo para la certiﬁcación ¿Cuál es la diferencia entre una clave principal y una externa?—4.2

Existen tres tipos distintos de restricciones disponibles dentro de SQL Server que le garantizaran que es capaz de administrar la integridad de la base de datos, la clave principal, clave externa y clave compuesta (única). Una clave de restricción única le permitirá cumplir la propiedad de unicidad de columnas, que no sea una clave principal dentro de una tabla. Una clave de restricción única actúa similar a una clave principal, pero con dos diferencias importantes:

www.pdftron.com

IBE RO

• Las columnas que contienen una clave de restricción única pueden contener solo un valor NULL. No puede tener dos ﬁlas que contengan un valor NULL en la misma opción ya que violaría el valor de error duplicado de la restricción única. • Una tabla puede tener múltiples restricciones únicas

Æ Crear una Restricción Única

PREPÁRESE. Para insertar datos a través de la herramienta de la interfaz gráﬁca, siga estos pasos. Antes de que comience, asegúrese de lanzar la aplicación de SQL Server Management Studio y conéctela a la base de datos con la que desea trabajar.

ETC

1. Utilizando el SQL Server Management Studio, abra la tabla en la que desea crear la restricción, en la vista de Diseño dando clic derecho en la tabla y seleccionando “diseño” desde la ventana emergente, como se muestra en la Figura 4-1.

Comprender el Almacenamiento de Datos

Figura 4-1

AM ÉRI

Vista de Diseño

www.pdftron.com

IBE RO

2. Desde el menú desplegable de Diseñador de Tablas ubicado en la parte superior de la barra de herramientas del menú, seleccione Índices o Claves, como se muestra en la Figura 4-2. Esto abrirá la ventana de Índices o Claves. Se dará cuenta que la tabla ya tiene una restricción de clave principal identiﬁcada como se muestra en la Figura 4-3.

Figura 4-2

ETC

Índices o Claves

Lección 4

Figura 4-3

AM ÉRI

Conectándose a una Base de Datos

www.pdftron.com 3. Haga clic en el botón Agregar para crear una nueva clave.

4. Haga clic en la propiedad Tipo en el lado derecho del cuadro de la propiedad y cámbielo

IBE RO

del Índice por defecto a Clave Unique, como se muestra en la Figura 4-4.

Figura 4-4

ETC

Type Property Box

Comprender el Almacenamiento de Datos

Figura 4-5

5. Haga clic en el botón (…) el cual se encuentra al lado de la sección de propiedad Columnas, una vez que haga clic en él, como se muestra en la Figura 4-5. Ahora puede seleccionar las columnas que desea incluir en su restricción única.

AM ÉRI

Seleccionando columnas a agregar

www.pdftron.com Haga clic en el botón cerrar

IBE RO

Figura 4-6

7. Guarde su restricción recién creada seleccionando el “guardar todo” desde el menú Archivo, como se muestra en la Figura 4-6.

ETC

Guardar una restricción nueva

Ahora ha creado su restricción única.

Lección 4

Claves principales

PAUSA. Deje abierta la interfaz de SQL Server Management Studio abierta para el siguiente ejercicio.

Tal vez el concepto más importante del diseño de cualquier tabla de base de datos es que tiene una clave principal, un atributo o conjunto de atributos que pueden ser utilizados para identificar de forma única a una fila. Cada tabla debe tener una clave principal, sin una clave principal, no es una tabla valida. Por definición, una clave principal debe ser única y debe tener un valor (que no sea null). Para algunas tablas, podría haber múltiples claves principales posibles para elegir, tal como, número de empleado, número de licencia de manejo o un número emitido por el gobierno tal como Número de Seguro Social (NSS). En este caso, todas las claves principales potenciales son conocidas como claves candidato. Las claves candidato que no son seleccionadas como la clave principal, son conocidas como claves alternas. Recuerde, en la fase de diagramación de base de datos inicial, la clave principal podría ser fácilmente visible, por ejemplo, podría ser un número de empleado, un nombre de fabricante, pero, frecuentemente no hay un valor de identificación única reconocible claramente para cada ítem cuando se busca en la mayoría de los casos en el mundo real.

AM ÉRI

Claves externas

www.pdftron.com

IBE RO

A través de los capítulos de este libro, ha sido inundado con la terminología de bases de datos relacionales. Esta terminología lleva también hacia el uso de claves de índice, tales como las claves externas. Es importante que note que cuando se discute tan importante aplicación de integridad de base de datos, la integridad referencial siempre jugara un rol importante en esa aplicación. Cuando tiene dos tablas relacionadas entre sí, una de estas tablas actuará como principal y la otra como secundaria. Con el fin de conectar las dos tablas, la clave principal es entonces replicada de la tabla principal a la secundaria y ahora todos los atributos clave duplicados desde la tabla principal son conocidos como la clave externa. Aunque esto podría ser definido, en ocasiones, como una relación padre-hijo, hacer cumplir el atributo de clave externa está realmente definido como integridad referencial (referirse al último capítulo discutiendo la integridad referencial).

ETC

La ﬁgura 4-7 permite tener una mejor idea visual del tipo de claves de índice.

Comprender el Almacenamiento de Datos

Figura 4-7

AM ÉRI

Clave de índice

www.pdftron.com

IBE RO

Cuando se discuten las claves externas, claves principales, etc. La terminología casi siempre incluirá la restricción como parte de la descripción. Como ejemplo a cual rol se refiere la restricción de clave externa, utilicemos el ejemplo de una base de datos de una tienda de venta al por menor. Cada tienda tendrá una tabla que contenga la información relativa a esa tienda, tal como información de empleado, productos vendidos, inventario y probablemente la información con respecto a los clientes. Con el fin de referenciar los datos de la tabla lógicamente sería crear un campo de Unit_Number dentro de la tabla de Employees y este campo contendría el Unit_Number (la clave principal de la tabla Stores) de la tienda de los empleados, por lo tanto crear un enlace entre las dos tablas. ¿Configurado lógicamente o no? Pregúntese a sí mismo “¿Qué pasa si una de las tiendas cierra en el futuro?”. Sería más probable que todos los empleados asociados con la tienda serian entonces “huérfanos” como si hubieran sido asociados con el campo Unit_Number que ya no existe. También hay posibilidades para el potencial error humano por lo cual durante la entrada de datos de un empleado en la base de datos, alguien podría teclear inadvertidamente un Unit_Number incorrecto para un empleado, por lo tanto crear un número para una tienda que no existe. Este podría ser un problema durante el tiempo de pago de nóminas o para otras acciones de los recursos humanos. Estos tipos de problemas están definidos como cuestiones de “integridad relacional”, como lo aprendió en el capítulo anterior. SQL Server proporciona la restricción de clave externa necesaria para prevenir que ocurra este tipo de error. Una clave externa entonces crea una relación entre dos tablas enlazando la clave externa de una de las tablas a la clave principal de la tabla referenciada.

º Tome Nota

ETC

Cada tabla debe tener una clave principal, sin una clave principal, no es una tabla valida. Por deﬁnición, una clave principal deber ser única y debe tener un valor (el cual no es null).

Lección 4

Æ Crear una Vista utilizando el SQL Server Management Studio

PREPÁRESE. Para crear una restricción externa a través de la herramienta de interfaz gráﬁca, siga estos pasos. Antes de que inicie, asegúrese de lanzar la aplicación del SQL Server Management Studio y conéctelo a la base de datos con la que desea trabajar. 1. En el SQL Server Management Studio, abra la tabla en la que desea crear una clave externa. Haga clic derecho en “vista de diseño” desde el menú emergente que observa, como se muestra en la Figura 4-8.

AM ÉRI

Figura 4-8 Vista de Diseño

IBE RO

www.pdftron.com

ETC

2. Desde la lista del menú desplegable del Diseñador de Tablas, como se muestra en la Figura 4-9, seleccione Relaciones.

Comprender el Almacenamiento de Datos

Figura 4-9

AM ÉRI

Seleccione “Relaciones”

www.pdftron.com 3. Seleccione la tabla en la que desea agregar la clave externa.

IBE RO

4. Haga clic en el (…) el cual se encuentra al lado del cuadro de dialogo de la propiedad de Especiﬁcación de Tablas y Columnas, como se muestra en la Figura 4-10.

Figura 4-10

ETC

Cuadro de la propiedad Tipo

Lección 4

5. Figura 4-11

Seleccione la tabla a la que se reﬁere la clave externa en la lista desplegable de clave externa como se muestra en la Figura 4-11.

AM ÉRI

Seleccione la tabla a la que se reﬁere su clave externa

IBE RO

www.pdftron.com 6. Una vez que ha terminado de agregar la información de la tabla de clave externa, haga clic en “Aceptar” para cerrar la ventana de diálogo. 7. Haga clic en el botón cerrar.

8. Guarde su restricción recién creada

DEJE abierta la interfaz del SQL Server Management Studio para el próximo ejercicio.

A través de la creación de esta relación de clave externa entre dos tablas, SQL Server ahora requerirá todos los valores asociados con la restricción, en la tabla de clave externa tienen valores correspondientes a la tabla de la clave principal. Esto no requiere que suceda lo contrario basta que la clave primaria corresponda con los valores con la tabla de la clave principal. Recuerde, que puede tener múltiples valores que correspondan en la tabla de la clave externa que puede referirse al mismo registro en la tabla de la clave principal.

ETC

Claves principales compuestas Una de las discusiones más confusas con respecto a las claves principales es con la definición de la clave principal compuesta. Una clave principal compuesta ocurre cuando define más de una columna como su clave principal y aunque muchos administradores de base de datos no las utilizan o están conscientes de ellas, ellas juegan una parte integral en el diseño de un buen y sólido modelo de datos. Como ejemplo simplificado, tome las tablas en una base de datos y categorícelas basado en dos tipos de datos: • Tablas que definen entidades • Tablas que relacionan entradas

Comprender el Almacenamiento de Datos

Las tablas que definen entidades son las tablas que definen cosas tales como clientes, vendedores, transacciones relacionadas con ventas. Puede elegir cualquier columna en estas tablas como una clave principal, por el bien de esta discusión acerca de las claves principales compuestas, las tablas que definen entidades no es el tema importante de esta discusión.

AM ÉRI

Cuando observa tablas que relacionan entidades, es cuando la clave principal compuesta juega un rol muy importante. Utilizando nuestro ejemplo anterior, suponga que tiene un sistema que registra clientes y que el sistema le permite asignar múltiples productos a múltiples clientes con el fin de indicar que pueden o no ordenar, usted observa una relación “muchos a muchos” entre las tablas Customer y Products. Ya que tiene una tabla para clientes (Customers) y una para productos (Products) y tiene seleccionada la clave principal para la columna ProductID de la tabla Products y la columna CustomerID de la tabla Customers, buscando la manera de definir nuestra tabla CustomerProducts. La tabla CustomerProducts relaciona a los clientes con los productos, así que el propósito de esta tabla es relacionar las dos entidades las cuales ya han sido definidas dentro de la base de datos. Muchas veces, cuando el administrador de bases de datos está diseñando una tabla por primera vez, un gran esfuerzo se dirige a garantizar la integridad de los datos siguiendo las guías de diseño, asegurándose que la clave principal de la tabla está identificada, un administrador de base de datos garantiza que la integridad de datos es mantenida. El resumen es que con el fin de mantener la integridad de datos, la clave principal debe formar parte de los requerimientos de diseño para cada tabla.

www.pdftron.com

IBE RO

Muchas veces una tabla no está diseñada con previsión de la posible duplicidad de entradas de datos y aunque muchos piensan que el UI (Identificador Único) puede manejar cualquier duplicidad de datos, siempre hay un punto en el tiempo cuando se pueden actualizar los datos de la tabla. Tales tiempos podrían ser cuando un sistema es actualizado y hay una necesidad de mover datos o que necesita restaurar alguna forma de transacción desde un respaldo, por lo tanto, hay una necesidad de garantizar que la integridad de datos es mantenida. Si comienza a mirar en el diseño de la tabla con la comprensión de la integridad de datos y la definición de una clave principal de una tabla, utilizando una restricción única garantizará que la integridad es mantenida. Recuerde, una clave principal es un conjunto de columnas en una tabla que identifican la unicidad de cada fila de datos.

Comprender los índices clustered y non-Clustered È EN RESUMEN

ETC

Es importante comprender los índices clustered (agrupados) y non-clustered (no agrupados)y su propósito en las bases de datos. ; Listo para la certiﬁcación

¿Cuáles son las diferencias entre un índice clustered y uno non-clustered?—4.3

Como administrador de base de datos, es importante comprender cuál de los dos tipos de índices (clustered y non-clustered) hacer y cuál es el rol de estos índices dentro del entorno de base de datos. Un ejemplo de lo que es un índice seria si ha adquirido algún tipo de libro y necesitará buscar algún tema en particular, palabra o idea, no dudaría en buscar en el índice en la parte trasera del libro. La Indexación con las bases de datos, en el amplio esquema de cosas, es exactamente lo mismo. En SQL server, si desea recuperar datos desde dentro de una base de datos, SQL server verificaría cada fila para buscar la consulta por la que está tratando de encontrar información. ¿Esto suena como una cantidad increíble de tiempo gastado ineficientemente? Si respondió que sí, ¡estaría en lo correcto! Lo que

Lección 4

AM ÉRI

hace el SQL Server, con la ayuda de los administradores de bases de datos, es construir y mantener una variedad de índices con el ﬁn de ubicar y enviar de regreso campos utilizados rápida y comúnmente. El único inconveniente real de utilizar la indexación es el tiempo que toma construir los índices actuales y el espacio de almacenamiento requerido para los índices. Una de las decisiones importantes a tomar cuando se toma la tarea de utilizar índices es que los índices son apropiados para su base de datos basado en los tipos de consultas que va a desempeñar. Recuerde, SQL Server le permite crear sus índices ya sea en columnas sencillas o múltiples, pero, la velocidad real que ganará será en aquellos índices basados en la columna (s) dentro del índice.

Índices clustered º Tome Nota Sólo un índice clustered es permitido para cada tabla.

Cuando comienza a mirar la implementación de índices, es importante recordar que cada tabla puede tener solo UN índice clustered que define cómo el SQL Server ordenará los datos almacenados dentro de la tabla ya que los datos solo pueden ser ordenados de una manera, simplemente no es posible tener dos índices clustered en la misma tabla. También debería mencionarse que el índice clustered es una construcción física, contrario a la mayoría de los índices los cuales son lógicos o basados en software. Una de las características de SQL Server es la creación automática de un índice clustered cuando la clave principal está definida para una tabla. Una clave principal significa para usted, como administrador de base de datos, determinar la creación índices non-clustered basados en las columnas en una tabla.

www.pdftron.com

IBE RO

Aunque lo que ha sido explicado es mucho más que una visión simplista de las deﬁniciones de clustered y non-clustered y por qué han sido creados, es importante observar las bases de un índice.

ETC

Un índice es una estructura en disco (o almacenada) asociada totalmente con una tabla o una vista la cual ayuda a la velocidad de recuperación de datos. Con el fin de construir este índice, una serie de claves son construidas desde uno o más columnas en cada fila dentro de la tabla o vista. Estas claves son luego almacenadas en una estructura llamada un Árbol-B el cual permite SQL Server encuentre la fila (s) asociadas con estos valores definidos mucho más rápida y eficientemente. Un ejemplo de una estructura de Árbol-B puede ser encontrado en la Figura 4-12.

Comprender el Almacenamiento de Datos

Figura 4-12

AM ÉRI

Estructura de Árbol-B

Ahora, en caso de un índice clustered, los datos son ordenados y almacenados en la tabla o vista la cual está basada en sus respectivos valores de clave. Estas columnas están incluidas dentro de la deﬁnición de índice ya que los datos en las columnas son ordenados en un solo orden, esto es porque, como se mencionó en la introducción, solo puede tener un índice clustered por tabla.

www.pdftron.com

IBE RO

En los términos de cuando una tabla tiene un índice clustered, es considerada una tabla clustered y cuando una tabla tiene un índice non-clustered, las ﬁlas de datos son almacenadas en un estatus desordenado llamado “heap”. Esto nos trae la deﬁnición de lo que es un índice non-clustered.

Índices non-clustered

Tiene la libertad de crear sus propios índices non-clustered ya que tienen una estructura separada de lo que se encuentra en la estructura de un índice clustered. Esto se debe a que un índice non-clustered contiene los valores de la clave de índice non-clustered y cada una de estas claves tiene un apuntador a una fila de datos que contiene el valor de la clave. Este apuntador es definido como un localizador de fila y la estructura del localizador depende de si las páginas de datos están almacenadas en un heap o como una tabla clustered. Esto es una parte importante de una función de un índice non-clustered como si apuntara a un heap, el localizador de fila es la clave de índice clustered.

ETC

Crear una Tabla Hay dos maneras de crear un índice non-clustered en una tabla. Una utilizando sentencias de secuencias de comandos de Transact-SQL y la otra es a través de la interfaz visual del SQL Server Management Studio. Es importante saber cómo crear los índices de cualquier manera. Crear una tabla Non-Clustered

Lección 4

Para crear una tabla non-clustered a través de la herramienta de la interfaz gráﬁca, siga estos pasos.

Æ Crear una tabla non-clustered utilizando el SQL Server Management PREPÁRESE. Antes de empezar, asegúrese de lanzar la aplicación del SQL Server Management Studio y conéctelo a la base de datos con la que desea trabajar.

AM ÉRI

1. Haga clic en el icono de suma (+) a la izquierda de la carpeta de las “Bases de Datos” con el ﬁn de expandirlas. Deberá observar instancias de muchas subcarpetas de su carpeta principal “bases de datos” (Como se muestra en la Figura 4-13). Figura 4-13 Carpeta de Bases de Datos

IBE RO

www.pdftron.com

ETC

2. Haga clic en el icono de suma (+) al lado de la base de datos en la que le gustaría crear un índice (como se muestra en la Figura 4-14). Ahora deberá observar muchas subcarpetas apareciendo.

Comprender el Almacenamiento de Datos

Figura 4-14

AM ÉRI

Creando un Índice

www.pdftron.com

IBE RO

Figura 4-15

3. Haga clic en el icono de suma (+) a la izquierda de la carpeta Tablas con el ﬁn de expandirla (como se muestra en la Figura 4-15). Ahora deberá ver un número de tablas aparecer debajo de la carpeta Tablas.

ETC

Viendo la carpeta Tablas

4. Haga clic derecho a la subcarpeta Índices y seleccione “Nuevo Índice” en el menú emergente que aparece (como se muestra en la Figura 4-16).

Lección 4

Figura 4-16

AM ÉRI

Menú del Nuevo Índice

www.pdftron.com

IBE RO

Figura 4-17

5. Ahora observará un Nuevo cuadro de dialogo aparecer, el cuadro de las propiedades del Nuevo índice por el cual puede introducir las entradas deseadas. Esto es donde seleccionaría si el tipo de índice sería clustered o non-clustered (como se muestra en la Figura 4-17)

ETC

Cuadro de propiedades del Nuevo Índice

PAUSA Deje la interfaz del SQL Server Management Studio abierta para el siguiente ejercicio.

Comprender el Almacenamiento de Datos

Figura 4-18

Para seleccionar un índice clustered, podría verse similar a la Figura 4-17 y un cuadro de propiedades de índice non-clustered a la ﬁgura 4-18

AM ÉRI

Cuadro de propiedades de un Índice Clustered

www.pdftron.com

IBE RO

En un índice clustered, notará que cuando está buscando en estos dos ejemplos proporcionados, no puede agregar otro índice. Esto es, como se mencionó anteriormente, la importancia de asegurarse de elegir la clave correcta para actuar como su clave de índice clustered ya que esta es su índice de ordenamiento principal para la tabla y no puede tener dos índices clustered por tabla. Pero, en una tabla con un índice non-clustered, puede agregar múltiples columnas a la clave del índice, como se muestra en la Figura 4-19.

Figura 4-19

ETC

Cuadro de propiedades del Índice Non-clustered

Lección 4

En esta lección, aprendió

Resumen de Habilidades • Normalización, en otras palabras, es la eliminación de los datos redundantes para ahorrar espacio. • La primera forma normalizada (1NF) significa que los datos están en un formato de entidad, lo cual básicamente significa que las siguientes tres condiciones deben cumplirse, la tabla no debe tener registros duplicados, tampoco debe tener atributos de valores múltiples, las entradas en la columna o atributo son del mismo tipo de datos. • La segunda forma normal (2NF) garantiza que cada atributo de hecho describe a la entidad. • La tercera forma normal (3NF) verifica las dependencias transitivas. Una dependencia transitiva es similar a una dependencia parcial en que ambas se refieren a los atributos que no son totalmente dependientes en la clave principal. • La cuarta forma normal (4NF) involucra dos atributos independientes reunidos para formar una clave principal junto con un tercer atributo. • La quinta forma normal (5NF) proporciona el método para el diseño de relaciones complejas involucrando entidades múltiples (usualmente tres o más). • Hay tres distintos tipos de restricciones disponibles dentro de SQL Server que le garantizan que es capaz de mantener la integridad de la base de datos, clave principal, clave externa y clave compuesta (unique). • Una clave de restricción unique le permitirán cumplir la propiedad de unicidad de las columnas, otra más que una llave primaria en una tabla. • Tal vez el concepto más importante del diseño de cualquier tabla de base de datos es que tiene una clave principal, un atributo o conjunto de atributos que pueden identificar la unicidad de la fila. • Cada tabla debe tener una clave principal, sin una clave principal, no es una tabla valida. Por definición, una clave principal debe ser única y debe tener un valor (el cual no es null). • Con el fin de conectar dos tablas, la clave principal es entonces replicada desde la tabla principal a la secundaria y ahora todos los atributos clave duplicados desde la tabla principal son conocidos como la clave externa. • Una clave compuesta ocurre cuando define más de una columna como su clave principal. • El único inconveniente real de utilizar la indexación es el tiempo que toma construir los índices actuales y el espacio de almacenamiento requerido para los índices. • Cuando comienza a observar la implementación de índices, es importante recordar que cada tabla puede tener solo UN índice clustered que define cómo el SQL Server ordenará los datos almacenados dentro de la tabla, ya que los datos solo pueden ser ordenados de una manera. • Un índice non-clustered contiene los valores clave del índice non-clustered y cada uno de estas claves tiene un apuntador a la fila de los datos el cual contiene el valor clave.

AM ÉRI

ETC

IBE RO

www.pdftron.com

Comprender el Almacenamiento de Datos

Verdadero o Falso

» Evaluación de Conocimientos

Encierre la V si el enunciado es verdadero o F si es falso. F F

V V

F F

1. Crear una clave principal satisface la primera forma normal 2. Las tablas en una base de datos deben satisfacer las 5 formas normales con el ﬁn de maximizar el desempeño 3. Una clave principal puede contener valores NULL 4. Un índice CLUSTERED usualmente mejora el desempeño cuando se insertan datos 5. Una tabla puede contener solo un índice CLUSTERED.

AM ÉRI

V V

Llene los espacios en blanco

Complete las siguientes sentencias escribiendo la palabra o palabras correctas en los espacios en blanco. 1. La Normalización es la eliminación de datos redundantes para ahorrar ________. 2. El valor de una clave principal debe ser __________.

www.pdftron.com 3. Una clave externa trabaja en conjunto con una clave principal o restricción unique para cumplir la _________entre tablas

IBE RO

4. Agregar un índice a una o más columnas para aumentar la velocidad de _________ de datos. 5. Los valores en un índice CLUSTERED son ___________.

Opción múltiple

Encierre en un círculo la letra que corresponda a la mejor respuesta. 1. ¿Cuáles de las siguientes no son restricciones? a. b. c. d.

CHECK DEFAULT UNIQUE ÍNDICE

ETC

2. ¿Cuál de los siguientes aumenta la velocidad de recuperación de datos? a. b. c. d.

Restricción DEFAULT Restricción de CLAVE PRINCIPAL Índice CLUSTERED Restricción de CLAVE EXTERNA

3. Seleccione las sentencias que son verdaderas acerca de la Normalización a. Un número mayor de tablas estrechas (con pocas columnas) es una característica de una base de datos normalizada b. Unas pocas tablas amplias (con más columnas) es una característica de una base de datos normalizada c. Los índices permiten una recuperación más rápida de datos

Lección 1

d. Se logra un mejor desempeño indexando cada columna en una tabla 4. ¿Cuál de las siguientes sentencias no es verdadera acerca de una CLAVE EXTERNA?

a. Una clave externa es una combinación de una o más columnas utilizadas para establecer e imponer un enlace entre los datos en dos tablas. b. Puede crear una clave externa deﬁniendo una restricción de CLAVE EXTERNA cuando crea o altera una tabla. c. Una CLAVE EXTERNA cumple la integridad referencial garantizando que solo los datos validos son almacenados d. Una tabla puede contener solo una clave EXTERNA

AM ÉRI

100

5. Se considera utilizar un índice CLUSTERED cuando a. b. c. d.

las columnas contienen un gran número de valores distintos las columnas son accedidas secuencialmente las columnas se someten a cambios frecuentes en las consultas se ejecutan grandes conjuntos de resultados

6. ¿Cuál forma normal le garantiza que cada atributo describe a la entidad? a. b. c. d.

1NF 2NF 3NF 4NF

www.pdftron.com 7. ¿Cuál de las siguientes no podría ser utilizada como una clave principal? Número de Seguro Social Dirección Número de empleado Número de serie para un componente electrónico

IBE RO

a. b. c. d.

8. ¿Cuantos índices puede tener para una base de datos? a. b. c. d.

1 2 4 8

9. ¿Cómo se llama cuando tiene más de una columna que actúa como clave principal? a. b. c. d.

Clave principal compuesta Clave escalada Clave externa Clave de restricción

ETC

10. Cuando deﬁne una clave principal, cumple los criterios para _________. a. b. c. d.

1NF 2NF 3NF 4NF

Comprender el Almacenamiento de Datos

101

Caso 4-1: Mirando en 1NF

» Evaluación de Competencia

Su jefe acude a usted y le entrega en la mano una unidad ﬂash USB con la siguiente base de datos: Model Specs

PC-1250 PC-1500

Dell OptiPC 200XD, 2.0 Ghz, 512mb RAM, 40 GB HD

AM ÉRI

PC-1000

Dell SlimPC 400LX, 2.25Ghz, 2gb RAM, 160GB HD Dell OptiPC 300XD 1.5Ghz, 4gb RAM, 200 GN HD

Él desea que inicie utilizando esta base de datos. Desea saber lo que está equivocado de la base datos y le pregunta si se debería hacer algo con ella Caso 4-2: Comparar Índices Clustered y Non-Clustered

Su jefe desea aumentar la velocidad del servidor de base de datos. Por lo tanto, él piensa que usted debe crear un par de índices. Entonces le pide que explique las ventajas y desventajas de crear un índice clustered contra un índice non-clustered. ¿Qué responde?

www.pdftron.com

IBE RO

» Evaluación de Competencia

Caso 4-3: Crear un Índice Clustered

Usted es un ABD para la AdventureWorks Corporation. Ha creado recientemente algunas bases de datos y se ha dado cuenta de cuán grande será la base de datos en el futuro. Por lo tanto, necesita crear un nuevo índice clustered para ayudar con el desempeño general.

ETC

Utilizando la interfaz gráﬁca de SSMS, ¿cuáles son los pasos que utilizaría para crear un índice clustered en la columna name (nombre) para la base de datos de AdventureWorks?

Lección 1

Caso 4-4: Crear un Índice Clustered utilizando Transact-SQL

Como ABD, necesita incrementar el desempeño de la tabla PlanetsID. Por lo tanto, decide crear un índice clustered. Pero en lugar de utilizar SSMS, decide utilizar consultas para desempeñar esta tarea. Por lo tanto, crea una nueva base de datos PlanetsID utilizando los siguientes comandos dentro de SMMS: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[PlanetsID] AND type in (N’U’)) USE AdventureWorks2008 DROP TABLE [dbo].[PlanetsID] GO USE [AdventureWorks2008] GO CREATE TABLE [dbo].[PlanetsID]( [ID] [int] NOT NULL, [Item] [int] NOT NULL, [Value] [int] NOT NULL ) ON [PRIMARY] GO INSERT INTO PlanetsID VALUES (4, 23, 66) INSERT INTO PlanetsID VALUES (1, 12, 59) INSERT INTO PlanetsID VALUES (3, 66, 24) SELECT * FROM PlanetID GO

AM ÉRI

102

www.pdftron.com

IBE RO

Ahora deberá ver la salida en el panel de resultados (debajo de la ventana del Query Editor), lo siguiente: ID

Item

Value

Ahora que tiene una base de datos con información, ¿qué pasos utilizaría para crear un índice clustered basado en la columna ID? Caso 4-4: Crear un Índice Clustered utilizando Transact-SQL

ETC

En este ejercicio, creará la base para la consulta que necesitará ser optimizada ejecutándola sin agregar ningún índice. 1. En el SQL Server Management Studio, expanda la base de datos de AdventureWorks2008, luego la de tablas (si es que no está expandida) y encuentre la tabla que recién ha creado llamada PlanetsID. 2. Haga clic derecho en la carpeta Índices debajo de la tabla PlanetsID y seleccione Nuevo Índice desde el menú desplegable. El cuadro de dialogo de las propiedades del nuevo índice se abrirá.

Comprender el Almacenamiento de Datos

103

3. En el campo del Nombre de Índice, teclee IX_ID (como se muestra en la Figura 4-18). En el campo de Tipo de Índice, seleccione Clustered desde el menú desplegable. Haga clic en el botón “Agregar”, luego seleccione la columna “ID”, ﬁnalmente, haga clic en el botón “ACEPTAR”. Figura 4-24

AM ÉRI

Cuadro de Diálogo de Nueva Consulta

IBE RO

www.pdftron.com 4. Seleccione el botón de Nueva Consulta desde la barra de herramienta y teclee la siguiente sintaxis en la ventana del Editor de Consulta, luego haga clic en el botón Ejecutar: SELECT * FROM PlanetsID

Al ejecutar el comando, los resultados serán retornados de acuerdo al orden de los datos como fueron ingresados en la estructura b-tree. La clave principal es la columna ID, por lo tanto, los datos están ordenados basados en la clave principal: Item

Value

ETC

Ahora puede configurar una tabla de índice clustered basado en el conocimiento que ha recibido a través de la configuración de un índice clustered. La única diferencia cuando utiliza sentencias de Transact-SQL es para agregar “NX” enfrente del campo del nombre del índice para identificarlo como un valor nonclustered. Recuerde, no puede crear un non-clustered en una columna que ya sea clustered.

CA AM ÉRI

ETC

IBE RO

www.pdftron.com

Lección 5

Administración de una Base de Datos Matriz de Dominio de objetivos

Asegurar Bases de Datos

Respaldar y Restaurar Bases de Datos

Términos Clave

Descripción del Dominio del objetivo Comprender Conceptos de Seguridad de Bases de Datos Comprender los Respaldos y Restauraciones de Bases de Datos

Número del objetivo

AM ÉRI

Habilidades/Conceptos

5.1 5.2

www.pdftron.com • Usuario invitado • Respaldo incremental • Respaldos de log • Inicio de sesión • Permisos

IBE RO

• Autenticación • Respaldo • Seguridad de base de datos • Respaldo diferencial • Respaldo completo

• Restaurar • Funciones del servidor • Cuenta sa • Sysadmin • Cuentas de usuario

ETC

Después de crear la nueva tabla para la base de datos que tiene el nuevo inventario y los datos de ventas de la tienda del estado de Washington, su jefe ahora le ha pedido que garantice que los empleados de la tienda recientemente adquirida están iniciando sesión en la base de datos correcta para los datos de la tienda así como también le ha pedido que trabaje en la implementación de un plan de respaldo para cada tienda. Se le ha solicitado que implemente una programación tanto de un respaldo completo y un respaldo incremental, como también deﬁnir el nuevo esquema de seguridad.

106

Lección 5

Seguridad de bases de datos

AM ÉRI

È EN RESUMEN

Aunque este capítulo puede parecer un poco aburrido, es importante comprender cómo se conceden los permisos a los usuarios a ciertas tablas o bases de datos, así también comprender cuales son los distintos tipos de respaldos cuando se tiene el propósito de programarlos.

El objetivo es comprender la necesidad de asegurar una base de datos, cuáles objetos pueden ser asegurados, qué objetos deberían ser asegurados, así como también cuentas de usuario y las funciones. Es común desarrollar primero la base de datos y después preocuparse acerca de la seguridad. Aun cuando no tiene caso aplicar seguridad cuando la base de datos este en desarrollo, el proyecto le beneﬁciará al desarrollar e implementar un plan de seguridad lo más pronto posible. La seguridad, al igual que cualquier otro aspecto del proyecto de la base de datos, debe ser diseñada cuidadosamente, implementada y probada. Dado que la seguridad puede afectar la ejecución de algunos procedimientos, debe tomarse en cuenta al momento de desarrollar el código del proyecto.

www.pdftron.com

IBE RO

Un plan de seguridad simple con unas pocas funciones y los usuarios de TI como sysadmins puede bastar para una organización pequeña, pero organizaciones grandes, tal como la militar, bancos u organizaciones internacionales, requieren un plan de seguridad más complejo que se diseñe e implemente con medidas fuertes de seguridad. El resultado ﬁnal de trabajar con la seguridad de base de datos es garantizar los derechos y responsabilidades otorgado a los usuarios, a través de la creación de usuarios y funciones.

Se utiliza un permiso para conceder una entidad como un acceso de usuario a un objeto tal como otro usuario o una base de datos. Cuando se mira el modelo de seguridad en SQL Server, es muy complejo y requiere de gran capacidad aplicada a las funciones de usuario y sus diferentes permisos. En SQL Server, el modelo de seguridad está basado en lo que se define como “protegibles”. Esto significa que los objetos pueden tener acceso a un protegible. Los objetos son definidos como bases de datos, tablas, inicios de sesión, usuarios y funciones.

ETC

Un inicio de sesión (login or logon) es el proceso por el cual el acceso individual a un sistema de computadora es controlado por la identiﬁcación del usuario utilizando sus credenciales. El método de inicio de sesión más común es con un nombre de usuario y una contraseña. Una cuenta de usuario es una representación lógica de una persona dentro de un sistema electrónico. Es importante tener en cuenta los derechos y permisos para cada objeto ya que podría conceder inadvertidamente, por ejemplo, CONTROL SERVER a un inicio de sesión, que concede a ese usuario los mismos derechos que el sysadmin del servidor. Los usuarios son asignados a funciones (roles), los cuales podrían conceder permisos a objetos. Cada objeto tiene un propietario, una propiedad también afecta los permisos.

Administración de una Base de Datos

107

f Seguridad de nivel de servidor

Un resumen del modelo de seguridad de SQL Server muestra cómo los usuarios son primero autenticados al servidor, seguido por las bases de datos y ﬁnalmente los objetos dentro de las bases de datos. Los círculos representan la forma en la cual se identiﬁcan los usuarios.

AM ÉRI

Cuando se diseña el modelo de seguridad para un SQL Server, hay tres métodos diferentes por los cuales un usuario puede ser identificado inicialmente, como se indica en esta sección. Los tres diferentes métodos por los cuales un usuario puede ser identificado inicialmente incluyen: • Inicio de sesión de usuario de Windows • Membership al grupo de usuarios de Windows • Inicio de sesión específica de SQL Server (si el servidor utiliza seguridad de modo mixto)

; Listo para la certiﬁcación

Es importante recordar que en el nivel de SQL Server, donde reside la base de datos, los usuarios son conocidos por su nombre de inicio de sesión. Este puede ser ya sea inicio de sesión de SQL Server, dominio de Windows o una elección de inicio de sesión de nombre de usuario.

www.pdftron.com Una vez que un usuario inicia sesión en el servidor con sus credenciales de usuario y es subsecuentemente veriﬁcado, ese usuario ahora tiene derechos de administración de nivel servidor que han sido concedidos a través de derechos administrativos de nivel de servidor vía funciones (roles) del servidor (esto será discutido más adelante en este capítulo).

IBE RO

¿Cuáles son las funciones asignadas a un Microsoft SQL Server y cuáles funciones son asignadas a una base de datos SQL? —5.1

º Tome Nota

ETC

Los usuarios pueden iniciar sesión a un SQL Server utilizando ya sea un inicio de sesión de dominio de Windows, inicio de sesión de nombre de usuario o un inicio de sesión de SQL Server.

Recuerde, si agrega un usuario a la función sysadmin, ese usuario ahora tendrá acceso total a cada función, base de datos y objetos para ese servidor. Con ese acceso total, un usuario puede entonces conceder permiso a otros usuarios contra otros elementos protegibles del servidor. Esta es una función de usuario muy poderosa que debe asegurarse de no concederla a un usuario equivocado ya que puede realizar una variedad de acciones de nivel de sistema tales como conceder acceso a la base de datos. Pero, si el usuario no tiene este tipo de acceso tan poderoso, no puede alterar las conﬁguraciones del servidor de base de datos o conceder accesos donde no debería. Si un usuario no tiene el mínimo acceso a la base de datos, podría tenerlo utilizando la cuenta de usuario “invitado” y los cambios que podría hacer dentro del servidor de base de datos serían limitados.

108

Lección 5

f Seguridad de Nivel de base de datos

Ciertas funciones de base de datos pueden también afectar el acceso a objetos. Lo cual signiﬁca, el derecho a leer y escribir desde la base de datos. ; Listo para la certiﬁcación ¿Cuál es el permiso principal que da al usuario permisos completos a todas las bases de datos y cuál es el permiso principal que da al usuario los permisos a una sola base de datos? —5.1

Todos los usuarios son miembros de la función de base de datos pública estándar, pero las funciones deﬁnidas por el usuario son funciones personalizadas que sirven como grupos y estas funciones pueden entonces ser concedidas con el permiso al objeto de base de datos y los usuarios pueden ser asignados a una función deﬁnida por el usuario de la base de datos.

AM ÉRI

º Tome Nota

A pesar de que un usuario podría pertenecer a una función de base de datos ﬁja y tener ciertos permisos de nivel administrativo, un usuario no puede acceder a los datos sin primero concederle permiso al objeto de la base de datos. (Por ejemplo, tablas, procedimientos almacenados, vistas, funciones).

Cada permiso de objeto es asignado a través ya sea de la concesión, revocación o negación de permisos de inicio de sesión. • Conceder permiso signiﬁca que el usuario puede acceder al objeto • Denegar permiso reemplaza un permiso concedido • Revocar remueve el permiso asignado, sin importar si está denegado o concedido. • Un usuario puede tener múltiples rutas de permiso a un objeto (individualmente, a través de una función de base de datos estándar y a través de la función pública). Si cualquiera de estas rutas son denegadas, el usuario entonces será bloqueado y no podrá acceder al objeto.

IBE RO

www.pdftron.com f Seguridad de Windows

Ya que SQL Server es un entorno dentro del sistema de Windows Server, una de las preocupaciones principales del plan de seguridad se relaciona con la garantía de que Windows Server en sí es seguro. Cómo las bases de datos de SQL Server a menudo soportan sitios web, necesita tener en consideración que todos los cortafuegos y otras aplicaciones del servidor de internet se consideren cuando se elabora un plan de seguridad.

Cuenta de servicio de SQL Server

ETC

Es importante notar que el proceso del SQL server, necesita permiso para acceder a los archivos y directorios, por lo tanto, requiere una cuenta de Windows. Hay tres diferentes tipos de cuentas las cuales están disponibles para la cuenta de servicio de SQL Server: • Cuenta de usuario local: Si encuentra que el acceso a la red no es requerido, esta es la opción perfecta a considerar ya que una cuenta de usuario local no se puede utilizar fuera del entorno del servidor. • Cuenta de sistema local: Si está utilizando una instalación de un solo servidor podría elegir esta cuenta ya que el SQL Server puede utilizar la cuenta de sistema local del sistema operativo para acceder a la máquina. La única desventaja de utilizar esta cuenta de inicio de sesión es que si falla al proporcionar las credenciales de seguridad de red, necesarias para acceder

Administración de una Base de Datos

109

AM ÉRI

Autenticación de Windows

a las bases de datos con esta cuenta, puesto que es una cuenta de sistema local, esta tiene privilegios dentro del sistema operativo que la cuenta del administrador no tiene. Por lo tanto tiene un potencial hueco de seguridad. • Cuenta de usuario de dominio: Esta es la cuenta de inicio de sesión recomendada ya que SQL Server puede entonces utilizar la cuenta de Windows especíﬁcamente creada para SQL Server. Entonces puede conceder permisos de administrador para la cuenta de SQL Server con la cual puede interactuar con otros servidores en la red.

La Autenticación es el acto de establecer o confirmar una identidad de usuario o sistema. El modo de autenticación de Windows es superior al modo mixto ya que los usuarios no necesitan aprender otra contraseña debido a que aprovecha el diseño de seguridad de la red. Utilizar la Autenticación de Windows significa que los usuarios deben tener una cuenta de Windows válida con el fin de que sea reconocida por SQL Server. El SID de Windows (identificador de seguridad) es enviado al SQL Server. La Autenticación de Windows es muy robusta puesto que autenticará no sólo a los usuarios de Windows, sino también a los usuarios dentro de los grupos de usuarios de Windows. Cuando un grupo de usuario de Windows es aceptado como un inicio de sesión de SQL Server, cualquier usuario de Windows que sea miembro del grupo puede ser autenticado por SQL Server. El acceso, funciones y permisos pueden ser asignados para el grupo de usuarios de Windows y se aplicarán a cualquier usuario de Windows en el grupo.

IBE RO

www.pdftron.com SQL Server también conoce el nombre de usuario de Windows actual, de forma que la aplicación puede reunir información tanto del nivel de usuario y el nivel de grupo.

Agregar un nuevo Inicio de sesión de Windows

Los usuarios de Windows son creados y administrados en varios lugares en diferentes versiones de Windows. En Windows Vista y versiones más nuevas, los usuarios locales pueden ser administrados seleccionando el Panel de Control, después Herramientas Administrativas y ﬁnalmente Administración de equipos. Los usuarios del dominio son administrados con herramientas tales como el Active Directory Users y Computers. Una vez que existen los usuarios en la lista de usuarios de Windows o el dominio de Windows, SQL Server los puede reconocer.

ETC

Æ Agregar un Nuevo Inicio de sesión para SQL Server

PREPÁRESE. Agregar un Nuevo Inicio de sesión para SQL Server 1. Abra Seguridad y después Selección de usuario debajo del servidor y utilice el menú de contexto para seleccionar nuevo inicio de sesión (como se muestra en la Figura 5-1).

110

Lecciรณn 5

Tabla 5-1

AM ร RI

Agregando un nuevo usuario

www.pdftron.com

IBE RO

Tabla 5-2

2. En el Nuevo diรกlogo de la pรกgina General del usuario de la base de datos (como se muestra en la Figura 5-2) y/o utilice el botรณn de buscar para localizar el usuario de Windows (como se muestra en la Figura 5-3).

ETC

Eligiendo un nombre de usuario

Administración de una Base de Datos

111

Tabla 5-3

AM ÉRI

Buscando un nombre de usuario existente

www.pdftron.com

IBE RO

Tabla 5-4

3. Puede introducir un nombre de usuario o utilizar el botón Buscar para buscar a un usuario (como se muestra en la Figura 5-4 y Figura 5-5). Los usuarios de Windows son administrados y asignados a diferentes grupos de Windows utilizando la herramienta Administración de equipos.

ETC

Búsqueda de usuarios existentes

112

Lección 5

Tabla 5-5

AM ÉRI

Inicios de Sesión de SQL Serverusuario

www.pdftron.com

IBE RO

El usuario puede estar asignado a una base de datos y lenguaje predeterminados en la parte inferior del cuadro de diálogo de las propiedades de inicio de sesión de SQL Server, pero note que el asignar una base de datos predeterminada a un usuario, no se concede automáticamente el acceso a esa base de datos. Al usuario puede otorgársele el acceso a las bases de datos en la ﬁcha Acceso a base de datos.

º Tome Nota

Para crear un inicio de sesión utilizando la sintaxis de Transact-SQL de forma que pueda agregar un usuario o grupo de Windows, ejecute el comando CREATE LOGIN. Asegúrese de utilizar un nombre de usuario de Windows completo, incluyendo el nombre de dominio, del usuario que está tratando de agregar como se indica a continuación: CREATE LOGIN [XPS\Joe] FROM WINDOWS; GO

Si está buscando crear y editar inicios de sesión de usuario en el nivel de servidor, utilice la página General del inicio de sesión/Nuevo. El cuadro de diálogo Nuevo inicio de sesión también se utiliza para administrar usuarios existentes.

Remover un Inicio de sesión de Windows

ETC

Al usuario se le puede conceder acceso a las bases de datos en la ﬁcha Acceso a base de datos.

Remover un inicio de sesión de Windows desde SQL Server puede ser llevado a cabo a través del SQL Server Management Studio. Seleccione la carpeta de seguridad, al igual que como lo hizo para crear un Nuevo inicio de sesión de usuario en el Explorador de Objetos y utilice el menú para encontrar y eliminar al usuario (como se muestra en la Figura 5-6). Por supuesto, esto no elimina al usuario desde Windows, sólo remueve al usuario de SQL Server.

Administración de una Base de Datos

113

Tabla 5-6

AM ÉRI

Eliminando un Usuario Existente Serverusuario

www.pdftron.com

IBE RO

Para remover un usuario o grupo de Windows desde SQL Server, use el comando DROP LOGIN. Similar con el método utilizado en SQL Server Management Studio, el usuario o grupo de Windows continuara existiendo en Windows, será descontinuado para ser reconocido por el SQL Server: DROP LOGIN ‘XPS\Joe’

f Autenticación de SQL

El SQL server también soporta el modo Mixto, el cual le permite conectarse al SQL Server utilizando la autenticación de Windows o Autenticación de SQL Server. Una cuenta de inicio de sesión de SQL Server y las contraseñas relacionadas se deﬁnen en el SQL Server y no están relacionadas con el Active Directory o las cuentas de Windows. La cuenta sa está asociada con la autenticación de SQL. La cuenta sa es la cuenta de administrador integrada en el SQL asociada con la autenticación de SQL. Puesto que es menos segura que los inicios de sesión de Windows, se recomienda evitar el modo mixto, sin embargo está disponible para la compatibilidad con versiones anteriores.

ETC

f Roles de nivel Servidor de base de datos

Hay tres tipos de roles (funciones) del servidor de base de datos: Roles ﬁjos, roles públicos y roles deﬁnidos por el usuario. Cada una de ellas se comenta en esta sección.

Roles fijos de servidor El SQL Server incluye Roles de fijos predefinidas. Principalmente, estos roles conceden permisos para desempeñar ciertas tareas administrativas relacionadas con el servidor. Un

Lección 5

usuario puede pertenecer a varias funciones.

• • • • • •

Las siguientes funciones son las más recomendadas para delegar ciertas tareas administrativas del servidor: Bulkadmin: Puede desempeñar operaciones de insertar en volumen Dbcreator: Puede crear, alterar, eliminar y restaurar bases de datos Diskadmin: Puede crear, alterar y eliminar archivos de disco

Processadmin: Puede detener la ejecución de un proceso de SQL Server Securityadmin: Puede administrar los inicios de sesión para el servidor

AM ÉRI

114

Serveradmin: Puede conﬁgurar el servidor, incluyendo la conﬁguración de

búsquedas de texto completo y apagar el servidor • Setupadmin: Puede configurar servidores enlazados, procedimientos almacenados extendidos y el procedimiento almacenado de inicio • Sysadmin: Puede desempeñar cualquier actividad en la instalación de SQL Server, independientemente de cualquier otra configuración de permiso. La función de sysadmin incluso anula permisos denegados en un objeto. El usuario que el SQL Server crea automáticamente durante la instalación del software es el BUILTINS/Administrators el cual incluye todos los usuarios de Windows en el grupo de Administradores de Windows y permite una elección de qué grupos o usuarios son agregados durante la instalación. El usuario BUILTINS/Administrators puede ser eliminado o modificado si se desea después de la instalación.

www.pdftron.com

IBE RO

Si agrega un usuario al grupo de funciones del sysadmin, el usuario debe reconectarse a la instancia del SQL Server a ﬁn de que las capacidades de la función del sysadmin surtan efecto.

ETC

Los roles de servidor son establecidas en el Management Studio en la página de las Funciones del Servidor del cuadro de propiedades del inicio de sesión (como se muestra en la Figura 5-7).

Administración de una Base de Datos

115

Tabla 5-7

AM ÉRI

Cuadro de diálogo Propiedades de Inicio de Sesión

www.pdftron.com

IBE RO

En sintaxis de Transact-SQL, puede asignar un usuario a diferentes funciones de servidor por medio de un procedimiento almacenado, como sigue: sp_addsrvrolemember [ @loginame = ] ‘login’ , [ @rolename = ] ‘role’

Por ejemplo, el siguiente código agrega el inicio de sesión del usuario ‘‘XPS\Lauren’’ al rol del sysadmin:

Rol público

El rol público es un rol ﬁjo, pero puede tener permisos de objeto como un rol estándar. Cada usuario es automáticamente miembro del rol público y no puede ser removido, de manera que el rol público sirve como línea base o nivel de permiso mínimo.

ETC

Roles deﬁnidos por el Usuario

Puesto que no puede modificar los permisos asignados a un rol de servidor fijo, puede conceder permisos de servidor individuales a un usuario que no está definido por un rol de servidor fijo. Esto lo utilizan típicamente los usuarios que necesitan desempeñar roles de base de datos específicas y a los cuales no desea concederles un rol que permitiría al usuario hacer más de lo que necesita hacer.

116

Lección 5

f Conceder Acceso a la base de datos

A los usuarios se les debe conceder explícitamente el acceso a cualquier base de datos de usuario. Debido a que esta es una relación de muchos-a-muchos entre inicios de sesión y la base de datos, puede administrar el acceso ya sea del lado del inicio de sesión o del lado de la base de datos.

AM ÉRI

Cuando un inicio de sesión es concedido con el acceso a la base de datos, al inicio de sesión también le es asignado un nombre de usuario de la base de datos, lo cual puede ser igual al nombre del inicio de sesión o algún otro nombre a través del cual se reconozca el inicio de sesión en la base de datos. Para conceder acceso a la base de datos desde el lado del inicio de sesión utilizando el Explorador de Objetos, utilice la página de asignación de usuarios en el formulario Propiedades de inicio de sesión (como se muestra en la Figura 5-8).

Para conceder el acceso desde la base de datos, utilice el comando del menú de contexto de nuevo usuario debajo de la Base de datos después seleccione Seguridad y ﬁnalmente Nodo de usuarios para abrir el formulario de nuevo usuario de la base de datos. Introduzca el inicio de sesión a agregar en el campo de nombre de inicio de sesión. Para buscar un inicio de sesión, utilice el botón con los puntos suspensivos (. . .). Debe introducir un nombre por el cual el usuario será conocido dentro de la base de datos en el campo de nombre de usuario.

www.pdftron.com Puede utilizar el formulario Propiedades de inicio de sesión para conceder un acceso al inicio de sesión a cualquier base de datos y para asignar funciones de base de datos.

IBE RO

Por supuesto, hay una sintaxis de Transact-SQL disponible para conceder acceso a la base de datos a un usuario. El comando debe ser emitido desde la base de datos a la cual el usuario se le concederá el acceso. El primer parámetro es el inicio de sesión del servidor y el segundo el nombre de usuario opcional de la base de datos: USE Family CREATE USER ‘XPS\Lauren’, ‘LRN’

El cuadro de diálogo de inicio de sesión puede ser utilizado para agregar un nuevo usuario a la base de datos o para administrar al usuario actual. USE Family DROP USER ‘LRN’

Cuentas de inicio de sesión de Invitado

ETC

Cualquier usuario que desee acceder a la base de datos pero que no ha sido declarado como usuario dentro de esta, será automáticamente concedido con los privilegios de usuario invitado (guest user), siempre y cuando la cuenta de usuario invitado exista. La cuenta de usuario invitado no se genera al crear la base de datos ya que tiene que ser agregada especíﬁcamente ya sea a través de la interfaz del SQL Server Management Studio o a través de una sentencia de Transact-SQL, como se muestra a continuación: EXEC sp_adduser ‘Guest’ El usuario invitado debe ser removido de la base de datos cuando los invitados ya no son bienvenidos, ya que se presenta un riesgo de seguridad.

Administración de una Base de Datos

117

Seguridad de Objeto

Si un usuario accede a la base de datos, entonces se puede conceder el permiso a los objetos individuales de la base de datos. El permiso se puede conceder ya sea directamente al usuario o a una función estándar y el usuario asignado a la función. Los usuarios pueden ser asignados a múltiples funciones, así que pueden existir múltiples rutas de seguridad desde un usuario a un objeto.

AM ÉRI

DROP TABLE <nombre_tabla>

f Roles de nivel de base de datos

SQL Server incluye algunos roles de base de datos estándar o fijos. Como los roles fijos de servidor, estos principalmente organizan tareas administrativas. Un usuario puede pertenecer a varios roles. Los roles de base de datos fijos incluyen lo siguiente:

• db_accessadmin: Puede autorizar a un usuario acceder a la base de datos, pero no administrar la seguridad de nivel de base de datos. • db_backupoperator: Puede desarrollar respaldos, puntos de control y comandos de DBCC, pero no restauraciones (sólo los sysadmins del servidor pueden realizar restauraciones). • db_datareader: Puede leer los datos en la base de datos. Esta función es el equivalente de una concesión a todos los objetos de esta y puede ser anulado por medio de la negación de un permiso. • db_datawriter: Puede escribir a toda la información en la base de datos. Esta función es el equivalente a una concesión a todos los objetos y puede ser anulado por medio de la negación de un permiso. • db_ddladmin: Puede emitir comandos DDL (crear, alterar, eliminar). • db_denydatareader: Puede leer desde cualquier tabla en la base de datos. Esta negación anulará cualquier concesión a nivel de objeto. • db_denydatawriter: Bloquea la modiﬁcación de información en cualquier tabla en la base de datos. Esta denegación anulará cualquier concesión de nivel de objeto. • db_owner: Un rol especial que tiene todos los permisos en la base de datos. Esta función incluye todas las capacidades de las otras funciones. Es diferente a la función de usuario dbo. No tiene el mismo nivel que la función sysadmin del servidor, una negación de nivel de objeto anulará la membrecía en esta función. • db_securityadmin: Puede administrar la seguridad de nivel de base de datos, (roles y permisos).

IBE RO

www.pdftron.com

ETC

Asignar roles de base de datos ﬁjas con SQL Server Management Studio Los roles de nivel de base de datos ﬁjas pueden asignarse con Management Studio con cualquiera de los siguientes dos procedimientos: • Agregar un rol al usuario en el formulario Propiedades de usuario de la base de datos, ya sea al crear el usuario o después. • Agregar el usuario al rol en el cuadro de diálogo Propiedades de funciones de base de datos. Seleccionar funciones debajo del nodo de seguridad de base de datos y utilizar el menú de contexto para abrir el formulario Propiedades (como se muestra en la Figura 5-7).

118

Lección 5

Asignar roles de base de datos ﬁjas con Transact-SQL

Desde código, puede agregar un usuario a un rol de base de datos ﬁjo con el procedimiento almacenado del sistema sp_addrole. El siguiente ejemplo crea el rol de base de datos auditors que pertenece al rol de base de datos ﬁjo db_securityadmin:

AM ÉRI

USE AdventureWorks; CREATE ROLE auditors AUTHORIZATION db_securityadmin; GO

Roles de aplicación

Un rol de aplicación es un rol específico de la base de datos con la intención de permitir a una aplicación el obtener acceso sin importar el usuario. Por ejemplo, si un programa específico de Visual Basic es utilizado para buscar la tabla Customer y no maneja la identificación del usuario, el programa de VB puede acceder a SQL Server utilizando un rol de aplicación codificada. Quien esté utilizando la aplicación obtendrá acceso a la base de datos. El cuadro de diálogo Propiedades de función de base de datos enlista todos los usuarios asignados a la función actual. Para agregar o quitar un usuario del rol, utilice los botones Agregar y Quitar.

www.pdftron.com

IBE RO

f Permisos de objetos

Los permisos de objetos son los permisos que le permiten a un usuario actuar en los objetos de la base de datos (tales como tablas, procedimientos almacenados y vistas). Algunos tipos de permisos son:

ETC

• Select: El derecho para seleccionar datos. El permiso Select puede ser aplicado a columnas específicas. • Insert: El derecho a insertar datos • Update: El derecho para modificar los datos existentes. Los derechos Update para la cual la cláusula WHERE es utilizada requiere derechos de Select también. El permiso Update puede ser establecido en columnas específicas. • Delete: El derecho para eliminar los datos existentes • DRI (Referencias): El derecho para crear claves externas con DRI • Execute: El derecho para ejecutar procedimientos almacenados o funciones definidas por el usuario

Los permisos de objetos se asignan con los comandos de SQL DCL, GRANT, REVOKE y DENY. Los permisos en SQL Server funcionan como lo hacen en el sistema operativo. SQL Server agrega todos los permisos que un usuario dado podría tener, ya sea asignando directamente al usuario o a través de roles. Entonces el SQL Server otorga el MAXIMUM de lo que ha sido concedido. DENY es una excepción. DENY funciona como una gran herramienta. En cualquier lugar que exista un DENY, entonces al igual que en Windows, el usuario es bloqueado. Por ejemplo, si un

Administración de una Base de Datos

119

usuario puede utilizar SELECT en una tabla directamente asignada, pero si el rol del cual es usuario es miembro tiene un DENY para SELECT, entonces el usuario es bloqueado para emitir un SELECT contra la tabla. Si la seguridad está siendo administrada desde Management Studio o desde código, es importante comprender estos tres comandos. La concesión de permisos de objeto interactúa con el servidor y los roles de la base de datos. A continuación se tiene la jerarquía global de los roles y permisos, con 1 sobre escribiendo a 2, etc:

AM ÉRI

1. El rol del servidor sysadmin. Un inicio de sesión de Windows que posee una base de datos será mapeada a una dbo, y ya que lo hace a una dbo, ignora toda la seguridad en la base de datos. 2. Denegar un permiso de objeto o un rol de la base de datos db_denydatareader o el rol de base de datos db_denydatawriter. 3. Conceder permisos de objeto o la posesión de un objeto o el rol de la base de datos db_datareader o el rol de base de datos db_datawriter. Si su entorno prohíbe la seguridad de modo mixto, entonces la forma más fácil de veriﬁcar la seguridad es dando clic derecho en el Management Studio o QueryAnalyzery utilizar el comando RUN AS para ejecutarse como un usuario diferente, pero esto implica crear usuarios falsos en el dominio de Windows. En general, en un dominio de Windows de “producción”, la mayoría de los auditores marcarían a los usuarios falsos como un punto para la auditoria. Puesto que las estaciones de trabajo que pertenezcan a las DBA tienden a estar en dominios de producción, esta recomendación no funcionara donde los auditores estén operando.

IBE RO

www.pdftron.com Conceder permisos de objeto con sentencias de Transact-SQL

Las sentencias de concesión de permisos de objeto de Transact-SQL conceden permisos en una tabla, vista, función con valores de tabla, procedimiento almacenado, procedimiento almacenado extendido, función escalar, función de agregada, consulta de servicio o similar. La declaración de Transact-SQL para proporcionar la concesión de permiso a un objeto para un usuario especíﬁco y su función es: GRANT Permission, Permission ON Object TO User/role, User/role WITH GRANT OPTION

ETC

Los permisos pueden ser ALL, SELECT, INSERT, DELETE, REFERENCES, UPDATE o EXECUTE. El rol o nombre de usuario se reﬁere al nombre de usuario de la base de datos, cualquier función pública deﬁnida por el usuario o la función pública. Por ejemplo, el siguiente código concede permiso de Select a Joe para la tabla Emails: GRANT Select ON Emails TO Joe El siguiente ejemplo concede todos los permisos a la función pública para la tabla Contacts: GRANT All ON Contacts TO dbcreator

120

Lección 5

Múltiples usuarios o roles y múltiples permisos, pueden enlistarse en el comando. El siguiente código concede permisos de Select y Update al usuario invitado y a LRN:

GRANT Select, Update ON Emails to Guest, LRN

La opción WITH GRANT proporciona la capacidad de conceder permisos para el objeto, por ejemplo, el siguiente comando concede a Joe el permiso de Select desde la tabla Email y puede conceder el permiso Select a otros usuarios:

f Administrar roles

AM ÉRI

GRANT Select ON Email TO Joe WITH GRANT OPTION

Los Roles pueden ser creados, administrados y removidos utilizando SSMS o ejecutando sentencias de Transact-SQL. Por supuesto, sólo debería crear roles y asignar usuarios a esos roles cuando sea necesario.

Administrando roles con sentencias de Transact-SQL

Crear roles estándar con código involucra utilizar el procedimiento almacenado del sistema sp_addrole. El nombre puede ser de hasta 128 caracteres y no puede incluir un backslash, ser nulo o ser una cadena vacía. Por defecto, los roles serán del usuario dbo. Sin embargo, puede asignar el rol de propietario agregando un segundo parámetro. El siguiente código crea la función de administración:

www.pdftron.com

IBE RO

CREATE ROLE “Manager”

Lo contrario a crear un rol es removerlo. Un rol podría no ser eliminado si algún usuario está asignado actualmente a él. El procedimiento almacenado del sistema sp_droprole removerá el rol de la base de datos: DROP ROLE “Manager”

Una vez que el rol ha sido creado, los usuarios pueden ser asignados al rol por medio del procedimiento almacenado del sistema sp_addrolemember. El siguiente ejemplo de código asigna a Joe el rol Manager: EXEC sp_addrolemember “Manager” , “Joe” No es de extrañar que el procedimiento almacenado del sistema sp_droprolemember remueva un usuario de un rol asignado. Este código libera a Joe de la tarea de ser manager:

ETC

EXEC sp_dropRoleMember”Manager” , “Joe”

Estructura jerárquica de roles Si la estructura de seguridad es compleja, entonces una técnica de organización de permisos poderosa es la que diseña la estructura jerárquica de roles de base de datos estándar. En otras palabras, puede anidar funciones de base de datos deﬁnidas por el usuario. • El rol worker puede tener acceso limitado.

Administración de una Base de Datos

121

• El rol de administración puede tener todos los derechos de worker además de derechos adicionales para buscar tablas. • El rol del administrador podría tener derechos de administración además del derecho para desempeñar otras tareas de administración en la base de datos. Para llevar a cabo este tipo de diseño, siga estos pasos: 1. Crear el rol worker y establecer sus permisos.

AM ÉRI

2. Crear el rol de administración y establecer sus permisos. Agregar la función de administración como usuario al rol worker.

3. Crear el rol admin. Agregue el rol admin como un usuario a la función de administración.

La ventaja de este tipo de organización de seguridad es que un cambio en el nivel inferior afecta a todos los niveles superiores. Como resultado, se requiere administración en una sola ubicación, en lugar de docenas de ubicaciones.

f Seguridad de objetos y Management Studio

Debido a que involucra a usuarios, roles y objetos, los permisos de objeto pueden establecerse desde numerosas ubicaciones dentro del Management Studio. Es casi un laberinto.

Æ Modiﬁcar el permiso de un objeto

www.pdftron.com

IBE RO

PREPÁRESE. Desde la lista de objetos, siga estos pasos para modiﬁcar los permisos a un objeto: 1. Desde el nodo de un objeto (tablas, vistas, procedimientos almacenados o funciones deﬁnidas por el usuario).

2. En el Navegador de Objetos, seleccione Propiedades desde el menú de contexto para abrir el diálogo de Propiedades para ese tipo de objeto.

3. Haga clic en la página de Permisos para abrir el cuadro de diálogo Propiedades de Objeto.

La sección superior del formulario es para seleccionar un usuario o rol y asignarle o verificar permisos. El usuario debe tener acceso a la base de datos que será seleccionada. Al igual que con la configuración de sentencias de permisos en la ficha Propiedades de Seguridad de la Base de Datos, puede seleccionar conceder, withgrant o denegar. La lista de objetos en la parte superior del cuadro de diálogo enlista todos los objetos en la base de datos. Esta lista puede utilizarse para cambiar rápidamente a otros objetos sin salir del formulario a la consola y seleccionar un objeto diferente.

ETC

Si el usuario o rol tiene permiso para la tabla, el botón de Columnas abre el cuadro de diálogo Permisos de Columna. Seleccione el usuario y haga clic en el botón para establecer los permisos de columnas para ese usuario. Sólo los permisos de seleccionar y actualizar pueden ser establecidos en el nivel de columna, ya que los de insertar y eliminar afectan toda la ﬁla.

Establecer permisos desde la lista de usuario Desde la lista de los usuarios de la base de datos en Management Studio, seleccione un usuario y haga doble clic o seleccione Propiedades del menú de contexto al hacer clic

122

Lección 5

derecho. El cuadro de diálogo Propiedades de Usuario de la base de datos se utiliza para asignar usuarios a los roles (como se muestra en la Figura 5-8).

Tabla 5-8

AM ÉRI

Cuadro de diálogo Propiedades de inicio de sesión Server usuario

www.pdftron.com

IBE RO

Dar clic en el botón de Propiedades abrirá las propiedades del rol seleccionado. En el cuadro de diálogo de las propiedades de Usuario de la Base de Datos, la página de los Elementos que pueden protegerse se utiliza para asignar o veriﬁcar los permisos de objetos. Este cuadro de diálogo es similar a la ﬁcha Permisos del cuadro de diálogo Propiedades de Objeto de la Base de Datos.

Establecer permisos desde la lista de roles La tercera forma de controlar los permisos de objetos es desde el rol de la base de datos. Para abrir el cuadro de diálogo de propiedades de los roles de la base de datos, haga doble clic en un rol en la lista de funciones o seleccione Propiedades desde el menú de contexto al hacer clic derecho. El cuadro de diálogo de las propiedades de las Funciones de la Base de Datos se puede utilizar para asignar usuarios u otros roles al rol y para eliminarlos del rol.

ETC

El botón de Permisos abre el cuadro de diálogo de permisos para el rol. Este formulario opera como el otro formato de permisos excepto que está organizado desde la perspectiva del rol.

Administración de una Base de Datos

123

f Cadenas de propiedad

Por ejemplo:

En bases de datos de SQL Server, los usuarios pueden acceder con frecuencia a los datos a través de uno o varios objetos. Las cadenas de propiedad aplican a las vistas, procedimientos almacenados y a funciones deﬁnidas por el usuario.

AM ÉRI

• Un programa puede invocar un procedimiento almacenado y luego seleccionar datos de una tabla. • Un informe puede seleccionar de una vista, la cual realice una selección de una tabla. • Un procedimiento almacenado complejo puede invocar otros varios procedimientos almacenados. En estos casos, el usuario debe tener permiso para ejecutar el procedimiento almacenado o realizar una selección de la vista. El hecho de que el usuario también necesite permiso para seleccionar de las tablas subyacentes, depende de la cadena de propiedad del objeto que llame el usuario hacia las tablas subyacentes. Si la cadena de propiedad permanece sin romperse desde el procedimiento almacenado hacia las tablas subyacentes, el procedimiento almacenado puede ejecutarse utilizando el permiso de su propietario. El usuario únicamente necesita permiso para ejecutar el procedimiento almacenado. El procedimiento almacenado puede utilizar el permiso de su propietario para acceder a las tablas subyacentes.

www.pdftron.com º Tome Nota

IBE RO

Es importante notar que si se utiliza SQL dinámico, entonces la cláusula EXECUTE AS para CREATE PROCEDURE fue agregado a SQL Server 2005. Puesto que se ejecuta en un lote separado, rompe la cadena de propiedad.

El usuario no necesita tener permiso para las tablas subyacentes.

Las cadenas de propiedad son de utilidad para desarrollar una seguridad estricta en la cual los usuarios ejecuten procedimientos almacenados pero no se les otorgan permisos directos a las tablas. Si la cadena de propiedad se rompe, lo cual signiﬁcaría que hay un propietario diferente entre un objeto y el siguiente objeto inferior, entonces SQL Server revisa el permiso del usuario para cada objeto que se acceda.

ETC

Cuando se rompe la cadena:

• La cadena de propiedad de dbo.Aa dbo.Ba dbo.Person permanece sin romperse, así que dbo.A puede invocar adbo.B y acceder adbo.Person como dbo. • La cadena de propiedad de dbo.A a Sue.C a Joe.Purchase se rompe debido a que se encuentran presentes diferentes propietarios. Por lo tanto dbo.A llama a Sue.C utilizando los permisos de Joe, y Sue.C accede a Joe.Purchase utilizando los permisos de Joe. • La cadena de propiedad de dbo.A a través de dbo.B a Joe.Person también se rompe, así que dbo.A llama a dbo.B utilizando los permisos de dbo, pero dbo.B debe acceder a Joe.Purchase utilizando los permisos de Joe. • Es posible que dbo, Sue, y Joe tengan el mismo propietario. En este caso, la cadena de propiedad funcionará.

124

Lección 5

f Un ejemplo de un modelo de seguridad

Tabla 5-1 Rol estándar

Función jerárquica

Función de servidor Sysadmin

Clerk

Admin Rol ﬁjo de base de datos Customer

Tablas de grupo de archivos primarios

Tablas de grupo de archivo estáticos

Otros permisos

AM ÉRI

Conﬁguración de permisos para PBXKites

Para algunos ejemplos de permisos utilizando la base de datos OBXKites, la tabla 5-1 enlista la conﬁguración de los permisos de las funciones de base de datos estándar. La tabla 5-2 enlista algunos de los usuarios y sus roles.

Ejecuta permisos para varios procedimientos almacenados que leen y actualizan tablas diarias.

Db_owner

www.pdftron.com

IBE RO

Tabla 5-2

Permisos Select

Usuario

Sammy Joe LRN Clerk Windows group (Betty, Tom, Martha, and Mary)

Roles estándar en la base de datos Admin Public IT DBA Clerk

Utilizando este modelo de seguridad, los siguientes usuarios pueden realizar las siguientes tareas: • Betty, como miembro de la función Clerk, puede ejecutar la aplicación que ejecuta lo procedimientos almacenados que recuperan y actualizan datos. Betty puede ejecutar consultas select como miembro del rol Public. • LRN, como el IT DBA, puede realizar cualquier tarea en la base de datos como miembro del rol de servidor sysadmin. • Joe puede ejecutar consultas select como miembro del rol public. • Como miembro del rol admin, Sammy puede ejecutar todos los procedimientos almacenados. Él también puede modiﬁcar manualmente cualquier tabla utilizando consultas. Como miembro del rol admin que incluye el rol db_owner, Joe puede realizar cualquier tarea administrativa en la base de datos y seleccionar o modiﬁcar datos en cualquier tabla. • Sólo LRN puede restaurar desde los respaldos.

ETC

Usuarios y sus funciones para OBXKites

Administración de una Base de Datos

125

Respaldar y restaurar bases de datos

È EN RESUMEN

El objetivo es comprender varios tipos de respaldos como el complete y el incremental, la importancia de los respaldos y cómo restaurar una base de datos.

AM ÉRI

Este tema explica cómo crear un respaldo y restaurar un respaldo de base de datos completo. El propósito de un respaldo de una base de datos es en caso de que tenga que restaurarla si los datos se pierden en alguna transacción cotidiana. Un usuario puede eliminar accidentalmente una tabla o un administrador de la base de datos puede necesitar restaurar varias tablas en distintos servidores para poder combinarlas en una sola base de datos. La necesidad de respaldar una base de datos y contar con un plan de restauración son prioritarios.

f Respaldos de bases de datos

El ámbito de un respaldo de datos (un data backup) puede ser toda la base de datos, una parte de la base de datos o un conjunto o grupo de archivos. Para cada uno de estos casos, SQL soporta respaldos completos, diferenciales e incrementales.

www.pdftron.com • Respaldo completo: Un respaldo completo contiene todos los datos en una base de datos especíﬁca o conjunto o grupo de archivos y es lo suﬁcientemente grande para recuperar los datos. • Respaldo diferencial: Un respaldo diferencial se basa en el último respaldo completo de datos. A esto se le conoce como la base del diferencial. Un respaldo diferencial contiene sólo los datos que han cambiado a partir de la base diferencial. Generalmente, los respaldos diferenciales que se toman a la brevedad después del respaldo base son más pequeños y rápidos de crear que la base de un respaldo completo. Por lo tanto, al utilizar respaldos diferenciales se acelera el proceso de llevar a cabo respaldos frecuentes para reducir el riesgo de pérdida de datos. Generalmente, una base diferencial se utiliza por varios respaldos diferenciales sucesivos. A la hora de restaurar, el respaldo completo se restaura primero, seguido del respaldo diferencial más reciente. • Respaldo incremental: Un respaldo incremental se basa en el último respaldo de datos. Un respaldo incremental contiene sólo los datos que han cambiado desde el último respaldo completo o incremental. Los respaldos incrementales son los más pequeños y rápidos de crear en comparación con el respaldo completo y diferencial. Sin embargo, al momento de la restauración, primero se restaura el respaldo completo, seguido de cada respaldo incremental que siga al respaldo completo.

; Listo para la certiﬁcación

IBE RO

¿Cuál es la diferencia entre un respaldo completo y uno incremental? — 5.2

ETC

A través del tiempo, a medida que se actualiza una base de datos, la cantidad de datos que se incluyen en los respaldos diferenciales se incrementa. Esto hace que sea más lento crear y restaurar el respaldo. Eventualmente, se tendrá que crear otro respaldo completo para proporcionar una nueva base diferencial para otra serie de respaldos diferenciales. Después del primer respaldo de datos, debajo del modelo de recuperación completo o modelo de recuperación bulk-logged, se requieren de respaldos log de transacción (respaldos log). Cada respaldo log cubre la parte del log de transacción que estaba activo

126

Lección 5

cuando se creó el respaldo y el respaldo log incluye todos los registros log que no estaban respaldados en el respaldo log anterior.

Tipos de respaldos de bases de datos

Tipo de respaldo Respaldos de bases de datos Respaldos diferenciales de bases de datos

Apellido Un respaldo completa de toda la base de datos. Los respaldos de la base de datos representan a toda la base de datos cuando se termina el respaldo. Un respaldo de todos los archivos en la base de datos. Este respaldo contiene sólo los datos que se modiﬁcaron desde el respaldo más reciente de cada archivo.

AM ÉRI

Tabla 5-3

Los respaldos de bases de datos son fáciles de utilizar y recomendables siempre que el tamaño de la base de datos lo permita. Consulte la tabla 5-3 para ver los tipos de respaldos de bases de datos soportados por SQL Server.

Respaldos parciales

Los respaldos parciales y parciales diferenciales se diseñaron para proporcionar una mayor ﬂexibilidad para realizar respaldos de bases de datos que contienen grupos de archivos de sólo lectura bajo el mismo modelo simple de recuperación. Sin embargo, estos respaldos se encuentran soportados por todos los modelos de recuperación. Consulte la tabla 5-4 para ver los tipos de respaldos parciales soportados por SQL Server 2008.

Tabla 5-4 Tipos de respaldos parciales

Tipo de respaldos Respaldo parcial

Descripción Un respaldo de todos los datos completos del grupo de archivos primarios, cada grupo de archivos de lectura/escritura y cualquier archivo o grupo de archivos de sólo lectura. Un respaldo parcial de bases de datos de solo lectura contienen solo el grupo de archivos primarios. Un respaldo que contiene sólo los datos que se modiﬁcaron desde el respaldo parcial más reciente del mismo conjunto de grupos de archivos.

IBE RO

www.pdftron.com Respaldo parcial diferencial

Respaldos de archivos

ETC

Los archivos en una base de datos se pueden respaldar y restaurar de forma individual. Al utilizar los respaldos de archivos se puede incrementar la velocidad de recuperación puesto que le permite restaurar sólo los archivos dañados sin la necesidad de restaurar el resto de la base de datos. Por ejemplo, si una base de datos tiene varios archivos que se encuentran localizados en discos diferentes y uno falla, sólo el archivo del disco dañado se debe restaurar. Sin embargo tanto, la planeación y restauración de respaldos de archivos puede ser algo complejo; por lo tanto, los respaldos de archivo se deben utilizar sólo cuando estos aportaran claramente valor a su plan de restauración. Como se muestra en la tabla 5-5 para los tipos de respaldos de archivos soportados por SQL Server.

Administración de una Base de Datos

Tabla 5-5 Tipos de respaldos de archivos

Tipo de respaldos Respaldo de archivo

127

Descripción Un respaldo completo de todos los datos en uno o más archivos o grupos de archivos.

AM ÉRI

Importante: Bajo el modelo de recuperación simple, los respaldos de archivos se encuentran básicamente restringidos a grupos de archivos de sólo lectura. Puede crear un respaldo de archivos de un grupo de archivos de lectura/escritura, pero antes de poder restaurar un respaldo de archivos de lectura/escritura debe establecer el grupo de archivos a sólo lectura y tomar un respaldo de archivos de sólo lectura diferencial.

Respaldos de archivos diferenciales

Un respaldo de uno o más archivos que contienen datos que cambiaron desde el respaldo completo más reciente de cada archivo. Nota: Bajo un modelo de recuperación simple, este asume que los datos han cambiado a sólo lectura desde el archivo completo.

Dispositivos de respaldo

Los respaldos de SQL Server se crean en dispositivos de respaldos como archivos de disco o medios de cinta. Puede agregar nuevos respaldos a cualquier respaldo existente en un dispositivo o sobre escribir respaldos existentes.

www.pdftron.com Calendarizar respaldos

IBE RO

Realizar una operación de respaldo tiene un efecto mínimo en las transacciones que se estén ejecutando; por lo tanto, las operaciones de respaldo pueden ejecutarse durante operaciones regulares. Durante una operación de respaldo, SQL Server copia los datos directamente de los archivos de la base de datos hacia los dispositivos de respaldo. Los datos no cambian y las transacciones que se encuentran ejecutándose durante el respaldo no se retrasan. Por lo tanto, es posible llevar a cabo un respaldo en SQL Server con un efecto mínimo sobre la carga de trabajo.

f Restaurar bases de datos

SQL Server soporta una variedad de escenarios de restauración que se encuentran incluidas en esta sección

ETC

Los escenarios de restauración incluyen lo siguiente: • Restauración completa de la base de datos: Restaura toda la base de datos, empezando con un respaldo completo de la base de datos, que puede ir seguido por una restauración de base de datos diferencial (y respaldos log). • Restauración de archivos: Restaura un archivo o grupo de archivos en una base de datos de grupos de archivos múltiples. Después de una restauración de archivos completa, se puede restaurar un respaldo de archivos diferencial. • Restauración de página: Restaura páginas individuales. • Restauración por Etapas: Restaura la base de datos por etapas, empezando con el grupo de archivos primario y uno o más grupos de archivos secundarios. • Sólo recuperación: Recupera datos que son consistentes con la base de datos y necesita sólo estar disponible.

Lección 5

• Restauración log de transacciones: Bajo el modelo de restauración completo o bulk-log, se requiere de respaldos log de restauración para alcanzar el punto de restauración deseado. • Crear una base de datos espejo. • Crear y mantener un servidor en standby (espera).

Usar el SSMS

Para insertar datos a través de una herramienta de interfaz gráﬁca, siga los siguientes pasos.

AM ÉRI

128

Æ Modiﬁcar el permiso de un objeto

PREPÁRESE. Antes de comenzar, asegúrese de abrir la aplicación de SQL Server Management Studio y conectarse a la base de datos con la que desee trabajar.

1. Después de conectarse con la instancia apropiada del Motor de Base de Datos del Microsoft SQL Server, en el Explorador de Objetos, haga clic en el nombre del servidor y expanda el árbol del servidor. 2. Expanda Bases de Datos. Dependiendo de la base de datos, seleccione ya sea una base de datos de usuario o expanda Bases de Datos del Sistema y posteriormente seleccione una base de datos de sistema.

www.pdftron.com 3. Haga clic derecho en la base de datos, coloque el cursor sobre Tareas y haga clic en Restaurar.

IBE RO

4. Haga clic en Base de Datos, para abrir el cuadro de diálogo Restaurar Base de Datos.

5. En la página General, el nombre de la base de datos a restaurar aparece en la lista A una base de datos. Para crear una nueva base de datos, introduzca su nombre en la lista. 6. En el cuadro de texto A un momento dado, conserve ya sea la opción predeterminada (lo más reciente posible) o seleccione una fecha y hora especíﬁca al hacer clic en el botón explorar, que abre el cuadro de diálogo Restauración a un momento dado.

7. Para especiﬁcar la fuente y ubicación de los respaldos a restaurar, haga clic en una de las siguientes opciones:

• Desde base de datos Introduce el nombre de una base de datos en la lista. • Desde dispositivo Haga clic en el botón restaurar que abre el cuadro de diálogo Especiﬁcar copia de seguridad. En la lista Medio para copia de seguridad, seleccione uno de los tipos de dispositivos enlistados. Para seleccionar uno o más dispositivos de la lista ubicación de la copia de seguridad, haga clic en Agregar. Después de agregar los dispositivos que quiera de la lista Ubicación de la copia de seguridad, haga clic en Aceptar para regresar a la página General.

ETC

8. En Seleccionar los conjuntos de copia de seguridad que va a restaurar, seleccione los respaldos a restaurar. Este apartado muestra los respaldos disponibles en la ubicación especiﬁcada. De forma predeterminada, se recomienda un plan de recuperación. Para sustituir el plan de recuperación especíﬁca, puede cambiar la selección en la cuadrícula. Cualquier respaldo que dependa de un respaldo no seleccionado se desactiva de forma automática. 9. Para ver o seleccionar las opciones avanzadas, haga clic en Opciones en el panel Seleccionar una página.

10. En el panel Opciones de restauración, puede seleccionar cualquiera de las siguientes opciones si corresponden a su situación:

Administración de una Base de Datos

Sobrescribir la base de datos existente Conservar la conﬁguración de replicación Preguntar antes de restaurar cada copia de seguridad Restringir el acceso a la base de datos restaurada

• • • •

129

11. De forma opcional, puede restaurar la base de datos a una nueva ubicación al especiﬁcar un destino de restauración nueva para cada archivo en la cuadrícula Restaurar los archivos de base de datos como.

AM ÉRI

12. El panel Estado de recuperación determina el estado de la base de datos después de la operación de restauración. El comportamiento predeterminado es:

Dejar la base de datos lista para utilizar al revertir las transacciones no completadas. Los logs de transacciones adicionales no se pueden restaurar(RESTORE WITH RECOVERY) (sólo seleccione esta opción si se encuentra restaurando todos los respaldos necesarios en este punto). ; Listo para la certiﬁcación

13. De manera alternativa, puede seleccionar cualquiera de las siguientes opciones:

• Deje la base de datos como no-operacional y no revierta las transacciones no completas. Los logs de transacciones adicionales se pueden restaurar. (RESTORE WITH NORECOVERY). • Deje la base de datos en modo sólo lectura. Deshaga las transacciones no completadas, pero guarde las acciones deshechas en un archivo en standby para que los efectos de recuperación se pueda revertir. (RESTORE WITH STANDBY).

Comprender los respaldos de base de datos y restauración —5.2

www.pdftron.com Uso del comando RESTORE

ETC

IBE RO

EL comando Transact-SQL RESTORE le permite llevar a cabo los siguientes escenarios de restauración:

• Restaurar toda una base de datos de un respaldo de bases de datos completa (restauración completa). • Restaurar parte de una base de datos (restauración parcial). • Restaurar archivos específicos o grupos de archivos en una base de datos (restauración de archivos). • Restaurar páginas específicas en una base de datos (restauración de páginas). • Restaurar un log de transacciones en una base de datos (restauración de log de transacciones). • Revertir una base de datos a un punto específico capturado por una snapshot (línea base) de base de datos

130

Lección 5

En esta lección aprendió:

Resumen de Habilidades

AM ÉRI

• El resultado final de trabajar con la seguridad de una base de datos es asegurar los derechos y responsabilidades asignados a los usuarios, a través de la creación de usuarios y funciones se establece un plan de seguridad. • Se utiliza un permiso para otorgar a una entidad como un usuario el acceso a un objeto como otro usuario o una base de datos. • Un inicio de sesión es el proceso a través del cual el acceso a un sistema computacional se controla con la identificación de las credenciales del usuario. El método más común de inicio de sesión es a través de un nombre de usuario y una contraseña. • Una cuenta de usuario es una representación lógica de una persona en un sistema electrónico. • Aun cuando un usuario puede pertenecer a un rol de base de datos fija y tener alguno permisos de nivel administrativo, el usuario puede seguir sin tener acceso a los datos si primero no tiene permiso al objeto de base de datos en si (por ejemplo, tablas, procedimientos almacenados, vistas, funciones). • Cada permiso de objeto se asigna ya sea al otorgar, revocar o denegar permisos de inicio de sesión de usuario. • La autenticación es el acto de establecer o confirmar un usuario o identidad de sistema. • El modo de autenticación de Windows es superior al modo mixto ya que los usuarios no necesitan aprender otra contraseña y debido a que ayuda en el diseño de seguridad de la red. • En sociedad con la autenticación de SQL se encuentra la cuenta sa. La cuenta sa es la cuenta de administrador prediseñada de SQL asociada con la autenticación de SQL. • SQL Server tiene roles de servidor predeterminadas fijas, estos roles otorgan permisos para llevar a cabo ciertas tareas administrativas relacionadas con el servidor. • Sysadmin puede llevar a cabo cualquier actividad en la instalación de SQL Server, a pesar de cualquier otro conjunto de permisos. El rol sysadmin reemplaza los permisos en un objeto. • El rol public es una rol fija, pero puede tener permisos de objeto como un rol estándar. Cada usuario es automáticamente miembro del rol public y no puede ser removido, así que el rol public sirve como base o como nivel de permiso mínimo. • Los usuarios deben tener acceso explicito para acceder a cualquier base de datos. • db_owner es un rol especial que tiene todos los permios en la base de datos. • Un rol de aplicación es un rol específico de base de datos que pretende permitir que una aplicación tenga acceso sin importar el usuario. • El propósito de un respaldo de base de datos es restaurar los datos si existe una perdida durante una rutina cotidiana. • Un usuario puede borrar accidentalmente una tabla o un administrador de base de datos pueden necesitar restaurar varias tablas en servidores diferentes para combinarlas en una sola base de datos. • El respaldo completo contiene todos los datos en una base de datos específica o conjunto de grupos de archivos o archivos y también es lo suficientemente grande para permitir la recuperación de los datos.

ETC

IBE RO

www.pdftron.com

Administración de una Base de Datos

131

» Evaluación de Conocimientos

AM ÉRI

Verdadero o Falso

• El respaldo diferencial sólo respalda los datos desde la última actualización completa. • El respaldo incremental sólo respalda los datos desde el último respaldo completo o incremental.

Encierre la V si la frase es cierta o F si es falsa. V

1. Un usuario debe tener permisos para acceder a los archivos que conforman la base de datos para poder utilizar la base de datos. 2. Utilice la sentencia CREATE LOGIN para permitirle a una cuenta de Windows acceder a SQL-Server. 3. Cualquier permiso DENY siempre reemplaza un permiso concedido. 4. Utilice la sentencia CREATE ROLE para crear funciones nuevas dentro de una base de datos. 5. Los respaldos diferenciales múltiples deben restaurarse en el mismo orden en el cual se crearon originalmente.

www.pdftron.com Completar los espacios en blanco

IBE RO

Complete las siguientes frases al escribir la palabra o palabras correctas en los espacios en blanco proporcionados.

1. SQL Server utiliza la _________ de Windows para veriﬁcar que un usuario es válido antes de permitirle el acceso. 2. Un respaldo diferencial contiene sólo los datos que han _______ desde la base diferencial.

3. Todos los usuarios son miembros automáticos del rol de base de datos _________. 4. Utilice el comando _______ para permitir a los usuarios acceder a los objetos en la base de datos. 5. Utilice el comando __________ para recuperar datos que se eliminaron de forma accidental por un usuario.

Opción múltiple

ETC

Encierre la letra que corresponde a la respuesta. 1. ¿Cuál de las siguientes opciones no es un comando T-SQL para manejar permisos? a. b. c. d.

GRANT REVOKE PERMIT DENY

Lección 1

a. b. c. d.

Server Database (Base de datos) Table (Tabla) Task (Tarea)

2. ¿Cuál de las siguientes opciones no es un nivel de seguridad soportado por SQL-Server?

3. ¿Cuál de las siguientes opciones no es un permiso de base de datos que pueda aplicarse a objetos? a. b. c. d.

DROP SELECT INSERT UPDATE

AM ÉRI

132

4. Indique cuáles de las siguientes opciones son soportadas por escenarios de restauración de bases de datos. a. b. c. d.

Restaurar toda una base de datos a partir de un respaldo completo Restaurar toda una base de datos a partir de una serie de respaldos parciales Restaurar parte de una base de datos utilizando respaldos parciales Restaurar archivos especíﬁcos utilizados por la base de datos

5. ¿Cuál de las siguientes opciones no es un tipo de respaldo soportado por SQL Server?

www.pdftron.com Completo Diferencial Archivo Dispositivo

IBE RO

a. b. c. d.

6. ¿Cuál es la cuenta SQL prediseñada utilizada en modo mixto que tiene acceso total al SQL Server? a. b. c. d.

Fulladmin Sa Admin Administrator

7. Se le acaba de contratar como administrador de la base de datos y quiere dar acceso total de administrador de base de datos a su SQL Server. ¿Cuál rol debería asignar? a. b. c. d.

Diskadmin SecurityAdmin Sysadmin Db_owner

ETC

8. ¿Cuál rol da acceso total a una base de datos individual? a. b. c. d.

Db_owner Db_accessadmin Db_securityadmin Db_ddladmin

Administración de una Base de Datos

133

a. b. c. d.

Respaldar, respaldar, respaldar Utilizar RAID Utilizar UPS Utilizar NICs redundantes

9. El mejor método de recuperación de datos es __________.

10. ¿Cuál modo permite inicios de sesión de cuentas de Windows y de SQL? Cualquiera Completo Compartido Mixto

AM ÉRI

a. b. c. d.

» Evaluación de destreza

Escenario 5-1: Seguridad de SQL Server

Su jefe tomó un curso de SQL en la Universidad hace algunos años. Le pregunta si puede iniciar sesión en el servidor SQL con la cuenta sa para poder ver la base de datos y ejecutar algunas consultas. Desafortunadamente, usted le dice que la cuenta sa no está disponible en el servidor. Él quiere saber por qué y ver la forma en la cual puede acceder a la base de datos. ¿Qué es lo que le dirá?

www.pdftron.com Escenario 5-2: Uso de respaldos completos e incrementales

IBE RO

Tiene 6 bases de datos grandes, cada una de al menos 2 GB. Necesita asegurarse de respaldar las bases de datos todas las noches en caso de desastre y necesitar recuperarse a través de un respaldo. Puesto que a cada base de datos le toma 2 horas llevar a cabo un respaldo y sólo cuenta con 6 horas para hacer respaldos, ¿Cuál solución de respaldos recomendaría?

» Evaluación de dominio

Escenario 5-3: Respaldo de una base de datos Como administrador de bases de datos experimentado, comprende la importancia de los respaldos. Puesto que va a llevar a cabo algunas tareas de mantenimiento a las bases de datos y a los servidores de bases de datos, decide llevar a cabo un respaldo. Por lo tanto, utilizando SQL Server Management Studio, ¿cuáles pasos usaría para realizar un respaldo completo de AdventureWorks y guardarlo en su disco duro?

ETC

Escenario 5-4: Restaurar un respaldo (Restauración completa) Cuando estaba llevando a cabo algunas tareas de mantenimiento, una de sus tablas se dañó. Afortunadamente, había realizado un respaldo de la base de datos. Por lo tanto, utilizando SSMS, ¿cuáles pasos usaría para restaurar la base de datos que respaldó en el ejercicio anterior? Deje la base de datos lista para utilizar al revertir las transacciones no cometidas. Los logs de transacciones adicionales no se pueden restaurar. (RESTORE WITH RECOVERY)

CA AM ÉRI

ETC

IBE RO

www.pdftron.com

Apéndice A

Fundamentos de bases de datos

Dominio del Objetivo

AM ÉRI

» Fundamentos: Examen 98-364

Comprender cómo la información es almacenada en tablas.

Número de la Habilidad

Número de la Lección

1.1

Comprender los conceptos de bases de datos relacionales.

1.2

Comprender el lenguaje de manipulación de datos (DML).

1.3

Comprender el lenguaje de deﬁnición de datos (DDL).

1.4

Seleccionar tipos de datos.

2.1

Comprender las tablas y saber cómo crearlas .

2.2

Crear vistas.

2.3

2.4

Seleccionar datos.

3.1

Insertar datos.

3.2

Actualizar datos.

3.3

Eliminar datos.

3.4

Comprender la Normalización..

4.1

Comprender las claves principales, externas y compuestas..

4.2

Comprender los índices.

4.3

Comprender Conceptos de Seguridad de Bases de Datos.

5.1

Comprender los Respaldos y Restauraciones de Bases de Datos.

5.2

www.pdftron.com

ETC

IBE RO

Crear procedimientos almacenados y funciones

CA AM ÉRI

ETC

IBE RO