Expression des contraintes sur une base de donnée SQL

 

Les contraintes exprimées sur les données assurent la cohérence et la pertinence des données contenues dans une base. Les SGBDR permettent l'expression de ces contraintes.

Ici les contraintes sont exprimées pour une base strictement conforme à la norme SQL ANSI 92. Pour certains moteurs il y aura donc quelques changements à effectuer (se référer à la documentation du moteur.

 

Chapitre 1 Contraintes d'intégrité d'entité:

Il faut que chaque occurrence d'une entité puisse être identifiée donc qu'elle possède un identifiant. Le SGBDR doit contrôler que deux occurrences ne peuvent avoir la même valeur de l'identifiant. Il doit contrôler aussi que toute occurrence a bien une valeur de l'identifiant.

 

Expression avec les bases DBASE

Identifiant simple:

Create Table EMP(no_emp integer not NULL ,nom_emp char(25),pnom_emp char(25)…

Create Unique Index I_no_emp on EMP(no_emp);

Identifiant composé:

Create Table PARTICIPER (no_emp integer not null ,no_projet integer not null,date_deb date)

Create Unique Index I_participer on EMP(no_emp,no_projet);

Observations:

Le caractère obligatoire de l'identifiant est assuré par l'ajout de la clause "NOT NULL"

Le caractère discriminant de l'identifiant est assuré par la création d'un index UNIQUE.

 

Expression avec les Bases conformes à la norme SQL ANSI 92:

Create Table EMP(no_emp integer not NULL ,nom_emp char(25),pnom_emp char(25

PRIMARY KEY (no_emp));

Create Table PARTICIPER (no_emp integer not null ,no_projet integer not null,date_deb date , PRIMARY KEY (no_emp,no_projet));

Observations:

Le caractère obligatoire de l'identifiant est assuré par l'ajout de la clause "NOT NULL"

Le caractère discriminant de l'identifiant est assuré par la création d'un index clause PRIMARY KEY.

 

Exercice: Ecrire selon les deux normes l'instruction SQL permettant de créer les tables

TACHE & PARTICIPE

Chapitre 2 Contraintes d'intégrité référentielle

Les clefs étrangères doivent se référer à des occurrences existantes dans les tables référencées.

Le numero du client d'une commande doit référencer un client existant dans la table client.

Ce client ne pourra être supprimé tant qu'il existe des commandes de ce client.

 

Ce type de contrainte n'est pris en charge que dans la norme SQL ANSI 92

Expression:

Create Table PARTICIPER (no_emp integer not null ,no_projet integer not null,date_deb date , PRIMARY KEY (no_emp,no_projet)

, FOREIGN KEY (no_emp) REFERENCES emp(no_emp)

, FOREIGN KEY (no_projet) REFERENCES projet(no_projet));

remarques:

La clé étrangère peut être une clé composée.

La clé étrangère peut ne pas avoir le même nom que la clé primaire.

 

Altérez les TABLES TACHE & PARTICIPE pour qu'elles prennent en compte l'intégrité référentielle.

Chapitre 3 Contraintes d'intégrité de domaine(de définition)

Les valeurs attribuées à un champ d'une table  doivent appartenir à l'ensemble de définition du champ.

Création d'un domaine:

 

CREATE DOMAIN d_salaire  as Integer

Check (Not(value is NULL) and (value>4500) and (value <50000));

 

CREATE DOMAIN d_nom_de_famille as char(25)

CHECK (value=upper(value));

 

CREATE DOMAIN d_pays as char(15)

CHECK (UPPER value in ("ESPAGNE","FRANCE","ITALIE"));

 

CREATE DOMAIN d_emulsifiant as char(5)

CHECK ((VALUE STARTING WITH "E" )or (value  is NULL) );

 

CHECK(value CONTAINING "glue")

CHECK (value BETWEEN val1 and val2)

CHECK (value LIKE (_j_klm%))

Rappel: le  " _"  remplace  n'importe quelle lettre.

tandis que "%" remplace n'importe quelle chaîne.

 

Syntaxe complète

CREATE DOMAIN domain [AS] <datatype>

[DEFAULT { literal | NULL | USER}]

[NOT NULL] [CHECK ( <dom_search_condition>)]

[COLLATE collation];

< datatype> = {

{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION} [ <array_dim>]

| {DECIMAL | NUMERIC} [( precision [, scale])] [ <array_dim>]

| DATE [ <array_dim>]

| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}

[(1…32767)] [ <array_dim>] [CHARACTER SET charname]

| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}

[VARYING] [(1…32767)] [ <array_dim>]

| BLOB [SUB_TYPE { int | subtype_name}] [SEGMENT SIZE int]

[CHARACTER SET charname]

| BLOB [( seglen [, subtype])]

}

<array_dim> = [[x:]y [, [x:]y …]]

<dom_search_condition> = {

VALUE <operator> <val>

| VALUE [NOT] BETWEEN <val> AND <val>

| VALUE [NOT] LIKE <val> [ESCAPE <val>]

| VALUE [NOT] IN ( <val> [ , <val> …])

| VALUE IS [NOT] NULL

| VALUE [NOT] CONTAINING <val>

| VALUE [NOT] STARTING [WITH] <val>

| ( <dom_search_condition>)

| NOT <dom_search_condition>

| <dom_search_condition> OR <dom_search_condition>

| <dom_search_condition> AND <dom_search_condition>

}

< operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

 

Comment lire ceci ?

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

{}= ensemble

| signifie ou

un operator est donc l'un des opérateurs sites dans l'ensemble.

Ce qui est entre crochet n'est pas obligatoire.

 

 

Utilisation du domaine

Create Table EMP(no_emp integer not NULL ,nom_emp char(25),pnom_emp char(25), pays d_pays,

PRIMARY KEY (no_emp));

 

Chapitre 4 Contrainte d'intégrité liées:

Les valeurs de certaines données ne peuvent évoluer de façon totalement indépendante.

 

Exemple la quantité commandée d'un produit ne peut être supérieure à la quantité en stock du produit.

Le salaire d'un employé ne peut être inférieur au salaire minimum prévu pour sa qualification.

Expression des contraites d'intégrité liées:

 

Create Table EMP (no_emp integer not NULL ,nom_emp char(25),pnom_emp char(25),salaire integer,no_qualif integer, pays d_pays,

PRIMARY KEY (no_emp)

FOREIGN KEY (no_qualif) REFERENCES qualification(no_qualif),

Check (salaire>=(select sal_min from qualification where qualification.no_qualify=no_qualif))

And (salaire<=(select sal_max from qualification where qualification.no_qualify=no_qualif))  );

Exercices:

Après avoir réalisé le petit extrait de MLDR correspondant , Exprimer le check relatif au premier exemple: la quantité commandée d'un produit ne peut être supérieure à la quantité en stock du produit.

 

Chapitre 5 Contrainte d'intégrité de totalité

Cette contrainte correspond au 1 en cardinalité minimale.

On distingue 2 cas:

 

La Cardinalité 1,1

Elle se traduit par le caractère obligatoire de la clé étrangère

Create Table EMP (no_emp integer not NULL ,nom_emp char(25),pnom_emp char(25),salaire integer,no_qualif INTEGER NOT NULL, pays d_pays,

PRIMARY KEY (no_emp)

FOREIGN KEY (no_qualif) REFERENCES qualification(no_qualif),

Check (salaire>=(select sal_min from qualification where qualification.no_qualify=no_qualif))

And (salaire<=(select sal_max from qualification where qualification.no_qualify=no_qualif))  );

 

cardinalités1,n

Une division contient au moins 1 salarié.

Check (NOT(0=(select count * from Emp where EMP.no_division=no_divis)));

 

Check (EXISTS(select * from Emp where EMP.no_division=no_divis));

Attention: A ne pas appliquer si l'employé doit toujours appartenir à une division sinon il y a interbloquage on ne pourra pas rentrer d'employé de cette division.

 

Chapitre 6 Contraintes de totalité

Exemple: Personne(particulier-entrepreneur) toutes nos personnes sont soit des particuliers soit des entrepreneurs mais les entrepreneurs peuvent être aussi des particuliers)

