crear y administrar bases de datos - cb06tic - homey+administrar+bd.pdf · guía de estudio 1 a...

Guía 2011-B

Crear y administrar bases de datos

Quinto Semestre

Guía de Estudio

1

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

BLOQUE I. Diseñar la base de datos relacional para cubrir las necesidades y

requerimientos de una organización.

1.1Base de datos

Es un conjunto de información organizada de tal

manera que sea rápido realizar consultas, borrar,

guardar nuevos datos. Esta información es almacenada

en dispositivos electrónicos y gestionada por diversos

aplicativos.

Las bases de datos deben de cubrir las siguientes características:

Versatilidad para la representación de la información

Desempeño

Mínima redundancia de datos

Acceso a través de lenguajes de programación estándar

Simplicidad

Integridad de datos

Seguridad y privacidad

Independencia física y lógica

Acceso concurrente por parte de

múltiples usuarios

BASE DE DATOS

http://www.maestrosdelweb.com/principiantes/los-diferentes-lenguajes-de-programacion-para-la-web/


2

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

1.2 Modelo Entidad Relación (MER)1

El Modelo de Entidad Relación es un modelo de datos basado en una percepción

del mundo real que consiste en un conjunto de objetos básicos llamados entidades

y relaciones, implementándose en forma gráfica a través del Diagrama Entidad

Relación.

1 PDF. MER 2007. Storti Guillermo, Ríos Gladys, Campodónico Gabriel.

Se puede definir como

ENTIDAD a cualquier objeto,

real o abstracto, que existe en

un contexto determinado o

puede llegar a existir y del cual

deseamos guardar información.

Se hace referencia a

REGISTROS cuando nos

referimos a una clase de

objetos con características

similares.

Guía de Estudio

3

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Tipos de Relaciones:


4

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Guía de Estudio

5

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia


6

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Solución: Se identifican las posibles ENTIDADES…

Se colocan los ATRIBUTOS de cada entidad:

Por último se establecen las RELACIONES y CARDINALIDAES que existen entre las

entidades correspondientes:

De esta manera se va completando el modelo entidad realción.2

2 Información extraída de http://www.youtube.com/watch?v=HYHLt-NLy6E&feature=related

Guía de Estudio

7

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Llave primaria o clave principal o identificador

Se denomina LLAVE PRINCIPAL O PRIMARIA al atributo o conjunto mínimo de

atributos (uno o más campos) que permiten identificar en forma única cada instancia

de la entidad, es decir, a cada registro de la tabla. Las llaves principales se utilizan

cuando se necesita hacer referencia a registros específicos de una tabla desde otra

tabla. En un principio se puede identificar más de un atributo que cumpla las

condiciones para ser clave, los mismos se denominan llaves candidatas.

Si la llave primaria se determina mediante un solo atributo de la entidad, entonces

se dice que la misma es una Clave simple. En caso de estar conformada por más

de un atributo, la misma se conoce como Clave compuesta.

La LLAVE FORÁNEA (también llamada externa o secundaria) es un atributo que es

Llave primaria en otra entidad con la cual se relaciona.

En el modelo entidad relación algunos autores acostumbran subrayar el nombre del

atributo, otros lo representan rellenando con un color obscuro todo el óvalo, en

este ejemplo se identifica por estar en letras negritas.


8

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

La llave primaria de la entidad Rep_Ventas es Num_Empl, en el caso de la entidad

Productos su llave primaria está compuesta por dos elementos Id_Fabrica y

Id_Producto.

Pasos para la construcción del Diagrama Entidad

Relación:

1.-Identificar las entidades

2.-Determinar las claves primarias

3.- Describir los atributos de las entidades

4.- Establecer las relaciones entre las entidades

5.- Dibujar el modelo de datos

6.- Realizar comprobaciones

Guía de Estudio

9

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

1.3 Modelo de Datos Relacional (MDR) 3

El modelo entidad relación, nos permite identificar de manera clara las relaciones

que existen entre las entidades. Una vez que se tiene el MER es fácil realizar el

modelo de datos relacional, ambos modelos están totalmente asociados, de hecho

el modelo de datos permite a los administradores de base de datos y/o analistas

tener una visión más limpia y rápida de la base de datos con la que se va a

trabajar.

Veamos la similitud de ambos modelos con la tabla siguiente:

Nombre MER Componente en MER Componente en MDR Nombre MDR

Entidad

Tabla

Atributos

Campo

Relaciones

Relación

3 PDF. MER 2007. Storti Guillermo, Ríos Gladys, Campodónico Gabriel.

Alumno

Nombre

Alumno

Alumno

Nombre

Atributos -n

Inscrito


10

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Tipos de Relaciones

Se entiende por RELACIÓN a la asociación entre 2 o más entidades.

A. CLASIFICACION POR CARDINALIDAD

Relación Uno a Uno: Cuando un registro de una tabla sólo

puede estar relacionado con un único registro de la otra

tabla y viceversa.

En este caso la clave foránea se ubica en alguna de las 2

tablas.

