Les systèmes de gestion de base de données – SQL et NoSQL

La gestion des données

L’importance des données

Les données sont présentes partout, capital immatériel des entreprises, essentiel dans plusieurs domaines. Le stockage de masse sur ordinateur a débuté dans les années 50. Les capacités de stockage sont en perpétuelle évolution.

  • dans les statistiques des entreprises et des gouvernements
  • en intelligence économique pour déterminer les facteurs d’amélioration
  • dans les comptabilités des entreprises
  • dans les applications pour des services (transport, vente etc)
  • pour la surveillance des territoires ( eau, urbanisme etc), des populations et plus récemment des objets.

 

 

 

Organisations de fichiers

Séquentiel

Le principe de cette organisation est de gérer les enregistrements comme des suites d’octets structurées.
L’avantage réside dans la simplicité d’utilisation, la standardisation des structures et dans l’optimisation de l’espace de stockage.
Par contre, les fichiers sont indépendants les uns des autres dans l’application et les lectures ne peuvent se faire que séquentiellement.

Séquentiel indexé

C’est une amélioration de l’organisation séquentielle, par l’ajout d’un fichier de clés (ou d’index), lié au fichier séquentiel.
Ce fichier d’index contient des critères de recherche triés (index) et l’adresse de la donnée correspondante dans le fichier séquentiel.
En plus des avantages du système séquentiel (simplicité, standardisation), on peut donc retrouver rapidement un enregistrement en fonction de la clé.
Le principal inconvénient reste l’indépendance des fichiers dans l’application, d’où la nécessité de beaucoup de programmation pour gérer l’intégrité des données.

Bases navigationnelles

Ce sont des collections de fichiers, logiquement appareillés entre eux. Ces bases ont été créées sur des systèmes propriétaires afin de compenser la faiblesse des organisations précédentes.
Les bases navigationnelles sont complexes à maintenir.

Les systèmes de gestion de base de données relationnelle

Les SGBD relationnelles sont basés sur le modèle relationnel. Ils sont apparus dans les années 70.
Une base de données relationnelle est une collection de données mises en relation dans des tables logiques ; une table étant un ensemble de lignes et de colonnes.
Les Systèmes de Gestion de Bases de Données Relationnelles gèrent indépendamment le niveau logique (tables) et le niveau physique (fichiers).
Le succès des SGBDR provient notamment de cette caractéristique. La gestion par l’utilisateur du niveau logique uniquement, donne une grande simplicité dans la gestion des données.

Big Data

Ensemble de données trop volumineux pour les capacités d’analyse humaine et la plupart des systèmes de gestion de données classiques. Nouveaux ordres de grandeurs pour obtenir des tendances et la manière dont ces données sont stockées. Le volume considérable et la variété des données nécessitent des applications avec des temps de réponses et des algorithmes différents des systèmes classiques. La vitesse est primordiale.

 

 

Les critères de choix d’un SGBD sont principalement le type de données,le type de requête et le temps d’exécution en lecture et en écriture.
Pour les serveurs qui l’héberge, le coût, la configuration à mettre en œuvre et le taux de disponibilité.

Disponibilité en %          Indisponibilité par année
99 % (deux neuf)            3,65 jours
99,9 % (trois neuf)         8,76 heures
99,99 % (quatre neuf)   52,56 minutes
99,999 % (cinq neuf)     5,26 minutes
99,9999 % (six neuf)     31,5 secondes

Représentation d’une base de données

 

Schéma réseau

Le serveur web et le navigateur peuvent être remplacés par une application lourde.

 

Les Data center dans le monde

Les Data Center sont composés de milliers de serveurs en rack. Les applications sont virtualisées pour une facilité de maintenance et un découpage des ressources. Le stockage se compte en pétaoctets et tend actuellement vers l’exaoctet pour les nouveaux Data center, 1Po =1024²Go =1 048 576 Go. 1Eo=1024 Po.
Les principaux critères d’un Data center sont sa capacité de stockage, son taux de disponibilité supérieur à 99% et sa consommation d’énergie équivalent à une grosse ville.

Une classification des data center en Tier :

Type- Caractéristiques- Indisponibilité- Maintenance à chaud- Tolérance aux pannes
Tier I      Faibles                                                         28,8 h Non Non
Tier II    Redondance partielle                                22 h   Non Non
Tier III   Maintenabilité des composants              1,6 h Oui Non
Tier IV   Tolérance pannes                                       0,4 h Oui Oui

