Question 4 : Comment peut-on produire de l'information à partir de données contenues dans une base ?

A. Terminologie structurelle

Les éléments de base souvent référencés dans la modélisation relationnelle sont les domaines, les relations, les attributs, les enregistrements (ou tuples) et les degrés. La figure suivante illustre ces concepts :

B. Cardinalité de relation

Le modèle relationnel prévoit trois types de relations entre tables : 1:1, 1:N et N:N. Les relations entre les tables sont définies dans la colonne partagée.

C. Clé primaire

Une clé primaire est la donnée qui permet d'identifier de manière unique un enregistrement dans une table.

Cela induit 3 caractéristiques obligatoirement et simultanément réunies pour toute occurrence (enregistrement) de la relation :

• l'existence d'une valeur non vide pour cette clé primaire,
• l'unicité de cette valeur (aucune autre occurrence ne peut porter la même valeur de clé primaire),
• la stabilité de cette valeur, cette troisème caractéristique étant liée au fait que la clé primaire peut servir à établir le lien avec d'autres relations or si sa valeur change, le lien sera rompu.

Il y a une seule clé primaire par relation (mais elle peut-être constituée de plusieurs attributs, c'est leur ensemble qui forme la clé primaire d'une relation et leur ensemble de valeurs qui doit respecter les contraintes précédentes).

Il peut-être difficile de trouver un attribut ou groupe d'attributs respectant de façon certaine les trois contraintes d'une clé primaire pour toutes les occurrences possibles (passées ou à venir). Dans ce cas on crée généralement un nouvel attribut (numéro, code) destiné à être clé primaire, signifiant ou non (simple numéro auto-incrémenté).

D. Clé étrangère

Une clé étrangère est ainsi qualifiée car elle tire ses valeurs de la clé primaire d'une autre relation à laquelle elle fait référence. En ce sens :

• elle doit posséder le même type de données que sa clé primaire de référence,
• elle ne doit pas « créer » ses propres valeurs et ne peut que reprendre des valeurs pré-existantes de sa clé primaire de référence.

La mise en œuvre de ces deux contraintes sous un SGBDR, garantissant la cohérence de la base de données, se nomme la contrainte d'intégrité réfentielle.

Par contre, une clé étrangère pour toute occurrence de la relation n'est pas soumise aux trois contraintes essentielles d'une clé primaire :

• elle peut éventuellement être vide (Null), selon le contexte,
• elle peut ne pas être unique,
• elle peut ne pas être stable.

Une relation peut n'avoir aucune clé étrangère ou en avoir une à plusieurs, indépendantes les unes des autres.

E. Représentation d'une relation

Une relation peut être représentée ainsi :

Relation (attribut1, attribut2...)
Clé primaire : attribut1

Par convention, le nom de la relation commence par une majuscule et les attributs par une minuscule.

F. Dépendance fonctionnelle

En théorie des bases de données relationnelles, une dépendance fonctionnelle est une contrainte entre deux ensembles d'attributs dans une relation d'une base de données.

Dans une relation R, on dit qu'il y a dépendance fonctionnelle entre un ensemble d'attributs A et un ensemble d'attributs B, ou que l'ensemble A d'attributs détermine l'ensemble B d'attributs (et on écrit A→B) si quand deux enregistrements coïncident sur leurs attributs A, alors ils coïncident aussi sur leurs attributs B.

Exemple :

Considérons une relation (cours, heure, jour, salle).

B. Recensement des données nécessaires

Il convient de décomposer les données selon un niveau pertinent par rapport aux besoins futurs de transformation.

Exemple : Raphaël Dorol pourrait être une occurrence d'un attribut identité mais il semble plus pertinent de considérer que Raphaël est une occurrence d'une donnée prénom et Dorol une occurrence d'un attribut nom, permettant ensuite une exploitation séparée des noms et des prénoms.

Il est sous-jacent que les données recensées devront être acquises. Cependant certaines données peuvent être obtenues automatiquement par transformation d'autres données (données dites « calculées ») ce qui permettra d'économiser leur acquisition.