Elle peut être exprimée par la non création de la table ancêtre.

 

Chapitre 7 Contraintes de partition

Auteur(auteur invité, auteur accepté, auteur refusé)

Elle peut être exprimée par un état de l'auteur. Ou par des vérifications d'existence dans les tables descendantes et des vérifications de non existence dans plusieurs tables descendantes.

Chapitre 8 Contraintes d'exclusion

Contrat(contrat credit, contrat epargne)

Dans ce cas il y a 3 tables:

On utilisera soit un check avec not exist (select * from autre table where le numero = numero)

 

Chapitre 9 Syntaxe complète de l'instruction create Table

 

CREATE TABLE table [EXTERNAL [FILE] ’ <filespec>’]

( <col_def> [, <col_def> | <tconstraint> …]);

<col_def> = col {< datatype> | COMPUTED [BY] (< expr>) | domain}

[DEFAULT { literal | NULL | USER}]

[NOT NULL]

[ <col_constraint>]

[COLLATE collation]

< datatype> = {

{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION} [ <array_dim>]

| {DECIMAL | NUMERIC} [( precision [, scale])] [ <array_dim>]

| DATE [ <array_dim>]

| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}

[( int)] [ <array_dim>] [CHARACTER SET charname]

| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}

[VARYING] [( int)] [ <array_dim>]