Un exemple de Data center gouvernemental
https://nsa.gov1.info/utah-data-center/
https://fr.wikipedia.org/wiki/Utah_Data_Center

 

http://www.datacentermap.com/

 

 

 

 

Les métiers de la données

  • Chief Data Officer (CDO)
  • Business Intelligence Manager
  • Master Data Manager
  • Data Architect
  • Data Protection Officer
  • Data Analyst
  • Data Miner
  • Data Scientist
  • Database Administrator
  • Chief Information Officer (CIO)
  • tous les autres métiers connexes, developer, network administrator…

 

Le SQL

Le SQL, structured query language, est un langage de requête utilisé pour la manipulation de bases de données relationnelles. Il a été créé au milieu des années 70 par IBM. Le premier produit SQL, Oracle a été mis sur le marché en 1979. Oracle est l’un des principaux acteurs du marché des technologies de bases de données relationnelles. Il d’autres langages de requête.

Depuis l’approbation en 1986 de l’implémentation IBM, le SQL a été défini comme le langage standard de communication des bases de données relationnelles. Attention, certaines commandes diffèrent selon le SGBDR. Les exemples ci-dessous sont adaptés pour MariaDB et MySQL.
https://mariadb.com/kb/en/library/documentation/
https://dev.mysql.com/doc/

Les structures de données

Création d’une base de données

CHARACTER SET : liste de symboles.
COLLATE : règles de comparaison des caractères.

Création d’une table

InnoDB : moteur de stockage.
Les différents types de données avec MariaDB https://mariadb.com/kb/en/library/data-types/

 

Avec contraintes sur colonnes, le NOCLI et le NOMCLI sont obligatoires. Le CODE_POSTAL doit être dans l’intervalle indiqué.

Définir les contraintes d’intégrité.

Clef primaire composée.

Créer la table fournisseur. On souhaite connaitre le nom, l’adresse, le numéro de téléphone du fournisseur.

Suppression d’une table

Supprimer une table revient à éliminer sa structure et toutes les données qu’elle contient ; les index associés sont également supprimés.

Si la clé primaire de la table est référencée dans d’autres tables par des contraintes REFERENCES ou FOREIGN KEY, la clause CASCADE CONSTRAINTS permet de supprimer ces contraintes d’intégrité référentielle dans les tables « enfants ».

Suppression d’une table CLIENT dont la colonne NOCLI est clé étrangère dans la table COMMANDE :

Modification d’une table

Il est possible de modifier la structure d’une table à plusieurs niveaux :

  • ajout de colonnes (nom, type, valeur par défaut, contrainte NOT NULL),
  • ajout de contraintes de colonne (contrainte NOT NULL uniquement),
  • ajout de contraintes de table,
  • redéfinition d’une colonne (type, valeur par défaut),
  • activation, désactivation de contraintes de colonne ou de table,
  • suppression de contraintes de colonne ou de table,
  • changement du nom de la table.
  • autorisation ou non de modifications dans la table.

Ajout ou modification de colonnes

Ajouter le champ dateCreation à la table fournisseur

Ajout d’une contrainte de table

L’exemple suivant permet d’ajouter une contrainte de validation à la table ARTICLE.

Si des données sont déjà présentes dans la table au moment où la contrainte d’intégrité est ajoutée, alors toutes les lignes d’information doivent vérifier la contrainte. Dans le cas contraire, la contrainte n’est pas posée sur la table.

Ajout d’une clef étrangère

Ajouter la clef étrangère fournisseur à la table article

Suppression d’une contrainte

Suppression de colonnes

Il est possible de supprimer une colonne en utilisant la clause DROP COLUMN de l’instruction ALTER TABLE.

Supprimer la colonne TEL dans la table CLIENT.

Changement de nom d’une table

L’instruction RENAME permet de renommer une table mais également les vues.

Renommer la table ARTICLE en table PRODUIT.

Manipulation de données

Création de lignes

L’ajout d’une ligne à la table est réalisé si les contraintes sont respectées. Si les noms des colonnes à valoriser ne sont pas cités, une expression doit être donnée pour chaque colonne dans l’ordre des définitions de colonne faites lors de la création de la table. Aucune colonne ne peut être omise.

 

Création d’un client (les colonnes non citées sont initialisées à NULL).

Valorisation de toutes les colonnes pour un article :

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Ajouter un fournisseur et un article

