Skip to content

Instantly share code, notes, and snippets.

@IGI-111

IGI-111/gestion_mysql.sql

Created October 28, 2014 20:40
Show Gist options
  • Save IGI-111/81918b8ceded7791f4eb to your computer and use it in GitHub Desktop.
Save IGI-111/81918b8ceded7791f4eb to your computer and use it in GitHub Desktop.
Download ZIP
Base de donnée pour TP JDBC
Raw
gestion_mysql.sql
DROP TABLE IF EXISTS ETUDIANT;
DROP TABLE IF EXISTS PROF;
DROP TABLE IF EXISTS MODULE;
DROP TABLE IF EXISTS ENSEIGNT;
DROP TABLE IF EXISTS NOTATION;
CREATE TABLE ETUDIANT
(
NUM_ET INT(6) PRIMARY KEY AUTO_INCREMENT,
NOM_ET VARCHAR(20),
PRENOM_ET VARCHAR(15),
CP_ET VARCHAR(5),
VILLE_ET VARCHAR(20),
ANNEE DECIMAL(2,0),
GROUPE DECIMAL(1,0)
);
CREATE TABLE PROF
(
NUM_PROF INT(3) PRIMARY KEY AUTO_INCREMENT,
NOM_PROF VARCHAR(30),
PRENOM_PROF VARCHAR(20),
ADR_PROF VARCHAR(40),
CP_PROF VARCHAR(5),
VILLE_PROF VARCHAR(30),
MAT_SPEC VARCHAR(7)
);
CREATE TABLE MODULE
(
CODE VARCHAR(7) PRIMARY KEY,
LIBELLE VARCHAR(30),
H_COURS_PREV DECIMAL(3,0),
H_COURS_REA DECIMAL(3,0),
H_TP_PREV DECIMAL(3,0),
H_TP_REA DECIMAL(3,0),
DISCIPLINE VARCHAR(15),
COEFF_TEST DECIMAL(3,0),
COEFF_CC DECIMAL(3,0),
RESP DECIMAL(3,0),
CODEPERE VARCHAR(7)/*,
FOREIGN KEY(CODEPERE) REFERENCES MODULE(CODE)*/
);
CREATE TABLE ENSEIGNT
(
CODE VARCHAR(7),
NUM_PROF INT(3),
NUM_ET INT(6),
PRIMARY KEY(CODE, NUM_PROF, NUM_ET)/*, */
/* FOREIGN KEY(NUM_PROF) REFERENCES PROF(NUM_PROF), */
/* FOREIGN KEY(NUM_ET) REFERENCES ETUDIANT(NUM_ET), */
/* FOREIGN KEY(CODE) REFERENCES MODULE(CODE) */
);
CREATE TABLE NOTATION
(
NUM_ET INT(6),
CODE VARCHAR(7),
MOY_CC DECIMAL(2,0),
MOY_TEST DECIMAL(2,0),
PRIMARY KEY(NUM_ET, CODE)
);
INSERT INTO ENSEIGNT
(CODE,NUM_PROF,NUM_ET)
VALUES
('ACSI', 1 , 2101),
('ACSI', 1 , 2102),
('ACSI', 1 , 2404),
('ACSI', 1 , 2422),
('ACSI', 2 , 2101),
('ACSI', 2 , 2102),
('ACSI', 2 , 2103),
('ACSI', 2 , 2104),
('ACSI', 2 , 2105),
('ACSI', 2 , 2113),
('ACSI', 2 , 2114),
('ACSI', 2 , 2121),
('ACSI', 2 , 2124),
('ACSI', 2 , 2125),
('ACSI', 2 , 2201),
('ACSI', 2 , 2202),
('ACSI', 2 , 2207),
('ACSI', 2 , 2209),
('ACSI', 2 , 2211),
('ACSI', 2 , 2212),
('ACSI', 2 , 2217),
('ACSI', 2 , 2220),
('ACSI', 2 , 2222),
('ACSI', 2 , 2224),
('ACSI', 2 , 2226),
('ACSI', 2 , 2301),
('ACSI', 2 , 2302),
('ACSI', 2 , 2304),
('ACSI', 2 , 2306),
('ACSI', 2 , 2316),
('ACSI', 2 , 2317),
('ACSI', 2 , 2318),
('ACSI', 2 , 2320),
('ACSI', 2 , 2326),
('ACSI', 2 , 2401),
('ACSI', 2 , 2402),
('ACSI', 2 , 2404),
('ACSI', 2 , 2406),
('ACSI', 2 , 2412),
('ACSI', 2 , 2415),
('ACSI', 2 , 2421),
('ACSI', 2 , 2422),
('ACSI', 2 , 2423),
('ACSI', 2 , 2501),
('ACSI', 2 , 2503),
('ACSI', 2 , 2509),
('ACSI', 2 , 2512),
('ACSI', 2 , 2516),
('ACSI', 2 , 2523),
('ACSI', 3 , 2101),
('ACSI', 3 , 2102),
('ACSI', 3 , 2103),
('ACSI', 3 , 2422),
('ACSI', 3 , 2501),
('ADA' , 5 , 2401),
('ADA' , 5 , 2402),
('BD' , 1 , 2317),
('BD' , 1 , 2401),
('BD' , 1 , 2402),
('BD' , 1 , 2422),
('BD' , 1 , 2501),
('BD' , 2 , 2317),
('BD' , 2 , 2422),
('BD' , 2 , 2501),
('BD' , 3 , 2302),
('BD' , 3 , 2422),
('BD' , 3 , 2501),
('BD' , 3 , 2503),
('BD' , 3 , 2509),
('BD' , 3 , 2512),
('BD' , 3 , 2516),
('BD' , 3 , 2523),
('BD' , 13, 1101),
('BD' , 13, 1102),
('BD' , 13, 1103),
('BD' , 13, 1104),
('BD' , 13, 1105),
('BD' , 13, 1106),
('BD' , 13, 1107),
('BD' , 13, 1108),
('BD' , 13, 1109),
('BD' , 13, 1110),
('BD' , 13, 1111),
('BD' , 13, 2317),
('C++' , 5 , 1101),
('C++' , 5 , 1102),
('C++' , 5 , 1103),
('C++' , 5 , 1104),
('C++' , 5 , 1105),
('C++' , 5 , 1106),
('C++' , 5 , 1107),
('C++' , 5 , 1108),
('C++' , 5 , 1109),
('C++' , 5 , 1110),
('C++' , 5 , 1111),
('C++' , 5 , 2317),
('C++' , 7 , 2317),
('CPTA', 12, 2302),
('CPTA', 12, 2317),
('CPTA', 12, 2412),
('CPTA', 12, 2415),
('CPTA', 12, 2422),
('CPTA', 12, 2423),
('PRL' , 9 , 2302),
('RES' , 14, 1101),
('RES' , 14, 1102),
('RES' , 14, 1103),
('RES' , 14, 1104),
('RES' , 14, 1105),
('RES' , 14, 1106),
('RES' , 14, 1107),
('RES' , 14, 1108),
('RES' , 14, 1109),
('RES' , 14, 1110),
('RES' , 14, 1111),
('RES' , 15, 2302),
('SYS' , 5 , 2401),
('SYS' , 5 , 2402),
('SYS' , 5 , 2404),
('SYS' , 7 , 2317),
('SYS' , 15, 2317);
INSERT INTO MODULE
(CODE,LIBELLE,H_COURS_PREV,H_COURS_REA,H_TP_PREV,H_TP_REA,DISCIPLINE,COEFF_TEST,COEFF_CC,RESP,CODEPERE)
VALUES
('PPNINFO', 'PROGRAMME DUT INFO' , null, null, null, null, null , null, null, null, null ),
('PINFO2' , 'INFORMATIQUE 2EME ANNEE' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'PPNINFO'),
('PINFO1' , 'INFORMATIQUE 1ERE ANNEE' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'PPNINFO'),
('PCC2' , 'COMPETENCES COMP 2' , null, null, null, null, null , null, null, null, 'PPNINFO'),
('PCC1' , 'COMPETENCES COMP 1' , null, null, null, null, null , null, null, null, 'PPNINFO'),
('OMGL' , 'OUTILS MODELES GENIE LOGICIEL' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'PINFO2' ),
('OMGL1' , 'ANALYSE, CONCEPTION' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'OMGL' ),
('OMGL2' , 'PRINCIPES DES BD' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'OMGL' ),
('ACSI' , 'CONCEPTION DE SI' , 30 , null, 40 , null, 'INFORMATIQUE' , 60 , 40 , 1 , 'OMGL1' ),
('CAS1' , 'ETUDE DE CAS 1' , null, null, null, null, 'INFORMATIQUE' , 0 , 100 , 1 , 'OMGL1' ),
('CAS2' , 'ETUDE DE CAS 2' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'OMGL1' ),
('ASR' , 'ARCHITECTURE, ING. RESEAUX' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'PINFO2' ),
('ASR1' , 'CODAGE ET CIRCUITS' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'ASR' ),
('AP' , 'ALGORITHMIQUE ET PROGRAMMATION', null, null, null, null, null , null, null, null, 'PINFO2' ),
('AP1' , 'ALGORITHMIQUE' , null, null, null, null, 'INFORMATIQUE' , null, null, null, 'AP' ),
('EGO' , 'ECONOMIE GESTION ORGANISATION' , null, null, null, null, 'GESTION' , null, null, null, 'PCC2' ),
('EC' , 'EXPRESSION COMMUNICATION' , null, null, null, null, 'COMMUNICATION', null, null, null, 'PCC2' ),
('MATH2' , 'MATHEMATIQUES 2' , null, null, null, null, null , null, null, null, 'PCC2' ),
('MATH1' , 'MATHEMATIQUES 1' , null, null, null, null, null , null, null, null, 'PCC1' ),
('LANG1' , 'LANGUE VIVANTE 1' , null, null, null, null, null , null, null, null, 'PCC1' ),
('LANG2' , 'LANGUE VIVANTE 2' , null, null, null, null, null , null, null, null, 'PCC2' ),
('RES' , 'RESEAUX' , null, null, null, null, null , null, null, null, 'ASR1' ),
('C++' , 'PROGRAMMATION C++' , 45 , null, 50 , null, 'INFORMATIQUE' , 50 , 50 , 7 , 'AP1' ),
('SYS' , 'SYSTEME' , 45 , null, 50 , null, 'INFORMATIQUE' , 50 , 50 , 4 , 'ASR1' ),
('PRL' , 'PROLOG' , 20 , null, 35 , null, 'INFORMATIQUE' , 40 , 60 , 9 , 'AP1' ),
('ADA' , 'LANGAGE ADA' , 45 , null, 60 , null, 'INFORMATIQUE' , 50 , 50 , 6 , 'AP1' ),
('CPTA' , 'COMPTABILITE' , 25 , null, 0 , null, 'GESTION' , 60 , 40 , 10 , 'EGO' ),
('ANGL2' , 'ANGLAIS' , 80 , null, 0 , null, null , 100 , 0 , 11 , 'LANG2' ),
('ANGL1' , 'ANGLAIS' , 80 , null, 0 , null, null , 100 , 0 , 11 , 'LANG1' ),
('MKTG' , 'MARKETING' , 25 , null, 0 , null, 'GESTION' , 60 , 40 , 12 , 'EGO' ),
('COMM' , 'COMMUNICATION' , 40 , null, 15 , null, 'COMMUNICATION', 100 , 0 , null, 'EC' ),
('RO' , 'RECHERCHE OPERATIONNELLE' , 20 , null, 25 , null, 'MATHS' , 50 , 50 , null, 'MATH2' ),
('BD' , 'BASES DE DONNEES' , 20 , null, 60 , null, 'INFORMATIQUE' , 60 , 40 , 3 , 'OMGL2' );
INSERT INTO NOTATION
(NUM_ET,CODE,MOY_CC,MOY_TEST)
VALUES
(2406, 'ACSI' , 11 , 15 ),
(2404, 'ACSI' , 10 , 13 ),
(2401, 'ACSI' , 8 , 12 ),
(2402, 'ACSI' , 15 , 17 ),
(2326, 'ACSI' , 13 , 17 ),
(2320, 'ACSI' , 6 , 10 ),
(2318, 'ACSI' , 15 , 12 ),
(2317, 'ACSI' , 9 , 12 ),
(2301, 'ACSI' , 16 , 18 ),
(2523, 'ACSI' , 11 , 14 ),
(2516, 'ACSI' , 10 , 12 ),
(2512, 'ACSI' , 9 , 13 ),
(2509, 'ACSI' , 14 , 14 ),
(2503, 'ACSI' , 8 , 12 ),
(2406, 'BD' , 12 , 15 ),
(2404, 'BD' , 14 , 15 ),
(2401, 'BD' , 8 , 13 ),
(2402, 'BD' , 5 , 7 ),
(2326, 'BD' , 13 , 16 ),
(2320, 'BD' , 10 , 12 ),
(2318, 'BD' , 15 , 17 ),
(2317, 'BD' , 12 , 13 ),
(2301, 'BD' , 15 , 18 ),
(2523, 'BD' , 14 , 14 ),
(2516, 'BD' , 8 , 12 ),
(2512, 'BD' , 7 , 10 ),
(2509, 'BD' , 12 , 15 ),
(2503, 'BD' , 6 , 10 ),
(2406, 'RO' , 13 , 15 ),
(2404, 'RO' , 17 , 19 ),
(2401, 'RO' , 8 , 13 ),
(2402, 'RO' , 10 , 0 ),
(2326, 'RO' , 13 , 14 ),
(2320, 'RO' , 12 , 13 ),
(2318, 'RO' , 16 , 17 ),
(2317, 'RO' , 14 , 13 ),
(2301, 'RO' , 11 , 13 ),
(2523, 'RO' , 15 , 16 ),
(2516, 'RO' , 12 , 12 ),
(2512, 'RO' , 7 , 11 ),
(2509, 'RO' , 11 , 15 ),
(2503, 'RO' , 8 , 10 ),
(2101, 'ACSI' , 10 , 12 ),
(2101, 'PRL' , 4 , 16 ),
(2102, 'ACSI' , 13 , 6 ),
(2102, 'PRL' , 12 , 3 ),
(2103, 'ACSI' , 14 , 10 ),
(2103, 'PRL' , 12 , 9 ),
(2104, 'ACSI' , 10 , 10 ),
(2104, 'PRL' , 9 , 15 ),
(2105, 'ACSI' , 13 , null),
(2105, 'PRL' , 10 , 13 ),
(2113, 'ACSI' , 9 , 8 ),
(2113, 'PRL' , 11 , 12 ),
(2121, 'ACSI' , 7 , 18 ),
(2121, 'PRL' , 9 , 16 ),
(2124, 'ACSI' , 15 , 14 ),
(2124, 'PRL' , 8 , 15 ),
(2125, 'ACSI' , 10 , 10 ),
(2422, 'ACSI' , 10 , 19 ),
(2422, 'PRL' , 10 , 12 ),
(2422, 'SYS' , 6 , 10 ),
(2422, 'RO' , 14 , 17 ),
(2422, 'CPTA' , 12 , 13 ),
(2422, 'ADA' , 8 , 10 ),
(2422, 'MKTG' , 13 , 15 ),
(2316, 'ACSI' , 16 , 8 ),
(2316, 'PRL' , 12 , 10 ),
(2316, 'SYS' , 8 , 14 ),
(2422, 'ANGL2', null, 10 ),
(2422, 'BD' , null, 13 ),
(2201, 'ACSI' , 10 , 12 ),
(2202, 'ACSI' , 9 , 10 ),
(2207, 'ACSI' , 13 , 12 ),
(2209, 'ACSI' , 6 , null),
(2201, 'BD' , 15 , 12 ),
(2202, 'BD' , 17 , null),
(2207, 'BD' , 13 , 12 ),
(2209, 'BD' , 6 , null);
INSERT INTO PROF (NUM_PROF,NOM_PROF,PRENOM_PROF,ADR_PROF,CP_PROF,VILLE_PROF,MAT_SPEC) VALUES
(1 , 'BOITARD' , 'DIDIER' , null, null, 'AIX-EN-PROVENCE', 'ACSI' ),
(2 , 'FAURE' , 'BERNARD' , null, null, 'AIX-EN-PROVENCE', 'ACSI' ),
(3 , 'CICCHETTI' , 'ROSINE' , null, null, 'AIX-EN-PROVENCE', 'BD' ),
(4 , 'FENEUILLE' , 'DANIEL' , null, null, 'AIX-EN-PROVENCE', 'ADA' ),
(5 , 'LAPORTE' , 'MARC' , null, null, 'AIX-EN-PROVENCE', 'C++' ),
(6 , 'AVIGNON' , 'THIERRY' , null, null, 'AIX-EN-PROVENCE', 'ADA' ),
(7 , 'MATHIEU' , 'DIDIER' , null, null, 'MARSEILLE' , 'C++' ),
(8 , 'BETARI' , 'KADER' , null, null, 'MARSEILLE' , 'ADA' ),
(9 , 'PAPINI' , 'ODILE' , null, null, 'MARSEILLE' , null ),
(10, 'MALPAS' , 'MYLENE' , null, null, null , null ),
(11, 'GIOVAMPAOLI', 'JOSETTE' , null, null, null , 'ANGL2'),
(12, 'CACCHIA' , 'MARIE-CLAUDE', null, null, null , 'EGO' ),
(13, 'LAKHAL' , 'LOTFI' , null, null, 'AIX-EN-PROVENCE', 'BD' ),
(14, 'PAIN-BARRE' , 'CYRIL' , null, null, 'MARSEILLE' , 'RES' ),
(15, 'DRAGUT' , 'ANDREEA' , null, null, 'AIX-EN-PROVENCE', 'SYS' ),
(16, 'BONHOMME' , 'CHRISTIAN' , null, null, 'MARSEILLE' , 'BD' ),
(17, 'SUN' , 'PIERRE' , null, null, 'MARSEILLE' , 'ACSI' );
INSERT INTO ETUDIANT (NUM_ET,NOM_ET,PRENOM_ET,CP_ET,VILLE_ET,ANNEE,GROUPE) VALUES
(2101, 'ALARCON' , 'LAURENT' , null, null , 2, 1),
(2102, 'ARNAUD' , 'EMMANUEL' , null, 'AIX-EN-PROVENCE', 2, 1),
(2103, 'BALESTRIERI', 'KARINE' , null, 'MARIGNANE' , 2, 1),
(2104, 'BERENGUIER' , 'BERNARD' , null, 'AIX-EN-PROVENCE', 2, 1),
(2105, 'BOURMAD' , 'MEHDI' , null, 'AIX-EN-PROVENCE', 2, 1),
(2113, 'GALINIER' , 'BENOIT' , null, 'AIX-EN-PROVENCE', 2, 1),
(2121, 'RIBES' , 'MARIE-LAURENCE', null, 'AIX-EN-PROVENCE', 2, 1),
(2124, 'TRAN' , 'TRI' , null, 'MARTIGUES' , 2, 1),
(2125, 'VERGES' , 'LAURENT' , null, 'MARSEILLE' , 2, 1),
(2114, 'GASSE' , 'DIDIER' , null, 'MARSEILLE' , 2, 1),
(2201, 'ALVES' , 'EMMANUEL' , null, 'MARSEILLE' , 2, 2),
(2202, 'ARCHAMBAULD', 'JULIEN' , null, 'MARSEILLE' , 2, 2),
(2207, 'CADET' , 'CELINE' , null, 'MARSEILLE' , 2, 2),
(2209, 'CHADEAU' , 'CEDRIC' , null, 'MARSEILLE' , 2, 2),
(2211, 'COSTE' , 'THOMAS' , null, 'MARSEILLE' , 2, 2),
(2212, 'DEFOSSE' , 'JEREMY' , null, 'MARSEILLE' , 2, 2),
(2217, 'JEGO' , 'NICOLAS' , null, 'MARSEILLE' , 2, 2),
(2220, 'MICHAUD' , 'OLIVIER' , null, 'ISTRES' , 2, 2),
(2222, 'NEVEU' , 'PASCAL' , null, 'AIX-EN-PROVENCE', 2, 2),
(2224, 'REY' , 'CATHERINE' , null, 'AIX-EN-PROVENCE', 2, 2),
(2226, 'VIO' , 'BERTRAND' , null, 'AIX-EN-PROVENCE', 2, 2),
(2301, 'ANDRE' , 'NATHALIE' , null, 'MARSEILLE' , 2, 3),
(2302, 'ATLANI' , 'JEROME' , null, 'MARSEILLE' , 2, 3),
(2304, 'BASSET' , 'OLIVIER' , null, 'MARSEILLE' , 2, 3),
(2306, 'BOREL' , 'BERNARD' , null, 'MARSEILLE' , 2, 3),
(2317, 'LYON' , 'PHILIPPE' , null, 'MARSEILLE' , 2, 3),
(2318, 'MAI' , 'CHRISTINE' , null, 'GAP' , 2, 3),
(2316, 'LEVY' , 'SANDRINE' , null, 'AIX-EN-PROVENCE', 2, 3),
(2320, 'NELLI' , 'ANNICK' , null, 'AIX-EN-PROVENCE', 2, 3),
(2326, 'WELKER' , 'THIERRY' , null, 'AIX-EN-PROVENCE', 2, 3),
(2401, 'ARTAUD' , 'SABINE' , null, 'MARSEILLE' , 2, 4),
(2402, 'AUBERT' , 'MICHEL' , null, 'MARSEILLE' , 2, 4),
(2404, 'BOEUF' , 'FREDERIC' , null, 'MARSEILLE' , 2, 4),
(2406, 'CROTTI' , 'MURIEL' , null, 'MARSEILLE' , 2, 4),
(2412, 'LANDAIS' , 'SANDRA' , null, 'MARSEILLE' , 2, 4),
(2421, 'PONZIO' , 'ANNE' , null, 'MARSEILLE' , 2, 4),
(2422, 'ROCCHI' , 'STEPHANE' , null, 'AIX-EN-PROVENCE', 2, 4),
(2423, 'SEU' , 'SEBASTIEN' , null, 'AIX-EN-PROVENCE', 2, 4),
(2415, 'MESSINA' , 'DIDIER' , null, 'AIX-EN-PROVENCE', 2, 4),
(2501, 'APPRIOU' , 'GUERLAIN' , null, 'AIX-EN-PROVENCE', 2, 5),
(2503, 'BLANC' , 'DOMINIQUE' , null, 'AIX-EN-PROVENCE', 2, 5),
(2509, 'DONNAY' , 'SEVERINE' , null, 'AIX-EN-PROVENCE', 2, 5),
(2512, 'GARRO' , 'PATRICK' , null, 'AIX-EN-PROVENCE', 2, 5),
(2516, 'LAMY' , 'DAVID' , null, 'AIX-EN-PROVENCE', 2, 5),
(2523, 'PINELLI' , 'SERGE' , null, null , 2, 5),
(1101, 'ALLIONE' , 'MATHIEU' , null, null , 1, 1),
(1102, 'ANDRE' , 'JULIE' , null, null , 1, 1),
(1103, 'BEAUJEAN' , 'ADAM' , null, null , 1, 1),
(1104, 'BELLE' , 'FLORENT' , null, null , 1, 1),
(1105, 'BERNO' , 'CHARLES' , null, null , 1, 1),
(1106, 'BOVEE' , 'CHARLES' , null, null , 1, 1),
(1107, 'BRYIS' , 'JONATHAN' , null, null , 1, 1),
(1108, 'CHIABAUT' , 'DIMITRI' , null, null , 1, 1),
(1109, 'COPPARD' , 'REMI' , null, null , 1, 1),
(1110, 'DEFOSSEZ' , 'GAEL' , null, null , 1, 1),
(1111, 'DUMAS' , 'BENJAMIN' , null, null , 1, 1);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment