6R9zXENA

1-- phpMyAdmin SQL Dump
2-- version 4.5.2
3-- http://www.phpmyadmin.net
4--
5-- Host: 127.0.0.1
6-- Generation Time: Jan 27, 2017 at 12:49 PM
7-- Server version: 5.7.9
8-- PHP Version: 5.6.16
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 utf8mb4 */;
18
19--
20-- Database: `ip_hw`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Table structure for table `ip_halls`
27--
28
29DROP TABLE IF EXISTS `ip_halls`;
30CREATE TABLE IF NOT EXISTS `ip_halls` (
31  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID',
32  `theather_id` int(11) NOT NULL COMMENT 'The FK for theater ID',
33  `seat_chart_id` int(11) NOT NULL COMMENT 'The FK for seat chart',
34  `max_seats` int(4) NOT NULL COMMENT 'The amount of total seats',
35  `local_number` int(2) NOT NULL COMMENT 'The number of the hall in the local theater(ex.  hall 1)',
36  `is_vip` enum('0','1') NOT NULL COMMENT 'Shows if it is VIP hall',
37  `rows` int(8) NOT NULL COMMENT 'the number of rows in the hall',
38  PRIMARY KEY (`id`),
39  KEY `theather_id` (`theather_id`),
40  KEY `seat_chart_id` (`seat_chart_id`)
41) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The list for all the halls';
42
43-- --------------------------------------------------------
44
45--
46-- Table structure for table `ip_movies`
47--
48
49DROP TABLE IF EXISTS `ip_movies`;
50CREATE TABLE IF NOT EXISTS `ip_movies` (
51  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The Unique ID(PK)',
52  `movie_name` varchar(64) NOT NULL COMMENT 'The name of the movie',
53  `time_start` time NOT NULL COMMENT 'The starting time of the movie',
54  `time_end` time NOT NULL COMMENT 'The ending time of the movie',
55  `date` date NOT NULL COMMENT 'the date of the movie',
56  `theather_id` int(11) NOT NULL COMMENT 'The FK for for the theater, where the event is held',
57  `hall_id` int(11) NOT NULL COMMENT 'The FK for the hall, where the event is held',
58  PRIMARY KEY (`id`),
59  KEY `hall_id` (`hall_id`),
60  KEY `theather_id` (`theather_id`)
61) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for all the movies in the movie theaters';
62
63-- --------------------------------------------------------
64
65--
66-- Table structure for table `ip_orders`
67--
68
69DROP TABLE IF EXISTS `ip_orders`;
70CREATE TABLE IF NOT EXISTS `ip_orders` (
71  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The Unique ID(PK)',
72  `date_received` datetime NOT NULL COMMENT 'The date on which the order is received',
73  `date_confirmed` datetime NOT NULL COMMENT 'The date on which the order is confirmed as payed',
74  `user_id` int(11) NOT NULL COMMENT 'The FK for the user',
75  `order_type` int(11) NOT NULL COMMENT 'The FK for the order type',
76  `unique_token` int(64) NOT NULL COMMENT 'The unique token for the registration',
77  `price_category` int(4) NOT NULL COMMENT 'The FK for the price category',
78  `tickets_number` int(4) NOT NULL COMMENT 'The amount of tickets bought',
79  `payment_method_id` int(11) NOT NULL COMMENT 'The FK for the payment method',
80  `movie_id` int(11) NOT NULL COMMENT 'The FK for the movie',
81  `total_price` int(8) NOT NULL COMMENT 'The total price of the order',
82  PRIMARY KEY (`id`),
83  KEY `customer_id` (`user_id`),
84  KEY `payment_method_id` (`payment_method_id`),
85  KEY `order_type` (`order_type`),
86  KEY `price_category` (`price_category`),
87  KEY `movie_id` (`movie_id`)
88) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity, used for the orders.';
89
90-- --------------------------------------------------------
91
92--
93-- Table structure for table `ip_order_types`
94--
95
96DROP TABLE IF EXISTS `ip_order_types`;
97CREATE TABLE IF NOT EXISTS `ip_order_types` (
98  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
99  `order_type` varchar(8) NOT NULL COMMENT 'The order type',
100  PRIMARY KEY (`id`)
101) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The helping table for the order types';
102
103-- --------------------------------------------------------
104
105--
106-- Table structure for table `ip_payment_methods`
107--
108
109DROP TABLE IF EXISTS `ip_payment_methods`;
110CREATE TABLE IF NOT EXISTS `ip_payment_methods` (
111  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
112  `payment_method` int(11) NOT NULL COMMENT 'The payment method name',
113  PRIMARY KEY (`id`)
114) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for payment methods';
115
116-- --------------------------------------------------------
117
118--
119-- Table structure for table `ip_price_category`
120--
121
122DROP TABLE IF EXISTS `ip_price_category`;
123CREATE TABLE IF NOT EXISTS `ip_price_category` (
124  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
125  `price_category_name` varchar(16) NOT NULL COMMENT 'The name of the price category',
126  `age_requirements` int(2) NOT NULL COMMENT 'The minimum/maximum age requirement for this category',
127  `document_requirements` varchar(256) NOT NULL COMMENT 'The documents required for this price category(ex. Student''s Card)',
128  `theather_id` int(11) NOT NULL COMMENT 'The FK for the theater',
129  `price` int(4) NOT NULL COMMENT 'The price per single ticket in this price category',
130  PRIMARY KEY (`id`),
131  KEY `theather_id` (`theather_id`)
132) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for the list of price categories and their properties';
133
134-- --------------------------------------------------------
135
136--
137-- Table structure for table `ip_program`
138--
139
140DROP TABLE IF EXISTS `ip_program`;
141CREATE TABLE IF NOT EXISTS `ip_program` (
142  `id` int(11) NOT NULL COMMENT 'The unique ID(PK)',
143  `date` date NOT NULL COMMENT 'The date of the p',
144  `movie_id` int(255) NOT NULL COMMENT 'The list of all the movies for the date of the program',
145  `is_weekend` enum('0','1') NOT NULL COMMENT 'Check if it is weekend',
146  `has_price_reduction` enum('0','1') NOT NULL COMMENT 'Check if there is any price reduction for all of the price categories',
147  `movies_count` int(64) NOT NULL COMMENT 'The amount of movies for the given date',
148  KEY `movie_id` (`movie_id`)
149) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The program for the cinemas';
150
151-- --------------------------------------------------------
152
153--
154-- Table structure for table `ip_reservations`
155--
156
157DROP TABLE IF EXISTS `ip_reservations`;
158CREATE TABLE IF NOT EXISTS `ip_reservations` (
159  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
160  `date_reserved` date NOT NULL COMMENT 'The date of the reserevation',
161  `expiration_hour` datetime NOT NULL COMMENT 'The hour and date for the expiration of the reservation',
162  `user_id` int(11) NOT NULL COMMENT 'The FK for the users',
163  `theather_id` int(11) NOT NULL COMMENT 'The FK for the theaters',
164  `total_price` int(8) NOT NULL COMMENT 'The total price of the resercation',
165  `number_of_tickets` int(4) NOT NULL COMMENT 'The number of tickets in the reservation',
166  `price_category_id` int(11) NOT NULL COMMENT 'The FK for the price category',
167  PRIMARY KEY (`id`),
168  KEY `customer_id` (`user_id`),
169  KEY `theather_id` (`theather_id`),
170  KEY `customer_id_2` (`user_id`),
171  KEY `price_category_id` (`price_category_id`)
172) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The list for all the non-payed reservations(when a reservation is payed it is transfered to ip_orders)';
173
174-- --------------------------------------------------------
175
176--
177-- Table structure for table `ip_roles`
178--
179
180DROP TABLE IF EXISTS `ip_roles`;
181CREATE TABLE IF NOT EXISTS `ip_roles` (
182  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
183  `role_name` varchar(255) NOT NULL COMMENT 'The name of the role',
184  `user_id` int(11) NOT NULL COMMENT 'The FK for the user',
185  `price_reduction` int(4) NOT NULL COMMENT 'The price reduction in percents',
186  `is_default` enum('1','0') NOT NULL COMMENT 'Check if it is the default role for an user',
187  `is_staff` enum('0','1') NOT NULL COMMENT 'Check if the user is member of the Staff',
188  `access_level` int(4) NOT NULL COMMENT 'The access level of the users(0 if non-staff)',
189  PRIMARY KEY (`id`),
190  KEY `user_id` (`user_id`)
191) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for all the roles';
192
193-- --------------------------------------------------------
194
195--
196-- Table structure for table `ip_seat_charts`
197--
198
199DROP TABLE IF EXISTS `ip_seat_charts`;
200CREATE TABLE IF NOT EXISTS `ip_seat_charts` (
201  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
202  `hall_id` int(11) NOT NULL COMMENT 'The FK for the hall id',
203  `row` int(2) NOT NULL COMMENT 'The number of row',
204  `number` int(2) NOT NULL COMMENT 'The number of the seat',
205  `is_taken` enum('0','1') NOT NULL COMMENT 'Check if is taken',
206  `is_reserved` enum('0','1') NOT NULL COMMENT 'Check if is reserved',
207  PRIMARY KEY (`id`),
208  KEY `hall_id` (`hall_id`)
209) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for the seat charts';
210
211-- --------------------------------------------------------
212
213--
214-- Table structure for table `ip_theathers`
215--
216
217DROP TABLE IF EXISTS `ip_theathers`;
218CREATE TABLE IF NOT EXISTS `ip_theathers` (
219  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
220  `name` varchar(64) NOT NULL COMMENT 'The name of the Theater',
221  `address` varchar(128) NOT NULL COMMENT 'The address of the theater',
222  `halls` int(4) NOT NULL COMMENT 'The amount of halls',
223  `max_seats` int(8) NOT NULL COMMENT 'The amount of people, that can watch movie at the same time',
224  `city` varchar(64) NOT NULL COMMENT 'The city, where it is located',
225  PRIMARY KEY (`id`)
226) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for the theaters';
227
228-- --------------------------------------------------------
229
230--
231-- Table structure for table `ip_users`
232--
233
234DROP TABLE IF EXISTS `ip_users`;
235CREATE TABLE IF NOT EXISTS `ip_users` (
236  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID(PK)',
237  `first_name` varchar(64) NOT NULL COMMENT 'The first name of the user',
238  `last_name` varchar(64) NOT NULL COMMENT 'The last name of the user',
239  `pid` varchar(10) NOT NULL COMMENT 'The pid(EGN) of the user',
240  `date_registered` date NOT NULL COMMENT 'The date, on which he registered',
241  PRIMARY KEY (`id`)
242) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The entity for the users';
243
244--
245-- Constraints for dumped tables
246--
247
248--
249-- Constraints for table `ip_halls`
250--
251ALTER TABLE `ip_halls`
252  ADD CONSTRAINT `ip_halls_ibfk_1` FOREIGN KEY (`theather_id`) REFERENCES `ip_theathers` (`id`),
253  ADD CONSTRAINT `ip_halls_ibfk_2` FOREIGN KEY (`seat_chart_id`) REFERENCES `ip_seat_charts` (`id`);
254
255--
256-- Constraints for table `ip_movies`
257--
258ALTER TABLE `ip_movies`
259  ADD CONSTRAINT `ip_movies_ibfk_1` FOREIGN KEY (`hall_id`) REFERENCES `ip_halls` (`id`),
260  ADD CONSTRAINT `ip_movies_ibfk_2` FOREIGN KEY (`theather_id`) REFERENCES `ip_theathers` (`id`);
261
262--
263-- Constraints for table `ip_orders`
264--
265ALTER TABLE `ip_orders`
266  ADD CONSTRAINT `ip_orders_ibfk_1` FOREIGN KEY (`order_type`) REFERENCES `ip_order_types` (`id`),
267  ADD CONSTRAINT `ip_orders_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `ip_users` (`id`),
268  ADD CONSTRAINT `ip_orders_ibfk_3` FOREIGN KEY (`payment_method_id`) REFERENCES `ip_payment_methods` (`id`),
269  ADD CONSTRAINT `ip_orders_ibfk_4` FOREIGN KEY (`price_category`) REFERENCES `ip_price_category` (`id`),
270  ADD CONSTRAINT `ip_orders_ibfk_5` FOREIGN KEY (`movie_id`) REFERENCES `ip_movies` (`id`);
271
272--
273-- Constraints for table `ip_price_category`
274--
275ALTER TABLE `ip_price_category`
276  ADD CONSTRAINT `ip_price_category_ibfk_1` FOREIGN KEY (`theather_id`) REFERENCES `ip_theathers` (`id`);
277
278--
279-- Constraints for table `ip_program`
280--
281ALTER TABLE `ip_program`
282  ADD CONSTRAINT `ip_program_ibfk_1` FOREIGN KEY (`movie_id`) REFERENCES `ip_movies` (`id`);
283
284--
285-- Constraints for table `ip_reservations`
286--
287ALTER TABLE `ip_reservations`
288  ADD CONSTRAINT `ip_reservations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `ip_users` (`id`);
289
290--
291-- Constraints for table `ip_roles`
292--
293ALTER TABLE `ip_roles`
294  ADD CONSTRAINT `ip_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `ip_users` (`id`);
295
296--
297-- Constraints for table `ip_seat_charts`
298--
299ALTER TABLE `ip_seat_charts`
300  ADD CONSTRAINT `ip_seat_charts_ibfk_1` FOREIGN KEY (`hall_id`) REFERENCES `ip_halls` (`id`);
301
302/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
303/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
304/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;