• Author / Uploaded
• Maurizio Sh

• Categories
• Base de datos relacional
• Bases de datos
• Datos
• Recuperación de información
• Tecnologías de la información

Citation preview

SERVICIO NACIONAL DE ADIESTRAMIENTO EN TRABAJO INDUSTRIAL

COMPUTACIÓN E INFORMÁTICA

MANUAL DE APRENDIZAJE

FUNDAMENTOS DE BASE DE DATOS

CÓDIGO: 89001826 Profesional Técnico

CONTENIDO

TAREA-DENOMINACIÓN DISEÑA MODELOS DE DATOS, BASES DE DATOS Y TABLAS EN SQL. 1.1. Crear modelos de datos. 1.2. Crear modelo E-R y la base de datos. 1.3. Crear tablas y su estructura. 1.4. Aplicar SQL para crear base de datos y tablas. 1.5. Crear taller de modelo de datos y creación de base de datos. Marco Teórico. II. IMPLEMENTA TRANSACT SQL. 2.1. Implementar tendencias DDL. 2.2. Crear y optimizar con DML. 2.3. Crear variables y estructura de control. 2.4. Implementar funciones SQL. Marco Teórico. III. IMPLEMENTA BASE DE DATOS Y TABLAS EN MYSQL. 3.1. Diseñar modela relacional en MYSQL. 3.2. Crear operaciones con la base de datos en MYSQL. 3.3. Implementar operaciones utilizando DML. 3.4. Crear taller de implementación de base de datos con MYSQL. Marco Teórico. IV. IMPLEMENTA PROGRAMACIÓN CON T-SQL. 4.1. Implementar Store Procedure Básico. 4.2. Implementar Store Procedure con varias tablas. 4.3. Crear disipadores básicos. 4.4. Crear disipadores avanzados. V. IMPLEMENTAR OPERACIONES AVANZADAS CON SQL. 5.1. Crear operaciones utilizando el agente SQL. 5.2. Crear Subquery con SQL. 5.3. Crear operación con SQL Azure. Marco Teórico. I.

PÁG N° 5 5 10 19 32 36 39 44 44 46 51 53 65 75 75 78 81 88 90 103 103 107 113 119 128 128 134 136 140

FUNDAMENTOS DE BASE DE DATOS I.

DISEÑA MODELO DE DATOS, BASE DE DATOS Y TABLAS EN SQL.

El participante al término de esta Tarea podrá diseñar, crear modelos de datos y base de datos aplicando sql server. Esta Tarea deberá desarrollar las siguientes operaciones. - Crear modelos de datos. - Crear modelo E-R y la base de datos. - Crear tablas y su estructura. - Aplicar SQL para Crear Base de datos y Tablas. - Crear taller de Modelo de Datos y Creacion de Base de Datos. 1.1. CREAR MODELOS DE DATOS. Un modelo de datos es un sistema formal y abstracto que permite describir los datos de acuerdo con reglas y convenios predefinidos o podrias decir que es un conjunto de conceptos que permiten describir, a distintos niveles de abstracción, la estructura de una base de datos. Existen diferentes tipos de modelos dentro de los mas utilizados se encuetran los modelos entidad relación el cual realizaras algunos ejemplos preliminares. Ejemplo 1. CASO ALMACÉN. 1. Descripción del proceso: Se trata de una base de datos sobre los suministros que

ingresan los proveedores hacia un determinado almacén, para lo cual se debe llevar un control de los suministros y de sus cuentas contables. 2. Identificar conjunto de entidades:

A primera vista existen tres conjuntos de entidades: PROVEEDOR

SUMINISTRO

CUENTA CONTABLE

3. Identificar conjunto de Relaciones: Cada proveedor ingresa uno o más suministros, y

estos pueden ser vendidos por uno o más proveedores, dándose una relación de uno a muchos. Los suministros pertenecen a una determinada cuenta contable, y esta contendrá múltiples suministros dándose una relación de muchos a uno. 4. Trazar primer diagrama:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

5

FUNDAMENTOS DE BASE DE DATOS Utilizando la herramienta sería:

5. Identificar atributos. Se deben identificar los atributos para cdda conjunto de entidades:

Entorno del programa

Paso 1.

Paso 2:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

6

FUNDAMENTOS DE BASE DE DATOS

Paso 3. Crear igual para suministro y cuenta contable.

Paso 4. Identificar los tipos de relaciones o cardinalidad a aplicar.

Proceder a relacionar.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

7

FUNDAMENTOS DE BASE DE DATOS

6. Seleccionar claves principales: Proveedor: Código o RUC. Suministro: Código Suministro. Cuenta Contable: Número de Cuenta. Ejemplo 2. CASO BIBLIOTECA. Paso 1. 1. Descripción del proceso: Se trata de una base de datos que debe almacenar información sobre el préstamo de libros a los alumnos de una determinada escuela, los cuales son escritos por varios autores y pertenecen a una determinada editorial, además se refieren a una determinada especialidad. 2. Identificar Conjunto de entidades: A primera vista existen 5 conjuntos de entidades: ALUMNO

LIBRO

AUTOR

EDITORIAL

ESPECIALIDAD

3. Identificar Conjunto de relaciones. • Un alumno puede prestar muchos libros, a su vez que estos libros pueden ser prestados por muchos alumnos: Muchos a Muchos. • Cada libro pertenece a una especialidad, y pueden existir muchos libros de esta especialidad: Muchos a Uno. • Un libro es escrito por uno o varios autores, y estos autores pueden escribir uno o mas libros: Muchos a Muchos Un libro pertenece a una editorial, y en esa editorial se pueden imprimir muchos libros: Muchos a Uno 4. Trazar primer diagrama.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

8

FUNDAMENTOS DE BASE DE DATOS

5. Identificar Atributos. Se deben identificar los atributos para cada conjunto de entidades

6. -

Seleccionar claves principales: Libro: Código o Título. Alumno: CodAlumno Especialidad CodEspecialidad. Autor: CodAutor. Editorial: CodEditorial.

Utiizando la herramienta siga las indicaciones del instructor. Ejemplo 3. Elaborar el siguiente modelo de datos para un sistema de venta utilizando la herramienta dbdesigner siguiendo las indicaciones del instructor:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

9

FUNDAMENTOS DE BASE DE DATOS

Ejemplo 4. Elaboracion de un modelo físico utilizando el Diagrama de base de datos de SQL server

1.2. CREAR MODELO E-R Y LA BASE DE DATOS. Los diagramas o modelos entidad-relación (denominado por su sigla, ERD “Diagram Entity relationship”) son una herramienta para el modelado de datos de un sistema de información. Estos modelos expresan entidades relevantes para un sistema de información, sus inter-relaciones y propiedades veamos algunos ejercicios aplicativos para entender su construcción.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

10

FUNDAMENTOS DE BASE DE DATOS

Conceptos preliminares. Entidades (clases)

Objeto: SENATI.

Entidad (Clase): Instituto Objeto: TECSUP.

REPRESENTACION DE UNA ENTIDAD (CLASE). Para poder representar una entidad o clases se va a realizar lo siguiente:

Ejemplo 1:

Ejemplo 2: Relacionar: Una relación es una asociación entre dos o más entidades (Clases).

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

11

FUNDAMENTOS DE BASE DE DATOS

Ejemplo 3. Relaciones entre entidades:

Ejemplo 4

CASO APLICATIVO 1. En una hoja y papel realice los siguientes diagramas: - Identificar las entidades. - Identificar los atributos por entidades. - Relacionar cada una de las entidades.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

12

