Conceptos Clave de Bases de Datos y Comandos Oracle SQL/PLSQL

Conceptos Fundamentales de Bases de Datos

Definiciones Clave

• Base de Datos: Es un conjunto de datos pertenecientes a un mismo contexto, almacenado sistemáticamente para su utilización, que incluye esquemas, vistas, definiciones, etc.
• Esquema de BD: Estructura que contiene tablas, relaciones, también llamado lenguaje formal soportado por el sistema de bases de datos. Los esquemas generalmente son almacenados en los diccionarios de datos.
• Esquema Conceptual: Contiene mapa de conceptos y relaciones. Es una herramienta gráfica.
• Esquema Lógico: Contiene entidades, atributos y relaciones.
• Esquema Físico: Es la aplicación de un esquema lógico.
• Esquema Objeto: Base de datos objeto (ej. Oracle).
• Catálogo: Es una base de datos que permite almacenar los objetos dentro de la base de datos.
• DDL: Lenguaje de Definición de Datos (CREATE, ALTER, DROP).
• DML: Lenguaje de Manipulación de Datos (INSERT, UPDATE, DELETE).
• Índice: Cluster que permite almacenar la PK en páginas.
• Instancia de BD: Conjunto de procesos del servidor que tiene su propia área global de memoria y una base de datos asociada a ella.
• Tablespace: División lógica de la base de datos.
• Segmento: Es la separación entre diversos objetos que se pueden almacenar en un espacio de tablas.
• Estructura de una BD: Tablas, columnas, restricciones, usuarios, esquemas, índices, cluster, vistas, secuencias, procedimientos.
• Vista: Relación virtual entre tablas y vistas de una base de datos, almacenada como objeto en la BD.
• Vista Materializada: Asegura que el servidor de BD puede asignar correctamente la operación de inserción, actualización o eliminación a través de triggers.

El Catálogo o Diccionario de BD

Tiene varias categorías de vistas:

• DBA: Accesible para usuarios con privilegios de administrador.
• ALL: Muestra objetos accesibles para el usuario actual (dominio privilegiado y actual).
• USER: Muestra objetos propiedad del usuario actual (dominio actual).

Roles y PL/SQL

• Rol: Conjunto de privilegios que se pueden otorgar a otro usuario o rol.
• PL/SQL: Es un lenguaje de programación incorporado en Oracle.
  • Contiene secciones como DECLARE, BEGIN, EXCEPTION, END.

Ejemplos de Comandos SQL y PL/SQL en Oracle

Creación de Objetos

Crear Tabla

create table cliente (
  cod_cli integer not null,
  nom_cli varchar2(15) not null,
  apell_cli varchar2(20) not null,
  dir_cli varchar2(35) not null,
  ciudad_cli varchar2(20),
  cpostal varchar2(10),
  fono varchar2(12) not null,
  empresa varchar2(35),
  constraint PK_CLIENTE primary key (cod_cli)
);

Crear Índice

create index DEPT_JEFE_DEPT_FK on departamento (
  jefe_dept ASC
);

Crear Vista

Creamos la vista:

create view mostrar_jefes as
select e.nom_emp as Nombre_empleado, e.apell_emp as Apellido_empleado,
       jefe.nom_emp as Nombre_jefe, jefe.apell_emp as Apellido_jefe, jefe.nom_dept as Nombre_depto_jefe
from empleado e,
     (select nom_emp, apell_emp, cod_jefe, nom_dept from empleado
      join departamento on cod_emp = jefe_dept) jefe
where e.cod_jefe = jefe.cod_jefe
group by e.nom_emp, e.apell_emp, jefe.nom_emp, jefe.apell_emp, jefe.nom_dept;

Gestión de Usuarios y Privilegios

Crear Usuario

CREATE USER ADMINISTRADOR IDENTIFIED BY ADMIN
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA 500K ON USERS;

Expirar Contraseña

PASSWORD EXPIRE;

Otorgar Privilegios

• Dar privilegio de crear sesión:

GRANT CREATE SESSION TO ADMINISTRADOR;

Dar privilegio para borrar usuario:

GRANT DROP USER TO ADMINISTRADOR;

Dar privilegios de crear tablas, procedimientos, funciones, etc.:

GRANT CREATE USER, ALTER USER, DROP USER TO ahernandez;

Dar permisos a una tabla (Ventas) para un usuario particular:

GRANT SELECT, INSERT, UPDATE ON analista.venta TO jperez;

Dar privilegio de profile al administrador:

GRANT ALTER PROFILE TO ADMINISTRADOR;

Conectarse como usuario y dar a otro usuario permiso para seleccionar con opción de otorgar:

GRANT SELECT ON PRUEBA1.CODIGOS TO PRUEBA2 WITH GRANT OPTION;

Modificar Usuario

• Modificar tablespace por defecto:

ALTER USER ADMINISTRADOR DEFAULT TABLESPACE feo;

Desbloquear cuenta:

ALTER USER ADMINISTRADOR ACCOUNT UNLOCK;

Gestión de Roles

• Crear rol "secreto" identificado por "total":

CREATE ROLE SECRETO IDENTIFIED BY TOTAL;

Asignar rol a otro usuario:

GRANT SECRETO TO PRUEBA;

Borrar rol:

DROP ROLE ADMINISTRADOR;

Revocar Privilegios

REVOKE ... FROM ...;

Borrar Usuario

• Borrar usuario en cascada:

DROP USER ... CASCADE;

Otros Comandos CREATE

• CREATE SEQUENCE
• CREATE SYNONYM FOR
• CREATE INDEX
• CREATE OR REPLACE AS