Discussion :

[beegees]

Bonjour tout le monde,

J'ai une base de données Access qui reprend les étudiants d'un Institut.

Pour réussir leur études les étudiants doivent réussir 5 années.

Les années sont donc :

BA1 (Bacc1)
BA2 (Bacc2)
BA3 (Bacc3)
MA1 (Master 1)
MA2 (Master 2)

Chaque étudiant est identifié (administrativement et en plus de leur nom et prénom) par un numéro de matricule UNIQUE.

J'aimerais créer un formulaire qui me permettrait de connaître combien d'année un étudiant à effectuer, quelles ont été ses grade (satis, distinction...) et surtout de pouvoir retrouver leur point dans les différentes matières des différentes années.

Je propose ceci, pourriez-vous me dire si je susi dans le bon chemin :

Une table Eleve, une table année et une table matière.

Les trois sont reliées par le numéro de matricule.

Le principal but serait de savoir combien l'étudiant 123456 à eu dans la matière MEDIJ301 en BA3.

Est-ce que mon schéma est correcte ?

J'en doute mais enfin ne sait-on jamais.

Merci pour tout.

beegees

[fsmrel]

Bonjour Beegees,
Ça y est, vous vous lancez à l’eau. Votre schéma appelle quelques commentaires.
Ainsi, l’entité-type Année comporterait 5 propriétés : BA1, BA2, BA3, MA1, MA2. Selon votre schéma (dont vous ne fournissez pas le mode d’emploi de lecture), il semblerait qu’un étudiant détermine (à un instant t) une année donnée (relation « est en »). Je suppose que la propriété en gras BA1 identifie l’entité-type Année. Autrement dit, l’élève est —ou a nécessairement été — en année BA1. Je suppose par ailleurs que vous avez un mécanisme (un booléen, un nombre de points, etc.) pour repérer l’année dans laquelle se trouve actuellement l’élève (lequel n’a manifestement pas le droit de redoubler). En outre, le jour où l’école est astreinte à revoir le système des années (ajout d’une année par exemple), se pose un problème. Imaginez que la table dérivée comporte non pas quelques centaines ou milliers de lignes, mais quelques centaines de millions : vos rapports avec la Production informatique risqueraient d’être plus que tendus...
Même si formellement ça n’est pas le cas, vous pêchez par l’esprit en ne respectant pas la première forme normale. Vous devriez avoir une seule propriété Année, pouvant prendre les valeurs "BA1", "BA2", "BA3", "MA1", "MA2".
Concernant l’entité-type Matière, on retrouve le même problème relatif à la première forme normale. Qui plus est, puisque les propriétés Matière 1, Matière 2, ..., Matière n, valent pour tous les élèves, chaque matière étudiée à l’école doit faire l’objet d’une propriété. Là encore, Il ne devrait y avoir qu’une seule propriété Matière pouvant prendre les valeurs "Matière 1", "Matière 2", ..., "Matière n".
Une proposition de modélisation (il peut y en avoir bien d’autres, au moins une par participant de ce forum !) :
J’utilise ici la représentation tabulaire. J’ai remplacé Année par Niveau, car Année est plus utilisé pour définir ou manipuler les dates.
Par rétroconception, vous pourrez produire le MCD correspondant (à titre d’exercice !)

