Restricciones

Hemos visto que una de las alternativas que Oracle ofrece para asegurar la integridad de datos es el uso de restricciones (constraints). Aprendimos que las restricciones se establecen en tablas y campos asegurando que los datos sean válidos y que las relaciones entre las tablas se mantengan.

Vimos tres tipos de restricciones:

primary key, unique y check. Ahora veremos "foreign key".

Con la restricción "foreign key" se define un campo (o varios) cuyos valores coinciden con la clave primaria de la misma tabla o de otra, es decir, se define una referencia a un campo con una restricción "primary key" o "unique" de la misma tabla o de otra.

La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. Por ejemplo, controla que si se agrega un código de editorial en la tabla "libros", tal código exista en la tabla "editoriales".

También controla que no pueda eliminarse un registro de una tabla ni modificar la clave primaria si una clave externa hace referencia al registro. Por ejemplo, que no se pueda eliminar o modificar un código de "editoriales" si existen libros con dicho código.

La siguiente es la sintaxis parcial general para agregar una restricción "foreign key":

alter table NOMBRETABLA1

add constraint NOMBRERESTRICCION

foreign key (CAMPOCLAVEFORANEA)

references NOMBRETABLA2 (CAMPOCLAVEPRIMARIA);

Analicémosla:

- NOMBRETABLA1 referencia el nombre de la tabla a la cual le aplicamos la restricción,

- NOMBRERESTRICCION es el nombre que le damos a la misma,

- luego de "foreign key", entre paréntesis se coloca el campo de la tabla a la que le aplicamos la restricción que será establecida como clave foránea,

- luego de "references" indicamos el nombre de la tabla referenciada y el campo que es clave primaria en la misma, a la cual hace referencia la clave foránea. El campo de la tabla referenciada debe tener definida una restricción "primary key" o "unique"; si no la tiene, aparece un mensaje de error.

Para agregar una restricción "foreign key" al campo "codigoeditorial" de "libros", tipeamos:

alter table libros

add constraint FK_libros_codigoeditorial

foreign key (codigoeditorial)

references editoriales(codigo);

En el ejemplo implementamos una restricción "foreign key" para asegurarnos que el código de la editorial de la de la tabla "libros" ("codigoeditorial") esté asociada con un código válido en la tabla "editoriales" ("codigo").

Cuando agregamos cualquier restricción a una tabla que contiene información, Oracle controla los datos existentes para confirmar que cumplen con la restricción, si no los cumple, la restricción no se aplica y aparece un mensaje de error. Por ejemplo, si intentamos agregar una restricción "foreign key" a la tabla "libros" y existe un libro con un valor de código para editorial que no existe en la tabla "editoriales", la restricción no se agrega.

Actúa en inserciones. Si intentamos ingresar un registro (un libro) con un valor de clave foránea (codigoeditorial) que no existe en la tabla referenciada (editoriales), Oracle muestra un mensaje de error. Si al ingresar un registro (un libro), no colocamos el valor para el campo clave foránea (codigoeditorial), almacenará "null", porque esta restricción permite valores nulos (a menos que se haya especificado lo contrario al definir el campo).

Actúa en eliminaciones y actualizaciones. Si intentamos eliminar un registro o modificar un valor de clave primaria de una tabla si una clave foránea hace referencia a dicho registro, Oracle no lo permite (excepto si se permite la acción en cascada, tema que veremos posteriormente). Por ejemplo, si intentamos eliminar una editorial a la que se hace referencia en "libros", aparece un mensaje de error.

Esta restricción (a diferencia de "primary key" y "unique") no crea índice automáticamente.

La cantidad y tipo de datos de los campos especificados luego de "foreign key" DEBEN coincidir con la cantidad y tipo de datos de los campos de la cláusula "references".

Esta restricción se puede definir dentro de la misma tabla (lo veremos más adelante) o entre distintas tablas.

Una tabla puede tener varias restricciones "foreign key".

No se puede eliminar una tabla referenciada en una restricción "foreign key", aparece un mensaje de error.

Consultas avanzadas

C

