El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Cas d'Estudi: Base de Dades Relacional

Següent ►
►

En aquest cas d'estudi, explorarem el disseny, implementació i ús d'una base de dades relacional per a una biblioteca. Aquest exemple pràctic ens permetrà aplicar els conceptes apresos en els mòduls anteriors, com el model relacional, el llenguatge SQL, el disseny d'esquemes i la normalització.

  1. Requisits del Sistema

1.1. Descripció del Sistema

La biblioteca necessita un sistema per gestionar els llibres, els membres, els préstecs i les devolucions. Els requisits principals són:

  • Emmagatzemar informació sobre els llibres, incloent-hi títol, autor, any de publicació i gènere.
  • Gestionar la informació dels membres, incloent-hi nom, adreça, telèfon i data d'inscripció.
  • Registrar els préstecs de llibres, incloent-hi la data de préstec, la data de devolució prevista i la data de devolució real.
  • Permetre la cerca de llibres per títol, autor o gènere.
  • Generar informes sobre els llibres prestats i els membres actius.

1.2. Requisits Funcionals

  • Alta, baixa i modificació de llibres.
  • Alta, baixa i modificació de membres.
  • Registre de préstecs i devolucions.
  • Cerca de llibres per diferents criteris.
  • Generació d'informes.

  1. Disseny de l'Esquema

2.1. Diagrama Entitat-Relació (ER)

Primer, dissenyarem un diagrama ER per representar les entitats i les seves relacions.

  • Entitats:

    • Llibre
    • Membre
    • Préstec

  • Relacions:

    • Un llibre pot ser prestat a zero o més membres.
    • Un membre pot tenir zero o més préstecs.

2.2. Transformació del Diagrama ER a Esquema Relacional

Transformarem el diagrama ER en taules relacionals.

Taula Llibre

Columna Tipus de Dada Descripció
id_llibre INT Clau Primària
titol VARCHAR(255) Títol del llibre
autor VARCHAR(255) Autor del llibre
any_publicacio INT Any de publicació
genere VARCHAR(100) Gènere del llibre

Taula Membre

Columna Tipus de Dada Descripció
id_membre INT Clau Primària
nom VARCHAR(255) Nom del membre
adreça VARCHAR(255) Adreça del membre
telefon VARCHAR(20) Telèfon del membre
data_inscripcio DATE Data d'inscripció

Taula Prestec

Columna Tipus de Dada Descripció
id_prestec INT Clau Primària
id_llibre INT Clau Forana (Llibre)
id_membre INT Clau Forana (Membre)
data_prestec DATE Data del préstec
data_devolucio_prevista DATE Data de devolució prevista
data_devolucio_real DATE Data de devolució real

  1. Implementació en SQL

3.1. Creació de Taules

CREATE TABLE Llibre (
    id_llibre INT PRIMARY KEY,
    titol VARCHAR(255) NOT NULL,
    autor VARCHAR(255) NOT NULL,
    any_publicacio INT,
    genere VARCHAR(100)
);

CREATE TABLE Membre (
    id_membre INT PRIMARY KEY,
    nom VARCHAR(255) NOT NULL,
    adreça VARCHAR(255),
    telefon VARCHAR(20),
    data_inscripcio DATE
);

CREATE TABLE Prestec (
    id_prestec INT PRIMARY KEY,
    id_llibre INT,
    id_membre INT,
    data_prestec DATE,
    data_devolucio_prevista DATE,
    data_devolucio_real DATE,
    FOREIGN KEY (id_llibre) REFERENCES Llibre(id_llibre),
    FOREIGN KEY (id_membre) REFERENCES Membre(id_membre)
);

3.2. Inserció de Dades

-- Inserir llibres
INSERT INTO Llibre (id_llibre, titol, autor, any_publicacio, genere)
VALUES (1, '1984', 'George Orwell', 1949, 'Distopia');

INSERT INTO Llibre (id_llibre, titol, autor, any_publicacio, genere)
VALUES (2, 'To Kill a Mockingbird', 'Harper Lee', 1960, 'Ficció');

