Curso SQL Nivel Avanzado 1

Miguel Jurado García

© Copyright Amaris 2006

Temario Sesión 3: .

Diagramas E-R Normalización y Generalización de B.D. Creación y configuración de Esquemas Creación de un snapshot de la Base de Datos Uso de snapshot para restauración

© Copyright Amaris 2006

MODELO E-R Permite representar las entidades relevantes de un sistema de información así como sus interrelaciones y propiedades. Entidad: Representa una “cosa” u "objeto" del mundo real con existencia independiente. Atributo: Características que definen o identifican a una entidad Relación: Describe cierta dependencia entre entidades o permite la asociación de las mismas. Las entidades suelen acabar siendo una o varias tablas, los atributos campos de dichas tablas y las relaciones suelen ser o tablas de look up o claves.

© Copyright Amaris 2006

© Copyright Amaris 2006

Bases de Datos Normalizadas El proceso de normalización de Bases de datos consiste en aplicar una serie de reglas a las relaciones obtenidas tras el paso del modelo entidad relación al modelo relacional. Las bases de datos relacionales se normalizan para: Evitar la redundancia de los datos. Evitar problemas de actualización de los datos en las tablas. Proteger la integridad de los datos. En el modelo relacional es frecuente llamar Tabla a una relación, aunque para que una tabla sea considerada como una relación tiene que cumplir con algunas restricciones: Cada columna debe tener su nombre único. No puede haber dos filas iguales. No se permiten los duplicados. Todos los datos en una columna deben ser del mismo tipo. Ing. Miguel Jurado © Copyright Amaris 2006

Terminología relacional equivalente

Figura 1.0: Trabajo (Código, Nombre, Posición, Salario), donde Código es la Clave Primaria •Relación = tabla o archivo •Tupla = registro, fila o renglón •Atributo = campo o columna •Clave = llave o código de identificación •Clave Primaria = superclave •Clave Ajena = clave externa o clave foránea •Clave Alternativa = clave secundaria •Dependencia Multivaluada = dependencia multivalor •RDBMS = Del inglés Relational Data Base Manager System que significa, Sistema Gestor de Bases de Datos Relacionales.

© Copyright Amaris 2006

Dependencia funcional

B es funcionalmente dependiente de A. Una dependencia funcional es una conexión entre uno o más atributos. Por ejemplo si conocemos el valor de FechaDeNacimiento podemos conocer el valor de Edad. Las dependencias funcionales del sistema se escriben utilizando una flecha, de la siguiente manera: FechaDeNacimiento -> Edad Aquí a FechaDeNacimiento se le conoce como un determinante. Se puede leer de dos formas FechaDeNacimiento determina a Edad o Edad es funcionalmente dependiente de FechaDeNacimiento. De la normalización (lógica) a la implementación (física o real) puede ser sugerible tener éstas dependencias funcionales para lograr mayor eficiencia en las tablas.

© Copyright Amaris 2006

Dependencia funcional transitiva

Dependencia funcional transitiva. FechaDeNacimiento -> Edad Edad -> Conducir FechaDeNacimiento -> Edad -> Conducir Entonces tenemos que FechaDeNacimiento determina a Edad y la Edad determina a Conducir, indirectamente podemos saber a través de FechaDeNacimiento a Conducir (En muchos paises , para una persona poder conducir un automovil la persona necesita ser mayor de X edad, por eso se utiliza este ejemplo).

© Copyright Amaris 2006

Claves Una clave primaria es aquella columna (pueden ser también dos columnas o más) que identifica únicamente a esa fila. La clave primaria es un identificador que va a ser único para cada fila. Se acostumbra poner la clave primaria como la primera columna de la tabla pero esto no tiene que ser necesario, si no es más una conveniencia. Muchas veces la clave primaria es autonumérica. En una tabla puede que tengamos más de una clave, en tal caso se puede escoger una para ser la clave primaria, las demas claves son las claves candidatas. Una clave foránea es aquella columna que existiendo como dependiente en una tabla, es a su vez clave primaria en otra tabla. Una clave alternativa es aquella clave candidata que no ha sido seleccionada como clave primaria, pero que también puede identificar de forma unica a una fila dentro de una tabla. Una clave compuesta es una clave que está compuesta por más de una columna.

© Copyright Amaris 2006

