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
| CREATE TABLE `t_ville` (
`id_ville` SMALLINT(5) UNSIGNED NOT NULL auto_increment,
`cp` VARCHAR(5) NOT NULL,
`nom` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id_ville`),
UNIQUE KEY (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_ville` (`id_ville`, `cp`, `nom`) VALUES
(1, "72000", "Le Mans"),
(2, "68130", "Altkirch"),
(3, "61000", "Alençon"),
(4, "50470", "La Glacerie"),
(5, "72190", "Coulaines"),
(6, "68640", "Riespach"),
(7, "75016", "Paris"),
(8, "72190", "Sargé-lès-le-Mans"),
(9, "92170", "Vanves");
CREATE TABLE `t_groupe` (
`id_groupe` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`nom` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id_groupe`),
UNIQUE KEY (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_groupe` (`id_groupe`, `nom`) VALUES
(1, "Groupe 1"),
(2, "Groupe 2"),
(3, "Groupe 3"),
(4, "Groupe 4");
CREATE TABLE `t_etablissement` (
`id_etablissement` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`nom` VARCHAR(100) NOT NULL,
`adresse` VARCHAR(255) NOT NULL,
`id_ville` SMALLINT(5) UNSIGNED NOT NULL,
`tel` VARCHAR(10) NOT NULL,
`fax` VARCHAR(10) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`id_groupe` SMALLINT(5) UNSIGNED,
PRIMARY KEY (`id_etablissement`),
INDEX (`nom`),
FOREIGN KEY (`id_groupe`) REFERENCES `t_groupe` (`id_groupe`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`id_ville`) REFERENCES `t_ville` (`id_ville`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_etablissement` (`id_etablissement`, `id_groupe`, `nom`, `adresse`, `id_ville`, `tel`, `fax`, `email`) VALUES
(1, NULL, "Etablissement 1 sans groupe", "31, rue Diderot", 8, "0102030405", "0102030405", "mail@mail.fr"),
(2, 1, "Etablissement 2 - groupe 1", "Rue Henri Cornat", 2, "0102030405", "0102030405", "mail@mail.fr"),
(3, 2, "Etablissement 3 - groupe 2", "ZI Quartier Plessier", 3, "0102030405", "0102030405", "mail@mail.fr"),
(4, 2, "Etablissement 4 - groupe 2", "48, rue 3ème Zouave", 3, "0102030405", "0102030405", "mail@mail.fr"),
(5, 2, "Etablissement 5 - groupe 2", "90, route de Feret", 7, "0102030405", "0102030405", "mail@mail.fr"),
(6, 2, "Etablissement 6 - groupe 2", "90, route de Feret", 7, "0102030405", "0102030405", "mail@mail.fr"),
(7, 2, "Etablissement 7 - groupe 2", "90, route de Feret", 7, "0102030405", "0102030405", "mail@mail.fr"),
(8, 2, "Etablissement 8 - groupe 2", "rue de Paris", 3, "0102030405", "0102030405", "mail@mail.fr"),
(9, 3, "Etablissement 9 - groupe 3", "12, rue du 18 juin 1940 - BP 21", 2, "0102030405", "0102030405", "mail@mail.fr"),
(10, 4, "Etablissement 10 - groupe 4", "24, rue de la Paix", 5, "0102030405", "0102030405", "mail@mail.fr"),
(11, 4, "Etablissement 11 - groupe 4", "Rue du Ponceau", 5, "0102030405", "0102030405", "mail@mail.fr"),
(12, NULL, "Etablissement 12 sans groupe", "17, rue de Priori", 4, "0102030405", "0102030405", "mail@mail.fr"),
(13, NULL, "Etablissement 13 sans groupe", "6, rue Eugene Manuel", 9, "0102030405", "0102030405", "mail@mail.fr"),
(14, 2, "Etablissement 14 - groupe 2", "ZI Quartier Plessier", 3, "0102030405", "0102030405", "mail@mail.fr"),
(15, 2, "Etablissement 15 - groupe 2", "ZI Quartier Plessier", 3, "0102030405", "0102030405", "mail@mail.fr");
CREATE TABLE `t_personne` (
`id_personne` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`civilite` ENUM("","Mr","Mme","Mlle") NOT NULL,
`nom` VARCHAR(255) NOT NULL,
`prenom` VARCHAR(255) NOT NULL,
`tel` VARCHAR(10) NOT NULL,
`fax` VARCHAR(10) NOT NULL,
`portable` VARCHAR(10) NOT NULL,
`email` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id_personne`),
KEY (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_personne` (`id_personne`, `civilite`, `nom`, `prenom`, `tel`, `fax`, `portable`, `email`)
VALUES
(1, "Mr", "AAAAA", "aaaaaaaa", "", "", "0606060606", "directeur.general@mariepire.asso.fr"),
(2, "Mr", "BBBB", "bbbbbbb", "0606060606", "0606060606", "0606060606", ""),
(3, "Mme", "CCCCCCC", "ccccccccccc", "", "", "0606060606", "sdsds@wanadoo.fr"),
(4, "Mme", "DDDDDDD", "ddd", "", "", "", ""),
(5, "Mr", "EEEEEEE", "", "", "", "", ""),
(6, "Mr", "FFF", "ffff", "", "", "", ""),
(7, "Mlle", "GGGG", "gggggg", "", "", "", "sdsds@wanadoo.fr"),
(8, "Mr", "HHHH", "hhhhhhhhhhhhhh", "0606060606", "", "", "sdsds@wanadoo.fr"),
(9, "Mr", "II", "iiiiiiii", "0606060606", "", "", "sdsds@wanadoo.fr"),
(10, "Mr", "JJJJJ", "jjjjjjjjjjj", "", "", "", "sdsds@wanadoo.fr");
CREATE TABLE `t_appartenance` (
`id_personne` SMALLINT(5) UNSIGNED NOT NULL,
`id_etablissement` SMALLINT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`id_personne`, `id_etablissement`),
FOREIGN KEY (`id_personne`) REFERENCES `t_personne` (`id_personne`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`id_etablissement`) REFERENCES `t_etablissement` (`id_etablissement`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_appartenance` (`id_personne`, `id_etablissement`)
VALUES
(1, 3),
(2, 9),
(2, 13),
(3, 9),
(4, 10),
(5, 11),
(7, 3),
(8, 5),
(9, 4),
(9, 14),
(9, 15),
(10, 14); |
Partager