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
| CREATE TABLE CHANTIER (
NUMCHANTIER NUMBER (4),
VILLAGE CHAR (7),
PAYS CHAR (7),
BATIMENT CHAR (17) ,
PARTENAIRE CHAR (8),
DEBUT DATE,
FIN DATE,
CONSTRAINT CHANTIER_NUMCHANTIER_FK PRIMARY KEY (NUMCHANTIER));
INSERT INTO CHANTIER VALUES (1000,'CONGO','AFRIQUE','ECOLE','HSBC',TO_DATE('01-01-2006','dd.mm.yyyy'),NULL);
INSERT INTO CHANTIER VALUES (1001,'BULLION','FRANCE','LOGEMENTS','BOUYGUES',TO_DATE('03-04-2006','dd.mm.yyyy'),NULL);
INSERT INTO CHANTIER VALUES (1002,'TUNIS','TUNISIE','REFECTION D ECOLE','LENOTRE',TO_DATE('07-07-2005','dd.mm.yyyy'),TO_DATE('03-07-2005','dd.mm.yyyy'));
CREATE TABLE CA (
NUMCONSEIL NUMBER (4),
DEBUTC DATE,
FINC DATE,
CONSTRAINT CA_NUMCONSEIL_FK PRIMARY KEY (NUMCONSEIL));
INSERT INTO CA VALUES (1002,TO_DATE('01-01-2005','dd.mm.yyyy'),TO_DATE('30-12-2005','dd.mm.yyyy'));
INSERT INTO CA VALUES (1003,TO_DATE('01-01-2006','dd.mm.yyyy'),TO_DATE('30-12-2006','dd.mm.yyyy'));
CREATE TABLE MEMBRECA (
NUMMEMBRECA NUMBER (4),
NOMMEMBRECA CHAR (8) NOT NULL,
FONCTION CHAR (14),
NUMCONSEIL NUMBER (4) NOT NULL,
CONSTRAINT MEMBRECA_NUMCONSEIL_KEY FOREIGN KEY (NUMCONSEIL) REFERENCES CA (NUMCONSEIL),
CONSTRAINT MEMBRECA_NUMMEBRECA_KEY PRIMARY KEY (NUMMEMBRECA));
INSERT INTO MEMBRECA VALUES (2000, 'TURNER', 'PRESIDENT', 1002);
INSERT INTO MEMBRECA VALUES (2001, 'JAMES', 'SECRETAIRE', 1003);
INSERT INTO MEMBRECA VALUES (2002, 'WARD', 'SECRETAIRE', 1002);
INSERT INTO MEMBRECA VALUES (2003, 'MULLER', 'TRESORIER', 1002);
INSERT INTO MEMBRECA VALUES (2004, 'DURAND', 'ADMINISTRATEUR', 1003);
INSERT INTO MEMBRECA VALUES (2005, 'DUBOIS', 'TRESORIER', 1003);
INSERT INTO MEMBRECA VALUES (2006, 'THOMPSON', 'PRESIDENT', 1003);
INSERT INTO MEMBRECA121 VALUES (2007, 'THOMAS', 'ADMINISTRATEUR', 1002);
CREATE TABLE DONATEUR (
NUMDONATEUR NUMBER (4),
NOMDONATEUR CHAR (8) NOT NULL,
ADRESSE CHAR (30) NOT NULL,
CP NUMBER (5) NOT NULL,
VILLE CHAR (13) NOT NULL,
CONSTRAINT DONATEUR_NUMDONATEUR_FK PRIMARY KEY (NUMDONATEUR)) ;
INSERT INTO DONATEUR VALUES (3000,'MULLER','15 RUE DES TULIPES',78000,'VERSAILLES');
INSERT INTO DONATEUR VALUES (3001,'LEFRANC','23 AVENUE DES ROSES',78120,'RAMBOUILLET');
INSERT INTO DONATEUR VALUES (3002, 'DURAND','45 ALLEE DES TUYAS',78120,'RAMBOUILLET');
INSERT INTO DONATEUR VALUES (3003, 'THOMAS','75 RUE DES MARGUERITES',78610,'LE PERRAY');
INSERT INTO DONATEUR VALUES (3004,'RIBIERE','36 ALLEE DES ORCHIDEES',78610,'AUFFARGIS');
INSERT INTO DONATEUR VALUES (3005,'SABATINO','42 ALLEE DU BOIS FLEURI',78830,'BULLION');
INSERT INTO DONATEUR VALUES (3006,'JAMES','68 RUE DE PARIS',78830, 'BONNELLES');
INSERT INTO DONATEUR VALUES (3007,'TRIPLET','115 RUE DE CHARTRES',78000,'VERSAILLES');
INSERT INTO DONATEUR VALUES (3008,'AUBRIN','569 AVENUE DE LA FORET',78610,'LE PERRAY');
INSERT INTO DONATEUR VALUES (3009, 'SIMPSONS', '37 RUE DES PAQUERETTES', 92270, 'BOIS-COLOMBES');
CREATE TABLE DON (
NUMDON NUMBER(2),
DATEDON DATE NOT NULL,
MONTANT NUMBER (4) NOT NULL,
MODEP CHAR(8),
ORIGINE CHAR (8),
DATERECU DATE,
NUMDONATEUR NUMBER (4) NOT NULL,
CONSTRAINT DON_NUMDONATEUR_FK FOREIGN KEY (NUMDONATEUR) REFERENCES DONATEUR (NUMDONATEUR),
CONSTRAINT DON_NUMDON_FK PRIMARY KEY (NUMDON));
INSERT INTO DON VALUES (00,TO_DATE('03-04-2005','dd.mm.yyyy'),200,'CHEQUE','INCONNUE',TO_DATE('01-01-2006','dd.mm.yyyy'),3000);
INSERT INTO DON VALUES (01,TO_DATE('05-07-2005','dd.mm.yyyy'),60,'LIQUIDE','MAILING',TO_DATE('01-01-2006','dd.mm.yyyy'),3001);
INSERT INTO DON VALUES (02,TO_DATE('12-07-2005','dd.mm.yyyy'),1010,'VIREMENT','INCONNUE',TO_DATE('01-01-2006','dd.mm.yyyy'),3002);
INSERT INTO DON VALUES (03,TO_DATE('21-08-2005','dd.mm.yyyy'),250,'CHEQUE','RECU',TO_DATE('01-01-2006','dd.mm.yyyy'),3003);
INSERT INTO DON VALUES (04,TO_DATE('31-10-2005','dd.mm.yyyy'),85,'LIQUIDE','MAILING',TO_DATE('01-01-2006','dd.mm.yyyy'),3004);
INSERT INTO DON VALUES (05,TO_DATE('01-12-2005','dd.mm.yyyy'),1300,'VIREMENT','CHANTIER',TO_DATE('01-01-2006','dd.mm.yyyy'),3005);
INSERT INTO DON VALUES (06,TO_DATE('14-02-2006','dd.mm.yyyy'),20,'LIQUIDE','INCONNUE',NULL,3006);
INSERT INTO DON VALUES (07,TO_DATE('05-05-2006','dd.mm.yyyy'),2400,'CHEQUE','CHANTIER',NULL,3007);
INSERT INTO DON VALUES (08,TO_DATE('11-09-2006','dd.mm.yyyy'),40,'CHEQUE','RECU',NULL,3008);
INSERT INTO DON VALUES (09,TO_DATE('11-09-2006','dd.mm.yyyy'),55,'LIQUIDE','RECU',NULL,3009);
INSERT INTO DON VALUES (10,TO_DATE('25-11-2006','dd.mm.yyyy'),250,'CHEQUE','INCONNUE',NULL,3004);
CREATE TABLE MEMBRE (
NUMMEMBRE NUMBER(4),
NOMMEMBRE CHAR (9) NOT NULL,
MAIL CHAR (20) ,
TELEPHONE NUMBER (3),
COTISATION NUMBER (2),
DATECOT DATE,
CHEFCHANTIER CHAR (3),
NUMCHANTIER NUMBER (4) NOT NULL,
NUMMEMBRECA NUMBER (4) NOT NULL,
NUMDONATEUR NUMBER (4) NOT NULL,
CONSTRAINT MEMBRE_NUMCHANTIER_FK FOREIGN KEY (NUMCHANTIER) REFERENCES CHANTIER121 (NUMCHANTIER),
CONSTRAINT MEMBRE_NUMMEMBREC_FK FOREIGN KEY (NUMMEMBRECA) REFERENCES MEMBRECA121 (NUMMEMBRECA),
CONSTRAINT MEMBRE_NUMDONATEUR_FK FOREIGN KEY (NUMDONATEUR) REFERENCES DONATEUR121 (NUMDONATEUR),
CONSTRAINT MEMBRE_PRIMARY_KEY PRIMARY KEY (NUMMEMBRE));
INSERT INTO MEMBRE VALUES (4000,'MULLER','MULLER@WANADOO.FR',110,20,TO_DATE('01-05-2006','dd.mm.yyyy'),'OUI',1001,2003,3000);
INSERT INTO MEMBRE VALUES (4001,'DURAND','DURAND@WANADOO.FR',111,10,TO_DATE('12-08-2006','dd.mm.yyyy'),'NON',1000,2004,3001); |
Partager