LIBRO-Diseño de Base de Datos

UNIVERSIDAD PRIVADA TELESUP

q

1


Pre acio:

La asignatura de Diseño de Base de Datos, es de naturaleza teórico práctico y pertenece al área de formación profesional. El presente curso tiene como propósito introducir al participante en el diseño de la base de datos; para ello se plantea el uso de herramientas de software sofisticados con la finalidad de brindar el soporte para el diseño y manejo de la base de datos. Debido al creciente proceso de mejora en las organizaciones, se requiere de la disposición de herramientas y técnicas específicas específicas de diseño de base de datos en las organizaciones actuales.

Comprende cuatro Unidades de Aprendizaje:

Unidad I: Base de Datos Unidad II: Herramientas de Base de Datos: ERWIN. Unidad III: Manejo del SQL Básico Unidad IV: Manejo de SQL Avanzad Avanzado. o.

2


Base de Datos

Introducción a la Base de Datos

Herramientas de

Manejo del SQL

Base de Datos :

Básico

Manejo de SQL Avanzado

Erwin SQL-Básico Introducción al Erwin

Sistema de Gestión de base de datos

Lenguaje de Manipulación de Datos

Creación de Llaves Modelo Entidad Relación

Consultas con varias tablas internas y externas

Consultas Avanzadas Funciones en SQL

Creación de Relaciones Normalización

Índices y Vistas

Modelo físico de Datos

Procedimientos Almacenados

Triggers

La competencia que el estudiante debe lograr al final de la asignatura es: “Aplica las técnicas y métodos del Diseño de Base de

Datos para la construcción de modelo, expresando sus ideas con coherencia, lógica, orden, claridad, fundamento y buen lenguaje.”

3


Índice del Contenido

I. REFACIO II. DESARROLLO DE LOS CONTENIDOS UNIDAD DE APRENDIZAJE 1: BASE DE DATOS 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. Tema 01: Introducción a la Base de datos b. Tema 02: Sistema de Gestión de base de datos c. Tema 03: Modelo Entidad Relación d. Tema 04: Normalización 3. Lecturas recomendadas 4. Actividades 5. Autoevaluación 6. Resumen UNIDAD DE APRENDIZAJE 2: HERRAMIENTAS DE BASE DE DATOS: ERWIN 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. Tema 01:Introducción al Erwin b. Tema 02: Creación de Llaves c. Tema 03: Creación de Relaciones d. Tema 04: Modelo físico de Datos 3. Lecturas recomendadas 4. Actividades 5. Autoevaluación 6. Resumen UNIDAD DE APRENDIZAJE 3: MANEJO DEL SQL BASICO 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. Tema 01: SQL-Básico b. Tema 02: Lenguaje de Manipulación de Datos c. Tema 03: Funciones en SQL d. Tema 04: Índices y Vistas 3. Lecturas recomendadas 4. Actividades 5. Autoevaluación 6. Resumen UNIDAD DE APRENDIZAJE 4: MANEJO DE SQL AVANZADO 1. Introducción a. Presentación y contextualización b. Competencia c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. Tema 01: Consultas con varias tablas internas y externas b. Tema 02: Consultas Avanzadas c. Tema 03: Procedimiento Almacenados d. Tema 04: Triggers 3. Lecturas recomendadas 4. Actividades 5. Autoevaluación 6. Resumen III. GLOSARIO IV. FUENTES DE INFORMACI N V. SOLUCIONARIO

02 05 - 167 05-48 06 06 06 06 06 06 07-34 07 15 25 34 45 45 46 48 49-85 50 50 50 50 50 50 51-73 51 61 68 73 82 82 83 85 86-125 87 87 87 87 87 87 88-97 88 98 108 118 122 122 123 125 126-164 127 127 127 127 127 127 128-125 128 138 146 154 161 161 162 164 165 166 167

4


Introducción 5


Introducción

a)Presentación y contextualización En esta unidad de aprendizaje conoceremos las herramientas y técnicas a usar para el diseño y la implementación de una base de datos.

Así como también el proceso de Normalización para la validez y consistencia al momento de crear la base de datos.

b)Competencia Conceptualiza las tres perspectivas básicas para el modelado de datos: 

Diseño conceptual.



Diseño lógico.



Diseño físico.

c) Capacidades 1. Reconoce la importancia del uso de los niveles de diseño en el modelado de datos. 2. Reconoce la importancia que tiene los sistemas de gestión de base de datos en las organizaciones. 3. Reconoce la importancia del Modelo Entidad - Relación. 4. Identifica las etapas de la Normalización.

d)Actitudes Desarrolla una actitud emprendedora mediante la toma de iniciativas, promoción de actividades y toma de decisiones en relación a la actividad asignada. Actúa con responsabilidad personal, al cumplir con los horarios establecidos y el respeto a las normas de convivencia. Cumple con la presentación de los trabajos encomendados con puntualidad. Desarrolla la creatividad, la innovación, la actitud emprendedora y el respeto a la honestidad intelectual.

e) Presentación de Ideas básicas y contenido esenciales de la Unidad: La Unidad de Aprendizaje 01: Base de Datos comprende el desarrollo de los siguientes temas: TEMA 01: Introducción a la Base de datos TEMA 02: Sistema de Gestión de base de datos TEMA 03: Modelo Entidad Relación TEMA 04: Normalización

6


TEMA 1

Reconocer la importancia del uso de los niveles de diseño en el modelado de datos.

7


Desarrollo de los Temas INTRODUCCIÓN

El objetivo principal de las bases de datos es el de unificar los datos que se manejan y los programas o aplicaciones que los manejan.

Anteriormente los programas se codificaban junto con los datos, es decir, se diseñaban para la aplicación concreta que los iba a manejar, lo que desembocaba en una dependencia de los programas respecto a los datos, ya que la estructura de los ficheros va incluida dentro del programa, y cualquier cambio en la estructura del fichero provocaba modificar y recopilar programas.

Además, cada aplicación utiliza ficheros que pueden ser comunes a otras de la misma organización, por lo que se produce una REDUNDANCIA de la información, que provoca mayor ocupación de memoria, laboriosos programas de actualización (unificar datos recogidos por las aplicaciones de los diferentes departamentos), e inconsistencia de datos (no son correctos) si los datos no fueron bien actualizados en todos los programas. Con las bases de datos, se busca independizar los datos y las aplicaciones, es decir, mantenerlos en espacios diferentes.

Los datos residen en memoria y los programas mediante un sistema gestor de bases de datos, manipulan la información. El sistema gestor de bases de datos recibe la petición por parte del programa para manipular los datos y es el encargado de recuperar la información de la base de datos y devolvérsela al programa que la solicitó.

8


Cada programa requerirá de una cierta información de la base de datos, y podrá haber otros que utilicen los mismos datos, pero realmente residirán en el mismo espacio de almacenamiento y los programas no duplicarán esos datos, si no que trabajarán directamente sobre ellos concurrentemente. Aunque la estructura de la base de datos cambiara, si los datos modificados no afectan a un programa específico, éste no tendrá por qué ser alterado.

Mediante estas técnicas de base de datos se pretende conseguir a través del Sistema Gestor de Bases de Datos (SGBD):

INDEPENDENCIA de los Datos: Cambios en la estructura de la Base de Datos no modifican las aplicaciones. .

INTEGRIDAD de los Datos: Los datos han de ser siempre correctos. Se establecen una serie de restricciones (reglas de validación) sobre los datos.

.

SEGURIDAD de los Datos: Control

de

acceso

a

los

datos

para

evitar

manipulaciones de estos no deseadas.

Definición de Bases de Datos Es una colección de datos referentes a una organización estructurada según un modelo de datos de forma que refleja las relaciones y restricciones existentes entre los objetos del mundo real, y consigue independencia, integridad y seguridad de los datos. Lo que debemos tener claro es la diferencia entre Base de Datos y SGBD. La base de datos es el almacenamiento donde residen los datos. El SGBD es el encargado de manipular la información contenida en ese almacenamiento mediante operaciones de lectura/escritura sobre la misma.

9


Además las bases de datos no sólo contendrán las tablas (ficheros) de datos, sino que también almacenará formularios (interfaces para edición de datos), consultas sobre los datos, e informes. El SGBD se encargará de manipular esos datos, controlar la integridad y seguridad de los datos, reconstruir y reestructurar la base de datos cuando sea necesario.

Modelos de Datos: Hablar del concepto lógico o físico de Base de Datos involucra un conjunto de pensamientos concretos que hacen posible la absorción temática del significado de los datos. La abstracción de los datos como una forma o un comportamiento que hace posible concretar un “algo”, se asocia con un esquema del conocimiento lógico, su

semántica, condiciones y acciones, que permiten la producción de modelos por medio de los cuales se representa la funcionalidad de un sistema.

Inicialmente el "dato" fue trabajado desde la óptica pura de su almacenamiento a través de los "Sistemas de Archivos"; donde cada uno de los archivos que se creaban solo obedecía a una necesidad de almacenamiento más que a la utilización funcional del dato. Por este motivo surgen los esquemas conceptuales que son elaborados a través del análisis de procesos de las áreas del negocio, los cuales involucran al "dato" como una consecuencia lógica funcional de ellos.

Pero para poder estandarizar este tratamiento se crea el concepto del " Modelo ", Datos

de

por medio del cual se definen un conjunto de elementos y símbolos que

permiten estandarizar y traducir dicho análisis a un lenguaje semántico y sistemático que dispone de reglas de control y evaluación del comportamiento del dato en el transcurso del tiempo, tanto en su almacenamiento como en su utilización.

10


Estos modelos de datos, hacen posible que la lógica de un negocio pueda ser estructurada de forma tangible a través de un esquema físico que representa el almacenamiento de los datos bajo las reglas del negocio y de un sistema gestor de base de datos que permitirá la persistencia de estos a través del tiempo. Los Modelos de Datos, también llamados modelos lógicos, se han clasificado en dos grandes grupos debido al tratamiento de los datos: Basados en Objetos y basados en Registros.

Estos dos grupos de modelos representan dos estados de la interpretación de los requerimientos de usuario:

Basados en Objetos Un problema de la vida real maneja concepciones abstractas o concretas, tangibles o intangibles, a las cuales se les ha dado el nombre de "objetos", calificados a partir de un valor significativo dentro de los parámetros de una forma o estilo de vida; dichos objetos se modelan a través de propuestas que fueron estructuradas para así poder estandarizar la forma de manipularlos. Dentro de estos modelos tenemos: o

Modelo Entidad Relación (MER)

o

Modelo Orientado a Objetos (MOO)

o

Modelo Semántico

o

Modelo Deductivo

Basados en Registros Otra forma de tratar lógicamente la información suministrada por un sistema es a través de los "Registros", originalmente concebidos por los sistemas de archivos (registro: conjunto de campos que almacenan información de diferentes tipos), lo cual dio pie a la estructuración de modelos lógicos tales como: o

Modelo Relacional (MR)

o

Modelo de Red

o

Modelo Jerárquico

11


Al modelar es importante conocer muy bien la semántica de estos modelos y lo que es posible lograr con ellos. En la Ingeniería de Software, el tratamiento de los datos Las Bases de Datos dentro del ciclo de vida de un proyecto informático, están ubicadas dentro del proceso del Diseño, "el CÓMO", estructurar la funcionalidad expuesta en los requerimientos. El siguiente esquema escenifica esta situación:

_________

Algunas Terminologías en las bases de datos

DATO: Es una información que refleja el valor de una característica de un objeto real, sea concreto o abstracto, o imaginario. Debe permanecer en el tiempo, debe tener un significado y debe ser manipulable mediante operadores.

Un dato es un valor que representa algo.

12


Por ejemplo:

o“maría”: es un dato que representa el nombre de una persona. o18: es un dato que puede ser la edad de una persona. o1.70: es un dato que representa la talla de una persona.

Información: Es un conjunto de datos convenientemente tratado, que puede ser utilizado en la realización de cálculos o toma de decisiones.

Ejemplo: con los datos anteriores se puede tener la siguiente observación: María tiene 18 años y mide 1.70

Campo: Es la unidad más pequeña a la cual uno puede referirse en un programa. Desde el punto de vista del programador representa una característica de un individuo u objeto. CAMPO

Registro: Colección de campos de iguales o de diferentes tipos.

REGISTROS

Archivo:

13


Colección de registros almacenados siguiendo una estructura homogénea.

Aplicación: Son aquellos programas que permiten la interacción entre el usuario y la computadora. Esta comunicación se lleva acabo cuando el usuario elige entre las diferentes opciones o realiza actividades que le ofrece el programa.

14


TEMA 2

Reconocer la importancia que tiene los sistemas de gestión de base de datos en las organizaciones.

15


Definición Los . Sistemas de Gestión de Base de Datos (SGBD) son un tipo de software muy específico, dedicado a servir de interfaz entre la base de datos, el usuario y las aplicaciones que la utilizan. Se compone de un lenguaje de definición de datos, de un lenguaje de manipulación de datos y de un lenguaje de consulta. El SGBD y DBMS, son ambos equivalentes, y acrónimos, respectivamente. respectivamente.

El DBMS: (Data Base Management System ) Es un conjunto de programas que se encargan de manejar la creación y todos los accesos a las bases de datos, está compuesto por:

DDL: Lenguaje de Definición de Datos DML: Lenguaje de Manipulación de Datos SQL: Lenguaje de Consulta.

Propósito El propósito general de los sistemas de gestión de base de datos es el de manejar de manera clara, sencilla y ordenada un conjunto de datos.

Objetivos Existen distintos objetivos que deben cumplir los SGBD:

Ventajas 1. Facilidad de manejo de grandes volúmenes de información. 2. Gran velocidad en muy poco tiempo. información. 3. Independencia del tratamiento de información.

4. Seguridad de la información (acceso a usuarios autorizados), protección de información, de modificaciones, modificaciones, inclusiones, inclusiones, consulta.

16


5. No hay duplicidad de información, comprobación de información en el momento de introducir la misma.

6. Integridad referencial el terminar los registros.

Inconvenientes 1. El costo de actualización del hardware y software son muy elevados.

2. Costo (salario) del administrador de la base de datos es costoso.

3. El mal diseño de esta puede originar problemas a futuro. 4. Un mal adiestramiento a los usuarios puede originar problemas a futuro. 5. Si no se encuentra un manual del sistema no se podrán hacer relaciones con facilidad. 6. Generan campos vacíos en exceso. 7. El mal diseño de seguridad genera problemas.

Arquitectura de un Sistema de Gestión de Base De Datos Los SGBD pueden ser estudiados desde 3 niveles distintos:

1.- Nivel Físico Es el nivel real de los datos almacenados. Es decir cómo se almacenan los datos, ya sea en registros, o como sea. Este nivel es usado por muy pocas personas que deben estar cualificadas para ello. Este nivel lleva asociada una representación de los datos, que es lo que denominamos Esquema Físico.

2.- Nivel Conceptual. Es el correspondiente a una visión de la base de datos desde el punto de visto del mundo real. Es decir tratamos con la entidad u objeto representado, sin importarnos como está representado o almacenado. Este nivel lleva asociado el Esquema Conceptual.

17


3.- Nivel Visión (Usuario) Son partes del esquema conceptual. El nivel conceptual presenta toda la base de datos, mientras que los usuarios por lo general sólo tienen acceso a pequeñas partes de ésta. El nivel visión es el encargado de dividir estas parcelas. Un ejemplo sería el caso del empleado que no tiene porqué tener acceso al sueldo de sus compañeros o de sus superiores. El esquema asociado a éste nivel es el Esquema de Visión. Los 3 niveles vistos, componen lo que conocemos como arquitectura de base de datos a 3 niveles. A menudo el nivel físico no es facilitado por muchos DBMS, lo cual no permite al usuario elegir como se almacenan sus datos y vienen con una forma estándar de almacenamiento y manipulación de los datos.

Lenguaje de definición de datos. Un esquema de BD se especifica por medio de un conjunto de definiciones que se expresan mediante un lenguaje especial llamado lenguaje de definición de datos (data).

El resultado de la compilación de sentencias de DDL es un conjunto de tablas que se almacenan en un archivo especial que llamado diccionario de datos o directorio. Un directorio de datos es un archivo que contiene metadatos, es decir, datos sobre datos. Este archivo se consulta antes de leer o modificar los datos reales en el sistema de BD. La estructura de almacenamiento y los métodos de acceso se especifican por medio de un conjunto de definiciones en un tipo especial de DDL llamado lenguaje de almacenamiento y definición de datos. El resultado de la compilación de estas definiciones es un conjunto de instrucciones que especifican los detalles de implementación de los esquemas que normalmente se esconden a los usuarios.

18


Lenguaje de manipulación de datos. Por manipulación de datos entendemos la recuperación y modificación de la información almacenada y la inserción y supresión de información. Un lenguaje de manipulación de datos (data manipulation lenguage, DML) es un lenguaje que capacita a los usuarios a acceder o manipular los datos. Una consulta es una sentencia que solicita la recuperación de información. El trozo de un DML que implica recuperación de información se llama lenguaje de consultas. Aunque es incorrecto, suelen utilizarse los términos lenguaje de consultas y lenguaje de manipulación de datos como sinónimos.

Administrador de bases de datos. Una de las razones principales para tener DBMS es tener control central de los datos y de los programas que acceden a esos datos. La persona que tiene dicho control central sobre el sistema se llama administrador de la BD (data, base, administrador, DBA).

Las funciones del DBA son:

1.- Definición de esquema. El esquema original de la BD se crea escribiendo un conjunto de definiciones que son traducidas por el compilador de DDL a un conjunto de tablas, almacenadas permanentemente en el diccionario de datos.

2.- Definición de la estructura de almacenamiento y del método de acceso. Se crean escribiendo un conjunto de definiciones traducidas por el compilador del lenguaje de almacenamiento y definición de datos.

3.- Modificación del esquema y de la organización física. Las modificaciones son poco comunes, pero se logran escribiendo un conjunto de definiciones usadas por el compilador DDL para generar modificaciones a las tablas internas apropiadas.

19


4.- Concesión de autorización para el acceso a los datos. La concesión de diferentes tipos de autorización permite al DBA regular qué partes de la BD van a poder ser accedidas por varios usuarios.

5.- Especificación de las restricciones de integridad. Las restricciones de integridad se mantienen en una estructura especial del sistema que consulta el gestor de la BD cada vez que tiene lugar una actualización.

Usuarios de bases de datos. Un objetivo principal de un DBMS es proporcionar un entorno para recuperar y almacenar información en la BD. Hay cuatro tipos de usuarios, diferenciados por la forma de

interaccionar con el sistema:

1.- Programadores de aplicaciones. Las profesionales en computación interaccionan con el sistema por medio de llamadas en DML, incorporadas en un programa escrito en un lenguaje principal (como Pascal

o

C).

Estos

programas

se

denominan

comúnmente programas de aplicación. Puesto que la sintaxis de DML es normalmente diferente de la del lenguaje principal, las llamadas en DML van normalmente precedidas de un carácter especial de forma que se pueda generar el código apropiado. Un preprocesador especial, llamado pre compilador de DML, convierte las sentencias en DML a llamadas en el lenguaje principal. El programa resultante se ejecuta entonces por el compilador del lenguaje principal, que genera el código objeto apropiado.

2.- Usuarios sofisticados. Interaccionan con el sistema sin escribir programas, en cambio escriben sus preguntas en un lenguaje de consultas. Cada consulta se somete a un procesador de consultas, cuya función es tomar una sentencia en DML y descomponerla en instrucciones que entienda el gestor de la BD.

20


3.- Usuarios especializados. Algunos usuarios sofisticados escriben aplicaciones de BD especializadas que no encajan en el marco tradicional de procesamiento de datos, como diseño asistido por computador, sistemas basados en el conocimiento, etc.

4.- Usuarios ingenuos. Los usuarios no sofisticados interactúan con el sistema invocando a uno de los programas de aplicación permanentes que se han escrito anteriormente.

Tipos de bases de datos Las bases de datos pueden clasificarse de varias maneras, de acuerdo al criterio elegido para su clasificación:

Según la variabilidad de los datos almacenados

Bases de datos estáticas Éstas son bases de datos de sólo lectura, utilizadas primordialmente posteriormente

para se

almacenar pueden

datos

utilizar

históricos

para

estudiar

que el

comportamiento de un conjunto de datos a través del tiempo, realizar proyecciones y tomar decisiones.

Bases de datos dinámicas

Éstas son bases de datos donde la información almacenada se modifica con el tiempo, permitiendo operaciones como actualización y adición de datos, además de las operaciones fundamentales de consulta. Un ejemplo de esto puede ser la base de datos utilizada en un sistema de información de una tienda de abarrotes, una farmacia, un videoclub, etc.

21


Según el contenido Bases de datos bibliográficas Solo contienen una representación de la fuente primaria, que permite localizarla. Un registro típico de una base de datos bibliográfica contiene información sobre el autor, fecha de publicación, editorial, título, edición, de una determinada publicación, etc. Puede contener un resumen o extracto de la publicación original, pero nunca el texto completo, porque si no estaríamos en presencia de una base de datos a texto completo (o de fuentes primarias). Como su nombre lo indica, el contenido son cifras o números. Por ejemplo, una colección de resultados de análisis de laboratorio, entre otras.

Bases de datos de texto completo Almacenan las fuentes primarias, como por ejemplo, todo el contenido de todas las ediciones de una colección de revistas científicas.

Directorios Un ejemplo son las guías telefónicas en formato electrónico.

Bases de datos o "bibliotecas" de información Biológica Son bases de datos que almacenan diferentes tipos de información proveniente de las ciencias de la vida o médicas. Se pueden considerar en varios subtipos:

Bases de datos de

estructura,

comprende

los

registros

de

datos

experimentales sobre estructuras 3D de biomoléculas Bases de datos clínicas Bases de datos bibliográficas (biológicas),

22


Modelos de datos. Es una colección de herramientas conceptuales que se utilizan para describir datos, relaciones entre ellos, semántica asociada a los datos y restricciones de consistencia. Los diversos modelos de datos se dividen en tres grupos:

Modelos lógicos basados en objetos Modelos lógicos basados en registros Modelos físicos de datos.

Modelos lógicos basados en objetos Los modelos lógicos basados en objetos se usan para describir datos en el nivel conceptual y de visión. Se caracterizan porque proporcionan capacidad de estructuración bastante flexible y permiten especificar restricciones de datos explícitamente. Hay muchos modelos diferentes, y aparecerán más. Algunos de los más conocidos son, el modelo entidad-relación, el orientado a objetos, el binario, el semántico de datos y el modelo funcional de datos. El modelo entidad-relación ha ganado aceptación y se utiliza ampliamente en la práctica, el modelo orientado a objetos incluye muchos conceptos del anterior, y es también aceptado rápidamente.

Modelos lógicos basados en registros. Los modelos lógicos basados en registros se utilizan para describir datos en los modelos conceptual y físico. A diferencia de los modelos lógicos basados en objetos, se usan para especificar la estructura lógica global de la BD y para proporcionar una descripción a nivel más alto de la implementación. Los modelos basados en registros se llaman así porque la BD está estructurada en registros de formato fijo de varios tipos. Cada tipo de registro define un número fijo de campos, o atributos, y cada campo normalmente es de longitud fija. La estructura más rica de estas BD a menudo lleva a registros de longitud variable en el nivel físico.

23


Los modelos basados en registros no incluyen un mecanismo para la representación directa de código de la BD, en cambio, hay lenguajes separados que se asocian con el modelo para expresar consultas y actualizaciones

Los tres modelos de

datos más aceptados son los modelos relacional, de red y jerárquico. El modelo relacional ha ganado aceptación por encima de los otros.

Modelos físicos de datos. Los modelos físicos de datos se usan para describir datos en el nivel más bajo. Hay muy pocos de modelos físicos de datos en uso, siendo los más conocidos el modelo unificador y de memoria de elementos.

24


TEMA 3

Reconocer la importancia del Modelo Entidad - Relación.

25


Definición Denominado por sus siglas como: E-R. Percibe el mundo real como una serie de objetos relacionados entre si y pretende representarlos gráficamente, mediante un determinado mecanismo de abstracción. Este mecanismo está basado en una serie de símbolos, reglas y métodos que nos permitirán representar gráficamente los datos de interés del mundo real. El modelo entidad-relación es una técnica para el modelado de datos utilizando diagramas entidad relación. No es la única técnica pero si la más utilizada. Brevemente consiste en

los siguientes pasos:

1. Se parte de una descripción textual del problema o sistema de información a Automatizar (los requisitos). 2. Se hace una lista de los sustantivos y verbos que aparecen. 3. Los sustantivos son posibles entidades o atributos. 4. Los verbos son posibles relaciones. 5. Analizando las frases se determina la cardinalidad de las relaciones y otros detalles. 6. Se elabora el diagrama (o diagramas) entidad-relación. 7. Se completa el modelo con listas de atributos y una descripción de otras restricciones que no se pueden reflejar en el diagrama.

Dado lo rudimentario de esta técnica se necesita cierto entrenamiento y experiencia para lograr buenos modelos de datos.

26


Elementos del Modelo Entidad - Relación Entidades Una entidad es cualquier "objeto" discreto sobre el que se tiene información. Se representa mediante un rectángulo o "caja" etiquetada en su interior mediante un nombre.

Ejemplos de entidades habituales en los sistemas de información son: factura, persona, Producto, empleado, etc.

Se representa gráficamente de la siguiente manera:

Cliente

Artículos

Cada ejemplar de una entidad se denomina instancia. Por ejemplo, Francisco y Luisa pueden ser dos instancias distintas de la entidad "persona". Las instancias no se representan en el diagrama. No obstante, se pueden documentar aparte porque son útiles para inicializar la base de datos resultante.

Ejemplo Práctico: Que entidades podemos observar en una clínica:

Respuesta Correcta: Paciente, Medico, Medicinas.

Atributos También llamadas Propiedades, los atributos describen información útil sobre las entidades. En particular, los atributos identificativos son aquellos que permiten diferenciar a una instancia de la entidad de otra distinta. Por ejemplo, el atributo identificativo que distingue a un empleado de otro es su numero de la Seguridad Social.

Ejemplos de atributos de la entidad "persona": Documento Nacional de Identidad (Identificativo), nombres, apellidos, dirección, código postal, etc.

27


Los atributos poseen la siguiente estructura: Nombre: Especificación física Tipo: Valor que se utiliza para escribir características relacionadas al dato (cadena, booleano, fecha, etc.)

Dominio: Conjunto de valores permitidos que va a usar un atributo. Ejemplo grafico de Atributo:

Los atributos se representan mediante elipses, y en su interior el nombre del atributo:

Ejemplo Práctico Mostrar los atributos que tengan las entidades cliente y artículos.

ARTICULOS Código Nombre Precio Stock

Tipos de Atributos: 1. Simples , aquellos que no están divididos en sub-partes. Ejem: Código, DNI

2. Compuestos , aquellos que pueden dividirse en sub-partes (es decir, en otros atributos.)

28


Ejemplo:

3. Monovalorados, cuando se tiene un único valor para una entidad concreta. Ejemplo: Número de Factura, Número de Matrícula.

4. Multivalorado, cuando se tiene un conjunto de valores para una entidad especifica. Ejemplo: Considera un conjunto de entidades alumno, con el atributo número teléfono, cualquier alumno puede tener cero, uno o más números de teléfonos a los que se le pueda ubicar. Es frecuente usar una doble elipse para indicar este tipo de atributos.

Relaciones Es una asociación entre entidades, se caracteriza por restricciones específicas que determinan las entidades que pueden o no participar en dicha relación. Una relación describe cierta interdependencia (de cualquier tipo) entre una o más entidades. Se representa mediante un rombo etiquetado en su interior empleando un

verbo. Además, dicho rombo debe unirse mediante líneas con las entidades que relaciona (es decir, los rectángulos). Una relación no tiene sentido sin las entidades que la relaciona.

29


A la asociación entre conjunto de entidades, se le conoce como Participación. Cuando una entidad participa en una relación puede adquirir un papel fuerte o débil. Una entidad débil es aquella que no puede existir sin participar en la relación, es decir, aquella que no puede ser identificada solamente por sus atributos. Una entidad fuerte es aquella que "presta" algunos de sus atributos a la entidad débil para que se pueda identificar cualquier instancia. Esto lo hace a través de la relación que las une. Las entidades débiles se representan mediante un doble rectángulo, es decir, un rectángulo con doble línea.

Cardinalidad de las relaciones Es el número de ocurrencias de una entidad asociada a una ocurrencia de otra entidad o la misma entidad a través de una relación. Las relaciones, en principio binarias, pueden involucrar a un numero distinto de instancias de cada entidad. Así, son posibles tres tipos de cardinalidades:

de uno a uno: una instancia de la entidad A se relaciona con una y solamente una de la entidad B.

de uno a muchos: cada instancia de la entidad A se relaciona con varias instancias de la entidad B.

de muchos a muchos: cualquier instancia de la entidad A se relaciona con cualquier instancia de la entidad B.

30


o

Opcionalidad: es la participación obligatoria u opcional en la entidad de la relación.

Como se lee el Grado ó Cardinalidad: .Uno a muchos: una instancia de la entidad A se relaciona con una ó más instancias de la entidad B.

.Muchos a muchos: una instancia de la entidad A se relaciona con una ó más instancias de la entidad B y una instancia de la entidad B se relaciona con uno ó más instancias de le entidad B.

.Uno a uno: una instancia de la entidad A se relaciona con uno y sólo una instancia de la entidad B.

31


Claves o Llaves Llave Primaria (PK): Un atributo (Llave simple) o conjunto de atributos (Llave compuesta) que identifican únicamente una instancia (fila o registro) de una entidad.

Llave Alterna (AK): Un atributo (Llave simple) o conjunto de atributos (Llave compuesta) que identifican únicamente una instancia (fila o registro) de una entidad, pero que NO ES ESCOGIDA como llave primaria.

Llave Foránea (FK): Una llave foránea es una llave primaria de una entidad padre (Fuerte) que es AGREGADA a la entidad hijo (Débil) a través de su relación.

Modelo Relacional

El Modelo Relacional representa los datos y las relaciones entre estos, a través de una colección de tablas, en las cuales los renglones (tuplas o registros) equivalen a los cada uno de los registros que contendrá la base de datos y las columnas corresponden a las características (atributos) de cada registro localizado en la tupla.

32


Las características más importantes de los modelos relacionales son:  Cada atributo solo puede tomar un único valor del dominio, no se admiten valores

múltiples.  No existen 2 tuplas iguales.  La información en las bases de datos son representados como datos explícitos,

no existen apuntadores o ligas entre las tablas.  El orden de las tuplas dentro de la relación y el de los atributos, dentro de cada

tupla, no es importante.

El modelo se compone de tres partes: 1. Estructura de datos: Básicamente se compone de relaciones. 2. Manipulación de datos: Un conjunto de operadores para recuperar, derivar o modificar los datos almacenados.

3. Integridad de datos: Una colección de reglas que definen la consistencia de la base de datos.

33


TEMA 4

Identificar las etapas de la normalización.

34


Definición La normalización es el proceso mediante el cual se transforman datos complejos a un conjunto de estructuras de datos más pequeñas, que además de ser más simples y más estables, son más fáciles de mantener. También se puede entender la normalización como una serie de reglas que sirven para ayudar a los diseñadores de bases de datos a desarrollar un esquema que minimice los problemas de lógica. Cada regla está basada en la que le antecede.

La normalización se adoptó porque el viejo estilo de poner todos los datos en un solo lugar, como un archivo o una tabla de la base de datos, era ineficiente y conducía a errores de lógica cuando se trataban de manipular los datos. La normalización también hace las cosas fáciles de entender. Los seres humanos tenemos la tendencia de simplificar las cosas al máximo.

Lo hacemos con casi todo, desde los animales hasta con los automóviles. Vemos una imagen de gran tamaño y la hacemos más simple agrupando cosas similares juntas. Las guías que la normalización provee crean el marco de referencia para simplificar una estructura de datos compleja.

Grados de normalización Existen básicamente tres niveles de normalización: Primera Forma Normal (1NF), Segunda Forma Normal (2NF) y Tercera Forma Normal (3NF). Cada una de estas formas tiene sus propias reglas. Cuando una base de datos se conforma a un nivel, se considera normalizada a esa forma de normalización.

35


No siempre es una buena idea tener una base de datos conformada en el nivel más alto de normalización, puede llevar a un nivel de complejidad que pudiera ser evitado si estuviera en un nivel más bajo de normalización.

En la tabla siguiente se describe

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

Regla Primera

Forma

Normal (1FN)

Segunda Forma

Descripción Incluye la eliminación de todos los grupos repetidos.

Asegura que todas las columnas que no son llave sean completamente dependientes de la llave primaria (PK).

Normal (2FN)

Tercera

Forma

Normal (3FN)

Elimina cualquier dependencia transitiva. Una dependencia transitiva es aquella en la cual las columnas que no son llave son dependientes de otras columnas que tampoco son llave.

1. Proceso de Normalización. Proceso mediante el cual una tabla va pasando de una forma normal hacia otra del siguiente nivel, con la finalidad de evitar la redundancia de los datos, protegiendo la actualización y la integridad de los datos.

2. Primera Forma Normal (1FN). Una tabla que se encuentra en 1FN se caracteriza por:  Todos los atributos son atómicos.  Hay una clave primaria.  No existen atributos nulos.  No existen grupos repetitivos.

Alumnos Código

Nombre

Fecha de Nacimiento

Teléfono

36


1935.0456

Carlos Castro

01-05-1917

543-2178

1944.9870

Pedro Prado

26-03-1924

442-2226, 442-3256

1956.0045

Juan Bautista

18-07-1935

Cuadro 2: Tabla de Alumnos En el cuadro 2, se presenta una tabla de Alumnos. La necesidad de guardar múltiples números telefónicos implica un problema de grupos repetidos, si consideramos tenerlos en una sola tabla. Asimismo, existe la posibilidad de que algún alumno no tenga teléfono, lo que da lugar a atributos nulos. Esto nos indica que el atributo teléfono no está bien ubicado en la tabla Alumnos. Es recomendable retirar este atributo y formar una nueva tabla llamada Teléfonos del Alumno, En el cuadro 3 vemos a la tabla Alumno modificada, mientras que en el cuadro 4 vemos a la nueva tabla Teléfonos del Alumno.

Teléfonos del Alumno Alumnos Código

1935.0456

1944.9870

1956.0045

Nombre Carlos Castro Pedro Prado Juan Bautista

Código

Teléfono

1935.0456

543-2178

1944.9870

442-2226

1944.9870

442-3256

Fecha de Nacimiento 01-05-1917

26-03-1924

18-07-1935

Cuadro 4: Nueva tabla de Teléfonos del Alumno.

Cuadro 3: Tabla de A l u m n o s modificada.

37


3. Segunda Forma Normal (2FN). Una tabla que se encuentra en 2FN se caracteriza por:  Se encuentra en 1FN.  Todo atributo que no forma parte de alguna clave depende de manera

completa de la clave principal. En el cuadro 5, se presenta una tabla de Cursos por Alumno. En ella se consideran los atributos código, curso y correo electrónico. La clave candidata está formada por los atributos código y curso. Observe que el atributo correo electrónico sólo depende del atributo código; pero, no depende del atributo curso. Esto implica que el atributo correo electrónico no dependa de manera completa de la clave principal o primaria. Por ello, este atributo no está bien ubicado en la tabla Cursos por Alumnos.

Código

Curso

Correo electrónico

1935.0456

Sistemas de Información Gerencial

[email protected]

1935.0456

Dirección Financiera 3

[email protected]

1935.0456

Deontología Profesional

[email protected]

1944.9870


[email protected]

1944.9870

Dirección Financiera 2

[email protected]

1956.0045


[email protected]

1956.0045

Deontología Profesional

[email protected]

Cuadro 5: Tabla Cursos por Alumno. La pregunta aquí es, ¿qué hacemos con el atributo correo electrónico? La duda pasa por saber si lo incorporamos en la tabla que naturalmente correspondería, que sería una tabla Alumno. Sin embargo, la duda pasa por si las necesidades del sistema a desarrollar consideran que debamos almacenar más de un correo electrónico por alumno.

38


Asumiendo que esto último es una condición, lo recomendable es crear una nueva tabla llamada Correo electrónico del alumno. El cuadro 6 presenta a la tabla Cursos por Alumno modificada, mientras que el cuadro 7 muestra a la nueva tabla Correo electrónico del alumno.

Cursos por Alumno Código

Curso Correo electrónico del alumno Sistemas de Información

Código

1935.0456 Gerencial

Correo electrónico

1935.0456 Dirección Financiera 3

1935.0456 [email protected]

1935.0456 Deontología Profesional


1944.9870

Gerencial

1944.9870 Dirección Financiera 2 1956.0045


Sistemas de Información


Así, se permite tener varios correos electrónicos por alumno .

Cuadro 7: Nueva tabla Correo electrónico del Alumno.

1956.0045 Deontología Profesional

Cuadro 6: Tabla Cursos por Alumno modificada.

4. Tercera Forma Normal (3FN). Una tabla que se encuentra en 3FN se caracteriza por:  Se encuentra en 2FN.  Ningún atributo no primario depende transitivamente de alguna

clave candidata.

39


Ciclo

Primer Lugar

Fecha de Nacimiento

1935.0456

01-05-1917

1957-2

1944.9870

26-03-1924

1958-1

1956.0045

18-07-1935

1958-2

1944.9870

26-03-1924

1957-1

Cuadro 8: Tabla Primer Lugar por Ciclos. En el cuadro 8, se presenta la tabla Primer Lugar por Ciclos. En ella se presentan los atributos ciclo, primer lugar y fecha de nacimiento. La clave candidata es ciclo. El atributo no primario fecha de nacimiento es dependiente transitivamente, vía el atributo no primario primer lugar. Luego, el atributo fecha de nacimiento debe ser retirado de la tabla Primer Lugar por Ciclos. La pregunta es ¿A dónde va a ir? En este caso, la fecha de nacimiento no puede tener múltiples valores por cada alumno. Por ello, es natural que sea incorporada en una tabla llamada Alumno. El cuadro 9 nos presenta a la tabla Primer Lugar por Ciclos modificada, mientras que el cuadro 10 nos presenta a la nueva tabla alumno.

Primer Lugar por Ciclos

Ciclo

1957-1

Primer Lugar 1935.0456

Alumno

Código

1935.0456

Fecha de Nacimiento 01-05-1917

1957-2

1944.9870

1944.9870

26-03-1924

1958-1

1956.0045

1956.0045

18-07-1935

1958-2

1944.9870

Cuadro 10: Nueva tabla A l u m n o s . Cuadro 9: Tabla Primer Lugar por Ciclos modificada.

40


Ejercicio de Normalización. En el cuadro 11, se presenta una tabla llamada Menú Básico Semanal por Cafetería. Menú Básico Semanal por Cafetería Cafetería

“El Ruso”

Dueño Eric Moscovita

Correo

Teléfono

Día

123-2345, [email protected]

Básico

Miércoles

Estofado con pasas

Lunes

Fondue de caviar

Martes

Arroz a lo pobre

Jueves

Lomo fino a lo pobre

Viernes

Arroz con Pollo

345-1234 999-99999,

“Cool”

999-88888,

Miguel Ángelo

[email protected] 444-4444, 444-5555

“El Pueblo”

Paro Misio

[email protected] m

“Alpha”

Humano Homero

[email protected]

“El Ruso”

Eric Moscovita

[email protected]

777-7777 123-2345, 345-1234

