b. caron bases de données licence irx bernard caron 2008-2009
Post on 04-Apr-2015
120 Views
Preview:
TRANSCRIPT
B. Caron
Bases de données
Licence IRXBernard Caron
2008-2009
2 B. Caron
Présentation
9 heures de cours12 heures de TD et TP
1 contrôle de TP de 1h301 contrôle
Pré-requis : aucun
Contact : bernard.caron@univ-savoie.fr
3 B. Caron
déroulement
1 : intro2 : Dépendances fonctionnelles + TD3 : Entités Association +TD4 : Modélisation logique + TD5 : Langage de manipulation + TD6 : TP SQL7 : TP SQL8 : Exam TP
4 B. Caron
Plan du cours
IntroductionModélisation conceptuelleModélisation LogiqueDescription des relations en SQLPour aller plus loin
5 B. Caron
Bibliographie
• Pierre Crescenzohttp://www.crescenzo.nom.fr/CMBasesDeDonnees/
• Georges Gardarinhttp://perso.wanadoo.fr/georges.gardarin/
• Sébastien Choplinhttp://www.laria.u-picardie.fr/~choplin/enseignement/DB_IUP1.pdf
• MySQLhttp://dev.mysql.com/doc/mysql/fr/
6 B. Caron
Introduction
• Exemple• Limites des fichiers• BD SGBD• Propriétés• Les types d’utilisateurs• Architecture• Les différents types de SGBD• Historique
7 B. Caron
Introduction – exemples
• gestion d'entreprise (stocks, personnels, clients…) • banques (comptes, emprunts…) • systèmes de réservation (avions, trains,
spectacles…) • bibliothèques, vidéothèques, médiathèques… • bureautique (agendas, répertoires, photos,
courriers…) • géographie (cartes routières, cartes maritimes…) • informatique (programmes, documentations…)
8 B. Caron
Introduction – exemples
• Agence de presse gère ses reportages et les photos associées aux reportages ainsi que les auteurs des photos et des reportages
• Un reportage est composé de plusieurs images, d’un auteur et d’un texte ainsi que d’une date de parution. Une image qui peut appartenir à plusieurs reportages est composée de l’image elle-même, de son auteur et d’une date.
• Les auteurs des images et des reportages sont définis par leur nom et prénom.
• Plusieurs postes informatiques doivent avoir accès à ces informations, sous différents OS pour des usages différents
9 B. Caron
Introduction – Limites des fichiers
• Application sur chaque poste• Fichiers à partager entre les postes
auteurs
Exécutable 1 Exécutable 2 Exécutable 3
Images Reportages
10 B. Caron
Introduction – Limites des fichiers
• Problèmes à résoudre– Données
• Modifications concurrentes• Peu fiables et redondantes
– Maintenance logicielle et matérielle– Evolution
• Format propriétaire des fichiers• Format propriétaire de l’accès• Complexité de l’application
– Changement de l’application
11 B. Caron
Introduction – BD SGBD
• Base de donnée et Système de Gestion de Base de Données
Base de données
SGBD
Logiciel 1 Logiciel 2 Logiciel 3
Filtres d’accès
12 B. Caron
Introduction – BD SGBD
• Base de données : ensemble d’informations :– exhaustives, – non redondantes, – structurées, – persistantes
13 B. Caron
Introduction – BD SGBD
• SGBD, un logiciel :– décrire– modifier– interroger– administrer
les données d'une base de données. Son langage d’interrogation est normalisé
14 B. Caron
Introduction – BD SGBD
• Problèmes résolus– Données
• Modifications concurrentes : SGBD s’en charge• Peu fiables et redondantes : SGBD s’en charge
– Maintenance : matériel et logiciel SGBD peuvent être changés de manière transparente
– Evolution• Format propriétaire des fichiers : SGBD s’en charge• Format propriétaire de l’accès : SGBD s’en charge• Complexité de l’application : normalisation de l’accès
– Changement des applications : possible sans changer la bdd
Indépendance traitement et données
15 B. Caron
Introduction – BD SGBD
• Avantages BDD– Information n'est stockée qu'une seule fois– Une seule source d’information pour toutes les applications– Contrôle de l’accès par des vues différentes
• Avantages SGBD– Centralisation des données = intégrité des données– Contrôle centralisé de l'accès aux données = Sécurité– Traitements puissants = rapidité de développement– Traitements non prévus par les applications– Indépendance vis-à-vis de la structure physique et logique
des données = maintenance facilitée
16 B. Caron
Introduction – Propriétés
• La BD doit correspondre à la réalité qu'elle modélise : description des données
• Pas de redondance d'information dans la BD, physiquement à un seul endroit.
• Le SGBD doit être indépendant du stockage physique• LE SGBD doit posséder des fonctions de consultation
et de mise à jour.• Le SGBD doit pouvoir gérer plusieurs accès simultanés• Accès rapides (améliorations possibles indépendantes
de l’application)• Toutes les données ne sont pas accessibles à tous les
utilisateurs • Tolérance aux problèmes matériels, logiciels ou
humains.
17 B. Caron
Introduction – Types d’utilisateurs
• Utilisateurs inconscients • Utilisateurs conscients • Utilisateurs confirmés requêtes• Concepteurs• Développeurs d’application • Administrateurs de BD • Développeurs de SGBD
18 B. Caron
Introduction : différents niveaux
• Niveau externe :– Environnement de Programmation,
Interface conviviales : outils d’aides, de saisie et d’impression
• Niveau logique: – Structure des données,
consultation et mise à jour. Confidentialité et intégrité des données
• Niveau physique :– Mémoire, fichiers, concurrence
d’accès, reprises sur pannes, fiabilité
19 B. Caron
Introduction - Architecture
• Architecture clients/serveur pour les données (2 tiers)
Base de données
SGBD
Logiciel 1 Logiciel 2 Logiciel 3
Filtres d’accès
20 B. Caron
Introduction - Architecture
• Architecture clients/serveur données et Application (3 tiers)
Base de données
SGBD
Application 1
NavigateurWEB
NavigateurWEB
NavigateurWEB
Application 2
Application 3
Serveur d’applications
21 B. Caron
Introduction - Différents types de SGBD
• SGBD hiérarchique– Données sous forme d’arbre– Utilisation de pointeurs
Reportages
Auteur Texte Photo
Reportages_1 Texte_1 Photos
Auteur Image Date
Auteur Image
Auteur Image Date
Date
22 B. Caron
Introduction - Différents types de SGBD
• SGBD Réseau– Données sous forme de graphe quelconque– Utilisation de pointeurs– Evite la dissymétrie
Reportages_1 Texte_1 Photos
Auteur Image Date
Auteur Image Date
Rep
Rep
23 B. Caron
Introduction - Différents types de SGBD
• SGBD Hiérarchique– Dissymétrie (hiérarchique)– Application liée à l’implantation physique (pointeurs)– Pas de partage des données
• SGBD Réseau– Application liée à l’implantation physique (pointeurs)– Langage de manipulation complexe
24 B. Caron
Introduction - Différents types de SGBD
• SGBD relationnel– Tables (lignes colonnes)– Plus de pointeurs– Langage non procédural de manipulation
Reportage ( auteur, texte, photos)Photo (auteur,laphoto,date)
Reportage, Photo sont des relations
25 B. Caron
1961Premiers systèmes IDS (Integrated Data Storage) modèle réseau
1965 Fichiers et modèle hiérarchique IMS (Information Management
System). IMS devient IMS DB/DC (DataBase/DataCom) modèle réseau.
1970 Modèle relationnel
1974Méthode Merise Entité/Relation
1975SGBD relationnels expérimentaux : INGRES SGBD fichiers sur micro : dbase
1980 SGBD relationnels commerciaux : Oracle, DB2, MySQL, Postgres
1990 SGBD orientés objet expérimentaux
Introduction - Historique
26 B. Caron
• Nécessité d’utiliser des méthodes de conception de bases de données• Systématiques• Permettent la réutilisation
• Méthodes les plus possible indépendantes du SGBD• Méthodes permettant de décrire le monde réel
Introduction - Modélisation
27 B. Caron
Modélisation conceptuelle - les étapes
Monde réel
Modèle conceptuel
Modèle logique
Implantation
Etape 1 de modélisation conceptuelle•UML•Dépendances fonctionnelles puis Entité/Association
Ne dépend pas du SGBD
Etape 2 de modélisation logique•Relationnel•Objet
Dépend su SGBD
•Etapes de modélisation
Vue 1
Vue 2
28 B. Caron
Chap 1 : Modélisation conceptuelle
• Etapes de la modélisation• Dictionnaire des données• Dépendances fonctionnelles• Modèle Entité/Association• Passage DF vers EA
29 B. Caron
Modélisation conceptuelle – les étapes
Dictionnaire des données
Dépendances fonctionnelles
Modèle Entité/Associations
30 B. Caron
Mod. conceptuelle – dic. des données
Suppression des synonymesVérifier que 2 données différentes ont un nom différentEnlever les données inutilesEnlever les paramètresEnlever les données calculablesDonner le domaine des données restantes
On peut conserver des données calculables pour des raisons de temps de calcul, mais à faire lors de l’optimisation, à la fin.
Donnée :Information quelconque
31 B. Caron
Mod. conceptuelle – dic. des données
synonyme
inutileparamètre
calculable
Date naissance
Age *Nbre enfants *Nbre Filles
Nbre garçons
Nombre total d’enfants
Couleur des cheveux *Part fiscale enfant *Part fiscale adulte *
32 B. Caron
Mod. conceptuelle – dic. des données
domaine
Date naissance 2N-2N-4NNbre Filles 2NNbre garçons 2N
33 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles
• Dépendance fonctionnelle :Lien d’une donnée source vers une donnée cible.Tel que à une source correspond une seule cible
• Représentation graphique :
Source Cible
N° étudiant Nom étudiant
Nom de famille Livre emprunté
Livre emprunté N° étudiant
34 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles
• Représentation sous forme de matrice :
Sources ->
V Cibles V
Nom étudiant
N° étudiant
Age
Nom étudiant
* 1
N° étudiant
*
Age 1 *
35 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles• Règles 1 :
– Une ligne qui n’est pas une source ne doit avoir qu’un 1 (1 seul antécédent)
• Graphiquement :
N° étudiant N° INSEE
Nom étudiant
N° étudiant N° INSEE
Nom étudiant
36 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles• Règles 2 :
– Recherche des données isolées (pas de 1 dans une ligne de la matrice)– Combiner 2 sources
• Graphiquement :
Cours N° étudiant
Note
37 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles• Règles 3 :
– Suppression des dépendances implicites
• Graphiquement :
N° étudiant N° INSEE
Nom étudiant
38 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles• Règles 4 :
– Recherche des dépendances entre sources (avec ou sans données associées)– Ajouter une donnée
• Graphiquement :
Groupe
Nom étudiant
Etudiant
Nom Groupe
AppartenanceDate
39 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles
• Résumé de la démarche graphique:– Mettre un lien entre les sources et les cibles– Bien vérifier qu’à une source correspond une seule cible– Recherche des données isolées : 2 sources sont
nécessaires– Recherche des dépendances entre 2 sources– Suppression des dépendances implicites– Souligner les sources
40 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles• Exemple
– Une agence de presse gère ses reportages et les photos associées aux reportages ainsi que les auteurs des photos et des reportages
– Un reportage est composé de plusieurs images, d’un auteur et d’un texte ainsi que d’une date de parution. Une image qui peut appartenir à plusieurs reportages est composée de l’image elle-même, de son auteur et d’une date.
– Les auteurs des images et des reportages sont définis par leur nom et prénom.
Num_rep 10NNum_image 12NNum_auteur 5NTexte 10000CDate_rep 2N+2N+4NDate_image 2N+2N+4NImage 1000000CNom 20CPrénom 20C
41 B. Caron
Mod. Conceptuelle – Dep. fonctionnelles
Num_rep
Num_image
Num_auteur
NomPrénom
ImageDate_image
Date_Rep
Appartenance
42 B. Caron
Mod. Conceptuelle – Entité Association
• Entité :– Objets ou individus du monde réel– Plusieurs occurrences de l’objet– Nom du type de l’objet– Un ou des identifiants (soulignés)– Des propriétés
numero_enom
prénom
Etudiant
Les propriétés viennent du dictionnaireLes identifiants permettent de distinguer de manière unique les occurrences de l’objet= clé
43 B. Caron
Mod. Conceptuelle – Entité Association
Association :– Regroupement entre 2 ou plusieurs entités– On lui donne un nom
numero_enom
prénom
Etudiant
numero_lauteur
titre
Livre
emprunter
44 B. Caron
Mod. Conceptuelle – Entité Association
– On indique la cardinalité– On peut ajouter des attributs
numero_enom
prénom
Etudiant
numero_lauteur
titre
Livre
emprunter
(Emprunte)
0..n
(est emprunté par)
0..1Emprunter
date
45 B. Caron
Mod. Conceptuelle – Entité Association
– L’association de plusieurs entités est possible
numero_enom
prénom
Etudiant
numero_lauteur
titre
Livre
emprunternumero_b
lieu
Biliothèque
(Emprunte)
0..n
(est emprunté par)
0..1
(accueille des emprunteurs)
0..n
46 B. Caron
Mod. Conceptuelle – DF vers EA
Num_rep
Num_image
Num_auteur
NomPrénom
ImageDate_image
Date_Rep
Ajout
Une source élémentaire -> identité + cléUne source vers plusieurs cibles elles-mêmes sources -> identité plus clé des ciblesCible terminale -> attributDF entre identifiants -> Association
Auteur Reportage
Image
Num_auteurPrénom
Nom
Num_repDate_rep
Num_imageDate_image
Image
Créer
1..1
0..n
Rédiger
0..n 1..1
Ajout
0..n
0..n
47 B. Caron
Mod. Conceptuelle – DF vers EA
– Autres cas
N° cours N° étudiant
NoteN° Cours Etudiant
Num_auteurPrénom
Nom
N° étudiantDate_rep
NoterNote
1..n 1..n
48 B. Caron
Chap 2 : Modélisation logique
• Modèle relationnel• Passage E-A -> modèle relationnel• Normalisation
49 B. Caron
Modélisation logique - Modèle
• Modèle relationnel– Mod. Conceptuelle : Entités-Associations– Bdd relationnelles– Normalisé : SQL
• Modèle objet– Mod. Conceptuelle : UML– Bdd objet– Normalisé : SQL
50 B. Caron
Modélisation logique - Modèle
• Produit cartésien D1 X D2 X D3
– D1 D2 D3 : ensembles– les tuples <V1,V2,V3> tels que :
V1 D1 , V2 D2 , V3 D3
• Exemple :– D1={Eléphant,Souris} D2={Grand, Petit} D3 = {Mammifère,
Oiseau}– <Eléphant, Grand, Mammifère> <Souris, Petit, Mammifère>
• Graphiquement :
ElephantSouris
GrandPetit
MammifèreOiseau
51 B. Caron
Modélisation logique - Modèle
• Relation : table à 2 dimensions
Collection Animal Taille Genre
Eléphant Grand Mammifère
Souris Petit Mammifère
Attribut
Nom de la relation
52 B. Caron
Modélisation logique - Modèle
• Clé :– Un ou plusieurs attributs permettant
de distinguer chaque tuple (la connaissance de la clé permet d’identifier un tuple unique)
– Toute relation doit posséder une clé– Dans l’exemple, clé : Animal
53 B. Caron
Modélisation logique - Modèle
• Clé candidate :– Une clé candidate d'une relation est un
ensemble minimal des attributs de la relation dont les valeurs identifient à coup sûr une occurrence.
– La valeur d'une clé candidate est donc distincte pour toutes les occurrences.
– La notion de clé candidate est essentielle dans le modèle relationnel.
– Toute relation a au moins une clé candidate et peut en avoir plusieurs.
54 B. Caron
Modélisation logique - Modèle
• Clé primaire :– La notion de clé primaire est moins
importante que celle de clé candidate dans le modèle relationnel.
– La clé primaire peut être choisie arbitrairement mais le contexte aide souvent à déterminer laquelle des clés candidates doit être considérée comme clé primaire.
– Pour signaler la clé primaire, ses attributs sont soulignés.
55 B. Caron
Modélisation logique - Modèle
• Clé étrangère– Une clé étrangère d'une relation est
formée d'un ou plusieurs de ses attributs qui constituent une clé candidate dans une autre relation on met un # devant l’attribut.
56 B. Caron
Modélisation logique - Modèle
• Schéma d’une relationNom de la relationNom des attributsLes Clés
• Notation– Collection (Animal, Taille,
Classification)
Clé primaire : soulignée
Liste des attributs
Nom de la relation
57 B. Caron
Modélisation logique - Modèle
• Clé étrangère– Exemple :
Image(Num_image,Date_image,ImageReportage(Num_rep,Date_rep,Texte)Ajout(#Num_rep,#Num_image)
Clé étrangère
58 B. Caron
Modélisation logique - Passage E-A -> Mod. R
• Entité correspond Association• Relation correspond Association si aucune cardinalité maximale 1
Si clé : conservée, sinon on concatène les clé des entités associées
Auteur Reportage
Image
Num_auteurPrénom
Nom
Num_repDate_rep
Num_imageDate_image
Image
Créer
1..1
0..n
Rédiger
0..n 1..1
Ajout
0..n
0..n
Image(Num_image,Date_image,Image)Reportage (Num_rep,Date_rep)Auteur (Num_auteur, Prénom,Nom)Ajout (Num_rep,Num_image)
Clé formée des 2 attributs
59 B. Caron
Modélisation logique - Passage E-A -> Mod. R
Auteur Reportage
Image
Num_auteurPrénom
Nom
Num_repDate_rep
Num_imageDate_image
Image
Créer
1..1
0..n
Rédiger
0..n 1..1
Ajout
0..n
0..n
Image(Num_image,Date_image,Image, Num_auteur)Reportage (Num_rep,Date_rep,Num_auteur)Auteur (Num_auteur, Prénom,Nom)Ajout (Num_rep,Num_image)
• Relation correspond Association avec cardinalité maximale 1on ajoute à la relation (correspond à la card. 1) comme attribut la clé de la relation qui a une cardinalité >1
60 B. Caron
Modélisation logique - Normalisation
• Première forme normale (1NF):– Un attribut d’une relation ne doit pas
pouvoir prendre plusieurs valeurs– Solution : créer une autre relation– Exemple :
Machine(Num_machine,Date_instal, logiciel)Devient :Machine(Num_machine,Date_instal)logiciel(Num_Logiciel, Num_machine)
61 B. Caron
Modélisation logique - Normalisation
• Deuxième forme normale (2NF)– Un attribut qui ne fait pas partie de la clé
candidate ne doit pas être dépendent d’une partie de la clé.
– Toute clé candidate a un seul attribut est forcément 2NF
– Solution : créer une relation pour enlever l’attribut– Exemple :
Fournisseur (NomFourn, Article, Adresse, Prix)
NomFourn, Article Prix et NomFourn Adresse (manque Article)
Devient :
Fournisseur (NomFourn, Adresse)
Produit (NomFourn, Article, Prix)
62 B. Caron
Modélisation logique - Normalisation
• Deuxième forme normale (2NF)– Exemple correct :
Ajout (Num_rep, Num_photo, date, Num_personne)
– Astuces :
Éviter les clés à 2 attributs
Éviter d’autres attributs que ceux de la clé
63 B. Caron
Modélisation logique - Normalisation
• Troisième forme normale (3NF) :– chacun des attributs d’une relation qui ne fait pas
partie de la clé élémentaire est en dépendance fonctionnelle élémentaire directe de la clé (pas de transitivité).
– Solution : 2 relations– Exemple :
EMPLOYE (Matricule, Nom, Dep, NomDep)Matricule Dep NomDep
DevientEMPLOYE (Matricule, Nom, Dep)EMPLOYE (Dep, NomDep)
64 B. Caron
Modélisation logique - Normalisation
• Relations de l’exemple :Image(Num_image,Date_image,Image, Num_auteur)Reportage (Num_rep,Date_rep,Num_auteur)Auteur (Num_auteur, Prénom,Nom)Ajout (Num_rep,Num_image)
Toutes 3NF (et donc 2NF et 1NF)
La première étape de modélisation conceptuelle par les DF doit normalement aboutir à des relations 3NF.
Il existe d’autres formes normales
65 B. Caron
Chap 3 : SQL
• Introduction• Description des relations• Modification des relations• Manipulation des données• Interrogation
66 B. Caron
SQL - Introduction
• Il existe de nombreux langages, SQL est le plus répandu
• SQL = Structured Query Language (langage de requête structuré).
• Normalisation internationale et en constante évolution
• Utilisés dans de nombreux produits :– Oracle– SQL server (Microsoft)– Mysql– …
67 B. Caron
SQL - Introduction• SQL permet de supprimer créer modifier des relations dans une
base de données : langage de description des données
• SQL permet de supprimer créer modifier des occurrences dans une relation : langage de manipulation des données
• SQL basée sur l’algèbre relationnelle sur laquelle SQL réalise des opérations d’interrogation : langage d’interrogation des données• l’algèbre relationnelle ne sera pas étudiée ici
• On utilisera MySQL qui est un sous ensemble et ne respecte pas complètement la syntaxe SQL
• Manuel de référence : http://dev.mysql.com/doc/mysql/fr/index.html
• Manuel de la version 4.1 : http://www.nexen.net/docs/mysql/annotee/manual.php
68 B. Caron
SQL – description des relations
Image(Num_image,Date_image,Image, Num_auteur)Reportage (Num_rep,Date_rep,Num_auteur)Auteur (Num_auteur, Prénom,Nom)Ajout (#Num_rep,#Num_image)
Image(Num_image,Date_image,Image, Num_auteur)CREATE TABLE Image(Num_image INTEGER AUTO_INCREMENT,
Date_image DATE,Image BLOB, Num_auteur INTEGER,PRIMARY KEY (NUM_image))
CREATE DATABASE agence
RAW en SQL
Classique pour une clé
69 B. Caron
SQL – description des relations
Reportage (Num_rep,Date_rep,Num_auteur)CREATE TABLE Reportage( Num_rep INTEGER
AUTO_INCREMENT,Date_rep DATE,Num_auteur INTEGER,PRIMARY KEY (Num_rep))
Auteur (Num_auteur, Prénom,Nom) CREATE TABLE Auteur(Num_auteur INTEGER AUTO_INCREMENT,
Prenom CHAR(20), Nom CHAR(20),
PRIMARY KEY (Num_auteur))
70 B. Caron
SQL – description des relations
Ajout (#Num_rep,#Num_image)CREATE TABLE Ajout(Num_rep INTEGER,
Num_image INTEGER,PRIMARY KEY (Num_rep, Num_image))
Clé étrangère inexistante en MySQLSelon le format de la table
71 B. Caron
SQL – modification des relations
Ajout (#Num_rep, #Num_image, date)ALTER TABLE Ajout ADD Date_aj DATE
Ajout (#Num_rep,#Num_image)ALTER TABLE Ajout DROP Date_aj
Suppression d’une table :DROP TABLE Ajout
Suppression d’une base :DROP DATABASE Agence
72 B. Caron
SQL – Manipulation des données
Auteur (Num_auteur, Prénom,Nom)
Ajout d’un tuple : INSERT INTO Auteur VALUES (NULL, 'Jean', 'Blanc')
Modification d’un tuple UPDATE Auteur SET Prenom='Paul' WHERE Prenom='Jean'
Suppression d’un tuple DELETE FROM Auteur WHERE Prenom='Paul'"
73 B. Caron
SQL – Interrogation des données
INSERT INTO auteur VALUES (NULL, 'Jean', 'Blanc'),(NULL,'Claude','Blanc'),(NULL,'Jean-Claude','Blanc'),(NULL,'Jean','Noir'),(NULL,'Claude','Noir')
SELECT * FROM auteur
74 B. Caron
SQL – Interrogation des données
SELECT * FROM auteur WHERE NOM= 'Noir'
SELECT * FROM auteur WHERE NOM= 'Noir' AND PRENOM= 'Jean'
75 B. Caron
SQL – Interrogation des données
reportages (Num_rep,Date_rep,Num_auteur)INSERT INTO reportages VALUES
(NULL, '2005-08-31', '1'),(NULL, '2005-06-25', '2'),(NULL, '2005-06-01', '2'),(NULL, '2005-03-01', '3'),(NULL, '2005-03-01', '3')
SELECT * FROM reportages
76 B. Caron
SQL – Interrogation des données
image(Num_image,Date_image,Image, Num_auteur)
INSERT INTO image VALUES (NULL, '2004-08-31', 'xxx', '2'),(NULL, '2004-06-25', 'xxx', '5'),(NULL, '2005-03-08', 'xxx', '3'),(NULL, '2005-02-05', 'xxx', '4'),(NULL, '2005-02-05', 'xxx', '1')
SELECT * FROM images
77 B. Caron
SQL – Interrogation des données
ajout (#Num_rep,#Num_image) INSERT INTO ajout VALUES
('1', '2'),('1', '3'),('2', '1'),('2', '2'),('3', '4')
SELECT * FROM ajout
78 B. Caron
SQL – Interrogation des données
• Jointure de tables (partagent une clé)
select auteur.nom from auteur,reportage where auteur.Num_auteur=reportage.Num_auteur
79 B. Caron
Pour aller plus loin
• Optimisation• Clés étrangères
80 B. Caron
Pour aller plus loin – Optimisation
• SELECT * FROM auteur WHERE nom = 'BLANC'– Le SGBD regarde toutes les lignes pour
retrouver tous les 'BLANC‘– Temps de réponse très lent
• Création d’un index dans la tableCREATE TABLE Auteur(Num_auteur INTEGER AUTO_INCREMENT,
Prenom CHAR(20), Nom CHAR(20),
PRIMARY KEY (Num_auteur), Index (Nom))
– Le SGBD crée en mémoire des blocs disques– Ils contiennent des couples : index, numéro
de bloc– Accès plus rapide mais :
• Modification plus lente (création blocs et couples)• Prend plus de place
81 B. Caron
Pour aller plus loin – Optimisation
• Utiliser les types les plus compacts possibles (TINYINT, MEDIUMINT,INT)
• N’utiliser des SELECT que sur les attributs utiles. Exemple on veut les prénoms :
SELECT PRENOM FROM auteur WHERE NOM= 'Noir'
Préférable à :SELECT * FROM auteur WHERE NOM= 'Noir'
82 B. Caron
Clés étrangères
Ajout (#Num_rep,#Num_image)CREATE TABLE Ajout(Num_rep2 INTEGER,
Num_image2 INTEGER,FOREIGN KEY (Num_rep2) REFERENCES reportage(Num_rep),
FOREIGN KEY (Num_image2) REFERENCES image(Num_image) )TYPE=InnoDB
Image(Num_image,Date_image,Image, Num_auteur)CREATE TABLE Image(Num_image INTEGER AUTO_INCREMENT,
Date_image DATE,Image BLOB, Num_auteur INTEGER,PRIMARY KEY (NUM_image)) TYPE=InnoDB
Reportage (Num_rep,Date_rep,Num_auteur)CREATE TABLE Reportage( Num_rep INTEGER AUTO_INCREMENT,
Date_rep DATE,Num_auteur INTEGER,PRIMARY KEY (Num_rep)) TYPE=InnoDB
top related