L’instruction INSERT permet d’ajouter une ou plusieurs lignes dans la table de destination.
Si l’insertion d’une ligne d’informations est le fonctionnement le plus fréquent, il est également possible d’ajouter plusieurs lignes dans la table. Ces lignes insérées de façon massive sont extraites de la base de données par une requête de type SELECT.
Ce processus facilite le transfert de données d’une table à une autre.

Suppression de lignes

L’instruction DELETE supprime toutes les lignes d’une table. Si la clause WHERE est utilisée, seules les lignes pour lesquelles la condition est vraie sont supprimées.
En pratique la suppression a rarement lieu sur une base de données déployée. On préfère indiquer par une colonne de type date l’indisponibilité de la donnée.

Suppression d’un article facture :

Suppression d’articles valant entre 153 et 306 euros.

Ou

Suppression des articles dont la désignation commence par « Ca » (AA00;AB03) :

Suppression des articles dont la quantité en stock n’est pas valorisée :

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Supprimer les commandes qui ont une date à NULL
  • Supprimer tous les fournisseurs

Modification de lignes

L’instruction UPDATE permet de remplacer, dans une table, la valeur des colonnes spécifiées par des expressions. Si aucune clause WHERE n’est spécifiée, la mise à jour est réalisée pour toutes les lignes de la table. Dans le cas contraire, seules les lignes pour lesquelles la condition spécifiée dans la clause WHERE est vérifiée sont mises à jour.

Mise à jour de la quantité en stock à 15 pour l’article AB10 :

Augmentation de 10% du prix pour les articles dont la référence commence par AB :

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Modifier le nom du fournisseur 425 par Harrys
  • Modifier l’adresse du client Dufour Liliane par 14 rue du Tilleuls Cergy 95300

Interrogation de données

L’instruction SELECT permet d’afficher les données d’une table. Le symbole * permet de séléctionner toutes les colonnes.

Projection

La projection a pour but d’éliminer les colonnes inutiles. Elle se fait en SQL en ne citant que les colonnes voulues dans le SELECT.

Alias

Nom alternatif donné à une colonne ou à une table dans une requête.

Les alias de colonne permettent de :

  • Changer le nom de la colonne à l’affichage ou pour la table résultante.
  • Donner un nom comportant des caractères spéciaux (espace par exemple).

Les alias de table définis dans les clauses FROM correspondent à des synonymes internes à la requête. Ils permettent d’alléger l’écriture de l’instruction et de référencer dans des contextes différents la même table.

Affichage d’un nom de colonne comportant des espaces :

Distinct

La syntaxe DISTINCT permet de n’afficher qu’une occurrence de ligne dans le cas où la requête ramène plusieurs lignes identiques. Le mot clé DISTINCT permet uniquement d’effectuer l’affichage de valeurs distinctes.

Affichage d’une ligne par nom de client :

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher les différentes désignations d’articles.
  • Afficher les différentes dates des commandes

Restriction

La restriction permet de n’obtenir que les lignes répondant à une condition.

Références ayant moins de 20 unités en stock :

Restriction sur le numéro de commande dans la table COMMANDE (nocde= 100) :

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher les articles dont les prix sont supérieurs à 150 euros.
  • Afficher le client numéro 200
  • Afficher les commandes du client numéro 20

Le tri

Pour obtenir un résultat trié, on utilise la clause ORDER BY en fin de commande SELECT.
Par défaut, le tri est croissant. Le tri décroissant est obtenu à l’aide de l’option DESC de la clause ORDER BY. L’ordre de tri peut également être spécifié en indiquant dans la clause ORDER BY le numéro d’ordre correspondant dans la clause SELECT à la colonne servant au tri.

Affichage des articles triés par famille et prix décroissant.

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher le nom et le prénom des clients par ordre alphabétique.
  • Afficher les articles par ordre de prix décroissant.

Calculs élémentaires

Le calcul élémentaire permet d’obtenir des colonnes calculées pour chaque ligne.

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher le prix TTC des articles.

Jointures

La jointure permet de lier chaque ligne d’une table avec des lignes d’une autre table en respectant une condition.

 

 

INNER JOIN

select * from A
inner join B on A.key=B.key

select * from A
inner join B on A.key=B.key
inner join C on B.key=C.key

On peut aussi écrire la jointure directement avec les clefs mais la jointure n’est pas explicite.