| BLOB [SUB_TYPE { int | subtype_name}] [SEGMENT SIZE int]

[CHARACTER SET charname]

| BLOB [( seglen [, subtype])]

}

<array_dim> = [[x:]y [, [x:]y …]]

< expr> = A valid SQL expression that results in a single value.

<col_constraint> = [CONSTRAINT constraint] <constraint_def>

<constraint_def> = {UNIQUE | PRIMARY KEY

| CHECK ( <search_condition>)

| REFERENCES other_table [( other_col [, other_col …])]}

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

<tconstraint> = CONSTRAINT constraint <tconstraint_def>

= {{PRIMARY KEY | UNIQUE} ( col [, col …])

| FOREIGN KEY ( col [, col …]) REFERENCES other_table

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

| CHECK ( <search_condition>)}

<search_condition> =

{ <val> <operator> { <val> | ( <select_one>)}

| <val> [NOT] BETWEEN <val> AND <val>

| <val> [NOT] LIKE <val> [ESCAPE <val>]

| <val> [NOT] IN ( <val> [ , <val> …] | <select_list>)

| <val> IS [NOT] NULL

| <val> {[NOT] {= | < | >} | >= | <=}

{ALL | SOME | ANY} ( <select_list>)

| EXISTS ( <select_expr>)

| SINGULAR ( <select_expr>)

| <val> [NOT] CONTAINING <val>

| <val> [NOT] STARTING [WITH] <val>

| ( <search_condition>)

| NOT <search_condition>

| <search_condition> OR <search_condition>

| <search_condition> AND <search_condition>}

<val> = {

col [ <array_dim>] | : variable

| <constant> | <expr> | <function>

| udf ([ <val> [, <val> …]])

| NULL | USER | RDB$DB_KEY | ?

} [COLLATE collation]

<constant> = num | ' string' | charsetname ' string'

<function> = {

COUNT (* | [ALL] <val> | DISTINCT <val>)

| SUM ([ALL] <val> | DISTINCT <val>)

| AVG ([ALL] <val> | DISTINCT <val>)

| MAX ([ALL] <val> | DISTINCT <val>)

| MIN ([ALL] <val> | DISTINCT <val>)

| CAST ( <val> AS <datatype>)

| UPPER ( <val>)

| GEN_ID ( generator, <val>)

}

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

<select_one> = SELECT on a single column; returns exactly one value.

<select_list> = SELECT on a single column; returns zero or more values.

<select_expr> = SELECT on a list of values; returns zero or more values.


Chapitre 10 Les Champs calculés

Les valeurs des champs peuvent être calculés .

La valeur est alors créée à l'insertion mais aussi maj lors de l'affichage.

En réalité, le champ calculé n'est pas stocké dans la table.

On ne peut faire un select pour calculer la valeur d'un champ.

Exemple:

montant COMPUTED BY (qte* pu)

 

Chapitre 11 Les déclencheurs

Un déclencheur est une routine autonome associée à une table. Il exécute automatiquement une action à l'insertion , la mise à jour ou la suppression d'une ligne dans une table.

 

Exemple de déclencheur:

 génération d'une valeur d'identifiant :

Create trigger generation for client

Before insert

Declare variable plusGros integer;

As

Begin

     Select max(no_cli) from client into :plusGros;

     New.no_cli=plusGros+1;

End;

 

Analyse du déclencheur:

Ligne 1

Create trigger generation for client 

generation est le nom donné au trigger .

client est le nom de la table sur laquelle porte le trigger

 

ligne 2

Before insert

Lorsqu'une insertion est demandée et avant que la ligne ne soit écrite le trigger s'exécutera

On a le choix entre: before ou after

On a le choix entre insert, update, ou delete.

 

Ligne 3

Declare variable plusGros integer;

Declaration d'une variable plusGros de type integer;

 

Ligne 4

As

Begin

    /*le corps est ici et les trois lignes à gauche sont obligatoires.*/

End;

 

Ligne 6

     Select max(no_cli) from client into :plusGros;

Il s'agit d'une requête sql classique .

