420-5D3 : Le SGBD Oracle

15 septembre 2023

1 – Présentation

Oracle est un incontournable dans les DB des grandes organisations. Son SGBD occupe la troisième place dans les parts de marché global. On retrouve souvent le SGBD Oracle dans des entreprises de 10 000+ employés.

1.1 – Oracle Multitenant

« Oracle Multitenant permet à une base de données Oracle Database de fonctionner comme une base de données de conteneurs (CDB). Une base de données de conteneurs consolide plusieurs bases de données enfichables (PDB), une collection portable de schémas, d’objets de schéma et d’objets autres que de schéma. Qu’elles soient déployées on-premise ou dans le cloud, avec Oracle Multitenant, les applications s’exécutent sans changement dans des bases de données enfichables autonomes, ce qui permet d’améliorer l’utilisation des ressources, la gestion et la sécurité globale. »

Source

Définition des concepts ici et là.

1.2 – Part de marché d’Oracle

Voir ici

2 – Installation

https://www.oracle.com/ca-en/database/free/get-started/

2.1 – Lancer le SGBD Oracle via Docker

docker pull container-registry.oracle.com/database/free:latest

# En date du 2023.09.28, latest correspond à la version 23c.

docker run -d --name oracle -it -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=password -e ORACLE_CHARACTERSET=AL32UTF8 container-registry.oracle.com/database/free:latest

# Méthode avec un lien avec sur dossier local:
docker run -d --name oracle-db -it \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_PWD=password \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v ${PWD}/opt/oracle/oradata:/opt/oracle/oradata \
container-registry.oracle.com/database/free:latest

Référence du CHARACTERSET

2.2 – Déployer une VM

Suivre les instructions de cette page

3 – Lancer le client SQL (sqlplus)

Pour la gestion du SGBD, plusieurs méthodes de connexion au serveur Oracle sont disponibles.

Commençons par utiliser un client de type CLI, proposé par Oracle.

# Pour se connecter à la base de données de conteneurs (CDB):
$ docker exec -it oracle sqlplus sys/password@localhost:1521/FREE as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed Sep 20 15:56:38 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL>

3.1 – Syntaxe de la commande de connexion

3.1.2 – Une base de données enfichable – PDB – est aussi disponible avec l’installation de base:

# Pour se connecter la première BD enfichable (PDB):
sqlplus sys@localhost:1521/FREEPDB1 as sysdba

3.2 – Premiers pas avec le CLI sqlplus

# Afficher le nom du conteneur courant:
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
# Note: CDB$ROOT est le conteneur racine d'une infrastructure Oracle.
 
# Afficher les bases de données enfichables PDBS/PLUGGABLE DATABASES
SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 FREEPDB1                  READ WRITE NO


# Créer une base de données enfichable
SQL> CREATE PLUGGABLE DATABASE CSTJ4205D3 ADMIN USER etudiant IDENTIFIED BY password create_file_dest='/home/oracle';

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 FREEPDB1                  READ WRITE NO
     4 CSTJ4205D3                MOUNTED


# Lister le contenu de la table des utilisateurs:
SQL> select name from user$;

NAME
--------------------------------------------------------------------------------
ACCHK_READ
ADM_PARALLEL_EXECUTE_TASK
ANONYMOUS
...
AUDSYS
AUTHENTICATEDUSER

NAME
--------------------------------------------------------------------------------
AVTUNE_PKG_ROLE
BDSQL_ADMIN
BDSQL_USER
...
DATAPUMP_IMP_FULL_DATABASE
DBA
...

# Rechercher ETUDIANT
SQL> select name from user$ where name='ETUDIANT';

no rows selected

# Ou est ETUDIANT ??

NOTE: La requête « select name from user$ where name=’etudiant’; » n’a pas retourné d’enregistrement, pourquoi?

Nous avons pourtant créé une nouvelle base de données ‘CSTJ4205D3‘ avec un compte administrateur ‘etudiant‘.

