cours BD

Introduction aux Bases de données
en Sciences de la Vie et en Santé.
UE : Conception de bases de données
Master1 - BIG
Master1 - MTIBH
Fouzia Moussouni-Marzolf
Un peu d’histoire …
Limites de l’utilisation des fichiers
L’utilisateur doit écrire
un programme souvent
complexe !!!
Application :
rigide,
longue,
coûteuse
Données :
redondantes,
peu fiables,
ponctuelles
Quelles sont mes données ?
Comment les structurer?
Comment y accéder ?
Comment créer un index ?
Comment les manipuler ?
#% »*’àç @é()$£ »&@!!!!
?
?
Les utilisateurs exigent de plus en plus :
•
•
•
•
•
des systèmes globaux
des systèmes cohérents
de ne pas écrire des programmes complexes (ou plutôt compliqués!)
d’avoir des réponses rapides aux questions qu’ils posent
avoir des données à jour, ...
décrire
modifier
BD
Exhaustive
non redondante
structurée
persistante
interroger
Outils
SGBD
Exemple de base de données
PPI : 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, leurs familles fonctionnelles,
… etc.
• Applications
– analyse des interactions, annotations fonctionnelles,
– aide à la compréhension de phénomènes biologiques
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 l’interaction Y (avec
SNF4)
La protéine X participe au complexe Z
L ’interaction Y est détectée dans l ’expérience E
BD sur les Interactions protéiques
• Des Modèles pour représenter ces objets
Objet ou Entité ainsi que leurs associations
Données (entités)
Protéines
N° Acc
Descrip
0:n
Interagit
Sequence
2:n
Complexe
Protéique
Fonction
Conformation
Associations
Cardinalité 0:n pour l´entité <Protéines> : exprime qu‘une protéine
peut être partenaire dans aucune (0) ou plusieurs complexes
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é 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.
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é.
Architecture d'un SGBD
Fonctionnalités du 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é.
Architecture d'un SGBD
Fonctionnalités du 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 d’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, ...).
Système de Gestion de Bases de Données
Objectifs
Transparence
besoins
questions
SGBD
applications
réponses
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.
Comment assurer ces objectifs ?
Les trois niveaux de descriptions définies par la norme ANSI/ SPARC
Niveau interne
Stockage
BD
Niveau conceptuel ou logique
Vues
.
.
.
Exemple de vues
Vues d’une base de données <université> :
•
•
•
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 : l´age est calculé à partir de la date de naissance.
La description des données est le résultat
de la conception d’une base de données
Choix d’un modèle
de données (relationnel, objet,
etc.)
Un modèle de données est un :
•
outil intellectuel pour comprendre
des données ou de la connaissance.
l’organisation logique
•
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 qu’il 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 décrites structurellement, on peut :
• les insérer
• les lire, afficher
• les modifier
• les détruire
Les composants d ’un SGBD
« quelque soit le modèle de données qu’il supporte »
1 Outils de description des données
2
(en gros des langages) :
- décrire la vision qu’a l ’utilisateur des données
- décrire le stockage physique des données.
Outils de manipulation des données :
- interroger,
- modifier,
- détruire,
- croiser
les données, de façon optimisée via :
- des langages de manipulation des données,
- des extensions de langages classiques.
pouvant être
classiques aussi
SQL, OQL
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 étudié ».
Retour sur l’histoire …
1960
Uniquement des systèmes de gestion de fichiers très sophistiqués
(Mais aussi compliqués ! et non adpaté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énient : 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.
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…
Base de données
hiérarchique
Patron
Cellule
Chef Chromosome
Sous-chef
Sous-chef
Gène
Sous-chef
Chef
Sous-chef
Sous-chef
Base de données réseau
Patron
Chef
Sous-chef
Sous-chef
Patron
Chef
Sous-chef
Sous-chef
Sous-chef
Base de données relationnelle
Une table ?
Plusieurs tables ?
Genome
Chromosome
Gene
CodeGenome
NomGénome
….
CodeChromosome
NumChromosome
….
CodeGénome
CodeGene
NomGene
….
CodeChromosome
Select
Select
NomGene
Cekejeuveu
From
From
TableGene
LaTableKeJeudi
Where Where
CodeChromosome
LaConditionKimeuplé
= HUM-10
Base de données objet
Personne
Transcris
épissé
Adresse
Taille
Salaire
Séquence
Lieu de travail
Patron
ARNm
Primes de responsabilité
Niveau d’expression
Voiture de fonction
condition
Chef
ARNt
Sous-chef
SnARN
Nom
: de fonction Vélo de fonction
Scooter
Fonction
:
Localisation
Ref chef Moléculaire
Ref patron
Conception de Base de données relationnelles,
Algèbre relationnelle et langage SQL
Bon nombre de logiciels
apparaissent tous les mois,
mais l ’algèbre relationnelle
a été inventée par E.F.
CODD en 1970
La conception d’une base de
données n’est pas un domaine
réservé aux informaticiens !
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 d’un SGBDR est loin d ’être aussi
facile à acquérir que celle d ’un logiciel de
traitement de texte par ex.
LES ETAPES DE CONSTRUCTION
Structurer les données de la base
Modèle conceptuel des données VS. tableaux de
données
Définir précisément les besoins
(i.e. pourquoi créer une base de données?)
Communication intensive entre les experts du
domaine d ’application et le concepteur de la
base de données.
Description de la Structure de la
base de données sous formes de
tableaux de données reliées par des
données clés.
Visualiser la réalité sous forme de tables de données reliées entre elles.
A une table est associée ce qu’on appelle une relation.
Ex: Génomes (CodeGenome,Espèce,Nb_chromosome)
• Tables
Génomes
Code
Genome
Espèce
Chromosomes
Nb_chromosomes
Code
chromosome
N°Chrom
Code
Genome
Taille
Relation
AutresFragments
Gènes
Code
Code
gene chromosome
Séquence ...
Code
Fragment
type
Début
fin
Code
chromosome
LES ETAPES
Modèle conceptuel de traitement
Traitement des besoins (enfin! )
Les besoins
Connaissance
/ Expertise
Analyse
Traitement
Requêtes
Applications
LES ETAPES
Les interfaces utilisateurs ou
L ’APPLICATION
Il s’agit de réaliser une application de consultation et de
mise à jour de la base de données.
A savoir :
Un premier principe d’ergonomie à connaître est qu’une 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
n’est pas parce que l’interface est graphique que les utilisateurs sauront ce qu’ils ignorent.
« Il ne faut pas présenter des données, il faut présenter des informations »
Algèbre Relationnelle et langage SQL
Illustration avec des études de cas
Conception facile
de n’importe quelle
requête aussi
complexe soit-elle
Maîtrise de
l’algèbre
relationnelle
Protéines
Une relation est
représentée
par une table
=
ensemble
de tuples
NIP
…
…
…
…
…
…
…
NOM
Fonction
……….
……….
……….
……….
……….
……….
……….
………..
………..
………..
………..
………..
………..
………..
Mise en œuvre
à l ’aide de
SQL
Opérations classiques
sur les ensembles :
 
Opérations propres
projection, sélection,
jointure, division
Gestion simplifiée d’un génome
génome
1
n
chromosome
Génome
Code
Genome
Clé primaire
Clé
étrangère
Chromosomes
Code
chromosome N°Chrom Taille
code
genome
1
I
29.6
ARB
2
II
19.6
HUM
3
III
23.3
4
IV
17.5
DRL
DRL
5
I
230.2
DRL
6
V
576.8
MOU
...
Espèce
Nb_chromosomes
ARB
Arabidopsis
_thaliana
5
YST
Yeast
16
DRL
Drosophile
5
MOU
Mouse
44
HUM
Human
46
ZBF
ZebraFish
25
n-uplets
attributs
Gènes
Code
Génome
Code
chromosome
Nom
Source
Séquence
ADN
Code Gene
HUM
1
IREG1
1
MOU
1
TRFr
2
DROZ
1
DMT1
3
8
TNFa
4
MOU_KO
Clé primaire
Clé
étrangère
Il existe deux grands types de liens
Identification des
objets de gestion :
Génomes, Gènes,...
Un - plusieurs
Plusieurs - Plusieurs
Chromosomes
Génomes
1
CodeGenome
Nom
Espèce
nbChromosomes
*
CodeChromosome
Numéro
Taille
CodeGénome
Autres Fragments
CodeFragment
Type
séquence
début
fin
CodeChromosome
CodeGenome
1
Gènes
*
Voilà le modèle !
et les règles de gestion
du domaine modélisé :
interviews,
étude des documents
manipulés, des fichiers,
...
Présence de
redondance !?
CodeGene
Nom-locus
Source
Sequence
Codechromosome
CodeGenome
*
Opérations propres à l ’algèbre relationnelle
Opération PROJECTION / Exemples Génomes
Formalisme : R = PROJECTION (R1, liste des attributs)
Espèce
NB chromosomes
CodeGenome
Homo sapiens
25
1
Mus musculus
22
2
S.Cerevisiae
17
3
DROSOPHILE.M
8
4
Une seule relation.
Pas de doublons.
Espèce
Homo sapiens
Mus musculus
S. Cerevisiae
Drosophile.M
R1 = PROJECTION (Génomes, Espèce)
Espèce
NB chromosomes
R2 = PROJECTION (Génomes, Espèce, NB chromosomes)
Homo sapiens
25
Mus musculus
22
S.Cerevisiae
17
DROSOPHILE.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.
Opération PROJECTION
SELECT liste d'attributs FROM table ;
SELECT DISTINCT
liste d'attributs FROM table
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.
Opération SELECTION / Exemple : Génome
Formalisme : R = SELECTION (R1, condition)
Espèce
Cet opérateur ne
porte que sur 1
relation.
NB chromosomes CodeGenome
Homo sapiens
25
1
Mus musculus
22
2
S.Cerevisiae
17
3
DROSOPHILE.M
8
4
Il permet de ne
retenir que les nuplets répondant à
une condition.
R3 = SELECTION(Génome,
Nb chromosomes pair)
Espèce
NB chromosomes
CodeGenome
Mus musculus
22
2
DROSOPHILE.M
8
4
Quels sont les gènes qui participent au codage de la même
protéine PROD (albumine par exemple) ?
Gènes
Code Gene
Nom
Source
Séquence
ADN
Code
chromosome
1
IREG1
1
2
TRFr
1
3
DMT1
1
4
TNFa
8
taille
Produit
gène ?
Prod
Prod
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 :
des opérateurs de comparaison : =, >, <, <=, >=, <>
des opérateurs logiques : AND, OR, NOT
des opérateurs : IN, BETWEEN, LIKE
Autres exemples :
Soit la table Gène ( CodeGene, nom, source, sequence, taille,
CodeChromosome, produit)
SELECT * FROM
FROMGène
GèneWHERE
WHEREtaille
tailleINBETWEEN
(2000, 2500,
2000
2575,
AND
2600,
30003000)
SELECT * FROM Gène WHERE produit LIKE '%albumine%'
sous Access : LIKE
"*albumine*"
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
CodeGenome
Espèce
NB chromosomes
1
Homo sapiens
25
2
Mus musculus
22
3
S.Cerevisiae
17
4
DROSOPHILE.M
8
CodeGenome N°Chrom
Code
Taille >
chromosome
300000
Cet opérateur porte sur 2
310000
II
1
relations ayant
au moins
un
attribut défini dans leIII même
350000
4
domaine
400000
1
2
3
4
IV
4
2
I
350405
4
V
5768001
5
R = JOINTURE (Genome,Chromosome,
Les n-uplets sont formés
Genome.CodeGenome=Chromosome.CodeGenome)
CodeGenome N°Chrom
Code
Taille chromosome
Espèce
par la concaténation des
n-uplets des relations
NB chromosomes
d'origine qui vérifient la
condition de jointure.
25
Espèce
1
II
310000
1
Homo sapiens
350000
2
DROSOPHILE.M
8
4
III
400000
3
8
4
IV
DROSOPHILE.M
4
2
350405
4
DROSOPHILE.M
8
I
V
576800
5
Mus Musculus
22
Homo sapiens
ProjectionDROSOPHILE.M
sur
Espèce sans
Mus Musculus
doublons
Opération JOINTURE en SQL
En SQL, il est possible d'enchaîner plusieurs
jointures dans la même instruction SELECT :
SELECT * FROM table1, table2, table3, ...
WHERE table1.attribut1=table2.attribut1 AND
table2.attribut2=table3.attribut2 AND ...;
Exemple :
Ou en utilisant des alias pour les noms des tables :
SELECT Espece FROM Genome A, Chromosome B
WHERE A.CodeGenome =B.CodeGenome and
B.taille >= 300000;
Question 2
Quels sont les chromosomes : (numéro, taille, …)
du génome de la Drosophile ?
Chromosomes
Génomes
Code
Genome
Code
Nom génome . . . Génome
Souris Fluo!
N°
Code
.
.
.
Chromosome
Chromosome
3
Drosophile.M
5
Sélection ‘ Drosophile.M ’
3
II
5
V
5
VI
...
1
...
2
...
3
Génomes
Code
Nom génome . . . Génome
Drosophile.M
Jointure
5
Nom
génome
Drosophile.M
Drosophile.M
Code
N°
Code
.
.
.
Genome Chromosome
Chromosome
5
V
5
VI
Requête SQL
Select * from Genome,Chromosome
where Genome.nom like ‘ Drosophile.M’
and Chromosomes.CodeGenome = Genomes.CodeGenome
...
...
2
3
Question 3
Quels sont les génomes pour
lesquels le gène X1 est présent ?
jointure1
jointure2
Gènes
Génomes
Chromosomes
Code
Nom génome . . . Génome
Code
Code
N°
Chromosome Chromosome . . . Genome
Souris Fluo!
Drosophile.M
Code
Nom du gène
Chromosome
3
5
10
II
5
18
V
3
Select genome.nom from Genome, Chromosome, Gene
where Gene.nom like ‘ X1 ’
10
X1
18
X1
.
.
Première jointure
Deuxième jointure
and Genes.CodeChromosome = Chromosomes.CodeChromosome
and Chromosomes.CodeGenome = Genomes.CodeGenome
Exercice d'application n°1
Soit le modèle relationnel suivant relatif à une base de données sur des
représentations musicales :
REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°repré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.
Donner la liste des noms des musiciens et des titres des
représentations auxquelles ils participent.
Donner la liste des titres des représentations, les lieux et
les tarifs pour la journée du 14/09/96.
34-
Correction de l'exercice d'application n°1
(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.n°représentation=Représentation.n°représentation)
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, date=14/09/96)
R2 = JOINTURE(R1, REPRESENTATION,
R1.n°représentation=Représentation.n°représentation)
R3 = PROJECTION(R2, titre_représentation, lieu, tarif)
Correction de l'exercice d'application n°1
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 REPRESENTATION
WHERE 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ésentation
FROM MUSICIEN, REPRESENTATION
WHERE MUSICIEN.n°représentation = REPRESENTATION.n°repré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, tarif
FROM REPRESENTATION, PROGRAMMER
WHERE PROGRAMMER.n°représentation=REPRESENTATION.n°représentation
AND date='14/06/96' ;
Opération DIVISION
Formalisme : R = DIVISION (R1, R2)
Un Exemple hors Bio
PARTICIPER
EPREUVE
Qui a participe à toutes
les épreuves
DIVISION (PARTICIPER,
EPREUVE)?
Athlète
Epreuve
Epreuve
Athlète
Dupont
200 m
200 m
Dupont
Durand
400 m
400 m
Dupont
400 m
110 m H
Martin
110 m H
Dupont
110 m H
Martin
200 m
Le dividende doit avoir au
moins une colonne de plus
que le diviseur
Tous les attributs du diviseur
doivent être des attributs du
dividende.
La concaténation
donnant un n-uplet
du dividende
Quels sont
les gènes
du à
"L'athlète
Dupont
participe
génome
quiépreuves"
sont présents
toutesY les
dans tous les chromosomes ?
… à vos idées! ...
Le dividende doit avoir
au moins une colonne
de plus que le diviseur
dividende
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
Tous les gènes du génome y
diviseur
Code
Chromosome
Nom du
gène
Tous les Chromosomes du génome Y
1
X1
10
X1
18
X1
1
II
5
10
x2
10
V
5
18
x2
18
VI
5
Tous_les_Gènes_de_5
Code
N°
Code
Chromosome Chromosome . . . Genome
Nom du
gène
X1
Tous_les_Chromosomes_de_5
La division
La concaténation
donnant un n-uplet
du dividende
Opération DIVISION
Attention ! Il n'existe pas en SQL d'équivalent direct à la 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 :
N=CALCULER(EPREUVE,
Comptage())
N=CALCULER(Tous_les_chromosomes_de_y,
Comptage())
R1=REGROUPER_ET_CALCULER(PARTICIPER,
Athlète, Nb:Comptage())
R1=REGROUPER_ET_CALCULER(Tous_les_gènes_de_y,
NomDuGène,
R2=SELECTION(R1,
Nb=N)
Nb:Comptage())
R3=PROJECTION(R2,
Athlète)
R2=SELECTION(R1, Nb=N)
R3=PROJECTION(R2, NomDuGène)
et
et en
en SQL
SQL ::
SELECT
NomDuGène,Count(*)
FROM
Tous_les_gènes_de_y
SELECT Athlète,
Count(*) FROM
PARTICIPER
GROUP
NomDuGène
GROUP BY
BY Athlète
HAVING
HAVING COUNT(*)
COUNT(*) =
=N
N;
Opération CALCULER
R = CALCULER (R0, fonction1, fonction2, ...)
ou N = CALCULER (R0, fonction)
Gènes
Code Gene
Nom
Source
Séquence
ADN
Code
chromosome
taille
1
IREG1
1
20000
2
TRFr
1
30000
3
DMT1
8
45000
4
TNFa
8
3000
R1=CALCULER(Gènes, Somme(taille))
Somme(taille)
98000
SELECT SUM(taille) FROM Gènes;
On désire obtenir
la taille totale des
Produit
zones codantes
gène ?
Prod1
Prod2
En SQL
SELECT
fonction1(attribut1),
fonction2(attribut2), ...
FROM table;
Opération REGROUPER_ET_CALCULER
R=REGROUPER_ET_CALCULER(R0, att1, att2, ..., fonction1, fonction2, ...)
On désire obtenir le total
de la zone codante par
chromosome
Gènes
Code Gene
Nom
Source
Séquence
ADN
Le regroupement s'effectue sur
un sous ensemble des attributs
de la relation R0.
Code
chromosome
taille
1
IREG1
1
20000
2
TRFr
1
30000
Produit
gène ?
Prod1
Sum (taille)
3
DMT1
8
45000
4
TNFa
8
3000
Prod2
R2=REGROUPER_ET_CALCULER
(Gènes,CodeChromosome,
Code chromosome
codante
1
50000
8
48000
codante : Somme(taille))
La relation résultat comportera autant
de lignes que de groupes de n-uplets,
les fonctions s'appliquant à chacun des
groupes séparément.
EN SQL
Select codeChromosome, SUM(taille) as codante
from Genes Group by CodeChromosome
Les opérations ensemblistes
Union
AUB
Intersection
Différence
Produit
AnB
A-B
AxB
!!
!!!
Opération UNION
Formalisme : R = UNION (R1, R2)
E1 : Enseignants élus au CA
n° enseignant
nom_enseignant
On désire obtenir
l'ensemble des enseignants
élus au CA ou
représentants syndicaux.
E2 : Enseignants représentants syndicaux
n°enseignant
nom_enseignant
1
DUPONT
1
DUPONT
3
DURAND
4
MARTIN
4
MARTIN
6
MICHEL
5
BERTRAND
R1=UNION (E1, E2)
n°enseignant
Les deux relations doivent avoir le
même nombre d'attributs définis dans
le même domaine. On parle de
relations ayant le même schéma.
La relation résultat possède les
attributs des relations d'origine et les
n-uplets de chacune, avec élimination
des doublons éventuels.
nom_enseignant
1
DUPONT
3
DURAND
4
MARTIN
5
BERTRAND
6
MICHEL
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 ?
Gènes
Clé
chromosome
Métabolisme
Nom du gène
Sélection
Fer
Description Fonctionnelle
1
X1
.blablabla Iron blabla
8
X1
.blabla cell cycle blabla
.
.
.
Sélection
Cycle ou lipide

