Le cours est ici

Sources: https://monlyceenumerique.fr/nsi_terminale/bdd/bdd2.php

Base de données relationnelles - SGBD⚓︎

Nous avons défini le modèle relationnel. Pour utiliser ce modèle, on utilise comme interface entre l'utilisateur et les données, un système de gestion de bases de données que l'on nomme par l'acronyme SGBD (système de gestion de base de données). Un SGBD permet de trouver, de modifier, de trier, de transformer des données contenues dans la base de données. Il possède un langage (on parle de requêtes) qui permet de traduire tous les ordres de l'usager. Les SGBD utilisent un langage de requêtes qui permet d'interroger et de manipuler la base de données. Nous utiliserons le langage SQL (Structured Query Language : langage de requêtes structurées) qui est largement répandu (mais il en existe d'autres).

Il existe différents types de SGBD : hiérarchiques, relationnels, orientés objet, etc.

Dans le programme de terminale NSI, nous nous intéressons aux SGBD relationnels.

Le vocabulaire utilisé dans un SGBD diffère du vocabulaire utilisé dans le modèle relationnel. Voici un tableau qui va vous permettre de vous y retrouver. Attention toutefois, les notions ne sont pas équivalentes.

Modèle relationnel	SGBD
relation	table
attributs	colonnes
enregistrements	lignes

Il existe de nombreux SGBD. Certains SGBD sont payants, d'autres sont libres. Les différences entre les SGBD se font également en fonction du nombre de données traitées ainsi que son hébergement. Certains SGBD sont des logiciels (voire une composante d'un logiciel), d'autres des serveurs. Voici une liste non exhaustive :

Apache Derby
DB2
dBase
MariaDB
Microsoft SQL server
MySQL
Oracle Database
PostgreSQL
SQLite

1. Rôle et fonctions d'un SGBD⚓︎

Un SGBD doit assurer un ensemble de fonctions :

Le SGBD doit assurer la persistance des données. Il doit assurer la pérennité de la structure quelque soit les modifications. De plus l'accès aux données doit être garanti en toute circonstance : panne matérielle, logicielle, coupure de courant, etc.
Le SGBD doit gérer les accès concurrents. Tout comme le système d'exploitation Linux étudié en première, il gère un système de droits et de privilèges pour des utilisateurs et des groupes d'utilisateurs.
Le SGBD doit être efficace. Cette efficacité tient à des algorithmes puissants qui ne sont pas 'visibles' par les utilisateurs.

2. Langage SQL pour la définition des données⚓︎

Nous avons vu que notre base de données est un ensemble de schémas que nous pouvons implanter par la création de tables.

Créer une base de données n'est pas explicitement au programme de Terminale NSI, nous allons simplement étudier les ordres qui permettent d'implémenter d'un point de vue logiciel une base de données.

Lors de la création d'une table, on peut :

lui donner un nom
indiquer ses attributs
indiquer les différents types des attributs
indiquer les contraintes d'intégrité (clés primaires, clés étrangères, contraintes de domaines, etc)
...

Le langage SQL utilise des mots clés et une syntaxe à respecter. Par exemple la commande qui permet la création de la table series :

CREATE TABLE serie (
    idSerie int NOT NULL,
    nomSerie varchar(30) NOT NULL,
    desSerie TEXT,
    AnneeSerie DATE,
    nbSaison int NOT NULL,
    PRIMARY KEY (idSerie)
  );

Vous remarquerez le ; à la fin de la commande. Les espaces et l'indentation n'ont pas d'incidence, cela facilite simplement la lecture.

Exercice: En observant la syntaxe de la création de la table serie, indiquer les attributs, les domaines et les contraintes d'intégrité de cette table.

3. Création de tables⚓︎

Info

Pour créer une table, on utilise les mots clés CREATE TABLE. La syntaxe est :

CREATE TABLE nom_table( attribut_1 domaine_1 contrainte_1,
                        attribut_2 domaine_2 contrainte_2,
                        ...
                        attribut_n domaine_n contrainte_n,
                        ...
                        contrainte_globale);

Remarques

le langage SQL est insensible à la casse,
dans un script, si vos utilisez plusieurs commandes SQL, il faut écrire un ; à la fin de chaque ordre,
une convention d'écriture : écrire le nom des tables au singulier,
les contraintes pour les attributs et les contraintes globales ne sont pas obligatoires.

Exemple: voir au dessus

3.1. Domaines: types de données en SQL⚓︎

Nom du type/mot clé	Description
SMALLINT	Entier de 16 bits signés (valeur exacte)
INTEGER	Entier de 32 bits signés (valeur exacte)
INT	Alias pour INTEGER
BIGINT	Entier de 64 bits signés (valeur exacte)
DECIMAL(t,f)	Décimal signé de t chiffres dont f chiffres arès la virgule (valeur exacte)
REAL	Flottant de 32 bits (valeur approchée)
DOUBLE PRECISION	Flottant de 32 bits (valeur approchée)
CHAR(n)	Chaîne de \(n\) caractères
VARCHAR(n)	Chaîne d'au plus \(n\) caractères
TEXT	Chaîne de taille quelconque
BOOLEAN	Type booléen parfois non supportés
DATE	Date au format 'AAAA-MM-JJ'
TIME	Heure au format 'hh:mm:ss'
TIMESTAMP	Un instant (date et heure) au format 'AAAA-MM-JJ hh:mm:ss'
NULL	Il existe une valeur NULL (comme None en Python)

3.2. Contraintes⚓︎

