cours 5,6: optimisation de requêtes & normalisation de bases de données tuanloc nguyen miage...

Cours 5,6: Cours 5,6: Optimisation de requêtesOptimisation de requêtes& Normalisation de bases & Normalisation de bases

de donnéesde données

Tuanloc NGUYEN Tuanloc NGUYEN

Miage de Paris 12Miage de Paris 12

Optimisation de requêtesOptimisation de requêtes

Algèbre relationnelAlgèbre relationnel Décomposition de requêtesDécomposition de requêtes Optimisation de requêtesOptimisation de requêtes

Architecture SGBDArchitecture SGBD

ANALYSEUR

META-BASE

CONTROLE

Traitement

EXECUTABLE

Schéma

VueAutorisationIntégrité

OrdonnancementÉlaboration/Optimisation

Méthode d’accès(hachage,arbre B+,index)

BD

Traitement d’une requêteTraitement d’une requête

Normalisation

Analyse

Restructuration

SQL

Simplification

Optimisation

Processeur derequêtes

Exécution des plans

SimplificationSimplification

Plus une requête est simple, plus son Plus une requête est simple, plus son exécution peut être efficaceexécution peut être efficace

ImplémentationImplémentation SélectionSélection

– Parcours séquentielParcours séquentiel– Parcours avec index (hachage,arbre B)Parcours avec index (hachage,arbre B)

ProjectionProjection

Jointure: TJointure: T = R |x| = R |x| SSforeach tuple r foreach tuple r ЄЄ R do R do

foreach tuple s foreach tuple s ЄЄ S do S do if r==s if r==s

T = T + <r,s>T = T + <r,s>

RestructurationRestructuration

Objectif: choisir l’ordre de l’exécution Objectif: choisir l’ordre de l’exécution des opérations algébriques des opérations algébriques (élaboration du plan logique)(élaboration du plan logique)

– Conversion en arbre algébriqueConversion en arbre algébrique– Transformation de l’arbre (optimisation)Transformation de l’arbre (optimisation)

Appliquer les règles de transformationAppliquer les règles de transformationEstimation du coût des opérations (taille)Estimation du coût des opérations (taille)Ordre des jointures (coûte le plus cher) Ordre des jointures (coûte le plus cher)

SELECT ENSEIGNANTS.nom, ENSEIGNANTS.prenom, SELECT ENSEIGNANTS.nom, ENSEIGNANTS.prenom, MATIERES.nommat, ENSEIGNANTS.note, ENSEIGNANTS.gentilMATIERES.nommat, ENSEIGNANTS.note, ENSEIGNANTS.gentil

FROM FROM MATIERESMATIERES INNER JOIN INNER JOIN ENSEIGNANTSENSEIGNANTS INNER JOIN INNER JOIN ENSEIGN_MATENSEIGN_MAT

ON ENSEIGNANTS.codens=ENSEIGN_MAT.codens ON ENSEIGNANTS.codens=ENSEIGN_MAT.codens ON MATIERES.codemat =ENSEIGN_MAT.codematON MATIERES.codemat =ENSEIGN_MAT.codemat

WHERE ENSEIGNANTS.note=5 WHERE ENSEIGNANTS.note=5 ANDAND ENSEIGNANTS.gentil="top" ENSEIGNANTS.gentil="top" AND AND ((nommat="ADMIN BASE DE DONNEES"nommat="ADMIN BASE DE DONNEES"OR OR nommat="ACCESS"nommat="ACCESS"));;

RESULTAT

Nom,Prénom,Note,gentil

ENSEIGN_MAT.Codemat MATIERES.Codemat=

MATIERESENSEIGNANTS.Codens ENSEIGN_MAT.Codens

ENSEIGNANTS

=

ENSEIGN_MAT

Arbre algébriqueArbre algébrique

nommat="ADMIN BASE DE DONNEES"OR nommat="ACCESS"nommat="ADMIN BASE DE DONNEES"OR nommat="ACCESS"

