C, PHP, VB, .NET

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


* Задача от контролно 2 – 2015 г. за заминаващи на бригада

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

Задачата е дадена за заминаващите на бригада през 2015 г. Задачата от основното второ контролно през 2015 г. ще бъде формулирана в същия дух.

ФКСУ разполага с три хранилища за стари книжни тела:

  • Дипломните работи се съхраняват 5 години;
  • Курсовите проекти се съхраняват 3 години;
  • Изпитните работи се пазят 5 години.

За всяко едно от споменатите се пази електронен списък със следните данни:

  • Име, фалимилия и факултетен номер на студент, който ги е предал;
  • Име, фалимия и длъжност на преподавателя/ите, който ги е проверил (за дипломните работи е комисия от няколко преподавателя, а за другите е само един);
  • Дата, на която въпросния документ е внесен в хранилището;
  • Номер на хранилището – 1, 2 или 3;
  • Получена оценка от 2 до 6.

Допълнително се съхранява и следната информация:

  • За курсовите проекти и изпитните работи: име на учебен предмет, по който се водят;
  • Само за дипломните работи: тема на дипломната работа и кратък текст с анотация;
  • Само за курсовите проекти: неуникален номер на проекта;
  • Само за изпитните работи: номер на зала, в която е провеждан изпита, дата и час.

Задача 1. Направете ER диаграма на база от данни, която моделира въпросният електронен списък. Обяснете с пояснителен текст защо точно по този начин сте организирали класовете и атрибутите. Ясно обозначете какъв тип данни ще използвате за всеки един атрибут (може на самата диаграма, може и отделно). Ясно обозначете и кои връзки имат каскадни операции и кои нямат. НЕ е задължително да правите CREATE TABLE заявки. НЕ правете INSERT заявки.

Решение: В условието не се изискват CREATE TABLE и INSERT заявки (на изпита времето е малко и по този начин го пестим). В случая ще ги предоставим и тях, за по-ясна демонстрация. Специално отбелязваме, че това не е единственото възможно решение на тази задача (а не е и безпроблемно, както може да се види от допълнителните задачи в края на статията):

ER-brigada

CREATE DATABASE exam_2_brigada;
USE exam_2_brigada;

CREATE TABLE students(
   faculty_id SERIAL PRIMARY KEY,
   firstname VARCHAR(255) NOT NULL,
   lastname VARCHAR(255) NOT NULL
);
INSERT INTO students VALUES 
(123, "Ivan", "Ivanov"),
(345, "Petar", "Petrov"),
(456, "Marina", "Marinova");

CREATE TABLE documents(
   id INT UNSIGNED PRIMARY KEY,
   type ENUM("Diploma", "Project", "Exam") NOT NULL,
   stored_at DATETIME NOT NULL,
   store_id ENUM("1", "2", "3") NOT NULL,
   grade ENUM("2", "3", "4", "5", "6") NOT NULL,
   author BIGINT UNSIGNED NOT NULL,
   FOREIGN KEY(author) REFERENCES students(faculty_id)
);
INSERT INTO documents VALUES
(1, "Exam", DATE_SUB(NOW(), INTERVAL 6 YEAR), "1", "5", 123),
(2, "Exam", DATE_SUB(NOW(), INTERVAL 5 YEAR), "2", "6", 123),
(3, "Project", DATE_SUB(NOW(), INTERVAL 5 YEAR), "3", "6", 123),
(4, "Diploma", DATE_SUB(NOW(), INTERVAL 5 YEAR), "1", "5", 123),
(5, "Exam", DATE_SUB(NOW(), INTERVAL 4 YEAR), "3", "4", 345),
(6, "Exam", DATE_SUB(NOW(), INTERVAL 4 YEAR), "3", "4", 345),
(7, "Project", DATE_SUB(NOW(), INTERVAL 3 YEAR), "2", "5", 345),
(8, "Exam", DATE_SUB(NOW(), INTERVAL 4 YEAR), "3", "4", 456),
(9, "Exam", DATE_SUB(NOW(), INTERVAL 3 YEAR), "2", "5", 456),
(10, "Project", DATE_SUB(NOW(), INTERVAL 2 YEAR), "2", "5", 456),
(11, "Diploma", DATE_SUB(NOW(), INTERVAL 2 YEAR), "3", "5", 456);

CREATE TABLE professors(
   id SMALLINT UNSIGNED PRIMARY KEY,
   firstname VARCHAR(255) NOT NULL,
   lastname VARCHAR(255) NOT NULL,
   position ENUM("assist", "senior assist", "docent", "professor") NOT NULL
);
INSERT INTO professors VALUES
(1, "Dimitar", "Dimitrov", "professor"),
(2, "Todor", "Todorov", "docent"),
(3, "Ivailo", "Ivailov", "senior assist");