Nom	Mot-clé	Explications
Clé primaire	PRIMARY KEY	idEleve INT PRIMARY KEY(lors de la déclaration de l'attribut) ou PRIMARY KEY(idEleve) (lors des contraintes globales)
Clé étrangère	REFERENCES nom_table (parfois avec FOREIGN KEY)	Nom_attribut domaine_attribut REFERENCES nom_table(nom_attribut)
Unicité d'un attribut	UNIQUE	Parfois utiliser pour un attribut qui n'est pas une clé primaire (une adresse email par exemple).
Obliger à la non nullité	NOT NULL	Pour un nom par exemple.
Contrainte utilisateur	CHECK	Par exemple pour imposer un âge positif (ou la majorité). Se déclare dans les contraintes globales. Exemple : CHECK (age >=0)

3.3 Exemple⚓︎

CREATE TABLE Realisateur (
    idReal int NOT NULL PRIMARY KEY,
    nom varchar(30) NOT NULL,
    prenom varchar(30) NOT NULL,
    anneeNaissance DATE
  );
CREATE TABLE Film (
    idFilm int NOT NULL PRIMARY KEY,
    nom varchar(30) NOT NULL,
    description TEXT,
    annee DATE,
    idReal int NOT NULL REFERENCES Realisateur(idReal)
  );

4. Suppression de tables⚓︎

Info

La commande qui permet de supprimer une table est la commande DROP TABLE, dont la syntaxe est:

DROP TABLE nom_table;

Remarque Il faut faire attention aux contraintes de référencement sous peine d'avoir un message d'erreur qui spécifie que la suppression n'a pas été effectuée. Cela arrive lorsque votre table sert de référence pour une clé étrangère dans une autre table.

5. Insertion dans un table⚓︎

Info

Pour insérer des valeurs, nous utiliserons la commande INSERT INTO dont la syntaxe est:

```sql
INSERT INTO nom_table VALUES (valeur_1, valeur_2, ...);
```

ou

```sql
INSERT INTO nom_table(attribut_1, attribut_2, ...) VALUES (valeur_1, valeur_2, ...);
```

remarques:

Les contraintes d'intégrité sont vérifiées au moment de l'insertion.
Dans le cas d'insertion sans nommer les attributs les valeurs doivent être indiquées dans l'ordre définie dans la table

exemple: Suite de l'exemple

INSERT INTO Realisateur VALUES (1, 'LUCAS', 'GEORGES', 1944);
INSERT INTO Film VALUES (1, "L'Empire contre-attaque", '', 1980, 1);

6. Python et SQL⚓︎

Il existe une API (Application Programming Interface) en langage python qui s'appelle sqlite3 qui permet d'utiliser le langage SQL (https://docs.python.org/fr/3/library/sqlite3.html)

Example On considère le schéma relationnel : Artiste(#idArtiste,nom,prénom,annéeNaiss) avec :

idArtiste comme clé primaire de la relation Artiste,
nom, prénom et annéeNaiss sont trois attributs de la relation Artiste.

Observer et analyser le code ci-dessous :

import sqlite3


bdd = sqlite3.connect(":memory:") #BDD dans la RAM
cur = bdd.cursor() # Récupération d'un curseur, méthode qui permet de parcourir la base de données
cur.execute("PRAGMA foreign_keys = 1") # 
# création des tables
cur.execute("""
CREATE TABLE Film (
    idFilm int NOT NULL PRIMARY KEY,
    nom varchar(30) NOT NULL,
    description TEXT,
    annee DATE,
    idReal int NOT NULL REFERENCES Realisateur(idReal)
  );
""")
cur.execute("""
CREATE TABLE Realisateur (
    idReal int NOT NULL PRIMARY KEY,
    nom varchar(30) NOT NULL,
    prenom varchar(30) NOT NULL,
    anneeNaissance DATE
  );
""")
cur.execute("INSERT INTO Realisateur VALUES (1, 'LUCAS', 'GEORGES', 1944);")
cur.execute("""INSERT INTO Film VALUES (1, "L'Empire contre-attaque", '' , 1980, 1);""")
bdd.commit() # Soumettre l'ordre
requete = " SELECT * FROM Film"  # Notre requête, afficher tous les films de la table Film
cur.execute(requete)
# Affichage des résultats
for element in cur :
    print(element)


requete = " SELECT * FROM Realisateur"
cur.execute(requete)
# Affichage des résultats
for element in cur :
    print(element)
cur.close()
bdd.close() #Déconnexion

La ligne bdd = sqlite3.connect(":memory:") permet de stocker les données de la base de données dans la RAM dans un objet appelé ici bdd.

Ceci a un défaut : les données sont perdues lors de la déconnexion (avec bdd.close()) et lors de l'arrêt de l'ordinateur.
Par contre, ceci a deux avantages :
- L'accès à la RAM est plus rapide que celui à un disque dur,
- En cas de test, les essais sont reproductibles (car repartent toujours de la même base) et la base de données initiale n'est pas altérée.
Si vous préferez travailler directement sur la base de données, vous pouvez directement y accéder en plaçant l'emplacement du fichier comme paramètre de la fonction connect avec comme code, par exemple, bdd = sqlite3.connect(ma_base_de_donnees.db)
Les lignes 27 bdd.commit() servent à valider les modifications faites sur la table : celle-ci est dès lors modifiée.
Il est possible de revenir à l'état précédent la dernière modification issue d'un commit avec l'instruction bdd.rollback(). Ainsi, les modifications dues à ce commit sont annulées et perdues.
À la ligne 5 dans cur = bdd.cursor(), cursor est une méthode qui permet de créer un objet cur (un "curseur") à partir de la base de données importées sous l'objet bdd. C'est sur cet objet cur que les requêtes s'appliquent, non pas directement sur l'objet bdd.
Pour exécuter une requête sur un objet de type "curseur", il suffit d'appliquer la méthode execute suivi du code en langage SQL écrit entre des '. (cf. le code des lignes 9 à 14)