Enreas
Registrarse
Advertisement

Uso de NULL[]

La siguiente consulta muestra el título de las películas en una columna y, concatenados, el nombre de director y el del actor separados por comas:

USE videoteca;
SELECT
  titulo,
  CONCAT(director,', ',actor)
FROM
  pelicula;

Insertemos ahora una nueva película en nuestra base de datos:

INSERT INTO pelicula(titulo,director)
VALUES('Naves misteriosas','Douglas Trumbull');

La consulta

SELECT * FROM pelicula;

nos devuelve todas las películas almacenadas. Podemos ver una de ellas con NULL en la columna actor, precisamente la que acabamos de insertar. Si ejecuta ahora la consulta con concatenación, verá que aunque sí tenemos un director para Naves misteriosas, el resultado no lo muestra. Eso se debe a que el resultado de concatenar NULL con cualquier cadena es NULL, NULL no se comporta como una cadena vacía. Una posible solución a este problema es evitar el uso de NULL, cambiando la definición de la tabla por esta otra:

CREATE TABLE pelicula(
  titulo VARCHAR(64) NOT NULL,
  director VARCHAR(128) NOT NULL DEFAULT '',
  actor VARCHAR(128)NOT NULL DEFAULT ''
);

Si no desea perder los datos que ya hemos insertado en la tabla, puede alterar su definición:

ALTER TABLE pelicula
  MODIFY titulo VARCHAR(64) NOT NULL,
  MODIFY director VARCHAR(128) NOT NULL
    DEFAULT '',
  MODIFY actor VARCHAR(128) NOT NULL
    DEFAULT '';

Tras la modificación de la tabla, la consulta con concatenación sí muestra el nombre del director.

Claves primarias[]

Insertemos una nueva película, la segunda parte de Alien:

USE videoteca;
INSERT INTO pelicula
VALUES(
  'Alien',
  'James Cameron',
  'Sigourney Weaver'
);

Por error, hemos utilizado el título Alien, cuando en realidad la película se llamaba Aliens. Ahora tenemos en la tabla de películas dos con el mismo título, una situación no deseable. Podemos evitarlo cambiando la forma en la que se crea la tabla:

CREATE TABLE pelicula(
  titulo VARCHAR(64) NOT NULL,
  director VARCHAR(128) NOT NULL DEFAULT '',
  actor VARCHAR(128)NOT NULL DEFAULT '',
  PRIMARY KEY(titulo)
);

o sólo modificando sus propiedades. Previamente hemos de evitar la existencia de registros con el mismo título:

UPDATE
  pelicula
SET
  titulo='Aliens'
WHERE
  director='James Cameron';

USE videoteca;
ALTER TABLE pelicula ADD PRIMARY KEY(titulo);

Incremento automático[]

Podemos modificar la tabla de películas para que su clave primaría sea un identificador con incremento automático. Para crearla desde cero:

USE videoteca;
CREATE TABLE pelicula(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo VARCHAR(64) NOT NULL,
  director VARCHAR(128) NOT NULL DEFAULT '',
  actor VARCHAR(128) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
);

También podemos modificar el diseño de la que ya tenemos:

ALTER TABLE pelicula
  DROP PRIMARY KEY,
  ADD id INT NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY(id);

Modelo relacional[]

Vamos a crear una tabla en la que guardar qué películas prestamos a quién:

USE videoteca;
CREATE TABLE prestamo (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  idpelicula INT UNSIGNED NOT NULL,
  persona VARCHAR(128) NOT NULL,
  fecha DATE NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(idpelicula)
    REFERENCES pelicula(id)
);

Insertemos un valor en la tabla de préstamos. Se supone que la película insertada tiene ese identificador:

INSERT INTO prestamo(idpelicula,persona,fecha)
VALUES(4,'Kake',CURRENT_DATE());

Ahora, intentemos la inserción de una película no existente:

INSERT INTO prestamo(idpelicula,persona,fecha)
VALUES(123,'Germán',CURRENT_DATE());

Debería fallar, pero no lo hace, porque para que la integridad referencial se mantenga hay que utilizar tablas InnoDB. Modifiquemos la tabla de películas para que sea de ese tipo:

ALTER TABLE pelicula TYPE=InnoDB;

Creamos de nuevo la tabla de préstamos, ahora con el tipo InnoDB:

USE videoteca;
DROP TABLE prestamo;
CREATE TABLE prestamo (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  idpelicula INT UNSIGNED NOT NULL,
  persona VARCHAR(128) NOT NULL,
  fecha DATE NOT NULL,
  INDEX prestamo_FK(idpelicula),
  PRIMARY KEY(id),
  FOREIGN KEY(idpelicula)
    REFERENCES pelicula(id)
)TYPE=InnoDB;

Si intenta ahora la inserción del préstamo de una película inexistente, fallará.

Divide y vencerás[]

Para la depuración del diseño de nuestra base datos, partimos de cero con una nueva versión de nuestra tabla de películas:

