420-5D4 – SGBD bandes dessinées

28 septembre 2023

A03 – Gestion d’une collection de bandes dessinées

Voici un schéma de base de données pour gérer une collection de bandes dessinées en utilisant Oracle Database. Le schéma comprendra des tables pour les titres de bandes dessinées, les personnages, les citations par personnage, l’année de publication et les auteurs.

-- Supprimer la table Lecteurs_Albums si elle existe
DROP TABLE Lecteurs_Albums;

-- Supprimer la table Lecteurs si elle existe
DROP TABLE Lecteurs;

-- Supprimer la table Citations_Albums si elle existe
DROP TABLE Citations_Albums;

-- Supprimer la table Citations si elle existe
DROP TABLE Citations;

-- Supprimer la table Albums_Personnages si elle existe
DROP TABLE Albums_Personnages;

-- Supprimer la table Personnages si elle existe
DROP TABLE Personnages;

-- Supprimer la table Albums si elle existe
DROP TABLE Albums;

-- Supprimer la table Auteurs si elle existe
DROP TABLE Auteurs;

-- Créer la table Auteurs
CREATE TABLE Auteurs (
    auteur_id NUMBER PRIMARY KEY,
    nom VARCHAR2(255)
);

-- Créer la table Albums
CREATE TABLE Albums (
    album_id NUMBER PRIMARY KEY,
    titre VARCHAR2(255),
    annee_publication NUMBER,
    auteur_id NUMBER,
    FOREIGN KEY (auteur_id) REFERENCES Auteurs(auteur_id),
    nombre_pages NUMBER,
    isbn VARCHAR2(20),
    photo_couverture VARCHAR2(255)
);

-- Créer la table Personnages
CREATE TABLE Personnages (
    personnage_id NUMBER PRIMARY KEY,
    nom VARCHAR2(100)
);

-- Créer la table Citations
CREATE TABLE Citations (
    citation_id NUMBER PRIMARY KEY,
    texte CLOB,
    personnage_id NUMBER,
    FOREIGN KEY (personnage_id) REFERENCES Personnages(personnage_id)
);

-- Créer la table Albums_Personnages
CREATE TABLE Albums_Personnages (
    album_id NUMBER,
    personnage_id NUMBER,
    FOREIGN KEY (album_id) REFERENCES Albums(album_id),
    FOREIGN KEY (personnage_id) REFERENCES Personnages(personnage_id)
);

-- Créer la table Citations_Albums
CREATE TABLE Citations_Albums (
    citation_id NUMBER,
    album_id NUMBER,
    page NUMBER,
    FOREIGN KEY (citation_id) REFERENCES Citations(citation_id),
    FOREIGN KEY (album_id) REFERENCES Albums(album_id)
);

-- Créer la table Lecteurs
CREATE TABLE Lecteurs (
    lecteur_id NUMBER PRIMARY KEY,
    nom VARCHAR2(50),
    prenom VARCHAR2(50),
    email VARCHAR2(100)
);

-- Créer la table Lecteurs_Albums
CREATE TABLE Lecteurs_Albums (
    lecteur_id NUMBER,
    album_id NUMBER,
    FOREIGN KEY (lecteur_id) REFERENCES Lecteurs(lecteur_id),
    FOREIGN KEY (album_id) REFERENCES Albums(album_id)
);

Ce schéma comprend les tables suivantes :

Albums :

album_id (clé primaire)
titre (titre de l’album)
annee_publication (année de publication)
auteur (auteur de l’album)
nombre_pages (nombre de pages)
isbn (ISBN de l’album)
photo_couverture (chemin vers la photo de la page couverture)

Personnages :

personnage_id (clé primaire)
nom (nom du personnage)

Citations :

citation_id (clé primaire)
texte (texte de la citation)
personnage_id (clé étrangère vers la table Personnages)

Albums_Personnages (table de liaison pour gérer la relation entre les albums et les personnages) :

album_id (clé étrangère vers la table Albums)
personnage_id (clé étrangère vers la table Personnages)

Citations_Albums (table de liaison pour gérer la relation entre les citations et les albums) :

citation_id (clé étrangère vers la table Citations)
album_id (clé étrangère vers la table Albums)
page (numéro de page où la citation apparaît dans l’album)

Lecteurs :

lecteur_id (clé primaire)
nom (nom du lecteur)
prenom (prénom du lecteur)
email (adresse e-mail du lecteur)

Lecteurs_Albums (table de liaison pour enregistrer les albums que les lecteurs ont lus) :

lecteur_id (clé étrangère vers la table Lecteurs)
album_id (clé étrangère vers la table Albums)

Ce schéma vous permettra de gérer une collection de bandes dessinées en enregistrant les détails des titres, des personnages, des citations, des années de publication et des auteurs et des lecteurs.

Ajouter dans la bases de données, les auteurs, 10 albums de Tintin, les personnages suivants: Tintin, Milou, Capitaine Haddock, Allan, Professeur Tournesol, Le colonel Alvarez, Dupond, Dupont, Nestor et Bianca Castafiore. Les albums dans lesquels apparaissent ces personnages. Ajouter 10 citations du Capitaine Haddock avec l’album et la page ou apparait la citation.

