Tle NSI / Cours

Les systèmes de gestion de bases de données relationnelles

Description

Un système de gestion de bases de données relationnelle (SGBDR) est un logiciel conçu pour interagir (stocker, manipuler, gérer) avec une base de données relationnelle : Il sert d'intermédiaire entre l'utilisateur et les données de la base de données.
Un tel système doit implémenter quatre opérations de base :

Create (créer)
Read ou Retrieve (lire)
Update (mettre à jour)
Delete ou Destroy (supprimer)

Ainsi, l'acronyme CRUD résume les fonctions dont un utilisateur ou gestionnaire a besoin pour créer et gérer des données. En français, on utilise parfois le sigle LMD (la création est sous-entendue) :

Lire
Modifier
Détruire

Logiciels et langage SQL

Il existe plusieurs logiciels SGBDR, tous basés sur CRUD :

MariaDB ou MySQL (environ 20% du marché) ;
Microsoft SQL Server (environ 20% du marché) ;
Oracle (environ 20% du marché) ;
PostgreSQL (environ 10% du marché) ;
MongoDB (environ 10% du marché) ;
SQLite (environ 5% du marché) ;
Microsoft Access ou LibreOffice Base (en lien avec la bureautique) ;
...

Ces logiciels utilisent le langage SQL (Structured Query Language, en français : langage de requêtes structurées), dont ils partagent les instructions de base mais présentent aussi des variations mentionnées dans leur documentation respective.

Services rendus par le SGBD

Au niveau conceptuel, les SGBD utilisent l'architecture Ansi/Sparc crée en 1975 par Charles Bachman, félicité par un prix Turing (équivalent d'un prix Nobel d'informatique).

Elle est divisée en trois niveaux, fréquentés par différents acteurs :

schémas externes (SE) pour les utilisateurs
Des informations de la BD sont présentées d'une manière différente et adaptée à chacun, à travers des applications spécifiques ou à l'aide d'un langage type SQL pour les utilisateurs les plus avancés.
schéma conceptuel (SC) pour les concepteurs/développeurs
C'est la vue de la totalité des informations de la BD, souvent organisée à l'aide du modèle entité-association.
Les développeurs créent des applications et des scripts destinées aux utilisateurs.
Les concepteurs stockent les données de manière appropriée en identifiant les relations entre ces données et les contraintes liées. Ils utilisent pour cela un SGBD.
schéma interne (SI) pour les administrateurs
C'est l'implémentation physique du SGBD et de la BD.
Les administrateurs installent et maintiennent le SGBD. Ils sont responsables de l'intégrité, de la sécurité, de la disponibilité des données ainsi que de la performance du SGBD.

Cette architecture permet de :

Séparer clairement le niveau physique (géré par les administrateurs), du niveau logique.
Créer des vues utilisateur distinctes : Un utilisateur ne peut manipuler ou lire que la portion de la base de données auquel il a accès, et sous une forme ergonomique.

Chap. 5 : Langage SQL / Création Mémorisation 0x Réussite 0/0 30s

Démarrer

Qu'est-ce que l'architecture « ANSI/SPARC » ?

Équivalente à N/PR lorsque I=C.

Une architecture théorique sur 3 niveaux pour construire des SGBD.

Un encodage des chaînes de caractères à respecter dans une base de données.

Une norme pour qu'un terminal puisse exécuter des requêtes SQL.

Cocher la bonne réponse.

Construction de la BD avec le langage SQL

Création d'une base de données sur un serveur MySQL

Installé sur un serveur, un SGBD traditionnel (Oracle, MySQL, MariaDB, etc) est capable de manipuler plusieurs bases de données et de fournir des accès aux utilisateurs. L'administrateur peut gérer les bases de données à l'aide des instructions SQL suivantes :

Opération	Instruction `SQL`
Création d'une BD	`CREATE DATABASE nom_base CHARACTER SET 'utf8'`
Suppression d'une BD	`DROP DATABASE nom_base`
Utilisation d'une BD	`USE nom_base`

Remarques :