Le résultat de la requête est toutefois stocké dans la variable précédée de ":"

On aurait pu écrire aussi where no_client= :vnocli ou vnocli aurait été déclarée comme variable.

Ces variables sont appelées: VARIABLES HÔTES

Et pour les utiliser dans une requête SQL on les fait précéder de ":"

 

Ligne 7

     New.no_cli=plusGros+1;

New est la ligne qui va être insérer dans la table.

New.no_cli est la valeur de no_cli pour cette ligne.

 

Remarque: lorsque l'on fait un update:

NEW correspond à la ligne après changement

Tandis que OLD correspond à la ligne avant changement

 

Exemple 2

Il s'agit de tenir dans une table histo_salaires l'historique des salaires des salariés

CREATE TRIGGER gerehisto for SALARIE

AFTER UPDATE

AS

BEGIN

    If (NEW.salaire<>OLD.salaire) then

    INSERT into hist_sal values(NEW.nom,NEW.prenom,"NOW",OLD.salaire,NEW.salaire);

    /* else ce n'est pas le salaire qui a change mais autre chose*/

    /* remarque: ceci est un commentaire*/

END;

Chapitre 12  

Chapitre 13 Les procédures stockées

Exemple:

Create procedure get_pu_pdt (numero integer)

returns (lepu float)

As

Begin

  SELECT pu from pdt where no_pdt= :numero into :lepu;

end ;

Create trigger set_pu  for ldc

before insert

As

Begin

  msg=new.NO_PDT;

  select pu from get_pu_pdt(:msg)

  into :pu;

end ;

utilisation des procédures stockées

select * from get_pu_dt(1);

va renvoyer le prix unitaire du produit n°1;

 

Chapitre 14 Etude de cas:

Il s'agit de réaliser une base de donnée commerciale contenant les tables suivantes:

Produit, commande, et ligne de commande.

Tous les montants doivent se mettre à jour automatiquement.

Ainsi que les quantités en stock. On ne pourra de plus enregistrer une ligne de commande que l'on ne pourrait satisfaire pour cause de stock insuffisant.

 

 

Table produit

Create Table pdt(no_pdt integer not null primary key, design_pdt ,pu float not null);

 

Table commande

Create Table commande(no_com integer not null primary key, date_com date,montant float default 0);

 

Table Ligne de commande

Create Table ldc (no_ldc integer not null, no_cde integer not null, qte integer not null,
no_pdt integer not null, pu float, montant COMPUTED BY (qte* pu),

Primary key(no_ldc,nocde),

foreign key (no_cde) references commande(no_com),

foreign key (no_pdt) references pdt(no_pdt));

 

 

 

 

 

Chapitre 15 Les Déclencheurs

 

Lorsqu'il y a nouvelle ligne de commande , le pu actuel du produit est enregistré dans la ligne.

 

Création d'un déclencheur sur la table ldc qui rempli le pu de la ldc.

Chapitre 16 Create trigger set_pu  for ldc
before insert
As
declare variable msg integer;
declare variable pul float;
Begin
   msg=NEW.NO_PDT;
   select pu from pdt where no_pdt= :msg into :pul;
   NEW.PU=pul;

end ;

 

A chaque nouvelle ligne de commande il faut mettre à jour le montant de la commande.

 

Création d'un déclencheur sur la table ldc qui modifie le montant  de la commande.

Create trigger maj_commande  for ldc

after insert

As

declare variable vnocde integer;

declare variable vmontant float;

Begin

   vmontant=NEW.MONTANT;

   vnocde=NEW.no_cde;

   update commande set montant=montant+:vmontant where no_com = :vnocde;

end ;

 

A chaque maj d'une ldc il faut mettre à jour le montant de la commande.

 

Création d'un déclencheur sur la table ldc qui modifie le montant  de la commande.

Create trigger maj_upd  for ldc

after update

As

declare variable vnocde integer;

declare variable vnmontant float;

declare variable vamontant float;

Begin

   vnmontant=NEW.MONTANT;

   vamontant= OLD.MONTANT;

   vnocde=NEW.no_cde;

   update commande set montant=montant+:vnmontant-:vamontant where no_com = :vnocde;

end ;

A chaque suppression d'une ldc il faut mettre à jour le montant de la commande

Create trigger maj_delete  for ldc

before delete

As

declare variable vnocde integer;

 

declare variable vamontant float;

Begin

  

   vamontant= OLD.MONTANT;

   vnocde=OLD.no_cde;

   update commande set montant=montant-:vamontant where no_com = :vnocde;

end ;