Cours 9 Les formes normales tude de cas

1
Cours 9Les formes normalesÉtude de cas

• Pierre Delisle
• Université du Québec à Chicoutimi
• Département dinformatique et de mathématique

2
Plan

• Retour sur le modèle relationnel
• Les dépendances fonctionnelles
• Les formes normales
• 1FN
• 2FN
• 3FN
• FNBC
• Cas Centre sportif Peter inc.

3
Le modèle relationnel

• Table Sous-ensemble du produit cartésien dune
  liste de domaines
• Cette table représente une occurence de la table
  personne, représentée en mode extension

Chaque colonne est un attribut ou un champ
PERSONNE
Nom de la table
Chaque ligne est un n-uplet, ou tuple, ou
enregistrement
4
Le modèle relationnel (suite)

• 2 propriétés des tuples à respecter
• Lunicité des tuples il ne peut y avoir de
  tuples identiques
• Lordre des tuples lordre des tuples na pas
  dimportance, cest la même occurrence
• 3 propriétés des attributs à respecter
• Indivisibilité Les données ne sont pas
  décomposables
• Domaine unique les attributs ne peuvent prendre
  nimporte quelle valeur (intervalle, type de
  données)
• Ordre lordre des attributs na pas dimportance

5
Le modèle relationnel (suite)

• Représentation dune base de données
  relationnelle en mode formel
• FILM(NoIdentification, NoDistributeur, Titre,
  AnnéeProduction, Durée, Couleur, Producteur,
  Réalisateur, Genre)
• ACTEUR-FILM(NomActeur, NoIdentification)
• DISTRIBUTEUR(NoDistributeur, Nom, Adresse,
  Rachat)
• CASSETTE(NoSérie, NoIdentification, Format)
• CASSETTE-LOUÉE(NoSérie, NoBon, DateRetour)
• BON-LOCATION(NoBon, NoClient, DateLocation)
• CLIENT(NoMembre, Nom, Adresse, NoTél,
  NoCarteCrédit, MontantDépôt)

6
Dépendance fonctionnelle (DF)

• Association plusieurs-vers-un entre deux
  ensembles dattributs
• X?Y
• Y est en dépendance fonctionnelle de X
• X détermine fonctionnellement Y
• si et seulement si à chaque valeur de X
  correspond exactement une seule valeur de Y
• Autrement dit, lorsque 2 tuples saccordent sur
  leur valeur de X, ils saccordent également sur
  leur valeur de Y

7
Dépendance fonctionnelle (DF)

• Les dépendances fonctionnelles peuvent être
  internes
• DF entre attributs dune même entité
• Ex. PERSONNE (NAS, Nom, Prénom)
• NAS ? Nom
• Avec le NAS, je trouve un et un seul nom
• Elles peuvent aussi être externes
• DF entre entités
• Facture ? Client

8
La normalisation

• Théorie élaborée par E.F. Codd en 1970
• But éviter les anomalies dans les bases de
  données relationnelles
• Supprimer certains types de redondances
• Éviter certaines anomalies de mise à jour
• Élaborer une conception représentative du monde
  réel
• Simplifier la satisfaction de certaines
  contraintes dintégrité
• Plus le niveau des formes normales est élevé pour
  une table, plus cette table sera exempte
  danomalies
• Un bon MCD implique souvent un modèle relationnel
  ayant déjà atteint un bon niveau de normalisation
• Lanalyse des FN devient alors un bon outil de
  validation

9
Première forme normale (1FN)

• Une table est en 1FN si tous ses attributs sont
  simples et non décomposables
• Ne sont pas en 1FN
• PERSONNE (NAS, Nom, Prénom, PrénomEnfants)
• PERSONNE (NAS, Nom, Prénom, Adresse)
• Si la transposition dun MCD en modèle
  relationnel nest pas déjà en 1FN, cest quil a
  été mal modélisé !

10
Deuxième forme normale (2FN)

• Une table est en 2FN si et seulement si elle est
  en 1FN et si chaque attribut non clé est en
  dépendance fonctionnelle irréductible avec la clé
  primaire
• Autrement dit, une table est en 2FN si
• Elle est en 1FN
• Une des 3 conditions suivantes est vérifiée
• La clé primaire nest formée que dun seul
  attribut
• La clé primaire contient tous les attributs de la
  table
• Si la clé primaire a plus dun attribut, une
  dépendance fonctionnelle ne doit jamais exister
  entre une partie de la clé et un autre attribut
  de la table
• Tout attribut qui ne fait pas partie de la clé
  dépend de toute la clé (dépendance fonctionnelle
  totale)

11
Deuxième forme normale (2FN)

• Pour passer de la 1FN à la 2FN, il faut diviser
  chaque table ne satisfaisant pas les critères en
  deux tables distinctes
• Pour diviser une table en deux, il faut
• Créer une nouvelle table ayant pour clé la partie
  de la clé primaire dont dépend le ou les
  attributs, ainsi que ces attributs eux-mêmes.
• Éliminer ces attributs (ceux qui ne font pas
  partie de la clé) de la table originale

12
Deuxième forme normale (2FN) - Exemple