Opération UNION
en langage SQL
SELECT liste d'attributs FROM table1
UNION
SELECT liste d'attributs FROM table 2 ;
ExempleExemple
:
:
SELECT NomGène FROM Gènes Where Description_Fonctionnelle like «%iron
SELECT n°enseignant, NomEnseignant FROM E1
homéostasie%»
UNION UNION
SELECT SELECT
NomGène FROM
Gènes Where
Description_Fonctionnelle
n°enseignant,
NomEnseignant
FROMlike
E2«%cell
cycle%»
Opération Intersection
Formalisme : R=INTERSECT (R1, R2)
E1 : Enseignants élus au CA
n° enseignant nom_enseignant
On désire obtenir
l'ensemble des enseignants
du CA qui sont
représentants syndicaux.
E2 : Enseignants représentants syndicaux
n°enseignant nom_enseignant
1
DUPONT
1
DUPONT
3
DURAND
4
MARTIN
4
MARTIN
6
MICHEL
5
BERTRAND
R1=INTERSECT (E1, E2)
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.
n°enseignant
nom_enseignant
1
DUPONT
4
MARTIN
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 d’organiser les gènes : catégoriser les gènes par
métabolisme et créer une table pour chaque métabolisme ?
FER!
CodeGène
1
3
4
5
Cycle cellulaire!
Produit/rôle ...
.
.
.
.
CodeGène
Produit/rôle ...
.
.
.
1
4
6
CodeGène

