C, PHP, VB, .NET

* Задача от контролно 2 – 2015 г. вариант 1

Публикувано на 17 юни 2015 в раздел Бази от Данни.

CREATE TABLE schools(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL
);

INSERT INTO schools(name, city) VALUES
("1 SOU", "Plovdiv"), ("2 SOU", "Sofia");

CREATE TABLE classes(
  school_id SMALLINT UNSIGNED NOT NULL
                REFERENCES schools(id),
  year ENUM("1", "2", "3", "4", "5", "6", 
            "7", "8", "9", "10", "11", "12") NOT NULL,
  letter ENUM("A", "B", "C", "D", "E", "F"),
  PRIMARY KEY(school_id, year, letter)
);

INSERT INTO classes(school_id, year, letter) VALUES
(1, "1", "A"), (1, "1", "B"), (1, "1", "C"), 
(1, "1", "D"), (1, "1", "E"), (1, "1", "F"), 
(1, "2", "A"), (1, "2", "B"), (1, "2", "C"), 
(1, "2", "D"), (1, "2", "E"), (1, "2", "F"), 
(1, "3", "A"), (1, "3", "B"), (1, "3", "C"), 
(1, "3", "D"), (1, "3", "E"), (1, "3", "F"), 
(2, "1", "A"), (2, "1", "B"), (2, "1", "C"), 
(2, "1", "D"), (2, "1", "E"), (2, "1", "F"), 
(2, "2", "A"), (2, "2", "B"), (2, "2", "C"), 
(2, "2", "D"), (2, "2", "E"), (2, "2", "F"), 
(2, "3", "A"), (2, "3", "B"), (2, "3", "C"), 
(2, "3", "D"), (2, "3", "E"), (2, "3", "F");


CREATE TABLE students(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(255) NOT NULL,
  lname VARCHAR(255) NOT NULL,
  school_id SMALLINT UNSIGNED NOT NULL,
  year ENUM("1", "2", "3", "4", "5", "6", 
            "7", "8", "9", "10", "11", "12") NOT NULL,
  letter ENUM("A", "B", "C", "D", "E", "F"),
  FOREIGN KEY(school_id, year, letter)
          REFERENCES classes(school_id, year, letter)  
);

INSERT INTO students(fname, lname, school_id, year, letter)
VALUES
("Ivancho", "Ivanchov", 1, "1", "A"),
("Mariika", "Mariikova", 1, "1", "A"),
("Toshko", "Toshkov", 1, "2", "A");

CREATE TABLE authors(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(255) NOT NULL,
  lname VARCHAR(255) NOT NULL,
  user VARCHAR(64) NOT NULL UNIQUE,
  pass VARCHAR(64) NOT NULL
);

INSERT INTO authors (fname, lname, user, pass) VALUES
("Ivan", "Ivanov", "ivan", "password"),
("Petar", "Petrov", "petar", "123456");

CREATE TABLE problems(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(author_id) REFERENCES authors(id),
  description TEXT NOT NULL,
  correct_answer VARCHAR(255) NOT NULL
);

INSERT INTO problems(author_id, description, correct_answer)
VALUES
(1, "2+2 = ?", "4"), (1, "2+1 = ?", "3"), 
(1, "5+3 = ?", "8"), (1, "3+3 = ?", "6"),
(2, "2*2 = ?", "4"), (2, "2*1 = ?", "2"), 
(2, "5*3 = ?", "15"), (2, "3*3 = ?", "9");

CREATE TABLE distractors(
  problem_id INT UNSIGNED NOT NULL,
  FOREIGN KEY(problem_id) REFERENCES problems(id),
  dist1 VARCHAR(255) NOT NULL,
  dist2 VARCHAR(255) NOT NULL,
  dist3 VARCHAR(255) NOT NULL
);

INSERT INTO distractors(problem_id, dist1, dist2, dist3)
VALUES
(3, "2", "7", "9"), (4, "4", "5", "7"), 
(7, "10", "20", "8"), (8, "6", "12", "8");