Par convention, les mots clés sont écrits en majuscules.
La création ou la suppression d'une base nom_base est assez simple. Le codage de caractères le plus répandu est UTF8, et celui-ci est précisé lors de la création. Il existe des exceptions où l'on va utiliser de l'ASCII simple, ou de l'UTF32. Les bases de données contenant des caractères très spéciaux ne sont pas au programme. Pour utiliser une base de donnée, on doit l'ouvrir (USE).

Création d'une base de données SQLite3 en utilisant Python

Nous allons utiliser la bibliothèque sqlite3, qui permet d'utiliser une base de données légère directement sous Python (sans avoir à installer de serveur dédié).
Références :

sur python.doctor ;
la documentation.

On importe sqlite3 ; un connecteur permet de manipuler le fichier .db contenant la base. Le curseur permet de donner des instructions au connecteur :

import sqlite3

# S'il n'existe pas encore, un fichier .db sera créé lors de la connexion.
NOM_BASE = "ma_base_sqlite.db"

# L'objet «connecteur» permet de se connecter à la base (fichier .db).
connecteur = sqlite3.connect(NOM_BASE)

# Un objet «curseur» permet d'exécuter des instructions en langage SQL, par l'intermédiaire du connecteur.
curseur = connecteur.cursor()

# L'instruction suivante affiche les noms des tables de la base de données :
reponse = curseur.execute("SELECT name FROM sqlite_schema WHERE type='table' ;").fetchall()
print(reponse)

# ...

# On termine en fermant la connexion :
connecteur.close()

Utiliser ce script Python pour créer une base de donnée «base01» ;

On utilise :

curseur.execute("instruction_sql") pour exécuter une seule instruction SQL ;
curseur.executemany("instruction_sql", liste) pour exécuter plusieurs instructions dépendant d'une liste fournie en argument.

curseur.executescript("script_sql") pour exécuter un script d'instructions SQL. Voici des modalités d'implémentation :

mon_script_sql_1 = """
-- première possibilité : utiliser les triples quotes permet d'écrire
-- un script sql sur plusieurs lignes directement dans le script python.
"""
reponse_1 = curseur.executescript(mon_script_sql_1).fetchall()

# seconde possibilité : lire un fichier texte .sql
mon_script_sql_2 = ""
with open("mon_fichier.sql") as f:
    mon_script_sql_2 = f.readlines()

reponse_2 = curseur.executescript(mon_script_sql_2).fetchall()"

Construction des tables avec le langage SQL

Création d'une table : syntaxe globale

CREATE TABLE nom_table (
  nom_attribut_1 son_type_1, -- cf tables des types plus loin
  nom_attribut_2 son_type_2 eventuelle_contrainte,
  ...
  Contrainte_d'integrite_1(nom_attribut_concerné_1, attribut_concerné_2 options)
    -- cf table des contraintes plus loin
  ...
);

Créer une table dans la BD.

DROP TABLE nom_table

Supprimer une table de la BD.

C'est à la création de la table que l'on précise les attributs, leurs types et les contraintes. Le choix d'un type à un impact important sur la taille de la base de données et sa performance tandis que le choix des contraintes participe à maintenir l'intégrité des données.

