C, PHP, VB, .NET

Има ежегоден рок фестивал, в който се провеждат поредица от концерти в няколко поредни дни. Във всеки отделен ден свирят по няколко групи на три различни сцени – една голяма и две малки. По правило всеки ден на голямата сцена свирят две популярни групи – една подгряваща и една основна (headliners). През останалото време на малките сцени един след друг свирят по-неизвестни състави. Трябва да се състави база от данни, в която да се пази следната информация:

• Всяка година фестивалът има основно мото (едно изречение), начална и крайна дата;
• За всяка рок група пазим име на групата, държава, име и фамилия на музикантите в нея;
• Всеки концерт започва на точно определена дата и час, на точно определена сцена и има планирана продължителност, която се измерва с положително цяло число – брой минути;
• Голямата сцена е с номер 1, а малките са с номера 2 и 3;
• За всеки концерт се пази информация колко публика (брой зрители) е имал;
• Не е проблем една група да свири повече от веднъж в рамките на един фестивал.

Задача 1. Начертайте ER диаграма за описаната база от данни. НЕ пишете CREATE TABLE заявки.

Решение: И тук освен ER диаграмата ще дадем CREATE и INSERT заявките въпреки, че не се изискват...

CREATE TABLE festivals(
  id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  start_date DATETIME NOT NULL UNIQUE,
  end_date DATETIME NOT NULL UNIQUE,
  motto VARCHAR(255) NOT NULL
);

INSERT INTO festivals (start_date, end_date, motto) VALUES
("2014-05-01 18:00:00", "2014-05-2 23:59:59", "No drugs!"),
("2015-05-01 18:00:00", "2015-05-2 23:59:59", "No chalga!");

CREATE TABLE concerts(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  festival_id TINYINT UNSIGNED NOT NULL,
  FOREIGN KEY(festival_id) REFERENCES festivals(id),
  visitors SMALLINT UNSIGNED NULL DEFAULT NULL,
  scene ENUM("1-warm", "1-head", "2", "3") NOT NULL,
  start_dt DATETIME NOT NULL,
  length TINYINT UNSIGNED NOT NULL,
  band_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(band_id) REFERENCES bands(id)
);

INSERT INTO concerts(visitors, scene, start_dt, 
                     length, festival_id, band_id) VALUES
(3500, "1-warm", "2014-05-01 20:00:00", 90, 1, 3),
(5000, "1-head", "2014-05-01 22:00:00", 90, 1, 10),
(590, "2", "2014-05-01 18:00:00", 90, 1, 1),
(550, "2", "2014-05-01 20:00:00", 90, 1, 2),
(400, "3", "2014-05-01 18:00:00", 90, 1, 6),
(900, "3", "2014-05-01 20:00:00", 90, 1, 4),
(3900, "1-warm", "2014-05-02 20:00:00", 90, 1, 9),
(5500, "1-head", "2014-05-02 22:00:00", 90, 1, 11),
(300, "2", "2014-05-02 18:00:00", 90, 1, 5),
(420, "2", "2014-05-02 20:00:00", 90, 1, 7),
(800, "3", "2014-05-02 18:00:00", 90, 1, 2),
(910, "3", "2014-05-02 20:00:00", 90, 1, 4),
(3500, "1-warm", "2015-05-01 20:00:00", 90, 2, 3),
(5000, "1-head", "2015-05-01 22:00:00", 90, 2, 10),
(590, "2", "2015-05-01 18:00:00", 90, 2, 1),
(550, "2", "2015-05-01 20:00:00", 90, 2, 2),
(400, "3", "2015-05-01 18:00:00", 90, 2, 8),
(900, "3", "2015-05-01 19:00:00", 90, 2, 4),
(3900, "1-warm", "2015-05-02 20:00:00", 90, 2, 9),
(5500, "1-head", "2015-05-02 21:00:00", 90, 2, 11),
(300, "2", "2015-05-02 18:00:00", 90, 2, 5),
(420, "2", "2015-05-02 19:00:00", 90, 2, 7),
(800, "3", "2015-05-02 18:00:00", 90, 2, 2),
(910, "3", "2015-05-03 20:00:00", 90, 2, 4);

CREATE TABLE bands(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  country VARCHAR(255) NULL DEFAULT NULL
);

INSERT INTO bands(name) VALUES
("Hipodil"), ("Kontrol"), ("Ahat"),
("Milena"), ("Shturcite"), ("BTR"),
("Epizod"), ("Ostava"), ("D2"),
("Sepultura"), ("Nightwish");

CREATE TABLE members(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  band_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(band_id) REFERENCES bands(id),
  fname VARCHAR(255) NOT NULL,
  lname VARCHAR(255) NOT NULL
);

Не сме добавяли редове в "members", защото тази таблица няма да се използва в следващите задачи. Можете допълнително да нормализирате базата като изнесете "country" в отделна таблица.

Друго, по-добре нормализирано решение на същата задача, което отразява неща по условието, което предишното решение би оставило на приложната програма, би било следното:

Реализирайте го и го обсъдете.