CREATE TABLE problems_classes(
  problem_id INT UNSIGNED NOT NULL,
  FOREIGN KEY(problem_id) REFERENCES problems(id),
  school_id SMALLINT UNSIGNED NOT NULL,
  year ENUM("1", "2", "3", "4", "5", "6", 
            "7", "8", "9", "10", "11", "12") NOT NULL,
  letter ENUM("A", "B", "C", "D", "E", "F"),
  FOREIGN KEY(school_id, year, letter)
          REFERENCES classes(school_id, year, letter),
  PRIMARY KEY(problem_id, school_id, year, letter)
);

INSERT INTO problems_classes(problem_id, school_id,
                             year, letter) VALUES
(1, 1, "1", "A"), (1, 1, "1", "B"), (1, 1, "1", "C"),
(1, 1, "1", "D"), (1, 1, "1", "E"), (1, 1, "1", "F"),
(1, 2, "1", "A"), (1, 2, "1", "B"), (1, 2, "1", "C"),
(1, 2, "1", "D"), (1, 2, "1", "E"), (1, 2, "1", "F"),
(2, 1, "1", "A"), (2, 1, "1", "B"), (2, 1, "1", "C"),
(2, 1, "1", "D"), (2, 1, "1", "E"), (2, 1, "1", "F"),
(3, 1, "1", "A"), (3, 1, "1", "B"), (3, 1, "1", "C"),
(4, 1, "1", "A"), (4, 1, "1", "B"), (4, 1, "1", "C"),
(5, 1, "2", "A"), (5, 1, "2", "B"), (5, 1, "2", "C"), 
(5, 1, "2", "D"), (5, 1, "2", "E"), (5, 1, "2", "F"), 
(5, 2, "2", "A"), (5, 2, "2", "B"), (5, 2, "2", "C"), 
(5, 2, "2", "D"), (5, 2, "2", "E"), (5, 2, "2", "F"), 
(6, 1, "2", "A"), (6, 1, "2", "B"), (6, 1, "2", "C"), 
(7, 1, "2", "A"), (7, 1, "2", "B"), (7, 1, "2", "C"), 
(8, 1, "2", "A"), (8, 1, "2", "B"), (8, 1, "2", "C");

DELIMITER //
CREATE PROCEDURE generate_test_table(IN var_school_id SMALLINT,
                 IN var_year ENUM("1", "2", "3", "4", "5", "6", 
                              "7", "8", "9", "10", "11", "12"),
                 IN var_letter ENUM("A","B","C","D","E","F"),
                 IN var_problems_count TINYINT,
                 IN var_open_answers TINYINT)
BEGIN
  DECLARE var_closed_answers TINYINT;
  SET var_closed_answers = var_problems_count - var_open_answers;
  IF(var_closed_answers < 0)
  THEN
    SIGNAL SQLSTATE "45000"
    SET message_text="Open answers cannot be more than total amount";
  END IF;
  
  CREATE TEMPORARY TABLE problems_temp_table(
    description TEXT NOT NULL,
    correct_answer VARCHAR(255) NOT NULL,
    dist1 VARCHAR(255) NULL DEFAULT NULL,
    dist2 VARCHAR(255) NULL DEFAULT NULL,
    dist3 VARCHAR(255) NULL DEFAULT NULL
   );
   
   INSERT INTO problems_temp_table(description, correct_answer,
                                           dist1, dist2, dist3)
   (SELECT description, correct_answer, NULL, NULL, NULL
    FROM problems
    WHERE id NOT IN(SELECT problem_id FROM distractors)
             AND
          id IN (SELECT problem_id FROM problems_classes
                 WHERE school_id = var_school_id
                          AND
                       year = var_year
                          AND
                       letter = var_letter)
    ORDER BY RAND() LIMIT var_open_answers)
      UNION
   (SELECT problems.description, problems.correct_answer, 
          distractors.dist1, distractors.dist2, distractors.dist3
    FROM problems 
    JOIN distractors ON problems.id = distractors.problem_id
    WHERE id IN (SELECT problem_id FROM problems_classes
                 WHERE school_id = var_school_id
                          AND
                       year = var_year
                          AND
                       letter = var_letter)
    ORDER BY RAND() LIMIT var_closed_answers);