Consulta Oracle SQL para conocer Vista que muestra el estado de la base de datos:
select * from v$instance
•• Consulta Oracle SQL para conocer Consulta que muestra si la base de datos está abierta
select status from v$instance
•• Consulta Oracle SQL para conocer Vista que muestra los parámetros generales de Oracle
select * from v$system_parameter
•• Consulta Oracle SQL para conocer Versión de Oracle
select value from v$system_parameter where name = 'compatible'
•• Consulta Oracle SQL para conocer Ubicación y nombre del fichero spfile
select value from v$system_parameter where name = 'spfile'
•• Consulta Oracle SQL para conocer Ubicación y número de ficheros de control
select value from v$system_parameter where name = 'control_files'
•• Consulta Oracle SQL para conocer Nombre de la base de datos
select value from v$system_parameter where name = 'db_name'
•• Consulta Oracle SQL para conocer Vista que muestra las conexiones actuales a Oracle Para visualizarla es necesario entrar con privilegios de administrador
select osuser, username, machine, program
from v$session
order by osuser
•• Consulta Oracle SQL para conocer Vista que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc
•• Consulta Oracle SQL para conocer Vista que muestra los usuarios de Oracle conectados y el número de sesiones por usuario
select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc
Propietarios de objetos y número de objetos por propietario
select owner, count(owner) Numero
from dba_objects
group by owner
order by Numero desc
•• Consulta Oracle SQL para conocer Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos)
select * from dictionary
•• Consulta Oracle SQL para conocer Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "XXX"
select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'
•• Consulta Oracle SQL para conocer Tablas propiedad del usuario actual
select * from user_tables
•• Consulta Oracle SQL para conocer Todos los objetos propiedad del usuario conectado a Oracle
select * from user_catalog
•• Consulta Oracle SQL para conocer Consulta SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC
•• Consulta Oracle SQL para conocer Productos Oracle instalados y la versión:
select * from product_component_version
•• Consulta Oracle SQL para conocer Roles y privilegios por roles:
select * from role_sys_privs
•• Consulta Oracle SQL para conocer Reglas de integridad y columna a la que afectan:
select constraint_name, column_name from sys.all_cons_columns
•• Consulta Oracle SQL para conocer Tablas de las que es propietario un usuario, en este caso "xxx":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx'
•• Consulta Oracle SQL para conocer Otra forma más efectiva (tablas de las que es propietario un usuario):
SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'HR'
Parámetros de Oracle, valor actual y su descripción:
SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1
•• Consulta Oracle SQL para conocer Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):
Select * FROM dba_users
•• Consulta Oracle SQL para conocer Tablespaces y propietarios de los mismos:
select owner, decode(partition_name, null, segment_name,
segment_name || ':' || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
from dba_segments
Where 1=1 And extents > 1 order by 9 desc, 3
Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
select distinct vs.sql_text, vs.sharable_mem,
vs.persistent_mem, vs.runtime_mem, vs.sorts,
vs.executions, vs.parse_calls, vs.module,
vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.users_opening, vs.loads,
to_char(to_date(vs.first_load_time,
'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value ,
rows_processed , vs.command_type, vs.parsing_user_id ,
OPTIMIZER_MODE , au.USERNAME parseuser
from v$sqlarea vs , all_users au
where (parsing_user_id != 0) AND
(au.user_id(+)=vs.parsing_user_id)
and (executions >= 1) order by buffer_gets/executions desc
•• Consulta Oracle SQL para conocer todos los Tablespaces:
select * from V$TABLESPACE
•• Consulta Oracle SQL para conocer Memoria Share_Pool libre y usada
select name,to_number(value) bytes
from v$parameter where name ='shared_pool_size'
union all
select name,bytes
from v$sgastat where pool = 'shared pool' and name = 'free memory'
Cursores abiertos por usuario
select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3
•• Consulta Oracle SQL para conocer Aciertos de la caché (no debería superar el 1 por ciento)
select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');
Sentencias SQL completas ejecutadas con un texto determinado en el SQL
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece
Una sentencia SQL concreta (filtrado por sid)
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and sid = 105
ORDER BY c.sid, d.piece
•• Consulta Oracle SQL para conocer Tamaño ocupado por la base de datos
select sum(BYTES)/1024/1024 MB from DBA_EXTENTS
•• Consulta Oracle SQL para conocer Tamaño de los ficheros de datos de la base de datos
select sum(bytes)/1024/1024 MB from dba_data_files
•• Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta sin incluir los índices de la misma
select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'
•• Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta incluyendo los índices de la misma
select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))
•• Consulta Oracle SQL para conocer Tamaño ocupado por una columna de una tabla
select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA
•• Consulta Oracle SQL para conocer Espacio ocupado por usuario
SELECT owner, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by owner
•• Consulta Oracle SQL para conocer Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...)
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by SEGMENT_TYPE
•• Consulta Oracle SQL para conocer Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD'
order by object_name
•• Consulta Oracle SQL para conocer Espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero
SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB
group by SEGMENT_NAME
order by 2 desc

Diccionario de datos

Diccionario de datos

1.- Introducción

Un diccionario de datos es un conjunto de metadatos que contiene las características lógicas de los datos que se van a utilizar en el sistema que se programa, incluyendo nombre, descripción, alias, contenido y organización.

Estos diccionarios se desarrollan durante el análisis de flujo de datos y ayuda a los analistas que participan en la determinación de los requerimientos del sistema, su contenido también se emplea durante el diseño del proyecto.

2.-Diccionario de la base de datos

En base de datos se utiliza para llevar un recuento detallado de todas las tablas dentro de de la base de datos que han sido creadas por el usuario, por el diseñador o por ambos.

Este contiene todos los nombres y características de atributo de cada una de las tablas en el sistema.

En ocasiones, el diccionario de datos se describe como “la base de datos del diseñador de la base de datos” ya que registra las decisiones de diseño sobre las tablas y sus estructuras.

Bibliografia

