Intranet école supérieure

**fsmrel** · 05/08/2012, 11h46

Bonjour spring,

Si vous abandonnez la discussion dans laquelle le lion a rugi, n’oubliez pas de la marquer résolue.

Have a good time!

**spring.time** · 05/08/2012, 15h45

bonjour

svp, concernant le MLD des bulletins:
vous parlez de bulletin de rattrapage et sans rattrapage, mais je ne vois que la variable relationnelle Bulletin.

Concernant la moyenne de la formation scientifique et la moyenne générale avec ou sans rattrapage, on a tous les éléments pour calculer ça

mais si on pouvait les stocker, pour l'historique et pour ne pas refaire à chaque besoin leur calcul, ou' les mettre?

autre chose, je vois dans le MLD, qu'il y'a un lien entre NOTE_DISCIPLINE et BULLETIN, le sens de la fleche, je ne comprends pas

pour BULLETIN, vous signifiez son type, mais comment on peut distinguer entre les 2?

le lien entre NOTE et BULLETIN, svp pourriez vous m'expliquer d'avantage?
et Merci

**spring.time** · 05/08/2012, 23h56

Salut,
j'ai testé la 1ère requete, pour chaque promotion x et année y,( #78)
mais je ne reçois pas une liste triée par l'ordre alphabétique des noms

il faut que je rajoute quelque chose?

svp j'ai essayé de décortiquer la requete pour la comprendre, mais

pourriez vous m'expliquer svp les jointures?

**fsmrel** · 06/08/2012, 02h48

Hello spring,

Envoyé par spring.time

Je ne reçois pas une liste triée par l'ordre alphabétique des noms, il faut que je rajoute quelque chose?

Il y a un ORDER BY x.EtudiantNom qui a manifestement disparu à l’occasion des copier/coller et qu'il faut mettre à sa place. Requête complétée (ligne 14) :

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
INSERT INTO @T (EtudiantId, EtudiantNom) 
    SELECT x.EtudiantId, x.EtudiantNom
    FROM   ETUDIANT AS x JOIN SCOLARITE AS y ON x.EtudiantId = y.EtudiantId
           JOIN PROMOTION AS z ON y.PromotionId = z.PromotionId
           JOIN ANNEE_SCOLAIRE AS t ON y.AnneeScolaireId = t.AnneeScolaireId
    WHERE  z.PromotionNo = @PromotionNo
      AND  t.AnneeScolaireLibelle = @Annee
      AND  NOT EXISTS 
              (SELECT *
               FROM   @REDOUBLANT AS r
               WHERE  x.EtudiantId = r.EtudiantId
                 AND  y.AnneeScolaireId = r.AnneeScolaireId
              )
    ORDER BY x.EtudiantNom
;

Tenez-moi au courant.

Envoyé par spring.time

J'ai essayé de décortiquer la requete pour la comprendre, mais pourriez vous m'expliquer svp les jointures ?

Qu'est-ce qui ne va pas avec les jointures ? Est-ce parce qu'il y a 4 tables concernées (triple jointure) ?

**spring.time** · 06/08/2012, 03h40

je vous remercie pour votre générosité et pour votre patience...

Qu'est-ce qui ne va pas avec les jointures ? Est-ce parce qu'il y a 4 tables concernées (triple jointure) ?

je ne saisis toujours pas les jointures avec

Code :

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

JOIN

, par exemple, pour la requete de insert de la variable table @REDOUBLANT, il y'a plus d'un appel de

Code :

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

JOIN PROMOTION as z

et autre

Code :

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

as t

, je ne comprends pas

**spring.time** · 06/08/2012, 03h47

oui ordrer by a fait l'affaire

**fsmrel** · 06/08/2012, 20h05

Bonsoir spring,

Je rappelle d’abord que la jointure est l’opération par excellence de l’algèbre relationnelle et c’est Ted Codd qui en est l’inventeur (en 1969). Pour les autres opérateurs, il les a repris des mathématiques en les adaptant aux relations n-aires, alors que les mathématiques s’intéressent essentiellement aux relations binaires (le terme relation n’a évidemment rien à voir avec les relations plutôt sémantiques (relationships) du modèle Entité/Relation de Chen ou de Merise).

Considérons la RELVAR (table SQL) des étudiants et celle des scolarités :

Code :

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

1
2
3
4
5
ETUDIANT {EtudiantId, EtudiantNom} 
    KEY {EtudiantId} ;

SCOLARITE {EtudiantId, AnneeScolaireId, PromotionId, NiveauId} 
    KEY {EtudiantId, AnneeScolaireId} ;

Les images suivantes symbolisent les relations (tables en SQL, lequel utilise le même mot pour variable et valeur) :

Code :

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

1
2
3
4
5
ETUDIANT    EtudiantId    EtudiantNom
            ----------    -----------
                     1    Albert 
                     2    Bernard
                    10    Inès

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SCOLARITE    EtudiantId    AnneeScolaireId    PromotionId    NiveauId
             ----------    ---------------    -----------    --------
                      1                  1              1           1
                      1                  2              1           2
                      1                  3              0           2

                      2                  1              1           1
                      2                  2              1           2
                      2                  3              1           3

                     10                  3              2           1
                     10                  4              2           2
                     10                  5              2           3

Selon SEQUEL (le nom de SQL en 1974), pour effectuer la jointure des deux tables, on écrit :

Code :

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

1
2
ETUDIANT, SCOLARITE
WHERE ETUDIANT.EtudiantId = SCOLARITE.EtudiantId ;

L’opérateur de comparaison utilisé ici est « = », on dit en l’occurrence qu’on effectue une équi-jointure. Disons que du point de vue théorique, on effectue le produit ETUDIANT X SCOLARITE, auquel on applique une restriction (WHERE).

En relationnel, la jointure se note tout simplement : ETUDIANT JOIN SCOLARITE, ou SCOLARITE JOIN ETUDIANT, car JOIN est commutatif. On peut aussi écrire : JOIN (ETUDIANT, SCOLARITE).

Par la suite, comme on peut effectuer des auto-jointures (jointure d’une table avec elle-même), il a été ajouté la possibilité de qualifier les colonnes au sein d’une requête SQL, en faisant précéder un nom de colonne d'un qualificateur plus un point séparateur (exemple : e.EtudiantId), qualificateur qui ci-dessous est un alias pour un nom de table ("e" pour ETUDIANT) :

Code :

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

1
2
ETUDIANT AS e, SCOLARITE AS s
WHERE e.EtudiantId = s.EtudiantId ;

Dans la requête que vous décortiquez, j’ai utilisé des qualificateurs, mais ça n’était pas nécessaire (je cherche seulement à gagner de la place à l’affichage...)

Pour produire un résultat (une relation) ne comportant que le contenu des colonnes intéressantes, en plus de la jointure on déclare une projection (SELECT en SQL) :

Code :

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

1
2
3
SELECT e.EtudiantId, e.EtudiantNom, s. PromotionId, s.AnneeScolaireId
FROM   ETUDIANT AS e, SCOLARITE AS s
WHERE  e.EtudiantId = s.EtudiantId ;

En relationnel :

JOIN (ETUDIANT, SCOLARITE) {EtudiantId, PromotionId, AnneeScolaireId}

Où {EtudiantId, PromotionId, AnneeScolaireId} dénote la projection (utilisation imposée des accolades).

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SCOLARITE    EtudiantId    EtudiantNom    PromotionId    AnneeScolaireId
             ----------    -----------    -----------    ---------------
                      1    Albert                   1                  1
                      1    Albert                   1                  2
                      1    Albert                   0                  3

                      2    Bernard                  1                  1
                      2    Bernard                  1                  2
                      2    Bernard                  1                  3

                     10    Inès                     2                  3  
                     10    Inès                     2                  4
                     10    Inès                     2                  5

Quand en 1986, Chris Date a proposé la jointure externe (OUTER JOIN), il a donné la possibilité de qualifier d’interne (INNER) la jointure SQL. Tout cela a été repris dans la norme SQL/92 et beaucoup de Sqlistes ont pris l’habitude d’utiliser la jointure interne :

Code :

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

1
2
SELECT e.EtudiantId, e.EtudiantNom, s. PromotionId, s.AnneeScolaireId
FROM   ETUDIANT AS e INNER JOIN SCOLARITE AS s ON e.EtudiantId = s.EtudiantId ;

(On peut faire l’économie du qualificatif INNER, et c’est du reste ce que j’ai fait).

Au nom du principe de fermeture, le résultat du mariage des relations est aussi une relation, le bébé est de même nature que ses parents et peut à son tour se marier et enfanter.

Appelons BÉBÉ le résultat de la jointure JOIN (ETUDIANT, SCOLARITE) :

BÉBÉ = JOIN (ETUDIANT, SCOLARITE)

On peut donc marier BÉBÉ et PROMOTION. En relationnel :

BÉBÉ JOIN PROMOTION

Et l’on écrit, pour tout marier en même temps (en notant que la jointure est associative)... :

JOIN (ETUDIANT, SCOLARITE, PROMOTION)

En SQL :

Code :

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

1
2
3
SELECT e.EtudiantId, e.EtudiantNom, s. PromotionId, s.AnneeScolaireId
FROM   ETUDIANT AS e JOIN SCOLARITE AS s ON e.EtudiantId = s.EtudiantId
                     JOIN PROMOTION AS p ON s. PromotionId = p.PromotionId ;

