Estandares de Base de Datos v.8.2.0[F]

Estndares de Base de Datos

Versin 8.2.0

Abril 2015

OFICINA GENERAL DE TECNOLOGAS DE LA INFORMACION

Documento: Estndares de Diseo e Interfaces para Aplicaciones

Web

Elaborado Por: Oficina de Seguridad Informtica de la OGTI

mbito: Uso Interno Nombre del Archivo: Estndares de Base de datos V8 Versin: 8.2.0 2 / 35

Identificacin del documento

tem Nombre Cargo Fecha

Elaboracin Iamys Rodriguez-Florido Administrador de Bases de

Datos 21.04.2015

Revisin Jorge Gutierrez Mendoza Director de la Oficina de

Tecnologa de la Informacin 27.04.2015

Aprobacin Oscar Gomez Marin

Director General de la Oficina

General de Tecnologas de la

Informacin

29.04.2015


Web



Historial de Versiones

Versin Descripcin del cambio Fecha

cambio Modificado por

Aprobado

por

1.0 Versin Inicial 28/06/2012 Gestor de Sistemas Jefe de

Sistemas

2.0

Define los estndares de

nomenclatura para el

modelo fsico de datos.

01/07/2012

Jess Vsquez

3.0

Estndares de nombres de

tablas con prefijos,

diccionario d datos, etc.

23/08/2012 Iamys Rodrguez-

Florido

DBA

4.0

Scripts para adicionar

propiedad extendida y

otras modificaciones


Florido

DBA

5.0

Scripts para buscar

cadenas dentro del

diccionario


Florido

DBA

6.0

Detalla la documentacin

de interaccin con otras

BD

26/12/2014 Iamys Rodriguez-

Florido

DBA

7.0 Agregando buenas

prcticas


Florido

DBA

8.0 Definiendo explcitamente

documentacin a entregar


Florido

DBA

8.1 Agregando ejemplos

detallados en el captulo 4


Florido

DBA


Web



Tabla de Contenido

Estndares para el diseo de las bases de datos ................................................................... 6

OBJETIVOS ........................................................................................................................................................................... 6

CONVENCIONES GENERALES ............................................................................................................................................. 6

CAPITULO 1: Nomenclaturas a utilizar en la base de datos .............................................. 7

NOTACIN DE BASE DE DATOS ........................................................................................................................................ 7

NOTACIN DE TABLAS....................................................................................................................................................... 7

NOTACIN DE CAMPOS ...................................................................................................................................................... 8

NOTACIN DE LLAVES FORNEAS QUE SE REFIEREN A UNA BASE DE DATOS.......................................................... 9

NOTACIN DE CONSTRAINTS ........................................................................................................................................ 10

NOTACIN DE LOS NDICES ............................................................................................................................................ 10

NOTACIN DE LOS TRIGGERS ........................................................................................................................................ 11

NOTACIN DE LOS STORED PROCEDURE ..................................................................................................................... 11

ESTRUCTURA DEL STORED PROCEDURE ..................................................................................................................... 12

NOTACIN DE LAS FUNCTIONS ..................................................................................................................................... 13

NOTACIN DE LAS VISTAS ............................................................................................................................................. 14

NOTACIN DE LOS USUARIOS DE LA BASE DE DATOS ............................................................................................... 14

OTRAS CONVENCIONES EN LA PROGRAMACIN SQL ................................................................................................. 15

CAPTULO 2: Diccionario de Datos .......................................................................................... 17

CMO HACER UN DICCIONARIO USANDO LA PROPIEDAD EXTENDIDA DESCRIPCIN ........................................ 17

CDIGO FUENTE DEL STORED PROCEDURE VERCAMPOS ....................................................................................... 18

SCRIPT PARA AGREGAR PROPIEDAD EXTENDIDA DESCRIPCION A UN CAMPO DE UNA TABLA USANDO SCRIPT.

............................................................................................................................................................................................ 20

SCRIPT PARA ELIMINAR LA PROPIEDAD EXTENDIDA DESCRIPCION DE UN CAMPO DE UNA TABLA. ............... 20

SCRIPT PARA BUSCAR TROZOS DE LA DESCRIPCIN DE UN CAMPO ......................................................................... 20

SCRIPT PARA BUSCAR NOMBRES DE CAMPOS EN EL DICCIONARIO DE DATOS ........................................................ 21

CAPTULO 3: Buenas prcticas en la programacin SQL ................................................. 21

SENTENCIAS INSERT..................................................................................................................................................... 22

CONSULTAS QUE DEVUELVEN MUCHOS REGISTROS ................................................................................................... 22


Web



USO DE LOS NDICES ....................................................................................................................................................... 22

USO DE CURSORES ........................................................................................................................................................... 23