• La table suivante représente des modèles
  génériques de télévisions construites par
  différents fabricants. La marque et le modèle
  permettent didentifier de façon unique chaque
  sorte de télévision. Le mode sonore ainsi que la
  résolution sont spécifiques au modèle et non à la
  marque (ex HDTV)
• TÉLÉVISION (Marque, Modèle, ModeSon, Résolution)
• Il y a donc une DF entre "Modèle" et "ModeSon",
  ainsi quentre "Modèle" et "Résolution"
• Il faudra donc diviser cette table en deux de la
  façon suivante 
• TÉLÉVISION (Marque, Modèle)
• MODÈLETV (Modèle, ModeSon, Résolution)

13
Troisième forme normale (3FN)

• Une table est en 3FN si et seulement si elle est
  en 2FN et si chaque attribut non clé est en
  dépendance non transitive avec la clé primaire
• Autrement dit, une table est en 3FN si
• Elle est en 2FN
• Aucun attribut ne faisant pas partie de la clé
  primaire ne dépend dun autre attribut ne faisant
  pas partie lui non plus de la clé primaire
• Les dépendances fonctionnelles entre deux
  attributs ordinaires (ne faisant pas partie de la
  clé) sont interdites

14
Troisième forme normale (3FN)

• Cette table nest pas en 3FN
• AÉROPORT (CodeAéroport, Nom, Ville, Prov-État,
  Pays, Altitude, LongueurPiste)
• Pourquoi ?

15
Troisième forme normale (3FN)

• Pour passer de 2FN à 3FN, il faut
• Diviser chaque table ne satisfaisant pas ce
  critère en deux tables. La nouvelle table aura
  comme clé lattribut dont provient la dépendance
  et comme attributs, ceux qui en dépendent.
• Éliminer les attributs dépendants de la table
  originale. La clé de la nouvelle table demeure
  dans lancienne en tant que clé étrangère
• Correction
• AÉROPORT (CodeAéroport, Nom, Ville, Prov-État,
  Altitude, LongueurPiste)
• VILLE (Ville, Prov-État, Pays)

16
Troisième forme normale (3FN) - Exemple

• Un vendeur de voitures usagées vend ses voitures
  à un prix unique selon lannée de construction
• VOITURE (NoStock, Marque, Modèle, Année, Couleur,
  Prix)
• Il y a donc une DF entre "Année" et "Prix", ce
  qui signifie que cette table nest pas en 3FN.
  Il faut donc décomposer cette table en deux.
• VOITURE (NoStock, Marque, Modèle, Année, Couleur)
• PRIXVENTE (Année, Prix)

17
Forme normale de Boyce-Codd (FNBC)

• Définition plus rigide de la 3FN
• Une table est en FNBC si
• Elle est en 3FN
• Aucun attribut faisant partie de la clé primaire
  ne dépend dun attribut ne faisant pas partie de
  la clé primaire
• Autrement dit, une table est en FNBC si une des
  conditions suivantes est remplie
• Sa clé nest constituée que dun seul attribut
• Tous les attributs sont dans la clé primaire
• IL ny a pas de dépendance fonctionnelle entre un
  attribut ordinaire et un attribut faisant partie
  de la clé primaire

18
Forme normale de Boyce-Codd (FNBC)

• Une base de donnée peut généralement être
  considérée comme implantable lorsquelle est en
  FNBC
• Les cas de tables modélisées et transformées en
  3FN qui ne sont pas déjà en FNBC sont très rares

19
MCD Centre sportif Peter inc.
20
Dictionnaire de données Centre sportif Peter
inc.
21
MPD Centre sportif Peter inc.
22
Modèle relationnel formel

• CLIENT (NoClient, Nom, Prenom, Statut, Reduction)
• SALLE (NoSalle, Description)
• RESERVATION (NoClient, NoSalle, DateDebut,
  DateFin, Cout, Autorisation)
• FORFAIT (NoForfait, DateDebut, DateFin, Prix)
• CARTE (NoClient, NoForfait)
• ACTIVITÉ (NoActivité, Description, NoSalle)
• ACTIVITÉ FORFAITAIRE (NoForfait, NoActivite)
• ACT_EN_COURS (NoSalle, NoActivité)
• GROUPE (NoGroupe, Description, DateDebut,
  DateFin, NoActivite)
• EMPLOYE (NoEmploye, Nom, Prenom)
• AFFECTATION (NoGroupe, NoEmploye)
• COMPETENCE (NoEmploye, NoActivite, Fonction)
• Reste à le normaliser !

23
Passage à la 1ère forme normale

• Tous les attributs de toutes les tables sont
  simples et non décomposables
• Le modèle est donc déjà en 1FN

24
Passage à la 2e forme normale

• Le statut dautorisation dune réservation dépend
  uniquement de la salle réservée
• Il y a donc une DF, dans la table RESERVATION,
  entre NoSalle et Autorisation (NoSalle ?
  Autorisation)
• Cest une DF entre un attribut ordinaire et une
  partie de la clé, cette table nest donc pas en
  2FN
