Langage SQL/92 LDD : le langage de définition des données

Résumé du document

Comme nous l'avons déjà souligné, le langage SQL (Structured Query Language) peut être considéré comme le langage d'accès normalisé aux bases de données. Il a fait l'objet de plusieurs normes ANSI/ISO dont la plus répandue aujourd'hui est la norme SQL2 qui a été définie en 1992. Nous décrivons ici les principaux aspects de cette norme, concernant la partie Langage de Définition des Données (LDD).

Le succès du langage SQL est dû essentiellement à sa simplicité et au fait qu'il s'appuie sur le schéma conceptuel pour énoncer des requêtes en laissant le SGBD responsable de la stratégie d'exécution. C'est un langage de requêtes ensembliste et assertionnel. Néanmoins, le langage SQL ne possède pas la puissance d'un langage de programmation : entrées/sorties, instructions conditionnelles, boucles et affectations. Pour certains traitements il est donc nécessaire de coupler le langage SQL avec un langage de programmation complet au sens Turing du terme.

Extraits

[...] Bibliographie indicative SQL, le langage structuré d'interrogation par Carolyn J. Hursch et Jack L. Hursch (2000) SQL Server 2005 : SQL, Transact SQL par Jérôme Gabillaud décembre 2005) Index du texte 1 Introduction La base de données relationnelle Projet Description Modèle Conceptuel des Données (WinDesign) Schéma relationnel Relations issues des entités Relations issues des associations Modèle Logique des données (WinDesign) Le Langage de Définition des Données (LDD) Notion de schéma Création de Tables (CREATE TABLE) Créer une table Création simple Création avec Insertion de données Les types de données Contraintes d'intégrité Modifier une table (ALTER TABLE) Ajouter une colonne Modifier une colonne Ajout d'une contrainte Supprimer une table (DROP TABLE) Renommer une table (RENAME) Insertion de données (INSERT) Les Triggers (gachettes) Syntaxe Trigger sur INSERT (insertion) Trigger sur UPDATE (mise à jour) Trigger sur DELETE (suppression) Remarques sur l'intégrité référentielle 18 Index des illustrations Figure 1 : Modèle Conceptuel des Données "Projet" 4 Figure 2 : Modèle Logique des Données "Projet" 5 Annexe A : Script SQL[14] de création de la base de données Projet[15] Génération d"une base de données pour Borland Interbase (23/12/2002 18:14:12) Nom de la base : PROJET Projet : projet Auteur : Claude Belleil Date de dernière modification : 23/12/2002 18:13:29 CREATION DE LA BASE SET SQL DIALECT CREATE DATABASE 'PROJET.GDB' USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 4096 DEFAULT CHARACTER SET NONE; CREATION DES TABLES ET INDEX TABLE : PAYS create table PAYS ( NO_PAYS CHAR(2) not null, NOM_PAYS CHAR(32) , constraint PK_PAYS primary key (NO_PAYS) TABLE : SERV create table SERV ( NO_SERV CHAR(2) not null, NOM_SERV CHAR(15), CHEF_SERV CHAR(3) not null , constraint PK_SERV primary key (NO_SERV) INDEX DE LA TABLE SERVICE create index I_FK_SERV_EMP on SERV (CHEF_SERV); TABLE : TYP_PART create table TYP_PART ( NO_TP CHAR(2) not null, NOM_TP CHAR(20) , constraint PK_TYP_PART primary key (NO_TP) TABLE : EMP create table EMP ( NO_EMP CHAR(3) not null, NOM_EMP CHAR(20), SEXE CHAR(1), N_C CHAR(3)not null , constraint PK_EMP primary key (NO_EMP) INDEX DE LA TABLE EMP create index I_FK_EMP_CAT_PROF on EMP TABLE : PROJET create table PROJET ( NO_PJ CHAR(2) not null, NOM_PJ CHAR(15) , DIR_PJ CHAR(3) not null, BUDG_PJ DECIMAL(10,2) , constraint PK_PROJET primary key (NO_PJ) INDEX DE LA TABLE PROJET create index I_FK_PROJET_EMPLOYE on PROJET (DIR_PJ); TABLE : CAT_PROF create table CAT_PROF ( NO_CAT CHAR(3) not null, NOM_CAT CHAR(20) , SALAIRE DECIMAL(10,2) , constraint PK_CAT_PROF primary key (NO_CAT) TABLE : PART create table PART ( NO_PART CHAR(2) not null, NOM_PART CHAR(20), N_TY CHAR(2)not null , constraint PK_PART primary key (NO_PART) INDEX DE LA TABLE PARTENAIRE create index I_FK_PART_TYP_PART on PART TABLE : EMP_SERV create table EMP_SERV ( N_E CHAR(3) not null, N_S CHAR(2) not null , constraint PK_TRAVAILLE primary key INDEX DE LA TABLE TRAVAILLE create index I_FK_EMP_SERV_EMP on EMP_SERV(N_E); create index I_FK_EMP_SERV_SERV on EMP_SERV(N_S); TABLE : EMP_PROJ create table EMP_PROJ ( N_E CHAR(3) not null, N_P CHAR(2) not null, PRIME DECIMAL(10,2) not null , constraint PK_EMP_PROJ primary key INDEX DE LA TABLE PARTICIPE create index I_FK_EMP_PROJ_PROJET on EMP_PROJ(N_P); create index I_FK_EMP_PROJ_EMPLOYE on EMP_PROJ(N_E); TABLE : PJ_PART create table PJ_PART ( N_PJ CHAR(2) not null, N_PART CHAR(2) not null , constraint PK_PJ_PART primary key N_PART) INDEX DE LA TABLE EST_ASSOCIE create index I_FK_PJ_PART_PROJET on PJ_PART create index I_FK_PJ_PART_PARTENAIRE on PJ_PART (N_PART); TABLE : PAR_PAYS create table PAR_PAYS ( N_PAR CHAR(2) not null, N_PAY CHAR(2) not null , constraint PK_PAR_PAYS primary key (N_PAR, N_PAY) INDEX DE LA TABLE APPARTIENT create index I_FK_PAR_PAYS_PART on PAR_PAYS(N_PAR); create index I_FK_PAR_PAYS_PAYS on PAR_PAYS(N_PAY); CREATION DES REFERENCES DE TABLE alter table SERV add constraint FK_SERV_EMP foreign key (CHEF_SERV) references EMP (NO_EMP); alter table EMP add constraint FK_EMP_CAT_PROF foreign key references CAT_PROF (NO_CAT); alter table PROJET add constraint FK_PROJET_EMP foreign key (DIR_PJ) references EMP (NO_EMP); alter table PART add constraint FK_PART_TYP_PART foreign key references TYP_PART alter table EMP_SERV add constraint FK_EMP_SERV_EMPLOYE foreign key references EMP (NO_EMP); alter table EMP_SERV add constraint FK_EMP_SERV_SERVICE foreign key references SERV (NO_SERV); alter table EMP_PROJ add constraint FK_EMP_PROJ_PROJET foreign key references PROJET alter table EMP_PROJ add constraint FK_EMP_PROJ_EMPLOYE foreign key references EMP (NO_EMP); alter table PJ_PART add constraint FK_PJ_PART_PROJET foreign key references PROJET alter table PJ_PART add constraint FK_PJ_PART_PARTENAIRE foreign key (N_PART) references PART (NO_PART); alter table PAR_PAYS add constraint FK_PAR_PAYS_PART foreign key (N_PAR) references PART (NO_PART); alter table PAR_PAYS add constraint FK_PAR_PAYS_PAYS foreign key (N_PAY) references PAYS (NO_PAYS); FIN DE GENERATION Annexe B : Script d'alimentation en données de la base Projet INSERT INTO CAT_PROF VALUES('C01','Administrateur Civil' INSERT INTO CAT_PROF VALUES('C02','Ingenieur en Chef' INSERT INTO CAT_PROF VALUES('C03','Ingenieur Recherche' INSERT INTO CAT_PROF VALUES('C04','Cadre Administratif' INSERT INTO CAT_PROF VALUES('C05','Ingenieur develop.' INSERT INTO CAT_PROF VALUES('C06','Ingenieur' INSERT INTO CAT_PROF VALUES('C07','Technicien Sup' INSERT INTO CAT_PROF VALUES('C08','Technicien Spe' INSERT INTO CAT_PROF VALUES('C09','Technicien' INSERT INTO CAT_PROF VALUES('C10','Employe' INSERT INTO CAT_PROF VALUES('C11','Ouvrier Prof.' INSERT INTO CAT_PROF VALUES('C12','Ouvrier Specialise' INSERT INTO EMP VALUES('E01','Dupont','M','C12'); INSERT INTO EMP VALUES('E02','Etienne','M','C12'); INSERT INTO EMP VALUES('E03','Adrien','M','C11'); INSERT INTO EMP VALUES('E04','Scipion','M','C10'); INSERT INTO EMP VALUES('E05','Antoine','M','C10'); INSERT INTO EMP VALUES('E06','Zazou','F','C02'); INSERT INTO EMP VALUES('E07','Roger','F','C01'); INSERT INTO EMP VALUES('E08','Tibure','M','C03'); INSERT INTO EMP VALUES('E09','Yann','M','C04'); INSERT INTO EMP VALUES('E10','Urien','F','C06'); INSERT INTO EMP VALUES('E11','Isis','F','C05'); INSERT INTO EMP VALUES('E12','Olympia','F','C01'); INSERT INTO EMP VALUES('E13','Paris','M','C02'); INSERT INTO EMP VALUES('E14','Quentin','M','C06'); INSERT INTO EMP VALUES('E15','Simon','F','C07'); INSERT INTO EMP VALUES('E16','Damien','M','C05'); INSERT INTO EMP VALUES('E17','Favry','M','C05'); INSERT INTO EMP VALUES('E18','Gus','F','C09'); INSERT INTO EMP VALUES('E19','Electre','F','C12'); INSERT INTO EMP VALUES('E20','Hubert','M','C08'); INSERT INTO EMP VALUES('E21','Janus','M','C09'); INSERT INTO EMP VALUES('E22','Kaola','F','C08'); INSERT INTO EMP VALUES('E23','Lion','F','C10'); INSERT INTO EMP VALUES('E24','Marius','M','C12'); INSERT INTO EMP VALUES('E25','Walter','M','C12'); INSERT INTO EMP VALUES('E26','Xaviera','F','C10'); INSERT INTO EMP VALUES('E27','Calinou','F','C07'); INSERT INTO EMP VALUES('E28','Victor','M','C08'); INSERT INTO EMP VALUES('E29','Biba','F','C09'); INSERT INTO EMP VALUES('E30','Neron','M','C01'); INSERT INTO PROJET VALUES('P1','Petra','E06',200000); INSERT INTO PROJET VALUES('P2','Saturne','E07',350000); INSERT INTO PROJET VALUES('P3','Colibri','E12',450000); INSERT INTO PROJET VALUES('P4','Amazone','E13',230000); INSERT INTO PROJET VALUES('P5','Equinoxe','E30',560000); INSERT INTO PROJET VALUES('P6','Apollo','E08',630000); INSERT INTO PROJET VALUES('P7','System_5','E06',950000); INSERT INTO PROJET VALUES('P8','Sylphide','E30',450000); INSERT INTO PROJET VALUES('P9','Data_6','E13',750000); INSERT INTO EMP_PROJ VALUES('E01','P1',3000); INSERT INTO EMP_PROJ VALUES('E01','P3',500); INSERT INTO EMP_PROJ VALUES('E01','P8',6000); INSERT INTO EMP_PROJ VALUES('E02','P3',500); INSERT INTO EMP_PROJ VALUES('E02','P5',630); INSERT INTO EMP_PROJ VALUES('E03','P9',900); INSERT INTO EMP_PROJ VALUES('E03','P7',800); INSERT INTO EMP_PROJ VALUES('E04','P3',850); INSERT INTO EMP_PROJ VALUES('E05','P7',600); INSERT INTO EMP_PROJ VALUES('E05','P9',2500); INSERT INTO EMP_PROJ VALUES('E06','P1',9000); INSERT INTO EMP_PROJ VALUES('E06','P7',12000); INSERT INTO EMP_PROJ VALUES('E06','P2',5000); INSERT INTO EMP_PROJ VALUES('E07','P2',9000); INSERT INTO EMP_PROJ VALUES('E08','P6',3500); INSERT INTO EMP_PROJ VALUES('E09','P9',7000); INSERT INTO EMP_PROJ VALUES('E12','P3',9000); INSERT INTO EMP_PROJ VALUES('E12','P8',900); INSERT INTO EMP_PROJ VALUES('E13','P4',8000); INSERT INTO EMP_PROJ VALUES('E13','P8',600); INSERT INTO EMP_PROJ VALUES('E14','P1',500); INSERT INTO EMP_PROJ VALUES('E14','P2',800); INSERT INTO EMP_PROJ VALUES('E14','P3',600); INSERT INTO EMP_PROJ VALUES('E14','P4',900); INSERT INTO EMP_PROJ VALUES('E14','P6',300); INSERT INTO EMP_PROJ VALUES('E15','P9',800); INSERT INTO EMP_PROJ VALUES('E15','P8',200); INSERT INTO EMP_PROJ VALUES('E15','P7',200); INSERT INTO EMP_PROJ VALUES('E15','P6',800); INSERT INTO EMP_PROJ VALUES('E16','P4',120); INSERT INTO EMP_PROJ VALUES('E16','P3',600); INSERT INTO EMP_PROJ VALUES('E17','P1',500); INSERT INTO EMP_PROJ VALUES('E17','P2',600); INSERT INTO EMP_PROJ VALUES('E17','P3',700); INSERT INTO EMP_PROJ VALUES('E17','P4',800); INSERT INTO EMP_PROJ VALUES('E17','P5',600); INSERT INTO EMP_PROJ VALUES('E17','P6',400); INSERT INTO EMP_PROJ VALUES('E17','P9',1000); INSERT INTO EMP_PROJ VALUES('E18','P9',800); INSERT INTO EMP_PROJ VALUES('E18','P8',600); INSERT INTO EMP_PROJ VALUES('E18','P6',300); INSERT INTO EMP_PROJ VALUES('E20','P1',600); INSERT INTO EMP_PROJ VALUES('E20','P2',600); INSERT INTO EMP_PROJ VALUES('E21','P3',900); INSERT INTO EMP_PROJ VALUES('E21','P4',600); INSERT INTO EMP_PROJ VALUES('E21','P5',300); INSERT INTO EMP_PROJ VALUES('E22','P1',900); INSERT INTO EMP_PROJ VALUES('E22','P3',800); INSERT INTO EMP_PROJ VALUES('E22','P4',500); INSERT INTO EMP_PROJ VALUES('E22','P5',800); INSERT INTO EMP_PROJ VALUES('E22','P6',800); INSERT INTO EMP_PROJ VALUES('E23','P9',400); INSERT INTO EMP_PROJ VALUES('E23','P2',600); INSERT INTO EMP_PROJ VALUES('E24','P9',600); INSERT INTO EMP_PROJ VALUES('E24','P8',700); INSERT INTO EMP_PROJ VALUES('E24','P6',300); INSERT INTO EMP_PROJ VALUES('E24','P5',800); INSERT INTO EMP_PROJ VALUES('E25','P1',300); INSERT INTO EMP_PROJ VALUES('E25','P5',300); INSERT INTO EMP_PROJ VALUES('E26','P6',300); INSERT INTO EMP_PROJ VALUES('E26','P3',300); INSERT INTO EMP_PROJ VALUES('E27','P1',300); INSERT INTO EMP_PROJ VALUES('E27','P2',800); INSERT INTO EMP_PROJ VALUES('E27','P3',800); INSERT INTO EMP_PROJ VALUES('E28','P2',900); INSERT INTO EMP_PROJ VALUES('E28','P3',300); INSERT INTO EMP_PROJ VALUES('E29','P2',300); INSERT INTO EMP_PROJ VALUES('E30','P5',6000); INSERT INTO EMP_PROJ VALUES('E30','P8',56000); INSERT INTO EMP_PROJ VALUES('E30','P1',4500); INSERT INTO EMP_PROJ VALUES('E24','P7',900); INSERT INTO EMP_PROJ VALUES('E17','P7',960); INSERT INTO EMP_PROJ VALUES('E17','P8',650); INSERT INTO EMP_PROJ VALUES('E22','P2',1100); INSERT INTO EMP_PROJ VALUES('E22','P7',1200); INSERT INTO EMP_PROJ VALUES('E22','P8',560); INSERT INTO EMP_PROJ VALUES('E22','P9',2500); INSERT INTO SERV VALUES('S1','Labo Recherche','E06'); INSERT INTO SERV VALUES('S2','Ingenierie','E08'); INSERT INTO SERV VALUES('S3','Gestion','E30'); INSERT INTO SERV VALUES('S4','Production','E13'); INSERT INTO SERV VALUES('S5','Developpement','E09'); INSERT INTO SERV VALUES('S6','Qualite','E15'); INSERT INTO SERV VALUES('S7','Rel. [...]