END//
DELIMITER ;

DELIMITER ;
CREATE PROCEDURE get_test(IN var_student INT UNSIGNED)
BEGIN
  DECLARE var_school_id SMALLINT UNSIGNED;
  DECLARE var_year ENUM("1", "2", "3", "4", "5", "6", 
            "7", "8", "9", "10", "11", "12");
  DECLARE var_letter ENUM("A", "B", "C", "D", "E", "F");
  
  SELECT school_id, year, letter
  INTO var_school_id, var_year, var_letter
  FROM students
  WHERE id - var_student;
              
  CALL generate_test_table(var_school_id, var_year,
                                  var_letter, 4, 2);
  
  SELECT problems_temp_table.*, students.fname, 
         students.lname, schools.name, var_year, var_letter
  FROM problems_temp_table
  JOIN students
  JOIN schools ON students.school_id = schools.id
  WHERE schools.id = var_school_id
          AND
        students.id = var_student;
END//
DELIMITER ;

13 коментара

1. ас. Петров, смятате ли, че задачите, които дадохте на 4-те контролни (това за бригадирите, това в понеделник и двете в събота) са с еднаква сложност? Според мен и дума не може да става за сравнение между контролното от понеделник и варианта с рок групите от събота! До днес, мнението на студентите за Вас беше, че като цяло сте доста обективен и безпристрастен, в случая обаче не можете да претендирате за обективност и справедливост! Редно е критериите и метода на оценяване да са еднакви за всички студенти, ясно е, че това е неосъществимо при задачи с космическа разлика в трудността. В сайта на проф. Гоцева беше уточнено, че извънредното контролно е само за хора, които не могат по основателна причина да се явят на редовното, не виждам смисъл в такъв случай да се толерира ''ранното'' им явяване.

2. Здравей Влади,

   Моля бъди по-конкретен.

   Задачата от 08-ми по ER диаграма e точно тази задача от тази статия (!), но с известни (смятам равностойни) промени в условието. Можеш да питаш колегите, които се явяваха на нея. Да се твърди, че в понеделник задачата е била по-лесна от тази, аз смятам за нелепо, защото това е... ами същата задача.

   Вариантът с рок групите ще го публикувам днес или утре. Какво пък й беше "космически трудното" на нея? Забелязвам, че много хора са тръгнали да я решават с цикли и правене на много заявки една след друга все едно решават задачата в някой език за програмиране... но това не значи, че няма решение от една простичка self-join заявка. Пък и решенията с цикли също се приемат, нищо че са адски неоптимални.

   Заминаващите на бригада имаха по-сложна първа задача с проектиране, но по-лесна процедура. Сега доколко проектирането е по-сложно от писането на код, това е спорен въпрос.

   Не е лошо да се гледа на трудността и спрямо резултатите. Средният брой точки в различните групи е доста сходен - в рамките на 20-23 точки от 40 е.

3. Здравейте отново, радвам се, че не игнорирахте коментара ми и ще се постарая да бъда максимално изчерпателен и да отговоря на всичките Ви въпроси. Първо, задачата, към която беше насочено вниманието ми в първия коментар, не е ТАЗИ задача, сравнението е между задачата от понеделник ("сюжетът" е като този, но задачите са доста по-леки) и задачата с рок концертите от събота. Не бих квалифицирал последната като "космически трудна", по-скоро като "космически ПО-трудна" в сравнение с останалите 3 и особено с тази от понеделник. За да бъда максимално точен, ще уточня: не сложността на задачата е космическа, а РАЗЛИКАТА в сложността. Естествено, колегите от понеделник ни бяха предупредили, че нашата задача, по ваши думи, щяла да бъде по-трудна от тяхната(което също е адски нелогично!?). Спрямо резултатите не смятам, че можем да съдим, поради простата причина че Вие сте този, който проверява резултатите, а те, разбира се, подлежат на манипулация.

