/* Sesión03 – Creación de ObjetosEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:i201010865@cibertec.edu.pe*/

Objetivo de la sesión.

Describir algunos objetos de la base de datos y sus usos.Crear, mantener y usar tablas, índices, constraints, secuencias y sinónimos.

1) Manejo de tablas. (Ver sesión anterior)2) Manejo de constraints.3) Manejo de secuencias.4) Manejo de índices.5) Manejo de sinónimos.

2) Manejo de constraints.

Fuerzan las reglas a nivel de tabla. Evitan la eliminación de una tabla o de sus registros si hay dependencias. Si no se asigna un nombre a un constraints Oracle Server genera un nombre con el

formato SYS_Cn. Un constraint se puede crear al mismo tiempo que se crea la tabla o bien una vez creada la

tabla. Se pueden definir a nivel de columna o de tabla. Los constrtaints se pueden visualizar en el diccionario de datos.

Sentencias Tipos de constraintsCREATE TABLE [schema.] table(column datatype [DEFAULT expr][column_constraint],

NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK

Utilice la sentence ALTER TABLE para: Agregar o borrar una restricción, sin

modificar su estructura. Activar o desactivar restricciones. Agregar una restricción NOT NULL

agregando la cláusula MODIFY.

ALTER TABLE tableADD CONSTRAINT nom_constraintFOREING KEY(column)REFERENCES table_dest (column);ALTER TABLE empDROP CONSTRAINT emp_manager_fk;ALTER TABLE departamentoDROP PRIMARY KEY CASCADE;

Desactivar un constraint Activar un constraintALTER TABLE tableDISABLE CONSTRAINT nom_constraint

ALTER TABLE tableENABLE CONSTRAINT nom_constraint

Ejemplo1: Cree la tabla CINE y verifique que los campos NOMBRE y DIRECCION no sean nulos.

SQL> CREATE TABLE CINE ( CODCINE NUMBER (4), NOMBRE VARCHAR2 (50) CONSTRAINT NN_CINE_NOMBRE NOT NULL, DIRECCION VARCHAR2 (100) NOT NULL, CAPACIDAD NUMBER (4), FECHAINAUG DATE );

Tabla creada.

Muestra la estructura de tabla CINE

SQL> DESC CINE; Nombre ┐Nulo? Tipo ----------------------------------------- -------- ----------------------------

CODCINE NUMBER(4) NOMBRE NOT NULL VARCHAR2(50) DIRECCION NOT NULL VARCHAR2(100) CAPACIDAD NUMBER(4) FECHAINAUG DATE

Muestra los CONSTRAINTS de la tabla CINE.

SQL> SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'CINE';

CONSTRAINT_NAME TABLE_NAME------------------------------ ------------------------------NN_CINE_NOMBRE CINESYS_C0011190 CINE

Muestra los CONSTRAINTS de la tabla CINE y en que columna.

SQL> SELECT CONSTRAINT_NAME NOMBRE,TABLE_NAME TABLA, SUBSTR(COLUMN_NAME,1,255)COLUMNA FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = 'CINE';

NOMBRE TABLA COLUMNA------------------------------ ------------------------------ ----------------------------------SYS_C0011190 CINE DIRECCIONNN_CINE_NOMBRE CINE NOMBRE

Eliminar la tabla CINE.

SQL> DROP TABLE CINE;

Tabla borrada.

Ejemplo2: Creamos nuevamente la tabla CINE y verificamos que los campos NOMBRE Y DIRECCION sean únicos.

SQL> CREATE TABLE CINE ( CODCINE NUMBER (4), NOMBRE VARCHAR2 (50) CONSTRAINT UK_NOMBRE UNIQUE, DIRECCION VARCHAR2 (100) UNIQUE, CAPACIDAD NUMBER (6), FECHAINAUG DATE );

Tabla creada.

Eliminar la tabla CINE.

SQL> DROP TABLE CINE;

Tabla borrada.

Creamos la tabla CINE con su llave primaria y verifique que el valor de la columna NOMBRE sea único.

SQL> CREATE TABLE CINE ( CODCINE NUMBER (4) CONSTRAINT PK_CINE PRIMARY KEY, NOMBRE VARCHAR2 (50) CONSTRAINT UK_NOMBRE UNIQUE, DIRECCION VARCHAR2 (100), CAPACIDAD NUMBER (6), FECHAINAUG DATE );

Tabla creada.

Creamos la tabla SALA con su llave primaria y referenciamos a la tabla CINE.

SQL> CREATE TABLE SALA ( CODSALA NUMBER (4), CODCINE NUMBER (4), NOMBRE VARCHAR2 (20), CAPACIDAD NUMBER (6), CONSTRAINT PK_CODSALA PRIMARY KEY (CODSALA), CONSTRAINT FK_CODCINE FOREIGN KEY (CODCINE) REFERENCES CINE(CODCINE) );

Tabla creada.

Muestra los CONSTRAINTS de la tabla SALA y en que columna.

SQL> SELECT CONSTRAINT_NAME NOMBRE,TABLE_NAME TABLA, SUBSTR(COLUMN_NAME,1,255)COLUMNA FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = 'SALA';

NOMBRE TABLA COLUMNA------------------------------ ------------------------------ ----------------------------------

FK_CODCINE SALA CODCINE

PK_CODSALA SALA CODSALAEliminEliminamos la tabla SALA ar la tabla SALA.SQL> DROP TABLE SALA;

Tabla borrada.

Ejemplo3: Creamos la tabla SALA aplicando el CONSTRAINT CHECK en el campo capacidad, el CONSTRAINT PRIMARY KEY al campo CODSALA y el CONSTRAINT FOREIGN KEY al campo CODCINE.

SQL> CREATE TABLE SALA ( CODSALA NUMBER (4), CODCINE NUMBER (4), NOMBRE VARCHAR2 (20), CAPACIDAD NUMBER (6) CONSTRAINT CK_CAPACIDAD CHECK (CAPACIDAD>0), CONSTRAINT PK_CODSALA PRIMARY KEY (CODSALA), CONSTRAINT FK_CODCINE FOREIGN KEY (CODCINE) REFERENCES CINE(CODCINE) );

Tabla creada.

Eliminamos la tabla CINE y SALA.

SQL> DROP TABLE SALA;

Tabla borrada.

SQL> DROP TABLE CINE;

Tabla borrada.

Ejemplo4: Creamos la tabla CINE para luego alterar la tabla agregando restricciones.

SQL> CREATE TABLE CINE ( CODCINE NUMBER (4), NOMBRE VARCHAR2 (50), DIRECCION VARCHAR2 (100), CAPACIDAD NUMBER (6), FECHAINAUG DATE );

Tabla creada.

Modificamos la tabla CINE agregándole un PRIMARY KEY al campo CODCINE y un UNIQUE al campo nombre.

SQL> ALTER TABLE CINE ADD CONSTRAINT PK_CODCINE PRIMARY KEY (CODCINE);

Tabla modificada.

SQL> ALTER TABLE CINE ADD CONSTRAINT UK_NOMBRE UNIQUE (NOMBRE);

Tabla modificada.

Ejemplo5: Creamos la tabla SALA para luego alterar la tabla agregando restricciones.

SQL> CREATE TABLE SALA ( CODSALA NUMBER (4), CODCINE NUMBER (4), NOMBRE VARCHAR2 (20), CAPACIDAD NUMBER (6) );

Tabla creada

Modificamos la tabla SALA agregando un PRIMARY KEY a CODSALA, un FOREIGN KEY a CODCINE, un CHECK a CAPACIDAD y un NOT NULL a NOMBRE.

SQL> ALTER TABLE SALA ADD CONSTRAINT PK_CODSALA PRIMARY KEY (CODSALA);

Tabla modificada.

SQL> ALTER TABLE SALA ADD CONSTRAINT FK_CODCINE FOREIGN KEY (CODCINE) REFERENCES CINE (CODCINE);

Tabla modificada.

SQL> ALTER TABLE SALA ADD CONSTRAINT CK_CAPACIDAD CHECK (CAPACIDAD>0);

Tabla modificada

SQL> ALTER TABLE SALA MODIFY (NOMBRE VARCHAR2 (20) CONSTRAINT NN_NOMBRE NOT NULL);

Tabla modificada

3) Manejo de secuencias