Relación Uno a Muchos: Cuando un registro de una tabla

(tabla secundaria) sólo puede estar relacionado con un

único registro de la otra tabla (tabla principal) y un registro

de la tabla principal puede tener más de un registro

relacionado en la tabla secundaria.

En este caso la clave foránea se ubica en la tabla secundaria.

Relación Muchos a Muchos: Cuando un registro de una tabla puede estar

relacionado con más de un registro de la otra tabla y viceversa. En este caso las

dos tablas no pueden estar relacionadas directamente, se tiene que añadir una

tabla entre las dos (Tabla débil o de vinculación) que incluya los pares de valores

relacionados entre sí.

El nombre de tabla débil deviene de que con sus atributos propios no se puede

encontrar la clave, por estar asociada a otra entidad. La clave de esta tabla se

conforma por la unión de los campos claves de las tablas que relaciona.

B. CLASIFICACION POR MODALIDAD

Dadas las tablas A y B, que se encuentran relacionadas:

Si para todo registro de A debe existir siempre al menos un

registro de B asociado, se dice que la relación en sentido

A->B es Obligatoria. Fig. Clasificación

por Modalidad

Fig. Clasificación

por Cardinalidad

Guía de Estudio

11

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Si para todo registro de A, pueden existir o no, uno o varios registros de B

asociados, se dice que la relación en sentido A->B es Optativa. La modalidad de

las relaciones se debe analizar en ambos sentidos.

Fig. Tipos de Relaciones

Ejemplos…

i) Relación uno a uno

_ Todo Departamento tiene solamente un Profesor a cargo

_ Un Profesor puede ser jefe de un solo Departamento


12

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

La relación UNO A UNO se da cuando un registro de una tabla sólo puede

estar relacionado con un único registro de la otra tabla y viceversa. La Clave

Foránea se ubica en alguna de las 2 tablas.

ii) Relación uno a muchos

_ Un Curso tiene muchos Alumnos

_ Un Alumno está en sólo un curso

_ Un Curso puede estar transitoriamente vacío

_ Todo Alumno tiene un Curso asignado

La Relación Uno a Muchos se establece cuando un registro de una tabla (tabla

secundaria) sólo puede estar relacionado con un único registro de la otra tabla

(tabla principal) y un registro de la tabla principal puede tener más de un registro

relacionado en la tabla secundaria. La clave foránea se ubica en la tabla

secundaria.

Guía de Estudio

13

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

iii) Relación muchos a muchos

_ Un Profesor puede estar asignado en varios Cursos

_ Un Curso puede tener muchos Profesores asignados

_ Todo Profesor tiene un Curso asignado

_ Un Curso puede no tener transitoriamente un Profesor

La Relación Muchos a Muchos se da

cuando un registro de una tabla

puede estar relacionado con más de

un registro de la otra tabla y

viceversa. En este caso las dos tablas

no pueden estar relacionadas

directamente, se tiene que añadir una

tabla entre las dos (Tabla débil o de

vinculación) que incluya los pares de

valores relacionados entre sí.

ROMPER CON LA RELACIÓN DE

MUCHOS A MUCHOS


14

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

El nombre de tabla débil deviene de que con sus atributos propios no se puede

encontrar la clave, por estar asociada a otra entidad. La clave de esta tabla se

conforma por la unión de los campos claves de las tablas que relaciona.

1.4 Normalización

Consiste verificar el cumplimiento de ciertas reglas que aseguran la eliminación de

redundancias e inconsistencias.

Esto se hace mediante la aplicación de ciertos procedimientos y en ocasiones se

traduce en la separación de los datos en diferentes relaciones.

• Las relaciones resultantes deben cumplir ciertas características:

• Se debe conservar la información:

– Conservación de los atributos.

– Conservación de las tuplas, evitando la aparición de tuplas que no

estaban en las relaciones originales.

• Se deben conservar las dependencias.

Guía de Estudio

15

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Ejemplo:

Tabla usuarios

Observa que el campo url tiene varios datos, esto es incorrecto, debe tener sólo

valores atómicos. Para ello debemos separar los valores compuestos en los campos

url1 y url2.


16

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Otra forma de modificar los valores compuestos, aplicando la normalización sería la

siguiente:

F1N

F2N

1.-Crear tablas separadas para aquellos grupos de datos que se aplican a varios

registros.

2. Relacionar estas tablas mediante una clave externa.

Guía de Estudio

17

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

¿Pero, qué ocurre cuando queremos añadir otro empleado a la empresa ABC?

¿ó 200 empleados ?

Así que tendremos que aplicar el tercer nivel de F/N

F3N

1. Eliminar aquellos campos que no dependan de la clave.Nuestro nombre de

empresa y su dirección no tienen nada que ver con el campo userId, así que tienen

que tener su propio empresaId:


18

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Nuestras tablas de usuarios y urls pueden crecer todo lo que quieran sin

duplicación ni corrupción de datos.

La mayoría de los desarrolladores dicen que el tercer nivel de F/N es suficiente,

que nuestro esquema de datos puede manejar fácilmente los datos obtenidos de

