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

[beegees]

Bonjour fsmrel,

Je vous prie de m'excuser pour mon silence, mais la semaine j'étais occupé sur d'autres projets (boulot...).

J'ai relu attentivement votre message et j'ai quelques questions :

PRIMARY KEY(Niveau_Id, Matiere_Id),
FOREIGN KEY (Niveau_Id)

Vous notez ci-dessus que niveau_id est en clé primaire et en clé étrangère, est-ce possible de mettre deux sortes de clé sur la même propriété ?

Ci-dessous, je ne comprends pas :

REFERENCES Niveau(Niveau_Id)
ON DELETE CASCADE
ON UPDATE CASCADE

Désolé, mais je ne comprends pas cette remarque non plus :

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

Très important : souvent, un élève à cours en BA2 mais prend aussi un ou plusieurs cours en BA3 (pour s'avancer) est-ce qu'il y'a des changements à faire dans votre structure ?

Je ne comprends pas ceci, mais c'est peut être du au fait que je travaille avec Access ?

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

idem ci-dessous :

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

Est-ce que ces actions peuvent être mies en oeuvre avec Access ?

Je vous remercie encore pour votre aide et je vous souhaîte un bon Dimanche.

beegees

[fsmrel]

Bonjour Beegees,

PRIMARY KEY (Niveau_Id, Matiere_Id),
FOREIGN KEY (Niveau_Id)

Vous notez ci-dessus que niveau_id est en clé primaire et en clé étrangère, est-ce possible de mettre deux sortes de clé sur la même propriété ?

Bien entendu ! Cette question revient sans cesse, depuis bien longtemps : la preuve. En fait, vous avez le scrupule de qualifier le terme "KEY" simultanément par "PRIMARY" et "FOREIGN", en présupposant qu’il y a comme une sorte de collision entre ces adjectifs. En réalité, le terme "FOREIGN KEY" est trompeur et il faut le comprendre comme l’ensemble F des attributs d’une table T1 astreints à prendre les valeurs des attributs constituant la clé primaire P d’une table T2. Cette contrainte porte le nom d’intégrité référentielle.
Ceci n’interdit en rien que les constituants de F participent par ailleurs à la clé primaire de T1, l’intégrité référentielle s’en moque.
Ainsi pour reprendre votre cas, quand j’écris

CREATE TABLE Matiere
Niveau_Id INTEGER NOT NULL,
Matiere_Id INTEGER NOT NULL,
PRIMARY KEY (Niveau_Id, Matiere_Id),
FOREIGN KEY (Niveau_Id)
REFERENCES Niveau (Niveau_Id)

cela s’interprète ainsi : La table Matiere, dont la clé primaire est composée des attributs Niveau_Id et Matiere_Id, est astreinte à ce que les valeurs de l’attribut Niveau_Id soient déjà présentes dans la table Niveau (attribut Niveau_Id). En SQL, la contrainte est définie par la clause REFERENCES Niveau (Niveau_Id).

A noter :

1) Concernant le Modèle relationnel (dont s’inspire SQL), le terme PRIMARY KEY n’existe plus qu’à titre historique, il est remplacé par le terme CANDIDATE KEY. En effet, plusieurs ensembles d’attributs d’une table peuvent prétendre au rôle de PRIMARY KEY (pensez au tableau périodique des éléments, avec comme prétendants, le symbole de l’élément, son nom, sa masse atomique...) Jadis, une FOREIGN KEY était astreinte à faire référence exclusivement à une PRIMARY KEY. Il a été démontré que cette contrainte ne se justifiait pas : désormais, cette FOREIGN KEY est astreinte à seulement faire référence à l’une des CANDIDATE KEYs de la table référencée. SQL s’est mis au diapason, puisque dans la clause REFERENCES, on fournit le nom des colonnes (en l’occurrence Niveau_Id) de la table de référence (Niveau).

2) Lorsque dans le MCD MERISE, vous définissez une relation M,N (plusieurs à plusieurs) telle que Eleve_Matiere, entre les entités Matiere et Eleve_Niveau, si les identifiants respectifs de ces entités sont Matiere_Id et {Eleve_Id, Niveau_Id}, alors par définition l’identifiant de la relation est le triplet {Matiere_Id, Eleve_Id, Niveau_Id}, bien que ces attributs ne figurent pas explicitement en tant que propriétés de la relation. Par dérivation du MCD, les identifiants font l’objet de clés primaires, et dans le cas des relations, elles font en plus l’objet de clés étrangères, Matiere_Id d’une part et {Eleve_Id, Niveau_Id} d’autre part (table Eleve_Matiere).

