30/01/2011

1

Licence 3 Informatique

2010 - 2011

1

Bases de données I (licence 2 Informatique)

Bases de données II (licence 3 Informatique)

Banques de données multimédia, Entrepôts de données et informatique décisionnelle, Fouilles de données, Fouilles de données réparties, …

2

Séances ◦ CM : 22h

◦ TD : 22h

◦ TP : 22h (Projet)

◦ Contenu : http://dpt-info.u-strasbg.fr/~mveit

Evaluation◦ Projet (1/3)

◦ Contrôle terminal (2/3)

3

30/01/2011

2

4

5

Programme 1

Programme 2

Programme 3

Fichier 1

Fichier 2

Système de gestion de

fichiers

Données

Données

Données

Les données sont stockées dans des fichiers

Inconvénients pouvant se produire lors du stockage d’une grande masse de données, accessible par différents utilisateurs et applications :◦ Différents formats de fichier, dépendant des programmes et

généralement faiblement structurés

◦ Difficulté d’accès à l’information (index)

◦ Pas de vérification sur la cohérence des données (redondance, erreurs, …)

◦ Pas de contrôle des accès concurrents

6

30/01/2011

3

7

Programme 1

Programme 2

Programme 3

Bases de données

SGBDDonnées

Données

Données

Transactions

Requêtes

Base de données (BD)◦ Une base de données est un ensemble d’informations de grande taille,

structuré, mémorisé sur un support permanent

Système de gestion de Bases de Données (SGBD)◦ Un système de gestion de bases de données est un outil informatique

permettant aux utilisateurs de structurer, d’insérer, de modifier, de rechercher de manière efficace des données spécifiques au sein d’une grande quantité d’informations stockées sur mémoires secondaires partagée de manière transparente par plusieurs utilisateurs

8

BD pour une compagnie aérienne

Objectif : gérer les réservations

◦ Quelles informations doivent être stockées ?

◦ Quels types d'interrogations va-t-on effectuer ?

9

30/01/2011

4

Les données◦ Les appareils◦ Les vols◦ Les aéroports◦ Les réservations◦ Les achats

Les types d'interrogations◦ Quels sont les vols au départ de X et arrivant à Y le 15 mars

2004 ?◦ Quels sont les prix de ces vols ? ◦ Combien de passagers ont voyagé sur le vol 1234 du 15 mars

2004 ?

10

SGBD relationnel : les données sont stockées dans des tables

Volsnum_vol compagnie type_avion

123 Air France Boeing 747

234 Alitalia Airbus A340

… … …

Requête SQL: Donner le type d'avion du vol 123

SELECT type_avion

FROM Vols

WHERE num_vol = 123;

11

Centralisation des données (une information ne doit être stockée qu’une seule fois)◦ Cohérence et intégrité des données

Contrôle centralisé de l'accès aux données partagées◦ Sécurité accrue

Indépendance du modèle physique et logique des données◦ Maintenance facilitée

Existence d’un langage déclaratif simple (pour les BD relationnels: SQL)

Performance d’accès sur de grands volumes de données pour un grand nombre de clients

12

30/01/2011

5

1. Persistance

2. Langage de description, d’interrogation, de manipulation données

3. Partage des données

4. Fiabilité des données

5. Sécurité des données

6. Gestion des disques

7. Indépendance logique/physique

13

Persistance◦ Stockage des données sur disque

Langage de requête sur la base◦ Les requêtes doivent être simples, déclaratives, optimisées

avant l’exécution

◦ SQL (Structured Query Langage), OQL (Object Query Langage), Xquery (XML Query Langage)

Partage des données◦ Chaque utilisateur doit avoir l’impression d’être seul à accéder aux

données -> notion de transaction◦ Cohérence des mises à jour d’un utilisateur◦ Cohérence collective : sérialisabilité

Fiabilité des données◦ Vérification de contrainte d’intégrité◦ Atomicité des transactions : transaction complètement effectuée ou

pas du tout◦ Résistance aux pannes En cas de panne mémoire : restauration automatique des dernières

transactions validées avant la panne En cas de panne disque : restauration d’une sauvegarde, journal Mécanisme de réplication des données (mirroring)

30/01/2011

6

Sécurité des données◦ Droits des utilisateurs sur la manipulation des données Notion d’autorisation Notion de groupe d’utilisateur Granularité des autorisations

