Modelo entidad relación (Administración de sistema de información)

Teniendo en cuenta que trabajaremos con el motor de bases de datos Oracle, se expone un modelo entidad relación ejemplo, en este caso se ha diseñado un MER que puede cumplir con la seguridad de cualquier sistema de información, incluyendo administración del menú de la aplicación.

Por lo tanto, se creó un usuario en la base de datos con el nombre de CORE que en este caso será nuestro núcleo del sistema y a partir de ahí cualquier solución que se diseñe de ahora en adelante puede usar este esquema genérico de seguridad y administración. A continuación se describen a modo general las tablas a crear:

Segcargo: Almacenara los cargos que tiene la empresa o institución a la cual se hace la solución informática.

Segusuario: Almacena los usuarios finales del sistema con su respectivo cargo.

Segrupo: Almacena los grupos que tiene la empresa o institución a la cual se hace la solución informática, ejemplo: Contabilidad, facturación, recaudo.

Esta administración de menú está pensado para darle permisos a pantallas o módulos por grupos de usuarios por lo tanto se crea la tabla Seggrupousuario.

Segmenu: Almacena los menú o pantallas que tendrá el sistema de información.

Segmenugrupo: Asocia los menú que puede visualizar un grupo determinado de usuarios.

Segerror: Almacena trazas de la aplicación.

Segparametro: Almacena diferentes parámetros de la aplicación en caso de ser necesario.

Se adjunta imagen del ejemplo y script de ejecución del modelo.

CREATE TABLE CORE.SEGCARGO

  (

    IDSEGCARGO NUMBER (10) NOT NULL ,

    DESCCARGO  VARCHAR2 (20 BYTE) NOT NULL

  ) ;

COMMENT ON TABLE CORE.SEGCARGO

IS

  'Cargos de los usuarios finales' ;

  COMMENT ON COLUMN CORE.SEGCARGO.IDSEGCARGO

IS

  'Secuencia de la tabla' ;

  COMMENT ON COLUMN CORE.SEGCARGO.DESCCARGO

IS

  'Nombre del cargo' ;

  ALTER TABLE CORE.SEGCARGO ADD CONSTRAINT SEGCARGO_PK PRIMARY KEY ( IDSEGCARGO ) ;

CREATE TABLE CORE.SEGERROR

  (

    IDSEGERROR     NUMBER (15) NOT NULL ,

    FECHA_REGISTRO DATE ,

    METNAME        VARCHAR2 (100 BYTE) ,

    CALLSTACK      VARCHAR2 (2000 BYTE) ,

    DESCRIPCION    VARCHAR2 (2000 BYTE) ,

    IDMENSAJE      NUMBER (5) ,

    USUARIODB      VARCHAR2 (100 BYTE) ,

    USUARIOSO      VARCHAR2 (100 BYTE) ,

    SESSIONID      NUMBER (15) ,

    APPNAME        VARCHAR2 (100 BYTE) ,

    MAQUINA        VARCHAR2 (50 BYTE) ,

    TERMINAL       VARCHAR2 (50 BYTE) ,

    DIRECCIONIP    VARCHAR2 (50 BYTE) ,

    USUARIOAP      VARCHAR2 (100 BYTE) ,

    MODULO         VARCHAR2 (20 BYTE) ,

    OBSERVACION    VARCHAR2 (2000 BYTE) ,

    ERRORSTACK CLOB

  ) ;

COMMENT ON TABLE CORE.SEGERROR

IS

  'Errores generados por la aplicacisn' ;

  COMMENT ON COLUMN CORE.SEGERROR.IDSEGERROR

IS

  'Id del Error' ;

  COMMENT ON COLUMN CORE.SEGERROR.FECHA_REGISTRO

IS

  'Fecha de registro' ;

  COMMENT ON COLUMN CORE.SEGERROR.METNAME

IS

  'Metodo que geners el error' ;

  COMMENT ON COLUMN CORE.SEGERROR.CALLSTACK

IS

  'Orden de ejecucisn de metodos' ;

  COMMENT ON COLUMN CORE.SEGERROR.DESCRIPCION

IS

  'Descripcisn del error' ;

  COMMENT ON COLUMN CORE.SEGERROR.IDMENSAJE