NATURAL JOIN diffère, elle recherche automatiquement les colonnes qui ont le même nom et type dans les deux tables. La syntaxe peut varier selon le SGBDR. Il est préférable d’utiliser INNER JOIN en SQL pour éviter que la modification d’une table influe sur le résultat des requêtes.

On préférera donc INNER JOIN.

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher les dates de commande du ou des clients John
  • Afficher le prix des articles de la commande 324.
  • Afficher les adresses des fournisseurs pour l’article 328.
  • Afficher le nom des clients qui ont commandé chez le fournisseur WAMTER.

 

Les jointures

 

CROSS JOIN (aucune jointure, produit cartésien)

select * from A cross join B
ou
select * from A,B

 

NATURAL JOIN (à éviter)

select * from A
natural join B

 

INNER JOIN

select * from A
inner join B on A.key=B.key

 

LEFT JOIN

select * from A
left join B on A.key=B.key

select * from A
left join B on A.key=B.key
where B.key is null

 

RIGHT JOIN (inverse)

select * from A
right join B on A.key=B.key

select * from A
left join B on A.key=B.key
where B.key is null

 

FULL JOIN

select *
from A
full join B on A.key = B.key

select *
from A
full join B on A.key = B.key
where A.key is null
or b.key is null

 

 

Le regroupement (agrégat)

On peut regrouper les valeurs grâce à la clause GROUP BY. Attention, cette clause ne trie pas par ordre alaphabéthique. Ne pas confondre avec ORDER BY.

Regroupement par NOM et VILLE.

On utilisera le GROUP BY avec ces fonctions de groupe:

 

Nombre de client sur la table CLIENT :

Nombre de clients par ville.

Prix le plus élevé et moyenne des quantités en stock par article:

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher le nombre de commandes par clients.
  • Afficher le nombre d’articles par code tva
  • Afficher le nombre de commandes pour le client 324
  • Afficher le nombre de commandes pour le client 325 et le client 326
  • Afficher le nombre de commandes du client 328 groupé par date.
  • Afficher le nombre de commandes du client 328 chaque jour.
  • Afficher le prix maximum des articles par fournisseur
  • Afficher la somme du prix des articles

Restrictions sur regroupement (agrégat)

Lorsque l’on souhaite restreindre le nombre de lignes renvoyées par une requête comportant un calcul d’agrégats, il est possible d’utiliser la clause HAVING.

Les clients ayant plus de cinq commandes :

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Afficher les commandes du client 400 groupé par date à partir du 18/09/2011
  • Afficher les nombres de commandes supérieurs à 7 groupé par date à partir de 2011
  • Afficher les articles groupés par catégorie dont les prix totaux sont supérieurs à 25 euros

Les vues

Les vues sont des tables virtuelles présentant le résultat d’un SELECT.

L’un des intérêts de l’utilisation des vues vient du fait que la vue ne stocke pas les données, mais fait référence à une ou plusieurs tables d’origine à travers une requête SELECT, requête qui est exécutée chaque fois que la vue est référencée. De ce fait, toute modification de données dans les tables d’origine est immédiatement visible dans la vue dès que celle-ci est à nouveau exécutée.

Les cas d’utilisation des vues sont multiples :

  • Cacher aux utilisateurs certaines colonnes ou certaines lignes en mettant à leur disposition des vues de projection ou de restriction. Ceci permet de fournir un niveau de sécurité supplémentaire.
  • Simplifier l’utilisation de tables comportant beaucoup de colonnes, beaucoup de lignes, ou des noms complexes, en créant des vues avec des structures plus simples et des noms plus explicites.
  • « Sauvegarder » des requêtes fréquemment utilisées sous un nom.
  • Simplifier la saisie des instructions SQL pour les utilisateurs en masquant les jointures fréquemment utilisées.

Les vues, une fois créées, sont utilisables comme des tables.

Une vue définie par une jointure supporte les instructions INSERT, UPDATE, DELETE si elle référence dans sa définition une table dont la (les) colonne(s) clé primaire apparaît (apparaissent) dans la liste des colonnes projetées de la jointure et si les instructions INSERT, UPDATE, DELETE portent sur cette table.

Création

Vue retournant les clients du département 95. L’option WITH CHECK OPTION empêche toute insertion de client n’appartenant pas à ce département.

La vue V_CLICMD sauvegarde la jointure CLIENT-COMMANDE.