FUNDAMENTOS DE BASE DE DATOS TITULO: ARTÍCULOS Y ENCARGOS. Diseñar el diagrama entidad relación para crear una base de datos en una pequeña empresa esta debe contener información acerca de clientes, artículos y Pedidos. Hasta el momento se registran los siguientes datos en documentos varios: - Para cada cliente: Número de cliente (único), Direcciones de envío (varias por cliente), Saldo, Límite de Crédito (depende del cliente, pero en ningún caso debe superar los 3.000.000 soles), Descuento. - Para cada artículo: Número de artículo (único), Fábricas que lo distribuyen, Existencias de ese artículo en cada fábrica, Descripción del artículo. - Para cada pedido: Cada pedido tiene una cabecera y el cuerpo del pedido. La cabecera está formada por el número de cliente, dirección de envío y fecha del pedido. El cuerpo del pedido son varias líneas, en cada línea se especifican el número del artículo pedido y la cantidad. Además, se ha determinado que se debe almacenar la información de las fábricas. Sin embargo, dado el uso de distribuidores, se usará: Número de la fábrica (único) y Teléfono de contacto. Y se desean ver cuántos artículos (en total) provee la fábrica. También, por información estratégica, se podría incluir Información de fábricas alternativas respecto de las que ya fabrican artículos para esta empresa. Nota: Una dirección se entenderá como Nº, Calle, Comuna y Ciudad. Una fecha incluye hora. CASO APLICATIVO 2: Sistema de ventas. Le contratan para hacer una Base de Datos que permita apoyar la gestión de un sistema de ventas. La empresa Necesita llevar un control de proveedores, clientes, productos y ventas. Un proveedor tiene un RUT, nombre, dirección, teléfono y página web. Un cliente también tiene RUT, Nombre, dirección, pero puede tener varios teléfonos de contacto. La dirección se entiende por calle, Número, comuna y ciudad. Un producto tiene un id único, nombre, precio actual, stock y nombre del proveedor. Además, se organizan en categorías, y cada producto va sólo en una categoría. Una categoría tiene id, nombre y descripción. Por razones de contabilidad, se debe registrar la información de cada venta con un id, fecha, cliente, descuento y monto final. Además, se debe guardar el precio al momento de la venta, la cantidad vendida y el monto total por el producto. CASO APLICATIVO 3: Sistema de vuelos. Diseñar el diagrama E/R para un sistema de control de vuelos adaptado a las siguientes reglas de gestión (indicar las entidades, interrelaciones, etc., que se deducen de cada una de las reglas): a. De cada aeropuerto se conoce su código, nombre, ciudad y país. b. En cada aeropuerto pueden tomar tierra diversos modelos de aviones (el modelo de un avión determina su capacidad, es decir, el número de plazas. c. En cada aeropuerto existe una colección de programas de vuelo. En cada programa de vuelo se indica el número de vuelo, línea aérea y días de la semana en que existe dicho vuelo. d. Cada programa de vuelo despega de un aeropuerto y aterriza en otro. e. Los números de vuelo son únicos para todo el mundo.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

13

FUNDAMENTOS DE BASE DE DATOS f. En cada aeropuerto hay múltiples aterrizajes y despegues. Todos los aeropuertos contemplados están en activo, es decir, tienen algún aterrizaje y algún despegue. g. Cada vuelo realizado pertenece a un cierto programa de vuelo. Para cada vuelo se quiere conocer su fecha, plazas vacías y el modelo de avión utilizado. h. Algunos programas de vuelo incorporan escalas técnicas intermedias entre los aeropuertos de salida y de llegada. Se entiende por escala técnica a un aterrizaje y despegue consecutivos sin altas ó bajas de pasajeros. i. De cada vuelo se quieren conocer las escalas técnicas ordenadas asignándole a cada una un número de orden. Por ejemplo, el programa de vuelo 555 de Iberia con vuelos los lunes y jueves despega de BarajasMadrid-España y aterriza en CaudellSydney-Australia teniendo las siguientes escalas técnicas: 1- Los Pradiños-Sao Paulo-Brasil, 2-El Emperador-Santiago-Chile y 3-Saint KittsAuckland-Nueva Zelanda. ¿Qué cambios se producirán en el caso anterior si en las escalas pudiesen bajar o subir pasajeros? Explicar cómo se podría representar esta nueva situación. Caso domiciliario: Se desea crear un sitio web con información referente a las películas en cartel en las salas de un dudoso Cine cercano a la plaza mayor del centro de Lima. De cada película, se almacena una ficha con su título de distribución, su título original, su género, el idioma origina, si tiene subtítulos en español o no, los paises de origen, el año de la producción, la url del sitio web de la película, la duración (en horas y minutos), la calificación (Apta todo público, +9 años, +15 años, +18 años), fecha de estreno en Santiago, un resumen y un identificador de la película. De cada película interesa conocer la lista de directores y el reparto, es decir para cada actor que trabaja, el nombre de todos los personajes que interpreta. Además, interesa disponer de información sobre los directores y actores que trabajan en cada película. De ambos, se conoce su nombre (que lo identifica) y su nacionalidad. Además, se desea conocer la cantidad de películas en las que dirigieron o actuaron. Tener en cuenta que hay personas que cumplen los dos roles. Los cines pueden tener más de una sala y cada semana cada uno de los cines envía la cartelera para dicha semana, indicando de detalle de las funciones. Para cada función se conoce el día de la semana y la hora de comienzo, y obviamente la sala y la película que exhibe. De cada sala se sabe el nombre, un número que la identifica dentro del cine y la cantidad de butacas que posee. De cada cine se conoce el nombre que lo identifica, su dirección y teléfono para consultas. Algunos cines cuentan con promociones. Estas promociones dependen de la función. (Ej. de lunes a jueves antes de las 18:00 horas, 50% de descuento en la sala tal del cine ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

14

FUNDAMENTOS DE BASE DE DATOS tal para la película cual...La función del lunes a las 14:00 para la película tal en la sala cual, no se cobra a los escolares con túnica...). De cada promoción se conoce una descripción y el descuento que aplica. Además del resumen de la película que se incluye en la ficha interesa mostrar la opinión de las personas que vieron la película. De cada opinión se conoce el nombre de la persona que la realiza, su edad, le fecha en que registró su opinión, la calificación que le dio a la película (Obra Maestra, Muy Buena, Buena, Regular, Mala) y el comentario propiamente dicho. A cada opinión se le asigna un número que la identifica respecto de la película sobre la cual opina. CASO APLICATIVO 4. UTILIZANDO SQL SERVER DISEÑAR EL MODELO RELACIONAL DE LOS SIGUIENTES CASOS:

BASE DE DATOS: MODELO RELACIONAL

CASO1. Convertir el siguiente diagrama E-R en un modelo relacional. Establecer los atributos claves, definir los campos y migrar su resultado a SQL SERVER, donde la base de datos tendrá por nombre: GestiónVentas

Distrito Calle

Provincia

No

Depatam IdArticulo

NoCliente

ARTICULO

N

Saldo

N

Cantidad

CLIENTE

Límite de Credito

Detalle

Direcciones De Envió

Existencias

1 Se Incluye

Descuento

Fabrica

Es Hecho

N N

PEDIDO

FABRICA

N

NoArticulos Provistos

NoPedido IdFabrica

Telfonos Fecha

Monto

mail

CASO 2. La cadena de Videos Glob Búster, ha decidido, para mejorar su servicio, emplear una base de datos para almacenar la información referente a las películas que ofrece en alquiler. Esta información es la siguiente: - Una película se caracteriza por su titulo, nacionalidad, productora y fecha. ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

15

FUNDAMENTOS DE BASE DE DATOS - En una película pueden participar varios actores (nombre, nacionalidad, sexo) algunos de ellos como actores principales. - Una película esta dirigida por un director (nombre, nacionalidad). - De cada película se dispone de uno o varios ejemplares diferenciados por un número de ejemplar y caracterizados por su estado de conservación. - Un ejemplar se pude encontrar alquilado a un cliente (DNI, nombre, dirección, teléfonos). Se desea almacenar la fecha de comienzo del alquiler y de la devolución. - Cada socio puede tener alquilados, en un momento dado, 4 ejemplares como máximo. - Un socio tiene que ser avalado por otro socio que responda de él en caso de tener problemas de alquiler. Elaborar el modelo lógico y convertirlo en una base de datos de SQL SERVER con el nombre: VIDEOS. CASO 3. Convertir el siguiente diagrama E-R en un modelo relacional. Establecer los atributos claves, definir los campos y migrar su resultado a SQL SERVER, donde la base de datos tendrá por nombre: CadenaFarmaceutica - Se desea mantener una base de datos para una cadena de farmacias distribuida en diferentes ciudades. - Cada farmacia tiene sus empleados propios y un farmacéutico. Por cada ciudad existe un único farmacéutico; esto es, si en una ciudad hubiera más de una farmacia, el mismo farmacéutico estaría afectado a todas las farmacias de esa ciudad. - Cada farmacia tiene a su vez su stock de medicamentos. El mismo se mantiene por medicamento y presentación. Los medicamentos se organizan según la o las monodrogas que lo componen, su presentación (por ejemplo, ampollas de 5 unidades, jarabe de 100ml, inyecciones por 10 unidades, pomada 60gr, etc.), el laboratorio que lo comercializa, y su acción terapéutica (analgésico, antibiótico, etc.). Por cada medicamento se mantiene su precio y la cantidad en existencia del mismo. - El sistema deberá permitir consultar la base de datos de diferentes alternativas para medicamentos compuestos por una monodroga, medicamentos de un laboratorio, presentaciones de un medicamento, entre otras. - El siguiente modelo ER representa lo anterior:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

16

FUNDAMENTOS DE BASE DE DATOS

Monodroga N

Presentación

Contiene

N

Ciudad

1

N

Pertenece

Farmacia

N

N

Tiene Stock

N

1

Tiene

N

N

Medicamento N

Cantidad Sirve Para

Provee

N

Esta a cargo

Personal

Acción Terapéutica

1 N

Laboratorio

1

Farmacéutico

Propio

CASO 4. Convertir el siguiente diagrama E-R en un modelo relacional. Establecer los atributos claves, definir los campos y migrar el resultado a SQL SERVER, donde la base de datos tendrá por nombre: Facturacion

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

17

FUNDAMENTOS DE BASE DE DATOS Caso domiciliario: CASO 1. Un Banco desea almacenar información concerniente a todos sus clientes, así como los productos que puede ofrecer a cada uno de ellos. Del mismo modo, el banco tiene interés en invertir en empresas, por lo que desea conocer en todo momento la situación de dicha empresa para poder mejorar su política de inversiones. Puesto que dicho banco esta constituido como S.A., desea llevar un registro de todos los miembros de su consejo de administración (actuales y ex-miembros), así como de todas las reuniones ordinarias y extraordinarias que se realicen. Las decisiones de inversión saldrán como resultado de dichas reuniones, si como el lanzamiento de nuevos productos financieros. - De los clientes se desea conocer su nombre, apellidos, dirección, teléfonos, documento de identidad, fecha de nacimiento, y estarán identificados con un código interno. - De los productos que ofrece el banco se almacenará su descripción, inicio de operaciones, y estará identificado por un código. - De las empresas en las que el banco invierte se desea conocer su razón social, direcciones, teléfonos, y RUC. - Del consejo directivo, que se encarga de aprobar los nuevos productos e inversiones, se almacenara los nombres y apellidos de sus miembros y si este se encuentra vigente o no. Además, cada miembro del consejo tendrá un cargo el cual deberá permitir identificar la labor que desempeñan en el consejo. Del cargo se almacenará el nombre del cargo y las funciones propias del cargo. - De las reuniones que se realizan periódicamente y de las cuales se levanta un acta de los acuerdos firmados, se almacenara la fecha, si es extraordinaria u ordinaria y una descripción. Además, las reuniones serán identificas por un código En cuanto a las relaciones: - Es posible cada que cada cliente pueda escoger varios productos y cada producto puede ser ofrecido o varios clientes. - Los miembros del consejo solo pueden tener un cargo y a cada cargo puede pertenecer más de un miembro. - A cada reunión pueden asistir varios miembros del consejo de administración y cada miembro puede asistir a más de una reunión. - Las reuniones de consejo permitirán la oferta de mas de un nuevo producto, pero estos nuevos productos solo podrán ser ofrecidos mediante una reunión de consejo, además estas reuniones permitirán determinar en que una nueva empresa se va a invertir, y en consecuencia las decisiones de inversión sobre nuevas empresas solo podrán salir de una reunión. CASO 2. Una empresa de venta anticipada de entradas para espectáculos quiere informatizar su funcionamiento, para la cual se deberá elaborar una base de datos que almacene la información que se genera. El funcionamiento de la empresa es el siguiente:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

18

FUNDAMENTOS DE BASE DE DATOS -

-

-

-

-

Cuando un cliente llama por teléfono para comprar unas entradas, se comprueba si el cliente es nuevo, en cuyo caso se almacenará en la Base de Datos el nombre del cliente, su DNI y su teléfono. Además, se le asignará un código de cliente único. Existen diferentes locales de espectáculos que trabajan con esta empresa. La información de interés de dichos locales es el código de local, el nombre, la dirección, el teléfono y el aforo de dicho local. En los diferentes locales se dan distintos espectáculos. Hay que tener en cuenta que la empresa quiere tener almacenada la programación de espectáculos de toda la temporada por lo que un espectáculo podrá estar en varios locales a lo largo de la temporada y un local podrá tener también diferentes espectáculos durante la temporada. Es esencial saber las fechas de comienzo y fin de los espectáculos en cada local. De los espectáculos interesa saber el código de espectáculo, su nombre y el precio por entrada. Hay que tener en cuenta que el precio de un espectáculo puede variar según el local donde se produzca. Cuando el cliente realiza una compra de entradas se tendrá en cuenta lo siguiente: hay que almacenar la fecha para la que quiere las entradas, en que espectáculo y en cual local, así como el número de entradas que pide. Si un cliente habitual lleva compradas más de 20 entradas, se le realizará un descuento del 15 %. Por lo tanto, hay que almacenar todas las compras realizadas por un cliente. También hay que comprobar que quedan entradas disponibles. De las compras interesa saber el código de cliente, el número de entradas compradas, la fecha, el código del local y el código del espectáculo.

1.3. CREAR TABLAS Y SU ESTRUCTURA. Creacion de objetos en tiempo de diseño. Paso 1.

Paso 2.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

19

FUNDAMENTOS DE BASE DE DATOS

Paso 3. Creacion de la tabla.

Paso 4. Creación de la tabla artículos.

Creación de la tabla clientes:

Creación de la tabla Vendedores:

Creación de la tabla Facturas:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

20

FUNDAMENTOS DE BASE DE DATOS

RELACION DE TABLAS (MODELO RELACIONAL). Paso 1.

Paso 2.

Paso 3. Agregar un campo para relacionar la tabla facturas con artículos.

Quedará de la siguiente manera:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

21

FUNDAMENTOS DE BASE DE DATOS

OPERACIONES CON LA BASE DE DATOS. 1. Exportar. Paso 1. Crear una nueva base de datos que sirva de repositorio asigne el nombre suministros2 Paso 2.

Paso 3.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

22

FUNDAMENTOS DE BASE DE DATOS Paso 4. Seleccionar el nombre del servidor local.

Pegar el nombre en nombre de servidor y selecciona la base de datos de destino:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

23

FUNDAMENTOS DE BASE DE DATOS Paso 5. Seleccionar todas las tablas que desea migrar a la nueva base de datos.

Paso 6. Ahora para visualizar las tablas en la nueva base de datos sólo se tendrá que actualizar.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

24

FUNDAMENTOS DE BASE DE DATOS 2.

Crear Copia de seguridad de la base de datos suministros.

Paso 1.

Paso 2. La copia de seguridad automáticamente se genera con la extensión BKP.

Paso 3. Verificar la existencia del archivo BKP.

3.

Eliminacion de la base de datos.

Paso 1.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

25

FUNDAMENTOS DE BASE DE DATOS

Paso 2. Activar el casillero cerrar conexiones existentes.

4.

Restaurar una base de datos.

Paso 1.

Paso 2. Seleccionar dispositivo y presionar…

Paso 3.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

26

FUNDAMENTOS DE BASE DE DATOS

Paso 4. Aceptar.

5. Copiar la base de datos. Paso 1. Separar la base de datos.

Paso 2.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

27

FUNDAMENTOS DE BASE DE DATOS Nota: La base de datos suministros2 ya no se visualiza en el panel; por lo tanto, se podrá copiar en un usb para su portabilidad. Paso 3. En la siguiente ruta se encuentra la BD se deberá copiar los 2 archivos.

6.

Operación Adjuntar la base de datos.

Paso 1.

Paso 2. Click en el botón agregar. Paso 3. Seleccionar la base de datos con extensión MDF(maestro).

7.

Operación Generar script.

Paso 1.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

28

FUNDAMENTOS DE BASE DE DATOS Paso 2. Seleccionar las tablas.

Paso 3. Ruta predefinida donde se almacenará el archivo.

Paso 4. Se deberá mostrar la siguiente ventana de confirmación.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

29

FUNDAMENTOS DE BASE DE DATOS

Paso 5. Verificar la existencia y ejecutar el archivo.

Paso 6. Eliminar la base de datos y pegar todo el contenido. Selecionar y ejecutar. IMPORTATE: Crear la base de datos suministro2. USE [SUMINISTRO2] GO /****** Object: Table [dbo].[ARTICULOS] Script Date: 03/01/2017 17:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ARTICULOS]( [ARTCOD] [char](4) NOT NULL, [ARTDESCRI] [varchar](30) NULL, [ARTSTOCK] [int] NULL, [ARTPRECIO] [money] NULL, [ARTFECHA] [smalldatetime] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[CLIENTES] Script Date: 03/01/2017 17:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CLIENTES]( [CLICOD] [char](4) NOT NULL, [CLIAPE] [varchar](30) NULL, [CLINOM] [varchar](30) NULL, [CLIDIR] [varchar](30) NULL, [CLIDIS] [varchar](25) NULL, [CLITEL] [char](9) NULL, [CLIGEN] [char](1) NULL, [CLIFNAC] [smalldatetime] NULL ) ON [PRIMARY] ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

30

FUNDAMENTOS DE BASE DE DATOS GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[FACTURAS] Script Date: 03/01/2017 17:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[FACTURAS]( [FACOD] [char](5) NOT NULL, [FACFECHA] [smalldatetime] NULL, [CLICOD] [char](4) NOT NULL, [VENCOD] [char](4) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[sysdiagrams] Script Date: 03/01/2017 17:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[sysdiagrams]( [name] [nvarchar](128) NOT NULL, [principal_id] [int] NOT NULL, [diagram_id] [int] NOT NULL, [version] [int] NULL, [definition] [varbinary](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[VENDEDORES] Script Date: 03/01/2017 17:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[VENDEDORES]( [VENCOD] [char](4) NOT NULL, [VENAPE] [varchar](30) NULL, [VENNOM] [varchar](30) NULL, ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

31

FUNDAMENTOS DE BASE DE DATOS [VENDIR] [varchar](30) NULL, [VENDIS] [varchar](25) NULL, [VENTEL] [char](9) NULL, [VENFNAC] [smalldatetime] NULL, [VENGEN] [char](1) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO RESULTADO FINAL:

1.4. APLICAR SQL PARA CREAR BASE DE DATOS Y TABLAS. En esta operación usted realiza la creación de una base de datos por intermedio de código SQL tal como vera a continuación. 1. Creación de una base de datos. Comando create database nombre_bd Ejemplo CREATE DATABASE SUMINISTROS Creacion de una tabla Comando Create Table Nombre_tabla ( Nombre_Columna tipo_de_dato, Nombre_Columna2 tipo_de_dato, ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

32

FUNDAMENTOS DE BASE DE DATOS Nombre_Columna3 tipo_de_dato ) Ejemplos de creacion de tablas CREATE TABLE ARTICULOS ( ARTCOD char (4) PRIMARY KEY, ARTDESCRI varchar (30), ARTSTOCK int, ARTPRECIO money, ARTFECHA smalldatetime, ) CREATE TABLE CLIENTES( CLICOD char (4) PRIMARY KEY, CLIAPE varchar (30), CLINOM varchar (30), CLIDIR varchar (30), CLIDIS varchar (25), CLITEL char (9), CLIGEN char (1), CLIFNAC smalldatetime ) CREATE TABLE VENDEDORES( VENCOD char (4) PRIMARY KEY, VENAPE varchar (30), VENNOM varchar (30), VENDIR varchar (30), VENDIS varchar (25), VENTEL char (9), VENFNAC smalldatetime, VENGEN char (1) ) CREATE TABLE FACTURAS ( FACOD char (5) NOT NULL PRIMARY KEY , FACFECHA smalldatetime, CLICOD char (4) NOT NULL REFERENCES CLIENTES, VENCOD char (4) NOT NULL REFERENCES VENDEDORES ) Nota El termino REFERENCES es para establecer una relación de tablas directa al momento de construir la tabla. Relacion de tablas --SINTAXIS ALTER TABLE TABLA_FKAdd foreign key(campo_FK) REFERENCES TABLA_PK(campo_Pk) Ejemplo 1. Relacionando la tabla facturas con la tabla vendedores.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

33

FUNDAMENTOS DE BASE DE DATOS ALTER TABLE FACTURAS Add foreign key(vencod) REFERENCES VENDEDORES(vencod) Ejemplo 2. Relacionando la tabla facturas con la tabla clientes. ALTER TABLE FACTURAS Add foreign key(clicod) REFERENCES CLIENTES(clicod) CASO APLICATIVO 1. Utilizando código realizar.

   

 

CASO APLICATIVO 2.  

 

 

Creación de la base de datos personalizada. Ejemplo 1: CREATE DATABASE SistFact ON (NAME = SistFact_dat, FILENAME = 'C:\BD\SistFact_dat.mdf', --ruta del archivo logico SIZE = 10, --tamaño de la base de datos Logica MAXSIZE = 50, --tamaño maximo FILEGROWTH = 5 ) --porcentaje de crecimiento LOG ON (NAME = 'SistFact_log',--ruta del archivo log

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

34

FUNDAMENTOS DE BASE DE DATOS FILENAME = 'C:\BD\SistFact_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB)

Deberá mostrar comando completados con éxito. Comprobando:

Creación de grupo de archivos:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

35

FUNDAMENTOS DE BASE DE DATOS Comprobando la ubicación del nuevo archivo en el grupo.

--4 creacion de 2 tablas independientes de prueba (CLIENTES y ALMACEN) -- el primero en el filegroup PRIMARY y el Segundo -- en el filegroup SECUNDARY create table cliente ( cod_cliente int, nomb_cliente varchar(20) ) go create table almacen ( cod_alm int, descrip varchar(20), ubicacion int ) on secundary --esta tabla en el grupo secundary go

1.5. CREAR TALLER DE MODELO DE DATOS Y CREACION DE BASE DE DATOS. En esta operación el estudiante pondrá a prueba lo aprendido para lo cual tendrá que identificar las entidades, atributos asi como también codificar la base de datos, tablas y relacionar cada una de las tablas, elaborando una estructura adecuada de las tablas el tamaño total de la base de datos será de 50 Mb con un tamaño máximo de 100 Mb y una tasa de crecimiento de 10% con la creación de grupo de archivos para la protección de las tablas que se logren identificar. Una vez terminado comunicar al instructor para su exposición final. El caso a analizar es: CASO FINAL 1. En la Empresa "Educando S.A." lleva el control de sus Bienes y Servicios. El interés primario es poder hacer que los Bienes se manejen de forma rápida y con el menor grado de error. Para esto quien maneja la sección de "Bienes y Suministros" plantea las siguientes condiciones del negocio para la construcción de una base de datos: La Sección está dividida en tres (3) áreas: COMPRAS, ALMACEN, INVENTARIO. ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

36

FUNDAMENTOS DE BASE DE DATOS El área de Compras funciona de la siguiente forma: Recibe las solicitudes de compras de las diferentes áreas de la empresa. o Cada solicitud tiene un responsable. Cada solicitud es autorizada por el Jefe del Área y posteriormente por el Director Financiero. Quien realiza una solicitud puede ser responsable de uno o varios centros de costos, con la salvedad de que él como empleado solo está adscrito a uno. En la solicitud se debe diligenciar la siguiente información: Número de la Solicitud (consecutivo), Fecha, Responsable (nombre y cédula), Centro de Costos, Rubro presupuestal del cual se descargará la compra. En cada solicitud se pueden discriminar uno o muchos ítems con la siguiente información: ítem, nombre del bien, cantidad solicitada, unidad de medida del bien, valor unitario y valor total. Cada solicitud debe ser totalizada. Cada bien es identificado por un código universal que es único y es de carácter devolutivo (suministro) o un bien inmueble. Una vez diligenciada la solicitud es remitida al área de compras para realizar su correspondiente cotización. Las cotizaciones son realizadas con uno o varios proveedores de los bienes solicitados. Una vez la cotización definitiva está lista, se crea una orden contractual que maneja la siguiente información: Número de la orden contractual, nit y nombre del proveedor al cual se le va a realizar la compra, fecha de la orden, monto total de la orden, fecha de entrega. Cada orden puede tener asociado uno o varios ítems de la solicitud o solicitudes que van a ser despachadas. Cada ítem tiene la siguiente información: nombre del bien, cantidad solicitada, cantidad despachada, unidad de medida del bien, valor unitario y valor total. La orden de compra es aprobada por el Director Financiero para que sea enviada al proveedor elegido. El área de Almacén funciona de la siguiente forma: Su función principal es recepcionar los bienes que llegan de los proveedores y distribuirlos a las correspondientes áreas que realizaron las solicitudes de compras. Cuando llega un proveedor mercancía, este hace una entrega física de los bienes, los cuales son comparados con la factura que esta entrega y con la orden de compra correspondiente. Si esta acción es correcta se registra una entrada de almacén por cada factura relacionada, con la siguiente información: Número de Entrada, Fecha, Número de factura, Proveedor, Total Bienes, Valor Total (los totales deben coincidir con los de la factura). Adjunto a esta se discriminan los ítems recibidos con la siguiente información: nombre del bien, cantidad entregada.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

37

FUNDAMENTOS DE BASE DE DATOS Cuando el almacén decide despachar los bienes a las diferentes áreas solicitantes, registra cada una de las entregas en Salidas de Almacén con la siguiente información: Número de Salida, Empleado responsable del bien a entregar, fecha de salida, fecha de entrega. Por cada entrega se detalla cada uno de los ítems con la siguiente información: nombre del bien, cantidad entregada. Una entrada de almacén puede generar muchas salidas de almacén, por ejemplo: Pueden ingresar 500 pacas de papel higiénico, pero como se debe repartir entre varias áreas, cada una requiere de una salida de almacén. El área de inventarios funciona de la siguiente forma: Es la encargada de administrar y controlar la ubicación de los bienes dentro de la empresa, por esto antes de que el bien salga del almacén debe ser codificado a través de un código único que lo haga identificable dentro de la empresa. La ubicación del bien se identifica por la siguiente información: responsable del bien, fecha de entrega, dirección del bien (ubicación). Diseñar modelo ER para la base de datos. Tipos de datos, alcances y variables CASO FINAL 2. Codificar el siguiente diseño de base de datos para un sistema de facturación

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

38

FUNDAMENTOS DE BASE DE DATOS MARCO TEÓRICO Base de datos (BD). Una BD es un conjunto de datos interrelacionados almacenados en conjunto, sin redundancias innecesarias, de forma independiente de los programas que acceden a ellos. Modelos de datos. Los modelos se utilizan en todo tipo de ciencias. Su finalidad es la de simbolizar una Parte del mundo real de forma que sea más fácilmente manipulable. En definitiva, es un Esquema mental (conceptual) en el que se intentan reproducir las características de una Realidad específica. En el caso de los modelos de datos, lo que intentan reproducir es una información real que deseamos almacenar en un sistema informático. Se denomina esquema a una descripción específica en términos de un modelo de datos. El conjunto de datos representados por el esquema forma la base de datos. Clasificación de los modelos de dato: Esquema interno BD Conceptual Físical Mundo Modelo real Esquema Conceptual canónico Modelo Esquema Lógico

Modelo Interno DBMS

Clasificación de los modelos de datos. En la ilustración anterior aparecen los distintos esquemas que llevan desde el mundo real a la base de datos física. Como se ve aparecen varios esquemas intermedios. Los que están Más a la izquierda se alejan más de las características físicas. Los elementos de ese Esquema son: Mundo real. Contiene la información tal cual la percibimos como seres humanos. Es el punto de partida Esquema conceptual. Representa el modelo de datos de forma independiente del DBMS que se utilizará. Esquema canónico (o de BD). Representa los datos en un formato más cercano al del ordenador Esquema interno. Representa los datos según el modelo concreto de un sistema gestor de bases de datos (por ejemplo, Oracle) Base de datos física. Los datos tal cual son almacenados en disco. Diseño conceptual de bases de datos Modelado de datos Para conseguir estos esquemas se utilizan modelos de datos. El paso entre cada esquema Se sigue con unas directrices concretas. Estas directrices permiten adaptar un esquema Hacia otro. Los dos modelos fundamentales de datos son el conceptual y el lógico. Ambos son: Conceptuales en el sentido de que convierten parámetros del mundo real en abstracciones Que permiten entender los datos sin tener en cuenta la física de los mismos.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

39

FUNDAMENTOS DE BASE DE DATOS Diferencias entre el modelo lógico y el conceptual. El modelo conceptual es independiente del DBMS que se vaya a utilizar. El lógico Depende de un tipo de SGBD en particular El modelo lógico es más cercano al ordenador, Es más Cercano al usuario el modelo conceptual, el lógico forma el paso entre el informático y el sistema. Algunos ejemplos de modelos conceptuales son: Modelo E/R, Modelo semántico. Ejemplos de modelos lógicos son: Modelo relacional, Codasyl, Jerárquico, Modelo entidad relación. Introducción. Fue ideado por Peter Chen en los años 1976 y 1977 a través de dos artículos. Se trata de Un modelo que sirve para crear esquemas conceptuales de bases de datos. De hecho, es Prácticamente un estándar para crear esta tarea. Se le llama modelo E/R e incluso EI (Entidad / Interrelación). Sus siglas más populares son las E/R por que sirven para el inglés y el español. Inicialmente (en la propuesta de Chen) sólo se incluían los conceptos de entidad, relación y atributos. Después se añadieron otras propuestas (atributos compuestos, generalizaciones,) que forman el llamado modelo entidad relación extendido (se conoce con las siglas ERE) entidad/Relacion. Entidad. Se trata de cualquier objeto u elemento (real o abstracto) acerca del cual se pueda Almacenar información en la base de datos. Ejemplos de entidades son Alumno, factura Entidad es un objeto que puede poseer múltiples Propiedades (atributos). Conjunto de entidades Las entidades que poseen las mismas propiedades forman conjuntos de entidades. Ejemplos: persona, factura, Auto. En el modelo entidad relación una entidad se representa con un rectángulo dentro del cual se escribe el nombre de la entidad: PERSONA Representación de la entidad persona:

Persona

Tipos de entidades. 1. Fuertes. Son las entidades normales que tienen existencia por sí mismas sin depender de otras. Notación: 2. Entidad débil. Es aquella que sí necesita de otra para existir. Por ejemplo, en un video-club lo que realmente se alquila a los clientes no son películas, sino las copias de películas que tiene el video-club adquirido:

Ejemplo 1.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

40

FUNDAMENTOS DE BASE DE DATOS

Cardinalidad. Indica el número de relaciones en las que una entidad puede aparecer. Se anota en términos de: Cardinalidad mínima. Indica el número mínimo de asociaciones en las que aparecerá cada ejemplar de la entidad (el valor que se anota es de cero o uno). Cardinalidad máxima. Indica el número máximo de relaciones en las que puede aparecer cada ejemplar de la entidad (puede ser uno o muchos) en los esquemas entidad/relación la cardinalidad se puede indicar de muchas formas. Actualmente una de las más populares es esta: Muchos: De cero a muchos. Uno: De uno a muchos. De cero a uno. Ejemplo, cada equipo cuanta con varios jugadores. Un jugador juega como mucho en un equipo y podría no jugar en ninguno. Cada entrenador entrena a un equipo (podría no entrenar a ninguno), el cual tiene un solo entrenador, Propiedades de las entidades y las relaciones. En este modelo se representan Con un círculo, dentro del cual se coloca el nombre del atributo. Ejemplo: ASISTE CURSO Nro_Nota, Nro_DNI, Nombre_ALUMNO, Nombre Domicilio Nombre Número Tipos de atributos Compuesto: Fecha: Dia/Mes/Año Atómicos: Son aquellos que no se pueden descomponer. Ejemplo: N° DNI, N° Telefono, etc. Sistema de Gestión de Base de Datos (SGBD). Los SGB (en inglés DataBase Management System) 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.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

41

FUNDAMENTOS DE BASE DE DATOS Desventajas de las BD: Complejidad: Los SGBD son conjuntos de programas que pueden llegar a ser complejos con una gran funcionalidad. Es preciso comprender muy bien esta funcionalidad para poder realizar un buen uso de ellos. Costo del equipamiento adicional: Tanto el SGBD, como la propia base de datos, pueden hacer que sea necesario adquirir más espacio de almacenamiento. Además, para alcanzar las prestaciones deseadas, es posible que sea necesario adquirir una máquina más grande o una máquina que se dedique solamente al SGBD. Todo esto hará que la implantación de un sistema de bases de datos sea más cara. Vulnerable a los fallos: El hecho de que todo esté centralizado en el SGBD hace que el sistema sea más vulnerable ante los fallos que puedan producirse. Es por ello que deben tenerse copias de seguridad (Backup). Tipos de Campos: Cada Sistema de BD posee tipos de campos que pueden ser similares o diferentes. Entre los más comunes se pueden nombrar: - Numérico: Entre los diferentes tipos de campos numéricos podemos encontrar enteros “sin decimales” y reales “decimales”. - Booleanos: Poseen dos estados: Verdadero “Si” y Falso “No”. - Memos: Son campos alfanuméricos de longitud ilimitada. Presentan el inconveniente de no poder ser indexados. - Fechas: Almacenan fechas facilitando posteriormente su explotación. Almacenar fechas de esta forma posibilita ordenar los registros por fechas o calcular los días entre una fecha y otra. - Alfanuméricos: Contienen cifras y letras. Presentan una longitud limitada (255 caracteres). - Autoincrementables: son campos numéricos enteros que incrementan en una unidad su valor para cada registro incorporado. Su utilidad resulta: Servir de identificador ya que resultan exclusivos de un registro. Tipos de BD. Se pueden encontrar los siguientes: - MySql: Es una base de datos con licencia GPL basada en un servidor. Se caracteriza por su rapidez. No es recomendable usar para grandes volúmenes de datos. - PostgreSql y Oracle: Son sistemas de base de datos poderosos. Administra muy bien grandes cantidades de datos, y suelen ser utilizadas en intranets y sistemas de gran calibre. - Access: Es una base de datos desarrollada por Microsoft. Esta base de datos, debe ser creada bajo el programa access, el cual crea un archivo .mdb con la estructura ya explicada. - Microsoft SQL Server: es una base de datos más potente que access desarrollada por Microsoft. Se utiliza para manejar grandes volúmenes de informaciones. - SQL (Structured Query Language) es un lenguaje de programación estándar e interactiva para la obtención de información desde una base de datos y para

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

42

FUNDAMENTOS DE BASE DE DATOS actualizarla. Aunque SQL es a la vez un ANSI y una norma ISO, muchos productos de bases de datos soportan SQL con extensiones propietarias al lenguaje estándar. Lenguaje de definición de datos (DDL). Las sentencias DDL se utilizan para crear y modificar la estructura de las tablas; así como, otros objetos de la BDs. CREATE - Para crear objetos en la BD. ALTER - Modifica la estructura de la BD. DROP - Borra objetos de la BD. TRUNCATE - Elimina todos los registros de la tabla, incluyendo todos los espacios asignados a los registros. CUESTIONARIO 1) 2) 3) 4) 5) 6)

¿Qué es el modelo físico de datos? ¿Qué es una BD? ¿Qué son las BD estáticas? Objetivo de una tabla normalizada de datos. ¿Qué es una tabla maestro? ¿A qué se denomina inconsistencia de BD?

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

43

FUNDAMENTOS DE BASE DE DATOS II.

IMPLEMENTA TRANSACT SQL

En esta Tarea se tratarán las siguientes Operaciones: -

Implementar sentencias DDL. Crear y optimizar con DML. Crear variables y Estructura de control. Implementar funciones SQL.

2.1. IMPLEMENTAR SENTENCIAS DDL. Lenguaje de definición de datos (DDL). Las sentencias DDL se utilizan para crear y modificar la estructura de las tablas; así como, otros objetos de la base de datos. CREATE. Para crear objetos en la BD. ALTER. Modifica la estructura de la BD. DROP. Borra objetos de la BD. TRUNCATE. Elimina todos los registros de la tabla, incluyendo todos los espacios asignados a los registros. EJEMPLO 1 DE APLICACIÓN RECORDANDO. Creación de una BD. Create database bdsemana2 Creación de una table. create table Articulo ( idart char (3), nomart varchar (30), dit varchar (30), precio money ) Creación de reglas (Rules). Las reglas especifican los valores que se pueden ingresar en un campo, asegurando que los datos se encuentren en un intervalo de valores específico. Ejemplo1: Create rule preciorule as @precio>=1000 and @precio 50 AND CategoryID ='6' ORDER BY UNITPRICE DESC --23) Crear la tabla TBLPrecio_mayor_50 para el listado anterior SELECT*INTO TBLPrecio_mayor_50 FROM Products WHERE UNITPRICE > 50 AND CategoryID ='6' ORDER BY UNITPRICE DESC --24) Sin palabra CLAVE IN SELECT ProductID as ' ID de Producto', ProductName as 'nombre ', CategoryID as 'Categoria' FROM Products WHERE CategoryID=1 OR CategoryID=4 or CategoryID=5 --25) Con palabra clave IN SELECT ProductID as ' ID de Producto', ProductName as 'nombre ', CategoryID as 'Categoria' FROM Products WHERE CategoryID IN ( 1,4,5) --26) Otra manera SELECT ProductID as ' ID de Producto', ProductName as 'nombre ', CategoryID as 'Categoria' FROM Products WHERE CategoryID LIKE '[ 1,4,5]'

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

49

FUNDAMENTOS DE BASE DE DATOS PRACTICANDO. 1. Listar una fila por cada tipo de libro que contenga los subtotales agregados para cada grupo pero solo para la suma de adelantos superiores a 25000 */ USE pubs SELECT type 'TIPO ', SUM(price)'SUMA PRECIOS ', SUM(advance)'SUMA ADELANTOS', SUM(advance * 0.50)as 'SUMA INCREMENTO 50 % ',AVG(price)'PROMEDIO PRECIOS', AVG(advance)'PROMEDIO ADELANTOS', AVG(advance * 0.50)as 'PROMEDIO INCREMENTO 50%' FROM titles GROUP BY type HAVING SUM(ADVANCE)> 25000 2. Listar los titulos, tipos y precios solo de los libros de negocios (business), y luego el precio promedio con el numero de filas, y luego el numero de registros, el maximo precio, el minimo precio, la suma de precios y el precio incremento en 50 % */ USE PUBS select title 'titulo ', type 'tipo ', price'precio' FROM titles WHERE type = 'business' --LISTA PROMEDIO DE PRECIOS Y NUMERO DE FILAS DE LOS LIBROS DE NEGOCIOS SELECT AVG (price)as 'precio promedio', count (*)'numero de filas' FROM titles WHERE type = 'business' --LISTA EL RESUMEN SIGUIENTE DE TODAS LAS FILAS select count(*)'tot.registros', max(price)'maximo', min (price)'minimo', sum(price)'suma', sum(price)* 1.5 'incremento 50% 'from titles WHERE type = 'business' Creando vistas (Utilizando la BD northwind). select productid, productname from products where productid between 11 and 20 create view v_clientes as select CustomerID, companyname,numero=row_number() over(order by customerid) from customers go select * from v_clientes where numero between 91 and 91 create view v_res_ventas as select top 100 o.customerid,anio=year(o.orderdate), total=sum(d.quantity*d.unitprice)from orders o, [order details] d where o.orderid=d.orderid group by o.customerid, year(o.orderdate) order by anio asc, o.customerid asc go select * from v_res_ventas select customerid,[1996],[1997],[1998] from v_res_ventas PIVOT(sum(total) for anio IN ([1996],[1997],[1998])) as T1 select customerid,[1996]=ISNULL([1996],0),[1997],[1998] from v_res_ventas PIVOT(sum(total) for anio IN ([1996],[1997],[1998])) as T1 ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

50

FUNDAMENTOS DE BASE DE DATOS

2.3. CREAR VARIABLES Y ESTRUCTURA DE CONTROL. Una variable en SQL es un espacio de memoria que almacena un valor de cualquier tipo. Estas son declaradas dentro de un bloque de instrucciones y solo tienen alcance dentro de él. Al terminarse la ejecución del bloque las variables son destruidas. Algunos ejemplos de aplicación: Ejemplo 1. --devolver si el numero es impar con IF --IF declare @n int set @n = 5 if (@n % 2)= 0 print 'PAR' else print 'IMPAR' Ejemplo 2. --devolve pares con bucle while Declare @n int set @n = 0 while @n < 10 begin if (@n % 2) = 0 select @n as Numero set @n = @n + 1 end Ejemplo 3. --lista de numeros enteros acompañados de su descripción DECLARE @N INT SET @N = 1 WHILE (@NIndica la máxima anchura al mostrar los datos. El máximo ancho de muestra es 255. D ->Se aplica a tipos de coma flotante y de coma fija e indica el número de dígitos a continuación del punto decimal. El valor máximo posible es 30, pero no debe ser mayor que M-2. Los corchetes ('[' y ']') indican partes de especificadores de tipos que son opcionales. TIPOS NUMÉRICOS BIT[(M)] En un tipo de datos bit. M indica el número de bits por valor, de 1 a 64. El valor por defecto es 1 si se omite M. Este tipo de datos se añadió en MySQL 5.0.3 para MyISAM, una extensión en 5.0.5 para MEMORY, InnoDB, y BDB. Antes de 5.0.3, BIT es un sinónimo de TINYINT(1). TINYINT[(M)] [UNSIGNED] [ZEROFILL] Un entero muy pequeño. El rango con signo es de -128 a 127. El rango sin signo es de 0 a 255. BOOL, BOOLEAN Son sinónimos para TINYINT(1). Un valor de cero se considera falso. Valores distintos a cero se consideran ciertos. En el futuro, se introducirá tratamiento completo de tipos booleanos según el estándard SQL. SMALLINT[(M)] [UNSIGNED] [ZEROFILL] Un entero pequeño. El rango con signo es de -32768 a 32767. El rango sin signo es de 0 a 65535. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] Entero de tamaño medio. El rango con signo es de -8388608 a 8388607. El rango sin singo es de 0 a 16777215. INT[(M)] [UNSIGNED] [ZEROFILL]

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

90

FUNDAMENTOS DE BASE DE DATOS Un entero de tamaño normal. El rango con signo es de -2147483648 a 2147483647. El rango sin signo es de 0 a 4294967295. INTEGER[(M)] [UNSIGNED] [ZEROFILL] Es un sinónimo de INT. BIGINT[(M)] [UNSIGNED] [ZEROFILL] Un entero grande. El rango con signo es de -9223372036854775808 9223372036854775807. El rango sin signo es de 0 a 18446744073709551615.

a

Algunos aspectos a considerar con respecto a las columnas BIGINT : Toda la aritmética se hace usando valores BIGINT o DOUBLE, así que no debe usar enteros sin signos mayores que 9223372036854775807 (63 bits) except con funciones bit! Si lo hace, algunos de los últimos dígitos en el resultado pueden ser erróneos por culpa de errores de redondeo al convertir valores BIGINT a DOUBLE. MySQL 5.0 puede tratar BIGINT en los siguientes casos: Cuando usa enteros para almacenar valores grandes sin signo en una columna BIGINT . En MIN(col_name) o MAX(col_name), donde col_name se refiere a una columna BIGINT Al usar operadores (+, -, *, y así) donde ambos operadores son enteros. Siempre puede guardar un valor entero exacto en una columna BIGINT almacenándolo usando una cadena de carácteres. En este caso, MySQL realiza una conversión cadena de carácteres-número que no implica representación de doble precisión intermedia. Los operadores -, +, y * usan BIGINT en operaciones aritméticas cuando ambos operandos son valores enteros. Esto significa que si multiplica dos enteros grandes (o resultados de funciones que devuelven enteros), puede obtener resultados inesperados cuando el resultado es mayor que 9223372036854775807. FLOAT(p) [UNSIGNED] [ZEROFILL] Número con coma flotante. p representa la precisión. Puede ir de 0 a 24 para números de coma flotante de precisión sencilla y de 25 a 53 para números de coma flotante con doble precisión. Estos tipos son como los tipos FLOAT y DOUBLE descritos a continuación. FLOAT(p) tiene le mismo rango que los tipos correspondientes FLOAT y DOUBLE, pero la anchura de muestra y el número de decimales no están definidos. En MySQL 5.0, este es un valor de coma flotante auténtico. Esta sintaxis se proporciona para compatibilidad con ODBC. Usar FLOAT puede darle algunos problemas inesperados ya que todos los cálculos se en MySQL se hacen con doble precisión. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

91

FUNDAMENTOS DE BASE DE DATOS

Un número de coma flotante pequeño (de precisión simple). Los valores permitidos son de 3.402823466E+38 a -1.175494351E-38, 0, y de 1.175494351E-38 a 3.402823466E+38. Si se especifica UNSIGNED, los valores negativos no se permiten. M es la anchura de muestra y D es el número de dígitos significativos. FLOAT sin argumentos o FLOAT(p) (donde p está en el rango de 0 a 24) es un número de coma flotante con precisión simple. DOUBLE[(M,B)] [UNSIGNED] [ZEROFILL] Número de coma flotante de tamaño normal (precisión doble). Los valores permitidos son de -1.7976931348623157E+308 a -2.2250738585072014E-308, 0, y de 2.2250738585072014E308 a 1.7976931348623157E+308. Si se especifica UNSIGNED, no se permiten valores negativos. M es la anchura de muestra y B es el número de bits de precisión. DOUBLE sin parámetros o FLOAT(p) (donde p está en el rango de 25 a 53) es un número de coma flotante con doble precisión. Un número de coma flotante con precisión sencilla tiene una precisión de 7 decimales aproximadamente; un número con coma flotante de doble precisión tiene una precisión aproximada de 15 decimales. DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL] Son sinónimos de DOUBLE. Excepción: Si el modo del servidor SQL incluye la opción REAL_AS_FLOAT, REAL es un sinónimo para FLOAT en lugar de DOUBLE. DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] A partir de MySQL 5.0.3: Número de punto fijo exacto y empaquetado. M es el número total de dígitos y D es el número de decimales. El punto decimal y (para números negativos) el signo '-' no se tiene en cuenta en M. Si D es 0, los valores no tienen punto decimal o parte fraccional. El máximo número de dígitos (M) para DECIMAL es 64. El máximo número de decimales soportados (D) es 30. Si UNSIGNED se especifica, no se permiten valores negativos. Si se omite D, el valor por defecto es 0. Si se omite M, el valor por defecto es 10. Todos los cálculos básicos (+, -, *, /) con columnas DECIMAL se hacen con precisión de 64 dígitos decimales. Si se omite D, el valor por defecto es 0. Si se omite M, el valor por defecto es 10. DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] Son sinónimos para DECIMAL. El sinónimo FIXED está disponible por compatibilidad con otros servidores. TIPOS CRONOLÓGICOS. 1.-DATE Una fecha. El rango soportado es de '1000-01-01' a '9999-12-31'. MySQL muestra valores DATE en formato 'YYYY-MM-DD', pero permite asignar valores a columnas DATE usando cadenas de caracteres o números.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

