C, PHP, VB, .NET

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

Проектирайте 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 минути аванс :)