DROP DATABASE IF EXISTS videoteca;
CREATE DATABASE videoteca;
USE videoteca;
CREATE TABLE pelicula (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo VARCHAR(64) NOT NULL,
  director VARCHAR(128) NOT NULL DEFAULT '',
  actor VARCHAR(128) NOT NULL DEFAULT '',
  soporte VARCHAR(3) NOT NULL DEFAULT '',
  imdb_director VARCHAR(32) NOT NULL DEFAULT '',
  imdb_actor VARCHAR(32) NOT NULL DEFAULT '',
  genero VARCHAR(2) NOT NULL DEFAULT '',
  genero_desc VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
)
TYPE=InnoDB;

Podemos realizar algunas inserciones en esta tabla:

USE videoteca;
INSERT INTO pelicula(
  titulo,director,actor,
  soporte,imdb_director,imdb_actor,
  genero,genero_desc
)
VALUES(
  'Blade Runner','Ridley Scott','Harrison Ford',
  'DVD','nm0000631','nm0000148',
  'CF','Ciencia-ficción'
);

INSERT INTO pelicula(
  titulo,director,actor,
  soporte,imdb_director,imdb_actor,
  genero,genero_desc
)
VALUES(
  'Gladiator','Ridley Scott','Russell Crowe',
  'DVD','nm0000631','nm0000128',
  'A','Aventuras'
);

INSERT INTO pelicula(
  titulo,director,actor,
  soporte,imdb_director,imdb_actor,
  genero,genero_desc
)
VALUES(
  'A propósito de Henry','Mike Nichols',
  'Harrison Ford','VHS','nm0001566','nm0000148',
  'D','Drama'
);

Este diseño presenta varios problemas:

  • cada vez que introducimos el nombre de un actor, si ese actor ya participó en alguna de las películas que tenemos en la bases de datos, estamos duplicando los datos, ocupando más espacio.
  • Con relación al punto anterior, es posible que nos equivoquemos alguna de las veces que introducimos el nombre del actor. Podríamos escribir Harrisond Ford como protagonista de una de las películas de Indiana Jones. Si posteriormente hiciésemos una consulta de las películas protagonizadas por este actor, la de Indiana Jones no aparecería por el error en el nombre.
  • Esta misma fuente de errores se repite con el género de la película y su descripción y con los códigos de IMDb.
  • Sólo podemos almacenar un director por película. Algunas, como Delicatessen, fueron dirigidas por dos personas.
  • Lo mismo se puede decir, con más causa, de los actores.[/list]Puede crear la base de datos resultante del nuevo diseño utilizando estas instrucciones:
DROP DATABASE IF EXISTS videoteca;
CREATE DATABASE videoteca;
USE videoteca;

DROP TABLE IF EXISTS director;
CREATE TABLE director (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  imdb VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS genero;
CREATE TABLE genero (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(2) NOT NULL,
  descripcion VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS actor;
CREATE TABLE actor (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  imdb VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS soporte;
CREATE TABLE soporte (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(3) NOT NULL,
  descripcion VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS pelicula;
CREATE TABLE pelicula (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo VARCHAR(64) NOT NULL,
  idsoporte INTEGER UNSIGNED NOT NULL,
  idgenero INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  INDEX p_FK1(idsoporte),
  INDEX p_FK2(idgenero),
  FOREIGN KEY(idsoporte)
    REFERENCES soporte(id),
  FOREIGN KEY(idgenero)
    REFERENCES genero(id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS actores_por_pelicula;
CREATE TABLE actores_por_pelicula (
  idpelicula INTEGER UNSIGNED NOT NULL,
  idactor INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(idpelicula, idactor),
  INDEX app_FK1(idpelicula),
  INDEX app_FK2(idactor),
  FOREIGN KEY(idpelicula)
    REFERENCES pelicula(id),
  FOREIGN KEY(idactor)
    REFERENCES actor(id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS directores_por_pelicula;
CREATE TABLE directores_por_pelicula (
  idpelicula INTEGER UNSIGNED NOT NULL,
  iddirector INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(idpelicula, iddirector),
  INDEX dpp_FK1(idpelicula),
  INDEX dpp_FK2(iddirector),
  FOREIGN KEY(idpelicula)
    REFERENCES pelicula(id),
  FOREIGN KEY(iddirector)
    REFERENCES director(id)
)
TYPE=InnoDB;

Enlaces interesantes[]

Puede encontrar DBDesigner aquí: [1].
En la siguiente página puede encontrar una traducción no oficial de los términos de la licencia GPL al castellano: [.
Si decidió compilar DBDesigner usted mismo, seguramente encuentre de utilidad:

Otros programas para diseñar bases de datos:

Si desea profundizar en el diseño de bases de datos, puede encontrar interesantes los artículos sobre integridad relacional y normalización publicados en MySQL Hispano.

Advertisement