Langage de définition des données

SQL Structured Query Language
Langage de définition des
données
INT Management
148
Plan du document
Introduction
Définition des données
Contraintes d’intégrité
slide 150
slide 152
slide 159
149
Présentation de SQL
Introduction
Fonctionnalités :
définition et manipulation de données au format
relationnel
contrôle des données
Le langage de manipulation
non procédural
emprunté à l'algèbre relationnelle et au calcul
relationnel de tuples
150
Présentation de SQL (2)
Introduction
Origine
langage SEQUEL du prototype de SGBD relationnel
SYSTEM/R (74-76)
laboratoire de recherche IBM à San José
Normalisation ISO
norme SQL1 (1986, 1989)
norme SQL2 (1992)
nouvelle norme en préparation SQL3
Langage de requêtes des SGBD relationnels
ORACLE (Oracle Corporation - 1977)
INGRES (Ingres Technology - 1980)
DB2 (IBM - 1984)
INFORMIX (Informix Inc - 1981)
SYBASE (Sybase Inc - 1984)
MySQL (1995)
151
Définition des données
Définition des données
définition des schémas des relations
définition de vues relationnelles
définition de contraintes d'intégrité
définition de droits
validation d'un traitement
définition du placement et des index
(non normalisé => SGBD dépendant !!)
152
LDD SQL vs. Modèle relationnel
Domaines limités
Clé facultative :
Doublons
Opérateur de projection différent ( DISTINCT)
Relation n’est plus un ensemble
153
Domaines de base
Numériques :
Définition des données
Entier : INTEGER, SMALLINT
Décimal : DECIMAL (m,n), NUMBER(m,n)
Réel flottant : FLOAT, REAL
Chaîne de caractères : CHAR (n), VARCHAR(n)
Temporel : DATE (dans la norme SQL2 !)
chaque SGBD possède d'autres domaines qui lui
sont propres
Valeur NULL : absence de valeur
154
Schéma d’une relation
Définition des données
Création
CREATE TABLE Vins (
num Integer,
cru Char(20),
annee Integer)
Mise à jour
Ajout d'un attribut (norme SQL2 !)
ALTER TABLE Vins ADD COLUMN degre Integer
Suppression (norme SQL2 !)
DROP TABLE Vins
155
BD Exemple: les vins
Vins(num, cru, annee, degre)
Recoltes(nvin, nprod, quantite)
Producteurs(num, nom, prenom, region)
Buveurs(num, nom, prenom, ville)
Commandes(ncde, date, nb, nvin, qte)
Livraisons(ncde, no_ordre, qteLivree)
BD Exemple
V
R
P
B
C
L
156
Modèle E/A de la BD des vins
Vins
Buveurs
num
cru
degré
annee
0,n
recoltes
BD Exemple
num
nom
prenom
ville
0,n
0,n
concerne
Passer
quantite
0,n
Producteurs
num
nom
prenom
region
1,1
1,1
Commandes
ncde
qté
date
0,n
Donner_lieu
Livraisons
(1,1)
No_ordre
qteLivrée
date
157
Exemple en SQL
CREATE TABLE VINS (
num integer,
cru char (40),
annee integer,
degre float)
CREATE TABLE PRODUCTEURS (
num integer,
nom char(40),
prenom char(40),
region char(40))
CREATE TABLE RECOLTES(
nprod integer,
nvin integer,
quantite integer)
158
Contraintes d’intégrité
Règles de gestion des données
Etat cohérent de la base
Vérifiées en permanence
159
Exemples de contraintes d’intégrité
Modèle relationnel :
Schéma de la BD Vins
Vins(num, cru, annee, degre)
Producteurs(num, nom, prénom, region)
Unicité de la clé (num dans vins)
CI référentielle (nvin vers num)
Domaine :
Année entre 1970 et 2000
Région : ‘ Bourgogne ’,
‘ Beaujolais ’, …
Non nullité :
Nom de cru de vin obligatoire
Recoltes(nprod, nvin, quantité)
Comportementale:
Le degré d’un vin est supérieur à 7
Le degré d’un vin augmente
160
Les CIs dans les SGBD
Quand les déclarer ?
Comment les exprimer?
À la création du schéma de BD
Au cours de la vie de la BD
SQL
Extensions de SQL
Qu’offrent les SGBD ?
Peu de CIs en général
Autres CIs vérifiées par programme
161
Les CIs dans les SGBD (2)
Normes :
SQL 86 : unicité, non nullité, vue avec « check option »
SQL89 : domaine, clé, intégrité référentielle avec « rejet »
SQL2 (SQL92) : intégrité référentielle avec «cascade
delete et update »
Définition d ’une CI
2 possibilités:
À chaque définition d’attribut
À la fin de la définition d’une relation
162
Exemple
CREATE TABLE VINS (
num integer PRIMARY KEY,
cru char (40) NOT NULL,
annee integer CONSTRAINT Cannee
CHECK (annee between 1970 and 2000),
degre float CONSTRAINT Cdegre
CHECK (degre between 9.0 and 15.0))
CREATE TABLE PRODUCTEURS (
num integer,
nom char(40),
prenom char(40),
region char(40)
PRIMARY KEY(num))
ALTER TABLE PRODUCTEURS
add CONSTRAINT Cregion CHECK
(region in ('Bourgogne', 'Beaujolais',
'Alsace', 'Jura', 'Corse'))
CREATE TABLE RECOLTES(
nprod integer,
nvin integer,
quantite integer)
ALTER TABLE RECOLTES
add primary key (nprod, nvin)
ALTER TABLE RECOLTES
add constraint refVIN
foreign key (nvin)
references
VINS(num) on delete cascade
ALTER TABLE RECOLTES
add constraint refREP
foreign key (nprod)
references
PRODUCTEURS(num) on delete
cascade
163
Problèmes liés aux CIs
Cohérence
Redondance
Pas de règles contradictoires
Age > 18 et âge > 21
Optimisation
Nombre minimal de données mises à jour en jeu pour la
vérification
Vérification sur certains types de màj uniquement
164