C. Le dictionnaire des données

À l'issue de ces étapes on peut établir un dictionnaire des données de la base :

Le choix du type de donnée permet d'optimiser les volumes mobilisés en stockage de la donnée et en mémoire lors de ses traitements. Les principaux types de données sont :

• le type alphanumérique (tous les caractères), en spécifiant généralement le nombre maximal de caractères,
• le type numérique (avec des sous-types : entier, réel...),
• le type date/heure (avec des sous-types : date seule, heure seule...)
• le type booléen (vrai/faux mis en œuvre généralement avec les valeurs 1/0 ou True/False).

Toute occurrence d'un attribut devra respecter une contrainte d'intégrité de domaine, c'est-à-dire présenter une valeur ayant le type requis par l'attribut.

A. Les types de données

Les principaux types de données sont pris en charge par les SGBDR, avec de nombreux sous-types.

B. La création de tables

La syntaxe de création d'une table est la suivante :

  1. CREATE TABLE nom_table
  2.   (creer_definition, ...)
  3. 
  4. creer_definition: {
  5.   nom_colonne type_de_donnees [NOT NULL | NULL] [AUTO_INCREMENT] [PRIMARY KEY]
  6. | [CONSTRAINT nom_contrainte] PRIMARY KEY (champ_cle_primaire, ...) ...
  7. | [CONSTRAINT nom_contrainte] FOREIGN KEY (nom_colonne, ...) definition_reference
  8. }
  9. 
 10. definition_reference: {
 11.   REFERENCES nom_table(nom_colonne, ...)
 12. }

1. On commence par utiliser l'instruction CREATE TABLE suivi du nom de la table à créer ;
2. La 2ème ligne signifie que l'on peut ensuite ajouter un à plusieurs champs ou contraintes de clé, dans une paire de parenthèses et séparés par des virgules ;
4. Chaque création d'une définition de colonne (création d'un champ ou d'une contrainte de clé) devra respecter la syntaxe définie dans "creer_definition" ;
5. Une colonne peut être définie en indiquant son nom suivi de son type. Les crochets "[]" servent à délimiter les éléments facultatifs. Un champ peut éventuellement être déclaré null ("absence" de valeur toléré) ou (signification de la barre verticale "|") non-null, auto-incrémenté ou encore clé primaire ;
6. La ligne 6 permet de définir la clé primaire de la table. On utilise l'instruction PRIMARY KEY suivie entre parenthèses du ou des champs (préalablement définis) constituant la clé primaire.
   Le caractère "|" signifie "ou" : chaque définition de colonne (creer_definition) est soit une création de champ, soit une déclaration de contrainte de clé (primaire ou étrangère).
   L'instruction CONSTRAINT sert à nommer la contrainte. Un nom sera automatiquement généré si l'instruction est omise (afin de pouvoir modifier ou supprimer la contrainte ultérieurement) ;
7. La ligne 7 permet d'ajouter une clé étrangère à la table. On utilise l'instruction FOREIGN KEY suivie entre parenthèses du ou des champs (préalablement définis) constituant la clé étrangère, puis de la définition de la référence (voir ligne 11) ;
8. Fin de la syntaxe de création d'une définition de colonne ;
10. Début de la syntaxe de la définition d'une référence ;
11. La clé étrangère tirant ses valeurs d'une autre table, on utilise l'instruction REFERENCES suivie du nom de la table source puis, entres parenthèses, du ou des champs référencés, dans le bon ordre.

Exemples :

Considérons le schéma relationnel suivant :

Service (num, libelle, budget)
clé primaire : num (auto-incrémenté)

Qualification (num, libelle, niveau)
clé primaire : num (auto-incrémenté)

Salarie (num, nom, prenom, dateEmbauche, salaire, indemnite, poste, numService)
clé primaire : num (auto-incrémenté)
clé étrangère : num_service en référence à num de la relation service

Competence (numSalarie, numQualif, dateAcq)
clé primaire : numSalarie, numQualif
clés étrangères :

• numSalarie en référence à num de la relation Salarie
• numQualif en référence à num de la relation Qualification

Essayez d'écrire vous même le code en utilisant par exemple cette éditeur en ligne W3Schoolsopen_in_new. Ceci n'est pas un SGBDR mais permet de tester le langage SQL facilement.

Voici les requêtes de création de table correspondantes :

 CREATE TABLE Service (
   num INT AUTO_INCREMENT,
   libelle VARCHAR(20) NOT NULL,
   budget FLOAT,
   PRIMARY KEY (num)
 );

num n'a pas été explicitement spécifié NOT NULL puisqu'il l'est de fait en étant clé primaire.

Le paramètre AUTO_INCREMENT, associé à un entier, s'écrit différemment selon les SGBDR.

Le libellé du service ne pourra pas dépasser 20 caractères (espaces inclus).

 CREATE TABLE Qualification (
   num INT AUTO_INCREMENT,
   libelle VARCHAR(20) NOT NULL,
   niveau VARCHAR(1) NOT NULL,
   PRIMARY KEY (num)
 );

 CREATE TABLE Salarie (
   num INT AUTO_INCREMENT,
   nom VARCHAR(25) NOT NULL,
   prenom VARCHAR(25) NOT NULL,
   dateEmbauche DATE NOT NULL,
   salaire FLOAT,
   numService INT,
   PRIMARY KEY (num),
   FOREIGN KEY (numService) REFERENCES Service(num)
 );

 CREATE TABLE Competence (
   numSalarie INT,
   numQualif INT,
   dateAcq DATE NOT NULL,
   PRIMARY KEY (numSalarie, numQualif),
   FOREIGN KEY (numSalarie) REFERENCES Salarie(num),
   FOREIGN KEY (numQualif) REFERENCES Qualification(num)
 );

C. La suppression d'une table

La suppression d'une table supprime également de manière irréversible son contenu :

 DROP TABLE nom_table

D. La modification de la structure d'une table

La syntaxe de modification d'une table est la suivante :

  1. ALTER TABLE nom_table
  2.   option_alteration [, option_alteration ...]
  3. 
  4. option_alteration: {
  5.   ADD nom_colonne type_de_donnees [NOT NULL | NULL] [AUTO_INCREMENT] [PRIMARY KEY]
  6. | ADD (nom_colonne type_de_donnees [NOT NULL | NULL] [AUTO_INCREMENT] [PRIMARY KEY], ...)
  7. | ADD [CONSTRAINT nom_contrainte] PRIMARY KEY (champ_cle_primaire, ...)
  8. | ADD [CONSTRAINT nom_contrainte] FOREIGN KEY (nom_colonne, ...) definition_reference
  9. | DROP nom_colonne
 10. | DROP PRIMARY KEY
 11. | DROP FOREIGN KEY nom_contrainte
 12. }
 13. 
 14. definition_reference: {
 15.   REFERENCES nom_table(nom_colonne, ...)
 16. }

1. L'instruction ALTER TABLE permet d'ajouter ou de supprimer une colonne ou une contrainte de clé, de la table nom_table ;
2. Plusieurs modifications de la structure de la table peuvent être indiquées en les séparant par une virgule ;
5. Ajout d'un champ avec l'instruction ADD, la colonne est ensuite définie de la même manière que pour la création de table ;
6. Ajout de plusieurs champs, ceux-ci doivent être entres parenthèses et séparés par des virgules ;
7. et 8. Ajout d'une contrainte de clé (primaire et étrangère respectivement) ;
9. Suppression d'un champ ;
10. Suppression de la clé primaire ;
11. Suppression d'une clé étrangère.

A. L'insertion d'enregistrements

L'ajout d'enregistrement dans une table passe par l'instruction suivante :

 1. INSERT INTO nom_table
 2. [(nom_colonne [, nom_colonne] ...)]
 3. VALUES (valeur [, valeur] ...) [, (valeur [, valeur] ...)] ...

