Ff7PSL7V

1CREATE DATABASE IF NOT EXISTS stackoverflow_29333110;
2USE stackoverflow_29333110;
3
4DROP TABLE IF EXISTS `participer`;
5DROP TABLE IF EXISTS `versionquestionnaire`;
6DROP TABLE IF EXISTS `questionnaire`;
7DROP TABLE IF EXISTS `utilisateurs`;
8
9CREATE TABLE IF NOT EXISTS `participer` (
10  `statut_Invitation` tinyint(1) NOT NULL,
11  `NumVersion` int(11) NOT NULL,
12  `InviteCode` varchar(255) NOT NULL,
13  `IdQuest` int(11) NOT NULL,
14  PRIMARY KEY (`NumVersion`,`InviteCode`,`IdQuest`),
15  KEY `FK_Participer_NumVersion` (`NumVersion`),
16  KEY `FK_Participer_IdQuest` (`IdQuest`),
17  KEY `FK_Participer_InviteCode` (`InviteCode`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8;
19
20
21
22INSERT INTO `participer` (`statut_Invitation`, `NumVersion`, `InviteCode`, `IdQuest`) VALUES
23(0, 2, '2548', 1),
24(0, 2, '8742', 1);
25
26
27
28CREATE TABLE IF NOT EXISTS `questionnaire` (
29  `IdQuest` int(11) NOT NULL AUTO_INCREMENT,
30  `Nom` varchar(256) DEFAULT NULL,
31  `DateCreation` date,
32  PRIMARY KEY (`IdQuest`)
33) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
34
35
36INSERT INTO `questionnaire` (`IdQuest`, `Nom`, `DateCreation`) VALUES
37(1, 'Parions Sport', '2015-03-23'),
38(2, 'GPS', '2015-03-23');
39
40CREATE TABLE IF NOT EXISTS `utilisateurs` (
41  `InviteCode` varchar(255) NOT NULL,
42  `Email` varchar(25) NOT NULL,
43  `DateNaissance` date DEFAULT NULL,
44  `Ville` varchar(25) DEFAULT NULL,
45  PRIMARY KEY (`InviteCode`)
46) ENGINE=InnoDB DEFAULT CHARSET=utf8;
47
48
49
50INSERT INTO `utilisateurs` (`InviteCode`, `Email`, `DateNaissance`, `Ville`) VALUES
51('1235314', 'bocchi@gmail.com', NULL, NULL),
52('2548', 'bocchiTEST@gmail.com', NULL, NULL),
53('337752493652424404824466004444846460026', 'dylan@gmail.com', NULL, NULL),
54('53131172170670664482420846024208824402', 'dylan2@gmail.com', NULL, NULL),
55('5446544', 'lool', NULL, NULL),
56('8742', 'max@gmail.com', NULL, NULL);
57
58-- --------------------------------------------------------
59
60
61
62CREATE TABLE IF NOT EXISTS `versionquestionnaire` (
63  `NumVersion` int(11) NOT NULL,
64  `DateExpiration` date DEFAULT NULL,
65  `SommeNote` float DEFAULT NULL,
66  `NbReponses` int(11) DEFAULT NULL,
67  `IdQuest` int(11) NOT NULL,
68  PRIMARY KEY (`NumVersion`,`IdQuest`),
69  KEY `FK_VersionQuestionnaire_IdQuest` (`IdQuest`)
70) ENGINE=InnoDB DEFAULT CHARSET=utf8;
71
72INSERT INTO `versionquestionnaire` (`NumVersion`, `DateExpiration`, `SommeNote`, `NbReponses`, `IdQuest`) VALUES
73(1, '2015-03-26', 120, 2, 1),
74(1, '2015-03-30', 100, 1, 2),
75(2, '2015-03-26', 210, 3, 1),
76(2, '2015-03-30', 300, 4, 2);
77
78
79ALTER TABLE `participer`
80  ADD CONSTRAINT `FK_Participer_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `versionquestionnaire` (`IdQuest`),
81  ADD CONSTRAINT `FK_Participer_InviteCode` FOREIGN KEY (`InviteCode`) REFERENCES `utilisateurs` (`InviteCode`),
82  ADD CONSTRAINT `FK_Participer_NumVersion` FOREIGN KEY (`NumVersion`) REFERENCES `versionquestionnaire` (`NumVersion`);
83
84
85ALTER TABLE `versionquestionnaire`
86  ADD CONSTRAINT `FK_VersionQuestionnaire_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `questionnaire` (`IdQuest`);
87
88DELETE FROM `versionquestionnaire` WHERE `versionquestionnaire`.`NumVersion` = 1 AND `versionquestionnaire`.`IdQuest` = 1;
89
90DROP DATABASE stackoverflow_29333110;