CREATE TABLE subjects(
   id SMALLINT UNSIGNED PRIMARY KEY,
   name VARCHAR(255) NOT NULL UNIQUE
);
INSERT INTO subjects VALUES
(1, "Bazi danni"),
(2, "PIK 3");

CREATE TABLE course_exams(
   doc_id BIGINT UNSIGNED PRIMARY KEY REFERENCES documents(id)
	ON DELETE CASCADE ON UPDATE CASCADE,
   room SMALLINT UNSIGNED NOT NULL,
   from_date DATETIME NOT NULL,
   revised_by SMALLINT UNSIGNED NOT NULL,
   FOREIGN KEY(revised_by) REFERENCES professors(id),
   subject_id SMALLINT UNSIGNED NOT NULL,
   FOREIGN KEY(subject_id) REFERENCES subjects(id)
);
INSERT INTO course_exams VALUES
(1, 1152, DATE_SUB(NOW(), INTERVAL 6 YEAR), 1, 1),
(2, 1152, DATE_SUB(NOW(), INTERVAL 6 YEAR), 2, 2),
(5, 1152, DATE_SUB(NOW(), INTERVAL 5 YEAR), 1, 1),
(6, 1152, DATE_SUB(NOW(), INTERVAL 5 YEAR), 2, 2),
(8, 1152, DATE_SUB(NOW(), INTERVAL 4 YEAR), 1, 1),
(9, 1152, DATE_SUB(NOW(), INTERVAL 4 YEAR), 2, 2);

CREATE TABLE course_projects(
   doc_id BIGINT UNSIGNED PRIMARY KEY REFERENCES documents(id)
	ON DELETE CASCADE ON UPDATE CASCADE,
   id INT NOT NULL,
   revised_by SMALLINT UNSIGNED  NOT NULL,
   FOREIGN KEY(revised_by) REFERENCES professors(id),
   subject_id SMALLINT UNSIGNED NOT NULL,
   FOREIGN KEY(subject_id) REFERENCES subjects(id)
);
INSERT INTO course_projects VALUES
(3, 1, 3, 2),
(7, 1, 3, 2),
(10, 1, 3, 2);

CREATE TABLE diploma_thesises(
   doc_id BIGINT UNSIGNED PRIMARY KEY,
   FOREIGN KEY(doc_id) REFERENCES documents(id)
           ON DELETE CASCADE ON UPDATE CASCADE,
   subject VARCHAR(255) NOT NULL,
   annotation TEXT NOT NULL
);
INSERT INTO diploma_thesises VALUES
(4, "Super dimlomna rabota", "AAA"),
(11, "Another super diploma", "AAA");

CREATE TABLE thesis_revisers(
   thesis_id BIGINT UNSIGNED NOT NULL,
   FOREIGN KEY(thesis_id) REFERENCES diploma_thesises(doc_id)
           ON DELETE CASCADE ON UPDATE CASCADE,
   professor_id SMALLINT UNSIGNED NOT NULL,
   FOREIGN KEY(professor_id) REFERENCES professors(id)
           ON DELETE CASCADE ON UPDATE CASCADE,
   PRIMARY KEY(thesis_id, professor_id)
);
INSERT INTO thesis_revisers VALUES
(4, 1), (4, 2);

Задача 2.

Поради препълване на хранилище №2, от факултета са направили реорганизация:

  • Бракували са всички стари книжни тела (т.е. тези, които са внесени преди сроковете, описани в началото на задачата) от всички хранилища;
  • Дипломните работи от хранилище 3 са преместени в хранилище 1;
  • Курсовите проекти и изпитни работи от хранилище 2 са преместени хранилище 3.

А) Създайте съхранена процедура с име “movePaper” със следните характеристики:

  • Има три входни (IN) параметри:
    • storeOut – номер на хранилище, от което ще се вадят книжа;
    • storeIn – номер на хранилище, в което ще се внасят книжа;
    • booksType – типът на книжата, които ще бъдат преместени;
  • Процедурата премества книжата от указания тип от storeOut в StoreIn чрез трансакция;
  • Връща изходен (OUT) параметър result с 1 ако преместването е успешно и 0 ако не е.

Решение:

DELIMITER |
CREATE PROCEDURE movePaper(
	IN storeOut ENUM('1','2','3'),
	IN storeIn ENUM('1','2','3'),
	IN booksType ENUM("Diploma", "Project", "Exam"),
	OUT result BIT)
BEGIN
START TRANSACTION;

UPDATE documents SET store_id = storeIn
WHERE  documents.store_id = storeOut
	 AND
       documents.type = booksType;

IF(ROW_COUNT() = 0)
THEN
	ROLLBACK;
	SET result = 0;
ELSE
	COMMIT;
	SET result = 1;
END IF;
END |
DELIMITER ;

Б) Създайте съхранена процедура „maintenance2015”, която е без входно/изходни параметри и която извършва реорганизацията дефинирана в задача 2, като внася съответните промени в електронния списък. Всичко трябва да се извърши чрез трансакция и е крайно желателно да се възползвате от вече създадената в подточка A) процедура movePaper.

