Cours 6

Publié par

Cours 6 Les formes normales o Les formes normales sont différents stades de qualité qui permettent d’éviter des anomalies dans les bases de données relationnelles o Représentent l’état des tables relationnelles en fonction de leurs dépendances fonctionnelles o Il existe 5 formes normales principales et deux extensions 1. Première forme normale (1FN) 2. Deuxième forme normale (2FN) 3. Troisième forme normale (3FN) 4. Forme normale de Boyce-Codd 5. Quatrième forme normale (4FN) 6. Cinquième forme normale (5FN) 7. Forme Normale Domaine-Clé o Plus le niveau de normalisation est élevé, plus la table sera exempte d’anomalies o Une table en forme normale de niveau x est automatiquement en forme normale de niveau x-1 o À la suite d’une modélisation, la plupart des modèles seront déjà en forme normale de Boyce-Codd (ou presque) Première Forme Normale (1FN) o Une table est en forme normale si tous ses attributs sont simples et non décomposables o Si une table n’est pas en 1FN, alors elle en FNN (Forme Non Normalisée) o Si les tables relationnelles résultant de la modélisation ne sont pas déjà en 1FN, il serait approprié de retourner à l’étape de modélisation. Une modélisation de qualité minimale devrait toujours être en 1FN. VOL VOL JOURNÉES-VOL NoVol* NoVol* NoVol* Code AéroportDép# Code AéroportDép# Jour* CodeAéArr# CodeAéArr# HeureDécollage HeureDécollage HeureAtterrissage HeureAtterrissage Jours o Ici, ...
Publié le : vendredi 23 septembre 2011
Lecture(s) : 28
Nombre de pages : 8
Voir plus Voir moins
Cours 6
Les formes normales
o
Les formes normales sont différents stades de qualité qui permettent d’éviter des anomalies dans les bases
de données relationnelles
o
Représentent l’état des tables relationnelles en fonction de leurs dépendances fonctionnelles
o
Il existe 5 formes normales principales et deux extensions
1.
Première forme normale (1FN)
2.
Deuxième forme normale (2FN)
3.
Troisième forme normale (3FN)
4.
Forme normale de Boyce-Codd
5.
Quatrième forme normale (4FN)
6.
Cinquième forme normale (5FN)
7.
Forme Normale Domaine-Clé
o
Plus le niveau de normalisation est élevé, plus la table sera exempte d’anomalies
o
Une table en forme normale de niveau x est automatiquement en forme normale de niveau x-1
o
À la suite d’une modélisation, la plupart des modèles seront déjà en forme normale de Boyce-Codd (ou
presque)
Première Forme Normale (1FN)
o
Une table est en forme normale si tous ses attributs sont simples et non décomposables
o
Si une table n’est pas en 1FN, alors elle en FNN (Forme Non Normalisée)
o
Si les tables relationnelles résultant de la modélisation ne sont pas déjà en 1FN, il serait approprié de
retourner à l’étape de modélisation. Une modélisation de qualité minimale devrait toujours être en 1FN.
o
Ici, "Jours" est non simple, alors on crée une nouvelle entité pour représenter la multiplicité des jours de vol,
ce qui donne des tables résultantes en 1FN
Deuxième Forme Normale (2FN)
o
Une table est en deuxième forme normale si elle est en 1FN et que l’une de ces trois conditions est respectée
1.
La clé primaire n’est formée que d’un seul attribut
2.
La clé primaire contient tous les attributs de la table
3.
Si la clé a plus d’un attribut, une dépendance fonctionnelle ne doit jamais exister entre une partie
seulement de la clé et un autre attribut de la table. Tout attribut ne faisant pas partie de la clé doit
dépendre de toute la clé (dépendance fonctionnelle totale)
VOL
NoVol*
Code AéroportDép#
CodeAéroportArr#
HeureDécollage
HeureAtterrissage
Jours
JOURNÉES-VOL
NoVol*
Jour*
VOL
NoVol*
Code AéroportDép#
CodeAéroportArr#
HeureDécollage
HeureAtterrissage
o
Pour passer de la première forme normale à la deuxième, il faut diviser chaque table ne satisfaisant pas les
critères en deux tables distinctes
o
Pour diviser une table en deux, il faut
o
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.
o
Éliminer ces attributs (ceux qui ne font pas partie de la clé) de la table originale.
Exemple : Supposons la table suivante représentant 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.
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)
Troisième Forme Normale (3FN)
o
Une table est en 3FN si elle est déjà en 2FN et qu’aucun attribut ne faisant pas partie de la clé ne dépend
d’un autre attribut ne faisant pas partie lui non plus de la clé
o
En 3FN, les dépendances fonctionnelles entre deux attributs ordinaires (ne faisant par partie de la clé) ne
sont pas autorisées.
o
Pour passer de 2FN à 3FN, il faut
o
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.
o
É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
Exemple du livre : Dans le cas des voitures usagées, toutes les voitures de la même année sont vendues au
même prix.
VOITURE (NoStock, Marque, Modèle, Année, Couleur, Prix, TélFabricant)
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, TélFabricant)
PRIXVENTE (Année, Prix)
Forme Normale de Boyce-Codd (FNBC)
o
Extension de la 3FN, plus rigide que celle-ci, faite par R.F. Boyce et E.F. Codd, qui ont constaté que la 3FN
pouvait comporter certaines anomalies
o
Un modèle relationnel en FNBC est considéré comme étant de qualité suffisante pour une l’implantation
o
Une table est en FNBC si elle est déjà en 3FN et qu’aucun attribut faisant partie de la clé ne dépend d’un
attribut ne faisant pas partie de la clé primaire
o
Les cas de tables modélisées et transformées en 3FN qui ne sont pas déjà en FNBC sont très rares
o
Pour passer de la 3FN à la FNBC, il faut
o
Diviser chaque table ne satisfaisant pas au critère ci-haut en deux tables. La nouvelle table aura
comme clé l’attribut ordinaire dont provient la dépendance et comme attributs la partie de la clé qui
en dépend.
o
Remplacer la partie de la clé concernée par l’attribut ordinaire (qui est devenu la clé de l’autre
table)
Exemple : CLAVIER (MarqueClavier, NombreTouches, TypeClavier)
Supposons qu’il y a une DF entre le type de clavier et le nombre de touches. Il faut donc diviser
en deux tables de la façon suivante :
CLAVIER (MarqueClavier, TypeClavier)
TOUCHES (TypeClavier, NombreTouches)
Note : Il y a une erreur dans le livre, le résultat ci-haut est le bon
Résumé des Formes Normales
Normalisation du cas de l’aéroport du dernier cours
Modèle relationnel résultant de la modélisation
AÉROPORT (CodeAéroport, Nom, Ville, Prov-État, Pays, Altitude, LongueurPiste)
VOL (NoVol, Code AéroportDép, CodeAéroportArr, HeureDécollage, HeureAtterrissage)
JOURNÉES-VOL (NoVol, Jour)
RÉSERVATION (NoRéservation, NoClient, NoEnvolée, Prix, Classe)
ENVOLÉE (NoEnvolée, NoVol, CodeAvion, Date)
PASSAGER (NoClient, Nom, NoTél)
PILOTE (NoPilote, Nom, AnnéesExp)
AVION (CodeAvion, Modèle, TypeMoteur, LongueurPisteReq, NbSiègesPrem, NbSiègesDeux, NbSiègesAff)
HABILITATION (NoPilote, CodeAvion)
Tous les attributs sont simples et non décomposables, donc ce modèle est en 1FN.
Pour respecter les contraintes du modèle relationnel, il faut décomposer VOL (exception "2 à plusieurs" du dernier
cours) en deux tables :
VOL (NoVol, Code Aéroport, HeureDécollage, HeureAtterrissage)
1FN
2FN
3FN
FNBC
Éliminer toute
dépendance non
totale des
attributs sur la
clé primaire
Éliminer toute
dépendance
entre deux
attributs ne
faisant pas
partie de la clé
primaire
Éliminer toute
dépendance à
partir d’un
attribut
ordinaire vers
une partie de la
clé primaire
AEROPORT-DE-VOL (NoVol, CodeAéroport, Direction)
Les tables AÉROPORT, VOL, RÉSERVATION, ENVOLÉE, PASSAGER, PILOTE et AVION ont des clés
formées d’un seul attribut (condition 1 de la 2FN) et les tables JOURNÉES-VOL et HABILITATION ont des clés
constituées de tous les attributs de la table (condition 2 de la 2FN), donc le modèle est en 2FN.
Dans la table AÉROPORT, il existe une DF qui a pour origine les attributs "Ville" et "Prov-État" et dont dépend
"Pays" (Avec une ville et une province (ou état) donnés, il ne correspond qu’un seul pays). On divise donc cette
table en deux de la façon suivante :
AÉROPORT (CodeAéroport, Nom, Ville, Prov-État, Altitude, LongueurPiste)
VILLE (Ville, Prov-État, Pays)
Il n’existe pas d’autres DF entre attributs ne faisant pas partie de la clé, donc ce modèle est en 3FN.
Il n’existe pas de DF entre des attributs ordinaires et des parties de clé, donc le modèle est en FNBC.
Le modèle final en FNBC est donc le suivant :
AÉROPORT (CodeAéroport, Nom, Ville, Prov-État, Altitude, LongueurPiste)
VILLE (Ville, Prov-État, Pays)
VOL (NoVol, Code Aéroport, HeureDécollage, HeureAtterrissage)
AEROPORT-DE-VOL (NoVol, CodeAéroport, Direction)
JOURNÉES-VOL (NoVol, Jour)
RÉSERVATION (NoRéservation, NoClient, NoEnvolée, Prix, Classe)
ENVOLÉE (NoEnvolée, NoVol, CodeAvion, Date)
PASSAGER (NoClient, Nom, NoTél)
PILOTE (NoPilote, Nom, AnnéesExp)
AVION (CodeAvion, Modèle, TypeMoteur, LongueurPisteReq, NbSiègesPrem, NbSiègesDeux, NbSiègesAff)
HABILITATION (NoPilote, CodeAvion)
Le langage SQL
o
Langage de requête de requête standard pour la majorité des SGBD relationnels
o
Peut être utilisé de façon autonome ou intégré à un programme
o
Contient des énoncés de définition, de requête et de manipulation
Énoncés de définition
CREATE TABLE
o
Permet de créer une nouvelle table et spécifier ses attributs
o
Syntaxe : CREATE TABLE <Nom de la table> (<Nom Champ> <TypeChamp> <Contrainte>);
o
Le contenu de la parenthèse se répète pour chaque champ
o
La contrainte "NOT NULL" permet d’exiger que ce champ contienne toujours une donnée
Exemple :
CREATE TABLE PILOTE ( NoPilote CHAR(10) NOT NULL,
Nom CHAR(25),
AnneesExp SMALLINT
PRIMARY KEY (NoPilote)
);
o
Pour chaque attribut, il faut spécifier le type de données. Les types principaux sont :
o
CHARACTER(Taille) ou CHAR(Taille)
: type alphanumérique (lettres et chiffres) qui permet
d’entrer du texte ou des codes. Dans les parenthèses on inscrit la taille maximale de cette chaîne
de caractères.
o
INTEGER et SMALLINT
: représentent des données numériques entières. SMALLINT permet
d’entrer des petits entiers (variant entre –32 767 et 32 767) et INTEGER permet de plus grands
entiers (entre –2 147 483 647 et 2 147 483 647).
o
DECIMAL(x,y) :
représente des données numériques réelles. x représente le nombre de chiffres
significatifs du nombre réel et y représente le nombre de chiffres après le point. Par exemple,
DECIMAL(12,4) représentera un nombre réel de 12 chiffres, dont 4 après le point.
o
DATE
permet de représenter une date (année/mois/jour)
o
Il existe d’autres types de données en SQL, mais celles-ci sont les principales et les plus utilisées
DROP TABLE
o
Permet d’éliminer une table de la B.D.
o
Syntaxe : DROP TABLE <Nom Table> ;
Exemple :
DROP TABLE PILOTE ;
ALTER TABLE
o
Permet de modifier la structure d’une table
o
Permet, entre autres, d’ajouter et d’éliminer et de modifier des attributs de la table
o
Ajout d’un attribut :
ALTER TABLE <Nom de la table> ADD <Nom Champ> <Type Champ> <Contrainte>
Exemple : ALTER TABLE PILOTE ADD Adresse Char(25) ;
o
Suppression d’un attribut :
ALTER TABLE <Nom de la table> DELETE <Nom Champ>
Exemple : ALTER TABLE PILOTE DELETE Adresse ;
o
Modification du type d’un attribut
ALTER TABLE <Nom de la table> MODIFY <Nom Champ> <Type Champ> <Contrainte>
Exemple : pour augmenter de 10 caractères la taille possible du nom du pilote et obliger qu’une valeur
soit affectée à cet attribut lors de l’entrée de données
ALTER TABLE PILOTE MODIFY Nom CHAR(35) NOT NULL ;
Énoncés de manipulation
o
Permettent d’ajouter, d’enlever et de manipuler des données à l’intérieur de la B.D.
INSERT
o
Permet d’ajouter des données dans une table
o
Syntaxe : INSERT INTO <Nom Table> VALUES (Valeur1, Valeur2,...)
Exemple : INSERT INTO PILOTE VALUES (‘113B45’, ‘Jean Girard’, 4) ;
DELETE
o
Permet d’éliminer des données d’une table selon certains critères
o
Syntaxe : DELETE FROM <Nom Table> WHERE <Condition>
Exemple : DELETE FROM PILOTE WHERE AnneesExp < 5;
Élimine de la B.D. les pilotes qui ont moins de 5 années d’expérience
UPDATE
o
Permet de modifier des données dans une table selon certains critères
o
Syntaxe : UPDATE <Nom Table> SET <NomAttribut> = <Nouvelle Valeur> WHERE <Condition>
Exemple : UPDATE PILOTE SET AnneesExp=AnneesExp+1 WHERE Nom ‘Jean Girard’;
SELECT
o
Opération primordiale en SQL
o
Sert à faire des opérations algébriques de sélection, projection et jointure
o
Syntaxe : SELECT <liste des champs> FROM <liste des tables> WHERE <condition> ;
Projection
o
La liste des champs sur lesquels on veut faire la projection est spécifiée après le SELECT, la liste des tables
ne contient qu’une seule table et il n’y a pas de condition
SELECT Nom FROM PILOTE ;
Donne la liste des noms des pilotes
SELECT NoPilote, Nom FROM PILOTE ;
Donne la liste des numéros et noms des pilotes
Sélection
o
Pour une simple sélection, la liste des champs est spécifié par une astérisque "*" et n’y a qu’une seule table
SELECT * FROM PILOTE WHERE AnneesExp > 10 ;
Permet d’obtenir la liste des pilotes qui ont plus de 10 ans d’expérience. Le symbole "*" signifie "tous",
donc cette requête équivaut à une sélection précédée d’une projection sur tous les champs de la table, donc
d’une sélection simple en algèbre relationnelle.
SELECT * FROM PILOTE WHERE AnneesExp > 10 AND Nom=’Tremblay’;
Sélection contenant plusieurs conditions. Donne la liste des pilotes qui ont plus de 10 ans d’expérience et
qui ont Tremblay comme nom de famille.
Jointure
o
Pour une simple jointure, la liste des champs est spécifiée par "*", il y a deux tables ou plus dans la liste et
la condition est vide
SELECT * FROM PILOTE, HABILITATION ;
Donne la liste des habilitations reliant les pilotes et les avions, ainsi que les informations concernant les
pilotes
On peut, bien entendu, combiner ces trois opérations de manipulation pour construire des requêtes plus complexes.
o
Projection et sélection
SELECT NoReservation, Prix FROM RESERVATION WHERE Classe=’Affaires’ ;
o
Jointure et projection
SELECT NoEnvolee, HeureDecollage FROM ENVOLEE, VOL ;
o
Sélection et jointure
SELECT * FROM VOL, AÉROPORT WHERE Ville=’Montreal’ ;
o
Projection, sélection et jointure
SELECT NoVol, Ville FROM VOL, AÉROPORT-VOL, AÉROPORT, VILLE WHERE Pays=’Canada’
AND Direction=’Depart’;
Énoncés d’indexation
o
On peut définir des index pour certains attributs d’une table. Un des buts principaux des index est de
réduire le temps de traitement des requêtes
o
Syntaxe : CREATE INDEX <Nom Index> ON <Nom Table (Attribut)>
Exemple : Soit la requête suivante :
SELECT * FROM RESERVATION WHERE Prix = 400 ;
Par défaut, le système effectue cette requête en vérifiant une par une toutes les lignes de la table et en
ajoutant la ligne donnée au résultat si la condition est vraie.
Si le nombre d’enregistrements de la table est petit, alors il n’est pas profitable d’utiliser un index. Le temps
de gestion de l’index serait alors trop lourd pour le bénéfice qu’il apporte. Cependant, si la table contient
des milliers d’enregistrements, alors l’index permettra de réduire le temps de traitement en classifiant les
enregistrements et en permettant l’accès direct à ceux qui respectent la condition.
CREATE INDEX IndexPrix ON RESERVATION (Prix) ;
o
On peut simuler une clé par un index en spécifiant que l’index doit être unique pour chaque enregistrement.
Syntaxe : CREATE UNIQUE INDEX <Nom Index> ON <Nom Table (Attribut)> . Un index unique
permet de garantir qu’une ou plusieurs colonnes d’une table ne contient pas de valeurs identiques.
Soyez le premier à déposer un commentaire !

17/1000 caractères maximum.