Upload
simawet
View
183
Download
1
Embed Size (px)
Citation preview
Cours 9Les formes normales
Étude de cas
Pierre Delisle
Université du Québec à ChicoutimiDépartement d’informatique 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 d’une liste de domaines
Cette table représente une occurence de la table personne, représentée en mode extension
Nom Prénom DateNaissance
Tremblay Joseph 1950-03-12
Bouchard Marie 1966-08-23
Girard Johanne 1943-06-30
Nom de la table
PERSONNE
Chaque ligne est un n-uplet, ou tuple, ou enregistrement
Chaque colonne est un attribut ou un champ
4
Le modèle relationnel (suite)
2 propriétés des tuples à respecter L’unicité des tuples : il ne peut y avoir de tuples
identiques L’ordre des tuples : l’ordre des tuples n’a pas
d’importance, c’est 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
n’importe quelle valeur (intervalle, type de données)
Ordre : l’ordre des attributs n’a pas d’importance
5
Le modèle relationnel (suite)
Représentation d’une 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 d’attributs
XY 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 s’accordent sur
leur valeur de X, ils s’accordent également sur leur valeur de Y
7
Dépendance fonctionnelle (DF)
Les dépendances fonctionnelles peuvent être internes DF entre attributs d’une 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 CLIENT
PK NoClient
Prenom Nom NoTel
FACTURE
PK NoFacture
DateFK1 NoClient
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 d’intégrité
Plus le niveau des formes normales est élevé pour une table, plus cette table sera exempte d’anomalies
Un bon MCD implique souvent un modèle relationnel ayant déjà atteint un bon niveau de normalisation L’analyse 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 d’un MCD en modèle relationnel n’est pas déjà en 1FN, c’est qu’il 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 n’est formée que d’un seul attribut La clé primaire contient tous les attributs de la
table Si la clé primaire a plus d’un 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 d’identifier 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 qu’entre "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 d’un 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 n’est 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é l’attribut 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 l’ancienne 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 l’anné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 n’est 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 d’un 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é n’est constituée que d’un seul attribut Tous les attributs sont dans la clé primaire IL n’y 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 lorsqu’elle 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.
FORFAIT
*NoForfaitDateDébutDateFinPrix
EMPLOYÉ
*NoEmployéNomPrénom
Est affecté
1,n
0,n
N:M
ACTIVITÉ
*NoActivitéDescription
Permet
1,n
1,n
N:M GROUPE
*NoGroupeDescriptionDateDebutDateFin
Est affecté 1,1
1:N
1,n
Peut être affectéFonction
0,n
Réserve
DateDébutDateFin
CoutAutorisation
N:M
CLIENT
*NoClientNomPrénomStatutRéduction
Achète
1,n
1,n
N:M
SALLE
*NoSalleDescription
Dirige
0,n
1,1
1:N
Autorise
DateDébutDateFin
0,n
N:M
N:M
0,n
0,n
Se pratique
1:N
1,n
1,1
0,n
0,n
20
Dictionnaire de données : Centre sportif Peter inc.
Attribut Type Description
Exemple
NoClient Texte Le numéro d’un client
DEL1234
Fonction Texte Fonction d’un employé sur une activité précise
Arbitre
Prix Monnaie Montant payé pour une carte d’abonnement
25.00
Réduction Numérique Pourcentage de réduction pour un client privilégié
20
... ... ... ...
21
MPD : Centre sportif Peter inc.
CLIENT
PK NoClient
Nom Prenom Statut Réduction
RESERVATION
PK,FK1 NoClientPK,FK2 NoSalle
DateDebut DateFin Cout Autorisation
SALLE
PK NoSalle
Description
FORFAIT
PK NoForfait
DateDebut DateFin Prix
ACTIVITÉ
PK NoActivité
DescriptionFK1 NoSalle
CARTE
PK,FK1 NoClientPK,FK2 NoForfait
ACT_EN_COURS
PK,FK1 NoSallePK,FK2 NoActivité
GROUPE
PK NoGroupe
Description DateDebut DateFinFK1 NoActivité
EMPLOYE
PK NoEmploye
Nom Prenom
AFFECTATION
PK,FK1 NoGroupePK,FK2 NoEmploye
COMPETENCE
PK,FK1 NoEmployePK,FK2 NoActivité
Fonction
ACT_FORFAITAIRE
PK,FK1 NoForfaitPK,FK2 NoActivité
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 d’autorisation d’une 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)
C’est une DF entre un attribut ordinaire et une partie de la clé, cette table n’est 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 l’attribut 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) C’est une DF entre un attribut ordinaire et un
autre attribut ordinaire, cette table n’est 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 d’une salle dépend de la période de l’anné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) C’est une DF entre un attribut ordinaire et un autre
attribut ordinaire, cette table n’est 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 ?