По-надолу ще работим върху първия вариант.

Задача 2. От следващата година организаторите на фестивала са решили да въведат едно ново правило – трите групи, които са свирили на малка сцена, които са събрали най-много зрители в един концерт от предходната година, ще бъдат автоматично канени да бъдат подгряваща група на един от големите headliners. Създайте съхранена процедура “getPotentialSupportBand”, която:

• Приема за входен параметър var_year – година на фестивала, за която ще търсим популярни групи от малките сцени;
• Връща resultset (т.е. изпълнява SELECT заявка), който изважда ПЕТ-те групи с най-голям концерт на малка сцена от въпросната година, подредени по популярност (от най-посетена, към по-малко посетена). Заявката връща пет, а не три групи, за да има възможност за „резерви“, в случай, че някоя група се откаже от предложението.

Решение: Единствената уловка в тази задача е, че една група може да има повече от един концерт в рамките на един фестивал. Тоест тук е нужно да отчетем само най-добрия, т.е. MAX(visitors) на групите. Така се предпазваме от възможността една група да попадне два или повече пъти в резултатния списък, при положение, че е имала повече от един много успешен концерт.

DELIMITER //
CREATE PROCEDURE getPotentialSupportBand(IN var_year YEAR)
BEGIN
  SELECT bands.name, MAX(concerts.visitors) AS visits
  FROM bands 
  JOIN concerts ON bands.id = concerts.band_id
  WHERE concerts.festival_id =
        ( SELECT id FROM festivals
          WHERE YEAR(start_date) = var_year )
          AND
        concerts.scene IN ("2", "3")
  GROUP BY bands.id
  ORDER BY visits
  LIMIT 5;
END//
DELIMITER ;

Задача 3. Създайте съхранена процедура “validateConcerts”, в която по подаден входен параметър var_year - година на фестивал, - търси несъответствия в програмата на фестивала свързани с датите на концертите. Несъответствие има ако времената на започване и завършване на концерт на дадена дата се засичат (например единия започва в 8 и завършва в 10, а другия започва в 9 и завършва в 11 часа, като и двата са на една и съща сцена в една и съща дата). Друго несъответнствие би било ако даден концерт е назначен преди началната дата или след крайната дата на фестивала. Процедурата трябва да създаде временна (temporary) таблица, която да бъде с име “schedule_issues”. В тази таблица трябва да се съдържа информация за име и държава на група, дата с начален и краен час на концерт и номер на сцена, попълнена само с проблемните за разписанието концерти.

Решение: За улеснение ще изпълним задачата с две insert-select заявки, вместо с една, което е възможно (и оптимално), но ще направи where клаузата сложна. Друг вариант е да обедините тези две select заявки с union. За улеснение в началото си пазим id-то на фестивала в отделна променлива.

DELIMITER //
CREATE PROCEDURE validateConcerts(IN var_year YEAR)
BEGIN
  DECLARE var_festival_id TINYINT UNSIGNED;
  SET var_festival_id = ( SELECT id FROM festivals
                       WHERE YEAR(start_date) = var_year );
                       
  CREATE TEMPORARY TABLE schedule_issues(
    band_name VARCHAR(255) NOT NULL,
    band_country VARCHAR(255) NULL DEFAULT NULL,
    concert_start_dt DATETIME NOT NULL,
    concert_length TINYINT UNSIGNED NOT NULL,
    concert_scene ENUM("1-warm", "1-head", "2", "3") NOT NULL
  );
  
  INSERT INTO schedule_issues(band_name, band_country,
           concert_start_dt, concert_length, concert_scene)
  SELECT bands.name, bands.country, 
         concerts.start_dt, concerts.length, concerts.scene
  FROM bands
  JOIN concerts ON bands.id = concerts.band_id
  JOIN festivals ON concerts.festival_id = festivals.id
                 AND festivals.id = var_festival_id
  WHERE concerts.start_dt < festivals.start_date
            OR
       (concerts.start_dt + INTERVAL concerts.length MINUTE) 
            > festivals.end_date;

  INSERT INTO schedule_issues(band_name, band_country,
           concert_start_dt, concert_length, concert_scene)
  SELECT bands.name, bands.country,
         c1.start_dt, c1.length, c1.scene
  FROM bands
  JOIN concerts AS c1 ON bands.id = c1.band_id
  JOIN concerts AS c2 ON c1.id <> c2.id
  JOIN festivals ON c1.festival_id = festivals.id
                 AND festivals.id = var_festival_id
  WHERE c1.scene = c2.scene 
    AND (
        c1.start_dt 
        BETWEEN c2.start_dt AND c2.start_dt + INTERVAL c2.length MINUTE
           OR
        c1.start_dt + INTERVAL c1.length MINUTE
        BETWEEN c2.start_dt AND c2.start_dt + INTERVAL c2.length MINUTE
    );
END//
DELIMITER ;

Очевидно сложното в тази задача е наличието на SELF JOIN условие във втория insert-select. То е наложително, защото искаме да сравняваме стойностите на един ред със стойностите на други редове от една и съща таблица.