CREATE TABLE DONATEURS (
NUMDON INT NOT NULL,
DTYPE CHAR(10) CHECK (VALUE (DTYPE) IN ('PRIVE','PUBLIC')),
DONNOM CHAR(15),
DONPRENOM CHAR(15),
ADRESS CHAR(30),
CP INT,
VILLE CHAR(20)
PRIMARY KEY (NUMDON));
/*INSERT INTO DONATEURS VALUES (0003,'PUBLIC','DE LA FORET','JEAN','36 rue preschez',92210,'saint cloud');*/
CREATE TABLE DONS (
DNUM INT NOT NULL,
DDATE DATE,
MONTANT INT,
TRANSAC CHAR(8) CHECK (VALUE (TRANSAC) IN ('CHEQUE','LIQUIDE','VIREMENT')),
ORIGINE CHAR(8) CHECK (VALUE (ORIGINE) IN ('MAILING','RECU','CHANTIER','INCONNUE')),
DATEENVOI DATE,
NUMDON INT NOT NULL,
PRIMARY KEY (DNUM),
FOREIGN KEY (NUMDON) REFERENCES DONATEURS(NUMDON));
CREATE TABLE MEMBRES (
NUMMEMB INT NOT NULL,
NOMMEMBRE CHAR(15),
PRENOMMEMBRE CHAR(15),
MAIL CHAR(50),
TEL INT,
COTISATION CHAR(10),
NUMCHANT INT NOT NULL,
NUMDON INT NOT NULL,
PRIMARY KEY (NUMMEMB),
FOREIGN KEY (NUMCHANT) REFERENCES CHANTIER (NUMCHANT),
FOREIGN KEY (DNUM) REFERENCES DONS (DNUM));
CREATE TABLE CHANTIER (
NUMCHANT INT NOT NULL,
VILLAGE CHAR(20),
PAYS CHAR(20),
TYPEBAT CHAR(10) CHECK IN TYPEBAT ('ECOLE','LOGEMENT','RENOVATION','PISCINE'),
PARTENAIRE CHAR(20),
DATEDBT DATE,
DATEFIN DATE,
PRIMARY KEY (NUMCHANT));
CREATE TABLE CONSEIL (
NUMCONSEIL INT NOT NULL,
DBTCONSEIL DATE,
FINCONSEIL DATE,
PRIMARY KEY (NUMCONSEIL));
CREATE TABLE PARTICIPE (
NUMCHANT INT NOT NULL,
NUMMEMB INT NOT NULL,
CHEFCHANTIER CHAR(3) CHECK IN CHEFCHANTIER ('OUI','NON'),
FOREIGN KEY (NUMCHANT) REFERENCES CHANTIER (NUMCHANT)
FOREIGN KEY (NUMMEMB) REFERENCES MEMBRES (NUMMEMB));
CREATE TABLE FAITPARTIE (
NUMMEMB INT NOT NULL,
NUMCONSEIL INT NOT NULL,
FONCTION CHAR(15) CHECK IN FONCTION ('PRESIDENT','TRESORIER','SECRETAIRE','ADMINISTRATEUR'),
FOREIGN KEY (NUMCONSEIL) REFERENCES CONSEIL (NUMCONSEIL)
FOREIGN KEY (NUMMEMB) REFERENCES MEMBRES (NUMMEMB));
Partager