Fichier de rejeu Close
Indication Close
A propos de... Close
Commentaire Close
Systèmes d'Information
- Notions mathématiques
- Calcul relationnel
- Algèbre relationnelle
- Langage de requêtes
- Arbre de requêtes
- Exercices
- Introduction
- Commandes de bases
- Langage de définition de données (LDD)
- Langage de manipulation de données (LMD)
- Types de données
- Exercice
- Dépendances fonctionnelles
- Décomposition de relations
- Inférence logique
- Normalisation
- Aux pays des bières
- Modélisation
- Exercices
© Your Copyright
Aide
Bases de données relationnelles
« La connaissance s’acquiert par l’expérience, tout le reste n’est que de l’information. » Albert Einstein
Ce site accompagne les enseignements d’informatique du troisième semestre postbac de l' Ecole nationale d'ingénieurs de Brest.
Il a pour objectif de familiariser les étudiants avec les Systèmes de Gestion de Bases de Données Relationnelles (SGBDR) et le langage SQL (Structured Query Language), langage standard d’accès aux bases de données relationnelles. Les SGBDR font partie des Systèmes d’Information (SI) que l’on rencontre dans la majorité des entreprises. Dans ce cours en ligne de l' Enibook nous utiliserons le moteur de base de données SQLite qui n’offre pas toutes les fonctionnalités que doit proposer un SGBD relationnel mais reste suffisant pour l’apprentissage du modèle relationnel. Pour aller plus loin et avoir accès à plus de fonctionnalités spécifiques aux SGBD relationnels, nous renvoyons le lecteur intéressé sur les SGBD relationnels PostgreSQL, MySQL et ORACLE.
Ce cours a été réalisé sous la direction de Alexis NÉDÉLEC avec la participation de Eric MAISEL et Dominique FABRE, vacataire à l’ENIB et consultant chez Thalès Services à Brest.
L’objectif de ce cours consiste à :
- savoir rechercher et manipuler des données avec le langage déclaratif SQL.
- formaliser des requêtes SQL à l’aide des opérateurs de l’algèbre relationnelle et d’arbre de requêtes.
- modéliser et structurer une base de données en s’appuyant sur le formalisme UML et la normalisation des bases de données relationnelles.
Les évaluations reposeront sur :
- les concepts mathématiques de base sur lesquels reposent le modèle relationnel.
- la formulation de requêtes SQL sur un modèle de données spécifique.
- l’écriture de requêtes en calcul relationnel
- la représentation de requêtes en algèbre relationnelle et arbre de requêtes.
- la mise en œuvre d’un modèle de base de données relationnelles sour forme de mini-projet
- connaissances générales des SGBD relationnelles sous forme d’exercices de synthèse.
Calendrier prévisionnel des évaluations :
- semaine 3 : concepts généraux des modèles relationnels
- semaine 6 : requêtes SQL sur une base de données
- semaine 9 : mini-projet, correspondance cahier des charges / modélisation UML
- semaine 10 : calcul et algèbre relationnelle, arbre de requêtes
- semaine 12 : mini-projet, formulation de cas d’usage en calcul relationnel et structuration de la base de données en plusieurs tables.
- semaine 14 : mini-projet, version finale du projet avec formulation de requêtes correspondant aux cas d’usages sur la base. Contrôle de synthèse.
Les sujets de mini-projets se trouvent dans le chapitre correspondant. Le travail de projet sera réalisé en binôme. Il n’y aura pas de possibilité de choisir un même sujet pour deux binômes. L’attribution des projets sera fixée en début de deuxième période du semestre.
Généralités
Nous proposons au lecteur de commencer sa formation sur les SGBD Relationnels par une compréhension des notions mathématiques de base :
- théorie des ensembles
- logique du premier ordre
et des concepts spécifiques au domaine d’étude des systèmes d’information :
- calcul relationnel
- algèbre relationnelle
- arbres de requêtes
sur lesquels se baseront les traitements pour rechercher des informations spécifiques dans une masse d’informations réparties dans les ensembles (entités, structures, tables, …) modélisés. Ces notions seront abordés concrètement en les appliquant sur des exemples très basiques à l’aide du langage SQL. Ces exemples permettront de se familiariser au langage et à la représentation des requêtes SQL sous forme d’arbre de traitement des données à l’aide des opérateurs de l’algèbre relationnelle.
Langage SQL
Cette partie de cours a pour objectif de connaître et maîtriser la syntaxe et les principales commandes du langage SQL pour créer, manipuler et interroger une base de données relationnelles.
SQL est un langage déclaratif, normalisé ANSI, pour communiquer avec une base de données relationnelles.
Les exemples proposés dans ce cours pourront être testés directement sur ce site avec le moteur de base de données SQLite.
Comme son nom l’indique c’est un SGBDR allégé, il n’y a rien à configurer, rien à maintenir ou à administrer.
En contrepartie, certaines fonctionnalités sont absentes, comme la gestion des utilisateurs ou la possibilité de se connecter à distance à la base de données en mode client-serveur.
Pour tester ses connaissances sur ce langage, en supplément de ce qui est proposé dans ce cours, nous invitons le lecteur à consulter les sites, SQL et SQL w3c.
Algèbre relationnelle
- Cette partie de cours a pour objectif de présenter :
- le calcul relationnel qui permet d’exprimer, sous forme de proposition logique, les propriétes des informations que l’on recherche.
- l’algèbre relationnelle qui permettra de représenter les opérateurs à mettre en œuvre pour éxécuter une requête SQL.
- la correspondance entre une expression d’algèbre relationnelle et sa formulation SQL.
- l’opérateur de division relationnelle, qui s’exprime à l’aide d’opérateurs de base et permet de représenter le quantificateur universel (\(\forall\)).
Modélisation
L’objectif de cette partie est d’initier l’étudiant à la modélisation d’une bas de données d’un Système d’Information en se basant sur la notion mathématique de relations, le modèle Entité Association (EA) qui en découle et un formalisme de modélisation (UML) qui aidera le concepteur d’une base de donnée relationnelle à décrire précisement et de manière compréhensible la représentation des données à manipuler par le SI.
Nous présentons les différentes notions abordés sous forme de :
- définition mathématique des relations binaires possibles entre deux ensembles
- leur interprétation sur un exemple (relations entre un ensemble d’employés et un ensemble de sociétés)
Pour chaque notion les définitions de relation entre deux ensembles sous présentées par :
- la cardinalité de la relation
- le diagramme sagittal de la relation
- le modèle Entité-Association
- la modélisation UML des entités et des associations
Ces notions sont concrétisés par le développement d’un modèle de données à partir l’étude de deux cahiers des charges.
Le lecteur pourra s’entraîner à créer son propre modèle de données à partir du cahier des charges proposé dans l’exercice qui conclut ce chapitre.
Normalisation
Au modèle relationnel est associé la théorie de normalisation des relations.
Cette théorie a pour but l’élimination des comportements anormaux lors des mises à jour des données.
La normalisation consiste à étudier les dépendances dites fonctionnelles (au sens mathématique du terme) entre les informations afin de structurer efficacement une base de données.
L’étude de ces dépendances permettra de répartir l’information de manière cohérente dans différentes tables afin de les retrouver en effectuant des jointures entre les tables où elles se trouvent.
L’objectif de la normalisation est de structurer l’information dans un modèle de données (un schéma relationnel) cohérent par rapport aux contraintes existantes entre les informations à mémoriser.
Pour qu’un modèle relationnel soit normalisé, il faudra donc qu’il respecte certaines contraintes appelées formes normales. Les formes normales s’appuient sur les dépendances fonctionnelles entre les informations à manipuler.
Une structuration de la base de données basée sur les formes normales permettra de gérer efficacement les problèmes de recherches, mises à jour et suppression de l’information.
L’étude de la normalisation aboutie à la spécification de six formes normales.
Une bonne modélisation de base de données consistera à structurer l’information dans des ensembles (tables) en respectant au moins les trois premières formes normales.
Les formes normales ont leurs avantages et leurs inconvénients.
Les principaux avantages sont :
- de limiter les redondances de données (multiples écritures) donc de diminuer la volumétrie globale des données à manipuler.
- d’interdire les incohérences de données venant des redondances dont une partie seulement a été mise à jour, ce qui rendrait de fait les données inutilisables (différentes versions d’une même information, sans que l’on sache laquelle est valide)
- de limiter grandement le nombre et la durée des mises à jour qui sont des processus bloquants (écritures sur disque).
L’inconvénient essentiel tient aux temps d’accès potentiellement plus longs si les requêtes sont très complexes (nombreuses jointures).
De nos jours, cela est largement compensé par la qualité des optimiseurs et la mise en place d’index pertinents adéquats.
Pour des petites bases de données, se limiter à la troisième forme normale est généralement une bonne solution d’un point de vue architecture de base de données, mais pour des bases de données plus importantes, il est nécessaire d’aller plus loin, eventuellement après normalisation et étalonnage, d’effectuer une dénormalisation intelligente dont un benchmark prouve globalement les gains réalisés par rapport au modèle de données normalisé.
Dans cette partie nous présenterons les principes de normalisation correspondant aux trois premières formes normales (1NF,2NF,3NF) et la forme normale de Boyce-Codd (BCNF).
Ces formes normales sont basés sur l’étude des dépendances fonctionnelles existantes entre informations et des inférences logiques (dépendances fonctionnelles déduites de celle existantes à partir des axiomes d’Armstrong).
A partir de l’ensemble des dépendances fonctionnelles nécessaires (couverture minimale) pour retrouver toute l’information et des formes normales on pourra décomposer (théorème de décomposition sans perte) une relation (table) en plusieurs tables sans perdre de l’information tout en gardant la cohérence des données et des contraintes entre ces informations.
Après cette présentation des formes normales, les étapes de normalisation seront illustrés par la mise en place à partir d’un schéma relationnel non normalisé (« Aux pays des bières ») pour aboutir progressivement à un schéma relationnel en 3NF.
Un exemple de modélisation d’une base de données conforme à la normalisation 3NF sera étudié en partant d’un simple cahier des charges d’une entreprise souhaitant gérer des commandes de clients.
Projets
Dans cette partie sont présentés les « cahier des charges » de projets proposés pour créer un Système d’Information basé sur le modèle relationnel.
L’objectif est de choisir un projet différent par binôme d’étudiants dans la liste proposée et de modéliser, à partir de ce cahier des charges, un Système d’Information.
Pour modéliser la base de données on s’appuiera sur l’étude des modèle Entité-Association (E-A) et UML (Unified Modeling Language).
A partir du modèle retenu, il s’agira de créer la base de données correspondante et de l’interroger en formulant des requêtes SQL afin de vérifier la cohérence du modèle proposé.
Les tutoriaux pour se familiariser avec la modélisation E-A et UML :
- ERD tutorials : modélisation Entité-Association
- UML tutorials : langage de modélisation unifié
La modélisation en UML :
A partir du cahier des charges des projets présentés dans la liste ci-dessous, le travail demandé consiste à établir trois versions de la base de données.
- identification des entités, des informations qu’elles contiennent et des associations entre ces entités. On s’appuiera sur le formalisme UML pour représenter les entités et leurs assocations.
- formulation de cas d’usage (en calcul relationnel), structuration (à partir du modèle UML validé lors de la première version) et alimentation de la base de données (en SQL)
- interrogation (requêtes SQL) sur la base de données à partir des cas d’usage pour mettre en oeuvre
La première version permettra de relever l’ensemble des entités, des informations qui leur sont liées ainsi que les associations entre ces entités. Cette version permettra de modifier et étoffer les cahiers de charges initiaux et de vérifier la concordance entre celui-ci (phrase : sujet-verbe-complément) et le modèle UML retenu (entité-association-entité) avec les cardinalités des associations (un représentant d’une entité est liée à un ou plusieurs représentants d’une autre). On fera attention à faire apparaître les différents types d’associations (un-à-plusieurs, plusieurs-à-plusieurs,réflexives …).
La deuxième version consistera à formaliser des recherches d’informations sur la base de données. Ces cas d’usage mettront en jeu les opérations classiques sur les bases de données (sur une table ou plusieurs, opérations ensemblistes, division relationnelle, fonctions d’agrégats, regroupement et restriction sur les regroupements). La structuration de la base de données fera apparaître les tables associatives (associations plusieurs-à-plusieurs de la première version). Le nombre de tables sera limité (entre 5 et 10 tables).
On vérifiera à partir des instances de tables (relations) qu’une jointure sur l’ensemble des tables permet de retrouver l’ensemble des informations utiles de la base de données.
La version finale consistera à mettre en place des requêtes SQL correspondant aux cas d’usages relevés dans la version précédente pour récupérer les informations :
- sur une seule table avec projections (\(\Pi\)) et critères de restriction (\(\sigma\))
- sur plusieurs tables par jointure et critères de restriction (\(\Join, \sigma\))
- par des requêtes ensemblistes (\(\cup,\cap,\setminus\))
- avec une division relationnelle (\(\div\))
- en appliquant des fonctions d’agrégats (
count(),sum(),max(),min(),avg() ...)- en faisant des groupements (
GROUP BY)- puis des groupements avec critères de restrictions (
GROUP BY ... HAVING)
- Dans cette version finale on fera apparaître les différents cas d’usage en :
- calcul relationnel
- algèbre relationnelle
- arbre de requêtes
- requête SQL
On se limitera à 2 requêtes pour chacun de ces types de requêtes.
Exercices
Le lecteur trouvera dans cette partie les exercices qui lui permettront de s’entraîner sur différents modèles de données pour formuler des requêtes SQL.
Pour chaque modèle de données une liste de questions en langage naturel est proposée.
L’objectif des exercices est de savoir trouver leur formulation en SQL puis en arbre de requêtes.
QCM
Le lecteur trouvera dans cette partie des QCM qui lui permettront d’évaluer son niveau global de connaissance sur ce cours.
Contrôles
On trouvera dans cette section une correction de contrôles « types » qui pourraient être proposés lors des évaluations.