IS

  'Error generado' ;

  COMMENT ON COLUMN CORE.SEGERROR.USUARIODB

IS

  'Usuario Base de datos' ;

  COMMENT ON COLUMN CORE.SEGERROR.USUARIOSO

IS

  'Usuario' ;

  COMMENT ON COLUMN CORE.SEGERROR.SESSIONID

IS

  'SESSION' ;

  COMMENT ON COLUMN CORE.SEGERROR.APPNAME

IS

  'Aplicacion que genero el error' ;

  COMMENT ON COLUMN CORE.SEGERROR.MAQUINA

IS

  'Maquina donde se geners el error' ;

  COMMENT ON COLUMN CORE.SEGERROR.TERMINAL

IS

  'Terminal que geners el error' ;

  COMMENT ON COLUMN CORE.SEGERROR.DIRECCIONIP

IS

  'IP del cliente' ;

CREATE UNIQUE INDEX CORE.SEGERROR_PK ON CORE.SEGERROR

  (

    IDSEGERROR ASC

  )

  ;

  ALTER TABLE CORE.SEGERROR ADD CONSTRAINT SEGERROR_PK PRIMARY KEY ( IDSEGERROR ) ;

CREATE TABLE CORE.SEGMENU

  (

    IDSEGMENU      NUMBER (10) NOT NULL ,

    NOMBREMENU     VARCHAR2 (100 BYTE) NOT NULL ,

    FORMANEMONICO  VARCHAR2 (40 BYTE) NOT NULL ,

    NIVEL          NUMBER (1) NOT NULL ,

    IDPADRESEGMENU NUMBER (10)

  ) ;

COMMENT ON TABLE CORE.SEGMENU

IS

  'Configuracion del menu de la aplicacion, pantallas que tiene el sistema' ;

  COMMENT ON COLUMN CORE.SEGMENU.IDSEGMENU

IS

  'Codigo secuencia del menu' ;

  COMMENT ON COLUMN CORE.SEGMENU.NOMBREMENU

IS

  'Descripcion de la pantalla, modulo o submenu' ;

  COMMENT ON COLUMN CORE.SEGMENU.FORMANEMONICO

IS

  'Nemonico o abreviacion de la pantalla' ;

  COMMENT ON COLUMN CORE.SEGMENU.NIVEL

IS

  'Nivel o posicion de la pantalla, modulo o submenu del menu de la aplicacion' ;

  COMMENT ON COLUMN CORE.SEGMENU.IDPADRESEGMENU

IS

  'Padre del menu, de que pantalla, modulo o submmenu depende' ;

CREATE UNIQUE INDEX CORE.SEGMENU_PK ON CORE.SEGMENU

  (

    IDSEGMENU ASC

  )

  ;

  ALTER TABLE CORE.SEGMENU ADD CONSTRAINT SEGMENU_PK PRIMARY KEY ( IDSEGMENU ) ;

CREATE TABLE CORE.SEGMENUGRUPO

  (

    SEGMENUGRUPO NUMBER (10) NOT NULL ,

    IDSEGRUPO    NUMBER (10) NOT NULL ,

    IDSEGMENU    NUMBER (10) NOT NULL

  ) ;

COMMENT ON COLUMN CORE.SEGMENUGRUPO.SEGMENUGRUPO

IS

  'CODIGO SECUENCIA DE LOS MENUS POR GRUPOS' ;

  COMMENT ON COLUMN CORE.SEGMENUGRUPO.IDSEGRUPO

IS

  'Clave ajena del grupo de usuarios' ;

  COMMENT ON COLUMN CORE.SEGMENUGRUPO.IDSEGMENU

IS

  'Clave ajena de menus' ;

CREATE UNIQUE INDEX CORE.SEGMENUGRUPO_PK ON CORE.SEGMENUGRUPO

  (

    SEGMENUGRUPO ASC

  )

  ;

CREATE UNIQUE INDEX CORE.CK_MENUGRUPO ON CORE.SEGMENUGRUPO

  (

    IDSEGRUPO ASC , IDSEGMENU ASC

  )

  ;

  ALTER TABLE CORE.SEGMENUGRUPO ADD CONSTRAINT SEGMENUGRUPO_PK PRIMARY KEY ( SEGMENUGRUPO ) ;

  ALTER TABLE CORE.SEGMENUGRUPO ADD CONSTRAINT CK_MENUGRUPO UNIQUE ( IDSEGRUPO , IDSEGMENU ) ;

