Creación de una base de datos completa

Curso básico de MariaDB

Este módulo te guiará en la creación de un proyecto de base de datos completo, aplicando todo lo aprendido a lo largo del curso. Aquí diseñarás una base de datos para un proyecto real, implementarás la estructura en MariaDB, escribirás consultas para gestionar los datos, y documentarás el proyecto para exportación y uso futuro.

8.1 Diseño de la Estructura de la Base de Datos para un Proyecto Real

Para esta práctica, diseñaremos una base de datos de ejemplo para un sistema de gestión de biblioteca que almacenará información sobre libros, autores, miembros, y préstamos.

Pasos de Diseño:

1. Definir Entidades:
   • Libros: La base de datos debe contener información sobre cada libro, como título, autor, categoría, ISBN y fecha de publicación.
   • Autores: Una entidad para registrar datos de los autores.
   • Miembros: Una entidad para gestionar los miembros registrados en la biblioteca.
   • Préstamos: Información sobre los préstamos de libros, incluyendo qué libro fue prestado, a qué miembro, y en qué fecha.
2. Definir las Relaciones:
   • Un autor puede haber escrito varios libros (relación uno a muchos).
   • Un libro puede ser prestado a varios miembros a lo largo del tiempo (relación muchos a muchos, que resolveremos con la tabla Préstamos).
   • Cada préstamo registra un miembro y un libro específicos.
3. Crear un Diagrama de Entidades y Relaciones (DER):
   • Visualiza las entidades y relaciones que has definido. Puede ser útil dibujar un DER para entender cómo se relacionan entre sí.
4. Definir los Atributos de Cada Entidad:
   • Libros: id, titulo, isbn, fecha_publicacion, id_autor.
   • Autores: id, nombre, nacionalidad.
   • Miembros: id, nombre, direccion, telefono, email.
   • Préstamos: id, id_libro, id_miembro, fecha_prestamo, fecha_devolucion.

8.2 Implementación de la Base de Datos en MariaDB

Crear la Base de Datos:

• Abre MariaDB y crea la base de datos para el proyecto:sqlCopiar códigoCREATE DATABASE gestion_biblioteca; USE gestion_biblioteca;

Crear las Tablas:

Usa los siguientes comandos SQL para crear cada una de las tablas basadas en el diseño anterior.

Tabla de Autores:

CREATE TABLE autores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  nacionalidad VARCHAR(50)
);

Tabla de Libros:

CREATE TABLE libros (
id INT AUTO_INCREMENT PRIMARY KEY,
titulo VARCHAR(150) NOT NULL,
isbn VARCHAR(20) UNIQUE,
fecha_publicacion DATE,
id_autor INT,
FOREIGN KEY (id_autor) REFERENCES autores(id)
);

Tabla de Miembros:

CREATE TABLE miembros (
id INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
direccion VARCHAR(255),
telefono VARCHAR(20),
email VARCHAR(100) UNIQUE
);

Tabla de Préstamos:

CREATE TABLE prestamos (
id INT AUTO_INCREMENT PRIMARY KEY,
id_libro INT,
id_miembro INT,
fecha_prestamo DATE NOT NULL,
fecha_devolucion DATE,
FOREIGN KEY (id_libro) REFERENCES libros(id),
FOREIGN KEY (id_miembro) REFERENCES miembros(id)
);

Poblar las Tablas con Datos de Ejemplo:

Inserta algunos registros de ejemplo para trabajar con la base de datos en la siguiente sección.

Ejemplos de Autores:

INSERT INTO autores (nombre, nacionalidad) VALUES
('Gabriel García Márquez', 'Colombiana'),
('Isabel Allende', 'Chilena'),
('J.K. Rowling', 'Británica');

Ejemplos de Libros:

INSERT INTO libros (titulo, isbn, fecha_publicacion, id_autor) VALUES
('Cien Años de Soledad', '1234567890', '1967-05-30', 1),
('La Casa de los Espíritus', '0987654321', '1982-11-01', 2),
('Harry Potter y la Piedra Filosofal', '1122334455', '1997-06-26', 3);

Ejemplos de Miembros:

INSERT INTO miembros (nombre, direccion, telefono, email) VALUES
('Juan Perez', 'Calle Falsa 123', '123456789', 'juan@example.com'),
('Maria Lopez', 'Avenida Siempre Viva 456', '987654321', 'maria@example.com');

Ejemplos de Préstamos:

INSERT INTO prestamos (id_libro, id_miembro, fecha_prestamo, fecha_devolucion) VALUES
(1, 1, '2024-10-01', '2024-10-15'),
(2, 2, '2024-10-10', NULL);

8.3 Creación de Consultas para Manejo de Datos

Consulta de Libros por Autor:

• Lista los libros escritos por cada autor.

SELECT autores.nombre AS autor, libros.titulo AS libro
FROM libros
JOIN autores ON libros.id_autor = autores.id;

Consultar los Préstamos Activos:

• Consulta los préstamos activos (aquellos sin fecha de devolución).s

SELECT libros.titulo, miembros.nombre AS miembro, prestamos.fecha_prestamo
FROM prestamos
JOIN libros ON prestamos.id_libro = libros.id
JOIN miembros ON prestamos.id_miembro = miembros.id
WHERE prestamos.fecha_devolucion IS NULL;

Contar Libros Prestados por Miembro:

• Consulta el número total de libros prestados por cada miembro.

SELECT miembros.nombre, COUNT(prestamos.id) AS total_prestamos
FROM prestamos
JOIN miembros ON prestamos.id_miembro = miembros.id
GROUP BY miembros.id;

Actualizar Estado de un Préstamo:

• Marca un préstamo como devuelto al agregar una fecha_devolucion.

UPDATE prestamos
SET fecha_devolucion = '2024-10-20'
WHERE id = 1;

8.4 Documentación y Preparación para Exportación de la Base de Datos

Crear un Archivo README para Documentación:

Documenta la estructura de la base de datos y describe el propósito de cada tabla, la relación entre ellas, y los campos de interés.

Ejemplo:

# Gestión de Biblioteca - Base de Datos

## Descripción
Base de datos diseñada para gestionar los libros, autores, miembros, y préstamos de una biblioteca.

## Tablas
- **autores**: Almacena información sobre los autores.
- **libros**: Contiene los datos de los libros.
- **miembros**: Registro de los miembros de la biblioteca.
- **prestamos**: Guarda información de los préstamos de libros.

## Relaciones
- Cada libro está relacionado con un autor.
- Cada préstamo está vinculado a un miembro y a un libro.

Exportar la Base de Datos:

• Usa mysqldump para crear una copia de seguridad de la base de datos completa.

mysqldump -u root -p gestion_biblioteca > gestion_biblioteca.sql

Pruebas y Verificación:

• Importa la base de datos en otro sistema o en una nueva instalación para asegurarte de que todos los datos y la estructura están correctos.

mysql -u root -p gestion_biblioteca < gestion_biblioteca.sql

Con este proyecto final hemos pretendido consolidar todos los conocimientos adquiridos, desde el diseño de una base de datos y la creación de consultas, hasta la gestión de usuarios y la realización de copias de seguridad.