C, PHP, VB, .NET

В статията за вложен SELECT представихме едно допълнение към ER диаграмата за база данни на университет. Да припомним – проблемът беше, че в оригиналния ER модел връзката между студенти и факултети минаваше през записани учебни предмети. Така ние нямаше възможност да разберем от кой факултет е даден студент, ако той не е записал нито един учебен предмет. Предложеното решение беше да пазим допълнителен външен ключ от таблицата „студенти“ към таблицата „факултети“:

Това обаче не решава друг основен проблем – какво гарантира, че дадения студент ще запише само и единствено предмети от този факултет? Отговорът е, че все още нищо не го гарантира! Нека демонстрираме това като построим същата диаграма в опростен вид (без таблици университети, преподаватели и задочници, а директна връзка факултети -< предмети >-< студенти):

CREATE TABLE faculties(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY(id),
   `name` VARCHAR(255) NOT NULL UNIQUE
) ENGINE=INNODB;

CREATE TABLE subjects(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY(id),
   `name` VARCHAR(255) NOT NULL,
   `faculty_id` INT UNSIGNED NOT NULL,
   FOREIGN KEY(faculty_id) REFERENCES faculties(id),
   UNIQUE(name, faculty_id)
) ENGINE=INNODB;

CREATE TABLE students(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY(id),
   `name` VARCHAR(255) NOT NULL,
   `faculty_id` INT UNSIGNED NOT NULL,
   FOREIGN KEY(faculty_id) REFERENCES faculties(id),
   UNIQUE(name, faculty_id)
) ENGINE=INNODB;

CREATE TABLE subjects_students(
   `student_id` INT UNSIGNED NOT NULL,
   `subject_id` INT UNSIGNED NOT NULL,
   PRIMARY KEY(student_id, subject_id),
   FOREIGN KEY(student_id) REFERENCES students(id),
   FOREIGN KEY(subject_id) REFERENCES subjects(id)
) ENGINE=INNODB;

Сега да вмъкнем информация в таблиците и да демонстрираме проблема:

INSERT INTO faculties(`name`)
      VALUES ("FKSU"),("FET");

INSERT INTO subjects(`name`, `faculty_id`)
VALUES ("Bazi Danni", (SELECT id FROM faculties WHERE name="FKSU")),
       ("Sapromat", (SELECT id FROM faculties WHERE name="FET"));

INSERT INTO students(`name`, `faculty_id`)
VALUES ("Ivan Ivanov", (SELECT id FROM faculties WHERE name="FKSU"));

Така имаме два факултета (FKSU и FET), които имат по един учебен предмет всеки:

SELECT * FROM faculties;
+----+------+
| id | name |
+----+------+
|  2 | FET  |
|  1 | fksu |
+----+------+

SELECT * FROM subjects;
+----+------------+------------+
| id | name       | faculty_id |
+----+------------+------------+
|  1 | Bazi Danni |          1 |
|  2 | Sapromat   |          2 |
+----+------------+------------+

mysql> SELECT * FROM students;
+----+-------------+------------+
| id | name        | faculty_id |
+----+-------------+------------+
|  1 | Ivan Ivanov |          1 |
+----+-------------+------------+

Ние сме сигурни, но нека все пак се убедим, че проблема е налице – ще запишем Иван Иванов едновременно и за двата предмета, които са от различни факултети:

INSERT INTO subjects_students(subject_id, student_id)
VALUES ((SELECT id FROM subjects WHERE name="Bazi Danni"),
              (SELECT id FROM students WHERE name="Ivan Ivanov")),
       ((SELECT id FROM subjects WHERE name="Sapromat"),
              (SELECT id FROM students WHERE name="Ivan Ivanov"));

Проблемът вече е налице и имаме невалидна информация в базата данни:

SELECT students.name AS student, faculties.name AS faculty
FROM students JOIN faculties ON students.faculty_id = faculties.id
WHERE students.name="Ivan Ivanov";
+-------------+---------+
| student     | faculty |
+-------------+---------+
| Ivan Ivanov | FKSU    |
+-------------+---------+

SELECT students.name AS student, faculties.name AS faculty
FROM students
JOIN subjects_students ON students.id = subjects_students.student_id
JOIN subjects ON subjects.id = subjects_students.subject_id
JOIN faculties ON faculties.id = subjects.faculty_id
WHERE students.name = "Ivan Ivanov";
+-------------+---------+
| student     | faculty |
+-------------+---------+
| Ivan Ivanov | FKSU    |
| Ivan Ivanov | FET     |
+-------------+---------+

Очевидно, за да се справим с този проблем ние трябва да НЕ позволяваме в таблицата subjects_students да се записват предмети, които са от „чужд“ факултет. Това може да се постигне чрез ограничение CHECK. Ето как можем да поправим предишната CREATE TABLE заявка, за таблица „subjects_students“:

CREATE TABLE subjects_students(
   `student_id` INT UNSIGNED NOT NULL,
   `subject_id` INT UNSIGNED NOT NULL,
   PRIMARY KEY(student_id, subject_id),
   FOREIGN KEY(student_id) REFERENCES students(id),
   FOREIGN KEY(subject_id) REFERENCES subjects(id),
   CHECK (subject_id = (SELECT subjects.id
			FROM subjects
			WHERE subjects.faculty_id=(
				SELECT students.faculty_id
				FROM students
				WHERE students.id = student_id
			))
	 )
) ENGINE=INNODB;

Така ние въвеждаме ограничение, чрез което забраняваме добавянето на предмет от различен факултет от този на студента. За съжаление тази функционалност все още не работи в СУБД MySQL. До сега (най-новата версия е MySQL 5.1.41) CHECK ограниченията се приемат от MySQL като валидни записи, но в последствие не се проверяват при изпълнение на заявки INSERT (тоест вие можете да продължите да въвеждате невалидни данни). Очаква се подобна функционалност да бъде въведена скоро, но за сега не е налична. Все пак е добре да я познавате, защото в други СУБД като Oracle например функционалността CHECK работи отлично. За момента, при използване на MySQL, е в ръцете на програмиста да се проверяват тези циклични зависимости през софтуера достъпващ базата данни.

Затова при използване на MySQL засега бъдете внимателни – интегритета на данните може да бъде нарушен дори когато ограниченията са уж написани перфектно – различните СУБД имплементират функционалности по различен начин (а както виждате понякога въобще не се имплементират). Ако все пак желаете да реализирате споменатата по-горе функционалност и контрола все пак да бъде на ниво база данни с MySQL, а не при приложението – тогава забранете заявки INSERT и въведете алтернатива чрез съхранени процедури.

Задача: Начертайте опростената ER диаграма на примерната база данни

Задача: „Поправете“ цикличната зависимост в базата данни banks, която беше разгледана в предишни статии, чрез въвеждане на ограничение/я CHECK.

Задача: Създайте съхранена процедура за „записване на студент към предмет“, при която не се позволява записването към предмет от „чужд“ факултет.