diseño físico de ddbb - presentación - universidad de...

Mª Carmen Gabarrón

Diseño físico de DDBB

GBD

GBD. Diseño físico de DDBB

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/index.htm

Manual SQL de Oracle 10g


SQL

SQL es el lenguaje de consulta universal para bases

de datos.

SQL ANSI 92 es el estándar SQL.

Existen otras variantes que lo extienden como: T-SQL

(Transact-SQL) y PL/SQL (Procedure Language /

SQL)

SQL proporciona tres conjuntos básicos de

sentencias: D.M.L (lenguaje de manipulación de datos).

D.D.L (Lenguaje de definición de datos).

D.C.L (Lenguaje de Control de Datos).


Objetos de una base de datos.

Las bases de datos están compuestas básicamente por objetos:

Tablas, Vistas, Funciones, Índices, Procedimientos almacenados y

Trigger

Vistas

Funciones

Tablas

Índices

Procedimientos almacenados

Triggers

Sinónimos

Base de datos


Lenguaje de Definición de Datos DDL

Este lenguaje se utiliza para la definición de

objetos de la base de datos.

Comandos: CREATE – para crear objetos

ALTER – para modificar la estructura de objetos

DROP – para eliminar objetos

TRUNCATE – para eliminar todos los registros de una tabla.

COMMENT – para agregar comentarios de un objeto al diccionario

de datos

RENAME – para cambiar el nombre de un objeto


Lenguaje de Manipulación de Datos DML

Como su nombre indica provee comandos para la

manipulación de los datos, es decir, podemos

seleccionar, insertar, eliminar y actualizar datos.

Comandos:

SELECT – para consultar datos.

INSERT – Insertar datos.

UPDATE – actualizar datos.

DELETE – eliminar algunos o varios registros.


Lenguaje de Control de Datos DCL

Comandos:

GRANT – Para otorgar privilegios a un usuario sobre un

objeto.

REVOKE – Para quitar privilegios dados a un usuario

sobre un objeto.

Este lenguaje provee comandos para manipular

la seguridad de la base de datos, respecto al

control de accesos y privilegios entre los

usuarios.


Numéricos (con o sin decimales).

Alfanuméricos.

Fecha y Hora

Lógico

Además, la mayoría de gestores de BD actuales soportan

el tipo: BLOB (Binary Large Object), para almacenar

archivos

Tipos de datos

Tipos de Datos: SQL posee varios tipos de datos

para almacenar información, los tipos de datos

pueden ser:


Tipos de datos

Dependiendo de cada gestor de bases de datos,

en general, se pueden tener los siguientes:

Númericos Alfanúmericos Fecha Lógico BLOB

Integer Char (n) Date Bit Image

Numeric (n,m) Varchar (n) DateTime Text

Decimal (n) Varchar2 (n)

Float

Number


Tipos de datos más usados en Oracle (1)

Tipo de dato Descripción

Varchar2 (n) Caracteres de longitud variable

Char (n) Caracteres de longitud fija

Number (p, s) Dato numérico de longitud variable con una precisión y

escala máximas de 38

Date Fecha y hora

Long Caracteres de longitud variable de hasta 2 gigabytes

Clob Caracteres de hasta 4 gigabytes


Tipos de datos más usados en Oracle (2)

Tipo de dato Descripción

Clob Caracteres de hasta 4 gigabytes

Raw Datos binarios de longitud variable con un tamaño

máximo de 2.000 bytes

Long Raw Datos binarios de longitud variable con un tamaño

máximo de 2 gigabytes.

Blob Dato binario de hasta 4 gigabytes

Bfile archivo Dato binario almacenado en un fichero externo, hasta 4

gigabytes

ROWID de una fila Sistema numérico de base 64 que representa la dirección

única de su tabla


Nomenclatura.

La mayoría de la sintaxis de SQL se basa en el

estándar, Extended Backus–Naur Form.

Símbolo Significado

< > Encierran parámetros de una orden que el usuario

debe sustituir al escribir dicha orden por los valores

que queramos dar a los parámetros.

