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