OR REPLACE
Permet le remplacement de la description par la nouvelle requête si la vue existe déjà. En effet, la définition d’une vue ne peut être modifiée partiellement.

READ ONLY
Interdit toute insertion, modification, suppression de données à travers la vue.

Suppression

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  • Réaliser une vue permettant d’afficher les adresses de fournisseur de chaque article
  • Réaliser une vue permettant d’afficher chaque commande avec sa date, le prix, la catégorie, et la quantité de stock des articles.
  • Réaliser une vue permettant d’afficher le nom des clients qui ont commandé chez le fournisseur DELBA.

La gestion des utilisateurs

On attribut des comptes aux administrateurs de base de données et aux applications. Pour l’accès classique, en lecture, on privilégie la gestion des droits par un annuaire LDAP dont la structure diffère du modèle relationnel. On gère toutefois des données…

Pour des raisons de sécurité, le système de base de données doit posséder une gestion des utilisateurs. Il est nécessaire de créer des comptes puis de leur attribuer des droits. Ces comptes doivent être protégés avec un mot de passe. Il n’existe pas de standard SQL pour créer des utilisateurs, les requêtes varient selon le SGBDR.

Attribuer des droits

Pour attribuer des droits à un utilisateur, on utilise la commande GRANT.

Avec l’option « grant option », l’utilisateur 1 peut donner à son tour les droits de selection et d’insertion sur la table client.

Public désigne l’ensemble des utilisateurs

Retirer des droits

Pour retirer des droits à un utilisateur, on utilise la commande REVOKE.

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  1. Donner des droits de sélection sur la table articles à tous les utilisateurs
  2. Donner des droits d’insertion et de modification sur la table fournisseur à admin1
  3. Donner des droits de suppression et de modification sur la table fournisseur, ligcde à admin2 avec la possibilité de transmettre ces droits.
  4. Enlever les droits de modification sur la table fournisseur à admin1.

Il est possible d’attribuer des droits à des groupes et d’inclure des utilisateurs à ces groupes ce qui facilite la gestion des droits de la base.

Il est possible de supprimer un groupe comme un utilisateur. On peut lancer une session d’un utilisateur avec l’instruction set role.

Créer un groupe d’administrateur, donner les droits d’insertion, de modification, de suppression sur les tables commandes et fournisseurs au groupe puis inclure les deux utilisateurs admin1 et admin2 au groupe.

Les requêtes imbriquées

Comparaison avec une valeur ( =, >, <, <=, >=,<>) ou un ensemble de valeur ( IN )

Dans une sous-requête imbriquée il n’y a pas de lien explicite entre la requête interne et la requête externe. La requête interne est exécutée une seule fois pour construire la liste de valeurs, avant l’exécution de la requête externe (quel que soit le nombre de lignes ramenées par celle-ci, sauf si le résultat de la seconde dépend de la premiere).

Exemple avec une seule valeur de retour

Liste des commandes dont le prix est supérieur au produit 12.

Exemple avec un ensemble de valeur en retour

Liste des clients qui habitent dans la même ville que le client « DUBOIS Jean ».

Exists

La condition est vraie si la sous-requête retourne au moins une ligne.

La liste des clients n’est affichée que si au moins une commande existe dans la table COMMANDE.

Liste des clients pour lesquels il n’existe pas de commande.

Any

Compare, suivant l’opérateur donné (=,<>,<,<=,>,>=), les valeurs des colonnes spécifiées avec chacune des valeurs de la liste. L’expression est vraie si au moins une des comparaisons est vraie.

Affichage des articles ayant le même prix que l’article ZZ01 :

All

Compare, suivant l’opérateur donné (=,<>,<,<=,>,>=), les valeurs des colonnes spécifiées avec chacune des valeurs de la liste. L’expression est vraie si toutes les comparaisons sont vraies. La liste de valeurs peut être une liste de constantes littérales ou des valeurs retournées par une sous-requête.

Résumé

Exists -> if
in -> {}
prix>=32
prix>={}
prix >=ANY {10} -> au moins 1
prix >=ALL {10} -> les 10 doivent être vrai

ARTICLE : refa, designation, prixht, tauxtva, categorie, qtestock, numF
Clef primaire : refa
Clef étrangère : numF en référence à Fournisseur.numF

COMMANDE : nocde, nocli, datecde, etat
Clef primaire : nocde
Clef étrangère : nocli en référence à Client.nocli

