SQL (LDD)

Cours
“Bases de données”
3° année (MISI)
Antoine Cornuéjols
www.lri.fr/~antoine
[email protected]
Conception d’une base de données
1. Un modèle conceptuel :
le modèle entité- association
2. Le modèle relationnel
2.1. Définition
2.2. Règles de traduction
2.3. Dépendances entre données et formes normales
2.4. Le LDD SQL2
Le modèle relationnel
3
Langage de Définition de Données SQL2
Plusieurs versions de SQL
1989 : plus ancien standard
1992 : SQL-92 ou SQL-2
1999 : SQL-99 ou SQL-3 ((un peu) orienté objet)
2003 : SQL-2003 (fonctions pour XML)
SQL (Structured Query Language)
Interface de communication avec les SGBD relationnels
Langage non procédural.
Le modèle relationnel
4
Langage de Définition de Données SQL2
SQL permet :
de définir le schéma de la base de données (LDD)
de charger les tables relationnelles (LMD)
de manipuler les données stockées (LMD)
de gérer la base de données (LDD) : sécurité, organisation physique
Ici : aperçu de la partie LDD
Plus loin : le LMD
SQL n’est pas un langage de programmation complet

Le modèle relationnel
5
Langage de Définition de Données SQL2
Types SQL
Le modèle relationnel
6
Langage de Définition de Données SQL2
Création des tables
Commande : CREATE TABLE
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss DECIMAL (4))
Le modèle relationnel
7
Langage de Définition de Données SQL2
Les contraintes
Règles permettant d’assurer une certaine intégrité des données :
1. Un attribut doit toujours avoir une valeur. (Contrainte NOT NULL)
2. Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation
3. Un attribut dans une table est liée à la clé primaire d’une autre table (intégrité
référentielle)
4. La valeur d’un attribut doit être unique au sein de la relation
5. Autres règles s’appliquant à la valeur d’un attribut (e.g. min et max)
Le modèle relationnel
8
Langage de Définition de Données SQL2
Les clés
Clé
Un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière unique un tuple
d’une relation.
Clé primaire
Il peut y avoir plusieurs clés, mais l’une d’entre elles doit être choisie comme clé
primaire.
Choix capital : la clé primaire est la clé utilisée pour référencer une ligne et une seule à
partir d’autres tables.
Est spécifiée avec l’option PRIMARY KEY

Le modèle relationnel
9
Langage de Définition de Données SQL2
Clés Commande : PRIMARY KEY
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss DECIMAL (4),
PRIMARY KEY (email))
CREATE TABLE Notation (idFilm INTEGER NOT NULL,
email VARCHAR (50) NOT NULL,
note INTEGER DEFAULT 0,
PRIMARY KEY (titre, email))
Clé constituée de plusieurs attributs :
Le modèle relationnel
10
Langage de Définition de Données SQL2
Clé secondaire Commande : UNIQUE
On spécifie que la valeur d’un attribut est unique pour l’ensemble de la colonne.
CREATE TABLE Artiste (id INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
PRIMARY KEY (ID),
UNIQUE (nom, prenom));
Le modèle relationnel
11
Langage de Définition de Données SQL2
Clé étrangère Commande : FOREIGN KEY
Attributs qui font référence à une ligne dans une autre table.
CREATE TABLE Film (idFilm INTEGER NOT NULL,
nom VARCHAR (50) NOT NULL,
année INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (ideFilm),
FOREIGN KEY (idMES) REFERENCE Artiste,
FOREIGN KEY (codePays) REFERENCE Pays);
Référence la clé
primaire de la table
Artiste.
Le SGBD vérifiera, pour toute modification
pouvant affecter le lien entre les deux tables,
que la valeur de idMES correspond bien à une
ligne de Artiste.
Le modèle relationnel
12
Langage de Définition de Données SQL2
Clé étrangère
Modifications contrôlées :
1. l’insertion dans Film avec une valeur inconnue pour idMES
2. la destruction d’un artiste
3. la modification de id dans Artiste ou de idMES dans Film.
Que se passe-t-il si une violation d’une contrainte d’intégrité est détectée ?
Par défaut, la mise à jour est rejetée
On peut demander la répercussion de cette mise à jour de manière à ce que la
contrainte soit respectée
grâce à UPDATE et ON DELETE

