Cours d`Informatique “Bases de données”

1. L’informatique
Cours d’Informatique
“Bases de données”
Que serait le monde sans l’informatique ?
1° année
Antoine Cornuéjols
!"#$%%&&&'()*+#(*,-./0!'1*%21*3,41+%5/56*/-%0+*42/7+8-%
(4.+,4/'0+*42/7+8-9()*+#(*,-./0!'1*
!"#$%%&&&'()*+#(*,-./0!'1*%21*3,41+%5/56*/-%0+*42/7+8-%:/(0!,4)%;<=>%:?%?+2*-3@;3ABCD'#E1
Informatique - Bases de données • ! A. Cornuéjols 2014
Rosetta et Philae
2
/119
4
/119
1. L’informatique
Transports
Avions
Voitures (des ordinateurs sur roues)
Communications
Réseaux (communications par paquets)
!"#$%&'"#
Satellites
()*+#,-+'"#!"#$!%&'()*&+,-$!
Modes de pensée
!+.$/.!"#!.&!/(&0#*/,)(#
Calculs complexes / Simulations
!"00/#,$+'"#
Connaissances toujours à portée de clics
1.+#,2$+'"#!"#$!#123()#-*#$!$,4$!*,-/(&)-/#$
Modification de la mémoire et de la cognition
3)+,4%0%#4!"#$!",--3#$
Informatique - Bases de données • ! A. Cornuéjols 2014
3
/119
Informatique - Bases de données • ! A. Cornuéjols 2014
1. Changement de paradigme
1. Changement de paradigme
Carte analogique vs. carte numérique sur tablette graphique
Informatique - Bases de données • ! A. Cornuéjols 2014
5
La photo numérique
/119
1. Changement de paradigme
Informatique - Bases de données • ! A. Cornuéjols 2014
6
/119
Informatique - Bases de données • ! A. Cornuéjols 2014
8
/119
1. Changement de paradigme
La photo numérique
La photo numérique
Support numérique
Copies multiples
Affichage sur plusieurs dispositifs
Effets spéciaux : calculs
Les appareils à venir vont être très peu chers et très bons ...
... grâce à l’informatique
Informatique - Bases de données • ! A. Cornuéjols 2014
7
/119
Partie 1 - Place de l’informatique
Partie 2 - Les bases de données
Partie 3 - Bases sur les BD
Partie 4 - Conception des BD
Partie 5 - Manipulation : SQL
Partie 6 - PHP / MySQL
Quatre approches pour appréhender le monde
56!!!>;2)():4#!?!"#$*()2+,-!#/!*.&$$#;#-/
@6!!!AB3,():4#!?!C,"3.)$&+,-D!*,-$/(4*+,-!"#!/B3,()#$
56!748#$/9*#!:4#!.8)-%,(;&+:4#!<
56!!!6,0/.+'"#!?!$E$/F;#$!*,;2.#1#$!#/G,4!-,-!(#2(,"4*+'.#$
=-!!"#$%&#'(%)&(*!$4(!.#!;,-"#
78!!!9"/,..%:;%:;"##<%-!HI!J)K!"&/&!LM
F4+*5/-G5(--/-GE/GE+44H/-G425H*,-H/I(*)/5/4.GE,-#+4,68/J+2*0/-G/.G1+*5(.-G.*K-GE,LH*/4.-
Informatique - Bases de données • ! A. Cornuéjols 2014
9 /119
L’informatique et les sciences du vivant
Informatique - Bases de données • ! A. Cornuéjols 2014
10 /119
Le calcul et l’information comme nouveaux paradigmes
!"#$%"&'#()&#&"*+","$'#-*"#.
/)#!"#$%&'()#"'#'0*'"&#+"&#&12"$1"&#"$#3#0,2-*"&#4#$%"52&'"6)2"$'#()&
N3-,;#!O!")P&)-#$!"#!;)..)#($!"#!2&)(#$!"#!'&$#
3#!"#$"#%&$$'()#*')#+,-./"#0(0'$.1#+"#%"2232"1#+"#4))31#&/5'$"1#
Q.)K-#;#-/$
($+(0(+3#".#*"3.6-./"#7-7"#")*8%"1#+&$.#&$#$"#*3())"#*')#+(/"#
R#*,-$/(4*+,-
93,(2#!"#$%&#+"#2,($:&/7'4&$1#93,(2#"'()"&#+"#2,($:&/7'4&$1#93,(2#
S-/#(2(3/&+,-
*+&"#93,(2#'&,#)"#+"#2,($:&/7'4&$;#4
/"#*"+,-(.)%)#/0+1&%-2'()#$%)*6)2'#("*'78'6"#()&#"$106"#9'9#
:910*;"6'
Michel Serres!!
;#$4(#$!2.&-3/&)(#$
$/,*T&K#!#/!)-/#((,K&+,-$!"#!'&$#$!"#!",--3#$!")U#($#$!#/!")$2#($3#$
#1/(&*+,-$!"#!/#-"&-*#$!#/!"#!*,((3.&+,-$
Informatique - Bases de données • ! A. Cornuéjols 2014
11 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
12 /119
1. L’informatique comme science
1. L’informatique comme outil de pensée en biologie
Par rapport à d’autres sciences ?
• La génétique comme un langage : codage / décodage
• ADN --> ARN --> Protéines
• Protéines <--> Protéines
• Le calcul sur graphe permet de modéliser la :
• diffusion d’un virus
• la compétition entre espèces
<$"#3+&)#+)04)015&%%-/"*&)1
• La dynamique évolutive comme théorie des jeux répétés sur réseaux
• Les systèmes multi-agents permettent de modéliser les :
• insectes sociaux
• bancs de poissons
:4)!",)/!V/(#!=")0+.,-<
#/!)<+.,-<
• La récursion permet de modéliser la croissance des plantes
• ...
Informatique - Bases de données • ! A. Cornuéjols 2014
Au point que certains
physiciens pensent que
l’univers n’est en fait que la
manifestation d’un
gigantesque ordinateur.
6-&3$##)%)#/
7(%"*&3-2$##:*#,0$:"
13 /119
1. L’informatique
Informatique - Bases de données • ! A. Cornuéjols 2014
14 /119
1. L’informatique
1- @+$?,#%Architecture
1- Science du raisonnement et de son automatisation
Système d’exploitation ; environnement de programmation
Machine
Algorithmes
2 - A#=")0+'"#
Codage
Organisation : fichiers, BD, ...
2- Science du codage de l’information
3 - >.*"),4?0%
Numérisation
Expressions ; variables ; fonctions ; ...
Indépendance du support matériel
Structure
Communication
4 - B+#*+*%
Traduction sans ambiguïté entre hommes et machines
Informatique - Bases de données • ! A. Cornuéjols 2014
15 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
16 /119
1. Qu’est-ce que l’informatique
1. Qu’est-ce que l’informatique
L’informatique concerne les abstractions ...
... en faisant attention à :
L’informatique concerne les abstractions ...
• L’efficacité
• Rapidité
• Taille mémoire
• Coût calcul
• Choisir les bonnes abstractions
• Utiliser plusieurs niveaux d’abstraction simultanément
• Fonctionnement correct
• Est-ce que cela fait ce que l’on veut ?
• Définir les relations entre ces niveaux d’abstraction
• Est-ce que le programme donne une réponse ?
• -ilité
• Simplicité et élégance
•
•
•
•
•
Comme les maths
Informatique - Bases de données • ! A. Cornuéjols 2014
i,j=1
Maintenabilité
Coût
...
Informatique - Bases de données • ! A. Cornuéjols 2014
18 /119
1. Questions qui occupent les informaticiens
Comment organiser un calcul
xi yj
Comme les sciences
de l’ingénieur
Modifiabilité
17 /119
1. Questions qui occupent les informaticiens
2
!
Utilisabilité
Comment organiser un calcul
2
!
?
xi yj
i,j=1
2
!
?
xi yj = x1 y1 + x1 y2 + x2 y1 + x2 y2
i,j=1
4 produits + 3 sommes
Informatique - Bases de données • ! A. Cornuéjols 2014
19 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
20 /119
1. Questions qui occupent les informaticiens
1. Questions qui occupent les informaticiens
Comment organiser un calcul
2
!
xi yj
i,j=1
2
!
Comment organiser un calcul : le tri
?
xi yj = x1 y1 + x1 y2 + x2 y1 + x2 y2
i,j=1
4 produits + 3 sommes
2
!
xi yj = (x1 + x2 ) (y1 + y2 )
i,j=1
2 sommes + 1 produit
Informatique - Bases de données • ! A. Cornuéjols 2014
21 /119
1. Questions qui occupent les informaticiens
Tris « naïfs »
Tri par sélection
22 /119
1. Questions qui occupent les informaticiens
Tris « naïfs »
Sélection : chercher
l’élément le + petit et
l’échanger avec le 1er
élément du tableau, puis
répéter sur le tableau - le 1er
élément
Tri par insertion
Informatique - Bases de données • ! A. Cornuéjols 2014
Sélection : chercher
l’élément le + petit et
l’échanger avec le 1er
léméent du tableau, puis
répéter sur le tableau - le 1er
élément
Tri par insertion
Tri par sélection
Complexité en O(n2)
Complexité en O(n2)
Insertion : comme on
range des cartes
n = 106 => O(1012) opérations
Tris réfléchis
Tris réfléchis
Tri par fusion
Tri par fusion
Tri rapide (quicksort)
Tri rapide (quicksort)
Complexité en O(n log(n))
Complexité en O(n log(n))
n = 106 => O(2 x 106) opérations !!
Informatique - Bases de données • ! A. Cornuéjols 2014
Insertion : comme on
range des cartes
n = 106 => O(1012) opérations
Quicksort : on prend un
pivot et on range tous les
éléments < avant et >=
après et on réitère sur les
sous-tableaux
n = 106 => O(20 x 106) opérations !!
23 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
24 /119
1. Questions qui occupent les informaticiens
1. Questions qui occupent les informaticiens
Comment organiser un calcul : le tri
Comment organiser un calcul : le tri
12 17 10 23
Informatique - Bases de données • ! A. Cornuéjols 2013
25 /107
1. Questions qui occupent les informaticiens
10 23
12 17
10 23
12 17
10 23
11 33 77 83
10 11 12 17 23 33 77 83
10 12 17 23
Informatique - Bases de données • ! A. Cornuéjols 2013
26 /107
1. Questions qui occupent les informaticiens
Comment organiser un calcul : le tri
Informatique - Bases de données • ! A. Cornuéjols 2013
12 17
33 77 83 11
Comment organiser un calcul : le tri
27 /107
Informatique - Bases de données • ! A. Cornuéjols 2013
28 /107
1. Questions qui occupent les informaticiens
1. Questions qui occupent les informaticiens
Comment organiser un calcul : le tri
Informatique - Bases de données • ! A. Cornuéjols 2013
Comment organiser un calcul : le tri
29 /107
1. Questions qui occupent les informaticiens
30 /148
1. Questions qui occupent les informaticiens
Comment organiser un calcul : le tri
Informatique - Bases de données • ! A. Cornuéjols 2011
Informatique - Bases de données • ! A. Cornuéjols 2011
Comment organiser un calcul : le tri
31 /148
Informatique - Bases de données • ! A. Cornuéjols 2013
32 /107
1. Questions qui occupent les informaticiens
1. Questions qui occupent les informaticiens
Comment organiser un calcul : le tri
Informatique - Bases de données • ! A. Cornuéjols 2013
Comment organiser un calcul : appariemment de graphe
33 /107
1. Questions qui occupent les informaticiens
Informatique - Bases de données • ! A. Cornuéjols 2014
34 /119
1. Questions qui occupent les informaticiens
Comment organiser un calcul : intelligence artificielle
Comment organiser un calcul : L’ennemi absolu
Bug ! panne
Informatique - Bases de données • ! A. Cornuéjols 2014
35 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
36 /119
seconde ou plus.
Ces aspects techniques permettent de comprendre ce qu’il est possible de réaliser,
comment et à quel prix. Nous les avons volontairement quelque peu simplifiés pour
1. Questions quifaciliter
occupent
les informaticiens
1. Questions
leur compréhension.
Et quelques mots à l’attention de ceux qui aiment
se réfugier qui occupent les informaticiens
derrière « je ne comprends rien à l’informatique ». La vision de l’informatique véhiculée
L’ennemi absolu
: le bug
Comment
par les médias souffre d’une trop grande fascination pour le matériel
et la coder / représenter de l’information
programmation. À mon avis, il importe peu de comprendre les détails du fonctionnement
Plantage des ordinateurs, distributeurs bancaires, systèmes de réservation,
très complexe d’un processeur ou d’une carte graphique. Il est par contre essentiel de
sites Web, etc.
maîtriser les bases de l’algorithmique et de sa mécanique du raisonnement. Il n’est pas
non plus nécessaire
de savoir
programmer (même si une expérience de programmation
Blocages de téléphones
ou d’appareils
photos
avec un langage comme CAML – Categorical Abstract Machine Language – peut faciliter
Crash du téléphone
interurbainde
américain
la compréhension
l’algorithmique). Pour des questions de performance, il peut être
utilemaldeplacée
comprendre
une ligne de code
sur un millionoù l’information que nous utilisons est stockée, en mémoire, sur
disque ou sur le réseau. Surtout, il est indispensable de comprendre le sens de cette
Explosion d’Ariane 501 (1996) et perte de satellites
information, comment elle est représentée, comment elle est organisée.
Débordement
arithmétique
dans chiffres
un calcul inutile
Voici
quelques
à retenir :
21
20
Support de stockage
Temps d’accès
Disque dur
millisecondes
millisecondes ou plus
décisecondes voire secondes
Mémoire vive
microsecondes
Bug subtil dans la division flottante
du Pentium
Coût : 470 millions de dollars
pourlocal
Intel
Réseau
La Toile
Taille
gigaoctets (109)
quelques centaines de gigaoctets au téra
téraoctets (1012)
Virtuellement !
Informatique - Bases de données • ! A. Cornuéjols 2014
37 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
38 /119
Mesurer les zettaoctets à la cuillère à café
En alignant les bits, nous pouvons représenter des informations. Nous pouvons stocker de
plus en plus d’informations pour les retrouver à la demande, telle une sauvegarde quasi
1. L’informatique : mémoire et stockage
1. L’informatique
illimitée de notre mémoire personnelle.
Nous pouvons aller au-delà des dimensions déjà mentionnées en alignant les bits :
23
kilo méga giga téra péta exa zetta yotta
Ordres de grandeurs
• Représenter le monde
22
103
106
109
1012
1015
1018
1021
1024
/ inférer / raisonner
• Calculer
Discutons brièvement ces unités de mesures. Par exemple, cette leçon devrait
peser
quelques 100 000 octets, c’est-à-dire 100 kilooctets. Le kilooctet est une mesure « cool »
1- Savoir représenter
car il est presque convenable de confondre
103 = 1000 et 210 = 1024, ce qui permet de
!!,*/#/$!!H5XX![,M
3%C4%!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!?!!W5XX!2&K#$!!!!!!!!!!Y9Z!!!5XX!6!5X
de données
passer facilement du système décimal, le plus commun, au système binaire, cher• Structures
aux
"!,*/#/$!!H5X!C,M
• Représentation des connaissances
@/-,D/%!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!?!!\,-*#(/,!])U&.")!!!Y9Z!!!5X!6!5X
informaticiens. Une dizaine de Nocturnes de Chopin sur mon téléphone prennent
75 mégaoctets. La #vidéo
de la remise de diplôme de ma fille et ses quelques gigaoctets
E,;<"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!?!!!!!::$!5X
!,*/#/$!!HN,M
2- Tous les calculs ne sont pas possibles
nous conduisent aux frontières du gigantisme. Selon les chiffres de Michael Brodie8, tous
$%&
• Notion de complexité
3"/-:.%-:.,F)%-!0&;&)$!3*()/$!!!!!?!!@XX!6!5X
les livres jamais écrits ne!H@XX!A,M
demanderaient que 200 téraoctets en texte brut et la quantité de
• Distribution / Parallélisation / « Cloud computing »
données
produites
par
le
collisionneur
de
particules
du
CERN
en
une
minute
est
de
>123()#-*#$!BG!!!!!!!!!!!!!!!!!!!!!!!!!?!!!!W!@XX!A,!G!;Il n’est pas facile de programmer de manière fiable
l’ordre d’une centaine de pétaoctets. Pour représenter toutes les phrases 3-jamais
3)+2$:+##/%.!$4(!S-/#(-#/!!!!!!!!?!!W!5X%$!,*/#/$!!H5!^,M
prononcées, il faudrait quelques exaoctets. Enfin, le zettaoctet, c’est l’ordre de grandeur
• Méthodes de production de logiciel
du trafic annuel sur Internet de nos jours, et c’est aussi celui du stockage disponible• Vérification
(en
/ Certification
24
Informatique - Bases de données • ! A. Cornuéjols 2014
4 sur 26
39 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
1/12/14 16:57
40 /119
1. L’informatique ... et vous
1. L’informatique et VOUS
Outils de travail
_&!H/)%+/'D/%!
En quoi cela vous concerne ?
_#!4)+F+,.:$"..+H")+'=!#/!S-/#(-#/
1- Acteur direct / en interaction directe avec l’informatique
Programmation - Bases de Données
• Programmation de simulation
748#$/9*#!:4)!#$/!2,$$)'.#!<
• Utilisation de BD, de SIG
E.g. AA :
- Croire que c’est magique
- Ne pas demander assez
• Traitement d’images, télédétection
•
Bio-informatique
A#4%)+$'"#!&U#*!"#$!)-%,(;&+*)#-$
I<+.,-+'"#-!"#!2#+/$!$E$/F;#$
Simulation
2- En interaction avec des acteurs directs
\,;2(3B#-$),-!G!2(3")*+,-!"#!-J-4K0%-:$"0&.%C%-
• Que puis-je attendre de l’outil informatique ?
R3&.)/3!U)(/4#..#!G!&4K;#-/3#
• Qu’est-ce qui est possible / difficile / impossible
Fouille de données
• Comment interagir / dialoguer / orienter
I<*/.+),4<-D!/#-"&-*#$D!&)<;,$'"#-D!666
Informatique - Bases de données • ! A. Cornuéjols 2014
41 /119
1. L’informatique ... et vous ?
Informatique - Bases de données • ! A. Cornuéjols 2014
42 /119
1. Informatique et recherche à AgroParisTech
Outils de travail
La bureautique
Le travail collaboratif et Internet
SIG (Systèmes d’Information Géographiques)
Programmation - Bases de Données
Simulation
Compréhension / prédiction de systèmes complexes
Réalité virtuelle / augmentée
Fouille de données
1A « Bureautique »
2A (ATHENS) « Publier
sur Internet »
1A/2A « SIG »
1A TC « SI »
2A « C et télédétection »
2A « Java »
2A (ATHENS)
« Informatique et
Sciences de la Vie »
1A « Module Intégratif »
Régularités, tendances, prédictions, ...
• Étude des échanges thermiques dans l’Atlantique Nord :
• Le Gulf-Stream est-il moins actif ?
• Grosses bases de données réparties ; apprentissage
artificiel ; Systèmes experts, ...
• Étude du risque alimentaire :
• à partir de textes sélectionné sur Internet (EU / USA)
• Ontologies, traitement de la langue, ...
• Analyse en imagerie médicale :
• détection en temps réel du contour du rein
• Apprentissage artificiel, analyse d’images
3A DA « IODAA »
Informatique - Bases de données • ! A. Cornuéjols 2014
43 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
44 /119
Organisation du cours
1. Partie Organisation de l’information : les Bases de Données
`(K&-)$&/),-!"4!*,4($
•
Un amphi
•
4 TD/TP
•
1 TP / QCM
(1 / 5)
2. Partie Algorithmique - Programmation (PHP)
•
Un amphi
•
8 TD/TP
•
1 TP / QCM
(1 / 5)
3. Projet
Informatique - Bases de données • ! A. Cornuéjols 2014
•
6 TP
•
Soutenance
45 /119
(3 / 5)
Informatique - Bases de données • ! A. Cornuéjols 2014
46 /119
Ce cours et vous : le projet
• b!2&(+(!"84-!$+?,%):;%-:$?+)*%-:L:
_#!2(,0#/!aa<
•
@";<.,-%):.%:M0"#;%N:!H>-+/3$!G!R#.&+,-$M
•
3)+;/,)%:#-!",--3#$!#/!(#.&+,-$!?:4+H.%-
•
R3c3*B)(!&41!"&<)+'"#-:&"--,H.%-!$4(!.&!'&$#!"#!",--3#$
• 1)"*)+00%):4-#!)-/#(%&*#!d#'!&U#*!.&!'&$#!"#!",--3#$
Informatique - Bases de données • ! A. Cornuéjols 2014
47 /119
SQL
•
Q!/(&U#($!@J6OB!H4-!eNJfM
•
P<)%)!.#$!/'.,-+4%/)-:g!.#$!)%--"/)$%-
•
()*+#,-%):.#$:)%D/Q4%-:#/:0";,2$+'"#-!"#!.&!Jf
P
My
HP :
Informatique - Bases de données • ! A. Cornuéjols 2014
48 /119
Partie 1 - Place de l’informatique
Partie 2 - Les bases de données
Partie 3 - Bases sur les BD
Partie 4 - Conception des BD
Partie 5 - Manipulation : SQL
Partie 6 - PHP / MySQL
2. Quelles données ?
Les données sont omniprésentes autour de nous ...
• Base de données sur des films (e.g. Allociné)
• Base de données bibliothécaire (e.g. BN, Amazon, ...)
@6!_#$!J&$#$!"#!f,--3#$
• Marathon
de New-York, Paris, Londres, du Médoc, ...
• Location de voitures (e.g. Hertz, ...) / Réservation de place d’avion
• Sécurité sociale (carte Vital), Hôpitaux, ...
• Communications téléphoniques (opérateurs)
• Second Life
• Systèmes d’information géographique
• ...
Informatique - Bases de données • ! A. Cornuéjols 2014
49 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
50 /119
1. L’informatique : mémoire et stockage
2. Quelles données ?
Les données sont omniprésentes autour de nous ...
!"#!"#$%&"'()*+,#"-./#+#$%#&'(($)%#*+#%,'-.$,#/
0-$#0#+1&"%&"#/
='01>)?"@#91A)$?"@#)$)+B&"#.#3+&)#+)04)304$##")3
Informatique - Bases de données • ! A. Cornuéjols 2014
51 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
52 /119
La toile ?
2. Quelles données ?
Les données sont omniprésentes autour de nous ... dans l’entreprise
<$#2,,"$&"#-J-4K0%:;%:2$?,%)• Les clients
C6D&#6*:2,"$')26"#"$#'"6,"&#:"#E0$1F0$$)+2'9&#:"#?"&F0$#:"#
:0$$9"&
• Les produits
• Les commandes
• Les factures
• ...
R##6PST##G<=).87"#+"#>")4&$#+"#?')")#+"#@&$$A")H
Informatique - Bases de données • ! A. Cornuéjols 2014
53 /119
2. Quelles données ?
Informatique - Bases de données • ! A. Cornuéjols 2014
54 /119
2. Comment les stocker ?
Les données sont omniprésentes autour de nous ...
Informatique - Bases de données • ! A. Cornuéjols 2014
Comment les ranger dans un ordinateur ?
55 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
56 /119
2. Comment structurer les données ?
2. Comment les stocker ?
Comment les ranger dans un ordinateur ?
En regardant d'un peu plus près ...
• scanner les bons de commande [1/10]
données
du client
• copier les données dans un tableau Word [2/10]
• copier les données dans une feuille Excel [4/10]
Scanner : seulement pour le
stockage : pas d’édition de factures, de
gestion des stocks ou de ou
comptabilité.
• copier les données dans une base de données [10/10]
données
de la
commande
données
d'un détail
Word : exploitation des données pas
vraiment supportée
Excel : format très complexe.
Comment mettre tout dans un seul
tableau ? Comment faire face à
croissance de l’activité ?
3 catégories de données
Informatique - Bases de données • ! A. Cornuéjols 2014
57 /119
2. Comment structurer les données ?
NCOM
DATECOM
30188 2/1/2009
58 /119
2. Comment structurer les données ?
Reportons ces données dans des tableaux :
données de la commande
Informatique - Bases de données • ! A. Cornuéjols 2014
Données sans redondances et avec références :
données du client
données de la commande
données du client
TOTAL-COMMANDE
NCLI
NOM
ADRESSE
LOCALITE
NCOM
NCLI
DATECOM
NCLI
69720
B512
GILLET
14,r. de l'Eté
Toulouse
30188
B512
2/1/2009
B512
données des détails
NOM
ADRESSE
LOCALITE
GILLET 14,r. de l'Eté Toulouse
données des détails
NPRO
LIBELLE
PRIX
QCOM
SOUS-TOTAL
NCOM
NPRO
QCOM
LIBELLE
PRIX
CS464
CHEV. SAPIN 400x6x4
220
180
39600
30188
CS464
180
CHEV. SAPIN 400x6x4
220
PA45
POINTE ACIER 45 (2K)
105
22
2310
30188
PA45
22
POINTE ACIER 45 (2K)
105
PA60
POINTE ACIER 60 (1K)
095
70
6650
30188
PA60
70
POINTE ACIER 60 (1K)
095
PH222
PL. HETRE 200x20x2
230
92
21160
30188
PH222
92
PL. HETRE 200x20x2
230
Observations :
Observation
1. les données TOTAL-COMMANDE et SOUS-TOTAL sont calculées : inutile de
les conserver, on pourra les recalculer en cas de besoin
si plusieurs détails mentionnent le même produit, ses caractéristiques
sont répétées autant de fois : on isole les données des produits dans un
2. il est impossible de reconstituer le bon de commande d'origine : quel est le
client de la commande, quelle est la commande d'un détail ?
Informatique - Bases de données • ! A. Cornuéjols 2014
tableau spécifique
59 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
60 /119
2. Comment structurer les données ?
2. Comment structurer les données ?
Ajoutons d'autres données : notre première base de données
Distribution optimale des données des bons de commande
données de la commande
données du client
NCOM
NCLI
DATECOM
NCLI
NOM
ADRESSE
LOCALITE
30188
B512
2/1/2009
B512
GILLET
14,r. de l'Eté
Toulouse
données des détails
données des produits
NCOM
NPRO
QCOM
NPRO
LIBELLE
PRIX
30188
CS464
180
CS464
CHEV. SAPIN 400x6x4
220
30188
PA45
22
PA45
POINTE ACIER 45 (2K)
105
30188
PA60
70
PA60
POINTE ACIER 60 (1K)
95
30188
PH222
92
PH222
PL. HETRE 200x20x2
230
Informatique - Bases de données • ! A. Cornuéjols 2014
61 /119
2. Comment structurer les données ?
•
Que veut-on faire de ces données ?
Vérifier une commande lors de son enregistrement : le client est-il connu ? son
adresse a-t-elle changé ? les produits commandés sont-ils répertoriés ?
On a besoin de systèmes spécifiques pour conserver et manipuler les
données
Produire les factures
Les données sont représentées dans des tables
•
Les lignes sont des entités
•
Les colonnes sont des propriétés
62 /119
2. Utiliser une base de données
Quelques leçons partielles
•
Informatique - Bases de données • ! A. Cornuéjols 2014
Préparer le réapprovisionnement des produits en rupture de stock
Calculer le chiffre d'affaire mensuel
•
Le découpage en tables demande une analyse et une optimisation
•
L’analyse doit aller au-delà des besoins immédiats (abstraction)
Informatique - Bases de données • ! A. Cornuéjols 2014
Etudier la répartition géographique des ventes
Et mille autres applications ...
63 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
64 /119
2. Utiliser une base de données
2. Utiliser une base de données
Que peut-on faire de ces données ?
Que peut-on faire de ces données ?
Avant tout, les conserver aussi longtemps que nécessaire !
Un dernier exemple :
calculer la répartition du chiffre d'affaire par localité et par produit
Les interroger : quel est le numéro, le nom et l'adresse des clients de
Toulouse ?
select NCLI, NOM, ADRESSE
from
CLIENT
where LOCALITE = 'Toulouse';
ou encore :
ile
fac
quelles sont les commandes des clients de Toulouse ?
select C.LOCALITE, P.NPRO, sum(D.QCOM*P.PRIX)
er
from
CLIENT C, COMMANDE M, DETAIL D, PRODUIT P
ay
ss dre
d'e
where C.NCLI = M.NCLI and M.NCOM = D.NCOM
le pren nt !
i
t
inu com nsta
and
D.NPRO = P.NPRO
de ur l'i
po
group by C.LOCALITE, P.NPRO;
lus
up
select NCOM
pe ile
un iffic
d
from
COMMANDE
where NCLI in (select NCLI
from CLIENT where LOCALITE = 'Toulouse');
Cette question complexe est résolue en une seule instruction SQL de 5 lignes !
requêtes rédigées dans le langage SQL
Informatique - Bases de données • ! A. Cornuéjols 2014
65 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
Partie 1 - Place de l’informatique
Partie 2 - Les bases de données
Partie 3 - Bases sur les BD
Partie 4 - Conception des BD
2. Les SGBD
66 /119
Partie 4 - Manipulation : SQL
Partie 5 - PHP / MySQL
Les systèmes de gestion de bases de données ou SGBD.
•
Organisation des données : le SGBD organise les données en tables permanentes
stockées sur disque; il crée les mécanismes garantissant un accès rapide aux données; il
3- Bases ... sur les Bases de Données
informe les utilisateurs sur ces structures.
•
Gestion des données : le SGBD garantit l'évolution cohérente des données; il vérifie
que les contraintes (unicité, référence entre tables, etc.) sont respectées.
•
Accès aux données : le SGBD permet l'accès aux données à la fois par l'utilisateur
occasionnel et par les programmes de traitement de données.
Informatique - Bases de données • ! A. Cornuéjols 2014
67 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
68 /119
quiStar
représentent
relations
comme
Séance,
des entrées
de ces relations comme
Wars
G. des
Lucas
Harrison
Ford Film ou
Stars
Wars
Sel
22:15
«
Star
Wars
»,
des
variables
comme
t,
h,
et
des
symboles
logiques,
⋀ (et), ⋁à(ou),
(non),
Dans le modèle relationnel, les données sont organisées en tableaux
deux¬dimensions
Un système
de gestion
de de
bases
de données
de médiateur
entre
des individus
et des
Un
système
de ∃gestion
de bases
données
sert
desert
médiateur
entre
des
individus
et des
30 ⇒30
(implique),
(existe),
∀
(pour
tout).
Avec
tout
ça,
des
formules
logiques
peuvent
être
que nous
appellerons
des relations.
la différence
des mathématiciens,
nous supposons
machines.
Pour mieux
s’adapter
auxÀindividus,
doit organiser
et présenter
les données
machines.
Pour mieux
s’adapter
aux individus,
il doit il
organiser
et présenter
les données
comme
: finie.
de façon
Il doit
aussi proposer
un langage,
exprimer une
des requêtes,
lesconstruites
relations
de intuitive.
taille
Comme
illustration,
nouspour
utiliserons
base de données
31
BD de
et façon
calcul
relationnel
intuitive.
Il doit aussi
proposer un langage, pour exprimer des requêtes,
des
êtreset
humains.
Ces
exigences
forment
lede
point
de départ
du
qfacilement
∃ t, une
d utilisable
( Film(t,
r,par
«
Humphrey
Bogart
»)
⋀ Séance(t,
s,
h) point
) (figure
HB =en
facilement
utilisable
par
des
êtres
humains.
Ces exigences
forment
le
départ
du
consistant
relation
Film
une
relation
Séance
1).
Une
ligne
de ces
10
10
modèle
relationnel
proposé
par
Ted
Codd,
un
chercheur
d’IBM,
dans
les
années
1970.
modèle
relationnel
proposé
par
Ted
Codd,
un
chercheur
d’IBM,
dans
les
années
1970.
est appelée
un n-uplet
où n est cela
le nombre
colonnes.
⟨Star rWars,
Si
cela vous
paraît cryptique,
en français,
se lit :Ililde
unt ettitre
t etexemple,
un réalisateur
existe
un titre
un Par
33 relations
e existe
/"&#:0$$9"&#&0$'#06?)$2&9"&#"$
e
Des mathématiciens
développé
la fin
XIX
siècler avant
(bien
avant
l’invention
de
réalisateur
tq.
le n-uplet
Film(t,
Des mathématiciens
avaientavaient
développé
à la finà du
XIXdu
siècle
(bien
l’invention
de
tels22:15⟩est
que
le
n-uplet
⟨
t,
r,
«
Humphrey
Bogart
»
⟩
se
trouve
dans
la
relation
Film,
et lenoms,
Sel,
un
3-uplet,
un
triplet,
dans
la
relation
Séance.
Les
colonnes
ont
r,
«Humphrey
Bogart»)
se
trouve
l’informatique
des de
bases
de données)
la logique
du premier
ordre,formaliser
pour formaliser
le des
l’informatique
et des et
bases
données)
la logique
du premier
ordre,
le
dans la
relationpour
Film et le n-uplet
n-uplet
⟨
t,
s,
h
⟩
dans
Séance.
Observez
que
s
et
h
ne
sont
pas
quantifiées
dans
la
formule
langage
des comme
mathématiques.
a eu d’adapter
l’idée d’adapter
cette
logique
pour définir
un
Séance(t,
s, h) dans
Séance.définir
appelés
attributs,
Titre.
langage
des 4+H.%+/C:U:V:;,0%#-,"#-#)(("+9&#3#'&-(.#/!#4
mathématiques.
Codd aCodd
eu l’idée
cette
logique
pour
un
Donc, donnez-moi les salles s et
précédente
; sont
nous
dirons
que
ces
variables
sontles
libres.
Laleformule
qHB peut être Le
vuecalcul
modèle
de gestion
de données,
modèle
relationnel.
modèle
de gestion
de
données,
le modèle
relationnel.
heures h (variables
libres), s’il
Les
données
interrogées
enledeux
utilisant
comme
langage
calcul
relationnel.
32
existe
un réalisateur
r et un titreles salles s et les
comme
une
requête
du
calcul
relationnel.
Elle
se
lit
alors
:
donnez-moi
Figure
1.base
Une
base
deinspiré
données
relationnelle
que ...
Figure
1. Une
de
données
relationnelle
relationnel
(très
fortement
de
la logique dut telpremier
ordre) s’appuie sur des noms
Film
horaires
s’il existe un réalisateur r Séance
et un Séance
titre t tels que... En d’autres termes, « Où et à
Film h,
Titre
qui représentent
desRéalisateur
relations
comme
Film
ou
Séance,
des
entrées
de ces relations comme
Titre
Salle
Heure
Titre
Réalisateur
Acteur Acteur
Titre
Salle
Heure
quelle
heure
puis-je
voir
un
filmBogart
avec
Humphrey
Bogart
? ». Ce langage, le calcul
Casablanca
Humphrey
Casablanca
19:00
Casablanca
M. Curtiz M. Curtiz
Humphrey
Bogart
Casablanca
LucernaireLucernaire
19:00
« StarCasablanca
WarsCasablanca
», des
variables
comme
t, h, et
des
logiques, ⋀ (et), ⋁ (ou), ¬ (non),
M. Curtiz
Peter
Lorre Casablanca
Casablanca
Studio
20:00
M. Curtiz
Peter Lorre
Studio symboles
20:00
relationnel,
d’exprimer
des questions
dans
une
syntaxe
Lespermet
400 F.
coups
Jean-Pierre
Sel 20:30
20:30 qui évite les ambiguïtés de
Les 400 coups
TruffautF. Truffaut
Jean-Pierre
Léaud Léaud
Star WarsStar Wars
Sel
⇒ nos
(implique),
∃
(existe),
∀
(pour
tout).
Avec
tout
ça,
des
formules
logiques
peuvent
Wars
Harrison
Ford Stars Wars
StarsSel
WarslesSel
22:15
Star WarsStar naturelles.
G. Lucas G. Lucas
Harrison
Ford
22:15
langues
Si elles
pouvaient
aimer,
machines
aimeraient
la simplicité,
la être
Dans
le
modèle
relationnel,
les
données
sont
organisées
en
tableaux
à
deux
dimensions
31
Dans le modèle
relationnel,
les données
sont organisées
tableaux le
à deux
dimensions
comme
: relationnel.
31 construites
précision
du
calcul
En pratique,
ellesenutilisent
langage
SQL (Structured
queappellerons
nous appellerons
des relations.
À la différence
des mathématiciens,
nous supposons
que nous
des relations.
À la différence
des mathématiciens,
nous supposons
Query
Language)
qui exprime
différemment
mêmes s,
questions.
Par exemple, la
qHB
∃ t,de!d taille
( Film(t,
« Humphrey
Bogart
»)les
⋀nous
Séance(t,
h)une
) debase
les=relations
de finie.
tailler,Comme
finie.
Comme
illustration,
utiliserons
de données
les relations
illustration,
nous utiliserons
une base
données
question
précédente
s’exprime
en
SQL
comme
:
consistant
une relation
Film
une relation
Une de
ligne
consistant
en uneenrelation
Film et
uneetrelation
SéanceSéance
(figure (figure
1). Une1).ligne
ces de ces
BD et calcul relationnel
!0,,"$'#1)+1*+"6#+)#69(0$&"#I
>.*KH)%:)%.+'"##%..%###J!0::@#KLMNO
C6)$&E06,"#+)#6"-*8'"#"$#"5(6"&&20$#)+?9P62-*"#1)+1*+)P+"
Q"F'#$0,P6"#:%$8"*-2$#304)09-3)
!"#$%&'(
Si cela vous paraît cryptique, en français, cela se lit : il existe un titre t et un réalisateur r
)'"*(+,"$
relations
est heure
appelée
un n-uplet
oùlennombre
est le nombre
de colonnes.
Par exemple,
⟨Star Wars,
relations
est appelée
un n-uplet
où n est
de colonnes.
Par exemple,
QLlaWars,
salle,
yS⟨Star
tels queselect
le
n-uplet
⟨
t,
r,
«
Humphrey
Bogart
»
⟩
se
trouve
dans
relation
Film,
et
le
M
Sel, 22:15⟩est
un 3-uplet,
un triplet,
la relation
Les colonnes
des noms,
-./(+,"$
Sel, 22:15⟩est
un 3-uplet,
un triplet,
dans ladans
relation
Séance.Séance.
Les colonnes
ont desont
noms,
from
Film,
Séance
n-uplet
⟨
t,
s,
h
⟩
dans
Séance.
Observez
que
s
et
h
ne
sont
pas
quantifiées
dans
la
formule
appelés
attributs,
comme
Titre.
appelés
attributs,
comme
Titre.
where Film.titre= Séance.titre and acteur= « Humphrey Bogart »
666
Les données
sont
interrogées
utilisant
comme
le relationnel.
calculLa
relationnel.
Le
calcul
32
Les données
sont interrogées
en utilisant
comme
langagelangage
le calcul
Le calcul
32 précédente
; nous
dirons
que
cesendeux
variables
sont
libres.
formule
qHB
peut être vue
C’est
presque
compréhensible,
non
?deEtla qu’Alice
s’exprime
en français
ou
qu’elle
34
relationnel
(trèsdu
fortement
logique
ordre)
s’appuie
sur
des
nomsutilise
relationnel
fortement
inspiréinspiré
de
la logique
du
premier
ordre)
s’appuie
sur des
noms
Informatique
- Bases
dedu
données
• ! A.
Cornuéjols
Informatique - Bases de données • ! A. Cornuéjols 2014
69 /119
comme
une (très
requête
calcul
relationnel.
Elle
sepremier
lit
alors
: 2014
donnez-moi
11 les salles s et les
une
interface
graphique,
le système
transforme
sa
ences
requête
SQL
. comme
iences des données
: de
la logique
premier
ordre
à ou
laFilm
Toil...
http://books.openedition.org/cdf/529?lang=fr
qui
représentent
des
relations
comme
ou Séance,
des entrées
de ces relations
qui
représentent
desdu
relations
comme
Film
Séance,
desquestion
entrées
de
relations
comme
horaires
h,
s’ilWars
existe
un relationnel
réalisateur
rt,des
eth, un
titre(ou
t tels
que...
En
d’autres
termes,
« Où et à
«Wars
Star »,
des variables
et des symboles
⋀
¬ce
(non),
La
question
du
calcul
précédente
enlogiques,
SQL)
bien
qu’Alice
« Star
des »,
variables
commecomme
t, h, et
symboles
logiques,
⋀ (et), précise
⋁ (et),
(ou),⋁¬(ou),
(non),
35
quelle
heure
puis-je
voir
un
film
avec
Humphrey
Bogart
?
».
Ce
langage,
⇒ (implique),
∃ (existe),
∀ tout).
(pour
tout).
Avec
ça,
des formules
être leuncalcul
⇒ (implique),
∃ (existe),
∀ (pour
tout
ça,tout
des
formules
logiques
peuventpeuvent
êtreréponse,
demande.
Cette
question
a un
sensAvec
précis,
une
sémantique.
Elle logiques
définit
une
construites
comme
:Nous nedes
construites
comme
: d’exprimer
relationnel,
permet
questions
dans
une syntaxe
lesCe
ambiguïtés
ensemble
n-uplets.
préciserons
pas
comment
dans qui
cetteévite
leçon.
la de
sélection
etde
larelationnel
projection,
que nous
composerons
dans l’expression
suivante
deque
l’algèbre
BD
et calcul
BD et calcul relationnel
q
=
∃
t,
d
(
Film(t,
r,
«
Humphrey
Bogart
»)
⋀
Séance(t,
s,
h)
)
q
=
∃
t,
d
(
Film(t,
r,
«
Humphrey
Bogart
»)
⋀
Séance(t,
s,
h)
)
nos
langues
naturelles.
Si
elles
pouvaient
aimer,
les
machines
aimeraient
la
simplicité,
la
HB
HB
question ne :dit pas, c’est comment calculer la réponse. Pour le « comment », on utilise
relationnelle
Si celaSivous
paraît
cryptique,
en français,
cela
secela
lit
: se
il existe
titreun
t et
un
cela
vous
paraît
cryptique,
enpar
français,
lit : il un
existe
titre
tréalisateur
et un réalisateur
r
33 précision
33
l’algèbre
relationnelle
introduite
Codd.
Une
étape
importante
à rSQL
transformer
du
calcul
relationnel.
En
pratique,
elles
utilisent
le consiste
langage
(Structured
E
= que
!
(! ⟨« t,("
⋈
Salle)
HB
salle,heure
acteur
= « Humphrey
»(Film))
tels question
que
n-uplet
⟨ t,
Humphrey
BogartBogart
»algébrique
⟩ se
dans
ladans
relation
Film, etFilm,
lela réponse
telsle
le n-uplet
r,
« expression
Humphrey
Bogart
»trouve
⟩ les
se qui
trouve
laderelation
le
une
du
calcul
en
une
permet
calculer
à
Query
Language)
quir,titre
exprime
différemment
mêmes
questions.
Paret exemple,
la
n-upletn-uplet
⟨ t, s, h⟨ ⟩t,dans
Observez
que s etque
h ne
sont
pas
quantifiées
dans ladans
formule
s, h ⟩Séance.
dans Séance.
Observez
s et
h ne
sont
pas quantifiées
la formule
!0,(+"52'9#:*#$+.$/.:;%:.+:)<&"#-%
cette
question.
Figure
2.
L’évaluation
d’une
requête
algébrique
question
précédente
s’exprime
en
SQL
comme
:sont libres.
précédente
; nous ;dirons
que cesque
deux
sont libres.
La formule
q peut
vueêtre vue
précédente
nous dirons
cesvariables
deux variables
La formule
q être
peut
33
36
HB
L’algèbre relationnelle consiste en un petit nombre d’opérations HB
de base
qui, appliquées à
comme
une salle,
requête
du calcul
relationnel.
Elle seElle
lit alors
donnez-moi
les salles
et less et les
comme
une heure
requête
du calcul
relationnel.
se lit: alors
: donnez-moi
lesssalles
select
des
relations,
produisent
deréalisateur
nouvelles
relations.
opérations
peuvent
composées
horaires
h, Film,
s’il existe
un réalisateur
r et unrtitre
d’autres
termes,
« Oùêtre
et«àOù
horaires
h, s’il
existe
un
et
unt tels
titreque...
t Ces
telsEn
que...
En d’autres
termes,
et à
from
Séance
pour
construire
despuis-je
expressions
algébriques
de Bogart
plus en
à
quelle
heure heure
puis-je
voir
un
film
? plus
». Ce
langage,
le Pour
calcul
quelle
voir
unavec
film Humphrey
avec Humphrey
Bogart
?complexes.
».
Ce langage,
le répondre
calcul
where Film.titre= Séance.titre and acteur= « Humphrey Bogart »
permet
d’exprimer
des
questions
dans
une
syntaxe
qui
évite
les
ambiguïtés
larelationnel,
question
qui permet
nous
sert
d’exemple,
il nous
faudra
trois
opérations,
ladejointure,
la
relationnel,
d’exprimer
des questions
dans
une syntaxe
qui
évite
les ambiguïtés
de
nospresque
langues
naturelles.
Si ellesSipouvaient
aimer,
les machines
aimeraient
la simplicité,
laou qu’elle
nos langues
naturelles.
ellesnon
pouvaient
les machines
aimeraient
la simplicité,
la
C’est
compréhensible,
? Et aimer,
qu’Alice
s’exprime
en
français
utilise
du calcul
relationnel.
En pratique,
elles utilisent
le langage
SQL (Structured
précision
du calcul
relationnel.
Entransforme
pratique,
elles
le langage
SQL (Structured
uneprécision
interface
graphique,
le système
sautilisent
question
en
requête
SQL11.
Query Query
Language)
qui exprime
différemment
les mêmes
questions.
Par exemple,
la
Language)
qui exprime
différemment
les mêmes
questions.
Par exemple,
la
6 sur 26 35
La question
question
du calcul
relationnel
précédente
(ou en SQL) précise bien ce qu’Alice
précédente
s’exprime
en SQLen
comme
:
question
précédente
s’exprime
SQL comme
:
demande.
Cette
question a un sens précis, une sémantique. Elle définit une réponse, un
select select
salle, heure
salle, heure
from
Séance
ensemble
deFilm,
n-uplets.
Nous ne préciserons pas comment dans cette leçon. Ce que la
from
Film, Séance
Film.titre=
Séance.titre
and acteur=
« Humphrey
Bogart »Bogart »
where
Film.titre=
Séance.titre
and
acteur=
«laHumphrey
questionwhere
ne dit
pas,
c’est
comment
calculer
réponse.
Pour le « comment », on utilise
C’est presque
compréhensible,
non ? non
Et qu’Alice
s’exprime
en français
ou qu’elle
utilise utilise
34 l’algèbre
C’est
presque
compréhensible,
?
Et
qu’Alice
s’exprime
en
français
ouconsiste
qu’elle
34
relationnelle introduite par Codd. Une étape importante
à transformer
une interface
graphique,
le système
transforme
sa question
en requête
SQL11. SQL11.
une interface
graphique,
le système
transforme
sa question
en requête
une
question
du
calcul
en
une
expression
algébrique
qui
permet
de
calculer
la réponse à
La question
du calcul
relationnel
précédente
(ou en(ou
SQL)
bien cebien
qu’Alice
35
La question
du calcul
relationnel
précédente
en précise
SQL) précise
ce qu’Alice
35
cette
question.
demande.
Cette question
a un sens
une sémantique.
Elle définit
une réponse,
un
demande.
Cette question
a unprécis,
sens précis,
une sémantique.
Elle définit
une réponse,
un
Informatique - Bases de données • ! A. Cornuéjols 2014
71 /119
ensemble
de
n-uplets.
Nous
ne
préciserons
pas
comment
dans
cette
leçon.
Ce
que
la
L’algèbre
relationnelle
consiste
en
un
petit
nombre
d’opérations
de
base
qui,
à
ensemble
de
n-uplets.
Nous
ne
préciserons
pas
comment
dans
cette
leçon.
Ce
que appliquées
la
36
question
ne dit ne
pas,
c’est
comment
calculer
la réponse.
Pour lePour
« comment
», on utilise
question
dit
pas,
c’est
comment
calculer
la
réponse.
le
«
comment
»,
on
utilise
des relations, produisent de nouvelles relations. Ces opérations peuvent être composées
l’algèbre
relationnelle
introduite
par Codd.
étape
consiste
à transformer
l’algèbre
relationnelle
introduite
par Une
Codd.
Uneimportante
étape importante
consiste
à transformer
pour
algébriques
endeplus
complexes.
uneconstruire
question du des
calculexpressions
en une expression
algébrique de
qui plus
permet
calculer
la réponse àPour répondre à
70 /119
R&'71"#-*"#1"#?"$6"#:"#3#1)+1*+#4#("*'#()&&"6#S#+%91A"++"#I
34
1)"*)+00%-#:%0(F,2&)F0$#:"#6"-*8'"&
1/12/14 16:57
!+)&&"&#:"#$"0&.%C,4<#:"&#1)+1*+&
Informatique - Bases de données • ! A. Cornuéjols 2014
72 /119
3. Notions de base
3. Notions de base
Base de données = ensemble de tables
Tables, lignes et colonnes
• Chaque table a un nom unique (ex : Film)
schéma
• Chaque table contient les données relatives à des entités de même nature.
ligne
• Chaque ligne (enregistrement) d’une table décrit les données relatives à une entité.
• Chaque colonne d’une table décrit une propriété des entités (ex : Prix).
données
• Les lignes d’une table sont distinctes.
• Les noms de table et de colonnes constituent le schéma de la base.
• Les lignes (entités) constituent le contenu de la base.
colonne
obligatoire
Informatique - Bases de données • ! A. Cornuéjols 2014
73 /119
colonne
facultative
Informatique - Bases de données • ! A. Cornuéjols 2014
74 /119
3. Notions de base - Exemple
3. Notions de base
Un schéma
Le schéma d'une table définit sa structure. Il spécifie notamment :
1. le nom de la table,
2. pour chaque colonne, son nom, son type, son caractère obligatoire,
3. l'identifiant primaire (liste de colonnes)
4. les identifiants secondaires éventuels (liste de colonnes)
5. les clés étrangères éventuelles (liste de colonnes et table cible).
Le contenu d'une table est formé d'un ensemble de lignes conformes au
schéma.
Le contenu d'une table est sujet à de fréquentes modifications.
Le schéma d'une table peut évoluer mais moins fréquemment.
Informatique - Bases de données • ! A. Cornuéjols 2014
75 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
76 /119
3. Notions de base
3. Notions de base - Exemple
Les données
identifiant
(clé primaire)
clé étrangère
dans le schéma
dans les données
Informatique - Bases de données • ! A. Cornuéjols 2014
Partie 1 - Place de l’informatique
Partie 2 - Les bases de données
Partie 3 - Bases sur les BD
Partie 4 - Conception des BD
Partie 5 - Manipulation : SQL
Partie 6 - PHP / MySQL
3.1 Introduction
3.2 Modèle Entité-Association
3.3 Types d'entités
3.4 Attributs
77 /119
3.5 Types d'associations
3.6 Exemples de schéma conceptuel
3.7 Exemple détaillé
Informatique - Bases de données • ! A. Cornuéjols 2014
78 /119
4.1 Pourquoi est-ce difficile de concevoir une BD
Comment construire une base de données ?
• définir les tables, les contraintes, les index, etc.
• introduire les données dans ces tables (ceci est un autre problème).
4 - Conception d’une base de données
Encore faut-il que le schéma des tables satisfasse les besoins des utilisateurs des
données !
L'esprit humain n'est pas conçu pour réfléchir en termes de tables !
Informatique - Bases de données • ! A. Cornuéjols 2014
79 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
80 /119
4.1 Pourquoi est-ce difficile de concevoir une BD
4.2 Le modèle Entité-Association (E/A)
Il faut un autre langage pour exprimer naturellement les informations que doit
contenir une base de données :
le modèle Entité-association
Le monde est perçu comme formé d'entités, dotées de propriétés et en
associations les unes avec les autres.
=> Modélisation
Gestion des étudiants dans
un établissement supérieur.
Informatique - Bases de données • ! A. Cornuéjols 2014
81 /119
4.2 Le modèle Entité-Association (E/A)
Informatique - Bases de données • ! A. Cornuéjols 2014
82 /119
4.5 Types d'associations
CLIENT
CLIENT
Le modèle Entité-association propose une lecture du monde (le domaine
NumClient
Nom
d'application) simple mais limitée :
appartient
Adresse
signe
• le monde est constitué d'objets ou entités
CONTRAT
VEHICULE
Marque
Modèle
couvre
Type
DateSign
Année
• les entités d'un type ont des attributs spécifiques
Cylindrée
VEHICULE
NumCtr
NumVéh
• les entités sont classées en types d'entités
ACCIDENT
• les entités sont en association les unes avec les autres
implique
CONTRAT
NumAcc
DateAcc
ACCIDENT
Montant[0-1]
• les associations sont classées en types d'associations.
dessin des types
Informatique - Bases de données • ! A. Cornuéjols 2014
83 /119
quelques instances
Informatique - Bases de données • ! A. Cornuéjols 2014
84 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
4.5 Types d'associations
Cahier des charges (simplifié)
Informatique - Bases de données • ! A. Cornuéjols 2014
•
Des agriculteurs possèdent des parcelles
•
Une parcelle a un et un seul propriétaire
•
Un agriculteur possède une ou plusieurs parcelles
•
Pour exploiter ces parcelles, les agriculteurs font appel à un GIE. Celui-ci
fournit de la main d’oeuvre d’appoint, à la journée.
•
Cette main d’oeuvre est assurée par des employés du GIE.
•
Chaque employé du GIE a un tarif, qui constitue son salaire journalier brut.
85 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
Informatique - Bases de données • ! A. Cornuéjols 2014
86 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
Cahier des charges (simplifié)
Cahier des charges (simplifié) (2)
•
Des agriculteurs possèdent des parcelles
•
Une parcelle a un et un seul propriétaire
•
Un agriculteur possède une ou plusieurs parcelles
•
Pour exploiter ces parcelles, les agriculteurs font appel à un GIE. Celui-ci
fournit de la main d’oeuvre d’appoint, à la journée.
•
Cette main d’oeuvre est assurée par des employés du GIE.
•
Chaque employé du GIE a un tarif, qui constitue son salaire journalier
brut.
Informatique - Bases de données • ! A. Cornuéjols 2014
• Le GIE paie ses employés mensuellement, en fonction de leurs
interventions.
• Chaque intervention concerne une parcelle, un employé et un nombre
de jours.
• Le système d’information désiré doit pouvoir fournir :
87 /119
•
la liste des agriculteurs
•
la liste des employés
•
la liste des interventions par employé
•
la liste des interventions par agriculteur
Informatique - Bases de données • ! A. Cornuéjols 2014
88 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
Inventaire des données (simplifié)
Nom
• Agriculteur : Nom, prénom, lieu de
résidence
• Employé GIE : Nom et prénom
•
Prénom
Anne-Marie
Arith
Martoz
Christian
Montargy
Carrez
François
Arith
Ferrer
Mariette
Lenoyer
Mernaz
Francine
propriétaire
Martoz
Christian
Clé primaire
Lieu de
résidence
Dulhac
Parcelle : nom, superficie, lieu et
4.7 Exemple - Conception d’une BD pour un GIE agricole
IdAgri
Lieu de
résidence
Dulhac
Anne-Marie
Arith
Lescheraines
2
Martoz
Christian
Montargy
Lescheraines
3
Carrez
François
Arith
4
Ferrer
Mariette
Lenoyer
5
Mernaz
Francine
Lescheraines
6
Martoz
Christian
Lescheraines
Comment identifier ?
• Interventions : employé, parcelle,
Prénom
1
• Employé : numéro insee et salaire
journalier
Nom
date de début, nombre de jours
Informatique - Bases de données • ! A. Cornuéjols 2014
89 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
Ville_Arr
Distance
Aiglun
Saint-Auban
25
Aix-les-Bains
Chambery
14
Type des attributs (colonnes)
Agr-Id
Champ
Agr_id
Rennes
Rennes
Saint-Alban
Paris
Aiglun
Type
int(11)
75
23
Agr_Nom
Agr_Prn
Agr_Resid
Extra
auto_increment
1
Dulhac
Anne-Marie
Arith
2
Martoz
Christian
Montargy
François
Arith
Agr_Nom
varchar(30)
3
Carrez
Agr_Prn
varchar(20)
4
Ferrer
Mariette
Lenoyer
Agr_Resid varchar(50)
5
Mernaz
Francine
Lescheraines
6
Martoz
Christian
Lescheraines
342
Saint-Malô
90 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
Clé primaire : pas nécessairement une seule colonne
Ville_Dep
Informatique - Bases de données • ! A. Cornuéjols 2014
Table des distances entre villes
Informatique - Bases de données • ! A. Cornuéjols 2014
91 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
92 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
4.7 Exemple - Conception d’une BD pour un GIE agricole
Les parcelles et leur propriétaire
Les parcelles
Par_Idf
Par_Nom
Par_Lieu
1
Le Pré au Vent
Arith
Par_Superficie Par_Prop
350
1
Par_Idf
Par_Nom
Par_Lieu
Par_Superficie
2
Le grand Verger
Arith
300
2
1
Le Pré au Vent
Arith
350
3
Montargy
220
1
2
Le grand Verger
Arith
300
4
Plan des
Bauges
Les Prés Rus
Arith
750
4
5
Lafosse
Montargy
600
1
3
Plan des Bauges
Montargy
220
4
Les Prés Rus
Arith
750
5
Lafosse
Montargy
600
Parcelles
Une relation entre
deux tables
Comment indiquer le propriétaire ?
Informatique - Bases de données • ! A. Cornuéjols 2014
1
Par_Nom
Par_Lieu
Une relation 1 à n
entre deux tables
2
Martoz
Christian
Arith
3
Carrez
François
Arith
4
Ferrer
Mariette
Lenoyer
5
Mernaz
Francine
Lescheraines
6
Martoz
Christian
Lescheraines
Montargy
Informatique - Bases de données • ! A. Cornuéjols 2014
94 /119
Emp-nss
Emp_Nom
Emp_prn
Emp_tarif
Par_id
Agr_Prn
Agriculteur
Agr_Resid
Anne-Marie
La table employé
Parcelle
Agr_Nom
Agr_Resid
Agr_Prn
Dulhac
4.7 Exemple - Conception d’une BD pour un GIE agricole
Les parcelles et leur propriétaire
Agr_id
Agr_Nom
1
93 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
Agriculteur
Agr_Id
Agriculteur
n
Clé étrangère
1,75077E+12
Grandet
Marc
110
1,82023E+12
Barnier
Nicole
115
1,79011E+12
Pernet
Henri
119
Par_Sup
Par_Prop
Quelle clé primaire ?
Parcelles
Informatique - Bases de données • ! A. Cornuéjols 2014
95 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
96 /119
4.7 Exemple - Conception d’une BD pour un GIE agricole
4.7 Exemple - Conception d’une BD pour un GIE agricole
Les parcelles et les employés
Quelle relation entre les tables parcelle et employé ?
Parcelle
Employé
Intervention
Emp_nss
Int_Emp_nss
Emp_Nom
• Un employé peut être amené à travailler sur plusieurs parcelles
• Sur une même parcelle peuvent travailler plusieurs employés
(simultanément ou l’un après l’autre)
n
1
Int_Deb
Emp_tarif
Int_Nb_Jours
nouvelle table
Emp_nss
1,75077E+12
Emp_Nom
Grandet
Emp_prn
Marc
Employé
Partie 1 - Place de l’informatique
Partie 2 - Les bases de données
Partie 3 - Bases sur les BD
Partie 4 - Conception des BD
Partie 5 - Manipulation : SQL
Partie 6 - PHP / MySQL
4.1 Introduction : LDD et LMD
4.2 Création / modification de table
4.3 Extractions simples
4.4 Extractions complexes
97 /119
4.5 Les fonctions agrégatives
4.6 Les sous-requêtes
Par_id
Par_Nom
Par_Lieu
n
Emp_Prn
L’employé Grandet a
travaillé sur la parcelle du
pré au vent, 5 jours à partir
du 5 juillet 2004.
• C’est une relation plusieurs à plusieurs : on la représente par une
Informatique - Bases de données • ! A. Cornuéjols 2014
Int_Par_id
1
Par_Sup
Par_Prop
Intervention
Int_Emp_nss
Int_Par_i
d
Int_Deb
Int_Nb_Jours
1,75077E+12
1
2004-07-05
5
Emp_tarif
110
Parcelle
Par_Idf
Par_Nom
1
Le Pré au
Vent
Par_Lieu Par_Superficie Par_Prop
Arith
350
Informatique - Bases de données • ! A. Cornuéjols 2014
1
98 /119
5.1 Introduction à SQL : LDD et LMD
SQL n’est pas un langage de programmation complet
SQL permet :
5 - LE LANGAGE SQL
(Structured Query Language)
de définir le schéma de la base de données (LDD)
de charger les tables relationnelles (LMD)
de manipuler les données stockées (LMD)
de gérer la base de données (LDD) : sécurité, organisation
physique
Informatique - Bases de données • ! A. Cornuéjols 2014
99 /119
Ici
: aperçu de la partie LMD
En TP
: le LDD aussi
Informatique - Bases de données • ! A. Cornuéjols 2014
100 /119
5.2 Extraction simple - projection
Personnes
Introduction
nom
prénom
adresse
Martin
Pierre
7 allée des vers 258941236
téléphone
Le sous-langage LMD de SQL permet de consulter le contenu des
Dupond
Jean
32 allé Poivrot
526389152
tables et de les modifier. Il comporte 4 verbes :
Dupond
Marc
8 rue de l’octet
123456789
• La requête select extrait des données des tables
On projette la table
Personnes sur les
colonnes nom et prénom.
• La requête insert insère de nouvelles lignes dans une table
SELECT nom, prénom
FROM Personnes
• La requête delete supprime des lignes d'une table
• La requête update modifie les valeurs de colonnes de lignes
existantes
Informatique - Bases de données • ! A. Cornuéjols 2014
101 /119
prénom
Martin
Pierre
Dupond
Jean
Dupond
Marc
Informatique - Bases de données • ! A. Cornuéjols 2014
102 /119
5.2 Extraction simple - sélection
5.2 Extraction simple - sélection
Personnes
nom
prénom
adresse
Martin
Pierre
7 allée des vers 258941236
Dupond
Jean
32 allé Poivrot
526389152
Dupond
Marc
8 rue de l’octet
123456789
Relation de départ :
téléphone
SELECT *
FROM Personnes
WHERE nom = ‘’Dupond’’
nom
SELECT * FROM Gens
1
Gens
On ne sélectionne que les
tuples dont l’attribut nom
est égale à ‘Dupond’.
Nom
Prenom
Age
Dupond
Pierre
24
Martin
Marc
48
Dupont
Jean
51
Martin
Paul
36
Dupond
Lionel
68
Chirac
Jacques
70
SELECT Nom
FROM Gens
Gens
Nom
Dupond
Martin
Dupond
prénom
adresse
téléphone
Dupond
Jean
32 allé Poivrot
526389152
Dupond
Marc
8 rue de l’octet
123456789
Informatique - Bases de données • ! A. Cornuéjols 2014
SELECT DISTINCT Nom
FROM Gens
Martin
Dupont
nom
2
Chirac
3
Gens
Nom
Dupond
Martin
Dupont
Chirac
103 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
104 /119
5.2 Extraction simple - sélection
5.3 Extraction complexe - jointure
Bibliothèque
Personnes
Gens
Nom
Chirac
SELECT DISTINCT Nom
FROM Gens
ORDER BY Nom ASC
4
Dupond
Dupont
5
Martin
Gens
Nom
Chirac
Dupond
6
Gens
Nom
Dupond
SELECT DISTINCT Nom
FROM Gens
ORDER BY Nom ASC
LIMIT 2
Dupond
Robinson
32 allé Poivrot
Jospin
Faust
Martin
Misère
adresse
Martin
Pierre
Dupond
Jean
téléphone
526389152
SELECT Personnes.prénom, dernierlivre
FROM Personnes, Bibliothèque
WHERE Personnes.nom = Bibliothèque.nom
105 /119
5.3 Conditions plus complexes - exemples
prénom
Dernierlivre
Jean
Robinson
Pierre
Misère
Et on combine cette jointure à
une projection sur les attributs
nom et dernierlivre.
Attention à lever toute ambiguïté sur les noms d’attribut
dans le cas où deux tables
possèdent des colonnes de
même nom.
Informatique - Bases de données • ! A. Cornuéjols 2014
106 /119
5.3 Conditions plus complexes - Combinaisons logiques
select nom
from
produit
where prix <= 100.5;
Liste des noms de produits dont le
prix est inférieur ou égal à 100.5 €
select nom, prénom
from
ELEVES
where age between 12 and 16;
Liste des noms et prénoms des
élèves dont l’âge est dans [12,16]
select modèle
from
VOITURES
where couleur in (‘bleu’, ‘blanc’, ‘noir’);
Liste des modèles de voitures dont la
couleur est dans la liste : bleu, blanc,
noir.
select modèle
from
VOITURES
where couleur not in (‘rose’, ‘violet’);
Liste des modèles de voitures dont la
couleur n’est pas dans la liste : rose,
violet.
Informatique - Bases de données • ! A. Cornuéjols 2014
Dernierlivre
7 allée des vers 258941236
prénom
On joint les deux tables, grâce à
la colonne nom.
SELECT DISTINCT Nom
FROM Gens
WHERE Nom <> ‘Chirac’
ORDER BY Nom ASC
LIMIT 2
Informatique - Bases de données • ! A. Cornuéjols 2014
nom
nom
107 /119
select NOM, ADRESSE, COMPTE
from
CLIENT
where LOCALITE = 'Toulouse' and COMPTE < 0;
select NOM, ADRESSE, COMPTE
where COMPTE > 0
and (CAT = 'C1' or LOCALITE = 'Paris')
Informatique - Bases de données • ! A. Cornuéjols 2014
108 /119
5.4 Données extraites et données dérivées - expressions de calcul
5.4 Les sous-requêtes - Principe
select 'TVA de ', NPRO, ' = ',0.21*PRIX*QSTOCK
from
PRODUIT
where QSTOCK > 500;
TVA de
NPRO
=
TVA de
TVA de
TVA de
TVA de
CS264
PA45
PH222
PS222
=
=
=
=
select *
from
PRODUIT
where NPRO in
(select NPRO
from
DETAIL
where NCOM in
(select NCOM
from
COMMANDE
where NCLI in
(select NCLI
from
CLIENT
where LOCALITE='Namur')));
0,21*PRIX*QSTOCK
67788
12789
37770.6
47397
e
select NPRO as Produit, 0.21*PRIX*QSTOCK as Valeur_TVA
st
nn
" e colo
from
PRODUIT
uit
od s de
r
P
a
where QSTOCK > 500;
" ali
n
u
Produit
CS264
PA45
PH222
PS222
les clients de Namur
Valeur_TVA
67788
12789
37770.6
47397
les commandes des clients de Namur
les détails des commandes des clients de Namur
les produits référencés par les détails des commandes des clients de Namur
Informatique - Bases de données • ! A. Cornuéjols 2014
Partie 1 - Place de l’informatique
Partie 2 - Les bases de données
Partie 3 - Bases sur les BD
Partie 4 - Conception des BD
Partie 5 - Manipulation : SQL
Partie 6 - PHP / MySQL
109 /119
6.1 Introduction
6.2 Le langage MySQL
6.3 Administration avec phpMyAdmin
6.4 Interface avec PHP
Informatique - Bases de données • ! A. Cornuéjols 2014
110 /119
6. MySQL
MySQL est un Système de Gestion de Bases de Données
• Se charge du stockage, de l’accès, de la recherche, de la sécurité ... des données
• Utilise le langage SQL
6 - PHP / MySQL
• Système libre (disponible sous Mac OS X, Linux, Windows, ...)
• L’un des plus utilisé au monde (e.g. Wikipedia, Google, YouTube, ...)
• Multi-utilisateur
• Utilisable depuis une grande variété de langages de programmation (C, C++, Java, Python, PHP, ...)
• ...
Informatique - Bases de données • ! A. Cornuéjols 2014
111 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
112 /119
6. Administration avec phpMyAdmin
6. Administration avec phpMyAdmin
Interface intuitive pour l’administration des bases de données du serveur
•
phpMyAdmin est une application PHP développée pour administrer une
Outil pour :
base MySQL à distance via un navigateur web
• créer de nouvelles bases
• créer / modifier / supprimer des tables
•
Application intégrée à EasyPHP
•
phpMyAdmin permet également de visualiser les requêtes SQL
• afficher / ajouter / modifier / supprimer des tuples
dans des tables
• effectuer des sauvegarde de la structure et/ou des
données
• effectuer n’importe quelle requête
• gérer les privilèges des utilisateurs
Informatique - Bases de données • ! A. Cornuéjols 2014
113 /119
6. Conclusions
Informatique - Bases de données • ! A. Cornuéjols 2014
114 /119
Les défis des BDs aujourd’hui et demain
• Multiplicité des types de données.
Une base de données moderne peut contenir :
•
Base de données = ensemble de données structurées, persistantes placé
dans des fichiers
•
des données multimédias,
•
des données textuelles,
•
Gestion par SGBD (Système de Gestion de Bases de Données)
•
des données spatiales (données GPS par exemple),
•
Stockage ; accès ; recherche ; sécurité ...
•
des données historiques (plusieurs lignes par entité),
•
Exemples : MySQL, ORACLE, PostgreSQL, ...
•
des données semi-structurées.
•
Langage spécialisé : SQL
•
On choisit une application cliente permettant de dialoguer avec un serveur
MySQL : PHP ; phpMyAdmin ; ...
• Volumes et performances.
•
Une base de données peut contenir des dizaines de milliers de tables, des milliards
de lignes.
•
Comment garantir l'accessibilité de ces données, leur protection contre les
incidents, des temps d'accès satisfaisants ?
Informatique - Bases de données • ! A. Cornuéjols 2014
115 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
116 /119
Les défis des BDs aujourd’hui et demain
Les défis des BDs aujourd’hui et demain
• Maintenance et évolution.
•
•
• Les BD et le Web.
La structure d'une base de données peut évoluer : ajouter ou supprimer une table,
Le Web peut être vu comme une gigantesque base de données (largement incohérente
une colonne, une contrainte.
et redondante !)
Comment préserver les données et les programmes utilisateurs lors de cette
Qu'en est-il des principes rigoureux des bases de données dans ce contexte ?
évolution ?
• Les données décisionnelles.
•
Les données ne servent pas seulement à contrôler la gestion et le fonctionnement d'une
Les données distribuées et nomades.
•
entreprise au jour le jour. Elles sont aussi souvent utilisées pour soutenir des
Une base de données peut être répartie et/ou dupliquées sur plusieurs ordinateurs
décisions tactiques et stratégiques.
répartis géographiquement. (cf. le « cloud computing »)
•
Caractéristiques : traitement de très gros volumes de données complexes pour
Certains de ceux-ci peuvent être des appareils mobiles (embarqués, portables,
produire une réponse courte (une heure de traitement de plusieurs téraoctets pour en
smart phones).
•
extraire un seul nombre !)
Comment garantir la cohérence, la protection et l'accessibilité des données.
Comment structurer une base de données dans ce sens (= entrepôts de données) ?
Informatique - Bases de données • ! A. Cornuéjols 2014
117 /119
Remerciements
•
Ces transparents doivent beaucoup à :
•
Gérard Berry
•
Hugo Etiévant
•
Jean-Luc Hainaut (auteur d’un excellent livre sur les BDs)
dont les transparents sont extraordinairement complets et bien faits.
Merci !!
Informatique - Bases de données • ! A. Cornuéjols 2014
119 /119
Informatique - Bases de données • ! A. Cornuéjols 2014
118 /119