CREATE TABLE CORE.SEGPARAMETRO

  (

    IDSEGPARAMETRO VARCHAR2 (30 BYTE) NOT NULL ,

    PARAVANU       NUMBER (18,5) ,

    PARAVAVA       VARCHAR2 (1000 BYTE) ,

    PARADESC       VARCHAR2 (2000 BYTE)

  ) ;

COMMENT ON TABLE CORE.SEGPARAMETRO

IS

  'Parametros generales' ;

  COMMENT ON COLUMN CORE.SEGPARAMETRO.IDSEGPARAMETRO

IS

  'Codigo geparametro' ;

  COMMENT ON COLUMN CORE.SEGPARAMETRO.PARAVANU

IS

  'Valor Numerico' ;

  COMMENT ON COLUMN CORE.SEGPARAMETRO.PARAVAVA

IS

  'Valor String' ;

  COMMENT ON COLUMN CORE.SEGPARAMETRO.PARADESC

IS

  'Observacion' ;

CREATE UNIQUE INDEX CORE.GEPARAMETRO_PK ON CORE.SEGPARAMETRO

  (

    IDSEGPARAMETRO ASC

  )

  ;

  ALTER TABLE CORE.SEGPARAMETRO ADD CONSTRAINT GEPARAMETRO_PK PRIMARY KEY ( IDSEGPARAMETRO ) ;

CREATE TABLE CORE.SEGRUPO

  (

    IDSEGRUPO   NUMBER (10) NOT NULL ,

    NOMBREGRUPO VARCHAR2 (40 BYTE) NOT NULL ,

    ROL         VARCHAR2 (40 BYTE) NOT NULL

  ) ;

COMMENT ON TABLE CORE.SEGRUPO

IS

  'Grupos de usuarios finales del sistema donde se tiene el rol que los identifica' ;

  COMMENT ON COLUMN CORE.SEGRUPO.IDSEGRUPO

IS

  'Codigo secuencia del grupo' ;

  COMMENT ON COLUMN CORE.SEGRUPO.NOMBREGRUPO

IS

  'Nombre del grupo o descripcion' ;

  COMMENT ON COLUMN CORE.SEGRUPO.ROL

IS

  'Rol del grupo de usuarios finales del sistema

' ;

CREATE UNIQUE INDEX CORE.PK_SEGRUPO ON CORE.SEGRUPO

  (

    IDSEGRUPO ASC

  )

  ;

  ALTER TABLE CORE.SEGRUPO ADD CONSTRAINT PK_SEGRUPO PRIMARY KEY ( IDSEGRUPO ) ;

CREATE TABLE CORE.SEGRUPOUSUARIO

  (

    SEGRUPOUSUARIO NUMBER (10) NOT NULL ,

    IDSEGRUPO      NUMBER (10) NOT NULL ,

    IDSEGUSUARIO   NUMBER NOT NULL

  ) ;

COMMENT ON TABLE CORE.SEGRUPOUSUARIO

IS

  'Rompimiento entre roles y usuarios del sistema' ;

  COMMENT ON COLUMN CORE.SEGRUPOUSUARIO.SEGRUPOUSUARIO

IS

  'Codigo secuencia segrupousuario' ;

  COMMENT ON COLUMN CORE.SEGRUPOUSUARIO.IDSEGRUPO

IS

  'clave ajena de la tabla segrupo' ;

  COMMENT ON COLUMN CORE.SEGRUPOUSUARIO.IDSEGUSUARIO

IS

  'Clave ajena de usuarios' ;

CREATE UNIQUE INDEX CORE.SEGRUPOUSUARIO_PK ON CORE.SEGRUPOUSUARIO

  (

    SEGRUPOUSUARIO ASC

  )

  ;

