Université Virtuelle de Tunis - Institut Supérieur d’Informatique

Module 2 – Section 2 : Le langage de définition des données (LDD) de SQL

Riadh ZAAFRANI Octobre 2020

1ère année MP2L

Page 1

09/10/2020

Riadh Zaafrani

Plan du cours

 Création des tables avec SQL  Modification de la structure

d’une table avec SQL

 Les contraintes d’intégrité sous

SQL

Page 2

09/10/2020

Riadh Zaafrani

Les tables

 Les tables représentent

le mécanisme de stockage des

données dans une base Oracle.

 Une table contient un ensemble fixe de colonnes.

 Chaque

colonne caractéristiques spécifiques.

possède

nom ainsi

que

des

 Les opérations de création, de suppression et de modification des tables mettent à jour le dictionnaire de données du SGBD. On rappelle que le dictionnaire de données est une structure propre au SGBD qui contient la description des objets du SGBD (base de données, tables, colonnes, droits, etc.).

Page 3

09/10/2020

Riadh Zaafrani

Création des tables

 La création d’une table est une opération importante qu’il

faut entreprendre avec soin. C’est lors de cette étape que l’on définit le type de données, la clé, les index éventuels et qu’il convient d’imposer des contraintes de validation garantissant la bonne qualité des informations entrées dans la table.

 La forme générale de l’instruction de création de table est la suivante :

 CREATE TABLE <Nom de la table> (

liste des colonnes avec leur type séparées par ,) ;

 CREATE TABLE voiture (NumVoit INT,

Marque CHAR(40), Type CHAR(30), Couleur CHAR(20)) ;

Page 4

09/10/2020

Riadh Zaafrani

Création des tables

 Le nom de la table ou d’une colonne ne doit pas dépasser 128 caractères. Il commence par une lettre, contient des chiffres, des lettres et le caractère « _ ».

 Attention de même à ne pas utiliser un mot clé SQL.

 Les tables peuvent être créées de manière temporaire : elles seront donc effacées à la fin de la session de l’utilisateur à l’aide du mot clé TEMPORARY.

 CREATE TEMPORARY TABLE temporaire (

Identifiant INT, Jour DATE, Valide BOOLEAN ) ;

Page 5

09/10/2020

Riadh Zaafrani

Création des tables  Les tables peuvent être issues directement du résultat

d’une requête en utilisant le mot clé AS :

 c’est particulièrement commode pour pouvoir disposer de lors d’une

résultats intermédiaires en fin de vérification, série de manipulations sur une table.

 CREATE TEMPORARY TABLE resultat

AS (SELECT Vo.Marque, Vo.Couleur FROM voiture AS Vo);

Page 6

09/10/2020

Riadh Zaafrani

Type de données

 Le type de données est choisi essentiellement en la fonction des opérations qui sont effectuées sur colonne.

 Le choix du type permet également de mettre en place un premier niveau de restriction sur le contenu des données : une colonne de type numérique ne pourra pas contenir de caractères. Des restrictions plus fines seront définies à la section « Contraintes d’intégrité ».

 Voici une liste (non exhaustive) des types de données

SQL.

Page 7

09/10/2020

Riadh Zaafrani

Type de données

INT SMALLINT REAL FLOAT(n) NUMBER (n,[d])

CHAR(n)

Entier standard (32 bits) Entier « petit » (16 bits) Réel (taille spécifique au SGBD) Réel (représenté sur « n » bits) Champ de longueur variable de n chiffres dont d après la virgule, acceptant des nombres négatifs.

Chaîne de caractères de longueur « n » (codage ASCII 1 octet)

VARCHAR(n) Chaîne de caractères de longueur maximale « n »

NCHAR(b)

NVARCHAR( b)

(codage ASCII 1 octet) Chaîne de caractères de longueur « n » (codage Unicode sur 2 octets) Chaîne de caractères de longueur maximale « n » (codage Unicode sur 2 octets)

Page 8

09/10/2020

Riadh Zaafrani

Type de données

DATE TIME[(n)]