4. Колега,

   Ето решението на задачата с рок концерта:
   https://www.cphpvb.net/db/9698-exam-2-2015-var-2/

   Ако продължавате да смятате, че е по-трудна от тази задача тук, давайте да коментирате по същество - какво точно и най-вече ме интересува защо. Нейната ER диаграма е значително по-проста, няма M:M връзки, втората задача е меко казано елементарна... И да, задача 3 не е лесна, но то пък не може всичко да е лесно, нали?

   Колкото до понеделник - както казах вариант 1 от събота е преработена задачата от понеделник. А това, че се пошегувах с колегите ви (защото бяха 5 човека при очаквани много повече), а те са го приели сериозно, не означава, че е истина.

5. По-трудно е наличието на self join, както и многобройните проверки, които трябва да бъдат направени. Затруднява и фактът, че вместо сигнал, например, резултатите се записват в нова таблица (това удължава процедурата). Тази нова таблица се състои от колони от няколко други таблици, тоест, трябва да направим и JOIN на всички тях. По-трудна е и самата проверка за застъпване на двата интервала.
   Не мога да Ви кажа със сигурност дали сте се пошегували, или не, но е факт, че сравняваме задача със, да, сравнително лесна ЕР диаграма и self join със задача от 2 таблици и create заявки. Наистина ли смятате, че са равностойни?

6. Хайде сега:

   Задача 1 на вариант 1 е със 7 таблици. Задача 1 на вариант 2 е със 4 таблици.

   Сравнете задача 3 на вариант 1 със задача 2 на вариант 2. В първия вариант се правят три заявки, а във втория една не по-сложна.

   Единственото, за което сте прав, е че в задача 3 на вариант 2 има self join и то е по-трудно. Не е ли обаче това напълно нормално, при положение, че другите две задачи са били по-лесни??? При това нека разгледаме подробно задача 2 на вариант 1 спрямо задача 3 на вариант 2:

   - "Вместо сигнал" - във вариант 2 няма signal, а в 1 има
   - В задача 2 на вариант 1 също има temporary table, който СЪЩО включва информация от две различни таблици и се прави JOIN между тях
   - Условието where е по-трудно с едно логическо "И" за сцената. Иначе във вариант 1 също има двойно условие в WHERE. А доколко "x AND (y or z)" е по-лесно от "x NOT IN(yyy) AND x IN (zzz)" с добавен ORDER BY RAND()?

   Няма да продължавам, защото ще вземе да влезе някой недоволен, че задача 1 е била по-трудна :)

7. Вие явно ще оспорвате до край, но няма да признаете, че задачите не са равоностойни... В крайна сметка нещата са ясни според мен. Ако не вярвате на студентското мнение,дайте задачите на някой безпристрастен преподавател, проф. Гоцева например, и нека тя да прецени и да отсъди. (Пак визирам задачата от понеделник и задачата от събота с фестивала). Наистина е безсмислено и нелепо да се опитвате да докажете, че задачите са с еднаква тежест. Ако желаете, можете да качите и задачата от понеделник, за да видим все пак за какво става дума, защото слушахме за нея много, но да си призная и аз не съм я виждал. Както и да е, аз съм доволен от резултата си, в случай, че си мислите, че просто плюя системата заради слабия си успех.

8. Влади,

   Аз се аргументирам с факти колега. В случая не говорим за субективно мнение "според мен е по-трудна", а показвам директно - в една задача има такива и такива компоненти, а в друга има такива и такива.

   Факт 1. Задача 1 и задача 3 от вариант 1 са по-сложни от задача 1 и задача 2 на вариант 2.

   Факт 2. Задача 2 на вариант 1 има signal, а в задача 3 на вариант 2 няма

   Факт 3. Задача 2 на вариант 1 и задача 3 на вариант 2 имат равностойно сложни temporary tables, в които се събира информация от две различни таблици с JOIN.

   Факт 4. В задача 2 на вариант 1 има вадене на произволни редове с ORDER BY RAND(). За сметка на това в задача 3 на вариант 2 е компенсирано с използване на функции за дата и час.

   Събирайки фактите дотук еднозначно излиза, че вариант 1 е по-сложен от вариант 2. Е, ако не беше компенсирано с повишаване на трудността във варинат 2 - в случая с добавяне на self join - наистина нямаше да бъде честно.

   Всички асистенти, както и водещия преподавател са видяли задачите.

   Ще ви кача задачата от понеделник. Тя е същата като тази с козметични разлики.

