Discussion :

[dalinbm]

re,
merci pour votre reponse rapide ...Alors que pensez-vous ?

[Paprick]

La contrainte ente Employé et Chauffeur n'apparait pas, donc tout va bien côté Looping

[fsmrel]

Bonjour,

Je ne comprends pas : je n'ai pas le problème chez moi...
François : peux-tu vérifier à partir de quoi tu as généré le modèle UML qui a rajouté cette contrainte, et m'envoyer le fichier si le problème est avéré.
Merci !

Paprick, désolé de t'avoir dérangé, la contrainte dans le MCD était restée à "XT" (Partition). En passant à "Pas de contrainte", plus de problème.

[fsmrel]

@dalinbm,


Merci pour votre MCD (post #79).

L’association Affecter (entre Chauffeur_Ste et Voiture_Service) ne répond pas au besoin. En effet, elle permet seulement de produire pour chaque voiture de service l’ensemble des missions pour lesquelles elle a été utilisée. Pour la mission m1, on sait qu’a été utilisée la voiture de service v1, mais cela ne permet pas de savoir quel chauffeur a été impliqué dans m1.

Ne vous focalisez pas trop sur la base de données SQL, en effet les règles de gestion ne sont pas stabilisées, pas plus que le MCD. Chaque chose en son temps.

En passant, dans le post #57, vous précisez que votre société possède 60 sites distants. J’en déduis, peut-être à tort, que pour bon nombre de missions, le déplacement se fera en voiture.

Dans ces conditions, je verrais par exemple d’un bon oeil la modélisation suivante (une vue sur un MCD plus complet), selon laquelle les missions sont « lambda », c’est-à-dire sans chauffeur, ou bien en voiture, avec chauffeur de la société, ou avec chauffeur occasionnel. Sont mis en évidence les sous-types MissionAchauffeurSte et MissionAchaufeurOccas. Un sous-type pour les missions « lambda » ne paraît pas nécessaire.
 

 
En tout cas, quel que soit le scénario final, il y aura des triggers pour bétonner à la Naf-Naf (par exemple interdire qu’un employé soit affecté en même temps à deux missions, etc.)...
 

 
Encore courage, et à bientôt.

[fsmrel]

@dalinbm

J’ai soumis à SQL Server le code SQL que vous avez généré avec Looping, et que vous m’avez transmis (cf. post #79).

Tout s’est bien passé. Cela dit, "Site" et "Service" étant des mots réservés chez SQL Server, ils ne peuvent être utilisés pour des noms de tables. Même pas grave, Looping permet de donner des "noms logiques" aux tables sans avoir à renommer les entités-types, intégrité de MCD oblige .

Par exemple :
 

 
Bonne nuit !

[fsmrel]

Bonjour,

@dalinbm

A propos du code SQL que vous avez généré avec Looping, et que vous m’avez transmis (cf. post #79).

J’ai oublié de préciser que SQL Server n’accepte pas le type DOUBLE (cf. table Defrayer, colonne FraisMontant).
Quoiqu’il en soit on pourrait en l’occurrence gérer des montants plus qu'astronomiques !
Le type DECIMAL ou le type INT seraient raisonnablement plus pertinents...

[fsmrel]

@dalinbm

Comme Colombo, je pose beaucoup de questions (comme on me le reprochait parfois du temps lointain où j’étais opérationnel...)

Q1. Les employés e1, e2, e3, etc. affectés à la mission m1 font-ils tous partie du même site ? (voire du même service ?)

Q2. Il semblerait qu’il y ait deux types de sites : les sites distants qui, à leur demande, font l’objet des missions (sites que je symbolise ici par la lettre D), et les sites qui ont les employés qualifiés pour mener à bien les missions (sites que je symbolise ici par la lettre Q). Un site peut-il être à la fois du type D et du type Q ?

A suivre.

[Paprick]

Bonjour,

A propos du code SQL que vous avez généré avec Looping, et que vous m’avez transmis (cf. post #79).
J’ai oublié de préciser que SQL Server n’accepte pas le type DOUBLE (cf. table Defrayer, colonne FraisMontant).
Quoiqu’il en soit on pourrait en l’occurrence gérer des montants plus qu'astronomiques !
Le type DECIMAL ou le type INT seraient raisonnablement plus pertinents...

Pour le code SQL en question, Looping n'était pas configuré avec "SQL Server" : sinon, il aurait généré un type FLOAT.

[fsmrel]

Pour le code SQL en question, Looping n'était pas configuré avec "SQL Server" : sinon, il aurait généré un type FLOAT.

OK d’acc !

=>

[dalinbm]

merci pour vos tous ,c'est trop tard demain , j'examine mes diagrames...bonne nuit

[dalinbm]

Q1. Les employés e1, e2, e3, etc. affectés à la mission m1 font-ils tous partie du même site ? (voire du même service ?)

non c'est pas forcement, un equipe peut contenir plusieur service ...

Q2. Il semblerait qu’il y ait deux types de sites : les sites distants qui, à leur demande, font l’objet des missions (sites que je symbolise ici par la lettre D) , et les sites qui ont les employés qualifiés pour mener à bien les missions (sites que je symbolise ici par la lettre Q). Un site peut-il être à la fois du type D et du type Q ?

bien dit ,Inspecteur colombo, un site peut a la fois du type D et de type Q (exemple : informatique centrale peur recevoire une mission d'audit de service juridique ... )

merci ..

[fsmrel]

Bonjour,
 
 

non c'est pas forcement, un equipe peut contenir plusieur service

 
Une mission peut donc être constituée d’employés appartenant à des services différents, d’accord.
Mais ces employés peuvent-ils appartenir à des services de sites différents ? Autrement dit, si l’équipier (employé) e1 appartient à un service du site z1 et si l’équipier (employé) e2 appartient à un service du site z2, peuvent-ils faire partie de la même mission ?

[fsmrel]

 

Quel que soit le scénario final, il y aura des triggers pour bétonner à la Naf-Naf (par exemple interdire qu’un employé soit affecté en même temps à deux missions, etc.)

 
Allez ! un trigger pour faire respecter la règle de gestion RG01 :
 

RG01 : Pour une période donnée, un employé ne peut effectuer qu'une et une seule mission.

 
Un jeu d’essai (création des tables) :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
USE dalinbm ;
 
DROP TABLE IF EXISTS EmployeMission ;
DROP TABLE IF EXISTS Mission ;
DROP TABLE IF EXISTS Employe ;
 
CREATE TABLE Employe
(
   EmployeId INT 
 , EmployeMatricule VARCHAR(8) NOT NULL
 , EmployeNom VARCHAR(50) NOT NULL
 , EmployePrenom VARCHAR(50) NOT NULL
 , CONSTRAINT Employe_PK PRIMARY KEY(EmployeId)
 , CONSTRAINT Employe_AK UNIQUE(EmployeMatricule)
);
 
CREATE TABLE Mission
(
   MissionId INT 
 , MissionCode VARCHAR(8) NOT NULL
 , MissionDesignation VARCHAR(50) NOT NULL
 , MissionDateDebut DATE NOT NULL
 , MissionDateFin DATE NOT NULL
 , CONSTRAINT Mission_PK PRIMARY KEY(MissionId)
);
 
CREATE TABLE EmployeMission
(
   MissionId INT
 , EmployeId INT
 , CONSTRAINT EmployeMission_PK PRIMARY KEY(MissionId, EmployeId)
 , CONSTRAINT EmployeMission_Mission_FK FOREIGN KEY(MissionId) REFERENCES Mission(MissionId)
 , CONSTRAINT EmployeMission_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
);

 
Le jeu d’essai (avec trigger)
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
drop trigger if exists EmployeMissionAfterInsertTrigger ;
go
CREATE TRIGGER EmployeMissionAfterInsertTrigger on EmployeMission after insert, update as
begin
  declare @n varchar(24)
  declare @emp varchar(8) ;
  declare @errId as int = 314115  -- numéro de l'erreur à afficher
  declare @errTexte as varchar (255) -- message d'erreur
 
  select @n = count(*)
  from   inserted as a -- new EmployeMission  
  join EmployeMission as b on a.EmployeId = b.EmployeId and a.MissionId < b.MissionId
  join Mission as c on a.MissionId = c.MissionId
  join Mission as d on b.MissionId = d.MissionId
  where 
    c.MissionDateDebut <= d.MissionDateFin 
    and c.MissionDateFin >= d.MissionDateDebut
  ;   
 
  if @n > 0
    begin 
      set @emp = (select top (1) e.EmployeMatricule 
                  from Inserted as a 
                    join EmployeMission as b on a.EmployeId = b.EmployeId and a.MissionId < b.MissionId
                    join Mission as c on a.MissionId = c.MissionId
                    join Mission as d on b.MissionId = d.MissionId
                    join Employe as e on a.EmployeId = e.EmployeId
                  where c.MissionDateDebut <= d.MissionDateFin and c.MissionDateFin >= d.MissionDateDebut
                 ) ;
 
      set @errTexte = 
          char(13)
        + 'A un instant t, un employe ne peut participer qu''à une seule mission.' 
        + char(13)
        + 'L''employé '''
        + @emp
        + ''' serait en infraction par rapport à cette obligation.' 
        + char(13)
      ; throw @errId, @errTexte, 16
    end ;
end
go
 
delete from EmployeMission ;
delete from Mission ;
delete from Employe ;
go
insert into Employe
values 
   (1, 'e1', 'Naudin', 'Fernand') 
 , (2, 'e2', 'Volfoni', 'Raoul')
 , (3, 'e3', 'Volfoni', 'Paul') 
 , (4, 'e4', 'Monpetit', 'Patricia') 
 , (5, 'e5', 'Venanti', 'Pascal') 
 , (6, 'e6', 'Folace', 'Francis') 
 , (7, 'e7', 'Madame', 'Mado') 
;
select '' as Employe, * from Employe order by EmployeMatricule ;
 
insert into Mission
values 
   (1, 'm1', 'debugging programme PG09', '2023-10-01', '2023-10-05')
 , (2, 'm2', 'mise en oeuvre de l''AGL Looping', '2023-09-28', '2023-10-03')
 , (3, 'm3', 'installation SQL Server', '2023-10-04', '2023-10-10')
 , (4, 'm4', 'test perfs SQL Server', '2023-10-10', '2023-10-15')
 , (5, 'm5', 'test perfs SQL Server', '2023-10-16', '2023-10-18')
;
select '' as Mission, * from Mission order by MissionCode ;
 
insert into EmployeMission
values
   ((select MissionId from Mission where MissionCode = 'm2')
    , (select EmployeId from Employe where EmployeMatricule = 'e1'))
 , ((select MissionId from Mission where MissionCode = 'm2')
    , (select EmployeId from Employe where EmployeMatricule = 'e2'))
 , ((select MissionId from Mission where MissionCode = 'm2')
    , (select EmployeId from Employe where EmployeMatricule = 'e3'))
 , ((select MissionId from Mission where MissionCode = 'm2')
    , (select EmployeId from Employe where EmployeMatricule = 'e6'))
; 
 
insert into EmployeMission
values
   ((select MissionId from Mission where MissionCode = 'm1')
    , (select EmployeId from Employe where EmployeMatricule = 'e1'))
 , ((select MissionId from Mission where MissionCode = 'm1')
    , (select EmployeId from Employe where EmployeMatricule = 'e3'))
; 
 
insert into EmployeMission
values
   ((select MissionId from Mission where MissionCode = 'm5')
    , (select EmployeId from Employe where EmployeMatricule = 'e6'))
; 
go
 
select '' as EmployeMission, a.MissionId, MissionCode, a.EmployeId, EmployeMatricule 
  from EmployeMission a 
    join Mission b on a.MissionId = b.MissionId 
    join Employe c on a.EmployeId = c.EmployeId
  order by EmployeMatricule, MissionCode

 
Le trigger est à secouer avec énergie !

[fsmrel]

Bonsoir,

Un oubli de ma part...
Table Mission : contrôler que la date de début d'une mission ne soit pas postérieure à sa date fin.

=>
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE Mission
(
   MissionId INT 
 , MissionCode VARCHAR(8) NOT NULL
 , MissionDesignation VARCHAR(50) NOT NULL
 , MissionDateDebut DATE NOT NULL
 , MissionDateFin DATE NOT NULL
 , CONSTRAINT Mission_PK PRIMARY KEY(MissionId)
 , CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck CHECK (MissionDateDebut <= MissionDateFin)
);

 
Dans le contexte Looping, la contrainte peut être ajoutée automatiquement dans le code SQL, au moyen d’une règle demandant la mise en oeuvre d’un ALTER TABLE.
 

 
Contenu SQL de la règle :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE MISSION  
  ADD CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck CHECK (MissionDateDebut <= MissionDateFin) ;

[dalinbm]

 

Le trigger est à secouer avec énergie !

Mille merci à vos efforts, mais (le trigger) c'est plus fort que moi (je suis débutant) ,j'essaye de le comprendre ,juste un peu de temp et je vous donne mes résultat.

[fsmrel]

Bonsoir,
 

Mille merci à vos efforts, mais (le trigger) c'est plus fort que moi (je suis débutant) ,j'essaye de le comprendre ,juste un peu de temp et je vous donne mes résultats.

Je comprends vos efforts...

Je vous recommande la lecture de l’ouvrage "SQL Server 2104" de Frédéric Brouard (SQLpro chez developpez.com), Christian Soutou (Soutou), Nicolas Souquet (elsuket), David Barbarin (mikedavem).
Les triggers sont traités à partir de la page 339.

On y traite aussi des fonctions, par exemple que l’on peut attacher aux tables sous forme de contraintes.
Prenons par exemple le cas de nos chauffeurs. Un employé ne peut pas être à la fois chauffeur de société et chauffeur occasionnel :
 

 
C’est parti !
 
(A) Créons les tables manquantes (la table Employe est censée déjà exister, cf. post #93) :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
use dalinbm ;
 
DROP TABLE IF EXISTS ChauffeurSociete ;
DROP TABLE IF EXISTS ChauffeurOccasionnel ;
DROP TABLE IF EXISTS Chauffeur ;
 
CREATE TABLE Chauffeur(
   EmployeId INT,
   CONSTRAINT Chauffeur_PK PRIMARY KEY(EmployeId),
   CONSTRAINT Chauffeur_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
       ON DELETE CASCADE
);
 
CREATE TABLE ChauffeurOccasionnel(
   EmployeId INT,
   CONSTRAINT ChauffeurOccasionnel_PK PRIMARY KEY(EmployeId),
   CONSTRAINT ChauffeurOccasionnel_Chauffeur_FK FOREIGN KEY(EmployeId) REFERENCES Chauffeur(EmployeId)
       ON DELETE CASCADE
);
 
CREATE TABLE ChauffeurSociete(
   EmployeId INT,
   CONSTRAINT ChauffeurSociete_PK PRIMARY KEY(EmployeId),
   CONSTRAINT ChauffeurSociete_Chauffeur_FK FOREIGN KEY(EmployeId) REFERENCES Chauffeur(EmployeId)
   ON DELETE CASCADE)
;

 
(B) Créons la fonction dont l’objet est d’interdire qu’un chauffeur de la société soit en même temps chauffeur occasionnel :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP FUNCTION IF EXISTS Exclusion_ChauffeurSociete_fonction ;
go
CREATE FUNCTION Exclusion_ChauffeurSociete_fonction(@EmployeId INT)
    RETURNS CHAR(2)
  AS BEGIN
      RETURN CASE WHEN NOT EXISTS
        (SELECT *
         From ChauffeurSociete
         WHERE EmployeId = @EmployeId) 
      THEN 'ok'
      ELSE 'ko'
     END ;
END ;
GO

 
(C) Ajoutons à la table des chauffeurs occasionnels la contrainte interdisant aux chauffeurs de la société d’être aussi des chauffeurs occasionnels :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER TABLE ChauffeurOccasionnel
  ADD CONSTRAINT Exclusion_ChauffeurSociete 
     CHECK (dbo.Exclusion_ChauffeurSociete_fonction(EmployeId) = 'ok') ;

 
(D) Quelques employés :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
delete from Employe ;
insert into Employe
values 
   (1, 'e1', 'Naudin', 'Fernand') 
 , (2, 'e2', 'Volfoni', 'Raoul')
 , (3, 'e3', 'Volfoni', 'Paul') 
 , (4, 'e4', 'Monpetit', 'Patricia') 
 , (5, 'e5', 'Venanti', 'Pascal') 
 , (6, 'e6', 'Folace', 'Francis') 
 , (7, 'e7', 'Madame', 'Mado') 
 , (8, 'e8', 'Mac', 'Sébastien') 
) ;

 
(E) Quelques chauffeurs :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
insert into Chauffeur
  values 
   (1), (2), (5), (8) ; 
select '' as Chauffeur, * from Chauffeur ;

 
(F) Quelques chauffeurs occasionnels :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
insert into ChauffeurOccasionnel
  values    (1), (2) ;
 
select '' as ChauffeurOccasionnel, * from ChauffeurOccasionnel

 
(G) Quelques chauffeurs de la société :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
insert into ChauffeurSociete
  values (5), (8) ; 
 
select '' as ChauffeurSociete, * from ChauffeurSociete ;

(H) Pascal est chauffeur de la société et ne peut donc pas être en même temps chauffeur occasionnel :
 

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
insert into ChauffeurOccasionnel
  values (5) ;

 
=>
 
Msg 547, Niveau 16, État 0, Ligne 89
L'instruction INSERT est en conflit avec la contrainte CHECK "Exclusion_ChauffeurSociete". Le conflit s'est produit dans la base de données "dalinbm", table "dbo.ChauffeurOccasionnel", column 'EmployeId'.

 
Reste évidemment à créer le trigger interdisant aux chauffeurs occasionnels de devenir des chauffeurs de la société. A vous de jouer ! 

[fsmrel]

Bonsoir,

Une précision concernant la fonction Exclusion_ChauffeurSociete_fonction (point (C) dans mon message précédent) :

La table ChauffeurOccasionnel a été dotée de la contrainte suivante :

Exclusion_ChauffeurSociete

Si l’on veut supprimer/recréer la fonction, il faudra au préalable supprimer cette contrainte :
 

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER TABLE ChauffeurOccasionnel
 DROP CONSTRAINT if exists Exclusion_ChauffeurSociete ;
GO

[dalinbm]

bonsoir,
Merci, merci, merci infiniment fsmrel , vraiment vous faites des bon soutien pour les nouveaux et pour les adhérons de ce forum.
En effet, j’espère bien que ce script SQL réponds au exigence de mes réglés de gestion .

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 CREATE TABLE Type_Mission(
   Id_Type_Mission INT,
   Designation_TMission VARCHAR(50),
   PRIMARY KEY(Id_Type_Mission)
);

CREATE TABLE Type_Site(
   Id_Type_Site INT,
   Designation_TSte VARCHAR(50),
   PRIMARY KEY(Id_Type_Site)
);

CREATE TABLE Type_Reservation(
   Id_Type_Reservation INT,
   Designation_T_Reservation VARCHAR(50),
   PRIMARY KEY(Id_Type_Reservation)
);

CREATE TABLE Type_Transport(
   Id_Type_Transport INT,
   Designation_TTransport VARCHAR(50),
   PRIMARY KEY(Id_Type_Transport)
);

CREATE TABLE Poste(
   Id_Poste INT,
   Nom_Poste VARCHAR(50),
   PRIMARY KEY(Id_Poste)
);

CREATE TABLE TypeFrais(
   IdTypeFrais INT,
   DesignatioTfrais VARCHAR(50),
   PRIMARY KEY(IdTypeFrais)
);
CREATE TABLE Modele(
   IdModele INT,
   NomModele VARCHAR(50),
   PRIMARY KEY(IdModele)
);
 CREATE TABLE Employe
(
   EmployeId INT 
 , EmployeMatricule VARCHAR(8) NOT NULL
 , EmployeNom VARCHAR(50) NOT NULL
 , EmployePrenom VARCHAR(50) NOT NULL
 , CONSTRAINT Employe_PK PRIMARY KEY(EmployeId)
 , CONSTRAINT Employe_AK UNIQUE(EmployeMatricule)
);
 CREATE TABLE Mission
(
   MissionId INT 
 , MissionCode VARCHAR(8) NOT NULL
 , MissionDesignation VARCHAR(50) NOT NULL
 , MissionDateDebut DATE NOT NULL
 , MissionDateFin DATE NOT NULL
 , CONSTRAINT Mission_PK PRIMARY KEY(MissionId)
);
 CREATE TABLE EmployeMission
(
   MissionId INT
 , EmployeId INT
 , CONSTRAINT EmployeMission_PK PRIMARY KEY(MissionId, EmployeId)
 , CONSTRAINT EmployeMission_Mission_FK FOREIGN KEY(MissionId) REFERENCES Mission(MissionId)
 , CONSTRAINT EmployeMission_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
);
  
CREATE TABLE Chauffeur(
   EmployeId INT,
   CONSTRAINT Chauffeur_PK PRIMARY KEY(EmployeId),
   CONSTRAINT Chauffeur_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
       ON DELETE CASCADE
);
 
CREATE TABLE ChauffeurOccasionnel(
   EmployeId INT,
   CONSTRAINT ChauffeurOccasionnel_PK PRIMARY KEY(EmployeId),
   CONSTRAINT ChauffeurOccasionnel_Chauffeur_FK FOREIGN KEY(EmployeId) REFERENCES Chauffeur(EmployeId)
       ON DELETE CASCADE
	   
);
CREATE TABLE ChauffeurSociete(
   EmployeId INT,
   CONSTRAINT ChauffeurSociete_PK PRIMARY KEY(EmployeId),
   CONSTRAINT ChauffeurSociete_Chauffeur_FK FOREIGN KEY(EmployeId) REFERENCES Chauffeur(EmployeId)
   ON DELETE CASCADE);
CREATE TABLE Voiture_Personnel(
   Id_Voiture INT,
   IdEmploye INT NOT NULL,
   PRIMARY KEY(Id_Voiture),
   UNIQUE(IdEmploye),
   FOREIGN KEY(Id_Voiture) REFERENCES Voiture(Id_Voiture),
   FOREIGN KEY(IdEmploye) REFERENCES chauffeur_occasionel(IdEmploye)
);
CREATE TABLE MissionAchauffeurOccas(
   Id_Mission INT,
   Id_Voiture INT NOT NULL,
   PRIMARY KEY(Id_Mission),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Voiture) REFERENCES Voiture_Personnel(Id_Voiture)
);


CREATE TABLE Utiliser_Transport(
   Id_Mission INT,
   Id_Type_Transport INT,
   PRIMARY KEY(Id_Mission, Id_Type_Transport),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Type_Transport) REFERENCES Type_Transport(Id_Type_Transport)
);

CREATE TABLE Defrayer(
   Id_Mission INT,
   IdEmploye INT,
   IdTypeFrais INT,
   FraisMontant DOUBLE,
   PRIMARY KEY(Id_Mission, IdEmploye, IdTypeFrais),
   FOREIGN KEY(Id_Mission, IdEmploye) REFERENCES EmployeMission(Id_Mission, IdEmploye),
   FOREIGN KEY(IdTypeFrais) REFERENCES TypeFrais(IdTypeFrais)
);

CREATE TABLE Planifier(
   Id_Mission INT,
   Id_Site INT,
   PRIMARY KEY(Id_Mission, Id_Site),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Site) REFERENCES Site(Id_Site)
);
ALTER TABLE Mission
ADD CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck CHECK (MissionDateDebut <= MissionDateFin);
ALTER TABLE ChauffeurOccasionnel
ADD CONSTRAINT Exclusion_ChauffeurSociete 
CHECK (dbo.Exclusion_ChauffeurSociete_fonction(EmployeId) = 'ok') ;
ALTER TABLE ChauffeurOccasionnel DROP CONSTRAINT if exists Exclusion_ChauffeurSociete ;

NB:J’utilise MySQL phpMyAdmin. Il n'accepte pas cette requête :CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck CHECK (MissionDateDebut <= MissionDateFin)
de même je veux attribué des id commence par un entier spécifique (exemple pour IdTypeMission je veux que le premier type prend 10 et le deuxième prend 20 )

[fsmrel]

Bonsoir,
 

J’utilise MySQL phpMyAdmin. Il n'accepte pas cette requête :CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck CHECK (MissionDateDebut <= MissionDateFin)

 
Votre post #20 :
 

En passant, quel est votre SGBD ? Vu le modèle de votre 1er message, ce serait SQL Server. Exact ?

Oui, c'est bien dit , J'applique les modifications très prochainement.

 
=>

Je croyais que vous utilisiez SQL Server. Si je comprends bien, vous basculez vers MySQL.

Le code ci-dessous est correct avec SQL Server :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
ALTER TABLE Mission
ADD CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck CHECK (MissionDateDebut <= MissionDateFin);

Je ne connais pas MySQL, mais d’après la doc officielle de ce produit (version 8.0.16), il ne devrait pas y avoir de problème, voyez ici :

https://dev.mysql.com/blog-archive/m...ck-constraint/

Vérifiez que votre version de MySQL n’est pas antérieure.
Pour ma part, je n’ai jamais utilisé phpMyAdmin et ne saurais en l’occurrence être d’un quelconque secours...
 
 

je veux attribué des id commence par un entier spécifique (exemple pour IdTypeMission je veux que le premier type prend 10 et le deuxième prend 20 )

 
Avec SQL Server, utiliser l’instruction CREATE SEQUENCE.

Avec MySQL, peut-être avec la fonction LAST_INSERT_ID()? Voyez par exemple :

https://www.mysqltutorial.net/mysql-sequence/


Table Defrayer : comme je l’ai déjà écrit, n’utilisez pas le type DOUBLE pour l’attribut FraisMontant ! Le type DECIMAL est celui qui est pertinent.

Pour les clés étrangères, vous codez systématiquement :

FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission)

Mais la clé primaire de la table Mission contient l’attribut MissionId.

[fsmrel]

Suite,

Pour faciliter la mise en oeuvre des contraintes des chauffeurs et des voitures, la vue suivante du MCD convient mieux : les missions sont soit avec des chauffeurs occasionnels (entité-type MissionAchauffeurSte), soit avec des chauffeurs de société (entité-type MissionAchaufeurOccas).
 

Les DROP/CREATE TABLE correspondants :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
begin transaction ;
 
DROP TABLE IF EXISTS MissionAchaufeurOccas ;
DROP TABLE IF EXISTS MissionAchauffeurSte ;
DROP TABLE IF EXISTS VoiturePersonnelle ;
DROP TABLE IF EXISTS ChauffeurOccasionnel ;
DROP TABLE IF EXISTS ChauffeurSociete ;
DROP TABLE IF EXISTS Chauffeur ;
DROP TABLE IF EXISTS VoitureSociete ;
DROP TABLE IF EXISTS Voiture ;
 
DROP TABLE IF EXISTS EmployeMission ;
DROP TABLE IF EXISTS Mission ;
DROP TABLE IF EXISTS TypeMission ;
DROP TABLE IF EXISTS MoyenDeplacement ;
DROP TABLE IF EXISTS EmployeFonction ;
DROP TABLE IF EXISTS Employe ;
DROP TABLE IF EXISTS Fonction ;
go
 
CREATE TABLE Fonction(
   FonctionId INT,
   FonctionDesignation VARCHAR(50) NOT NULL,
   CONSTRAINT Fonction_PK PRIMARY KEY(FonctionId)
);
 
CREATE TABLE Employe(
   EmployeId INT,
   Matricule VARCHAR(8) NOT NULL,
   EmployeNom VARCHAR(50) NOT NULL,
   EmployePrenom VARCHAR(50) NOT NULL,
   CONSTRAINT Employe_PK PRIMARY KEY(EmployeId),
   CONSTRAINT Employe_AK UNIQUE(Matricule)
);
 
CREATE TABLE EmployeFonction(
   EmployeId INT,
   FonctionId INT,
   CONSTRAINT EmployeFonction_PK PRIMARY KEY(EmployeId, FonctionId),
   CONSTRAINT EmployeFonction_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
       ON DELETE CASCADE,
   CONSTRAINT EmployeFonction_Fonction_FK FOREIGN KEY(FonctionId) REFERENCES Fonction(FonctionId)
);
 
 
CREATE TABLE Voiture(
   VoitureId INT,
   Immatriculation VARCHAR(16) NOT NULL,
   CONSTRAINT Voiture_PK PRIMARY KEY(VoitureId),
   CONSTRAINT Voiture_AK UNIQUE(Immatriculation)
);
 
CREATE TABLE Chauffeur(
   EmployeId INT,
   CONSTRAINT Chauffeur_PK PRIMARY KEY(EmployeId),
   CONSTRAINT Chauffeur_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
);
 
CREATE TABLE ChauffeurOccasionnel(
   EmployeId INT,
   CONSTRAINT ChauffeurOccasionnel_PK PRIMARY KEY(EmployeId),
   CONSTRAINT ChauffeurOccasionnel_Chauffeur_FK FOREIGN KEY(EmployeId) REFERENCES Chauffeur(EmployeId)
);
 
CREATE TABLE ChauffeurSociete(
   EmployeId INT,
   CONSTRAINT ChauffeurSociete_PK PRIMARY KEY(EmployeId),
   CONSTRAINT ChauffeurSociete_Chauffeur_FK FOREIGN KEY(EmployeId) REFERENCES Chauffeur(EmployeId)
);
 
CREATE TABLE VoitureSociete(
   VoitureId INT,
   CONSTRAINT VoitureSociete_PK PRIMARY KEY(VoitureId),
   CONSTRAINT VoitureSociete_Voiture_FK FOREIGN KEY(VoitureId) REFERENCES Voiture(VoitureId)
);
 
CREATE TABLE TypeMission(
   TypeMissionId INT,
   TypeMissionDesignation VARCHAR(50) NOT NULL,
   CONSTRAINT TypeMission_PK PRIMARY KEY(TypeMissionId)
);
 
CREATE TABLE MoyenDeplacement(
   MoyenDeplacementId INT,
   MoyenDeplacementDesignation VARCHAR(50) NOT NULL,
   CONSTRAINT MoyenDeplacement_PK PRIMARY KEY(MoyenDeplacementId)
);
 
CREATE TABLE Mission(
   MissionId INT,
   MissionCode VARCHAR(8) NOT NULL,
   MissionDateDebut DATE NOT NULL,
   MissionDateFin DATE NOT NULL,
   MissionDesignation VARCHAR(50) NOT NULL,
   MoyenDeplacementId INT NOT NULL,
   TypeMissionId INT NOT NULL,
   CONSTRAINT Mission_PK PRIMARY KEY(MissionId),
   CONSTRAINT Mission_AK UNIQUE(MissionCode),
   CONSTRAINT Mission_MoyenDeplacement_FK FOREIGN KEY(MoyenDeplacementId) REFERENCES MoyenDeplacement(MoyenDeplacementId),
    CONSTRAINT Mission_TypeMission_FK FOREIGN KEY(TypeMissionId) REFERENCES TypeMission(TypeMissionId)
);
 
CREATE TABLE EmployeMission(
   MissionId INT,
   EmployeId INT,
   CONSTRAINT EmployeMission_PK PRIMARY KEY(MissionId, EmployeId),
   CONSTRAINT EmployeMission_Mission_FK FOREIGN KEY(MissionId) REFERENCES Mission(MissionId),
   CONSTRAINT EmployeMission_Employe_FK FOREIGN KEY(EmployeId) REFERENCES Employe(EmployeId)
);
 
CREATE TABLE VoiturePersonnelle(
   VoitureId INT,
   EmployeId INT NOT NULL,
   CONSTRAINT VoiturePersonnelle_PK PRIMARY KEY(VoitureId),
   CONSTRAINT VoiturePersonnelle_AK UNIQUE(EmployeId),
   CONSTRAINT VoiturePersonnelle_Voiture_FK FOREIGN KEY(VoitureId) REFERENCES Voiture(VoitureId),
   CONSTRAINT VoiturePersonnelle_ChauffeurOccasionnel_FK FOREIGN KEY(EmployeId) REFERENCES ChauffeurOccasionnel(EmployeId)
);
 
CREATE TABLE MissionAchauffeurSte(
   MissionId INT,
   VoitureId INT NOT NULL,
   EmployeId INT NOT NULL,
   CONSTRAINT MissionAchauffeurSte_PK PRIMARY KEY(MissionId),
   CONSTRAINT MissionAchauffeurSte_Mission_FK FOREIGN KEY(MissionId) REFERENCES Mission(MissionId),
   CONSTRAINT MissionAchauffeurSte_VoitureSociete_FK FOREIGN KEY(VoitureId) REFERENCES VoitureSociete(VoitureId),
   CONSTRAINT MissionAchauffeurSte_ChauffeurSociete_FK FOREIGN KEY(EmployeId) REFERENCES ChauffeurSociete(EmployeId)
);
 
CREATE TABLE MissionAchaufeurOccas(
   MissionId INT,
   VoitureId INT NOT NULL,
   CONSTRAINT MissionAchaufeurOccas_PK PRIMARY KEY(MissionId),
   CONSTRAINT MissionAchaufeurOccas_Mission_FK FOREIGN KEY(MissionId) REFERENCES Mission(MissionId),
   CONSTRAINT MissionAchaufeurOccas_VoiturePersonnelle_FK FOREIGN KEY(VoitureId) REFERENCES VoiturePersonnelle(VoitureId)
);
-- la date de fin d'une mission ne doit pas être antérieure à la da de début...
ALTER TABLE MISSION  
  ADD CONSTRAINT Mission_Datedebut_Avant_Datefin_Chck 
    CHECK (MissionDateDebut <= MissionDateFin) ;
 
commit transaction ;

Il est tard. Un jeu d’essai suivra demain.