CREATE UNIQUE INDEX CORE.CK_USUARIO_GRUPO ON CORE.SEGRUPOUSUARIO

  (

    IDSEGRUPO ASC , IDSEGUSUARIO ASC

  )

  ;

  ALTER TABLE CORE.SEGRUPOUSUARIO ADD CONSTRAINT SEGRUPOUSUARIO_PK PRIMARY KEY ( SEGRUPOUSUARIO ) ;

  ALTER TABLE CORE.SEGRUPOUSUARIO ADD CONSTRAINT CK_USUARIO_GRUPO UNIQUE ( IDSEGRUPO , IDSEGUSUARIO ) ;

CREATE TABLE CORE.SEGUSUARIO

  (

    IDSEGUSUARIO        NUMBER NOT NULL ,

    NUMIDENTIFICACION   NUMBER (15) NOT NULL ,

    NOMBRES             VARCHAR2 (20 BYTE) NOT NULL ,

    APELLIDOS           VARCHAR2 (20 BYTE) NOT NULL ,

    DIRECCION           VARCHAR2 (25 BYTE) NOT NULL ,

    TELEFONO            VARCHAR2 (20 BYTE) NOT NULL ,

    SEXO                CHAR (1 BYTE) NOT NULL ,

    USUARIO             VARCHAR2 (20 BYTE) NOT NULL ,

    ACTIVO              CHAR (1 BYTE) DEFAULT 'S' NOT NULL ,

    CONTRASENA          VARCHAR2 (100 BYTE) NOT NULL ,

    EMAIL               VARCHAR2 (30 BYTE) ,

    IDSEGCARGO          NUMBER (10) NOT NULL ,

    SEGCARGO_IDSEGCARGO NUMBER (10) NOT NULL

  ) ;

ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT CK_SEXOMF CHECK ( SEXO               IN ('F', 'M')) ;

ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT CK_SEGUSUARIOACTIVOSN CHECK ( ACTIVO IN ('N', 'S')) ;

COMMENT ON TABLE CORE.SEGUSUARIO

IS

  'Usuarios finales del sistema' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.IDSEGUSUARIO

IS

  'Codigo secuencia del usuario' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.NUMIDENTIFICACION

IS

  'Identificacion de persona natural o juridica' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.NOMBRES

IS

  'Nombre del usuario final, si es persona juridica el nombre de la empresa va en este campo' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.DIRECCION

IS

  'Direccion de ubicacion del usuario' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.TELEFONO

IS

  'Telefono de contacto del usuario' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.SEXO

IS

  'Sexo del usuario' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.USUARIO

IS

  'Alias o nick del usuario final del sistema' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.ACTIVO

IS

  'N=inactivo S=activo' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.CONTRASENA

IS

  'Contrasena de usuario final del sistema' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.EMAIL

IS

  'Direccion electronica del usuario final' ;

  COMMENT ON COLUMN CORE.SEGUSUARIO.IDSEGCARGO

IS

  'Cargo al que pertenece el usuario final del sistema' ;

CREATE UNIQUE INDEX CORE.SEGUSUARIO_PK ON CORE.SEGUSUARIO ( IDSEGUSUARIO ASC ) ;

CREATE UNIQUE INDEX CORE.CK_IDENTIFICACION ON CORE.SEGUSUARIO ( NUMIDENTIFICACION ASC ) ;

CREATE UNIQUE INDEX CORE.CK_USERNAME ON CORE.SEGUSUARIO ( USUARIO ASC ) ;

  ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT SEGUSUARIO_PK PRIMARY KEY ( IDSEGUSUARIO ) ;

  ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT CK_IDENTIFICACION UNIQUE ( NUMIDENTIFICACION ) ;

  ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT CK_USERNAME UNIQUE ( USUARIO ) ;

ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT FK_SEGUSUARIO_CARGO FOREIGN KEY ( IDSEGUSUARIO ) REFERENCES CORE.SEGUSUARIO ( IDSEGUSUARIO ) ;

ALTER TABLE CORE.SEGMENUGRUPO ADD CONSTRAINT SEGMENUGRUPO_SEGMENU_FK FOREIGN KEY ( IDSEGMENU ) REFERENCES CORE.SEGMENU ( IDSEGMENU ) ;

ALTER TABLE CORE.SEGMENUGRUPO ADD CONSTRAINT SEGMENUGR_SEGRUPO_FK FOREIGN KEY ( IDSEGRUPO ) REFERENCES CORE.SEGRUPO ( IDSEGRUPO ) ;