Cuadro 11: Tabla Menú Básico Semanal por Cafetería. Entendiendo que sólo es una muestra de los datos ya que cada cafetería presenta un menú por día, aplique la 3FN a todas las tablas que se generen. Una solución posible: Nuestra primera tabla es Menú Básico Semanal por Cafetería. Le aplicamos la 1FN. Todos los atributos son Atómicos: La columna que podría ser considerada no atómica es el Dueño. Cabe la posibilidad de convertirla en dos columnas: Nombre y Apellido. La clave primaria es: Cafetería, Día y Básico. Estas 3 columnas hacen única las filas de la tabla. Encontramos que la columna Teléfono incumple tanto por tener contenidos nulos como por contener grupos repetitivos.

41


Ahora tenemos dos tablas, entendiendo que la columna Teléfono corresponde al Dueño.

La segunda tabla, Teléfonos del Dueño, tiene como clave primaria: Nombre, Apellido y Teléfono. Esta tabla se encuentra en la 3FN. Continuamos con la primera tabla, para revisar la 2FN. Todo atributo que no forma parte de alguna clave depende de manera completa de la clave principal. Las columnas Nombre y Apellido, que no forman parte de una clave, sólo dependen de la columna Cafetería. Por ello, tenemos una tercera tabla, llamada Dueño y una cuarta tabla llamada Cafetería. Las tablas van quedando así:

Menú Básico Semanal por Cafetería Cafetería

Correo

Día

Básico

Teléfonos del Dueño Nombre

Teléfono

Cafetería

Dueño Nombre

Apellido

Apellido

Cafetería

Nombre

Apellido

Para la cuarta tabla, la clave primaria es Cafetería, Nombre y Apellido. Falta resolver cuál será la clave primaria para la tercera tabla. Dado que los nombres pueden repetirse o ser muy largos, es conveniente buscar otra clave. Una clave candidata es el DNI. Sin embargo, existe la posibilidad de que algún dueño sea extranjero y no tenga DNI, sino, carné de extranjería.

42


Entonces, se puede definir una clave primaria mediante dos nuevas columnas: Código de Documento de Identidad y Número de Documento de Identidad. Esto nos conduce a crear una quinta tabla, llamada Códigos de Documento de Identidad.

Las tablas quedan así:

Teléfonos del Dueño


Correo

Día

COD DOC

Básico

Dueño COD NUM Nombre DOC DOC

NUM DOC

Teléfono

Códigos de Documento de Identidad Apellido

COD DOC

Descripción

1

DNI

2

Carné de extranjería

9

Otros

Esta quinta tabla tiene como clave primaria a la columna COD DOC. A excepción de la primera tabla, todas se encuentran en la 3FN. La columna Correo depende del Dueño y no de toda la clave primaria. Por ello, esta columna es trasladada a la tabla Dueño. Con ello, la situación final es la siguiente:

43



Día

Teléfonos del Dueño COD DOC

Básico

NUM DOC

Cafetería Cafetería

Dueño

COD DOC

COD NUM Nombre Apellido Correo DOC DOC

NUM DOC

Códigos de Documento de Identidad COD DOC

Teléfono

Cafetería Cafetería

Descripción

COD DOC

NUM DOC

IMPORTANTE TOMAR EN CUENTA: si se considera que los dueños pueden tener más de un correo, entonces es pertinente definir una sexta tabla, llamada Correos del Dueño. Menú Básico Semanal por Cafetería Cafetería

Día

Teléfonos del Dueño

Básico

COD DOC

NUM DOC

Teléfono

Dueño COD NUM Nombre DOC DOC

Apellido

Correo Correos del Dueño

Códigos de Documento de Identidad COD DOC

COD DOC

NUM DOC

Correo

Descripción

Esta última tabla tiene como clave primaria a COD DOC, NUM DOC y Correo. Con esto se concluye esta solución propuesta. Como podrán ver, los supuestos que uno vaya planteando van configurando y refinando el diseño de las tablas.

44


Lecturas Recomendadas

 INTRODUCCIÓN A LA BASE DE DATOS

http://www.desarrolloweb.com/articulos/introduccion-base-datos.html

 MODELO ENTIDAD-RELACIÓN

http://www.cs.us.es/cursos/bd-2002/HTML/modeloER.htm

 NORMALIZACIÓN

http://es.wikipedia.org/wiki/Normalizaci%C3%B3n_de_bases_de_datos

Actividades y Ejercicios 1.

Ingresa al link “Base de Datos” lee atentamente las indicaciones, desarróllalo y envíalo por el mismo medio. ¿Cuál es la diferencia entre Lenguaje de Definición de Datos y Manipulación de datos? Enumere 5 entidades para un sistema académico. Enumere 5 atributos para la ENTIDAD : ALUMNO Enumere 3 atributos para la ENTIDAD : MATRICULA Enumere los tipos de usuarios para una base de datos.

45


Autoevaluaciones

1- Es un valor numérico o alfanumérico que representa algo. a. Información b. Objeto c. Dato d. Campo e. Registro 2.- Es como una característica o propiedad de un objeto. Información Objeto Dato Campo Registro

a. b. c. d. e.

3.- Es un lenguaje que se encarga de manipular los datos de la base de datos. a. b. c. d. e.

DDL DLL DML DLM DCL

4.- ¿Qué tipo de usuario de base de datos es la persona que desarrolla los sistemas que interactúan con la Base de Datos? a. b. c. d. e.

Usuario final Programadores de Aplicación DBA Operador DbOwner

5.- ¿Qué tipo de usuario de base de datos son los que no pueden hacer nada y solo utilizan los programas que ya están hechos? a. Ingenuos b. Sofisticados c. Programadores d. Especializados e. Novatos 6.- Son las propiedades que tiene una entidad. a. Atributo b. Property c. Registro d. Relación e. DML

46


7.- Unión que existen entre entidades a. b. c. d. e.

Relación Atributo Registro Campo Interconexión

8.- Cuando una instancia de una Entidad se relación con muchas Instancias de otra Entidad. ¿Cómo se llama esta relación? a. b. c. d. e.

Uno a Uno Uno a Muchos Muchos a Muchos Uno a Todos Uno a Cero.

9.- ¿Cuántos niveles de normalización básicos existen? a. b. c. d. e.

1 2 3 4 5

10.- Asegurar que todas las columnas que no son llaves, sean completamente dependientes de la llave primaria: ¿es…? a. b. c. d. e.

1 forma normal 2 forma normal 3 forma normal 4 forma normal 5 forma normal

47


Resumen

Una base de datos es una colección de datos referentes a una organización estructurada según un modelo de datos de forma que refleja las relaciones y restricciones existentes entre los objetos del mundo real, y consigue independencia, integridad y seguridad de los datos. Utilizamos DBMS para permitir a los usuarios acceder y manipular la base de datos. Asimismo, para proveer a los administradores de bases de datos (DBA) herramientas que le permitan realizar tareas de mantenimiento y administración de los datos. Existen los siguientes modelos de datos: Entidad – Relación, Jerárquico, de red, relacional, relacional extendido y orientado a objetos.

Los Sistemas Gestores de Bases de Datos (SGBD)

son un tipo de software muy

específico, dedicado a servir de interfaz entre las bases de datos y las aplicaciones que la utilizan. Se compone de un lenguaje de definición de datos, de un lenguaje de manipulación de datos y de un lenguaje de consulta. El modelo Entidad – relación denominada por sus siglas como: E-R. Percibe el mundo real como una serie de objetos relacionados entre si y pretende representarlos gráficamente, mediante un determinado mecanismo de abstracción. Este mecanismo esta basado en una serie de símbolos, reglas y métodos que nos permitirán representar gráficamente los datos de interés del mundo real. El modelo entidad-relación es una técnica para el modelado de datos utilizando diagramas entidad relación. No es la única técnica pero si la más utilizada.

La normalización es el proceso mediante el cual se transforman datos complejos a un conjunto de estructuras de datos más pequeñas, que además de ser más simples y más estables, son más fáciles de mantener. También se puede entender la normalización como una serie de reglas que sirven para ayudar a los diseñadores de bases de datos a desarrollar un esquema que minimice los problemas de lógica. Cada regla está basada en la que le antecede. La normalización se adoptó porque el viejo estilo de poner todos los datos en un solo lugar, como un archivo o una tabla de base de datos, era ineficiente y conducía a errores de lógica cuando se trataban de manipular los datos.

48


49


Introducción

a) Presentación y contextualización En esta unidad se presenta un

tutorial de uso de la herramienta ERwin. Una

herramienta muy útil para el diseño y modelo de una base de datos. Abarcando la creación de entidades, atributos, llaves y relaciones.

b) Competencia Determina la importancia del uso de herramientas para la implementación de base de datos.

c) Capacidades 1. Identifica y desarrolla las herramientas básicas del software ERWIN. 2. Desarrolla las llaves primarias y alternas. 3. Analiza y crea las relaciones entre entidades 4. Desarrolla el modelo físico de datos.

d) Actitudes Desarrolla una actitud emprendedora mediante la toma de iniciativas, promoción de actividades y toma de decisiones en relación a la actividad asignada. Actúa con responsabilidad personal, al cumplir con los horarios establecidos y el respeto a las normas de convivencia. Cumple con la presentación de los trabajos encomendados de manera individual y en equipo, respetando la iniciativa y aportes de los integrantes. Desarrolla la creatividad, la innovación, la actitud emprendedora y el respeto a la honestidad intelectual.

e) Presentación de Ideas básicas y contenido esenciales de la Unidad: La Unidad de Aprendizaje 02: Herramientas de base de Datos: ERWIN comprende el desarrollo de los siguientes temas: TEMA 01: Introducción al ERwin TEMA 02: Creación de Llaves TEMA 03: Creación de Relaciones TEMA 04: Modelo físico de Datos

50


TEMA 1

Identificar y desarrollar las herramientas básicas del software ERWIN.

51


Desarrollo de los Temas Rol de ERwin en el Modelamiento de Datos

ERwin es una herramienta de base de datos que le ayuda a diseñar, generar y mantener aplicaciones de base de datos de calidad y alto rendimiento. Desde un modelo lógico de sus requerimientos de información y reglas del negocio que definen su base de datos, hasta un modelo físico, optimizado por las características específicas de su base de datos de destino, ERwin le permite visualizar la estructura adecuada, los elementos clave y un diseño optimizado de su base de datos.

ERwin genera tablas automáticamente y miles de líneas de stored procedures y código trigger para las principales bases de datos. Su tecnología "complete-compare" permite el desarrollo interactivo, de manera que su modelo está siempre sincronizado con su base de datos. A través de la integración con los ambientes de desarrollo líderes en la industria, ERwin también acelera la creación de aplicaciones data-centric.

Beneficios de Erwin poder reusarlo, la integración de los datos del proyecto al proporcionar el bosquejo que las IT necesitan para entender, analizar y comunicar la estructura de la base de datos. Mejora la productividad entre los desarrolladores cuando los diseños de la base de datos son divididos, compartidos, y reutilizados. El ambiente gráfico facilita la visualización de la estructura completa, los elementos claves y el diseño optimizado de la base de datos. transaccionales de alto rendimiento y para data ware house. Mantiene los recursos y mejora la precisión al sincronizar el modelo y la base de datos.

52


Herramientas de Navegación de ERwin

53


ATENCION: para el presente manual se esta usando el ERwin 3.52 se debe entrar al ERwin y hacer click en el menú new – file (pide escoger un modelo, se escoge modelo físico/lógico y clic en ok).

El Editor de Diagrama Para crear un Modelo en ERwin utilice el Editor de Diagrama. Para ello realizar los siguientes pasos: 1. Desde el menú Edit, elegir Diagram...

2. Ingrese Nombre y Autor. 3. Seleccione el Servidor de Base de Datos (Target Server). 4. Clic en OK para retornar al modelo.

54


Ventana Principal de ERwin

Creando Entidades y Atributos Tipos de Entidades en ERwin En ERwin el modelo lógico puede contener dos tipos de entidades: independiente y dependiente.

Una entidad independiente es una entidad que puede identificarse sin determinar su relación con otra entidad. Cada entidad tiene llave propia, se representa como una caja con rincones cuadrados. Una entidad dependiente es una entidad que para identificarse requiere de su relación a otra entidad o entidades. Se representa como una caja con rincones redondeados.

Entidad Independiente Entidad Dependiente

Asegurarse que este seleccionado Logical

55


Construyendo y Nombrando Entidades Para crear una entidad realizar lo siguiente:

Nota: ERwin establece un nombre por defecto para la Entidad así como para cualquier objeto construido.

El Editor de Entidades Utilice el editor de entidades para ingresar/editar, definiciones de entidades y sus notas, para explorar definiciones, cambiar el nombre a la entidad o para asignar propiedades definidas por el usuario UPD. Para ello realizar lo siguiente:

56


Finalmente la entidad quedara definida tal como se muestra en la figura:

57


El Editor en Pantalla El editor en pantalla permite crear en forma rápida el nombre de la entidad, atributos, y Llaves. Para ello realizar lo siguiente:

Nota: 1. Si la llave primaria es compuesta use la tecla ENTER para escribir atributos adicionales en esta área.

2. Utilizar la tecla ENTER para incluir mas atributos

El Editor de Atributos El editor es utilizado construir y editar llaves primarias y atributos no-llave, explorar entidades, definir atributos, asignar dominios y crear llaves alternas y entradas inversas.

Para ingresar al editor de atributos realizar lo siguiente: 1. Clic derecho en una entidad. 2. Elija la opción Attribute Editor. 3. Finalmente ERwin le mostrara la siguiente ventana.

58


1. Seleccione el CheckBox

2. Seleccione el CheckBox

“Primary Key” en el tab

“Logical Only” para indicar

General para designar que un

que un atributo seleccionado

atributo es parte de la llave

no aparezca como una

primaria

columna en el modelo físico.

3. Seleccione el CheckBox “Required ” para indicar que

un atributo debe tener un valor.

Para el caso en estudio las entidades a crear son las siguientes:

PRODUCTO, CLIENTE, PERSONAL, USUARIO, PROFORMA, FACTURA, NOTA DE PEDIDO, BOLETA. El modelo quedará tal como se muestra en la siguiente figura:

59


60


TEMA 2

Desarrollar las llaves primarias y alternas.

61


Creando Llaves Primaria (PK) Por definición un Llave Primaria es aquella que identifica de manera única una fila de la entidad. Esta debe cumplir ciertos

re uisitos: 1. No cambia con el tiempo 2. No acepta valores NULL 3. No acepta valores repetidos En ERwin para crear una Llave Primaria realizar lo siguiente: 1.

Clic derecho sobre la entidad

2.

Seleccione Key Group

62


3.

4.

Posteriormente ERwin le mostrará la siguiente ventana:

En la zona de Available Attributes seleccione el atributo CODIGO DE PERSONAL.

5.

Posteriormente haga clic en el botón

para elegir el atributo como PK.

6. En el cuadro combinado que tiene como titulo Entity elija otra entidad y seleccione el atributo que usted ha elegido como Llave Primaria.

7.

Repita el paso 5

8.

Finalmente Clic en OK

Otra forma de crear Llaves Primarias es haciendo

uso

de

la

herramienta

de

manipulación de atributos de la barra de trabajo de ERwin de la siguiente manera:

63


1. Clic en la caja de herramienta que tiene como titulo manipulación de atributos. 2. Haga clic en el atributo CODIGO DE PERSONAL, arrástrelo y ubíquelo por encima de la línea y suelte. 3. Automáticamente ERwin creara la Llave Primaria. 4. Realizar los mismos pasos para las demás entidades. Para el caso en estudio las lleves primaria del modelo serán las que se muestran a continuación:

ENTIDADES DEL MODELO CON SUS LLAVES PRIMARIA (PK)

64


Creando Llaves Alternas (AK) Una llave alterna (AK) es un atributo o grupos de atributos que no fueron elegidos como Llaves Primarias. En ERwin para crear una Llave Alterna realizar lo

siguiente: 1. Clic derecho sobre la entidad 2. Seleccione Key Group... 3. ERwin le mostrará la siguiente ventana

1. Haga clic en el botón NEW 2. ERwin le mostrara la siguiente ventana

1. Seleccione Key Group y escriba AK 2. Un nombre por defecto es creado pero puede ser modificado. Finalmente clic en OK

65


4. Luego seleccione los atributos que van a ser incluidos dentro de la llave. 5. Clic en la flecha derecha para incluir atributos. 6. Finalmente clic en OK.

Creando Llaves Inversas (IE) Para crear Llaves Inversa o Entrada Inversa repita los mismos pasos que siguió para crear una Llave Alterna. En la ventana

N ew K e y G r o u p

elija el

botón que tiene como título Inversion Entr y (no-uniqu e).

Tal como se muestra en la figura:

66


Finalmente Seleccione los atributos que van a ser incluidos dentro de la llave y haga clic en la flecha derecha para incluirlos. El modelo quedara tal como se muestra en la figura:

El Modelo para el caso en estudio la entidad personal quedara tal como se muestra en la figura:

Entidad Personal con una Llave Alterna (AK1.1) y con una Entrada Inversa (IE1.1) definidas

67


TEMA 3

Analizar y crear las relaciones entre entidades.

68


Creando Relaciones ERwin define tres tipos de relaciones:

1. Relación Identificada. 2. Relación no Identificada. 3. Relación Muchos a Muchos.

Para todos los casos la construcción de las relaciones es tal como se detalla a Continuación:

1. Seleccione el tipo de relación de la caja de herramienta. 2. Seleccione la entidad Padre. 3. Seleccione la entidad hijo. 4. Seleccione la flecha de la caja de herramienta para desactivar la relación. Para el ejemplo las relaciones quedaran establecidas de acuerdo a la siguiente figura:

69


Nota:

Las relaciones están sin editarse dentro del modelo, a continuación

pasaremos a hablar sobre la edición de la relaciones. Para crear la relación se arrastrará un atributo primario de una entidad hacia otra. En la otra entidad se creara automáticamente el campo arrastrado con unas siglas (FK) el cual significa que es una clave foránea. f oránea.

Editando la Relaciones Para editar una relación realizar lo siguiente:

1. 2.

Haga doble clic sobre la relación

ERwin le mostrara la siguiente ventana:

70


Para el presente modelo edite las siguientes relaciones de acuerdo a la tabla que se muestra a continuación:

Finalmente el modelo se visualizara tal como se muestra en la figura:

71


Creando Frase-Verbo a las Relaciones Una frase verbo permite describir la relación entre dos entidades, para ello realizar lo siguiente:

1. Doble clic sobre la relación 2. En el TAB General ingrese a la zona que tiene como titulo Parent-to-Child y escriba el verbo para la relación

3. Finalmente clic en OK

Si aplicamos esta opción a todas nuestra relaciones dentro del modelo estas se visualizaran tal como se muestra en la figura:

Nota: El modelo lógico debe tener todas sus entidades totalmente relacionadas, no puede existir ninguna entidad acéfala ya que dejaría de ser un modelo EntidadRelación.

72


TEMA 4

Desarrollar el Modelo Físico de Datos.

73


Representación Física del Modelo de Datos ERwin mantiene las representaciones Lógica y Física del modelo de datos. Soporta características físicas del servidor objetivo (Target Server). Sincroniza el modelo físico de datos con el catálogo del servidor objetivo.

Vista en el Modelo Lógico

Vista en el Modelo Físico

Objetos Logical Only (Solo Lógicos) Entre estos objetos tenemos: 1. Las relaciones Muchos a Muchos solamente se muestran en el modelo lógico.

2. Los símbolos de Generalización Jerárquica cambian en el modelo Físico.

3. Entidades, atributos individuales o relaciones podrían ser diseñadas como solo lógicos.

74


Relaciones Muchos a Muchos Vista en el Modelo Lógico Vista en el Modelo Físico

Modelo Lógico

Modelo Físico

Modelo Lógico

75


Modelo Físico

Asignando Propiedades a Columnas Para asignar propiedades a las columnas realizar lo siguiente:

1. Clic derecho sobre la tabla 2. Elija Column Editor …

