Advertisement
Guest User

Gestion scolarité

a guest
Jul 25th, 2016
99
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 4.62 KB | None | 0 0
  1. #------------------------------------------------------------
  2. #        Script MySQL.
  3. #------------------------------------------------------------
  4.  
  5.  
  6. #------------------------------------------------------------
  7. # Table: eleves
  8. #------------------------------------------------------------
  9.  
  10. CREATE TABLE eleves(
  11.         elevesid  Int NOT NULL ,
  12.         nom       Varchar (2550) NOT NULL ,
  13.         prenom    Varchar (50) NOT NULL ,
  14.         adresse   Varchar (250) NOT NULL ,
  15.         matricule Varchar (25) NOT NULL ,
  16.         PRIMARY KEY (elevesid )
  17. )ENGINE=InnoDB;
  18.  
  19.  
  20. #------------------------------------------------------------
  21. # Table: annee_scolaire
  22. #------------------------------------------------------------
  23.  
  24. CREATE TABLE annee_scolaire(
  25.         anneeid     Int NOT NULL ,
  26.         annee       Int NOT NULL ,
  27.         commentaire Varchar (200) ,
  28.         PRIMARY KEY (anneeid )
  29. )ENGINE=InnoDB;
  30.  
  31.  
  32. #------------------------------------------------------------
  33. # Table: niveau
  34. #------------------------------------------------------------
  35.  
  36. CREATE TABLE niveau(
  37.         niveauid           Int NOT NULL ,
  38.         nomNiveau          Varchar (30) NOT NULL ,
  39.         commentaire_niveau Varchar (100) NOT NULL ,
  40.         PRIMARY KEY (niveauid )
  41. )ENGINE=InnoDB;
  42.  
  43.  
  44. #------------------------------------------------------------
  45. # Table: statut
  46. #------------------------------------------------------------
  47.  
  48. CREATE TABLE statut(
  49.         statutid   Int NOT NULL ,
  50.         status_nom Varchar (30) NOT NULL ,
  51.         PRIMARY KEY (statutid )
  52. )ENGINE=InnoDB;
  53.  
  54.  
  55. #------------------------------------------------------------
  56. # Table: payement_scolarite
  57. #------------------------------------------------------------
  58.  
  59. CREATE TABLE payement_scolarite(
  60.         payement_scolarite_id Int NOT NULL ,
  61.         montant_verse         Decimal (25,2) NOT NULL ,
  62.         reste_a_verser        Decimal (25,2) NOT NULL ,
  63.         elevesid              Int NOT NULL ,
  64.         frais_scolarite_id    Int NOT NULL ,
  65.         PRIMARY KEY (payement_scolarite_id )
  66. )ENGINE=InnoDB;
  67.  
  68.  
  69. #------------------------------------------------------------
  70. # Table: classe
  71. #------------------------------------------------------------
  72.  
  73. CREATE TABLE classe(
  74.         classId   Int NOT NULL ,
  75.         classname Varchar (25) NOT NULL ,
  76.         PRIMARY KEY (classId )
  77. )ENGINE=InnoDB;
  78.  
  79.  
  80. #------------------------------------------------------------
  81. # Table: frais_scolarite
  82. #------------------------------------------------------------
  83.  
  84. CREATE TABLE frais_scolarite(
  85.         frais_scolarite_id Int NOT NULL ,
  86.         anneeid            Int NOT NULL ,
  87.         niveauid           Int NOT NULL ,
  88.         classId            Int NOT NULL ,
  89.         statutid           Int NOT NULL ,
  90.         PRIMARY KEY (frais_scolarite_id )
  91. )ENGINE=InnoDB;
  92.  
  93.  
  94. #------------------------------------------------------------
  95. # Table: scolarite_eleve
  96. #------------------------------------------------------------
  97.  
  98. CREATE TABLE scolarite_eleve(
  99.         elevesid Int NOT NULL ,
  100.         statutid Int NOT NULL ,
  101.         niveauid Int NOT NULL ,
  102.         anneeid  Int NOT NULL ,
  103.         classId  Int NOT NULL ,
  104.         PRIMARY KEY (elevesid ,statutid ,niveauid ,anneeid ,classId )
  105. )ENGINE=InnoDB;
  106.  
  107. ALTER TABLE payement_scolarite ADD CONSTRAINT FK_payement_scolarite_elevesid FOREIGN KEY (elevesid) REFERENCES eleves(elevesid);
  108. ALTER TABLE payement_scolarite ADD CONSTRAINT FK_payement_scolarite_frais_scolarite_id FOREIGN KEY (frais_scolarite_id) REFERENCES frais_scolarite(frais_scolarite_id);
  109. ALTER TABLE frais_scolarite ADD CONSTRAINT FK_frais_scolarite_anneeid FOREIGN KEY (anneeid) REFERENCES annee_scolaire(anneeid);
  110. ALTER TABLE frais_scolarite ADD CONSTRAINT FK_frais_scolarite_niveauid FOREIGN KEY (niveauid) REFERENCES niveau(niveauid);
  111. ALTER TABLE frais_scolarite ADD CONSTRAINT FK_frais_scolarite_classId FOREIGN KEY (classId) REFERENCES classe(classId);
  112. ALTER TABLE frais_scolarite ADD CONSTRAINT FK_frais_scolarite_statutid FOREIGN KEY (statutid) REFERENCES statut(statutid);
  113. ALTER TABLE scolarite_eleve ADD CONSTRAINT FK_scolarite_eleve_elevesid FOREIGN KEY (elevesid) REFERENCES eleves(elevesid);
  114. ALTER TABLE scolarite_eleve ADD CONSTRAINT FK_scolarite_eleve_statutid FOREIGN KEY (statutid) REFERENCES statut(statutid);
  115. ALTER TABLE scolarite_eleve ADD CONSTRAINT FK_scolarite_eleve_niveauid FOREIGN KEY (niveauid) REFERENCES niveau(niveauid);
  116. ALTER TABLE scolarite_eleve ADD CONSTRAINT FK_scolarite_eleve_anneeid FOREIGN KEY (anneeid) REFERENCES annee_scolaire(anneeid);
  117. ALTER TABLE scolarite_eleve ADD CONSTRAINT FK_scolarite_eleve_classId FOREIGN KEY (classId) REFERENCES classe(classId);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement