B. Caron Bases de données Licence IRX Bernard Caron 2008-2009

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