Comme il faut filtrer sur la promotion @PromotionNo, on effectue une restriction (WHERE) en plus :

Code :

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

1
2
3
4
SELECT e.EtudiantId, e.EtudiantNom, s. PromotionId, s.AnneeScolaireId
FROM   ETUDIANT AS e JOIN SCOLARITE AS s ON e.EtudiantId = s.EtudiantId
                     JOIN PROMOTION AS p ON s. PromotionId = p.PromotionId 
WHERE  p.PromotionNo = @PromotionNo ;

Par exemple, pour @PromotionNo = 41 (c'est-à-dire PromotionId = 1), le résultat précédent est restreint à :

Code :

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

1
2
3
4
5
6
7
8
9
SCOLARITE    EtudiantId    EtudiantNom    PromotionId    AnneeScolaireId
             ----------    -----------    -----------    ---------------
                      1    Albert                   1                  1
                      1    Albert                   1                  2

                      2    Bernard                  1                  1
                      2    Bernard                  1                  2
                      2    Bernard                  1                  3

Et comme on doit aussi filtrer sur l’année scolaire dont la valeur est à récupérer dans ANNEE_SCOLAIRE, on fait aussi participer cette table au mariage :

Code SQL :

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

1
2
3
4
5
SELECT e.EtudiantId, e.EtudiantNom, s. PromotionId, s.AnneeScolaireId
FROM   ETUDIANT AS e JOIN SCOLARITE AS s ON e.EtudiantId = s.EtudiantId
                     JOIN PROMOTION AS p ON s. PromotionId = p.PromotionId 
                     JOIN ANNEE_SCOLAIRE AS a ON s.AnneeScolaireId = a.AnneeScolaireId
WHERE  p.PromotionNo = @PromotionNo ;

Sans oublier de filtrer :

Code SQL :

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

1
2
3
4
5
6
SELECT e.EtudiantId, e.EtudiantNom, s. PromotionId, s.AnneeScolaireId
FROM   ETUDIANT AS e JOIN SCOLARITE AS s ON e.EtudiantId = s.EtudiantId
                     JOIN PROMOTION AS p ON s. PromotionId = p.PromotionId 
                     JOIN ANNEE_SCOLAIRE AS a ON s.AnneeScolaireId = a.AnneeScolaireId
WHERE  p.PromotionNo = @PromotionNo 
  AND  a. AnneeScolaireLibelle = @Annee ;

Pour l'année scolaire 2010-2011 (c'est-à-dire AnneeScolaireId = 3), le résultat est à nouveau restreint :

Code :

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

1
2
3
4
SCOLARITE    EtudiantId    EtudiantNom    PromotionId    AnneeScolaireId
             ----------    -----------    -----------    ---------------
                      2    Bernard                  1                  3

Et voilà...

Est-ce que ça va un peu mieux, ou est-ce que ça empire ?

**fsmrel** · 07/08/2012, 04h05

Envoyé par spring.time

je vois dans le MLD, qu'il y'a un lien entre NOTE_DISCIPLINE et BULLETIN, le sens de la flèche, je ne comprends pas

Le sens de la flèche reflète celui de la contrainte d’intégrité référentielle qui veut qu’une note fasse nécessairement référence à un bulletin. Mais ce MLD est en balance avec celui que je propose ci-dessous comme alternative.

Envoyé par spring.time

le lien entre NOTE et BULLETIN, svp pourriez vous m'expliquer d'avantage?

J’ai traité des notes dans le message #73, où j’expliquais que NOTE est le nom donné finalement à la relvar NOTE_6 résultant du travail de normalisation. Disons qu’à ce stade, on ne s'intéresse pas encore aux notes de rattrapage.

La partie correspondante du MCD était la suivante :