Gestion des disques◦ Répartition des données◦ Optimisation des performances Index, hachage, bitmap Regroupement des données Optimisation des requêtes Cache mémoire

Indépendance logique / physique◦ Organisation physique de la BD transparente pour le

développeur d’application

◦ Possibilité de changer la structure physique des données sans changer le code de l’application

Begin // début de la transaction

CEpargne = CEpargne – 1000

CCourant = CCourant + 1000

Commit // fin de la transaction

Begin

CEpargne = CEpargne – 1000

>>>> panne

CCourant = CCourant + 1000

Commit T1

=> annuler le débit !

18

30/01/2011

7

Atomicité : Une transaction est indivisible. Elle est soit complètement exécutéesoit pas du tout (unité atomique de traitement)

Cohérence : une transaction doit effectuer une transition d’un état cohérent de labase à un autre état cohérent (e.g. pas de violation de contrainte d’intégrité). Lacohérence peut être non respectée pendant l'exécution d'une transaction. En casd’échec de la transaction, la base doit retourner dans l’état cohérent initial

Isolation : le résultat d’un ensemble de transactions concurrentes et validées correspond à une exécution successive des mêmes transactions (pas d’inférence entre les transactions)

Durabilité : après la fin d'une transaction, les mises à jour sont définitives même en cas de problèmes matériels (mécanisme de reprise en cas de panne)

19

20

Vues externeschaque vue représente

la partie de la bd relativeà chaque utilisateur / application

Schéma conceptuel vue globale de la base :

les données stockées et les relations parmi les données

Schéma physiquedécrit comment les données

sont stockées dans la bd +les index

R1(A number(10), B string, …, primary key (A), ...)….Rn (X number(15), Y string, …, primary key (X), …)

Schéma logiqueReprésente un modèle de données conforme au modèle ciblé de SGBD

(par ex. SGBDR)

21

Schéma conceptuel

Schéma physique

R1(A number(10), B string, …, primary key (A), ...)….Rn (X number(15), Y string, …, primary key (X), …)

Schéma logique

Indépendance logique : Un changement du schéma conceptuel (nouvel attribut, nouvelle relation, …),n'entraîne pas de changement dans les vues existantes

Indépendance physique :Les changements du schéma interne ne sont pas visibles aux niveaux des schémas conceptuels et logiques; conséquences sur les performances

Schéma externe

30/01/2011

8

Niveau externe : Vues, Environnement de programmation (intégration avec un langage de programmation), Interfaces conviviales, Outils d’aide (ex: conception de schémas)

Niveau logique : Définition de la structure de données, Langages de description des données (LDD), Consultation et mise à jour des données : Langages de Requêtes (LR) et Langage de Manipulation de Données (LMD), Gestion de la confidentialité (sécurité), Intégrité des données

Niveau physique : Gestion sur mémoire secondaire (fichiers) des données, du schéma, des index, Partage des données et gestion de la concurrence d’accès, Reprise sur pannes (fiabilité), Distribution des données et interopérabilité (accès réseaux)

Type d’utilisateur◦ Utilisateur de base◦ Concepteur d’application◦ Administrateur de la base

22

Modèle conceptuel◦ Pour la conception d’applications◦ Modèle entité-association (notation MERISE, UML, …)

Modèle logique◦ Pour la définition de BD supportées par un SGBD◦ Modèle hiérarchique, réseau, relationnel, objet, …

Modèle physique◦ Implémentation du SGBD : fichier simple, base de faits, XML,

…◦ Organisation et stockage des données, index, …

24

30/01/2011

9

Définition : le modèle logique décrit la structure de données

Objectifs◦ Éviter les incohérences dans les données Une personne n’a qu’une date de naissance, le prix d’un produit est unique, ….

◦ Éviter la redondance d’information La même information est stockée dans différentes relations Anomalies : insertion, suppression, modification

◦ Éviter les valeurs nulles Difficiles à interpréter : inconnu, connu mais non disponible, inapplicable Rend les jointures difficiles à spécifier

◦ Éviter les jointures inutiles Améliorer les performances : la jointure est une opération coûteuse

25

Méthodologie à suivre pour modéliser un problème◦ Point de départ : un énoncé informel

