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

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

Embed Size (px)

Citation preview

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

B. Caron

Bases de données

Licence IRXBernard Caron

2008-2009

Page 2: B. Caron Bases de données Licence IRX Bernard 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 : [email protected]

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

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

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

4 B. Caron

Plan du cours

IntroductionModélisation conceptuelleModélisation LogiqueDescription des relations en SQLPour aller plus loin

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

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/

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

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

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

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…)

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

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

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

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

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

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

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

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

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

12 B. Caron

Introduction – BD SGBD

• Base de données : ensemble d’informations :– exhaustives, – non redondantes, – structurées, – persistantes

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

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é

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

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

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

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

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

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.

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

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

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

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é

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

29 B. Caron

Modélisation conceptuelle – les étapes

Dictionnaire des données

Dépendances fonctionnelles

Modèle Entité/Associations

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

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

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

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 *

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

32 B. Caron

Mod. conceptuelle – dic. des données

domaine

Date naissance 2N-2N-4NNbre Filles 2NNbre garçons 2N

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

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

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

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 *

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

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

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

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

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

37 B. Caron

Mod. Conceptuelle – Dep. fonctionnelles• Règles 3 :

– Suppression des dépendances implicites

• Graphiquement :

N° étudiant N° INSEE

Nom étudiant

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

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

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

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

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

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

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

41 B. Caron

Mod. Conceptuelle – Dep. fonctionnelles

Num_rep

Num_image

Num_auteur

NomPrénom

ImageDate_image

Date_Rep

Appartenance

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

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é

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

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

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

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

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

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

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

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

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

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

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

48 B. Caron

Chap 2 : Modélisation logique

• Modèle relationnel• Passage E-A -> modèle relationnel• Normalisation

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

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

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

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

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

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

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

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

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

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.

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

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.

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

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.

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

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

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

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

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

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

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

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

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

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)

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

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)

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

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é

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

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)

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

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

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

65 B. Caron

Chap 3 : SQL

• Introduction• Description des relations• Modification des relations• Manipulation des données• Interrogation

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

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– …

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

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

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

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é

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

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))

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

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

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

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

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

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'"

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

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

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

74 B. Caron

SQL – Interrogation des données

SELECT * FROM auteur WHERE NOM= 'Noir'

SELECT * FROM auteur WHERE NOM= 'Noir' AND PRENOM= 'Jean'

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

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

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

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

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

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

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

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

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

79 B. Caron

Pour aller plus loin

• Optimisation• Clés étrangères

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

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

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

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'

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

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