C, PHP, VB, .NET

Давам примерно решение на задачата от контролна работа 2. Показаното по-надолу решение на 80% е дадено от студент. Редактирал съм някои грешки, които е нормално да се появят по време на контролна работа на лист хартия, както и на някои места леко съм оптимизирал заявки (например съм променил начина за итериране на цикъла в задача 2). Поправил съм и двете имена на променливи, заради които условието беше определено от ваш колега като „сбъркано“, а задачата „дефектна“ в друга тема от сайта (а пък самият аз явно съм показал „дребнава и мизерна същност“ понеже условията на задачите са били неясни, а по време на контролната работа съм отказвал да помагам). Маркирал съм проблемните две места с коментари в SQL кода (както беше отбелязано и на самата контролна работа на дъската). Условието започва от следващия параграф. Оставям на читателите сами да преценят нивото на сложност и доколко задачите са дефинирани неясно. Утре ще публикувам и втория вариант, който беше даден на другите групи.

Застрахователна компания пази списък с клиенти и типове застраховки. В началото на всеки месец се извършва плащане по застраховките. Ако плащането не може да се извърши (например картата на човека няма достатъчно пари), се прави запис в таблица за длъжниците.

CREATE TABLE users(
   id INT UNSIGNED PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   address VARCHAR(255) NOT NULL,
   cc_num INT(12) UNSIGNED NOT NULL
) ENGINE=InnoDB;