Justement, le compte ‘etudiant‘ n’est pas dans la BD courante mais bien dans la BD ‘CSTJ4205D3‘.

Il faut se connecter sur cette dernière pour pouvoir localiser l’utilisateur ‘etudiant’.

Procédons

4 – Branchement à une BD spécifique

docker exec -it oracle sqlplus sys/password@localhost:1521/CSTJ4205D3 as sysdba

# Afficher le nom du conteneur courant:
SQL> show con_name;

CON_NAME
------------------------------
CSTJ4205D3


# Afficher les bases de données
SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 4 CSTJ4205D3			  READ WRITE NO

 

# Afficher la liste des utilisateurs:
SQL> select name from user$;

NAME
--------------------------------------------------------------------------------
ACCHK_READ
ADM_PARALLEL_EXECUTE_TASK
ANONYMOUS
...

NAME
--------------------------------------------------------------------------------
DV_STREAMS_ADMIN
DV_XSTREAM_ADMIN
EJBCLIENT
ETUDIANT
...

# =============================================================================
#                          NOTE: EN CAS D`ERREUR
# =============================================================================

SQL> select name from user$;
                 *
ERROR at line 1:
ORA-01219: Database or pluggable database not open. Queries allowed on fixed
tables or views only.

Il faudra OUVRIR la base de données:

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 CSTJ4205D3                     READ WRITE NO
SQL>
# =============================================================================
# =============================================================================


# Localiser ETUDIANT dans la table user$
SQL> select name from user$ where name='ETUDIANT';

NAME
--------------------------------------------------------------------------------
ETUDIANT

SQL>

4.1 – Accorder des droits à l’utilisateur ‘ETUDIANT’

SQL> GRANT create session TO etudiant;
Grant succeeded.

SQL> GRANT create table TO etudiant;
Grant succeeded.

SQL> GRANT unlimited tablespace TO ETUDIANT;
Grant succeeded.

SQL>

L’utilisateur va maintenant pour créer des sessions (login), créer des tables et insérer des enregistrements. Par contre, il ne pourra pas créer des séquences (Champs de type AUTO-INCREMENT). Il faudra, plus tard ajouter un privilège supplémentaire.

Pour tester ses nouveaux privilèges, il va falloir se connecter avec le compte ‘etudiant’.

NOTE: La liste des privilèges systèmes est disponible ici.

4.1.1 – Utilisation du compte ‘etudiant’

SQL> exit

# Noter le nom du compte dans la chaine de connexion:
docker exec -it oracle sqlplus etudiant/password@localhost:1521/CSTJ4205D3

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed Sep 20 15:16:14 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue Sep 19 2023 23:47:32 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
CSTJ4205D3

SQL> show user;
USER is "ETUDIANT"

4.2 – Créer un nouvelle table (via le compte ETUDIANT)

SQL> CREATE TABLE TBL_COURS (NUMERO VARCHAR2(20) NOT NULL, NOM VARCHAR2(60) NOT NULL, PERIODES NUMBER NOT NULL, CONSTRAINT TBL_COURS_PK PRIMARY KEY (NUMERO)  ENABLE );

Table created.

# Lister les tables:
SQL> SHOW TABLES;
SP2-0158: unknown SHOW option "TABLES"
# VOIR NOTE

SQL> SELECT table_name FROM user_tables ORDER BY table_name;

TABLE_NAME
--------------------------------------------------------------------------------
TBL_COURS

NOTE: Contrairement à MySQL, il n’est pas possible d’utiliser la syntaxe ‘SHOW tables;‘ avec Oracle. Il faut plutôt faire un ‘select‘ sur la table système ‘user_tables‘. Référence

4.3 – Ajouter un enregistrement dans la table TBL_COURS:

Rappel de la syntaxe:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
SQL> INSERT INTO tbl_cours VALUES ('420-5D3', 'Sécurité des données', 3);
1 row created.


SQL> select * from tbl_cours;

NUMERO                NOM                             PERIODES
-------------------- -------------------------------- ---------
420-5D3              Sécurité des données	      3

