C, PHP, VB, .NET

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


* Някои задачи от контролна работа №2

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

Прилагам трите най-сложни задачи, които се паднаха на контролната работа на 21.05.2012г., заедно с примерни (бързи) решения. Във вариант 4 "трудната задача" беше от друго естество, затова по-нататък ще я разгледам отделно. Другите задачи от вариантите смятам за достатъчно лесни (решават се непосредствено чрез прилагане на заучени правила) и на тях няма да давам решения.

Вариант 1. Дадена е следната ER диаграма:

er-diagrama

В таблица “Scores” ще се пази само по един (най-добрия постигнат) резултат на играч за дадена игра. Ако знаете, че полетата Players.username, Games.name и Games.vendor са от тип VARCHAR(100) NOT NULL, а всички останали са от тип INT, то решете следните задачи...

...

Задача 4. Изведете името на играча, който е постигнал най-добър резултат за играта „Heroes 5” и средния брой точки, които той е постигнал във всички игри, в които е играл.

Решение: При всички положения ще ни трябва да намерим "най-добрият резултат за играта Heroes 5", а от там да намерим и идентификационния номер на играча. Това е лесно, защото от условието знаем, че "се пази само по един (най-добрия постигнат) резултат на играч". Тоест не ни е нужно да групираме и да сумираме точки, а само трябва да намерим най-добрия резултат за въпросната игра и от там директно намираме кой е играча. Това може да стане като вземем редовете от Scores с играта "Heroes 5" (в scores нямаме нейното име, значи трябва да я намерим чрез нейното g_id), сортираме точките в низходящ ред и накрая вземем само първия резултат:

SELECT p_id
FROM scores
WHERE g_id =(
   SELECT games.id
   FROM games
   WHERE games.name = "Heroes 5"
)
ORDER BY points DESC
LIMIT 1;

Това е съвсем валидно решение на задачата "да намерим най-добрия играч, който е играл Heroes 5" и разбира се би се приело за напълно вярно (приемайки, че "играч" е в единствено число). Ако все пак искаме да сме перфектни, то би трябвало да отчетем и възможността да има повече от един играч на призовата позиция. Ако искаме да отчетем и него ще направим заявката по следния начин:

SELECT p_id
FROM scores
WHERE (g_id, points) = (
   SELECT g_id, MAX(points) AS tochki
   FROM scores
   WHERE g_id =(
      SELECT games.id
      FROM games
      WHERE games.name = "Heroes 5"
   )
);

Във вложената заявка връщаме идентификационния номер на играта, заедно с най-големия резултат, който е постигнат за нея. В най-външния SELECT използваме тези данни, за да намерим id на играчИТЕ, които са постигнали именно този най-висок резултат и то само за тази игра.

Дотук намерихме идентификационния номер на играча/ите с най-добър резултат в "Heroes 5". На нас обаче ще ни трябва неговото/тяхното потребителско име. Тук отново можем да използваме вложен SELECT:

SELECT username, id
FROM players
WHERE id IN (
   SELECT p_id
   FROM scores
   WHERE (g_id, points) = (
      SELECT g_id, MAX(points) AS tochki
      FROM scores
      WHERE g_id =(
        SELECT games.id
        FROM games
        WHERE games.name = "Heroes 5"
      )
   )
);

Сега към това потребителско име (или имена ако са повече от един) трябва да долепим и "средния брой точки, които той е постигнал във всички игри, в които е играл". Лесно можем да намерим средния брой точки за всички играчи:

SELECT scores.p_id, AVG(scores.points)
FROM scores
GROUP BY scoresp_id

Сега остава да направим връзка между резултатните таблици от двете заявки горе:

SELECT t1.username, t2.avgpoints
FROM (
SELECT username, id
FROM players
WHERE id IN (
   SELECT p_id
   FROM scores
   WHERE (g_id, points) = (
      SELECT g_id, MAX(points) AS tochki
      FROM scores
      WHERE g_id =(
        SELECT games.id
        FROM games
        WHERE games.name = "Heroes 5"
      )
   )
) ) AS t1
JOIN (SELECT scores.p_id, AVG(scores.points) AS avgpoints
      FROM scores
      GROUP BY scoresp_id
     ) AS t2
ON t1.id = t2.p_id;

Дали полученото решение е оптимално? На този етап се съмнявам, че е - виждаме много вложени заявки, част от които може би могат да се обединят. Опитайте се да оптимизирате. Но "по пътя на най-малкото съпротивление" и следването на непосредствената логика решението е такова.

