xbd - wordpress.com• lenguaje de definición de datos: ddl----ldd: permite definir la estructura...
TRANSCRIPT
XBD
1
1. Introducción a sistemas de almacenamiento de información
-Vocabulario básico: • Registro : Colección de datos que refieren a un elemento de información.
• Archivo o fichero : Conjunto de registros.
1.1 Sistema de ficheros
• Sistemas de ficheros secuenciales• Sistemas de ficheros aleatorios
1.1.1 Ficheros o archivos secuenciales
• Características
Los registros se almacenan unos detrás de otros, consecutivamente. La grabación de datos es continua.
2
• Ventajas
– El acceso de un registro a otro es inmediato, una ventaja a la hora de consultar grandes listados de registros.
– Los registros varían en longitud.– O son abiertos en modo lectura o en modo escritura (o se abren para leerlos o para
escribir en ellos) nunca a la vez.
• Inconvenientes
– Para recorrer un registro se deben recorrer todos los anteriores.– Se dejan huecos.
Ejemplo:
1.1.2 Ficheros o archivos aleatorios
• Características
– La grabación de registros no tiene porque ser continua.– Tienen una longitud fija.
• Ventajas
– Acceso directo a un registro sin necesidad de leer los anteriores.– Se abren en modo lectura/escritura.
• Inconveniente
-Tienen una longitud fija, (si los registros se graban de forma consecutiva y la clave es numérica se hace esto).
3
“Nota”
Clave: Se usa para identificar un elemento.
– Si las claves son alfanuméricas (B1, C3) o (AB, BC), etc..., es obligatorio aplicar un algoritmo que transforma estas claves en números, que son las posiciones físicas donde se guardan los registros, lo cual puede dar lugar a la aparición de sinónimos (posición de almacenamiento idénticas para registros de claves distintas).
Ejemplo:
Tienen claves diferentes AB y BA pero la misma posición de almacenamiento (21)
La solución para evitar esto, es crear una zona de almacenamiento de los registros que generan sinónimos, zona que tienen que ser secuencial.– Deben tener un tamaño mínimo (al fichero).
El trabajo con sistemas de ficheros (secuenciales y aleatorios) tiene una serie de inconvenientes comunes.
• Redundancia : Repetición de los mismos datos en ficheros distintos.
4
C.C. = Código clientes C.F. = Código facturas
C.C. C.F. C.C.
1 Luis 25 36... F1 1
2 Ana 35 36... F2 1
3 Eva 45 36... F3 3
4 Iria 46 36... F4 4
5 Oscar 47 36... F5 2
Clientes Facturas
• Dependencia físico-lógica de los datos : La definición de los datos depende del lenguaje con el que se manipulan.
• Dificultad en el acceso concurrente : Dos personas quieren acceder a los datos.• Problemas en la seguridad • Integridad de los datos
2. Bases de datos
-Definición:
Conjunto de datos pertenecientes a un mismo contexto organizados sistemáticamente junto con su descripción.
2.1 Tipos según la localización de la información
• Bases de datos distribuidas : Los datos están localizados en diferentes ordenadores.• Bases de datos centralizadas : Los datos están localizados en un solo ordenador.
5
2.2 Tipos según el modelo de datos
– Modelo jerárquico
• Características :
– Todo registro tiene un registro (depende de otro) excepto el nodo raíz.– Un registro puede tener varios hijos.– Un registro solo puede tener un padre.– Los registros se conectan mediante direcciones físicas.
• Ventajas :
– El acceso de padres a hijos es muy rápida.
• Inconvenientes :
– Redundancia.– No se puede acceder directamente a un registro hijo, hay que recorrer todo desde la raíz.– Un hijo no puede tener dos padres.– Los hijos no pueden relacionarse entre sí.– No existe garantía de que un hijo este relacionado con un padre (se puede borrar un
padre sin borrarse los hijos).
– Modelo en red :
• Características :
– Un padre puede tener varios hijos– Un hijo puede tener varios padres.
6
• Organización :
– Consta de tres elementos: Propietario, Miembro y Conjunto.– Cada elemento del conjunto consta de la clave de un Propietario y de la clave de un
Miembro
• Limitación :
– Un miembro que pertenezca a un elemento de un conjunto no puede relacionarse con más de un propietario a través del mismo conjunto, pero sí a través de conjuntos distintos.
7
La solución sería:
“Nota”
Sistema de gestión de una base de datos: Herramientas para manipular esos datos.
2.2.2 Modelo relacional
2.3 Otros sistemas de almacenamiento
2.3.1 XML
• Lenguaje de marcas pensado para describir datos (estructurados).
Ejemplo:
– Cliente <Cliente>– Nombre <Nombre>Ana</Nombre>– Apellido <Apellido>Pérez</Apellido>– Teléfono <Teléfono>986......</Teléfono>
</Cliente>
• En este tipo de lenguaje las etiquetas no están predeterminadas.
8
2.3.2 HTML
• Lenguaje en el que las etiquetas están predeterminadas.
<B>Luis</B> ----------------- Pone Luis en negrita.
2.3.3 Servicios de directorio
• Son aplicaciones que organizan información sobre los usuarios de la red de ordenadores y sobre los recursos de la red y permite a los administradores gestionar el acceso de los usuarios a los recursos de dicha red.
2.4 Sistemas de información
• Conjunto organizado por personas, datos, actividades, recursos, materiales de una empresa.
2.4.1 Tipos de sistemas de información
• Sistemas de procesamiento de transacciones.• Sistemas de información gerencial.• Sistemas de toma de decisiones.• Sistemas de automatización de oficinas.• Sistemas de información geográfica.
3. Sistemas gestores de bases de datos
• Realizan estas acciones:
– Definir la base : Cual va a ser su estructura.– Crearla : Crearla físicamente.– Mantenerla : Hacer copia, etc...– Controlar el acceso : Hacer que solo pueda acceder a la base quien queramos.
3.1 Servicios de un sistema gestor de bases de datos
• Servicio de creación y definición de la base : Crear la estructura donde se almacenan los datos.
• Manipulación de datos : Realizar operaciones con los datos almacenados en la estructura de la base.
• Acceso controlado : Control de usuarios y operaciones que pueden realizar sobre la base.• Mantener la integridad y la consistencia de los datos .• Acceso compartido a los datos .• Copias de respaldo y recuperación ante caídas .
9
3.2 Componentes de un sistema gestor de bases de datos
• Lenguaje de definición de datos : DDL----LDD: Permite definir la estructura de la base.• Lenguaje de manipulación de datos : DML/LMD (SQL)
– Permite meter datos.– Permite eliminar datos. Operaciones de actualización– Permite modificar datos.
• Consulta : No alteran el contenido de la base.• Diccionario de datos : Especie de biblioteca en donde se recoge información de todos los
objetos que se crean en la base de datos así como de su estructura y de su estado actual.
– Se auto mantiene.– Contiene meta datos (datos sobre los datos).
• Otras : Herramientas de administración para gestionar usuarios, copias de seguridad, importación y exportación de datos.
3.3 Usuarios de bases de datos
Los usuarios de una base de datos ABD-DBA son:
• Administrador de bases de datos : Es el que tiene todo el poder para hacer lo que quiera.
– Administrar espacio en disco.– Crear y destruir usuarios.– Iniciar y parar la base, etc...
• Resource : Usuario que puede crear objetos en la bases de datos. Puede crear listas, etc.... Está limitado a lo que diga el administrador.
• Connect : Puede acceder a los objetos creados por otros usuarios. Solo puede hacer eso, no puede hacer nada más. No puede crear, eliminar ni modificar nada.
3.4 Ventajas de un sistema gestor de bases de datos
• Liberan a los programadores de aplicaciones del trabajo de organizar y almacenar los datos, y controlar el acceso.
• Bajan los tiempos de desarrollo de las aplicaciones.• Proviene de lenguajes que facilitan la consulta y la actualización de los datos (SQL).
10
3.5 Inconvenientes de un sistema gestor de bases de datos
• Requieren de personal especializado en el manejo del sistema gestor de bases de datos.• Requieren gran espacio y memoria (más coste).• Coste de servicio de mantenimiento.• El acceso a los datos no es directo, por eso es más lento.
3.6 Tipos de sistemas gestores de bases de datos
• Según el modelo :
– Modelo jerárquico.– Modelo en red.– Modelo relacional.– Orientados a objetos.– Nativos XML.
• Según la variabilidad de los datos almacenados :
– Dinámicos : Muy frecuentes operaciones de actualización (crear, modificar y eliminar).– Estáticos : Muy pocas o ninguna actualización (solo se hacen consultas).
• Según el contenido almacenado en ellos :
– Sistema gestor de bases de datos bibliográficas : Solo contienen una representación de la fuente primaria de información y como mucho un resumen de dicha información.
– Sistema gestor de bases de datos de texto completo : Contiene toda la fuente primaria de datos.
– Sistema gestor de bases de datos de directorios : Guías telefónicas, móviles, agendas, etc...
– Sistema gestor de bases de datos de almacén de datos empresariales (Data warehouse) : Almacenan todo tipo de datos de una empresa y se usan para tomar decisiones en base a esos datos.
3.7 Relación entre el Sistema Gestor de Bases de Datos (SGBD) y el Sistema Operativo (S.O.)
11
3.8 Modelado de datos
4. Modelo entidad/relación (E/R) (Entity/Relation Ship)
Los elementos del modelo entidad relación son:
Entidades, Interrelación (relación o asociación), dominios, atributos (propiedades), restricción.
• Entidad : Objeto, persona, animal, concepto real o abstracto acerca del cual se quiere recoger información.
Su símbolo es:
12
Tipos de entidades:
– Fuertes o regulares : Las que tienen existencia por si mismas, no dependen de otras para existir.
Su símbolo es:
– Débiles : Las que dependen de otras entidades para existir.
Su símbolo es:
• Interrelación : Vínculo que se establece entre entidades.
Su símbolo es:
Ejemplos:
13
• Dominio y valor : Conjunto nominado (que tiene nombre) de valores homogéneos, sobre los que se definen las propiedades de los objetos (E/R).
Su símbolo es:
Hay dos tipos de dominio:
– Dominio definido por extensión : Hay que especificar los valores que contiene el dominio.
– Dominio definido por intensión : Se explicita cada valor del dominio.
“Nota”
Hay gestores de datos que no pueden crear dominios.
• Atributo o propiedad : Cada una de las características de un objeto (E/R).
14
Ejemplo:
15
• Restricciones : Solo se permiten establecer relaciones entre entidades.
Tipos de restricciones:
• Restricciones referidas a los atributos :
– I.C. (Identificador Candidato): Es cualquier atributo de una entidad susceptible de identificarlo inequívocamente (que no se repita).
– I.P. (Identificador Principal): Aquel que elijo como clave para identificar a un ejemplar de la entidad (se llama también “campo clave” o “clave principal”).
– I.A. (Identificador Alternativo): Aquel identificador candidato que no es identificador principal.
• Restricciones referidas a las interrelaciones :
– Grado de una relación : Número de entidades que conecta.
16
4.1 Correspondencia
– Es el número máximo de ejemplares de una entidad que pueden relacionarse con un ejemplar de otra entidad.
Se representa:
1:11:NN:M
Ejemplo:
• 1:1
– Un profesor dirige un curso como máximo.– Un curso es dirigido por un profesor como máximo.
P1 - C2
P2 - C3
17
• 1:N
– Un profesor puede dirigir varios cursos.– Un curso es dirigido por un profesor como máximo.
• N:M
– Un profesor puede dirigir varios cursos.– Un curso puede ser dirigido por varios profesores.
18
Otro ejemplo:
• Los asegurados asisten al hospital.
– Los asegurados solo pueden asistir a un hospital como máximo.– A un hospital pueden asistir varios asegurados.
4.2 Cardinalidad de una entidad
• Número máximo y mínimo de ejemplares de una entidad que pueden relacionarse con un ejemplar de otra entidad.
Se representa así:
(0,1)(1,1)(1,N)(0,N)
Ejemplo:
– Un profesor solo debe estar en un solo departamento.– Un departamento puede tener varios profesores.
19
Ejercicio:
– Los empleados de una empresa pueden asistir a cursos de formación.– Cada curso de formación puede ser impartido como máximo por un profesor.– A los cursos de formación puede asistir más de un empleado.– Un mismo profesor puede impartir varios cursos.
4.3 Atributos de las relaciones
• Atributos en una relación 1:N : Los atributos de la interrelación pasan a la entidad débil (la que depende de otra entidad).
20
• Atributos en una relación 1:1 : Los atributos de la interrelación pasan a cualquiera de las entidades.
• Atributos en una relación N:M : Los atributos no pueden pasar a ninguna entidad, se quedan en la interrelación (la interrelación se transforma en una tabla individual, como si fuera una entidad, con sus atributos).
Ejemplo (1:N):
21
Ejemplo (1:1):
4.4 Dependencia en existencia y dependencia en identificación
• Dependencia en existencia : Se da cuando los ejemplares de una entidad no pueden existir si desaparecen los ejemplares de la entidad fuerte de la cual dependen.
Su símbolo es:
22
• Dependencia en identificación : Se da cuando, además de darse dependencia en existencia, los ejemplares de la entidad débil se identifican a partir de la clave de la entidad fuerte, añadiéndole un nuevo atributo para formar la clave de dicha entidad débil.
Ejemplo de dependencia en existencia:
Si, por ejemplo, ASIR1 desaparece, todos los alumnos matriculados en ASIR 1 tienen que desaparecer.
Ejemplo de dependencia en identificación:
23
Ejercicio:
Realizar el esquema entidad relación (E/R) para organizar los discos que tengo.
– Disco: Clave a partir del grupo al que pertenece, fecha edición y casa musical.– Grupo: Nombre grupo (clave), número de componentes y fecha de fundación.– Canciones: Se identifican a partir del disco al cual pertenecen, título, tiempo de
duración, letra (sí o no) y estilo.
24
4.5 Jerarquías
• Es un caso especial de relación entre varias entidades denominadas subtipos con una entidad de tipo más general denominada supertipo.
Los supertipo contienen atributos comunes a todos los subtipo.
Se representa así:
Ejercicio:
– Clasificar las personas que hay en el centro.– Solo los datos de profesores y alumnos.– Alumnos: DNI, nombre, dirección, teléfono, estudios previos.– Profesores: DNI, nombre, dirección, teléfono, titulación, fecha de nacimiento.
25
• Las jerarquías se dividen en cuatro clases (Solapamiento, Exclusividad, Totalidad y Parcialidad) atendiendo si sus subtipos se solapan o no y a si la unión de los subtipos recubre en su totalidad a el supertipo.
– Solapamiento : Se da cuando un mismo ejemplar del supertipo puede pertenecer a más de un subtipo.
26
– Exclusividad : Se da cuando un mismo ejemplar del supertipo solo puede pertenecer a un subtipo
– Totalidad : Se da un ejemplar del supertipo puede pertenecer a un subtipo o a varios.
27
– Parcialidad : Se da cuando no todo ejemplar del supertipo puede pertenecer a algún subtipo
Debido a esto se dan cuatro casos:
• Solapamiento + Parcialidad
28
• Exclusividad + Parcialidad
• Exclusividad + Totalidad
29
• Solapamiento + Totalidad
Ejercicio:
• Guardar los datos de los profesores de una universidad.
– Profesores: DNI, nombre y teléfono.– Los que son doctores: Nombre de su tesis y año en que la sacaron.– Los que no son doctores: Años de experiencia.
30
Ejercicio:
• Guardar información de todas las personas del centro.
– Personas: DNI y nombre.– Profesores: Titulación.– Alumnos: Estudios previos.– Un profesor no puede ser alumno.
Ejercicio:
• Realizar un esquema E/R en el que se recoja la siguiente información:
– Ganaderos: DNI, nombre, dirección y teléfono.– Vacas que posee cada ganadero: Nº de identificación, nombre, fec. Nacimiento y raza.– Veterinarios que tratan a esas vacas: DNI, nombre y teléfono.
• Cardinalidades
– Un veterinario puede tratar varias vacas y una vaca puede ser tratada por varios veterinarios.
– Un ganadero puede tener varias vacas y una vaca solo puede tener un ganadero.– Un veterinario no puede ser ganadero.
31
4.6 Modelo relacional
• Es un modelo propio de ciertos sistemas gestores de bases de datos (SGBD). Está orientado a describir los datos a un nivel lógico para un SGBD concreto.
4.7 Estructuras permitidas
• Tabla o relación : Tanto las entidades como las interrelaciones (Relaciones) del modelo E/R se convierten en tablas cuando traducimos un esquema de dicho modelo (E/R) al modelo relacional.
• Restricciones : Tiene que haber una clave primaria y no se permiten tuplas (filas) duplicadas (la clave de una fila no puede ser usada por otra fila)
32
4.8 Transformación del modelo E/R al modelo relacional
• Toda entidad se transforma en una tabla.
• Toda interrelación N:M se transforma en una tabla.
• Toda interrelación 1:N
– Propaga su clave y desaparece.– Se convierte en una tabla.
“Nota”
Para pasar del modelo E/R al relacional se recomienda usar los grafos relacionales.
Ejemplos:
• Toda entidad se transforma en una tabla.
33
• Toda interrelación N:M se transforma en una tabla.
• Toda interrelación 1:N propaga su clave desapareciendo.
Ejercicio:
• Dado el siguiente esquema E/R, hacer el grafo.
34
Ejercicio:
• Dado el siguiente esquema E/R, hacer el grafo.
4.9 Realizar el grafo de una jerarquía
35
Ejercicio:
• Dado el siguiente esquema E/R, hacer el grafo.
36
Ejercicio:
• Gestionar el almacén de datos de información de una biblioteca. Se guarda información de los libros que se quieren tener en la biblioteca, así como de los ejemplares de cada libro.
– Libro: Código, título y autor.– Ejemplares: Fec. Compra y estado.– Socios: DNI, nombre y fec. De alta.– La clave del ejemplar a partir del libro al cual pertenece más un número.– Cuando se presta un libro se registra: Fec. Préstamo, fec. Devolución y fec. Devuelto.
• Cardinalidades:
– Un socio puede tomar prestado más de un ejemplar de un libro.– Un ejemplar puede ser llevado por varios socios.
37
“Nota”
Cuando una entidad depende de si misma, se le denomina reflexiva.
Ejercicio:
• Base de datos de una enciclopedia.
– Tema: Código y título tema.
• Cardinalidades:
– Un tema depende de otro tema.
38
Ejercicio:
• Base de datos de una empresa.
– Empleado: Código, nombre y puesto.
• Cardinalidades:
– Un empleado depende de otro empleado.
39
5. Como iniciar oracle Database 11g Express Edition
• conn / as sysdba• alter user (Nombre de usuario) account unlock: Desbloquea la cuenta del usuario que
queramos.• alter user (Nombre de usuario) identified by (Contraseña): Para cambiar la contraseña
del usuario que queramos.
5.1 Oracle
• conn hr/hr: Para conectar como usuario hr con la contraseña hr.• conn sys/orcl as sysdba: Si al poner el comando anterior no funciona, se pone este.
5.2 Comandos y consultas en una base de datos
• Select * from cat; Para ver las tablas del usuario actual.
• Describe (nombre de la tabla que queramos); Para ver la tabla que queremos.
40
• Select * from (nombre de la tabla que queramos); Para ver todos los datos que tiene dentro la tabla.
• Select country_name from countries where region_id=2; Para ver los nombres de la región 2.
“Nota”
country_name: Es un campo de la tabla countries.countries: La tabla que seleccionada.where: Condición que se pone.
• Insert into (nombre de la tabla que queramos) values; Para insertar datos dentro de la tabla ('TO','Tourilandia',2).
“Nota”
El texto de los valores que se introducen va entre comillas simples, y si es solo un número, sin texto, se pone sin comillas.
41
• Commit; Para guardar los cambios.• Rollback; Se deshacen todos los cambios desde el último commit.• Delete from (nombre de la tabla que queramos); Borra todos los datos de la tabla.• Drop (nombre de la tabla que queramos); Borra la tabla.• Delete from regions where region_id=2; Borra la fila que cumpla lo que le decimos.• Update alumnos set codp='p2' where name='victor'; Modifica un valor de la tabla
alumnos.• Select coda,name from alumnos where codp='p1'; Muestra coda y name de la tabla
alumnos cuando codp = 1.• Select name from alumnos where coda>1; Muestra name de la tabla alumnos cuan coda
sea mayor a 1.• Update alumnos set nota=nota+1; De esta forma le subimos un punto la nota a todos los
alumnos.
5.3 Funciones agregadas a SQL
• Count (*); Cuenta filas.• Select count(*) from alumnos; Cuenta el número de alumnos.• Select count(*) from alumnos where codp='p2'; Solo cuenta a los alumnos que tengan p2.• Null; Valor nulo.• Update alumnos set nota=null where coda=4; Anular la nota al alumno 4.
Ejercicios:
“Nota”
Las consultas están hechas en tablas que previamente han sido metidas en la base.
• Select avg(nota) from alumnos; Muestra la nota media de todos los alumnos (no tiene en cuenta los nulos).
• Select sum(nota)/count(coda) from alunmos; Muestra la nota media de todos los alumnos (tiene en cuenta los nulos).
• Select avg(nota) from alumnos where codp='p2'; Muestra la nota media de los alumnos que tienen como profesor a p2 (no tiene en cuenta los nulos).
• Select avg(nota) from alumnos where codp in (select codp from profesores where nome='eva'); Selecciona la nota media de todos los alumnos que tienen de profesor a eva.
• Select nome from alumnos where nome like '%a%'; Muestra los nombres de los alumnos que tengan una a en su nombre.
• Select nome from alumnos where codp='p1' and nota>5; Muestra el nombre de los alumnos que tengan como profesor a p1 y la nota sea mayor a 5.
• Select nome from alumnos where nota>6 and nota<8; Muestra el nombre de los alumnos que tengan una nota mayor a 6 y menor a 8.
• Select alumnos.nome,profesores.nome from alumnos,profesores where alumnos.codp=profesores.codp; Muestra el nombre de los alumnos y los profesores que correspondan a cada alumno.
• Select codp,count(codp) from alumnos group by codp; Selecciona a los profesores y pone el número de alumnos que tiene cada profesor agrupados (group by) por profesor.
42
• Select nome,count(nome) from alumnos group by nome; Muestra cuantos alumnos tienen el mismo nombre.
• Select avg(salario) from xogador; Muestra el salario medio de todos los jugadores.
• Select nomx from xogador where salario > (select avg(salario) from xogador); Muestra todos los nombres de los jugadores que cobren más de la media.
• Select codequ,avg(salario) from xogador group by codequ; Muestra el código del equipo y cuanto cobran de media sus jugadores agrupados por el código del equipo.
43
• Select codequ,avg(salario) from xogador group by codequ having avg(salario) > 1500; Muestra los códigos de los equipos y la media de los salarios de los jugadores de cada equipo agrupados por el código del equipo, siempre que la media sea mayor a 1500.
• Select codequ,count(codx) from xogador group by codequ; Muestra los códigos de los equipos y cuenta el número de jugadores que tiene cada equipo y los agrupa por codequ.
• Select codest,count(codpar) from partido group by codest; Muestra los códigos de los estadios y cuenta el número de partidos y los agrupa por codest.
44
• Select xogador.codx,codpar from xogador left join xoga on xogador.codx=xoga.codx; Muestra el código de los jugadores y el código del partido que juegan.
• Select nomp from persoas where dnip in (select dnip from farmaceuticos); Muestra el nombre de las personas que son farmacéuticos.
• Select nomf from farmacias where codc in (select codc from cidades where nomc='ferrol'); Muestra el nombre de las farmacias que hay en Ferrol.
45
• Select nomf from farmacias where codf in ( select codf from traballan where dnip in (select dnip from persoas where nomp='antia')); Muestra el nombre de farmacias en donde trabajan personas que se llaman antia.
• Select codc,nomc from componentes where codc in (select codc from componen where codm in (select codm from medicamentos where nomm='feparil')); Muestra el código y el nombre de los componentes de un medicamento llamado feparil.
“Nota”
Trabajar con varios selects en una consulta se denomina consulta con subselects.
46
• Select traballan.dnip,persoas.nomp,farmacias.nomf from traballan,persoas,farmacias where traballan.codf=farmacias.codf and traballan.dnip=persoas.dnip; Muestra el dni y el nombre de las personas y el nombre de la farmacia donde trabaja cada persona.
• Select nomp from persoas; Muestra el nombre de todas las personas.
• Select nomp from persoas where dnip in (select dnip from farmaceuticos); Muestra los nombres de las personas que sean farmacéuticos.
47
• Select nomp,ncolexiado from persoas left join farmaceuticos on persoas.dnip=farmaceuticos.dnip; Muestra el nombre de todas las personas y su número de colegiado si lo son.
• Select nomp,ncolexiado from persoas left join farmaceuticos on persoas.dnip=farmaceuticos.dnip where nomp like '%o%'; Muestra los nombres de la personas que tengan una “o” en su nombre y su número de colegiado si lo son.
• Select nomm from medicamentos where cif in (select cif from laboratorios where noml='esteve'); Muestra el nombre de los medicamentos del laboratorio esteve.
48
• Select nomm from medicamentos where coda in (select coda from accions where noma='antiacida'); Muestra los nombres de los medicamentos que tienen acción antiácida.
• Select nomm from medicamentos where cif in (select cif from laboratorios where noml='esteve') and coda in (select coda from accions where noma='antiacida'); Muestra los nombres de los medicamentos del laboratorio esteve que tienen una acción antiácida.
• Select noml,count(codm) from laboratorios,medicamentos where laboratorios.cif=medicamentos.cif group by noml; Muestra el nombre de los laboratorios y el número de medicamentos que tiene cada laboratorio.
49
• Select noma,count(codm) from accions,medicamentos where accions.coda=medicamentos.coda group by noma; Muestra el nombre de las acciones que tienen los medicamentos y el número de medicamentos que tiene cada acción.
• Select nomc from componentes where codc not in (select codc from componen); Muestra los nombres de los componentes que no forman parte de ningún medicamento.
“Nota”
Cuando en una consulta hay que poner dos campos iguales se pone un “alias” para diferenciarlos. Profesores = p Alumnos = aTambién se puede indicar con un punto para diferenciarlos.Ejemplo:Select alumnos.nome,profesores.nome from alumnos,profesores where alumnos.codp=profesores.codp;Alumnos.nome = Nombre de los alumnos.Profesores.nome = Nombre de los profesores.
50
“Nota”
% = Cualquier carácter. _ (guión bajo) = 1 carácter and = y or = oHaving = Se pone siempre con un group by.Left join = Combina los valores de la primera tabla con los valores de la segunda tabla. Siempre devolverá las filas de la primera tabla, incluso aunque no cumplan la condición.
“Nota”
Después de poner left join siempre hay que poner “on”.
5.4 Como realizar un script (para meter unas tablas)
• En un fichero de texto plano (bloc de notas, por ejemplo) se escribe:
Drop table alumnos;Drop table profesores;Purge recyclebin;Create table profesores(Codp varchar2(2),Nome varchar2(10),Primary key (codp));Create table alumnos(Coda integer,Nome varchar2(15),Codp varchar2(2),Primary key (Coda),Foreign key (Codp) references profesores (Codp));Commit;
“Nota”
Drop table = Borra la tabla que se le diga.Purge recyclebin = Vacía la papelera de reciclaje.Create table = Crea la tabla que se le diga.Varchar2 = Nos dice que es un campo de caracteres.Integer = Nos dice que es un campo número.Primary key = Es la clave primaria de la tabla.Foreign key = Es la clave primaria de la otra tabla, pero como es una interrelación 1:N pasa a esta tabla como un atributo. References = Nos dice a que tabla hace referencia (pertenece) este campo.Commit = Guarda los cambios hechos.
51
5.4.1 Como lanzar el script en la base de datos
• Para lanzar el script solo hay que poner en la base de datos:
@'Ruta en la que este situado el script\nombre del script.sql'
“Nota”
El script tiene que ser un archivo .sql
6. Herramientas para importar y exportar los datos de un usuario (Centos)
6.1 Exportación
• Para exportar hay que:
– Levantar la base de datos : . oraenv | orcl (o como se llame la base) | sqlplus / as sysdba | startup | conn hr/hr (o el usuario y contraseña que sea).
– Trabajar con el objeto directory
• Lo primero que hay que hacer es:
– Crear una carpeta en el escritorio (o donde queramos) que se llame dhr (o como queramos).
– Crear el objeto directory: Create directory nombre del directorio (el que queramos) as la ruta de la carpeta creada antes (si está en el escritorio) '/home/oracle/Desktop/dhr';
“Nota”
Al objeto directory le voy a llamar directoriohr.
El comando quedaría así:
Create directory directoriohr as '/home/oracle/Desktop/dhr';
Si ya hemos creado un objeto directory queremos crear otro con ese mismo nombre se hace:
Create or replace directory directoriohr as '/home/oracle/Desktop/dhr';
52
– Se va a la base de datos y, como sys, se le dan los permisos de lectura y escritura al usuario que queremos que realice la copia: Grant read,write on directory directoriohr to hr (o al usuario que queramos).
– Se crean dos archivos dentro de la carpeta dhr: Los archivos se llamarán nombre.txt (el nombre que queramos).
– Después de crear los archivos, salimos de la base de datos: exit– Ahora sin conectar la base de datos nos ponemos en la ruta de la carpeta:
cd /home/oracle/Desktop/dhr.– Después de ponernos en la ruta y sin conectar la base de datos ponemos: expdp hr/hr
parfile = nombre2.txt.– Ahora vamos a la carpeta dhr y miramos como se han creado dos archivos, un .dmp y un
.log.
• Los archivos tienen que estar formados por :
nombre.txt (archivo opcional)
ESTIMATE_ONLY = YNOLOGFILE = Y
nombre2.txt
DIRECTORY = directoriohrDUMPFILE = F_EXPORT_hr.dmpLOGFILE = EXPORT_hr.log
Otras ordenes que se pueden poner en el archivo:
– TABLE_EXISTS_ACTION = REPLACE------ Si ya existen las tablas las reescribe. APPEND-------- Escribe datos aunque ya existan.
– CONTENT = METADATA_ONLY------- Copia solo la estructura. DATA_ONLY--------------- Copia solo los datos, no la estructura. ALL--------------------------- Copia todo
– TABLES = TABLA------- Selecciona la tabla que queremos.– QUERY = TABLA: “where cod>2”------ Pone una condición.– EXCLUDE = CONSTRAINT------ Excluimos todo lo que tenga que ver con la clave
primaria.– REMAP_SCHEMA = scott:hr------ Manda tablas de un usuario a otro.
53
6.2 Importación
• Para importar hay que:
– Se crea un archivo dentro de la carpeta dhr: El archivo se llamará nombre.txt (el nombre que queramos).
– Después de crear los archivos, salimos de la base de datos: exit– Ahora sin conectar la base de datos nos ponemos en la ruta de la carpeta:
cd /home/oracle/Desktop/dhr.– Después de ponernos en la ruta y sin conectar la base de datos ponemos: impdp hr/hr
parfile = nombre3.txt.
• El archivo tiene que estar formado por :
nombre3.txt
DIRECTORY = directoriohrDUMPFILE = F_EXPORT_hr.dmpLOGFILE = IMPORT_hr.log
6.3 Herramientas para cargar datos de una base de datos en otra
SQL LOADER (Carga variable)
• Para cargar datos de una base de datos en otra hay que seguir los siguientes pasos:
– Se crean dos archivos: Un archivo se llamará nombre.txt y el otro nombre.ctl (el nombre que queramos).
– Después de crear el archivo, salimos de la base de datos: exit– Ahora sin conectar la base de datos nos ponemos en la ruta de la carpeta:
cd /home/oracle/Desktop/dhr.– Después de ponernos en la ruta y sin conectar la base de datos ponemos: sqlldr hr/hr
control = nombre.ctl.
• Los archivos tienen que estar formados por :
nombre.txt
1,angel,sanchez,h,070119852,maría,gonzalez,m,3,maría,sanchez,m,27012007
Los datos van separados por comas (porque son de carga variable).
54
nombre.ctl
LOAD DATA INFILE nombre.txt INTO TABLE nombre de la tablaAPPENDFIELDS TERMINATED BY ','TRAILING NULLCOLS(NUMERO, NOME, …..... DATAN “TO_DATE (:DATAN,'DDMMYYYY')”)
“Nota”
También se puede poner:Load data infile * into table nombre de la tablaPero para poner esto, el archivo .ctl tiene que ser así.LOAD DATA INFILE * INTO TABLE NOMBRE DE LA TABLAAPPENDFIELDS TERMINATED BY ','TRAILING NULLCOLS(NUMERO,NOME,APELIDOS,SEXO,DATAN "TO_DATE(:DATAN,'DDMMYYYY')")BEGINDATA1,angel,sanchez,h,070119852,maria,gonzalez,m,3,maria,sanchez,m,27012007
• Otros comandos
– Alter user hr identified by hr; Para cambiar la contraseña de un usuario.– Select * from all_directories where directory_name:'DIRECTORIOSCOTT'; Para
saber en la ruta que está el objeto directory (el objeto directory tiene que estar escrito en mayúsculas, que no tiene que ser DIRECTORIOSCOTT, esto es un ejemplo)
– Select directory_name from all_directories; Para ver todas las rutas.– (NOME, …....), NUMERO “s_parents.nextval”): Para crear un generador de
secuencias (tiene que existir una sequence en la base de datos).
SQL LOADER (Carga fija)
• Para cargar datos de una base de datos en otra hay que seguir los siguientes pasos:
– Se crean dos archivos: Un archivo se llamará nombre.txt y el otro nombre.ctl (el nombre que queramos).
– Después de crear el archivo, salimos de la base de datos: exit– Ahora sin conectar la base de datos nos ponemos en la ruta de la carpeta:
cd /home/oracle/Desktop/dhr.– Después de ponernos en la ruta y sin conectar la base de datos ponemos: sqlldr hr/hr
control = nombre.ctl.
55
• Los archivos tienen que estar formados por :
nombre.txt
angelsanchez h07011985mariagonzalezmmariasanchez m27012007
Los datos van todos juntos, y siempre dejando la misma distancia unos datos de otros y hay que dejar espacios (porque son de carga fija).
nombre.ctl
LOAD DATA INFILE nombre.txt INTO TABLE nombre de la tablaAPPENDTRAILING NULLCOLS(NOME position (01:05),APELLIDOS position (06:13),SEXO position (14:14),DATAN position (15:22) “TO_DATE (:DATAN,'DDMMYYYY')”,NUMERO “s_parentes.NEXTVAL”)
“Nota”
FILLER: Para no cargar filas. Solo se usa cuando hay una condición (when) con ese campo.Ejemplo:SEXO FILLER POSITION ()WHEN: Pone una condición
• Si se quiere meter datos en dos tablas distintas a la vez se hace esto:
LOAD DATA INFILE nombre.txt INTO TABLE nombre de la tablaAPPENDWHEN (sexo=“m”)TRAILING NULLCOLS(NOME position (01:05),APELIDOS position (06:13),SEXO FILLER position (14:14),DATAN FILLER position (15:22),NUMERO “s_parentes.nextval”)INTO TABLE nombre de la tablaAPPENDWHEN (sexo=“h”)TRAILING NULLCOLS(NOME position (01:05),APELIDOS position (06:13),SEXO FILLER position (14:14),DATAN position (15:22)“TO_DATE(:DATAN,'DDMMYYYY')”,NUMERO “s_parentes.nextval”)
56
6.4 Exportar datos de una base de datos a un archivo .txt (de texto plano)
Datos de longitud variable
• Los pasos para realizar la exportación son:
– Se crea un archivo .sql.
nombre .sql
SET ECHO OFFSET HEADING OFFSET FEEDBACK OFFSET LINESIZE 1000SET NEWPAGE NONESET TRIMSPOOL ONSET TERMOUT OFFSPOOL nombre.txtselect codx||','||nomx||','||salario||','||TO_CHAR (DATAN,'DDMMYYYY') from xogador;/SPOOL OFFSET HEADING ONSET FEEDBACK ONSET LINESIZE 80SET NEWPAGE 1SET TERMOUT ON
“Nota”
SET ECHO OFF: Elimina el eco de las consultas.SET HEADING OFF: Oculta las cabeceras.SET FEEDBACK OFF: Oculta el número de línea.SET LINESIZE 1000: Oculta el dimensionado de líneas.SET NEWPAGE NONE: Elimina el salto de línea en el cambio de página.SET TRIMSPOOL ON: Suprime los espacios en el fin de línea.SET TERMOUT OFF: No lanza la visualización por pantalla.SPOOL nombre.txt: Crea el fichero en donde se van a guardar los datos.select codx||','||nomx||','||salario||','||TO_CHAR (DATAN,'DDMMYYYY') from xogador;/SPOOL OFFSET HEADING ONSET FEEDBACK ONSET LINESIZE 80SET NEWPAGE 1SET TERMOUT ON
57
Datos de longitud fija
• Los pasos para realizar la exportación son:
– Se crea un archivo .sql.
nombre .sql
SET ECHO OFFSET HEADING OFFSET FEEDBACK OFFSET LINESIZE 1000SET NEWPAGE NONESET TRIMSPOOL ONSET TERMOUT OFFSPOOL nombre.txtselect RPAD(codx,3,' ')||RPAD(nomx,11,' ')||RPAD(nvl(salario,0),38,' ')||TO_CHAR(DATAN,'DDMMYYYY') from xogador;/SPOOL OFFSET HEADING ONSET FEEDBACK ONSET LINESIZE 80SET NEWPAGE 1SET TERMOUT ON
7. Administración
• Perfil : Es un grupo de recursos limitados que tienen un nombre en común.• Diccionario : Diccionario de metadatos (información de toda la base de datos).
7.1 Perfiles
58
7.2 Opciones de configuración del perfil
• SESSIONS_PER_USE UNLIMITED: Un usuario puede abrir todas las sesiones que quiera.• FAILED_LOGIN_ATTEMPS 10: Máximo de fallos al logearse.• PASSWORD_LIFE_TIME 180: Tiempo de vida de la password.• IDLE_TIME UNLIMITED: Tiempo de espera sin que se realice nada, la sesión se cierra.• PASSWORD_GRACE_TIME: Tiempo que se nos da para cambiar la contraseña una vez
caducada.
7.3 Perfiles
• Crear de un perfil :
– Create profile nombre limit;– Sessions_per_user 2;– Failed_login_attemps 3;
• Modificar el perfil :
– Alter profile nombre limit;– Sessions_per_user 3;
• Borrar el perfil :
– Drop profile nombre;
7.4 Usuarios
• Crear un usuario y asignarle un perfil :
– Create user nombre identified by contraseña profile nombre;
• Dar permiso a un usuario para conectarse :
– Grant connect to nombre de usuario;
• Dar permiso a un usuario para crear tablas :
– Grant create table to nombre de usuario;
• Dar cuota a un usuario para poder insertar valores :
– Alter user nombre quota 6 M on users;
59
• Desbloquear la cuenta de un usuario :
– Alter user nombre account unlock;
• Bloquear la cuenta de un usuario :
– Alter user nombre account lock;
• Activar sessions_per_user :
– Alter system set RESOURCE_LIMIT=true scope=memory;
“Nota”
– password expired: Obliga a cambiar la contraseña al entrar. (Create user X identified by X profile perfil1 password expired).
– scope: memory, spfile o both.
7.5 Privilegios
• Son derecho que tienen los usuarios.
Hay dos tipos de privilegios:
– Privilegios de sistema : Relacionados con instrucciones DLL (lenguaje de definición de datos) (create table) y de control (connect, etc...).
Ejemplo:
Grant create table to nombre de usuario;Grant create session to nombre de usuario;
Grant connect to nombre de usuario;
“Nota”
Grant create table to nombre de usuario with admin option;
With admin option: Le da al usuario el poder de otorgar ese mismo privilegio a otros usuarios.
Para quitar With admin option: Hay que revocárselo y volver a dárselo.
Revoke create table to nombre de usuario with admin option;
60
– Privilegios de objeto : Relacionados con los objetos propiedad de un usuario (con instrucciones DML)(insert, update y delete).
Ejemplo:
Alter user nombre de usuario quota 6 M on users;Alter user nombre de usuario ACCOUNT LOCK;
Alter user nombre de usuario ACCOUNT UNLOCK;Alter system set RESOURCE_LIMIT=true scope=memory
“Nota”
Grant create table to nombre de usuario with grant option;
With grant option: Le da al usuario el poder de otorgar ese mismo privilegio a otros usuarios.
Para quitar With grant option: Hay que revocárselo y volver a dárselo.
Revoke create table to nombre de usuario with grant option;
7.6 Dar permisos
• Se pueden dar permisos sobre los objetos que uno tiene.
– Grant insert on nombre de la tabla to nombre de usuario;– Grant select on nombre de la tabla to nombre de usuario;
• También se pueden dar permisos sobre los objetos de otros usuarios (pero entonces hay que poner esto).
– Grant insert on pepito.t to hr;– Grant select on pepito.t to hr;
“Nota”
Nombre de usuario.nombre de la tabla: Siempre que el que da el permiso no sea propietario de la tabla.
7.7 Quitar permisos
• Para quitar permisos solo hay que hacer:
– Revoke select on pepito.t from hr;
61
“Nota”
Al retirar los privilegios, de un usuario que le dio privilegios a otros, también se le retiran a los otros.Solo puede retirar privilegios el usuario que los dio.
7.8 Vistas
• Significado :
– dba_profiles: Información sobre los perfiles creados.– PROFILE: Nombre del perfil.– RESOURCE_NAME: Nombre del recurso.– RESOURCE_TYPE: Tipo del recurso.– LIMIT: Tiempo que dura la cuenta.– user_sys_privs: Información sobre los perfiles creados.– USERNAME: Usuario.– PRIVILEGE: privilegios.– ADMIN_OPTION: YES/NO.
• RESOURCE_NAME interesantes :
– SESSIONS_PER_USE: Un usuario puede abrir todas las sesiones que quiera.– FAILED_LOGIN_ATTEMPS: Máximo de fallos al logearse.– PASSWORD_LIFE_TIME: Tiempo de vida de la password.– IDLE_TIME: Tiempo de espera sin que se realice nada, la sesión se cierra.– PASSWORD_GRACE_TIME: Tiempo que se nos da para cambiar la contraseña una
vez caducada.
7.9 Roles
• Rol :
– Agrupamiento de privilegios de sistema y de objetos.
• Para ver los privilegios de sistema:
– Select privilege from dba_sys_privs where grantee='ROL';– Select * from user_sys_privs;
• Para ver los privilegios de objeto:
– Select privilege from dba_tab_privs where grantee='ROL/USUARIO';– Select privilege from dba_col_privs where grantee='ROL USUARIO';– Select * from user_tab_privs;– Select * from user_col_privs;
62
• Crear un Rol :
– Create role nombre del rol;
• Meter privilegios en el rol :
– Grant create session to nombre del rol:– Grant select on nombre de la tabla to nombre del rol;– Grant insert on nombre de la tabla to nombre del rol;
• Asignar un rol a un usuario ;
– Grant nombre de rol to nombre del usuario;
• Para quitar un rol ;
– Revoke nombre del rol from nombre de usuario;
• Para quitar privilegios al rol ;
– Revoke select on nombre de la tabla from nombre del rol;
• Crear secuencia en un usuario ;
– Grant create sequence to PUBLIC;
“Nota”
Si se le da un privilegio a PUBLIC, por defecto ese privilegio lo adquieren todos los usuarios.
Otras vistas:
• Select owner,table_name,grantor,grantee,privilege from user_tab_privs; Obtiene información de los privilegios sobre las tablas a las que tiene acceso el usuario.
• Select owner,column_name,grantor,grantee,privilege from user_col_privs; Obtiene información de los privilegios sobre las columnas a las que tiene acceso el usuario.
• Select * from USER_ROLE_PRIVS; Para ver los roles que tiene el usuario actual.• Select privilege from dba_sys_privs where grantee='ROL'; Para ver los privilegios de
sistema que tiene un rol.• Select privilege from dba_tab_privs where grantee='ROL'; Para ver los privilegios de
objeto que tiene un rol.
63
8. Crear una nueva base de datos
Para crear una nueva base de datos hay que:
• Abrir la terminal.• Crear los directorios de repositorio de la nueva base.
cd /home/oracle/app/oracle/admin mkdir bbackups
cd bbackups mkdir adump
mkdir dpdump mkdir pfile
• Crear el fichero de parámetros servidor.
gedit /home/oracle/app/oracle/admin/bbackups/pfile/init.ora
compatible='11.2.0.0.0' control_files='/home/oracle/app/oracle/oradata/bbackups/control01.ctl' control_files='/home/oracle/app/oracle/oradata/bbackups/control02.ctl'
db_name='bbackups' db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=3882M memory_target=843055104
• conectamos como sqlplus / as sysdba.
export ORACLE_SID=bbackups (enter) export ORACLE_BASE=/home/oracle/app/oracle (enter)
. oraenv
ORACLE_SID = [bbackups] ?
ORACLE_HOME = [/home/oracle] ? /home/oracle/app/oracle/product/11.2.0/dbhome_1
SQL> connect / as sysdba *(idle instance)
• Creamos el spfile desde el pfile anterior,
SQL> create spfile= '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebbackups.ora' from pfile='/home/oracle/app/oracle/admin/bbackups/pfile/init.ora';
64
• Y ahora se pone.
SQL> startup nomount
• Ahora se crea la base de datos mediante:
DEFINE ruta= /home/oracle/app/oracle/oradata/bbackups
y mediante este archivo.sql
create database bbackups user sys identified by orcl user system identified by orcl set default smallfile tablespace datafile '&ruta/system01.dbf' size 200M autoextend on next 10M extent management local sysaux datafile '&ruta/sysaux01.dbf' size 100M autoextend on next 10M logfile group 1 ('&ruta/redo01a.log','&ruta/redo01b.log') size 50M, group 2 ('&ruta/redo02a.log','&ruta/redo02b.log') size 50M, group 3 ('&ruta/redo03a.log','&ruta/redo03b.log') size 50M smallfile undo tablespace undotbs datafile '&ruta/undotbs01.dbf' size 100M autoextend on next 10M maxsize 1024M smallfile default temporary tablespace temp tempfile '&ruta/temp01.dbf' size 100M autoextend on next 10M maxsize 1024M default tablespace deftbs datafile '&ruta/deftbs01.dbf' size 10M autoextend on next 10M maxsize 500M extent management local autoallocate noarchivelog character set WE8ISO8859P15 national character set AL16UTF16 set TIME_ZONE='Europe/Madrid' maxinstances 1 maxlogfiles 16 maxlogmembers 4 maxdatafiles 128 /
65
Consultas que se pueden realizar para ver el estado de la base de datos:
• Select log_mode from v$database; Para ver los archive.log.• Select status from v$database; Para ver el estado de la base de datos.• Select status,sequence#,archived,group# from v$log; Para ver en que grupo está
grabando.• Alter system switch logfile; Provocar un salto de grupo.
8.1 Hacer copias de una base de datos
• Se insertan datos en una tabla y hacemos un commit.• Salimos (exit) y nos ponemos como sys para hacer un shutdown.• Ahora se haría startup mount.• Abrimos otra consola y ponemos:
– oraenv– bbackups– RMAN Target /– backup database tag='copia1';
• De esta forma ya estaría hecha la copia de la base bbackups.
“Nota”
Para ver las copias que tenemos de la base se va a:
– RMAN Target /– cd /home/oracle/app/oracle– list backup of database
Para activar la base de datos
– RMAN Target /– alter database open
Esto solo se hace una vez y es para que se guarden los control files:
RMANconfigure controlfile.
“Nota”
Para pasar de un grupo en el que se graba a otro se pone:
Alter system switch logfile (el número de veces que queramos).
66
8.2 Para recuperar los datos que no se copiaron (no archivelog)
• Hay que hacer:
– Se insertan datos en una tabla y le damos a commit.– Luego salimos (exit).– RMAN Target /– . oraenv– bbackups– RMAN Target /– Shutdown
• Y ahora:
– Startup mount– Restore database– Recover database– Alter database open resetlogs (y comprobar en la tabla si los datos están bien).
8.3 Para recuperar los datos que no se copiaron (archivelog)
“Nota”
Desde sys se pone: Alter database archivelog;
• Hay que hacer:
– Se insertan datos en una tabla y le damos a commit.– Luego salimos (exit).– RMAN Target /– . oraenv– bbackups– RMAN Target /– Shutdown
• Y ahora:
– Startup mount– Restore database– Recover database– Alter database open (y comprobar en la tabla si los datos están bien).
67
9. Algunas practicas
• Grafos
Vinhedo:
68
Startreck:
69
Piscina:
70
Alugueres:
• Esquemas E/R
Alugueres:
71
Piscina:
72
FIN
73