92

FUNDAMENTOS DE BASE DE DATOS 2.-DATETIME Combinación de fecha y hora. El rango soportado es de '1000-01-01 00:00:00' a '9999-12-31 23:59:59'. MySQL muestra valores DATETIME en formato 'YYYY-MM-DD HH:MM:SS', pero permite asignar valores a las columnas DATETIME usando cadenas de caracteres o números. TIMESTAMP[(M)] Una marca temporal. El rango es de '1970-01-01 00:00:00' hasta el año 2037. Una columna TIMESTAMP es útil para registrar la fecha y hora de una operación INSERT o UPDATE . La primera columna TIMESTAMP en una tabla se rellena automáticamente con la fecha y hora de la operación más reciente si no le asigna un valor. Puede asignar a cualquier columna TIMESTAMP la fecha y hora actual asignándole un valor NULL . En MySQL 5.0, TIMESTAMP se retorna como una cadena de caracteres en el formato 'YYYYMM-DD HH:MM:SS' cuya anchura de muestra son 19 caracteres. Si quiere obtener el valor como un número, debe añadir +0 a la columna timestamp . 3.-TIME Una hora. El rango es de '-838:59:59' a '838:59:59'. MySQL muestra los valores TIME en formato 'HH:MM:SS', pero permite asignar valores a columnas TIME usando números o cadenas de caracteres. 4.-YEAR [(2|4)] Un año en formato de dos o cuatro dígitos. El valor por defecto está en formato de cuatro dígitos. En formato de cuatro dígitos, los valores permitidos son de 1901 a 2155, y 0000. En formato de dos dígitos, los valores permitidos son de 70 a 69, representando los años de 1970 a 2069. MySQL muestra los valores YEAR en formato YYYY pero permite asignar valores a columnas YEAR usando cadenas de caracteres o números. TIPOS DE CADENA DE CARACTERES. [NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE] Una cadena de caracteres de longitud fija que siempre tiene el número necesario de espacios a la derecha para ajustarla a la longitud especificada al almacenarla. M representa la longitud de la columna. El rango de M en MySQL 5.0 es de 0 a 255 caracteres. Nota: Los espacios a la derecha se borran cuando se obtiene los valores CHAR. Antes de MySQL 5.0.3, una columna CHAR con una longitud especificada mayor que 255 se convierte al tipo TEXT más pequeño que pueda tener los valores de la longitud dada. Por ejemplo, CHAR(500) se convierte a TEXT, y CHAR(200000) se convierte en MEDIUMTEXT. Esta es una característica de compatibilidad. Sin embargo, esta conversión causa que la columna tenga longitud variable, y también afecta a la eliminación de espacios. CHAR es una abreviatura para CHARACTER. NATIONAL CHAR (o su forma equivalente de, NCHAR) es la forma estándard de SQL de definir que una columna CHAR debe usar el conjunto de caracteres por defecto. Este es el comportamiento por defecto en MySQL. El atributo BINARY es una abreviatura para especificar la colación binaria del conjunto de caracteres de la columna. La ordenación y comparación se basa en los valores numéricos de los caracteres. ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