76


En esta ventana especifique la información del cliente y del servidor incluyendo tipo de dato y regla de validación. En el TAB General seleccione:

Un dominio Configure la Llave Primaria Configure Physical Only

Asignando Tipo de Datos Para asignar tipo de dato a las columnas realizar lo siguiente:

1. Clic derecho sobre la tabla. 2. Elija Column Editor… 3. Elija el TAB que tiene como nombre el servidor de base de datos elegido.

Elija el TAB DBMS y seleccione: Tipo de dato Longitud si es necesario Opción NULL

77


Creando Reglas de Validación (Validation Rule) Es una expresión que establece un rango de valores aceptables que pueden ser almacenadas en una columna. Pueden ser ingresadas y mantenidas desde ERwin y permiten que el servidor de bases de datos valide la información a ser almacenada. Permite que la aplicación cliente valide la información antes de hacer una llamada a la base de datos.

Para crear una regla de validación en ERwin realizar los siguientes pasos:

1. Cambiar al modelo Físico. 2. Clic derecho en la tabla y elija la opción Column Editor… 3. ERwin le mostrará la siguiente ventana:

1. Clic al costado del ComboB ox “VALID” 2. ERwin le mostrara la siguiente ventana

78


79


Posteriormente ERwin le mostrara nuevamente la ventana anterior con la regla de validación creada:

Clic en OK nuevamente para regresar al editor de columnas donde la regla será ligada a la columna tal como se muestra en la figura.

80


Dominio Conjunto

de

propiedades

de

atributos/columnas

definidas por el usuario. Utilizar los dominios para:  Ahorrar tiempo. 

Evitar errores.



Estandarizar el Modelo.

Todo lo que puede especificar para una columna con aquellas especificadas para el dominio lógico. Estas propiedades están relacionadas con aquellas especificadas para el dominio lógico. Crear dominios para la información de uso común.

Asignando Tipo de Datos a un Dominio Para asignar un tipo de datos a un dominio realizar lo siguiente:

1. Pasar al modelo Físico. 2. En el examinador de dominios haga doble clic sobre uno de ellos Dominios creados en el modelo Lógico.

81



 TUTORIAL DE ERWIN http://es.scribd.com/doc/3075477/Manual-de-Usuario-de-Erwin

 REFINANDO EL MODELO DE DATOS http://es.scribd.com/doc/52157227/9/Refinando-el-Modelo-de-Datos

Actividades y Ejercicios Ingresa al link

“Erwin”

lee atentamente las indicaciones,

desarróllalo y envíalo por el mismo medio. 1. ¿Qué es el modelo Entidad – Relación? 2. Enumere 3 ejemplos de atributos de una entidad ALUMNO: 3. Enumere 3 beneficios de usar ERwin:  Las siguientes preguntas se desarrollaran en el programa Erwin ,

se tomará un pantallazo y se pondrá en el documento de Word y esta se enviará a través de “Desarrollo en Erwin” 1. Crear una entidad Alumno, Curso y Notas (con sus respectivos atributos), luego relacionarlos entre si. 2. Poner en el modelo Físico los tipos de datos a los atributos del modelo creado en la pregunta anterior.

82


Autoevaluaciones 1.- ¿Qué es el ERwin? a. Lenguaje de programación. b. Software para diseñar BD. c. DBMS. d. Lenguaje de juegos. e. Software libre.

2.- ¿Tipos de entidades que maneja el ERwin? a. Lógica/Física. b. Independiente/Dependiente. c. Alta/Baja. d. Baja e. Dependiente 3.- Características de una llave primaria a. No acepta valores nulos. b. Acepta valores repetidos. c. Acepta valores nulos. d. Valores superficiales. e. Acepta codificaciones alternas. 4.- ¿Cuántos tipos de llaves maneja el ERwin? a. 1 b. 2 c. 3 d. 4 e. 5 5.- ¿Las llaves que no fueron seleccionadas como primarias se denominan? a. Secundarias b. Alternas c. Atributos d. Primarias e. Opcionales

83


6.- ¿Cuántos tipos de relaciones existen según Erwin? a. 1 b. 2 c. 3 d. 4 e. 5 7.- Una frase verbo permite describir la relación entre: a. Dos entidades. b. Una clave alterna. c. Una clave inversa. d. Llave primaria. e. Llave primary key. 8.- Conjunto de propiedades de atributos/columnas definidas por el usuario: a. Almacenamiento: b. Dominio c. Modelo Físico d. Datos e. Llave primary key 9.- Los tipos de datos se asignan en el… a. b. c. d. e.

Modelo Físico Modelo Lógico Área de Negocio Atributo La entidad

10.- Son condiciones para que se puedan almacenar valores en un campo de la entidad: a. Validation ruler b. Normalizacion c. Primary key d. Software para diseñar e. Logica

84


Resumen

ERwin es una herramienta de base de datos que le ayuda a diseñar, generar y mantener aplicaciones de base de datos de calidad y alto rendimiento. Desde un modelo lógico de sus requerimientos de información y reglas del negocio que definen su base de datos, hasta un modelo físico, optimizado por las características específicas de su base de datos de destino. ERwin le permite visualizar la estructura adecuada, los elementos clave y un diseño optimizado de su base de datos. Entre los beneficios de usar ERwin tenemos: Asegura consistencia, poder reusarlo, la integración de los datos del proyecto al proporcionar el bosquejo que las IT necesitan para entender, analizar y comunicar la estructura de la base de datos. Mejora la productividad entre los desarrolladores cuando los diseños de la base de datos son divididos, compartidos, y reutilizados. elementos claves y el diseño optimizado de la base de datos. La creación de llaves permite que una entidad tenga un campo clave, es decir un campo que identifique a toda la entidad. Es como si hablaremos del DNI de una persona es un dato que identifica a una persona y con ese dato podemos obtener mas datos como el nombre, dirección, teléfono, etc. Entonces se diría que el DNI es la llave principal de una persona. El ERwin permite crear estas llaves en el modelado de las entidades Las relaciones entre entidades es muy importante por que estas nos mostrarán el ámbito general de las entidades y como interactúan entre ellas. El ERwin permite crear estas relaciones mostrando una flecha gráfica que indica que entidad se relaciona con otra, de esa forma se tiene una mejor visión del modelo de datos ya que se estaría observando las interacciones entre las entidades.

ERwin mantiene las representaciones Lógica y Física del modelo de datos. Soporta características físicas del servidor objetivo (Target Server). Sincroniza el modelo físico de datos con el catálogo del servidor objetivo. Generalmente el modelo físico es el que nos servirá para asignar los tipos de datos a cada atributo de la entidad.

85


86


Introducción

a) Presentación y contextualización El contenido de esta unidad es el manejo básico de las sentencias SQL. Estas sentencias permitirán la creación, manipulación y control de las bases de datos. Así como también el uso de funciones, índices y vistas.

b) Competencia Identificar las sentencias principales de SQL para el manejo de la base de datos.

c) Capacidades 1. Analiza y comprende comandos básicos de SQL. 2. Desarrolla comandos SQL para la manipulación de datos. 3. Desarrolla comandos SQL usando funciones. 4. Define índices y vistas.

d) Actitudes Desarrolla una actitud emprendedora mediante la toma de iniciativas, promoción de actividades y toma de decisiones en relación a la actividad asignada. Actúa con responsabilidad personal, al cumplir con los horarios establecidos y el respeto a las normas de convivencia. Cumple con la presentación de los trabajos encomendados con puntualidad. Desarrolla la creatividad, la innovación, la actitud emprendedora y el respeto a la honestidad intelectual.

e) Presentación de Ideas básica y contenida esencial de la Unidad: La Unidad de Aprendizaje 03: Manejo del SQL Básico comprende el desarrollo de los siguientes temas: TEMA 01: SQL-Básico TEMA 02: Lenguaje de Manipulación de Datos TEMA 03: Funciones en SQL TEMA 04: Índices y Vistas

87


TEMA 1

Analizar y comprender comandos básicos de SQL.

88


Desarrollo de los Temas 1.1.- Lenguaje SQL

El SQL (Structured query language), lenguaje de consulta estructurado, es un lenguaje surgido de un proyecto de investigación de IBM para el acceso a bases de datos relacionales. Actualmente se ha convertido en un estándar de lenguaje de bases de datos, y la mayoría de los sistemas de bases de datos lo soportan, desde sistemas para ordenadores personales, hasta grandes ordenadores.

Por supuesto, a partir del estándar cada sistema ha desarrollado su propio SQL que puede variar de un sistema a otro, pero con cambios que no suponen ninguna complicación para alguien que conozca un SQL concreto.

Como su nombre indica, el SQL nos permite realizar consultas a la base de datos. Pero el nombre se queda corto ya que SQL además realiza funciones de definición, control y gestión de la base de datos.

Las sentencias SQL se clasifican según su finalidad dando origen a tres ‘lenguajes’ o mejor dicho sublenguajes:

DML (Data Manipulation Language), lenguaje de manipulación de datos, nos permite recuperar los datos almacenados en la base de datos y también incluye órdenes para permitir al usuario actualizar la base de datos añadiendo nuevos datos, suprimiendo datos antiguos o modificando datos previamente almacenados.

89


DDL (Data Description Language), lenguaje

de definición de datos,

incluye órdenes para definir, modificar o borrar las tablas en las que se almacenan los datos y de las relaciones entre estas. (Es el que más varia de un sistema a otro).

DCL (Data Control Language), lenguaje

de control de datos, contiene

elementos útiles para trabajar en un entorno multiusuario, en el que es importante la protección de los datos, la seguridad de las tablas y el establecimiento de restricciones en el acceso, así como elementos para coordinar la compartición de datos por parte de usuarios concurrentes, asegurando que no interfieren unos con otros.

1.2.- Terminología de BD Es bueno saber algunos términos en bases de datos como lo que es una tabla, registro o campo, aquí algunos conceptos:

CONCEPTO

DESCRIPCIÓN

Tabla

Es la estructura básica de almacenamiento en una BD relacional. Consiste de una o más columnas y cero o más filas.

Fila(Registro)

Es el equivalente a un registro en un sistema de archivos. Las filas se componen de columnas.

Columna(Campo)

Es un elemento de una fila que contiene un dato. Equivale a un campo en un registro.

Llave primaria (PK)

Es la columna o conjunto de ellas que identifica de forma única a una fila dentro de la tabla.

Llave foránea (FK)

Es la columna o conjunto de columnas que sirve de relación con la llave primaria de otra tabla.

Vistas

Estructuras dinámicas "virtuales" que se comportan en

90


forma similar a una tabla (para efectos de consulta) y que se crean con un select.

Transacción

Conjunto de instrucciones que se ejecutan todas o ninguna.

La

transaccionalidad

permite

mantener

consistente la información en una BD.

1.3.- Lenguaje de Definición de Datos.- (DDL) El lenguaje de definición de datos permite:  Definir y crear una nueva tabla.  Suprimir una tabla que ya no se necesita.  Cambiar la definición de una tabla existente.  Definir una tabla virtual (o vista) de datos.  La concesión de privilegios sobre un objeto de la base de

datos.  La revocación de privilegios sobre un objeto de la base de

datos.  Construir un índice para hacer más rápido el acceso a la tabla.  Aquí unos ejemplos de los comandos mas usados (Se está tomando como referencia

el gestor de Base de Datos SQL Server).

1.- Comando USE.- Use sirve para activar alguna base de datos por ejemplo si queremos trabajar con la base de datos NORTHWIND debemos poner en el analizador de consultas USE NORTHWIND (Por defecto cuando se abre el analizador de consultas se está en la base de datos MASTER).

2.- Objeto SYSDATABASES.- Es una tabla del sistema que se encuentra en la base de datos MASTER en esa tabla se almacena todos los objetos creados en el SQL Server. En el campo name se guarda el nombre de las bases de datos creadas.

SELECT * FROM SYSDATABASES

91


3.- Objeto SYSOBJECTS .- Es una tabla del sistema que se almacena en cada base de datos y tiene guarda el nombre, fecha de creación, tipo, etc. de todos los objetos creados en esa base de datos. El campo name hace referencia al nombre del objeto y el campo type hace referencia a que clase de objeto es por ejemplo si es “U” es una tabla hecha por el usuario, si es “P” es un procedimiento almacenado. Aquí los posibles tipos :C = Restricción CHECK, D = Valor predeterminado o restricción DEFAULT,F = Restricción FOREIGN KEY,FN = Función escalar,IF = Función de tabla en línea,K = Restricción PRIMARY KEY o UNIQUE,L = Restricción,P = Procedimiento almacenado,R = Regla,RF = Procedimiento almacenado de filtro de duplicación S = Tabla del sistema ,TF = Función de tabla,TR = Desencadenador,U = Tabla de usuario,V = Vista,X = Procedimiento almacenado extendido.

4.- Crear una Base de Datos.- CREATE DATABASE Nombre Ejemplo: Crear una base de Datos llamada BDIDAT: CREATE DATABASE BDIDAT

5.-Eliminar una Base de Datos.- DROP DATABASE Nombre Ejemplo: Borrar la base de datos llamada BDIDAT DROP DATABASE BDIDAT.

6.- Crear una tabla.- CREATE TABLE Nombre (Campo tipo, Campo2 tipo....) Ejemplo: Crear la tabla AREAS áreas (código, nombre, departamento). CREATE TABLE áreas (código varchar 3), nombre

(varchar

55),

departamento

(varchar3). Hay que tener en cuenta que debemos estar en la base de datos donde queremos crear las tablas antes de hacer esto.

92


Tipos de Datos en SQL Server bigint .-Datos enteros (números enteros grandes) int .- Datos enteros (números enteros) smallint .- Datos enteros pequeños tinyint.- Datos enteros comprendidos 0 y 255. bit .- Datos enteros con valor 1 ó 0. decimal .- Datos de precisión y escala numérica(Enteros y decimales). numeric .- Funcionalmente equivalente a decimal. money .- Valores de moneda comprendidos con una precisión de una diezmilésima de la unidad monetaria.

smallmoney .-

Valores de moneda (más pequeño) con una precisión de una

diezmilésima de la unidad monetaria.

float .- Un valor de coma flotante de precisión doble. real .- Un valor de coma flotante de precisión simple. datetime .- Datos de fecha y hora comprendidos entre el 1 de enero de 1753 y el 31 de diciembre de 9999, con una precisión de 3,33 milisegundos.

smalldatatime.- Datos de fecha y hora comprendidos entre el 1 de enero de 1900 y el 6 de junio de 2079, con una precisión de un minuto.

char.- Datos de caracteres de longitud fija con una longitud máxima de 8.000 caracteres. varchar.- Datos de longitud variable con un máximo de 8.000 caracteres. text.- Datos de longitud variable con una longitud máxima de 231 - 1 (2.147.483.647) caracteres.

image.- Datos binarios de longitud variable con una longitud máxima de 231 - 1 (2.147.483.647) bytes.

Otros tipos de datos cursor.- Una referencia a un cursor. sql_variant.- Un tipo de datos que almacena valores de varios tipos de datos aceptados en SQL Server, excepto text, ntext, timestamp y sql_variant.

table.- Un tipo de datos especial que se utiliza para almacenar un conjunto de resultados para un proceso posterior. timestamp.- Un número único para toda la base de datos que se actualiza cada vez que se actualiza una fila.

93


1.4.- Lenguaje de Control de Datos.- (DCL)

Contiene elementos útiles para trabajar en un entorno multiusuario, en el que es importante la protección de los datos, la seguridad de las tablas y el establecimiento de restricciones en el acceso, así como elementos para coordinar la compartición de datos por parte de usuarios concurrentes, asegurando que no interfieren unos con otros.

Restricciones (CONSTRAINT) Son reglas de validación a algunos campos se puede declarar al momento de crear la tabla o después modificando la tabla.

Comandos más comunes: NOT NULL. La columna no permitirá valores nulos. CONSTRAINT. Permite asociar un nombre a una restricción. DEFAULT. La columna tendrá un valor por defecto. PRIMARY KEY. Permite indicar que esta columna forma parte de la clave primaria. FOREIGN KEY Indica que campo/s, es/son clave externa y hace referencia a la clave primaria de otra tabla.

REFERENCES. Es la manera de indicar que este campo, es clave externa y hace referencia a la clave primaria de otra tabla. Esta foreign key es sólo de una columna.

UNIQUE. Obliga a que los valores de una columna tomen valores únicos (no puede haber dos filas con igual valor). Se implementa creando un índice para dicha(s) columna(s).

CHECK (condición). Permite indicar que condición debe de cumplir esa columna. Ejemplo: Crear una tabla carrera que el campo codcar tenga la restricción de que solo acepte letras

1.- Esta es una forma se pone al constraint al momento de crear la tabla y al costado del campo

94


create table carrera(codcar varchar(1) primary key not null constraint rscodcar check(codcar like '[a-z]'), nomcar varchar(15))

2.- Esta otra forma también es cuando se crea la tabla pero el constraint se pone al final de haber declarado todos los campos. create table carrera(codcar varchar(1) primary key not null, nomcar varchar(15), constraint rscodcar check(codcar like '[a-z]'))

3.- Y aquí primero se crea la tabla y luego se añade el constraint create table carrera(codcar varchar(1) primary key not null, nomcar varchar(15)) Alter table Carrera Add constraint rscodcar check(codcar like '[a-z]') En los 3 casos se creo un constraint llamado RscodCar, para borrar un constraint seria: Alter table Carrera Drop constraint rscodcar Ejemplo: Crear una tabla llamada Alumno con los campos CODALU, NOMALU, FECHANAC, SEXO y CIUDAD donde no se permita ingresar ningún valor NULL además de que el campo CODALU sea clave primaria ,en el campo CIUDAD darle un valor por defecto que sea “LIMA” , y al ca mpo sexo solo se pueda ingresar M o F.

CREATE TABLE ALUMNO ( CODALU INT NOT NULL PRIMARY KEY, NOMALU VARCHAR(30) NOT NULL, FECHANAC DATETIME NOT NULL, SEXO VARCHAR(1) NOT NULL CONSTRAINT PKSEXO CHECK (SEXO LIKE '[M,F]'), CIUDAD VARCHAR(15) NOT NULL CONSTRAINT PKCIU DEFAULT 'LIMA', CODCUR VARCHAR(4) NOT NULL)

4.- Borrar una tabla.- DROP TABLE Nombre Ejem: DROP TABLE CURSO Se está eliminando la tabla curso

5.- Modificar tabla.Ejemplo tenemos la tabla CURSO con 2 campos CODALU, NOMALU. Añadir un campo ALTER TABLE Nombre ADD Nombre, Campo, Tipo. Ejemplo: Agregar el campo CODPROF de tipo INT a la tabla curso.

95


ALTER TABLE CURSO ADD CODPROF INT Eliminar un campo: ALTER TABLE Nombre DROP COLUMN Nombre Campo. Ejemplo: borrar el campo CODPROF de la tabla curso. ALTER TABLE CURSO DROP COLUMN CODPROF. Modificar un Campo: ALTER TABLE Nombre ALTER COLUMN Nombre Tipo. Ejemplo: Modificar el campo nomcur para que tenga 10 caracteres. ALTER TABLE CURSO ALTER COLUMN NOMCUR VARCHAR(10)

Ejemplo Práctico go CREATE DATABASE ESTUDIOS GO USE ESTUDIOS GO Create Table Carreras (codcar int not null primary key, nomcar varchar(30) ) go Create Table Alumnos (codalu int identity(1001,1) not null primary key, nomalu varchar(30), codcar int, foreign key(codcar) references carreras(codcar) on update cascade on delete cascade ) go - Agregar tabla Locales con 2 campos (cod y nom) Create table Locales(Codlocal varchar(2) primary key,nomlocal varchar(30)) - Agregar campo Ubicacion(Lima,Lince,Jesus Maria) a la tabla carreras alter table carreras add Ubicacion varchar(10)

96