una cualquier empresa en su totalidad, y en la mayoría de los casos esto será

cierto. Pero echemos un vistazo la tabla urls en el campo url - ¿Ves duplicación de

datos?

Entonces tendríamos que llevar nuestro diseño de BD al siguiente nivel de F/N, el

cuarto, muchos desarrolladores lo pasan por alto porque depende mucho de un tipo

muy específico de relación, la relación 'varios-con-varios', la cual aún no hemos

encontrado en nuestra aplicación.

F4N

Guía de Estudio

19

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

En resumen:

• F1N … valores atómicos

• F2N … depende de forma completa de la llave primaria

• F3N … no depende de forma transitiva de la llave primaria

F4N …. Realización de catálogos

Preguntas de repaso del primer bloque

BASE DE DATOS

1.- ¿Qué es una Base de Datos?

2.- ¿Cuál es su funcionalidad en el mundo actual?

3.-Menciona 5 características de una base de datos

4.- ¿Cuáles son los MODELOS que se utilizan para representar una base de datos?

5.-Describe la FUNCIONALIDAD de cada uno de ellos (diferencias principales)

MER

6.-Describe cada uno de los siguientes componentes: ENTIDAD, ATRIBUTO, RELACION , CARDINALIDAD.

7.- ¿Cuáles son los GRAFOS asociados a los componentes del MER?

8.- ¿Cuál es la principal función del MODELO ENTIDAD RELACIÓN?

MODELO DE DATOS RELACIONAL

9.- ¿Cuáles son los GRAFOS utilizados por esté modelo?

10.- Describe los pasos utilizados para diseñar un MDR en la HERRAMIENTA DE SOFTWARE ( ACCESS).

NORMALIZACION

11.- ¿Para qué sirve la Normalización en una base de datos?

12.- ¿Qué es un VALOR COMPUESTO?

13.- ¿Qué es un VALOR ATOMICO?

14.- Escribe un ejemplo en donde muestres la diferencia entre un valor compuesto y los valores atómicos.

15.- ¿Qué es un catálogo?


20

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

BLOQUE II. Configurar y administrar un sistema gestor de base de datos

relacional considerando los recursos de la organización.

2.1 Sistema gestor de base de datos (SGBD)4

Un SGBD es una colección de numerosas rutinas de software interrelacionadas, está

dedicado a servir de interfaz entre la base de datos, el usuario y las aplicaciones

que la utilizan. Se compone de un lenguaje de definición de datos, de un lenguaje

de manipulación de datos y de un lenguaje de consulta.

+ =

Funciones Principales de un SGBD.

Crear y organizar la Base de datos.

Establecer y mantener las trayectorias de acceso a la base de datos de tal

forma que los datos puedan ser accesados rápidamente.

Manejar los datos de acuerdo a las peticiones de los usuarios.

Registrar el uso de las bases de datos.

Interacción con el gestor de archivos.

Esto a través de las sentencias en DML al comando del sistema de archivos.

Así el manejador de bases de datos es el responsable del verdadero

almacenamiento de los datos.

Respaldo y recuperación de datos.

Control de concurrencia. Consiste en controlar la interacción entre los

usuarios concurrentes para no afectar la inconsistencia de los datos.

Seguridad e integridad. Consiste en contar con mecanismos que permitan el

control de la consistencia de los datos evitando que estos se vean

perjudicados por cambios no autorizados o previstos.

4 Información extraída de: http://www.slideshare.net/kika19/sistemas-manejadores-de-base-de-datos-5072035

BASE DE DATOS

Aplicación

SGBD

Guía de Estudio

21

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

2.1.1 SGBD Free (Código abierto)

Descripción técnica de MySQL

Caracteristicas

•Sus principales características son:

•Posibilidad de crear y configurar usuarios, asignando a estos permisos diferentes.

•Facilidad de exportación e importación de datos, incluso de la base de datos completa.

Aplicaciones

•Es utilizado en aplicaciones web, como Drupal o php, en plataformas (Linux/Windows-apache-MySQL-PHP/Perl/Python), y por herramientas de segimiento de errores comoo Bugzilla.

•Dentro de las aplicaciones web hace que la lectura de datos sea muy rápida.

Desventajas

•Puede provocar problemas de integridad en entornos de alta concurrencia en la modificación.


22

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Descripción técnica



Guía de Estudio

23

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

2.1.2 SGBD Comercial.




24

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

2.2 Diseño físico y diseño lógico

ABSTRACCION DE DATOS -El sistema esconde ciertos detalles de cómo se almacena y mantiene los datos del usuario

Nivel Físico(Interno)

-Define la estructura de la base de datos: Dispositivos de almacenamiento físico, direcciones físicas, estrategias de acceso, relaciones, índices, apuntadores, etc.

Nivel Lógico (Conceptual)

-Describe que datos serán almacenados en la base de datos y las relaciones que existen entre ellos mismos. Definición de datos: Se describe el tipo de datos y la longitud de campo. Relaciones entre datos: Se definen relaciones para enlazar tipos de registros relacionales para el procesamiento de archivos múltiples.

