C, PHP, VB, .NET

Дневникът на Филип Петров


* Задача от контролно 2 – 2015 г. вариант 2

Публикувано на 17 юни 2015 в раздел Бази от Данни.

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

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

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

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

zad_2_reshenie

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. То е наложително, защото искаме да сравняваме стойностите на един ред със стойностите на други редове от една и съща таблица.

 



4 коментара


  1. Георги каза:

    Здравейте, моля за малко критика относно представеното от вас решение на задачата горе.
    „Има ежегоден рок фестивал“. Как по-точно осигурихте, че фестивалът е ежегоден и няма шанс в базата ви да се вмъкнат два фестивала в една и съща година? startDate е UNIQUE, но това не е гаранция за уникалност на годината! Въобще няма нужда, според мен, началната и крайната дата да са уникални- това не спомага с нищо. Например нищо не пречи да сменим часа в следния формат ‘YYYY-MM-DD HH:MM:SS’ (datetime) или дори секундата и да получим съвършено нова дата. От тук нататък можем да въведем безброй много фестивали, провели се през една и съща година YYYY, от това следва, че заявката ви в задача 2 автоматично пропада, заради поставеният знак ‘=’ в: WHERE concerts.festival_id =
    ( SELECT id FROM festivals
    WHERE YEAR(start_date) = var_year ) Няма да има само един фестивал, провел се в конкретната година.
    Явно, че зрителите на рок фестивалите ви ще бъдат много ограничени – едва до 32767, тоест сигурно това са местни фестивалчета, на които се явяват най-много хора от околията. Не знам защо давате толкова строги ограничения в базата, но това в никакъв случай не я прави по-всеобхватна.. 1 музикант в една група ли може да участва, в условието не се казва изрично, а практиката показва обратното.
    Как подсигурихте в задачата следното: „По правило всеки ден на голямата сцена свирят две популярни групи – една подгряваща и една основна (headliners).“. Ако не е реализирано, тогава защо го пише в условието, много хора размишляват по този въпрос.
    „Всеки концерт започва на точно определена дата и час, на точно определена сцена и има планирана продължителност, която се измерва с положително цяло число – брой минути.“ – тук не пише, че в един концерт участва само 1 група, както вие сте го направили, това сигурно са някакви моноспектакли. Или по-скоро това не са концерти а участия на концерт, така ли е или не е така, нито в условието го пише, нито пък някой знае..Пише, че на 1 концерт свирят 2 групи (1 популярна и 1 непопулярна), тоест имаме 1 концерт : М групи. Съжалявам, но по моему не ви е издържано решението този път. Как очаквате ние да се сетим за тези неща, след като вие, който сте писал условието, не сте ги реализирал съвсем коректно. Да не говоря за зад. 3, която е напълно безмислена от практическа гледна точка. Според мен, това е валидация на данни и би следвало да се прави преди данните да постъпят в базата, а не след като са постъпили да се вади някаква таблица с грешни данни, не е много добра практика, но вие сигурно ще кажете, че е просто с упражнителна цел. Мислете и давайте колкото се може по- реални примери, а не „просто с упражнителна цел“, с които да ни давате лоши примери и да ни учите на недобри практики. И по-принцип забелязвам, че го правите, тук не знам защо така се е получило. Ще се радвам да отговорите на въпросите ми!

  2. Георги,

    Преди да отговоря подробно на забележките ти, ще отбележа нещо много важно, което всички вие много добре знаете:

    ! Когато нещо в задачата е неясно, вие го реализирате така, както сте го разбрали. Затова към задачите хората (повечето) пишат обяснения. Например ако сте сметнали, че един концерт може да е с две или повече групи, правите връзката М:М и го описвате. Това се изисква от решенията ви. Това не са задачи, които имат едно единствено решение – напротив, има интерпретации, всички те са валидни и се приемат за вярни, стига да са логични. Може например да си пазите датата разбита по година, месец, ден (за начало) и месец, ден (за край), като годината да е UNIQUE – това даже може да улесни решението нататък, защото няма да се извиква функция YEAR, а годината ще се използва наготово. Ваш избор. Няма как в рамките на една страница да ви дам свръхподробно условие. Няма как и да дам книжка с техническа документация. Не е това целта.

    или

    ! При оценяването ние НЕ следим дали задачата е решена точно по начина описан по-горе. Това от по-горе е примерно решение.

    Сега по същество. Забележката ти за ежегодността на фестивала естествено е уместна, друг е въпроса какво е практическото й значение за реална разработка на софтуер (никакво). Ако толкова се настоява за това, може да се направи тригер от 3-4 реда, който предотвратява този проблем. Може и да се реши по принципиално друг начин – оставям го на вас. Адекватни алтернативни решения не се отхвърлят. Например не сме отнемали никакви точки за решения на задача 3 с вложени цикли и сравнение на редовете 1 по 1, нищо че хем е по-сложно, хем е крайно неоптимално.

    Перфекционизмът е хубаво нещо, за което те поздравявам!

    Зрителите са „за концерт“, а не за целия фестивал. И типът е „unsigned“ между другото, не че е от особено значение.

    Относно „на един концерт повече от една група“ – щом не е уточнено дали е така се прави както прецените.

    Колкото до практическите ситуации – в моята практика съм се борил в пъти повече време със справяне с проблеми от невалидно въведени данни от други хора (работели по проект преди мен), отколкото с писане на коректни валидации. Но това е моята практика все пак (работил съм и продължавам да работя като tech support). Може да е някакво изключение, не знам. На изпитът се старая да проверявам знания, а не да карам хората да решават нови нерешени проблеми (което впрочем е истинската практика на един софтуерен инженер).

  3. Георги,

    Оценките от Excel-ския файл са това, което съм писал аз. Не отговарям за това, което проф. Гоцева е публикувала на сайта си. Някои хора ги е бонифицирала заради Nasa Space Apps challenge, други по друга линия – това не е от мен и аз нямам нищо общо с тези неща. Разговор с нея относно трудността на задачите не е имало. Разговор със студенти, извън този тук с теб, също.

    П.П. Ако се вгледаш по-подробно в списъка с оценките, които проф. Гоцева е поставила, ще видиш, че по-голямата част от получилите различна оценка са от групи 40 до 45, т.е. вариант 1. Тезата ти издиша.

  4. Георги каза:

    Ей, добре че се разписахме тук, че да ви смъмри проф. Гоцева и да напишете шестците, които щяхме да изкараме ако задачите не бяха с толкова различна трудност. С последните оценки на практика си признавате грешката, която сте допуснал, макар и късно, но е добре да се осъзнаете. Добре е, че това е в полза на студентите и ощетени няма. Иначе се правите, че хващате студенти да преписват- всички знаят, че при вас на изпити се преписва най-лесно, оценките на преписващите го потвърдиха. Добре, че има и някой над вас, който да ви „издърпа за ушите“, когато не сте прав и да въздаде справедливост.

Добави коментар

Адресът на електронната поща няма да се публикува


*