CREATE TABLE plans(
   id INT UNSIGNED PRIMARY KEY,
   name VARCHAR(32) NOT NULL,
   monthly_price DECIMAL (5,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE users_subscriptions(
   user_id INT UNSIGNED NOT NULL,
   plan_id INT UNSIGNED NOT NULL,
   FOREIGN KEY (user_id) 
               REFERENCES users(id),
   FOREIGN KEY (plan_id) REFERENCES plans(id),
   PRIMARY KEY (user_id, plan_id)
) ENGINE=InnoDB;

CREATE TABLE debtors(
   user_id INT UNSIGNED PRIMARY KEY,
   FOREIGN KEY (user_id) 
               REFERENCES users(id),
   debt_amount DECIMAL(7,2) NOT NULL
) ENGINE=InnoDB;

Задача 1. Направете ER диаграмата отговаряща на горните заявки (5 точки);

Решение: Според мен няма нужда от обяснения:

ER диаграма

Задача 2. Нека в базата данни има вече създадена съхранена процедура с име “getPayment”, която приема три параметъра. Първите два са номер на кредитна карта (cc_num) и сума пари. Процедурата се опитва да извърши банков превод. Ако преводът е успешен, третия параметър (result от тип BIT) приема стойност 1, в противен случай 0.

Реализирайте регулярно събитие (EVENT), което на първо число всеки месец извършва всички плащания в системата за всички потребители (5 точки). Ако организирате плащанията така, че за всеки потребител те да се извършват накуп (напр. ако един потребител има запис към два плана, да се прави едно общо плащане вместо две) получавате допълнително 5 точки. В случай, че някое плащане в регулярното събитие е неуспешно, да се направи запис в таблица debtors. За целта организирайте трансакция. В таблица debtors се натрупват парите, които дължи потребителя (т.е. да не се губят негови стари задължения) (10 точки)!

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

DELIMITER |
CREATE PROCEDURE processPayments()
BEGIN
  DECLARE iterator INT UNSIGNED;
  DECLARE var_user_id INT UNSIGNED;
  DECLARE var_user_cc_num INT UNSIGNED;
  DECLARE var_user_sum DECIMAL(7,2);
  DECLARE result BIT;
  # Взимаме общият брой на потребителите
  SELECT COUNT(*) FROM users INTO iterator;

  WHILE(iterator > 0)
  DO
    SET iterator = iterator - 1;
    # Поне на пръв поглед, почти никой не се е справил с
    # намирането на потребителите един по един както например
    # както долу (имам предвид LIMIT 1 OFFSET iterator).
    # Повечето студенти са приели, че id са поредни номера
    # и са ги взимали един след друг, което естествено в реална
    # ситуация би довело до грешки (липсващи или непоредни id).
    # Понеже е масово явление, все пак ще бъде прието за вярно.
    SELECT id, cc_num INTO var_user_id, var_user_cc_num
    FROM users LIMIT 1 OFFSET iterator;

    # Тук се изчислява общата сума пари, която потребителя
    # трябва да плати - сума от цените на плановете, за които
    # се е записал
    SELECT SUM(plans.monthly_price) INTO var_user_sum
    FROM plans JOIN users_subscriptions
         ON users_subscriptions.plan_id = plans.id
    WHERE users_subscriptions.user_id = var_user_id;

    START TRANSACTION;
    CALL getPayment(var_user_cc_num, var_user_sum, result);
    IF(result = 0)
    THEN
      INSERT INTO debtors(user_id, debt_amount)
      VALUES (var_user_id, var_user_sum)
      ON DUPLICATE KEY UPDATE
      debt_amount = debt_amount + var_user_sum;
    END IF;
    COMMIT;
  END WHILE;	
END |
DELIMITER ;

CREATE EVENT monthly_payments
ON SCHEDULE EVERY 1 MONTH
STARTS TIMESTAMP '2013-05-27 00:00:00'
DO CALL processPayments();

П.П. В подобна задача е много по-подходящо да се подходи с курсори, но такива не бяха изучавани на упражнения поради липса на време. Преместването с OFFSET на всяка итерация е много бавно и неподходящо, особено при таблица с динамична дължина на редовете (с VARCHAR), каквато е users! Именно курсорите (CURSORS) са създадени за такива ситуации – чрез тях се итерира ред по ред по резултатна таблица от SELECT заявка в цикъл, като на всяка следваща итерация курсорът „помни“ своето текущо място и така няма отмествания всеки път от началото на таблицата, както е в горния пример.

Задача 3. В този вариант на базата от данни ако един човек има множество записвания към различни планове и има неплатена сума само към част от тях, никъде не се пази информация за какво точно той дължи пари. Променете базата от данни така, че такава информация да се записва и при нужда да може да се извежда (10 точки).

Решение: На първо време е добре да се отбележи, че в началното условие таблица debtors принципно е ненужна – полето debt_amount спокойно може да се премести в „users“.

В тази задача обаче такава отделна таблица ще е необходима. Първо връзката ще се направи 1:М, защото вече един човек ще има повече от един дълг (по един с натрупана сума за всеки негов план). Освен това ще е нужно да има и връзка към плановете. Тоест:

DROP TABLE debtors;
CREATE TABLE debtors(
  user_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  debt_amount DECIMAL(7,2) NOT NULL,
  FOREIGN KEY (user_id)
      REFERENCES users(id),
  FOREIGN KEY (plan_id)
      REFERENCES plans(id),
  PRIMARY KEY (user_id, plan_id)
) ENGINE=InnoDB;

Естествено подобна промяна би повлекла и промени (не малко) в процедурата от предишната задача. Направете ги като допълнително упражнение. Например очевидно е, че „плащане накуп“ няма да е резонно, защото искаме да се пази конкретно кой план не е платен.

Може да се отбележи, че тази връзка припокрива връзката users_subscriptions. Ако не позволяваме на клиент да се отказва от записа си за даден план, то двете връзки могат да се обединят в една.

Задача 4. Направете тригери, които при обновяване или добавяне на нов запис в таблица debtors да извикват абстрактна (приемаме, че вече я има реализирана) процедура “SendEMailToJohn”, с параметри user_id и debt_amount, която системата ни ще използва, за да уведоми събирача на дългове John, че има нова задача (5 точки).

Решение: Не е казано дали оригиналната или променената таблица debtors, но няма и особено значение, защото няма да има разлика в самите тригери. Смятам, че самото решение няма нужда от коментар:

CREATE TRIGGER debtors_insert_trigger
AFTER INSERT ON debtors FOR EACH ROW
BEGIN
  CALL SendEMailToJohn(NEW.user_id, NEW.debt_amount);
END |

CREATE TRIGGER debtors_update_trigger
AFTER UPDATE ON debtors FOR EACH ROW
BEGIN
  CALL SendEMailToJohn(NEW.user_id, NEW.debt_amount);
END |
DELIMITER ;