2.2.1 Diseño físico

Nivel Físico está relacionado totalmente con los bytes que va a ocupar nuestra base

de datos en cualesquier dispositivo electrónico donde se va a almacenar, para ello

es importante conocer los tipos de datos que va a requerir nuestra información,

ya que cada dato ocupa diferentes números de bytes, no es igual almacenar sólo

texto a imágenes.

Guía de Estudio

25

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Números exactos bigint

bit Smallint decimal Smallmoney int Tinyint mooney

Números decimales float Real Fecha y hora date Datetimeoffset

datetime2 Smalldatatime Datetime Time


26

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Estimación de tablas y Base de datos en SQL SERVER 5

En cuanto al espacio ocupado por la base de datos, hay que tener presente que los

datos se almacenan en páginas (bloques de 8 Kb de espacio contiguo de

almacenamiento permanente), lo que implica un almacenamiento de 128 páginas por

Megabyte. Hay que tener presente que las filas no pueden abarcar más de una

página, con lo que la máxima información por fila es de 8,060 bytes (quitando el

espacio necesario para la cabecera de fila). En cuanto a los índices, se almacenan

en extensiones de 8 páginas contiguas (64 KB).

A la hora de crear una base de datos es conveniente tener presente algunas

recomendaciones. La mayor parte de las bases de datos funcionarán correctamente

con un solo archivo de datos principal6 y un solo archivo de registro de

transacciones7. Además si utiliza varios archivos, cree un segundo grupo de archivos

para el archivo adicional8 (de este modo, el archivo principal sólo contendrá objetos

y tablas del sistema). Para aumentar al máximo el rendimiento, cree archivos o

grupos de archivos en tantos discos físicos como estén disponibles.

Estimar el tamaño de una tabla.

Toma en cuenta los siguientes pasos:

1.- Se multiplica el número de columnas fijas por la longitud en bytes de cada una.

2.- Las columnas de longitud fija necesitan un espacio adicional para gestionar la

administración de valores NULL que ocupa un espacio igual a la parte entera de 2

+ (número de columnas + 7)/8. A este tamaño es necesario añadir el calculado en

el paso 1.

3.-Si hay columnas de longitud variable en la tabla, se determina el espacio que se

utiliza para almacenar las columnas de la fila mediante la expresión:

2*(número de columnas variables) + tamaño máximo de columna variable + 2

En esta fórmula, se supone que todas las columnas de longitud variable están llenas

al 100%. Si se prevé que va a utilizarse un porcentaje inferior del espacio de

almacenamiento de las columnas de longitud variable, podrá ajustarse el resultado

5 Información extraída de Microsoft SQL Server 2005, ver bibliografía.

6 Archivo con extensión .mdf que contiene información de inicio para la BD y se utiliza para almacenar datos.

7 Archivo con extensión .ldf que contiene la información del registro que se utiliza para recuperar la base de datos.

8 Archivo con extensión.ndf contiene todos los datos que no caben en el archivo principal.

Guía de Estudio

27

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

en función de ese porcentaje para obtener una estimación más precisa del tamaño

de la tabla. A este cálculo se le adiciona los valores obtenidos en los puntos 1 y 2.

4.- Al valor calculado hasta ahora se añaden 4 bytes del tamaño del encabezado y

se obtiene el espacio que ocupa una fila. Si se multiplica este valor por el número

de filas estimado, se obtendrá el número total de bytes.

5.- A continuación se calcula el número de filas por página mediante la expresión:

8096/ (tamaño de fila +2)

Redondeada a la fila completa anterior (ya que las filas no abarcan varias páginas).

No olvidemos que se dispone de 8,096 byte por página.

6.- A continuación se calcula en número de páginas necesarias para almacenar

todas las filas mediante la expresión:

número de filas/( filas por página – filas libres por página)

El número de páginas estimado debe redondearse a la siguiente página completa.

7.- Por último, para calcular la cantidad de espacio necesario para almacenar los

datos en una tabla (8.192 bytes por página) se usa la expresión:

Tamaño de la tabla (bytes) = 8.192 x número de páginas

Es necesario tener en cuenta que cada página ocupa 8,192 bytes aunque sólo tiene

libres 8,096 bytes.

Estimar el tamaño de la base de datos.

Para calcular el espacio total a asignar para una base de datos hay que tener en

cuenta varios factores, entre los que destacan los siguientes:

Tamaño de las tablas del sistema, incluyendo la posibilidad de crecimiento.

Total de datos a almacenar en las tablas, incluyendo la previsión de su

crecimiento.

Los índices9 a utilizar, sobre todo el tamaño del valor de las claves, el

número de filas y el valor del factor de relleno.

El tamaño del registro de transacciones que depende sobre todo de la

frecuencia de las modificaciones, el tamaño de las transacciones y la

9 Permiten realizar búsquedas de manera más rápida.


28

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

frecuencia de las copias de seguridad. Suele asignarse al registro de

transacciones entre el 10 y 25 % del tamaño de la base de datos.

