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
|
CREATE TABLE personnes (
Num_Pers SMALLINT UNSIGNED NOT NULL auto_increment,
Nom_Pers VARCHAR(50) NOT NULL,
Prenom_Pers VARCHAR(50) NOT NULL,
Adr1_Pers VARCHAR(70),
Adr2_Pers VARCHAR(50),
CP_Pers MEDIUMINT(5),
Ville_Pers VARCHAR(50),
NumFix_Pers CHAR(14),
NumPort_Pers CHAR(14),
NumUrgence_Pers CHAR(14),
AdrsMail_Pers VARCHAR(70),
DateN_Pers Date,
Intitule_Pers CHAR(3),
DateCotis_Pers date,
Com_Pers VARCHAR(250),
PRIMARY KEY (`Num_Pers`));
CREATE TABLE voyage(
Num_Voyage SMALLINT unsigned NOT NULL auto_increment,
DateArriv date,
DateDepar date,
Num_Pers SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (`Num_Voyage`),
FOREIGN KEY (`Num_Pers`) REFERENCES personnes(`Num_Pers`);
CREATE TABLE appartenir(
Num_Pers SMALLINT unsigned NOT NULL,
Num_Pays TINYINT unsigned NOT NULL,
FOREIGN KEY (`Num_Pays`) REFERENCES `pays`(`id`),
FOREIGN KEY (Num_Pers) REFERENCES personnes(Num_Pers) ON DELETE CASCADE
);
CREATE TABLE sejourner (
Num_Voyage SMALLINT UNSIGNED NOT NULL,
Num_Pers SMALLINT UNSIGNED NOT NULL,
FOREIGN KEY (`Num_Voyage`) REFERENCES voyage(`Num_Voyage`),
FOREIGN KEY (`Num_Pers`) REFERENCES personnes(`Num_Pers`)); |
Partager