[ ] Indica que su contenido es opcional.

{ } Indica que su contenido puede repetirse una o más

veces.

| Separador de expresiones. Se debe poner al menos

uno de la lista.


Creación de tablas en Oracle.

CREATE TABLE tbEmpresa

( nit varchar(150),

nombre varchar(255),

ubicacion varchar(255)

) ;

CREATE TABLE <nombre_tabla>

(

<nombre_campo> <tipo_datos>

{,<nombre_campo> <tipo_datos>}

) ;


Creación de tablas con SQL en Oracle.

CREATE TABLE <nombre_tabla>

( <nombre_campo> <tipo_datos (tamaño)>

[null | not null] [default <valor_por_defecto>]

{,<nombre_campo> <tipo_datos(tamaño)>

[null | not null] [default <valor_por_defecto>]}

[, constraint <nombre>

primary key (<nombre_campo>)]

{, constraint <nombre>

foreign key (<nombre_campo> {,<nombre_campo>})

references <tabla_referenciada> ( <nombre_campo>

{,<nombre_campo>} ) }

) ;

Obligatorio

Opcional

Puede repetirse


Restricciones a tablas en Oracle.

DEFAULT <Valor>Valor por defecto: Se asigna al atributo si no se

especifica otro valor.

NOT NULL Atributo que no admite valores NULL.

PRIMARY KEY Clave o llave primaria (no admite NULL).

UNIQUE Llave alternativa.

CHECK (<Condición>) Comprueba que se cumple esa condición

(si el atributo es no nulo).



Llave externa (foránea o ajena): Debe indicarse la tabla

referenciada.

FOREIGN KEY (<Clave Externa>) REFERENCES Tabla>(<Atributos>)

[ON DELETE {CASCADE | SET NULL | SET DEFAULT}]

[ON UPDATE {CASCADE | SET NULL | SET DEFAULT}]

Si se borra la llave

referenciada, se

borran las tuplas que

la referencian

Si se actualiza la

llave referenciada, se

actualizan las tuplas

que la referencian

Si se borra/actualiza

la llave referenciada,

se ponen a NULL los

valores que la

referencian (clave

Externa o ajena).

Si se borra/actualiza

la llave referenciada,

se ponen los valores

que la referencian a su

valor por defecto.

Oracle 10 no permite esta opción

No incluido en Oracle

On update no incluido en Oracle



La clave externa (foránea o ajena): Debe indicar la tabla referenciada.

FOREIGN KEY (<Clave Externa>) REFERENCES Tabla>(<Atributos>)

[ON DELETE {CASCADE | SET NULL} ]


Creación de tablas. Ejemplo 1.

CREATE TABLE tbAutos

(

matricula char(8) not null,

marca varchar(255),

modelo varchar(255),

color varchar(255) not null,

numero_kilometros numeric(14,2) default 0,

constraint PK_Coches primary key (matricula)

) ;


Creación de tablas. Ejemplo 2.

CREATE TABLE tbProveedor

(

id numeric (10) not null,

nombre varchar2 (50) not null,

correo varchar2 (50),

CONSTRAINT pk_tbProveedor

PRIMARY KEY (id, nombre)

);


Creación de tablas. Ejemplo 2.2

CREATE TABLE tbProducto (

id numeric (10) not null,

tbProveedor_id numeric (10) not null,

tbProveedor_nombre varchar2 (50) not null,

nombre varchar2 (50) not null,

cantidad numeric (10) not null,

CONSTRAINT pk_tbProducto

PRIMARY KEY (id, tbProveedor_id, tbProveedor_nombre),

CONSTRAINT fk_tbProducto_tbProveedor

FOREIGN KEY (tbProveedor_id, tbProveedor_nombre)

REFERENCES tbProveedor(id, nombre)

);


Ejemplo ON DELETE SET NULL, ON DELETE CASCADE:

Creación de tablas. Ejemplo 2.3