2.2.2 Diseño lógico.

El diseño lógico es la parte que se encarga de la representación lógica general del

problema, desde los modelos utilizados para establecer las relaciones que se van a

dar entre los datos, como los aplicativos que permiten manipular estos datos.

Entre estos modelos tenemos al modelo entidad relación y el modelo de datos

relacionales, una vez que se ha llegado a normalizar el MDR, se debe tomar en

cuenta el DICCIONARIO DE DATOS.

Diccionario de datos.

Está formado por la información cada entidad y sus respectivos atributos, Nombre

de la tabla con el que va hacer identificado en la base de datos, tipo de dato,

longitud del atributo, si el atributo es obligatorio y una breve descripción de este.

En esta parte las entidades se les conoce como tablas, a los atributos como

campos, dada la representación del modelo de datos relacional.

Nombre de la tabla-n

Nombre del

campo

Tipo de dato Longitud Nulo Descripción

Guía de Estudio

29

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Preguntas…

BLOQUE II

1.- ¿Qué es un sistema gestor de base de datos?

2.- Menciona 3 SGBD de software libre y 3 comerciales

3.- ¿Qué SGBD se maneja en la sala de cómputo?

4.- ¿En qué consiste el diseño físico de una BD?

5.- ¿El tipo dato texto, cuántos bytes tiene? ¿Un tipo de dato int, fecha , varchar?

6.- ¿Qué es una página? ¿Cuánto pesa una página?

7.- ¿Qué es un servidor?

8.- ¿Qué es el diseño lógico de una BD?

9.- ¿Cuál es la importancia de tener dentro de los documentos del sistema el diccionario de datos de una BD?


30

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

BLOQUE III. Implantar y administrar la base de datos relacional a partir de

las políticas y procedimientos de la organización.

3.1 SQL

Ejemplo:

Enter password: ********* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database persona; mysql> use persona; Database changed mysql> create table correo (cve_correos int(3), nombre char(10), apaterno char(10), amaterno char(10), correo_electronico varchar(30) ); mysql> alter table persona add primary key (cve_correos); mysql> describe correo; +--------------------+------------------+-------+-----+-----------+---------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+-------+-----+-----------+---------+ | cve_correos | int(3) | NO | PRI | 0 | | | nombre | char(10) | YES | | NULL | | | apaterno | char(10) | YES | | NULL | | | amaterno | char(10) | YES | | NULL | | | correo_electronico | varchar(30) | YES | | NULL| | +--------------------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) mysql> insert into correo values (1, 'Susana', 'Diaz', 'Garza', 'susana@hotmail. com'); Query OK, 1 row affected (0.02 sec)

Guía de Estudio

33

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Realiza una consulta en donde sólo se despliegue los apellidos maternos de

las personas que tienen su clave entre el 5 y 9.

mysql> select amaterno from correo where cve_correos between 5 and 9; +---------------+ | amaterno | +---------------+ | Chavez | | Sanz | | Solis | | Gutierrez | | austria | +-------------- + 5 rows in set (0.00 sec)

Realiza una consulta en donde sólo se despliegue los apellidos paternos de

las personas que sus apellidos paternos terminen con a.

mysql> select apaterno from correo where apaterno like '%a'; +--------------+ | apaterno | +--------------+ | Mendieta | | Huerta | +--------------+

2 rows in set (0.01 sec)

Despliega los apellidos paternos en donde la segunda letra de su apellido sea

a, no importando las demás letras.

mysql> select apaterno from correo where apaterno like '%a'; +-------------+ | apaterno | +-------------+ | Campos | | Casarez | | Martinez | | Palafoz | | Sanchez | +-------------+ 5 rows in set (0.00 sec)


34

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

3.3 Vistas

Una vista es una tabla virtual cuyos contenidos están definidos por una instrucción

select. Las vistas son un poderoso mecanismo de modelado de datos y seguridad.

Las vistas indexadas también pueden proporcionar un beneficio sustancial en el

rendimiento. Las tablas referenciadas por la definición de la vista se conocen como

tablas base.

En el ejemplo que sigue, vistatítulos es una vista que selecciona los datos desde

tres tablas base: título, autortítulo y títulos. Estas tablas sonparte de la base de

datos pubs incluida en SQL Server.

Se puede hacer referencia a la vista vistatitulos en instrucciones de la misma forma

a como se haría con una tabla base:

select * form

vistatitulos;

where precio >= 30;

Create view vistatitulos as

select titulo, au_ord, au_nombre, precio,

ventas, id editorial

from autores as a join autortítulo as at

on(a.au_id = at.au_id)

join titulos as t on (t.titulo_id = at.titulo_id);

Guía de Estudio

35

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

3.4 Seguridad10

Para una introducción a la Seguridad puedes ver el video de “La información y la

seguridad” en: http://www.youtube.com/watch?v=aO7Q2-K-ADo&feature=related.

La seguridad en las bases de datos consiste en tener protección contra: Revelación

de datos no autorizados (confidencialidad), alteración no autorizada (Integridad de

datos), destrucción intencional o involuntaria de datos, virus, troyanos. Ofrece