Formas Normales Las formas normales son aplicadas a las tablas de una base de datos, decir que una base de datos está en la forma normal N es decir que todas sus tablas están en la forma normal N. En general, las primeras tres formas normales son suficientes para cubrir las necesidades de la mayoría de las bases de datos. El creador de estas 3 primeras formas normales (o reglas) fue Codd . Primera Forma Normal (1FN) Una tabla está en Primera Forma Normal sólo si Todos los atributos son atómicos. Un atributo es atómico si los elementos del dominio son indivisibles, mínimos. La tabla contiene una clave primaria La tabla no contiene atributos nulos Una columna no puede tener múltiples valores. Los datos son atómicos. (Si a cada valor de X le pertenece un valor de Y, entonces a cada valor de Y le pertenece un valor de X)....

© Copyright Amaris 2006

Un diseñador principiante desea guardar los nombres y los números telefónicos de los clientes. Procede a definir una tabla de cliente como la que sigue:

ID Cliente

Nombre

Apellido

Teléfono

123

Rachel Ingram

555-861-2025

456

James Wright

555-403-1659

789

Maria

Fernandez 555-808-9633

En este punto, el diseñador se da cuenta de un requisito debe guardar múltiples números teléfonicos para algunos clientes. Razona que la manera más simple de hacer esto es permitir que el campo "Teléfono" contenga más de un valor en cualquier registro dado: ID Cliente

Nombre

Apellido

Teléfono

123

Rachel Ingram

555-861-2025

456

James Wright

555-403-1659 555-776-4100

789

Maria

Fernandez 555-808-9633

La 1NF (y, para esa materia, el RDBMS) prohíbe a un campo contener más de un valor de su dominio de columna.

© Copyright Amaris 2006

Ejemplo 2: Grupos repetidos a través de columnas El diseñador puede evitar esta restricción definiendo múltiples columnas del número telefónico: ID Cliente

Nombre

Apellido

Teléfono 1

Teléfono 2

123

Osvaldo Ingram

555-861-2025

456

James

Wright

555-403-1659 555-776-4100

789

Maria

Fernandez 555-808-9633

Teléfono 3

Sin embargo, esta representación hace uso de columnas que permiten valores nulos, y por lo tanto no se conforman con la definición de la 1NF de Date. Incluso si se contempla la posibilidad de columnas con valores nulos, el diseño no está en armonía con el espíritu de 1NF.

© Copyright Amaris 2006

Diseño conforme con 1NF Un diseño que está inequívocamente en 1NF hace uso de dos tablas: una tabla de cliente y una tabla de teléfono del cliente.

ID Cliente

Nombre

Apellido

Rachel Ingram

456

James Wright

789

Maria

Fernandez

Cliente 1aN

123

Teléfono del cliente ID Cliente

Teléfono

123

555-861-2025

456

555-403-1659

456

555-776-4100

789

555-808-9633

En este diseño no ocurren grupos repetidos de números telefónicos. En lugar de eso, cada enlace Cliente-a-Teléfono aparece en su propio registro. © Copyright Amaris 2006

Segunda Forma Normal (2FN)

Dependencia completa. Una relación está en 2FN si está en 1FN y si los atributos que no forman parte de ninguna clave dependen de forma completa de la clave principal. Es decir que no existen dependencias parciales. Los atributos dependen de la clave. Varía la clave y varían los atributos. En otras palabras podríamos decir que la segunda forma normal está basada en el concepto de dependencia completamente funcional. Una dependencia funcional X -> Y es completamente funcional si al eliminar los atributos A de X significa que la dependencia no es mantenida, esto es que A Є X, (X – {A}) -x-> Y. Una dependencia funcional X-> Y es una dependencia parcial si hay algunos atributos A Є X que pueden ser removidos de X y la dependencia todavía se mantiene, esto es A Є X, (X – {A}) -> Y . Por ejemplo {SSN,PNUMBER} -> HOURS es completamente dependencia dado que ni SSN -> HOURS ni PNUMBER -> HOURS mantienen la dependencia. Sin embargo {SSN,PNUMBER} -> ENAME es parcialmente dependiente dado que SSN->ENAME mantiene la dependencia

© Copyright Amaris 2006

Considere una tabla describiendo las habilidades de los empleados:

El atributo restante, Lugar actual de trabajo, es dependiente en solo parte de la clave candidato, llamada Empleado. Por lo tanto la tabla no está en 2NF. Observe la redundancia de la manera en que son representadas los Lugares actuales de trabajo: nos dicen tres veces que Jones trabaja en la 114 Main Street, y dos veces que Ellis trabaja en 73 Industrial Way. Esta redundancia hace a la tabla vulnerable anomalías de actualización © Copyright Amaris 2006

