--
-- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 6.3.358.0
-- Домашняя страница продукта: http://www.devart.com/ru/dbforge/mysql/studio
-- Дата скрипта: 27.01.2016 1:03:08
-- Версия сервера: 5.5.5-10.1.8-MariaDB
-- Версия клиента: 4.1
--


USE tickets;

CREATE TABLE halls (
  id int(11) NOT NULL AUTO_INCREMENT,
  halls_nm varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 3
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'залы, театры';

CREATE TABLE actions (
  id int(11) NOT NULL AUTO_INCREMENT,
  action_nm varchar(255) DEFAULT NULL,
  halls_id int(11) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_actions_halls_id FOREIGN KEY (halls_id)
  REFERENCES halls (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 5
AVG_ROW_LENGTH = 4096
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'мероприятие';

CREATE TABLE place (
  id int(11) NOT NULL AUTO_INCREMENT,
  ryad int(11) DEFAULT NULL,
  mesto int(11) DEFAULT NULL,
  rang int(11) DEFAULT NULL,
  hall_id int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_place_halls_id FOREIGN KEY (hall_id)
  REFERENCES halls (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 9
AVG_ROW_LENGTH = 2048
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'места';

/* таблица сеансов, выступлений, содержащая дату время, выступлений может несколько в день*/
CREATE TABLE action_date (
  id int(11) NOT NULL AUTO_INCREMENT,
  dt datetime DEFAULT NULL,
  action_id int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_action_date_actions_id FOREIGN KEY (action_id)
  REFERENCES actions (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 6
AVG_ROW_LENGTH = 3276
CHARACTER SET utf8
COLLATE utf8_general_ci;

/* таблица продаж*/
CREATE TABLE stock (
  id int(11) NOT NULL AUTO_INCREMENT,
  act_dt_id int(11) NOT NULL,
  hall_id int(11) NOT NULL, /* избыточность устраняется по задаче*/
  place_id int(11) NOT NULL,
  PRIMARY KEY (id),
  INDEX FK_stock_halls_id (hall_id),
  INDEX FK_stock_place_id (place_id),
  CONSTRAINT FK_stock_action_date_id FOREIGN KEY (act_dt_id)
  REFERENCES action_date (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 2
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'продажи';

--
-- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 6.3.358.0
-- Домашняя страница продукта: http://www.devart.com/ru/dbforge/mysql/studio
-- Дата скрипта: 27.01.2016 1:11:23
-- Версия сервера: 5.5.5-10.1.8-MariaDB
-- Версия клиента: 4.1
--


SET NAMES 'utf8';

INSERT INTO tickets.actions(id, action_nm, halls_id) VALUES
(1, 'Концерт Газманова', 1);
INSERT INTO tickets.actions(id, action_nm, halls_id) VALUES
(2, 'Выступление баянистов', 1);
INSERT INTO tickets.actions(id, action_nm, halls_id) VALUES
(3, 'Смотр строя и песни', 2);
INSERT INTO tickets.actions(id, action_nm, halls_id) VALUES
(4, 'Постановка Оловянный солдатик', 2);

INSERT INTO tickets.action_date(id, dt, action_id) VALUES
(1, '2016-01-30 16:00:00', 1);
INSERT INTO tickets.action_date(id, dt, action_id) VALUES
(2, '2016-01-30 22:00:00', 1);
INSERT INTO tickets.action_date(id, dt, action_id) VALUES
(3, '2016-01-27 21:00:00', 2);
INSERT INTO tickets.action_date(id, dt, action_id) VALUES
(4, '2016-01-27 22:00:00', 3);
INSERT INTO tickets.action_date(id, dt, action_id) VALUES
(5, '2016-01-28 22:00:00', 4);

INSERT INTO tickets.halls(id, halls_nm) VALUES
(1, 'ДК Октябрь');
INSERT INTO tickets.halls(id, halls_nm) VALUES
(2, 'Театр Кукол');

INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(1, 1, 1, 1, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(2, 1, 2, 1, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(3, 1, 3, 1, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(4, 1, 4, 1, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(5, 2, 5, 2, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(6, 2, 6, 2, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(7, 2, 7, 2, 1);
INSERT INTO tickets.place(id, ryad, mesto, rang, hall_id) VALUES
(8, 2, 8, 2, 1);

INSERT INTO tickets.stock(id, act_dt_id, hall_id, place_id) VALUES
(1, 1, 1, 1);