protección dirigida a los datos (restringiendo el acceso a los datos y ofreciendo

diversos permisos dependiendo del tipo de personal que los va a manejar).

La información que se encuentra en una BD es lo más valioso de toda organización

u empresa, se debe de mantener la confidencialidad, además de vigilar los

aspectos de tipo legal, social y éticos, problemas de sabotaje, errores humanos.

Mecanismos de seguridad en un BD:

Control de accesos.

Control de flujo.

Control de inferencia.

Encriptado de datos.11

Control de Acceso Mandatorio. Clasifica usuarios y datos en múltiples niveles de

seguridad, determinadas reglas acordes a cada nivel.

Control de Acceso Discrecional. Garantiza los privilegios a usuarios, incluyendo la

capacidad para acceder a archivos de datos específicos, registros o campos para

operar de una manera determinada (read, insert, delete o update).

10

http://www.slideshare.net/aefeijo/seguridad-de-base-de-datos 11

Es un método que permite ocultar la información a simple vista, ya sea utilizando una serie de letras y/o números con un orden diferente al normal. La Criptografía utiliza funciones matemáticas para codificar la información, de tal manera que únicamente el receptor y el emisor, conozcan la información original.

http://www.youtube.com/watch?v=aO7Q2-K-ADo&feature=related


36

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Para asignar un usuario a una base de

datos:

GRANT SCHEMA nombreEsqBD AUTHORIZATION usuario;

Con está instrucción se otorgan

privilegios:

GRANT privilegios ON objeto TO usuarios

[WITH GRANT OPTION];

Para quitar permisos ó privilegios:

REVOKE [ GRANT OPTION FOR] privilegios

ON objeto FROM usuarios { CASCADE |

RESTRINCT};

Privilegios que se asignan a un usuario de una base de datos:

Usar una BD.

Consultar ciertos datos.

Actualizar datos.

Crear o actualizar objetos.

Ejecutar procedimientos almacenados.

Referenciar objetos.

Crear identificadores.

Indexar objetos.

Estos privilegios se asignan a través de:

SELECT Para leer todas las columnas (incluyendo las que se añadan con

ALTER TABLE)

DELETE Para remover datos

INSERT (Columna/s) – para incorporar nuevas tuplas con valores no nulos (o

no default) en esa/s columna/s.

INSERT idem Para todas las columnas.

UPDATE Análogo a INSERT para modificar datos existentes.

REFERENCES

(columna)

Para definir: foreign keys.

Sólo el propietario de la BD puede ejecutar CREATE, ALTER y DROP.

Guía de Estudio

37

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

La sentencia WITH GRANT OPTION permite que el poseedor de ciertos privilegios

pueda transmitirlos a otros usuarios.

Usuario -> puede ser un ‘username´ o Public