Primero estar seguros que estamos conectado con el usuario scott/tiger.

SQL> connect scott/tigerConectado.

NEXTVAL: Genera una nueva secuencia.CURRVAL: Obtiene el ultimo valor de la secuencia.DUAL: Tabla auxiliar de Oracle que permite ejecutar este tipo de secuencias sin necesidad de usar la base de datos.

Ejemplo1: Creamos una secuencia cuyo valor con que inicia sea 1 y el incremento sea 2.

SQL> CREATE SEQUENCE MISECUENCIA START WITH 1 INCREMENT BY 2;

Secuencia creada.

Luego comprobamos generando una nueva secuencia una y otra vez.

SQL> SELECT MISECUENCIA.NEXTVAL FROM DUAL;

NEXTVAL---------- 1

SQL> SELECT MISECUECNIA.NEXTVAL FROM DUAL;

NEXTVAL---------- 3

Ahora comprobamos cual fue el último valor de la secuencia.

SQL> SELECT MISECUENCIA.CURRVAL FROM DUAL;

CURRVAL---------- 3

Ejemplo2: Insertar un registro a la tabla departamento del esquema Scott generando el código de departamento mediante una secuencia.

Muestra la información de la tabla DEPARTAMENTO del esquema SCOTT

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC--------------- --------------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Creamos una secuencia para la tabla DEPT.

