View
242
Download
2
Category
Preview:
Citation preview
Bases de Données Relationnelles
Théorie + Système + Conception
Pagora Valérie Bellynck, Mazen Marhous
Organisation, Objectifs et Plan
• Partie « Théorique » de l’enseignement • Cours et TD sur 3h ! Mais + que sensibilisation
Notion de SGBD :
? Bases de Données (Data Base) : ensemble de données organisées pour faciliter leur manipulation
+ Utilisation simultanée par différents usagers Exemples de données :
– Stocks (d’une imprimerie) – Comptes d’un parc de photocopieuses – Données variables à insérer dans des documents à imprimer – Planches de bandes dessinées d’un éditeur – Articles de journaux / photos et informations sur leurs auteurs – Informations sur les documents à imprimer et leur état – Dossiers de fabrication … propositions de dossiers de fabrication
Exemple
Organisation des données ?
Si les données étaient dans une feuille d’un fichier Excel ? • risque de données erronées • présence de doublons • risque d’incohérences • plus grand nombre de données que nécessaire • limitation du nombre de « lignes » • impossibilité du partage partiel de données du fichier
– entre plusieurs utilisateurs (un seul utilisateur du fichier à la fois) • intolérence aux pannes
Séparation des données et des programmes
FICHIER Les données des fichiers sont décrites dans les programmes
BASE DE DONNEES Les données de la BD sont décrites hors des programmes dans la base elle-même Description
fichier !
Description fichier
!
Description unique
!
Si les données sont gérées directement dans les fichiers par les programmes?
Les programmes s’appuient sur la description de l’organisation des données et ne s’occupent pas de leur « implémentation »
Notion de SGBD :
? Bases de Données (Data Base) : ensemble de données organisées pour faciliter leur manipulation
+ Utilisation simultanée par différents usagers Fiabiliser la gestion des données
Système de Gestion de Bases de Données (Data Base Management System) : écran entre les usagers (et les programmes) et les données mémorisées
Une BD n’est pas seulement un ensemble organisé de données, mais doit n’être gérée que par un SGBD
Fonctions des SGBD :
• Rangement : mémorisation des données
• Recherche : accès par le nom d’une donnée ou de ses relations à d’autres données dans une grande masse de données
• Assemblage : regroupement dynamique des données pour les présenter ensemble quand l’usager s’y attend
• Conversion de données : passage dans l’encodage utile à l’usager
• Protection des données : incidents et accidents (ex : pannes en cours de modification…)
• Performances acceptables : réponses presque en temps réel (attente imperceptible ou supportable pour l’usager)
Plan du cours
? Bases de Données (Data Base) : ensemble de données organisées gérées par un SGBD pour faciliter leur manipulation et fiabiliser leur gestion
accessibles seulement via un système : SGBD s’appuie sur une théorie : (ici :) modèle relationnel = tables algèbre relationnelle conception cadrée par une technique de modélisation :
modèle entités-associations
Systèmes de gestion de bases de données
Que doit permettre un SGBD ? (1) Décrire les données indépendamment des applications (de manière intrinsèque : en intention, pas en extension)
� langage de définition des données DATA DEFINITION LANGUAGE (DDL)
http://www.i3s.unice.fr/~nlt/cours/licence/sgbd1/sgbd1_cours.pdf�!
Manipuler les données interroger et mettre à jour les données sans préciser comment (dire QUOI sans dire COMMENT) langage de requêtes déclaratif
� langage de manipulation des données DATA MANIPULATION LANGUAGE (DML)
Contrôler les données assurer l’intégrite ́ (vérification de contraintes d'intégrite ́) assurer la confidentialite ́ contrôler les droits d'accès, autorisation
� langage de contrôle des données DATA CONTROL LANGUAGE (DCL)
Que doit permettre un SGBD ? (2) Partage d’une BD entre plusieurs utilisateurs en même temps contrôle des accès concurrentsTransaction : son exécution permet de préserver la cohérence de la BD
� notion de transaction
http://www.i3s.unice.fr/~nlt/cours/licence/sgbd1/sgbd1_cours.pdf�!
Sécurité � reprise après pannes, journalisation
Performance d’accès Tables de hashage, arbre de recherche balancés � indexage
Que doit permettre un SGBD ? (3)
Indépendance physique Permettre la modification des structures de stockage ou d’index sans répercussion sur les applications Enfouissement des méthodes d’accès aux disques, des modes de placement, du codage effectif des données
� systèmes en couches couche physique/couche logique
Indépendance logique Permettre différentes vues logiques de l’organisation des données les programmes exploitent des schémas logiques déclarables à partir de l’organisation réelle des données
� systèmes en couches couche logique/couche externe
http://www.i3s.unice.fr/~nlt/cours/licence/sgbd1/sgbd1_cours.pdf�!
Caractéristiques Système en 3 couches séparant les aspects
• présentation des résultats • gestion des données • système de gestion de fichiers
+ Description des données (= définition des propriétés d’ensembles d’objets modélisés dans la base de données) séparée de leur utilisation
ce cours
intention ≠ extension
Différents types de BD • Bases hiérarchiques
schéma de la base -> arborescent technique -> bases navigationnelles (gestion de pointeurs entre les
enregistrements). • Bases réseaux : + rapides, années 70
technique -> bases navigationnelles schéma de la base -> graphe (arbres, treillis, … ).
• Bases relationnelles : + utilisées schéma de la base -> relations entre tables technique -> algèbre relationnelle + langage déclaratif (SQL)
• Bases déductives. schéma de la base-> tables et prédicats technique -> calcul des prédicats et logique du premier ordre + langage
d’interrogation • Bases objets
schéma de la base -> instances de classes hiérarchisées, champ = objet technique -> méthodes d’interrogation et d’affectation = partie des
objets, la notion d’héritage permet de factoriser la connaissance
ce cours
Notions fondamentales caractérisant un type de BD
Modèle de description des données (data model) : ensemble de concepts et de règles de composition de ces concepts permettant de décrire des données. Par exemple : réseau atome / agrégat / article / ensemble / BD réseau / placement hiérarchique champ / segment / arbre de segments / forêts / BD hiér. relationnel domaine/ relation/ attribut/ uplet / BD relationnelle Langage de description des données (data description language) : langage supportant un modèle et permettant de décrire des données d’une base de données d’une manière assimilable par une machine. Par exemple : relationnel SQL Schéma (schema) : description d’un ensemble de données particulier au moyen d’un langage formel déterminé.
Le modèle relationnel Exemple (de ce qu’on va voir…)
Schéma relationnel (sous forme fonctionnelle) Table relationnelle Création de la table en SQL par le Langage de
Définition de données - LDD (data definition language - DDL)
Auteur
n°A Nom Prenom DateNaissance 1 Hugo Victor 26 février 1802 2 Perec Georges 7 mars 1936
Auteur&(noA:integer,&Nom:string[30],&Prenom:string[30],&DateNaissance:date)!
CREATE TABLE Client ( noA NUMBER NOT NULL, Nom VARCHAR (30) NOT NULL, PrenomVARCHAR (30) NOT NULL, DateNaissance TIMESTAMP NOT NULL, PRIMARY KEY (noA) );
Relation
Attributs � Domaine
tuples Clé primaire
Historique • Années 60 : développement des systèmes de fichiers
(mémoires secondaires partagées directement adressables et de capacité infinie)
• Milieu des années 60 : première génération des SGBD (séparation de la description des données des programmes d’application, langages d’accès navigationnels reposant sur la gestion de pointeurs entre les enregistrements et visant à optimiser le placement des données dans leur supports physiques pour réduire les temps d’accès)
• Entre 60 et 70 : deuxième génération des SGBD (modèle relationnel, langages assertionnels basés sur la logique pour spécifier les données à acquérir plutôt que comment y accéder)
• Début des années 80 : premières commercialisations • Depuis 80 : troisième génération des SGBD
(langages d’accès plus puissants et plus rationnels supportant des types de données plus variées, possibilités de déduction et de répartition)
• Années 90 : Internet et XML (intégration de XML : oracle 8i et +)
• Années 2010 : Cloud computing(informatique dématérialisée opposée à l’informatique « locale » depuis 2008 : exploitation des technologies de l’information et évolution des usages consistant à proposer systématiquement les services informatiques sous la forme de services à la demande accessibles n’importe où, n’importe quand et par n’importe qui)
Modèle et algèbre relationnelle
Introduction au « relationnel » • Objectif : définir un langage de requête unifiant toutes les
manipulations des données • Moyen : décrire un système d’information
dans un modèle de description des données tel que toute manipulation réalise une opération
Système d’information!
Exemple
Pagora! !!
Imp. des 4 ponts!
Tournon!
Jean-Luc!461 rue de la Papeterie!
CS 10065- 38402!
Saint-Martin d'Hères!
Eybens!14 bd Gambetta!
38320!
297!210! Carré!
Rectangle!Ovale!
Rond!
Spécial!
Velin!
Couché!
Kromekot!
Fluo!PP blanc!
PP transparent!
PE blanc!
Bobine!
Planche!
Paravent!
Unitaire!
150!
46!
98!
laize!
forme!
matière!
contact!
quantité!conditionnement!
Cadre mathématique : théorie des ensembles et algèbre relationnelle
Tout type de données est représentable dans un ensemble de tables et de relations
Les systèmes de gestion de bases de données relationnelles
organisent les données en tables (à la manière d’un tableur). Il est simple, facile à comprendre et fidèle à un cadre mathématique
(l’algèbre relationnelle). Le concept mathématique sous-jacent est celui de relation de la
théorie des ensembles, qui se définit comme un sous-ensembles du produit cartésien de plusieurs domaines : toute table sera un ensemble de relations
Exemple
Pagora! !!
Imp. des 4 ponts!
Tournon!
Jean-Luc!461 rue de la Papeterie!
CS 10065- 38402!
Saint-Martin d'Hères!
Eybens!14 bd Gambetta!
38320!
contact!
contact! entreprise! adresse! code postal! ville!Pagora! 461 rue de la Papeterie! CS…38402! St-Martin d’Hères!
38320! Eybens!
Domaine des villes #= toutes les valeurs possibles pour « ville »!
Domaine des codes postaux#= tous les codes postaux possibles!
Domaine …!
U-uplet #avec n = 4!
Et alors… • Peut-on représenter toutes les données dans des relations ? • Peut-on faire sur l’ensemble des relations toutes les
opérations réalisant les manipulations dont on a besoin ? Algèbre relationnelle
+ langage de requête (SQL) • Comment répartir les données dans des tables relationnelles
de manière « optimale » (et caractériser cette notion d’optimalité) ?
Modèle Entité-association + Analyse des dépendances fonctionnelles et application
d’algorithmes de normalisation
Le modèle relationnel • Requête (request) : relation calculée par le système à partir des
schémas de tables et des associations.
• Relation en Algèbre relationnelle
• Sélection en SQL par le Langage de Manipulation des données – LMD (data manage language - DML)
π Titre (σ Nom=Hugo (σAuteur.N°A=Livre.N°A (Auteur x Livre)))!
SELECT Nom, Prenom FROM Auteur, Livre WHERE Auteur.noA = Livre.noA AND Nom='Hugo'!
Livre
n°L Titre Thème n°A 1003 1 2118 1 1389 2
Auteur
n°A Nom Prenom DateNaissance 1 Hugo Victor 26 février 1802 2 Perec Georges 7 mars 1936
σ Auteur.N°A=Livre.N°A (Auteur x Livre) n°A Nom Prenom DateNaissance n°L Titre Thème n°A
1 Hugo Victor 1003 1 1 Hugo Victor 2118 1 2 Perec Georges 1389 2
Quels sont les titres des livres écrits par Victor Hugo ?!
Produit cartésien et sous-ensemble
A!B!C!…!Z!
1!2!3!…!10!
volume maximal = # 26*10 = 260!
A!B!C!…!Z!
1!2!3!…!10!
volume maximal = # seuls les couples concernés!
Produit cartésien! Sous-ensemble #du produit cartésien!
D1 x D2!D1! D2!
Domaines et tables relationnelles
Bellynck!Marechal!Dufresne!Reverdy!Marhous!
Table relationnelle!Sous-ensemble de #D1 x D2!
D1 = #Prof.Nom! D2 =#
Prof.Prénom!
Valérie!Alain!Nadège!Mazen!Lionel!
Bellynck!Marechal!Dufresne!Reverdy!Marhous!
Valérie!Alain!Alain!Nadège!Mazen!Lionel!
Prof! Nom! Prénom!
Exemple - définitions!Table relationnelle de D1 X D2 #
= sous-ensemble de D1 X D2!!!!!Tuple (t-uplet ou n-uplet) = #
!ligne d’une relation correspondant à un enregistrement!Afin de rendre l’ordre des colonnes sans importance #
tout en permettant plusieurs colonnes de même domaine, # !on associe un nom à chaque colonne. !
-> Les différentes colonnes d’une table constituent ce que l’on appelle # !les attributs de la table relationnelle.!
Les valeurs d’un attribut sont prises dans un domaine, mais par forcément toutes…!Le nom doit être porteur de sens (D1 -> Nom ; D2 -> Prénom)!
PERSONNE D1 D2
Bellynck Valérie
Chagas Lionel
Martin Céline
Définitions!Variation d’une relation (ajout/suppression/modification de tuples)! <-> invariance de sa description (domaines, relations)!Un schéma d’une table relationnelle (=schéma d’une relation)!! !est constituée de l’ensemble des attributs de la table, #
! ! !et la définition de ses domaines.!
! !Notation : PERSONNE(Nom:D1, Prénom:D2)!Intention = résultat de la description des données => schéma!Extension = état des données à un instant donné => relation!Une base de données relationnelle est une base de données dont le schéma est
un ensemble de schémas de tables relationnelles et dont les occurrences sont les tuples de ses tables.!
Par extension, le schéma d’une base de données relationnelle est !!constituée de l’ensemble de toutes les tables.!
Exemple de schéma de BD!dans le domaine de l’édition, l’impression,… : Schéma de BD relationnelle pour gérer un stock de matériaux!Produits (pNo, cat, typ, qual, ref, q, u) ! où - pNo, le numéro du produit est un entier dont chaque valeur n’apparaît qu’une seule fois dans la relation #
- cat, la catégorie est une valeur parmi PAPIER, ENCRE, ...# - typ, le type est une valeur portée par une chaîne (« papier velin », « encre UV », …) # - qual, la qualité est une valeur portée par une chaîne (spécifiant par exemple le grammage pour du papier) # - ref, la référence est la référence du produit chez un fabriquant (spécifique du produit et indépendante du fournisseur) # - q, la quantité en stock effectivement est un nombre décimal représentant la quantité dans une certaine unité# - u, l’unité est celle dans laquelle est exprimée la quantité!
Fournisseurs (fNo, nom, adresse, ville, contact, tel) ! où - fNo, le numéro du fournisseur est un entier dont chaque valeur n’apparaît qu’une seule fois dans la relation #
- nom, adresse, ville, contact et tel sont des textes !
Commandes (cNo, pNo, fNo, date, q, etat) ! où - cNo, le numéro de la commande est un entier dont chaque valeur n’apparaît qu’une seule fois #
- pNo et fNo sont des valeurs prises parmi celles de l’attribut de même nom dans les relations précédentes # et où chaque valeur peut apparaître plusieurs fois, mais ne peut être nulle. # - date est la date de la commande# - q est la quantité du produit commandé, dans l’unité correspondante pour ce produit# - etat est une valeur parmi ENPREPARATION, FAITE, RECUE, RELANCEE## !
Exemple de schéma de BD!
dans le domaine de l’édition, l’impression,… : Schéma de BD relationnelle pour gérer un stock de matériaux!
!Produits (pNo:integer, cat:enumCat, typ:enumTyp, qual:enumQual,
ref:varchar[20], q:float, u:enumU) !Fournisseurs (fNo:integer, nom:varchar[30], adresse:varchar[60],
ville:varchar[20], contact:varchar[30], tel:varchar[20]) !Commandes (cNo:integer, pNo:integer, fNo:integer, date:integer,
q:float, etat:enumE) ! !
Les opérations de base!
Deux familles d’opérateurs : !– les opérateurs ensemblistes!
! !(l’union, la différence et le produit cartésien)!
– et les opérateurs unaires (un-aires)!(élimination de lignes ou de colonnes : #
! ! !la projection et la restriction)!!Dans la suite, on utilisera les 2 relations :!
R A B C S A B C a d c
b a b
c f d
b d
g a
a f
Opérations unaires (1) :#la projection!
T = ΠX1,…,Xn (R)!T
R
Π
ΠAC (R) A C a d c
c f d
R A B C
a b c d a f c b d
Opérations unaires (1) :#la projection!
Lister les numéros et noms des fournisseurs! T = ΠfNo,nom (Fournisseurs)!
Fournisseurs fNo nom adresse ville contact tel 10 LabelPap Lille Dupont 02 89 76 43 88 11 VelinLux Amien Martin 02 22 78 98 21 12 Tollens Lyon Jacquet 04 54 28 91 01 13 MaxCoul Lyon Durand 04 54 76 33 21 14 PapiVert Nice Martin 04 21 56 56 02 15 Ink Lille Durand 02 66 54 32 12 16 MatPro Paris Dupont 01 78 78 33 24 17 PetI Lille Lefebvre 02 66 33 87 65 19 Nuances Paris Maurice 01 54 56 33 82
T fNo nom 10 LabelPap 11 VelinLux 12 Tollens 13 MaxCoul 14 PapiVert 15 Ink 16 MatPro 17 PetI 19 Nuances
(1) : la projection
« Projection » ou « Vue »!
Opérations unaires (2) :#la sélection (restriction)!
T = σQ (R)!!
T
R
Q
σB=’b’ (R) A B C a c
b b
c d
R A B C
a b c d a f c b d
Opérations unaires (2) :#la sélection (restriction)!
Lister les données sur les produits dont la quantité est supérieur à 5 T = σq>5 (Produits)!!
Lister les données sur les produits de type « velin »!T = σtyp>’velin’ (Produits)!!
Produits pNo cat typ qual ref q u 101 PAPIER velin 100gr/µ2 A100jap 18 103feuilles 102 PAPIER velin 120gr/µ2 A120jap 3 103feuilles 103 ENCRE UV 30 T30#854 1.3 102l 104 ENCRE UV 40 WN40p 2.4 102l 105 PAPIER velin 80gr/µ2 A80jap 8 103feuilles 106 ENCRE UV 12 WN12s 2.6 102l 107 ENCRE UV 20 T20#854 0.2 102l 108 PAPIER cason 180gr/µ2 A180cas 1.6 103feuilles
T pNo cat typ qual ref q u 101 PAPIER velin 100gr/µ2 A100jap 18 103feuilles 105 PAPIER velin 80gr/µ2 A80jap 8 103feuilles
T pNo cat typ qual ref q u 101 PAPIER velin 100gr/µ2 A100jap 18 103feuilles 102 PAPIER velin 120gr/µ2 A120jap 3 103feuilles 105 PAPIER velin 80gr/µ2 A80jap 8 103feuilles
(2) : la sélection (restriction)
Opérations ensemblistes (1) :#l’Union!
T = (R U S)!T
R S
U
R U S A B C
a b c d a f c b d b g a
R A B C a b c d a f c b d
S A B C b g a d a f
Opérations ensemblistes (1) :#l’Union!
Lister les numéros, noms et villes des fournisseurs habitant Lille ou Lyon!!!
T = ΠfNo,nom,ville ( σville=’Lille’ (Fournisseurs) U σville=’Lyon’ (Fournisseurs) )!T = ΠfNo,nom,ville ( σville=’Lille’ ou ville=‘Lyon’ (Fournisseurs) )!T = ΠfNo,nom,ville ( σville�{’Lille’,’Lyon’} (Fournisseurs))!!
Fournisseurs fNo nom adresse ville contact tel 10 LabelPap Lille Dupont 02 89 76 43 88 11 VelinLux Amien Martin 02 22 78 98 21 12 Tollens Lyon Jacquet 04 54 28 91 01 13 MaxCoul Lyon Durand 04 54 76 33 21 14 PapiVert Nice Martin 04 21 56 56 02 15 Ink Lille Durand 02 66 54 32 12 16 MatPro Paris Dupont 01 78 78 33 24 17 PetI Lille Lefebvre 02 66 33 87 65 19 Nuances Paris Maurice 01 54 56 33 82
T fNo nom ville 10 LabelPap Lille 12 Tollens Lyon 13 MaxCoul Lyon 15 Ink Lille 17 PetI Lille
Fournisseurs fNo nom adresse ville contact tel
10 LabelPap Lille Dupont 02 89 76 43 88
15 Ink Lille Durand 02 66 54 32 12
17 PetI Lille Lefebvre 02 66 33 87 65
Fournisseurs fNo nom adresse ville contact tel
12 Tollens Lyon Jacquet 04 54 28 91 01
13 MaxCoul Lyon Durand 04 54 76 33 21
Opérations ensemblistes (2) :#la Différence!
T = (R — S)!T
R S
—
R A B C a b c d a f c b d
S A B C b g a d a f R — S A B C
a b c c b d
Opérations ensemblistes (2) :#la Différence!
Lister les produits de catégorie PAPIER mais qui ne sont pas de qualité 120gr/µ2 ! T = ΠpNo (σcat=PAPIER(Produits) — σqual=’120gr/µ2 ’ (Produits) )!
Produits pNo cat typ qual ref q u 101 PAPIER velin 100gr/µ2 A100jap 18 103feuilles 102 PAPIER velin 120gr/µ2 A120jap 3 103feuilles 103 ENCRE UV 30 T30#854 1.3 102l 104 ENCRE UV 40 WN40p 2.4 102l 105 PAPIER velin 80gr/µ2 A80jap 8 103feuilles 106 ENCRE UV 12 WN12s 2.6 102l 107 ENCRE UV 20 T20#854 0.2 102l 108 PAPIER cason 180gr/µ2 A180cas 1.6 103feuilles
T pNo cat typ qual ref q u 101 PAPIER velin 100gr/µ2 A100jap 18 103feuilles 105 PAPIER velin 80gr/µ2 A80jap 8 103feuilles 108 PAPIER cason 180gr/µ2 A180cas 1.6 103feuilles
Opérations ensemblistes (3) :#le Produit cartésien!
T = (R X S)!!!
T
R S
XR A B C
a b c d a f c b d
S A B C b g a d a f
R X S R.A R.B R.C S.A S.B S.C a b c b g a a b c d a f d a f b g a d a f d a f c b d b g a c b d d a f
(3) : le Produit cartésien
Opérations ensemblistes (3) :#le Produit cartésien!
Lister les noms des fournisseurs avec les numéros de produits commandés #ainsi que la quantité commandé (avec l’unité correspondante).!
T = (Fournisseurs X Commandes)!!
Opérations dérivés (1) :#l’Intersection!
Lister les produits dont la quantité en stock est comprise entre 2 et 10 ! T = ΠpNo (σq>2(Produits) ∩ σq<10 (Produits) )!T = ΠpNo (σ2<q<10 (Produits) )!!
Produits pNo cat typ qual ref q u 101 PAPIER velin 100gr/µ2 A100jap 18 103feuilles 102 PAPIER velin 120gr/µ2 A120jap 3 103feuilles 103 ENCRE UV 30 T30#854 1.3 102l 104 ENCRE UV 40 WN40p 2.4 102l 105 PAPIER velin 80gr/µ2 A80jap 8 103feuilles 106 ENCRE UV 12 WN12s 2.6 102l 107 ENCRE UV 20 T20#854 0.2 102l 108 PAPIER cason 180gr/µ2 A180cas 1.6 103feuilles
Produits pNo 102 104 105 106
Opérateurs dérivés (3) :#la Jointure!
La jointure de deux relations R et S par une qualification Q #est l’ensemble des tuples du produit cartésien R X S satisfaisant la qualification Q.!
La qualification Q peut être exprimée à l’aide de constantes, comparateurs arithmétiques (<, ≤, >, ≥, =, ≠) #et opérateurs logiques ( ∨, ∧, ¬).!
On notera T = (R ��Q S ) #ou T = (R ΘQ S ) #ou T = joinQ (R, S)! T
R
Θ
S
La θ–jointure peut s’écrire avec les opérateurs de base :!! R ��Q S = σQ (R X S)!
Cette opération est essentielle dans les systèmes relationnels #et permet l’utilisation raisonnable du produit cartésien.!
R A B C S D E R ��B<D S A B C D E1 4 7
2 5 8
a c f
3 6
b c
? ? ?
2 ? 5
a ? ?
? ? ?
b ? c
Opérateurs dérivés (4) :#des jointures particulières!
L’équijointure est une θ–jointure #!avec pour qualification l’égalité entre deux colonnes.!
L’autojointure est une θ–jointure d’une table avec elle-même. #Dans un tel cas, tout se passe comme si on avait deux copies différentes de la même table. Les noms des colonnes sont alors préfixés par le numéro de la copie afin d’éviter les ambiguïtés entre les noms.!
La jointure naturelle est une équijointure de R et S sur les attributs de même nom, suivie de la projection qui permet de supprimer les attributs répétés.!
La jointure naturelle est la jointure la plus utilisée dans la pratique. !Elle s’écrit simplement R �� S et peut se définir avec les opérations de base. !
Rapport avec les BD ? Une BD relationnelle est constituée de relations primaires, constituant les
tables représentant les éléments qu’elle gère.!Pratiquement chaque relation primaire contient des attributs privilégiés :
sa clé primaire qui peut-être 1 seul de ses attributs ou plusieurs. Cette clé permet d’identifier les éléments de la relation : !
À chaque valeur de la clé ne correspond qu’une seule relation unique, que cette valeur permet d’identifier.!
!La jointure naturelle est utilisée pour reconstruire des relations plus large
reliant les informations de plusieurs tables.!La relation universelle #
= la relation qui associe « en 1 table » toutes les informations de la BD !! c’est le jointure naturelle de toutes les tables de la BD!
Mais elle a tous les défauts d’une BD mal organisée # (elle n’est pas organisée … doublons etc…)!
!
Les opérations de calcul!
! !-> ne font pas partie de l’algèbre relationnelle!! !-> produisent des nouvelles tables #
! !à partir de calculs sur d’autres tables!Exemples : Compte, Somme, …groupements!! !-> il ne peut y avoir 2 uplets identiques dans une relation : relation = ensemble#!-> il n’y a pas d’ordre dans les uplets d’une relation
(mais dans leur implémentation dans les SGBD, si…) #!
!
Vers SQL et mySQL …
• SQL est la norme qui définit le langage et ce qui doit être réalisé par le SGBD lorsqu’il reçoit de requêtes.
• mySQL est une implémentation de SQL • Idem PostGre-SQL, SQL-plus…
Exemple avec valeurs Combinaison des opérations élémentaires!->!construction des expressions de l’algèbre relationnelle !->!réponses à des questions complexes sur la base.!
Exemple : 3 tables représentant les commandes à des fournisseurs!
commandes cNo fNo pNo quté1001100310051007101110131017101910231029
17151715191319141017
103103102108107107105103102108
10211125310815
fournisseurs fNo nom adresse ville101112131415161719
DupontMartinJacquetDurandMartinDurandDupontLefebvreMaurice
LilleAmienLyonLyonNiceLilleParisLilleParis
produits pNo design prix poids couleur101102103104105106107108
fauteuilfauteuilbureaubureauarmoirecaissoncaissonclasseur
45 €40 €50 €60 €45 €15 €15 €40 €
79304035121220
grisrougevertgrisrougegrisjaunebleu
Schéma sous forme « fonctionnelle » :!produits&(pNo,&design,&prix,&poids,&couleur)&fournisseurs&(fNo,&nom,&adresse,&ville)&commandes&(cNo,&fNo,&pNo,&quté)!
Exemples de requêtes
1°) Déterminer !! ! !les numéros de fournisseur des différents « Dupont »!!!
!2°)! Déterminer !! !les numéros de fournisseur qui ont au moins trois commandes!!!
!Représenter les expressions de l’algèbre relationnelle sous forme d’arbres. #
Les feuilles sont étiquetées par les tables à exploiter, #tandis que chaque nœud est constitué d’un opérateur relationnel.!
!
Exemples de requêtes
Déterminer les numéros de fournisseur des différents « Dupont » revient à effectuer l’opération suivante :!!proj fNo (select nom=’Dupont’ (fournisseurs))!
!Déterminer les numéros de fournisseur qui ont au moins trois commandes
revient à effectuer l’opération suivante :!!proj fNo (select compte>=3 (compte fNo(commandes)))!
!Représentation des expressions de l’algèbre relationnelle sous forme d’arbres. #
Les feuilles sont étiquetées par les tables à exploiter, #tandis que chaque nœud est constitué d’un opérateur relationnel.!
!
Opérations de calcul • extension aux opérateurs de base • ne peuvent pas être exprimés à l’aide de ceux-ci Exemple : Compte = nombre de lignes d’une relation qui ont une même valeur d’attribut en commun. T = CompteX1,...,Xn (R) ou T = CountX1,...,Xn (R) X1,...,Xn étant les attributs de regroupement
Somme = somme cumulée des valeurs d’un attribut Y pour chacune des différentes valeurs des attributs de groupement X1,...,Xn
S = SommeX1,,..,Xn (R, Y) ou S = Sum X1,,..,Xn (R, Y)
CompteX1,...,Xn !
T !
R!
R A B C a n 17 b o 14 c n 9 d p 13 e m 20 f m 10
CompteB (R) B Compte n 2 m 2 o 1 p 1 && &&
Compte (R) Compte 6
Agrégats ou Groupements
non !!!
Modélisation de l’organisation des données
Votre sujet Vous êtes employé dans la société de production "Imprimerie de 4 ponts". Votre responsable vous confie la tâche de concevoir une base de données qui gère des clients, des commandes, des articles commandés et des fournisseurs. Cette base de données devra permettre de faire des recherches rapidement. Elle devra également permettre d'ajouter, de visualiser et d'imprimer des fiches lorsque que l'on a un nouveau client, commande, article ou fournisseur. Le service informatique de votre entreprise vous fait part des habitudes de programmation existantes en ce qui concerne les bases de données. Pour être viables, les bases de données doivent contenir au moins 4 tables reliées avec des relations un à plusieurs. Chaque table doit avoir sa clé primaire. Les champs dans chaque table doivent être choisis avec les types adéquats. L'exploitation de la base de données doit se faire au moyen des requêtes. Les champs qui nécessitent un calcul ne sont pas autorisés dans les tables et doivent être calculés dans les requêtes. Le remplissage et l'affichage des tables doivent être effectués à travers des formulaires et non pas directement dans les tables. Les impressions devront être gérées avec des états. Votre responsable souhaite avoir un rapport technique de 5 à 10 pages maximum présentant le travail effectué, à rendre avant la réunion finale de novembre pendant laquelle vous présentez ce travail pendant 15 minutes.
Modèle de données
• Modèle hiérarchique • Modèle réseau
• Modèle relationnel
Modèle de description des données (data model) : ensemble de concepts et de règles de composition de ces concepts permettant de décrire des données.
quelques modèles de données :
quelques modèles de conception : • Merise
• Entités-Associations • UML
Les niveaux de description Le niveau conceptuel correspond à la structure canonique des données
(= structure sémantique inhérente, sans souci d’implémentation en machine, représentant la vue intégrée de tous les groupes de travail)
modèles de données : MCD (modèle conceptuel de données) et MLD (modèle logique de données)
Le niveau interne : correspond à la structure de stockage des données
types de fichiers utilisés, caractéristiques des enregistrements (longueur, composants), chemin d’accès aux données (type d’index, chaînages, etc.)
Le niveau externe : correspond à l’ensemble des vues externes
qu’ont les groupes d’utilisateurs.
Les modèles MCD, MLD
• Modèle conceptuel des données MCD modèle abstrait pour transcrire une description
textuelle (en langage naturel) de l’organisation du système à modéliser (c-à-d des relations entre des ensembles de données)
• Modèle logique des données MLD modèle utilisé pour la mise en œuvre
informatique
Modèle entité-association Principe :
Analyser les dépendances sémantiques des relations entre les données par observation systématique du cahier des charges (analyse systémique) et les écrire sous la forme de dépendances fonctionnelles
Remplacer la relation universelle (=une relation englobant toutes les relations possibles) en collections de relations permettant de retrouver la relation universelle par jointure naturelle
Résultat : Entités et associations canoniques du monde réel schéma conceptuel
Méthode formelle : Ajustement/validation par traduction en
dépendances fonctionnelles des dépendances sémantiques puis application algorithmes de normalisation
Exemple : description textuelle d’un système d’information d’une petite société de services
Cette société réalise des projets commandés pas des clients. Les projets sont composés de plusieurs tâches qui seront réalisées pas les
salariés de l’entreprise. Chaque tâche a un coût qui lui est propre. Plusieurs salariés peuvent
participer à une même tâche et, bien sûr, une tâche est en général réalisée par plusieurs salariés. En général, les salariés sont affectés à une tâche pour une durée déterminée par une date de début et de fin. On considère qu’un salarié ne peut participer qu’une seule fois à une tâche donnée.
Pour effectuer ce travail, il utilise différents matériels référencés par l’entreprise. Un matériel peut être composé de plusieurs autres matériels de l’entreprise.
Un projet est toujours coordonné par un chef de projet, salarié de l’entreprise. Un chef de projet encadre donc d’autres salariés.
Le personnel est obligatoirement rattaché à une seule des divisions de l’entreprise mais peut, en revanche, être regroupé dans différentes équipes de l’entreprise.
Modèle entité-association : comment s’y prendre ?
(-> afficher l’exemple de description textuelle - quelques diapos plus loin…) 1. Modéliser des concepts
définir des entités avec les concepts en jeu dans le système 2. Trouver les attributs
un attribut est une propriété d'une entité, une qualité de cet objet
3. Trouver les associations une association est la possibilité de relier plusieurs entités
4. Déterminer les cardinalités des associations une cardinalité est le nombre de fois maximal et minimal
qu’une entité peut être associé à d’autres dans une association
Exemple : description textuelle d’un système de gestion simplifiée
des emprunts de livres d’une bibliothèque scolaire
Une bibliothèque scolaire propose à ses lecteurs d’emprunter les livres de son catalogue.
Les élèves sont inscrits à la bibliothèque avec leur Nom, leur Prénom, et leur Classe, cette classe étant mise à jour chaque année. Les livres du catalogue sont classés par Auteur et Titre, l’Editeur, l’Année de parution et et la Date d’acquisition du livre et son N° ISBN sont mémorisés, mais il n’y a qu’un seul exemplaire de chaque Titre d’un Auteur.
La Date d’emprunt et la Date de retour sont notées pour suivre les prêts de livres de la bibliothèque. . L’Etat d’usure du livre est vérifié à chaque emprunt et mis à jour à chaque retour.
Comment s’y prendre ? (1.)
1. Modéliser des concepts concept = élément fondamental du système à modéliser. Métaphore :
représentation en fiches (cf. boîtes/tiroirs collectant des fiches support, toutes identiques pour chaque boite)
Livre
Lecteur
Comment s’y prendre ? (2.) 2. Trouver les attributs attribut = propriété d'un concept, ou
qualité de cet objet. Exemples : – une chaîne de caractère ou – une valeur numérique sont généralement des attributs , – (le plus souvent) toute propriété qui ne peut accomplir aucune action
Livre!Auteur Titre Editeur Année de parution Date d’acquisition N° ISBN
Lecteur Nom Prénom Classe
Comment s’y prendre ? (3.) 3. Trouver les associations
association = relation entre plusieurs entités. Exemple (service de la bibliothèque) : - les livres sont associés aux « un ou plusieurs » lecteurs.
Nom d’association + Valeurs portées
Lecteur Nom Prénom Classe
Livre!Auteur Titre Editeur Année de parution Date d’acquisition N° ISBN
Date d’emprunt Date de retour
est emprunté par
Comment s’y prendre ? (4.) 4. Trouver les cardinalités
cardinalité d’une association = nombre d'instances permises pour chaque concept, = nombre de fois minimal et maximal que chacun des concepts
peut/doit être en relation avec un exemple de l’autre concept. Exemple (service de la bibliothèque) : - chaque livre est associé à « un ou plusieurs » lecteur. - chaque lecteur est associé à « zéro ou plusieurs » livre. Lecteur
Nom Prénom Classe
Livre!Auteur Titre Editeur Année de parution Date d’acquisition N° ISBN
Date d’emprunt Date de retour
est emprunté par
En lisant l'association, on obtient une phrase complète qui fait sens.
1…*! 0…*!
Notations américaines dans ACCESS
3 types de relations : (1-n), (n-m), et (1,1)
association « un à plusieurs » (1-n) - 1 enregistrement d’une table A peut-être en relation avec
plusieurs (n) enregistrements d’une table B - et 1 enregistrement de la table B ne peut pas être en relation
avec plusieurs enregistrements de la table A. association « plusieurs à plusieurs » (n-m) - 1 enregistrement d’une table A peut être en relation avec
plusieurs (n) enregistrements d’une table B - et 1 enregistrement de la table B peut-être en relation avec
plusieurs enregistrements (n) de la table A.
association « un à un » (1-1) - 1 enregistrement d’une table A peut être en relation avec 1 seul
enregistrement d’une table B - et 1 enregistrement de la table B peut-être en relation avec 1
seul enregistrement de la table A.
Exemple : chercher l’erreur Division
Numéro de la divisionNom de la divisionCA de la division
Adresse de la division
Équipe
Numéro de l’équipeSpécialisation
Client
Numéro du client.Nom du clientRaison sociale
Adresse du clientActivité du client
Contact chez le clientTéléphone du client
Fax du client
Salarié
Numéro du salarié.Nom du salarié
Prénom du salariéFonction du salarié
Rémunération du salariéCommission du salarié
Projet
Numéro du projet.Thème du projet.Libellé du projet
.Date début du projet.Date échéance
.Date fin du projet
Matériel
Numéro de matériel.Nom du matériel.Type de matériel
Tâche
Nom de la tâcheCoût de la tâche
regroupe
1,n
1,n
1,n
0,n
1,1
0,n 0,n
0,n
1,n
1,1
1,1
0,n
0,1
0,n
0,n
1,1
0,n
0,n
travaille
commande
encadreutilise
ParticipeDate début
Date fin
coordonne
constituecompose
composécomposant
a pour chef
est chef de
Exemple « notes » • Cahier des charges :
Réaliser une base de données concrète liée à la formation, avec les « vraies » données, dans laquelle
• les enseignants puissent entrer les notes • les étudiants puissent consulter leur notes et imprimer leurs bulletins de notes et moyennes
Exemple « notes » : MCD
Etudiant#nom#prénom#adel#cursus #profil#n° Etu!
Cours #n° cours #sigle!intitulé!nb heures!
Prof#nom#prénom#adel#n° prof!organisme de form.!
Industrie#id Ent. #nom#domaine#type!
Projet turoré#-transversal#sujet#responsable#n° pt!
1,n!
0,n!0,1!
0,n!
0,n!
1,n!
0,n!
1,n!
note!pourcentage!
Transformation du MCD en MLD (1)
• Traitement des entités – chaque entité devient une table ; – chaque propriété d�une entité devient une
colonne de cette table ; – l�identifiant d�une entité devient la clé primaire
de la table correspondante (création d�un index).
Association n-n : exemple d’occurence
Etudiant#nom#prénom#adel#cursus #profil#n° Etu!
Cours #n° cours #sigle!intitulé!nb heures!
Prof#nom#prénom#adel#n° prof!organisme de form.!
Industrie#id Ent. #nom#domaine#type!
Projet turoré#-transversal#sujet#responsable#n° pt!
1,n!
0,n!0,1!
0,n!
0,n!
1,n!
0,3!
1,n!
note! pourcentage!
#Bellynck#Valérie#bellynck@ef..#33!7 , 11!
#7#EFPG->Pagora#imprimerie!formation!
Association 1-n : migration de clé étrangère
Etudiant#nom#prénom#adel#cursus #profil#n° Etu!
Cours #n° cours #sigle!intitulé!nb heures!n°prof#pourcentage!
Prof#nom#prénom#adel#n° prof!organisme de form.!
Industrie#id Ent. #nom#domaine#type!
Projet turoré#-transversal#sujet#responsable#n° pt!
1,n!
0,n!0,1!
0,n!
0,n!
1,n!
0,3!
1,n!
note! pourcentage!
#Bellynck#Valérie#bellynck@ef..#33!7!#
18#M1B2#b. don#15h#33#12%!
Attribut d’association : table de correspondance
Etudiant#nom#prénom#adel#cursus #profil#n° Etu!
Cours #n° cours #sigle!intitulé!nb heures!n°prof#pourcentage!
Prof#nom#prénom#adel#n° prof!organisme de form.!
Industrie#id Ind. #nom#domaine#type!
Projet turoré#-transversal#sujet#responsable#n° pt!
1,n!
0,n!
0,1!
0,n!
0,n!
1,n!
0,3!
1,n!
note!
pourcentage!Note!n°Etu#n° cours!note!
Méthode de transfertMCD -> MLD Entité :
1 table Association 1-n :
migration de la clé primaire de la table du côté n de l’association vers la table du côté 1 + de ses attributs. nouvel attribut := « clé étrangère »
Association n-n : création d’une table d’association pour mémorisés les relations associées + les attributs correspondants 2(+) « clés étrangères » dans la table d’association
Association 1-1 : fusion des 2 tables ou migration croisée des clés
Transformation du MCD en MLD (2-a)
• Traitement des associations – Une association (0,n)–(0,1) (lien hiérarchique)
Entité 1
AB
Entité 2
CDRelation
E0,1 0,n
Table 1
A B C E
Table 2
C D ∞ 1
devient :
Transformation du MCD en MLD (2-b)
• Traitement des associations – Une association (0,n)–(0,n) (lien maillé)
devient :!où index #soit = sous-relation des clés de toutes les entités associées!soit = nouvel index!
Entité 1
AB
Entité 2
CDRelation
E0,n 0,n
Table 1
A B
Table 2
C D
Table 3
A C E
∞ ∞ 1 1
Table 1
A B
Table 2
C D
Table 3
E A C E
∞ ∞ 1 1
Transformation du MCD en MLD (2-c)
• Traitement des associations – Une association n-aires conduit à la création d�une
nouvelle table,
devient : où index soit = sous-relation des clés de toutes les entités associées soit = nouvel index
Entité 1
Id1 B C
Entité 2
Id2 E F G
est associé à
H 1,n
1,n
Entité 3
Id3 K L
1,n
Table 1
Id1 B C
Table 2
Id2 E F G
Table 4
Id1 Id2 Id3 H Table 3
Id3 K L
∞ ∞
∞
1 1
1
Transformation du MCD en MLD (2-c)
• Traitement des associations – Une association associée à une autre association conduit à la création de deux nouvelles tables,
Entité 1
Id1 B C
Entité 2
1,n 1,n
Id3 K L
1,n
1,n
est associé à
H
Association 12-3
I
Entité 3
Id2 E F G
Table 1
Id1 B C
Table 2
Id2 E F G
Table 4
Id1 Id2 H
∞ ∞
∞
1 1
1 Table 5
Id1 Id2 Id3 I ∞
1 Table 3
Id3 K L
devient :!
Modèle Entités-Associations enrichi
Pour décrire plus précisément le monde réel, affinage du modèle
sous-entités sous-associations extension du transfert MCD -> MLD
Fin du cours théorique
Références : • G. Gardarin,
Bases de données – Les systèmes et leurs langages , Eyrolles, 1982 ; ré-édition 1994
Objet et Relationnel , Eyrolles, 1999
• Fabien Celaia, La généralogie des SGBD ,
http://fadace.developpez.com/sgbdcmp/story/
• Équipe pédagogique SGBD de l’Ensimag Supports de cours PSGBD
Ensimag, 2011 • http://www.i3s.unice.fr/~nlt/cours/licence/sgbd1/sgbd1_cours.pdf
Ecole Internationale!du Papier, de la Communication Imprimée et des Biomatériaux !
Recommended