Date Heure, n (optionnel) est le nombre de decimals représentant la fraction de secondes

BOOLEAN BLOB

Booléen Binary Large Object : permet de stocker tout type binaire (photo, fichier traitement de texte…)

Page 9

09/10/2020

Riadh Zaafrani

Suppression

 La commande DROP TABLE permet de supprimer une table.

 DROP TABLE voiture

 Si

la table est référencée dans une autre table (par exemple, contrainte d’intégrité référentielle), le SGBD refuse en général de la supprimer : il utilise l’option RESTRICT par défaut. Si l’on désire tout de même la supprimer ainsi que tous les objets qui lui sont liés, il faut alors utiliser l’option CASCADE. Dans l’exemple, si la table ‘vente’ utilise la table ‘voiture’ comme table de référence pour le contenu de la colonne ‘NumVoit’, on ne peut supprimer la table ‘voiture’ avant d’avoir supprimé la table ‘vente’.

 DROP TABLE voiture CASCADE

Page 10

09/10/2020

Riadh Zaafrani

Plan du cours

 Création des tables avec SQL  Modification de la structure

d’une table avec SQL

 Les contraintes d’intégrité sous

SQL

Page 11

09/10/2020

Riadh Zaafrani

Modification

 La commande ALTER TABLE permet de modifier la structure de la table, c’est-à-dire d’ajouter, de supprimer ou modifier des colonnes.

 ALTER TABLE voiture

ADD COLUMN enplus INT ;

 SELECT * FROM voiture ;

NumVoit Marque Peugeot 1 Citroen 2 Opel 3 Peugeot 4 Renault 5 Renault 6

Type 404 SM GT 403 Alpine A310 Rose Bleue Floride

enplus Couleur NULL Rouge Noire NULL Blanche NULL Blanche NULL NULL NULL

Page 12

09/10/2020

Riadh Zaafrani

Modification

 ALTER TABLE voiture

DROP COLUMN Couleur;

 SELECT * FROM voiture ;

enplus Type NumVoit Marque NULL 404 Peugeot NULL SM Citroen NULL GT Opel Peugeot NULL 403 Renault Alpine A310 NULL NULL Floride Renault

1 2 3 4 5 6

Page 13

09/10/2020

Riadh Zaafrani

Modification  La commande ALTER permet de modifier également les contraintes associées aux colonnes. Cette partie est traitée ultérieurement. Le mot clé COLUMN est optionnel.

 Il n’est pas possible de modifier directement le nom d’une colonne ou son type. faut pour cela écrire une série d’opérations, en utilisant par exemple des colonnes temporaires.

 Voici

la suite d’instructions permettant la modification du nom de la colonne de nom ‘Couleur’ de la table ‘voiture’ en ‘Teinte’ en changeant son type.

Page 14

09/10/2020

Riadh Zaafrani

Modification

 ALTER TABLE voiture

ADD COLUMN Teinte CHAR(60);

 SELECT * FROM voiture ;

NumVoit Marque Type 1 2 3 4 5 6

Peugeot 404 SM Citroen Opel GT Peugeot 403 Renault Alpine A310 Rose Bleue Floride Renault

Couleur Teinte NULL Rouge Noire NULL Blanche NULL Blanche NULL NULL NULL

Page 15

09/10/2020

Riadh Zaafrani

Modification

 UPDATE voiture

SET Teinte=Couleur ;

 SELECT * FROM voiture ;

NumVoit Marque Type

Peugeot 404

Citroen SM GT Opel

Peugeot 403

2 3

5 6

Couleur Teinte

Rouge

Noire Blanche Blanche

Noire

Blanche Blanche

Renault Alpine A310 Rose Bleue Renault Floride

Rose Bleue

Page 16

09/10/2020

Riadh Zaafrani

Modification

 ALTER TABLE voiture

DROP COLUMN Couleur;

 SELECT * FROM voiture ;

NumVoit Marque Type 1 2 3 4 5 6

Peugeot 404 Citroen SM Opel GT Peugeot 403 Renault Alpine A310 Rose Bleue Renault Floride