CLIENT : nocli, nomcli, prenomcli, adrcli, codePostal, ville
Clef primaire : nocli

FOURNISSEUR : numF, nom, adresse, numtel, datecreation
Clef primaire : numF

LIGCDE : nocde, refart, qtecde
Clef primaire : nocde, refart
Clef étrangère : nocde en référence à Commande.nocde, refart en référence à Article.refa

  1. Afficher les dates des commandes du client 324 qui sont plus récentes que certaines commandes du client 24.
  2. Afficher les dates de commandes du client 325 qui sont égales aux dates de commandes du client 328.
  3. Afficher les dates de commandes du client 326 qui sont inférieurs à toutes les commandes du client 529.
  4. Afficher tous les fournisseurs qui n’ont pas d’articles.
  5. Afficher les noms des fournisseurs qui ont la même date de création que le fournisseur Zarg.

Triggers et procédures stockées

Une procédure stockée est un ensemble d’instruction SQL stockées dans le SGBDR.

Il est possible d’écrire en langage PL/SQL selon le SGBDR, très proche du C avec des instructions SQL.  Il est nécessaire de rechercher les instructions en allant voir les documentations techniques de chaque SGBDR utilisés.

Définir une procédure stockée permet d’exécuter du code coté SGBDR indépendamment des applications qui vont utiliser les bases de données. Toutefois le temps de traitement peut considérablement augmenter. En pratique, on utilise peu les procédures stockées.

Déclaration de variables

On utilise @ devant le nom de la variable.

Les procédures stockées

Création de procédure

Cette procédure renvoie le nombre de client via le paramètre nommé param1. Il faut indiquer un délimiter, le plus généralement un caractère comme | ou # qui va indiquer au SGBDR le début et la fin de la procédure.

paramètre :

On peut préciser que le paramètre est en entré (IN) en sortie (OUT) ou les deux. IN est par défaut, il n’y a pas besoin de le préciser.

Appel de procédure

On appelle une procédure grâce à son nom avec une variable.  On exécute la procédure  puis on l’affiche avec le select

 

Les fonctions stockées

différence entre une procédure et une fonction
https://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when

Création de fonction

On déclare une fonction qui prend une chaine de caractère. Une fonction renvoie une valeur contrairement à une procédure. On renvoie ici une autre chaine de caractère représentant la concaténation du paramètre et de Bonjour.

Appel de fonction

L’appel s’effectue grâce au select :

Affichage

 

Les triggers

Un trigger est une procédure stockée qui est exécutée automatiquement lors d’un évènement dans le SGBDR sur une vue ou une table. Un trigger est un déclencheur en français.

Création de déclencheurs

Le trigger définit une variable @sum qui va ajouter le montant à avant chaque insertion.

L’événement est soit BEFORE ou AFTER.
L’action correspond à une instruction SQL (update, select etc).
On accède au futur variable grâce à NEW.nomColonne (insert, update) et aux anciennes grâce à OLD.nomColonne (delete, update).

Exécution automatique

Affichage

Accès concurrent et montée en charge

La scalabilité horizontale correspond à l’ajout de machines supplémentaires. Ceci permet une tolérance à la panne, des mises à jour sans interruption de service mais en contre partie un cout des licences élevé et administration importante.

La scalabilité verticale consiste à rajouter des ressources supplémentaire à la machine (CPU, RAM, disque carte réseau…). Il n’y a pas de tolérance de panne et les mises à jour necessite une interruption de service.

 

Lors de l’utilisation de son application par plusieurs utilisateurs, il faut vérifier que son code n’engendrera pas d’erreur lors des manipulation de données.
Ici on bloque la table en écriture pour éviter que le numéro de l’équipe obtenu ne soit pas celui correspondant à l’insertion. LOCK TABLE / UNLOCK TABLES

 

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

Les systèmes de gestion de bases de données NoSQL (Not Only SQL) se distinguent des SGBD relationnelles (SGBDR) notamment par leurs performances. Les enregistrements ne sont plus faits sous forme de tables mais, généralement, sous forme de collections gérées par un système de clef/valeur. Elles permettent de ne pas restreindre la base à son schéma.

Le système est simplifié et répond aux problématiques du Big Data grâce à son principe de scalabilité (il est possible de maintenir des performances en augmentant le nombre de serveurs).Il permet aussi une plus grande portabilité sur différentes plateformes (Android, Windows, Linux) tout en utilisant moins de ressources machine (mémoire et processeur).