Un alternativa 2NF a este diseño representaría la misma información en dos tablas:

Habilidades

© Copyright Amaris 2006

Tercera Forma Normal (3FN)

La tabla se encuentra en 3FN si es 2FN y cada atributo que no forma parte de ninguna clave, depende directamente y no transitivamente, de la clave primaria. Un ejemplo de este concepto sería que, una dependencia funcional X->Y en un esquema de relación R es una dependencia transitiva si hay un conjunto de atributos Z que no es un subconjunto de alguna clave de R, donde se mantiene X->Z y Z->Y.. Por ejemplo, la dependencia SSN->DMGRSSN es una dependencia transitiva en EMP_DEPT de la siguiente figura. Decimos que la dependencia de DMGRSSN el atributo clave SSN es transitiva via DNUMBER porque las dependencias SSN->DNUMBER y DNUMBER->DMGRSSN son mantenidas, y DNUMBER no es un subconjunto de la clave de EMP_DEPT. Intuitivamente, podemos ver que la dependencia de DMGRSSN sobre DNUMBER es indeseable en EMP_DEPT dado que DNUMBER no es una clave de EMP_DEPT.

© Copyright Amaris 2006

Un ejemplo de una tabla 2NF que falla en satisfacer los requisitos de la 3NF es:

La única clave candidato es {Torneo, Año}. La violación de la 3NF ocurre porque el atributo no primario Fecha de nacimiento del ganador es dependiente transitivamente de {Torneo, Año} vía el atributo no primario Ganador. El hecho de que la Fecha de nacimiento del ganador es funcionalmente dependiente en el Ganador hace la tabla vulnerable a inconsistencias lógicas, pues no hay nada que impida a la misma persona ser mostrada con diferentes fechas de nacimiento en diversos registros.

© Copyright Amaris 2006

Para expresar los mismos hechos sin violar la 3NF, es necesario dividir la tabla en dos:

© Copyright Amaris 2006

Los diagramas o modelos entidad-relación (a veces denominado por su siglas, E-R "Entity relationship") son una herramienta para el modelado de datos de un sistema de información. Estos modelos expresan entidades relevantes para un sistema de información, sus inter-relaciones y propiedades

© Copyright Amaris 2006

Entidades Una entidad es cualquier "objeto" discreto sobre el que se tiene información. Se representa mediante un rectángulo o "caja" etiquetada en su interior mediante un nombre. Ejemplos de entidades habituales en los sistemas de información son: factura, persona, empleado, etc. Relaciones Una relación describe cierta interdependencia (de cualquier tipo) entre entidades. Se representa mediante un rombo etiquetado en su interior mediante un verbo. Además, dicho rombo debe unirse mediante líneas con las entidades que relaciona (es decir, los rectángulos). Una relación no tiene sentido sin las entidades que relaciona. Por ejemplo: una persona (entidad) trabaja (relación) para un departamento (entidad).

Atributos Los atributos son propiedades relevantes propias de una entidad y/o relación. Se representan mediante un círculo o elipse etiquetado mediante un nombre en su interior. Cuando un atributo es identificativo de la entidad se suele subrayar dicha etiqueta. Por motivos de legibilidad, los atributos no suelen representarse en un diagrama entidadrelación, sino que se describen textualmente en otros documentos adjuntos. Los atributos describen información útil sobre las entidades. En particular, los atributos identificativos son aquellos que permiten diferenciar a una instancia de la entidad de otra distinta. Por ejemplo, el atributo identificativo que distingue a un empleado de otro es su número de seguridad social. © Copyright Amaris 2006

Entidades fuertes y débiles Cuando una entidad participa en una relación puede adquirir un papel fuerte o débil. Una entidad débil es aquella que no puede existir sin participar en la relación, es decir, aquella que no puede ser unívocamente identificada solamente por sus atributos. Una entidad fuerte es aquella que sí puede ser identificada unívocamente. En los casos en que se requiera, se puede dar que una entidad fuerte "preste" algunos de sus atributos a una entidad débil para que, esta ultima, se pueda identificar. Las entidades débiles se representan mediante un doble rectángulo, es decir, un rectángulo con doble línea.

© Copyright Amaris 2006