ALTER TABLE CORE.SEGMENU ADD CONSTRAINT SEGMENU_SEGMENU_FK FOREIGN KEY ( IDPADRESEGMENU ) REFERENCES CORE.SEGMENU ( IDSEGMENU ) ;

ALTER TABLE CORE.SEGRUPOUSUARIO ADD CONSTRAINT SEGRUPOUSUARIO_SEGRUPO_FK FOREIGN KEY ( IDSEGRUPO ) REFERENCES CORE.SEGRUPO ( IDSEGRUPO ) ;

ALTER TABLE CORE.SEGRUPOUSUARIO ADD CONSTRAINT SEGRUPOUSUARIO_SEGUSUARIO_FK FOREIGN KEY ( IDSEGUSUARIO ) REFERENCES CORE.SEGUSUARIO ( IDSEGUSUARIO ) ;

ALTER TABLE CORE.SEGUSUARIO ADD CONSTRAINT SEGUSUARIO_SEGCARGO_FK FOREIGN KEY ( SEGCARGO_IDSEGCARGO ) REFERENCES CORE.SEGCARGO ( IDSEGCARGO ) ;

CREATE SEQUENCE SEGCARGO_SEQ START WITH 1 NOCACHE ORDER ;

CREATE OR REPLACE TRIGGER SEGCARGO_TRG BEFORE

  INSERT ON CORE.SEGCARGO FOR EACH ROW WHEN (NEW.IDSEGCARGO IS NULL) BEGIN

  SELECT SEGCARGO_SEQ.NEXTVAL INTO :NEW.IDSEGCARGO FROM DUAL;

END;

/

CREATE SEQUENCE SEQ_SEGMENU START WITH 1 NOCACHE ORDER ;

CREATE OR REPLACE TRIGGER TRG_SEGMENU BEFORE

  INSERT ON CORE.SEGMENU FOR EACH ROW WHEN (NEW.IDSEGMENU IS NULL) BEGIN

  SELECT SEQ_SEGMENU.NEXTVAL INTO :NEW.IDSEGMENU FROM DUAL;

END;

/

CREATE SEQUENCE SEQ_SEGMENUGRUPO START WITH 1 NOCACHE ORDER ;

CREATE OR REPLACE TRIGGER TRG_SEGMENUGRUPO BEFORE

  INSERT ON CORE.SEGMENUGRUPO FOR EACH ROW WHEN (NEW.SEGMENUGRUPO IS NULL) BEGIN

  SELECT SEQ_SEGMENUGRUPO.NEXTVAL INTO :NEW.SEGMENUGRUPO FROM DUAL;

END;

/

CREATE SEQUENCE SEQ_SEGRUPO START WITH 1 NOCACHE ORDER ;

CREATE OR REPLACE TRIGGER TRG_SEGRUPO BEFORE

  INSERT ON CORE.SEGRUPO FOR EACH ROW WHEN (NEW.IDSEGRUPO IS NULL) BEGIN

  SELECT SEQ_SEGRUPO.NEXTVAL INTO :NEW.IDSEGRUPO FROM DUAL;

END;

/

CREATE SEQUENCE SEQ_SEGRUPOUSUARIO START WITH 1 NOCACHE ORDER ;

CREATE OR REPLACE TRIGGER TRG_SEGRUPOUSUARIO BEFORE

  INSERT ON CORE.SEGRUPOUSUARIO FOR EACH ROW WHEN (NEW.SEGRUPOUSUARIO IS NULL) BEGIN

  SELECT SEQ_SEGRUPOUSUARIO.NEXTVAL INTO :NEW.SEGRUPOUSUARIO FROM DUAL;

END;

/

CREATE SEQUENCE SEQ_SEGUSUARIO START WITH 1 NOCACHE ORDER ;

CREATE OR REPLACE TRIGGER TRG_SEGUSUARIO BEFORE

  INSERT ON CORE.SEGUSUARIO FOR EACH ROW WHEN (NEW.IDSEGUSUARIO IS NULL) BEGIN

  SELECT SEQ_SEGUSUARIO.NEXTVAL INTO :NEW.IDSEGUSUARIO FROM DUAL;

END;

/