Base de données Ecole

Invité · 09/04/2016, 18h14

Salut,
si je comprends bien je peux supprimer les FK(presenter_eleve_id, presenter_examen_id) dans la table "noter" provenant de "presenter" ?
J'ai fait ça mais j'ai toujours cette erreur dans phpmyadmin :
Erreur

Requête SQL :

-- -----------------------------------------------------
-- Table `stu_db`.`noter`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stu_db`.`noter` (
`num_dossier` INT NOT NULL,
`epreuve_id` INT NOT NULL,
`examen_id` INT NOT NULL,
`note` DECIMAL(5,2) NULL,
PRIMARY KEY (`num_dossier`, `epreuve_id`, `examen_id`),
CONSTRAINT `fk_presenter_has_epreuve_presenter1`
FOREIGN KEY (`num_dossier`)
REFERENCES `stu_db`.`presenter` (`num_dossier`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_presenter_has_epreuve_epreuve1`
FOREIGN KEY (`epreuve_id` , `examen_id`)
REFERENCES `stu_db`.`epreuve` (`epreuve_id` , `examen_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

MySQL a répondu: Documentation
#1215 - Cannot add foreign key constraint

**escartefigue** · 12/04/2016, 13h39

Bonjour,

Vos tables "présenter" et "noter" devraient avoir un script proche de ce qui suit :

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
CREATE TABLE IF NOT EXISTS `ia_db`.`presenter` 
  (
   `eleve_id`    INT  NOT NULL,
   `examen_id`   INT  NOT NULL,
   `date_pr`     DATE NOT NULL,
   `num_dossier` INT  NOT NULL,
   `moyenne`     DECIMAL(5,2) NULL,
   `mention`     CHAR(03)     NULL,
   PRIMARY KEY (`eleve_id`, `examen_id`, `date_pr`),
   CONSTRAINT `fk_presenter_eleve1`
        FOREIGN KEY (`eleve_id`)
        REFERENCES `ia_db`.`eleve` (`eleve_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
   CONSTRAINT `fk_presenter_examen1`
        FOREIGN KEY (`examen_id`)
        REFERENCES `ia_db`.`examen` (`examen_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
  )
ENGINE = InnoDB;

-------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stu_db`.`noter` 
  (
   `eleve_id`    INT  NOT NULL,
   `examen_id`   INT  NOT NULL,
   `epreuve_id`  INT  NOT NULL,
   `date_pr`     DATE NOT NUL,
   `note`        DECIMAL(5,2) NOT NULL,
   PRIMARY KEY (`eleve_id`, `examen_id`, `epreuve_id`, date_pr`),
   CONSTRAINT `fk_noter_presenter1`
        FOREIGN KEY (`eleve_id`, `examen_id`, `date_pr`)
        REFERENCES `ia_db`.`presenter` (`eleve_id`, `examen_id`, `date_pr`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
   CONSTRAINT `fk_presenter_epreuve1`
        FOREIGN KEY (`examen_id`, `epreuve_id`)
        REFERENCES `stu_db`.`epreuve` (`examen_id`, `epreuve_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
  )
 ENGINE = InnoDB;

Script corrigé manuellement à partir du votre, il peut donc y avoir quelques fautes de frappe, mais l'idée bien d'avoir, dans "présenter" le trio eleve+examen+date comme PK, et la même chose + epreuve dans "noter".
J'ai ajouté les actions update cascade et delete cascade : toute mise à jour dans "présenter" ou dans "épreuve" sera répercutée dans "noter", et toute suppression de "présenter" ou de "épreuve" provoquera la suppression des lignes correspondantes dans "noter"

Invité · 13/04/2016, 16h39

ca ne marche pas non plus :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`eleve_id` INT NOT NULL,
`examen_id` INT NOT NULL,
`epreuve_id` ' at line 4

Schematiquement ce MLD semble correct mais au moment de le creer dans une BD il ya des erreurs au niveau des tables precedemment citees

**escartefigue** · 13/04/2016, 17h42

J'avais précisé

Envoyé par escartefigue

Script corrigé manuellement à partir du votre, il peut donc y avoir quelques fautes de frappe

Et effectivement, je constate qu'il y en au moins une : "L" manquant dans "'NULL"
Cela dit, vous pouvez trouver ce genre d'erreur tout seul...

Envoyé par escartefigue

CREATE TABLE IF NOT EXISTS `stu_db`.`noter`
(
`eleve_id` INT NOT NULL,
`examen_id` INT NOT NULL,
`epreuve_id` INT NOT NULL,
`date_pr` DATE NOT NUL,
`note` DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`eleve_id`, `examen_id`, `epreuve_id`, date_pr`),
etc...[/CODE]

Envoyé par appendz21

ca ne marche pas non plus :
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`eleve_id` INT NOT NULL,
`examen_id` INT NOT NULL,
`epreuve_id` ' at line 4

Le bon reflexe en cas d'erreur c'est d'envoyer non seulement le message d'erreur, mais aussi le script qui va avec

Donc le create table complet pour la table en erreur + le message complet
Même combat si erreur SQL : requete complète + diagnostic complet

Invité · 13/04/2016, 17h54

Salut,
dsl j'avais pas vu le message "qu'il pourrait y avoir des erreurs...".

En tout cas voici le code complet :

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

CREATE SCHEMA IF NOT EXISTS `stu_db` DEFAULT CHARACTER SET utf8 ;
USE `stu_db` ;

-- -----------------------------------------------------
-- Table `stu_db`.`eleve`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stu_db`.`eleve` (
  `eleve_id` INT NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  `prenom` VARCHAR(45) NULL,
  `date_naissance` DATE NULL,
  `lieu_naissance` VARCHAR(45) NULL,
  PRIMARY KEY (`eleve_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stu_db`.`examen`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stu_db`.`examen` (
  `examen_id` INT NOT NULL AUTO_INCREMENT,
  `libelle` VARCHAR(45) NULL,
  PRIMARY KEY (`examen_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stu_db`.`presenter`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stu_db`.`presenter` (
   `eleve_id`    INT  NOT NULL,
   `examen_id`   INT  NOT NULL,
   `date_pr`     DATE NOT NULL,
   `num_dossier` INT  NOT NULL,
   `moyenne`     DECIMAL(5,2) NULL,
   `mention`     CHAR(03)     NULL,
   PRIMARY KEY (`eleve_id`, `examen_id`, `date_pr`),
   CONSTRAINT `fk_presenter_eleve1`
        FOREIGN KEY (`eleve_id`)
        REFERENCES `stu_db`.`eleve` (`eleve_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
   CONSTRAINT `fk_presenter_examen1`
        FOREIGN KEY (`examen_id`)
        REFERENCES `stu_db`.`examen` (`examen_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_eleve_has_examen_examen1_idx` ON `stu_db`.`presenter` (`examen_id` ASC);

CREATE INDEX `fk_eleve_has_examen_eleve_idx` ON `stu_db`.`presenter` (`eleve_id` ASC);


-- -----------------------------------------------------
-- Table `stu_db`.`epreuve`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stu_db`.`epreuve` (
  `epreuve_id` INT NOT NULL AUTO_INCREMENT,
  `examen_id` INT NOT NULL,
  `matiere` VARCHAR(45) NULL,
  `coefficient` DECIMAL(5,2) NULL,
  PRIMARY KEY (`epreuve_id`, `examen_id`),
  CONSTRAINT `fk_epreuve_examen1`
    FOREIGN KEY (`examen_id`)
    REFERENCES `stu_db`.`examen` (`examen_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_epreuve_examen1_idx` ON `stu_db`.`epreuve` (`examen_id` ASC);

-- -----------------------------------------------------
-- Table `stu_db`.`noter`
-- -----------------------------------------------------
   `eleve_id`    INT  NOT NULL,
   `examen_id`   INT  NOT NULL,
   `epreuve_id`  INT  NOT NULL,
   `date_pr`     DATE NOT NULL,
   `note`        DECIMAL(5,2) NOT NULL,
   PRIMARY KEY (`eleve_id`, `examen_id`, `epreuve_id`, date_pr`),
   CONSTRAINT `fk_noter_presenter1`
        FOREIGN KEY (`eleve_id`, `examen_id`, `date_pr`)
        REFERENCES `stu_db`.`presenter` (`eleve_id`, `examen_id`, `date_pr`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
   CONSTRAINT `fk_presenter_epreuve1`
        FOREIGN KEY (`examen_id`, `epreuve_id`)
        REFERENCES `stu_db`.`epreuve` (`examen_id`, `epreuve_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

**fsmrel** · 13/04/2016, 18h05

Bonjour,

Permettez-moi d'intervenir, car j'avais préparé une réponse, laquelle entre en collision avec les derniers échanges, mais j'en fais part en l'état...

Appendz21, vous n’aviez manifestement pas synchronisé vos structures de table avec celles d’escartefigue.
Mais il est vrai que la date à laquelle un élève se présente à un examen est en fait une propriété de l’examen et il est préférable de l’évacuer de la table PRESENTER et l’intégrer à la table EXAMEN (@escartefigue : et empêcher ainsi un viol de 2NF). En revanche, vous pouvez définir un attribut presenter_id (artificiel, invariant, non significatif et tout ça) pour dédoublonner les tentatives successives d’un élève à un même examen.

Exemple :

D’où le script SQL (valide ) :


USE ia_db ;

SET default_storage_engine=InnoDB ;

DROP TABLE IF EXISTS NOTER ;
DROP TABLE IF EXISTS EPREUVE ;
DROP TABLE IF EXISTS PRESENTER ;
DROP TABLE IF EXISTS EXAMEN ;
DROP TABLE IF EXISTS ELEVE ;

CREATE TABLE ELEVE 
(
  eleve_id INT NOT NULL,
  nom VARCHAR(32) NOT NULL,
  prenom VARCHAR(32) NOT NULL,
  date_naissance DATE NOT NULL,
  lieu_naissance VARCHAR(32) NOT NULL,
  ecole_origine VARCHAR(32) NOT NULL,
  CONSTRAINT ELEVE_PK PRIMARY KEY (eleve_id)
) ;

CREATE TABLE IF NOT EXISTS EXAMEN 
(
  examen_id INT NOT NULL,
  libelle VARCHAR(32) NOT NULL,
  date_examen DATE NOT NULL,  
  CONSTRAINT EXAMEN_PK PRIMARY KEY (examen_id)
) ;

CREATE TABLE IF NOT EXISTS PRESENTER 
(
  eleve_id INT NOT NULL,
  examen_id INT NOT NULL,
  presenter_id INT NOT NULL,
  num_dossier INT NOT NULL,
  moyenne DECIMAL(5,2) NOT NULL,
  mention varchar(16) NOT NULL,
  CONSTRAINT PRESENTER_PK PRIMARY KEY (eleve_id, examen_id, presenter_id),
  CONSTRAINT PRESENTER_ELEVE_FK
    FOREIGN KEY (eleve_id)
    REFERENCES ELEVE (eleve_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT PRESENTER_EXAMEN_FK
    FOREIGN KEY (examen_id)
    REFERENCES EXAMEN (examen_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ;

CREATE TABLE IF NOT EXISTS EPREUVE 
(
  epreuve_id INT NOT NULL,
  examen_id INT NOT NULL,
  matiere VARCHAR(32) NOT NULL,
  coefficient DECIMAL(5,2) NOT NULL,
  CONSTRAINT EPREUVE_PK PRIMARY KEY (examen_id, epreuve_id),
  CONSTRAINT EPREUVE_EXAMEN_FK
    FOREIGN KEY (examen_id)
    REFERENCES EXAMEN (examen_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ;

CREATE TABLE IF NOT EXISTS NOTER 
(
  eleve_id INT NOT NULL,
  examen_id INT NOT NULL,
  presenter_id INT NOT NULL,
  epreuve_id INT NOT NULL,
  note DECIMAL(5,2) NOT NULL,
  CONSTRAINT NOTER_PK PRIMARY KEY (eleve_id, examen_id, presenter_id, epreuve_id),
  CONSTRAINT NOTER_EPREUVE_FK
    FOREIGN KEY (examen_id, epreuve_id)
    REFERENCES EPREUVE (examen_id, epreuve_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT NOTER_PRESENTER_FK
    FOREIGN KEY (eleve_id, examen_id, presenter_id)
    REFERENCES PRESENTER (eleve_id, examen_id, presenter_id) 
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ;

Invité · 13/04/2016, 18h29

J'ai essaye et ca marche sans erreur merci. Mais j'ai une question concernant la table "NOTER" : laquelle de ces 2 tables "PRESENTER" ou "EPREUVE", le FK "examen_id" provient ?

**escartefigue** · 13/04/2016, 19h30

Envoyé par fsmrel

Permettez-moi d'intervenir, car j'avais préparé une réponse, laquelle entre en collision avec les derniers échanges, mais j'en fais part en l'état...

Vous êtes toujours le bienvenu

Envoyé par fsmrel

Appendz21, vous n’aviez manifestement pas synchronisé vos structures de table avec celles d’escartefigue.
Mais il est vrai que la date à laquelle un élève se présente à un examen est en fait une propriété de l’examen et il est préférable de l’évacuer de la table PRESENTER et l’intégrer à la table EXAMEN

C'est une option que je n'avais pas retenue, de façon à permettre plusieurs présentations à un même examen, avec un même n° de dossier (par exemple l'oral de rattrapage du bac)
A appendz21 de trancher en fonction du besoin réel

**fsmrel** · 13/04/2016, 19h33

Bonsoir,

Envoyé par Appendz21

j'ai une question concernant la table "NOTER" : laquelle de ces 2 tables "PRESENTER" ou "EPREUVE", le FK "examen_id" provient ?

Les deux, mon général !

Considérez la 1re clé étrangère définie pour la table NOTER :


  CONSTRAINT NOTER_EPREUVE_FK
    FOREIGN KEY (examen_id, epreuve_id)
    REFERENCES EPREUVE (examen_id, epreuve_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

Ainsi que la 2e clé étrangère définie pour cette table :


  CONSTRAINT NOTER_PRESENTER_FK
    FOREIGN KEY (eleve_id, examen_id, presenter_id)
    REFERENCES PRESENTER (eleve_id, examen_id, presenter_id) 
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

La 1re clé étrangère contraint chaque valeur de l’attribut examen_id de la table NOTER à être une valeur de l’attribut examen_id de la table EPREUVE.

La 2e clé étrangère contraint chaque valeur de l’attribut examen_id de la table NOTER à être une valeur de l’attribut examen_id de la table PRESENTER.

Ainsi, chaque valeur de l’attribut examen_id de la table NOTER doit être simultanément une valeur de l’attribut examen_id de chacune des deux autres tables : en conséquence, un élève ne peut être noté que dans une épreuve d’un examen auquel il s’est présenté. Essayez de prendre en défaut cette assertion ! Il s’agit en tout cas de concrétiser l’observation que j’avais faite ici.

Invité · 13/04/2016, 21h13

D'accord je vois maintenant. Merci aussi pour le lien.

Base de données Ecole [Entité-Association]

Schéma

Discussions similaires

Partager

Partager