• Il faut donc diviser la table RESERVATION de la
  façon suivante
• RESERVATION (NoClient, NoSalle, DateDebut,
  DateFin, Cout)
• AUTOR_SALLE (NoSalle, DateDebut, Autorisation)
• Note il faut en même temps transférer
  lattribut DateDebut pour que la clé de
  AUTOR_SALLE soit unique

25
Passage à la 2e forme normale

• CLIENT (NoClient, Nom, Prenom, Statut, Reduction)
• SALLE (NoSalle, Description)
• RESERVATION (NoClient, NoSalle, DateDebut,
  DateFin, Cout)
• AUTOR_SALLE (NoSalle, DateDebut, Autorisation)
• FORFAIT (NoForfait, DateDebut, DateFin, Prix)
• CARTE (NoClient, NoForfait)
• ACTIVITÉ (NoActivité, Description, NoSalle)
• ACTIVITÉ FORFAITAIRE (NoForfait, NoActivite)
• ACT_EN_COURS (NoSalle, NoActivité)
• GROUPE (NoGroupe, Description, DateDebut,
  DateFin, NoActivite)
• EMPLOYE (NoEmploye, Nom, Prenom)
• AFFECTATION (NoGroupe, NoEmploye)
• COMPETENCE (NoEmploye, NoActivite, Fonction)
• Toutes les autres tables sont en 2FN, donc le
  modèle est maintenant en 2FN

26
Passage à la 3e forme normale, cas 1

• Le pourcentage de réduction dont bénéficie un
  client dépend de son statut
• Il y a donc, dans la table CLIENT, une DF entre
  Statut et Réduction (Statut ? Réduction)
• Cest une DF entre un attribut ordinaire et un
  autre attribut ordinaire, cette table nest donc
  pas en 3FN
• Il faut donc diviser la table CLIENT de la façon
  suivante
• CLIENT (NoClient, Nom, Prenom, Statut)
• REDUC_CLIENT (Statut, Reduction)

27
Passage à la 3e forme normale, cas 2

• Le coût de la réservation dune salle dépend de
  la période de lannée où elle commence, donc de
  la date de début de la réservation
• Il y a donc, dans la table RESERVATION, une DF
  entre DateDebut et Cout (DateDebut ? Cout)
• Cest une DF entre un attribut ordinaire et un
  autre attribut ordinaire, cette table nest donc
  pas en 3FN
• Il faut donc diviser la table RESERVATION de la
  façon suivante
• RESERVATION (NoClient, NoSalle, DateDebut,
  DateFin)
• COUT_RESERV (DateDebut, Cout)

28
Passage à la 3e forme normale

• CLIENT (NoClient, Nom, Prenom, Statut)
• REDUC_CLIENT (Statut, Reduction)
• SALLE (NoSalle, Description)
• RESERVATION (NoClient, NoSalle, DateDebut,
  DateFin)
• COUT_RESERV (DateDebut, Cout)
• AUTOR_SALLE (NoSalle, DateDebut, Autorisation)
• FORFAIT (NoForfait, DateDebut, DateFin, Prix)
• CARTE (NoClient, NoForfait)
• ACTIVITÉ (NoActivité, Description, NoSalle)
• ACTIVITÉ FORFAITAIRE (NoForfait, NoActivite)
• ACT_EN_COURS (NoSalle, NoActivité)
• GROUPE (NoGroupe, Description, DateDebut,
  DateFin, NoActivite)
• EMPLOYE (NoEmploye, Nom, Prenom)
• AFFECTATION (NoGroupe, NoEmploye)
• COMPETENCE (NoEmploye, NoActivite, Fonction)
• Toutes les autres tables sont en 3FN, donc le
  modèle est maintenant en 3FN

29
Passage à la FNBC

• On ne retrouve, dans aucune table, de DF entre un
  attribut ordinaire et un attribut faisant partie
  de la clé
• Le modèle est donc en FNBC et prêt à être
  implanté sur un SGBD relationnel

30
Centre Sportif Peter inc. Modèle relationnel
formel en FNBC

• CLIENT (NoClient, Nom, Prenom, Statut)
• REDUC_CLIENT (Statut, Reduction)
• SALLE (NoSalle, Description)
• RESERVATION (NoClient, NoSalle, DateDebut,
  DateFin)
• COUT_RESERV (DateDebut, Cout)
• AUTOR_SALLE (NoSalle, DateDebut, Autorisation)
• FORFAIT (NoForfait, DateDebut, DateFin, Prix)
• CARTE (NoClient, NoForfait)
• ACTIVITÉ (NoActivité, Description, NoSalle)
• ACTIVITÉ FORFAITAIRE (NoForfait, NoActivite)
• ACT_EN_COURS (NoSalle, NoActivité)
• GROUPE (NoGroupe, Description, DateDebut,
  DateFin, NoActivite)
• EMPLOYE (NoEmploye, Nom, Prenom)
• AFFECTATION (NoGroupe, NoEmploye)
• COMPETENCE (NoEmploye, NoActivite, Fonction)
• Note Les clés primaires sont soulignées et les
  clés étrangères sont en italiques

31
Des questions ?