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
| CREATE TABLE PE_personne(
PE_ident INT IDENTITY,
PE_nom VARCHAR(50) NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE CL_client(
PE_ident INT,
CL_numero SMALLINT NOT NULL,
PRIMARY KEY(PE_ident),
UNIQUE(CL_numero),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE EM_employe(
PE_ident INT,
EM_matricule CHAR(8) NOT NULL,
EM_dtemb DATE NOT NULL,
PRIMARY KEY(PE_ident),
UNIQUE(EM_matricule),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE DM_demenagement(
DM_ident INT IDENTITY,
DM_date DATE,
PE_ident INT NOT NULL,
PRIMARY KEY(DM_ident),
FOREIGN KEY(PE_ident) REFERENCES CL_client(PE_ident)
);
CREATE TABLE VI_ville(
VI_ident INT IDENTITY,
VI_insee CHAR(5) NOT NULL,
VI_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(VI_ident)
);
CREATE TABLE CP_code_post(
CP_ident INT IDENTITY,
CP_code CHAR(5) NOT NULL,
PRIMARY KEY(CP_ident),
UNIQUE(CP_code)
);
CREATE TABLE FA_facture(
FA_ident INT IDENTITY,
FA_date DATE NOT NULL,
FA_HT DECIMAL(7,2) NOT NULL,
FA_TVA DECIMAL(7,2) NOT NULL,
FA_TTC DECIMAL(7,2) NOT NULL,
DM_ident INT NOT NULL,
PRIMARY KEY(FA_ident),
UNIQUE(DM_ident),
FOREIGN KEY(DM_ident) REFERENCES DM_demenagement(DM_ident)
);
CREATE TABLE YG_type_regl(
YG_ident INT IDENTITY,
YG_code CHAR(4) NOT NULL,
YG_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(YG_ident),
UNIQUE(YG_code)
);
CREATE TABLE AD_adresse(
AD_ident INT IDENTITY,
AD_ligne1 VARCHAR(38) NOT NULL,
AD_ligne2 VARCHAR(38) NOT NULL,
AD_ligne3 VARCHAR(38) NOT NULL,
AD_ligne4 VARCHAR(38) NOT NULL,
AD_ligne5 VARCHAR(38) NOT NULL,
CP_ident INT NOT NULL,
VI_ident INT NOT NULL,
PRIMARY KEY(AD_ident),
FOREIGN KEY(CP_ident) REFERENCES CP_code_post(CP_ident),
FOREIGN KEY(VI_ident) REFERENCES VI_ville(VI_ident)
);
CREATE TABLE RG_reglement(
FA_ident INT,
RG_ident SMALLINT,
RG_date DATE NOT NULL,
RG_mnt DECIMAL(7,2) NOT NULL,
YG_ident INT NOT NULL,
PE_ident INT NOT NULL,
PRIMARY KEY(FA_ident, RG_ident),
FOREIGN KEY(FA_ident) REFERENCES FA_facture(FA_ident),
FOREIGN KEY(YG_ident) REFERENCES YG_type_regl(YG_ident),
FOREIGN KEY(PE_ident) REFERENCES CL_client(PE_ident)
);
CREATE TABLE ET_etape(
DM_ident INT,
ET_seq SMALLINT,
ET_poids DECIMAL(5,2) NOT NULL,
ET_volume DECIMAL(5,2) NOT NULL,
ET_dth DATETIMEOFFSET NOT NULL,
AD_ident INT NOT NULL,
PRIMARY KEY(DM_ident, ET_seq),
FOREIGN KEY(DM_ident) REFERENCES DM_demenagement(DM_ident),
FOREIGN KEY(AD_ident) REFERENCES AD_adresse(AD_ident)
);
CREATE TABLE VP_ville_cp(
VI_ident INT,
CP_ident INT,
PRIMARY KEY(VI_ident, CP_ident),
FOREIGN KEY(VI_ident) REFERENCES VI_ville(VI_ident),
FOREIGN KEY(CP_ident) REFERENCES CP_code_post(CP_ident)
);
CREATE TABLE AF_affecter(
PE_ident INT,
DM_ident INT,
ET_seq SMALLINT,
PRIMARY KEY(PE_ident, DM_ident, ET_seq),
FOREIGN KEY(PE_ident) REFERENCES EM_employe(PE_ident),
FOREIGN KEY(DM_ident, ET_seq) REFERENCES ET_etape(DM_ident, ET_seq)
);
alter tabble AD_adresse
foreign key (VI_ident, CP_ident)
references VP_ville_cp (VI_ident, CP_ident) |
Partager