CREATE TABLE dept_20 (employee_id NUMBER(4) PRIMARY KEY, last_name VARCHAR2(10), job_id VARCHAR2(9), manager_id NUMBER(4) CONSTRAINT fk_mgr REFERENCES employees ON DELETE SET NULL,hire_date DATE, salary NUMBER(7,2), ommission_pct NUMBER(7,2), department_id NUMBER(2) CONSTRAINT fk_deptno REFERENCES departments (department_id) ON DELETE CASCADE );

Si consultamos "user_constraints", en la columna "delete_rule" mostrará "cascade".

select *

from user_constraints


Borrado de tablas.

DROP TABLE <nombre_tabla>;

DROP TABLE tbProducto ;


Aclaraciones.

Después de crear las tablas anteriores, ¿cuál seria el orden

de eliminación?

DROP TABLE <nombre_tabla>;


Aclaraciones.

¿Cuál es la diferencia entre char, varchar, varchar2?

Char (n): Se usa para almacenar cadenas de longitud fija. Si

la longitud de la cadena es menor que n, el espacio restante

NO es liberado.

Varchar2 (n): Se usa para almacenar cadenas de longitud

variable. Si la longitud de la cadena es menor que n, el

espacio restante es liberado.

Varchar (n): funciona igual que varchar2; sin embargo, no

es recomendado usarlo, dado que Oracle lo tiene reservado

para usos futuros


Modificación de tablas con SQL en Oracle.

Permite:

Añadir campos a la estructura inicial de una tabla.

Añadir restricciones y referencias.

Nota: para ver los atributos de una tabla, se usa el comando

ALTER TABLE <table_name>

add [CONSTRAINT <constraint_name>

FOREIGN KEY (<column1>, column2, ... column_n)

REFERENCES parent_table (column1, column2, ...

column_n)];

DESCRIBE <nombre_tabla>;


Modificación de tablas. Ejemplo 1.

Veamos los campos de la tabla tbProducto antes de modificarla.

Se agregan dos campos:

Veamos los campos de la tabla tbProducto después de

modificarla.

DESCRIBE tbProducto;

ALTER TABLE tbProducto ADD calidad number(10) default 4;

ALTER TABLE tbProducto ADD calidad2 number(10) default 5;




Veamos los campos de la tabla tbProducto antes de modificarla.

Se elimina un campo:

Veamos los campos de la tabla tbProducto después de

modificarla.

DESC tbProducto;


ALTER TABLE tbproducto DROP COLUMN calidad2;



Ahora se desea que el campo CALIDAD forme parte de la clave

primaria de la tabla Producto.

Consultar las columnas que pertenecen a la clave primaria de la

tabla TBPRODUCTO.

Las tablas en rojo son del sistema.

SELECT COLUMN_NAME FROM user_cons_columns

ucc JOIN user_constraints uc ON

ucc.constraint_name=uc.constraint_name

WHERE ucc.TABLE_NAME = 'TBPRODUCTO' AND

uc.CONSTRAINT_NAME = 'PK_TBPRODUCTO' ;



Modificar la clave primaria de TBPRODUCTO.

Se comprueba si efectivamente se hizo el cambio.

ALTER TABLE tbproducto DROP CONSTRAINT PK_TBPRODUCTO;

ALTER TABLE tbProducto ADD CONSTRAINT pk_tbProducto

PRIMARY KEY (id, tbProveedor_id, tbProveedor_nombre,

calidad);

SELECT COLUMN_NAME FROM user_cons_columns ucc

JOIN user_constraints uc

ON ucc.constraint_name=uc.constraint_name

WHERE ucc.TABLE_NAME = 'TBPRODUCTO' AND

uc.CONSTRAINT_NAME = 'PK_TBPRODUCTO' ;



En la siguiente dirección pueden encontrarse más ejemplos

sobre el uso de ALTER.

http://www.techonthenet.com/sql/tables/alter_table.php

Por ejemplo, cambiar el nombre de un campo:

ALTER TABLE <table_name>

RENAME COLUMN <old_name> to <new_name>;

http://www.techonthenet.com/sql/tables/alter_table.php


select *

from user_tables

diseño físico de ddbb - presentación - universidad de...

Documents