Discussion :

[lesultan2007]

bonjour à tous, j'ai 3 tables:

RESERVATION(NUMERORES,IDTYPERES,IDSTATUT,MATRICULEEMPLOYE,
DATEDEMANDE,DATEDEB,DATEFIN,HEUREDEB,HEUREFIN)

MATERIEL(CODEMAT,NUMEROSERIEMAT,LIBELLEMAT)

PORTER(NUMERORES,CODEMAT)

Losrqu'on fait une réservation les tables "reservation" et "porter" sont renseignées. Pour une réservation on peut réserver plusieurs matériels. Alors je voulais savoir si cette modélisation me permettra facilement d'extraire à l'aide d'une requête le libellé des matériels disponibles?Si oui comment aidez moi svp...

[hed62]

Ton problème est plutôt porté sur ta modélisation/conception, ou sur la requête à faire ?

[lesultan2007]

je pense que c'est au niveau de ma modélisation est ce qu'on ne pourrait pas en trouver une qui permette d'extraire les matériels disponibles? merci d'avance

[fsmrel]

j'ai 3 tables:

RESERVATION(NUMERORES,IDTYPERES,IDSTATUT,MATRICULEEMPLOYE,
DATEDEMANDE,DATEDEB,DATEFIN,HEUREDEB,HEUREFIN)

MATERIEL(CODEMAT,NUMEROSERIEMAT,LIBELLEMAT)

PORTER(NUMERORES,CODEMAT)

Lorsqu'on fait une réservation les tables "reservation" et "porter" sont renseignées. Pour une réservation on peut réserver plusieurs matériels. Alors je voulais savoir si cette modélisation me permettra facilement d'extraire à l'aide d'une requête le libellé des matériels disponibles? Si oui comment...

Il y a du flou.

Quelles colonnes sont impliquées dans la détermination des matériels disponibles, et pour quelle raison ?

Il faudrait préciser le genre de date dont est fonction la disponibilité : date du jour, date quelconque fournie par l'utilisateur, etc.

Fournissez les définitions des données, en effet un nom de colonne ne vaut pas définition. Ainsi, à quoi correspond un statut (cf. colonne IDSTATUT), joue-t-il un rôle dans votre affaire ?

A quoi correspondent formellement les colonnes DATEDEB et DATEFIN ? Quel rôle jouent-elles ?

Précisez quelles sont les clés primaires des tables.

Sans tous ces éléments, avant même de pouvoir répondre à votre question, on ne peut pas dire si votre modèle est satisfaisant (je subodore du reste qu’il est simplifiable).

Pour mieux aborder les problèmes, un outil de modélisation s’impose, par exemple MySQL Workbench (gratuit).

[lesultan2007]

merci en fait les 3 colones sont impliqués.la table matériel sert à stocker les différents matériels. Le systeme doit etre fai de sotre que pou une reservation on puisse choisi plusieurs matériels et le mem matériel paut faire l'objet de plusieurs reservation.
La table "porter" permetrra donc de savoir quels matériels ont été choisis pour une reservation donnée."DATEDEB" et "DATEFIN" permettre de savoir la période de réservation et donc la période de réservation de matériel puisque une réservation comporte des matériels. Ce sont des dates quelkqiuonque fouynie par l'utilisateur. idstatut et idtype res sont des cles etrangere provenan des tables "STATUTRESERVATION" et "TYPERESERVATION".
numerores : cle primaire de "RESERVATION"

[fsmrel]

les 3 colones sont impliqués

Quelles trois colonnes ? MATRICULEEMPLOYE ? HEUREFIN ? LIBELLEMAT ?

Ma question était : "Quelles colonnes sont impliquées dans la détermination des matériels disponibles ?"

