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 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
|
DROP TABLE CHANTIER CASCADE CONSTRAINTS;
DROP TABLE HISTORIQUE CASCADE CONSTRAINTS;
DROP TABLE CA CASCADE CONSTRAINTS;
DROP TABLE MEMBRECA CASCADE CONSTRAINTS;
DROP TABLE DONATEUR CASCADE CONSTRAINTS;
DROP TABLE DON CASCADE CONSTRAINTS;
DROP TABLE MEMBRE CASCADE CONSTRAINTS;
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_KEY 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 HISTORIQUE (
NUMPERS CHAR (4),
NOM CHAR (7) NOT NULL,
NUMCHANTIER NUMBER (4),
CONSTRAINT HISTORIQUE_NUMCHANTIER_KEY FOREIGN KEY (NUMCHANTIER) REFERENCES CHANTIER (NUMCHANTIER),
CONSTRAINT HISTORIQUE_PRIMARY_KEY PRIMARY KEY (NUMPERS));
INSERT INTO HISTORIQUE VALUES (5000,'MULLER',1002);
INSERT INTO HISTORIQUE VALUES (5001,'JAMES',1002);
INSERT INTO HISTORIQUE VALUES (5002,'LIAUDET',1002);
INSERT INTO HISTORIQUE VALUES (5003,'TURNER',1002);
CREATE TABLE CA (
NUMCONSEIL NUMBER (4),
DEBUTC DATE,
FINC DATE,
CONSTRAINT CA_NUMCONSEIL_KEY 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 MEMBRECA 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_KEY 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_KEY FOREIGN KEY (NUMDONATEUR) REFERENCES DONATEUR (NUMDONATEUR),
CONSTRAINT DON_NUMDON_KEY 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),
NUMDONATEUR NUMBER (4),
CONSTRAINT MEMBRE_NUMCHANTIER_KEY FOREIGN KEY (NUMCHANTIER) REFERENCES CHANTIER (NUMCHANTIER),
CONSTRAINT MEMBRE_NUMMEMBREC_KEY FOREIGN KEY (NUMMEMBRECA) REFERENCES MEMBRECA (NUMMEMBRECA),
CONSTRAINT MEMBRE_NUMDONATEUR_KEY FOREIGN KEY (NUMDONATEUR) REFERENCES DONATEUR (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);
INSERT INTO MEMBRE VALUES (4002,'DUBOIS','DUBOIS@WANADOO.FR',112,5,TO_DATE('23-09-2006','dd.mm.yyyy'),'NON',1001,2005,NULL);
INSERT INTO MEMBRE VALUES (4003,'THOMPSON','THOMPSON@WANADOO.FR',113,14,TO_DATE('31-08-2006','dd.mm.yyyy'),'OUI',1001,2006,NULL);
INSERT INTO MEMBRE VALUES (4004,'THOMAS','THOMAS@WANADOO.FR',114,15,TO_DATE('01-09-2006','dd.mm.yyyy'),'NON',1000,2007,3003);
INSERT INTO MEMBRE VALUES (4005,'VANDOOREN','VANDOOREN@WANADOO.FR',115,32,TO_DATE('02-01-2006','dd.mm.yyyy'),'OUI',1000,NULL,NULL);
INSERT INTO MEMBRE VALUES (4006,'DUPONT','DUPONT@WANADOO.FR',116,24,TO_DATE('12-03-2006','dd.mm.yyyy'),'OUI',1001,NULL,NULL);
INSERT INTO MEMBRE VALUES (4007,'DUPOND','DUPOND@WANADOO.FR',117,16,TO_DATE('05-09-2006','dd.mm.yyyy'),'NON',1000,NULL,NULL);
INSERT INTO MEMBRE VALUES (4008,'LIAUDET','SEDER@WANADOO.FR',118,13,TO_DATE('28-08-2006','dd.mm.yyyy'),'OUI',1001,NULL,NULL);
INSERT INTO MEMBRE VALUES (4009,'ROULAZ','ROULAZ@WANADOO.FR',119,28,TO_DATE('08-03-2006','dd.mm.yyyy'),'OUI',1000,NULL,NULL);
INSERT INTO MEMBRE VALUES (4010,'GNOCCHI','GNOCCHI@WANADOO.FR',120,30,TO_DATE('29-12-2005','dd.mm.yyyy'),'NON',1001,NULL,NULL);
INSERT INTO MEMBRE VALUES (4011,'TAGLI','TAGLI@WANADOO.FR',121,31,TO_DATE('25-12-2005','dd.mm.yyyy'),'OUI',1001,NULL,NULL);
INSERT INTO MEMBRE VALUES (4012,'TOGLOU','TOGLOU@WANADOO.FR',122,3,TO_DATE('03-11-2006','dd.mm.yyyy'),'NON',1000,NULL,NULL);
INSERT INTO MEMBRE VALUES (4013,'FERNAND','FERNAND@WANADOO.FR',123,44,TO_DATE('25-10-2005','dd.mm.yyyy'),'NON',1001,NULL,NULL);
INSERT INTO MEMBRE VALUES (4014,'VINCENT','VINCENT@WANADOO.FR',124,40,TO_DATE('30-10-2005','dd.mm.yyyy'),'NON',1001,NULL,NULL);
INSERT INTO MEMBRE VALUES (4015,'FRANCOIS','FRANCOIS@WANADOO.FR',125,39,TO_DATE('27-10-2005','dd.mm.yyyy'),'NON',1000,NULL,NULL);
INSERT INTO MEMBRE VALUES (4016,'DOUVA','DOUVA@WANADOO.FR',126,46,TO_DATE('03-09-2006','dd.mm.yyyy'),'OUI',1000,NULL,NULL);
INSERT INTO MEMBRE VALUES (4017,'WARD','WARD@WANADOO.FR',127,36,TO_DATE('18-10-2005','dd.mm.yyyy'),'NON',1000,2002,NULL);
INSERT INTO MEMBRE VALUES (4018,'JAMES','JAMES@WANADOO.FR',128,37,TO_DATE('11-10-2005','dd.mm.yyyy'),'OUI',1001,2001,NULL);
INSERT INTO MEMBRE VALUES (4019,'TURNER','TURNER@WANADOO.FR',129,25,TO_DATE('01-03-2006','dd.mm.yyyy'),'OUI',1000,2000,NULL); |
Partager