Le modèle relationnel
13
Langage de Définition de Données SQL2
Clé étrangère
CREATE TABLE Film (idFilm VARCHAR NOT NULL,
année INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (ideFilm),
FOREIGN KEY (idMES) REFERENCE Artiste,
ON DELETE SET NULL,
FOREIGN KEY (codePays) REFERENCE Pays);
La destruction d’un metteur en scène déclenche la mise à NULL de la clé
étrangère idMES pour tous les films qu’il a réalisés.
CREATE TABLE Film (idFilm VARCHAR NOT NULL,
année INTEGER
CHECK (année BETWEEN 1890 AND 2000) NOT NULL,
genre VARCHAR (10)
CHECK (genre IN (‘Histoire’, ‘Western’,
‘Drame’)),
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (ideFilm),
FOREIGN KEY (idMES) REFERENCE Artiste,
ON DELETE SET NULL,
FOREIGN KEY (codePays) REFERENCE Pays);
Le modèle relationnel
14
Langage de Définition de Données SQL2
Énumération des valeurs possibles Commande : CHECK
Modification des attributs
ALTER TABLE Internaute ADD region VARCHAR(10);
ALTER TABLE Internaute MODIFY region VARCHAR(30) NOT NULL;
ALTER TABLE Internaute ALTER region SET DEFAULT ‘PACA’;
ALTER TABLE Internaute DROP region;
Le modèle relationnel
15
Langage de Définition de Données SQL2
Modification du schéma
où ACTION peut être :
ADD, MODIFY, DROP ou RENAME
Commande : ALTER TABLE nomTable ACTION description
Le modèle relationnel
16
Langage de Définition de Données SQL2
Modification du schéma
Commande : ALTER TABLE nomTable ACTION description
Différents types d’altérations sont possibles :
Ajout d’une colonne (ADD COLUMN)
Modification de la définition d’une colonne (MODIFY COLUMN)
Suppression d’une colonne (DROP COLUMN)
Modification du nom de la table ou d’une colonne (RENAME TO, RENAM
COLUMN)

Le modèle relationnel
17
Langage de Définition de Données SQL2
Commande : ALTER TABLE nomTable ACTION description
Exemples :
ALTER TABLE Livre RENAME TO Livre2;
ALTER TABLE Livre RENAME COLUMN Titre TO Titre2;
ALTER TABLE Livre
ADD COLUMN Data_Achat DATE,
MODIFY Auteur VARCHAR2(30) NOT NULL;
DROP TABLE Livre [CASCADE CONSTRAINT];
Le modèle relationnel
18
Langage de Définition de Données SQL2
Suppression d’une table
Commande : SQL DROP TABLE
Exemple :
Remarque : L’option cascade constraint permet de supprimer la table même si des
contraintes d’intégrité référentielle portent sur des colonnes de cette table.
INSERT INTO Livre (Auteur, Titre, Année, Prix, Genre)
VALUES (‘Balzac, ‘Le père Goriot’, 1834, 148.5, ‘Roman’);
Le modèle relationnel
19
Langage de Définition de Données SQL2
Insertion d’un tuple dans une table
Commande : INSERT INTO
Exemples :
Insère l’ensemble des valeurs associées à un tuple de la table.
INSERT INTO Livre
VALUES (‘HUGO’, ‘HERNANT’, 1830, ‘THEATRE’, 120.00);
Insère un nouveau tuple en utilisant un ordre différent de l’ordre de
de définition.
INSERT INTO Livre (Auteur, Titre, Année, Genre)
VALUES (‘Balzac, ‘Le père Goriot’, 1834, ‘Roman’);
Le modèle relationnel
20
Langage de Définition de Données SQL2
Insertion d’un tuple dans une table
Commande : INSERT INTO
Exemples :
Insère un nouveau tuple sans initialiser le prix.