Autrement dit, quelles sont celles qui permettent d’éviter des réservations en double, façon surbooking des TGV et des avions.
Merci d’en préciser le nom, ainsi que le nom des tables qui les hébergent. Par exemple, la colonne CODEMAT figurant dans les tables MATERIEL et PORTER est impliquée, puisque sans cela on ne sait pas de quel matériel il est question. Les dates ont certainement un rôle à jouer pour éviter les réservations en double. Peut-être aussi le statut d’une réservation ? Soyez précis et exhaustif.

Quand je pose la question : "Fournissez les définitions des données, en effet un nom de colonne ne vaut pas définition. Ainsi, à quoi correspond un statut (cf. colonne IDSTATUT), joue-t-il un rôle dans votre affaire ?"
Vous répondez que IDSTATUT est une clé étrangère référençant la table STATUTRESERVATION : on n’est pas plus avancés d’un point de vue sémantique : en corrélation avec mes remarques précédentes, précisez si le statut intervient oui ou non dan le blocage d’une tentative de réservation.

Quand je pose la question : "Précisez quelles sont les clés primaires des tables ?"
Vous ne répondez que pour une seule table, sachant que la table la plus sensible dans cette affaire est la table PORTER.

En fait, on est toujours au point mort. Je me doute qu'un matériel peut faire l'objet de plusieurs réservations, mais quand même pas en même temps (quoique, avec un peu de surbooking...)

Et n’oubliez pas de modéliser avec un outil, que ce soit celui que j’ai cité à titre d’exemple, ou avec tout autre.

[lesultan2007]

merci beaucoup déjà pour ce que vous faites.
En fait les colonnes qui entrent en ligne de compte dans la détermination des matériels disponibles sont:

RESERVATION.NUMERORES, PORTER.NUMERORES, MATERIEL.CODEMAT, PORTER.CODEMAT, RESERVATION.DATEDEB, RESERVATION.DATEFIN

Non le statut d'une réservation n'intervient pas dans la détermination des matériels disponibles. Le statut sert à déterminer si une réservation est en attente ou est validée. Même chose pour le type de réservation. Il sert à voir si la réservation est relative à une réunion ou à une formation.

La table RESERVATION a pour cle primaire NUMERORES
La table MATERIEL a pour cle primaire CODEMAT
La table PORTER a pour cle l'ensemble (NUMERORES,CODEMAT). Dans la table matériel on enregistre les matériels. Ainsi lorsqu'on fait une réservation (Ex:Réservation N°1), on choisi les matériels liés (Ex: mat 1 qui a pour code 001, mat2 qui a pour code 002) à cette réservation. A l'issue de cette réservation les colonnes des tables RESERVATION ET PORTER sont renseignés. Donc dans la table porter on aura les enregistrement suivants:

1,001
1,002

Je modélise avec power amc 11. Merci pour tout et j'espère que j'ai bien exprimé mes besoins.

[fsmrel]

Bonjour,

Je modélise avec PowerAMC 11.

Que ne le disiez-vous ! Nous aurions pu gagner du temps, car un schéma est dénué d’ambiguïté, contrairement a des formulations textuelles.
Voyez-vous, pour ma part je n’hésite pas à représenter graphiquement la façon dont j’interprète ce que vous avez écrit.

MCD :


MLD :



Dans mon précédent message, j’avais fait la remarque suivante :

Je me doute qu'un matériel peut faire l'objet de plusieurs réservations, mais quand même pas en même temps (quoique, avec un peu de surbooking...)

Manifestement selon le MCD, le surbooking est autorisé, car selon l’association-type Porter, le même matériel, disons 001 peut être associé à plusieurs réservations : 1, 2, etc. Tandis que la période de réservation ne fait l’objet d’aucune contrainte en ce sens.

Avant d’aller plus loin, j’attends que vous vous positionniez à ce sujet.

[lesultan2007]

merci, c'est vrai que j'aurai pu montrer le shema sous power amc mais bon j'y ai pas pensé. ceci dit je suis entièrement daccord avec les modèles que vous avez sorti... Effectivement le meme matériel peut faire l'objet de plusieurs réservations quelque soit la période de réservation...

