C, PHP, VB, .NET

В система на агенция по маркетинг и реклама се регистрират свободно потребители, които от своя страна водят свои приятели (приятелите са “referrals”, а довелите ги са „referrers”). Всеки потребител прави определено количество точки в системата (points). Всеки потребител взима и процент от точките на неговите referrals – 10% от техните точки. В крайна сметка на месечна база общият сбор от точки се превръща в пари на базата на т.нар. „pay rate” (например при pay rate 0.01 всеки 100 точки стават 1 долар). За всеки потребител се пази дневна статистика.
CREATE TABLE users(
id INT UNSIGNED PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE,
pay_rate DECIMAL(4,3) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_points_per_day(
user_id INT UNSIGNED NOT NULL,
on_date DATE NOT NULL,
points SMALLINT UNSIGNED NULL,
FOREIGN KEY (user_id)
REFERENCES users(id),
PRIMARY KEY (user_id, on_date)
) ENGINE=InnoDB;

CREATE TABLE user_referrals(
referrer_id INT UNSIGNED NOT NULL,
referral_id INT UNSIGNED NOT NULL,
FOREIGN KEY (referrer_id)
REFERENCES users(id),
FOREIGN KEY (referral_id)
REFERENCES users(id),
PRIMARY KEY (referrer_id, referral_id)
) ENGINE=InnoDB;
Задача 1 (5 точки). Направете ER диаграмата отговаряща на горните заявки;

Решение: Според конвенцията за диаграми, която сме приели, ще бъде малко нетипична връзката user_referrals – тя е от една таблица към самата нея:

ER диаграма

Задача 2 (5 точки). В предложената база от данни е възможно един referral да има повече от един referrer, а това не се счита за коректно. Опишете какво трябва да се направи, за да се гарантира, че един referral може да има само един или нито един referrer.

Решение: Очевидно в таблица user_referrals атрибута referral_id трябва да стане UNIQUE, за да не може да се повтаря. По този начин връзката ще стане 1:M. Което пък от своя страна означава, че е възможно цялата таблица user_referrals да бъде изтрита, а в users да се добави нова връзка referrer_id, който да изпълнява тази задача:
DROP TABLE user_referrals;

ALTER TABLE users
ADD referrer_id INT UNSIGNED NULL DEFAULT NULL;

ALTER TABLE users
ADD CONSTRAINT FOREIGN KEY(referrer_id)
REFERENCES users(id);
Промяната в ER диаграмата е минимална:

ER диаграма към задача 2

Задача 3 (5 точки). Направете тригер, с който да гарантирате, че при INSERT заявки към user_referrals един потребител не може да стане referral на самия себе си;

Решение: Очевидно тук се говори за оригиналната структура, а не за променената в задача 2. Проблемът обаче важи и за променената таблица users от задача 2! Затова ще покажа и двата варианта:
#Оригиналната задача
DELIMITER |
CREATE TRIGGER original_referer_check
BEFORE INSERT ON user_referrals FOR EACH ROW
BEGIN
IF(NEW.referrer_id = NEW.referral_id)
THEN
SIGNAL SQLSTATE '45000'
SET message_text = "Cannot be referal to himself";
END IF;
END |
DELIMITER ;
#Отчитайки промените в задача 2
DELIMITER |
CREATE TRIGGER zad_2_referer_check
BEFORE INSERT ON users FOR EACH ROW
BEGIN
IF(NEW.id = NEW.referrer_id)
THEN
SIGNAL SQLSTATE '45000'
SET message_text = "Cannot be referal to himself";
END IF;
END |
DELIMITER ;
Задача 4 (15 точки). Създайте съхранена процедура, с която се пресмятат парите, които даден потребител е натрупал за предишния месец (неговите points + 10% от точките на неговите referrals, всичко умножено по неговия собствен pay rate). Процедурата трябва да приема два параметъра: IN var_user_id и OUT var_ money. Във var_money се записва резултата.

Упътване: За да изведете всички редове от предишния месец използвате следното условие:
YEAR(on_date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(on_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
Решение: Можем да използваме както оригиналния вариант на задачата, така и променения в задача 2. От гледна точка на сложността няма никакво значение. Ще използвам променения в задача 2 вариант.

DELIMITER |
CREATE PROCEDURE last_month_money(IN var_user_id INT, 
                                  OUT var_money DECIMAL(7,3))
BEGIN
  DECLARE user_points SMALLINT UNSIGNED;
  DECLARE referral_earnings INT UNSIGNED;

  #Взимат се точките на потребителя от предишния месец
  SELECT IF(SUM(points) IS NULL, 0, SUM(points))
         INTO user_points
  FROM user_points_per_day 
  WHERE user_id = var_user_id
  AND YEAR(on_date) = 
          YEAR(CURRENT_DATE - INTERVAL 1 MONTH) 
  AND MONTH(on_date) = 
          MONTH(CURRENT_DATE - INTERVAL 1 MONTH);

  #Взимат се 10% от точките на неговите referrals
  SELECT IF(SUM(points) IS NULL, 0, SUM(points)/10)
         INTO referral_earnings
  FROM user_points_per_day
  WHERE user_id IN (SELECT id FROM users 
                    WHERE referrer_id = var_user_id)
  AND YEAR(on_date) = 
          YEAR(CURRENT_DATE - INTERVAL 1 MONTH) 
  AND MONTH(on_date) = 
          MONTH(CURRENT_DATE - INTERVAL 1 MONTH);

  #Изчисляват се парите, които печели за месеца
  SELECT (user_points+referral_earnings)*pay_rate 
         INTO var_money
  FROM users
  WHERE id = var_user_id;
END |
DELIMITER ;

С малко повече работа би било възможно SELECT заявките да се обединят в една обща.

Задача 5 (10 точки). Създайте съхранена процедура без входни параметри, чрез която се генерира resultset с потребителските имена и парите, които са натрупали съответните потребители за предишния месец. Реализирайте го чрез цикъл, в който многократно се извиква процедурата, която е създадена в задача 3, въпреки че е възможно да стане по-оптимално.

Решение: Според мен е еднозначно какво трябва да се направи:

DELIMITER |
CREATE PROCEDURE last_month_report()
BEGIN
  DECLARE iterator INT UNSIGNED;
  DECLARE var_tmp_id INT UNSIGNED;
  DECLARE var_tmp_username VARCHAR(32);
  DECLARE var_tmp_money DECIMAL(7,3);

  CREATE TEMPORARY TABLE result(
    username VARCHAR(32) NOT NULL UNIQUE,
    money DECIMAL(7,3)
  )ENGINE=Memory;

  SELECT COUNT(*) FROM users INTO iterator;

  WHILE(iterator > 0)
  DO
    SET iterator = iterator - 1;

    SELECT id, username 
    INTO var_tmp_id, var_tmp_username
    FROM users LIMIT 1 OFFSET iterator;

    CALL last_month_money(var_tmp_id, var_tmp_money);

    INSERT INTO result(username, money)
    VALUES(var_tmp_username, var_tmp_money);
  END WHILE;

  SELECT * FROM result;
  DROP TABLE result;
END |
DELIMITER ;

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