DOCUMENTACIN DEL CDIGO FUENTE ...................................................................................................................... 23

CONTROL DE TRANSACCIONES ...................................................................................................................................... 23

CAMBIOS EN BASES DE DATOS EXTERNAS A LA BASE DE DATOS DE LA APLICACIN ............................................ 24

CAPTULO 4: Documentacin requerida antes del Pase al Ambiente de Pruebas de

una nueva base de datos. ............................................................................................................ 25

1. MODELO DE DATOS ................................................................................................................................................ 25

2. DICCIONARIO DE DATOS ........................................................................................................................................ 25

3. DOCUMENTO DE INTERACCIN CON OTRAS BASES DE DATOS ......................................................................... 27

4. PROCESOS ADICIONALES ........................................................................................................................................ 30

5. DETALLE DE INFORMACIN SENSIBLE ................................................................................................................ 31

6. PROYECCIN DE CRECIMIENTO DE LA BASE DE DATOS ..................................................................................... 31

CAPTULO 5: Monitoreo de Informacin sensible ............................................................. 32

TABLAS DE AUDITORIA PARA LA INFORMACIN SENSIBLE ....................................................................................... 32

CAPTULO 6: Entregas de scripts para pases al ambiente de Pruebas y Produccin

.............................................................................................................................................................. 34

ORGANIZANDO LOS SCRIPTS .......................................................................................................................................... 34


Web



Estndares para el diseo de las bases de datos

Objetivos

Estandarizar la nomenclatura de objetos de base de datos utilizando para este desarrollo

el SQL Server.

Estandarizar la estructura de codificacin del SQL Transact.

Estandarizar la documentacin que debe entregarse antes del pase al ambiente de

Pruebas y durante.

Convenciones generales

Mantener nombres cortos y descriptivos, usando abreviaturas de mximo 4 5

caracteres en caso de que se requiera.

Ejemplo: En lugar de usar DAT_EXPEDIENTE_CAS_RESOLUCION_CAS

Mejor usar DAT_EXP_CAS_RESOL

Mantener nombres de objetos nicos, por ejemplo evitar crear la tabla ALMACEN y un

rol o vista con el mismo nombre.

Por defecto, no se aceptan espacios en blanco en medio de los identificadores; sin

embargo, su uso est permitido si se usan identificadores delimitados por comillas

dobles. En el presente estndar, no se permiten los espacios en blanco como parte de un

identificador.

Para la definicin de nombre de objetos de base de datos de acuerdo al caso se usar el

caracter underscore _ para separar las palabras_del_nombre.

Un campo numrico que sea identificador de llave se debe llamar id_xxx, mientras que

a los campos llaves alfanumricos se les debe nombre como Codxxx

El nombre de un campo campos (llaves forneas) que se refiera a una BD diferente a la

actual (en otro servidor o en el mismo) deben mantener su nombre igual en lo posible.


Web



CAPITULO 1: Nomenclaturas a utilizar en la base de datos

Notacin de Base de Datos

Nomenclatura:

El nombre de la base de datos debe estar en maysculas y comenzar con el prefijo DB_

Ejemplo:

Base de Datos : DB_COMALM

Archivo MDF : DB_COMALM_Data

Archivo LDF : DB_COMALM_Log

El nombre de la base de datos debe ser igual al nombre de la aplicacin a la que sirve, de

manera que sea ms facil a priori identificar a que aplicacin pertenece una base de datos

determinada

Notacin de Tablas

Nomenclatura:

El nombre de la tabla debe ser descriptivo, en singular y en maysculas.

Las tablas identifican una entidad del sistema con un nombre completo.

Si el nombre de la tabla es muy largo usar abreviaturas de 4 ms 5 caracteres,

ejemplo DAT_OBSERVACION mejor queda como DAT_OBSERV.

Una tabla hija debe llevar al inicio el nombre de la tabla padre.

Si la entidad o proceso que genera la tabla tienen ms de una palabra se deber colocar

cada una de ellas en singular y deben ser separadas por un "_".

Si la entidad o proceso representa a una tabla de uso temporal, el nombre de la misma

debe ser precedida por el siguiente prefijo TMP_.

Ejemplo:

Nomenclatura de una tabla padre (o cabecera)

DAT_PROVEED

Nomenclatura de una tabla hija

DAT_PROVEED_OBSERV

Nomenclatura de una tabla temporal


Web



TMP_XXX

Se usarn prefijos de 3 letras para identificar los tipos de tablas. Esto facilita la administracin de la Base

de Datos, ya que es ms fcil separar x el prefijo cuales tablas son codificadores y cuales son tablas

transaccionales.

DAT_ Tablas transaccionales

TIPO_ Tablas con maestros simples de (2 columnas) tipo cdigo/descripcin, ejemplo