9. Задачите определено са с различна сложност. Това с компенсирането не върви, според мен. Не може да кажете че self join компенсира по-сложното проектиране - едното няма общо с другото, просто се чудите как да избегнете ситуацията. Едно е да сложиш 2 таблици повече, друго е да напишеш self join. Обяснявам защо: за да направиш 2те таблици, имаш всичко поставено много ясно в условието- какви колони и какви ограничения ще се съдържат в тях, а за self join-а няма дори бегъл намек - просто е казано какво трябва да прави процедурата в най-общ план.
   Относно ORDER BY RAND() - има ясна и конкретна подсказка, че това трябва да се сложи след заявката, за да ни го подреди в произволен ред, тоест без въобще да разбираш, може просто да го поставиш след тривиалната заявка. Докато функциите за дата и час - просто е споменато как се вади ден от дата, година от дата и тн... Нищичко не е казано за това как трябва да се направят сравненията, не че е лошо, трябва да се измисли логиката, докато за подреждането, просто е дадено наготово.
   "Факт 2. Задача 2 на вариант 1 има signal, а в задача 3 на вариант 2 няма" - защо сравнявате 2ра с 3та задача от два различни варианта. Това е все едно да сравним първа с трета от двата варианта - сравняват се 1 с 1, 2 с 2 и тн...Задача 3 на вариант 2 е достатъчно сложна, за да има сигнал - а и то няма как да има сигнал, защото процедурата просто ще разпечата на екрана някаква таблица с грешки, ако имаше сигнал щеше да бъде прекъсвана самата процедура, а това е безсмислено тъй като тя нищо не прави по данните.
   Съдите по резултати на 5ма студенти, съпоставяйки ги с резултати на 200 - някак си от статистическа гледна точка не е много устойчиво това сравнение. А относно резултатите - инструментът е във вашите ръце, ако вие видите, че резултатите на втори вариант са по-слаби и студентите не са се справили с 3та задача или като цяло, то нищо не пречи да си свалите малко критериите с цел да ги доближите максимално до средния резултат, изкаран от групите с първи вариант, за да няма недоволни и вашите ръце да са чисти.

10. Ас. Петров, наистина, качете варианта от понеделник, и нека тогава говорим с факти. Фактите са достатъчно красноречиви и без това, но нека все пак това, което коментираме, го видим всички. Да видим дали наистина условието включва create заявки и елементарен селект... :)Вие самият можете да видите колко време ще ви отнеме да решите едната, и колко другата задача.

11. Колега,

    Тук вече говорите пълни небивалици, за които си залагам работното място, дипломите и името.

    Всеки учебен предмет включва определен набор от знания. Естествено е едни да имат повече или по-малко логически връзки в тях в сравнение с други. Затова когато става дума за оценяване, знанията се обединяват в малки равностойни групи - нека ги номерираме от k1, ... kn. На един изпит, който е от 2 учебни часа, НЕ може да бъдат проверени ВСИЧКИ знания от предмет, който се е водил цял семестър. Може да бъдат проверени само подмножества. Учебният материал по който се изпитва е "всичко изучено", а се пада на изпита "част от него". Така например при проф. Наков получавате два въпроса от конспект от 50. Там протестирате ли, че "не са се паднали едни и същи въпроси на различните изпити"? Не, нали? Тук е същото - на един изпит се падат едни групи знания, на друг изпит - други. В противен случай изпитите ще бъдат компрометирани.

    И като сме тръгнали в конкретика - твърдите, че "2 таблици повече" не било нищо сериозно, но резултатите от първото контролно - на заминаващите на бригада - показаха съвсем друго нещо. Там резултатите именно по първа задача бяха повече от трагични - хората се справиха много по-добре с другата. Това беше коментирано сред преподавателския екип и това беше именно причината да облекчим задачата за проектиране за сметка на другите в следващите изпити.

    В крайна сметка погледнете резултатите от контролните. Сметнете средния брой точки на явилите се - равен ли е (в рамките на 1-3 точки разлика)? Пуснете тестовете през статистически анализ - примерно на Ман-Уитни теста. Статистически равни ли са? Е след като са се получили равни крайни резултати, къде е разликата?

    Колкото до "смъкването на летвата" - от педагогическа гледна точка това се прави само при едно условие: ако нито един човек не се е справил с дадена задача, тя отпада от оценяването. Така се прави по устав. В случая категорично няма такова нещо.

    Дискусията ни приключва тук. Напишете подписка и подайте жалба до проф. Гоцева, аз нямам нищо против да ви бъдат анулирани резултатите. По ПИК2 вече веднъж беше правено, повторете го. Няма да се заричам (въпреки, че лично съм убеден), но очаквам резултатите да не бъдат различни (както и по ПИК2 не се получиха различни). Само ще си загубите времето.

    Ще ми направите голяма услуга ако направите така, че да не ви оценявам аз от тук нататък. То и без това от септември вероятно ще приключвам работата си в университета.