SQL> CREATE SEQUENCE SEQ_DEPT START WITH 50 INCREMENT BY 10;

Secuencia creada.

Insertamos un registro a la tabla DEPT.

SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (SEQ_DEPT.NEXTVAL, 'SISTEMA','LIMA');

1 fila creada.

Selecciona todos los campos de la tabla DEPT. Y podremos observar que hemos generado una secuencia.

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 SISTEMA LIMA

Ahora ejecuta la siguiente sentencia ¿qué ocurrirá?

SQL> SELECT SEQ_DEPT.NEXTVAL FROM DUAL;

NEXTVAL---------- 60

SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (SEQ_DEPT.NEXTVAL, 'RRHH','LINCE');

1 fila creada.

Nuevamente seleccionamos la tabla DEPT.

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 SISTEMA LIMA 70 RRHH LINCE

6 filas seleccionadas.

Eliminar una secuencia..

SQL> DROP SEQUENCE SEQ_DEPT;

Secuencia borrada.

4) Manejo de índices

Cuando se debe crear un índice.

Debe crear un índice si: Una columna contiene un amplio rango de valores. Una columna contiene un gran número de valores nulos. Una o más columnas se utilizan juntas frecuentemente en una clausula WHERE o en una

condición de unión. La tabla es grande y se espera que la mayoría de las consultas recuperen del 10 por ciento

de las filas.

Cuando no se debe crear un índice.

La tabla es pequeña. Las columnas no se suelen utilizar como condición en la consulta. Se espera que la mayoría de las consultas recuperen más del 10 por ciento de las filas de

la tabla.

La tabla se actualiza frecuentemente. Se hace referencia a las columnas indexadas como parte de una expresión.

Índices basados en funciones. Un índice basado en función es un índice basado en expresiones. La expresión de índice se crea desde columnas de tabla, constantes, funciones SQL y

funciones definidas por el usuario

Ejemplo: CREATE INDEX upper_dept_name_idx ON departamento (UPPER(department_name));

SintaxisCREATE INDEX indexON table (column[, column] …);

Ejemplo1: Estamos indicando a Oracle que cree la tabla "FACTURACION", con el campo "CODIGO" y que éste sea clave primaria, por lo que creará un índice automáticamente para este campo. Esta es una forma de crear índices, en la creación de la tabla:

SQL> CREATE TABLE FACTURACION ( CODIGO NUMBER (10) NOT NULL, FECHA DATE DEFAULT SYSDATE, CODIGOCLIENTE NUMBER (10), NOMBRECLIENTE VARCHAR2 (100), OBSERVACION VARCHAR2 (2000), CONSTRAINT PK_FACTURACION_CODIGO PRIMARY KEY (CODIGO) ) TABLESPACE USERS;

Tabla creada.

Tras crear la tabla insertaremos algunos registros

