Systèmes de Gestion de Bases de Données (SGBD) : Concepts et SQL

Les Systèmes de Bases de Données (SBD)

Un système de base de données se compose des éléments suivants :

• Base de données: Une collection de données interdépendantes. Une base se compose de:
  • Dictionnaire des données: Contient la description de la structure de la BD (les «métadonnées»).
  • Données
• Le Système de Gestion de Bases de Données (SGBD): Un ensemble de programmes qui permet de définir, créer, manipuler et contrôler l'accès à la base de données.
• Les utilisateurs: Administrateurs, concepteurs, utilisateurs finaux, etc.

Avantages de l'utilisation d'un SGBD

• Réduction et contrôle de la redondance.
• Éviter les incohérences (copies différentes de l'original).
• Maintien de l'intégrité (s'assurer que les informations stockées sont correctes). Ceci utilise des contraintes d'intégrité (type de données, longueur, etc.).
• Facilité à réaliser, développer et maintenir les relations entre les données.
• Contrôle d'accès, sécurité et gestion de la concurrence (simultanéité).
• Facilité de sauvegarde des données et de récupération après erreur.
• Flexibilité: changements dans la structure sans affecter les données stockées.

Le Modèle de Données

Le modèle de données d'une base de données (DB) est un ensemble de concepts décrivant les types de données, les relations et les restrictions qu'elles doivent respecter, ainsi que les opérations d'interrogation et de modification des données et de la structure de la base de données. Types de modèles :