SQL>

NOTE: Une modification d’une table dans une session, ne sera pas automatiquement disponible sur d’autres sessions. Pour synchroniser les changements, il faut faire :

SQL> COMMIT WORK;

Commit complete.

4.3.2 – À FAIRE (DÉFI) – Trouver une solution pour les caractères étendus non-affichés.

Dans une autre leçon, nous apprendrons à utiliser une application de type GUI, Oracle SQL Developer, pour la gestion du SGBD d’Oracle

4.4 – Changer de contexte

Il est possible de changer de context (PDB) avec ALTER SESSION

ALTER SESSION SET CONTAINER = bd_exemple;
ALTER SESSION SET CONTAINER = CSTJ4205D3;
ALTER SESSION SET CONTAINER = CDB$ROOT;  -- La racine de l'infrastructure

Au besoin, se connecter au nouveau contexte
connect sys as sysdba;

ALTER DATABASE OPEN;
CREATE USER OT IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, DBA TO OT;
CONNECT ot@bd_exemple  -- le context doit-être nommé

-- Pour exécuter un script SQL:
SQL>@c:\dbsample\ot_schema.sql

5 – LABORATOIRE

Nous avons une grande collection des oeuvres d’Hergé, plus précisément des albums de Tintin. Nous aimerions tenir une base de données complète des ouvrages d’Hergé et pourvoir interroger la BD avec des requêtes du type « Afficher les albums dans lesquels nous retrouvons le Capitaine Haddock« .

Suggestion avant de commencer, voir l’annexe A02

Étape 01

En utilisant le client ‘sqlplus‘ d’Oracle,

Étape 02

A) En utilisant une session sqlplus avec l’utilisateur TINTIN, créer les tables suivantes:

B) Insérer les personnages suivants:

C) Insérer les albums suivants:

D) Renseigner les informations suivantes:

E) Afficher les personnages : Nom et description

F) Afficher les albums : Année, Titre, nombre de pages

G) Afficher la liste des albums par personnage. (Cela est un peu plus difficile 😉)

Annexe

Note: CTRL F7 = Mise en forme (format)

A01 – Renseigner un champ en mode ‘AUTO INCREMENT’ Sous Oracle:

# Note, pas de clause 'IF EXISTS' sous Oracle 23c
# Voir: https://stackoverflow.com/questions/1799128/oracle-if-table-exists

DROP TABLE IF EXISTS tbl_animaux;

CREATE TABLE tbl_animaux (
     id NUMBER GENERATED AS IDENTITY,
     nom CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

# Oui bien :

CREATE TABLE tbl_animaux (
     id NUMBER GENERATED AS IDENTITY (START WITH 1 INCREMENT BY 1 NOCACHE),
     nom VARCHAR2(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO tbl_animaux (nom) VALUES
    ('chien'),('chat'),('cheval'),
    ('poisson'),('oiseau'),('serpent');

SELECT * FROM tbl_animaux;

A02 – Exemple d’un schema pour un site de commerce

A02.1 – Création du schéma – syntaxe ‘legacy Oracle’
Note: NE PAS UTILISER CETTE SYNTAXE DANS LE COURS.

-- Exemple utilisant la syntaxe 'legacy' pour les valeurs de type AUTO-INCREMENT.  
-- Fonctionne avec les versions plus anciennes d'Oracle

-- Drop tables if they exist (to avoid conflicts)
DROP TABLE OrderItems CASCADE CONSTRAINTS;
DROP TABLE Orders CASCADE CONSTRAINTS;
DROP TABLE Products CASCADE CONSTRAINTS;
DROP TABLE Categories CASCADE CONSTRAINTS;
DROP TABLE Customers CASCADE CONSTRAINTS;

-- Create Customers Table
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100) UNIQUE,
    password VARCHAR2(100), -- Hashed and salted password
    address VARCHAR2(255),
    phone_number VARCHAR2(20)
);

-- Create Categories Table
CREATE TABLE Categories (
    category_id NUMBER PRIMARY KEY,
    category_name VARCHAR2(100) UNIQUE
);

-- Create Products Table
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    description CLOB,
    price NUMBER(10, 2),
    stock_quantity NUMBER,
    category_id NUMBER REFERENCES Categories(category_id)
);