-- Ajouter 10 albums de Tintin
INSERT INTO Albums (titre, annee_publication, auteur_id, nombre_pages, isbn, photo_couverture)
VALUES
    ('Tintin au pays des Soviets', 1929, 1, 144, '978-2203001016', 'tintin_soviets.jpg'),
    ('Tintin au Congo', 1931, 1, 62, '978-2203001023', 'tintin_congo.jpg'),
    ('Tintin en Amérique', 1932, 1, 120, '978-2203001030', 'tintin_amerique.jpg'),
    ('Les Cigares du Pharaon', 1934, 1, 128, '978-2203001047', 'tintin_cigares_pharaon.jpg'),
    ('Le Lotus bleu', 1936, 1, 80, '978-2203001054', 'tintin_lotus_bleu.jpg'),
    ('L''Oreille cassée', 1943, 1, 62, '978-2203001061', 'tintin_oreille_cassee.jpg'),
    ('L''Île noire', 1938, 1, 62, '978-2203001078', 'tintin_ile_noire.jpg'),
    ('L''Étoile mystérieuse', 1942, 1, 62, '978-2203001108', 'tintin_etoile_mysterieuse.jpg'),
    ('Le Secret de La Licorne', 1943, 1, 62, '978-2203001115', 'tintin_secret_licorne.jpg'),
    ('Le Trésor de Rackham le Rouge', 1944, 1, 62, '978-2203001122', 'tintin_tresor_rackham_rouge.jpg');


-- Ajouter les personnages
INSERT INTO Personnages (nom) VALUES
    ('Tintin'),
    ('Milou'),
    ('Capitaine Haddock'),
    ('Allan'),
    ('Professeur Tournesol'),
    ('Le colonel Alvarez'),
    ('Dupond'),
    ('Dupont'),
    ('Nestor'),
    ('Bianca Castafiore');

-- Associer les personnages aux albums
INSERT INTO Albums_Personnages (album_id, personnage_id) VALUES
    (1, 1), (1, 2), (1, 3), -- Tintin, Milou, Capitaine Haddock dans Tintin au pays des Soviets
    (2, 1), (2, 2), (2, 3), -- Tintin, Milou, Capitaine Haddock dans Tintin au Congo
    (3, 1), (3, 2), (3, 3), -- Tintin, Milou, Capitaine Haddock dans Tintin en Amérique
    (4, 1), (4, 2), (4, 3), -- Tintin, Milou, Capitaine Haddock dans Les Cigares du Pharaon
    (5, 1), (5, 2), (5, 3), -- Tintin, Milou, Capitaine Haddock dans Le Lotus bleu
    (6, 1), (6, 2), (6, 3), -- Tintin, Milou, Capitaine Haddock dans L'Oreille cassée
    (7, 1), (7, 2), (7, 3), -- Tintin, Milou, Capitaine Haddock dans L'Île noire
    (8, 1), (8, 2), (8, 3), -- Tintin, Milou, Capitaine Haddock dans L'Étoile mystérieuse
    (9, 1), (9, 2), (9, 3), -- Tintin, Milou, Capitaine Haddock dans Le Secret de La Licorne
    (10, 1), (10, 2), (10, 3); -- Tintin, Milou, Capitaine Haddock dans Le Trésor de Rackham le Rouge

-- Ajouter 10 citations du Capitaine Haddock avec l'album et la page
INSERT INTO Citations (texte, personnage_id) VALUES
    ('Mille milliards de mille sabords !', 3), -- Capitaine Haddock dans Tintin au pays des Soviets
    ('Tonnerre de Brest !', 3), -- Capitaine Haddock dans Tintin au pays des Soviets
    ('Mille milliards de mille tonnerres !', 3), -- Capitaine Haddock dans Tintin au pays des Soviets
    ('Mille sabords !', 3), -- Capitaine Haddock dans Tintin au Congo
    ('Tonnerre de Brest et de brume !', 3), -- Capitaine Haddock dans Tintin au Congo
    ('Mille milliards de mille typhons !', 3), -- Capitaine Haddock dans Tintin en Amérique
    ('Mille milliards de mille boulons !', 3), -- Capitaine Haddock dans Les Cigares du Pharaon
    ('Mille milliards de mille mille mille sabords !', 3), -- Capitaine Haddock dans Le Lotus bleu
    ('Mille milliards de mille sabords de tonnerre !', 3), -- Capitaine Haddock dans L'Oreille cassée
    ('Mille milliards de mille tonnerres de Brest !', 3); -- Capitaine Haddock dans L'Île noire

-- Associer les citations aux albums et aux pages
-- Utilisez les IDs des citations et des albums correspondants
INSERT INTO Citations_Albums (citation_id, album_id, page) VALUES
    (1, 1, 5),
    (2, 1, 10),
    (3, 1, 15),
    (4, 2, 3),
    (5, 2, 8),
    (6, 3, 10),
    (7, 4, 7),
    (8, 5, 12),
    (9, 6, 5),
    (10, 7, 9);