[fsmrel]

le meme matériel peut faire l'objet de plusieurs réservations quelque soit la période de réservation...

Par exemple si le matériel est un ordinateur portable, deux personnes pourraient le réserver simultanément pour la semaine, alors que dans le même temps, l'une prendrait le train direction Bordeaux, tandis que l'autre prendrait l'avion pour Moscou, chacune emportant ce matériel ? Gloups...

[lesultan2007]

merci effectivement je viens de me rendre compte de la bêtise que j'étais en train de faire. Normalement le systeme ne doit pas permettre que le même matériel fasse l'objet de plusieurs réservation à la même période. Mais comment le modéliser?

[fsmrel]

Normalement le systeme ne doit pas permettre que le même matériel fasse l'objet de plusieurs réservation à la même période. Mais comment le modéliser?

Les attributs DateDeb, DateFin, HeureDeb, HeureFin doivent être transférés dans l’association-type Porter. Ainsi, vous savez de façon simple que tel matériel fait l’objet d’une réservation pendant telle période. Mais, pour se prémunir contre les recouvrements de périodes, au niveau SQL il faudra prévoir un trigger à cet effet. Si je trouve un peu de temps, j’essaierai de vous aider à ce sujet.

Pour la partie CREATE TABLE :

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
Create Table Employe (
   MatriculeEmp         Int                  Not null,
   NomEmp               Varchar(48)          Not null,
   Constraint Employe_PK Primary Key  (MatriculeEmp) 
) ;
Create Table Materiel (
   CodeMat              Int                  Not null,
   NoSerieMat           Varchar(48)          Not null,
   LibelleMat           Varchar(48)          Not null,
   Constraint Materiel_PK Primary Key  (CodeMat),
   Constraint MATERIEL_AK Unique (NoSerieMat)
) ;
Create Table StatutRes (
   IdStatut             Int                  Not null,
   LibelleStatut        Varchar(48)          Not null,
   Constraint StatutRes_PK Primary Key  (IdStatut)
) ;
Create Table TypeRes (
   IdTypeRes            Int                  Not null,
   LibelleTypeRes       Varchar(48)          Not null,
   Constraint TypeRes_PK Primary Key  (IdTypeRes)
) ;

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
Create Table Reservation (
   NumeroRes            Int                  Not null,
   IdStatut             Int                  Not null,
   MatriculeEmp         Int                  Not null,
   IdTypeRes            Int                  Not null,
   DateDemande          Char(10)             Not null,
   Constraint Reservation_PK Primary Key  (NumeroRes),
   Constraint Reservation_Employe_1 Foreign Key (MatriculeEmp)
      References Employe (MatriculeEmp),
   Constraint Reservation_TypeRes_2 Foreign Key (IdTypeRes)
      References Typeres (IdTypeRes),
   Constraint Reservation_StatutRes_3 Foreign Key (IdStatut)
      References Statutres (IdStatut)
) ;

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
Create Table Porter (
   CodeMat              Int                  Not null,
   NumeroRes            Int                  Not null,
   DateDeb              Char(10)             Not null,
   DateFin              Char(10)             Not null,
   HeureDeb             Char(10)             Not null,
   HeureFin             Char(10)             Not null,
   Constraint Porter_PK Primary Key  (CodeMat, NumeroRes),
   Constraint Porter_Materiel Foreign Key (CodeMat)
      References Materiel (CodeMat),
   Constraint Porter_Reservation Foreign Key (NumeroRes)
      References Reservation (NumeroRes),
   Constraint Porter_Date Check (Datefin >= DateDeb)
) ;

En passant, notez la contrainte Porter_Date pour la table Porter.

[lesultan2007]