93

FUNDAMENTOS DE BASE DE DATOS

El tipo de columna CHAR BYTE es un alias para CHAR BINARY. Esta es una característica de compatibilidad. El atributo ASCII puede especificarse para CHAR. Asigna el conjunto de caracteres latin1. El atributo UNICODE puede especificarse en MySQL 5.0 para CHAR. Asigna el conjunto de caracteres ucs2 . MySQL le permite crear un tipo de columna CHAR(0). Esto es útil cuando tiene que cumplir con las especificaciones de alguna aplicación vieja que dependa de la existencia de una columna pero que no usa realmente el valor. Esto es también útil cuando necesita una columna que sólo pueda tener dos valores: Una columna CHAR(0) que no esté definido como NOT NULL ocupa sólo un bit y sólo puede tener dos valores NULL y '' (la cadena de caracteres vacía). CHAR Es un sinónimo de CHAR(1). [NATIONAL] VARCHAR(M) [BINARY] Cadena de carácteres de longitud variable. M representa la longitud de columna máxima. En MySQL 5.0, el rango de M es de 0 a 255 antes de MySQL 5.0.3, y de 0 a 65,535 en MySQL 5.0.3 y posterior. (La longitud máxima real de un VARCHAR en MySQL 5.0 se determina por el tamaño de registro máximo y el conjunto de carácteres que use. La longitud máxima efectiva desde MySQL 5.0.3 es de 65,532 bytes.). Nota: Antes de 5.0.3, los espacios finales se eliminaban cuando se almacenaban los valores VARCHAR, lo que difiere de le especificación estándard de SQL. Previo a MySQL 5.0.3, una columna VARCHAR con una longitud especificada mayor a 255 se convertía al valor de tipo TEXT más pequeño que podía soportar el valor de la longitud dada. Por ejemplo, VARCHAR(500) se convertía a TEXT, y VARCHAR(200000) se convertía a MEDIUMTEXT. Esto era una cuestión de compatibilidad. Sin embargo, esta conversión afectaba la eliminación de espacios finales. VARCHAR es la abreviación de CHARACTER VARYING. En MySQL 5.0, el atributo BINARY es abreviatura para especificar la colación binaria del conjunto de carácteres de la columna. La ordenación y la comparación se basa en los valores numéricos de los carácteres. Desde MySQL 5.0.3, VARCHAR se guarda con un prefijo de longitud de uno o dos bytes + datos. La longitud del prefijo es de dos bytes si la columna VARCHAR se declara con una longitud mayor a 255. BINARY(M) El tipo BINARY es similar al tipo CHAR, pero almacena cadenas de datos binarios en lugar de cadenas de carácteres no binarias.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