• Conceptuel (haut niveau): Proche du monde réel, basé sur des entités ayant des attributs qui les définissent et se rapportant à d'autres entités. Exemple : UML.
• Logique: Décrit la structure globale. Compréhensible par les utilisateurs, mais également proche de l'organisation physique des données. Exemple : relationnel ou orienté-objet.
• Physique: Décrit la structure physique de la base de données (façon de stocker et d'accéder aux enregistrements, blocs, etc.).

Schéma et État de la Base de Données

Schéma: Spécifie la conception de la base de données (les métadonnées).

État (ou Instance): Ensemble des données que contient la base de données à un moment donné. Le gestionnaire doit s'assurer qu'il est toujours cohérent.

Langages et Interfaces du SGBD

LDD (Langage de Définition de Données): Permet de spécifier la structure conceptuelle de la base de données.

LDA (Langage de Définition de Stockage): Langage de définition de stockage.

LDV (Langage de Définition de Vue): Permet à l'utilisateur de définir des vues. La plupart des gestionnaires utilisent le LDD pour définir des vues.

LMD (Langage de Manipulation de Données): Permet l'accès, l'insertion, la suppression et la modification des données. Il peut être procédural (spécifie comment obtenir les données) ou déclaratif (spécifie ce qu'il faut obtenir, mais pas comment).

Le Modèle de Données Relationnel

C'est le modèle le plus largement utilisé, basé sur des enregistrements (ou tuples).

Dans la définition formelle, les relations possèdent ces propriétés :

• Il n'y a pas de tuples répétés.
• Les tuples ne sont pas ordonnés.
• Les attributs ne sont pas ordonnés.
• Les valeurs des attributs sont atomiques.

Règles d'Intégrité du Modèle Relationnel

Le gestionnaire permet de définir les restrictions qui s'appliquent aux tuples. Le modèle relationnel spécifie deux types de contraintes d'intégrité fondamentales, mais il en existe d'autres :

• Clés candidates, primaires et alternatives: Un sous-ensemble d'attributs qui satisfait la contrainte d'unicité et ne contient pas de valeur nulle. (Il n'y a pas deux tuples avec les mêmes valeurs pour ces attributs.)
• Clés étrangères: Ensemble d'attributs d'une relation qui référence la clé primaire d'une autre relation (ou de la même relation). Elles permettent de relier les relations. Le gestionnaire doit s'assurer de la correspondance des clés étrangères (sauf si elles sont nulles).

Valeurs Nulles (NULL)

Représentent les attributs inconnus, manquants ou non applicables. Dans les clés primaires, aucun de ses attributs ne peut contenir une valeur NULL. Les clés étrangères peuvent autoriser ou non les valeurs nulles.

Langages de Bases de Données

Algèbre Relationnelle

Collection d'opérateurs utilisant des relations comme opérandes et retournant des relations comme résultat.

Pour les opérations nécessitant des relations d'entrée compatibles, les deux relations doivent avoir le même nombre d'attributs et le même domaine.

Opérations de l'Algèbre Relationnelle

Calcul Relationnel

Il est exprimé par des conditions :

Le résultat est l'ensemble des tuples t qui satisfont la condition. Exemples :

• {T | Acteur (t) ET t.cache > 2000}
• {T.nombre, t.nacionalidad | Acteur (t) ET t.cache > 5000}
• (∀ t) (BANQUE (t) ET NON (t.ciudad = 'Londres'))
• (∃ t) (BANQUE (t) ET t.ciudad = 'Amsterdam')

Intégrité des Données et Contraintes

Définition des Règles d'Intégrité

Elles se composent de :

• Nom de la contrainte
• Contrainte d'intégrité à évaluer
• Réponse en cas de violation :
  • Rejeter l'opération
  • Exécuter une autre procédure

Les contraintes peuvent être de différents types :

• Domaine: Définit un domaine de valeurs. Exemple:

CREATE DOMAIN Color_ojos AS VARCHAR (10)
DEFAULT 'Brown'
CONSTRAINT Color_valido
CHECK (VALUE IN ('Brown', 'Grey', 'bleu', 'vert', 'noir'));

• Table: Incluses dans la définition d'une table, elles s'appliquent à :
  • Colonnes: Définissent le type de données, le domaine, si NULL est autorisé, etc.

CREATE TABLE Acteur
(Nom VARCHAR (30) NOT NULL)

• Clés primaires, candidates et étrangères.
• Contrôles au niveau de la table (CHECK).

CREATE TABLE films (
CONSTRAINT Pelicula_fechas_ok
CHECK (fecha_fin_rodaje < fecha_estreno)
... );

• Générale (Assertions): Non incluses dans la table, elles sont un élément distinct de la BD. Elles peuvent impliquer plusieurs tables.

CREATE ASSERTION RI1_age1_cache
CHECK (NOT EXISTS (SELECT * FROM Acteur
OÙ agence de cache = 1 ET cache < 300));

Vérification des Contraintes

Les restrictions peuvent être testées de deux manières :

• IMMÉDIATE: Après chaque instruction SQL exécutée.
• DIFFÉRÉE: À la fin de la transaction.

Déclencheurs (Triggers)

Permettent de spécifier l'action à exécuter suite à un événement (souvent lié à une tentative de violation de contrainte). Pour la conception, vous devez spécifier :

• L'événement qui provoque le déclenchement.
• Une condition qui doit être remplie pour l'exécution.
• Les actions prises lors de l'exécution.

Exemple de déclencheur :

CREATE TRIGGER <nom_disparador>
{BEFORE | AFTER} {INSERT | UPDATE [OF <liste_colonnes>] | DELETE}
ON <table_name>
[REFERENCING OLD ROW [|] TABLE [AS] <name>
[NEW [ROW | TABLE] [AS] <nom>]]
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN <condition>]
BEGIN ATOMIC
...
END;

Gestion des Transactions

Une transaction est une action ou une séquence d'actions effectuées par un utilisateur ou un programme d'application qui lit et met à jour le contenu de la base de données. Ses propriétés sont connues sous l'acronyme ACID :

• Atomicité: La transaction s'exécute entièrement ou pas du tout.
• Cohérence (Consistency): La transaction amène la base de données d'un état cohérent à un autre.
• Isolation: Les modifications apportées par une transaction (T) ne sont pas visibles par les autres transactions tant qu'elle n'est pas confirmée (COMMIT).
• Durabilité (Durability): Une fois terminée avec succès, ses changements persistent même si le système tombe en panne.

Les transactions sont finalisées avec un COMMIT (validation) ou un ROLLBACK (annulation).

Contrôle de la Concurrence

Problèmes Liés à la Concurrence

• Mise à jour perdue (Lost Update): Lorsque deux transactions (T) lisent la même valeur, l'une écrit, puis l'autre écrit, écrasant la valeur précédente sans tenir compte de sa modification.
• Lecture sale (Dirty Read): Lorsqu'une transaction lit une valeur modifiée par une autre transaction qui a ensuite échoué et est annulée (ROLLBACK).
• Sommaire Faux (Incorrect Summary): Utilisation de fonctions d'agrégation (comme COUNT ou SUM) sur des données en cours de modification par une autre transaction.
• Lecture non répétable (Non-Repeatable Read): Lorsqu'une transaction lit une valeur deux fois, mais qu'une autre transaction modifie cette valeur entre les deux lectures.

Sérialisation des Transactions

Le but de la sérialisation est de planifier les transactions de manière à éviter toute interférence entre elles.

Une Planification sérielle est celle où les opérations de chaque transaction sont exécutées consécutivement, sans opérations intercalées par d'autres transactions. C'est souvent inefficace.

Une Planification non sérielle est celle où les opérations sont entrelacées. Une planification est dite sérialisable si elle est équivalente à une planification sérielle avec les mêmes transactions. Cette équivalence peut être définie par le «conflit d'équivalence» :

• Si deux transactions (T) ne font que lire les données, l'ordre n'a pas d'importance.
• Si elles lisent et écrivent des données différentes, l'ordre n'a pas d'importance.
• Si elles accèdent aux mêmes données et qu'au moins une opération est une écriture, il y a conflit, et l'ordre d'exécution est crucial.

Pour déterminer si une planification non sérielle est sérialisable, on utilise un graphe de précédence :

• Un nœud est créé pour chaque transaction (T) dans la planification (P).
• Créer un arc T_j → T_k si T_k lit ou écrit une valeur après qu'elle a été écrite par T_j.
• Créer un arc T_j → T_k si T_k écrit une valeur après que T_j l'a lue.

Si le graphe contient un cycle, P n'est pas sérialisable. Sinon, pour obtenir un ordre sérialisable, on effectue un tri topologique des nœuds.

Méthodes de Verrouillage (Locking)

L'objectif est d'assurer la sérialisation des transactions en appliquant un ensemble de règles et de protocoles. L'utilisation de verrous ne garantit pas la sérialisation des planifications à elle seule.

Les méthodes de verrouillage sont basées sur l'utilisation de verrous pour contrôler les accès concurrents aux données :

• Verrou Partagé (Shared Lock - S): La transaction (T) peut lire l'élément, mais pas l'écrire.
• Verrou Exclusif (Exclusive Lock - X): La transaction (T) peut lire et mettre à jour l'élément.

Protocole de Verrouillage à Deux Phases (2PL)

C'est le protocole le plus connu. Une transaction T suit le protocole de verrouillage à deux phases (2PL) si toutes les opérations de verrouillage précèdent la première opération de déverrouillage. T peut être divisée en deux phases :

• Phase d'Expansion (ou Croissance):
  • T peut acquérir des verrous.
  • T ne peut pas libérer de verrous.
• Phase de Réduction (ou Décroissance):
  • T peut libérer des verrous existants.
  • T ne peut pas acquérir de nouveaux verrous.

Cette méthode assure la sérialisation (si elle est appliquée par toutes les transactions), mais elle réduit la concurrence et peut causer des problèmes d'interblocage (deadlocks) ou de blocage indéfini.

Quelques modifications du 2PL :

• 2PL Conservateur ou Statique: T doit verrouiller tous les éléments auxquels elle accède avant de commencer à s'exécuter. (Évite les interblocages).
• 2PL Strict: T ne libère pas de verrou exclusif avant de se terminer. Peut provoquer des interblocages.
• 2PL Rigoureux: T ne libère aucun verrou jusqu'à la fin (COMMIT ou ROLLBACK).

Problème d'Interblocage (Deadlock)

Se produit lorsque deux ou plusieurs transactions sont en attente de la libération d'un verrou détenu par une autre transaction. Le SGBD doit détecter ces situations et les résoudre.

Il existe trois techniques pour la gestion des interblocages :

• Délais d'attente (Timeout): Les transactions attendent les verrous pendant un certain laps de temps. Si le verrou n'est pas obtenu après ce délai, la transaction est annulée et redémarrée.
• Prévention des interblocages: Un horodatage est attribué à chaque transaction en fonction de son heure de départ. Lorsqu'une transaction tente de verrouiller un objet déjà verrouillé, deux algorithmes peuvent être utilisés :
  • Wait-Die (Attendre-Mourir): Si T est plus ancienne que la transaction détenant le verrou, elle attend. Sinon, elle est annulée et redémarrée.
  • Wound-Wait (Blesser-Attendre): Si T est plus ancienne, elle force l'annulation et le redémarrage de la transaction détenant le verrou (la plus jeune, qui conserve son horodatage). Sinon, elle attend.
• Détection des interblocages: Créer un graphe d'attente montrant les dépendances entre les transactions et rechercher des cycles. Si un cycle est trouvé, annuler certaines transactions pour le briser, en choisissant la victime la moins coûteuse.

Problème de la Famine (Starvation)

Une transaction souffre de famine lorsqu'elle est annulée à plusieurs reprises et n'arrive jamais à s'exécuter complètement. La solution est d'attribuer une priorité plus élevée à la transaction avortée à plusieurs reprises afin d'éviter une nouvelle victimisation.

Problème de Blocage Indéfini

Se produit lorsque le gestionnaire ne choisit jamais une transaction (T) qui est en attente d'un verrou, car des transactions plus prioritaires arrivent constamment.

Il peut être résolu de deux manières :

• Premier arrivé, premier servi (FIFO).
• Augmentation de la priorité en fonction du temps d'attente : ceux qui attendent depuis plus longtemps ont une priorité plus élevée.

Gestion des Pannes et Récupération (Recovery)

Le gestionnaire doit s'assurer que les transactions exécutent toutes leurs opérations et que leur effet est permanent dans la base de données, ou qu'elles n'ont aucun effet sur la BD ou sur d'autres transactions (garantie de l'Atomicité et de la Durabilité).

Types de Pannes (Défauts)

• Panne locale: Seule la transaction (T) échoue.
  • Échec local attendu.
  • Échec local imprévu (bug ou annulation par l'utilisateur).
  • Échec dû à l'imposition du contrôle de concurrence.
• Panne globale: Affecte toutes les transactions.
• Défaillance du système (Soft Crash): Dysfonctionnement du matériel ou du réseau. Aucun dommage sur le disque.
• Défaillance matérielle (Hard Crash): La BD est corrompue.
• Défaillance physique: Catastrophes naturelles, incendies, vols, etc.

Mécanismes de Récupération

L'objectif est de restaurer la base de données dans un état correct après une panne. Le gestionnaire s'assure qu'aucune transaction n'est perdue ou laissée inachevée.

Un des mécanismes utilisés est le fichier journal (log) qui enregistre les détails des transactions.

Récupération d'une Transaction Échouée

• Si la panne se produit lorsque T est en cours d'exécution, elle est annulée (ROLLBACK). Pour ce faire, le journal est parcouru en ordre inverse, défaisant chacune des opérations d'écriture effectuées par la transaction (UNDO).
• Si T a déjà été confirmée, elle doit être refaite (REDO). Le journal est lu dans l'ordre de la première opération d'écriture.

Protocole d'Écriture Anticipée (WAL)

Ce protocole garantit qu'une transaction (T) n'est pas considérée comme validée (COMMIT) tant que tous les enregistrements du journal (log records) relatifs à T n'ont pas été écrits sur le disque. Une fois ces enregistrements écrits, les changements apportés par T sont permanents, permettant la récupération via le journal en cas de panne.

Points de Contrôle (Checkpoints)

C'est une marque incluse dans le journal qui implique :

• L'écriture sur disque du tampon du journal.
• L'écriture sur disque de tous les changements apportés à la base de données.

Ces entrées permettent au système de récupérer à partir du dernier point de contrôle plutôt que d'avoir à recommencer depuis le début du journal.

Planifications Récupérables

Une planification est dite récupérable si aucune transaction T n'est confirmée avant que toute transaction ayant lu les données écrites par T ne soit elle-même confirmée. Cela garantit qu'une fois T confirmée, il ne sera jamais nécessaire de l'annuler.

Cependant, cela peut conduire à des annulations en cascade : une transaction non confirmée peut être annulée parce qu'elle a lu des données d'une transaction qui a été abandonnée.

Planifications Rigoureuses

Une planification rigoureuse empêche les transactions de lire ou d'écrire des éléments d'autres transactions qui n'ont pas été confirmées ou annulées. (Ceci évite les lectures sales et les lectures non répétables).

Stratégies de Rétablissement (Recovery)

Face à une défaillance physique (type 5 ou 6), la seule solution est de restaurer une sauvegarde et de refaire les entrées du journal.

Pour les pannes de type 1 à 4, on utilise le journal pour refaire (REDO) ou défaire (UNDO) les opérations concernées.

Technique de Mise à Jour sur Place (Update in Place)

La transaction (T) peut modifier la base de données sur disque avant de se terminer. En cas de panne, on utilise le journal pour effectuer un REDO ou un UNDO selon l'état de T au moment de l'erreur.

Technique de Mise à Jour Différée (Deferred Update)

La transaction (T) ne modifie jamais la base de données sur disque tant qu'elle n'est pas confirmée. Si la panne survient avant le COMMIT, aucune action n'est nécessaire. Sinon, il faut reconstruire ses opérations (REDO).

Variation de la Mise à Jour

La transaction (T) peut modifier la base de données sur disque avant de se terminer. La transaction n'est considérée comme confirmée que lorsque T enregistre ses modifications sur le disque. En cas d'échec avant ce point, l'opération est annulée (UNDO). Si l'échec se produit après, rien n'est fait (car les données sont déjà permanentes).

Niveaux d'Isolation des Transactions

SET TRANSACTION ISOLATION LEVEL <mode_acces> <niveau_isolation>

Modes d'accès:

• READ ONLY: Interdit les mises à jour.
• READ WRITE: Par défaut.

Niveau d'isolation:

Le Langage SQL

Interrogation des Données: La Clause SELECT

• SELECT <liste_colonnes> (Liste des colonnes ou des caractéristiques à obtenir)
• FROM <liste_tables> (Liste des tables nécessaires)
• WHERE <conditions_travail> (Conditions de sélection des lignes)
• GROUP BY <liste_colonnes> (Spécifications de regroupement des lignes)
• HAVING <conditions_groupes> (Conditions pour sélectionner les groupes de lignes)
• ORDER BY <liste_colonnes> (Ordre de présentation des résultats)

Opérations Ensemblistes (Set Operations)

UNION (∪), INTERSECT (∩), EXCEPT (-)

Dans Oracle, EXCEPT est souvent remplacé par MINUS. Les tables d'opération doivent être compatibles. Pour ne pas supprimer les doublons, utilisez UNION ALL, INTERSECT ALL, ou EXCEPT ALL.

Opérateurs d'Ensemble Explicite

• v IN (...) : Indique si la valeur v appartient à l'ensemble des valeurs.
• v <op> ANY (...) : Compare v avec les éléments de l'ensemble, et retourne VRAI si la condition est satisfaite pour au moins un d'entre eux. Vous pouvez aussi utiliser SOME (...).
• v <op> ALL (...) : Compare v avec les éléments de l'ensemble, et retourne VRAI si la condition est satisfaite pour chacun d'eux.

Requêtes Imbriquées (Subqueries)

• EXISTS (S) : Vérifie que la sous-requête S n'est pas un ensemble vide.
• UNIQUE (S) : Retourne VRAI si aucune ligne n'est répétée dans S.

Vues en Ligne (Inline Views)

Une vue en ligne est une sous-requête utilisée dans la clause FROM d'une autre requête. Exemple:

SELECT X.nd, X.nombred, E.nss, E.nombre, E.apellido, E.salario
FROM employé E, (SELECT nd, MAX(salaire) AS max_sal FROM employés GROUP BY nd) X
WHERE E.salario = X.max_sal AND E.nd = X.nd;

Jointures (Joins)

• INNER JOIN: Jointure interne par défaut, combinant les tables selon une condition de jointure.
• NATURAL JOIN: Effectue une équi-jointure automatique entre toutes les paires de colonnes ayant le même nom dans les deux tables.
• LEFT | RIGHT | FULL [OUTER] JOIN: Jointures externes qui incluent les lignes sans correspondance (avec des valeurs NULL pour les colonnes non correspondantes).

Insertion, Modification et Suppression (LMD)

INSERT INTO <Table> [<colonnes>] VALUES (<valeurs>)

UPDATE <Table> SET <colonne> = <valeur> [<colonne2> = <valeur2> ...] WHERE <Conditions> (Les valeurs peuvent être NULL ou DEFAULT)

DELETE FROM <Table> WHERE <Conditions>

Le programmeur doit s'assurer que les données saisies satisfont les contraintes d'intégrité du système, sinon le gestionnaire rejettera les requêtes non conformes.

Le Langage de Définition de Données (LDD)

Gestion des Schémas

Un schéma est un ensemble de tables et d'autres objets de la base de données.

CREATE SCHEMA <nom_schema> AUTHORIZATION <identificateur_propriétaire>

Suppression d'un schéma : DROP SCHEMA <nom_schema> <CASCADE|RESTRICT>;

Création et Modification des Tables

Permet de créer des tables et de définir leurs colonnes et contraintes.

CREATE TABLE <nom> (
<colonne> VARCHAR (15) [NULL | NOT NULL] [DEFAULT <valeur>]
[PRIMARY KEY <colonnes>]
[UNIQUE <colonnes>]
[FOREIGN KEY <colonnes> REFERENCES <Table> (<colonnes>) 
[ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}] 
[ON UPDATE <action>]]
[[CONSTRAINT <nom_restriction>] CHECK (<expression>)]
);

Types de Données SQL

• Numérique
  • INTEGER / INT
  • SMALLINT
  • REAL
  • FLOAT
  • NUMERIC (p, e) ou DECIMAL (p, e), où p est la précision et e est l'échelle.

Dans Oracle, tous les types numériques sont exprimés par NUMBER (p, e).

• Caractères
  • CHAR (n)
  • VARCHAR (n)
• Flux de Bits (Bitstreams)
  • BIT (n)
  • BIT VARYING (n)
• Temporel
  • DATE
  • TIME
  • TIMESTAMP
  • INTERVAL
• Domaines Personnalisés
  • Sont créés avec CREATE DOMAIN, incluant le nom, les valeurs par défaut et les restrictions.
  • Sont détruits avec DROP DOMAIN <nom> <CASCADE|RESTRICT>.

Modification des Tables (ALTER TABLE)

• Ajouter une colonne : ALTER TABLE <nom_table> ADD <définition_colonne>
• Supprimer une colonne : ALTER TABLE <nom_table> DROP <nom_colonne> <CASCADE|RESTRICT>
• Modifier une colonne : ALTER TABLE <nom_table> ALTER <nom_colonne> <action>;
• Ajouter une contrainte : ALTER TABLE <nom_table> ADD CONSTRAINT <nom_RI> <définition_RI>
• Retirer une contrainte : ALTER TABLE <nom_table> DROP CONSTRAINT <nom_RI> <option>
• Suppression des tables : DROP TABLE <nom> <CASCADE|RESTRICT>

Vues (Views)

Une vue est une table virtuelle dérivée d'autres tables.

CREATE VIEW <nom_vue> [(<liste_noms_colonnes>)] AS <requête_de_définition>

Pour les supprimer : DROP VIEW <nom>

Les vues ne sont pas créées physiquement ; elles sont générées par le SGBD lors de la consultation, de sorte que l'information est toujours à jour. Le gestionnaire traduit les requêtes sur la vue en requêtes équivalentes sur la table de base.

Cependant, la mise à jour des vues est limitée. Si la vue est basée sur plus d'une table ou utilise des fonctions d'agrégation, les opérations de mise à jour peuvent être ambiguës, voire impossibles.

La clause WITH CHECK OPTION indique au gestionnaire que les mises à jour effectuées sur la vue doivent vérifier la conformité aux conditions de définition de la vue. Par exemple, une vue définie avec WITH CHECK OPTION et indiquant un salaire inférieur à 900 € n'autoriserait pas l'insertion ou la mise à jour d'un salaire supérieur à ce montant.

Le gestionnaire peut travailler de deux façons : par traduction de la requête ou en créant une table physique temporaire (comme mémoire cache).

Index

Les index sont des structures de données auxiliaires qui permettent la recherche efficace sur un ou plusieurs attributs.

Les index sont créés automatiquement pour les clés PRIMARY KEY et UNIQUE.

CREATE INDEX <nom> ON <table> (<colonnes>)

DROP INDEX <nom>