SQL> INSERT INTO FACTURACION (CODIGO, CODIGOCLIENTE, NOMBRECLIENTE) VALUES (1, 50, 'AJPDSOFT');

1 fila creada.

Ejemplo2: Crear un índice al campo NOMBRECLIENTE de la tabla FACTURACION.

SQL> CREATE INDEX IN_FACTURACION_NOMBRECLIENTE ON FACTURACION (NOMBRECLIENTE);

═ndice creado.

Para ver el índice creado podemos ejecutar la siguiente consulta

SQL> SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'FACTURACION';

INDEX_NAME TABLE_NAME------------------------------ ------------------------------PK_FACTURACION_CODIGO FACTURACIONIN_FACTURACION_NOMBRECLIENTE FACTURACION

Para ver el índice creado podemos ejecutar también una consulta mas especifica.

SQL> SELECT INDEX_NAME NOMBRE, INDEX_TYPE TIPO, TABLE_NAME TABLA, TABLESPACE_NAME TABLESPACE, SECONDARY SECUNDARIO FROM ALL_INDEXES WHERE TABLE_NAME = 'FACTURACION';

NOMBRE TIPO TABLA TABLESPACE S ------------------------------ --------------------------- ------------------------------ ------------------------------ --PK_FACTURACION_CODIGO NORMAL FACTURACION USERS N

IN_FACTURACION_NOMBRECLIENTE NORMAL FACTURACION SYSTEM N

Ejemplo3: Para añadir un índice de tipo UNIQUE, obligando a que los valores del campo indexado sean unívocos, es decir no se puedan repetir en el campo de la tabla.

SQL> CREATE UNIQUE INDEX IN_FACTURACION_CODCLI_FE ON FACTURACION (CODIGOCLIENTE, FECHA);

═ndice creado.

De esta forma Oracle no permitirá que haya dos registros en la tabla "FACTURACION" con el mismo valor en los campos "CODIGOCLIENTE" y "FECHA", es decir, sólo podrá añadirse una factura por cliente y por día, un cliente no podrá tener dos facturas en un mismo día. Por ejemplo, si insertamos este registró:

SQL> INSERT INTO FACTURACION (CODIGO, CODIGOCLIENTE, FECHA) VALUES (6900, 500, TO_DATE ('09/12/2013', 'DD-MM-YYYY'));

1 fila creada.

Intentaremos insertar un segundo registro con el mismo valor en CODIGOCLIENTE y en FECHA:

SQL> INSERT INTO FACTURACION (CODIGO, CODIGOCLIENTE, FECHA) VALUES (6910, 500, TO_DATE ('09/12/2013', 'DD-MM-YYYY'));

INSERT INTO FACTURACION (CODIGO, CODIGOCLIENTE, FECHA)*ERROR en lÝnea 1:ORA-00001: restricci¾n ·nica (SYS.IN_FACTURACION_CODCLI_FE) violada

Eliminar un índice.

SQL> DROP INDEX IN_FACTURACION_NOMBRECLIENTE;

═ndice borrado.

SQL> DROP INDEX IN_FACTURACION_CODCLI_FE;

═ndice borrado.

5) Manejo de sinónimos Proporciona nombre alternativos a objetos. Se facilita la referencia a objetos pertenecientes a otros usuarios. Reduce nombres largos de objetos. Nombre de sinónimo. Nombre de objeto al que lo asociamos. Usuario creador debe tener privilegio CREATE SYNONYM.

SintaxsCREATE [PUBLIC] SYNONYM synonymFOR object;

Primero conectémonos con el usuario SYS.

SQL> connect sys/cibertec as sysdba

Conectado.

Seleccionamos la tabla CINE del esquema SCOTT desde el usuario SYS.

SQL> SELECT * FROM SCOTT.CINE;

ninguna fila seleccionada

Ahora creamos un sinónimo para la tabla CINE del esquema SCOTT.Y comprobamos haciendo un SELECT a la tabla CINE

SQL> CREATE PUBLIC SYNONYM CINE FOR SCOTT.CINE;

Sin¾nimo creado.

SQL> SELECT * FROM CINE;

ninguna fila seleccionada

Eliminar un sinónimo.

SQL> DROP PUBLIC SYNONYM CINE;

Sin¾nimo borrado.