C, PHP, VB, .NET

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

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

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

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

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

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

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

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

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

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);