- Agregar campo código de local a la tabla carreras y ponerlo como clave foránea alter table carreras add codlocal varchar(2) foreign key(codlocal) references Locales(codlocal) - Agregar campo fecha de matricula a la tabla alumnos alter table alumnos add fecmat datetime - Cambiar el tipo de dato al campo ubicación mas grande esto en la tabla carreras alter table carreras alter column Ubicacion varchar(50) .- Poner al campo ubicación por defecto Lima esto en la tabla carreras alter table carreras add constraint res1 default 'Lima' for Ubicación .- Poner al campo fecha de matricula por defecto la fecha actual esto en la tabla alumnos alter table Alumnos add constraint res2 default getdate() for fecmat - Eliminar el constraint res3 alter table notas drop constraint res3 - Validar que el campo precio de curso sea mayor a 100 esto en la tabla cursos alter table cursos add constraint res4 check(precio > 100) - Validar que el campo sexo solo acepte M o F esto en la tabla alumnos alter table Alumnos add constraint res5 check(sexo='M' or sexo ='F') --También podría ser así: --alter table Alumnos add constraint res5 check(sexo like'[M,F]') - Eliminar el campo código de local de la tabla carreras alter table carreras drop column codlocal -Eliminar constraints alter table carreras drop constraint FK__carreras__codloc__014935CB - Eliminar el campo ubicación de la tabla carerras alter table carreras drop column ubicación - Eliminar la tabla Local drop table locales

97


TEMA 2

Desarrollar comandos manipulación de datos.

SQL

para

la

98


2.1.- Lenguaje de Manipulación de Datos Se presentan las instrucciones que se pueden ejecutar desde un intérprete de SQL, lo que se denomina SQL interactivo. SQL es un lenguaje muy expresivo y, en general, permite muchas formas de expresar las mismas órdenes.

Las cuatro instrucciones que componen el lenguaje de manipulación de datos son las siguientes:

SELECT : permite la declaración de consultas para la recuperación de información de una o más tablas de una base de datos.

INSERT: realiza la inserción de una o varias filas sobre una tabla. DELETE: permite efectuar el borrado de una o varias filas de una tabla. UPDATE: realiza una modificación de los valores de una o más columnas de una o varias filas de una tabla.

Para demostrar estas sentencias utilizaremos una tabla de ejemplo llamada CLIENTES Create Table Clientes(Nombre varchar(100), Apellidos varchar(100), Dirección varchar(150), Población varchar(30), Código Postal varchar(6), email varchar(100), pedidos int)

2.2.- INSERT Los registros pueden ser introducidos a partir de sentencias que emplean la instrucción Insert.

99


La sintaxis utilizada es la siguiente:

Insert Into nomtabla (nomcampo1, nomcampo2,...) Values (valorcampo1, valorcampo2...) Un ejemplo sencillo a partir de nuestra tabla modelo es la introducción de un nuevo cliente lo cual se haría con una instrucción de este tipo: Insert Into clientes (nombre, apellidos, direccion, poblacion, codigopostal, email, pedidos) Values ('Perico', 'Palotes', 'Percebe n°13', 'Lepe', '123456', '[email protected]', 33)

Como puede verse, los campos no numéricos o booleanos van delimitados por apostrofes: '. También resulta interesante ver que el código postal lo hemos guardado como un campo no numérico. Esto es debido a que en determinados países (como en Inglaterra) los códigos postales contienen también letras. Por supuesto, no es imprescindible rellenar todos los campos del registro. Eso sí, puede ser que determinados campos sean necesarios. Estos campos necesarios pueden ser definidos cuando construimos nuestra tabla mediante la base de datos. Resulta muy interesante, ya veremos más adelante el por qué, el introducir durante la creación de nuestra tabla un campo auto incrementable que nos permita asignar un único número a cada uno de los registros. De este modo, nuestra tabla clientes presentaría para cada registro un número exclusivo del cliente el cual nos será muy útil cuando consultemos varias tablas simultáneamente. Una forma más sencilla de utilizar el insert seria así : Insert Into clientes Values ('Perico', 'Palotes', 'Percebe n°13', 'Lepe', '123456', '[email protected]', 33) Noten que aquí no hizo falta poner los nombres de los campos y se hizo la misma inserción.

100


2.3.- DELETE Para borrar un registro nos servimos de la instrucción Delete. En este caso debemos especificar cuál o cuales son los registros que queremos borrar. Es por ello necesario establecer una selección que se llevará a cabo mediante la cláusula WHERE. Delete From nomtabla Where condicion Si queremos por ejemplo borrar todos los registros de los clientes que se llamen Perico lo haríamos del siguiente modo: Delete From clientes Where nombre='Perico' Hay que tener cuidado con esta instrucción ya que si no especificamos una condición con WHERE, lo que estamos haciendo es borrar toda la tabla, como en este ejemplo: Delete From clientes.

2.4.- UPDATE Es la instrucción que nos sirve para modificar nuestros registros. Como para el caso de Delete, necesitamos especificar por medio de Where cuáles son los registros en los que queremos hacer efectivas

nuestras

modificaciones.

Además,

obviamente,

tendremos que especificar cuáles son los nuevos valores de los campos que deseamos actualizar. La sintaxis es de este tipo:

Update nomtabla Set nomcampo1 = valorcampo1, nomcampo2 = valorcampo2.. Where condicion

Ejemplo: Update clientes Set nombre='José' Where nombre='Pepe' Mediante esta sentencia cambiamos el nombre Pepe por el de José en todos los registros cuyo nombre sea Pepe. Aquí también hay que ser cuidadoso de no olvidarse de usar Where, de lo contrario, modificaríamos todos los registros de nuestra tabla.

101


2.5.- SELECT La selección total o parcial de una tabla se lleva a cabo mediante la instrucción Select. En dicha selección hay que especificar: Los campos que queremos seleccionar, la tabla en la que hacemos la selección y la condición que vamos a usar.

La sintaxis seria: Select nomcampo1.nomcampo2,…from nomtabla where condicion

Y si vamos a ver todos los campos seria: Select * From nomtabla where condicion

OJO: Si obviamos el WHERE se mostraran todo los datos En nuestra tabla modelo de clientes podríamos hacer por ejemplo una selección del nombre y dirección de los clientes con una instrucción de este tipo: Select nombre, dirección From clientes Si queremos ver todos los campos : Select * From clientes Resulta también muy útil el filtrar los registros mediante condiciones que vienen expresadas después de la cláusula Where. Si quisiésemos mostrar los clientes de una determinada

ciudad

usaríamos

una

expresión

como

esta:

Select * From clientes Where poblacion = 'Madrid'

2.6.- Operadores En la sentencia SELECT podemos utilizar los siguientes operadores que serán utilizados después de la cláusula Where y pueden ser combinados hábilmente mediante paréntesis para optimizar nuestra selección a muy altos niveles.

102


Operadores matemáticos: >

Mayor que

<

Menor que

Otros operadores Selecciona los registros cuyo valor de Like

campo se asemeje, no tomar en cuenta

>= Mayor o igual que

mayúsculas y minúsculas.

<= Menor o igual que

Da un conjunto de valores para un campo In y Not In

<> Distinto =

para los cuales la condición de selección es (o no) válida.

Igual

Is Null y Is Not Selecciona aquellos registros donde el Null

campo especificado está (o no) vacío.

Operadores lógicos Between...And

And

Selecciona los registros comprendidos en un intervalo

Or

Distinct

Selecciona los registros no coincidentes.

Not

Desc

Clasifica los registros por orden inverso.

Comodines * Sustituye a todos los campos. % Sustituye a cualquier cosa o nada dentro de una cadena. _ Sustituye un solo carácter dentro de una cadena.

Empleados

Id empleado

Salario

Beneficios

Cargo

101

75000

15000

Encargado

215

60000

12500

Encargado

244

50000

12000

Técnico

300

45000

10000

Técnico

335

40000

10000

Técnico

400

32000

7500

Aprendiz

103


Para ver los operadores que se pueden utilizar en la sentencia SELECT vamos a utilizar l tabla Empleado

AND , OR Mostrar los empleados cuyo salario es igual 4000 y que su crago sea TECNICO SELECT * FROM Empleados WHERE Salario=40000 AND Cargo = ‘Técnico’

Mostrar los códigos de todos aquellos que ganan menos de 40.000 o tienen menos de 10.000 en beneficios SELECT ID_EMPLEADO FROM EMPLEADOS WHERE SALARIO < 40000 OR BENEFICIOS < 10000;

AND & OR pueden ser combinadas, por ejemplo: SELECT ID_EMPLEADO FROM EMPLEADOS WHERE CARGO = 'Encargado' AND SALARIO 60000 OR BENEFICIOS 12000; Primero, SQL encuentra las filas donde el salario es mayor de 60.000 y la columna del cargo es igual a Encargado, una vez tomada esta nueva lista de filas, SQL buscará si hay otras filas que satisfagan la condición AND previa o la condición que la columna de los Beneficios sea mayor de 12.000. Consecuentemente, SQL solo muestra esta segunda nueva lista de filas, recordando que nadie con beneficios sobre 12.000 será excluido ya que el operador OR incluye una fila si el resultado de alguna de las partes es VERDADERO. Date cuenta que la operación AND se ha hecho primero. Para generalizar este proceso, SQL realiza las operaciones AND para determinar las filas donde las operaciones AND se mantienen VERDADERO (recordar: todas las condiciones son verdaderas), entonces estos resultados son usados para comparar con las condiciones OR, y solo muestra aquellas filas donde las condiciones unidas por el operador OR se mantienen ciertas para alguna de las partes de la condición. Para realizar OR antes de AND, pero si quisieras ver una lista de empleados ganando un gran salario (50.000) o con un gran beneficio (10.000), y sólo quieres que lo mire para los empleados con el cargo de Encargado, usa paréntesis: SELECT ID_EMPLEADO FROM EMPLEADOS WHERE CARGO = 'Encargado' AND (SALARIO 50000 OR BENEFICIO 10000);

104


IN , BETWEEN Un método fácil de usar condiciones compuestas es usando IN o BETWEEN. Por ejemplo si tú quieres listar todos los encargados y Técnico: SELECT * FROM EMPLEADOS WHERE CARGO IN ('Encargado', 'Técnico'); O para listar aquellos que ganen más o 30.000, pero menos o igual que 50.000, usa: SELECT * FROM EMPLEADOS WHERE SALARIO BETWEEN 30000 AND 50000; Para listar todos los que no están en este rango: SELECT * FROM EMPLEADOS WHERE SALARIO NOT BETWEEN 30000 AND 50000; De forma similar, NOT IN lista todas las filas excluyendo aquellas de la lista IN. SELECT * FROM EMPLEADOS WHERE CARGO NOT IN ('Encargado', 'Técnico');

IS NULL, IS NOT NULL Para listar todos los empleados que tenga el campo SALARIO nulo SELECT * FROM EMPLEADOS WHERE SALARIO IS NULL De forma similar sería el NOT SELECT * FROM EMPLEADOS WHERE SALARIO IS NOT NULL

LIKE Observa la Empleados, y di que quieres ver todas las personas en las cuales su apellido comience por "l":, intenta: SELECT

*

FROM

EMPLEADOS

WHERE

APELLIDOS

LIKE

'L%';

El tanto por ciento (%) es usado para representar un posible carácter (sirve como comodín), ya sea número, letra o puntuación, o para seleccionar todos los caracteres que puedan aparecer después de "L". Para encontrar las personas con el apellidos terminado en "L", usa ‘%L’, o si quieres la ‘L’ en medio de la palabra ‘%L%’. El ‘%’

puede ser usado en lugar de cualquier carácter en la misma posición relativa a los caracteres dados. NOT LIKE muestra filas que no cumplen la descripción dada.

105


ORDER BY Esta sentencia permite ordenar los registros ya se en forma ascendente o descendente (DESC). Mostrar los empleados ordenados por salario en forma descendente y por apellido en forma ascendente. SELECT * FROM EMPLEADOS ORDER BY SALARIO DESC, APELLIDO.

DISTINCT Esta es una función que sirve para mostrar resultados no repetitivos Ejemplo: mostrar los cargos de los empleados.

SELECT DISTINCT CARGO FROM EMPLEADOS Este select solo mostrará: Encargado, Técnico, Aprendiz Ahora si queremos ordenarlo alfabéticamente tendríamos que poner lo siguiente: SELECT DISTINCT CARGO FROM EMPLEADOS ORDER BY CARGO.

TOP Muestra solo cierta cantidad de registros depende del número que vaya acompañado de la palabra TOP. Mostrar 3 empleados que tengan los salarios más altos SELECT TOP 3 * FROM EMPLEADOS ORDER BY SALARIO DESC

Ejemplo Práctico : Manipulación de Base de Datos

Tomando de ejemplo las tablas anteriores (Carreras, Cursos, Alumnos y Notas) realizar diferentes operaciones SQL

106


Actualizar los Datos Modificar la nota TRA y poner 05 a todos los alumnos UPDATE NOTAS SET TRA=5 Modificar el nombre de la carrera marketing por marketing informático UPDATE CARRERAS SET NOMCAR='Marketing Informatics' WHERE CODCAR=4 Modificar al curso c0011 y poner de nombre 'taller de n-capas .net' y en precio 300 UPDATE CURSOS SET NOMCUR='Taller de N-Capas . Net’, PRECIO=300 WHERE CODCUR='C0011' Modificar el campo prom con el promedio de las notas (pp+tra+ex *2)/4 UPDATE NOTAS SET PROM=(PP + TRA + (EX * 2))/4

