2 - Maximilien Charlier

Université de Mons
Faculté des sciences
Département d’Informatique
Service des Systèmes d’Information
Réalisation d’un outil pour l’analyse
et la normalisation de bases de données
Auteurs :
Jannou Brohée
Julien Delplanque
Année académique 2013-2014
Table des matières
1
2
3
4
5
6
7
8
Introduction . . . . . . . . . . . . . . . . . . . . . . . . .
1.1
But du projet . . . . . . . . . . . . . . . . . . . .
1.2
Aperçu rapide du résultat . . . . . . . . . . . . .
Présentation générale de l’outil . . . . . . . . . . . . . .
2.1
Aperçu de l’organisation projet . . . . . . . . . .
2.2
Lecture/écriture des dépendances fonctionnelles .
2.3
Vérification des dépendances fonctionnelles . . .
2.4
Identification des clés/superclés . . . . . . . . . .
2.5
Vérification des normes 3NF & BCNF . . . . . .
2.6
Interface utilisateur . . . . . . . . . . . . . . . . .
Gestion des sauvegardes . . . . . . . . . . . . . . . . . .
Gestion du paramétrage de l’application . . . . . . . . .
Description des notions théoriques et algorithmes utilisés
5.1
Les notions théoriques . . . . . . . . . . . . . . .
5.2
Les algorithmes . . . . . . . . . . . . . . . . . . .
Problèmes rencontrés et solutions . . . . . . . . . . . . .
Répartition du travail . . . . . . . . . . . . . . . . . . .
Sources et remerciements . . . . . . . . . . . . . . . . .
1
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2
2
2
3
3
3
4
4
4
4
5
6
7
7
8
11
11
11
1
1.1
Introduction
But du projet
Le but principal de ce projet est d’appliquer les notions vues aux cours théoriques et pratiques
(MySQL) de Base de Données I. L’outil développé permet d’effectuer diverses opérations sur
les dépendances fonctionnelles présentes dans une base de données analysée. Parmi celles-ci,
proposer à l’utilisateur d’ajouter des dépendances fonctionnelles, les modifier voir les supprimer,
vérifier si celles-ci sont vérifiées, vérifier si un schéma est en 3NF ou en BCNF ou encore exporter
un schéma en 3NF.
1.2
Aperçu rapide du résultat
Le bouton "Select table" permet d’afficher les dépendances fonctionnelles, les attributs, les
superclés et les clés de la table selectionnée dans la liste Tables.
Le label "3NF" est en vert et "BCNF" est en rouge car la table selectionnée respecte le
norme 3NF mais pas la norme BCNF. La couleur de ces labels donne donc comme information
le respect ou non des normes 3NF et BCNF.
2
2
Présentation générale de l’outil
2.1
Aperçu de l’organisation projet
Le projet est consitué de cinq couches listées ci-dessous dans l’ordre de leurs importances :
• Lecture/écriture des dépendances fonctionnelles
· Lire la relation FuncDep.
· Ajouter des dépendances fonctionnelles à la relation FuncDep.
· Supprimer des dépendances fonctionnelles de la relation FuncDep.
· Modifier des dépendances fonctionnelles de la relation FuncDep.
• Vérification des dépendances fonctionnelles
· Détecter les dépendances fonctionnelles non satisfaites.
· Distinguer les dépendances fonctionnelles qui sont une conséquence logique d’autres
dépendances fonctionnelles.
· Supprimer les dépendances fonctionnelles inutiles, incohérentes, non satisfaites ou
concernant des relations ou des attributs n’existant pas.
• Identification des clés/superclés
· Identifier les clés.
· Identifier les superclés.
• Vérification des normes 3NF & BCNF
· Vérifier si une relation est en 3NF.
· Vérifier si une relation est en BCNF.
· Exporter une base de données au format 3NF.
• Interface utilisateur
· Interface graphique.
2.2
Lecture/écriture des dépendances fonctionnelles
Cette couche est la première qui a été implémentée. C’est le noyau de l’application, elle
contient les fonctions basiques indispensables à toutes les couches supérieures. Voici la liste
exhaustive de celles-ci :
• La classe Funcdep : Cette classe représente une dépendance fonctionnelle. Elle contient
des outils basiques pour manipuler une dépendance fonctionnelle.
· Surcharge de la méthode __init__ : À l’initialisation, trois variables sont créées,
self.table : le nom de la table concernée par la dépendance fontionnelle, self.lhs et
self.rhs : respectivement les attributs de gauche et droite de la dépendance fonctionnelle.
· Surcharge de la méthode __str__ : Une dépendance fonctionelle est représentée
sous forme de string comme "nom_table attribut1 , ..., attributn ->attribut1 , ..., attributn ".
· Surcharge de la méthode __eq__ : Deux dépendances fonctionnelles sont égales
ssi leurs noms de tables sont égaux et leurs attributs de gauche et droite sont égaux
deux à deux.
· La méthode generate_unique : Cette méthode génère les dépendances fonctionnelles singulières à partir d’une dépendance fonctionnelle quelconque.
· La méthode format_db : Cette méthode formate la dépendance fonctionelle afin de
pouvoir introduire le résultat de cette méthode directement dans une requête SQL.
• La classe FdReader :
· La méthode get_table_list : Cette méthode retourne la liste des tables présentes
dans la base de données.
3
· La méthode get_attribute_list : Cette méthode retourne les attributs de la table
dont le nom est passé en paramètre.
· La méthode add_functional_dependency : Cette méthode ajoute la dépendance
fonctionnelle passée en paramètre à la table dont le nom est passé en paramètre.
· La méthode remove_functional_dependency : Cette méthode supprime la dépendance fonctionnelle passée en paramètre de la table dont le nom est passé en
paramètre.
· La méthode modify_functional_dependency : Cette méthode remplace la dépendance fonctionnelle passée en paramètre de la table dont le nom est passé en
paramètre par la seconde dépendance fonctionnelle passée en paramètre.
· La méthode get_functional_dependencies : Cette méthode retourne toutes les
dépendances fonctionnelles de la table dont le nom est passé en paramètre.
2.3
Vérification des dépendances fonctionnelles
• La classe FdReader :
· La méthode is_satisfied : Retourne vrai si la dépendance fonctionnelle passée en
paramètre est vérifiée, faux sinon.
· La méthode get_not_satisfied : Retourne les tuples qui ne satisfont pas la dépendance fonctionnelle passée en paramètre.
· La méthode closure : Calcule la fermeture pour les dépendances fonctionnelles de la
table dont le nom est passé en paramètre.
· La méthode linear_closure : Idem que closure mais avec une complexité linéaire.
· La méthode member : Retourne vrai si la dépendance fonctionnelle passée en paramètre est une conséquence logique des dépendances fonctionnelles de la table concernée.
2.4
Identification des clés/superclés
• La classe FdReader :
· La méthode get_superkeys : Retourne les superclés d’une table dont le nom est
passé en paramètre.
· La méthode get_keys : Retourne les clés d’une table dont le nom est passé en
paramètre.
2.5
Vérification des normes 3NF & BCNF
• La classe FdReader :
· La méthode is_3nf : Retourne vrai si la table dont le nom est passé en paramètre
respecte la norme 3NF.
· La méthode is_bcnf : Retourne vrai si la table dont le nom est passé en paramètre
respecte la norme BCNF.
· La méthode export_to_3nf : Cette méthode exporte la table dont le nom est passé
en paramètre en 3NF dans la base de données dont le nom est passé en paramètre. Si
la base de données n’existe pas, celle-ci est créée.
2.6
Interface utilisateur
Pour la couche d’interface utilsateur, voici simplement la liste des classes utilisées.
• La classe MainFrame : Cette classe représente la fenêtre principale de l’application. Elle
hérite de la classe QMainWindow du module QtGui de la librairie PyQt4.
4
• La classe MainWidget : Cette classe représente le widget principal de l’application, c’est
dans celle-ci qu’une grande partie des informations sont représentées à l’utilisateur. Elle
hérite de la classe QWidget du module QtGui de la librairie PyQt4.
• La classe Popup : Cette classe représente le popup qui permet d’ajouter ou de modifier
une dépendance fonctionnelle. Elle hérite de la classe QDialog du module QtGui de la
librairie PyQt4.
• La classe Configuration : Cette classe représente le popup de configuration. C’est en
utilisant ce popup que l’utilisateur va configurer l’application. Elle hérite de la classe
QDialog du module QtGui de la librairie PyQt4.
• La classe ExportPopup : Cette classe représente le popup qui permet l’exportation
d’une base de données en 3NF. Elle hérite de la classe QDialog du module QtGui de la
librairie PyQt4.
3
Gestion des sauvegardes
Lorsque le paramètre "Modify directly the database" est déselectionné, une base de données
temporaire est créée simplement en demandant au système d’exploitation de faire une copie de
la base de données selectionnée par l’utilisateur. Les modifications sont faites sur cette base de
données qui diffère de l’originale uniquement de par son nom de fichier
Lorsque l’utilisateur appuie sur le bouton de sauvegarde, l’application demande au système
d’exploitation de faire une copie de la base de données temporaire vers la base de données originale. Cette action de copie écrase donc la base de données originale. Après une sauvegarde,
l’application continue le travail sur la base de données temporaire jusqu’à la prochaine sauvegarde et ainsi de suite.
Lorsque le paramètre "Modify directly the database" est selectionné, toutes les modifications
sont effectuées directement sur la base de données. Il n’y a donc aucun moyen de revenir en arrière en cas d’erreur.
Cette organisation des sauvegardes découle directement d’une réflexion sur les problèmes
que d’autres organisations peuvent poser. Nous avions en premier lieu pensé utiliser une base de
données en RAM qui serait copiée sur le disque uniquement lorsque l’utilisateur sauvegarde. Le
problème de cette approche est que s’il y a un arrêt de l’application ou de l’ordinateur, la base
de données est perdue. Les raisons pour lesquelles nous copions entièrement la base de données
et pas uniquement la table "FuncDep" dans une une base de données sont que : tout d’abord
l’approche est plus simple et puisque la copie est effectuée par l’OS, celle-ci est efficace. Ensuite,
si par la suite l’application est étendue pour pouvoir modifier les données dans la base donnée
afin de satisfaire des dépendances fonctionnelles, il sera dès lors plus judicieux de travailler sur
une base de données temporaire et de sauver les modifications à la demande de l’utilisateur.
5
4
Gestion du paramétrage de l’application
L’application peut être paramétrée via l’interface disponible dans l’application, pour y accéder il suffit de cliquer sur le bouton prévu à cet effet dans la barre d’outils.
Lors du premier démarrage de l’application, un fichier de configuration est créé et les paramètre par défaut y sont écrits. Par défaut, les deux options "Modify directly the database" et
"Allow to add logical consequencies" sont réglées au mode désactivé.
Si l’application a déjà été configurée auparavant, le fichier de configuration est lu et les checkboxes sont mises en l’état reflétant la configuration.
Lorsque que l’on modifie les paramètres (via les checkboxes) lorsque l’on clique sur le bouton
"Cancel", les paramètres sont ignorés et les checkboxes sont remises de façon à refléter le fichier
de configuration. Si on clique sur le bouton "Save", les modifications sont écrites dans le fichier
de configuration et les checkboxes sont laissées en l’état puisque qu’elles reflètent déjà le fichier
de configuration.
Nous avons décider de restreindre les moments où l’on peut configurer l’application par
soucis de sécurité. Il est possible de configurer l’application uniquement lorsque aucune base de
données n’est ouverte. Ainsi, pour configurer l’application, il suffit de cliquer sur l’icône du balai
(en ayant eventuellement sauvegardé les changements auparavant) afin de remettre l’application
dans son état original et ensuite modifier la configuration.
6
5
5.1
Description des notions théoriques et algorithmes utilisés
Les notions théoriques
• Dépendance fonctionnelle :
Soit U un ensemble d’attributs,
Une dépendance fonctionnelle (DF) sur U est une expression X → Y avec X, Y ⊆ U .
Une relation R sur U satisfait la DF X → Y , dénotée par R |= X → Y , ssi ∀t1 , t2 ∈ R,
si t1 [X] = t2 [X] alors t1 [Y ] = t2 [Y ].
• Dépendance fonctionnelle singulière :
Une DF X → Y est dite singulière ssi |Y | = 1.
• Conséquence logique :
Soit Σ un ensemble de DF sur l’ensemble U d’attributs, et X → Y une DF sur U .
On dit que X → Y est une conséquence logique de Σ, dénotée par Σ |= X → Y , ssi pour
toutes relations R sur U , si R |= Σ alors R |= X → Y .
• Superclé :
Une superclé pour un schéma (U, Σ) est un ensemble X ⊆ U tel que Σ |= X → U .
• Clé :
Une clé pour un schéma (U, Σ) est un ensemble X ⊆ U tel que :
· Σ |= X → U
· ∀X 0 ⊂ X, Σ |= X 0 → U est faux.
Une clé est donc une superclé minimale par rapport à ⊆.
• Fermeture :
La fermeture d’un ensemble d’attributs X représente l’ensemble des attributs de R qui
peuvent être déduits de X à partir d’une famille de dépendances fonctionnelles en appliquant les axiomes d’Armstrong (les axiomes d’Armstrong sont des propriétés qui permettent de déduire les conséquences logiques nous n’en parlons pas dans ce rapport car
ils ne sont pas utiles dans les algorithmes puisque inefficaces au niveau de la complexité).
• 3NF :
Un schéma (U, Σ) est en 3NF ssi pour toutes DF singulière X → A sur U telle que
Σ |= X → A, X est une superclé pour (U, Σ) ou l’attribut A fait partie d’une clé quelconque pour (U, Σ).
• BCNF :
Un schéma (U, Σ) est en 3NF ssi pour toutes DF singulière X → A sur U telle que
Σ |= X → A, X est une superclé pour (U, Σ).
7
5.2
Les algorithmes
• Calcul de la fermeture d’un ensemble d’attributs :
· Algorithme naïf :
1 def c l o s u r e ( s e l f , table_name , a t t r i b u t e _ s e t ) :
2
f u n c d e p _ l i s t = s e l f . g e t _ f u n c t i o n a l _ d e p e n d e n c i e s ( table_name )
3
old_dep = set ( )
4
new_dep = set ( copy . deepcopy ( a t t r i b u t e _ s e t ) )
5
while old_dep != new_dep :
6
old_dep = copy . deepcopy ( new_dep )
7
f o r funcdep in f u n c d e p _ l i s t :
8
i f new_dep . i s s u p e r s e t ( set ( funcdep . l h s ) ) :
9
new_dep = new_dep . union ( set ( funcdep . r h s ) )
10
return l i s t ( new_dep )
(Cet algorithme est une implémentation de l’algorithme écrit en pseudo-code à la page
64 du livre cité dans les sources.)
· Algorithme à la complexité linéaire O(n) :
1 def l i n e a r _ c l o s u r e ( s e l f , table_name , a t t r i b u t e _ s e t ) :
2
# Initialization
3
t a b l e _ a t t r i b u t e s = s e l f . g e t _ a t t r i b u t e _ l i s t ( table_name )
4
funcdep_list = s e l f . get_functional_dependencies (
table_name )
5
count = {}
6
l i s t _ d i c = {}
7
f o r a t t r i b u t e in t a b l e _ a t t r i b u t e s :
8
l i s t _ d i c [ a t t r i b u t e ] = set ( )
9
f o r funcdep in f u n c d e p _ l i s t :
10
count [ funcdep ] = len ( funcdep . l h s )
11
f o r A in funcdep . l h s :
12
l i s t _ d i c [A] = l i s t _ d i c [A ] . union ( set ( [ funcdep ] ) )
13
new_dep = set ( copy . deepcopy ( a t t r i b u t e _ s e t ) )
14
update = set ( copy . deepcopy ( a t t r i b u t e _ s e t ) )
15
# Computation
16
while len ( update ) != 0 :
17
A = update . pop ( )
18
f o r funcdep in l i s t _ d i c [A ] :
19
count [ funcdep ] = count [ funcdep ] − 1
20
i f count [ funcdep ] == 0 :
21
add = set ( funcdep . r h s ) − new_dep
22
new_dep = new_dep . union ( add )
23
update = update . union ( add )
24
# Return
25
return l i s t ( new_dep )
(Cet algorithme est une implémentation de l’algorithme écrit en pseudo-code à la page
66 du livre cité dans les sources.)
8
• Savoir si une DF est une conséquence logique d’un ensemble de DF :
1 def member ( s e l f , table_name , funcdep ) :
2
c l o s u r e = set ( s e l f . l i n e a r _ c l o s u r e ( table_name , funcdep . l h s ) )
3
i f set ( funcdep . r h s ) . i s s u b s e t ( c l o s u r e ) :
4
return True
5
else :
6
return F a l s e
(Cet algorithme est une implémentation de l’algorithme écrit en pseudo-code à la page
65 du livre cité dans les sources.)
• Calcul des superclés :
1 def g e t _ s u p e r k e y s ( s e l f , table_name ) :
2
superkeys = [ ]
3
a t t = s e l f . g e t _ a t t r i b u t e _ l i s t ( table_name )
4
att . sort ()
5
combi_att = [ ]
6
f o r i in range ( 1 , len ( a t t ) +1) :
7
combi_att += t o o l s . c o m b i n a i s o n _ l i s t ( a t t , i )
8
f o r a t t 2 in combi_att :
9
c l o s u r e = s e l f . l i n e a r _ c l o s u r e ( table_name , a t t 2 )
10
closure . sort ()
11
i f c l o s u r e == a t t :
12
s u p e r k e y s . append ( a t t 2 )
13
return s u p e r k e y s
• Calcul des clés :
1 def get_keys ( s e l f , table_name , s u p e r k e y s = [ ] ) :
2
i f s u p e r k e y s == [ ] :
3
s u p e r k e y s = s e l f . g e t _ s u p e r k e y s ( table_name )
4
k e y s = copy . deepcopy ( s u p e r k e y s )
5
oldlen = 0
6
f o r a t t in k e y s :
7
att . sort ()
8
keys . s o r t ( )
9
while len ( k e y s ) != o l d l e n :
10
o l d l e n = len ( k e y s )
11
f o r sk1 in k e y s :
12
add =F a l s e
13
f o r sk2 in k e y s :
14
v e r i f = True
15
f o r a t t in sk1 :
16
i f ( a t t not in sk2 ) and ( v e r i f != F a l s e ) :
17
v e r i f = False
18
if verif :
19
i f len ( sk1 ) < len ( sk2 ) :
20
k e y s . remove ( sk2 )
21
e l i f len ( sk1 ) > len ( sk2 ) :
22
k e y s . remove ( sk1 )
23
keys . r e v e r s e ( )
9
24
return k e y s
• Savoir si une relation est en 3NF :
1 def i s _ 3 n f ( s e l f , table_name ) :
2
n f 3 = True
3
f d _ l i s t = s e l f . g e t _ f u n c t i o n a l _ d e p e n d e n c i e s ( table_name )
4
i f len ( f d _ l i s t ) != 0 :
5
s u p e r k e y s _ l i s t = s e l f . g e t _ s u p e r k e y s ( table_name )
6
kl = [ ]
7
k e y s _ l i s t = s e l f . get_keys ( table_name )
8
f o r k e y s in k e y s _ l i s t :
9
f o r a t t in k e y s :
10
i f a t t not in k l :
11
k l . append ( a t t )
12
f o r f d in f d _ l i s t :
13
i f ( ( ( not ( ( f d . l h s in s u p e r k e y s _ l i s t ) or ( f d . r h s [ 0 ] in
k l ) ) ) and ( n f 3 != F a l s e ) ) ) :
14
nf3 = False
15
else :
16
n f 3 = True
17
return n f 3
• Savoir si une relation est en BCNF :
1 def i s _ b c n f ( s e l f , table_name ) :
2
funcdeps = s e l f . get_functional_dependencies ()
3
i f len ( f u n c d e p s ) != 0 :
4
s u p e r k e y s = s e l f . g e t _ s u p e r k e y s ( table_name )
5
f o r funcdep in f u n c d e p s :
6
i f not funcdep . l h s in s u p e r k e y s :
7
return F a l s e
8
return True
• Calcul d’une décomposition en 3NF qui préserve les DF et le contenu :
1 def export_to_3nf ( s e l f , table_name , db_name2=" exported3NF . db" ) :
2
i f ( s t r ( table_name ) != "FuncDep" ) :
3
cursor = s e l f . connection . cursor ()
4
funcdeps , r e q u e s t s = s e l f . get_new_funcdeps_and_requests (
table_name )
5
newdb = FdReader ( db_name2 )
6
newdb . c o n n e c t i o n = s q l i t e 3 . c o n n e c t ( db_name2 )
7
c u r s o r = newdb . c o n n e c t i o n . c u r s o r ( )
8
f o r r e q u e s t in r e q u e s t s :
9
cursor . execute ( request )
10
f o r f d in f u n c d e p s :
11
newdb . add_functional_dependency ( f d )
12
f o r t a b l e in newdb . g e t _ t a b l e _ l i s t ( ) :
13
j = len ( t a b l e )
14
i f ( ( s t r ( t a b l e ) != "FuncDep" ) and ( s t r ( t a b l e ) [ 0 : j ] ==
s t r ( table_name ) ) ) :
15
s e l f . f i l l _ t a b l e 3 n f ( table_name , t a b l e , db_name2 )
10
Remarque : Certaines fonctions que nous avons codées et qui ne sont pas présentées dans
ce rapport sont utilisées dans cet algorithme. Il est possible de les consulter dans le script
fdreader.py.
6
Problèmes rencontrés et solutions
Il n’y a pas eu de problèmes majeurs lors du développement de l’application mis a part lors
de l’exportation en 3NF où nous avons mis du temps a comprendre l’algorithme.
Néanmoins lors du démarrage de l’application sous Archlinux, un warning apparait. Il semblerait que cela vient d’un bug dans la librairie LibPng selon une page du wiki d’Archlinux
(https://wiki.archlinux.org/index.php/Libpng_errors). Ce warning semble ne poser aucun problème lors de l’utilisation de l’application.
7
Répartition du travail
Jannou Brohée
Identification des clés/superclés
Vérification des normes 3NF & BCNF
Tests unitaires
8
Julien Delplanque
Lecture/écriture des dépendances fonctionnelles
Vérification des dépendances fonctionnelles
Identification des clés/superclés
Vérification des normes 3NF & BCNF
Tests unitaires
Interface Utilisateur
Rapport
Sources et remerciements
• Livre de référence pour les algorithmes : http://web.cecs.pdx.edu/~maier/TheoryBook/
MAIER/.
• Site consulté : http://perso.univ-lr.fr/pboursie/BD_Niv1/.
• Icône point d’interrogation : http://www.xn--icne-wqa.com/tag-interrogation-0.
• Alexandre Delplanque pour la création du reste des images.
• Maximilien Charlier pour les comparaisons de résultats de tests sur des bases de données
identiques.
• Émeline Renard pour la relecture du rapport.
11