PUBLIC -> los privilegios se asignan a todos (ej, GRANT SELECT ON listaAlumnos TO

PUBLIC;

Seguimiento del “rastro” (Audit Trail). Si alguien entra en la BD, se puede ver a que

datos accedió y que hizo con ellos.

3.5 Integridad en la Base de Datos.12

Las restricciones de integridad proporcionan un medio de asegurar que las

modificaciones hechas a la base de datos por los usuarios autorizados no

provoquen la pérdida de la consistencia de los datos. Por tanto las restricciones de

integridad protegen a las bases de datos de daños accidentales.

La integridad tiene como función proteger la BD contra operaciones que introduzcan

inconsistencias en los datos. Se habla de integridad en el sentido de corrección,

validez o precisión de los datos.

El subsistema de integridad de un SGBD debe por tanto detectar y corregir, en la

mediad de lo posible, las operaciones incorrecta. En la práctica es el punto débil de

los SGBD comerciales, ya que casi toda la verificación de integridad se realiza

mediante código de procedimientos escritos por los usuarios de BD.

Habrá operaciones cuya falta de corrección no sea detectable, por ejemplo,

introducir una fecha de nacimiento 25/12/1945 cuando en realidad era

25/12/1954. En lo que tiene que ver con la seguridad también se protege los datos

frente al acceso de personas no autorizadas y destrucción o alteración

malintencionada.

12

Información extraída de http://www.slideshare.net/guest92c0d4/analisis-comparativo-my-sql-vs-oracle-2716900


38

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

3.6 Recuperación de la información

El objetivo del concepto de recuperación es proteger la base de datos contra fallas

lógicas o físicas que destruyan los datos en forma total o parcial. Estas fallas

pueden afectar al correcto almacenamiento de los datos.

Para asegurar que la base de datos siempre éste en un estado consistente, cada

base de datos tiene un proceso para obtener copias de seguridad, esto ayuda a

mantener un registro confiable de ellos datos ante desastres o posibles fallas del

sistema.

Por otro lado, las bases de datos crean unidades de ejecución llamadas

transacciones, que pueden definirse como una secuencia de operaciones que se

ejecutan en forma atómica, es decir, se realizan todas las operaciones que

comprende la transacción o no se realiza ninguna.

Las transacciones, o terminan el proceso con éxito y son completadas en la base

de datos, o fracasan y deben ser restaurado el estado anterior de la base de

datos.

La recuperación en frío, consiste en disponer de un backup o respaldo de la BD,

que permitirá junto con los archivos de log. que se han ido produciendo desde el

último backup, reconstruir la BD, para dejarla consistente.

El error fatal, se produce cuando se pierde el archivo de log. En este caso resulta

imposible recuperar la base. La solución pasa por disponer los archivos de log en

respaldos. El Administrador de base de datos, debe definir responsabilidades,

procedimientos, situaciones y plazos en los que se deben realizar las copias de

seguridad y el respaldo del archivo de log especificando a los operadores los

procedimientos de recuperación ante caídas. El principio básico en el que se basa la

recuperación es la redundancia.

En el siguiente video se muestra como hacer un respaldo utilizando dump.

http://www.youtube.com/watch?v=e2SBqi3yw2Y&feature=related

Guía de Estudio

39

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

3.7 Monitoreo de Bases de Datos13

El monitorear es necesario para asegurarse que las bases de datos se desempeñen

eficazmente. Ayuda a prevenir problemas antes de que surjan, además de mostrar el

estado actual de las bases de datos.

Entre las herramientas para monitorear y optimizar consultas y búsquedas,

encontramos MySQL – Proxy. El monitoreo del servidor SQL tiene la capacidad de

conectarse a la fuente de la base de datos y monitorear diversos valores en las

columnas, recolectar datos, así como notificar a través de mensajes de alerta.

Otra herramienta que se encuentra en el mercado es Microsoft SQL Server, permite

editar y ejecutar rápidamente consulta y scripts de SQL. Microsoft SQL Server junto

con el Profiler y el Performance Monitor permiten ver los procesos que se ejecutan

en nuestro servidor, permiten ver su rendimiento, observar el uso de los discos,

memoria y el CPU que está utilizando nuestro motor de base de datos.

El Profiler nos permite crear trace14 para darle seguimiento a las ejecuciones y

consultas que se realizan en un determinado servidor. El Perfomance monitor, es

una herramienta que permite visualizar como se están comportando nuestros discos

duros, la forma en que la BD está utilizando la memoria y el procesador de nuestro

servidor, los picos muy altos en los discos no es positivo para el servidor, estos

picos deben mantenerse por debajo de un 20 % y sólo la memoria y el CPU

pueden tener ligeros picos esporádicamente.

La siguiente tabla muestra las recomendaciones de monitoreo para realizar un buen

análisis.15

Detalle

Mantener las estadísticas al día.

Utilizar el Profiler para afinar consultas de larga duración.

Utilizar el Profiler para consultar scans de tablas e índices.

Utilizar el Performance Monitor para analizar el uso de recursos del sistema.

Analizar recursos de memoria del Server en consultas de larga duración.

Analizar la falta estadísticas actualizadas en consultas de larga duración.

Analizar la falta de índices en consultas de larga duración.

13

Información extraída de http://www.slideshare.net/mariaalfaro08/sql-server-3140203 14

Secuencia de instrucciones que se muestran junto con la información de la máquina que las origino y la hora en que fue realizada. 15

http://www.slideshare.net/CATOGA/check-list-para-el-diseo-de-bd


40

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

3.8 Migración de una base de datos16

El proceso de la migración puede ser bastante complejo y, como hay tantas bases

de datos distintas, difícil de dar una receta que funcione en todos los casos.

Además, aparte de la dificultad de transferir la información entre los dos sistemas

gestores de base de datos, también nos influirá mucho en la complejidad del

problema el tipo de los datos de las tablas que estamos utilizando. Por ejemplo, las

fechas, los campos numéricos con decimales o los booleanos pueden dar problemas

al pasar de un sistema a otro porque pueden almacenarse de maneras distintas o,

en el caso de los números, con una precisión distinta.

Recomendaciones para migrar de Access a MySQL

Si nuestra base de datos anterior estaba construida en Access lo tenemos bastante

fácil, gracias a que MySQL dispone de un driver ODBC para sistemas Windows, que

nos permite conectar Access con el propio MySQL y pasar información fácilmente.

Este tema está relatado en el artículo Exportar datos de MySQL a Access, aunque

hay que indicar que si deseamos hacer una exportación desde Access en local a

MySQL en remoto puede haber problemas porque no todos los alojadores permiten

las conexiones en remoto con la base de datos. Si no tenemos disponible una

conexión en remoto con nuestro servidor de bases de datos vamos a tener que

cambiar la estrategia un poco.

La idea en este último caso es instalar MySQL en local y realizar la migración

desde Access en local a MySQL en local y luego podríamos hacer un backup de la

base de datos local y subirla a remoto, tal y como se ha relatado antes.

Recomendaciones para migrar desde SQL Server a MySQL

La verdad es que no he tenido este caso nunca, pero hay que decir que Access

también nos puede ayudar en este caso. Access permite seleccionar una base de

datos SQL Server y trabajar desde la propia interfaz de Access. La idea es que

Access también permite trabajar con MySQL y posiblemente haciendo un puente

entre estos dos sistemas gestores podemos exportar datos de SQL Server a MySQL.

Lo que es seguro que utilizando el propio Access de puente podríamos realizar el

trabajo. Primero exportando de SQL Server a Access y luego desde Access a MySQL.

16

http://www.desarrolloweb.com/articulos/1231.php






Guía de Estudio

41

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Cambios de un formato de datos a otro

Toda la migración tiene que tener en cuenta muy especialmente, como ya se señaló,

las maneras que tenga cada base de datos de guardar la información, es decir, del

formato de sus tipos de datos. Tenemos que contar siempre con la posible

necesidad de transformar algunos datos como pueden ser los campos booleanos,

fechas, campos memo (texto con longitud indeterminada), etc., que pueden

almacenarse de maneras distintas en cada uno de los sistemas gestores, origen y

destino.

En algunos casos posiblemente tengamos que realizar algún script que realice los

cambios necesarios en los datos. Por ejemplo puede ser para localizar los valores

booleanos guardados como true / false a valores enteros 0 / 1, que es como se

guarda en MySQL. También las fechas pueden sufrir cambios de formato, mientras

que en Access aparecen en castellano (dd/mm/aaaa) en MySQL aparecen en el

formato aaaa-mm-dd. PHP puede ayudarnos en la tarea de hacer este script,

también Visual Basic Script para Access puede hacer estas tareas complejas y el

propio lenguaje SQL, a base de sentencias dirigidas contra la base de datos, puede

servir para algunas acciones sencillas.

3.9 Integración de datos

Datamarts: Es un conjunto de bases de datos grande que debe tener disposición

de bases de datos pasadas ó actuales, así como una proyección hacia futuro.

DatawareHouse: Es una colección de datos orientada a un determinado ámbito

(empresa, organización, etc), integrado, no volátil y variable en el tiempo, que ayuda

a la toma de decisiones en la entidad en la que se utiliza. Soporta todos los datos

a nivel organizacional.

DatawareHouse

BD Datamart

BD_Relacional

Gerencia

Administración

Operativo


42

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Preguntas…

BLOQUE III

1.- ¿Qué es SQL?

2.- ¿Qué es un query?

3.- Sintaxis para crear una base de datos y una tabla.

4.- Con que query veo el tipo de datos de una tabla.

5.- Sentencia para alterar una tabla.

6.- Sentencia para realizar consultas.

7.- Query para crear un usuario.

8.- ¿Cuál es la función de hacer una vista?

9.- ¿Cuál es la sentencia para agregar una llave primaria una vez que se creó la tabla?

10.- ¿Para la llave foránea?

11.- Menciona los tipos de seguridad que se pueden ofrecer a una base de datos

12.- ¿En qué consiste la integridad en una BD?

13.- ¿En qué consiste la recuperación en frio?

14.- Menciona las 2 herramientas que pertenecen a SQL Server, que ayudan al llevar el control del monitoreo

de una base de datos.

15.- ¿Qué es el monitoreo de una BD?

16.- ¿Cuáles son los privilegios que tiene un usuario de una base de datos?

17.- ¿Qué es la migración?

18.- ¿Cuáles son las tareas de un administrador de bases de datos?

Guía de Estudio

43

Ela

bo

ra

da

po

r:

So

co

rr

o B

ue

nd

ia

Bibliografía

Bases de Datos, Desde Chen hasta Codd con Oracle. Luque Ruiz Irene, Gómez-Nieto

Miguel Ángel. Alfaomega Ra-Ma. 2002.

Microsoft SQL Server 2005, Administración y Análisis de Bases de Datos. Pérez

César. Alfaomega Ra-Ma. 2007.

Ligas consultadas

Teoría de Base de datos

http://www.youtube.com/watch?v=EdZCDi78pz8&feature=related

Modelo Entidad Relacion de Base de Datos (2).wmv

http://www.youtube.com/watch?v=HYHLt-NLy6E&feature=related

Normalización

http://www.conclase.net/mysql/curso/?cap=004b#NOR_3FN

http://cnx.org/content/m18350/latest/

http://www.lsi.upc.edu/~bcasas/docencia/pfc/NormalitzacioBD.pdf

MySQL vs Oracle

http:// es.wikipedia.org/wiki/MySQL


http://dev.mysql.com/doc/refman/5.0/es/index.html

http://www.oracle.com/index.html

Respaldar y restaurar bases de datos

http://www.youtube.com/watch?v=cpJ218wrMnM

http://www.youtube.com/watch?v=EdZCDi78pz8&feature=related

http://www.youtube.com/watch?v=HYHLt-NLy6E&feature=related

http://www.conclase.net/mysql/curso/?cap=004b

http://cnx.org/content/m18350/latest/


http://dev.mysql.com/doc/refman/5.0/es/index.html

http://www.oracle.com/index.html

crear y administrar bases de datos - cb06tic - homey+administrar+bd.pdf · guía de estudio 1 a...

Documents