LIKE Actualizar los precios de los cursos a 300 a los cursos cuyo nombre empiece con la palabra Visual Basic UPDATE CURSOS SET PRECIO=300 WHERE NOMCUR LIKE 'Visual Basic%' IS NULL Actualizar el campo PP donde PP sea nulo UPDATE NOTAS SET PP=05 WHERE PP IS NULL Eliminar Datos Eliminar los cursos cuyo precio sea menor que 100 DELETE FROM CURSOS WHERE PRECIO <=100 BETWEEN Eliminar las notas cuyo promedio estén entre 0 y 10 DELETE FROM NOTAS WHERE PROM BETWEEN 0 AND 10 Eliminar los alumnos cuyo nombre termine con la letra A o que empiece con la M pero solo de la carrera de computación e informática DELETE FROM ALUMNOS WHERE (NOMALU LIKE '%A' OR NOMALU LIKE ‘M %') AND CODCAR=1 Seleccionar los datos Para Visualizar los Datos SELECT * FROM CARRERAS SELECT * FROM CURSOS SELECT * FROM ALUMNOS SELECT * FROM NOTAS Mostrar los nombres de los alumnos que son de la carrera 2 SELECT NOMALU FROM ALUMNOS WHERE CODCAR=1 IN Mostrar los alumnos que sean de la carrera 1 o 3 SELECT * FROM ALUMNOS WHERE CODCAR IN(1,3) ORDER BY Mostrar los cursos ordenados por precio SELECT * FROM CURSOS ORDER BY PRECIO Mostrar las notas del alumno 1000008 ordenadas por prom en forma descendente SELECT * FROM NOTAS WHERE CODALU=1000008 ORDER BY PROM DESC

107


TEMA 3

Desarrollar funciones.

comandos

SQL

usando

108


3.1 Funciones Agregadas Vamos

a

ver

cinco

importantes

funciones

agregadas: SUM, AVG, MAX, MIN y COUNT. Son llamadas funciones agregadas porque resumen el resultado de una consulta.

SUM ()

Devuelve el total de todas las fila, satisfaciendo todas las condiciones de una columna dada, cuando la columna dada es numérica.

AVG ()

Devuelve la media de una columna dada.

MAX ()

Devuelve el mayor valor de una columna dada.

MIN ()

Devuelve el menor valor en una columna dada.

COUNT(*) Devuelve el número de filas que satisfacen las condiciones.

SELECT SUM (SALARIO), AVG(SALARIO) FROM EMPLEADOS; Esta consulta muestra el total de todos los salarios de la tabla, y la media salarial de todas las entradas en la tabla. SELECT MIN (BENEFICIOS) FROM EMPLEADOS WHERE CARGO = 'Encargado'; Esta consulta devuelve el menor valor de la columna de beneficios, de los empleados que son Managers, la cual es 12.500. SELECT COUNT (ID_EMPLEADO)

FROM EMPLEADOS

WHERE

CARGO = 'Técnico'. Esta consulta nos dice cuantos empleados tienen la categoría de Técnico (3).

109


3.2.- GROUP BY Combina los registros con valores idénticos, en la lista de campos especificados, en un único registro. Para cada registro se crea un valor sumario si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción SELECT. Su sintaxis es:

SELECT campos FROM nomtabla WHERE

condicion GROUP BY campos del grupo Se utiliza la cláusula WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados.

Ejemplo: Mostrar la cantidad de empleados que hay por Cargo SELECT CARGO,COUNT(ID_EMPLEADO) FROM EMPLEADOS GROUP BY CARGO

El resultado seria asi:

Encargado

4

Técnico

3

Aprendiz

2

Utilizando el HAVING podriamos hacer una consulta como esta SELECT CARGO,COUNT(ID_EMPLEADO) FROM EMPLEADOS GROUP BY CARGO HAVING COUNT(ID_EMPLEADO) > = 3 Aqui estamos mostrando solo los cargos que tienen mas de 3 empleados

3.3.- Funciones de fecha y hora Estas funciones escalares realizan una operación sobre un valor de fecha y hora de entrada, y devuelven un valor de cadena, numérico o de fecha y hora. Esta tabla presenta las funciones de fecha y hora.

110


DATEPART.- DATEPART (datepart, date) Datepart.- Es el parámetro que especifica la parte de la fecha que se va a devolver. La tabla enumera las partes de las fechas y abreviaturas reconocidas por Microsoft SQL Server.

Parte de la fecha

Abreviaturas

Year

yy, yyyy

Quarter

qq, q

Month

mm, m

Dayofyear(dia del año son 365 dias dy, y por año) Day

dd, d

Week(Numero de semana Tome en wk, ww cuenta que le mes tiene 4 semanas mas o menos) Weekday(Numero de dia Ejemplo: 1 Dw lunes,2 martes …etc)

Hour

Hh

Minute

mi, n

Second

ss, s

Millisecond

Ms

Ejemplo: SELECT DATEPART(MM,'01/08/2003') .- Esto Devuelve el mes de la fecha 01-08-03 Otras Funciones.FUNCI N

CONCEPTO Devuelve un valor que es la suma de un intervalo a la fecha especificada.

DATEADD

Sintaxis DATEADD ( datepart , number , date ) En este ejemplo a un campo fecha llamado pubdate se le esta sumando 21 días. USE pubs GO SELECT DATEADD(day, 21, pubdate) FROM titles

111


Devuelve la diferencia de fecha y hora que hay entre dos fechas especificadas.

Sintaxis DATEDIFF ( datepart , startdate , enddate ) DATEDIFF

Ejemplos En este ejemplo se determina la diferencia en días entre la fecha actual y la fecha de publicación de los títulos de la base de datos pubs. USE pubs GO SELECT DATEDIFF(day, pubdate, getdate()) FROM titles

Devuelve una cadena de caracteres que representa la parte de la fecha especificada de la fecha especificada.

DATENAME

Sintaxis DATENAME ( datepart , date ) Ejemplos En este ejemplo se extrae el nombre del mes de la fecha devuelta por GETDATE. SELECT DATENAME(month, getdate()) AS 'Month Name'

DAY

Devuelve un entero que representa la parte del día de la fecha especificada.

Sintaxis DAY ( date ) Equivale a DATEPART(dd,Fecha) Devuelve la fecha y hora actual

GETDATE

Sintaxis GETDATE ( ) Devuelve un entero que representa el mes de una fecha especificada.

MONTH

Sintaxis MONTH ( date ) Equivale a DATEPART(mm,Fecha) Devuelve un entero que representa la parte de año de la fecha especificada.

YEAR

Sintaxis YEAR ( date ) Equivale a DATEPART(yy,Fecha)

112


3.4.- Funciones matemáticas Estas funciones escalares realizan un cálculo, normalmente basado en valores de entrada proporcionados como argumentos, y devuelven un valor numérico. Aquí solo algunas funciones de ejemplo:

ABS Devuelve el valor DEGREES Dado un ángulo en RAND Devuelve un valor absoluto, positivo de la radianes, devuelve el ángulo float aleatorio de 0 a 1. expresión numérica dada. correspondiente en grados. Sintaxis Sintaxis RAND Sintaxis ABS (numeric_expression DEGREES( numeric_expression) (numeric_expression) ) Ejem: SELECT RAND(5)

Todas las funciones matemáticas, excepto RAND, son funciones deterministas; devuelven siempre el mismo resultado cada vez que se les llama con un conjunto específico de valores de entrada. RAND es determinista sólo cuando se especifica un parámetro de inicialización

3.5.- Funciones de cadena Estas funciones escalares realizan una operación sobre una cadena de entrada y devuelven un valor de cadena o un valor numérico.

ASCII.- Devuelve el código ASCII del carácter más a la izquierda de una expresión de caracteres. Ejemplo: SELECT ASCII ('\').- Esta función devolverá 92

CHAR.- Una función de cadena que convierte un código ASCII int en un carácter. Ejemplo : SELECT CHAR('64').- Esta funcion devolvera @

CHARINDEX.- Devuelve la posición inicial de la expresión especificada en una cadena de caracteres.

113


Sintaxis CHARINDEX (Caracter a buscar,Cadena en donde se busca, [opcional desde que carácter se quiere buscar]) SELECT CHARINDEX (‘S’,'CASA').- Esta función devolverá 3 que es la posición SELECT CHARINDEX (‘S’,'CASA',4).- Esta función devolverá 0 por que no se encontró el carácter S a partir de la posición 4.

LEFT.- Devuelve la parte de una cadena de caracteres que comienza en un número de caracteres especificado a partir de la izquierda. SELECT LEFT(‘REFRIGERADORA’, 5) Esta función devolverá ‘REFRI’

RIGHT.- Devuelve la parte de una cadena de caracteres que comienza en el número de caracteres especificado en integer_expression a partir de la derecha. SELECT RIGHT (‘REFRIGERADORA’, 5) Esta función devolverá ‘ADORA’

LEN.-Devuelve la longitud de la cadena. SELECT LEN ( ‘CASA’) Esta función devolverá 4

LOWER.- Devuelve una cadena convertida en minúscula SELECT LOWER (‘casa’ ).- Esto devuelve ‘CASA’ UPPER.- Devuelve una cadena convertida en mayúscula. SELECT UPPER (‘CASA’).- Esto devuelve ‘casa’

LTRIM.- Suprime los espacios en blanco a la izquierda SELECT LTRIM (‘CASA’).- Esto devuelve ‘CASA’

RTRIM.- Suprime los espacios en blanco a la derecha SELECT RTRIM (‘CASA‘).- Esto devuelve ‘CASA’

SPACE.- Devuelve una cadena de espacios repetidos. SELECT ‘LA’ + SPACE (2 ) + ‘CASA’. -Esto devuelve ‘LA CASA’

REPLACE.- Reemplaza por una tercera expresión todas las apariciones de la segunda expresión de cadena proporcionada en la primera expresión de cadena. Sintaxis REPLACE (‘cadena donde se busca' , 'cadena a buscar' , 'cadena a reemplazar' ) SELECT REPLACE (‘ABCDEF’,’DEF’,’XX’) El resultado seria ‘ABCXXX’

SUBSTRING.- Devuelve una sub cadena a raíz de otra SELECT SUSBTRING (‘ABCD’,2,2) El resultado seria ‘BC’

114


3.6.- Otras funciones CAST y CONVERT Convierten explícitamente una expresión de un tipo de datos en otro. CAST y CONVERT proporcionan funciones similares. Uso de CAST: CAST (expression AS data type) Uso de CONVERT: CONVERT ( data type , expression ) Ejemplo: SELECT * FROM TITLES WHERE CAST (PRICE AS VARCHAR (7)) LIKE '19%' SELECT * FROM TITLES WHERE CONVERT (VARCHAR (7), PRICE) LIKE '19%' En ambos casos el campo Price es numérico y usando el CAST y el Convertí (se puede usar cualquiera de los dos) el campo se esta cambiando a varchar (7) por eso podemos usar el like.

Ejemplos de Funciones de fecha y de cadenas en SQL Server (funciones.sql)

Funciones de Fecha --Mostrar el nombre del alumno la fecha de matricula y la f echa de inicio de clases (10 días después de la matricula). SELECT NOMALU, FECHAMAT, DATEADD (DD, 10, FECHAMAT) AS FECHA_INICIO FROM ALUMNO. --Mostrar el nombre del alumno el año, y el mes en que se matricularon.

115


SELECT NOMALU, DATEPART (YY, FECHAMAT) AS ANIO, DATEPART (MM, FECHAMAT) AS MES FROM ALUMNO --Mostrar el nombre del alumno, la fecha de matricula, y la cantidad de días que han pasado desde que se matriculó. SELECT NOMALU, FECHAMAT, DATEDIFF (DD, FECHAMAT, AND GETDATE) LAS CANTIDIAS FROM ALUMNO. --Mostrar el nombre del alumno, la fecha de matricula, el nombre del día y el nombre del mes de la matrícula. SELECT NOMALU, FECHAMAT, DATENAME (DW, FECHAMAT) AS DIA, DATENAME (MM, FECHAMAT) AS MES FROM ALUMNO. --Mostrar el mes, el día y el año de la fecha actual y la fecha actual. SELECT YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE()),GETDATE()

Funciones de Cadena --autogenerar el código con el ultimo digito del codalu + el primer caracter del apellido -- los 2 primeros caracteres del nombre + los tres primeros caracteres del mes + -- los 2 últimos caracteres del año. SELECT RIGHT (CONVERT (VARCHAR (4), CODALU(1), FROM ALUMNO --Extrae el Ultimo caracter del código. SELECT SUBSTRING (NOMALU, CHARINDEX (' ', NOMALU) + 1 ,1) FROM ALUMNO— Extrae de la posición de 1 un caracter. --Lo primero charindex(' ',nomalu) esa función busca un caracter o sea busca el espacio en blanco en el campo nomalu y si lo encuentra devuelve la posición SELECT UPPER (LEFT (DATENAME (MM, FECHAMAT),3)) FROM ALUMNO--Saca los 3 primeros caracteres del mes. SELECT RIGHT (CAST (YEAR (FECHAMAT) AS VARCHAR (2)),2) FROM ALUMNO-Saca los 2 caracteres del año. --y aquí todo junto.

116


SELECT

ALUMNO.*,RIGHT(CONVERT(VARCHAR(4),CODALU),1)

+

SUBSTRING(NOMALU,CHARINDEX(' ' ,NOMALU)+1,1) + LEFT(NOMALU,2)

+

UPPER(LEFT(DATENAME(MM,FECHAMAT),3))

+

RIGHT(CAST(YEAR(FECHAMAT) AS VARCHAR(4)),2) FROM ALUMNO

USO DEL CASE WHEN THEN --Mostrar los datos de la tabla Curso más un mensaje que diga 'Caro' si el precio es mayor a 50 o 'Barato' si es menor a 50.

SELECT CURSO.*,

--la sintaxis del CASE WHEN seria así:

MENSAJE=CASE

--variable = CASE

WHEN PRECUR > 50 THEN 'CARO'

--WHEN COND1 THEN RESULTADO1

ELSE 'BARATO'


END


FROM CURSO

--ELSE RESULTADODELELSE

Donde Mensaje es una variable y es

--END

ahí donde se va a guardar el mensaje Caro o Barato.

117


TEMA 4

Definir índices y vistas.

118


ÍNDICES Los índices nos ayudan a obtener datos de las tablas en forma más rápida. Utilicemos un ejemplo para ilustrar este punto: Digamos que estamos interesados en leer en un libro de jardinería acerca de cómo cultivar pimientos. En vez de leer el libro desde el comienzo hasta que encontremos una sección sobre pimientos, es mucho más rápido para nosotros ir a la sección índice al final del libro, ubicar qué páginas contienen la información sobre pimientos, y luego dirigirnos a esas páginas directamente. Al dirigirnos al índice primero ahorramos tiempo y seguramente es el método más eficiente para ubicar la información que necesitamos.

Ejemplo : CREATE UNIQUE INDEX titles_ind ON titles (title,type) Aqui se esta creando un índice llamado titles_ind indicando que el campo title y type no se podrán repetir. Para borrar un índice seria DROP INDEX TITLES.TITLES_IND (Va el nombre de la tabla. El nombre del índice).

Ejemplo: En la tabla clientes, este ejemplo crea un índice único en el campo NIF. Esto nos permitirá buscar mucho mas rápido por el campo NIF y nos asegurará que no tengamos dos NIF iguales. CREATE UNIQUE INDEX UIX_CLIENTES_NIF ON CLIENTES (NIF)

119


Ejemplo: Teniendo la siguiente tabla : Tabla Customer (First Name char (50), Last Name char (50), Address char (50), Cities char (50), Country char (25), Birth Date date)

Si deseamos crear un índice tanto en Ciudad como en País, ingresaríamos, CREATE INDEX IDX_CUSTOMER_LOCATION on CUSTOMER (City, Country) No hay una regla estricta respecto de cómo nombrar un índice. El método generalmente aceptado es colocar un prefijo, tal como “IDX_”, antes del nombre

de un índice para evitar la confusión con otros objetos de la base de datos. También es una buena idea brindar información sobre qué tabla y columna(s) se utilizará el índice. Por favor note que la sintaxis exacta para CREATE INDEX puede ser distinta según las diferentes bases de datos. Debería consultar con su manual de referencia de base de datos para obtener la sintaxis precisa.

VISTAS .

En SQL, tu puedes (comprueba tu DBA) tener acceso a crear vistas por ti mismo. Lo que una vista hace es permitirte asignar resultados de una consulta a una tabla nueva y personal, que puedes usar en otras consultas, pudiendo utilizar el nombre dado a la tabla de tu vista en la cláusula FROM. Cuando accedes a una vista, la consulta que está definida en la sentencia que crea tu lista está relacionada (generalmente), y los resultados de esta consulta son como cualquier otra tabla en la consulta que escribiste invocando tu vista.

120


Por ejemplo, para crear una vista:

CREATE VIEW ANTVIEW AS SELECT PRODUCTOPEDIDO PRODUCTOPEDIDO FROM PEDIDOS; Ahora, escribe una consulta usando esta vista como tabla, donde la tabla es una listado de todos los Productos Pedidos de la tabla Pedidos: SELECT ID_VENDEDOR ID_VENDEDOR FROM ANTIGÜEDADES, ANTVIEW WHERE PRODUCTOPEDIDO = PRODUCTO; Esta consulta muestra todos los ID_vendedor de la tabla de Antigüedades donde el producto, en esta tabla, aparece en la vista Antview, la cual no es más que todos los Productos Desired de la tabla Pedidos. El listado es generado yendo uno por uno por los Productos Antigüos hasta donde hay una coincidencia con la vista Antview. Las vistas pueden ser usadas para restringir el acceso a las bases de datos, así como para simplificar una consulta compleja. Crea una tabla virtual que representa los datos de una o más tablas de una forma alternativa. CREATE VIEW debe ser la primera instrucción en una secuencia de consultas. Ejemplo: CREATE VIEW titles_vi t itles_view ew AS SELECT title, type, price, pubdate FROM titles Se ha creado una vista llamada titles_view con algunos campos de la tabla t excepción itles, a esta vista se le puede hacer los mismos procesos que a una tabla. Para eliminar se pone DROP VIEW Nombre de la Vista.

121



 TUTORIAL SQL BASICO http://www.webtaller.com/construccion/leng http://www.webtaller.com/con struccion/lenguajes/sql/manu uajes/sql/manuales/tutorial_ ales/tutorial_ basico_sql.php

 MANUAL DE SQL http://mygnet.net/manuales/sql//tu http://mygnet.net/ma nuales/sql//tutorial_de_sql_b torial_de_sql_basico.1108 asico.1108

Actividades y Ejercicios Ingresa al link “SQL Básico” lee atentamente las indicaciones, desarróllalo y envíalo por el mismo medio. Mediante código SQL desarrollar lo siguiente: 1. Crear una tabla llamada ALUMNO con los siguientes Campos (código, nombre y apellido, dirección, email, fecha de Nacimiento, distrito, pensión)

2. Mediante sentencias SQL llenar datos (10 registros) a la tabla creada (ALUMNO)

3. Mostrar los alumnos que nacieron en la década de los 80 y que son De COMAS o LINCE (Aplique SELECT)

4. Mostrar los alumnos que se apelliden GARCIA (no importa si es su Primer apellido o el segundo) y que su pensión este entre 300 y 500.

122


Autoevaluaciones

1.- ¿El lenguaje estándar estándar en la mayoría de Sistemas Sistemas de Gestión de Bases Bases de Datos Datos es el? a. b. c. d. e.

MySQL SQL DBMS RSQL DBS

2.- ¿Qué sentencias sentencias permiten modificar modificar las estructuras estructuras de los objetos de la base de datos? a. b. c. d. e.

DDL DBMS RDBMS DLL DBS

3.- ¿Qué sentencia SQL permite crear objetos en la base datos? a. Select b. Create c. Delate d. Update e. Insert 4.- ¿Qué sentencia sentencia SQL permite permite borrar borrar datos en la base datos? a. b. c. d. e.

Select Create Drop Delete Where

5.- ¿Qué sentencia sentencia SQL permite permite modificar modificar datos en la base base datos? a. b. c. d. e.

Select Create Delete Update Insert

6.- ¿Qué sentencia SQL permite visualizar visualizar los datos de una tabla? tabla? a. Alter b. Create

123


c. Delete d. Select e. Insert 7.- ¿La función que permite Calcular la media aritmética de un conjunto de valores es? a. b. c. d. e.

Sum avg max min div

8.- ¿Cuál es la función que sirve para añadir un valor a una fecha? a. Datediff b. AddDate c. DateAdd d. DatePart e. Date Fech 9.- ¿Sirve para encontrar mas rápido los datos dentro de una tabla? a. b. c. d. e.

Primary Key Foreign Key Índices Llaves alternas Llave Foránea

10.- ¿Una Vista seria? a. Una consulta temporal b. Como una tabla que se crea en base a una consulta c. Un conjunto de datos que solo funcionan con la sentencia WHERE d. La estructura de unas llaves primarias e. Una consulta

124


Resumen

SQL (Structured Query Language / Lenguaje de consulta estructurado) es el lenguaje estándar en la mayoría de Sistemas de Gestión de Bases de Datos. Es un lenguaje declarativo; es decir, que indica qué es lo que quiere y no cómo se obtiene. La manera en la que se acceden a los datos para responder a las sentencias SQL depende de cada motor de base de datos. El Lenguaje de Manipulación de Datos (Data Manipulation Language) permite realizar consultas y manipulación de los datos. Las sentencias DML son: SELECT: permite la declaración de consultas para la recuperación de información de una o más tablas de una base de datos. INSERT: realiza la inserción de una o varias filas sobre una tabla. DELETE: permite efectuar el borrado de una o varias filas de una tabla. UPDATE: realiza una modificación de los valores de una o más columnas de una o varias filas de una tabla. Las funciones en SQL son diversas y vienen integradas dentro del mismo SQL. Entre las funciones tenemos las siguientes: Funciones de fechas que sirve para manipular los campos de tipo fecha como por ejemplo mostrar el mes o año de una fecha dada. Funciones de cadenas que sirve para operar con los campos de tipo cadena (varchar , char, etc), por ejemplo mostrar los 2 primeros caracteres del nombre de una persona, buscar una letra dentro de una cadena, etc. Funciones Matemáticas que sirve para cálculos aritméticos como mostrar la raíz cuadrada de un campo, etc. Funciones de agregado son llamadas funciones agregadas porque resumen el resultado de una consulta. T enemos la función Count para contar, Max para mostrar el mayor, etc. Los índices nos ayudan a obtener datos de las tablas en forma más rápida. Lo que una vista hace es permitir asignar resultados de una consulta a una tabla nueva y personal, que se puede usar en otras consultas, pudiendo utilizar el nombre dado a la tabla de la vista en la cláusula FROM.

125


126


Introducción

a) Presentación y contextualización En esta unidad se desarrollara el manejo avanzado de las sentencias SQL, el cual permitirá al alumno crear consultas avanzadas utilizando las diferentes funciones que soporta el SQL. Además se mostrara la creación y manipulación de códigos internos dentro de las bases de datos denominados Procedimientos almacenados y Triggers

b) Competencia Identificar las sentencias avanzadas SQL el cual permitirá el manejo total de la base de datos .

c) Capacidades 1. Define correctamente consultas internas y externas. 2. Analiza y crea la administración de código almacenado para consultas avanzadas. 3. Analiza y define procedimientos almacenados. 4. Analiza y define Triggers.

d) Actitudes Desarrolla una actitud emprendedora mediante la toma de iniciativas, promoción de actividades y toma de decisiones en relación a la actividad asignada. Actúa con responsabilidad personal, al cumplir con los horarios establecidos y el respeto a las normas de convivencia. Cumple con la presentación de los trabajos encomendados con puntualidad. Desarrolla la creatividad, la innovación, la actitud emprendedora y el respeto a la honestidad intelectual.

e) Presentación de Ideas básicas y contenido esenciales de la Unidad: La Unidad de Aprendizaje 04: Manejo de SQL Avanzado comprende el desarrollo de los siguientes temas: TEMA 01: Consultas con varias tablas internas y externas TEMA 02: Consultas Avanzadas TEMA 03: Procedimientos Almacenados TEMA 04: Triggers

127


TEMA 1

Definir correctamente consultas internas y externas.

128


Desarrollo de los Temas Consultas de Unión Internas

Las vinculaciones entre tablas se realiza mediante la cláusula INNER que combina registros de dos tablas siempre que haya concordancia de valores en un campo común.

Su sintaxis es: SELECT campos FROM tb1 INNER JOIN tb2 ON tb1.campo1 comp tb2.campo2 En donde: tb1, tb2 Son los nombres de las tablas desde las que se combinan los registros. campo1, campo2 Son los nombres de los campos que se combinan. Si no son numéricos, los campos deben ser del mismo tipo de datos y contener el mismo tipo de datos, pero no tienen que tener el mismo nombre. comp Es cualquier operador de comparación relacional : =, <, <=, =, o <.

Se puede utilizar una operación INNER JOIN en cualquier cláusula FROM. Esto crea una combinación por equivalencia, conocida también como unión interna. Las combinaciones Equi son las más comunes; éstas combinan los registros de dos tablas siempre que haya concordancia de valores en un campo común a ambas tablas. Se puede utilizar INNER JOIN con las tablas Departamentos y Empleados para seleccionar todos los empleados de cada departamento. Por el contrario, para seleccionar todos los departamentos (incluso si alguno de ellos no tiene ningún empleado asignado) se emplea LEFT JOIN o todos los empleados (incluso si alguno no está asignado a ningún departamento), en este caso RIGHT JOIN.

129


Si se intenta combinar campos que contengan datos Memo u Objeto OLE, se produce un error. Se pueden combinar dos campos numéricos cualesquiera, incluso si son de diferent e tipo de datos. Por ejemplo, puede combinar un camp o Numérico para el que la propiedad Size de su objeto Fiel d está establecida como Entero, y un campo Contador.

El ejemplo siguiente muestra cómo podría combinar las tablas Categorías y Productos basándose en el campo IDCategoria:

SELECT

Nombre_Categoría,

FROM

Categorias

NombreProducto

JOIN

INNER

Productos