94

FUNDAMENTOS DE BASE DE DATOS VARBINARY(M) El tipo VARBINARY es similar al tipo VARCHAR, pero almacena cadenas de carácteres binarias en lugar de cadenas de carácteres no binarias. TINYBLOB Una columna BLOB con una longitud máxima de 255 (2^8 - 1) bytes. TINYTEXT Una columna TEXT con longitud máxima de 255 (2^8 - 1) carácteres. BLOB[(M)] Una columna BLOB con longitud máxima de 65,535 (2^16 - 1) bytes. Una longitud opcional M puede darse para este tipo en MySQL 5.0. Si se hace, MySQL creará las columnas como el tipo BLOB de tamaño mínimo para tratar los valores de M bytes. TEXT[(M)] Una columna TEXT con longitud máxima de 65,535 (2^16 - 1) carácteres. En MySQL 5.0, se puede dar una longitud opcional M . En ese caso MySQL creará las columnas con el tipo TEXT de longitud mínima para almacenar los valores de longitud M. MEDIUMBLOB Una columna BLOB con longitud de 16,777,215 (2^24 - 1) bytes. MEDIUMTEXT Una columna TEXT con longitud máxima de 16,777,215 (2^24 - 1) carácteres. LONGBLOB Una columna BLOB con longitud máxima de 4,294,967,295 o 4GB (2^32 - 1) bytes. La longitud máxima efectiva (permitida) de las columnas LONGBLOB depende del tamaño máximo configurado para los paquetes en el protocolo cliente/servidor y la memoria disponible. LONGTEXT Una columna TEXT con longitud máxima de 4,294,967,295 or 4GB (2^32 - 1) carácteres. La longitud máxima efectiva (permitida) de columnas LONGTEXT depende del tamaño máximo de paquete configurado en el protocolo cliente/servidor y la memoria disponible. ENUM('value1','value2',...) Una enumeración. Un objeto de cadena de carácteres que sólo puede tener un valor, elegido de una lista de valores 'value1', 'value2', ..., NULL o el valor de error especial '' . Una columna ENUM puede tener un máximo de 65,535 valores distintos. Los valores ENUM se representan internamente como enteros. SET('value1','value2',...) Un conjunto. Un objeto de cadena de carácteres que puede tener cero o más valores que deben pertenecer a la lista de valores 'value1', 'value2', ... Una columna SET puede tener un máximo de 64 miembros. Los valores SET se representan internamente como enteros. ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