-- Ajouter les lecteurs à la table Lecteurs
INSERT INTO Lecteurs (nom, prenom, email)
VALUES
    ('Dévore', 'Page', 'page.devore@example.com'),
    ('Chapitre', 'Premier', 'premier.chapitre@example.com'),
    ('Spontanée', 'Lecture', 'lecture.spontanee@example.com');

Ajoutons les lectures suivantes pour Dévore Page; Tintin en Amérique, L’Île noire et Le Crabe aux pinces d’or.

-- Ajout des lectures pour Dévore Page (lecteur_id = 1)
INSERT INTO Lectures (lecteur_id, album_id) VALUES
    (1, 3),  -- Tintin en Amérique
    (1, 7),  -- L'Île noire
    (1, 9);  -- Le Crabe aux pinces d'or

-- -----------------------------------------------------------------
-- Ou bien, à partir du nom des albums:

-- Sélectionner les identifiants d'album pour les albums spécifiques
DECLARE
    v_album_tintin_en_amerique NUMBER;
    v_album_l_ile_noire NUMBER;
    v_album_le_crabe_aux_pinces_d_or NUMBER;
BEGIN
    SELECT album_id INTO v_album_tintin_en_amerique FROM Albums WHERE titre = 'Tintin en Amérique';
    SELECT album_id INTO v_album_l_ile_noire FROM Albums WHERE titre = 'L''Île noire';
    SELECT album_id INTO v_album_le_crabe_aux_pinces_d_or FROM Albums WHERE titre = 'Le Crabe aux pinces d''or';

    -- Ajout des lectures pour Dévore Page
    INSERT INTO Lectures (lecteur_id, album_id) VALUES
        (1, v_album_tintin_en_amerique),
        (1, v_album_l_ile_noire),
        (1, v_album_le_crabe_aux_pinces_d_or);
END;
/

-- ---------------------------------------------------------------------
-- Ou encore:
DECLARE
    v_lecteur_id NUMBER;
BEGIN
    SELECT lecteur_id INTO v_lecteur_id FROM Lecteurs WHERE nom = 'Dévore' AND prenom = 'Page';
    
    -- Ajouter les lectures pour "Dévore Page"
    INSERT INTO Lecteurs_Albums (lecteur_id, album_id)
    SELECT v_lecteur_id, album_id FROM Albums WHERE titre IN ('Tintin en Amérique', 'L''Île noire', 'Le Trésor de Rackham le Rouge');
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Lecteur "Dévore Page" non trouvé.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Erreur lors de l''insertion des lectures : ' || SQLERRM);
        ROLLBACK;
END;
/

Ajoutons les lectures suivantes pour Chapitre Premier; Le Secret de La Licorne, Tintin au pays des Soviets

-- Obtenir l'identifiant du lecteur "Chapitre Premier"
DECLARE
    v_lecteur_id NUMBER;
BEGIN
    SELECT lecteur_id INTO v_lecteur_id FROM Lecteurs WHERE nom = 'Chapitre' AND prenom = 'Premier';

    -- Obtenir les identifiants des albums
    DECLARE
        v_album_id_sl NUMBER;
        v_album_id_tps NUMBER;
    BEGIN
        SELECT album_id INTO v_album_id_sl FROM Albums WHERE titre = 'Le Secret de La Licorne';
        SELECT album_id INTO v_album_id_tps FROM Albums WHERE titre = 'Tintin au pays des Soviets';

        -- Ajouter les lectures pour "Chapitre Premier"
        INSERT INTO Lecteurs_Albums (lecteur_id, album_id)
        VALUES
            (v_lecteur_id, v_album_id_sl), -- Lecture de "Le Secret de La Licorne"
            (v_lecteur_id, v_album_id_tps); -- Lecture de "Tintin au pays des Soviets";
            
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Album non trouvé.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Erreur lors de l''insertion des lectures : ' || SQLERRM);
            ROLLBACK;
    END;
END;
/

-- Afficher toutes les citations du Capitaine Haddock

SELECT
    P.nom AS nom_personnage,
    C.personnage_id,
    C.texte AS citation_texte
FROM
    Citations C
JOIN
    Personnages P ON C.personnage_id = P.personnage_id
WHERE
    C.personnage_id = (
        SELECT personnage_id
        FROM Personnages
        WHERE nom = 'Capitaine Haddock'
    );

-- Afficher le nom des lecteurs et le nom des albums lus trié par lecteur
SELECT
    l.nom    AS nom_lecteur,
    l.prenom AS prenom_lecteur,
    a.titre  AS titre_album
FROM
    lecteurs l
    JOIN lecteurs_albums la ON l.lecteur_id = la.lecteur_id
    JOIN albums          a  ON la.album_id = a.album_id
ORDER BY
    l.nom,
    l.prenom,
    a.titre;

-- Afficher le nombre de lectures des albums
SELECT
    substr(a.titre, 1, 40) AS titre_album,
    COUNT(la.album_id)     AS nombre_de_lectures
FROM
    albums          a
    LEFT JOIN lecteurs_albums la ON a.album_id = la.album_id
GROUP BY
    substr(a.titre, 1, 40)
ORDER BY
    nombre_de_lectures DESC,
    titre_album;