Déterminer les entités / classes et attributs◦ Entité / instance de classe = objet décrit par de l’information◦ Objet caractérisé uniquement par un identifiant = attribut◦ Attribut multi-valué ou avec une association 1:N = entité ou instance◦ Attacher les attributs aux ensemble d’entités / classes qu'ils décrivent le plus directement

Eviter au maximum les identificateurs composites◦ Identifier les généralisations-spécialisations / héritage◦ Définir les associations

Eliminer les associations redondantes◦ Eviter les associations n-aires◦ Calculer les cardinalités de chaque association

26

27

Automobiliste

Id

NomPrénomAdresse

Voiture

Immatriculation

MarqueTypePuissanceAnnée

Possède0:n 1:1

30/01/2011

10

28

Personne

Id

NomPrénomAdresseE-mail

Etudiant

CursusMention

IS_A

Enseignant

PositionEtablissement

29

Enseignant

Id_Enseignant

NomPrénomPositionTéléphoneE-mail

Cours

Id_Cours

TitreDescriptiondonne

0:n 0:m

SemestreAnnée

A lieu dans

Salle

BâtimentNuméro

1:1

0:n

Chaque salle a un numéro unique dans un bâtiment donné◦ Exemple : Salle 1 du bâtiment A et Salle 1 du bâtiment C

Pour distinguer une salle d’une autre, il faut connaître le bâtiment auquel elle est rattachée

30

Bâtiment

Id_Bâtiment

NomAdresse

Salle

Num_Salle

CapacitéPossède

0:n 1:1

30/01/2011

11

Contraintes d’intégrité : toutes règles implicites ou explicites que doivent suivre les données

Contraintes d'entité : toute entité doit posséder un identificateur

Contraintes de domaine : les valeurs de certains attributs doivent être prises dans un ensemble donné

Contraintes d'unicité : une valeur d'attribut ne peut pas être affectée deux fois à deux entités différentes

Contraintes générales : règle permettant de conserver la cohérence de la base de manière générale

31

Contraintes de domaine◦ « La fonction d’un enseignant à l’Université prend sa valeur

dans l’ensemble {vacataire, moniteur, ATER, MCF, Prof., PRAG, PAST}. »

Contraintes d'unicité◦ « Un département, identifié par son numéro, a un nom unique

(il n’y a pas deux départements de même nom). »

Contraintes générales◦ « Un même examen ne peut pas avoir lieu dans deux salles

différentes à la même date et à la même heure. »

32

Modèle de niveau logique simple défini par EdgarFrank Codd en 1970 (prix Turing en 1986)

Vocabulaire◦ Domaine : ensemble de valeurs caractérisé par un nom

◦ Relation : sous-ensemble du produit cartésien d'une liste de domaines caractérisé par un nom unique

Représentée sous forme de table à deux dimensions

Colonne = un domaine du produit cartésien

Un même domaine peut apparaître plusieurs fois

Ensemble de n-uplets sans doublon

33

30/01/2011

12

◦ Attribut : une colonne dans une relation

Caractérisé par un nom et dont les valeurs appartiennent à un domaine

Les valeurs sont atomiques

◦ N-uplet (ou tuple) : une ligne d'une relation

Correspondant à un enregistrement, i.e. une entité / instance de classe

Les n-uplets d'une relation sont tous différents

34

Un exemple de relation (Enseignant)

35

NSS Nom Prénom Position

…

123 Veit Manuel ATER

… … … …

Un attribut

Un tuple

Instances de base de données◦ les n-uplets (les valeurs) contenus dans la base à un instant

donné

Schéma de base de données◦ ensemble de schémas de relation

◦ modélisation logique de la base de données à l’aide du modèle relationnel

Schéma de relation◦ liste d’attributs et leurs domaines

36

30/01/2011

13

Objectif : transformation des ensembles d’entités◦ Passage du MCD au Modèle Logique de Donnée (MLD)

Chaque ensemble d'entités/classes E◦ une relation R dont le schéma est celui de l'ensemble d'entités / classe◦ l'identificateur de E devient la clé de R

Chaque ensemble d'entités faibles / association qualifiée E◦ une relation R qui comprend tous les attributs de E + l'identificateur de

l'ensemble d'entités fortes / classe associé(e)

Généralisation-spécialisation/héritage◦ l'ensemble d'entités généralisante / classe mère E ⇒ une relation R◦ chaque ensemble d'entités Ei spécialisé/classe fille ⇒ une relation Ri

dans laquelle l'identifiant est de même domaine que l'identifiant de E

