Bases de datos¶

-- Configuración tablespaces
ACCEPT ts_tablas DEFAULT SGF2_TVB_32K PROMPT 'Tablespace para tablas [Por defecto SGF2_TVB_32K] : '

ACCEPT ts_indices DEFAULT SGF2_IVB_32K PROMPT 'Tablespace para índices [Por defecto SGF2_IVB_32K] : '

ACCEPT ts_blobs DEFAULT SGF2_TES_01M PROMPT 'Tablespace para blobs [Por defecto SGF2_TES_01M] : '


-- Cálculo tamaño tablas

-- Tamaño cbas_registro_huellas = 40 bytes
-- Tamaño cbas_validacion_huellas = 40 bytes
-- En preexplotación:
-- 15 centros probando 10 cursos de 15 alumnos cada uno durante 1 mes 
--      = 15 x 10 x 15 = 2250 filas tabla registro (cbas_registro_huellas)
-- 2250x40 = 88 KB
--      = 15 x 10 x 15 x (30x2) = 135000 filas tabla registro (cbas_validacion_huellas)
-- 13500x40 = 5078 KB


-- En explotación:
-- 40 mil alumnos anuales con una media de duración de 1 mes 
--      = 40000 filas tabla registro (cbas_registro_huellas) => 40 000 x 2 = 80 000 
-- TI = 80000x40 = 3125 KB
--      = 40000 x (30x2) = 2 400 000 filas tabla registro (cbas_validacion_huellas)
-- TI = 240000x40 = 91 MB
-- crecimiento anual estimado = 10%
-- initial = (1 + 5*10/100) * TI = 137 MB



---------------------------------------------------------------------------------------------
-- TABLA CBAS_REGISTRO_HUELLAS (CPR): 
------------------------------------------------------------------------------------------------

CREATE TABLE CBAS_REGISTRO_HUELLAS
(   
    C_IDENTIFICADOR_ID    VARCHAR2(10) NOT NULL,
    N_DEDO_ID       NUMBER(2) NOT NULL,
    F_REGISTRO      DATE NOT NULL,  
    OBJ_CAPTURA_HUELLA      BLOB NOT NULL,
    N_CALIDAD NUMBER(3),
    N_CEN_PRV NUMBER(2),
    N_CEN_COD NUMBER(5),
    U_CREACION  VARCHAR2(30),
    U_MODIF VARCHAR2(30),
    F_CREACION  DATE,
    F_MODIF DATE
)
STORAGE (INITIAL 32K PCTINCREASE 0) TABLESPACE  &ts_tablas PCTFREE 10 PCTUSED 40 
LOB (OBJ_CAPTURA_HUELLA) STORE AS CBAS_OBJ_CAPTURA_HUELLA_LOB 
    (TABLESPACE &ts_blobs INDEX CBAS_OBJ_CAPTURA_HUELLA_INX  (TABLESPACE &ts_indices)) ;  


COMMENT ON TABLE CBAS_REGISTRO_HUELLAS IS 'TABLA PARA EL REGISTRO DE HUELLAS';  
COMMENT ON COLUMN CBAS_REGISTRO_HUELLAS.C_IDENTIFICADOR_ID IS 'IDENTIFICADOR DE LA PERSONA A LA QUE PERTENECE LA HUELLA';  
COMMENT ON COLUMN CBAS_REGISTRO_HUELLAS.N_DEDO_ID IS 'IDENTIFICADOR DEL DEDO AL QUE PERTENECE LA HUELLA';  
COMMENT ON COLUMN CBAS_REGISTRO_HUELLAS.F_REGISTRO IS 'FECHA DE REGISTRO HUELLA';  
COMMENT ON COLUMN CBAS_REGISTRO_HUELLAS.OBJ_CAPTURA_HUELLA IS 'MINUCIA HUELLA';  
COMMENT ON COLUMN CBAS_REGISTRO_HUELLAS.N_CEN_PRV IS 'PROVINCIA CENTRO';  
COMMENT ON COLUMN CBAS_REGISTRO_HUELLAS.N_CEN_COD IS 'CODIGO CENTRO';  