http://books.google.com.mx/books?id=B_UVi51RDY4C&pg=PA76&dq=diccionario+de+datos&hl=es&ei=QXYOTuKgLYGcsQPM7JSaDg&sa=X&oi=book_result&ct=result&resnum=1&ved=0CC0Q6AEwAA#v=onepage&q=diccionario%20de%20datos&f=false

http://www.mitecnologico.com/Main/DiccionarioDeDatos

Modelo relacional

 Modelo Relacional

1.1 El porque de modelo Relacional.

 El objetivo de el modelo relacional es crear un esquema, lo cual representa un conjunto de tablas que representan relaciones, relaciones entre los datos.

Estas tablas, se construyen de distintas formas:

·         Creando un conjunto de tablas iníciales y aplicar la regla de normalización.

·         Transformar el modelo entidad relación en tablas y aplicar la normarmalizacion hasta tener un esquema optimo.

• El partir de un diagrama visual es muy útil para apreciar los detalles, de ahí que se llame modelo conceptual.
• El crear las tablas iníciales es mucho más simple a través de las reglas de conversión.
• Se podría pensar que es lo mismo porque finalmente hay que "normalizar" las tablas de todas formas, pero la ventaja de partir del modelo e-r es que la "normalización" es mínima por lo general.
• Lo anterior tiene otra ventaja, aún cuando se normalice de manera deficiente, se garantiza un esquema aceptable, en la primer técnica no es así.

1.2 Conceptos Básicos

1.2.1 Tablas

Es una forma simple de representar los datos : Una tabla bidimensional llamada relación.

título	año	duración	tipo
Los malaventurados no lloran	2006	3:24	Rock punk
Here I am	2004	3:42	Happy punk
The roller	2011	3:36	Rock

Relación Musica

La relación Música tiene la intención de manejar la información de las instancias en la entidad Música, cada renglón corresponde a una entidad música y cada columna corresponde a uno de los atributos de la entidad. Sin embargo las relaciones pueden representar más que entidades, como se explicará más adelante.

1.2.2 Atributos

Los atributos son las columnas de una relación y describen características particulares de ella.

1.2.3 Esquemas

Es el nombre que se le da a una relación y el conjunto de atributos en ella.

Música (título, año, duración, tipo)

En un modelo relación, un diseño consiste de uno o más esquemas, a este conjunto se le conoce como "esquema relacional de base de datos" (relational database schema) o simplemente "esquema de base de datos" (database schema)

1.2.4 Tuplas

Cada uno de los renglones en una relación conteniendo valores para cada uno de los atributos.

(Los malaventurados no lloran, 2006, 3:24, Rock punk)

1.2.5 Dominios

Se debe considerar que cada atributo (columna) debe ser atómico, es decir, que no sea divisible, no se puede pensar en un atributo como un "registro" o "estructura" de datos.

1.2.6 Representaciones equivalentes de una relación

Las relaciones son un conjunto de tuplas, no una lista de tuplas. El orden en que aparecen las tuplas es irrelevante.

Así mismo el orden de los atributos tampoco es relevante

año	título	tipo	duración
2011	The roller	Rock	3:36
2006	Los malaventurados no lloran	Rock punk	3:24
2004	Here I am	Happy punk	3:42

Otra representación de la relación Música.

2 Clave candidata, clave primaria y clave alternativa de las relaciones.

Antes de ver en concreto este tema definiremos que es una súper clave:

Súper clave: En la relación del esquema R(A1,A2…..An) es un subconjunto de los atributos del esquema tal que no puede haber dos tuplas en la extensión de la relación que tengan la misma combinación de valores para los atributos del sub conjunto.

Por lo tanto, una súper clave nos permite identificar todas las tuplas que contiene la relación.

Una clave candidata es una súper clave C del a relación que cumple que ningún subconjunto propio de C es súper clave.

Es decir, C cumple que la eliminación de cualquiera de sus atributos da un conjunto de atributos que no es súper clave de la relación. Intuitivamente, una clave candidata permite identificar cualquier tupla de una relación de manera que no sobre ningún atributo para hacer la identificación.

En la tabla empleado de la tabla siguiente, la clave candidata puede ser CURP, NSS y No. emp.

Tabla empleado.

No. emp.	Nombre	CURP	NSS	Sueldo
1	Juna	91246afd	84sdf488	2000
2	Pedro	544adas7	Ds784f84	1000

Habitualmente, una de las claves candidatas de una relación se designa la clave primaria de la relación. La clave primaria es la calve candidata cuyos valores se utilizan para identificar las tuplas de la relación.

El creador de la base de datos es el que elige la clave primaria de entre las tablas candidatas.

Las claves candidatas no elegidas como primarias se denominan claves alternativas.
 
Bibliografia

http://books.google.com.mx/books?id=pEkTzCIYWaAC&printsec=frontcover&dq=Modelo+relacional&hl=es&ei=lHgOTraoEI6CsQPRsaCwDg&sa=X&oi=book_result&ct=result&resnum=1&ved=0CCkQ6AEwAA#v=onepage&q&f=false

http://ict.udlap.mx/people/carlos/is341/bases03.html