12. Влади,

    Задачата ми е на компютъра в университета - ще го направя утре.

13. Влади,

    Намерих условието в пощата си. Ето го:

    Име: ….......................................................................................... ф№...................................... Група ..........
    Трябва да се направи програма за генериране на изпитни тестове, които се отпечатват на хартия. За целта трябва да се създаде база от данни, в която да се пази следната информация:

    • Потребителите на системата са учители - автори на задачи. За тях се пази потребителско име, парола, име и фамилия;
    • В базата се предвижда да има голям набор от тестови задачи;
    • Всяка тестова задача, освен условие, трябва да отговаря на следните условия:
    o Пази се кой е автора на задачата;
    o Пази се верния отговор на задачата (текст);
    o Пазят се (незадължително) и „грешни“ отговори (дистрактори). Ако такива дистрактори са налични, задачата се приема, че е с избираем отговор. Ако не са налични, задачата се приема да е с отворен отговор;
    o Информация за кой клас (число от 1 до 12) е тестовата задача.

    Задача 1. Начертайте ER диаграма за описаната база от данни. Постарайте се базата да е оптимално нормализирана.

    Задача 2. Направете CREATE TABLE заявки, които напълно отговарят на ER диаграмата от задача 1.

    Задача 3. Напишете SELECT заявка, която извежда условията на 10 произволно избрани задачи за 12 клас, като до тях добавите верните им отговори и евентуални дистрактори. За да вземете произволни задачи използвайте “ORDER BY RAND()”. Например следната заявка би изкарала 5 произволни потребителски имена:
    SELECT username FROM users
    ORDER BY RAND()
    LIMIT 5;

    Задача 4. Напишете съхранена процедура generate_test, която приема три параметъра: var_class - клас, за който ще бъде теста; var_problems_count – колко задачи да има теста; var_open_answers – колко от задачите да имат отворен отговор.
    В процедурата да се извършат следните действия:

    • Ако var_open_answers е по-голямо от var_problems_count да се генерира SIGNAL със съобщение за грешка „Open answers cannot be more than total amount of answers”;
    • Създава временна (Memory) таблица “problems_temp_table”, която се състои от идентификатор на задача, условието й и верен отговор. След това намира var_open_answers на брой произволни въпроса с отворен отговор (т.е. такива, които са без дистрактори) и ги записва в problems_temp_table;
    • Създава временна (Memory) таблица “closed_answers_temp_table”, която се състои от идентификатор на задача и грешен отговор. Намира var_problems_count - var_open_answers на брой произволни въпроса със затворен отговор (т.е. такива, за които има дистрактори) и ги записва в problems_temp_table. След това записва дистракторите на тези намерените задачи в closed_answers_temp_table;
    • Прави SELECT * FROM problems_temp_table LEFT JOIN closed_answers_temp_table ON …
    Довършете ON условието и направете таблица с примерен изход от тази заявка...