C, PHP, VB, .NET

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


* Първи вариант от контролна работа 27.05.2013

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

Давам примерно решение на задачата от контролна работа 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 диаграма
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 ;

 



10 коментара


  1. А за трета задача, какво ще се случи, ако даден клиент е записан за два еднакви плана, но дължи пари само по единия план?

    Аз лично ви попитах и вие казахте, че това е напълно възможно. Поради тази причина аз редактирах users_subscriptions и debtors, тъй като по този начин ще знам точно за кой от двата плана се дължат пари от съответния човек. След това пък, в SendEMailToJohn съм предложил да има три входни променливи user_id, debt_amount и s_id - причината е ясна, за да знае потребителят за кой от двата плана има задължения.

    В задачата не е точно описано за какъв тип застраховки става въпрос. Напълно възможно е, потребителят да има два автомобила и да е направил две еднакви застраховки. А по начина, описан от вас, няма да се знае напълно какво е задължението.

  2. Това е възможно на базата за четвърта задача. Вероятно това съм разбрал, че си попитал.

    Иначе плановете са "месечни", т.е. всички се плащат всеки месец. В условието на задачата са показани заявки, които дефинират отношенията. Премахването на Primary Key от таблицата users_subscriptions би позволила един конкретен човек да направи две или повече застраховки от един и същи тип. Това никак не променя задачите обаче.

    Това, че си направил повече неща, отколкото е трябвало не е лошо. Напротив.

  3. Не знам доколко е в повече, вие ще решите като стигнете до там. Въпреки това, нищо не пречи даден потребител да плаща един и същ план два пъти месечно. А ако пропусне да плати едната месечна вноска за единия план, пак става объркване. Поне аз така го разбирам.

    Предполагам, че доста хора са начертали в ER диаграмата user_subscriptions като нормална таблица, а не по вашия образец. Това ще бъде ли проблем?

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

  4. Пф, аз и сега се сетих, че в трета съм дал Alter table на users_subscriptions като съм добавял въпросното поле (subscription_id INT UNSIGNED UNIQUE AUTO_INCREMENT). А вие в задачата сте задали primary key на двата външни ключа в таблицата.

    Така и дори моя уникален ключ да се явява външен (s_id) след това в debtors, няма как да влезе втори еднакъв запис в users_subscriptions, тъй като двете останали полета в нея са primary_key и съответно няма да бъде допуснат втори такъв запис.

    Този вариант щеше да работи, ако бях изтрил напълно таблицата user_subscriptions и след това я създам наново, като този път primary key да е моето поле. След това само оставаше alter table на debtors за да се добави полето като външен ключ. Но като се пишат такива неща на лист без да се тестват предварително...

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

  5. Аз Ви предлагам да направите задачи като миналогодишните или поне частично. Т.е. вкарайте някъде тройно вложен SELECT :D Горната задача си е много ясна, според мен, и единствено изисква доста познания, но на ниво сложност не е нещо чак толкова страшно. Все пак не знам дали ще се вслушате в мен с Вашата дребнава и мизерна същност :D :D :D

  6. Тук няма "мой" и "твой" вариант - има ясни правила, които не съм ги измислил аз или някой случаен човек. Който е покрил всичко както трябва - 6. Който се е объркал малко, което е нормално с оглед на ситуацията "изпит", а не работа вкъщи - пак 6. От там нататък оценката намалява и няма как да е различно. По конкретният въпрос за диаграмата - ако връзката е показана като клас, няма да е нещо фатално, но определено си е грешка, която ще намали някоя друга точка/точки.

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

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

    Нещата се развиват. Къде положително, къде не :)

  7. Така е :) Имах възможността да погледна от тазгодишните курсови задачи и бих казал, че ги намирам за доста полезни, необходими и практични. Със сигурност допринасят повече за научаването и усвояването на материала, така че като мое мнение бих отбелязал това за правилен път и би било хубаво да се приложи при по-голям брой дисциплини ;)

  8. А кога можем да очакваме резултатите от контролното

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

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

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


*