Aucun titre de diapositive

1
Introduction aux Bases de données en Sciences de
la Vie et en Santé.
UE Conception de bases de données Master1 SVE -
MSB Fouzia Moussouni-Marzolf
2
Un peu dhistoire
Limites de lutilisation des fichiers
Quelles sont mes données ?
Comment les structurer?
Comment y accéder ?
Comment créer un index ?
Comment les manipuler ?
 àç _at_é() _at_!!!!
Application rigide, longue, coûteuse
Données redondantes, peu fiables, ponctuelles
3

• Les utilisateurs exigent de plus en plus
• des systèmes globaux
• des systèmes cohérents
• ne pas écrire des programmes complexes (ou
  plutôt compliqués!)
• avoir des réponses immédiates aux
• questions quils posent
• avoir des données à jour, ...

SGBD
4
Exemple dobjets dune base de données
interactions protéines protéines

• expériences , complexes, protéines, interactions,
  familles fonctionnelles.
• Données
• Les expériences, liste des interactions révélées,
  liste des protéines impliquées, les complexes
  protéiques auxquels elles participent, la famille
  fonctionnelle, etc.
• Applications
• analyse des interactions, annotations
  fonctionnelles,
• aide à la compréhension de phénomènes biologiques

5
Exemple dune BD sur les Interactions protéiques

• Données (ou objets) protéines, interactions,
  complexes

• Associations entre objets

• binaires une interaction fait intervenir 2
  protéines.
• n-aires une protéine peut apparaître dans
  plusieurs interactions.
• diverses relations pour un même objet
• La protéine X (SNF1) intervient dans
  linteraction Y (avec SNF4)
• La protéine X participe au complexe Z
• L interaction Y est détectée dans l expérience
  E

6
Exemple dune BD sur les Interactions protéiques

• Modèle Objet-Relation ou Entité-Association

0n
2n
Cardinalité 0n pour lentité ltProtéinesgt
exprime quune protéine peut être partenaire dans
aucune (0) ou plusieurs complexes
7
Architecture d'un SGBD On distingue 3 niveaux
dans une BD (norme ANSI/SPARC)
Niveau interne ou physique. Le niveau interne
permet de décrire les données telles qu'elles
sont stockées dans la machine, en particulier
dans les fichiers qui les contiennent (nom,
localisation, taille,).
Niveau logique. Le niveau logique permet de
décrire, de manière "abstraite" et structurée,
la réalité de l'entreprise, du monde ou de
l'application.
Niveau externe Au niveau externe, les schémas ou
vues décrivent la partie des données présentant
un intérêt pour un utilisateur ou un groupe
d'utilisateurs.
8
Architecture d'un SGBD
Fonctionnalités du Niveau Physique

• Gestion des données sur mémoire secondaire
  (fichiers).

• Partage des données et gestion de la concurrence
  d'accès.

• Reprise sur pannes (fiabilité).

• Distribution des données et interopérabilité.

9
Architecture d'un SGBD
Fonctionnalités d'un SGBD - Niveau Logique

• Définition de la structure de données Langage
  de Description de 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é).

• Maintien de l'intégrité.

10
Architecture d'un SGBD

• Fonctionnalités d'un SGBD Niveau Externe
• Vues ou schémas externes décrivant la partie
  des données qui présentent un intérêt pour un
  utilisateur ou un groupe d'utilisateurs.

• Environnement de programmation (intégration avec
  un langage de programmation).

• Interfaces conviviales.

• Outils d'aides pour la conception de schémas.

• Outils de saisie, d'impression.

• Passerelles (réseaux, autres SGBD, ...).

11
Système de Gestion de Bases de Données Objectifs
Transparence
réponses
questions
des fonctions intégrées
Indépendance physique et logique.
Manipulation des données sans savoir
programmer (langages  quasi naturels ).
Efficacité des accès aux données.
Administration centralisée.
Non redondance.
Cohérence.
Partageabilité.
Sécurité.
Résistance aux pannes.
12
Comment assurer ces objectifsConcepts de base
Les trois niveaux de descriptions définies par la
norme ANSI/ SPARC
Niveau interne Stockage
Niveau conceptuel ou logique
Vues
BD
13
(No Transcript)
14
Exemple de vues

• Les vues dune base de données  École  (!!)
• Vue1 Planification des cours (nom du cours,
  nom du professeur, horaires et salles, liste
  des étudiants).

• Vue2 Paye des professeurs (nom, prénom, age,
  adresse, grade, nombre d'heures, ).

• Vue3 Résultats scolaires des étudiants .

Remarque Les données utilisées par une vue
peuvent être  dérivées  de la base de données
et ne pas être présentes physiquement dans la
base. Exemple lage est calculé à partir de la
date de naissance.
15
La description des données est le résultat de la
conception dune base de données
Utilisation d un modèle de données (relationnel,
objet, etc.)

• Un modèle de données est un
• outil intellectuel pour comprendre
  lorganisation logique
• des données ou de la connaissance.

• ensemble de concepts et de règles pour
  construire la réalité avec des types de données

Description des données Schéma
Un SGBD est caractérisé par le modèle de
description des données quil supporte. Les
données sont décrites selon ce modèle grâce à
un langage de description des données.
Manipulation des données

• Une fois les données spécifiées, on peut
• les insérer
• les lire, afficher
• les modifier
• les détruire

16
Les composants d un SGBD
 quelque soit le modèle de données quil
supporte 
1 Outils de description des données (en gros des
langages) - décrire la vision qua
l utilisateur des données - décrire le stockage
physique des données.
2 Outils de manipulation des données -
interroger, - modifier, - détruire, les
données, de façon optimisée via - des langages
de manipulation des données, SQL, OQL - des
extensions de langages classiques. JAVA,
C, Python ...
3 Outils de sauvegarde et récupération après panne
4 Outils pour permettre un accès concurrent aux
données de façon cohérente.
Chacun de ces composants sera décrit par la
suite .
17
Retour sur histoire
1960 Uniquement des systèmes de gestion de
fichiers très sophistiqués
(i.e. compliqués !)
1970 Début des systèmes de gestion de bases de
données réseaux et hiérarchiques, très proches
des systèmes de gestions de fichiers. Inconvénien
t on doit savoir la localité (chemin) de
l information recherchée.
Sortie de la théorie des relations et fondements
des bases de données relationnelles (Papier de
E.F. CODD) A relational model of data for large
shared data banks, Communications of the ACM
13(6), 377-387, 1970
1980 Apparition sur le marché des systèmes de
gestion de bases de données relationnelles
- Capacités plus grandes d'adaptation aux
évolutions de la gestion
1990 Les systèmes de gestion de bases de données
relationnelles dominent le marché
Début des systèmes de gestion de bases de données
orienté-objets.
18
A parte Base de données
Base de données données organisées, stockées
dans des fichiers liés entre eux grâce à un SGBD

• Il existe plusieurs  styles  de BDD
• Hiérarchique
• Réseau
• Relationnelle
• Objet
• multi dimensionnelle

19
Base de données hiérarchique
Patron
Chef
Chef
Sous-chef
Sous-chef
Sous-chef
Sous-chef
Sous-chef
20
Base de données réseau
Patron
Patron
Chef
Chef
Sous-chef
Sous-chef
Sous-chef
Sous-chef
Sous-chef
21
Base de données relationnelle
Une table ?
Plusieurs tables ?
Chromosome
Genome
Gene
CodeGeneNomGene .
CodeGenomeNomGénome .
CodeChromosomeNumChromosome .
Select Cekejeuveu From LaTableKeJeudi Where
LaConditionKimeuplé
Select NomGene From TableGene Where
CodeChromosome HUM-10
SQL
22
(No Transcript)
23
Base de données objet
24
Conception de Base de données relationnelles, Algè
bre relationnelle et langage SQL
La conception dune base de données nest pas un
domaine réservé aux informaticiens !
Bon nombre de logiciels apparaissent tous les
mois, mais l algèbre relationnelle a été
inventée par E.F. CODD en 1970
Preuve
Les logiciels de type SGBDR sont intégrés aux
suites bureautiques les plus connues. Ex ACCESS
du pack Office.
Cependant
La maîtrise dun SGBDR est loin d être
aussi facile à acquérir que celle d un logiciel
de traitement de texte par ex.
25
LES ETAPES DE CONSTRUCTION
Structurer les données de la base Modèle
conceptuel des données VS. tableaux de données
26
A une table est associée ce quon appelle une
relation.
Ex Génomes (CodeGenome,Espèce,Nb_chromosome)
27
LES ETAPES
Modèle conceptuel de traitement Traitement des
besoins (enfin! )
28
LES ETAPES
Les interfaces utilisateurs ou L APPLICATION
Il sagit de réaliser une application de
consultation et de mise à jour de la base de
données.
A savoir
Un premier principe dergonomie à connaître est
quune application a pour but dêtre utilisée par
des gens qui ont une certaine connaissance de son
contexte. Vous devez donc réaliser votre logiciel
en fonction de ces utilisateurs.
Les utilisateurs sont supposés avoir une idée du
flot de données qui rend votre base cohérente.
Ils connaissent les actions à réaliser pour
commencer telle ou telle tâche.
Un bouton, quelles que soient les aides que vous
pourrez y associer, restera un bouton. Ce nest
pas parce que linterface est graphique que les
utilisateurs sauront ce quils ignorent.
 Il ne faut pas présenter des données, il faut
présenter des informations 
29
Algèbre Relationnelle et langage SQL
Illustration avec des études de cas
Une relation est représentée par une table
? ? ? ?
ensemble de tuples
30
Gestion simplifiée dun génome
1
n
génome chromosome
31
Gènes
Séquence ADN
Code chromosome
Code Génome
Source
Nom
Code Gene
1 2 3 4
1 1 1 8
IREG1 TRFr DMT1 TNFa
HUM MOU DROZ MOU_KO
32
Il existe deux grands types de liens
Un - plusieurs
Plusieurs - Plusieurs
1
Autres Fragments
CodeFragment Type séquence début fin CodeChromo
some CodeGenome

Voilà le modèle !
Présence de redondance !?
33
Opérations propres à l algèbre relationnelle
Opération PROJECTION / Exemples
Génomes Formalisme R PROJECTION (R1, liste
des attributs)
Espèce Homo sapiens Mus
musculus S.Cerevisiae DROSOPHILE.M
Espèce NB chromosomes Homo
sapiens 25 Mus musculus 22 S.Cerevisiae 17 DROSOP
HILE.M 8
Cet opérateur ne porte que sur 1 relation.
Il permet de ne retenir que certains attributs
spécifiés d'une relation.
On obtient tous les n-uplets de la relation à
l'exception des doublons.
34
Opération PROJECTION
table
liste d'attributs
SELECT
FROM
SELECT
DISTINCT
Exemples SELECT DISTINCT Espèce FROM Génome
SELECT DISTINCT Espèce, NB chromosomes FROM
Génome
La clause DISTINCT permet d'éliminer les doublons.
35
Opération SELECTION / Exemple Génome
Formalisme R SELECTION (R1, condition)
36
Quels sont les gènes qui participent au codage
de la même protéine PROD (albumine par exemple) ?
Gènes
Produit gène ?
Séquence ADN
Code chromosome
Source
Code Gene
taille
Nom
1 2 3 4
IREG1 TRFr DMT1 TNFa
Prod
1 1 1 8
Prod
37
Opération SELECTION en SQL
SELECT FROM table
WHERE condition
Exemple SELECT FROM Génome WHERE
NBChromosomes 2 0
La condition de sélection exprimée derrière la
clause WHERE peut être spécifiée à l'aide
Autres exemples Soit la table Gène ( CodeGene,
nom, source, sequence, taille,
CodeChromosome, produit)
SELECT FROM Gène WHERE taille IN (2000, 2500,
2575, 2600, 3000)
SELECT FROM Gène WHERE taille BETWEEN 2000
AND 3000
SELECT FROM Gène WHERE produit LIKE
'albumine' sous Access LIKE     "albumine"
38
Quels sont les espèces pour lesquels il existe
des chromosomes à plus de 300000 bases
Opération JOINTURE Formalisme R JOINTURE (R1,
R2, condition d'égalité entre attributs)
Chromosomes
Génomes
R JOINTURE (Genome,Chromosome,
Genome.CodeGenomeChromosome.CodeGenome)
Projection sur Espèce sans doublons
39
Opération JOINTURE en SQL
En SQL, il est possible d'enchaîner plusieurs
jointures dans la même instruction SELECT
table1, table2, table3, ...
SELECT FROM
WHERE
table1.attribut1table2.attribut1 AND
table2.attribut2table3.attribut2 AND ...
Exemple
Ou en utilisant des alias pour les noms des
tables SELECT Espece FROM Genome A, Chromosome
BWHERE A.CodeGenome B.CodeGenome and B.taille
gt 300000
40
Quels sont les chromosomes (numéro, taille, )
du génome de la Drosophile ?
Question 2
41
Quels sont les génomes pour lesquels le gène X1
est présent ?
Question 3
Select genome.nom from Genome, Chromosome, Gene
where Gene.nom like  X1  and
Genes.CodeChromosome Chromosomes.CodeChromosome
and Chromosomes.CodeGenome Genomes.CodeGenome
42
Exercice d'application n1
Soit le modèle relationnel suivant relatif à une
base de données sur des représentations musicales
REPRESENTATION (nreprésentation,
titre_représentation, lieu)MUSICIEN (nom,
nreprésentation)PROGRAMMER (date,
nreprésentation, tarif) Remarque les clés
primaires sont soulignées et les clés étrangères
sont marquées par
Questions 1 - Donner la liste des titres des
représentations.
2 - Donner la liste des titres des
représentations ayant lieu à l'opéra Bastille.
3 - Donner la liste des noms des musiciens et
des titres des représentations auxquelles ils
participent.
4 - Donner la liste des titres des
représentations, les lieux et les tarifs pour
la journée du 14/09/96.
43
Correction de l'exercice d'application n1
(faire un graphique)
1 - Donner la liste des titres des
représentations.R PROJECTION(REPRESENTATION,
titre_représentation) 2 - Donner la liste des
titres des représentations ayant lieu à l'opéra
Bastille.R1 SELECTION(REPRESENTATION,
lieu"Opéra Bastille")R2 PROJECTION(R1,
titre_représentation) 3 - Donner la liste des
noms des musiciens et des titres des
représentations auxquelles ils participent.R1
JOINTURE(MUSICIEN, REPRESENTATION,
Musicien.nreprésentationReprésentation.nreprése
ntation)R2 PROJECTION(R1, nom,
titre_représentation) 4 - Donner la liste des
titres des représentations, les lieux et les
tarifs pour la journée du 14/09/96.R1
SELECTION(PROGRAMMER, date14/09/96)R2
JOINTURE(R1, REPRESENTATION, R1.nreprésentationR
eprésentation.nreprésentation)R3
PROJECTION(R2, titre_représentation, lieu, tarif)
44
Correction de l'exercice d'application n1
En SQL
1 - Donner la liste des titres des
représentations.SELECT titre_représentation FROM
REPRESENTATION 2 - Donner la liste des titres
des représentations ayant lieu à l'opéra
Bastille.SELECT titre_représentation FROM
REPRESENTATIONWHERE lieu"Opéra Bastille" 3 -
Donner la liste des noms des musiciens et des
titres des représentations auxquelles ils
participent.SELECT nom, titre_représentationFROM
MUSICIEN, REPRESENTATIONWHERE
MUSICIEN.nreprésentation REPRESENTATION.nrepré
sentation 4 - Donner la liste des titres des
représentations, les lieux et les tarifs pour la
journée du 14/09/96. SELECT titre_représentation,
lieu, tarifFROM REPRESENTATION,
PROGRAMMERWHERE PROGRAMMER.nreprésentationREPRE
SENTATION.nreprésentationAND date'14/06/96'
45
Opération DIVISION
Formalisme R DIVISION (R1, R2)
Un Exemple hors Bio
Tous les attributs du diviseur doivent être des
attributs du dividende.
"L'athlète Dupont participe à toutes les
épreuves"
Quels sont les gènes du génome Y qui sont
présents dans tous les chromosomes ? à vos
idées! ...
46
Opération DIVISION
Formalisme R DIVISION (R1, R2)
Quels sont les gènes qui sont présents dans tous
les chromosomes du génome de la Drosophile.M
47
Opération DIVISION
Cependant il est toujours possible de trouver une
autre solution, notamment par l'intermédiaire des
opérations de calcul et de regroupement.
Dans l'exemple présenté, on souhaite trouver les
athlètes qui participent à toutes les épreuves.
En algèbre relationnelle une autre solution que
la division pourrait être NCALCULER(EPREUVE,
Comptage())R1REGROUPER_ET_CALCULER(PARTICIPER,
Athlète, NbComptage())R2SELECTION(R1,
NbN)R3PROJECTION(R2, Athlète)
NCALCULER(Tous_les_chromosomes_de_y,
Comptage())R1REGROUPER_ET_CALCULER(Tous_les_gène
s_de_y, NomDuGène, NbComptage())R2SELECTION(R1,
NbN)R3PROJECTION(R2, NomDuGène)
et en SQL SELECT Athlète FROM PARTICIPERGROUP
BY AthlèteHAVING COUNT() (SELECT COUNT()
FROM EPREUVE)
et en SQL SELECT NomDuGène FROM
Tous_les_gènes_de_yGROUP BY NomDuGèneHAVING
COUNT() (SELECT COUNT() FROM
Tous_les_chromosomes_de_y)
48
Opération CALCULER
R CALCULER (R0, fonction1, fonction2, ...)
ou N CALCULER (R0, fonction)
En SQL SELECT fonction1(attribut1),
fonction2(attribut2), ...FROM table
R1CALCULER(Gènes, Somme(taille))
SELECT SUM(taille) FROM Gènes
49
Opération REGROUPER_ET_CALCULER
RREGROUPER_ET_CALCULER(R0, att1, att2, ...,
fonction1, fonction2, ...)
La relation résultat comportera autant de lignes
que de groupes de n-uplets, les fonctions
s'appliquant à chacun des groupes séparément.
50
Les opérations ensemblistes
A U B
51
Opération UNION
Formalisme R UNION (R1, R2)
52
Revenons à la génomique Exemple 1 Quels sont
les gènes qui sont connus dêtre impliqués dans
le métabolisme du fer OU dans le cycle cellulaire
?
53
Opération UNION en langage SQL
SELECT liste d'attributs FROM table1UNIONSELECT
liste d'attributs FROM table 2
Exemple SELECT NomGène FROM Gènes Where
Description_Fonctionnelle like iron
homéostasie UNIONSELECT NomGène FROM Gènes
Where Description_Fonctionnelle like cell
cycle
Exemple SELECT nenseignant, NomEnseignant
FROM E1 UNION SELECT nenseignant,
NomEnseignant FROM E2
54
Opération Intersection
Formalisme RINTERSECT (R1, R2)
Cet opérateur porte sur deux relations de même
schéma. La relation résultat possède les
attributs des relations d'origine et les n-uplets
communs à chacune.
55
Exemple Génomique Quels sont les gènes qui sont
connus d être impliqués dans le métabolisme du
fer ET dans le cycle cellulaire ?
Autres façons dorganiser les gènes catégoriser
les gènes par métabolisme et créer une table pour
chaque métabolisme ?
56
Opération INTERSECTION
SELECT attribut1, attribut2, ... FROM
table1INTERSECTSELECT attribut1, attribut2, ...
FROM table2
OU
SELECT attribut1, attribut2, ... FROM table1WHERE
attribut1 IN (SELECT attribut1 FROM table2)
Exemple SELECT nenseignant, NomEnseignant
FROM E1INTERSECTSELECT nenseignant,
NomEnseignant FROM E2
Exemple SELECT CodeGène, Produit FROM
FERINTERSECTSELECT CodeGène, Produit FROM Cycle

ou SELECT nenseignant, NomEnseignant FROM
E1WHERE nenseignant IN (SELECT nenseignant
FROM E2)
ou SELECT CodeGène, Produit FROM FERWHERE
CodeGène IN (SELECT CodeGène FROM Cycle)
57
Opération Différence
Formalisme RDIFFERENCE (R1, R2)
Cet opérateur porte sur deux relations de même
schéma. La relation résultat possède les
attributs des relations d'origine et les n-uplets
de la première relation qui n'appartiennent pas à
la deuxième. Attention ! DIFFERENCE (R1, R2) ne
donne pas le même résultat que DIFFERENCE (R2,
R1)
58
Revenons aux bases de données génomiques
Exemple 1 Quels sont les gènes qui sont connus
d être impliqués dans le métabolisme du fer mais
pas dans le cycle cellulaire ?
59
Opération DIFFERENCE
Exemple SELECT CodeGène, Produit FROM
FEREXCEPTSELECT CodeGène, Produit FROM Cycle
Exemple SELECT nenseignant, NomEnseignant
FROM E1EXCEPTSELECT nenseignant, NomEnseignant
FROM E2 ou SELECT nenseignant, NomEnseignant
FROM E1WHERE nenseignant NOT IN (SELECT
nenseignant FROM E2)
ou SELECT CodeGène, Produit FROM FERWHERE
CodeGène NOT IN (SELECT CodeGène FROM Cycle)
60

• Base de données génomique
• Quels sont les gènes impliqués dans le
  métabolisme du fer ou dans le cycle cellulaire ?

• Quels sont les gènes impliqués à la fois dans
  le métabolisme du fer, et dans le cycle
  cellulaire (ou des lipides) ?

• Quels sont ceux qui sont cycle cellulaire mais
  pas fer (pour linstant !) ?

61
Opération PRODUIT CARTESIEN
Formalisme R PRODUIT (R1, R2)
62
Opération PRODUIT CARTESIEN
SELECT FROM table1, table2 Exemple SELECT
FROM Etudiants, Epreuves
Facile ! Mais attention à son utilisation. Ce
n est pas une JOINTURE, ni une UNION !

• Exemple dutilisation - sémantiquement
• Expression de chaque gène dans tous les organes
  
• Expression Gene X Organe
• ( colonne de mesure d expression)

63
Principe d'écriture d'une requête
64
Exemple1
Remarque les clés primaires sont soulignées et
les clés étrangères sont marquées par
65
R2 NCommande Date CodeClient NomClient adrClient
TélClient
R1 NCommande Date CodeClient
CLIENT CodeClient NomClient, adrClient, TélClient
66
Exemple
Soient les deux tables (ou relations) suivantes
Chromosomes CodeGenome Gènes CodeChromosome,
CodeChromosome Taille, Nom_Numéro ...
Position ... Métabolisme ...
On désire obtenir tous les gènes impliqués dans
le métabolisme des lipides de l espèce E, et qui
sont compris entre la position n1 et n2 du
chromosome II
1

Remarque les clés primaires sont soulignées et
les clés étrangères sont marquées par
67
Le chromosome II du génome de l espèce E
R1 CodeGénome Espèce
INPUT
R2 CodeGenome CodeChromosome Espèce Taille, etc
CodeGénome CodeGénome et Nom_Numéro  II 
Chromosome CodeGenome CodeChromosome Taille,
Nom_Numéro, ...
Les gènes du chromosome II du génome de l espèce
E
Gènes CodeChromosome CodeGene Nom Métabolisme Posi
tion Taille
Etc etc Selection (lipides) puis projection
CodeChromosome CodeChromosome
68
Principe d'écriture d'une requête
Une même instruction SELECT (SQL) permet de
combiner Sélections, Projections, Jointures.
Exemple SELECT DISTINCT CLIENT.CodeClient,
NomClientFROM CLIENT, COMMANDEWHERE
CLIENT.CodeClientCOMMANDE.CodeClient AND
Date'10/06/97'
Remarque importante Si l'on ne précisait pas
CLIENT.CodeClient au niveau du SELECT, la
commande SQL ne pourrait pas s'exécuter. En effet
il y aurait ambiguïté sur le CodeClient à
projeter celui de la table CLIENT ou celui de
la table COMMANDE ?
69
Compléments Opération TRI
Foprmalisme R TRI(R0, att1À, att2Å, ...)
R1 TRI (CHAMPIGNONS, EspèceÅ, CatégorieÀ,
ConditionnementÀ)
Le tri s'effectue sur un ou plusieurs attributs,
dans l'ordre croissant ou décroissant. La
relation résultat a la même structure et le même
contenu que la relation de départ.
70
En langage SQL
SELECT attribut1, attribut2, attribut3, ...FROM
tableORDER BY attribut1 ASC, attribut2 DESC, ...

ASC par ordre croissant (Ascending) DESC par
ordre décroissant (Descending)
Exemple SELECT Espèce, Catégorie,
ConditionnementFROM ChampignonsORDER BY Espèce
DESC, Catégorie ASC, Conditionnement ASC
Remarque par défaut le tri se fait par ordre
croissant si l'on ne précise pas ASC ou DESC.
71
Attributs calculés et renommés
Atributs calculés RPROJECTION(R0, att1, att2,
att3, att4, att1att2, att3/att2)
Attributs renommés RPROJECTION(R0, att1, att2,
att3, att4, newatt1att1att2, newatt2att3/att2)
72
Exemple
73
Les Fonctions ensemblistes

• Ces fonctions sont utilisées dans les opérateurs
  
• calculer
• regrouper-et-calculer.

Les fonctions de calcul portent sur un ou
plusieurs groupes de n-uplets et évidemment sur
un attribut de type numérique.
74
Les Fonctions ensemblistes
La fonction de comptage Comptage() La fonction
de comptage donne le nombre de n-uplets d'un ou
de plusieurs groupes de n-uplets. Il n'est donc
pas nécessaire de préciser d'attribut.
75
Les Fonctions ensemblistes en SQL
Les fonctions de calcul SUM (attribut) total
des valeurs d'un attributAVG (attribut)
moyenne des valeurs d'un attributMIN (attribut)
plus petite valeur d'un attributMAX (attribut)
plus grande valeur d'un attribut
La fonction de comptage COUNT() nombre de
n-uplets COUNT(DISTINCT attribut) nombre de
valeurs différentes de l'attribut
76
SQL Syntaxe simplifiée de l'instruction SELECT
SELECT DISTINCT att1 , att2, att3,
... FROM Table1 , Table2, Table3, ...
WHERE conditions de sélection et/ou de
jointure
GROUP BY att1 , att2, ... HAVING conditions
de sélection sur les groupes de GROUP BY
ORDER BY att1 ASC DESC , att2 ASC DESC,
...
optionnel ou
77
SQL est un langage de Manipulation de données
relationnelles LMD-R

• Recherche de tuples vérifiant certains critères
• Insertion de tuples
• Suppression de tuples vérifiant certains
  critères
• Modification de tuples vérifiant certains
  critères

Ce langage nest pas utilisable à lui seul, il
doit aussi pouvoir être incorporable dans un
langage de programmation classique.
78
(No Transcript)
79
Exercice d'application n2
Soit le modèle relationnel suivant relatif à la
gestion des notes annuelles d'une promotion
d'étudiants
Remarque les clés primaires sont soulignées et
les clés étrangères sont marquées par
Questions 1 - Quel est le nombre total
d'étudiants ?
2 - Quelles sont, parmi l'ensemble des notes, la
note la plus haute et la note la plus basse ?
3 - Quelles sont les moyennes de chaque étudiant
dans chacune des matières ?
4 - Quelles sont les moyennes par matière ?
5 - Quelle est la moyenne générale de chaque
étudiant ?
6 - Quelle est la moyenne générale de la
promotion ?
7 - Quels sont les étudiants qui ont une moyenne
générale supérieure ou égale à la moyenne
générale de la promotion ?
80
Correction de l'exercice d'application n2
1 - Quel est le nombre total d'étudiants
?NCALCULER(ETUDIANT, Comptage()) 2 - Quelles
sont, parmi l'ensemble des notes, la note la plus
haute et la note la plus basse ?RCALCULER(EVALUE
R, Minimum(Note), Maximum(Note)) 3 - Quelles sont
les moyennes de chaque étudiant dans chacune des
matières ?R1REGROUPER_ET_CALCULER(EVALUER,
NEtudiant, CodeMat, MoyEtuMat Moyenne(Note))
R2JOINTURE(R1, MATIERE, MATIERE.CodeMatR1.CodeM
at) R3JOINTURE(R2, ETUDIANT, ETUDIANT.NEtudiant
R2.NEtudiant)MOYETUMATPROJECTION(R3,
NEtudiant, Nom, Prénom, LibelléMat, CoeffMat,
MoyEtuMat)
(faire un graphique)
81
4 - Quelles sont les moyennes par matière ?Idem
question 3 puis R4REGROUPER_ET_CALCULER(MOYETUM
AT, LibelléMat, Moyenne(MoyEtuMat))5 - Quelle
est la moyenne générale de chaque étudiant ?Idem
question 3 puis MGETUREGROUPER_ET_CALCULER(MOYE
TUMAT, NEtudiant, Nom, Prénom, MgEtu
Somme(MoyEtuMatCoeffMat)/Somme(CoeffMat))6 -
Quelle est la moyenne générale de la promotion
?Idem question 5 puis MGCALCULER(MGETU,
Moyenne(MgEtu)) 7 - Quels sont les étudiants qui
ont une moyenne générale supérieure ou égale à la
moyenne générale de la promotion ?idem question
5 et 6 puis RSELECTION(MGETU, MgEtugtMG)
82
Correction de l'exercice d'application n2
1 - Quel est le nombre total d'étudiants ?SELECT
COUNT() FROM ETUDIANT 2 - Quelles sont, parmi
l'ensemble des notes, la note la plus haute et la
note la plus basse ?SELECT MIN(Note), MAX(Note)
FROM EVALUER 3 - Quelles sont les moyennes de
chaque étudiant dans chacune des matières
? CREATE VIEW MOYETUMAT ASSELECT
ETUDIANT.NEtudiant, Nom, Prénom, LibelléMat,
CoeffMat, AVG(Note) AS MoyEtuMat
En SQL
FROM EVALUER, MATIERE, ETUDIANT
WHERE EVALUER.CodeMat MATIERE.CodeMatAND
EVALUER.NEtudiant ETUDIANT.NEtudiantGROUP BY
ETUDIANT.NEtudiant, Nom, Prénom, LibelléMat,
CoeffMat
83
Remarque la commande CREATE VIEW va permettre
de réutiliser le résultat de la requête
(notamment aux deux questions suivantes) comme
s'il s'agissait d'une nouvelle table (bien
qu'elle soit régénérée dynamiquement lors de son
utilisation).Sous Access, il ne faut pas
utiliser la commande CREATE VIEW mais seulement
enregistrer la requête. Il est alors possible de
s'en resservir comme une table. 4 - Quelles sont
les moyennes par matière ?Avec la vue MOYETUMAT
de la question 3 5 - Quelle est la moyenne
générale de chaque étudiant ?Avec la vue
MOYETUMAT de la question 3 CREATE VIEW MGETU AS
SELECT NEtudiant, Nom, Prénom,
SUM(MoyEtuMatCoeffMat)/SUM(CoeffMat) AS
MgEtuFROM MOYETUMAT GROUP BY NEtudiant, Nom,
Prénom
SELECT LibelléMat, AVG(MoyEtuMat) FROM MOYETUMAT
GROUP BY LibelléMat
84
6 - Quelle est la moyenne générale de la
promotion ? Avec la vue MGETU de la question 5
SELECT AVG(MgEtu) FROM MGETU 7 - Quels sont
les étudiants qui ont une moyenne générale
supérieure ou égale à la moyenne générale de la
promotion ? Avec la vue MGETU de la question 5
SELECT NEtudiant, Nom, Prénom, MgEtuFROM
MGETU WHERE MgEtu gt (SELECT AVG(MgEtu) FROM
MGETU)
85
Du monde Réel au SGBD
Retour à la modélisation conceptuelle
SYSTEMES DINFORMATIONS
Monde Réel
Modèle Conceptuel
Modèle Logique
Modèle Physique
SGBD
86
Modélisation Conceptuelle Objectif essentiel de
la modélisation Conceptuelle ? Définir les
informations pertinentes pour les applications
envisagées en mettant l'accent sur 1. La
structure (l'organisation) de ces
informations. 2. Le haut niveau d'abstraction
un schéma conceptuel doit être indépendant de
tout choix d'implémentation. En pratique, le
modèle conceptuel le plus utilisé est le modèle
Entité-Association, initialement proposé en 1976.
87
Le modèle Entité-Association Il se construit
par - Perception (entités? Associations?) -
Classification (mêmes entités? Mêmes
associations?) - Description (quelles
caractéristiques? quels attributs?) -
Abstraction (quels types? quelles classes?)
88
Le modèle Entité-Association Entité
Représentation d'un objet concret et abstrait
présent dans la réalité du monde et présentant
un intérêt pour la compréhension de cette
réalité. Une entité existe en tant que telle, a
une identité propre, c'est-à-dire qu'elle peut
être décrite et manipulée sans qu'il soit
nécessaire de connaître les autres entités de ce
réel. Exemple une personne, une voiture, une
salle, ...
89
Le modèle Entité-Association Attribut
Propriété ou caractéristique d'une entité. C'est
une information élémentaire dont la décomposition
ne présente aucun intérêt pour l'application.
Exemple nom, prénom, date de naissance,
... Identifiant ou Clé Attribut (ou ensemble
d'attributs) permettant d'identifier de manière
unique les occurrences de l'entité. Exemple
(nom, prénom). Souvent, on crée un identifiant
abstrait (un numéro par exemple).
90
Le modèle Entité-Association Association Lien
sémantique entre deux (ou plusieurs) entités. Une
association n'existe que par rapport aux entités
qu'elles relient. Une association n'est pas
autonome, contrairement à une entité. Cardinalité
d'une association Couple de valeurs m,n
(avec m lt n), traduisant les nombres minimum et
maximum d'occurrences d'associations auxquelles
peut participer une occurrence d'entité. Une
occurrence d'association est définie par une
occurrence de chacune des entités participantes.
91

• Modèle Entité-Association

Entités
0.n
2.n
Associations
Cardinalité 0.n pour lentité Protéines exprime
quune protéine peut être partenaire dans 0 ou
plusieurs complexes protéiques
92
Entités
Cardinalités
Cours
Professeur
Identifiant
id nom vol hor filière
1.n
0.n
nom prenom adresse grade
donne
Association
Attributs
Cardinalité 1.n pour lentité Professeur exprime
quun professeur donne (1) cours ou plusieurs (n)
cours. Cardinalité 0.n pour l entité Cours
exprime un cours est donné par aucun (0) ou
plusieurs professeurs.
93
Le modèle Entité association
94
Modèle de Données Définition Un modèle de
données est un ensemble de concepts et de règles
de composition de ces concepts permettant de
décrire les données.
95
Modèle Relationnel Le modèle de données proposé
dans le modèle relationnel consiste à percevoir
l'ensemble des données comme des tableaux
(Relations).
Nom de la relation
Attribut
Génome
Code Genome
Espèce
Nb_chromosomes
Arabidopsis_thaliana Yeast Drosophile Mouse Human
ZebraFish
ARB YST DRL MOU HUM ZBF
5 16 5 44 46 25
tuple n-uplet
96

• Modèle Relationnel Définitions
• Un domaine est un ensemble de valeurs.
• Exemple
• L'ensemble des entiers N.
• L'ensemble des booléens 0,1.
• L'ensemble des couleurs jaune, vert, gris, ....
• Un attribut prend ses valeurs dans un domaine.
• Plusieurs attributs peuvent appartenir à un même
  domaine.
• Un n-uplet (ou tuple) est une liste de n
  valeurs (v1, ,vn) où chaque valeur vi est la
  valeur d'un attribut Ai de domaine Di (vi ? Di).

97

• Modèle Relationnel Définitions
• Le Produit cartésien D1 ? ... ? Dn entre des
  domaines D1, , Dn est l'ensemble de tous les
  n-uplets possibles (v1, ... vn) où vi ? Di
• Une Relation définie sur les attributs A1, ...,
  An est un sous-ensemble du produit cartésien
  D1 ? ... ? Dn où
• D1,..., Dn sont les domaines respectifs de A1,
  ..., An.
• R est un ensemble de n-uplets.
• Une relation R est représentée sous forme d'une
  table.
• L'ordre des colonnes ou des lignes n'a pas
  d'importance.
• Les colonnes sont distinguées par les noms
  d'attributs et chaque ligne représente un
  élément de l'ensemble R (un n-uplet).
• Un attribut peut apparaître dans plusieurs
  relations.

98
Modèle Relationnel Définitions Le schéma
d'une relation R est défini par le nom de la
relation et la liste des attributs, avec pour
chaque attribut son domaine. Notation
R(A1D1, , AnDn) ou plus simplement
R(A1, , An) Exemple COURS(Id char(10),
Nomchar(20), VolHornum, Filierechar(30)) ou
COURS(Id, Nom, VolHor, Filiere) L'arité d'une
relation R est le nombre de ses attributs (nombre
de colonnes). Par exemple, la relation COURS est
d'arité 4.
99

• Modèle Relationnel Définitions
• Une Base de Données Relationnelle est un
  ensemble de relations.
• Le schéma logique d'une Base de Données
  Relationnelle est l'ensemble des schémas de ses
  relations.

100
Conception et Définition d'un Schéma
Relationnel La première étape de la
construction d'une base de données est la
définition du schéma logique de la base. Pour
une  bonne  définition du schéma logique, il
est nécessaire de concevoir au préalable un
schéma conceptuel décrivant de manière
structurée et formalisée les informations de
l'application pour laquelle on veut construire
la base de données. Des règles sont définies
pour le passage d'un schéma conceptuel (conçu
selon un modèle conceptuel choisi
Entité-Association, Merise, OMT, UML²...) vers un
schéma logique (dans le modèle de données choisi
relationnel, objets, )
101
Règles de passage du modèle Entité-Association au
modèle Relationnel

• Entité
• Chaque entité devient une relation (ou table).
• Chaque attribut de l'entité devient un attribut
  de la relation, y compris l'identifiant.
• Les attributs issus de l'identifiant
  constituent la clé de la relation.
• Remarque Pour les distinguer des autres
  attributs, on les souligne dans le schéma de la
  relation.
• Exemple
• PROFESSEUR(nom, Prenom, Adresse, Grade)
• COURS(Id, Nom, VolHor, Filiere)

102
Règles de passage du modèle Entité-Association au
modèle Relationnel

• Soit une association entre deux entités A et B.
• Chacune des entités A et B devient une relation,
  respectivement
• RA et RB. Puis, si ...
• Association x.1/x.n
• L'identifiant de B devient attribut
  supplémentaire de RA. Ce sera une
• clé étrangère.
• Association x.n/y.n
• On crée une relation RAB pour l'association.
• La clé de RAB est la concaténation des clés des
  relations
• RA et RB.
• Les attributs de l'association deviennent des
  attributs de RAB.

103
Règles de passage du modèle Entité-Association au
modèle Relationnel
Seance (Id,Id,Id,tarif)
104

• Modèle Relationnel Opérations
• Modifications
• Insertion Insérer un n-uplet dans une
  relation.
• Destruction Détruire un n-uplet dans une
  relation.
• Modification Modifier une ou plusieurs
  valeurs d'un attribut dans une relation.

105

• Modèle Relationnel Opérations
• Interrogations
• Rq Le résultat de l'interrogation d'une ou de
  plusieurs relations est une nouvelle relation.
• Cinq opérations de base pour exprimer toutes les
  requêtes
• Opérations unaires sélection, projection.
• Opérations binaires union, différence,
  produit cartésien.
• Autres opérations qui s'expriment en fonction
  des 5 autres fonctions de base jointure,
  intersection, division.

106
Exercices sur le modèle relationnel l On
souhaite créer une base de données concernant une
entreprise. Une première étude a mis en évidence
trois relations. Pour chacune des relations, la
clé est soulignée. EMPLOYE (NumEmp, Nom, Prénom,
Adresse, Téléphone, Qualification) SERVICE
(NomService, Responsable, Téléphone) PROJET
(NomProjet, DateDeb, DateFin, NumEmp) En
considérant les possibilités offertes par ce
schéma, répondre aux questions suivantes en
justifiant vos réponses par rapport au modèle
relationnel et par rapport à la sémantique
intuitive des relations Question 1 Un
employé peut il avoir plusieurs qualifications
? Question 2 Un employé peut il faire
plusieurs projets en même temps ? Question 3
Une personne peut elle être responsable de
plusieurs services ? Question 4 Un service
peut il avoir plusieurs responsables ?
TD de modélisation
107
Sur le contrôle de la cohérence lors dune
interrogation ou dun accès concurrent
la modif par user2 ne devrait pas être visible
par user1.
utilisation de l image d une variable pour
assurer la cohérence
108
Cohérence lors de plusieurs interrogations ou MAJ
Si l utilisateur ne veut pas que lon modifie la
table pendant une session de travail il peut la
verrouiller en mode partagé
LOCK TABLE nom_table IN SHARE MODE NOWAIT
NOWAIT spécifie que le process qui demande le
verrou n est pas mis en attente si celui-ci
n est pas disponible.
Pour s assurer l accès exclusif en modification
à une table, on peut verrouiller cette table en
mode EXCLUSIF
LOCK TABLE nom_table IN EXCLUSIVE MODE NOWAIT
La table n est accessible aux autres
utilisateurs quen lecture et ne peuvent plus la
verrouiller ni en mode exclusif, ni en mode
mise à jour partagé ni en mode partagé jusqu à
la fin de la transaction.
109
L'emploi de verrous implique que certaines
transactions soient mises en attente quand elles
ne peuvent obtenir un verrou. Pour que cette
attente ne soit pas infinie, on utilise souvent
une file d'attente FIFO, ce qui assure que toute
transaction passera à son tour, sauf s'il y a un
interblocage des transactions qui s'attendent
mutuellement. Exemple d'interblocage
temps Transaction A Transaction B t1 XFIND
E1 t2 XFIND E2 t3 XFIND E2 t4 XFIND
E1 La transaction A est mise en attente au
temps t3 et B au temps t4. Les deux transactions
s'attendent mutuellement.
110
Deux cas de figures de rupture de cohérence
M.A.J à partir dune valeur périmée
contrainte dintégrité A B
A 3 B 3
A6
A7
B4
B8
A7 B8
Avec une telle exécution, les données ne sont
pas mises à jour correctement, Ce qui rend A
B Une exécution séquentielle de T1 puis T2,
sauvegarde la cohérence
Incohérence survenue lorsquune transaction T1
exécute un calcul ou une MAJ à partir dune
valeur périmée de données, modifiée par une autre
transaction
111
(No Transcript)
112
Droits daccès aux tables
Les privilèges pouvant être accordés
SELECT INSERT UPDATE(col,) DELETE ALTER A
LL Un utilisateur ayant reçu un privilège avec
GRANT peut le transmettre à son tour.
Exemple GRANT SELECT ON emp TO douglas GRANT
SELECT,UPDATE ON emp TO PUBLIC
113
Bases de données avancées
En dépit de sa simplicité et de son élégance, le
modèle relationnel napporte pas une réponse
satisfaisante à tous les problèmes des
applications, tel que
Prendre en compte les objets de structure
complexe ainsi que les opérations qui leurs
sont associées (BD Orienté Objet)

• Les Objets ont
• une identité une adresse en mémoire de la
  machine
• des attributs comme couleur ou taille
• un état comme vert ou 50
• un comportement comme dessiner ou
  changer-couleur

114
Notion dhéritage
Illustration sur quelques modèles objet
Une classe est une représentation de Type
Abstrait de Données, une description générique
dobjets de même type. Un objet est une instance
dune classe. Les variables peuvent être à leur
tour des classes ou variables statiques, ou
instance de variables
115
Modèle orienté objet pour un gène
116
Classes et sous-classes
3 classes
Gene
3 descriptions
Protein-gene
RNA-gene
117
Modèle de données échantillons de cellules
d une expérience transcriptome MGED 1999
(hedeilberg)
118
Diverses technologies pour la conception de bases
de données
DBMS Orienté-objet
DBMS relationnel
DBMS hybride
Essais de standardisation
ASN.1,XML ...
119
Pourquoi utiliser la technologie ORIENTE-OBJET
(OO) ?
Puissance d expression de données complexes (nos
données bio en question!) Réutilisation données
et codes - modularité Requête concise et
intuitive sur les objets Une navigation facile de
données complexes Identifiant unique dun objet
Pourquoi ne pas utiliser la technologie OO ?
Relationnel DBMS simple, Relationnel DBMS
mature, Tout le monde a un Relationnel DBMS
! Standardisation SQL3
120
Autres bases de données avancées

• Prendre en compte des données peu structurées
  sons, texte, images, vidéo (BD multi-média)

• Faire le pont avec l intelligence artificielle
  permettre linférence de faits (nouvelles
  données) à partir des données de la base (BD
  déductives)

121
Entrepôt de BDs, insertion et réutilisation
flexible
Propriétés pertinentes

• Une exigence de haute performance
• données énormes, données complexes ...
• analyse complexe la réalisation dune seule
  tâche nécessite une intégration dune large gamme
  d objets complexes et hétérogènes

• intégration des données dintérêt à partir de
  sources externes en vue de construire un
  environnement intégré pour réaliser des analyses
  plus pointues (localement!).

122
Modèle Conceptuel

• Choisir une méthode de modélisation à la fois
  souple et efficace (permet la navigation,
  souplesse dimbrication,...

• Construire un premier modèle pour capturer les
  concepts clés (par exemple Génome - méthode
  OO?)

• Décrire ce modèle ( langage ? )

• Linstancier (sources?)

• Analyser ensuite (interfaces accessible à tous
  analyse privée de données génomiques, croisées
  avec des données expérimentales, phénotypiques,
  etc

123
Description du modèle

• Via un langage OO persistance (stockage)
• ex java
• décrire chaque concept
• décrire chaque relation entre concepts
• Instancier
• déterminer les sources d extraction
• sources fiables
• prévoir la réactualisation de la BD

124
accès à la base de données
125
Un cas de figure
Sources génomique
Données cliniques
Possible ?
Entrepôt biologique
Hypothèses ? Dautres investigations
? Expériences ?
126
Source Jef Wijsen
127
(No Transcript)
128
(No Transcript)
129
(No Transcript)
130
(No Transcript)
131
NON !
en M2 ! avec le Web Sémantique et les ontologies
de domaine.