Download Apunte del profesor Roberto Uribe Paredes, Universidad de
Document related concepts
Transcript
Universidad de Magallanes – Depto. De Ing. en Computación Capitulo 1 : Introducción Bases de Datos Es un conjunto de datos que están relacionados entre sí, relativos a un sistema de información y almacenados en forma más o menos permanente. El software para accesar los datos se llama Sistema Administrador de Bases de Datos (DBMS, DataBase Management System). Un DBMS permite al usuario utilizar la información a un nivel conceptual independiente de su implementación en el computador. Los DBMS permiten: Evitar (o al menos disminuir) la redundancia de información, Disminuir el costo de almacenamiento y acceso a la información, Evitar inconsistencias, Compartir datos (varios usuarios compartiendo la misma información) y Aplicar restricciones de seguridad al acceso a la Base de Datos. Niveles de Abstracción Nivel Físico: Son los datos tal como son almacenados en el computador. Por ejemplo archivos, métodos de acceso, archivos índices. En un DBMS el nivel físico es transparente para el usuario. Nivel Conceptual: Se describe qué datos se almacenan y las relaciones que existen entre ellos. Usualmente el usuario trabaja en este nivel y no se preocupa de los detalles del nivel físico. Nivel de vista: Es un subconjunto del nivel conceptual. A determinados usuarios de la Base de Datos se les presenta sólo un subconjunto de datos. Para una misma Base de Datos se pueden construir varias vistas según el tipo de usuarios del DBMS. Esquemas e Instancias Una instancia es el contenido de la Base de Datos en algún momento dado en el tiempo. Bases de Datos, 2er Semestre, 3er año Pág. Nº 1 Universidad de Magallanes – Depto. De Ing. en Computación Un esquema es la descripción de la estructura de la Base de Datos en un determinado nivel. Se habla de esquema físico, esquema conceptual y subesquema conceptual (vistas). En cada nivel se requiere información distinta para construir los esquemas. Independencia Existe independencia de datos entre niveles, es decir, existe capacidad de modificar el esquema de un nivel si alterar el nivel superior. Existe 2 tipos de independencia: Independencia de Datos Física: Es posible alterar la implementación sin alterar el nivel conceptual. Independencia de Datos Lógica: Es posible hacer varios cambios en el esquema conceptual sin alterar los subesquemas. Lenguajes de Base de Datos Lenguaje de Definición de Datos (DDL) Sirve para definir los esquemas de la Base de Datos (para cada nivel se requiere un lenguaje distinto). El resultado de la compilación de las instrucciones DDL son tablas con la descripción dela Base de Datos. El esquema físico lo define principalmente el DBMS. Lenguaje de Manipulación de Datos (DML) Permite acceder a la información almacenada en la Base de Datos. Las operaciones típicas son: Recuperar información (consultas a la Base de Datos), Agregar información, Modificar la información almacenada (actualizaciones), Borrar información. Bases de Datos, 2er Semestre, 3er año Pág. Nº 2 Universidad de Magallanes – Depto. De Ing. en Computación Usuarios de la Base de Datos Administrador Realiza la definición y modificación de los esquemas utilizados un DDL. Define las estructuras de almacenamiento y los métodos de acceso a los datos. Asigna los tipos de autorizaciones de acceso a la información. Programadores de Aplicaciones Interactúan con el DBMS a través del DML, incluyendo instrucciones del DML en programas escritos en un lenguaje host como Pascal, C o algún lenguaje 4GL especialmente diseñado para este tipo de aplicaciones. Usuarios Casuales Saben de computación e ingresan consultas directamente en el DML. Usuarios Finales No necesitan saber de computación debido a que interactúan directamente con los programas de aplicación. Estructura de un DBMS Administrador de Archivos Se encarga de las operaciones de almacenamiento en el disco. Usualmente utiliza llamadas a funciones del Sistema Operativo. Administrador de la Base de Datos Es la interfaz entre al administrador de archivos (bajo nivel) y las consultas en DML. Preprocesador de Consultas Traduce las consultas a instrucciones que el administrador de la Base de Datos entiende. Generalmente realiza optimizaciones a las consultas formuladas por el usuario. Bases de Datos, 2er Semestre, 3er año Pág. Nº 3 Universidad de Magallanes – Depto. De Ing. en Computación Precompilador de DML Convierte instrucciones de DML a invocaciones a funciones DBMS. Compilador DDL Recibe como entrada la definición de la Base de Datos (en DDL) y genera como salida tablas con la información de los esquemas (el diccionario de datos). USUARIO FINAL PROGRAMADOR APLICACIÓN Programa Aplicación Pre.Proc. DML Aplicación Compilada USUARIO CASUAL Consultas DML Pre. Proc. Consultas ADMINISTRADOR BASE DE DATOS Esquema Base Datos Compilador DDL Administrador Base de Datos DBMS Administrador de Archivos DATOS DICCIONARIO DISCO Figura 1.1 Bases de Datos, 2er Semestre, 3er año Pág. Nº 4 Universidad de Magallanes – Depto. De Ing. en Computación Capítulo 2: Modelo de Entidades - Relaciones Conceptos El modelo de Entidades-Relaciones se utiliza para describir el mundo real y sirve como medio de comunicación entre usuarios y desarrolladores. Entidades Una entidad es cualquier objeto distinguible en un sistema: Persona, Producto, Insumo. Un conjunto de entidades es un grupo de entidades del mismo tipo, Empleados de un Empresa, Productos que se fabrican en un Empresa, Insumos utilizados en los productos. Las entidades se describen por medio de un conjunto de atributos. Por ejemplo, cada empleado de una Empresa puede ser descrito mediante los atributos, rut, Nombre y Dirección. El dominio de un atributo es su tipo de dato (integer, real, char, etc). Llave es un atributo o conjunto de atributos que identifican a las entidades dentro de su conjunto. En principio todos los conjuntos de entidades tienen una llave (entidades distinguibles). Ejemplos: EMPLEADOS (rut, nombre, dirección) DEPARTAMENTOS (coddepto, nombre) PRODUCTOS (codprod, nombre, precio) INSUMOS (codins, nombre) CLIENTES (rut, nombre, fono, fax) Bases de Datos, 2er Semestre, 3er año Pág. Nº 5 Universidad de Magallanes – Depto. De Ing. en Computación Relaciones Las relaciones establecen asociaciones entre entidades. Por ejemplo, Los productos e insumos de una Empresa pueden estas relacionados mediante una relación llamada CONTIENE, que se puede utilizar para indicar los insumos que contiene cada producto. Los empleados y departamentos de una Empresa pueden ser relacionados mediante una relación llamada PERTENECE. En la práctica, la mayoría de las relaciones son binarias. Las relaciones pueden tener sus propios atributos, En la relación CONTIENE se puede tener un atributo que indique la proporción en que participan los insumos en los productos. El rol de cada entidad dentro de una relación es importante, Supongamos la relación SER JEFE entre entidades del mismo conjunto EMPLEADOS, en este caso debe especificarse quién es el jefe y quién el subordinado (el primero es el jefe, o al revés). Las relaciones binarias se clasifican en, Uno-Uno: Cada entidad de cada conjunto está relacionada a lo más con una entidad del otro conjunto. Por ejemplo, la relación JEFE entre EMPLEADOS y DEPARTAMENTOS. Uno-Muchos: En una relación uno a muchos desde E1 a E2 , cada elemento de E1 está relacionado con cero o uno de E2, y cada elemento de E2 está relacionado con cero o más de E1. Por ejemplo, la relación TRABAJA entre EMPLEADOS y DEPARTAMENTOS. Muchos-Muchos: Cada entidad puede estar relacionada con cualquier número de entidades del otro conjunto. Por ejemplo, la relación CONTIENE entre PRODUCTOS e INSUMOS. Bases de Datos, 2er Semestre, 3er año Pág. Nº 6 Universidad de Magallanes – Depto. De Ing. en Computación Diagramas E-R Conjuntos de entidades se representan mediante rectángulos y se utilizan elipses para los atributos, Figura 2.1. Las relaciones se representan mediante rombos, Figura 2.2. Las relaciones muchos-uno llevan una flecha hacia el uno, las relaciones uno-uno flechas hacia ambos lados y las relaciones muchos-muchos no llevan flechas. En la figura 2.3. se muestra un ejemplo de diagrama E-R. Conceptos Adicionales Generalidades y Especialización En el conjunto de entidades general se agrupan los atributos comunes a varios conjuntos de entidades que describen aspectos particulares del conjunto de entidades general, figura 2.4. Dependencia de Existencia La existencia de una entidad depende de la existencia de otra. Figura 2.5. En la figura, para que exista una cuenta necesariamente debe existir un cliente. Si se borra un cliente necesariamente debe borrarse la cuenta. Conjuntos de Entidades Fuertes y Débiles Hay conjuntos de entidades que no tienen una llave propia, como en el caso de la Figura 2.5 donde el conjunto de entidades VENDEDORES y OPERADORES tienen un único atributo (comisión y sobre_tiempo respectivamente). Este concepto está relacionado con la dependencia de existencia; el conjunto de entidades débiles es el subordinado y el fuerte el dominante. Bases de Datos, 2er Semestre, 3er año Pág. Nº 7 Universidad de Magallanes – Depto. De Ing. en Computación rut nombre EMPLEADOS dirección Figura 2.1 EMPLEADOS n 1 TRABAJA JEFE 1 1 DEPARTAMENTOS Figura 2.2 Bases de Datos, 2er Semestre, 3er año Pág. Nº 8 Universidad de Magallanes – Depto. De Ing. en Computación rut EMPLEADOS nombre n dirección TRABAJA 1 coddepto DEPARTAMENTOS nombre 1 codprod nombre FABRICA precio n n PRODUCTOS n VENTAS CLIENTES n rut nombre proporción CONTIENE fono fax n codins INSUMOS nombre Figura Bases de Datos, 2er Semestre, 3er año 2.3 Pág. Nº 9 Universidad de Magallanes – Depto. De Ing. en Computación rut nombre dirección EMPLEADOS VENDEDORES OPERADORES comisión sobre_tiempo Figura 2.4 CLIENTES 1 CREDITOS 1 CUENTAS Figura 2.5 Bases de Datos, 2er Semestre, 3er año Pág. Nº 10 Universidad de Magallanes – Depto. De Ing. en Computación Problemas E-R Los Arcoch La Tribu de los Arcoch, habitante del Planeta Nak y únicos sobrevivientes de la guerra del año 2084, que destruyó los cuatro planetas de un sistema solar, se han propuesto construir una nueva forma de organización social para proteger su raza, actualmente en extinción. Cada miembro de la tribu desarrolla alguna actividad, la cual debe aprender y luego perfeccionar. Por ejemplo, cazar, cultivar la tierra, educar, etc.. Cada niño es educado e inicia alguna actividad en la adolescencia, cada anciano puede dejar de trabajar si lo desea. De acuerdo a la actividad que desarrolle cada Arcoch, se le entregan los elementos necesarios. Si se cambia de actividad estos deben ser devueltos. Se conoce qué elementos deben ser entregados por actividad. Tal como se otorgan bienes por actividad, se otorgan bienes a las familias de acuerdo a la edad de la familia (tiempo que lleva constituida la familia) y al número de integrantes que tenga. Por ejemplo, cuando dos Arcoch deciden formar una familia, la tribu les da una choza y otros utensilios. Se pide construir el Diagrama E-R que permita almacenar la información relevante para los Arcoch. Farmacias Suponga una cadena de farmacias que posee varias bodegas en distintos puntos de la ciudad. Cada sucursal posee un stock de cada tipo de medicamento, los que son adquiridos a distintos laboratorios (cada medicamento es proporcionado por un solo laboratorio). Las sucursales solicitan medicamentos a cualquiera de las bodegas. Los medicamentos entregados por los laboratorios son almacenados en las bodegas. Los documentos utilizados son notas de pedido y guías de entrega. Además, el detalle del tipo de cantidad de medicamentos vendidos van quedando registrados en las boletas y facturas. Construya un diagrama E-R que permita llevar el control del flujo de entrada (Laboratorios) y salida de medicamentos (ventas). Bases de Datos, 2er Semestre, 3er año Pág. Nº 11 Universidad de Magallanes – Depto. De Ing. en Computación Capítulo 3 : Modelo Relacional La Base de Datos se representa mediante un conjunto de tablas. Las tablas representan tanto a conjunto de entidades como a relaciones. Sobre las tablas se define un conjunto de operaciones que permiten hacer consultas sobre la Base de Datos (Álgebra Relacional). En el modelo relacional a las tablas se les llama relaciones en un sentido más general y matemático. En matemáticas, una relación se define como un subconjunto del producto cartesiano entre un conjunto de dominios. Cada dominio es una columna de la tabla y cada fila es una tupla o elemento del producto cartesiano. Al conjunto de atributos de una relación se le llama esquema de la relación y se denota. Rel (a1: dom1, a2: dom2,..........,an: domn) donde Rel es el nombre de la relación, ai es el nombre del i-ésimo atributo y domi es el tipo de datos del i-ésimo atributo. Relaciones Entidades Fuertes Si E es el conjunto de entidades fuertes y e1, e2,......, en son sus atributos, entonces el conjunto E se representa mediante una tabla con una columna por cada atributo ei. Ver Figura 3.1. Entidades Débiles Si D es el conjunto de entidades débiles con atributo d1, ........., dn y E conjunto de entidades fuertes con llave formada por los atributos e1,.....ek, entonces D se representa mediante una tabla que tiene una columna por cada uno de los atributos e1,.....,ek,d1, ......, dn. Ver figura 3.2. EMPLEADOS rut 1111111-1 2222222-2 nombre Juan Pérez Luis Jiménez dirección Los Abedules 67 Los Alerces 98 Figura 3.1 Bases de Datos, 2er Semestre, 3er año Pág. Nº 12 Universidad de Magallanes – Depto. De Ing. en Computación VENDEDORES rut 1111111-1 2222222-2 comisión 20 15 Figura 3.2 Relaciones Si los conjuntos de entidades relacionadas son E1, E2, ......,En y sus llaves son key(Ei), entonces la relación se representa como una tabla con una columna por cada llave key(Ei)i=1,2, ....n, más los atributos propios de la relación (si lo hubiera). Ver Figuras 3.3. y 3.4. TRABAJA rut 1111111-1 2222222-2 coddepto. D1 D2 Figura 3.3 CONTIENE codprod P1 P1 P2 codins I1 I2 I1 proporción 10 90 100 Figura 3.4 Algebra Relacional Incluye una serie de operaciones que actúan sobre relaciones completas (tablas) y generan como resultado otra relación (tabla). Los operandos son relaciones o variables que representan relaciones. Operadores fundamentales: Unión: Si R y S son relaciones, entonces R S denota al conjunto de tuplas que están en R o S. Los atributos de R y S deben ser idénticos. Bases de Datos, 2er Semestre, 3er año Pág. Nº 13 Universidad de Magallanes – Depto. De Ing. en Computación Diferencia: Si R y S son relaciones con los mismos atributos, entonces R conjunto de tuplas de R que no están en S. - S es el Producto cartesiano: Si R y S son relaciones con atributos a1, a2,...., ar y b1, b2, ....., bs respectivamente, entonces R x S es el conjunto de tuplas con atributos a1, ..., ar, b1, ..., bs, tal que en cada tupla de R x S los primeros r atributos corresponden a R y los siguientes s atributos corresponden a S. En la tabla resultante cada tupla de R aparece con todas las tuplas de S. Ver figura 3.5. Proyección: El operador es un operador unario que permite seleccionar columnas. Si R es una relación de atributos a1, ....,an, entonces ai,aj,ak (R) produce una relación con los atributos ai, aj, ak. Selección: El operador permite seleccionar tuplas desde una relación de acuerdo a una condición . La condición se construye utilizando nombres de atributos o constantes, los operadores lógicos | (or), & (and) y ! (not), y los operadores de relación >, <, , , = y . Si R es una relación, entonces que cumplen la condición . A a1 a2 R= (R) produce una tabla que contiene todas las tuplas de R B b1 b2 R x S= A a1 a1 a2 a2 S= B b1 b1 b2 b2 C c1 c2 c1 c2 C c1 c2 D d1 d2 D d1 d2 d1 d2 Figura 3.5 Ejemplos: Para las tablas EMPLEADOS y VENDEDORES se pide, Seleccionar a todos los vendedores con comisión del 10%, R1 Bases de Datos, 2er Semestre, 3er año comisión = 10 (VENDEDORES) Pág. Nº 14 Universidad de Magallanes – Depto. De Ing. en Computación Mostrar el rut, R2 rut (R1) Mostrar el rut y el nombre (de los vendedores con comisión 10%), R3 EMPLEADOS x R2 R4 EMPLEADOS.rut = R2.rut (R3) R5 rut,nombre (R4) A partir de las operaciones fundamentales se pueden definir otros operadores, S = R - (R - S) ) Intersección: R Join : Join ( selección, R S es un operador binario que combina el producto cartesiano y la = (R x S) Join natural: Se indica con el operador y la condición de selección es que las tuplas tengan el mismo valor en las columnas con el mismo nombre (si no hay atributos con el mismo nombre el efecto es similar al producto cartesiano), R S= b1,........,bn ( R.a1 = S.a1 &....& R.ak = S.ak (R x S)), donde a1, ....., ak son los atributos comunes y b1, ....., bn son los atributos de R x S eliminando las repeticiones. Cuociente: Es la operación inversa al producto cartesiano. Si a1, ....., an son los atributos de R y a1, ..., ak (con k < n) son los atributos de S. (i.e. el esquema de S es un subconjunto del esquema de R), entonces R ÷ S es el conjunto de tuplas t tales que para toda tupla s en S exista una tupla r en R tal que s[a1, ...., ak ] = r [a1, ...., ak ] r[ak+1, ...., an ] = t Dicho de otra forma: R ÷ S es el conjunto de tuplas t tales que para toda tupla s en S, la tupla st está en R (Ver Figura 3.6). Utilizando los operadores fundamentales el operador ÷ se define como Bases de Datos, 2er Semestre, 3er año Pág. Nº 15 Universidad de Magallanes – Depto. De Ing. en Computación R÷S= R= ak+1, ....., an ( R ) - ak+1, ..., an (( ak+1, ....., an ( R ) x S) - R). A a1 a1 a2 B b1 b2 b1 S= B b1 b2 R÷S = A a1 Figura 3.6 Ejemplos: Se desea conocer el rut y nombre de todos los vendedores, rut,nombre (EMPLEADOS VENDEDORES) Ídem al anterior, pero para los vendedores con 10% de comisión, rut, nombre ( comisión = 10 (EMPLEADOS VENDEDORES) Obtener los insumos que se utilizan en todos los productos, codprod,codins (CONTIENE) codprod (PRODUCTOS) Bases de Datos, 2er Semestre, 3er año Pág. Nº 16 Universidad de Magallanes – Depto. De Ing. en Computación Problemas Proyectos Considere la siguiente Base de Datos: EMPLEADOS(CodEmp,Nombre,Salario) TRABAJA_EN(codproy,CodEmp) PROYECTO(CodProy,Presupuesto) DIRECTOR(CodProy,CodEmp) Responda las consultas: Salarios de todos los directores de proyectos. Salarios de todos los empleados que trabajan en todos los proyectos. Empleados asignados a proyectos que no tienen director. Medicamentos Una Base de Datos farmacológica contiene medicamentos, señalando para qué patología están indicados, y qué laboratorios los producen (indicando el nombre comercial que cada laboratorio le asigna). Además, se tiene una lista de medicamentos incomplatibles entre sí. Esto se almacena en las siguientes tablas: MEDICAMENTOS(Codigo,Descripcion) PROVEEDORES(Codigo,Laboratorio,Nombre_Comercial) INDICADORES(Codigo,Patologia) INCOMPATIBLES(Codigo1,Codigo2) Responder las siguientes consultas, Nombre comercial de todos los medicamentos para el resfrío común que se pueden tomar junto con “Aspirina”. Todos los pares de medicamentos que sirven par una misma enfermedad. Todos los laboratorios que no producen ninguna forma de “Ácido Acetil Salicílico” (substring en el atributo descripción). Bases de Datos, 2er Semestre, 3er año Pág. Nº 17 Universidad de Magallanes – Depto. De Ing. en Computación Capítulo 4: SQL SQL (Structured Query Language) es un estándar para lenguajes de consulta (DML) utilizados en los DBMS relacionales. SQL combina operadores del álgebra relacional en su instrucción básica de consulta, select-from-where. Consultas La instrucción select-from-where combina los operadores , y x, y su forma típica es select a1, a2, ....., an from R1, R2, ...., Rk where donde a1, ...., an son los atributos de la tabla resultante, R1, ..., Rk son las tablas que participan en la consulta y es la condición que deben cumplir las tuplas de salida. Esta instrucción es equivalente a la expresión a1, ..., an ( (R1 x R2 x ... x Rk)). Si where se omite, se supone que la condición es verdadera y colocar un “*” en select implica proyectar sobre todas las columnas del producto cartesiano. Además, se incluyen las operaciones union, intersect y minus que corresponden directamente a los operadores , y - del álgebra relacional. La equivalencia entre los operadores fundamentales y SQL están dadas en la Figura 4.1 R S select * from R union select * from S R-S select * from R minus select* from S RxS select * from R, S a1, ..., an (R) select a1, ..., an from R (R) select * from R where Figura 4.1 Bases de Datos, 2er Semestre, 3er año Pág. Nº 18 Universidad de Magallanes – Depto. De Ing. en Computación Descripción y Ejemplos Encontrar todos los productos que han sido vendidos alguna vez. select CodProd from VENTAS En la práctica no se eliminan automáticamente las tuplas repetidas, luego cada CodProd aparece en el resultado tantas veces como aparece en VENTAS. Una solución es utilizar la cláusula distinct. select distinct CodProd from VENTAS Encontrar el rut y nombre de todos los vendedores con comisión del 10%. Select ERut, ENombre, from EMPLEADOS, VENDEDORES where EMPLEADOS.ERut = VENDEDORES.VRut AND Comision=10 En SQL se pueden definir variables de tipo tupla. Se definen en la cláusula from y se asocian a una tabla en particular. Encontrar todos los empleados que trabajan en el mismo departamento de “González” (rut 2222222-2) select t.Rut from TRABAJA t, TRABAJA s where s.Rut = 2222222-2 AND t.CodDepto=s.CodDepto También se puede preguntar por la pertenencia de una tupla a una tabla. Encontrar todos los clientes que han comprado productos fabricados por el departamento de productos agrícolas (código DA). select CRut, CNombre from VENTAS, CLIENTES where VENTAS.CRut=CLIENTES.CRut AND CodProd IN (select CodProd from FABRICA where CodDepto=“DA”) Bases de Datos, 2er Semestre, 3er año Pág. Nº 19 Universidad de Magallanes – Depto. De Ing. en Computación Encontrar todos los productos que tengan precio mayor que algún producto del departamento de productos agrícolas. select CodProd,Precio from PRODUCTOS where Precio ANY (select Precio from PRODUCTOS,FABRICA where CodDepto=“DA” AND PRODUCTOS.CodProd=FABRICA.CodProd) Se puede preguntar si una relación está contenida en otra. Encontrar todos los insumos que se usan en todos los productos. select * from INSUMOS t where (select CodProd from CONTENIDOS s where s.CodIns=t.CodIns) CONTAINS (select CodProd from PRODUCTOS) También se pueden indicar que la tabla resultante de una consulta aparezca en algún orden. Mostrar los nombres de todos los vendedores en orden alfabético. select ENombre from EMPLEADOS,VENDEDORES where EMPLEADOS.ERut=VENDEDORES.VRut ORDER BY Enombre Existen las funciones estadísticas avg, min, max, sum y count. Calcular el número de empleados. select count (*) from EMPLEADOS Encontrar la cantidad total vendida del producto de código 100. select sum(Cantidad) from VENTAS where CodProd=100 Bases de Datos, 2er Semestre, 3er año Pág. Nº 20 Universidad de Magallanes – Depto. De Ing. en Computación Obtener el total de clientes que han comprado. select count(distinct Crut) from VENTAS Es posible obtener valores por grupos. Encontrar la cantidad vendida de cada producto. select CodProd,sum(Cantidad) from VENTAS group by CodProd Es posible aplicar condiciones a grupos. Ídem al anterior, pero sólo interesan los productos en que la suma sea mayor que 500 select CodProd,sum(Cantidad) from VENTAS group by CodProd HAVING sum(Cantidad) 500 Tablas Creación de Tablas CREATE TABLE NombreTabla (definición de columnas) Crea una tabla vacía y el creador queda como su dueño. Ejemplo, CREATE TABLE TRANSACCION( TN char(5) NOT NULL, VN char(5) NOT NULL, CN char(5) NOT NULL, Fecha date, Cantidad decimal(8,2) ) Eliminación de Tablas DROP TABLE NombreTabla Bases de Datos, 2er Semestre, 3er año Pág. Nº 21 Universidad de Magallanes – Depto. De Ing. en Computación Elimina una tabla con todas las tuplas que tenga. Sólo puede ser eliminada por su dueño. Ejemplo, DROP TABLE TRANSACCION Agregar Columnas a una Tabla ALTER TABLA NombreTabla ADD DefinicionesColumnas Ejemplo, ALTER TABLA EMPLEADOS ADD (Efono char(20)) Creación de Índices CREATE UNIQUE INDEX NombreIndice ON NombreTabla (Columnas) UNIQUE es opcional y no permite dos o más tuplas con el mismo valor en las columnas indexadas. Ejemplo, CREATE INDEX IRUT ON VENDEDORES (VRut) Eliminación de un índice, DROP INDEX IRUT Recuperación de la Información SELECT UNIQUE ListaColumnas FROM ListaTablas WHERE Condicion SubConsulta GROUP BY ListaConsulta HAVING Condicion ORDER BY ListaColumnas Inserción INSERT INTO NombreTabla VALUES ( Valores para cada columna ) Eliminación DELETE FROM NombreTabla WHERE Condicion Bases de Datos, 2er Semestre, 3er año Pág. Nº 22 Universidad de Magallanes – Depto. De Ing. en Computación Modificación UPDATE NombreTabla SET Columna a modificar con su nuevo valor WHERE Condicion Ejemplos Adicionales Encontrar todos los vendedores de Santiago. select * from VENDEDORES,EMPLEADOS where VENDEDORES.VRut=EMPLEADOS.ERut AND ECiudad=“Santiago” Encontrar VN y ENombre (ordenados por nombre) para todos los vendedores de Santiago que tienen un saldo mayor que 200.000. select VN,ENombre from VENDEDORES,EMPLEADOS where ECiudad=“Santiago” AND VSaldo > 200000 AND VENDEDORES.VRut=EMPLEADOS.ERut order by Enombre Encontrar CRut y CNombre para todos los clientes que tienen alguna transacción de monto mayor que 50.000. select unique CLIENTES.CRut, Cnombre from CLIENTES,TRANSACCION where CLIENTES.CRut=TRANSACCION.CRut AND Cantidad 50000 Encontrar CRut para los clientes que tienen transacciones con los vendedores V1 y V2. select unique X.CRut from TRANSACCION X, TRANSACCION Y where X.VN=“V1” AND Y.VN=“V2” AND X.CRut=Y.CRut Bases de Datos, 2er Semestre, 3er año Pág. Nº 23 Universidad de Magallanes – Depto. De Ing. en Computación De otra manera: select unique Crut from TRANSACCION where VN=“V1” AND CRut IN ( select CRut from TRANSACCION where VN=“V2” ) En cuantas ciudades distintas existen clientes? select count ( unique CCiudad ) from CLIENTES Ingresar el cliente “González” en la Base de Datos. insert into CLIENTES values (“C27”,“González”,0,50000) Aumentar el saldo a los clientes de Santiago en un 10% update CLIENTES set CSaldo = 1.10*CSaldo where CCiudad = “Santiago” Eliminar a los clientes con saldo cero. delete from CLIENTES where CSaldo = 0 Aumentar en 10% el saldo de los clientes que tienen más de 10 transacciones. update CLIENTES set CSaldo = 1.10*CSaldo where 10 (select count (*) from TRANSACCION where TRANSACCION.CRut=CLIENTES.CRut ) Vistas Se utiliza el término vista para referirse a una tabla virtual construida a partir de otras tablas (virtuales o reales). Se pueden crear varias vistas para una misma Base de Datos. Bases de Datos, 2er Semestre, 3er año Pág. Nº 24 Universidad de Magallanes – Depto. De Ing. en Computación Creación de una vista: CREATE VIEW NombreVista AS ExpresionSelect Ejemplo, create view CLIENTES_SANTIAGO AS ( select CRut,CNombre from CLIENTES where CCiudad=“Santiago” ) Una vez creada la vista, el nombre de esta puede usarse para hacer referencia a la tabla virtual. Los nombres de vistas pueden aparecer en cualquier lugar donde sea posible la aparición del nombre de una tabla. Ejemplo, select C.CRut, CNombre from CLIENTES_SANTIAGO C, TRANSACCION T where VN=“V1” AND C.CRut=T.CRut Eliminación: La eliminación de una vista se puede hacer mediante la instrucción, DROP VIEW NombreVista Bases de Datos, 2er Semestre, 3er año Pág. Nº 25 Universidad de Magallanes – Depto. De Ing. en Computación Problemas Actividades y Recursos La Base de Datos para una importante Empresa incluye las siguientes tablas, DEPARTAMENTOS(CodDepto,NomDepto) ACTIVIDADES(CodAct,NomAct,Unidad) RECURSOS(CodRec,NomRec,Unidad) AUTORIZACIONES(CodDepto,CodAct) PERMISOS(CodAct,CodRec) VALORES(CodRec,Mes,Precio) CONSUMOS(CodDepto,CodAct,CantAct,CodRec,CantRec,Fecha) Cada departamento de la Empresa está autorizado a realizar sólo ciertas actividades. Esto se refleja en la tabla AUTORIZACIONES. Análogamente, al realizarse una actividad se está autorizado a consumir ciertos recursos, esto se indica en la tabla PERMISOS. Cada recurso mantiene su precio durante un mes, la tabla VALORES indica el precio de cada recurso para cada mes del año en curso. La tabla consumo expresa las actividades realizadas por cada departamento y los recursos gastados al realizar dicha actividad. Responda las siguientes consultas en SQL: Qué departamentos han realizado alguna actividad sin tener autorización? (Indicar el nombre del departamento, actividad y fecha) Qué departamentos están autorizados a utilizar todos los recursos?. Cuánto dinero consumió el departamento de Mantención durante el mes de Marzo, Cuánto dinero consumió cada departamento en la actividad Ensamblar durante el mes de Abril?. Cual es el departamento más eficiente en realizar la actividad Aseo?. Cuales son los departamentos que están autorizados a realizar las mismas actividades?. (suponga que existe una función month que recibe como parámetro una fecha y retorna el mes correspondiente). Aerolíneas Para las tablas, VUELOS(CodVuelo, PaisOrigen,PaisDestino) Bases de Datos, 2er Semestre, 3er año Pág. Nº 26 Universidad de Magallanes – Depto. De Ing. en Computación AEROLINEAS(CodVuelo,NombreAerolinea) PILOTOS(CodPiloto,Nombre,Edad,HorasVuelo) PILOTEAN(CodVuelo,CodPiloto) Responda las siguientes consultas en SQL: Cantidad de vuelos que realiza "Avianca" a Colombia. Edad promedio de los pilotos que tienen más de 3.000 horas de vuelo. Nombre y aerolínea a la que pertenece el piloto con mayor cantidad de vuelos. Número total de horas de vuelo de "Lan Chile". Nombre, edad y aerolínea a la que pertenecen los pilotos que tienen menos de 40 años de edad. Nombre de las aerolíneas que viajan entre "España" y "Chile" (hacia y/o desde). Todos los pilotos que pertenecen a "Lan Chile". Países a donde llega "Lan Chile". Pilotos que han estado en todos los paises a donde llega "Lan Chile". Almacenes Considere las siguientes tablas, ALMACENES(CodAlm,Jefe,Direccion) PRODUCTOS(CodProd,Descrip,Precio) INVENTARIO(CodAlm,CodProd,Stock) VENTAS(CodAlm,CodBoleta,Fecha,Total) BOLETAS(CodBoleta,CodProd,Cantidad,Precio) Se pide responder las siguientes consultas en SQL: Para una boleta dada entregar el nombre y precio unitario de los productos que incluye, y la ubicación del almacén donde se emitió. Dada la fecha, entregar una lista con todos los productos que ha vendido cada almacén. Ubicación de todos los almacenes donde exista stock cero para algún producto. Bases de Datos, 2er Semestre, 3er año Pág. Nº 27