--  Create Primary Key Constraints
ALTER TABLE CBAS_REGISTRO_HUELLAS ADD CONSTRAINT CBAS_IDE_PK
    PRIMARY KEY (C_IDENTIFICADOR_ID, N_DEDO_ID) USING INDEX TABLESPACE &ts_indices STORAGE (INITIAL 32 K);  


---------------------------------------------------------------------------------------------
-- TABLA CBAS_REGISTRO_HUELLAS (CPR): 
------------------------------------------------------------------------------------------------

CREATE TABLE CBAS_VALIDACION_HUELLAS
(   
    C_IDENTIFICADOR_ID    VARCHAR2(10) NOT NULL,
    F_VALIDACION          DATE,
    OBJ_VALIDA_HUELLA     BLOB,
    B_VALIDADO            CHAR(1), 
    N_CALIDAD   NUMBER(3),
    N_DEDO_ID   NUMBER(2),
    N_CEN_PRV NUMBER(2),
    N_CEN_COD NUMBER(5),
    U_CREACION  VARCHAR2(30),
    U_MODIF VARCHAR2(30),
    F_CREACION  DATE,
    F_MODIF DATE
)
STORAGE (INITIAL 32K PCTINCREASE 0) TABLESPACE  &ts_tablas PCTFREE 10 PCTUSED 40 
LOB (OBJ_VALIDA_HUELLA) STORE AS CBAS_OBJ_VALIDA_HUELLA_LOB 
    (TABLESPACE &ts_blobs INDEX CBAS_OBJ_VALIDA_HUELLA_INX  (TABLESPACE &ts_indices)) ;

COMMENT ON TABLE CBAS_VALIDACION_HUELLAS IS 'TABLA PARA LA VALIDACION DE HUELLAS';  
COMMENT ON COLUMN CBAS_VALIDACION_HUELLAS.C_IDENTIFICADOR_ID IS 'IDENTIFICADOR DE LA PERSONA A LA QUE PERTENECE LA HUELLA';  
COMMENT ON COLUMN CBAS_VALIDACION_HUELLAS.F_VALIDACION IS 'FECHA DE VALIDACION HUELLA';  
COMMENT ON COLUMN CBAS_VALIDACION_HUELLAS.OBJ_VALIDA_HUELLA IS 'MINUCIA HUELLA';  
COMMENT ON COLUMN CBAS_VALIDACION_HUELLAS.B_VALIDADO IS 'INDICADOR SI LA HUELLA SE HA VALIADO CORRECTAMENTE';  
COMMENT ON COLUMN CBAS_VALIDACION_HUELLAS.N_CEN_PRV IS 'PROVINCIA CENTRO';  
COMMENT ON COLUMN CBAS_VALIDACION_HUELLAS.N_CEN_COD IS 'CODIGO CENTRO';  


--  Create Primary Key Constraints
ALTER TABLE CBAS_VALIDACION_HUELLAS ADD CONSTRAINT CBAS_IDF_PK
    PRIMARY KEY (C_IDENTIFICADOR_ID, F_VALIDACION) USING INDEX TABLESPACE &ts_indices STORAGE (INITIAL 32 K);   


-- Triggers auditoria
CREATE OR REPLACE TRIGGER tr_cbas_registro_huellas_aud_i BEFORE INSERT ON cbas_registro_huellas
FOR EACH ROW
BEGIN        
    :NEW.f_creacion := SYSDATE;   
END;
/

CREATE OR REPLACE TRIGGER tr_cbas_registro_huellas_aud_u BEFORE UPDATE ON cbas_registro_huellas
FOR EACH ROW
BEGIN  
    :NEW.f_modif := SYSDATE;  
END;
/

CREATE OR REPLACE TRIGGER tr_cbas_valid_huellas_aud_i BEFORE INSERT ON cbas_validacion_huellas
FOR EACH ROW
BEGIN        
    :NEW.f_creacion := SYSDATE;   
END;
/

CREATE OR REPLACE TRIGGER tr_cbas_valid_huellas_aud_u BEFORE UPDATE ON cbas_validacion_huellas
FOR EACH ROW
BEGIN  
    :NEW.f_modif := SYSDATE;  
END;
/