Reforzamiento Sesin: Refinamiento de los Modelos Normalizacin

Reforzamiento Sesin: Refinamiento de los Modelos NormalizacinIng. Ivan Crispin Sanchez1Normalizacin de BD RelacionalesManera en que los atributos son agrupados en los esquemas de relaciones de una BD, a fin de evitar anomalas y problemas que pueden ocurrir con los datos.Semntica de una relacinCuando agrupamos atributos para formar un esquema de relacin, buscaremos que exista un significado asociado a los atributos escogidos.La semntica especifica que relacin hay entre los valores de los atributos de una tupla. Cuanto mas fcil sea explicar la semntica de la relacin , mejor ser el diseo del esquema correspondiente.2EJEMPLO :El significado del esquema de relacin empleado es sencillo:Cada tupla representa a un empleado, con valores para su cdigo, nombre, fecha de nacimiento, sexo, sueldo y para el numero de departamento al que pertenece ( nDep ). El atributo nDep es una clave fornea que representa un vnculo implcito entre empleado y departamento, es decir un empleado pertenece a un departamento.numDep nom codJefefe fechIniJefeDEPARTAMENTOcodEm nom fechNac direc sexo suel nDepEMPLEADO3Formas NormalesFue Edgar F. Codd quien en 1972 propuso el proceso de normalizacin, as cualquier esquema de relacin se puede someter a una serie de pruebas para certificar si pertenece o no a cierta forma normal, que originalmente fueron tres : primera, segunda y tercera formas normales.

Posteriormente Boyce y Codd replantearon la tercera forma normal que se conoce hoy como Boice - Codd Norm Form ( BCNF). La segunda y tercera formas se fundamentan en el concepto de dependencias funcionales.

Despus se formularon la cuarta y quinta forma normal, basados en dependencias multivaluadas y dependencias de reunin.4Regla Descripcin

Primera Forma Normal (1FN) Incluye la eliminacin de todos los grupos repetidos.

Segunda Forma Normal (2FN) Asegura que todas las columnas que no son llave sean completamente dependientes de la llave primaria (PK).

Tercera Forma Normal (3FN) Elimina cualquier dependencia transitiva. Una dependencia transitiva es aquella en la cual las columnas que no son llave son dependientes de otras columnas que tampoco son llave.

Cuarta Forma Normal (4FN) Se separan las relaciones 0..n entre los atributos

Cuarta Forma Normal (4FN) Basada en las dependencias de JOINs