1
4
Produit/rôle
.
.
Opération INTERSECTION
SELECT attribut1, attribut2, ... FROM table1
INTERSECT
SELECT attribut1, attribut2, ... FROM table2 ;
OU
SELECT attribut1, attribut2, ... FROM table1
WHERE attribut1 IN (SELECT attribut1 FROM table2) ;
Exemple
Exemple: :
SELECT
SELECTCodeGène,
n°enseignant,
Produit
NomEnseignant
FROM FER FROM E1
INTERSECT
INTERSECT
SELECT
SELECTCodeGène,
n°enseignant,
Produit
NomEnseignant
FROM CycleFROM
;
E2 ;
ou
SELECT CodeGène,
n°enseignant,
Produit
NomEnseignant
FROM FERFROM E1
WHERE CodeGène
n°enseignant
ININ
(SELECT
(SELECT
CodeGène
n°enseignant
FROM
FROM
Cycle)
E2); ;
Opération Différence
On désire obtenir
l'ensemble des enseignants
du CA qui ne sont pas
représentants syndicaux.
Formalisme : R=DIFFERENCE (R1, R2)
E1 : Enseignants élus au CA
n° enseignant
E2 : Enseignants représentants syndicaux
nom_enseignant
n°enseignant
nom_enseignant
1
DUPONT
1
DUPONT
3
DURAND
4
MARTIN
4
MARTIN
6
MICHEL
5
BERTRAND
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)
R1=DIFFERENCE (E1, E2)
n°enseignant
nom_enseignant
3
DURAND
5
BERTRAND
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 ?
FER!
CodeGène
1
3
4
5
Cycle cellulaire!
Produit/rôle ...
.
.
.
.
CodeGène
Produit/rôle ...
.
.
.
1
4
6
CodeGène