[...] CHECK Permet de spécifier les valeurs acceptables pour une colonne. Voici, à titre d'exemple la version définitive de la commande de création de la table Pays avec les contraintes NOT NULL et PRIMARY KEY. create table PAYS ( NO_PAYS CHAR(2) not null, NOM_PAYS CHAR(32), constraint PK_PAYS[9] primary key (NO_PAYS) L'option NOT NULL interdit lors d'un INSERT ou d'un UPDATE qu'une colonne ne contienne la valeur NULL, par défaut elle est autorisée. Cette option est donc obligatoire pour un attribut clé d'une table, qu'il s'agisse d'une clé primaire comme c'est le cas dans l'exemple ci-dessus, ou d'une clé étrangère comme nous le verrons plus loin Modifier une table (ALTER TABLE) On peut modifier dynamiquement la définition d'une table grâce à la commande ALTER TABLE. [...]

[...] L'entreprise organise des projets (PROJET). Ceux-ci sont identifiés par un numéro de projet un nom de projet (NOM_PJ) , un chef de projet qui est repéré par son numéro d'employé (DIR_PJ), un budget (BUDG_PJ). Les employés peuvent ou non participer à un ou plusieurs de ces projets. Leur participation à chaque projet leur permet de percevoir une prime (PRIME). Les projets sont réalisés en association avec des partenaires (PART) qui sont repérés par un numéro (NO_PART) et un nom (NOM_PART). [...]