37

Automobiliste (Id_Automobiliste, Nom, Prénom, Adresse)

Voiture (Immatriculation, Marque, Type, Puissance,

Année)

Comment faire le lien ?◦ Accident (Id_Automobiliste, Nom, Prénom, Adresse,

Immatriculation, Marque, Type, Puissance, Année)

38

Automobiliste

Id_Automobiliste

NomPrénomAdresse

Voiture

Immatriculation

MarqueTypePuissanceAnnée

Accidenté lors de la location

1:1 1:1

Automobiliste (Id_Automobiliste, Nom, Prénom, Adresse)

Voiture (Immatriculation, Marque, Type, Puissance,

Année)

Comment faire le lien ?◦ Voiture(Immatriculation, Marque, Type, Puissance, Année,

#Id_Automobiliste)

39

Automobiliste

Id_Automobiliste

NomPrénomAdresse

Voiture

Immatriculation

MarqueTypePuissanceAnnée

Possède0:n 1:1

ou 1:nou 0:1

30/01/2011

14

Automobiliste (Id_Automobiliste, Nom, Prénom, Adresse)

Voiture (Immatriculation, Marque, Type, Puissance,

Année)

Comment faire le lien ?◦ Loue(#Id_Automobiliste, #Immatriculation, Date)

◦ Loue(Id_Location, #Id_Automobiliste, #Immatriculation, Date)

40

Automobiliste

Id_Automobiliste

NomPrénomAdresse

Voiture

Immatriculation

MarqueTypePuissanceAnnée

Loue0:n

ou 1:nou 0:1

0:m

ou 1:mou 0:1

Date

Personne(Id_Personne, Nom,

Prénom, Adresse, E-mail)

Etudiant(#Id_Personne, Cursus,

Mention)

Enseignant(#Id_Personne,

Position, Etablissement)

41

Personne

Id_PersonneNomPrénomAdresseE-mail

Etudiant

CursusMention

IS_A

Enseignant

PositionEtablissement

Batiment(Id_Batiment, Nom, Adresse)

Salle(Id_Salle, #Id_Batiment, Capacité)

42

Bâtiment

Id_Bâtiment

NomAdresse

Salle

Id_Salle

CapacitéPossède

0:n 1:1

30/01/2011

15

Unicité des clés◦ ensemble minimal d'attributs dont la connaissance des valeurs

permet d'identifier un n-uplet unique de la relation considérée

◦ R a pour clé K si : ∀ t1, t2 n-uplets d’une instance de R t1.K ≠ t2.K

Contraintes de référence◦ Contrainte d’intégrité référentielle : contrainte d'intégrité portant

sur une relation R qui consiste à imposer que la valeur d'un groupe d'attributs apparaissent comme valeur de clé dans une autre relation

◦ Clé étrangère : un groupe d'attributs qui doit apparaître comme clé dans une autre relation

43

Clé / Clé minimale / Surclé

◦ Accident(Id_Automobiliste, Nom, Prénom, Adresse, Immatriculation, Marque, Type, Puissance, Année)

Clés primaires possibles : Id_Automobiliste ou Immatriculation Surclé : (Id_Automobiliste, Immatriculation) + d’autres attributs

◦ Voiture(Immatriculation, Marque, Puissance, Type, Année, Id_Automobiliste)

Clé primaire : Immatriculation Surclé : (Immatriculation, Marque, Puissance, Type, Année, Id_Automobiliste)

◦ Location(Id_Automobiliste, Immatriculation, Date)

Clé primaire : (Id_Automobiliste, Immatriculation, Date)

44

Contrainte d’intégrité structurelle◦ Valeur nulle

Valeur conventionnelle introduite dans une relation pour représenter une information inconnue ou inapplicable

Tout attribut peut prendre une valeur nulle excepté les attributs de la clé primaire (contrainte d’entité)

◦ Contraintes de domaine

Contrainte d'intégrité qui impose qu'une colonne d'une relation doit comporter des valeurs vérifiant une assertion logique

45

30/01/2011

16

Quelques rappels sur la modélisation◦ MCD (en utilisant un Modèle E / A) et MLD (Modèle

Relationnel)

◦ Passage du Modèle E / A au Modèle Relationnel

Maintenant◦ Passage du MLD au Modèle Physique de Données (MPD)

◦ MPD = Tables SQL

46