Teinte Rouge Noire Blanche Blanche

Page 17

09/10/2020

Riadh Zaafrani

Plan du cours

 Création des tables avec SQL  Modification de la structure

d’une table avec SQL

 Les contraintes d’intégrité

sous SQL

Page 18

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ

 Lors de l’étape de conceptualisation, on a défini la notion de « domaine », qui décrira l’ensemble des valeurs que peut prendre un attribut.

 Au niveau de SQL, une première approche du domaine est établie par le choix du type de la colonne, mais cela n’est pas assez restrictif en général.

Page 19

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ

 SQL vous permet de définir des conditions de validité plus fines lors de la création de la table, que l’on nomme contraintes d’intégrité.

 C’est le SGBD qui applique ces conditions au moment de l’insertion, de la modification ou même de la suppression liées à de données dans le cas ou ces dernières sont d’autres tables. Cette étape est parfois fastidieuse, mais elle garantit la cohérence des données et évite de se retrouver avec des bases de données, conceptuellement correctes, mais inutilisables faute de données valides.

Page 20

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ

 On peut distinguer différents types de contraintes

sur les colonnes :

 les propriétés générales comme l’unicité et

l’obligation;

 les restrictions d’appartenance à un ensemble ;

 les dépendances entre plusieurs colonnes.

Page 21

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ : Propriétés générales

1) La valeur de la colonne doit être renseignée

absolument (NOT NULL).

2) La valeur doit être unique comparée à toutes les

valeurs de la colonne de la table (UNIQUE).

 Lorsque les deux conditions précédentes sont réunies, la colonne peut servir à identifier un enregistrement et constitue donc une « clé candidate ».

Page 22

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ : Propriétés générales

 On rappelle qu’il ne peut y avoir qu’une seule clé que l’on désignera en SQL par le mot clé PRIMARY KEY. Ici, on indique que la colonne ‘NumAch’ est choisie comme clé de la table (donc implicitement unique et non nulle) et que la colonne ‘Nom’ doit toujours être renseignée.

 CREATE TABLE personne ( NumAch INT PRIMARY KEY, Nom CHAR(20) NOT NULL, Age INT ) ;

Page 23

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ : Propriétés générales

 Si aucune mention n’est précisée comme pour la colonne

‘Age’, elle peut être renseignée ou non.

 Si la clé est constituée de plusieurs colonnes (elle est dite composite) ; on indique la liste des colonnes constitutives de la clé à la suite du mot clé PRIMARY KEY.

 CREATE TABLE vente (

DateVente DATE, PRIX INT, NumAch INT, NumVoit INT, PRIMARY KEY (NumAch, NumVoit)) ;

Page 24

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ : Condition d’appartenance à un ensemble

 Il s’agit de décrire le domaine dans lequel la colonne pourra prendre

ses valeurs. Un ensemble peut être décrit :

 En donnant la liste de tous ses éléments constitutifs (IN). L’ensemble des jours de la semaine ne peut être exprimé que de cette manière : « lundi », « mardi », etc. On vérifie que la colonne ‘couleur’ ne peut prendre que des valeurs « normalisées » : ‘Rouge’ ‘Vert’ ou ‘Bleu’.

 CREATE TABLE voiture(

NumVoit INT PRIMARY KEY, Marque VARCHAR(30) NOT NULL, Type VARCHAR(20), Couleur VARCHAR(40) CHECK Couleur in (‘Rouge’,’Vert’,’Bleu’));

Page 25

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ : Condition d’appartenance à un ensemble

 Par une expression (>, < , BETWEEN…).

 Par exemple, le prix doit être supérieur à 1 000.

 On vérifie que l’âge est compris entre 1 et 99.

 CREATE TABLE personne

(NumAch INT PRIMARY KEY, Nom CHAR(20) NOT NULL, Ville CHAR(40), AGE INT NOT NULL CHECK (Age BETWEEN 1 AND 99) );

Page 26

09/10/2020

Riadh Zaafrani