Помощ: За 1 година по-рано от дадена дата се използва DATE_SUB(theDate, INTERVAL 1 YEAR);

Решение:

DELIMITER |
CREATE PROCEDURE maintenance2015()
BEGIN
DECLARE var_dummy BIT;
START TRANSACTION;

# Изтриваме старите документи
DELETE FROM documents 
WHERE (
       (type = "Diploma" OR type = "Exam")
  	  AND
	stored_at < DATE_SUB(NOW(), INTERVAL 5 YEAR)
      )
      OR
      (
       type = "Project"
          AND
       stored_at < DATE_SUB(NOW(), INTERVAL 3 YEAR)
      );

# Местим останалите по указания начин
CALL movePaper("3", "1", "Diploma", var_dummy);
CALL movePaper("2", "3", "Project", var_dummy);
CALL movePaper("2", "3", "Exam", var_dummy);

COMMIT;
END |
DELIMITER ;

Допълнителна задача за упражнение (извън контролната работа): В горната реализация интегритета на данните може да бъде нарушен – възможно например даден документ да е „Diploma“, но за него да бъде вмъкнат ред в таблицата  „course_exams“. Създайте тригери, с които да гарантирате коректността при вмъкване на информация в трите подкласа (трябва да се проверява типа на документа, в противен случай да се генерира SIGNAL);

 



19 коментара


  1. Отбелязани са. Думичката „REFERENCES“ го прави.

  2. kraikov каза:

    Нарочно ли външните ключове не са отбелязани като такива?

  3. Георги каза:

    Здравейте.
    1. Защо в случая имаме повторение на колони subject_id. Не е ли по-добре да направим още един подклас на Document (характеризиращ обект), който да поеме общата колона и евентуално други общи такива. След това двата подкласа да „наследят“ въпросния междинен. Може би не сте го направили заради „прекалената нормализация“?
    2. Защо ни е нужна колоната type в Document? Не се ли подразбира щом дадено id присъства в таблицата с дипломни, че този документ е дипломна?

  4. Георги:

    1. Напълно възможно е. Възможно е и subject_id да е в Documents, като за Diploma да е NULL. Изобщо даденото не е единствено решение. В реална ситуация винаги се правят някакви компромиси с нормализацията с цел бързодействие.

    2. За удобство. Иначе ще трябва да правим 1, 2 или в най-лошия случай 3 SELECT заявки, за да видим какъв тип е документа. Или пък една, но много завъртяна и сложна (добра идея е да се помисли как да се направи).

  5. Иван каза:

    Какво е ролята на променливата var_dummy ?

  6. Процедурата от т.А, която ние извикваме, връща резултат в нея. Ние реално не го използваме, но сме принудени да го приемем – затова и променливата съм я кръстил „dummy“.

  7. тедо каза:

    защо стойносста на „author“ може да е „NULL“?

  8. Вероятно защото не е догледано и не е сложено NOT NULL. Позабравих я вече тази задача, но ми се струва логично всеки документ със сигурност да му се знае автора. Анонимни изпитни работи, курсови задачи или проекти, вероятно няма смисъл да се пазят.

  9. Ангелов каза:

    На редовното контролно, нещо с такова ниво на сложност ли ще бъде?

  10. Ангелов каза:

    И по-точно кои теми ще бъдат включени?? Само до процедури и тригери ли? Защото има доста други теми, които сме прескочили на упражненията?

  11. Сложността ще е такава.

    Трансакции, процедури, тригери + проектиране

    Тези теми естествено съдържат в себе си нуждата да можете да правите create table, да знаете select, insert, update, delete…

    Т.е. ВСИЧКО :)

  12. Константин каза:

    Колко са максималните точки ,които можем да вземем от всяка задача?

  13. 20 на задача.

  14. Ioana каза:

    Може ли малко помощ относно тригера в допълнителната задача?

  15. Да. Как мога да ти помогна?

  16. Студент каза:

    Какво точно означава ROW_COUNT() и в случая може ли да се замени с нещо друго?

  17. ROW_COUNT() – колко реда от последната заявка са били променени. В случая ако е 0, значи UPDATE заявката не е направила нищо.

    Maria – ще покрива всичко, което е учено по учебен план до упражнение 7 (без него).

  18. Maria каза:

    На второ контролно ще има ли да се ползва оптимизация, работа с виртуални таблици, симулация на check с тригери/view, събития(events)?

  19. Ангелов каза:

    Това решение на доп. задача правилно ли е ?
    delimiter //
    create trigger a
    before insert on diploma_thesises
    for each row
    if((select type
    from document
    where id = new.doc_id )!= „Diploma“)
    then
    signal sqlstate 45000 set_message = „ERROR“;
    end if;
    end
    //

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

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


*