YfMxsxFP

1DROP TABLE IF EXISTS `alert`;
2CREATE TABLE IF NOT EXISTS `alert` (
3  `alert_ID` int(11) NOT NULL AUTO_INCREMENT,
4  `client_ID` int(11) NOT NULL,
5  `bike_ID` int(11) NOT NULL,
6  `alert_status` varchar(200) DEFAULT NULL,
7  `alert_dateTime` datetime DEFAULT NULL,
8  PRIMARY KEY (`alert_ID`,`client_ID`,`bike_ID`),
9  KEY `alert_client_ID_FK` (`client_ID`),
10  KEY `alert_bike_ID_FK` (`bike_ID`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12
13-- --------------------------------------------------------
14
15--
16-- Structure de la table `bike`
17--
18
19DROP TABLE IF EXISTS `bike`;
20CREATE TABLE IF NOT EXISTS `bike` (
21  `bike_ID` int(11) NOT NULL AUTO_INCREMENT,
22  `bike_lat` float(8,6) DEFAULT NULL,
23  `bike_lng` float(8,6) DEFAULT NULL,
24  `bike_isMoving` tinyint(1) DEFAULT NULL,
25  `bike_powerLevel` int(11) DEFAULT NULL,
26  `bike_usable` tinyint(1) DEFAULT NULL,
27  `bike_runningCost` int(11) DEFAULT NULL,
28  PRIMARY KEY (`bike_ID`)
29) ENGINE=InnoDB DEFAULT CHARSET=utf8;
30
31-- --------------------------------------------------------
32
33--
34-- Structure de la table `bill`
35--
36
37DROP TABLE IF EXISTS `bill`;
38CREATE TABLE IF NOT EXISTS `bill` (
39  `bill_ID` int(11) NOT NULL AUTO_INCREMENT,
40  `client_ID` int(11) NOT NULL,
41  `bill_date` date DEFAULT NULL,
42  `bill_cost` int(11) DEFAULT NULL,
43  `bill_isPaid` tinyint(1) DEFAULT NULL,
44  PRIMARY KEY (`bill_ID`,`client_ID`),
45  KEY `bill_client_ID_FK` (`client_ID`)
46) ENGINE=InnoDB DEFAULT CHARSET=utf8;
47
48-- --------------------------------------------------------
49
50--
51-- Structure de la table `client`
52--
53
54DROP TABLE IF EXISTS `client`;
55CREATE TABLE IF NOT EXISTS `client` (
56  `client_ID` int(11) NOT NULL AUTO_INCREMENT,
57  `client_lastName` varchar(30) NOT NULL,
58  `client_name` varchar(30) NOT NULL,
59  `client_sex` varchar(1) DEFAULT NULL,
60  `client_birthDate` date DEFAULT NULL,
61  `client_password` varchar(30) NOT NULL,
62  `client_mail` varchar(60) NOT NULL,
63  `bill_ID` int(11) NOT NULL,
64  PRIMARY KEY (`client_ID`,`bill_ID`),
65  KEY `client_bill_ID_FK` (`bill_ID`)
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
68-- --------------------------------------------------------
69
70--
71-- Structure de la table `drive`
72--
73
74DROP TABLE IF EXISTS `drive`;
75CREATE TABLE IF NOT EXISTS `drive` (
76  `drive_ID` int(11) NOT NULL AUTO_INCREMENT,
77  `client_ID` int(11) NOT NULL,
78  `bike_ID` int(11) NOT NULL,
79  `drive_distance` int(11) DEFAULT NULL,
80  `drive_activationTime` datetime DEFAULT NULL,
81  `drive_deactivationTime` datetime DEFAULT NULL,
82  `drive_cost` int(11) DEFAULT NULL,
83  PRIMARY KEY (`drive_ID`,`client_ID`,`bike_ID`),
84  KEY `drive_client_ID_FK` (`client_ID`),
85  KEY `drive_bike_ID_FK` (`bike_ID`)
86) ENGINE=InnoDB DEFAULT CHARSET=utf8;
87
88-- --------------------------------------------------------
89
90--
91-- Structure de la table `users`
92--
93
94DROP TABLE IF EXISTS `users`;
95CREATE TABLE IF NOT EXISTS `users` (
96  `user_ID` int(11) NOT NULL AUTO_INCREMENT,
97  `user_lastName` varchar(30) NOT NULL,
98  `user_name` varchar(30) NOT NULL,
99  `user_sex` varchar(1) DEFAULT NULL,
100  `user_birthDate` date DEFAULT NULL,
101  `user_password` varchar(30) NOT NULL,
102  `user_mail` varchar(60) NOT NULL,
103  `user_rights` enum('user','admin','tech','finance','commerce') DEFAULT 'user',
104  PRIMARY KEY (`user_ID`)
105) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
106
107--
108-- Déchargement des données de la table `users`
109--
110
111INSERT INTO `users` (`user_ID`, `user_lastName`, `user_name`, `user_sex`, `user_birthDate`, `user_password`, `user_mail`, `user_rights`) VALUES
112(1, 'Johnny', 'test', 'H', '1998-07-23', 'test', 'test@mail.fr', 'user');
113
114--
115-- Contraintes pour les tables déchargées
116--
117
118--
119-- Contraintes pour la table `alert`
120--
121ALTER TABLE `alert`
122  ADD CONSTRAINT `alert_bike_ID_FK` FOREIGN KEY (`bike_ID`) REFERENCES `bike` (`bike_ID`),
123  ADD CONSTRAINT `alert_client_ID_FK` FOREIGN KEY (`client_ID`) REFERENCES `client` (`client_ID`);
124
125--
126-- Contraintes pour la table `bill`
127--
128ALTER TABLE `bill`
129  ADD CONSTRAINT `bill_client_ID_FK` FOREIGN KEY (`client_ID`) REFERENCES `client` (`client_ID`);
130
131--
132-- Contraintes pour la table `client`
133--
134ALTER TABLE `client`
135  ADD CONSTRAINT `client_bill_ID_FK` FOREIGN KEY (`bill_ID`) REFERENCES `bill` (`bill_ID`);
136
137--
138-- Contraintes pour la table `drive`
139--
140ALTER TABLE `drive`
141  ADD CONSTRAINT `drive_bike_ID_FK` FOREIGN KEY (`bike_ID`) REFERENCES `bike` (`bike_ID`),
142  ADD CONSTRAINT `drive_client_ID_FK` FOREIGN KEY (`client_ID`) REFERENCES `client` (`client_ID`);
143COMMIT;