Вариант 2. По същество началните условия на "Вариант 1" и "Вариант 2" са едни и същи - във "Вариант 1" базата от данни е зададена чрез ER диаграма, а във "Вариант 2" чрез самите таблици. Затова няма да даваме условието, а направо пристъпваме към задачата:

Задача 3. Изведете списък с имена на игри със съответни имена на техните разпространители (vendor), но само за игрите, които са играни от повече от 100 играчи. Получената таблица сортирайте в низходящ (DESC) ред по общ брой точки, които са получени за всяка игра.

Решение: Нека първо намерим игрите, които са играни от повече от 100 играчи. Тъй като за всеки играч пазим само по един резултат (най-добрия му), то ако просто преброим редовете в таблица "scores" с групиране по g_id ще можем да видим коя игра от колко играчи е играна. Накрая тази бройка можем да "отрежем" да е по-голяма от 100 чрез HAVING:

SELECT g_id, COUNT(*) AS players_count
FROM scores
GROUP BY g_id
HAVING players_count>100;

В условието обаче се иска и "сортиране по общ брой точки, които са получени за всяка игра". Ние в предишната заявка тъй или иначе прелистваме таблицата scores и групираме по g_id, т.е. можем да се възползваме от това и да "подобрим" предишната заявка така, че да връща и общата сума точки за всяка игра:

SELECT g_id, COUNT(*) AS players_count,
       SUM(points) AS total_points
FROM scores
GROUP BY g_id
HAVING players_count>100;

Сега от получения списък g_id трябва да бъде "заменено" с имената на игрите и имената на техните разпространители. Тези данни се пазят в отделна таблица "Games", която ще присъединим с JOIN (и накрая не забравяме да сортираме резултата):

SELECT games.name, games.vendor, t1.total_points
FROM(
      SELECT g_id, COUNT(*) AS players_count,
             SUM(points) AS total_points
      FROM scores
      GROUP BY g_id
      HAVING players_count>100
    ) AS t1
JOIN games ON games.id = t1.g_id
ORDER BY t1.total_points DESC;

С това решението на задачата е завършено.

Вариант 3.

Дадена е база от данни с две таблици:

CREATE TABLE users(
  id SMALLINT UNSIGNED PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE
)ENGINE=InnoDB;

CREATE TABLE posts(
  id INT UNSIGNED PRIMARY KEY,
  author SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(author) REFERENCES users(id),
  title VARCHAR(255) NULL DEFAULT NULL,
  data TEXT NOT NULL,
  created DATETIME NOT NULL,
  reply_to INT UNSIGNED NULL DEFAULT NULL,
  FOREIGN KEY(reply_to) REFERENCES posts(id)
)ENGINE=InnoDB;

В таблицата “posts” се записват два вида статии:

  • Ако reply_to не е попълнено (NULL), то това е заглавна статия;
  • Ако reply_to е попълнено, то това е „отговор“ на статия.

Ако знаете, че приложението не допуска „отговор на отговор“, то решете задачите:

...

Задача 4. Изведете списък с имената на тримата автори, които имат най-много ЗАГЛАВНИ статии в системата и към тях добавете имената на тримата автори, които имат най-много ОТГОВОРИ в системата. Резултатът от заявката би трябвало да изглежда като следната примерна таблица:

+----------------+-------------+---------------+
| Author         | Count       | Type          |
+----------------+-------------+---------------+
| Ivan           | 120         | Glavni        |
| Petar          | 90          | Glavni        |
| Maria          | 89          | Glavni        |
| Maria          | 560         | Otgovori      |
| Todor          | 499         | Otgovori      |
| Ivan           | 360         | Otgovori      |
+----------------+-------------+---------------+

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

-- за заглавните статии
SELECT author, COUNT(*) AS count
FROM posts
WHERE reply_to IS NOT NULL
GROUP BY author
ORDER BY count DESC
LIMIT 3;

-- за статиите-отговори
SELECT author, COUNT(*) AS count
FROM posts
WHERE reply_to IS NULL
GROUP BY author
ORDER BY count DESC
LIMIT 3;

Сега трябва да направим обединение (UNION) между двете резултатни таблици и да добавим колоната за "тип":

( SELECT author, COUNT(*) AS count,
         'Glavni' AS Type
  FROM posts
  WHERE reply_to IS NOT NULL
  GROUP BY author
  ORDER BY count DESC
  LIMIT 3
)
UNION ALL
(  SELECT author, COUNT(*) AS count,
          'Otgovori'
   FROM posts
   WHERE reply_to IS NULL
   GROUP BY author
   ORDER BY count DESC
   LIMIT 3
);