Cardinalidad de las relaciones Las relaciones, en principio binarias, pueden involucrar a un número distinto de instancias de cada entidad. Así, son posibles tres tipos de cardinalidades: Relaciones de uno a uno: una instancia de la entidad A se relaciona con una y solamente una de la entidad B. Relaciones de uno a muchos: cada instancia de la entidad A se relaciona con varias instancias de la entidad B. Relaciones de muchos a muchos: cualquier instancia de la entidad A se relaciona con cualquier instancia de la entidad B. El tipo de cardinalidad se representa mediante una etiqueta en el exterior de la relación, respectivamente: "1:1", "1:N" y "N:M", aunque la notación depende del lenguaje utilizado, la que más se usa actualmente es el unificado. Otra forma de expresar la cardinalidad es situando un símbolo cerca de la línea que conecta una entidad con una relación: "0" si la entidad no está obligada a participar en la relación. "1" si la entidad está obligada a participar en la relación y, además, cada instancia solamente participa una vez. "N" , "M", ó "*" si la entidad no está obligada a participar en la relación y cada instancia puede participar cualquier número de veces. © Copyright Amaris 2006

Ejercicio:

Crear un diagrama E/R de la base de datos del curso integrando todos los elementos que consideréis necesarios. Verificar que la base de datos está normalizada (3FN)

Implementar la nueva estructura y migrar la información de las tablas originales a las nuevas

© Copyright Amaris 2006

Creación de Esquemas Definición de Esquema Funcionamiento del Object Name Resolution

© Copyright Amaris 2006

Definición de Esquema Namespaces para Objetos de Base de datos Person Contact (Server1.AdventureWorks.Person.Co ntact)

Sales

Customer (Server1.AdventureWorks.Sales.Cust omer)

dbo

ErrorLog (Server1.AdventureWorks.dbo.ErrorL og)

AdventureWork s

© Copyright Amaris 2006

Los esquemas estan separados de su creador lo que ofrece los siguientes beneficios: Mayor Flexibilidad organizando los objetos de la BD en namespaces, porque agrupa estos objetos y los hace no dependientes del creador Simplifica la administración de permisos, ya que podemos dar permisos a nivel de esquema en lugar de a cada objeto individualmente. Mayor manejabilidad ya que al droppear un usuario no es necesario eliminar todos los objetos que creó.

CREATE SCHEMA nombre_esquema

© Copyright Amaris 2006

Cómo funciona el Resolutor de Nombres de Objetos Person SELECT * FROM Contact

Lance (Default schema = Person)

SELECT * FROM Person.Contact

Contact Sales

SELECT * FROM ErrorLog SELECT * FROM Contact

Anders (Default schema = Sales)

dbo

ErrorLog

© Copyright Amaris 2006

Creación de Snapshots de BD. Definición de Snapshots de BD. Funcionamiento de los Snapshots

© Copyright Amaris 2006

Definición de Snapshots Read-only, vistas consistentes de una BD. en un momento concreto de tiempo Muy potentes como test o como BD. de desarrollo o para el reporting Debe existir en el mismo server que la fuente de Base de datos

12:00 Snapshot

© Copyright Amaris 2006

Un snapshot es una vista estática de sólo lectura de una BD. en un específico momento en el tiempo, que no cambia tras la creación del snapshot. La BD. desde la que se crea el snapshot es referenciada como Base de Datos fuente.

Los snapshots pueden ser usados cómo punto rápido de restauración en caso de pérdida de datos accidental o maliciosa en la BD. No obstante jamás deben suplir la política de backups ya que un snapshot no contiene la información de todos los registros de la Base de Datos.

© Copyright Amaris 2006

Los snapshots de BD no pueden ser creados en las bases de datos model, master, o tempdb. Un Database snapshot no pueden tener su back up ni ser restaurado. Un Database snapshot no pueden ser attached ni detached. Los Database snapshots no pueden ser creados en particiones FAT32 ni RAW. Todo snapshot creado sobre una base de datos debe ser eliminado previo a la eliminación de la base de datos en sí. SQL Server Management Studio no soporta la creación de snapshots visualmente, con lo que deben ser creados únicamente usando Transact-SQL.

© Copyright Amaris 2006

Funcionamiento de los Snapshots Source DB

Snapshot DB

SELECT …

UPDATE …

Copy-On-Write

SELECT …

© Copyright Amaris 2006

Código Creación Snapshot CREATE DATABASE AW_Snapshot1 ON ( NAME = 'Adventureworks1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AW1.ss'), ( NAME = N'Adeventureworks2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AW2.ss') AS SNAPSHOT OF Adventureworks

Nota: Importante no dejarse ningún archivo si la BD. está particionada en varios ficheros.

© Copyright Amaris 2006