C, PHP, VB, .NET

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


* Решение на вариант 1 от изпит редовна сесия 2011

Публикувано на 19 май 2011 в раздел Бази от Данни.

Задача 1. Да се проектира база от данни за оффроуд състезание. В базата данни се пази информация за екипажите: стартовия номер, имената на пилота и щурмана, марката и модела на автомобила, и кръвните групи за всеки член на екипажа. Състезанието е разделено на различни етапи. В статистическата информация се пази времето на старта,     и времето на пристигане за всеки етап, на всеки автомобил. За всеки етап се пази присъдената му категория сложност от 1 до 10. За всеки етап от състезанието се назначава специален автомобил наречен „евакуатор“, който оказва техническа и медицинска помощ на повредени и катастрофирали автомобили. Също така за всеки етап се записва съдия, който регулира състезанието.

Проектирайте ER диаграма на описаната база от данни

Решение: Състезанието е едно, т.е. няма смисъл да се пази отделна таблица с негово име, дата или други характеристики. Основните обекти са етапите, съдиите, евакуаторите, екипажите и автомобилите. В задачата не е указано, но ще приемем, че един автомобил може да се кара само от един екипаж (т.е. екипажите не си сменят автомобилите в различните етапи, т.е. както е в реалните състезания), следователно те ще бъдат обединени в една таблица. Статистиката със сигурност ще бъде атрибут на свързващ обект между етапите и екипажите с техните автомобилите. Едно примерно решение е следното:

ER диаграма на база данни за оффроуд състезание

Забележка: Естествено е възможно един съдия или евакуатор да участва в повече от един етап (даже това е напълно нормално). Тоест за да бъде правилно нормализирана нашата база от данни ние би следвало да ги отделим в отделни таблици с връзки 1:M. Понеже не се очаква едно състезание да има прекалено много етапи, в настоящото примерно решение това не е направено. Ако бъде реализирано не е грешка, даже напротив.

Задача 2. Създайте базата данни чрез езика SQL.

Решение: Използваме синтаксиса на MySQL:

CREATE TABLE stages(
 number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 level INT(2) UNSIGNED NOT NULL DEFAULT 5,
 evacuator VARCHAR(255) NOT NULL,
 judge VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE crews(
 start_number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name_pilot VARCHAR(255) NOT NULL,
 name_assistant VARCHAR(255) NOT NULL,
 blood_pilot ENUM("A+","A-","B+","B-","0+","0-","AB+","AB-"),
 blood_assistant ENUM("A+","A-","B+","B-","0+","0-","AB+","AB-"),
 branch_car VARCHAR(255) NOT NULL,
 model_car VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE stats(
 crew_start_number INT UNSIGNED,
 stage_number INT UNSIGNED,
 PRIMARY KEY(crew_start_number,stage_number),
 start TIMESTAMP NULL DEFAULT NULL,
 finish TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB;

ALTER TABLE stats
ADD FOREIGN KEY (crew_start_number)
    REFERENCES crews(start_number) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (stage_number)
    REFERENCES stages(number) ON DELETE CASCADE ON UPDATE CASCADE;

За решението на задачата НЕ е нужно, но за демонстрация ще вмъкнем и примерни данни:

INSERT INTO stages(number, level, evacuator, judge)
VALUES (1,3,"Ivan Ivanov","Dimitar Dimitrov"),
       (2,5,"Stoian Stoianov","Dimitar Dimitrov"),
       (3,8,"Petar Petrov","Evgeni Evgeniev"),
       (4,10,"Ivan Ivanov","Dimitar Dimitrov");

INSERT INTO crews(start_number, name_pilot, name_assistant, blood_pilot,
                  blood_assistant, branch_car, model_car)
VALUES (1,"Ventcislav Ivanov","Stefan Kozarov","A+","0-","UAZ","469"),
       (2,"Krasimir Avramov","Petar Avramov","A-","A+","Nissan","Patrol"),
       (3,"Dimitar Dechev","Asen Bratanov","AB+","AB+","Lada","Niva 1.7i"),
       (4,"Iuri Petrov","Todor Alexiev","0+","B-","UAZ","Patriot"),
       (5,"Atanas Zhelev","Ivan Stoev","A+","B-","Lada","Niva 1.6"),
       (6,"Ivan Alexandrov","Philip Trifonov","A+","AB+","Toyota","Land Cruiser");

INSERT INTO stats(crew_start_number, stage_number, start, finish)
VALUES (1,1,20110518094400,20110518101539),
       (2,1,20110518101600,20110518105619),
       (3,1,20110518105800,20110518111030),
       (4,1,20110518111100,20110518115122),
       (5,1,20110518115300,20110518121913),
       (6,1,20110518122000,20110518125910),
       (1,2,20110518133000,20110518135012),
       (2,2,20110518135200,20110518141500),
       (3,2,20110518141600,NULL),
       (4,2,20110518143500,20110518145806),
       (5,2,20110518150000,20110518153219),
       (6,2,20110518153300,20110518155113),
       (1,3,20110518160000,NULL),
       (2,3,20110518163000,20110518165555),
       (4,3,20110518165700,20110518172133),
       (5,3,20110518172200,NULL),
       (6,3,20110518173000,20110518175320),
       (1,4,20110518180000,20110518181951),
       (2,4,20110518182200,20110518184932),
       (4,4,20110518185200,NULL),
       (6,4,20110518190000,20110518193359);

Задача 3. Изведете списък с генералното класиране в състезанието. Формулата, по която се изчислява е: сумата от (сложност на етап / време на завършване).

Решение: Понеже сложността на етапите е число от 1 до 10, а разликите във времената са много големи, за да не се получават прекалено малки числа в следващата заявка съм умножил получения резултат по 10000:

SELECT crews.start_number, crews.name_pilot, crews.name_assistant,
       crews.branch_car, crews.model_car,
       SUM(stages.level*10000/(stats.finish-stats.start)) AS points
FROM crews
JOIN stats ON crews.start_number = stats.crew_start_number
JOIN stages ON stages.number = stats.stage_number
WHERE stats.finish IS NOT NULL
GROUP BY crews.start_number
ORDER BY points DESC;

Забележка: В задачите на изпита погрешно беше отбелязано, че формулата е "сложността на етапа уможена по времето на завършване". Това естествено обръща резултатите в полза на по-слабите участници (те ще събират повече точки), т.е. там класирането трябваше да бъде в обратен на показания по-горе ред (като проблемни в този вид на решение на задачата идват незавършилите състезатели - за тях би трябвало да се предвижда някакво наказание извън тази задача). Логиката на конструиране на заявката обаче си остава абсолютно същата.

Задача 4. Изведете списък с имената на пилотите, марката и моделите на автомобилите, които не са успели да завършат етап номер 3.

Решение: Тук трябва да се досетим, че за незавършил автомобил се смята не само този, който е стартирал и се е провалил (т.е. в колона "finish" има стойност NULL), но също така и този, които въобще не е стартирал етапа (в нашите примерни данни има такава кола):

SELECT crews.name_pilot, crews.branch_car, crews.model_car
FROM crews
WHERE crews.start_number IN(
          SELECT stats.crew_start_number
          FROM stats
          WHERE stats.finish IS NULL
                AND stats.stage_number = 3
      )
      OR crews.start_number NOT IN(
          SELECT stats.crew_start_number
          FROM stats
          WHERE stats.stage_number = 3
      );

Забележка: На студентите, които не са се досетили за варианта кола въобще да не е стартирала етап 3, оценките не са им намалявани значително.

Задача 5. Изведете списък с класацията по марки автомобили, спечелили специалните етапи с категория на сложност от 7 нагоре.

Решение: Това очевидно е най-трудната задача, защото изисква специално внимание към групирането на данните за агрегатната функция и вложените заявки във FROM.

SELECT crews.branch_car, crews.model_car
FROM crews
WHERE crews.start_number IN(
          SELECT t1.crew_num
          FROM(
                SELECT stats.crew_start_number AS crew_num, stages.number AS stage_num,
                       MAX(stages.level*10000/(stats.finish-stats.start)) AS points
                FROM stats JOIN stages ON stats.stage_number = stages.number
                WHERE stages.level>=7 AND stats.finish IS NOT NULL
                GROUP BY stage_num
          ) AS t1
);

Забележка: Реално никой не се справи отлично със задача 5, но на студентите, които бяха подходили правилно им се отчете за вярна.

П.П. Генерирането на картинката и написването на горната статия на Notepad ми отне петдесет и седем минути. Впоследствие корекциите по кода, за да бъде изпълним на компютър (премахване на синтактични и граматически грешки), ми отнеха още дванадесет минути. Да, задачата е измислена от самия мен, но на изпита имаше още 45 минути аванс :)

 



16 коментара


  1. Задачата е стандартна, но според мен има прекалено много писане за да се решава на лист хартия.

  2. Николай Вълчев - Да, прав си, грешка в условието е. Не променя задачите значително и не се брои за грешка ако е направено. Задачите почти не се променят - просто се слага едно допълнително условие за това кое е конкретното id на състезание в WHERE клаузите. И в ER диаграмата ще има още един клас обекти наречен "Състезания".

    Колкото до началното време на етапа - няма никакво значение какво е. Аз съм избрал да стартират един след друг (както е в реалните състезания от този род), но дори да стартират едновременно решенията остават същите.

    mertol - без INSERT заявките е (добавил съм ги допълнително) и без обясненията. Времето на изпита е 1 час и 45 мин. Според мен е напълно достатъчно.

  3. "Всяко състезание е разделено на различни етапи." Под това не се разбира, че състезанието е едно. И когато питах квесторката дали от едно време може да се извади друго, тя ми каза, че няма как да стане, и трябва да се прави с отделни заявки...

  4. a също така, асистентката каза и че началното време на всички за даден етап е едно и също

  5. Николай Вълчев - Никой не проверява решенията на компютър и не търси перфектност, а правописните грешки или непомненето на конкретен термин НЕ намаляват оценката по никакъв начин. Показаното по-горе НЕ е единственото решение на задачата - има различни начини да се реши, а както самия ти отбелязваш - има и интерпретации. Например задача 4 почти никой не я е решил с вложени SELECT, а студентите са използвали JOIN. Интерпретации дори на ER диаграмата са възможни въпреки, че тя е доста проста. Масовата грешка беше да се отделят автомобилите от екипажа в отделна таблица, но и това не е считано за лоша грешка.

    П.С. Мисля, че ясно съм написал, че ми отне 57 минути. А чертаенето на диаграмата на компютър според мен е по-трудно, отколкото на ръка.

  6. Ако искаш ми пиши e-mail и ще ти отговоря подробно за твоята работа какво-къде и как е станало. Иначе ще има официална дата за разглеждане на контролните и нанасяне на оценките.

  7. Това което имах впредвид е, че всеки може да си направи различна интерпретация на условието, аз лично явно не съм го разбрал правилно и резултата е факт. Не искам да се заяждам, да се оправдавам, но вие сте успяли да я направите за 70 мин, като все пак вие сте измислили условието и сте знаели какво имате впредвид за всяка точка, докато ние имахме 90 (а не 105 мин) да усмислим всичко и да го напишем на ръка.

  8. Oкей, грешката си е в мен, не твърдя друго, просто не ми беше напълно ясно условието, което пак си е моя грешка, извинявам се, ако има нещо. Кога можем да си видим работите?

  9. За ER диаграмата не трябва ли да има и връзка между етапите и екипажите М:М?

  10. За какво точно служи GROUP BY във вътрешната заяка на задача 5? В смисъл такъв че

    SELECT stats.crew_start_number AS crew_num, stages.number AS stage_num,
    MAX(stages.level*10000/(stats.finish-stats.start)) AS points
    FROM stats JOIN stages ON stats.stage_number = stages.number
    WHERE stages.level>=7 AND stats.finish IS NOT NULL
    GROUP BY stage_num

    връща като резултат

    crew_num stage_num points
    2 3 34.48275862068966
    1 4 51.255766273705795

    Защо на екип 2 съответства точно етап 3, а на 1-вия етап 4 при положение, че имат значение точките и от 2-та етапа?

  11. Защо в задача 5 е използвано MAX, а не SUM както в задача 1?

  12. Позабравил съм я тази задача, но след бегъл поглед бих казал, че се използва MAX заради думата "спечелили", т.е. "най-добрите", т.е. "с най-добър резултат", т.е. предполагам "максимален"...

  13. В задача 3 според мен има грешка понеже при въвеждането на информацията в БД имаме само 2ма пилоти, които не са завършили етап 3ти. Решението което предлагам е:

    SELECT crews.name_pilot, crews.branch_car, crews.model_car 
    FROM crews
    JOIN stats ON crews.start_number = stats.crew_start_number
    WHERE stats.start IS NULL OR stats.finish IS NULL AND stage_number = 3;

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

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


*