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
| CREATE TABLE PE_personne(
PE_ident INT IDENTITY,
PE_nom VARCHAR(50) NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PE_ddn DATE NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE AS_association(
AS_ident INT IDENTITY,
AS_siren CHAR(9) NOT NULL,
AS_nom VARCHAR(255) NOT NULL,
PRIMARY KEY(AS_ident),
UNIQUE(AS_siren)
);
CREATE TABLE GR_groupe(
AS_ident INT,
GR_seq SMALLINT,
GR_code CHAR(4) NOT NULL,
GR_lib VARCHAR(50) NOT NULL,
PRIMARY KEY(AS_ident, GR_seq),
UNIQUE(GR_code),
FOREIGN KEY(AS_ident) REFERENCES AS_association(AS_ident)
);
CREATE TABLE TA_tarif(
TA_ident INT IDENTITY,
TA_code CHAR(4) NOT NULL,
TA_montant DECIMAL(7,2) NOT NULL,
PRIMARY KEY(TA_ident),
UNIQUE(TA_code)
);
CREATE TABLE CA_calendrier(
CA_dtdeb DATE,
PRIMARY KEY(CA_dtdeb)
);
CREATE TABLE AD_adhesion(
PE_ident INT,
AD_seq SMALLINT,
AD_dtdeb DATE NOT NULL,
AD_dtfin DATE NOT NULL,
AS_ident INT NOT NULL,
PRIMARY KEY(PE_ident, AD_seq),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident),
FOREIGN KEY(AS_ident) REFERENCES AS_association(AS_ident)
);
CREATE TABLE AF_affecter(
PE_ident INT,
AS_ident INT,
GR_seq SMALLINT,
PRIMARY KEY(PE_ident, AS_ident, GR_seq),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident),
FOREIGN KEY(AS_ident, GR_seq) REFERENCES GR_groupe(AS_ident, GR_seq)
);
CREATE TABLE AP_appliquer(
AS_ident INT,
GR_seq SMALLINT,
TA_ident INT,
CA_dtdeb DATE,
PRIMARY KEY(AS_ident, GR_seq, TA_ident, CA_dtdeb),
FOREIGN KEY(AS_ident, GR_seq) REFERENCES GR_groupe(AS_ident, GR_seq),
FOREIGN KEY(TA_ident) REFERENCES TA_tarif(TA_ident),
FOREIGN KEY(CA_dtdeb) REFERENCES CA_calendrier(CA_dtdeb)
);
CREATE TABLE CO_cotiser(
PE_ident INT,
TA_ident INT,
CA_dtdeb DATE,
PRIMARY KEY(PE_ident, TA_ident, CA_dtdeb),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident),
FOREIGN KEY(TA_ident) REFERENCES TA_tarif(TA_ident),
FOREIGN KEY(CA_dtdeb) REFERENCES CA_calendrier(CA_dtdeb)
); |
Partager