Le Saigneur Sombre Posté(e) le 1 septembre 2006 Partager Posté(e) le 1 septembre 2006 Le but de ce topic est de comprendre et maitriser les bases de données relationnelles. Je vais commencer par des généralités, puis je parlerai de SQL en commençant par les opérations ensemblistes (Il y’a d’excellent site pour apprendre la syntaxe SQL, http://www.w3schools.com par exemple). Ensuite je parlerai de l’organisation physique des données dans la base, des algorithmes de jointure, de l’optimisation des requêtes. Si des personnes sont intéressées, je pourrai aussi parler de la concurrence d’accès ou de la reprise sur panne. bases de données ? Dans quel genre d'applications trouve-t-on des bases de données ? Dans des applications de gestions de données : Salaires, Stock Transactionnel : Comptes bancaires, centrale d’achat, réservation Web : Commerce électronique Bases de données spatiales : cartes routières, système de guidage GPS… Comme on vient de le voir les bases de données se trouvent dans toutes applications devant gérer un grand volume d’informations. Ces informations doivent être structurées (format défini) et sont mémorisés sur un support permanent (qui ne peut pas tomber en panne et inaltérable, on en reparlera dans les pannes…). Un Système de Gestion de Base de Données (SGBD) est donc un logiciel manipulant ces informations. Une base de données peut se regarder sous trois niveaux : Le niveau physique : Accès aux données, gestion de la mémoire secondaires (disques), Partages des accès et concurrence… Le niveau Logique : Définition de la structure de données : Langage de description de données (LDD), Consultation et mises à jour des données (Langage de manipulation de données LMD, Langage de requêtes LR). En clair, SQL… Le niveau externe : Il regroupe les possibilités d’accès aux données par les usagers. Ces accès peuvent se faire via différents types d’interfaces et langages. Des vues (sorte de tables calculées) peuvent remplacer les tables. Nous allons maintenant nous intéresser au niveau logique. Que peut-on faire sur nos données ? Il y a quatre types d’opérations : La création ou l’insertion La modification ou la mise a jour La destruction La recherche (Requetes) Ces opérations correspondent à des commandes du LMD et du LR. La plus complexe est la recherche en raison de la variété des critères. Lorsque l’on envoie une requête au SGBD, il se passe plusieurs étapes : Analyse syntaxique : le SGBD va vérifier que votre requête est conforme à la norme SQL. Compilation et Optimisation : Le SGBD va déterminer les opérations a faire pour retrouver les données à vous envoyer. Il va aussi optimiser son travail pour qu’il en ait le moins à faire… feignasse afin de vous renvoyer les informations en un minimum de temps. Exécution de la requête : Calcul du résultat et envoie des données. La concurrence d’accès : Plusieurs utilisateurs doivent pouvoir accéder en même temps à la base. Le SGBD doit pouvoir gérer les conflits offrir un mecanisme de retour arrière si on décide d'annuler les modifications en cours donner une image cohérente des données si l'un fait des requetes et l'autre des mises à jour Le but est d'eviter les blocages, tout en garantissant que les données sont cohérentes. On reviendra en detail sur le point 1 dans le chapitre sur le verouillage des enregistrement. On reviendra en detail sur le point 2 dans le chapitre sur les transactions. On reviendra en detail sur le point 3 quand on parlera des niveaux d'isolations. Le modèle relationnel Un peu de théorie des ensembles, aspirine par ici Non c'est pour rire, c'est très simple. Pour la culture : Version Dieu sur Terre : Domaine : Ensemble de valeurs. Par exemple {0,1}, N, l'ensembles des chaines de longeurs 12... Relation : Sous-ensemble du produit cartésien d'une liste de domaines caractérisé par un nom. Version Comprehensible : En d'autres termes, une relation n'est ni plus ni moins qu'une table (Pourquoi faire simple quand on peut faire compliqué...) dans laquelle chaque colonne correspond à un domaine (des valeurs différentes quoi) et porte un nom ce qui rend leur ordre sans aucune importance. Exemple : Table Personne ( NOM, PRENOM, DateDeNaissance ) La relation c'est Personne et un domaine, c'est les différentes valeurs contenu dans la colonne PRENOM. On parle aussi d'Attribut. Un attribut est une colonne d'une relation caractérisée par un nom. Un NUPLET est une ligne de valeurs [V1,V2,...VN] ou chaque valeurs Vi est la valeur d'un domaine. Une Base de données est un ensemble de relation. Ce vocabulaire vous servira si vous lisez des "vrais" auteurs de livre sur les bases de données... Bon revenons à nos moutons. En algèbre relationnelle quelles sont les opérations de bases. Ce sont une combinaisons de ces fonctions qui vont être exécutés pendant votre requête SQL. Un compréhension de ces fonctions est nécessaire pour créer des requêtes, mais aussi pour les optimiser. La Projection : Lorsque on fait une projection d'une table, on choisi certaines colonnes et on élimine les autres. Dans ma Table Personne, une projection (P) pourrait être : P Nom Prenom (Personne). Cette projection prends les colonnes Nom Prénom et élimine les autres. La Sélection : Lorsque on fait une sélection (S) dans une table, on choisit les lignes dont la valeur correspont à un critère donné. S prenom="pierre" (Personne) renvoie l'ensemble des lignes dont le prénom est égal à pierre (Il va faire tout noir, ta gueule ) Pour ceux qui connaissent SQL, il y a souvent une confusion entre ces deux opérations. La raison viens du nom des instructions. une requete SQL s'ecrit : SELECT nom des colonnes FROM Tables WHERE conditions. La ligne SELECT contient les... projections (le nom des colonnes...) tandis que la ligne WHERE contient les conditions et donc les selections... Le produit Cartésien : Le produit cartésien de la table R (contenant une colonne nommée A) par la table S (Contenant un colonne nommée B), noté RxS, est l'opération suivante : Pour chaque ligne de S, on colle chacune des ligne de R. Si S contient (1,2,3) et R contient (A,B) RxS=(1,A),(1,B)(1,C),(2,A),(2,B),(3,A),(3,B) La jointure Naturelle : La jointure naturelle est un produit cartésien dans le on selectionne les valeurs identiques pour les colonnes ayant un nom identique entre les tables R et S. Exemple : La table R contient trois colonnes (A,B,C) colonnes A B C Valeurs: a b c d b c b b f c a d La table S contient trois colonnes (D,B,C) colonnes D B C Valeurs: d b c e b c b a d La table résultat sera : colonnes A B C D Valeurs: a b c d a b c e d b c d d b c e c a d b Les colonnes B et C contient les valeurs B et F dans R mais pas dans S et sont donc éliminées du produit cartésien. La Théta-Jointure : La Théta-Jointure est un produit cartésien entre deux tables dans lequel on va selectionner des lignes suivant une condition. Exemple : La table R contient deux colonnes (A,B) colonnes A B Valeurs: 1 a 1 b 3 a la table S contient trois colonnes (C,D,E). Dans ce cas, il n'y a pas de colonne communes. colonnes C D E Valeurs: 1 b a 2 b c 4 a a La condition est la colonne A soit inférieure ou égale à la colonne C. La table résultat sera : colonnes A B C D E Valeurs: 1 a 1 b a 1 a 2 b c 1 a 4 a a 1 b 1 b a 1 b 2 b c 1 b 4 a a 3 a 4 a a Pour obtenir ce resultat, il suffit de calculer le produit cartésien et de ne garder que les lignes pour lesquelles la colonne A est inférieure à la colonne C L'Equijointure : Cas particulier de la Théta-Jointure : la condition est que les valeurs comparé dans les colonnes soient identiques. La Jointure Externe: Les jointures externes ont la capacité de prendre en compte les éléments n’ayant pas de correspondance dans l’autre table lors d’une jointure. Toutes les lignes de table1 seront affichées que la condition de jointure soit réalisée ou non dans table2. Quel Interet ? Si on pose une condition et qu'elle ne soit pas respectée... Prenons une table contenant des étudiants E avec deux colonnes IDEtudiant et NomEtudiant Prenons une table contenant des notes N contenant IDEtudiant et NoteEtudiant. E IDEtudiant NomEtudiant 0 Pierre 1 Paul 2 Jacques N IDEtudiant NoteEtudiant 0 15 0 2 2 20 Pour répondre a la question : "Liste des etudiants et de leur notes ", je fais une jointure la table résultat est : IDEtudiant NomEtudiant NoteEtudiant 0 Pierre 15 0 Pierre 2 2 Jacques 20 Paul n'ayant pas de note il disparait... La jointure externe résout le problème : IDEtudiant NomEtudiant NoteEtudiant 0 Pierre 15 0 Pierre 2 1 Paul NULL <-------- Valeur indiquant aucune valeur présente. 2 Jacques 20 On peut ainsi retenir les éléments n’ayant pas de correspondance dans les tables. L'Union: Comme son nom l'indique, on fait l'union de deux tables. Exemple: La table R contient deux colonnes (A,B) colonnes A B Valeurs: a b a c d e La table S contient deux colonnes (A,B) colonnes A B Valeurs: a b a e d e f g La table Résultat: colonnes A B Valeurs: a b a c d e a e f g L'Intersection: Comme son nom l'indique, on prends l'intersection de deux tables, c'est a dire les valeurs communes aux deux tables. Exemple: La table R contient deux colonnes (A,B) colonnes A B Valeurs: a b a c d e La table S contient deux colonnes (A,B) colonnes A B Valeurs: a b a e d e f g La table Résultat: colonnes A B Valeurs: a b d e La Différence: La différence, c'est la liste des elements se trouvant dans une tables mais pas dans l'autre. La table R contient deux colonnes (A,B) colonnes A B Valeurs: a b a c d e La table S contient deux colonnes (A,B) colonnes A B Valeurs: a b a e d e f g La table Résultat R-S: colonnes A B Valeurs: a c La table Résultat S-R: colonnes A B Valeurs: a e f g Attention:Autant l'union et la l'intersection sont commutatives (A union B et B union A donne le même résultat, autant la différence ne l'est pas. La Division: Une petite derniere... par un exemple. La table Commande : colonnes NumCommande Nom Produit Quantité Valeurs: 1 Jean Briques 100 2 Jean Ciment 2 3 Jean Parpaing 2 4 Paul Briques 200 5 Paul Ciment 5 6 Vincent Briques 5 La table Produit: colonnes Produit Briques Ciment Parpaing Si on veut répondre à la question : "Quel client à commandé tous les produits ?" On divise Commande par Produit est on obtient Jean. Voila, c'est fini pour la théorie. L'aspirine n'a pas servi, j'espère... Si des points sont pas clairs, je modifierai l'article. A bientot pour la suite avec SQL. Lien vers le commentaire Partager sur d’autres sites More sharing options...
Le Saigneur Sombre Posté(e) le 26 septembre 2006 Auteur Partager Posté(e) le 26 septembre 2006 Désolé pour l'attente mais le boulot était de folie. Pour être totalement honnete, ma nouvelle config m'a un peu occupé Comme je le disais au début de l'article, je ne parle pas de la syntaxe SQL, vous devez la connaitre. Un petit tour sur le site http://www.w3schools.com/ peut résoudre le problème. N'ayant pas à ma disposition les lettres greques, je vais utiliser les majuscules : P pour la Projection, S pour la Selection, X pour la jointure... S.Q.L. Imaginons une table COMMANDES contenant les colonnes : NUM : Numéro de commande NOM : Nom de l'acheteur NOMP : Nom Produit QTE : Quanté de prodiut commandé. Prenons l'exemple d'une requete simple : SELECT NOMP FROM COMMANDES WHERE NOM='Pierre' Que va faire la base de données ? Elle va tout d'abord selectionner les lignes dont le nom est pierre. Ensuite, elle va faire une projection pour ne récuperer que la colonne "nom du produit". En algère, on ecrit : P [pnom] ( S(nom=Jean) [COMMANDES] ). Les minuscules sont censé être en indice... Pourquoi dans cet ordre ? Tout simplement parce que dans presque tous les cas, la selection est plus restrictive que la projection, c'est a dire qu'une selection renvoit moins de ligne qu'une projection. La base de données decide de l'ordre des opérations en fonction de statistiques qu'elle conserve dans une base de données gérant vos bases et souvent appellé catalogue (Le nom des tables, des schema, des utilisateurs... sont la). Moins le SGBD a d'enregistrement à gérer, plus rapide est l'execution de la requete. Rajoutons à la table commandes, une table des clients dont le nom serait la clé (Oui, c'est un cas d'ecole...) table CLIENT contenant les colonnes : NOM : Nom de l'acheteur ADR : Adresse Tel : Telephone. On doit contacter la liste des clients ayant acheté des baterries sony parce qu'elles risquent de mettre le feu à la baraque SELECT A.NOM, A.TEL FROM CLIENT A, COMMANDE B WHERE A.NOM=B.NOM AND NOMP="Batterie Sony" Que va faire la base de données : Avant de faire la jointure, elle cherche a réduire le nombre d'enregistrements. Elle va donc d'abord executer la selection sur la table des commandes : S(nomp="Batterie Sony") [COMMANDE] => R1 Sur ce resultat, elle va faire la jointure. R1 X CLIENT Pour terminer, elle effectue une projection pour n'avoir que la colonne des nom et numéro de telephone : P(Nom,Tel) [ R1 X CLIENT ] Vous pouvez appellez les clients... Ces étapes sont montrés par l'optimiseur de requêtes. Il est graphique sous certains SGBD, texte sous d'autres, mais le resultat est le même. Il vous montre étape par étape, les opérations faites par votre requêtes. Ecrivez des requetes et regarder les résultats montrés par votre optimiseur. C'est le meilleur moyen de voir ce que fait la base... Petit conseil : Même si l'optimiseur fait un boulot extraordinaire, il n'est pas devin. Vous devez limiter au maximum la taille de vos données traités par une requete. Par exemple, faire un SELECT * FROM COMMANDES et ne prendre que le premier enregistrement dans votre programme est une perte de temps et une surcharge du serveur. Lorsque vous construisez votre schema, faites une troisième forme normale, quite à le dénormaliser pendant la production. 90% du travail se fait à la conception (Shéma + Requete). Les 10% qui restent, sont l'optimisation du serveur de base de données et la création d'index. Un index est comme la table des matières d'un livre. Si vous cherchez un article en particulier, il est plus rapide de lire la table des matiere, trouver la page et lire l'article en question. Par contre, si vous devez lire quasiment tout le livre, il est plus rapide de lire le livre, que d'aller voir la page de tous les articles dans la table des matières. Pour les SGBD , c'est pareil. Le SGBD estime la limite à 90%. Si la selection de votre requete, renvoit 90% de la table, le SGBD ne se servira pas de l'index, même s'il existe... Prochaine étapes : Les différents algorithmes de jointures. J'aimerai avoir des retours. Si il y'a une partie des SGBD qui vous interessent plus qu'une autre... Ou si je dois arreter, j'aimerai autant le savoir Lien vers le commentaire Partager sur d’autres sites More sharing options...
Quarky Posté(e) le 26 septembre 2006 Partager Posté(e) le 26 septembre 2006 Oki sympa. Par contre pour être franc, j'ai pas trop le temps de tout lire en ce moment. J'ai donc lu en diagonale et voici quelques remarques pour améliorer la forme : - utilise cet icône () pour ton topic (modifiable par l'édition complète) - mets des couleurs sur tes titres et sous-titres Par contre bravo pour tes exemples et tes commandes. Je repasserais tout bien lire quand j'aurais plus de temps libre PS : c'est quoi ce sous-titre du topic ? : Rien compris. Lien vers le commentaire Partager sur d’autres sites More sharing options...
Le Saigneur Sombre Posté(e) le 27 septembre 2006 Auteur Partager Posté(e) le 27 septembre 2006 Ben en fait, c'était une blague Vu que les SGBD, c'est pas vraiment de la programmation (enfin du moins la partie gestion), SQL n'est pas vraiment un langage de programmation, je suis demandé ou mettre mon article. Alors pourquoi pas un nouveau forum/sous forum SGBD. Mais j'ai pas regardé,s'il y avait beaucoup d'article la dessus. Je ne pense pas qu'il y'en ai assez pour faire un nouveau forum. Lien vers le commentaire Partager sur d’autres sites More sharing options...
Messages recommandés
Archivé
Ce sujet est désormais archivé et ne peut plus recevoir de nouvelles réponses.