CREATE TABLE Matiere (
Matiere_Id INTEGER NOT NULL,
Matirere_Nom CHAR(64) NOT NULL,
PRIMARY KEY(Matiere_Id)
);
CREATE TABLE Niveau (
Niveau_Id INTEGER NOT NULL,
Niveau_Nom CHAR(64) NOT NULL,
PRIMARY KEY(Niveau_Id)
);
CREATE TABLE Eleve (
Eleve_Id INTEGER NOT NULL,
Nom CHAR(64) NOT NULL,
Prenom CHAR(64) NOT NULL,
PRIMARY KEY(Eleve_Id)
);
/* On suppose qu’une matière donnée peut être enseignée aussi bien en BA1 qu’en BA2, etc. Dans cette optique, on met en œuvre un référentiel des couples {Matière, Niveau} préexistant, chacun ayant été défini à une date donnée (Annee_Appli) */
CREATE TABLE Matiere_Niveau (
Matiere_Id INTEGER NOT NULL,
Niveau_Id INTEGER NOT NULL,
Annee_Appli DATE NOT NULL,
PRIMARY KEY(Matiere_Id, Niveau_Id),
FOREIGN KEY Rel_01(Matiere_Id)
REFERENCES Matiere(Matiere_Id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY Rel_02(Niveau_Id)
REFERENCES Niveau(Niveau_Id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
/* Année représente ici l'année calendaire à laquelle l'élève a étudié telle matière dans tel niveau. On suppose qu’un élève peut redoubler. */
CREATE TABLE Eleve_Histo (
Eleve_Id INTEGER NOT NULL,
Matiere_Id INTEGER NOT NULL,
Niveau_Id INTEGER NOT NULL,
Annee INTEGER NOT NULL,
Points INTEGER NOT NULL,
PRIMARY KEY(Eleve_Id, Matiere_Id, Niveau_Id, Annee),
FOREIGN KEY Rel_03(Eleve_Id)
REFERENCES Eleve(Eleve_Id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY Rel_04(Matiere_Id, Niveau_Id)
REFERENCES Matiere_Niveau(Matiere_Id, Niveau_Id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);

A cela, il serait bon de rajouter des contraintes du genre (exprimées ici avec les instructions du standard SQL :1999) et que je vous laisse reformuler en français, voire vérifier et corriger :
Create Assertion RG001 Check
(Not Exists (Select ‘x’ From Eleve_Histo a, Matiere_Niveau b
Where a. Matiere_Id = b. Matiere_Id And a.Niveau_Id = b. Niveau_Id
And a. Annee < b. Annee_Appli)
) ;

Je passe le relais au relecteur suivant...

[Nanci]

C'est sympa de donner une solution aussi détaillée en SQL avec des cascade et assertion, mais il serait peut être nécessaire au préalable de dire pourquoi cette solution, que veut-on exprimer. En résumer, faire un MCD avant le MPD. C'est ce qu'a préconisé Merise (titre de ce forum).

Tout à fait d'accord avec les 3 entités de base (et non des tables)
Etudiant
Année-Niveau (pour ne pas la confondre avec Année universitaire)
Matière

Tout le reste est question de relations... Et il n'est pas sûr que Beegees retrouve facilement toutes les subtilités exprimées dans le script SQL..

Q1/ Il n'est pas certain que la même matière soit au programme de plusieurs niveaux
Q2/ Peut-on avoir des notes dans la même matière dans des années différentes

Essayons de formaliser correctement le problèm, ce que l'on veut, avant de proposer une solution SQL

[beegees]

Bonjour fsmrel, bonjour Nanci, bonjour tout le monde,

Merci à vous deux pour vos réponses et désolé pour le retard de la mienne.

Je commence avec fsmrel :

Selon votre schéma (dont vous ne fournissez pas le mode d’emploi de lecture)

Désolé, je débute en modélisation, je ne savais pas qu'il fallait fournir un mode d'emploi de lecture, mais maintenant que vous le dites, je trouve cela tout à fait pertinent.

Je suppose que la propriété en gras BA1 identifie l’entité-type Année. Autrement dit, l’élève est —ou a nécessairement été — en année BA1.

Non, j'aurais du retirer le gras du BA1, car ce n'est pas une référence.

Je suppose par ailleurs que vous avez un mécanisme (un booléen, un nombre de points, etc.) pour repérer l’année dans laquelle se trouve actuellement l’élève (lequel n’a manifestement pas le droit de redoubler).

Je n'ai aucun mécanisme pour repérer où est l'étudiant, par contre, je pensais utiliser le numéro de matricule, déjà repris dans la table élève, pensez-vous

1) que c'est permis de faire cela avec MERISE
2) que c'est une bonne solution ? Si non, en avez vous une autre ?

En outre, le jour où l’école est astreinte à revoir le système des années (ajout d’une année par exemple), se pose un problème. Imaginez que la table dérivée comporte non pas quelques centaines ou milliers de lignes, mais quelques centaines de millions : vos rapports avec la Production informatique risqueraient d’être plus que tendus...

C'est vrai qu'il y'a aussi les DEC et surtout les cours isolés que j'aurai rajouté après.

Un cours isolé est un cours qu'un étudiant doubleur peut prendre dans l'année suivante afin d'avancer dans son cursus, si il le réussi, il ne devra plus représenter ce cours l'année d'après.

Vous devriez avoir une seule propriété Année, pouvant prendre les valeurs "BA1", "BA2", "BA3", "MA1", "MA2".

donc si je comprends bien une table Année avec comme propriété BA1,... MA2.

Mais que sont exactement des propriétés, c'est le nom des champs ?

Et "est en" c'est une relation alors ?

Pour ce qui est des matières, je vous remercie pour ce que vous m'expliquez mais je dois relire à nouveau votre message pour être bien sur de bien comprendre.

Merci beaucoup

Pour Nanci

Tout à fait d'accord avec les 3 entités de base (et non des tables)

On appelle cela des "Entités" alors et non des tables ?

Q1/ Il n'est pas certain que la même matière soit au programme de plusieurs niveaux

Chaque matière est appartient à un seul niveau. On retrouve donc MEDI-J-301 qu'en BA3 et nulle part d'autre.

Q2/ Peut-on avoir des notes dans la même matière dans des années différentes

Normalement non, par contre on a des notes pour la théorie et les travaux pratiques.

J'espère que vous pourrez encore m'aider.

Merci beaucoup à vous deux.

beegees

[fsmrel]

Bonjour Beegees,

La suite du feuilleton...

Je n'ai aucun mécanisme pour repérer où est l'étudiant, par contre, je pensais utiliser le numéro de matricule, déjà repris dans la table élève, pensez-vous
1) que c'est permis de faire cela avec MERISE
2) que c'est une bonne solution ? Si non, en avez vous une autre ?

Le numéro de matricule de l’élève participe au repérage, puisqu’il est impliqué, mais son niveau (alias Année) y participe aussi, ainsi que la Matière pour noter l’élève.

Si je comprends bien une table Année avec comme propriété BA1,... MA2.
Mais que sont exactement des propriétés, c'est le nom des champs ?

Dans un de vos précédents messages ("Pourriez vous m'expliquer les termes MCD, MCT"), je vous ai suggéré la lecture de certains ouvrages ou documents libres d’accès et éclairant tout cela. Bref, une propriété correspond à ce que vous appelez champ (terme que l’on utilise plutôt pour des fichiers) ou attribut (voir informellement colonne) au niveau relationnel. Une propriété telle que Année (ou Niveau_Nom dans le schéma de MLD ci-dessous) peut prendre les valeurs "BA1", "BA2", "BA3", "MA1", "MA2", etc. au fil des modifications dans le temps), de même que la propriété Nom peut prendre les valeurs "Beegees", "Martin", etc.