Получената таблица е почти това, което търсим. Остана да "заменим" идентификационния номер на автора (колоната author) с неговото име. Понеже името се пази в таблицата "users", то не ни остава друго, освен да я присъединим с JOIN:

( SELECT users.username AS author,
         t1.count AS count,
         t1.type AS type
  FROM(
     SELECT author, COUNT(*) AS count,
            'Glavni' AS Type
     FROM posts
     WHERE reply_to IS NOT NULL
     GROUP BY author
     ORDER BY count DESC
     LIMIT 3
  ) AS t1 
  JOIN users ON users.id = t1.author
)
UNION ALL
( SELECT users.username AS author,
         t1.count AS count,
         t1.type AS type
  FROM(
     SELECT author, COUNT(*) AS count,
            'Glavni' AS Type
     FROM posts
     WHERE reply_to IS NULL
     GROUP BY author
     ORDER BY count DESC
     LIMIT 3
  ) AS t1 
  JOIN users ON users.id = t1.author
)

С това решението на задачата е завършено.

П.П. Не съм убеден, че заявките са написани безгрешно - възможно е да има грешки, които да не позволят да се изпълнят. Не са изпробвани в реална среда с примерни данни. Когато имам възможност ще ги изпробвам и ще премахна евентуални неточности.

 



27 коментара


  1. Кога могат да се очакват резултатите от второто контролно и съответно крайните оценки по предмета?

  2. Би било добре да качите целите варианти. Ако не с решение то поне условията им.

  3. Аз не смятам, че е редно да се появят все още пълните решения, защото ако някой иска да коригира нещо по оценката си може да се опита на 4 юни. Но ако решенията излязат, тогава място за корекция няма да има.

  4. За тези от нас, които ще се явим на поправката на 26,06,2012 ще окажат ли влиание точките събрани през семестъра или всичко започва от 0 и същия ли ще бъде формата на изпита или ще има 1 или повече теоретични въпроса?

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

  6. Материалът е върху "каквото е преподавано на лекции". Трябва да има писано поне за 3 и по теорията и по задачата, в противен случай оценката ще е слаб 2.

  7. "Материалът е върху “каквото е преподавано на лекции”." Тоест каквото е преподадено на 4-5 лекции, на които доц.Гоцева е присъствала нали?

  8. Е, тъй като никой, никога не е замествал доц. Гоцева, материалът не би трябвало да е толкова много. И все пак ще съм много благодарен, ако има някакви примерни конспекти или въпроси, защото съм крайно разочарован от начина на преподаване на курса и несериозното отношение към него.

  9. Ако имате оплаквания относно лаб. упражнения - естествено не се сдържайте да изкажете цялата си критика. Относно материала преподаван на лекции ще помоля да се обърнете към доц. Гоцева. Аз не мога да взимам отношение по него.

  10. Единственото ми оплакване от лаб. е, че са твърде малко часовете. 7 часа за семестър, за курс по БД е недостатъчно, но предполагам и по този въпрос трябва да се обърна към някой друг, а като знам как стоят нещата ефектът ще е нулев...

  11. Здравейте, искам да попитам задачата на предстоящата поправка по БД ще запази ли формата си от второто контролно?

  12. Можеш по всяко време да се консултираш с мен като ми пишеш e-mail с конкретни въпроси. За доц. Гоцева - потърсете я в приемното й време в кабинета й.

  13. Кога ще има консултации за поправките по БД и ПИК3?

  14. Искам да попитам дали ще има дата за поправка по пик3 през септември,тъй като времето за подготвока сега е доста малко и втория ми въпрос е колко пъти можем да се явяваме на даден изпит(знам че по правилник имаме право на 3 явявания)?

  15. добре ще е да видим и задачите, в които имаше нормализация и процедури.

  16. преди поправката на 26ти. благодаря предварително.

  17. Може ли същата заявка от задачата с игрите да се напише като използваме функцията МАХ() за таблицата с точки? Тоест пак имаме 2 заявки с псевдоними, втората остава непроменена, а първата да бъде следната:
    select players.name, players.id, MAX(scores.points)
    from players JOIN scores
    on players.id=scores.p_id
    where g_id =(
    select games.id
    from games
    where games.name="heroes 5") AS t1;

    ?

  18. Задачите за тази година подобни ли ще бъдат? Благодаря!

  19. НЕ!

    Ще има две задачи - първата е проектиране на база от данни, а втората е създаване на съхранена процедура с трансакция.

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

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


*