95

FUNDAMENTOS DE BASE DE DATOS

FUNCIONES DE MySQL Funciones para cadena de caracteres Las funciones de cadenas de carácteres retornan NULL si la longitud del resultado es mayor que el valor de la variable de sistema max_allowed_packet . Para funciones que operan en posiciones de cadenas de carácteres, la primera posición es la 1. BIT_LENGTH(str) Retorna la longitud de la cadena de carácteres str en bits. mysql> SELECT BIT_LENGTH('text'); -> 32 CHAR(N,...) CHAR() interpreta los argumentos como enteros y retorna la cadena de carácteres que consiste en los carácteres dados por los códigos de tales enteros. Los valores NULL no se tienen en cuenta. mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(77,77.3,'77.3'); -> 'MMM' CHAR_LENGTH(str) Retorna la longitud de la cadena de carácteres str, medida en carácteres. Un carácter de múltiples bytes cuenta como un sólo carácter. Esto significa que para una cadena de carácteres que contiene cinco carácteres de dos bytes, LENGTH() retorna 10, mientras CHAR_LENGTH() returna 5. CONCAT(str1,str2,...) Retorna la cadena resultado de concatenar los argumentos. Retorna NULL si algúna argumento es NULL. Puede tener uno o más argumentos. Si todos los argumentos son cadenas no binarias, el resultado es una cadena no binaria. Si los argumentos incluyen cualquier cadena binaria, el resultado es una cadena binaria. Un argumento numérico se convierte a su forma de cadena binaria equivalente; si quiere evitarlo puede usar conversión de tipos explícita, como en este ejemplo: SELECT CONCAT(CAST(int_col AS CHAR), char_col) mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL' mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULL mysql> SELECT CONCAT(14.3); -> '14.3' INSTR(str,substr) Retorna la posición de la primera ocurrencia de la subcadena substr en la cadena str. Es lo mismo que la forma de dos argumentos de LOCATE(), excepto que el orden de los argumentos es inverso. mysql> SELECT INSTR('foobarbar', 'bar'); -> 4 mysql> SELECT INSTR('xbar', 'foobar'); -> 0 Esta función puede trabajar con múltiples bytes. En MySQL 5.0, sólo es sensible a mayúsculas si uno de los argumentos es una cadena binaria. LCASE(str)

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