Dans ce diagramme, la mise entre parenthèses d’une cardinalité 1,1 symbolise l’identification relative (au sujet de ce type d'identification voyez notamment ici). Par exemple, SCOLARITE n’a pas d’identifiant propre, mais hérite des identifiants des types d’entités « partenaires », ETUDIANT et ANNEE_SCOLAIRE. Dans le langage de Peter Chen (auteur de l’article considéré comme fondateur du modèle Entité/Relation), on peut considérer SCOLARITE comme un type d’entité faible (weak entity).

En réalité SCOLARITE est un déguisement pour un type d’association SCOLARISER :

Pourquoi donc avoir transformé SCOLARISER en SCOLARITE ? Parce que — c’est ballot ! — le modèle Entité/Relation (tel qu’il est repris avec PowerAMC) ne permet pas d’associer une association, or SCOLARISER devrait être associée à NOTE et bien sûr à NIVEAU et PROMOTION. Si on prend l’exemple d’une association avec NIVEAU :

De la même façon, d’un point de vue sémantique, NOTE est en réalité une association (porteuse de données) — appelons-la SANCTIONNER_PAR_NOTE — entre les types d’entités SCOLARITE et DISCIPLINE :

Ce qui se lit : Au terme d’une année scolaire, la scolarité d’un étudiant est sanctionnée par une note dans chacune des disciplines qu’il a étudiées.

Quand interviennent les notes de rattrapage (message #74), on est conduit à établir une association entre l’association SANCTIONNER_PAR_NOTE et le type d’entité NOTE_RATTRAPAGE, ce que Power AMC interdit une fois de plus, toujours pour la même raison. La solution de contournement est donc de déguiser SANCTIONNER_PAR_NOTE en type d’entité (NOTE est renommée ici en NOTE_DISCIPLINE) :

Ce qui correspond aux règles de gestion :

Une note de rattrapage permet d’améliorer exactement (c'est-à-dire au moins une et au plus une) une note insuffisante dans une discipline.

Une note dans une discipline peut faire l’objet d’au plus une note de rattrapage.

Pour en venir au bulletins :

Une scolarité est sanctionnée par au plus un bulletin et un bulletin sanctionne exactement une scolarité, d’où le diagramme :

Un bulletin comporte entre autres choses le détail des notes obtenues par un étudiant dans les disciplines scientifiques de son niveau. La règle de gestion correspondante est la suivante :

Un bulletin comporte chacune des notes obtenues par un étudiant dans ses disciplines et une note obtenue par l'étudiant dans une discipline est inscrite sur son bulletin.

La modélisation correspondante est celle-ci (indépendamment des notes de rattrapage) :

Mais dans le message #77, j’ai proposé l’alternative suivante :

En effet, quand on descend des nuages pour atterrir au niveau opérationnel, on peut avoir des problèmes pour raccrocher les wagons entre BULLETIN et NOTE_DISCIPLINE, car les notes d’un étudiant doivent être connues au moment où il s'agit de constituer son bulletin : dans le contexte du Modèle Relationnel de Données, avec un langage du type Tutorial D, il n’y a pas de problème, on peut engranger en une fois le moyenne FM ainsi que le numéro de classement d’une part et les notes d’autre part, mais avec SQL Server, changement de chanson, cela doit se faire en deux temps (tandis que la norme SQL permet de biaiser, temporiser, grâce à la clause INITIALLY DEFERRED utilisable pour les clés étrangères). Quand on modélise, on doit privilégier les contraintes sémantiques : un bulletin comporte au moins une note dans une discipline, mais les remontées venant de la soute, peuvent nous détourner de ce principe, si l’on ne veut pas en baver des ronds de chapeau une fois au pied du mur SQL (prononcez Askew Wall, je vous laisse traduire...)

Vous avez bien sûr toute liberté pour faire comme vous l’entendez, mais pour ma part, j’opte pour la version pragmatique des choses dont voici le MLD (l’autre fois j’avais fourni le MLD de la version sémantique) :

.

A suivre (les bulletins de rattrapage et tout ça...)

**fsmrel** · 07/08/2012, 17h21

Bonjour,

Envoyé par spring.time

pour BULLETIN, vous signifiez son type, mais comment on peut distinguer entre les 2?

Distinguer entre quoi et quoi ?

Envoyé par spring.time

vous parlez de bulletin de rattrapage et sans rattrapage, mais je ne vois que la variable relationnelle Bulletin.

C’est normal. En effet, j’ai écrit :

Envoyé par fsmrel

Concernant le bulletin sans rattrapage, les informations qui ne sont pas modélisées sont les suivantes :

1 — Moyenne de la formation scientifique,
2 — Moyenne de la formation militaire,
3 — Moyenne générale sans rattrapage,
4 — Moyenne générale avec rattrapage,
5 — Numéro de classement,
6 — Et, si j’ai à peu près compris, pour les étudiants en fin de 6e année, la note se soutenance, la note de documentation et la note militaire.

Concernant le bulletin avec rattrapage, il ne manque rien.

Un bémol concernant le numéro de classement : la main de l’homme peut-elle intervenir hors algorithme, par exemple dans cas d’ex æquo à départager ? J’ai fait figurer un attribut NoClassement dans l’en-tête de BULLETIN, mais en théorie il est à retirer si le calcul est complètement automatique.

Si au niveau du MCD on mettait en œuvre un type d’entité BULLETIN_RATTRAPAGE (donc une relvar au niveau du MLD), on n’aurait rien à y mettre : on s’abstient donc de modéliser ce type d’entité (on pourra revenir sur ce sujet à propos de l’optimisation (quel vilain mot...) de la base de données, mais pour le moment, on part du principe qu’on a un système suffisamment puissant et des utilisateurs très patients...)

En revanche, rien n’empêche de définir une vue portant le nom de BULLETIN_RATTRAPAGE (ou BULLETIN_AR si vous préférez) et dont les données proviennent de relations existantes (attention, le mot relation est à prendre ici au sens du Modèle Relationnel de Données, une relation est une valeur prise par une relvar), que ces données soient de base ou calculées (à l’image de la moyenne de la formation scientifique ou de la moyenne générale) :

Code SQL :

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

1
2
CREATE VIEW BULLETIN_RATTRAPAGE (EtudiantId, AnneeScolaireId, MoyenneScientifique, MoyenneGenerale, ...) AS
SELECT ...

Dans le MCD, le type d’entité BULLETIN est une structure d’accueil pour les seules données concernant les bulletins, et qu’on ne sait pas inférer des autres données, en l’occurrence il s’agit de la moyenne de la formation militaire (comme je l’ai signalé, j’y ai fait figurer le numéro de classement, mais si on sait le calculer de façon totalement automatique, en toute rigueur NoClassement doit disparaître).

Envoyé par spring.time

Envoyé par fsmrel

Concernant la moyenne de la formation scientifique et la moyenne générale avec ou sans rattrapage, on a tous les éléments pour calculer ça.

mais si on pouvait les stocker , pour l'historique et pour ne pas refaire à chaque besoin leur calcul, où les mettre?

Vous abordez en fait le problème de l’optimisation (synonyme de : « amélioration de la performance »). Si on dispose de tous les éléments pour calculer les moyennes, avec ou sans rattrapage, quel intérêt aurait-on a priori à les « stocker » comme vous le souhaitez ? C’est là qu’intervient la mise en place par le DBA d’un prototype de performance, permettant d’estimer l’ordre de grandeur du temps nécessaire pour effectuer les calculs. Si les mesures montrent que la durée des calculs n’est pas perceptible, on en reste là. Si les mesures montrent que cette durée est excessive, alors seulement on « stocke » les résultats. A cet effet, la structure d’accueil naturelle serait la table BULLETIN pour ce qui ne concerne pas la partie rattrapage, cette dernière partie faisant l’objet d’une table — du bout des lèvres appelons-la BULLETIN_RATTRAPAGE — contenant les résultats de calcul (moyennes) propres au rattrapage et trop onéreux à produire. En tout état de cause, les résultats stockés ne devraient concerner que les calculs de moyenne faisant intervenir des notes. Si l’on considère la moyenne générale pour les étudiants de 6e année (cf. votre post #51) :

((((note soutenance x 2)+(note documentation x 2))/4) x3)+ note militaire)/4

Comme ces notes de soutenance, documentation et militaire sont déjà stockées, la durée du calcul de la moyenne générale ne va quand même pas mettre un ordinateur à genoux ! Cette moyenne n’a pas à faire l’objet d’un attribut polluant l’en-tête d’une quelconque table. Même chose en ce qui concerne la moyenne des moyennes.
Pour résumer, si le prototypage des performance montre que la durée de tel ou tel calcul faisant intervenir des notes est pénalisante, alors seulement on prévoira une structure d’accueil (attributs ad-hoc dans l’en-tête de BULLETIN et/ou mise en œuvre de BULLETIN_RATTRAPAGE) pour héberger les résultats correspondants :

MCD

MLD

A plus tard

**spring.time** · 09/08/2012, 16h26

je vous remercie beaucoup et excusez moi de mon absence

oui, j'ai bien compris la notion de join mais ici:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO @REDOUBLANT
    SELECT DISTINCT x.EtudiantId, x.AnneeScolaireId 
    FROM   SCOLARITE AS x JOIN SCOLARITE AS y ON  x.EtudiantId = y.EtudiantId
           JOIN PROMOTION AS z ON x.PromotionId = z.PromotionId
           JOIN PROMOTION AS t ON y.PromotionId = t.PromotionId
           JOIN ANNEE_SCOLAIRE AS v ON x.AnneeScolaireId = v.AnneeScolaireId
    WHERE  z.PromotionNo < t.PromotionNo
      AND  v.AnneeScolaireLibelle = @Annee
      AND  z.PromotionNo = @PromotionNo
 
SELECT '' AS 'REDOUBLANT', * FROM @REDOUBLANT ;

je ne comprends pas ces 2lignes:

Code :

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

1
2
 JOIN PROMOTION AS z ON x.PromotionId = z.PromotionId
           JOIN PROMOTION AS t ON y.PromotionId = t.PromotionId

**fsmrel** · 10/08/2012, 02h44

Bonsoir spring,

Envoyé par spring.time

je ne comprends pas ces 2 lignes :

Code :

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

1
2
 JOIN PROMOTION AS z ON x.PromotionId = z.PromotionId
           JOIN PROMOTION AS t ON y.PromotionId = t.PromotionId

Étant donné que j’effectue une auto-jointure pour SCOLARITE (jointure de la table avec elle-même), je compare la promotion z de la scolarité x de l’étudiant avec la promotion t de sa scolarité y. Mais peu importe, la requête n’est pas complète.

En attendant, pour simplifier les calculs, j’ai ajouté une colonne AnneeScolaireNo dans l’en-tête de la table ANNEE_SCOLAIRE, prenant les valeurs 2008, 2009, etc.) :

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE ANNEE_SCOLAIRE
(
        AnneeScolaireId       INT          NOT NULL
      , AnneeScolaireNo       INT          NOT NULL
      , AnneeScolaireLibelle  VARCHAR(64)  NOT NULL
    , CONSTRAINT ANNEE_SCOLAIRE_PK PRIMARY KEY (AnneeScolaireId)
) ;

Considérons maintenant l’exemple ci-dessous et cherchons les redoublants pour l’année scolaire 2012-2013, promotion 42 :

A l’origine, Louis, Dorine, Noémie et Quentin font partie de la promo 43 tandis qu’Olaf a toujours fait partie de la promo 42. Noémie et Quentin ont redoublé en 2011 et Dorine en 2012. La situation avant requête est la suivante :

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
              Année scolaire    Promo
              --------------    -----
Louis         2010-2011            43
              2011-2012            43
              2012-2013            43

Olaf          2009-2010            42
              2010-2011            42
              2011-2012            42
              2012-2013            42

Dorine        2010-2011            43
              2011-2012            43
              2012-2013            42

Noémie        2010-2011            43 
              2011-2012            42 
              2012-2013            42 

Quentin       2010-2011            43 
              2011-2012            42 
              2012-2013            42

Nouvelle version de la requête d’insert (avec @Annee = '2012-2013' et @PromotionNo = 42) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO @REDOUBLANT
    SELECT DISTINCT x.EtudiantId, x.AnneeScolaireId
    FROM   SCOLARITE AS x JOIN SCOLARITE AS y ON x.EtudiantId = y.EtudiantId
           JOIN PROMOTION AS z ON x.PromotionId = z.PromotionId
           JOIN PROMOTION AS t ON y.PromotionId = t.PromotionId
           JOIN ANNEE_SCOLAIRE AS v ON x.AnneeScolaireId = v.AnneeScolaireId
           JOIN ANNEE_SCOLAIRE AS w ON y.AnneeScolaireId = w.AnneeScolaireId
    WHERE  v.AnneeScolaireLibelle = @Annee
      AND  z.PromotionNo = @PromotionNo
      AND  z.PromotionNo = t.PromotionNo - 1
      AND  v.AnneeScolaireNo = w.AnneeScolaireNo + 1 ;

Crayon en main, dans le démontage du meccano, considérez que l’on a deux jeux de tables SCOLARITE, PROMOTION et ANNEE_SCOLAIRE : (x, z, v) d’une part, (y, t, w) d’autre part.

Maintenant, quel résultat obtient-on avec cette requête ?

Louis n’y figurera pas puisqu’il n’a jamais fait partie la promo 42.

Parmi les 4 étudiants restants, sont à considérer comme redoublants en 2012 ceux qui lors de l’année scolaire précédente ont fait partie de la promo 43.

Olaf n’a jamais fait partie de la promo 43, il ne fait donc pas partie des redoublants de l’année 2012.
Noémie et Quentin ne faisaient pas partie de la promo 43 en 2011. Il ne reste que Dorine qui a redoublé en 2012 et satisfait la condition :

z.PromotionNo = t.PromotionNo - 1 AND v.AnneeScolaireNo = w.AnneeScolaireNo + 1.

Je rappelle que l’on a deux références à la table PROMOTION, une 1re (z) en relation avec la 1re référence à SCOLARITE (x), une 2e (t) en relation avec la 2e référence à SCOLARITE (y).

Même principe pour l’année scolaire, on a une 1re référence à ANNEE_SCOLAIRE (v) en relation avec la 1re référence à SCOLARITE (x), une 2e référence (w) en relation avec la 2e référence à SCOLARITE (y).

La requête peut paraître complexe, mais sa complexité est la mesure du système à géométrie variable et fou d’immatriculation des étudiants...

N.B. Il faut renommer la table OPTION (par exemple en SPECIALITE), car « OPTION » est un mot réservé SQL.

**fsmrel** · 10/08/2012, 14h21

Bonjour spring,

Suite du bombardement...

A propos de la table ANNEE_SCOLAIRE

J’ai omis de déclarer {AnneeScolaireNo} comme clé candidate. Par ailleurs, si la valeur de AnneeScolaireNo est égale par exemple à 2008, alors la valeur de AnneeScolaireLibelle doit être égale à '2008-2009'. En conséquence, la colonne AnneeScolaireLibelle peut disparaître auquel cas lorsque dans une requête vous faites référence à AnneeScolaireLibelle, soit vous calculez sa valeur à partir de AnneeScolaireNo, soit vous conservez AnneeScolaireLibelle comme colonne dans la table, mais vous ajoutez un contrôle de structure, comme ci-dessous :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE ANNEE_SCOLAIRE
(
        AnneeScolaireId       INT          NOT NULL
      , AnneeScolaireNo       INT          NOT NULL
      , AnneeScolaireLibelle  VARCHAR(64)  NOT NULL
    , CONSTRAINT ANNEE_SCOLAIRE_PK PRIMARY KEY (AnneeScolaireId)
    , CONSTRAINT ANNEE_SCOLAIRE_AK1 UNIQUE (AnneeScolaireNo)
    , CONSTRAINT ANNEE_SCOLAIRE_CHK1 CHECK (AnneeScolaireNo >= 2008)
    , CONSTRAINT ANNEE_SCOLAIRE_CHK2 CHECK (SUBSTRING(AnneeScolaireLibelle, 1, 4) = AnneeScolaireNo AND
                                            SUBSTRING(AnneeScolaireLibelle, 6, 4) = SUBSTRING(AnneeScolaireLibelle, 1, 4) + 1)
) ;

Pour ma part, vu la grande masse d’inserts à effectuer, en gros un par an

:, je conserverais sans problème la colonne dans la table.

Pour y voir plus clair dans la détection des redoublants.

D’une façon générale, vu le diagramme :

Vous pouvez définir une table dérivée (vue) qui reprenne les éléments des cinq tables comme s’il n’y en avait qu’une seule, appelons-la ETUDIANT_SCOLARITE. Cette table dérivée s’articule autour de SCOLARITE et on récupère les données des tables qui gravitent autour :

Ce qui se traduit par le code SQL suivant :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE VIEW ETUDIANT_SCOLARITE (EtudiantId, EtudiantNom
                              , AnneeScolaireId, AnneeScolaireNo, AnneeScolaireLibelle
                              , PromotionId, PromotionNo, PromotionLibelle 
                              , NiveauId, Niveau, NiveauLibelle) AS 
SELECT x.EtudiantId, y.EtudiantNom
     , x.AnneeScolaireId, z.AnneeScolaireNo, z.AnneeScolaireLibelle
     , x.PromotionId, t.PromotionNo, t.PromotionLibelle
     , x.NiveauId, u.Niveau, u.NiveauLibelle
FROM   SCOLARITE AS x JOIN ETUDIANT AS y ON x.EtudiantId = y.EtudiantId
       JOIN ANNEE_SCOLAIRE AS z ON x.AnneeScolaireId = z.AnneeScolaireId
       JOIN PROMOTION AS t ON x.PromotionId = t.PromotionId
       JOIN NIVEAU AS u ON x.NiveauId = u.NiveauId ;

La prise en compte des redoublants peut alors prendre la forme :

Code SQL :

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

1
2
3
4
5
6
7
INSERT INTO @REDOUBLANT
    SELECT DISTINCT x.EtudiantId, x.AnneeScolaireId
    FROM   ETUDIANT_SCOLARITE AS x JOIN ETUDIANT_SCOLARITE AS y ON x.EtudiantId = y.EtudiantId
    WHERE  x.AnneeScolaireLibelle = @Annee  
      AND  x.PromotionNo = @PromotionNo
      AND  x.PromotionNo = y.PromotionNo - 1
      AND  x.AnneeScolaireNo = y.AnneeScolaireNo + 1 ;

A vous de voir si cette façon de procéder qui se veut "simplificatrice" vous convient.

**spring.time** · 24/08/2012, 11h48

excusez moi pour ce retard, mais j'essaye d'analyser tout ce que vous m'avez dit, et l'implémenter....
je ne sais comment vous remercier

Monsieur fsmrel

je vous met au courant de mon travail très prochainement

**fsmrel** · 25/08/2012, 00h24

Bonsoir spring,

Surtout restez zen, si vous avez un problème n'hésitez pas à en faire part. La rentrée est pour bientôt ?

**spring.time** · 06/09/2012, 15h05

excusez moi

je travaillais toujours sur l'application,
la rentrée est dans 2semaines

lol
------
svp, j'ai un un blocage, au niveau des tables:
niveau_discipline
niveau_discipline_commune
niveau_discipline_a_specialite

j'aimerais par exemple, afficher pour chaque niveau n et dans une année a, tous les profs avec les niveaux et les spécialités qu'ils enseignent.
j'ai pas de problème s'il s'agit des 2niveaux 3 et 4
mais pour les niveaux 5 et 6, il y'a des profs qui enseignent des disciplines, et ces disciplines sont liées à des spécialités, le prof il peut prendre une spécialité, comme il peut enseigner toutes les spécialités,

je me bloque, comment j'affiche les profs de 5e et 6e pour une année a, qui enseignent des disciplines, sachant que ces disciplines, peuvent être liées à une spécialité ou aux 4 spécialités?

est ce que vous m'avez compris?

merci

**fsmrel** · 07/09/2012, 04h15

Bonsoir spring,

Avant de traiter des spécialités, j’ai une remarque à faire. Vous dites que vous n’avez pas de problème en ce qui concerne les niveaux 2,3 et 4 : soit.

Toutefois, selon votre message #23, il est convenu jusqu’ici, que pour un niveau et une discipline donnés, il n’y a qu’un professeur. Mais cette contrainte n'est-elle pas à compléter ? En effet, si c’est Albert qui enseigne la physique pour le niveau 2 pour la période 2008-2009, ça ne peut pas être quelqu’un d’autre par la suite ?

Envoyé par spring.time

comment j'affiche les profs de 5e et 6e pour une année a, qui enseignent des disciplines, sachant que ces disciplines, peuvent être liées à une spécialité ou aux 4 spécialités?

Votre problème est-il lié à la modélisation qui ne serait pas complète (problème de la période que je viens d'évoquer) ? Est-il lié à la programmation en SQL ? Au besoin, expliquez-vous par l'exemple.

En tout cas, en l’état actuel du MLD, pour retrouver les noms des spécialités (options) enseignées par un professeur, on effectue une jointure des tables PROFESSEUR, NIVEAU_DISCIPLINE, NIVEAU_DISCIPLINE_A_OPTION, OPTION.

Rappel de l’organisation du MLD :

**spring.time** · 07/09/2012, 16h23

oui vous avez raison, les affectations des profs changent chaque année, donc il me faut une année dans niveau_discipline.

**spring.time** · 07/09/2012, 16h31

j'ai pensé à une interface qui permet de lier chaque discipline aux niveaux appropriés(j'utilise donc les 3tables niveau_discipline sans prof et niveau_discipline_commune et niveau_discipline_a_specialite) , puis une autre pour affecter chaque prof, pour une année donnée aux 4 niveaux(une nouvelle table appelée affectation(id_niveau, id_discipline, id_prof, id_annee).

qu'est ce que vous en pensez, c'est correct?

**fsmrel** · 07/09/2012, 19h52

Envoyé par spring.time

vous avez raison, les affectations des profs changent chaque année, donc il me faut une année dans niveau_discipline.

On est donc d’accord. La règle de gestion :

« Pour un niveau et une discipline donnés on ne peut avoir qu'un seul professeur. »

doit évoluer ainsi :

« Pour un niveau, une discipline et une année scolaire donnés on ne peut avoir qu'un seul professeur. »

Pour cette partie, le MLD devient donc le suivant, où la table NIVEAU_DISCIPLINE est dotée d’une colonne supplémentaire, à savoir AnneeScolaireId, participant à la clé de la table :

Passons aux spécialités. Selon le MLD ci-dessus, un professeur ne peut pas enseigner un nombre restreint de spécialités...

On va donc faire évoluer le modèle, pour rectifier cela. Le plus simple est de remplacer le lien PROFESSEUR - NIVEAU_DISCIPLINE par deux liens : le 1er entre PROFESSEUR et NIVEAU_DISCIPLINE_COMMUNE (comme dans le MLD précédent, on sait quel est l’ensemble des disciplines communes enseignées par un professeur, telle année, à tel niveau), le 2e entre PROFESSEUR et NIVEAU_DISCIPLINE_A_OPTION (on sait cette fois-ci quel est l’ensemble des spécialités enseignées par un professeur, telle année, à tel niveau, en relation avec telle discipline).

Arrive-t-on à ce dont vous avez besoin ?

**spring.time** · 07/09/2012, 21h23

je vous remercie pour votre patience,

il y'a autre chose que j'ai totalement oublié

pour les disciplines communes à toutes les spécialités, un prof peut ne pas enseigner les étudiants de toutes les spécialités, par exemple les étudiants de la 5e étudient tous (des 4spécialités) l'anglais, et ils peuvent avoir meme prof, comme ils peuvent avoir des profs différents, selon la disponibilité des profs.
vous m'avez compris?
comment connaitre les spécialités prises en charge par les profs qui enseignent des disciplines communes?

Intranet école supérieure

Schéma

Discussions similaires

Partager

Partager