1.3 B Elaboración del diseño físico y su implementación en el sistema gestor

Creación de bases de datos.

Cada conjunto de relaciones que componen un modelo completo forma una base de datos. Desde el punto de vista de SQL, una base de datos es sólo un conjunto de relaciones (o tablas), y para organizarlas o distinguirlas se accede a ellas mediante su nombre. A nivel de sistema operativo, cada base de datos se guarda en un directorio diferente.

Debido a esto, crear una base de datos es una tarea muy simple. Claro que, en el momento de crearla, la base de datos estará vacía, es decir, no contendrá ninguna tabla.

Vamos a crear y manipular nuestra propia base de datos, al tiempo que nos familiarizamos con la forma de trabajar de MySQL.

Para empezar, crearemos una base de datos para nosotros solos, y la llamaremos "prueba". Para crear una base de datos se usa una sentencia:

Podemos averiguar cuántas bases de datos existen en nuestro sistema usando la sentencia:

Las bases de datos sólo necesitan ser creadas una sola vez, pero deben ser seleccionadas cada vez que se inicia una sesión de mysql. Puede hacerse a través del comando USE como se muestra en el ejemplo, o puede indicar la base de datos en la línea de comandos al ejecutar mysql. Simplemente debe indicar el nombre de la base de datos a continuación de los parámetros que necesite ingresar. Por ejemplo:

Creación de tablas.

 Estructuras de las Tablas

Una base de datos en un sistema relacional está compuesta por un conjunto de tablas, que corresponden a las relaciones del modelo relacional. En la terminología usada en SQL no se alude a las relaciones, del mismo modo que no se usa el término atributo, pero sí la palabra columna, y no se habla de tupla, sino de línea.

Tipos de Datos

 Los tipos de datos SQL se clasifican en 13 tipos de datos primarios y de varios sinónimos válidos reconocidos por dichos tipos de datos. Los tipos de datos primarios son:

Tipo de Datos	Longitud	Descripción
BINARY	1 byte	Para consultas sobre tabla adjunta de productos de bases de datos que definen un tipo de datos Binario.
BIT	1 byte	Valores Si/No ó True/False
BYTE	1 byte	Un valor entero entre 0 y 255.
COUNTER	4 bytes	Un número incrementado automáticamente (de tipo Long)
CURRENCY	8 bytes	Un entero escalable entre 922.337.203.685.477,5808 y 922.337.203.685.477,5807.
DATETIME	8 bytes	Un valor de fecha u hora entre los años 100 y 9999.
SINGLE	4 bytes	Un valor en punto flotante de precisión simple con un rango de - 3.402823*1038 a -1.401298*10-45 para valores negativos, 1.401298*10- 45 a 3.402823*1038 para valores positivos, y 0.
DOUBLE	8 bytes	Un valor en punto flotante de doble precisión con un rango de - 1.79769313486232*10308 a -4.94065645841247*10-324 para valores negativos, 4.94065645841247*10-324 a 1.79769313486232*10308 para valores positivos, y 0.
SHORT	2 bytes	Un entero corto entre -32,768 y 32,767.
LONG	4 bytes	Un entero largo entre -2,147,483,648 y 2,147,483,647.
LONGTEXT	1 byte por carácter	De cero a un máximo de 1.2 gigabytes.
LONGBINARY	Según se necesite	De cero 1 gigabyte.  Utilizado para objetos OLE.
TEXT	1 byte por carácter	De cero a 255 caracteres.

La siguiente tabla recoge los sinónimos de los tipos de datos definidos:

Tipo de Dato	Sinónimos		Tipo de Dato	Sinónimos
BINARY	VARBINARY		DATETIME	DATE TIME
BIT	BOOLEAN LOGICAL LOGICAL1 YESNO		SINGLE	FLOAT SINGLE REAL
BYTE	INTEGER1		DOUBLE	FLOAT NUMBER NUMERIC
COUNTER	AUTOINCREMENT		SHORT	INT SMALLINT
CURRENCY	MONEY		TEXT	VARCHAR

Creación de Tablas Nuevas

 CREATE TABLE tabla ( campo1 tipo (tamaño) índice1, campo2 tipo (tamaño) índice2,... , índice multicampo , ... ) motor

En donde: 

Tabla	Es el nombre de la tabla que se va a crear.
campo1 campo2	Es el nombre del campo o de los campos que se van a crear en la nueva tabla. La nueva tabla debe contener, al menos, un campo.
tipo	Es el tipo de datos de campo en la nueva tabla. (Ver Tipos de Datos)
tamaño	Es el tamaño del campo sólo se aplica para campos de tipo texto.
índice1 índice2	Es una cláusula CONSTRAINT que define el tipo de índice a crear. Esta cláusula en opcional.
índice multicampos	Es una cláusula CONSTRAINT que define el tipo de índice multicampos a crear. Un índice multicampo es aquel que está indexado por el contenido de varios campos. Esta cláusula en opcional.

EJEMPLOS:

Crea una nueva tabla llamada Empleados con dos campos, uno llamado Nombre de tipo texto y longitud 25 y otro llamado apellidos con longitud 50.