ENSEIGNANTS.gentil="top"ENSEIGNANTS.gentil="top"

ENSEIGNANTS.note=5ENSEIGNANTS.note=5

OptimisationOptimisation Elaborer des plansElaborer des plans

– Arbre algébrique, restructuration, ordre d’évalutionArbre algébrique, restructuration, ordre d’évalution

Estimer les coûtsEstimer les coûts– Temps d’exécutionTemps d’exécution– Coût I/O, CPU, poids entre I/O – CPU:Coût I/O, CPU, poids entre I/O – CPU:

Nombre d’instructions et d’accès au disqueNombre d’instructions et d’accès au disque

Choisir le meilleur planChoisir le meilleur plan– Algorithme de recherche: HeuristiqueAlgorithme de recherche: Heuristique– Coût de chaque plan est différentCoût de chaque plan est différent– Ordre des jointures est très importantOrdre des jointures est très important– Optimisation d’espace de rechercheOptimisation d’espace de recherche

Stratégie de rechercheStratégie de recherche– DéterministeDéterministe– Aléatoire : efficace avec beaucoup de relations, améliorer itinéraireAléatoire : efficace avec beaucoup de relations, améliorer itinéraire

OptimisationOptimisation

Pour optimiser, il y a une technique Pour optimiser, il y a une technique simple: descendre les opérateurs de simple: descendre les opérateurs de sélection et projection le plus près sélection et projection le plus près possible des feuilles pour réduire les possible des feuilles pour réduire les tables le plus possibletables le plus possible

RESULTAT

ENSEIGN_MAT.Codemat MATIERES.Codemat=

MATIERESENSEIGNANTS.Codens ENSEIGN_MAT.Codens

ENSEIGNANTS

=

ENSEIGN_MAT

Arbre optimiséArbre optimisé

nommat="ADMIN BASE DE DONNEES"nommat="ADMIN BASE DE DONNEES"OR OR nommat="ACCESS"nommat="ACCESS"

ENSEIGNANTS.gentil="top"ENSEIGNANTS.gentil="top"

ENSEIGNANTS.note=5ENSEIGNANTS.note=5

Nom,Prénom,Note,gentil

ConclusionConclusion

Optimisation des requêtes est très Optimisation des requêtes est très important pour administrateur de important pour administrateur de base de données:base de données:

améliorer les performances en améliorer les performances en réglant des paramètres pour réglant des paramètres pour optimiser des requêtesoptimiser des requêtes

NormalisationNormalisation

1NF1NF 2NF2NF 3NF3NF BCNFBCNF 4NF4NF

Donnéesnon-normalisation 1 NF 2 NF 3 NF BCNF

4 NF5 NF

Pourquoi normalisation ?Pourquoi normalisation ?

-Réduire Null-Réduire Null-Redondance de données-Redondance de données-Eviter le coding (trigger,procédure stocké,…)-Eviter le coding (trigger,procédure stocké,…)-Bien structurer des données-Bien structurer des données-Optimisation de performance:-Optimisation de performance:

.temps d’exécution(‘’thin table’’).temps d’exécution(‘’thin table’’)

.Maximiser ‘’Clustered indexes’’ .Maximiser ‘’Clustered indexes’’ (trier,rercherche)(trier,rercherche).Nombre d’index par table.Nombre d’index par table

Première forme normale 1NFPremière forme normale 1NF(First Normal Form)(First Normal Form)

Une relation est dite normalisée ou Une relation est dite normalisée ou en première forme normale si :en première forme normale si :– aucun attribut qui la compose n'est lui-aucun attribut qui la compose n'est lui-

même une relation, c'est-à-dire si tout même une relation, c'est-à-dire si tout attribut est attribut est atomiqueatomique (non (non décomposable).décomposable).