3
5
Produit/rôle
.
.
Opération DIFFERENCE
SELECT attribut1, attribut2, ... FROM table1
EXCEPT
SELECT attribut1, attribut2, ... FROM table2 ;
ou
SELECT attribut1, attribut2, ... FROM table1
WHERE attribut1 NOT IN (SELECT attribut1 FROM table2) ;
Exemple
Exemple ::
SELECT
CodeGène,NomEnseignant
Produit FROM
FERE1
SELECT n°enseignant,
FROM
EXCEPT
EXCEPT
SELECT n°enseignant,
FROM
E2 ;;
SELECT
CodeGène,NomEnseignant
Produit FROM
Cycle
ou
ou
SELECT n°enseignant,
FROM
SELECT
CodeGène,NomEnseignant
Produit FROM
FERE1
WHERE n°enseignant NOT IN (SELECT n°enseignant FROM E2) ;
WHERE CodeGène NOT IN (SELECT CodeGène FROM Cycle) ;
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 l’instant !) ?
Gènes
Clé
chromosome
Sélection
Fer
Métabolisme_
Nom du gène
familleFonctionnelle_...
5-II
X1
.blablabla Iron blabla
.
X1
.blabla cell cycle blabla
.
.
.
Sélection
Cycle ou lipide



Opération PRODUIT CARTESIEN
Formalisme : R = PRODUIT (R1, R2)
Etudiants
Epreuves
n°étudiant
nom
libellé épreuve
101
DUPONT
Informatique
2
102
MARTIN
Mathématiques
3
Gestion financière
5
coefficient
Examen = PRODUIT (Etudiants, Epreuves)
n°étudiant
nom
libellé épreuve
coefficient
101
DUPONT
Informatique
2
101
DUPONT
Mathématiques
3
101
DUPONT
Gestion financière
5
102
MARTIN
Informatique
2
102
MARTIN
Mathématiques
3
102
MARTIN
Gestion financière
5
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 d’utilisation - sémantiquement :
•
•
Expression de chaque gène dans tous les organes
Expression = Gene X Organe
(+ colonne de mesure d ’expression)
Principe d'écriture d'une requête
Les interrogations portant sur une
base sont réalisées en enchaînant
plusieurs requêtes successives
critère 1
{atti}
au moins un
attribut
commun
Critère 2
{attj}
Exemple1
Soient les deux tables (ou relations) suivantes :
CLIENT
COMMANDE
CodeClient
N°Commande
NomClient,
AdrClient,
TélClient
Date
1
*
On désire obtenir le
code et le nom des
clients ayant
commandé le 10/06/97
???
CodeClient*
Remarque : les clés primaires sont soulignées et les clés
étrangères sont marquées par *
On désire obtenir le
code et le nom des
clients ayant
commandé le 10/06/97
???
COMMANDE
N°Commande
INPUT
Date
CodeClient*
R2
Date = 10/06/97
R1
N°Commande
N°Commande
Date
Date
CodeClient
CodeClient*
NomClient
CLIENT
adrClient
CodeClient
=
CodeClient
TélClient
CodeClient
CodeClient
NomClient
NomClient,
adrClient,
TélClient
OUTPUT
R3
CodeClient
NomClient
Exemple
Soient les deux tables (ou relations) suivantes :
Chromosomes
CodeGenome
Gènes
CodeChromosome,
CodeChromosome
Taille,
1
*
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
Remarque : les clés primaires sont soulignées et les clés
étrangères sont marquées par *
Génome
CodeGénome
R1
INPUT
CodeGénome
Nb_chromosome
Espèce
Taille
Espèce = « E »
Espèce
Le chromosome II du
génome de l ’espèce E
R2
CodeGenome
CodeChromosome
CodeGénome = CodeGénome
et Nom_Numéro = « II »
Espèce
Taille, etc
Chromosome
CodeGenome
CodeChromosome
Les gènes du
Taille,
chromosome II du
Nom_Numéro, ...
génome de l ’espèce E
R3
Gènes
CodeChromosome
CodeGene
CodeChromosome
Etc etc
Espèce, taille,
Nom, métabolisme
etc...
Selection (lipides)
puis projection
jointure
Nom
Métabolisme
CodeChromosome = CodeChromosome
Position
Taille
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, NomClient
FROM CLIENT, COMMANDE
WHERE CLIENT.CodeClient=COMMANDE.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 ?
Compléments : Opération TRI
Foprmalisme : R = TRI(R0, att1À, att2Å, ...)
Champignons
Espèce
Catégorie
Conditionnement
Rosé des prés
Conserve Bocal
Rosé des prés
Sec
Verrine
Coulemelle
Frais
Boîte
Rosé des prés
Sec
Sachet plastique
R1 = TRI (CHAMPIGNONS, EspèceÅ, CatégorieÀ, ConditionnementÀ)
Espèce
Catégorie
Conditionnement
Rosé des prés
Conserve Bocal
Rosé des prés
Sec
Sacher plastique
Rosé des prés
Sec
Verrine
Coulemelle
Frais
Boîte
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.
En langage SQL
SELECT attribut1, attribut2, attribut3, ...
FROM table
ORDER BY attribut1 ASC, attribut2 DESC, ... ;
ASC : par ordre croissant (Ascending)
DESC : par ordre décroissant (Descending)
Exemple :
SELECT Espèce, Catégorie, Conditionnement
FROM Champignons
ORDER 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.
Attributs calculés et renommés
Atributs calculés
R=PROJECTION(R0, att1, att2, att3, att4, att1*att2, att3/att2)
Le calcul est spécifié
lors d'une projection ou
lors de l'utilisation
d'une fonction.
Un attribut calculé est un
attribut dont les valeurs sont
obtenues par des opérations
arithmétiques portant sur
des attributs de la même
relation.
Attributs renommés
R=PROJECTION(R0, att1, att2, att3, att4, newatt1:att1*att2, newatt2:att3/att2)
Il est possible de
renommer n'importe quel
attribut en le faisant
précéder de son nouveau
nom suivi de ":".
Exemple
LIGNE_COMMANDE
N°BonCommande
CodeProduit
Quantité
PuHt
96008
A10
10
83
96008
B20
35
32
96009
A10
20
83
96010
A15
4
96010
B20
R
110
55 R=PROJECTION(LIGNE_CO
32
MMANDE,N°BonCommande,
CodeProduit,Montant:Quanti
té*PuHt)
N°BonCommande
CodeProduit
Montant
96008
A10
830
96008
B20
1120
96009
A10
1660
96010
A15
440
96010
B20
1760
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.
Somme(attribut) : total des valeurs d'un attribut
Moyenne(attribut) : moyenne des valeurs d'un attribut
Minimum(attribut) : plus petite valeur d'un attribut
Maximum(attribut) : plus grande valeur d'un attribut
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.
Les Fonctions ensemblistes : en SQL
Les fonctions de calcul
SUM (attribut) : total des valeurs d'un attribut
AVG (attribut) : moyenne des valeurs d'un attribut
MIN (attribut) : plus petite valeur d'un attribut
MAX (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
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
SQL est un langage de Manipulation de
données relationnelles
LMD-R
Assertionnel - complet
Logique des prédicats d ’ordre 1
Spécifier sans dire comment y accéder
Opérations :
• 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 n’est pas utilisable à lui seul, il doit aussi
pouvoir être incorporable dans un langage de
programmation classique.
Le langage SQL est un langage normalisé.
C’est à la fois :
 un langage d'interrogation de données (LID) : SELECT;
 un langage de manipulation de données (LMD) : UPDATE, INSERT, DELETE;
 un langage de définition des données (LDD) : ALTER, CREATE, DROP;
 un langage de contrôle des données et des utilisateurs (LCD) : GRANT, REVOKE.
Exercice d'application n°2
Soit le modèle relationnel suivant relatif à la gestion des notes annuelles
d'une promotion d'étudiants :
1
ETUDIANT(N°Etudiant, Nom, Prénom)
MATIERE(CodeMat, LibelléMat, CoeffMat)
EVALUER(N°Etudiant*, CodeMat*, Date, Note)
Etudiant
*
Evaluer
Matière
1
*
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 ?
Correction de l'exercice d'application n°2
1 - Quel est le nombre total d'étudiants ?
N=CALCULER(ETUDIANT, Comptage())
2 - Quelles sont, parmi l'ensemble des notes, la note la plus
haute et la note la plus basse ?
R=CALCULER(EVALUER, Minimum(Note), Maximum(Note))
3 - Quelles sont les moyennes de chaque étudiant dans
(faire un graphique)
chacune des matières ?
R1=REGROUPER_ET_CALCULER(EVALUER, N°Etudiant, CodeMat,
MoyEtuMat : Moyenne(Note))
R2=JOINTURE(R1, MATIERE, MATIERE.CodeMat=R1.CodeMat)
R3=JOINTURE(R2, ETUDIANT, ETUDIANT.N°Etudiant=R2.N°Etudiant)
MOYETUMAT=PROJECTION(R3, N°Etudiant, Nom, Prénom, LibelléMat,
CoeffMat, MoyEtuMat)
4 - Quelles sont les moyennes par matière ?
Idem question 3 puis :
R4=REGROUPER_ET_CALCULER(MOYETUMAT, LibelléMat,
Moyenne(MoyEtuMat))
5 - Quelle est la moyenne générale de chaque étudiant ?
Idem question 3 puis :
MGETU=REGROUPER_ET_CALCULER(MOYETUMAT, N°Etudiant, Nom,
Prénom, MgEtu : Somme(MoyEtuMat*CoeffMat)/Somme(CoeffMat))
6 - Quelle est la moyenne générale de la promotion ?
Idem question 5 puis :
MG=CALCULER(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 :
R=SELECTION(MGETU, MgEtu>=MG)
Correction de l'exercice d'application n°2
1 - Quel est le nombre total d'étudiants ?
En SQL
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 AS
SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat,
CoeffMat, AVG(Note) AS MoyEtuMat
FROM EVALUER, MATIERE, ETUDIANT
WHERE EVALUER.CodeMat = MATIERE.CodeMat
AND EVALUER.N°Etudiant = ETUDIANT.N°Etudiant
GROUP BY ETUDIANT.N°Etudiant, Nom, Prénom,
LibelléMat, CoeffMat;
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 :
SELECT LibelléMat, AVG(MoyEtuMat) FROM
MOYETUMAT GROUP BY LibelléMat ;
5 - Quelle est la moyenne générale de chaque étudiant ?
Avec la vue MOYETUMAT de la question 3 :
CREATE VIEW MGETU AS SELECT N°Etudiant, Nom, Prénom,
SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT GROUP BY N°Etudiant, Nom, Prénom ;
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 N°Etudiant, Nom, Prénom, MgEtu
FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;
Du monde Réel au SGBD
SYSTEMES D’INFORMATIONS
Monde Réel
Modèle Conceptuel
Modèle Logique
Modèle Physique
SGBD
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.
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?)
Le modèle Entité-Association
Entité : Représentation d'un objet concret présent dans la
réalité du monde et présentant un intérêt pour la
compréhension de cette réalité (phase d’abstraction).
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, ...
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).
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 < 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.
Modèle Entité-Association
Entités
Protéines
N° Acc
Descrip
0.n
est-partenaire
Sequence
2.n
Complexe
Protéique
Fonction
Conformation
Associations
Cardinalité 0.n pour l´entité Protéines exprime qu‘une protéine peut
être partenaire dans 0 ou plusieurs complexes protéiques
Entités
Cardinalités
Cours
Professeur
# nom
prenom
adresse
grade
Identifiant
1.n
donne
0.n
# id
nom
vol hor
filière
Association
Attributs
Cardinalité 1.n pour l´entité Professeur exprime qu‘un 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.
Le modèle Entité association
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.
Modèle Relationnel
Le modèle de données proposé dans le modèle relationnel
consiste à percevoir l'ensemble des données comme des
tables (Relations).
Nom de la relation
Attribut
Génome
Code
Genome
Espèce
Nb_chromosomes
ARB
Arabidopsis
_thaliana
5
YST
Yeast
16
DRL
Drosophile
5
MOU
Mouse
44
HUM
Human
46
ZBF
ZebraFish
25
tuple
n-uplet
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).
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 nuplet).
• Un attribut peut apparaître dans plusieurs relations.
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(A1:D1, … , An:Dn)
ou plus simplement :
R(A1, … , An)
Exemple :
COURS(Id: char(10), Nom:char(20), VolHor:num, Filiere:char(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.
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.
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, …)
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)
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.
1.1
1.n
asso
RA
Espèce
Entries
Association x.n/y.n :
Clé espèce
• 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.
RA
1.n
asso
RB
1.n
RB
Références
Entries
ac
coderef
ref_num
Règles de passage du modèle Entité-Association au
modèle Relationnel
Seance (Id,Id,Id,tarif)
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.
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.
Petit exercice - 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
Sur le contrôle de la cohérence lors d’une
interrogation ou d’un accès concurrent
Instant t0 :
Select attribut X
FROM TABLE
WHERE condition
X=a
User 1
Instant t1 :
Update attribut X
FROM TABLE
t0
t1
X := b
X=b
X=a
commit
commit
User 2
Cohérence lors de plusieurs
interrogations ou MAJ
Si l ’utilisateur ne veut pas que l’on 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 qu’en 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.
L'emploi de verrous implique que certaines transactions soient mises en attente
quand elles ne peuvent pas 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 (ou DEADLOCK) des transactions qui s'attendent mutuellement.
Exemple d'interblocage :
temps
Transaction A
t1
t2
t3
t4
XFIND E1
Transaction B
XFIND E2
XFIND E2
XFIND E1
La transaction A est mise en attente au temps t3 et B au temps t4. Les deux
transactions s'attendent mutuellement.
Deux cas de figures de rupture de cohérence
M.A.J à partir d’une valeur périmée
A est un objet de la base de données
contrainte d’intégrité : A = B
A et B sont les objets de la base de
données
A=3;B=3;
T1
T2
1 : a : = A;
A=6
2: b := A;
1 : A := A*2;
3: b := b+1;
B=8
4: A := b;
5: a := 2*a;
6: A := a;
T2
T1
valeur de A déjà
périmée
Incohérence survenue lorsqu’une
transaction T1 exécute un calcul ou une
MAJ à partir d’une valeur périmée de
données, modifiée par une autre
transaction
2: A := A+1;
A=7
3: B := B+1;
B=4
4: B := B*2;
A=7 ; B=8
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
Dans tout SGBD on trouve un composant chargé du
contrôle de l ’accès concurrent aux données.
Il doit être tel que chaque utilisateur obtienne des
données cohérentes en cas de mises à jour simultanées
de la base.
Droits d’accès aux tables
La protection des objets d’une BD est décentralisée : c’est le créateur d’un objet qui
possède tous les droits de lecture, de modif, de suppression, …
Les autres utilisateurs n ’ont aucun droit d ’accès à cet objet à moins que le créateur
ne les leur accorde explicitement par une commande GRANT
GRANT privilège ON (nom_table/nom_vue) to nom_utilisateur
Les privilèges pouvant être accordés :
SELECT
INSERT
UPDATE(col,…)
DELETE
ALTER
ALL
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
Bases de données avancées
En dépit de sa simplicité et de son élégance, le modèle relationnel
n’apporte 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 (dans les SGBDO) ont :
 une identité propre : 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