96

FUNDAMENTOS DE BASE DE DATOS LCASE() es sinónimo de LOWER(). LEFT(str,len) Retorna los len carácteres empezando por la izquierda de la cadena str. mysql> SELECT LEFT('foobarbar', 5); -> 'fooba' LENGTH(str) Retorna la longitud de la cadena str, medida en bytes. Un carácter multi-byte cuenta como múltiples bytes. Esto significa que para cadenas que contengan cinco carácteres de dos bytes, LENGTH() retorna 10, mientras que CHAR_LENGTH() retorna 5. mysql> SELECT LENGTH('text'); -> 4 LOWER(str) Retorna la cadena str con todos los carácteres cambiados a minúsculas según el mapeo del conjunto de carácteres actual (por defecto es ISO-8859-1 Latin1). mysql> SELECT LOWER('QUADRATICALLY'); -> 'quadratically' Esta función funciona con múltiples bytes. LPAD(str,len,padstr) Retorna la cadena str, alineado a la izquierda con la cadena padstr a una longitud de len carácteres. Si str es mayor que len, el valor retornado se acorta a len carácteres. mysql> SELECT LPAD('hi',4,'??'); -> '??hi' mysql> SELECT LPAD('hi',1,'??'); -> 'h' LTRIM(str) Retorna la cadena str con los carácteres en blanco iniciales eliminados. mysql> SELECT LTRIM(' barbar'); -> 'barbar' Esta función trabaja con múltiples bytes. MAKE_SET(bits,str1,str2,...) Retorna un conjunto de valores (una cadena conteniendo subcadenas separadas por carácteres ',' ) consistiendo en cadenas que tienen el bit correspondiente en bits asignado. str1 se corresponde al bit 0, str2 al bit 1, y así. Los valores NULL en str1, str2, ... no se añaden al resultado. mysql> SELECT MAKE_SET(1,'a','b','c'); -> 'a' mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');-> 'hello,world' mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');-> 'hello' mysql> SELECT MAKE_SET(0,'a','b','c'); -> '' MID(str,pos,len) MID(str,pos,len) es sinónimo de SUBSTRING(str,pos,len). OCT(N) Retorna una representación en cadena del valor octal de N, donde N es un número largo (BIGINT). Es equivalente a CONV(N,10,8). Retorna NULL si N es NULL. mysql> SELECT OCT(12); -> '14'

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

97

FUNDAMENTOS DE BASE DE DATOS