-- Create Orders Table
CREATE TABLE Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER REFERENCES Customers(customer_id),
    order_date TIMESTAMP,
    status VARCHAR2(20) CHECK (status IN ('processing', 'shipped', 'delivered'))
);

-- Create OrderItems Table (to represent items in each order)
CREATE TABLE OrderItems (
    order_item_id NUMBER PRIMARY KEY,
    order_id NUMBER REFERENCES Orders(order_id),
    product_id NUMBER REFERENCES Products(product_id),
    quantity NUMBER,
    subtotal NUMBER(10, 2)
);

-- Create Indexes for Performance
CREATE INDEX idx_customer_email ON Customers(email);
CREATE INDEX idx_product_category ON Products(category_id);
CREATE INDEX idx_order_customer ON Orders(customer_id);
CREATE INDEX idx_order_product ON OrderItems(product_id);

-- Create Sequences for Auto-Generating IDs
CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE category_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE order_item_seq START WITH 1 INCREMENT BY 1;

A02.2 – Création du schéma – syntaxe ‘moderne’

-- Exemple utilisant la syntaxe 'moderne' pour les valeurs de type AUTO-INCREMENT. 

-- Drop tables if they exist (to avoid conflicts)
DROP TABLE OrderItems CASCADE CONSTRAINTS;
DROP TABLE Orders CASCADE CONSTRAINTS;
DROP TABLE Products CASCADE CONSTRAINTS;
DROP TABLE Categories CASCADE CONSTRAINTS;
DROP TABLE Customers CASCADE CONSTRAINTS;

-- Create Customers Table
CREATE TABLE Customers (
    customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100) UNIQUE,
    password VARCHAR2(100), -- Hashed and salted password
    address VARCHAR2(255),
    phone_number VARCHAR2(20)
);

-- Create Categories Table
CREATE TABLE Categories (
    category_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_name VARCHAR2(100) UNIQUE
);

-- Create Products Table
CREATE TABLE Products (
    product_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR2(100),
    description CLOB,
    price NUMBER(10, 2),
    stock_quantity NUMBER,
    category_id NUMBER REFERENCES Categories(category_id)
);

-- Create Orders Table
CREATE TABLE Orders (
    order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id NUMBER REFERENCES Customers(customer_id),
    order_date TIMESTAMP,
    status VARCHAR2(20) CHECK (status IN ('processing', 'shipped', 'delivered'))
);

-- Create OrderItems Table (to represent items in each order)
CREATE TABLE OrderItems (
    order_item_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id NUMBER REFERENCES Orders(order_id),
    product_id NUMBER REFERENCES Products(product_id),
    quantity NUMBER,
    subtotal NUMBER(10, 2)
);

A02.3 – Insérer des données dans la BD

-- Ajout de données dans la BD

-- Insert data into Categories Table
INSERT INTO Categories (category_name) VALUES
    ('Electronics'),
    ('Clothing'),
    ('Books');

-- Insert data into Customers Table
INSERT INTO Customers (first_name, last_name, email, password, address, phone_number) VALUES
    ('John', 'Doe', 'johndoe@example.com', 'hashed_password', '123 Main St, City, Country', '555-123-4567'),
    ('Jane', 'Smith', 'janesmith@example.com', 'hashed_password', '456 Elm St, City, Country', '555-987-6543');

-- Insert data into Products Table
INSERT INTO Products (product_name, description, price, stock_quantity, category_id) VALUES
    ('Smartphone', 'High-end smartphone with great features', 699.99, 100, 1),
    ('Laptop', 'Powerful laptop for work and gaming', 1299.99, 50, 1),
    ('T-shirt', 'Comfortable cotton T-shirt', 19.99, 200, 2),
    ('Jeans', 'Stylish and durable jeans', 49.99, 150, 2),
    ('Book: Introduction to SQL', 'Learn SQL programming', 29.99, 30, 3);