– Cette forme n'utilise que les structures Cette forme n'utilise que les structures de base d'une relation, elle ne résout de base d'une relation, elle ne résout pas le problème de la redondance.pas le problème de la redondance.

1NF1NF

Atomicité (emails, téléphone,adresses IP)Atomicité (emails, téléphone,adresses IP) Chaque entité a le même nombre de Chaque entité a le même nombre de

valeurs. (noms,adresses)valeurs. (noms,adresses) Tuple est uniqueTuple est unique

Exemple: 1NFExemple: 1NF

R(code_etudiant,nom,prénom,R(code_etudiant,nom,prénom,…,…,code_mat1,code_mat2,code_mcode_mat1,code_mat2,code_mat3,code_mat4,code_mat5at3,code_mat4,code_mat5))

-> ce n’est pas bien (-> ce n’est pas bien (non 1NFnon 1NF))

fournisseur

S#: code de fournisseur

sname: nom de fournisseur

status: statut de fournisseur

R(s#,p#,sname,city,status,qty)

p#: produit


S1S1 P1P1 AA C1C1 11 1010

S1S1 P2P2 AA C1C1 11 2020

S1S1 P3P3 AA C1C1 11 2525

S1S1 P4P4 AA C1C1 11 3030

S2S2 P1P1 BB C1C1 11 1515

S1S1 P2P2 BB C1C1 11 4040

S3S3 P1P1 CC C2C2 22 55

S4S4 P1P1 DD C2C2 22 3535

S5S5 P2P2 EE C3C3 33 77

1NF1NF

Problèmes sur 1NF: Problèmes sur 1NF: – Ajouter S6 pas de produit ?Ajouter S6 pas de produit ?– Changer nom S1 a en x:Changer nom S1 a en x:

Changer tous S1Changer tous S1Changer 1 enregistrement: conflitChanger 1 enregistrement: conflit

->redondance->redondance

– Supprimer S3: perde d’info S3Supprimer S3: perde d’info S3

Sections de données répétées Sections de données répétées imbriquéesimbriquées

Première forme normale (1NF)Première forme normale (1NF)– Table1(Table1(Key1Key1, aaa . . .), aaa . . .)– Table2(Table2(Key1Key1, , Key2Key2, bbb . .), bbb . .)– Table3(Table3(Key1Key1, , Key2Key2, , Key3Key3, ccc. . .), ccc. . .)

Table (Key1, . . . (Key2, . . . (Key3, . . .) ) )

Table1(Key1, . . .) TableA (Key1,Key2 . . .(Key3, . . .) )

Table2 (Key1, Key2 . . .) Table3 (Key1, Key2, Key3, . . .)

Deuxième forme normale 2NFDeuxième forme normale 2NF

Une relation est dite en deuxième Une relation est dite en deuxième forme normale si et seulement si :forme normale si et seulement si :– Elle est en première forme normale ;Elle est en première forme normale ;– Chaque attribut est Chaque attribut est totalement totalement

dépendantdépendant de la clé primaire. de la clé primaire.

Avec cette forme, les problèmes de Avec cette forme, les problèmes de redondance ne sont pas entièrement redondance ne sont pas entièrement résolus.résolus.



R1(s#,p#,sname,city,status) R2(s#,p#,qty)

Information de la société Activités de la société

S1S1 P1P1 1010

S1S1 P2P2 2020

S1S1 P3P3 2525

S1S1 P4P4 3030

S2S2 P1P1 1515

S1S1 P2P2 4040

S3S3 P1P1 55

S4S4 P1P1 3535

S5S5 P2P2 77

PAS DE PROBLEMES

2NF2NF

Troisième forme normale 3NFTroisième forme normale 3NF

Une relation est en troisième forme Une relation est en troisième forme normale si et seulement si :normale si et seulement si :– elle est en 2NF;elle est en 2NF;– et chaque attribut non-clé primaire et chaque attribut non-clé primaire

dépend directement de la clé primaire.dépend directement de la clé primaire. La 3NF est adéquate pour la majorité des La 3NF est adéquate pour la majorité des

designs de BD mais elle n'élimine pas designs de BD mais elle n'élimine pas toutes les redondances et incohérences. toutes les redondances et incohérences. Pour cela, Codd a pensé à BCNF qui est une Pour cela, Codd a pensé à BCNF qui est une forme plus stricte de 3NF.forme plus stricte de 3NF.

Exemple 3NFExemple 3NF

R

R1

R11(city, status) 3NF

R12(s#, sname,city) 3NF

R2 (s#,p#,qty) 3NF

1) s# sname

city

statusX

Enlever s# ->status

3NF : T={R11, R12, R2}

2) R12 R121

R122

Solution:

Quatrième forme normale 4NFQuatrième forme normale 4NF

Permet autant que possible de minimiser Permet autant que possible de minimiser l'occurence d'attributs indépendants à valeur l'occurence d'attributs indépendants à valeur mutiple.mutiple.

Une relation est de la 4NF si :Une relation est de la 4NF si :– elle satisfait la 3NFelle satisfait la 3NF– les données composant chaque attribut ne les données composant chaque attribut ne

comportent aucune répétition inutile -> comportent aucune répétition inutile -> dans une même colonne, il faut minimiser dans une même colonne, il faut minimiser les répétitions.les répétitions.

Une base qui est 4NF est des plus optimales Une base qui est 4NF est des plus optimales quoiqu'il soit possible de généraliser cette quoiqu'il soit possible de généraliser cette dernière afin d'obtenir la 5NF.dernière afin d'obtenir la 5NF.

Boyce-Codd Normal Form Boyce-Codd Normal Form (BCNF)(BCNF)

Dépendances fonctionnelles cachéesDépendances fonctionnelles cachées Exemple:Exemple:

– Employee-Specialty(Employee-Specialty(E#E#, , SpecialtySpecialty, Manager), Manager)– Est en 3NF.Est en 3NF.

Rêgle d’entreprise – “Business rules”.Rêgle d’entreprise – “Business rules”.– Un employé peut avoir plusieurs spécialités.Un employé peut avoir plusieurs spécialités.– Chaque spécialité a plusieurs “managers”.Chaque spécialité a plusieurs “managers”.– Chaque “manager”a seulement une spécialité.Chaque “manager”a seulement une spécialité.– Un employé a seulement 1 “manager” pour Un employé a seulement 1 “manager” pour

chaque spécialité.chaque spécialité. Le problème est dans la dépendance fonctionnelle Le problème est dans la dépendance fonctionnelle

caché entre “manager” et spécialité.caché entre “manager” et spécialité.– Besoin d’une table séparée pour “manager”.Besoin d’une table séparée pour “manager”.– But then we don’t need to repeat specialty.But then we don’t need to repeat specialty.

Dans monde réel, la duplication serait Dans monde réel, la duplication serait probablement acceptée (spécialité dans les 2 probablement acceptée (spécialité dans les 2 tables.tables.

Employee-Specialty(E#, Specialty, Manager)

Employee(E#, Manager)

Manager(Manager, Specialty)

Employee(E#, Specialty, Manager)


acceptable

Annexe: NormalisationAnnexe: Normalisation

Exemple: BD VideoExemple: BD Video

Section de données répétéesClé possible

Objets de la BD VidéoObjets de la BD Vidéo ClientsClients

– Clé: Assignation de Clé: Assignation de CustomerIDCustomerID

– PropriétésPropriétés NomNom AdresseAdresse TéléphoneTéléphone

VidéosVidéos– Clé: Attribution d’un Clé: Attribution d’un

noVidéonoVidéo– PropriétésPropriétés

TitreTitre PrixLocationPrixLocation CoteCote DescriptionDescription

TransactionLocationTransactionLocation– Relation/ÉvénementRelation/Événement– Clé: Attribution d’un Clé: Attribution d’un

noTransactionnoTransaction– PropriétésPropriétés

noClientnoClient DateDate

VidéosLouésVidéosLoués– Événement/ListeÉvénement/Liste– Clés: noTransaction + Clés: noTransaction +

noVidéonoVidéo– PropriétésPropriétés

noCopieVidéonoCopieVidéo

Formulaire initial BD vidéoFormulaire initial BD vidéo

Recueillir les formulaires de Recueillir les formulaires de l’usagerl’usager

Noter les propriétésNoter les propriétés Trouver les sections de Trouver les sections de

données répétéesdonnées répétées Noter les clés potentiellesNoter les clés potentielles Identifier les propriétés Identifier les propriétés

calculéescalculées Résultat équivalent à un Résultat équivalent à un

diagramme (modèle diagramme (modèle logique), mais va pouvoir logique), mais va pouvoir être contenu sur un page être contenu sur un page ou deux.ou deux.

RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode,(VideoID, Copy#, Title, Rent ) )

Problèmes avec les sections de Problèmes avec les sections de données répétéesdonnées répétées

RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode,(VideoID, Copy#, Title, Rent ) )

TransID RentDate CustomerID LastName Phone Address VideoID Copy# Title Rent1 4/18/02 3 Washington 502-777-7575 95 Easy Street 1 2 2001: A Space Odyssey $1.501 4/18/02 3 Washington 502-777-7575 95 Easy Street 6 3 Clockwork Orange $1.502 4/30/02 7 Lasater 615-888-4474 67 S. Ray Drive 8 1 Hopscotch $1.502 4/30/02 7 Lasater 615-888-4474 67 S. Ray Drive 2 1 Apocalypse Now $2.002 4/30/02 7 Lasater 615-888-4474 67 S. Ray Drive 6 1 Clockwork Orange $1.503 4/18/02 8 Jones 615-452-1162 867 Lakeside Drive 9 1 Luggage Of The Gods $2.503 4/18/02 8 Jones 615-452-1162 867 Lakeside Drive 15 1 Fabulous Baker Boys $2.003 4/18/02 8 Jones 615-452-1162 867 Lakeside Drive 4 1 Boy And His Dog $2.504 4/18/02 3 Washington 502-777-7575 95 Easy Street 3 1 Blues Brothers $2.004 4/18/02 3 Washington 502-777-7575 95 Easy Street 8 1 Hopscotch $1.504 4/18/02 3 Washington 502-777-7575 95 Easy Street 13 1 Surf Nazis Must Die $2.504 4/18/02 3 Washington 502-777-7575 95 Easy Street 17 1 Witches of Eastwick $2.00

Section de données répétées

Provoque des duplications

Beaucoup de problèmes seraient provoqués par cette structure.

Problèmes avec les sections de Problèmes avec les sections de données répétéesdonnées répétées

NamePhoneAddressCityStateZipCode

VideoID Copy# Title Rent1. 6 1 Clockwork Orange 1.502. 8 2 Hopscotch 1.503. 4. 5.

{Unused Space}

Pas en première forme normale

Autre idée: Autre idée: Mémoriser les Mémoriser les données sur la données sur la largeur (…)largeur (…)– Allocation Allocation

d’espaced’espace– Combien?Combien?

Ne peut être Ne peut être petitpetit

Perte d’espacePerte d’espace e.g., Combien de e.g., Combien de

vidéo seront loué ?vidéo seront loué ? Une meilleure Une meilleure

définition élimine ces définition élimine ces problèmes.problèmes.

Customer Rentals

Première forme normalePremière forme normaleRentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode,

(VideoID, Copy#, Title, Rent ) )

RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode)

RentalLine(TransID, VideoID, Copy#, Title, Rent )

Enlever les sections de données répétéesEnlever les sections de données répétées– Divisé en 2 tablesDivisé en 2 tables– Transmettre les clés de la table principale à la Transmettre les clés de la table principale à la

nouvelle tablenouvelle table RentalLine(RentalLine(TransIDTransID, , VideoIDVideoID, Copy#, . . .), Copy#, . . .)

– Chaque transaction peut avoir plusieurs vidéos (clé Chaque transaction peut avoir plusieurs vidéos (clé VideoID)VideoID)

– Chaque vidéo peut être loué dans plusieurs Chaque vidéo peut être loué dans plusieurs transactionstransactions

Sections de données répétées Sections de données répétées imbriquéesimbriquées

Première forme normale (1NF)Première forme normale (1NF)– Table1(Table1(Key1Key1, aaa . . .), aaa . . .)– Table2(Table2(Key1Key1, , Key2Key2, bbb . .), bbb . .)– Table3(Table3(Key1Key1, , Key2Key2, , Key3Key3, ccc. . .), ccc. . .)

Table (Key1, . . . (Key2, . . . (Key3, . . .) ) )

Table1(Key1, . . .) TableA (Key1,Key2 . . .(Key3, . . .) )

Table2 (Key1, Key2 . . .) Table3 (Key1, Key2, Key3, . . .)

Problèmes de la 1NFProblèmes de la 1NFTransID RentDate CustID Phone LastName FirstName Address City State ZipCode1 4/18/02 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 421712 4/30/02 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 371483 4/18/02 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 370314 4/18/02 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171

TransID VideoID Copy# Title Rent1 1 2 2001: A Space Odyssey $1.501 6 3 Clockwork Orange $1.502 8 1 Hopscotch $1.502 2 1 Apocalypse Now $2.002 6 1 Clockwork Orange $1.503 9 1 Luggage Of The Gods $2.503 15 1 Fabulous Baker Boys $2.003 4 1 Boy And His Dog $2.504 3 1 Blues Brothers $2.004 8 1 Hopscotch $1.504 13 1 Surf Nazis Must Die $2.504 17 1 Witches of Eastwick $2.00

1NF division en groupe 1NF division en groupe

Encore des problèmesEncore des problèmes– RedondanceRedondance– Dépendance fct Dépendance fct

cachée:cachée:– Si un vidéo n’a pas Si un vidéo n’a pas

été loué, quel est été loué, quel est son titre?son titre?

Définition de la 2ième forme Définition de la 2ième forme normalenormale

Chaque champ non clé doit Chaque champ non clé doit être fonctionnellement être fonctionnellement dépendant de la clé entière.dépendant de la clé entière.

– S’applique que sur les S’applique que sur les clés à plusieurs champsclés à plusieurs champs

– Diviser et créer une Diviser et créer une nouvelle table avec ces nouvelle table avec ces champs.champs.

Dépendance fonctionnelle Dépendance fonctionnelle (définition)(définition)– Si, pour une certaine Si, pour une certaine

valeur de clé X, on peut valeur de clé X, on peut toujours déterminer la toujours déterminer la valeur du champ Y alors le valeur du champ Y alors le champ Y est dit champ Y est dit fonctionnellement fonctionnellement dépendant de X.dépendant de X.

RentalLine(TransID, VideoID, Copy#, Title, Rent)

Dépend seulement sur VideoID

Dépend sur TransID ET VideoID


Tître dépend seulement du VideoIDTître dépend seulement du VideoID– Chaque VideoID ne peut avoir qu’un seul tîtreChaque VideoID ne peut avoir qu’un seul tître

Le champ Rent est dépendant de VideoIDLe champ Rent est dépendant de VideoID– Rêgle d’entreprise.Rêgle d’entreprise.– Pourrait être différent dans un autre club vidéo.Pourrait être différent dans un autre club vidéo.– Certain club vidéo pourrait charger un loyé Certain club vidéo pourrait charger un loyé

différent dépendant de la journée.différent dépendant de la journée. Chaque champ non clé est fonctionnellement Chaque champ non clé est fonctionnellement

dépendant de la clé et entièrement de la clé.dépendant de la clé et entièrement de la clé.

RentalLine(TransID, VideoID, Copy#, Title, Rent)

VideosRented(TransID, VideoID, Copy#)

Videos(VideoID, Title, Rent)

Exemple 2NF (Données)Exemple 2NF (Données)

TransID VideoID Copy#1 1 21 6 32 2 12 6 12 8 13 4 13 9 13 15 14 3 14 8 14 13 14 17 1

VideoID Title Rent1 2001: A Space Odyssey $1.502 Apocalypse Now $2.003 Blues Brothers $2.004 Boy And His Dog $2.505 Brother From Another Planet $2.006 Clockwork Orange $1.507 Gods Must Be Crazy $2.008 Hopscotch $1.50



RentalForm2(TransID, RentDate, CustomerID, Phone,Name, Address, City, State, ZipCode)

(non modifié)

Exemple 2NF: ProblèmesExemple 2NF: Problèmes

TransID RentDate CustID Phone LastName FirstName Address City State ZipCode1 4/18/02 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 421712 4/30/02 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 371483 4/18/02 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 370314 4/18/02 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171


Même en 2NF, certain problèmes persistentMême en 2NF, certain problèmes persistent– RedondanceRedondance– Dépendance fonctionnelle cachéeDépendance fonctionnelle cachée– Si un client nouveau client n’a pas Si un client nouveau client n’a pas

encore loué de vidéo, où mémorise-t-on encore loué de vidéo, où mémorise-t-on ces données personnelles ?ces données personnelles ?

Solution: divisé.Solution: divisé.

Définition de la 3ième forme Définition de la 3ième forme normalenormale


Chaque champ non-clé Chaque champ non-clé doit être fct dépendant doit être fct dépendant de la clé de la clé et seulementet seulement de la clé.de la clé.– Solution: divisé.Solution: divisé.– Exemple: Les noms Exemple: Les noms

de client ne change de client ne change pas à chaque pas à chaque transaction.transaction.

Dépend seulement du CustomerID

Dépend du TransID

Exemple 3NFExemple 3NF

Les attributs du client ne dépendent Les attributs du client ne dépendent que du numéro de client (CustomerID)que du numéro de client (CustomerID)– Solution: diviser et créer une Solution: diviser et créer une

nouvelle table (Customer)nouvelle table (Customer)– En laissant le CustomerID dans la En laissant le CustomerID dans la

table principale.table principale.

La 3NF est souvent plus facile à voir si La 3NF est souvent plus facile à voir si les objets principaux ont déjà été les objets principaux ont déjà été identifiésidentifiés


Rentals(TransID, RentDate, CustomerID )

Customers(CustomerID, Phone, Name, Address, City, State, ZipCode )

Exemple 3NF DonnéesExemple 3NF Données

TransID RentDate CustomerID1 4/18/02 32 4/30/02 73 4/18/02 84 4/18/02 3

CustomerID Phone LastName FirstName Address City State ZipCode1 502-666-7777 Johnson Martha 125 Main Street Alvaton KY 421222 502-888-6464 Smith Jack 873 Elm Street Bowling Green KY 421013 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 421714 502-333-9494 Adams Samuel 746 Brown Drive Alvaton KY 421225 502-474-4746 Rabitz Victor 645 White Avenue Bowling Green KY 421026 615-373-4746 Steinmetz Susan 15 Speedway Drive Portland TN 371487 615-888-4474 Lasater Les 67 S. Ray DrivePortland TN 371488 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 370319 502-222-4351 Chavez Juan 673 Industry Blvd. Caneyville KY 4272110 502-444-2512 Rojo Maria 88 Main Street Cave City KY 42127



(non modifié)



Tables en 3NFTables en 3NF





Procédure pour obtenir la 3NFProcédure pour obtenir la 3NF Diviser les sections de données répétéesDiviser les sections de données répétées

– Avec les clés parentales appropriées pour que Avec les clés parentales appropriées pour que l’information mémorisé puisse être recombiné.l’information mémorisé puisse être recombiné.

Vérifier les clésVérifier les clés– Est-ce que chaque ligne est uniquement identifiée par la Est-ce que chaque ligne est uniquement identifiée par la

clé primaire ?clé primaire ?– Est-ce que les relations M:N sont bient décomposées ?Est-ce que les relations M:N sont bient décomposées ?

Vérifier que chaque colonne non-clé ne dépend que de la Vérifier que chaque colonne non-clé ne dépend que de la clé, qu’entièrement de la clé et que seulement de la clé.clé, qu’entièrement de la clé et que seulement de la clé.– Pas de dépendances fonctionnelles cachées.Pas de dépendances fonctionnelles cachées.

Définition de la 4NFDéfinition de la 4NF Techniquement, si toutes les colonnes Techniquement, si toutes les colonnes

sont clé alors la devrait être en 3FN.sont clé alors la devrait être en 3FN. Dans certain cas il y a des dépendances Dans certain cas il y a des dépendances

fonctionnelles cachées entre les fonctionnelles cachées entre les colonnes clés.colonnes clés.

Exemple:Exemple:– EmployeeTasks(EmployeeTasks(E#E#, , SpecialtySpecialty, ,

Task#Task#))– Est en 3FN (BCNF) maintenant.Est en 3FN (BCNF) maintenant.

Rêgle d'entreprise - "Business Rules"Rêgle d'entreprise - "Business Rules"– Chaque employé a plusieurs Chaque employé a plusieurs

spécialités.spécialités.– Chaque spécialité a plusieurs tâche Chaque spécialité a plusieurs tâche

- "task".- "task".– Les tâches dépendent toujours des Les tâches dépendent toujours des

spécialités.spécialités. Encore une fois, dans monde réel, la Encore une fois, dans monde réel, la

duplication serait probablement duplication serait probablement acceptée.acceptée.

EmployeeTasks(E#, Specialty, Task#)

EmployeeSpecialty(E#, Specialty)

Specialty(Specialty, Task#)

EmployeeTasks(E#, Specialty, Task#)

Specialty(Specialty, Task#)

acceptable

Boyce-Codd Normal Form Boyce-Codd Normal Form (BCNF)(BCNF)

Dépendances fcts cachéesDépendances fcts cachées Exemple:Exemple:

– Employee-Specialty(Employee-Specialty(E#E#, , SpecialtySpecialty, Manager), Manager)– Est en 3FN.Est en 3FN.

Rêgle d’entreprise – “Business rules”.Rêgle d’entreprise – “Business rules”.– Un employé peut avoir plusieurs spécialités.Un employé peut avoir plusieurs spécialités.– Chaque spécialité a plusieurs “managers”.Chaque spécialité a plusieurs “managers”.– Chaque “manager”a seulement une spécialité.Chaque “manager”a seulement une spécialité.– Un employé a seulement 1 “manager” pour chaque Un employé a seulement 1 “manager” pour chaque

spécialité.spécialité. Le problème est dans la dépendance fonctionnelle Le problème est dans la dépendance fonctionnelle

caché entre “manager” et spécialité.caché entre “manager” et spécialité.– Besoin d’une table séparée pour “manager”.Besoin d’une table séparée pour “manager”.– But then we don’t need to repeat specialty.But then we don’t need to repeat specialty.

Dans monde réel, la duplication serait Dans monde réel, la duplication serait probablement acceptée (spécialité dans les 2 probablement acceptée (spécialité dans les 2 tables.tables.

Employee-Specialty(E#, Specialty, Manager)

Employee(E#, Manager)


Employee(E#, Specialty, Manager)


acceptable

cours 5,6: optimisation de requêtes & normalisation de bases de données tuanloc nguyen miage...

Documents