C, PHP, VB, .NET

Задачата е дадена на контролната работа за заминаващите на студентска бригада. В отдел „човешки ресурси“ на фирма се пази информация за служителите и техните заплати по следния начин:
CREATE TABLE departments(
id TINYINT UNSIGNED PRIMARY KEY,
name CHAR(12) NOT NULL,
min_salary SMALLINT UNSIGNED NOT NULL,
max_salary SMALLINT UNSIGNED NOT NULL
) ENGINE=InnoDB;

CREATE TABLE employees(
id SMALLINT UNSIGNED PRIMARY KEY,
name VARCHAR(255) NOT NULL,
salary SMALLINT UNSIGNED NOT NULL,
department_id TINYINT UNSIGNED,
FOREIGN KEY (department_id)
REFERENCES departments(id)
) ENGINE=InnoDB;
Задача 1 (10 точки). Създайте тригер, в който се прави проверка дали при INSERT заявка в таблицата employees стойността на salary е коректна – тя трябва да е между min_salary и max_salary от съответния запис в таблицата departments.

Решение: Задачата е повече от тривиална:
DELIMITER |
CREATE TRIGGER user_salary_check
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
DECLARE var_d_min_s SMALLINT;
DECLARE var_d_max_s SMALLINT;

SELECT min_salary, max_salary
INTO var_d_min_s, var_d_max_s
FROM departments
WHERE id = NEW.department_id;

IF(NEW.salary < var_d_min_s
OR NEW.salary > var_d_max_s)
THEN
SIGNAL SQLSTATE '45000'
SET message_text="Невалидна заплата";
END IF;

END |
DELIMITER ;
Задача 2 (15 точки). Създайте съхранена процедура, при която по подадено id на отдел, генерира резултатна таблица, която да има структурата на следната примерна таблица:

Department_id	Department_name	Kkey	Value
1	Sales	Най-ниска заплата на човек в отдела	950
1	Sales	Най-висока заплата на човек в отдела	1800
1	Sales	Най-ниска позволена заплата в отдела	700
1	Sales	Най-висока позволена заплата в отдела	2000
1	Sales	Средна заплата в отдела	1050

Решение: Тук сложността не е в създаването на самата процедура. Задачата е нестандартна заради вида на таблицата - по редове имаме смислено различни стойности. Подобна таблица едва ли би имала практически полезен смисъл, но в случая провокира мисленето и цели да провери дали сте овладели достатъчно техническия апарат на езика SQL, за да я конструирате по точно този начин. Или по-конкретно - дали сте работили с UNION:
DELIMITER |
CREATE PROCEDURE dept_summary(IN var_d_id TINYINT)
BEGIN
DECLARE var_d_name CHAR(12);
DECLARE var_d_min_s SMALLINT;
DECLARE var_d_max_s SMALLINT;
DECLARE var_d_avg_s SMALLINT;
DECLARE var_u_min_s SMALLINT;
DECLARE var_u_max_s SMALLINT;

SELECT departments.name,
departments.min_salary,
departments.max_salary,
AVG(employees.salary),
MAX(employees.salary),
MIN(employees.salary)
INTO var_d_name, var_d_min_s, var_d_max_s,
var_d_avg_s, var_u_max_s, var_u_min_s
FROM departments JOIN employees
ON departments.id = employees.department_id
WHERE departments.id = var_d_id;

SELECT var_d_id AS Department_id,
var_d_name AS Department_name,
'Най-ниска заплата на човек в отдела' AS Kkey,
var_u_min_s AS Value
UNION
SELECT var_d_id, var_d_name,
'Най-висока заплата на човек в отдела', var_u_max_s
UNION
SELECT var_d_id, var_d_name,
'Най-ниска позволена заплата в отдела', var_d_min_s
UNION
SELECT var_d_id, var_d_name,
'Най-висока позволена заплата в отдела', var_d_max_s
UNION
SELECT var_d_id, var_d_name,
'Средна заплата в отдела', var_d_avg_s;

END |
DELIMITER ;
Задача 3. Трябва да се реализира система за раздаване на бонуси към заплатите. За целта трябва да се създаде нова таблица, в която да се записва id на служител, година, месец и производителност на труда му (performance – число от 0 до 999) за месеца. Преди раздаването на заплатите, мениджърите взимат списък с тримата служители с най-висока производителност и добавят към месечната им заплата възнаграждение в размер 20% от заплатата. Изключение се прави в случая, когато основната заплата + бонуса надхвърлят максималната позволена заплата в отдела – тогава служителя получава максималната заплата за отдел.

a) (5 точки): Създайте новата таблица и начертайте ER диаграма на цялата база от данни

Решение: ER диаграмата ще оставя да направите сами. Създаването на новата таблица е повече от елементарно:
CREATE TABLE bonuses(
employee_id SMALLINT UNSIGNED NOT NULL,
FOREIGN KEY(employee_id) REFERENCES employees(id),
godina INT(4) UNSIGNED NOT NULL,
mesec ENUM("1", "2", "3", "4", "5", "6",
"7", "8", "9", "10", "11", "12") NOT NULL,
performance INT(3) UNSIGNED NOT NULL,
PRIMARY KEY(employee_id, godina, mesec)
)ENGINE = InnoDB;
б) (10 точки): Направете съхранена процедура с два входни параметъра - година и месец, която генерира като result set таблица със следната структура:

Еmpl_id	Empl_name	Performance	Salary	Bonus	Dept_max	Final_salary
92	Иван Петров	955	1100	220	2000	1320
67	Ева Иванова	912	1800	200	2000	2000
112	Ива Петрова	870	1200	240	1500	1440

Забележете, че в примера бонусът на Ева Иванова е редуциран така, че финалната заплата да е максималните за нейния отдел 2000, а не е 2160, колкото би била ако просто бяхме добавили бонус 20% към заплата й.

Решение: Отново някакви сериозни проблеми около самата процедура няма - всичко може да се направи с една единствена SELECT заявка. Тук имаме минимално затруднение относно изчисляването на Final_salary. За целта ще използваме функцията IF.
DELIMITER |
CREATE PROCEDURE top_empl_bonuses(
IN var_godina INT(4) UNSIGNED,
IN var_mesec ENUM("1", "2", "3", "4", "5",
"6", "7", "8", "9", "10", "11", "12")
)
BEGIN
SELECT employees.id AS Еmpl_id,
employees.name AS Еmpl_name,
bonuses.performance AS Performance,
employees.salary AS Salary,
IF(departments.max_salary > employees.salary + employees.salary*0.2,
employees.salary*0.2, departments.max_salary - employees.salary) AS Bonus,
departments.max_salary AS Dept_max,
IF(departments.max_salary > employees.salary + employees.salary*0.2,
employees.salary + employees.salary*0.2,
departments.max_salary) AS Final_salary
FROM employees JOIN departments
ON employees.department_id = departments.id
JOIN bonuses ON employees.id = bonuses.employee_id
WHERE bonuses.godina = var_godina
AND bonuses.mesec = var_mesec
ORDER BY bonuses.performance DESC
LIMIT 3;
END|
DELIMITER ;
Успех на контролната работа в понеделник на всички останали! В тази задача няма трансакции. На контролната работа е възможно да има.