[...] EUROPEEN'); INSERT INTO PART VALUES('E1','Deschiens & INSERT INTO PART VALUES('E2','Coherent','T2'); INSERT INTO PART VALUES('E3','Microsoft','T3'); INSERT INTO PART VALUES('E4','Borland','T4'); INSERT INTO PART VALUES('E5','Rivoire et Carre','T5'); INSERT INTO PART VALUES('E6','Baynols et Farjon','T6'); INSERT INTO PART VALUES('E7','Caribou','T7'); INSERT INTO PART VALUES('E8','Rero','T8'); INSERT INTO PART VALUES('E9','St-Etienne','T9'); INSERT INTO PJ_PART VALUES('P1','E2'); INSERT INTO PJ_PART VALUES('P1','E3'); INSERT INTO PJ_PART VALUES('P1','E9'); INSERT INTO PJ_PART VALUES('P2','E2'); INSERT INTO PJ_PART VALUES('P2','E5'); INSERT INTO PJ_PART VALUES('P2','E8'); INSERT INTO PJ_PART VALUES('P3','E2'); INSERT INTO PJ_PART VALUES('P3','E5'); INSERT INTO PJ_PART VALUES('P3','E8'); INSERT INTO PJ_PART VALUES('P4','E4'); INSERT INTO PJ_PART VALUES('P4','E6'); INSERT INTO PJ_PART VALUES('P5','E9'); INSERT INTO PJ_PART VALUES('P5','E8'); INSERT INTO PJ_PART VALUES('P5','E6'); INSERT INTO PJ_PART VALUES('P5','E1'); INSERT INTO PJ_PART VALUES('P6','E2'); INSERT INTO PJ_PART VALUES('P7','E4'); INSERT INTO PJ_PART VALUES('P8','E3'); INSERT INTO PJ_PART VALUES('P9','E1'); INSERT INTO PJ_PART VALUES('P9','E2'); INSERT INTO PJ_PART VALUES('P9','E3'); INSERT INTO PJ_PART VALUES('P9','E6'); INSERT INTO PAYS VALUES('01','Usa'); INSERT INTO PAYS VALUES('07','CEI'); INSERT INTO PAYS VALUES('30','Grece'); INSERT INTO PAYS VALUES('31','Pays-Bas'); INSERT INTO PAYS VALUES('32','Belgique'); INSERT INTO PAYS VALUES('33','France'); INSERT INTO PAYS VALUES('39','Italie'); INSERT INTO PAYS VALUES('40','Roumanie'); INSERT INTO PAYS VALUES('44','Royaume INSERT INTO PAYS VALUES('45','Danemark'); INSERT INTO PAYS VALUES('46','Suede'); INSERT INTO PAYS VALUES('47','Norvege'); INSERT INTO PAYS VALUES('48','Pologne'); INSERT INTO PAYS VALUES('49','Allemagne'); INSERT INTO PAYS VALUES('54','Argentine'); INSERT INTO PAYS VALUES('55','Bresil'); INSERT INTO PAYS VALUES('61','Australie'); INSERT INTO PAYS VALUES('81','Japon'); INSERT INTO PAYS VALUES('84','Viet-Nam'); INSERT INTO PAYS VALUES('91','Inde'); INSERT INTO PAR_PAYS VALUES('E1','01'); INSERT INTO PAR_PAYS VALUES('E2','49'); INSERT INTO PAR_PAYS VALUES('E2','61'); INSERT INTO PAR_PAYS VALUES('E2','91'); INSERT INTO PAR_PAYS VALUES('E2','48'); INSERT INTO PAR_PAYS VALUES('E3','33'); INSERT INTO PAR_PAYS VALUES('E3','01'); INSERT INTO PAR_PAYS VALUES('E4','84'); INSERT INTO PAR_PAYS VALUES('E5','33'); INSERT INTO PAR_PAYS VALUES('E5','47'); INSERT INTO PAR_PAYS VALUES('E6','01'); INSERT INTO PAR_PAYS VALUES('E7','55'); INSERT INTO PAR_PAYS VALUES('E7','33'); INSERT INTO PAR_PAYS VALUES('E8','30'); INSERT INTO PAR_PAYS VALUES('E8','01'); INSERT INTO PAR_PAYS VALUES('E9','47'); INSERT INTO PAR_PAYS VALUES('E9','01'); voir le polycopié La Langage SQL/92 Langage de Manipulation des Données idem Cette partie du langage SQL n'est pas traitée dans le cadre de ce cours les clés étrangères issues des cardinalités 1,1 du MCD sont précédées d'un # ce qui signifie que attribut de la table EMP_SERV, est union- compatible avec NO_SERV, attribut de la table SERV Equivalent au schéma relationnel voir plus loin le paragraphe sur le détail des types de données C'est la cas de WinDesign pour le génération des scripts de type Interbase qui sont compatibles avec FireBird Nom donné à la contrainte par le générateur de script de WinDesign Nom donné à l'index par le générateur de script de WinDesign Voir le script en annexe A Comme dans l'exemple précédent les deux tables cibles doivent avoir été créées préalablement. Hors programme pour l'UV Processus Base de données A4 Générateur de script WinDesign au format InterBase (Compatible FireBird) Pour le TP SQL sur la base Projet les deux scripts seront fournis au format texte. [...]

[...] BLOC_PL/SQL est le bloc d'instructions PL/SQL qui s'exécute lorsque le déclencheur est activé Trigger sur INSERT (insertion) Un déclencheur sur INSERT s'exécute à chaque opération d'insertion lancée par l'utilisateur ou par un programme. Lors d'une insertion, l'enregistrement est inséré à la fois dans la table cible est dans une table temporaire dénommée inserted. Une telle table peut permettre de vérifier la cohérence des enregistrements Trigger sur UPDATE (mise à jour) Un déclencheur sur UPDATE s'exécute à chaque opération de mise à jour lancée par l'utilisateur ou par un programme. [...]