--
-- proposition de solution TP sql - emploi du temps (L SPI)
-- 					Patrick TRAU 


--
-- Structure de la table `t_grp`
--

CREATE TABLE `t_grp` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `nom` varchar(16) NOT NULL DEFAULT '',
  `descr` varchar(50) DEFAULT NULL
);

--
-- Contenu de la table `t_grp`
--

INSERT INTO `t_grp` (`id`, `nom`, `descr`) VALUES
(1, 'LSPI3', 'Licence SPI 3eme annee'),
(2, 'L3FA', 'Lspi 3 Franco-allemand'),
(4, 'LSPI3-TP1', 'TP 1 Lspi3'),
(5, 'LSPI3-TP2', 'TP 2 Lspi3'),
(6, 'LSPI3-TP3', 'TP 3 Lspi3'),
(7, 'LSPI3-TD1', 'TD 1 Lspi3'),
(8, 'LSPI3-TD2', 'TD 2 Lspi3'),
(9, 'MPAIP ME1', 'Master PAIP mecatro 1'),
(10, 'MPAIP MNI1', 'MasterPAIP Meca num en inge');

-- --------------------------------------------------------

--
-- Structure de la table `t_mat`
--

CREATE TABLE `t_mat` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `nom` varchar(16) NOT NULL DEFAULT '',
  `descr` varchar(50) DEFAULT NULL
);

--
-- Contenu de la table `t_mat`
--

INSERT INTO `t_mat` (`id`, `nom`, `descr`) VALUES
(1, 'meca', 'mecanique des solides (donc tres dur)'),
(2, 'dynamique TP', 'comment ca tombe'),
(3, 'dynamique CM', 'cours Magistral'),
(4, 'PBD TP', 'TP infinissable'),
(5, 'PBD CM', 'spectacle epuisant');

-- --------------------------------------------------------

--
-- Structure de la table `t_prf`
--

CREATE TABLE `t_prf` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `nom` varchar(16) NOT NULL DEFAULT '',
  `prenom` varchar(16) NOT NULL DEFAULT '',
  `mail` varchar(30) DEFAULT NULL
);

--
-- Contenu de la table `t_prf`
--

INSERT INTO `t_prf` (`id`, `nom`, `prenom`, `mail`) VALUES
(1, 'einstein', 'albert', 'e.mc2@nasa.fr'),
(2, 'newton', 'isaac', 'apple.tree@ety.com'),
(3, 'trouvetou', 'geo', 'gt@disney.com');

-- --------------------------------------------------------

--
-- Structure de la table `t_sal`
--

CREATE TABLE `t_sal` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `nom` varchar(20) NOT NULL DEFAULT '',
  `descr` varchar(50) DEFAULT NULL,
  `places` int(11) DEFAULT NULL
);

--
-- Contenu de la table `t_sal`
--

INSERT INTO `t_sal` (`id`, `nom`, `descr`, `places`) VALUES
(1, 'Fresnel', 'amphitheatre', 200),
(2, 's115', 'cours', 40),
(3, 's10A', 'TP info', 20),
(4, 's212', 'TP info', 44),
(5, 'projets HALL', 'projets Hall techno Illkirch', 30);


-- --------------------------------------------------------

--
-- Structure de la table `t_edt`
--

CREATE TABLE `t_edt` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_pr` int(11) NOT NULL DEFAULT '0',
  `id_sa` int(11) NOT NULL DEFAULT '0',
  `id_ma` int(11) NOT NULL DEFAULT '0',
  `id_gr` int(11) NOT NULL DEFAULT '0',
  `moment` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `duree` float NOT NULL
);
--
-- relations
--
ALTER TABLE `t_edt` ADD FOREIGN KEY(`id_pr`) REFERENCES `t_prf`(`id`);
ALTER TABLE `t_edt` ADD FOREIGN KEY(`id_sa`) REFERENCES `t_sal`(`id`);
ALTER TABLE `t_edt` ADD FOREIGN KEY(`id_ma`) REFERENCES `t_mat`(`id`);
ALTER TABLE `t_edt` ADD FOREIGN KEY(`id_gr`) REFERENCES `t_grp`(`id`);

--
-- Contenu de la table `t_edt`
--

INSERT INTO `t_edt` (`id`, `id_pr`, `id_sa`, `id_ma`, `id_gr`, `moment`, `duree`) VALUES
(1, 1, 1, 1, 1, '2018-04-05 08:00:00', 2),
(2, 1, 4, 2, 4, '2018-04-05 10:00:00', 2),
(3, 2, 3, 2, 5, '2018-04-05 10:00:00', 2),
(4, 1, 1, 1, 1, '2018-04-06 08:00:00', 2),
(5, 1, 4, 2, 4, '2018-04-06 10:00:00', 2),
(6, 2, 3, 2, 5, '2018-04-06 10:00:00', 2);

-- --------------------------------------------------------