670 likes | 936 Views
UE FLIN601 - BD Année 2007 - 2008. Cours …. Plan. Généralités Rappels modèle relationnel Algèbre relationnelle, SQL Les contraintes Retour sur les fondements logiques Conception Organisation physique Transactions. Conception. Un schéma relationnel de base de données
E N D
UE FLIN601 - BD Année 2007 - 2008 Cours …
Plan • Généralités • Rappels modèle relationnel • Algèbre relationnelle, SQL • Lescontraintes • Retour sur les fondements logiques • Conception • Organisation physique • Transactions
Conception • Un schéma relationnel de base de données peut contenir des centaines de tables, des milliers d'attributs .. • Problème : comment éviter des erreurs de conception ? • Deux solutions (complémentaires) • dériver à partir de modèles conceptuels • théorie de la normalisation
Conception • UML (rappels) nom de la classe attributs Voiture type : string marque : string couleur : string opérations Implémentations repeindre(c: Couleur) déplacer(d : longueur) Méthodes
Conception • UML (rappels) Multiplicité et rôles d'une association employeur employé Personne Société emploie 1..* * nom date de nais. n°SS adresse nom adresse travaille-pour chef 0..1 encadre 1..* travailleur
Conception • UML (rappels) Classe association Possède-des-actions Entreprise Personne 1..* * nom date de nais. adresse capital nom adresse actionnaire Ligne de portefeuille quantité
Conception Sémantique Collection/Élément • UML (rappels) Agrégation Pays 1 Forêt 1 1..n Région 1..n Arbre 1 1..n Site
Conception • UML (rappels) Composition Association particulière tout /partie L’existence du composant est assujettie à celle de l’objet composite 1 * DeptEnseignement Université
Conception • UML (rappels) Généralisation / Spécialisation B spécialise A ; A généralise B A Définition : L’extension de A (ses instances) contient l’extension de B B A B Conséquence : une instance de B possède les propriétés de A éventuellement sous une forme affinée
Conception • UML (rappels) Contraintes de généralisation / Spécialisation Employé {incomplet} {complet, disjoint} Cotisant SalariéCdi Vacataire NonCotisant
Conception • UML (rappels) Contraintes de généralisation / Spécialisation Employé {incomplet} {complet, disjoint} Cotisant SalariéCdi Vacataire NonCotisant
Personne NSS : string {unique} Age : Integer {130>value ≥0} Conception Autres contraintes * 1..* subordonné chef Société 0..1 <dirige { Personne.employeur = Personne.chef.employeur } 0..1 NSIRET: String {unique} actif : Real {value 0} passif : Real employeur contraintes sur attribut Contrainte de domaine contrainte sur 2 associations
Conception Traduction Règles de « passage » d. structurels --> schémas d. dynamiques --> requêtes et traitements applicatifs divers Automatisées dans les AGL (cf TP)
Conception Classes ADRESSE N° Rue CP Ville Schéma relationnel Attributs Ajout de l’identifiant Attribut Domaine Non Null Schéma Adresse Clé primaire Id_adresse Id_adresse Identifiant Oui N° Entier Non Rue String(30) Non …..
Conception Classe UML { persistence} Instances de la classe Table Departement
Conception Spécialisation Plusieurs choix - « aplatir vers le haut » Schéma Personne Clé primaire NSS - « aplatir vers le bas » Schémas Etudiant, Enseignant Clé primaire NSS - conserver les niveaux PERSONNE NSS .... ETUDIANT ENSEIGNANT num .... grade ....
Conception Associations COMPTE PERSONNE possède 0..* 1 NSS .... N° Type Schéma Compte Clé primaire Id-compte N° On choisit N° Clé étrangère NSS Attribut Domaine Non Null Id_compte Identifiant Oui N° String(35) Id Oui NSS Identifiant Oui
Conception Théorie de la normalisation
Conception Relation universelle RR(U, F) U ensemble d'attributs F ens de DF/DMV définies sur U Stratégie : Décomposition { R1(U1, F1), ……..Rn(Un, Fn)}
Conception { R1(U1, F1), ……..Rn(Un, Fn)} • Ri 1≤ i≤n • Ri(Ui, Fi) • - Fi définies sur Ui • Ri = Ui (R) conservation des données • conservation des dépendances - Fi = Ui (F) {X Y tq XY Ui F X Y }
Conception Propriétés { R1(U1, F1), ……..Rn(Un, Fn)} • conservation des données • instance r de R • r = (ri) • conservation des dépendances F Fi
Décomposition Exemple Décomposition R1 (Titre , MES, Auteur,NomC) Titre MES BDCINE (Titre , MES, Acteur, NomC, Adresse , Téléphone) Titre MES NomC Adresse Téléphone Titre NomC Titre NomC Speed 2 JDB S. Bullock UGC Speed 2 JDB J. Patric UGC Marion MP C. Tetard Diago Marion MP MF Pisier Diago R3 (NomC, Adresse, Téléphone) NomC Adresse Téléphone UGC 9, Rue Boileau 04 67 41 85 67 Diago Bd Gambetta 04 67 54 32 31
Décomposition Exemple Décomposition R1 (Titre , MES, Acteur) Titre MES R2 (Titre , NomC) Titre NomC BDCINE (Titre , MES, Acteur, NomC, Adresse , Téléphone) Titre MES NomC Adresse Téléphone Titre NomC Speed 2 JDB S. Bullock Speed 2 JDB J. Patric Marion MP C. Tetard Marion MP MF Pisier Speed 2 UGC Marion Diago R3 (NomC, Adresse, Téléphone) NomC Adresse Téléphone UGC 9, Rue Boileau 04 67 41 85 67 Diago Bd Gambetta 04 67 54 32 31
Décomposition NAgence ChifAff BANQUE NAgence ChifAff NClient PClient Nprêt Montant Nclient PClient Nprêt Montant Montpellier 1 1000 Montpellier 23 2000 Béziers 13 1500 Nimes 20 5000 Béziers 18 2000 Montpellier 16 1500 ….. R1 (NAgence, Nprêt, Montant) R2 (NClient,Pclient, Montant) R3 (Nagence, ChiAff) Dupont Pierre 1000 Durand Louis 2000 Benoit Nestor 1500 Dupont Pierre 5000 Lebon Zoé 2000 Lesueur Désiré 1500 ….. Montpellier 90 Béziers 75 Nimes 120 ….. Perte de données
Les formes normales Trois premières formes normales Objectif : obtenir une décomposition sans perdre d'information à partir de DF Première forme normale Une relation est en 1FN si tout attribut contient une valeur atomique (On a des instances sans attributs multivalués)
Les formes normales Deuxième forme normale Une relation est en deuxième forme normale ssi • Elle est en 1FN • tout attribut n'appartenant pas à une clé ne dépend pas d'une partie de clé Fournisseur(Nom, Adresse, Article, Prix) Nom Article Prix, Nom Adresse Clé K= Nom Article Adresse K mais Nom Adresse
Les formes normales Deuxième forme normale (On élimine certaines redondances ..) Fournisseur(Nom, Adresse, Article, Prix) Nom Article Prix, Nom Adresse Clé K= Nom Article Adresse K mais Nom Adresse R1(Nom, Adresse) R2(Nom, Article, Prix)
Les formes normales Deuxième forme normale R K1 K2 X Y
Les formes normales Troisième forme normale Une relation est en troisième forme normale ssi • Elle est en 2FN • Tout attribut n'appartenant pas à une clé ne dépend pas d'un autre attribut non clé
Les formes normales Troisième forme normale (On élimine certaines redondances dues aux dépendances transitives) Voiture(NV, Marque, Type, Puissance, Couleur) Type Marque Puissance NV Marque Type Puissance Couleur NV Type Marque NV Type Puissance Clé NV Voiture(NV, Type, Couleur) Modele(Type, Marque, Puissance
Les formes normales Troisième forme normale R K Z X Y
Les formes normales Décomposition en Troisième forme normale Toute relation a au moins une décomposition en troisième forme normale telle que la décomposition préserve les DF est sans perte (de données)
Les formes normales Algorithme de décomposition en Troisième forme normale Données : Un schéma de relation R(U,F), F est un ensemble de dépendances élémentaires Résultat : Un schéma de base de données en forme normale3NF Trouver une couverture minimale irredondante (CIM) de F Conserver les DF élémentaires Partitionner la CIM en groupes Fi tels que les DF dans chaque Fi ait le même ensemble d'attributs en partie gauche Chaque groupe produit un schéma de relation en 3FN Si aucune clé de R(U,F)n'est incluse dans l'un des schémas produits, rajouter un schéma R(K)
Les formes normales Calcul d'une couverture minimale irredondante F ensemble de dépendances fonctionnelles Transformer les DF en DF élémentaires (un seul attribut en partie droite) Tester l'irredondance des DF Tester l'irredondance des parties gauches de DF Itérer jusqu'à stabilité
Les formes normales DF redondante Soit F un ensemble de DF Une DF f : X Y de F est redondante si F-f F cad (F-f)+ = F+ Ce qui revient à vérifier que Y X+ / F-f
Les formes normales Attribut redondant en partie gauche de DF Soit X Y une DF d'un ensemble F de dépendances fonctionnelles Un attribut A X est redondant si F- (X Y) (X-A Y) F cad (F- (X Y) (X-A Y) )+ = F+ Ce qui revient à vérifier que Y (X-A)+ / F
Les formes normales Algorithme de calcul de clé Donnée : R(U, F) et une superclé K de R Résultat : une clé de R X=K; pour chaque A X faire si A (K- A)+/ F alors K = K-A fsi fpour Retourner K Rque : il existe un algo retournant l'ensemble des clés
Les formes normales Un exemple R (ABCDEG) et F = { AB C, D EG C A, BE G BC D, CG BD ACD B , CE AG }
Les formes normales Forme normale de BOYCE-CODD Il peut rester des anomalies dues à des redondances entraînées par des dépendances entre parties de clés ou entre attributs non clés et parties de clé … Une relation est en BCNF ssi Les seules dépendances fonctionnelles élémentaires sont celles dans lesquelles une clé entière détermine un attribut Pas de dépendances autres que K A (K clé et A attribut non clé)
Les formes normales Forme normale de BOYCE-CODD R K1 K2 X Y R(K1K2XY) est en 3 FN R1(K1K2X) et R2(YK1) en 3 BCNF
Les formes normales Du point de vue logique la normalisation est essentielle Du point de vue performance Pas toujours optimal • dénormalisation (implémentation de la jointure) • groupement (clustering)
Conception Intégrité des données Définition des contraintes Vérification des contraintes Triggers (cf TP)
Conception Définition des contraintes Objectif Détecter et prévenir saisies et mises à jour erronées. Hypothèse - un langage de définition de contraintes d'intégrité - la vérification automatique de ces contraintes Avantages simplification du code des applications sécurité renforcée par l'automatisation mise en commun et cohérence globale des contraintes
Conception Définition des contraintes • Contraintes de DOMAINE ex: le nom d'un employé est de type chaîne de caractères le degré d'un vin ne peut être inférieur à 8 • Contraintes d'ENTITE (unicité et non nullité) toute relation doit posséder au moins une clé et cette clé ne peut pas prendre de valeurs nulles • Dépendance fonctionnelle Ex : CRU, ANNEE -------> DEGRE dans la relation VINS
Conception • Dépendance d'inclusion Concept de généralisation {valeurs d'un {groupe d'attributs x}} inclus dans {valeurs d'un {groupe d'attributs y}} EXEMPLE : ENSEIGNANT.NOM inclus dans PERSONNE.NOM ENSEIGNANT ----g----> PERSONNE • Contraintes REFERENTIELLE La dépendance référentielle est un cas particulier de dépendance d'inclusion ex: l'ensemble des valeurs de l'attribut Emp.N_Dept doit être inclus dans l'ensemble des valeurs de l'attribut Dept.N_Dept Définition des contraintes
Conception Définition des contraintes • Contraintes multi-attributs (horizontales) Ex: le prix de vente d'un produit doit être supérieur à son coût de production • Contraintes temporelles Ex : le degré d'un vin ne peut pas décroître • Contraintes agrégatives (verticales) Ex : la somme des quantités bues d'un vin doit être inférieure a la quantité produite de ce même vin