C, PHP, VB, .NET

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


* Задача от 23.05.2013

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

Задачата е дадена на контролната работа за заминаващите на студентска бригада. В отдел „човешки ресурси“ на фирма се пази информация за служителите и техните заплати по следния начин:
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 ;

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

 



8 коментара


  1. Да, в решението на задачата съм дал едно, в примерната таблица друго. Сега ще поправя примерната таблица.

    П.П. Ако трябваше да се направи както е в примерната таблица, щеше да е по-лесно :)

  2. Георги Братков каза:

    На задача 3, подусловие б), не би ли трябвало проверката за бонуса да отпадне или да се промени примерната таблица, тъй като в нея бонуса за Ева Иванова е 360, въпреки че с него надхвърля максималната за отдела заплата?

  3. Асен каза:

    IF(departments.max_salary > employees.salary + employees.salary*0.2,//до тук ясно отаналото каква роля има
    employees.salary*0.2, departments.max_salary – employees.salary)?

  4. Това е функция, не оператор.

    Първото е връщана стойност при TRUE, второто връщана стойност при FALSE.

  5. Глас в Пустиня каза:

    Искам да изкажа тоталното си разочарование от вас! След днешната контролна работа Вие наистина успяхте да покажете своята дребнава и мизерна същност. Успяхте наистина да паднете в очите ми, а мисля, че и в очите на още много хора. Не може асистент във ВУЗ да се държи като дете от детската градина. Ако имате поне малко достойнство вземете и си подадете оставката!

  6. Хубаво е да се говори с конкретни аргументи. Може би изговането на студент, който подсказва на друг свой колега, при това след повторна забележка?

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

    П.П. Не съм на изборна длъжност и не знам за каква „оставка“ говорите…

  7. Глас в Пустиня каза:

    Изгонването на студент, който преписва е правилно. Говаря за некоректното ви отношение към студентите и то не само днес. Искате конкретни примери – вземете второто контролно, със сбърканото условие. Това не някакво си упражнение за 3т. Дори ако имам максимум точки досега, това контролно убива всичките ми шансове за добра оценка, да не говорим какво става с хората, които имат по-малко точки. Като ви попита някой за разяснение, вие му отговаряте, че всичко е ясно, но явно щом някой ви пита, това не е така!

    Както и да е. Всеки , който има поне малко ум в главата знае защо днес дадохте такова „дефектно“ контролно и такава курсова. Може да отричате колкото си искате, но истината си е истина.

    ––––––
    Глас в Пустиня

  8. Да, две технически грешки имаше – не смятам, че са допринесли за някакво свръх объркване, че да станат непосилни. Подчертавам технически, защото бяха точно такива. Не смятам, че задачите са били некоректни – всъщност са доволно лесни. Ще ви публикувам и решение – ще подбера такова, което е направил студент. Дали е „дефектно“ или не ще покажат резултатите.

    П.П. Въпросите и разясненията се дават по време на подготовката, а не по време на контролните и изпитите.

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

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


*