1. On indique la table dans laquelle on souhaite insérer des données après l'instruction INSERT INTO ;
2. La 2ème ligne est facultative. Elle permet de spécifier dans quelle(s) colonne(s) on souhaite insérer des données, ce qui implique de fournir une valeur pour chaque colonne nommée (effectué en ligne 3) ;
3. C'est dans cette ligne que l'on spécifie les données à insérer dans la table. Si la 2ème ligne est omise, une valeur doit être fournie pour chaque champ que contient la table. Plusieurs enregistrements peuvent être insérés en même temps en les séparant par une virgule.

Exemple : Insérons deux salariés dans la relation suivante :

Salarie (num, nom, prenom, date_embauche, salaire, poste)
num est auto-incrémenté et non-null.

 INSERT INTO Salarie
 VALUES (NULL, 'Arist', 'Léa', '2003-08-05', 1630.41, 'Logisticienne'),
        (NULL, 'Lakab', 'Karim', '2011-12-22', , 'Ingénieur informatique');

Assigner la valeur null à un champ non-null et auto-incrémenté revient à assigner la valeur : plus haute valeur de la colonne (num le plus grand dans l'exemple) + 1, ou 1 si c'est le premier enregistrement de la table.

Une autre manière (notamment si le champ peut être null) de laisser le système gérer la séquence de nombre est d'assigner au champ la valeur zéro.

La partie décimale du salaire est notée à l'aide d'un point puisque la virgule sert de séparateur des valeurs insérées.

Le salaire du deuxième salarié n'a pas encore été déterminé d'où l'absence de valeur entre les deux virgules correspondant à ce champ. Par conséquent, l'insertion du deuxième salarié aurait pu aussi se faire en spécifiant les colonnes insérées (sans le salaire, ni le num car il est auto-incrémenté) :

 INSERT INTO Salarie(nom, prenom, dateEmbauche, poste)
 VALUES ('Lakab', 'Karim', '2011-12-22', 'Ingénieur informatique');

B. La mise à jour de champs d'enregistrements

La modification de données consiste à mettre à jour les valeurs d'un à plusieurs champs d'un à plusieurs enregistrements d'une table :

• cette table est désignée par l'instruction UPDATE,
• la ou les modification(s) à effectuer sont précisée(s) dans la clause SET en affectant (=) sa nouvelle valeur à chaque champ mis à jour (cette nouvelle valeur peut éventuellement être issue d'un calcul ou d'une sous-requête d'interrogation),
• les enregistrements concernés par ces mises à jour peuvent être limités à l'aide de critères de restriction portés par la clause WHERE (voir partie VII) ; en l'absence de telles restrictions, tous les enregistrements sont concernés.

Syntaxe :

 1. UPDATE nom_table
 2. SET nom_colonne = valeur [, nom_colonne = valeur] ...
 3. [WHERE condition_where]

1. Afin de mettre à jour les données d'une table, on utilise l'instruction UPDATE, suivie du nom de la table en question ;
2. Il faut ensuite renseigner dans la clause SET, quelle(s) valeur(s) assigner ou affecter (=) à telle(s) champ(s) ;
3. La clause facultative WHERE est vue dans la partie VII. En son absence, la mise à jour de valeurs affecte tous les enregistrements de la table.

Exemple 1 : Tous les salariés bénéficient d'une augmentation forfaitaire de 12€ :

 UPDATE Salarie
 SET salaire = salaire + 12;

Si le salaire de Karim est encore à null lors de l'exécution de cette requête, il le restera (null + 12 donne null).

Exemple 2 : Léa Arist (salarié n°29) est désormais responsable commerciale :

 UPDATE Salarie
 SET poste = "Responsable commerciale"
 WHERE num = 29;

On opère la restriction sur la salariée par sa clé primaire, du fait qu'elle lui est unique.

C. La suppression d'enregistrements

La suppression (irréversible) d'enregistrements :

• se fait grâce à l'instruction DELETE sur une table spécifiée par la clause FROM,
• les enregistrements concernés par cette suppression peuvent être limités à l'aide de critères de restriction portés par la clause WHERE ; en l'absence de telles restrictions, tous les enregistrements de la table sont concernés.

Syntaxe :

 DELETE FROM nom_table
 [WHERE condition_where]

Exemple : Dans une relation Commande (num, date, numClient), la commande n°2476 a été annulée, elle peut donc être supprimée :

 DELETE FROM Commande
 WHERE num = 2476;

D. Une mise en perspective

On notera que la saisie directe de requêtes SQL de manipulation n'a pas lieu d'être hors tâches d'administration de la base. Dans la plupart du temps ces requêtes sont insérées dans des programmes et leurs différentes valeurs (insérées, mises à jour) sont apportées par le programme à partir de différentes sources (traitement, formulaires...).

A. La projection

La projection est l'opération qui consiste à délivrer des données. Le plus souvent, les requêtes sont exécutées par des programmes pour un affichage ou un traitement.

Exemple : La projection d'une donnée « sexe » délivre les valeurs « F » et « M » qui ne seront pas forcément affichées telles quelles, mais qui peuvent être utilisées pour personnaliser un affichage (« Chère » ou « Cher »).

L'instruction SELECT est utilisée pour retrouver des lignes d'une ou plusieurs tables.

Voici la syntaxe d'une requête SELECT :

 SELECT nom_colonne [, nom_colonne] ...
 FROM nom_table [, nom_table] ...
 [WHERE condition_where]

En l'absence de clause WHERE, tous les enregistrements (lignes) sont projetés.

Syntaxe pour avoir directement tous les champs (colonnes) projetés :

 SELECT *
 FROM nom_table [, nom_table] ...

Exemples :

Utilisons la relation :

Salarie (num, nom, prenom, dateEmbauche, salaire, indemnite, poste)

Exemple 1 : Liste des salariés avec leur nom et prénom :

 SELECT nom, prenom
 FROM Salarie;

Exemple 2 : Liste des libellés de postes de l'organisation :

 SELECT poste
 FROM Salarie;

Un poste sera projeté par salarié, même si plusieurs salariés occupent le même, ce qui est peu pertinent.

 SELECT DISTINCT poste
 FROM Salarie;

Seuls les enregistrements différents sur le(s) champ(s) demandé(s) seront projetés grâce à la clause DISTINCT.

Exemple 3 : Liste des salariés avec leur rémunération (noms des colonnes : "nom", "prenom", "salaire + indemnite") :

 SELECT nom, prenom, salaire + indemnite
 FROM Salarie;

La rémunération est une donnée calculée.

B. Les tris

Les enregistrements projetés ne sont pas triés par défaut. On peut spécifier des critères hiérarchisés de tri grâce à la clause facultative ORDER BY.

Syntaxe :

 SELECT nom_colonne [, nom_colonne] ...
 FROM nom_table [, nom_table] ...
 [WHERE condition_where]
 [ORDER BY nom_colonne [ASC | DESC], ...]

Les tris ont lieu selon l'ordre spécifié dans la clause ORDER BY. Pour chaque critère de tri, le classement peut être ascendant (ASC par défaut si non spécifié) ou descendant (DESC à spécifier).

Exemples :

Exemple 4 : Liste alphabétique des salariés :

 SELECT nom, prenom
 FROM Salarie
 ORDER BY nom ASC, prenom ASC;

ASC est falculatif ("...ORDER BY nom, prenom" est identique).

Exemple 5 : Liste des salariés triés par salaires décroissants :

 SELECT nom, prenom
 FROM Salarie
 ORDER BY salaire DESC;

Un champ figurant dans la clause ORDER BY ne figure pas forcément dans l'instruction SELECT.

Exemple 6 : Liste des salariés triés par rémunération décroissante, celle-ci étant projetée :

 SELECT nom, prenom, salaire + indemnite AS remuneration
 FROM Salarie
 ORDER BY remuneration DESC;

La clause AS (dans l'instruction SELECT) permet de définir un alias (nommage) réutilisable.

À noter que les critères de restrictions (WHERE, partie VII) prennent place avant la clause ORDER BY.

C. Les jointures

Les jointures permettent d'exploiter pleinement le modèle relationnel des tables d'une base de données. Elles sont faites pour mettre en relation des tables concourant à rechercher la réponse à des interrogations. Une jointure permet donc de combiner les colonnes (champs) de plusieurs tables. Les jointures entre tables s'effectuent en imposant l'égalité des valeurs d'une colonne d'une table à une colonne d'une autre table (on parle alors de jointure naturelle).

Exemple :

Considérons le schéma relationnel suivant :

Service (num, libelle, budget)
clé primaire : num (auto-incrémenté)

Salarie (num, nom, prenom, dateEmbauche, salaire, numService)
clé primaire : num (auto-incrémenté)
clé étrangère : numService en référence à num de la relation Service

Exemple : Liste alphabétique des salariés (toutes les données) avec le libellé de leur service :

 SELECT Salarie.*, libelle
 FROM Salarie, Service
 ORDER BY nom, prenom;

En l'absence de jointure, le SGBDR ne « sait » pas comment associer les tables et projette pour chaque ligne de la première table, l'ensemble des lignes de la seconde. Si les tables Service et Salarie comporte respectivement 5 et 10 enregistrements, 50 seront projetés (au lieu des 10 souhaités).

Salarie.* signifie que l'on souhaite afficher tous les champs de la table Salarie. Il s'agit d'un préfixage, ici obligatoire car les tables interrogées (clause FROM) possèdent un champ homonyme.

 SELECT Salarie.*, libelle
 FROM Salarie, Service
 WHERE Service.num = Salarie.numService;

Le critère de jointure associe la clé primaire num de la table Service à la clé étrangère numService de la table Salarie qui lui fait référence.

D. La clause FROM

La clause FROM énumère les tables dont au moins un champ est projeté ou nécessaire à un critère de restriction ou de jointure (certaines tables peuvent n'être qu'un intermédiaire pour établir des jointures entre toutes les tables).

E. Une mise en perspective

Le développeur doit optimiser les temps d'exécution des requêtes en exécutant les critères de restriction avant les jointures (exécutées avant, elles prendraient du temps à mettre en relation des enregistrements dont une partie seraient ensuite éliminée par les restrictions).

A. Les fonctions d'agrégat

Nous avons vu qu'il était possible d'introduire des calculs dans l'instruction SELECT ou dans les clauses WHERE ou ORDER BY mais il s'agit alors de calculs répétés de manière indépendante pour chaque enregistrement (calcul « en ligne »). Une opération est au contraire réalisée en colonne et concerne simultanément tous les enregistrements d'un champ.

Une opération d'agrégat effectuée dans l'instruction SELECT délivrera un seul enregistrement-résultat, la clause ORDER BY n'a donc pas lieu d'être. Par contre, une telle requête peut faire appel à des critères de restriction et de jointure.

Exemple :

Utilisons la relation suivante :

Salarie (num, nom, prenom, dateEmbauche, salaire, indemnite, poste)

Exemple 1 : Le salaire moyen des salariés de l'organisation :

 SELECT AVG(salaire)
 FROM Salarie;

Le résultat de la requête consistera en un seul enregistrement d'une seule valeur, la moyenne de tous les salaires.

Exemple 2 : Les salaires extrêmes de l'organisation :

 SELECT MIN(salaire), MAX(salaire)
 FROM Salarie;

Le résultat de la requête consistera en un seul enregistrement comportant deux valeurs.

Exemple 3 : La date d'embauche de l'« ingénieur » ayant le plus d'ancienneté :

 SELECT MIN(dateEmbauche)
 FROM Salarie
 WHERE poste = "Ingénieur";

Exemple 4 : Le total des indemnités versées :

 SELECT SUM(indemnite)
 FROM Salarie;

Exemple 5 : Le total des rémunérations versées :

 SELECT SUM(salaire) + SUM(indemnite)
 ou
 SELECT SUM(salaire + indemnite)
 FROM Salarie;

Exemple 6 : Le nombre de salariés :

 SELECT COUNT(num)
 FROM Salarie;
 ou
 SELECT COUNT(*)
 FROM Salarie;

Exemple 7 : Le nombre de postes différents (distincts, qu'occupent les salariés) :

 SELECT COUNT(DISTINCT poste)
 FROM Salarie;

Le mot-clé DISTINCT permet de ne compter qu'une fois les postes occupés par plusieurs salariés.

B. Le regroupement des résultats

Les opérations présentées auparavant et exécutées dans l'instruction SELECT délivraient un seul enregistrement-résultat. La clause GROUP BY permet d'effectuer des opérations par sous-groupes d'enregistrements, délivrant ainsi plusieurs enregistrements-résultat (un pour chaque sous-groupe d'enregistrements). La clause ORDER BY retrouve ici sa raison d'être. Les critères de restriction et de jointure peuvent toujours être présents.

Exemples :

Considérons le schéma relationnel suivant :

Service (num, libelle, budget)
clé primaire : num (auto-incrémenté)

Qualification (num, libelle, niveau)
clé primaire : num (auto-incrémenté)

Salarie (num, nom, prenom, dateEmbauche, salaire, indemnite, poste, numService)
clé primaire : num (auto-incrémenté)
clé étrangère : numService en référence à num de la relation Service

Competence (numSalarie, numQualif, dateAcq)
clé primaire : numSalarie, numQualif
clés étrangères :

• numSalarie en référence à num de la relation Salarie,
• numQualif en référence à num de la relation Qualification

Exemple 1 : Le montant des salaires de chaque service :

 SELECT libelle, SUM(salaire)
 FROM Service, Salarie
 WHERE Service.num = Salarie.numService
 GROUP BY libelle;

Tous les salaires de salariés d'un même service seront totalisés ; il y aura autant d'enregistrements-résultat que de services.

Exemple 2 : Le nombre de qualifications possédées par chaque salarié :

 SELECT nom, COUNT(*)
 FROM Salarie, Competence
 WHERE Salarie.num = Competence.numSalarie
 GROUP BY num, nom;

Le regroupement par numéro (clé primaire) assure que la requête traitera chaque enregistrement ; tous les champs figurant dans SELECT doivent être placés dans GROUP BY, qui peut porter des champs supplémentaires.

Exemple 3 : Le salaire moyen par service, sauf direction, par ordre décroissant :

 SELECT Service.libelle, AVG(salaire) AS moyenneSalaireService
 FROM Service, Salarie
 WHERE Service.num = Salarie.numService AND Service.libelle <>  "Direction"
 GROUP BY Service.num, Service.libelle
 ORDER BY moyenneSalaireService DESC;

C. Les restrictions d'affichage des résultats

La clause HAVING permet une restriction d'affichage sur les résultats d'une opération avec regroupement. Cette restriction intervient après l'exécution de l'opération. Elle ne doit pas être confondue avec la clause WHERE qui restreint les enregistrements auxquels doit s'appliquer l'opération et qui intervient donc avant.

Exemple 4 : Les services comptant au moins 4 salariés :

 SELECT Service.libelle, COUNT(*)
 FROM Service, Salarie
 WHERE Service.num = Salarie.numService
 GROUP BY Service.num, Service.libelle
 HAVING COUNT(*) >= 4;

Exemple 5 : Le salaire moyen des services pour lesquels ce salaire moyen n'excède pas 2 000€ :

 SELECT Service.libelle, AVG(salaire) AS salMoy
 FROM Service, Salarie
 WHERE Service.num = Salarie.numService
 GROUP BY Service.num, Service.libelle
 HAVING salMoy <= 2000;

A. Les utilisateurs

Les bases de données comptent souvent plusieurs utilisateurs, notamment lorsqu'elles sont partagées (par exemple entre les acteurs d'un processus). Ces utilisateurs ont des besoins différents.

Pour des raisons de sécurité des données, de responsabilisation des acteurs, de traçabilité des actions, il est possible et souhaitable de mettre en place un contrôle d'accès aux données.

Les risques portant sur les données sont :

• la suppression ou la modification de données (accidentelle ou malveillante) pouvant provoquer des dysfonctionnements du système d'information et donc de l'organisation,
• le vol de données confidentielles (mettant en péril la compétitivité ou l'image de l'organisation).

Il s'agit d'attribuer à chaque utilisateur des droits d'accès (ou privilèges) ne correspondant qu'à ses besoins. L'attribution à une personne d'un compte d'utilisateur, associé àà un profil de droits est un choix organisationnel effectué conjointement par le gestionnaire du SI et les responsables métiers.

En pratique, on crée des profils d'utilisateurs définissant les acteurs par leur rôle organisationnel (comme dans les processus) et leurs attribuant des droits. Les utilisateurs sont des personnes physiques identifiées qu'on associe à un profil d'utilisateurs (plusieurs utilisateurs peuvent avoir le même profil).

À ces notions de droits s'ajoutent des considérations « techniques » que nous n'étudierons pas ici : partage en réseau, gestion des accès simultanés...

Il existe trois catégories de profils d'utilisateurs :

• administrateur :
  • l'administrateur du SGBDR possède tous les droits,
  • l'administrateur d'une base de données hébergée par le SGBDR, possède tous les droits sur cette base ;
• utilisateur final dont les droits doivent être limités aux besoins ; il peut exister plusieurs profils d'utilisateur final, chacun d'eux pouvant être associés à plusieurs personnes physiques ;
• développeur dont les droits sont aussi limités aux besoins (généralement plus étendus que ceux d'un utilisateur final) ; là aussi plusieurs profils peuvent coexister et être associés à plusieurs personnes physiques.

B. Les droits sur les bases de données

Les droits d'accès définissent ce que l'utilisateur a le droit de faire sur une base de données (tout ce qui ne lui est pas autorisé explicitement lui est interdit), via l'application. Ils peuvent porter sur plusieurs domaines (on indique entre parenthèses l'instruction correspondante) :

• l'exploitation des données :
  • consultation d'enregistrements (SELECT),
  • ajout d'enregistrements (INSERT INTO),
  • mise à jour d'enregistrements (UPDATE),
  • suppression d'enregistrements (DELETE),
• la structure de la base de données :
  • création de tables ou de vues (CREATE),
  • modification de tables ou de vues (ALTER),
  • suppression de tables ou de vues (DROP),
• l'administration des droits :
  • création de droits (GRANT),
  • révocation de droits (REVOKE).

C. Une mise en perspective

L'utilisateur final n'accède pas directement à la base de données, il le fait par l'intermédiaire d'une application métier et de ses interfaces (qui lui évite de saisir du SQL !). Pour lui, l'accès est transparent, on pourrait même dire que l'application est l'« utilisateur » de la base de données puisque c'est elle qui s'y connecte et transmet des requêtes SQL.

Il est donc de la responsabilité des développeurs que l'application ne permette à un utilisateur de n'exécuter que les actions (et les répercussions sur la base de données) dont il a besoin, ce besoin étant défini d'un point de vue organisationnel.

Toutefois, il est quand même prudent de « doubler » ce contrôle au niveau de l'application, par un contrôle au niveau des données, pour parer à certaines attaques. Ainsi l'application se connectera à la base de données avec un profil d'utilisateur, enregistré dans le SGBDR, ne comportant que les droits nécessaires (si un pirate parvient à s'emparer de ce profil, sa capacité de nuisance sera limitée).