· 7 years ago · Oct 25, 2018, 06:22 AM
1-- phpMyAdmin SQL Dump
2-- version 4.0.10deb1ubuntu0.1
3-- http://www.phpmyadmin.net
4--
5-- Client: localhost
6-- Généré le: Jeu 25 Octobre 2018 à 08:18
7-- Version du serveur: 5.5.61-MariaDB-1ubuntu0.14.04.1
8-- Version de PHP: 7.1.15-1+ubuntu14.04.1+deb.sury.org+2
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8 */;
18
19--
20-- Base de données: `dao11u_Restaurant`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Structure de la table `Achat`
27--
28
29CREATE TABLE IF NOT EXISTS `Achat` (
30 `num_ticket` varchar(4) NOT NULL,
31 `num_prest` int(11) NOT NULL,
32 `nb_prest` int(11) NOT NULL,
33 PRIMARY KEY (`num_ticket`,`num_prest`),
34 KEY `num_ticket` (`num_ticket`),
35 KEY `num_prest` (`num_prest`)
36) ENGINE=InnoDB DEFAULT CHARSET=latin1;
37
38--
39-- Contenu de la table `Achat`
40--
41
42INSERT INTO `Achat` (`num_ticket`, `num_prest`, `nb_prest`) VALUES
43('TI1', 1, 1),
44('TI10', 1, 1),
45('TI10', 4, 3),
46('TI11', 1, 1),
47('TI11', 4, 1),
48('TI12', 1, 1),
49('TI12', 4, 3),
50('TI12', 5, 2),
51('TI13', 1, 1),
52('TI13', 2, 2),
53('TI14', 2, 1),
54('TI2', 1, 1),
55('TI2', 4, 1),
56('TI3', 1, 1),
57('TI3', 5, 2),
58('TI4', 1, 1),
59('TI4', 4, 1),
60('TI5', 2, 1),
61('TI6', 1, 1),
62('TI7', 1, 1),
63('TI8', 1, 1),
64('TI9', 5, 3);
65
66-- --------------------------------------------------------
67
68--
69-- Structure de la table `Categorie`
70--
71
72CREATE TABLE IF NOT EXISTS `Categorie` (
73 `num_categ` varchar(3) NOT NULL,
74 `lib_categ` varchar(100) NOT NULL,
75 PRIMARY KEY (`num_categ`)
76) ENGINE=InnoDB DEFAULT CHARSET=latin1;
77
78--
79-- Contenu de la table `Categorie`
80--
81
82INSERT INTO `Categorie` (`num_categ`, `lib_categ`) VALUES
83('T1', 'petits revenus'),
84('T2', 'gros revenus');
85
86-- --------------------------------------------------------
87
88--
89-- Structure de la table `Depot`
90--
91
92CREATE TABLE IF NOT EXISTS `Depot` (
93 `num_carte` varchar(3) NOT NULL,
94 `date_depot` date NOT NULL,
95 `montant` int(11) NOT NULL,
96 PRIMARY KEY (`num_carte`,`date_depot`),
97 KEY `num_carte` (`num_carte`)
98) ENGINE=InnoDB DEFAULT CHARSET=latin1;
99
100--
101-- Contenu de la table `Depot`
102--
103
104INSERT INTO `Depot` (`num_carte`, `date_depot`, `montant`) VALUES
105('C1', '2009-04-22', 10),
106('C1', '2009-05-24', 10),
107('C1', '2010-10-01', 15),
108('C1', '2011-03-03', 30),
109('C2', '2009-05-24', 15),
110('C2', '2010-10-01', 10),
111('C3', '2010-06-22', 20),
112('C4', '2010-10-01', 20),
113('C5', '2011-02-02', 20),
114('C5', '2011-03-02', 10),
115('C5', '2011-05-02', 15),
116('C6', '2012-04-01', 20);
117
118-- --------------------------------------------------------
119
120--
121-- Structure de la table `Prestation`
122--
123
124CREATE TABLE IF NOT EXISTS `Prestation` (
125 `num_prest` int(11) NOT NULL,
126 `type_prest` varchar(100) NOT NULL,
127 PRIMARY KEY (`num_prest`)
128) ENGINE=InnoDB DEFAULT CHARSET=latin1;
129
130--
131-- Contenu de la table `Prestation`
132--
133
134INSERT INTO `Prestation` (`num_prest`, `type_prest`) VALUES
135(1, 'repas normal'),
136(2, 'quart de vin rouge'),
137(3, 'bière'),
138(4, 'supplément frites'),
139(5, 'supplément Chantilly');
140
141-- --------------------------------------------------------
142
143--
144-- Structure de la table `Tarif`
145--
146
147CREATE TABLE IF NOT EXISTS `Tarif` (
148 `num_prest` int(11) NOT NULL,
149 `num_categ` varchar(3) NOT NULL,
150 `prix` float NOT NULL,
151 PRIMARY KEY (`num_prest`,`num_categ`),
152 KEY `num_prest` (`num_prest`),
153 KEY `num_categ` (`num_categ`)
154) ENGINE=InnoDB DEFAULT CHARSET=latin1;
155
156--
157-- Contenu de la table `Tarif`
158--
159
160INSERT INTO `Tarif` (`num_prest`, `num_categ`, `prix`) VALUES
161(1, 'T1', 4),
162(1, 'T2', 5),
163(2, 'T1', 1),
164(2, 'T2', 1),
165(3, 'T1', 1),
166(3, 'T2', 1),
167(4, 'T1', 0.5),
168(4, 'T2', 0.6),
169(5, 'T1', 0.5),
170(5, 'T2', 0.6);
171
172-- --------------------------------------------------------
173
174--
175-- Structure de la table `Ticket`
176--
177
178CREATE TABLE IF NOT EXISTS `Ticket` (
179 `num_ticket` varchar(4) NOT NULL,
180 `num_carte` varchar(3) NOT NULL,
181 `date_achat` date NOT NULL,
182 PRIMARY KEY (`num_ticket`),
183 KEY `num_ticket` (`num_ticket`),
184 KEY `num_carte` (`num_carte`)
185) ENGINE=InnoDB DEFAULT CHARSET=latin1;
186
187--
188-- Contenu de la table `Ticket`
189--
190
191INSERT INTO `Ticket` (`num_ticket`, `num_carte`, `date_achat`) VALUES
192('TI1', 'C1', '2009-04-22'),
193('TI10', 'C5', '2011-02-02'),
194('TI11', 'C1', '2011-03-02'),
195('TI12', 'C5', '2011-03-04'),
196('TI13', 'C6', '2012-04-01'),
197('TI14', 'C6', '2012-04-01'),
198('TI2', 'C1', '2009-04-24'),
199('TI3', 'C2', '2009-05-24'),
200('TI4', 'C1', '2009-05-26'),
201('TI5', 'C1', '2009-05-26'),
202('TI6', 'C3', '2010-06-22'),
203('TI7', 'C4', '2010-10-02'),
204('TI8', 'C2', '2010-10-02'),
205('TI9', 'C2', '2011-02-02');
206
207-- --------------------------------------------------------
208
209--
210-- Structure de la table `Usager`
211--
212
213CREATE TABLE IF NOT EXISTS `Usager` (
214 `num_carte` varchar(3) NOT NULL,
215 `nom` varchar(100) NOT NULL,
216 `num_categ` varchar(3) NOT NULL,
217 `mt_caution` int(11) NOT NULL,
218 `date_carte` date NOT NULL,
219 PRIMARY KEY (`num_carte`),
220 KEY `num_categ` (`num_categ`)
221) ENGINE=InnoDB DEFAULT CHARSET=latin1;
222
223--
224-- Contenu de la table `Usager`
225--
226
227INSERT INTO `Usager` (`num_carte`, `nom`, `num_categ`, `mt_caution`, `date_carte`) VALUES
228('C1', 'Pierre L.', 'T2', 5, '2009-04-19'),
229('C2', 'Zsuzsanna R.', 'T1', 5, '2009-05-20'),
230('C3', 'Michel A.', 'T2', 6, '2010-06-22'),
231('C4', 'Nathalie B.', 'T1', 6, '2010-09-30'),
232('C5', 'Michel G.', 'T2', 6, '2011-01-31'),
233('C6', 'Bernard H.', 'T2', 7, '2012-04-01');
234
235--
236-- Contraintes pour les tables exportées
237--
238
239--
240-- Contraintes pour la table `Achat`
241--
242ALTER TABLE `Achat`
243 ADD CONSTRAINT `Achat_ibfk_1` FOREIGN KEY (`num_ticket`) REFERENCES `Ticket` (`num_ticket`),
244 ADD CONSTRAINT `Achat_ibfk_2` FOREIGN KEY (`num_prest`) REFERENCES `Prestation` (`num_prest`);
245
246--
247-- Contraintes pour la table `Depot`
248--
249ALTER TABLE `Depot`
250 ADD CONSTRAINT `Depot_ibfk_1` FOREIGN KEY (`num_carte`) REFERENCES `Usager` (`num_carte`);
251
252--
253-- Contraintes pour la table `Tarif`
254--
255ALTER TABLE `Tarif`
256 ADD CONSTRAINT `Tarif_ibfk_1` FOREIGN KEY (`num_prest`) REFERENCES `Prestation` (`num_prest`),
257 ADD CONSTRAINT `Tarif_ibfk_2` FOREIGN KEY (`num_categ`) REFERENCES `Categorie` (`num_categ`);
258
259--
260-- Contraintes pour la table `Ticket`
261--
262ALTER TABLE `Ticket`
263 ADD CONSTRAINT `Ticket_ibfk_1` FOREIGN KEY (`num_carte`) REFERENCES `Usager` (`num_carte`);
264
265--
266-- Contraintes pour la table `Usager`
267--
268ALTER TABLE `Usager`
269 ADD CONSTRAINT `Usager_ibfk_1` FOREIGN KEY (`num_categ`) REFERENCES `Categorie` (`num_categ`);
270
271/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
272/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
273/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;