-- Inserir membres
INSERT INTO Membre (id_membre, nom, adreça, telefon, data_inscripcio)
VALUES (1, 'Joan Garcia', 'Carrer Major, 1', '123456789', '2023-01-15');

INSERT INTO Membre (id_membre, nom, adreça, telefon, data_inscripcio)
VALUES (2, 'Maria López', 'Carrer Nou, 2', '987654321', '2023-02-20');

-- Inserir préstecs
INSERT INTO Prestec (id_prestec, id_llibre, id_membre, data_prestec, data_devolucio_prevista, data_devolucio_real)
VALUES (1, 1, 1, '2023-03-01', '2023-03-15', NULL);

INSERT INTO Prestec (id_prestec, id_llibre, id_membre, data_prestec, data_devolucio_prevista, data_devolucio_real)
VALUES (2, 2, 2, '2023-03-05', '2023-03-19', NULL);

3.3. Consultes SQL

Cerca de Llibres per Autor

SELECT * FROM Llibre WHERE autor = 'George Orwell';

Llistat de Préstecs Actius

SELECT * FROM Prestec WHERE data_devolucio_real IS NULL;

Informes de Membres Actius

SELECT Membre.nom, COUNT(Prestec.id_prestec) AS nombre_prestecs
FROM Membre
LEFT JOIN Prestec ON Membre.id_membre = Prestec.id_membre
GROUP BY Membre.nom;

  1. Exercicis Pràctics

Exercici 1: Inserir Nous Llibres i Membres

  • Inserir dos nous llibres a la taula Llibre.
  • Inserir dos nous membres a la taula Membre.

Exercici 2: Registre de Nous Préstecs

  • Registra dos nous préstecs a la taula Prestec.

Exercici 3: Consultes SQL

  • Escriu una consulta per trobar tots els llibres publicats després de l'any 2000.
  • Escriu una consulta per trobar tots els membres que han prestat més d'un llibre.

  1. Solucions als Exercicis

Solució a l'Exercici 1

-- Nous llibres
INSERT INTO Llibre (id_llibre, titol, autor, any_publicacio, genere)
VALUES (3, 'Brave New World', 'Aldous Huxley', 1932, 'Distopia');

INSERT INTO Llibre (id_llibre, titol, autor, any_publicacio, genere)
VALUES (4, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 'Ficció');

-- Nous membres
INSERT INTO Membre (id_membre, nom, adreça, telefon, data_inscripcio)
VALUES (3, 'Pere Puig', 'Carrer Vell, 3', '111222333', '2023-03-10');

INSERT INTO Membre (id_membre, nom, adreça, telefon, data_inscripcio)
VALUES (4, 'Anna Riera', 'Carrer Nou, 4', '444555666', '2023-03-15');

Solució a l'Exercici 2

-- Nous préstecs
INSERT INTO Prestec (id_prestec, id_llibre, id_membre, data_prestec, data_devolucio_prevista, data_devolucio_real)
VALUES (3, 3, 3, '2023-03-20', '2023-04-03', NULL);

INSERT INTO Prestec (id_prestec, id_llibre, id_membre, data_prestec, data_devolucio_prevista, data_devolucio_real)
VALUES (4, 4, 4, '2023-03-22', '2023-04-05', NULL);

Solució a l'Exercici 3

-- Llibres publicats després de l'any 2000
SELECT * FROM Llibre WHERE any_publicacio > 2000;

-- Membres que han prestat més d'un llibre
SELECT Membre.nom, COUNT(Prestec.id_prestec) AS nombre_prestecs
FROM Membre
LEFT JOIN Prestec ON Membre.id_membre = Prestec.id_membre
GROUP BY Membre.nom
HAVING COUNT(Prestec.id_prestec) > 1;

  1. Conclusió

Aquest cas d'estudi ha proporcionat una visió pràctica de com dissenyar i implementar una base de dades relacional per a una biblioteca. Hem cobert des de la identificació dels requisits fins a la creació de taules i l'execució de consultes SQL. Els exercicis pràctics han reforçat els conceptes apresos i han proporcionat una oportunitat per aplicar-los en situacions reals.

◄ Anterior
◄
Següent ►
►
El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2024. Tots els drets reservats