OCTET_LENGTH(str) OCTET_LENGTH() es sinónimo de LENGTH(). ORD(str) Si el carácter más a la izquierda de la cadena str es un carácter multi-byte , retorna el código de ese carácter, calculado a partir del valor numérico de sus bytes usando esta fórmula: (1st byte code)+ (2nd byte code * 256)+ (3rd byte code * 256^2) ... Si el caráctar más a la izquierda no es multi-byte , ORD() retorna el mismo valor que la función ASCII() . mysql> SELECT ORD('2'); -> 50 POSITION(substr IN str) POSITION(substr IN str) es sinónimo de LOCATE(substr,str). QUOTE(str) Acota una cadena para producir un resultado que puede usarse como un valor con carácteres de escape en un comando SQL. La cadena se retorna rodeado por comillas sencillas y con cada instancia de comilla sencilla ('''), antibarra ('\'), ASCII NUL, y Control-Z predecidos por una antibarra. Si el argumento es NULL, el valor de retorno es la palabra “NULL” sin comillas alrededor. mysql> SELECT QUOTE('Don\'t!'); -> 'Don\'t!' mysql> SELECT QUOTE(NULL); -> NULL REPEAT(str,count) Retorna una cadena consistente de la cadena str repetida count veces. Si count SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL' REPLACE(str,from_str,to_str) Retorna la cadena str con todas las ocurrencias de la cadena from_str reemplazadas con la cadena to_str. mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');-> 'WwWwWw.mysql.com' Esta función trabaja con múltiples bytes. REVERSE(str) Retorna la cadena str con el orden de los carácteres invertido. mysql> SELECT REVERSE('abc'); -> 'cba' Esta función trabaja con múltiples bytes. RIGHT(str,len) Retorna los len carácteres de la derecha de la cadena str. mysql> SELECT RIGHT('foobarbar', 4); -> 'rbar' Esta función trabaja con múltiples bytes. RPAD(str,len,padstr) Retorna la cadena str, alineada a la derecha con la cadena padstr con una longitud de len carácteres. Si str es mayor que len, el valor de retorno se corta a len carácteres. mysql> SELECT RPAD('hi',5,'?'); -> 'hi???' ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

98

FUNDAMENTOS DE BASE DE DATOS mysql> SELECT RPAD('hi',1,'?'); -> 'h' Esta función trabaja con múltiples bytes. RTRIM(str) Retorna la cadena str con los espacios precedentes eliminados. mysql> SELECT RTRIM('barbar '); -> 'barbar' Esta función trabaja con múltiples bytes. SPACE(N) Retorna la cadena consistente en N carácteres blancos. mysql> SELECT SPACE(6); -> ' ' SUBSTRING(str,pos) , SUBSTRING(str SUBSTRING(str FROM pos FOR len)

FROM

pos),

SUBSTRING(str,pos,len)

,

Las formas sin el arguemnto len retornan una subcadena de la cadena str comenzando en la posición pos. Las formas con el argumento len retornan una subcadena de longitud len a partir de la cadena str, comenzando en la posición pos.Las formas que usan FROM son sintaxis SQL estándard. En MySQL 5.0, es posible usar valores negativos para pos. En este caso, el inicio de la subcadena son pos carácteres a partir del final de la cadena, en lugar del principio. Un valor negativo puede usarse para pos en cualquier de las formas de esta función. mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' Esta función trabaja con múltiples bytes. Tener en cuenta que si usa un valor menor a 1 para len, el resultado siempre es una cadena vacía. SUBSTR() es sinónimo de SUBSTRING(). SUBSTRING_INDEX(str,delim,count) Retorna la subcadena de la cadena str antes de count ocurrencias del delimitador delim. Si count es positivo, todo a la izquierda del delimitador final (contando desde la izquierda) se retorna. Si count es negativo, todo a la derecha del delimitador final (contando desde la derecha) se retorna. mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);-> 'www.mysql' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com' Esta función trabaja con múltiples bytes.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

99

FUNDAMENTOS DE BASE DE DATOS

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM(remstr FROM] str) Retorna la cadena str con todos los prefijos y/o sufijos remstr eliminados. Si ninguno de los especificadores BOTH, LEADING, o se daTRAILING, BOTH se asumen. Si remstr es opcional y no se especifica, los espacios se eliminan. mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' Esta función trabaja con múltiples bytes. UCASE(str) UCASE() es sinónimo de UPPER(). UPPER(str) Retorna la cadena str con todos los carácteres cambiados a mayúsculas según el mapeo del conjunto de carácteres actual (por defecto es ISO-8859-1 Latin1). mysql> SELECT UPPER('Hej'); -> 'HEJ' Esta función trabaja con múltiples bytes. Funciones Numéricas.Todas las funciones matemáticas retornan NULL en caso de error. ABS(X) Retorna el valor absoluto de X. mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32 Esta función puede usar valores BIGINT. CEILING(X), CEIL(X) Retorna el entero más pequeño no menor a X. mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEIL(-1.23); -> -1 Estas dos funciones son sinónimos. Tenga en cuenta que el valor retornado se convierte a BIGINT. FLOOR(X) Retorna el valor entero más grande pero no mayor a X. mysql> SELECT FLOOR(1.23); -> 1 mysql> SELECT FLOOR(-1.23); -> -2 Tener en cuenta que el valor devuelto se convierte a BIGINT. LN(X) Retorna el logaritmo natural de X, esto es, el logaritmo de X base e. mysql> SELECT LN(2); -> 0.69314718055995 mysql> SELECT LN(-2); -> NULL ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

100

FUNDAMENTOS DE BASE DE DATOS Esta función es sinónimo a LOG(X). MOD(N,M) , N % M, N MOD M Operación de módulo. Retorna el resto de N dividido por M. mysql> SELECT MOD(234, 10); -> 4 mysql> SELECT 253 % 7; -> 1 mysql> SELECT MOD(29,9); -> 2 mysql> SELECT 29 MOD 9; -> 2 Esta función puede usar valores BIGINT. MOD() también funciona con valores con una parte fraccional y retorna el res-to exacto tras la división: mysql> SELECT MOD(34.5,3); -> 1.5 POW(X,Y) , POWER(X,Y) Retorna el valor de X a la potencia de Y. mysql> SELECT POW(2,2); -> 4 mysql> SELECT POW(2,-2); -> 0.25 RAND(), RAND(N) Retorna un valor aleatorio en coma flotante del rango de 0 a 1.0. Si se especifica un argumento entero N, es usa como semilla, que produce una secuencia repetible. mysql> SELECT RAND(); -> 0.9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881 mysql> SELECT RAND(20); -> 0.15888261251047 Se puede usar esta función para recibir registros de forma aleatoria como se muestra aquí: mysql> SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combinado con LIMIT es útil para seleccionar una muestra aleatoria de una conjunto de registros: mysql> SELECT * FROM table1, table2 WHERE a=b AND c ORDER BY RAND() LIMIT 1000; Tener en cuenta que RAND() en una cláusula WHERE se re-evalúa cada vez que se ejecuta el WHERE. RAND() no pretende ser un generador de números aleatorios perfecto, pero es una forma rápida de generar números aleatorios ad hoc portable entre plataformas para la misma versión de MySQL. ROUND(X), ROUND(X,D)

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

101

FUNDAMENTOS DE BASE DE DATOS

Retorna el argumento X, redondeado al entero más cercano. Con dos argumentos, retorna X redondeado a D decimales. D puede ser negativo para redondear D dígitos a la izquierda del punto decimal del valor X. mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2 mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1 mysql> SELECT ROUND(23.298, -1); -> 20 El tipo de retorno es el mismo tipo que el del primer argumento (asumiendo que sea un entero, doble o decimal). Esto significa que para un argumento entero, el resultado es un entero (sin decimales). Antes de MySQL 5.0.3, el comportamiento de ROUND() cuando el argumento se encuentra a medias entre dos enteros depende de la implementación de la biblioteca C. Implementaciones distintas redondean al número par más próximo, siempre arriba, siempre abajo, o siempre hacia cero. Si necesita un tipo de redondeo, debe usar una función bien definida como TRUNCATE() o FLOOR() en su lugar.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

102

FUNDAMENTOS DE BASE DE DATOS

IV.

IMPLEMENTA PROGRAMACIÓN CON T-SQL.

En esta tarea se tratarán las siguientes operaciones: -

Implementar Store Procedure básico. Implementar Store Procedure con varias tablas. Crear disparadores básicos. Crear disparadores avanzados.

4.1. IMPLEMENTAR STORE PROCEDURE BÁSICO. 1. Sp_definidos por el usuario (USP). Su propósito va a quedar como objetos fijos en el sistema. Sintaxis. Create procedure [nombre ][@param1 ,tipo 1 , @param2 ,tipo 2, @ param3 ,tipo 3 ] As instrucciones SQL GO Para ejecutar un sp utilizar el comando EXECUTE EXECUTE nombre del SP [Lista de datos] Ejemplos prácticos de aplicación. Ejemplo 1: Procedimiento almacenado que hace un select y realiza grupos por número de deparamento y cuenta cuántos empleados hay en cada departamento.

Nota: En este ejemplo sólo hay 1 empleado

CLIENTES REGISTRADOS:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

103

FUNDAMENTOS DE BASE DE DATOS

Ejemplo 2: Procedimiento almacenado de Inserción. create Procedure Usp_InsertarCLiente @Cod varchar(8), @nom_cliente VarChar(50) , @ape_cliente VarChar(50) , @dir_cliente VarChar(50) , @tel_cliente VarChar(8), @Ruc_cliente Varchar(15), @DNI_cliente Varchar(8), @Email_cliente Varchar(50), @Distrito VarChar(3) as Insert Clientes Values (@Cod,@nom_cliente,@ape_cliente,@dir_cliente,@tel_cliente,@ruc_cliente,@dni_cliente, @email_cliente,@Distrito) Go Print 'Insertar Cliente creado'

Ejemplo 3: Procedimiento de consulta.

Ejemplo 4:

Se acaba de eliminar un registro de la tabla clients.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

104

FUNDAMENTOS DE BASE DE DATOS Ejemplo 5: --Procedimiento de Modificacion(Edición) select * from clientes Create Procedure Usp_Update_CLiente @Cod varchar(8), @nom_cliente VarChar(50) , @ape_cliente VarChar(50) , @dir_cliente VarChar(50) , @tel_cliente VarChar(8), @Ruc_cliente Varchar(15), @DNI_cliente Varchar(8), @Email_cliente Varchar(50), @Distrito VarChar(3) as Update Clientes set nom_cli=@nom_cliente, ape_cli=@ape_cliente, dir_cli=@dir_cliente, tel_cli=@tel_cliente, Ruc_cli=@ruc_cliente, DNI_cli=@dni_cliente,Email_cli=@email_cliente,iddistrito=@Distrito where IdCliente=@Cod Go Print 'Cliente Modificado' Primero, verificar los registros que se tienen a disposición:

Proceder a ejecutar el procedimiento de actualización para ver los cambios:

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

105

FUNDAMENTOS DE BASE DE DATOS

Verificar con un select:

Ejemplo 6: (En la Bd Northwind) PROCEDIMIENTOS DEFECTO.

ALMACENADOS

CON

PARÁMETROS

CON

VALORES

POR

Sintaxis: Create Procedure Nombre @Variable tipo = Valor As Instrucciones Donde Valor es el valor que le damos por defecto, este valor puede almacenar Comodines (como % que equivale a *).

Ejemplo 7: VARIABLES DE SALIDA EN PROCEDIMIENTOS ALMACENADOS. Sintaxis: Create Procedure Nombre @Variable tipodedato Output. Instrucciones: Print @Variable Create Procedure Totalprecio @product varchar(25), @total int Output --Crear el procedimiento con un parámetro y una variable de salida As --Declarar dos variables para almacenar valores Declare @Sal Int Declare @Com Int --Asignar los valores correspondientes a las variables y después se suman y --guardar en la variable de salida. Select @Sal = Unitprice from products where productname = @product Select @Com = Unitprice from products where productname = @product ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

106

FUNDAMENTOS DE BASE DE DATOS --Select @Com = Comision from products where productname = @product Set @Total = @Sal + @Com --Devolver el valor de la variable que se quiere Print @total

Ejemplo 8: Listado de empleados. ************************ lista_empleado **********************/ create proc lista_employees as select employeeid,lastname from employees Ejemplo 9: (de la bd Northwind) create proc lista_paises as select distinct country from customers go

4.2. IMPLEMENTAR STORE PROCEDURE CON VARIAS TABLAS. Ejemplo 1: (de la Bd Northwind) /************************ lista_Productos segun categoria **********************/ create proc lista_productos @cod char(5) as select

c.categoryid,c.CategoryName,ProductName,UnitPrice from Categories c inner join products p on c.categoryid=p.categoryid --Ejecutando lista_productos 'Beverages'

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

107

FUNDAMENTOS DE BASE DE DATOS

Ejemplo 2: De la base de datos BDVENTASLIBRO

Primero realizando una consulta a la tabla Libro

Creando el procedimiento Lista de Nombres no se tiene que ingresar todo el nombre, sólo unos cuantos caracteres como muestra el ejemplo: CREATE PROC LISTAR_LIBROS_NOMBRE @NOMBRE VARCHAR(30)='%' AS SELECT COD_LIBRO,NOM_LIBRO,PRE_LIBRO,STK_LIBRO FROM LIBROS ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

108

FUNDAMENTOS DE BASE DE DATOS WHERE NOM_LIBRO LIKE '%'+@NOMBRE+'%' GO ---LISTAR_LIBROS_NOMBRE LISTAR_LIBROS_NOMBRE 'BEGINNING ' Ejemplo 3: Procedimiento de consulta que muestra los libros que se encuentran activos; es decir, cuyo estado sea igual a 1. CREATE PROC LISTA_LIBROS @COD INT=0 AS IF @COD 100) 

Caso 2: ‐‐Lista de todos los clientes con su número total  de pedidos  SELECT CompanyName, Address,          OrderCount = (SELECT COUNT(O.OrderID) FROM [Orders] O WHERE O.CustomerId =  C.CustomerID)  FROM Customers C  

 

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

134

FUNDAMENTOS DE BASE DE DATOS Caso 3:   ‐‐¿Qué productos fueron vendidos por unidad (es  decir, la cantidad = 1)    SELECT ProductName    FROM Products   WHERE ProductID = ANY         (SELECT ProductId             FROM [Order Details]           WHERE Quantity = 1) 

Caso 4: -- Lista de clientes que realizaron pedidos Mayores que el promedio de cada orden de cliente SELECT DISTINCT CompanyName + ' ' + Address as Direccion  FROM Customers, [Orders]   WHERE Customers.CustomerID = [Orders].CustomerId     AND freight > ALL          (SELECT AVG(freight)            FROM [Orders]           GROUP BY CustomerId)   

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

135

FUNDAMENTOS DE BASE DE DATOS

5.3. CREAR OPERACIÓN CON SQL AZURE. Creación de una base de datos con AZURE: Paso 1. Suscripción, tomar en cuenta que una vez suscrito se contará con $200 dolares para realizar pruebas. Una vez terminado su consumo tendrá la posibilidad de comprar si se cree necesario. Para ello, deberá contar con una cuenta Hotmail gratuita o coorporativa, ver el ejemplo:

Paso 2.

Paso 3.

Paso 4. Llenar los datos del formulario generará un código enviado al número telefónico.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

136

FUNDAMENTOS DE BASE DE DATOS

Paso 5. Autenticacion a través de tarjeta de débito o crédito.

Paso 6:

Paso 7:

Paso 8: Botón crear/crear base de datos/asignar nombre a la base de datos ejemplo senatiBd_Hugo ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

137

FUNDAMENTOS DE BASE DE DATOS

Paso 9: Seleccionar uno libre de 32 Mb como prueba.

Paso 10: Crear el servidor.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

138

FUNDAMENTOS DE BASE DE DATOS Paso 11. Visualizando a la base de datos creada.

Visualizando las propiedades de la base de datos. Paso 1:

TAREAS DE INVESTIGACIÓN 1) ¿Qué es Microsoft Azure? 2) ¿Qué son las Tecnologias SaaS, PaaS y IaaS? Exponer en clase sus diferencias con un ejemplo. 3) Generar un video de creación de tablas en la base de datos creada. 4) Generar un video de subir una base de datos de Sqlserver a azure.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

139

FUNDAMENTOS DE BASE DE DATOS

MARCO TEÓRICO Computacion en la nube (Cloud Computing). El término cloud computing hace referencia a una concepción tecnológica y a un modelo de negocio que reúne ideas tan diversas como el almacenamiento de información, las comunicaciones entre ordenadores, la provisión de servicios o las metodologías de desarrollo de aplicaciones, todo ello bajo el mismo concepto: todo ocurre en la nube. Ofrecer al cliente espacio de almacenamiento o capacidad de procesamiento en sus servidores. Así el usuario tendrá a su disposición “un disco duro de capacidad ilimitada” y un procesador de rendimiento casi infinito, solo restringido a su capacidad económica de contratación del servicio. Este servicio se basa en el acceso al uso de hardware radicado en la nube. Modelos de nube según la privacidad. El hecho de que la información manejada resida temporal o definitivamente en servidores en la nube lleva a que dichos servicios ofrezcan distintos formatos de privacidad que pueden elegir los usuarios. De ahí que se planteen varios modelos de nubes como espacios de desarrollo de los servicios ofertados. Serían: 1. Nubes públicas. Los usuarios acceden a los servicios de manera compartida sin que exista un exhaustivo control sobre la ubicación de la información que reside en los servidores del proveedor. El hecho de sean públicas no es un sinónimo de sean inseguras. 2. Nubes privadas. Para los clientes que necesiten, por la criticidad de la información que manejen una infraestructura, plataforma y aplicaciones de su uso exclusivo. 3. Nubes híbridas. Combinan características de las dos anteriores, de manera que parte del servicio se puede ofrecer de manera privada (por ejemplo, la infraestructura) y otra parte de manera compartida (por ejemplo, las herramientas de desarrollo). Características de un servicio ‘cloud’.  Autoservicio bajo demanda. Un usuario debe poder, de forma unilateral, proveerse de recursos informáticos tales como tiempo de proceso o capacidad de almacenamiento en la medida de sus necesidades sin que sea necesaria la intervención humana del proveedor del servicio.  Acceso amplio a la red. Los servicios proporcionados deben poder ser accesibles a través de mecanismos estándares y desde plataformas heterogéneas (por ejemplo: ordenadores, teléfonos móviles o tabletas).  Asignación común de recursos. Los recursos son puestos a disposición de los consumidores siguiendo un modelo de multipropiedad, asignándose y reasignándose dispositivos físicos o lógicos atendiendo a la demanda de dichos consumidores. En este sentido el usuario no tiene un estricto control del lugar exacto en el que se encuentra su información, aunque sí debe poder especificar un ámbito mínimo de actuación (por ejemplo: un país, un estado o un centro de proceso de datos concreto).

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

140

FUNDAMENTOS DE BASE DE DATOS  Rápida elasticidad. Las capacidades en los recursos proporcionados a los usuarios deben poder crecer o decrecer bajo demanda de los mismos con celeridad, incluso mediante procesos automáticos.  Servicio medible. Los sistemas cloud deben controlar y optimizar sus recursos dotándose de capacidades para medir su rendimiento en un nivel de abstracción suficiente para la naturaleza del servicio proporcionado. Además, dicho control debe permitir ser reportado de manera transparente tanto al proveedor del servicio como al consumidor del mismo.

ESCUELA DE TECNOLOGÍAS DE INFORMACIÓN

141