TIPO_COLEGIO (id_colegio, nom_colegio)

CAT_ Catlogos de cdigos que tengan ms de 2 columnas. Ejemplo: CAT_EMPRESAS (id_empresa,

nom_empresa, codigo_distrito, codigo_prov, codigo_dep )

MAE_ Maestros de cdigos, que se utilicen en ms de una Base de Datos

SEG_ Tablas que almacenan informacin relacionada con el control de acceso, ejemplo

SEG_USUARIOS, SEG_PAGINAS

ZZZ_ Tablas que por se creen para sacar algn resultado temporal pero que despus pueden ser

borradas de la Base de Datos por el Administrador

RES_ Tablas que almacenan resultados totalizados o tambin TOT_

IMP_ Tablas que se importan a partir de informacin de otras Bases de Datos

TMP_ Tablas temporales

En las consultas se deber especificar el alias de cada campo al inicio, ejemplo:

SELECT f.id_factura, cl.nombre

FROM CLIENTE cl inner join FACTURAS f

Nota: De esta manera, se reduce el tiempo de bsqueda del campo a la hora de ejecutar la consulta, de lo contrario el SQL

busca en todo el catlogo de campos para identificar a cual tabla pertenece ese campo.

Notacin de Campos

Nomenclatura:

Los nombres de los campos deben ser descriptivos, en singular y en minsculas.

Ejemplo:

id_empleado

nombre

documento

No utilizar acentos (,,,,), ni caracteres especiales como sino sustituirlo por

n .

Los campos llave q sean numricos, ya sean tipo IDENTITY o no, tendrn el prefijo id_


Web



Ejemplo: id_grupo, id_empleado, id_capitulo

Si por el contrario es un campo de caracteres tendr el prefijo cod_

Cuando un campo se utiliza en varias tablas, por ejemplo como llave fornea, dicho

campo debe tener exactamente el mismo nombre en todas las tablas donde se use. Esto

posibilita que sea ms fcil reconocer las relaciones de dependencia entre las tablas

incluso cuando no estn explcitamente declaradas. Por ejemplo entre estas 2 tablas

hay una relacin no explcita de 1 a muchos:

TIPO_COLEGIO ESTUDIANTE

id_colegio id_alumno

nombre_colegio id_colegio

edad

Si un campo en una tabla es una codificacin asociada a un maestro de cdigos de otra

tabla, el nombre del campo debe ser igual al nombre de la tabla maestra, ejemplo:

Tabla: DAT_CAPITULOGRUPO

o id_capitulo

o id_grupo

Tabla maestra: TIPO_GRUPO

o id_grupo

o Nombre_grupo

Notacin de Llaves forneas que se refieren a una Base de Datos

En caso que exista un campo que sea una llave fornea, que haga referencia a una Base de Datos

diferente a la Base de Datos actual, debe conservarse (en lo posible) el nombre del campo tal y

como aparece en la Base de Datos original.


Web



Por ejemplo: Si hacemos referencia al campo codigo_distrito de la Base de Datos DB_GENERAL

tabla DISTRITO, aunque la nomenclatura no corresponda con los estndares debemos respetar el

nombre.

Notacin de Constraints

El nombre de la tabla debe estar en mayscula.

ndices con llaves primarias (Primary Key).

Nomenclatura:

PK + Nombre de la tabla.

Ejemplo:

PK_PROVEEDOR

ndices con llaves forneas o relacionadas (Foreing Key).

Nomenclatura:

FK_NombreTablaOrigen_NombreTablaReferenciada

Ejemplo:

FK_PROVEEDOR_ORDENCOMPRA

Indices Unique:

Nomenclatura:

UQ_NemnicoTabla_NombreUnique

Ejemplo:

UQ_CLIENTE_CODIGO_CLIENTE

Notacin de los ndices

Nomenclatura

IXX_ YYYY.


Web



XX : Tipo de ndice (Unique, Clustered, NonClustered)

YYY : Nombre del Indice

Ejemplo:

IUQ_CODEMPLEADO (ndice Unique)

ICL_CCUSTODIA (ndice Clustered)

INC_CODDEPOSITANTE (NonClustered)

Notacin de los Triggers

Nomenclatura para los Triggers:

TR_NOMBRETABLE_D (Cuando se realiza una eliminacin en la tabla).

TR_NOMBRETABLA_U (Cuando se realiza una actualizacin en la tabla).

TR_NOMBRETABLA_I (Cuando se realiza una insercin en la tabla).

TR_NOMBRETABLA_IU (Cuando se realiza una insercin o actualizacin en la tabla).

Notacin de los Stored Procedure

Nomenclatura para los Stored Procedures:

Usarn el prefijo p_

Insert:

p_NOMBRETABLA_Add (Inserta un registro a la tabla)

Update:

p_NOMBRETABLA_Update (Actualiza un registro a la tabla)

Delete:

p_NOMBRETABLA_Delete (Elimina un registro de la tabla)

Select:


Web



p_NOMBRETABLA_GetByID (Retorna un slo registro por PK o AK).

p_NOMBRETABLA_ListByXXXX (Retornan ms de un registro)

p_NOMBRETABLA_ListXXXX

Ejemplo

p_CAT_EMPLEADO_Add

p_CAT_EMPLEADO _Insert

p_CAT_EMPLEADO _Update

p_CAT_EMPLEADO _GetByID

p_CAT_EMPLEADO _ListByEmpresa

p_CAT_EMPLEADO _ProcesaSueldo

Nota:

Los nombres de los Stored Procedures NO deben comenzar con sp, esto porque

generalmente el SQL piensa que son system procedures y los busca primero en la Base

de Datos master

SET NOCOUNT ON (elimina la notificacin del nro. de registros afectados por cada sentencia SQL lo cual

incrementa el performance.

Estructura del Stored Procedure

Identificador, nombre de stored procedure, y parmetros

Comentarios:

Descripcin: funcionalidad del stored procedure

Fecha Creacin:

Fecha Modificacin:

Descripcin de Parmetros de entrada y salida

Autor:

Versin:

Cambios Importantes

Declaracin Variables locales

Maysculas y minsculas

Sentencias SQL

Palabras del lenguaje SQL, y funciones de sistema en MAYUSCULAS, columnas

y otras variables en Maysculas.

Sentencias legibles e indentadas (cada clausula SQL en una lnea nueva)


Web



Ejemplo:

CREATE PROCEDURE p_EMPRESA_Add(

@CODEMPRESA int,

@RAZONSOCIAL varchar (50),

...)

AS

/***************************************

*Descripcin: Aade un registro a la tabla HUB

*Fecha Creacin: 19/02/2001

*Fecha Modificacin: 19/02/2001

*Parmetros: @CODEMPRESA: Cdigo de la empresa

* @RAZONSOCIAL: Razn Social de la Empresa

*

*Autor: Juan Perez (14201)

*Versin: Final (Beta | Final)

*Cambios Importantes: Inclusin de la condicin se consulta

(15/01/2011)

******************************************/

Notacin de las Functions

Usarn el prefijo f_ y seguirn la misma nomenclatura que los procedimientos

almacenados.

Ejemplo:

CREATE FUNCTION f_Obtener_FechaFinal(

@CODEMPRESA int,

@RAZONSOCIAL varchar (50),

...)

AS

/***************************************

*Descripcin: Busca el campo FechaFinal

*Fecha Creacin: 19/02/2001

*Fecha Modificacin: 19/02/2001

*Parmetros: @CODEMPRESA: Cdigo de la empresa

* @RAZONSOCIAL: Razn Social de la Empresa

*

*Autor: Juan Perez (14201)

*Versin: Final (Beta | Final)


Web



*Cambios Importantes: Inclusin de la condicin se consulta

(15/01/2011)

******************************************/

Notacin de las Vistas

Nomenclatura para las Vistas:

VW_YYY.

VW : Identificador para las Vistas

YYY : Nombre de la Vista en Maysculas

Ejemplo:

VW_CONSULTAR_PERSONA

Para el nombre de las Vistas utilizar verbos en Infinitivo,

Ejemplo:

VW_CONSULTAR_CUENTAS

Nota: La estructura de la vista debe ser similar a la del Stored Procedure.

Notacin de los Usuarios de la Base de Datos

Los usuarios nuevos que se creen dentro del SQL para acceder desde una aplicacin

seguirn el siguiente estndar de nomenclatura

usr_nombredelaBD usuario que utilizar la aplicacin

dvp_letradel nombreyapellido usuario que utilizarn los programadores

(Developers) durante el desarrollo


Web



Ejemplos:

usr_vup Es un usuario de aplicacin, y la Base de Datos se llama DB_VUP

dvp_jraygada Es un usuario para un desarrollador y su nombre comienza con j

su apellido es Raygada

Otras convenciones en la programacin SQL

Utilizar maysculas para las sentencias propias del SQL

Ejemplo.-

SELECT NumeroDocumento,

TipoDocumento ,

ApellidoPaterno,

ApellidoMaterno,

Nombre

FROM DAT_DEPOSITANTE

ORDER BY NumeroDocumento

Utilizar el Tabulador para separar los campos de una condicin (en la medida de lo

posible)

Ejemplo.-

SELECT 'Codigo_segmentoSAB' = CodigoDepositante,

'CodigoSegmentoCONASEV' = '00' +SUBSTRING(NumeroRegistro,2,1),

'CodigoRetorno' = '0'

FROM DAT_DEPOSITANTE

WHERE numero_documento = @numero_documento

AND tipo_documento = @tipo_documento

AND relacionador_correlativo = @relacionador_correlativo

Indentar el Cdigo para conservar un orden

Ejemplo.-


Web



CREATE PROCEDURE BUSCARCADENA

( @Variable VARCHAR (255),

@Tipo VARCHAR (1) =""

)

AS

BEGIN

IF LTRIM (RTRIM (@Variable)) "*"

IF @Tipo = ""

SELECT NOMBRE = name ,

TIPO = type ,

CREACION = crdate

FROM sysobjects

WHERE name LIKE '%'+ @Variable + '%'

ORDER BY type,

name

ELSE

SELECT NOMBRE = name ,

TIPO = type ,

CREACION = crdate

FROM sysobjects

WHERE name LIKE '%'+ @Variable + '%'

AND type = RTRIM (LTRIM (@Tipo))

ORDER BY name

END


Web



CAPTULO 2: Diccionario de Datos

Cmo hacer un diccionario usando la propiedad extendida Descripcin

Es posible utilizar la propiedad extendida Descripcin para incluir una explicacin de

cada campo (de cada tabla) de una Base de Datos SQL Server.

Vase a continuacin como actualizar dichas Descripciones.

Se implementar un procedimiento almacenado llamado vercampos que permita

recuperar las descripciones de la tabla. De esta manera el Diccionario de Datos podr ser

consultado en cualquier momento desde la misma Base de Datos.

Ejemplo: vercampos colegio


Web



Mostrar el siguiente listado:

Este procedimiento es especial vercampos y no tendr el prefijo p_ del resto de los

procedimientos almacenados, para diferenciarlo y hacer ms fcil su ejecucin.

Se recomienda incluir una nueva tabla en cada Base de Datos con el nombre

VinventarioBD, donde se incluya el Nombre de cada tabla con un campo Descripcin

donde se explique de qu trata la tabla. Esta informacin se podr recuperar implementando

el procedimiento almacenado vertabla que permita visualizar la descripcin de una Tabla

o de Todas

Cdigo fuente del stored procedure vercampos

USE [NombredelaBDatos]

GO

/****** Object: StoredProcedure [dbo].[vercampos] Script Date:

09/12/2012 12:08:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[vercampos]

@tabla as varchar(30)

AS

if @tabla IS NULL


Web



begin

SELECT

[Tabla] = OBJECT_NAME(c.object_id),

[Columna] = c.name,

[Descripcion] = ex.value,

[Tipo]= t.name ,

[Tam]=c.max_length

FROM

sys.columns c

LEFT OUTER JOIN

sys.extended_properties ex

ON

ex.major_id = c.object_id

AND ex.minor_id = c.column_id

AND ex.name = 'MS_Description'

JOIN sys.types AS t ON c.user_type_id=t.user_type_id

WHERE

OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0

ORDER

BY OBJECT_NAME(c.object_id), c.column_id

end

else

begin

SELECT


[Columna] = c.name,


[Tipo]= t.name ,

[Tam]=c.max_length

FROM

sys.columns c

LEFT OUTER JOIN


ON





WHERE


AND OBJECT_NAME(c.object_id) = @tabla

ORDER


end


Web



Script para agregar propiedad extendida Descripcion a un campo de una tabla

usando script.

Para agregar mediante SQL la Descripcion a un campo use la siguiente sentencia, cambiando los

parametros @value, @level0name (esquema), @level1name (nombre de la tabla), @level2name

(nombre del campo)

EXEC sys.sp_addextendedproperty @name=N'MS_Description'

, @value=N'Especifique aqu un comentario q describa al campo' ,

@level0type=N'SCHEMA',@level0name=N'dbo',

@level1type=N'TABLE',@level1name=N'NOMBRE_DE_LA_TABLA',

@level2type=N'COLUMN',@level2name=N'nombre_del_campo'

GO

Script para eliminar la propiedad extendida Descripcion de un campo de una

tabla.

EXEC sp_dropextendedproperty

@name = 'MS_Description'

,@level0type = 'schema'

,@level0name = dbo

,@level1type = 'table'

,@level1name = 'NOMBRE_DE_LA_TABLA'

,@level2type = 'column'

,@level2name = 'nombre_del_campo';

Script para buscar trozos de la descripcin de un campo

A continuacin se detalla un query que permite visualizar todas las descripciones de campos que

contienen una cierta cadena de caracteres.

SELECT


[Columna] = c.name,


Web




[Tipo]= t.name ,

[Tam]=c.max_length

FROM

sys.columns c

LEFT OUTER JOIN


ON





WHERE


and CAST(ex.value as varchar(300)) like '%CADENAQUEBUSCO%'

ORDER


Script para buscar nombres de campos en el diccionario de datos

A continuacin se detalla un query que permite visualizar todos los campos de todas las tablas de

la base de datos actual que contienen una cierta cadena de caracteres.

SELECT


[Columna] = c.name,


[Tipo]= t.name ,

[Tam]=c.max_length

FROM

sys.columns c

LEFT OUTER JOIN


ON





WHERE


AND c.name like '%CADENAQUEBUSCO%'

ORDER


CAPTULO 3: Buenas prcticas en la programacin SQL


Web



Sentencias INSERT

Incluir siempre los nombres de los campos en una sentencia INSERT, por ejemplo esta

sentencia es la recomendada:

INSERT INTO mitabla (campo1, campo2, campo3)

SELECT campofuente, campofuente2, campofuente3

FROM tablaX

Mientras que esta otra sentencia, puede acarrear problemas cuando se creen nuevos

campos en la tabla mitabla.

INSERT INTO mitabla

SELECT campofuente, campofuente2, campofuente3

FROM tabla2

Consultas que devuelven muchos registros

Evitar consultas que devuelvan demasiados registros, de manera que puedan retardar el

desempeo del servidor de BD. Por ejemplo si se hace una bsqueda de datos desde el ao

2004 al 2012, se podra restringir para que la bsqueda sea en un rango menor de aos

(mximo 2 aos).

Uso de los ndices

Recordar que cuando se utilice una sentencia WHERE, el orden de los criterios de bsqueda

debe estar igual al orden del ndice de la tabla que se referencia.

Ejemplo:

Tabla DAT_ESCARGA

Indexado por: FECHA, ID

El orden en la sentencia where para esta tabla debe ser

WHERE fecha> 20120101 and id =100


Web



y no WHERE id =100 and fecha> 20120101

Uso de cursores

Evitar en lo posible el uso de cursores, ya que consumen muchos recursos del servidor,

adems de hacer ms obscura la programacin, tratando de buscar soluciones en base a

cambios en el Diseo de la base de datos, y/o apoyndose en las sentencias siguientes:

PIVOT

RANK

DENSE_RANK

Documentacin del cdigo fuente

Proporcionar la mayor cantidad de comentarios en los procedimientos almacenados y

funciones de manera que se describan all los procesos implementados.

Control de transacciones

En los procedimientos almacenados dentro de la base de datos deben incluirse las

sentencias necesarias para el control de las transacciones, a saber: BEGIN TRANSACTION, END

TRANSACTION, ROLLBACK, etc. A continuacin un breve ejemplo:

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN

UPDATE Authors

SET Phone = '415 354-9866'

WHERE au_id = '724-80-9391'

SELECT @intErrorCode = @@ERROR

IF (@intErrorCode 0) GOTO PROBLEM

UPDATE Publishers

SET city = 'Calcutta', country = 'India'

WHERE pub_id = '9999'


Web



SELECT @intErrorCode = @@ERROR

IF (@intErrorCode 0) GOTO PROBLEM

COMMIT TRAN

PROBLEM:

IF (@intErrorCode 0) BEGIN

PRINT 'Unexpected error occurred!'

ROLLBACK TRAN

END

Cambios en bases de datos externas a la base de datos de la aplicacin

Cuando durante el desarrollo de nuevas aplicaciones se requiere hacer cambios ya sea de

estructura o de datos en alguna base de datos externa a la nueva aplicacin, se debe comunicar al

DBA con anticipacin, de manera que se pueda analizar la manera ms conveniente de agregar

los nuevos datos.

Por ejemplo, si se necesita agregar un nuevo campo en alguna de las tablas de la base de datos

DB_GENERAL.

Como una buena prctica se recomienda en lugar de hacer cambios a tablas ya existentes, crear

tablas adicionales con la llave primaria de la tabla deseada y all agregar los nuevos campos, de

manera que la modificacin no afecte a las aplicaciones ya existentes, a nivel de cdigo o de

desempeo.


Web



CAPTULO 4: Documentacin requerida antes del Pase al ambiente de

Pruebas de una nueva base de datos.

1. Modelo de datos

Describir a travs del Diagramas el modelo de datos de la aplicacin, pueden hacerse

usando alguna herramienta como ERWIN, Visio, Rational Rose, SQL Server o cualquier otra, y

luego creando imgenes.

Debe existir al menos un diagrama resumen que contenga los nombres de las tablas y sus

llaves, y aparte estara el diagrama detallado con todos los campos. A continuacin un ejemplo del

diagrama resumen.

2. Diccionario de datos


Web



Documentar un Diccionario de Datos detallado, donde deben aparecer:

Tablas y sus campos bien descritos

Vistas

Procedimientos

Funciones

Reportes si los hubiera

A continuacin un ejemplo de diccionario de datos.

El documento debe estar redactado en Excel o en su defecto el diccionario podra estar en la

misma base de datos, tal como se describe en el captulo 2 del presente documento.

El diccionario de datos, tambin debe incluir resaltado de alguna manera, el detalle de la

informacin sensible, tal como se explica en el captulo 5 del presente documento.


Web



3. Documento de interaccin con otras bases de datos

Proporcionar diagramas que describan la interaccin de la base de datos de la aplicacin con

otras bases de datos externas a ella, ya sea que se encuentren o no en el mismo servidor.

Si es necesario, habra que describir con palabras los procesos de interaccin.

Las interacciones ms comunes con otras bases de datos, se refieren por ejemplo al intercambio

de datos con bases de datos generales del Ministerio como son: Tramite Documentario o la base

de datos que contiene los catlogos generales de todas las aplicaciones del Ministerio, como la

tabla que registra la informacin de todos los administrados o la tabla con el listado de Especies

Marinas, o los catlogos de Ubicacin Geogrfica UBIGEO.

Es de destacar que se espera se destine una conexin de base de datos aparte para acceder desde

la nueva aplicacin a cualquiera de las bases de datos externas, de esta manera se evitar tener

que alojar la base de datos de la nueva aplicacin en el mismo servidor que la bases de datos que

contienen los Catlogos del Ministerio.

Si existieran triggers que apunten a tablas en otras bases de datos, o paquetes de integracin SSIS

que sean parte de la aplicacin, Linked Servers, o empaquetados de aplicacin (archivos .jar u

otros) que desaten procesos que actualizan otras bases de datos externas o importen informacin

desde otras bases de datos, deben ser descritos.

La intencin de esta documentacin es saber qu debemos mover, adicionalmente a la base de

datos, en caso de que se desee cambiar la base de datos de servidor.

De la misma manera debe documentarse cualquier nuevo dato/procedimiento/tabla que se haya

incluido en las bases externas a la base de datos de la aplicacin como parte del nuevo desarrollo.

A continuacin un ejemplo de diagrama simple donde se muestra la interaccin de dos bases de

datos, en este caso se actualiza mediante un proceso la data de una tabla en otra base de datos, la

imagen es referencial.


Web



Este documento debe incluir tambin un detalle con los permisos de acceso que debe tener el (o

los) usuarios de bases de datos en esas otras bases de datos externas, ya sean de SELECT, INSERT,

UPDATE, permisos globales o en el sistema, etc. Vase a continuacin un ejemplo de cmo podra

ser esta documentacin, la imagen es referencial.


Web



Todas las aplicaciones nuevas debern utilizar los Catlogos Generales de Datos que ya existen en

el Ministerio y que en la mayora de los casos se encuentran alojados en la base de datos

DB_GENERAL, por ejemplo:

Maestro de UBIGEO ( Departamento / Provincia / Distrito)

Maestro de Especies

Maestro de Embarcaciones

Maestro de Empresas y Personas (Administrados / Armadores, etc.)

Maestro de usuarios de extranet

Maestro de Trabajadores

Maestro de Dependencias

Maestro de Plantas Procesadoras


Web



Solo se justifica utilizar una tabla propia de la aplicacin en los casos en que la data de Empresas

/ Personas, ya que podra darse el caso de que en nuestro Maestro no estuvieran todas las

empresas deseadas. En ese caso debe incluirse en la tabla Maestra propia de la aplicacin el campo

id_persona para los casos en que exista un registro relacionado en la base de datos

DB_GENERAL.dbo.PERSONA de manera que se pueda enlazar la mayor cantidad posible de datos

a las bases de datos ya existentes en otras aplicaciones.

Est terminantemente prohibido crear aplicaciones donde se use una codificacin propia para los

datos ya existentes en los Catlogos del Ministerio.

Por ejemplo a continuacin se muestran catlogos que sirven para todas las aplicaciones del

Ministerio

Tabla: ESPECIES que contiene la lista de especies marinas asignado a un cdigo

Tabla: UBIGEO, que contiene la versin actualizada de los DEPARTAMENTOS / PROVINCIAS /

DISTRITOS de todo el pas

Tabla: PERSONA que contiene el listado con todas las personas naturales y empresas que han hecho

algn trmite documentario en PRODUCE alguna vez.

Tabla: Padrn SUNAT

4. procesos adicionales

Deben documentarse de alguna manera los procesos adicionales relacionados a la base de datos,

como importacin / exportacin masiva de datos, proyectos de integracin SSIS, webservices,

etc. en caso de que los hubiera.

Por ejemplo en el caso de usar un servidor de aplicaciones Java (GlassFish / Tomcat / Weblogic )

debe describirse donde se puede encontrar el cdigo fuente, y el paquete (.jar) debe ser

configurable mediante un archivo aparte, de manera que no sea necesario recompilar en caso de

que se cambie la contrasea.

El uso del Glassfish qued descontinuado, o sea, ya no se usar para nuevas aplicaciones


Web



5. Detalle de informacin sensible

El diccionario de datos debe describir en detalle cuales son las tablas y/o campos que contienen

informacin sensible en la base de datos y documentar cuales son las tablas de auditoria u otras

medidas que se tomaron en este sentido.

En el siguiente captulo 5, se describe las posibles maneras de documentar la informacin

sensible.

6. Proyeccin de crecimiento de la base de datos

Incluir un documento que haga una proyeccin del crecimiento estimado de las tablas de la base

de datos en un periodo de 5 aos.

A continuacin un ejemplo de lo que debe incluir este documento:


Web



CAPTULO 5: Monitoreo de Informacin sensible

tablas de auditoria para la informacin sensible

Para los datos cuya informacin sea sensible, se recomienda incluir tablas de auditoria con sus

correspondientes triggers de UPDATE/DELETE/INSERT, aunque quizs sea necesario adems

realizar procesos dentro de la aplicacin para obtener datos especficos como la IP desde donde

se est conectando el usuario cuando se trata de una web.

Ejemplo de informacin sensible es cualquier dato que sea personal de los administrados o

tambin los datos que en caso de ser adulterados puedan redundar en procesos penales, por

ejemplo la concesin de permisos de pesca, multas, etc.

A continuacin un ejemplo de una tabla que contiene el listado de embarcaciones y las

autorizaciones de permisos de pesca, vase el nombre de la tabla y el nombre de la tabla de

auditoria:

CAT_EMBNAC

CAT_EMBNAC_AUDT

La tabla de auditora debern incluir los mismos campos de la tabla inicial y adicionalmente

campos donde se detalle la IP, usuario de la red, fecha/hora en que se realiz el cambio a la tabla

y que tipo de cambio fue INS/DEL/UPD.

Propuesta de Campos

1. Maquina IP desde donde se hizo la actualizacin (direcc_ip)

2. Usuario de Aplicacin (usuario)

3. Campo auto numrico (id_audt)

4. Fecha del Registro de Auditoria (fecha_audt)

5. Tipo de Operacin (tipo_operac): INS / UPD / DEL

A continuacin un ejemplo del contenido de una tabla de auditora.


Web



Adicionalmente en el caso de la operacin de las operaciones de actualizacion (UPD) debe

incluirse un registro tanto de la informacin tal como estaba antes de la modificacion (OLD) como

del registro despues de la modificacin (UPD), de esta manera si se quisiera regresar por alguna

razn la data al estado antes de la actualizacin se contara con toda la data antes del cambio,

vease la siguiente imagen de ejemplo:


Web



CAPTULO 6: Entregas de scripts para pases al ambiente de Pruebas y

Produccin

Organizando los scripts

Se espera que los desarrolladores (ya sean empleados del Ministerio o proveedores externos)

entreguen las bases de datos que pasarn al ambiente de Pruebas y a Produccin en un uno o

varios archivos de scripts con extensin SLQ.

Los archivos deben estar numerados segn la secuencia en que deben ejecutarse.

Ejemplo 1:

Para el pase al ambiente de Pruebas de la aplicacin CERCAP, se le enviaron al DBA los siguientes

scripts que de ejecutar segn la secuencia numrica indicada:


Web



Script01_CERCAP_CreacionTablasyVistas.sql

Script02_CERCAP_CreacionStoredProcedures.sql

Script03_CERCAP_CreacionFunciones.sql

Script04_CERCAP_CreacionOtros.sql

Ejemplo 2:

Pase al ambiente de Pruebas segunda iteracin, se le enva al DBA scripts para actualizacin de

algunos objetos de la base de datos, los scripts deben ejecutarse en la secuencia numrica

indicada:

Script01_CERCAP_LimpiarTablas.sql

Script02_CERCAP_CambiosTablasv01.sql

Script03_CERCAP_InsercionDatosCatalogo.sql

Los nombres de los scripts son referenciales, pero si debe incluirse la secuencia numrica, porque

es el orden en que se deben ejecutar.

52654669726d61312e312e302015-05-05T09:39:55-0500172.16.210.23:D89D67C9F3C6:LocalTime Doy V B

52654669726d61312e312e302015-05-06T19:08:16-0500192.168.206.11:8851FB4D1C7A:LocalTime En seal de conformidad

Documents

Estandares de Base de Datos v.8.2.0[F]