ok merci pour tout je vais mettre en oeuvre cette solution mais j'avoue que je ne vois pas bien. Tel que vous avez fait un même matériel pourra toujours faire l'objet de plusieurs réservations à une même période. On peut avoir comme occurrence de "porter": (res 1,mat1, 12/04/2008, 14/04/2008, 15h, 17h)

(res2, mat1, 12/04/2008, 14/04/2008, 15h, 17h) non?

[fsmrel]

ok merci pour tout je vais mettre en oeuvre cette solution mais j'avoue que je ne vois pas bien. Tel que vous avez fait un même matériel pourra toujours faire l'objet de plusieurs réservations à une même période. On peut avoir comme occurrence de "porter": (res 1,mat1, 12/04/2008, 14/04/2008, 15h, 17h)

(res2, mat1, 12/04/2008, 14/04/2008, 15h, 17h) non?

Certes, dans la situation actuelle, rien n’interdit d’avoir plus d’une réservation à une période donnée. Mais les contrôles à effectuer nécessitent la mise en œuvre de l’instruction CREATE ASSERTION (ou CREATE TRIGGER si votre SGBD ne la propose pas). Dans mon précédent message, j’avais écrit :

pour se prémunir contre les recouvrements de périodes, au niveau SQL il faudra prévoir un trigger à cet effet. Si je trouve un peu de temps, j’essaierai de vous aider à ce sujet.

Voici donc un exemple (SQL Server) de triggers pour empêcher ces recouvrements. Je me suis limité à contrôler les dates, je vous laisse le soin d'étendre les contrôles aux heures.

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
Create Trigger T1 On Porter Instead of Insert As
 Select  ''   
 From    Inserted x Inner Join Porter y On x.CodeMat = y.CodeMat
 where   x.DateDeb < y.DateDeb  And x.DateFin >= y.DateDeb 
     Or  x.DateDeb >= y.DateDeb And x.DateDeb <= y.DateFin     
;
 If @@Rowcount > 0
    Begin 
       Raiserror ('Insert : Période déjà affectée',10,1) 
    Return
 End
 Insert into Porter 
     Select *
     From   Inserted 
GO

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
Create trigger T2 On Porter Instead Of Update As
  IF Update(DateDeb) or Update(DateFin)
    Select  '' 
    From    Inserted x Inner Join Porter y 
            On x.CodeMat = y.CodeMat And x.NumeroRes <> y.NumeroRes
    Where   x.DateDeb < y.DateDeb  And x.DateFin >= y.DateDeb 
       Or   x.DateDeb >= y.DateDeb And x.DateDeb <= y.DateFin     
  If @@Rowcount > 0
     Begin 
       Raiserror ('Update : Période déjà affectée ',10,1) 
     Return
  End
  Update Porter
    Set DateDeb = (Select Inserted.DateDeb
                   From   Inserted
                   Where  Porter.CodeMat = Inserted.CodeMat 
                     And  Porter.NumeroRes = Inserted.NumeroRes)
     ,
       DateFin = (Select Inserted.DateFin 
                  From   Inserted
                  where  Porter.CodeMat = Inserted.CodeMat 
                    and  Porter.NumeroRes = Inserted.NumeroRes)
    ,
       HeureDeb = (Select Inserted.HeureDeb
                   From   Inserted
                   Where  Porter.CodeMat = Inserted.CodeMat
                     And  Porter.NumeroRes = Inserted.NumeroRes)
   ,
      HeureFin = (Select Inserted.HeureFin 
                  From   Inserted
                  where  Porter.CodeMat = Inserted.CodeMat 
                    and  Porter.NumeroRes = Inserted.NumeroRes)
Where Exists (Select *
                 From   Inserted
                 Where  Porter.CodeMat = Inserted.CodeMat 
                    And Porter.NumeroRes = Inserted.NumeroRes) ;

Bien entendu, ces triggers sont à adapter en fonction de votre SGBD et à tester...

[lesultan2007]

ok merci pour tout