3) Les tables T1 et T2 ne sont pas nécessairement distinctes (cas par exemple, des nomenclatures que l’on souhaite représenter sous forme de tables s’auto-référençant).

Ci-dessous, je ne comprends pas :

Citation:
REFERENCES Niveau(Niveau_Id)
ON DELETE CASCADE
ON UPDATE CASCADE

Concernant la clause REFERENCES, reportez-vous à ce qui précède.
Pour la suite :
Il y a là deux dimensions à appréhender : une dimension statique et une autre qui est dynamique. La dimension statique concerne la contrainte référentielle que l’ont vient donc d’évoquer. Il existe aussi une dimension dynamique, en relation avec le métabolisme des tables, qui contrairement aux relations mathématiques ne sont pas figées dans le temps (et dans l’espace). Je complète d’abord votre citation :

CREATE TABLE Matiere
...
FOREIGN KEY (Niveau_Id)
REFERENCES Niveau (Niveau_Id)
ON DELETE CASCADE
ON UPDATE CASCADE
...
Supposons que, dans la table Niveau, vous tentiez de supprimer le niveau N1 auquel se raccroche la matière {N1, M1}. Si l’on a codé ON DELETE CASCADE, les lignes de la table Matiere —pour lesquelles l’attribut Niveau_Id prend la valeur N1— seront supprimées, sous réserve que pour les tables faisant référence à la leur tour à la table Matiere, on retrouve la clause ON DELETE CASCADE. C’est-à-dire que l’on peut supprimer dans NIVEAU la ligne ayant pour valeur de clé N1 si, pour l’ensemble E des tables dépendant directement ou indirectement de la table Matiere et contenant cette valeur N1 au sein d’un attribut d’une FOREIGN KEY, la clause ON DELETE CASCADE est présente (feu vert unanime).
En revanche, si l’on rencontre chemin faisant, cette valeur N1 au sein d’un attribut d’une FOREIGN KEY associée à une clause ON DELETE RESTRICT (et/ou NO ACTION, selon le SGBD), alors l’opération de suppression est complètement annulée (feu rouge).

Ce qui précède concerne la suppression, mais s’applique aussi à la modification, mutatis mutandis (clause ON UPDATE).

En SQL (et selon les SGBD), il existe des variantes : SET NULL, SET DEFAULT dont je ne parlerai pas ici.

Est-ce que ces actions peuvent être mises en oeuvre avec Access ?

Certes !
1) Sous forme textuelle, comme ci-dessus. Créer des requêtes en mode SQL, contenant les instructions Create Table (voir l’aide en ligne en relation avec cette instruction).
2) Ou bien, en passant par l’onglet Relations pour afficher les tables et tirer les liens qui les unissent, puis en cliquant sur les liens, et en cochant la case "Appliquer l’intégrité référentielle".

Très important : souvent, un élève à cours en BA2 mais prend aussi un ou plusieurs cours en BA3 (pour s'avancer) est-ce qu'il y'a des changements à faire dans votre structure ?

Cela dépend. La table Eleve_Niveau est structurée de telle sorte qu’un élève puisse participer à plus d’un niveau.
Si l’année scolaire en cours est a1 et qu’on anticipe sur a2 = a1+1, on peut avoir en table (clé primaire soulignée) :

Eleve_Niveau (Eleve_Id, Niveau_Id, Annee_Scolaire)
_______________e1_______n1_________a1
_______________e1_______n2_________a2

et l’année suivante on trouve une situation correcte.
Si vous préférez garder la trace de ce qui s’est passé durant a1 et a2 pour l’élève e1, pour n1 d’une part et n2 d’autre part, cela entraîne une modification de structure, consistant par exemple à intégrer l’attribut Annee_Scolaire dans la clé primaire de la table Eleve_Niveau (et par vois de conséquence dans la table Eleve_Matiere). Je vous laisse le soin de le vérifier, à titre d’exercice. Je vous engage à le faire avec Access, en valorisant les tables, une fois que les contraintes seront en place (clés primaires, clés étrangères).

Enjoy!