Download - Notion de SGBD Bases de Données Relationnelles - …tiprof.fr/SGBasesDonnees/Supports-Cours/coursBD-TheorieSysteme... · Bases de Données Relationnelles Théorie + Système + Conception

Transcript

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 !