ON Categorias.IDCategoria = Productos.IDCategoria; En el ejemplo anterior, IDCategoria es el campo combinado, pero no está incluido en la salida de la consulta ya que no está incluido en la instrucción SELECT. Para incluir el campo combinado, incluir el nombre del campo en la instrucción SELECT, en este caso, Categorias.IDCategoria. También se pueden enlazar varias cláusulas ON en una instrucción JOIN, utilizando la sintaxis siguiente:

SELECT FROM

tabla1

campos

JOIN

INNER

tabla2

ON

tb1.campo1

comp

tb2.campo1

AND

ON

tb1.campo2

comp

tb2.campo2)

OR

ON tb1.campo3 comp tb2.campo3)];

También puede anidar instrucciones JOIN utilizando la siguiente sintaxis: SELECT FROM [INNER

INNER

tb1

INNER

(tb2

JOIN

campos

[(

JOIN ]tablax

JOIN [(

[ INNER

]tb3

JOIN

...)]

ON

tb3.campo3

comp

tbx.campox)]

ON

tb2.campo2

comp

tb3.campo3)

ON tb1.campo1 comp tb2.campo2;

130


Un LEFT JOIN o un RIGHT JOIN puede anidarse dentro de un INNER JOIN, pero un INNER JOIN no puede anidarse dentro de un LEFT JOIN o un RIGHT JOIN.

Ejemplo SELECT DISTINCTROW Sum([Precio unidad] * [Cantidad]) AS [Ventas], [Nombre] & " " & [Apellidos] AS [Nombre completo] FROM [Detalles de pedidos], Pedidos, Empleados, Pedidos INNER JOIN [Detalles de pedidos] ON Pedidos. [ID de pedido] = [Detalles de pedidos].[ID de pedido], Empleados INNER JOIN Pedidos ON Empleados.[ID de empleado] = Pedidos.[ID de empleado] GROUP BY [Nombre] & " " & [Apellidos]; Crea dos combinaciones equivalentes: una entre las tablas Detalles de pedidos y Pedidos, y la otra entre las tablas Pedidos y Empleados. Esto es necesario ya que la tabla Empleados no contiene datos de ventas y la tabla Detalles de pedidos no contiene datos de los empleados. La consulta produce una lista de empleados y sus ventas totales. Si empleamos la cláusula INNER en la consulta se seleccionarán sólo aquellos registros de la tabla de la que hayamos escrito a la izquierda de INNER JOIN que contengan al menos un registro de la tabla que hayamos escrito a la derecha. Para solucionar esto tenemos dos cláusulas que sustituyen a la palabra clave INNER, estas cláusulas son LEFT y RIGHT. LEFT toma todos los registros de la tabla de la izquierda aunque no tengan ningún registro en la tabla de la izquierda. RIGHT realiza la misma operación pero al contrario, toma todos los registros de la tabla de la derecha aunque no tenga ningún registro en la tabla de la izquierda.

Consultas de Unión Externas Se utiliza la operación UNION para crear una consulta de unión, combinando los resultados de dos o más consultas o tablas independientes. Su sintaxis es:

[TABLE]

consulta1

UNION

[ALL]

[TABLE]

consulta2 [UNION [ALL] [TABLE] consultan [ ... ]]

En donde: consulta1, consulta2, consultan

131


Son instrucciones SELECT, el nombre de una consulta almacenada o el nombre de una tabla almacenada precedido por la palabra clave TABLE. Puede combinar los resultados de dos o más consultas, tablas e instrucciones

SELECT, en cualquier orden, en una única operación UNION. El ejemplo siguiente combina una tabla existente llamada Nuevas Cuentas y una instrucción SELECT:

TABLE

[Nuevas Cuentas]

UNION

ALL

SELECT

*

FROM

Clientes

WHERE [Cantidad pedidos] 1000; Si no se indica lo contrario, no se devuelven registros duplicados cuando se utiliza la operación UNION, no obstante puede incluir el predicado ALL para asegurar que se devuelven todos los registros. Esto hace que la consulta se ejecute más rápidamente. Todas las consultas en una operación UNION deben pedir el mismo número de campos, no obstante los campos no tienen porqué tener el mismo tamaño o el mismo tipo de datos. Se puede utilizar una cláusula GROUP BY y/o HAVING en cada argumento consulta para agrupar los datos devueltos. Puede utilizar una cláusula ORDER BY al final del último argumento consulta para visualizar los datos devueltos en un orden específico.

SELECT [Nombre de compañía], Ciudad FROM Proveedores WHERE País = 'Brasil' UNION SELECT [Nombre de compañía], Ciudad FROM Clientes

WHERE País = "Brasil" Recupera los nombres y las ciudades de todos proveedores y clientes de Brasil

SELECT [Nombre de compañía], Ciudad FROM Proveedores WHERE País = 'Brasil'

UNION SELECT [Nombre de compañía], Ciudad FROM Clientes WHERE País = 'Brasil' ORDER BY Ciudad Recupera los nombres y las ciudades de todos proveedores y clientes radicados en Brasil, ordenados por el nombre de la ciudad

SELECT [Nombre de compañía], Ciudad FROM Proveedores WHERE País = 'Brasil'

UNION SELECT [Nombre de compañía], Ciudad FROM Clientes WHERE País = 'Brasil' UNION SELECT [Apellidos], Ciudad FROM Empleados WHERE Región = 'América del Sur'

132


Recupera los nombres y las ciudades de todos los proveedores y clientes de brasil y los apellidos y las ciudades de todos los empleados de América del Sur

TABLE [Lista de clientes] UNION TABLE [Lista de proveedores] Recupera los nombres y códigos de todos los proveedores y clientes

Ejemplo Práctico: Manipulación de Base de Datos

133


Mostrar el código del alumno, nombre del alumno y nombre de la carrera. SELECT A.CODALU,A.NOMALU,C.NOMCAR FROM ALUMNOS AS A INNER JOIN CARRERAS AS C ON A.CODCAR=C.CODCAR

--Mostrar el código del alumno, nombre del alumno y nombre de la carrera solo de la carrera ADMINISTRACIÓN SELECT A.CODALU,A.NOMALU,C.NOMCAR FROM ALUMNOS AS A INNER JOIN CARRERAS AS C ON A.CODCAR=C.CODCAR WHERE C.NOMCAR='Administracion'

--Mostrar el nombre del alumno, nombre del curso y prom pero solo de los alumnos aprobados SELECT A.NOMALU,C.NOMCUR,N.PROM FROM ALUMNOS AS A INNER JOIN NOTAS AS N

ON A.CODALU=N.CODALU INNER JOIN CURSOS AS C ON C.CODCUR=N.CODCUR WHERE N.PROM >10

134


--Mostrar el nombre del alumno y todas sus notas pero solo las notas del curso de 'Taller' o de 'Economía' A.NOMALU,C.NOMCUR,N.PP,N.TRA,N.EX,N.PROM .EX,N.PROM SELECT A.NOMALU,C.NOMCUR,N.PP,N.TRA,N

FROM ALUMNOS AS A INNER JOIN NOTAS AS N ON

A.CODALU=N.CODALU A.CODALU=N.CODALU

C.CODCUR=N.CODCUR

INNER

WHERE

JOIN

(C.NOMCUR

CURSOS

LIKE

AS

C

ON

'Taller

%'

OR

C.NOMCUR='Economia')

--Mostrar los nombres de los alumnos que tienen Nota SELECT A.NOMALU FROM ALUMNOS AS A INNER JOIN NOTAS AS N ON A.CODALU=N.CODALU

--Mostrar los 3 primeros puestos(Nombre del alumno y Prom) A.NOMALU,N.PROM SELECT TOP 3 A.NOMALU,N.PROM

FROM ALUMNOS AS A INNER JOIN NOTAS AS N A.CODALU=N.CODALU ON A.CODALU=N.CODALU

ORDER BY PROM DESC

--Mostrar el Mayor y menor Precio de los cursos -- MAX,MIN,SUM,AVG,COUNT MAX,MIN,SUM,AVG,COUNT MAX((PRECIO) AS PREMA,MIN PREMA,MIN(PRECIO) (PRECIO) AS PREME FROM CURSOS SELECT MAX

135


--Mostrar los alumnos(codigo) con su mayor nota --GROUP BY CODALU,MAX(PROM) (PROM) FROM NOTAS SELECT CODALU,MAX

GROUP BY CODALU -Mostrar la cantidad de alumnos que hay por carrera C.NOMCAR ,COUNT COUNT(A.CODALU) (A.CODALU) FROM CARRERAS AS C INNER SELECT C.NOMCAR,

JOIN ALUMNOS AS A ON C.CODCAR=A.CODCAR GROUP BY C.NOMCAR --HAVING A.NOMALU, AVG( AVG(N.PROM) AS PROMEDIO FROM ALUMNOS AS A SELECT A.NOMALU, INNER JOIN NOTAS AS N

ON A.CODALU=N.CODALU GROUP BY A.NOMALU AVG(N.PROM)>10 (N.PROM)>10 HAVING AVG

--SUBCONSULTAS --Mostrar los cursos cuyo precio sea mayor o igual al precio promedio de todos los cursos MAX((PRECIO) FROM SELECT * FROM CURSOS WHERE PRECIO >=(SELECT MAX CURSOS)

--Mostrar los nombres de los alumnos que no tengan notas SELECT CODALU,NOMALU FROM ALUMNOS WHERE CODALU NOT IN(SELECT DISTINCT CODALU FROM NOTAS)

136


--Mostrar el código del alumno, nombre del curso, además un mensaje Aorobado o Desaprobado N.CODALU,A.NOMALU,C.NOMCUR,N.PROM ,N.PROM,, SELECT N.CODALU,A.NOMALU,C.NOMCUR OBS=CASE

WHEN N.PROM<=10 THEN 'Desaprobado' WHEN N.PROM>10 THEN 'Aprobado' END FROM ALUMNOS AS A INNER JOIN NOTAS AS N ON A.CODALU=N.CODALU INNER JOIN CURSOS AS C ON C.CODCUR=N.CODCUR

137


TEMA 2

Analizar y crear la administración de código almacenado para consultas avanzadas.

138


Agrupamiento de Registros 1.- GROUP BY Combina los registros con valores idénticos, en la lista de campos especificados, en un único registro. Para cada registro se crea un valor sumario si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción

SELECT. Su sintaxis es: SELECT campos FROM tabla WHERE criterio GROUP BY campos del grupo GROUP BY es opcional. Los valores de resumen se omiten si no existe una función SQL agregada en la instrucción SELECT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de las funciones SQL agregadas. Se utiliza la cláusula WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados.

A menos que contenga un dato Memo u Objeto OLE , un campo de la lista de campos GROUP BY puede referirse a cualquier campo de las tablas que aparecen en la cláusula FROM, incluso si el campo no esta incluido en la instrucción SELECT, siempre y cuando la instrucción SELECT incluya al menos una función SQL agregada.

Todos los campos de la lista de campos de SELECT deben o bien incluirse en la cláusula GROUP BY o como argumentos de una función SQL agregada.

SELECT Id_Familia, Sum(Stock) FROM Productos GROUP BY Id_Familia;

139


Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. HAVING es similar a WHERE, determina qué registros se seleccionan. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuales de ellos se van a mostrar.

SELECT Id_Familia Sum(Stock) FROM Productos GROUP BY Id_Familia HAVING Sum(Stock) 100 AND NombreProducto Like BOS*;

2 AVG Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente Avg(expr) En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.

SELECT Avg(Gastos) AS Promedio FROM Pedidos WHERE Gastos 100;

3 Count . Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente

Count(expr) En donde expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto.

140


Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco, Count calcula el número total de registros,

incluyendo

aquellos

que

contienen

campos

null.

Count(*)

es

considerablemente más rápida que Count(Campo). No se debe poner el asterisco entre dobles comillas ('*').

SELECT Count(*) AS Total FROM Pedidos; Si expr identifica a múltiples campos, la función Count cuenta un registro sólo si al menos uno de los campos no es Null. Si todos los campos especificados son Null, no se cuenta el registro. Hay que separar los nombres de los campos con ampersand (&). SELECT Count(FechaEnvío & Transporte) AS Total FROM Pedidos;

4 Max, Min Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es:

Min(expr) Max(expr) En donde expr es el campo sobre el que se desea realizar el cálculo. Expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).

SELECT Min(Gastos) AS ElMin FROM Pedidos WHERE Pais = 'España'; SELECT Max(Gastos) AS ElMax FROM Pedidos WHERE Pais = 'España';

5 StDev, StDevP Devuelve estimaciones de la desviación estándar para la población (el total de los registros de la tabla) o una muestra de la población representada (muestra aleatoria) . Su sintaxis es:

StDev(expr) StDevP(expr)

141


En donde expr representa el nombre del campo que contiene los datos que desean evaluarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL) StDevP evalúa una población, y StDev evalúa una muestra de la población. Si la consulta contiene menos de dos registros (o ningún registro para StDevP), estas funciones devuelven un valor Null (el cual indica que la desviación estándar no puede calcularse).

SELECT StDev(Gastos) AS Desviacion FROM Pedidos WHERE Pais = 'España'; SELECT StDevP(Gastos) AS Desviacion FROM Pedidos WHERE Pais= 'España';

6 Sum Devuelve la suma del conjunto de valores contenido en un campo especifico de una consulta. Su sintaxis es: Sum(expr) En donde expr respresenta el nombre del campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).

SELECT Sum(PrecioUnidad * Cantidad) AS Total FROM DetallePedido;

7 Var, VarP Devuelve una estimación de la varianza de una población (sobre el total de los registros) o una muestra de la población (muestra aleatoria de registros) sobre los valores de un campo. Su sintaxis es:

Var(expr) VarP(expr)

142


VarP evalúa una población, y Var evalúa una muestra de la población. Expr el nombre del campo que contiene los datos que desean evaluarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL) Si la consulta contiene menos de dos registros, Var y VarP devuelven Null (esto indica que la varianza no puede calcularse). Puede utilizar Var y VarP en una expresión de consulta o en una Instrucción SQL.

SELECT Var (Gastos) AS Varianza FROM Pedidos WHERE Pais = 'España'; SELECT VarP(Gastos) AS Varianza FROM Pedidos WHERE Pais = 'España';

SubConsultas Una subconsulta es una instrucción SELECT anidada dentro de una instrucción SELECT, SELECT...INTO, INSERT...INTO, DELETE, o UPDATE o dentro de otra subconsulta. Puede utilizar tres formas de sintaxis para crear una subconsulta: comparación

[ANY

expresión

|

ALL

[NOT]

| IN

SOME]

(instrucción

(instrucción

sql) sql)

[NOT] EXISTS (instrucción sql)

En donde: comparación Es una expresión y un operador de comparación que compara la expresión con el resultado de la subconsulta. expresión Es una expresión por la que se busca el conjunto resultante de la subconsulta. instrucción sql Es una instrucción SELECT, que sigue el mismo formato y reglas que cualquier otra instrucción SELECT. Debe ir entre paréntesis.

143


Se puede utilizar una subconsulta en lugar de una expresión en la lista de campos de una instrucción SELECT o en una cláusula WHERE o HAVING. En una subconsulta, se utiliza una instrucción SELECT para proporcionar un conjunto de uno o más valores especificados para evaluar en la expresión de la cláusula WHERE o HAVING. Se puede utilizar el predicado ANY o SOME, los cuales son sinónimos, para recuperar registros de la consulta principal, que satisfagan la comparación con cualquier otro registro recuperado en la subconsulta. El ejemplo siguiente devuelve todos los productos cuyo precio unitario es mayor que el de cualquier producto vendido con un descuento igual o mayor al 25 por ciento.:

SELECT

*

FROM

Productos

WHERE

PrecioUnidad

ANY

(SELECT PrecioUnidad FROM DetallePedido WHERE Descuento = 0 .25);

El predicado ALL se utiliza para recuperar únicamente aquellos registros de la consulta principal que satisfacen la comparación con todos los registros recuperados en la subconsulta. Si se cambia ANY por ALL en el ejemplo anterior, la consulta devolverá únicamente aquellos productos cuyo precio unitario sea mayor que el de todos los productos vendidos con un descuento igual o mayor al 25 por ciento. Esto es mucho más restrictivo. El predicado IN se emplea para recuperar únicamente aquellos registros de la consulta principal para los que algunos registros de la subconsulta contienen un valor igual. El ejemplo siguiente devuelve todos los productos vendidos con un descuento igual o mayor al 25 por ciento.:

SELECT

*

FROM

Productos

WHERE

IDProducto

IN

(SELECT IDProducto FROM DetallePedido WHERE Descuento = 0.25); Inversamente se puede utilizar NOT IN para recuperar únicamente aquellos registros de la consulta principal para los que no hay ningún registro de la subconsulta que contenga un valor igual. El predicado EXISTS (con la palabra reservada NOT opcional) se utiliza en comparaciones de verdad/falso para determinar si la subconsulta devuelve algún registro. Se puede utilizar también alias del nombre de la tabla en una subconsulta para referirse a tablas listadas en la cláusula FROM fuera de la subconsulta. El ejemplo siguiente devuelve los nombres de los empleados cuyo salario es igual o mayor que el salario medio de todos los empleados con el mismo título. A la tabla Empleados se le ha dado el alias T1::

144


SELECT Apellido, WHERE

Nombre,

Salario

Titulo,

Salario

(SELECT

=

FROM Empleados

Avg(Salario)

AS

T1

FROM

Empleados

FROM

Empleados

WHERE T1.Titulo = Empleados.Titulo) ORDER BY Titulo; En el ejemplo anterior , la palabra reservada AS es opcional.

SELECT

Apellidos,

Nombre,

Cargo,

Salario

WHERE Cargo LIKE "Agente Ven*" AND Salario ALL (SELECT Salario FROM Empleados WHERE (Cargo LIKE "*Jefe*") OR (Cargo LIKE "*Director*"));

Obtiene una lista con el nombre, cargo y salario de todos los agentes de ventas cuyo salario es mayor que el de todos los jefes y directores. SELECT DISTINCTROW NombreProducto,

FROM Productos

Precio_Unidad