Pour essayer de vous éclaire un peu plus, je joins en fin de document un MLD obtenu par rétroconception (SQL Server), dans lequel il est tenu compte de votre observation : "Chaque matière est appartient à un seul niveau." (En espérant que le fichier JPG vous soit accessible...)
Les instructions CREATE TABLE ont été régénérées en conséquence.
Je représente aussi sous forme tabulaire le contenu des tables du niveau logique (éléments identifiants soulignés) :

Niveau ([Niveau_Id, Niveau_Nom)
___________1_______BA1
___________2_______BA2
___________... ______...

Matiere (Niveau_Id, Matiere_Id, Matiere_Nom, Annee_Appli)
___________1________1________Mat-x_______1984
___________1________2________Mat-y_______1985
___________2________1________Mat-z_______1987
___________2________4________Mat-u_______1980
___________...________...________..._________...

Eleve (Eleve_Id,_____Nom,______ Prénom)
________1_________Beegees____Bernard
________2_________Martin______Louis
________...__________...________...

Eleve_Niveau (Eleve_Id, Niveau_Id, Annee_Solaire)
_______________1_______1_________2000
_______________1_______2_________2001
_______________2_______1_________2004
_______________...______..._________...

Eleve_Matiere (Eleve_Id, Niveau_Id, Matiere_Id, Points)
________________1_______1_________1_______100
________________1_______1_________2________80
________________1_______2_________4_______120
_______________... ______...________..._______...

Les instructions de création des tables, adaptées :

CREATE TABLE Niveau (
Niveau_Id INTEGER NOT NULL,
Niveau_Nom CHAR(64) NOT NULL,
PRIMARY KEY(Niveau_Id)
);

CREATE TABLE Eleve (
Eleve_Id INTEGER NOT NULL,
Nom CHAR(64) NOT NULL,
Prenom CHAR(64) NOT NULL,
PRIMARY KEY(Eleve_Id)
);

CREATE TABLE Matiere (
Niveau_Id INTEGER NOT NULL,
Matiere_Id INTEGER NOT NULL,
Matiere_Nom CHAR(64) NOT NULL,
Annee_Appli INTEGER NOT NULL,
PRIMARY KEY(Niveau_Id, Matiere_Id),
FOREIGN KEY (Niveau_Id)
REFERENCES Niveau(Niveau_Id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

/* On suppose qu'un élève ne redouble pas (sinon, rendre l'année scolaire identifiante) */

CREATE TABLE Eleve_Niveau (
Eleve_Id INTEGER NOT NULL,
Niveau_Id INTEGER NOT NULL,
Annee_Scolaire INTEGER NOT NULL,
PRIMARY KEY(Eleve_Id, Niveau_Id),
FOREIGN KEY (Niveau_Id)
REFERENCES Niveau(Niveau_Id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (Eleve_Id)
REFERENCES Eleve(Eleve_Id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

CREATE TABLE Eleve_Matiere (
Eleve_Id INTEGER NOT NULL,
Niveau_Id INTEGER NOT NULL,
Matiere_Id INTEGER NOT NULL,
Points INTEGER NOT NULL,
PRIMARY KEY(Eleve_Id, Niveau_Id, Matiere_Id),
FOREIGN KEY (Niveau_Id, Matiere_Id)
REFERENCES Matiere(Niveau_Id, Matiere_Id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (Eleve_Id, Niveau_Id)
REFERENCES Eleve_Niveau(Eleve_Id, Niveau_Id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

[beegees]

Bonjour fsmrel,

Je voudrais vous remercier pour le travail exceptionnel que vous êtes en train de créer pour moi ainsi que pour le temps que vous y consacrez.

Je vais imprimer et ensuite analyser votre réponse, je reviendrai ensuite avec mes commentaires et mes questions.

Encore une fois, merci.

beegees