-- Insert data into Orders Table
INSERT INTO Orders (customer_id, order_date, status) VALUES
    (1, TIMESTAMP '2023-09-26 10:00:00', 'processing'),
    (2, TIMESTAMP '2023-09-27 14:30:00', 'shipped');

-- Insert data into OrderItems Table
INSERT INTO OrderItems (order_id, product_id, quantity, subtotal) VALUES
    (1, 1, 2, 1399.98),
    (1, 3, 3, 59.97),
    (2, 2, 1, 1299.99);

-- ---------------------------------------------------------------------
-- Encore un peu plus ...
-- ---------------------------------------------------------------------

-- Insert data into Categories Table
INSERT INTO Categories (category_name) VALUES
    ('Toys'),
    ('Sports Gear'),
    ('Home Appliances');

-- Insert data into Customers Table
INSERT INTO Customers (first_name, last_name, email, password, address, phone_number) VALUES
    ('Alice', 'Johnson', 'alice@example.com', 'hashed_password', '789 Oak St, City, Country', '555-555-5555'),
    ('Bob', 'Smith', 'bob@example.com', 'hashed_password', '321 Cedar St, City, Country', '555-123-7890');

-- Insert data into Products Table
INSERT INTO Products (product_name, description, price, stock_quantity, category_id) VALUES
    ('Action Figure', 'Collectible action figure', 24.99, 50, 1),
    ('Soccer Ball', 'Official size and weight soccer ball', 19.99, 100, 2),
    ('Coffee Maker', 'Programmable coffee maker', 49.99, 30, 3),
    ('Dress', 'Elegant evening dress', 79.99, 70, 2),
    ('Book: Python Programming', 'Learn Python programming', 39.99, 25, 3);

-- Insert data into Orders Table
INSERT INTO Orders (customer_id, order_date, status) VALUES
    (3, TIMESTAMP '2023-09-28 11:15:00', 'processing'),
    (4, TIMESTAMP '2023-09-29 16:45:00', 'delivered');

-- Insert data into OrderItems Table
INSERT INTO OrderItems (order_id, product_id, quantity, subtotal) VALUES
    (3, 4, 1, 79.99),
    (3, 5, 2, 79.98),
    (4, 3, 1, 49.99),
    (4, 1, 4, 99.96);

A02.4 – Quelques requêtes

A02.4.1 – Obtenir la liste des commandes détaillées, par client:

SELECT
    o.order_id,
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.order_date,
    o.status,
    p.product_name,
    oi.quantity,
    oi.subtotal
FROM
    Orders o
JOIN
    Customers c ON o.customer_id = c.customer_id
JOIN
    OrderItems oi ON o.order_id = oi.order_id
JOIN
    Products p ON oi.product_id = p.product_id
ORDER BY
    o.order_id, oi.order_item_id;

A02.4.2 – Liste des status des commandes, incluent le nom des clients:

SELECT o.order_id,
       c.first_name || ' ' || c.last_name AS customer_name,
       o.status
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id;

A02.4.3 – Liste des clients qui ont acheté un produit plus d’une fois:

SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    p.product_id,
    p.product_name
FROM
    Customers c
JOIN
    Orders o ON c.customer_id = o.customer_id
JOIN
    OrderItems oi ON o.order_id = oi.order_id
JOIN
    Products p ON oi.product_id = p.product_id
GROUP BY
    c.customer_id, c.first_name, c.last_name, p.product_id, p.product_name
HAVING
    COUNT(DISTINCT oi.product_id) > 1;

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 :

Personnages :

Citations :

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

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

Lecteurs :

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

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;

Docum externe

Auto Incrément avec Oracle pour les anciennes versions

NOTE: L’utilisateur doit posséder le privilège système ‘CREATE SEQUENCE’ pour utiliser la clause ‘GENERATED AS IDENTITY’.