Intérêts des SGBDR

  • Un SGBDR s’occupe de la gestion des données et de leur mise à disposition. Les applications clientes utilisant des SGBDR n’ont donc pas à implémenter cette gestion.
  • La structure physique des données n’a pas d’influence sur le code de l’application client.
  • Les requêtes de traitement des données ne modifient pas systématiquement le code d’une application, notamment grâce aux vues.
  • Un SGBDR vérifie les contraintes d’intégrité établies par l’utilisateur et garantit par là même la cohérence des données.
  • Un SGBDR gère les accès concurrents pour éviter des incohérences lors de modification.

Limites des SGBDR

  • Le maintien de l’intégrité des données et la gestion des accès concurrents ne favorise pas les performances, surtout lors de la répartition de charge sur plusieurs serveurs.
  • Selon le SGBDR choisi, le prix des licences, lors de la multiplication des serveurs, est également à prendre en compte.

Comparaison

Système NoSQL Relationnel
Capacité de stockage Très élevée (> 1 To) Modérée (< 1 To)
Architecture Distribuée Centralisée
Modèle de données Destructuré Relationnel (tabulaire)
Réponse à la charge Lecture et écriture Lecture en majorité
Scalabilité Horizontale (nombre) Verticale (puissance)
Moteur de requêtes Propre au système SQL
Principales caractéristiques BASE ACID
Ancienneté de la technologie Récente Eprouvée

 

ACID atomicité, cohérence, isolation et durabilité
BASE basically available (disponibilité en lecture et en écriture), soft state (l’état du système change au cours du temps sur les différentes machines), eventually consistent (convergence vers l’état le plus récent au bout d’un certain temps)

 

Les différents types de SGBD NoSQL

Clé/Valeur :

Les données sont représentées sous forme de couple clef/valeur, comme pour les dictionnaires (HashMap). Les valeurs ne sont pas typées. L’application cliente prend en charge les contraintes et le typage des données.
Produits : Redis, Riak et Voldemort de LinkedIn

Orienté colonne :

Très proche des SGBDR, le nombre de colonnes est dynamique pour chaque enregistrement éliminant le problème des colonnes ayant pour valeur NULL.
Produits : HBase, BigTable de Google, Cassandra

Orienté document :

Système fonctionnant grâce au principe de clef/valeur, les données sont enregistrées sous forme de XML ou JSON. Les informations sont enregistrées sous forme structurée, plus proche des langages objets, évitant ainsi un certain nombre de jointures par rapport à un SGBDR.
Produits : CoucheDB, MongoDB, RavenDB

Orienté graphe :

Ce système enregistre des objets et leurs relations. Il répond à certaines problématiques de recherche de données grâce aux théories des graphes (plus court chemin). La recherche des relations entre données est facilitée. On notera qu’il est possible d’utiliser une base de données orientée document pour sauvegarder les relations et les nœuds. Pour cela, les bases de données utilisant le stockage natif de graphe sont rares
Produits : Neo4j

Intérêts et limites des SGBD NoSQL

Leur principal intérêt est de favoriser la disponibilité et le partitionnement, au détriment de la cohérence des données.

Le théorème de Brewer indique qu’un système distribué ne peut garantir que deux des trois contraintes suivantes à la fois :

  • Cohérence : les données sont les mêmes quelles que soient la modification et la répartition ;
  • Disponibilité : toute requête reçoit une réponse ;
  • Tolérance au partitionnement : continuité de fonctionnement en cas d’ajout de partition (ou nœud).

D’après ce théorème, un système de calcul distribué ne peut garantir à un instant T que deux de ces contraintes mais pas les trois.

La complexité est désormais dans le code applicatif pour assurer l’intégrité des données. Les schémas sont donc plus flexibles. Au contraire, un SGBDR priorisera la contrainte de cohérence, ce qui influera fortement sur les performances, surtout en cas de création de nœuds !

 

Le NoSQL permet de s’affranchir d’une structure déterminée à l’avance. Nous ne sommes plus contraints par des tables et des données dont le type est précisé. Il est aussi possible de distribuer les données plus facilement par l’ajout de nouveaux serveurs. Ces bases répondent aussi aux besoins de changement rapide, notamment pour les développeurs qui ne possèdent pas la structure exacte de leurs données lors des différentes phases de développement.

2018-11-10T11:34:57+00:00By |Tags: |

Leave A Comment