GRADOS DE NORMALIZACINSe implement para prohibir atributos compuestos, multivaluados y sus combinaciones. Entonces debemos evitar grupos repetitivos de datos.PRIMERA FORMA NORMAL1FN((EJEMPLO :numDep nomDep codJefefe lugaresDepDEPARTAMENTOUn departamento puede estar distribuido en varios lugares. Por ejemplo el Departamento de Ingeniera ( numDep=5) puede tener oficinas en Cuzco y Tacna (lugaresDep)numDep nomDep codJefefe lugaresDep 5 Ingeniera 400 Tacna 5 Ingeniera 400 Cuzco 4 Administracin 200 Trujillo El atributo multivaluado obliga a replicar la informacin del departamento, generando redundancia. Esta solucin ya esta en 1FN , pero no es aceptable porque genera anomalas de actualizacin.Para este ejemplo el esquema departamento no esta en 1FNGrupo repetitivo6LA SOLUCION :Es eliminar de la relacin departamento el atributo lugaresDep ( grupo repetitivo) que viola la 1FN y colocarlo en otra relacin aparte que llamaramos lugares_depa . Esta nueva relacin tendra como atributos : la clave primaria de la relacin departamento ( para asegurar el vnculo entre estas dos relaciones ) y lugarDep ( contiene los nombres de los lugares )La clave primaria de esta nueva relacin es la combinacin :{ numDep , lugarDep }DEPARTAMENTOnumDep nomDep codJefefe numDep lugarDepLUGARES_DEPAnumDep nomDep codJefefe 5 Ingeniera 400 4 Administracin 200DEPARTAMENTOnumDep lugarDep 4 Trujillo 5 Tacna 5 CuzcoLUGARES_DEPA7EJEMPLO :OBREROcodObr nom fechNac direc jornal codJefe oficio aosExpSe tiene el esquema OBRERO. Cada obrero puede tener mas de un oficio, as, el obrero Jorge Huaman es carpintero, albail y pintor y posee 3, 8 y 2 aos de experiencia en esos oficios.Esto quiere decir que los atributos oficio y aosExp forman una unidad multivaluada, con lo que se esta violando la 1FNDicho de otro modo {oficio, aosExp} ocurren varias veces en una tupla. De esta forma la tupla ya no es plana. Grupo repetitivoOBREROcodObr nom fechNac direc jornal codJefe oficio aosExp 300 Huaman Jorge 10-05-67 Surco 25 800 carpintero 3 300 Huaman Jorge 10-05-67 Surco 25 800 albail 3 300 Huaman Jorge 10-05-67 Surco 25 800 pintor 2 350 Sulca Amrico 22-11-70 Comas 30 900 electrnico 58SOLUCION :Es eliminar el grupo repetitivo {oficio, aosExp} de la relacin obrero, que viola la 1FN y colocarlo en otra relacin aparte que llamaramos habilidades . Esta nueva relacin tendra como atributos : la clave primaria codObr de la relacin obrero ( para asegurar el vnculo entre estas dos relaciones ) , adems de oficio y aosExp.La clave primaria de la nueva relacin habilidades estara conformada por :{ codObr , oficio }OBREROcodObr nom fechNac direc jornal codJefecodObr oficio aosExpHABILIDADES9DEPENDENCIAS FUNCIONALESSea el esquema de relacin R :R ( A1 , A2 , A3 , . . . Ak , Ak+1 , . . . Am , Am+1 , . . . At , At+1 , . . . An-1 , An )xyY los subconjuntos Se dice que Y depende funcionalmente de X , que X determina YxySi y solo si , cada valor de X tiene asociado en todo momento un nico valor de Y que X implica Yimplicanteimplicado10EJEMPLO : Tenemos la relacin :LIBRO ( cod_Lib, titulo, editorial )Se puede decir que el cdigo de un libro determina su ttulo.GRAFICAMENTE : Podemos mostrar grficamente la dependencia funcional :Cod_LibCod_LectorTitulo , editorialFech_prestamo , fecha_devolnombre , direc , fonodeterminadeterminadeterminaAqu se dice que titulo depende funcionalmente de codLibEste concepto de dependencia funcional tambin nos dice que el titulo es una informacin acerca del libro o tambin que para algn cdigo de libro existe un nico ttulo que le corresponde.11EJEMPLO : Veamos las DF en el siguiente esquema de relacin :codEmp numProy nomEmp nomProy lugarProyEMP_PROYcodEmpnomEmpnumProy{ nomProy , lugarProy }Se lee : EMP_PROY ( codEmp, numProy, nomEmp, nomProy, lugarProy )El valor del nmero de proyecto ( numProy ) determina de manera nica el nombre del proyecto ( nomProy ) y su lugar ( lugarProy ). El valor de cdigo del empleado ( codEmp ) determina de manera nica el nombre de ese empleado ( nomEmp ). Para un codEmp existe un nico nombre de empleado.12EJEMPLO : Veamos las DF en este otro esquema de relacin :codEmp nomEmp fechNac direc numDep nomDep codJefeEMP_DEPTOcodEmp{ nomEmp , fechNac , direc }numDep{ nomDep , codJefe }EMP_DEPTO ( codEmp, nomEmp, fechNac, direc, numDep, nomDep. codJefe )13EJEMPLO : Veamos cuando no existe dependencia funcional :PROFESOR CURSO TEXTOBASECanales Julio Algoritmos II Cairo Canales Julio Base de Datos KorthArias Freud Visual Basic Joyanes Pacheco Rosa Leng. Prog I Vasquez DICTARSe puede decir que profesor determina funcionalmente curso ?RESPUESTA : El hecho de que Canales julio dicta Algoritmos II as como Base de Datos nos lleva a concluir que profesor no determina funcionalmente curso14EJEMPLO : Veamos cuando no existe dependencia funcional :Cdigo de empleado (codEmp) no es funcionalmente dependiente de sueldo, porque mas de un empleado podra tener el mismo sueldocodEmp numProy nomEmp sueldo nomProy fechFinEMPLE-PROYcodEmp no es funcionalmente dependiente de numProy, ya que mas de un empleado puede trabajar para el mismo proyectoEMP_PROY ( codEmp, numProy, nomEmp, sueldo, nomProy, fechFin )Pero fecha de fin de proyecto ( fechFin) si es funcionalmente dependiente de nmero de proyecto (numProy)15DEPENDENCIA FUNCIONAL COMPLETAxyUna simple dependencia funcional se define as : Si X es un subconjunto de dos atributos :X ( X1 , X2 )Se dice que Y tiene dependencia funcional completa de X si depende funcionalmente de X pero no depende de X1 ni de X2 , esto es : xyx1yx2yLo que se representa como : xyConocida tambin como TOTAL16EJEMPLO : Veamos las DF en el siguiente esquema de relacin :codEmp numProy horas nomEmp nomProy lugarProyEMP_PROYcodEmpnomEmpnumProy{ nomProy , lugarProy }La combinacin de valores de ( codEmp ) y ( numProy ) determinan de manera nica el nmero de horas ( horas ) que el empleado trabaja en un proyecto cada semana. Ni cdigo de empleado, ni tampoco nmero de proyecto, determinan por si solos, las horas trabajadas, ya que un empleado puede tener diferentes horas trabajadas en diferentes proyectos, asi como un proyecto tiene diversas horas de trabajo de diferentes empleados. Por tanto, respecto al atributo HORAS tenemos una dependencia funcional completa :{ codEmp , numProy }horasDependencia funcional completaDependencia funcional parcialDependencias funcionales parciales17EJEMPLO : Analizemos el siguiente esquema de relacin :codLib codLector titulo editorial nombre direc fono fechPrestPRESTARDado un cdigo de libro ( codLib ) y un cdigo de lector ( codLector ) existe una nica fecha de prstamo ( fechPrest ) para ese libro. Ni cdigo de libro, ni tampoco cdigo de lector, determinan por si solos, la fecha de prstamo, ya que un libro se puede prestar en diversas fechas, asi como un lector puede recibir libros prestados en diferentes fechas. Por tanto, tenemos una dependencia funcional completa :{ codLib , codLector }fechPrestPRESTAR ( codLib, codLector, titulo, editorial, nombre, direc, fono, fechPrest )Dependencia funcional completa18EJEMPLO : Indique grficamente las dependencias funcionales del siguiente esquema de relacin :PROGRAM(codProgramador, codModulo, nomProgramador, nomModulo, horasTrab )RESPUESTA : codProgramador codModulo nomProgramador nomModulo horasTrabPROGRAMDependencia funcional completaDependencia funcional parcialDependencia funcional parcial19Atributo PrimoEs aquel atributo que forma parte de cualquier clave primaria o es clave candidata de una relacin.EMP_PROYAtributos NO PRIMOSAtributo PRIMOAtributo PRIMOcodEmp numProy horas nomEmp nomProy lugarProyClave primaria20SEGUNDA FORMA NORMAL2FN((Un esquema de relacin est en segunda forma normal , si est en primera forma normal y todo atributo no primo posee una dependencia funcional completa de la clave primaria de la relacin.EJEMPLO : El siguiente esquema representa un mal diseo, el cual si bien no tiene atributos compuestos ni grupos repetitivos y por tanto esta en 1FN , sin embargo no esta en 2FN.El atributo no primo nomEmp viola la 2FN debido a que su dependencia funcional de la clave primaria es solo parcial. Es decir, depende funcionalmente solo parcialmente de la clave primaria ( solo de codEmp ).codEmp numProy horas nomEmp nomProy lugarProyEMP_PROYClave primariaDependencia funcional parcialnomEmpNOTA : el nico atributo no primo que tiene dependencia funcional completa es horas21El atributo no primo nomProy viola la 2FN debido a que su dependencia funcional de la clave primaria es solo parcial. Es decir, depende funcionalmente solo parcialmente de la clave primaria ( solo de numProy ).codEmp numProy horas nomEmp nomProy lugarProyEMP_PROYClave primariaDependencia funcional parcialnomProyEl atributo no primo lugarProy viola la 2FN debido a que su dependencia funcional de la clave primaria es solo parcial. Es decir, depende funcionalmente solo parcialmente de la clave primaria ( solo de numProy ).codEmp numProy horas nomEmp nomProy lugarProyEMP_PROYClave primariaDependencia funcional parciallugarProycodEmp numProy horas nomEmp nomProy lugarProyEMP_PROYDependencias funcionales parcialeslugarProynomProyESTO SE ACOSTUMBRA GRAFICAR ASI :22SOLUCION : Como el esquema de relacin no esta en 2FN, debemos normalizar a varias relaciones en 2FN en las que los atributos no primos originales presenten una dependencia funcional total respecto a las nuevas claves primarias formadas :EMP_PROYsolucincodEmp numProy horas nomEmp nomProy lugarProyIdentificadas las dependencias, quedan definidas las nuevas relacionescodEmp numProy horasHORAS_TRABcodEmp nomEmp EMPLEnumProy nomProy lugarProyPROYEC23EJERCICIO : Una empresa comercializadora posee varias sucursales en diversas ciudades del pas. Donde cada sucursal es identificada por su cdigo de sucursal.

Cada sucursal tiene su staff de empleados, a los cuales se les reconoce por un cdigo de empleado en la sucursal , el cual siempre empieza con el nmero 100.Lo que significa que para distinguir a un empleado de otro es necesario conocer el cdigo de la sucursal y el cdigo que el empleado tenga en la sucursal. Es importante registrar el DNI , la hora de ingreso al trabajo y el nombre de la sucursal.codigoDeSucursal codigoEnSucursal DNI nom sueldo horaIngre nomSucursalEMPLEADOAtributo primoAtributos no primosClave primariaEntonces se pide normalizar el siguiente esquema de relacin :( Clave candidata )24Aqu de hecho estn combinados datos de sucursal y datos de empleado. Descartando el atributo primo DNI, por que presenta la propiedad de unicidad ( clave candidata ) , nos centraremos en los atributos no primos : sueldo, horaIngre y nomSucursal.SOLUCION : Datos de sucursal : horaIngre , nomSucursalDatos de empleado : DNI , sueldo1. Construyendo esquema para la sucursal :codigoDeSucursal horaIngre nomSucursalsucursalYa esta en 1FN ( no hay grupos repetitivos ) y esta en 2FN ya que no hay clave mltiple. Por tanto horaIngre y nomSucursal dependen totalmente de la clave primaria25El sueldo de un empleado no depende nicamente del cdigo del empleado en la sucursal, porque como ya sabemos estos cdigos se repiten en cada sucursal. Por tanto :2. Construyendo esquema para el empleado :{codigoDeSucursal , codigoEnSucursal} sueldoEn consecuencia :Ya esta en 1FN y en 2FNcodigoDeSucursal codigoEnSucursal DNI sueldoEMPLEADOEMPLEADOcodigoDeSucursal horaIngre nomSucursalSUCURSALcodigoDeSucursal codigoEnSucursal DNI sueldo26Dependencia TransitivaSuponga que se tiene la siguiente relacin R y sus atributos :ABCRDe donde se puede interpretar :C es funcionalmente dependiente de B y B es funcionalmente dependiente de A , entonces :ABCRC es funcionalmente dependiente de ATenemos as una dependencia funcional transitiva27De una manera mas formal :Sea la relacin : R ( A , B , C )Donde se presentan las siguientes dependencias funcionales :A BB CB AEntonces podemos decir que C depende transitivamente de AEsto se representa as : A C28Grficamente :ABC29Ejercicio :Se sabe que los libros para ser comercializados estn codificados con un nmero nico conocido como ISBN. Cada cdigo corresponde a alguna Editorial. Se entiende que cada Editorial tiene un nico pas de procedencia. Esta situacin puede expresarse en el siguiente esquema :LIBRO ( Cdigo, Editorial, Pas )Aqu se verifican las siguientes dependencias :El cdigo determina una nica editorialLa editorial determina un nico pas de orgenUna editorial no determina un nico libro, ya que puede publicar muchos libros Y adems se cumple que :PasCdigoEditorial30EMPLEPROY ( CodEmp, nomEmp, sueldo, numProy, fechaFin )El cdigo del empleado determina un nico proyecto y un proyecto tiene una nica fecha de finalizacinEjercicio :En una empresa laboran empleados debidamente codificados. Los empleados tiene un sueldo bsico de 2000 y trabajan para un solo proyecto que presenta una fecha de finalizacin y se identifica por su nmero. Se tiene entonces el siguiente esquema, identifique si existe alguna dependencia transitiva :EMPLEPROY ( CodEmp, nomEmp, sueldo, numProy, fecha Fin )Solucin :No es cierto que en un proyecto trabaje un nico empleadomas bien, en un proyecto trabajan muchos empleados31EMPLEPROY ( CodEmp, nomEmp, sueldo, numProy, fechaFin )Entonces se tiene una dependencia transitiva CodEmp fechaFina travs de numProyfechaFinCodEmpnumProy32TERCERA FORMA NORMAL3FN((Un esquema de relacin esta en 3FN, si esta en 2FN ( no hay dependencias parciales) y ningn atributo NO primo depende transitivamente de la clave primaria.Ejemplo :Normalizar el siguiente esquema de relacin :CodEmp nomEmp sueldo numProy fechaFinEMPLEPROYdependencia funcional transitivaEsta en 1FN por que no hay grupos repetitivosEsta en 2FN pues no existe clave compuestaPero la dependencia transitiva viola la 3FN33La solucin es desdoblar empleado y proyecto como esquemas separados, de esta forma estaramos rompiendo la transitividad :codEmp nomEmp sueldo nProyEMPLEADOnumProy fechaFinPROYECTOAs, ya esta en 1FN por que no hay grupos repetitivos, en 2FN porque no existen dependencias parciales sobre claves compuestas, y en 3FN por que no existen transitividades.34Ejercicio :En una empresa un empleado trabaja para un solo departamento. Vea entonces el siguiente esquema de relacin y proceda a normalizarlo :CodEmp nomEmp direc numDep nomDep codJef EMP_DEPdependencia funcional transitivaEsta en 1FN por que no hay grupos repetitivosEsta en 2FN pues no existe clave compuestaPero las dependencias transitivas violan la 3FN35La solucin es desdoblar empleado y departamento como esquemas separados, de esta forma estaramos rompiendo la transitividad presentada :codEmp nomEmp direc nDepEMPLEADOnumDep nomDep codJefDEPARTAMENTOAs, ya esta en 1FN por que no hay grupos repetitivos, en 2FN porque no existen dependencias parciales sobre claves compuestas, y en 3FN por que no existen transitividades.36Resumen de ConversionesConversin a PRIMERA FORMA NORMALA B C D E F GA B GA C D AACECFDEFA C D ACEFConversin a SEGUNDA FORMA NORMALA B DACA E FAConversin a TERCERA FORMA NORMALA E FAA E AE FE1FN por que no hay grupos repetitivos,. 2FN porque no existen dependencias parciales sobre claves compuestas, y 3FN por que no existen transitividades.37