Агентство по продаже билетов

4. Примеры запросов к базе данных
К полученной базе можно обращаться с помощью SQL запросов. Например, выберем все мероприятия, которые пройдут в определенном месте из расписания (представление SHEDULE).

SELECT * FROM mydb.shedule
WHERE place_'Большой Концертный Зал'
AND datetime_NOW();

Или всех клиентов купивших билет на концерт.

SELECT c.name, t.id, e.description FROM
mydb.customer c,
mydb.sale s,
mydb.ticket t,
mydb.tour tr,
mydb.event e
WHERE c.ids.id_customer
AND s.id_tickett.id
AND tr.idt.id_tour
AND tr.event_ide.id

...

СОДЕРЖАНИЕ
1. Агентство по продаже билетов 3
2. Схема базы данных, описание таблиц 4
3. SQL скрипты создания таблиц 5
4. Примеры запросов к базе данных 10

Агентство по продаже билетов реализует билеты на концерты и прочие мероприятия при помощи сайта в сети интернет. Билет может купить зарегистрированный пользователь, указавший email, имя, фамилию и дату рождения. Посетитель сайта может посмотреть расписание всех мероприятий, на которые продаются билеты, свободные места с ценами. Администратор сайта может посмотреть историю покупок пользователей, редактировать прайс-листы на мероприятия. Необходимо разработать схему базы данных для агентства.

Создадим таблицу CUSTOMER (покупатели) с полями id – уникальный идентификатор, name – имя, surname – фамилия, email – адрес электронной почты, phone – телефон, birthdate – дата рождения.
CREATE TABLE `customer` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`surname` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NULL,
`birthdate` DATE NOT NULL,
PRIMARY KEY (`id`))
Создадим таблицу SALE (продажи) с полями id_ticket – указатель на билет, id_customer – указатель на покупателя, datetime – дата и время оплаты.
CREATE TABLE `sale` (
`id_ticket` INT NOT NULL,
`id_customer` INT NOT NULL,
`datetime` DATETIME NULL,
CONSTRAINT `id_ticket`
FOREIGN KEY (`id_ticket`)
REFERENCES `ticket` (`id`)
CONSTRAINT `id_customer`
FOREIGN KEY (`id_customer`)
REFERENCES `customer` (`id`))
Создадим представление SHEDULE (расписание) с полями datetime_ – дат и время, event_ – мероприятие, place_ – место, address_ – адрес.
CREATE OR REPLACE VIEW `shedule` AS
SELECT
t.datetime AS datetime_,
e.description AS event_,
p.description AS place_,
p.address AS address_
FROM
tour t,
place p,
event e
WHERE
t.place_id = p.id AND t.event_id = e.id
ORDER BY datetime_ , event_;
Создадим представление PRICELIST (прайс-лист) с полями datetime_ – дат и время, event_ – мероприятие, place_ – место, address_ – адрес, section_ – тарифная зона, price_ – цена.
CREATE VIEW `pricelist` AS
SELECT
t.datetime AS datetime_,
e.description AS event_,
p.description AS place_,
p.address AS adress_,
s.name AS section_,
pr.price AS price_
FROM
section s,
tour t,
place p,
event e,
price pr
WHERE
s.id_place = p.id AND t.event_id = e.id
AND pr.tour_id = t.id
AND pr.section_id = s.id
ORDER BY t.datetime , e.description
Создадим функцию для проверки возраста покупателя. Функция принимает параметры: id_c – уникальный идентификатор покупателя, id_e – уникальный идентификатор мероприятия, функция возвращает 1, если возраст подходящий и 0 если нет.
CREATE FUNCTION `check_age`(id_c INT, id_e INT) RETURNS tinyint(1)
BEGIN
IF (SELECT
(YEAR(CURRENT_DATE)-YEAR(c.birthdate)) - RIGHT(CURRENT_DATE,5)<(RIGHT(c.birthdate,5))-e.agelimit
FROM customer c, event e
WHERE c.id=id_c
AND e.id=id_e)>0 THEN RETURN 1; ELSE RETURN 0; END IF;
END
Напишем процедуру создания билетов в базе данных.
CREATE PROCEDURE `new_tickets`(id_section INT, id_tour INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE j INT DEFAULT 1;
WHILE i<20 DO
SET j=1;
WHILE j<25 DO