CREATE TABLE Empleados ( Curp varchar(18) not null, Nombre varchar(10), Apellidos varchar(15), FechaNacimiento DATETIME, primary key(Curp));

Crea una nueva tabla llamada Empleados con un campo Curp de tipo texto y longitud 18, Nombre de tipo texto y longitud 10, otro con llamado Apellidos de tipo texto y longitud 15 y uno más llamado FechaNacimiento de tipo Fecha/Hora. También crea una llave primaria para lo cual se designa el campo Curp.

CREATE TABLE Empleados (IdEmpleado INTEGER not null,Nombre varchar(20),Apellidos varchar(20),FechaNacimiento DATETIME, primary key (IdEmpleado)) type=innodb;

Supresión de tablas.

A veces podemos decidir que necesitamos eliminar una tabla en la base de datos por alguna razón. De hecho, sería problemático si no podemos hacerlo ya que esto crearía una pesadilla de mantenimiento para DBA. Afortunadamente, SQL nos permite hacerlo, ya que podemos utilizar el comando DROP TABLE. La sintaxis para DROP TABLE es:

DROP TABLE "nombre_tabla";

Entonces, si deseamos eliminar una tabla denominada cliente que creamos en la sección CREATE TABLE, simplemente ingresamos:

Modificación de tablas.

Una vez que se crea la tabla en la base de datos, hay muchas ocasiones donde uno puede desear cambiar la estructura de la tabla. Los casos típicos incluyen los siguientes:

• Agregar una columna 
• Eliminar una columna 
• Cambiar el nombre de una columna 
• Cambiar el tipo de datos para una columna

La sintaxis SQL para ALTER TABLE es:

ALTER TABLE "nombre_tabla" [modificar especificación]

[modificar especificación] depende del tipo de modificación que deseamos realizar. Para los usos mencionados anteriormente, las instrucciones [modificar especificación] son:

• Agregar una columna: ADD “columna 1” “tipos de datos para columna 1”
• Eliminar una columna: DROP “columna 1”
• Cambiar el nombre de una columna: CHANGE “nombre antiguo de la columna” “nuevo nombre de la columna” “tipos de datos para la nueva columna".
• Cambiar el tipo de datos para una columna: MODIFY “columna 1” “nuevo tipo de datos”

Recorramos ejemplos para cada uno de lo anteriormente mencionado, utilizando la tabla “customer”.

Tabla customer

Column Name	Data Type
First_Name	varchar(50)
Last_Name	varchar(50)
Address	varchar(50)
City	varchar(50)
Country	varchar(25)
Birth_Date	date

Primero, deseamos agregar una columna denominada “Gender” a esta tabla. Para hacerlo, ingresamos:

Luego, deseamos renombrar “Address" a “Addr”. Para hacerlo, ingresamos:

Finalmente, deseamos eliminar la columna “Gender”. Para hacerlo, ingresamos:

Establecer Relaciones

 La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. También controla que no pueda eliminarse un registro de una tabla ni modificar la clave primaria si una clave externa hace referencia al registro.

Para que un campo sea clave foránea, este necesita ser definido como tal al momento de crear la tabla. En MySQL se pueden definir claves foráneas, pero únicamente se pueden utilizar cuando se usan tablas del tipo InnoDB.

Para poder trabajar con claves foráneas necesitan hacer lo siguiente:

1.   Crear ambas tablas del tipo InnoDB.

2.   Usar la sintaxis FOREIGN KEY (campo_fk) REFERENCES nombre_tabla (nombre_campo)

3.   Crear un índice en el campo que ha sido declarado clave foránea

 InnoDB no crea automaticamente índices en las claves foráneas, así que deben crearlos de manera explícita. Los índices son necesarios para que la verificación de las claves foráneas sea más rápida.

 Ejemplo:

CREATE TABLE cliente
( id_cliente INT NOT NULL,nombre VARCHAR(30),PRIMARY KEY (id_cliente)) ENGINE = INNODB;

CREATE TABLE venta
(id_factura INT NOT NULL,
id_cliente INT NOT NULL,
cantidad INT,PRIMARY KEY(id_factura),FOREIGN KEY (id_cliente) REFERENCES cliente(id_cliente))ENGINE = INNODB;

Los campos correspondientes en la clave foránea y en la clave referenciada deben tener tipos de datos similares para que puedan ser comparadas.

Cuando la tabla ya ha sido creada y no se han añadido claves foráneas éstas pueden agregarse mediante la opción de modificación de tablas como se muestra a conticuación:

EJEMPLO:

  

mysql> ALTER TABLE saca

->ADD FOREIGN KEY(codigo_usuario)

-> REFERENCES usuario(codigo)

-> ON UPDATE CASCADE ON DELETE RESTRICT, 

-> ADD FOREIGN KEY(codigo_ejemplar)

-> REFERENCES ejemplar(codigo)

-> ON UPDATE CASCADE ON DELETE RESTRICT;

Query OK, 0 rows affected (0.25 sec)

Records: 0  Duplicates: 0  Warnings: 0