On peut appliquer une contrainte (par exemple : NOT NULL directement sur l'attribut lors de sa déclaration ou regrouper toutes les contraintes à la fin en les appliquant à plusieurs attributs. L'important est d'être clair, pour faciliter la lecture du script par d'autres développeurs, et organisé pour ne rien oublier.

Les types MySQL et SQLite

Les types de données MySQL sont bien plus nombreux que les types de variable que vous connaissez en python. Cette grande variété sert à optimiser la taille d'une base de données.

NULL désigne le vide («non rempli»).

Types Entiers

Type	Min	Max	Taille en octet
`INT` ou `INTEGER`	-2147483648	2147483647	4
`TINYINT`	-128	127	1
`TINYINT UNSIGNED`	0	255	1
`SMALLINT`	-32768	32767	2
`SMALLINT UNSIGNED`	0	65535	2
`MEDIUMINT`	-8388608	8388607	3
`MEDIUMINT UNSIGNED`	0	16777215	3
`INT UNSIGNED` ou `INTEGER UNSIGNED`	0	4294967295	4
`BIGINT`	-9223372036854775808	9223372036854775807	8
`BIGINT UNSIGNED`	0	18446744073709551615	8

Remarque : MySQL n'a pas de type booléen propre : On peut utiliser BOOL mais cela revient à utiliser TINYINT(1). Les valeurs suivantes : true, false, TRUE, FALSE, True, False; sont interprétées respectivement comme 1, 0, 1, 0, 1, 0.

Types Nombres décimaux

Type	Min négatif	Max négatif	0	Min positif	Max positif	Taille en octet
`FLOAT`	-3.402823466E+38	-1.175494351E-38	0	1.175494351E-38	3.402823466E+38	4
`DOUBLE`	-1.7976931348623157E+308	-2.2250738585072014E-308	0	2.2250738585072014E-308	1.7976931348623157E+308	8

Types Texte

Les textes ou chaînes de caractères sont entre guillemets simples '...'. Pour écrire une apostrophe ' à l'intérieur d'une chaîne de caractères, on écrit '' (2 apostrophes).

Type	Taille en octet
`CHAR(X)` avec `X` le nombre de caractères	`X` (si chaque caractère est codé sur un seul octet) 255 au maximum
`VARCHAR(X)` avec `X` le nombre de caractères	≤`X` (si chaque caractère est codé sur un seul octet) 65532 au maximum
`TINYBLOB` ou `TINYTEXT`	≤255
`BLOB` données binaires brutes	≤65535
`TEXT` texte codé selon la base (souvent utf8)	≤65535
`MEDIUMBLOB` ou `MEDIUMTEXT`	≤16777215
`LONGBLOB` ou `LONGTEXT`	≤4294967295

Types Date

Type	Min	Max	Précision	Taille en octet	Représentation
`DATE`	1 Janvier 1000	31 Décembre 9999	jour	3	`AAAA-MM-JJ`
`DATETIME`	1 Janvier 1000 00h00m00s	31 Décembre 9999 23h59m59s	seconde	8	`AAAA-MM-JJ HH:MM:SS`
`TIME`	-838h59m59s	838h59m59s		5	`HH:MM:SS`
`YEAR`	1901	2155	année	4	`AAAA`

Gestion des types par SQLite

SQLite utilise un système de typage moins rigide que MySQL : le type d'un enregistrement est associé à l'enregistrement lui-même et non à l'attribut qui le contient.
SQLite emploie un système de règles permettant d'assurer une bonne compatibilité avec les instructions MySQL, mais il faut savoir que ses types de données se résument à :

NULL Champ vide.
INTEGER : Entier (avec signe) de 1 à 8 octets (équivalent de TINYINT à BIGINT).
REAL : valeur en virgule flottante IEEE 8 octets (équivalent de FLOAT).
TEXT : Texte (codage de caractères régi par la base : UTF-8, UTF-16BE or UTF-16LE).
BLOB : Données binaires brutes.

Par exemple, une date typée DATETIME sera stockée, en pratique, sous un type sql>TEXT, sous la forme YYYY-MM-DD HH:MM:SS.SSS (suivant la norme ISO8601).

Lors de l'écriture des scripts, on utilisera les types MySQL. Si une incompatibilité manifeste apparaît, on s'adaptera en utilisant les types utilisés par SQLite.

Chap. 5 : Langage SQL / Création Mémorisation 0x Réussite 0/0 30s

Démarrer

Cocher les types standards du langage SQL :

INTEGER

CHAR

TUPLE

__INIT__

ODB

VIDEO

IMAGE

Cocher la ou toutes les bonnes réponses.

Ajout des contraintes d'intégrité

Une contrainte d'intégrité est une règle qui définit la cohérence d'une donnée ou d'un ensemble de données au sein d'une base de données, afin de garantir leurs pertinence et leur validité.

`nom_attribut NOT NULL`	l'attribut ne peut pas avoir la valeur `NULL` (être vide)
`CHECK (nom_attribut BETWEEN a AND b)`	contrainte de domaine : ici, avoir des valeurs entre `a` et `b`
`UNIQUE (nom_attribut_1, ..., nom_attribut_n)`	les n-uplets d'attributs doivent être uniques
`PRIMARY KEY (nom_attribut_1, ..., nom_attribut_n)`	contrainte de relation : les attributs sont mis en clé primaire
`FOREIGN KEY (nom_attribut) REFERENCES nom_table(nom_attribut) ON DELETE CASCADE ON UPDATE CASCADE`	contrainte de référence : crée une clé étrangère les occurences sont mises à jour en cas de modification/suppression dans l'autre table ⚠ à écrire sur une même ligne en SQLite

Sans les remplir, construire les trois tables suivantes en langage SQL.
Remarques :

À vous de choisir judicieusement les types de chaque attribut !
Dans les noms d'attributs ou de table, on utilise des underscores _ et pas d'espaces !
N'oubliez pas les clés étrangères !

Schémas relationnels :

employés(employé_id, prénom, nom, naissance, sexe, salaire, #succursale_id, #superviseur_id)
succursales(succursale_id, ville, #manager_id)
fournisseurs(#succursale_id, fournisseur, fourniture)

Rappel : Pour chaque table, la clé primaire est soulignée et les clés étrangères sont précédées d'un dièse #.

CREATE TABLE employés (
  employé_id INT NOT NULL,
  prénom VARCHAR(255),
  nom VARCHAR(255),
  naissance DATE,
  sexe VARCHAR(1),
  salaire INT,
  succursale_id INT,
  superviseur_id INT,
  PRIMARY KEY (employé_id)
  FOREIGN KEY (succursale_id) REFERENCES succursales(succursale_id)
  FOREIGN KEY (superviseur_id) REFERENCES employés(employé_id)
) ;


CREATE TABLE succursales (
  succursale_id INT NOT NULL,
  ville VARCHAR(255),
  manager_id INT,
  PRIMARY KEY (succursale_id)
  FOREIGN KEY (manager_id) REFERENCES employés(employé_id)
) ;


CREATE TABLE fournisseurs (
  succursale_id INT NOT NULL,
  fournisseur VARCHAR(255) NOT NULL,
  fourniture VARCHAR(255),
  PRIMARY KEY (succursale_id, fournisseur)
  FOREIGN KEY (succursale_id) REFERENCES succursales(succursale_id)
) ;

Insertion, mise à jour, suppression de valeurs dans une table

`INSERT INTO table_à_remplir (nom_attribut1, nom_attribut2, ...) VALUES (valeur1, valeur2, ...) ;`	pour insérer des données dans la table
`INSERT INTO table_à_remplir VALUES (valeur1, valeur2, ...);`	Si vous ajouter des valeurs à toutes les colonnes de la table, il est possible de simplifier l'instruction en omettant le nom des attributs. Attention cependant à bien respecter l'ordre de ces attributs tels qu'ils ont été créés dans la table.
`UPDATE table_à_modifier SET modification_1, ... modification_n WHERE condition`	pour modifier des données ; par exemple : on peut remplacer `modification_1` par `nom=UPPER(nom)` pour mettre les noms en majuscule ; la condition est optionnelle.
`DELETE FROM table_à_modifier WHERE condition ;`	pour supprimer des données ; ⚠ la condition est essentielle ! Toutes les lignes seront supprimées sinon.

Remarque : Les conditions s'écrivent de la même manière qu'en python, sauf le test d'égalité qui est un simple = (pas d'ambiguïté en SQL). Les conjonctions AND, OR, NOT, ... sont en majuscules.

Les fichiers CSV ci-dessous contiennent toutes les données à injecter dans la BD. Écrire la requête SQL permettant d'injecter au moins un enregistrement dans chaque table.

Données : employes.csv ; succursales.csv ; fournisseurs.csv

Schémas relationnels :

employés(employé_id, prénom, nom, naissance, sexe, salaire, #succursale_id, #superviseur_id)
succursales(succursale_id, ville, #manager_id)
fournisseurs(#succursale_id, fournisseur, fourniture)

INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (100, 'Bruce', 'Salmons', '1967-04-10', 'M', 118000, 1, 100);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (101, 'Mirabelle', 'Beauchesne', '1969-09-17', 'F', 75000, 3, 100);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (102, 'Marius', 'Mikkelsen', '1993-05-11', 'M', 70000, 2, 103);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (103, 'Samawah', 'Hadad', '1979-02-24', 'F', 80000, 2, 100);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (104, 'Andy', 'Bernard', '2000-01-06', 'M', 62000, 3, 101);

INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (2, 'Hooper', 'papier');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (2, 'Kurihara', 'stylos');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Nikolic', 'papier');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (2, 'Comejo', 'formulaires');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Kurihara', 'stylos');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Hooper', 'papiers');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Ulyanova', 'formulaires');

INSERT INTO succursales (succursale_id, ville, manager_id)
VALUES (1, 'Paris', 100);
INSERT INTO succursales (succursale_id, ville, manager_id)
VALUES (2, 'Lyon', 103);
INSERT INTO succursales (succursale_id, ville, manager_id)
VALUES (3, 'Marseille', 101);

Écrire la requête SQL permettant de passer tous les noms en majuscule de la table employés.
UPDATE employés SET nom=UPPER(nom) ;
Écrire la requête SQL permettant d'augmenter le salaire de 10% pour toutes les femmes de la table employés.
UPDATE employés SET salaire=salaire*1.1 WHERE sexe='F' ;

Modificatons structurelles

Modification, suppression d'une table et/ou de clés étrangères

Attention : Avec SQLite, il est impossible de modifier une table pour insérer une clé étrangère. Les clés étrangères doivent être crées au niveau de la déclaration des tables.

`ALTER TABLE Table_à_modifier ADD nom_nouvel_attribut son_type contraintes_éventuelles ;`	pour ajouter un attribut
`ALTER TABLE Table_à_modifier MODIFY nom_attribut son_nouveau_type nouvelles_contraintes ;`	pour modifier le type ou les contraintes d'un attribut
`ALTER TABLE Table_à_modifier CHANGE nom_attribut son_nouveau_nom ;`	pour modifier le nom d'un attribut
`ALTER TABLE Table_à_modifier DROP nom_attribut ;`	pour supprimer un attribut
`ALTER TABLE Table_à_modifier ADD FOREIGN KEY (nom_clé) REFERENCES Autre_table(nom_attribut) ;`	pour créer une clé étrangère Attention : impossible avec SQLite.
`ALTER TABLE Table_à_modifier DROP FOREIGN KEY (nom_clé) ;`	pour supprimer une clé étrangère. Attention : impossible avec SQLite.
`DROP TABLE nom_table ;`	pour supprimer une table : ⚠ MySQL exige d'effacer les clés étrangères avant, pas SQLite

Chap. 5 : Langage SQL / Création Mémorisation 0x Réussite 0/0 30s

Démarrer

À quoi sert la clé primaire d'une table ?

À enregistrer les données sous forme cryptée.

À repérer la colonne qui contient des informations confidentielles.

À repérer la colonne qui contient le plus d'informations.

À distinguer chaque enregistrement de façon unique.

Cocher la bonne réponse.

Sécurité des transactions

Les modifications présentées dans le tableau ci-dessus étant potentiellement dangereuses pour la conservation des données, les SGBD implémentent un système de sauvegarde BEGIN TRANSACTION / ROLLBACK / COMMIT :

BEGIN TRANSACTION crée un point de sauvegarde ; c'est une pratique utile à effectuer avant d'opérer une modification importante ou destructrice dans une base de données.
ROLLBACK permet d'annuler toutes les modifications effectuées depuis BEGIN TRANSACTION.
COMMIT permet d'enregistrer définitivement les modifications courantes dans la base.

Attention : SQLite a vocation à utiliser un système d'autocommit dans les versions récentes. Comme d'habitude dans ce chapitre, avec SQLite, privilégier la syntaxe MySQL autant que possible.

Recopier le code ci-dessous à la suite de votre script python.
Expliquer l'utilité des instructions BEGIN TRANSACTION et ROLLBACK.

curseur.execute("BEGIN TRANSACTION ;")
curseur.execute("DROP TABLE employés ;")
reponse = curseur.execute("SELECT name FROM sqlite_schema WHERE type='table' ;").fetchall()
print("Après DROP TABLE : ", reponse)
curseur.execute("ROLLBACK ;")
reponse = curseur.execute("SELECT name FROM sqlite_schema WHERE type='table' ;").fetchall()
print("Après ROLLBACK : ", reponse)

La ligne 1 et 5 permettent d'éviter que la destruction de la table employés ne soient définitive.

3.5.3. Activation des contraintes de référence (clés étrangères)

⚠ Avec SQLite, les clés étrangères doivent être activées par PRAGMA foreign_keys = ON ; ; elles ne le sont pas par défaut 🚮.

Après avoir activé les clés étrangères, modifier la succursale d'un employé en lui affectant une id de succursale qui n'existe pas (par exemple 5) et observer le message d'erreur.
UPDATE employés SET succursale=5 WHERE employé_id='100' ; renvoie sqlite3.IntegrityError: FOREIGN KEY constraint failed.
Aurait-il été possible de remplir les tables (données données par les 3 fichiers .csv) en ayant activé les clés étrangères avant ?
C'est possible mais pas en entrant les données dans l'ordre initial : en effet, par exemple : le superviseur de l'id 102 ayant une id de 103, il aurait fallu créer l'id 103 avant la 102 à cause de la clé étrangère (intra-table) liant les id des superviseurs aux id des employés.

Consultation de la BD avec le langage SQL

4.1. L'instruction `SELECT` et les fonctions d'agrégation

Elle permet d'afficher des résultats de manière approfondie.

`SELECT nom_attribut_1, nom_attribut_2, ... FROM nom_table WHERE condition ORDER BY nom_attribut_1 ASC, nom_attribut_2 DESC;`	Affiche une liste de résultats concernés par une condition (optionnelle) classés dans un certain ordre (ascendant : `ASC` ou descendant : `DESC`) `SELECT * FROM ...` permet d'afficher tous les attributs de la table
`SELECT DISTINCT nom_attribut_1, nom_attribut_2, ... FROM Table ;`	Le mot-clé `DISTINCT` permet d'éviter les doublons dans la liste affichée
`SELECT CONCAT(nom_attribut_1, nom_attribut_2) AS résumé FROM nom_table ;`	Le mot-clé `CONCAT(a,b)` permet de formater les résultats en concaténant 2 champs ou 1 champ et `'du texte'` (on peut l'utiliser plusieurs fois : `CONCAT(CONCAT(a,b),c))`

On dispose aussi des fonctions, dites d'agrégation, suivantes :

`COUNT()`	Compte le nombre d'enregistrements
`MAX()` et `MIN()`	Récupèrent le max et le min sur un ensemble d'enregistrements
`SUM()` et `AVG()`	Récupèrent la somme et la moyenne sur un ensemble d'enregistrements

Construire la requête SQL permettant d'obtenir :

la liste des employés nom prénom triés par ordre alphabétique
SELECT CONCAT(CONCAT(nom,' '), prénom) AS personne FROM employés ORDER BY personne ASC ;
le salaire moyen de l'entreprise
SELECT AVG(salaire) FROM employés ;
le salaire moyen des hommes dans l'entreprise
SELECT AVG(salaire) FROM employés WHERE sexe='M' ;
le salaire moyen des femmes dans l'entreprise
SELECT AVG(salaire) FROM employés WHERE sexe='F' ;
le nombre de superviseurs
SELECT COUNT(DISTINCT superviseur_id) FROM employés ;

Chap. 5 : Langage SQL / Interrogation Mémorisation 0x Réussite 0/0/2 30s

Démarrer

Une requête SQL peut interroger plusieurs tables à la fois.

Faux

Vrai

Cocher la bonne réponse.

4.2. Produit cartésien

Définition : Si X et Y sont deux ensembles, on note X×Y l'ensemble de tous les couples de valeurs (x,y) ou x est un élément de X et y un élément de Y.

Exemple : Pour X={a,b,c} et Y={u,v}, on a X×Y={(a,u), (b,u), (c,u), (a,v), (b,v),(c,v)}.

Remarque : Un produit cartésien peut être représenté par un tableau :

Éléments de X×Y :

X×Y	u	v
a	(a,u)	(a,v)
b	(b,u)	(b,v)
c	(c,u)	(c,v)

Remarques :

Si X a n éléments et Y en a m, alors X×Y a n×m éléments (ce qui justifie l'utilisation du symbole × pour écrire le produit cartésien de deux ensembles ; d'ailleurs Y×Y se note aussi Y², et on a Y²={(u,u),(u,v),(v,u),(v,v)} ).
On peut étendre à X×Y×Z (triplets), à un nombre infini d'ensembles...
ℝ est une droite, ℝ² est un plan (dimension 2), ℝ³ un espace (dimension 3), ...

Exécuter la requête suivante :

SELECT DISTINCT e.nom,e.prénom,s.ville
FROM employés AS e, succursales AS s ;

À quoi sert le mot DISTINCT ?
Le mot DISTINCT permet d'éviter l'affichage de données en double (doublons).
DISTINCT peut il être remplacé par UNIQUE ?
Le mot UNIQUE s'applique à un attribut et permet d'interdire que deux enregistrements possèdent la même valeur pour cet attribut ; il s'agit d'une contrainte de domaine, on l'utilise lors de la déclaration des tables.
Bien que les mots «distinct» et «unique», leurs signification en SQL sont bien différentes. Il y a quand même un lien : si UNIQUE est appliqué à un attribut affiché plus tard avec SELECT, on sait qu'il n'y aura pas de doublons...
Est-il utile ici ?
Il est inutile dans ce cas car toutes les lignes affichées par SELECT sont déjà différentes entre elles.
À quoi sert le mot AS dans cet exemple ?
AS permet de produire des «alias», c'est à dire donner un autre nom à une entité ; en pratique, on l'utilise souvent pour raccourcir des noms, ce qui permet une écriture de la requête plus légère et plus facilement compréhensible.
Expliquer le résultat donné par cette requête.
SELECT affiche ici les attributs des enregistrements (nom, prénom , ville) pris dans le produit cartésien e×s (5×3=15 lignes).
Comparer ce résultat avec celui donné par la requête suivante :
```
SELECT DISTINCT e1.nom,e2.prénom,s.ville
FROM employés AS e1, employés AS e2, succursales AS s ;
```
SELECT affiche ici les attributs des enregistrements (nom, prénom , ville) pris dans le produit cartésien e1×e2×s soit 5×5×3=75 lignes.

4.3. `SELECT ... LIKE` : repérer un motif

On dispose, en SQL, d'un opérateur LIKE permettant de repérer des motifs dans les chaînes de caractères (selon le principe des expressions régulières mais la syntaxe est différente).

Exemple :

SELECT nom
FROM employés
WHERE nom LIKE 'B%'

LIKE '%a' les valeurs affichées se terminent par le caractère 'a'.
LIKE 'a%' les valeurs affichées débutent par le caractère 'a'.
LIKE '%a%' les valeurs affichées contiennent le caractère 'a'.
LIKE 'pas%s%i%mal' les valeurs affichées commencent par 'pas', suivi de 's' puis 'i' et se terminent par 'mal'.

Remarques : les «jokers» en SQL :

% désigne un nombre inconnu de caractères ; ce nombre peut très bien être nul.
_ remplace un seul et unique caratère.

Chap. 5 : Langage SQL / Interrogation Mémorisation 0x Réussite 0/0 30s

Démarrer

En SQL, le mot-clé DISTINCT :

doit être utilisé seulement pour les clés primaires.

permet de distinguer plusieurs tables lors d'une sélection.

doit être absolument utilisé pour une clé étrangère.

permet d'éliminer les doublons lors d'une sélection.

Cocher la ou toutes les bonnes réponses.

Donner la réponse à la requête :

SELECT DISTINCT e.prénom
FROM employés AS e
WHERE UPPER(e.nom) LIKE '%B%_'

Comment interpréter le %_ à la fin de la requête ?

Andy, Mirabelle
LIKE '%B%_' recherche les prénoms qui contiennent une lettre B ou b (on a utilisé UPPER), mais pas seulement en dernier (à cause du _, mais ce dernier pourrait remplacer un second «b»...).

Requêtes sur plusieurs tables : jointures

On appelle jointure de deux tables (table1 JOIN table2), l'opération consistant à rapprocher, selon une condition que l'on choisit (ON condition), des tuples issus de ces deux tables vérifiant la condition donnée.

Exemple :

SELECT nom, fournisseur, fourniture
FROM employés JOIN fournisseurs
ON employés.succursale_id = fournisseurs.succursale_id

Écrire une requête permettant de lister les couples de (nom du manager, fournisseur) pouvant être en contact.

SELECT employés.nom, fournisseurs.fournisseur
FROM employés JOIN succursales
ON employés.employé_id = succursales.manager_id
JOIN fournisseurs
ON succursales.succursale_id = fournisseurs.succursale_id

Question 1/8 :

Chap. 5 : Langage SQL / Interrogation Mémorisation 0x Réussite 0/0/4 1 min

Démarrer

Quelle est la requête qui sélectionne toutes les données des vols dont l'arrivée est CDG ?

idVol; compagnie; depart; arrivee; h_depart 1; AF28; TLS; CDG; 10:15 2; KLM; TLS; JFK; 10:20 3; LUFT; TLS; PTP; 11:05 4; AF28; CDG; TLS; 11:38

SELECT * FROM Vols WHERE TLS ;

SELECT * FROM Vols WHERE arrivee='CDG' ;

SELECT TLS FROM Vols ;

SELECT * FROM Vols WHERE CDG ;

SELECT CDG FROM Vols ;

SELECT * FROM Vols WHERE arrivee='TLS' ;

Cocher la bonne réponse.

5. Avec python : pilotage d'un serveur MySQL ; vers l'analyse de données (bonus)

La bibliothèques mysql permet de «piloter» un serveur MySQL installé sur la machine, avec Python (à l'aide d'un «connecteur», la syntaxe est proche de celle employée par SQLite). Références à consulter :
- sur python.doctor ;
- la documentation.
Les bases de données sont souvent exploitées en utilisant une bibliothèque d'analyse de données, comme pandas (combinée à des bibliothèques mathématiques pour le calcul : scipy, ...) :
Référence : la documentation.

Cours

Les systèmes de gestion de bases de données relationnelles

Description

Logiciels et langage SQL

Services rendus par le SGBD

Construction de la BD avec le langage SQL

Création d'une base de données sur un serveur MySQL

Création d'une base de données SQLite3 en utilisant Python

Construction des tables avec le langage SQL

Création d'une table : syntaxe globale

Les types MySQL et SQLite

Types Entiers

Types Nombres décimaux

Types Texte

Types Date

Gestion des types par SQLite

Ajout des contraintes d'intégrité

Insertion, mise à jour, suppression de valeurs dans une table

Modificatons structurelles

Modification, suppression d'une table et/ou de clés étrangères

Sécurité des transactions

3.5.3. Activation des contraintes de référence (clés étrangères)

Consultation de la BD avec le langage SQL

4.1. L'instruction SELECT et les fonctions d'agrégation

4.2. Produit cartésien

4.3. SELECT ... LIKE : repérer un motif

Requêtes sur plusieurs tables : jointures

5. Avec python : pilotage d'un serveur MySQL ; vers l'analyse de données (bonus)

4.1. L'instruction `SELECT` et les fonctions d'agrégation

4.3. `SELECT ... LIKE` : repérer un motif