Illustration par l`exemple

Base de Données
3 février 2001
M Cieloski
LE L D D
Le langage de description de données
Il permet d’exprimer les ordres de définition dans les structures
d’hébergement des données, notamment les tables, les vues et les index.
Il permet de créer, modifier ou supprimer des tables.
Les types de données :
alphanumérique : CHAR(n) liste de caractères, n
fixe la taille ; VARCHAR(n) liste de caractères de taille variable de longueur n.
Numérique : NUMBER(n ;d) c’est un nombre de n
chiffre de longueur (n) dont (d) chiffres après la virgule ;
De type INTEGER qui regroupe les entiers
FLOAT numérique flottant
SMALLINT (-32768 à +32767)
DATE type date
TIME heure
DATE/TIME date+heure
I CREATION DE TABLE
CREATE TABLE : permet de créer une table, de définir des colonnes, de leur
associer un type et éventuellement des contraintes à vérifier.
~> CREATE TABLE <nom_table> (<nom_colonne> <type>, <- - - > <- - - >) ;
Exemple CREATE TABLE département (n°_dep number(31),---) ;
Il est également possible de créer une table en insérant des lignes à la création
CREATE TABLE département (n°_dep number(31),---)
AS SELECT <nom_champ> FROM <table> avec conditions
II EXPRESSION DES CONTRAINTES D’INTEGRITES
La gestion automatique des contraintes d’intégrités est l’un des outils de gestion de
base de données, selon les contraintes, différentes anomalies peuvent se déclencher,
dès qu’une saisie, une modification, ou une destruction de données est effectuée.
Dès qu’un accès non conforme aux contraintes spécifiées survient, l’action est
automatiquement rejetée, par suite, elle est présentée à l’utilisateur si le traitement
se fait en interactif ou rangé dans une table qui trace les erreurs si le traitement se
fait en différé (batch).
LES DIFFERENTES CONTRAINTES :
1
Base de Données
3 février 2001
M Cieloski
Contrainte de clé : Ce premier type de contrainte traité par le SGBD permet de
vérifier l’unicité des clés de chacune des tables (clé primaire). Une clé primaire peut
être constituée de plusieurs colonnes. Elle doit répondre à une obligation ; Quelque
soit la table si une clé primaire est définie, elle doit être présente dans chaque
enregistrement et aucun de ses composants ne doit être NULL.
Si la clé à déjà été saisie cela déclenche une erreur.
Contrainte de type de données : permet de vérifier les types de données saisie et les
domaines de validité (ex : note entre 0 et 20)
Contrainte d’intégrité référentielle : La gestion des contraintes d’intégrités permet
aussi de vérifier automatiquement la présence de données référencées dans des
tables différentes.
C’est à dire qu’une contrainte d’intégrité référentielle peut s’appliquer dès qu’une clé
primaire d’une table est utilisée comme référence dans une autre table.
(ex : num_spec est une clé étrangère dans les tables jouer et representaion)
La clé étrangère : Elle peut être constituée de plusieurs colonnes, le n° de produit
(clé primaire de la table produits), mais elle est clé étrangère de la table commandes
quand au n° de fournisseur, clé primaire de la table fournisseurs et clé étrangère
dans commandes.
Anomalies de suppression : Si on supprime le fournisseur 17, 3 commandes de la
table commande deviennent incohérentes ; Elle doivent d’abord être supprimées
dans la table commandes.
Anomalies de modification : Si on transforme le n° de fauteuil rouge de 102 à 112,
les 2 enregistrements de la table des commandes doivent également être modifiés.
Anomalies d’ajout : Si on tente d’ajouter une commande qui se réfère au produit
111 ; L’ajout est refusé car il n’existe pas dans la table produits.
En résumé : Grâce à la gestion des contraintes dont le SGBD s’occupe à chaque
action sur les données cela contribue à la cohérence de la base.
3 façons selon les souhaits de l’utilisateur.
- un simple signalement de l’anomalie (message)
- Effacement automatique des tuples
- MAJ des tuples qui utilise la clé étrangère qui référence une clé primaire qui
vient de changer de valeur.
Le LDD1 permet d’exprimer ces différentes contraintes au moment de la création de
la table associée à la définition d’une colonne, il est possible d’ajouter l’expression de
ces différentes contraintes.
CONSTRAINT
1
: Permet de nommer la contrainte
LDD langage de description des données
2
Base de Données
3 février 2001
M Cieloski
DEFAULT
: Valeur par défaut d’une colonne
NOT NULL
: Une colonne n’est pas nulle
UNIQUE
: Toutes les valeurs d’une colonne doivent être différentes
CHECK
: Vérification de la validité suivi de la condition
CHECK (condition)
PRIMARY KEY
: (liste de colonnes)
FOREIGN KEY
: (liste de colonnes 1) Références table (liste colonnes 2)
Les modification automatiques à faire sur la clé étrangère donc un changement de la
clé primaire sont également exprimées par des clauses.
ON DELETE RESTRICT
CASACADE
SET NULL
SET DEFAULT
: donne un échec s’il existe encore dans la table
:
: place valeur null à place de toute occurence
:
Idem pour la mise à jour
ON UPDATE RESTRICT
CASCADE
SET NULL
SET DEFAULT
Dans les 2 cas si on ne spécifie pas, c’est la valeur RESTRICT qui est retenue par
défaut.
Illustration par l’exemple
 Supprimer le produit 103
Delete From produits
Where pno=103
CREATE TABLE commandes
(cno integer
pno integer
fno integer
qute integer
PRIMARY KEY (cno)
FOREIGN KEY (pno) references produits (pno)
ON DELETE RESTRICT);
Dans ce cas de figure mon ordre va être refusé car le produit 103 est référencé dans
la table commandes.
Delete From produits
Where pno=103
CREATE TABLE commandes
(cno integer
pno integer
3
Base de Données
3 février 2001
M Cieloski
fno integer
qute integer
PRIMARY KEY (cno)
FOREIGN KEY (pno) references produits (pno)
ON DELETE CASCADE);
Il aurait supprimé toutes les lignes qui références le produit 103 dans la table
commandes puis dans la table produits.
Delete From produits
Where pno=103
CREATE TABLE commandes
(cno integer
pno integer
fno integer
qute integer
PRIMARY KEY (cno)
FOREIGN KEY (pno) references produits (pno)
ON DELETE SET NULL);
Où il va trouver 103 dans la table commandes il va mettre NULL.
 La création d’une table produits avec pour contrainte le n° de produit sont une
clé primaire < 200, le prix du produit compris entre 1000 F et 9000 F, le poids
ne devrait excéder 100Kg, la couleur rouge, verte ou jaune.
CREATE TABLE produits
(pno integer, CHECK (pno<200),
design char (10),
prix integer, CHECK (prix BETWEEN 1000 and 9000),
poids integer, CHECK (poids < 100),
couleur char(10), CHECK (couleur IN (‘rouge’,’vert’,’jaune’)),
CONSTRAINT PK_produits primary key (pno)) ;
 Création de la table des commandes, avec cno primary key, fno et pno clés
étrangères en spécifiant effacement en cascade.
CREATE TABLE commandes
(cno UNIQUE NOT NULL,
fno integer,
pno integer,
qute integer,
CONSTRAINT PK_commandes primary key(cno),
CONSTRAINT FK_fournisseurs foreign key(fno),
References fournisseurs(fno),
ON DELETE CASCADES,
CONSTRAINT FK_produits foreign key(pno),
References produits(pno),
4
Base de Données
3 février 2001
M Cieloski
ON DELETE CASCADES) ;
LES TRIGGER : Nouveau type de contrainte qui s’exprime au travers d’un
déclencheur (en fait un script SQL), lors de l’ajout, la suppression, la modification
… .Il se lance avant ou après la modif. Supp. … ou l’associe à une action INSERT,
DELETE ou UPDATE.
Exemple : Tracer les action d’un utilisateur sur les destructions dans la table
commandes.
CREATE TRIGGER Espion
AFTER DELETE
ON commandes
(insert into trace
values (USER, SYSDATE, commandes.cno)) ;
LES VUES
C’est une table dont les données ne sont pas physiquement stockées mais qui
se référent à des données stockées dans d’autres tables.
C’est donc une fenêtre sur la table qui permet à chacun de voir les données
comme il le souhaite.
On peut ainsi définir plusieurs vues à partir d’une même table et en faire
varier la composition en fonction des utilisateurs. Il est aussi possible de combiner
des données venant de plusieurs tables, d’autres vues ou données calculées dans
une vue (données brutes ou transformées).
CREATE VIEW nom
(liste_col)
As selection des données ;
 Créer une vue qui présente la table des produits avec les prix en euro.
CREATE VIEW produits_euro
AS select pno,design,prix/6.55957
From produits;
 Création d’une vue qui montre la table des commandes avec les noms des
fournisseurs et produits en clair.
CREATE VIEW commandes
AS select cno,nom,design,qute,
From produits P,
fournisseurs F,
commandes C,
where F.fno = C.fno
5
Base de Données
3 février 2001
M Cieloski
and P.pno = C.pno;
AVANTAGES DES VUES :
-
les vues permettent de rendre les applications moins dépendantes des
évolutions des tables.
Elle permet également de restreindre l’accès d’une table dans un sousensemble de colonnes et à un sous-ensemble de lignes.
Il est possible de rassembler dans un seul objet des données qui sont
éparpillées dans plusieurs tables.
Elles permettent de simplifier la programmation dans la mesure ou une partie
du select est déjà codé dans la vue.
Les vues permettent de protéger l’accès aux tables en fonction des
utilisateurs.
Utiliser une vue partielle sur 1 ou plusieurs tables afin d’interdire l’accès aux
tables principales.
Pour faire une mise à jour au travers d’une vue, celle ci n’est réalisable que si
la clause From ne fait référence qu’à une seule table (ou une autre vue). Pour
que la vue soit modifiable, elle ne doit pas comporter de Distinct dans on
Select, ne pas avoir de fonction appliquée sur une colonne, pas de Group By,
ni d’union, pas d’Intercept, ni d’Except, ni se sous requête.
LA CREATION D’INDEX
Permet d’accéder rapidement et directement aux colonnes et lignes d’une
table.
On ne peut pas créer d’index sur une vue.
C’est un recueil de statistique sur les opérations les plus communes et les plus
fréquemment demandées, sur la localisation des données, mise à jour à chaque
modification de données.
SYNTAXE :
CREATE INDEX <nom-index>
ou CREATE UNIQUE INDEX(colonne unique)
ON <nom_table> (champs, …) ;
Si on sait que l’on va y accéder souvent, on peut associer à coté du nom : ASC
ou DESC.
Un INDEX, si le SGBD ne le fait pas on le fait sur les clés primaires, voir sur les clés
étrangères.
Dans le cas de jointures complexes, sur les champs de la jointure.
Création dur les éléments les plus discriminant.
~> Si je veux supprimer une table. J’utiliserais la solution :
6
Base de Données
3 février 2001
M Cieloski
DROP TABLE <nom_table> ;
~> Si je veux supprimer une vue. J’utiliserais la solution :
DROP VIEW <nom_vue> ;
~> Si je veux supprimer un index. J’utiliserais la solution :
DROP INDEX <nom_vue> ;
~> Si je veux modifier ou supprimer les colonnes d’une table :
ALTER <nom>
ADD (ajouter)
MODIFY (modifier)
DROP (supprimer)
Pour ajouter la colonne prénom à la table fournisseurs.
ALTER TABLE fournisseurs
ADD prénom char(15) ;
Si je veux modifier le champ qute de la table commandes.
ALTER TABLE commandes
ADD qute NUMBER(8,2) ;
Si je veux supprimer le prenom de la table fournisseur.
ALTER TABLE fournisseurs
DROP prenom char(15) ;
Attention à la suppression :
On ne peut supprimer une colonne si elle est référencée dans une vue ou sur un
index ou dans une autre table.
7