CONTRAINTES D’INTÉGRITÉ : Condition d’appartenance à un ensemble

 Par une référence aux valeurs d’une colonne d’une autre table (REFERENCES). Les colonnes doivent être de même type et l’on ne peut plus détruire par défaut une table qui apparaît comme référence. On vérifie que les valeurs identifiantes des personnes ‘NumAch’ et des voitures ‘NumVoit’ de la table ‘vente’ existent bien dans les tables de référence ‘personne’ et ‘voiture’.

 CREATE TABLE vente (

DateAch DATE, PRIX INT, NumAch INT NOT NULL REFERENCES personne(NumAch), NumVoit INT NOT NULL REFERENCES voiture(NumVoit), PRIMARY KEY (NumAch, NumVoit)) ;

Page 27

09/10/2020

Riadh Zaafrani

Condition sur plusieurs colonnes (contrainte de table)

 Lorsque l’on désire exprimer des contraintes plus élaborées impliquant plusieurs colonnes, on peut définir une contrainte de table en utilisant le mot clé CONSTRAINT. On vérifie que la colonne ‘Age’ et la colonne ‘Ville’ doivent être renseignées ou vides en meme temps.

 CREATE TABLE personne

(NumAch INT PRIMARY KEY, Nom CHAR(20) NOT NULL, Ville CHAR(40), AGE INT, CONSTRAINT la_contrainte CHECK ( (Age IS NOT NULL AND Ville IS NOT NULL) OR (Age IS NULL AND Ville IS NULL) );

Page 28

09/10/2020

Riadh Zaafrani

Dénomination des contraintes

 Les contraintes peuvent être nommées afin d’être plus

facilement manipulées ultérieurement.

 Dans le cas où aucun nom n’est affecté explicitement à une contrainte, Oracle génère automatiquement un nom de la forme SYS_CXXXXXX(XXXXXX est un nombre entier unique).

 De tels noms ne sont pas parlants. Il est donc préférable de la fournir vous-même. L’emploi d’une stratégie pour affecter des noms permet de mieux les identifier et les gérer.

Page 29

09/10/2020

Riadh Zaafrani

Dénomination des contraintes

 Lors de l’affectation explicite d’un nom à une contrainte, il est la convention de dénomination suivante :

pratique d’utiliser TABLE_COLONNE_TYPEDECONTRAINTE.

 Où TYPEDECONTRAINTE est associé au type de contrainte :

l’abréviation mnémonique

 NN NOT NULL

CK CHECK UQ UNIQUE

 PK PRIMARY KEY

FK FOREIGN KEY

 Cela facilite la compréhension des messages, et permet de modifier ou

de détruire une contrainte :

 ALTER TABLE CINEMA DROP CONSTRAINT CINEMA_SALLE_NN

Page 30

09/10/2020

Riadh Zaafrani

Dénomination des contraintes

 Exemple :

 CREATE TABLE UTILISATEUR (

NO_UTILISATEUR NUMBER(6)

CONSTRAINT UTILISATEUR_PK PRIMARY KEY,

NOM_PRENOM VARCHAR2(20)

CONSTRAINT

UTILISATEUR_NOM_PRENOM_NN NOT NULL,

Page 31

09/10/2020

Riadh Zaafrani

Dénomination des contraintes

DATE_CREATION DATE DEFAULT SYSDATE NOT NULL, DATE_MISEAJOUR DATE DEFAULT SYSDATE NOT NULL, CONNECTIONS NUMBER(6) CONSTRAINT

UTILISATEUR_CONNECTIONS_CK

CHECK (CONNECTIONS BETWEEN 1 AND 10), CONSTRAINT UTILISATEUR_DATE_MISEAJOUR_CK

CHECK (DATE_CREATION <=

DATE_MISEAJOUR));

Page 32

09/10/2020

Riadh Zaafrani

Université Tunis El Manar - Institut Supérieur d’Informatique

Module 2 – Section 2 : Le langage de définition des données (LDD) de SQL

Riadh ZAAFRANI 1ère année MP2L Merci pour votre attention

Page 33

09/10/2020

Riadh Zaafrani