Notion d’héritage
Illustration
sur quelques
modèles objet
…
Une classe est une représentation de Type Abstrait de Données, une description
générique d’objets de même type.
Un objet est une instance d’une classe.
Les variables peuvent être à leur tour des classes ou variables statiques, ou
instance de variables
Modèle orienté objet pour un gène
GENE
1
compose
* est composé
FRAGMENT
is a
TRANSCRIBED
FRAGMENT
NON TRANSCRIBED/
REGULATOR
is a
compose
SPLICED
*
TRANSCRIPT
*
est composé
is a
mRNA
snRNA
SPLICED
TRANSCRIPT
COMPONENT
(EXON)
tRNA
INTRON
PROMOTOR
rRNA
1
1
ORF
1
1
PRIMARY
POLYPEPTIDE
1
*
PROTEIN
TERMINATOR
Classes et sous-classes
3 classes
Gene
Protein-gene
RNA-gene
3 descriptions
Modèle de données : échantillons de cellules d ’une
expérience transcriptome : MGED 1999 (hedeilberg)
Diverses technologies pour la conception de bases de données
DBMS Orienté-objet
DBMS relationnel
DBMS hybride
Essais de standardisation
ASN.1,XML ...
Pourquoi utiliser la technologie ORIENTE-OBJET (OO) ?
Puissance d ’expression de données complexes
Réutilisation : données et codes - modularité
Requête concise et intuitive sur les objets
Une navigation facile de données complexes
Identifiant unique d’un objet
(nos données bio en question!)
Pourquoi ne pas utiliser la technologie OO ?
Relationnel DBMS simple,
Relationnel DBMS mature,
Tout le monde a un Relationnel DBMS !
Standardisation
SQL3
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
l’inférence de faits (nouvelles données) à partir des
données de la base (BD déductives) – comme
DATALOG
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 d’une seule tâche
nécessite une intégration d’une large gamme d ’objets
complexes et hétérogènes …
•
intégration des données d’intérêt à partir de sources
externes en vue de construire un environnement
intégré pour réaliser des analyses plus pointues
(localement!).
• Navigation entre objets
INTERFACE
accès à la
base de
données
• requêtes déclaratives ou complexes
• analyses pointues de données
Entrepôt Biologique
CHARGEUR / SOURCES
Un cas de figure …
Sources
génomique
Outils
Possible ?
Données
cliniques
Intégration
Données
expérimentales
Micro Array
Macro Array SAGE ...
Entrepôt
biologique
Analyse
Hypothèses ?
D’autres investigations ?
Expériences ?
Source : Jef Wijsen
NON !
en M2 ! avec le Web Sémantique
et les ontologies de domaine.