WHERE (Precio_Unidad = (SELECT Precio_Unidad FROM Productos WHERE Nombre_Producto = "Almíbar anisado");

Obtiene una lista con el nombre y el precio unitario de todos los productos con el mismo precio que el almíbar anisado. SELECT DISTINCTROW Nombre_Contacto, Nombre_Compañia, Cargo_Contacto, Telefono FROM Clientes WHERE (ID_Cliente IN (SELECT DISTINCTROW ID_Cliente FROM Pedidos WHERE Fecha_Pedido = #04/1/93# <#07/1/93#);

Obtiene una lista de las compañías y los contactos de todos los clientes que han realizado un pedido en el segundo trimestre de 1993. SELECT Nombre,

Apellidos

FROM Empleados

AS

E

WHERE

EXISTS

(SELECT * FROM Pedidos AS O WHERE O.ID_Empleado = E.ID_Empleado); Selecciona el nombre de todos los empleados que han reservado al menos un pedido .

SELECT (SELECT

DISTINCTROW

Pedidos.Id_Producto,

DISTINCTROW Productos.Nombre

Productos.Id_Producto

=

Pedidos.Id_Producto)

FROM AS

Pedidos.Cantidad, Productos ElProducto

WHERE FROM

Pedidos WHERE Pedidos.Cantidad 150 ORDER BY Pedidos.Id_Producto;

Recupera el Código del Producto y la Cantidad pedida de la tabla pedidos, extrayendo el nombre del producto de la tabla de productos.

NOTA: Para las sentencias SQL que se están usando en este tema se esta tomando como modelo el gestor de base de datos ACCESS

145


TEMA 3

Analizar y almacenados.

definir

procedimientos

146


Concepto. Bajo el nombre STORE PROCEDURE (Procedimiento Almacenado) se concentra una variedad de programas que se encuentran dentro de la Base de Datos. Los más usados son:

PROCEDURES (Procedimientos). FUNCTIONS (Funciones). TRIGGERS (Disparadores). PACKAGES (Paquetes). El código almacenado es fundamental en una base de datos. La ventaja reside en que está directamente bajo el control del motor de la base de datos. Con ello, se logra un mejor desempeño de la base de datos.

Un procedimiento almacenado es un objeto perteneciente a una base de datos, que contiene u n conjunto de instrucciones SQL, tanto de consulta, como de manipulación de datos, como de control de la secuencia del programa, asociados a u n nombre, y que son ejecutados en conjunto. Puede contener parámetros tanto de entrada como de salida (parámetros pasados por referencia), así como devolver un valor de retorno. Son pre compilados al ejecutarse por primera vez, y no vuelven a ser compilados con las subsiguientes ejecuciones, lo que proporciona una cierta mejora en el rendimiento. No obstante si se desea se puede forzar su re compilación.

Una de las principales ventajas de este tipo de objetos, es que al residir en la propia base de datos son compartibles por todos los usuarios, pudiendo de esta manera beneficiarse de los distintos cachés del servidor.

147


Al mismo tiempo al ser código externo a la aplicación puede ser alterado sin que exista siempre la necesidad de modificar el código de la misma. Al ser objetos de la base de datos se hallan sujetos a los esquemas de seguridad determinados por el administrador de la misma: Existen diversas clases de procedimientos almacenados, entre los que se encuentra los procedimientos almacenados del sistema, que sirven de herramientas para la realización de distintas tareas de administración.

Un procedimiento almacenado se crea con la sentencia CREATE PROCEDURE, que debe ser la única dentro de un mismo bat ch. La creación de un procedimiento almacenado puede ser realizada bien desde el ISQL_W, bien desde la opción Manage.Stored Procedures del Enterprise Manager, o bien desde la propia ventana donde se muestran los objetos de la base de datos, en el grupo correspondiente a los procedimientos almacenados, dentro de esta última herramienta. La sintaxis de dicha instrucción es básicamente la siguiente: CREATE PROCEDURE Nombre _del _ procedimiento [Lista_de_parámetros] AS (Sentencias SQL) [RETURN [Valor]]

Donde: Nombre _ del _ procedimiento Identificador. Que determina el nombre asignado al procedimiento y que debe cumplir con la regla de definición de identificadores establecida en MSSQL Server. Lista _ de _ parámetros.

148


Parámetros definidos en el procedimiento con la siguiente sintaxis:@nombre_var El símbolo @ es necesario no sólo en la declaración sino que forma parte del propio nombre. Dicha lista puede contener un máximo de 255 parámetros. Sentencias _SQL Como se explicó anteriormente, el cuerpo del procedimiento puede estar compuesto de cualquier tipo de instrucción SQL, a excepción de las siguientes: CREATE VIEW CREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER

Entre las instrucciones que puede contener, está la llamada a otros procedimientos almacenados, los cuales podrán acceder a los objetos pertenecientes al llamador, exceptuando las tablas temporales creadas por el mismo.

RETURN [Valor]

Un procedimiento almacenado puede devolver un valor de retorno de tipo integer, no nulo, que puede ser rescatado por el código llamador para tener conocimiento del resultado del proceso de dicho procedimiento. Los valores -1 al -99 están reservados por el sistema, así como el 0 que se interpreta como “finalizado con éxito”.

149


Si no se proporciona un código definido por el usuario, se utilizan los del sistema. De la misma forma los definidos por el usuario tiene prioridad sobre los definidos por el sistema. En caso de producirse varios errores a lo largo de la ejecución del mismo procedimiento, se devuelve el código cuyo valor absoluto es mayor. Algunos

ejemplos de códigos y sus significados son los siguientes: -2 Error de tipo de datos -4 Error de Permisos -5 Error de Sintaxis -13 Base de Datos Corrupta

Llamadas a procedimientos almacenados La sintaxis de la llamada a un procedimiento almacenado, depende de como se halla creado dicho procedimiento, por lo que en cada uno de los ejemplos que siguen, se especifica la llamada al mismo, poniéndose de manifiesto dicha sintaxis en cada caso particular.

Ejemplos Procedimiento 1 Es un procedimiento simple, sin parámetros que devuelve un conjunto de filas que cumplen siempre la misma condición.

CREATE PROCEDURE prod_1 AS SELECT * FROM CLIENTES Llamada: EXECUTE prod_1

150


Procedimiento 2 Es un procedimiento que recibe dos parámetros de entrada, correspondientes a un rango de códigos de clientes, y devuelve el conjunto de filas de la tabla CLIENT ES cuyo código se encuentra en el rango determinado por los parámetros.

CREATE PROCEDURE prod_2 @p_CodIni CHAR (6), @p_CodFin CHAR (6) AS SELECT * FROM CLIENTE WHERE IDCLIENTE BETWEEN @p_CodIni AND @p_CodFi Llamada: EXECUTE prod_2 ‘000100’, ‘000500’

Procedimiento 3 Es un procedimiento que recibe un parámetro de entrada y uno de salida. El parámetro de entrada corresponde a un código de tipo de producto y el de salida, el número de productos existentes, que corresponden a dicho tipo.

CREATE PROCEDURE prod_3 @p_CodTipProd CHAR (3), @p_NumProductos SMALLINT OUTPUT AS SELECT @p_NumProductos = COUNT(*) FROM PRODUCTOS WHERE IDTIPROD = @p_CodTipProd Llamada: DECLARE @p_parmsal SMALLINT EXECUTE prod_3 ‘KBD’, @p_parmsal OUTPUT

151


Procedimiento 4 Es un procedimiento almacenado que realiza inserciones en una tabla, con los valores devueltos por uns ubquer y realizado sobre otra tabla que tiene la misma estructura. El valor devuelto por el procedimiento almacenado, es el número de filas insertadas.

CREATE PROCEDURE prod_4 AS INSERT TIPSOPPRU SELECT * FROM TIPSOPORTE RETURN @@ROWCOUNT Llamada: DECLARE @p_retorno INTEGER EXECUTE @p_retorno = prod_4

Ejemplo de Procedimientos Almacenados Suponiendo que hay una tabla Artículos realizar los siguientes procedimientos

1.- Grabar Artículo Create Procedure GrabaArti @Cod char(5),@nom varchar(30),@uni char(4),@precio numeric(10,2),@stock int as Insert Into Articulos values(@cod,@nom,@uni,@precio,@stock) GO

2.- Editar Artículo Create Procedure EditaArti @Cod char(5),@nom varchar(30),@uni char(4),@precio numeric(10,2),@stock int as Update

Articulos

set

art_nombre=@nom,art_unidad=@uni,art_precio=@precio,art_stock=@stock where art_codigo=@cod GO

152


3.- Eliminar Artículo Create Procedure BorraArti @Cod char(5) as delete from Articulos where art_codigo=@cod GO

4.- Listar Artículo Create Procedure ListaArti as Select * from articulos GO

5.- Nuevo Artículo Create Procedure NuevoArti @Cod char(5) output as declare @auxi char(5) select @auxi = max(art_codigo) from articulos select @cod = 'A' + right(str(convert(int,right(@auxi,4)) + 10001),4)

153


TEMA 4

Analizar y definir Triggers.

154


Manejo de Errores

Raiserror .- Esta función permite establecer mensajes de errores en el SQL-Server Sintaxis: Raiserror (Msg, Nivel ,Estado)

Msg .- Es el mensaje que se quiere mostrar

Nivel.- Se trata del nivel de gravedad definido por el usuario que se asocia con este mensaje. Todos los usuarios pueden utilizar los niveles de gravedad de 0 a 18. Sólo los miembros de la función fija de servidor sysadmin pueden utilizar los niveles de gravedad de 19 a 25.

Estado.- Es un entero arbitrario entre 1 y 127 que representa información acerca del estado de llamada del error. Un valor negativo de Estado pasa a tener un valor predeterminado de 1.

Ejemplo: RaisError ('Este es es un mensaje con nivel 0 y estado 1', 0,1)

Sysmessages .- Es una tabla que se encuentra en la base de datos Master que contienes todos los mensajes disponibles Ejemplo: select * from sysmessages (donde el campo error es el numero de error, Severity el nivel, etc.)

SP _ add Message.- Agrega un nuevo mensaje de error a la tabla sysmessages Ejemplo: Sp _ addmessage 50001, 16, ‘Este es el mensaje 50001’ Una vez hecho esto el mensaje se adiciona a la tabla y para llamarlo seria así: RAISERROR (50001, 16,1)

155


sp_dropmessage .- Elimina un mensaje Ejemplo: sp_dropmessage 50001 donde 50001 es el número de mensaje que se quiere eliminar (Los mensajes se pueden añadir a partir del 50001)

@@Rowcount.- Saca la cantidad de Filas afectadas en la última instrucción SQL.

Triggers Los triggers son procedimientos internos que se ejecutan cada vez que se haga un Insert, Update o Delete, Internamente se crea una tabla temporal Inserted cuando se agrega, Deleted cuando se elimina y ambas cuando se actualiza. En el caso de una actualización se crean las dos tablas Inserted y Deleted en la tabla Inserted va los datos a insertar en la actualización y en Deleted los datos que van a ser sustituidos.

Sintaxis: Create Trigger Nombre Trigger On Table For Insert, update, Delete As Donde Tabla es la tabla donde se va a crear el Trigger y en For va cuando se va a ejecutar el Trigger (Si fuera en los 3 casos se pone los 3 Insert,Update y Deleted y solo se separan por una coma)

Ejemplos de Triggers: Crear la Siguiente Base de Datos. USE master go CREATE DATABASE ESTUDIOS GO USE ESTUDIOS GO -- Creación de las Tablas.

156


Create Table Carreras (codcar int not null primary key, nomcar varchar(30) ) GO

Create Table Alumnos (codalu char(7) not null primary key, nomalu varchar(30), codcar int ) GO

Create Table Notas (codalu char(7), codcur char(5), pp int, ep int, ef int ) GO

Create Table Cursos (codcur char(5) not null primary key, nomcur varchar(25) ) go

Create Table LogCursos (codcur char(5), nomcur varchar(25), Fecha Datetime, Usuario Varchar(10)) Go

157


Con la base de Datos Estudio crear los siguientes Triggers. --Creacion de Triggers

--1.- Mostrar un Mensaje Cuando se haga un Insert,Update o Delete Create Trigger TR1 on Cursos For Insert,Update,Delete as RaisError('Se ha afectado %d Fila',0,1,@@Rowcount) --Aqui el Nivel es 0 y el Estado 1 Al haber %d se le pasa el valor de una Variable en este Caso @@Rowcount GO

--2.- No permitir que se agregue un Curso con el Mismo Nombre Create Trigger TR2 on Cursos For Insert as Declare @nom varchar(25) Select @nom = nomcur from inserted If (Select count(*) from cursos where nomcur=@nom)>1 Bejín RaisError('Nombre del Curso ya existe',0,1) Rollback Transaction End Go

--3.- Crear un Trigger que no permita Modificar el Nombre de Un alumno Create Trigger TR3 on Alumnos For Update as If Update(Nomalu) Begin Print 'No se puede Modificar el Nombre del Alumno ' Rollback Transaction End Go

158


--4.- Crear un trigger que no permita Modificar el campo pp con una nota menor a la que ya tiene Create Trigger TR4 on Notas For Update as Declare @n1 int,@n2 int Select @n1 = pp from deleted Select @n2 = pp from inserted if Update(pp) Begin if @n2 < @n1 Begin --Print 'No se puede Modificar la nota ' + convert(varchar(2),@n1) + ' por la nota ' + convert(varchar(2),@n2) --Print 'No se puede Modificar la nota ' + cast(@n1 as varchar(2)) + ' por la nota ' + cast(@n2 as varchar(2))

RaisError('No se puede Modificar la nota %d por la nota %d por ser menor',0,1,@n1,@n2) Rollback Transaction End End Go

--5.- Crear un trigger que no permita eliminar Una Carrera que este en la tabla alumnos Create Trigger TR5 on Carreras For Delete as Declare @xcod int Select @xcod = codcar from deleted if Exists(select * from Alumnos where codcar=@xcod) Begin Print 'No se puede Eliminar la carrera' Rollback Transaction End Go

159


--6.- Crear Un Trigger que Al eliminar Un curso elimine en Cascada Las Notas de esos cursos. Create Trigger TR6 on Cursos For Delete as Declare @xCodcur VARCHAR(5) Select @xCodCur = Codcur from deleted Delete from Notas where Codcur = @XcodCur Go

--7.- Crear un Trigger a la tabla Cursos que cuando se modifique un registro se guarden los datos a Modificar en una tabla --Para este ejercicio debemos crear una tabla Idéntica a la Tabla Cursos la cual llamaremos LogCursos Create Trigger TR7 on Cursos For Update as Declare @xCod VARCHAR(5) Declare @xNom varchar(25) Select @xCod = codcur from deleted Select @xNom = NomCur from deleted INSERT INTO LogCursos values(@xcod,@xnom,getdate(),'SUPERVISOR')

160



 TUTORIAL SQL AVANZADO http://sql.1keydata.com/es/sql-avanzado.php

 PROCEDIMIENTOS ALMACENADOS http://www.devjoker.com/contenidos/catss/238/Procedimientos-almacenados-enTransact-SQL.aspx

 PROCEDIMIENTOS ALMACENADOS Y TRIGGERS http://es.scribd.com/doc/10325656/Procedimientos-Almacenados-y-Triggers-en-SQLServer

Actividades y Ejercicios Ingresa al link “SQL Avanzado” lee atentamente las indicaciones, desarróllalo y envíalo por el mismo medio. Trabajar con la tabla ALUMNO creada en la actividad de la Unidad 3 1. Crear un procedimiento almacenado que permita grabar datos a la Tabla ALUMNO.

2. Crear un procedimiento almacenado que permita actualizar datos a la tabla ALUMNO.

3. Crear un trigger que no permita grabar un nombre y apellido que ya exista en la tabla ALUMNO.

4. Crear un trigger que no permita grabar si en el campo de fecha de

161


Autoevaluaciones

1.- ¿Sentencia que sirve para comparar inexactamente? a. b. c. d. e.

Like Between Compare In On

2.- ¿Sentencia que sirve para ordenar los datos? a. b. c. d. e.

Sort Group By Having Order by Compare

3.- ¿Cuál es la sentencia que une a las tablas? a. b. c. d. e.

Relation Ship Select inner Join insert int

4.- ¿Cómo se pone una condición a un campo que usa SUM, Max, etc.? a. b. c. d. e.

Where Group By Having Insert Delete

5.- Sentencia que sirve para agrupar datos a. b. c. d. e.

Group Group By Having Order by Delete

6.- ¿Qué sentencia permite hacer una condición dentro de una sentencia SELECT? a. b. c. d. e.

IF CASE Select Case For Order by

162


7.- ¿Qué sentencia SQL permite pasar y devolver parámetros o argumentos? a. b. c. d. e.

Procedure Query Sum Create Delete

8.- ¿Cuál es la sentencia para ejecutar un procedimiento almacenado? a. b. c. d. e.

Call Execute Ex Connect Listar

9.- ¿Cuáles son los procedimientos que se ejecutan cuando se presenta un determinado evento? a. b. c. d. e.

Parámetros TRIGGERS Procedures Funciones Sentencias

10.- ¿Para mostrar un mensaje cuando se ejecuta un triggers la sentencia es? a. msgbox b. messagebox c. Raiserror d. msgError e. msq

163


Resumen

Las consultas entre tablas son diversas y hay veces tenemos la necesidad de mostrar datos que se encuentran en diferentes tablas. Para eso el SQL maneja la clausula INNER JOIN que es la que permite unir varias tablas cabe recordar que si se utiliza INNER JOIN se tendrá que utilizar también la clausula ON que es la que relaciona los campos en común. Se debe de tener en cuenta los campos que permiten esta unión para que con ellos se pueda hacer la relación entre las tablas. Ejemplo: Mostrar el código del alumno, nombre del alumno y nombre de la carrera. SELECT A.CODALU, A.NOMALU, C.NOMCAR FROM ALUMNOS AS A INNER JOIN CARRERAS AS C ON A.CODCAR=C.CODCAR El SQL permite hacer sub consultas que quiere decir una consulta dentro de otra esto se considera una consulta avanzada. Se tiene funciones como GROUP BY, HAVING, ORDER BY, etc. que permitirá hacer este tipo de consultas Un procedimiento almacenado es un objeto perteneciente a una base de datos, que contiene un conjunto de instrucciones SQL, tanto de consulta, como de manipulación de datos, como de control de la secuencia del programa, asociados a un nombre, y que son ejecutados en conjunto. Puede contener parámetros tanto de entrada como de salida (parámetros pasados por referencia), así como devolver un valor de retorno. Los triggers son procedimientos internos que se ejecutan cada vez que se haga un Insert, Update o Delete, Internamente se crea una tabla temporal Inserted cuando se agrega, Deleted cuando se elimina y ambas cuando se actualiza. En el caso de una actualización se crean las dos tablas Inserted y Deleted en la tabla Inserted va los datos a insertar en la actualización y en Deleted los datos que van a ser sustituidos.

164


Glosario

 MODELOS DE DATOS: También llamados modelos lógicos, se han clasificado en dos

grandes grupos debido al tratamiento de los datos: Basados en Objetos y basados en Registros.  ADMINISTRACIÓN DE DATOS: Sin los datos y la capacidad para procesarlos, una

organización no podría completar con éxito la mayor parte de sus actividades de negocios.  MODELOS DE BASE DE DATOS: En la mayor parte de las bases de datos, la

estructura de las relaciones sigue uno de tres modelos lógicos de base de datos. Jerárquico, de red y relacional.  BASES DE DATOS DISTRIBUIDAS: Un procesamiento distribuido incluye colocar

unidades de procesamiento en diferentes ubicaciones y vincularlas a través de equipos de telecomunicaciones.  DISPONIBILIDAD: Los sistemas de bases de datos deben asegurar la disponibilidad

de los datos a aquellos usuario que tienen derecho a ello por lo que proporcionan mecanismos que permiten recuperar la base de datos contra fallos lógicos o físicos que destruyan los datos en todo o en parte.  INTEGRIDAD: El objetivo en cuanto a la integridad es proteger la base de datos contra

operaciones que introduzcan inconsistencias en los datos, por eso hablamos de integridad en el sentido de corrección, validez o precisión de los datos de la base.  CAMPO: Es la unidad más pequeña a la cual uno puede referirse en un programa.

Desde el punto de vista del programador representa una característica de un individuo u objeto.  DATO: Es una información que refleja el valor de una característica de un objeto real,

sea concreto o abstracto, o imaginario. Debe permanecer en el tiempo, debe tener un significado y debe ser manipulable mediante operadores.

165


Fuentes de Información BIBLIOGRÁFICAS:

PÉREZ LOPEZ, CÉSAR MYSQL PARA WINDOWS Y LINUX Apress 20 04 KRUGLINSKI, DAVID J SISTEMAS DE ADMINISTRACIÓN DE BASE DE DATOS Editorial MxGraw – Hill 2004 DATE, C.J. INTRODUCCIÓN A LOS SISTEMAS DE BASES DE DATOS. 7A. ED. Prentice Hall 2001 MARCO A. GUEVARA INJOQUE CÉSAR R. FLORES NAZARIO TUTORIAL ERWIN

ELECTRONICAS: Tutorial SQL Avanzado

http://sql.1keydata.com/es/sql-avanzado.php

ERwin

http://es.scribd.com/doc/3075477/Manual-de-Usuario-de-Erwin

Normalización de la Base de Datos http://mygnet.net/articulos/mysql/182

Introducción a la base de datos

http://www.desarrolloweb.com/articulos/introduccion-base-datos.html

VIDEOS http://www.youtube.com/watch?v=4XMp9EubJTM http://www.youtube.com/watch?v=R5UVM9Bxod4&feature=related http://www.youtube.com/watch?v=iaYfDeijJng&feature=related

166

LIBRO-Diseño de Base de Datos

Recommend Documents