C, PHP, VB, .NET

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

Нека разгледаме следната таблица, в която се записва информация за оценките на студенти в специалност КСТ на ТУ-София:

CREATE TABLE students_grades(
 student_name VARCHAR(255) NOT NULL,                  -- име на студент
 fac_num BIGINT(12) NOT NULL,                         -- факултетен номер
 subject_id INT NOT NULL                              -- идентификатор на предмета
 subject_name VARCHAR(30) NOT NULL,                   -- име на предмета
 godina YEAR(4) NOT NULL,                             -- учебна година
 first_session_grade DOUBLE(3,2) NULL DEFAULT NULL,   -- оценка от редовна сесия
 second_session_grade DOUBLE(3,2) NULL DEFAULT NULL,  -- оценка от поправителна сесия
 third_session_grade DOUBLE(3,2) NULL DEFAULT NULL    -- оценка от ликвидационна сесия
 professor_name VARCHAR(255) NOT NULL                 -- име на водещ преподавател
 professor_id INT NOT NULL                            -- идентификатор на преподавателя
) Engine=InnoDB;

Какви проблеми има тук? Основно три:

• Имаме аномалия при нов запис" - добавянето на нова оценка на студент води до излишъци на информация като повторение на името на студента, име на предмет, име на преподавател и т.н.;
• Имаме и "аномалия при изтриване на данни" - това се получава, когато при изтриване на данни за един обект се изтриват и данни за друг, който не е зависим от него. Например при изтриване на студент от системата, ние ще изтриваме и данни за учебни предмети и преподаватели. Представете си, че даден учебен предмет е записан от един единствен студент - ако премахнем студента, премахваме изцяло и предмета. В реална система ние не желаем това;
• Съществува и "аномалия при промяна на данни" - при промяна на една стойност се налага промяна на множество други стойности. Например представете си, че сме сбъркали името на даден студент и трябва да го поправим - заради излишеството на данни и множеството повторения на имена на студенти, ние сме принудени да направим множество обновявания на едно и също име.

Погледнато "отгоре-отгоре" и трите аномалии идват основно поради т.нар. "излишество на информация" - повторенията на едни и същи данни по редовете в колоните. Нормализацията на бази от данни до трета нормална форма премахва именно този проблем - излишеството на информация. А премахвайки него, ние разрешаваме почти напълно проблемите със споменатите аномалии. Да пристъпим по същество!

Първа нормална форма (1НФ) в по-голямата си част винаги е постигната. За да бъде една релация в 1НФ, тя трябва да отговаря на следните условия:

• Имената на колоните не трябва да се повтарят. Всяка таблица в MySQL изпълнява това условие – не може да създадете таблица с две различни колони с едно и също име;
• Всеки запис в атрибут (колона в таблицата) трябва да има една единствена стойност. Например да не може в даден ред от таблицата в полето “fac_num” да запишете два или повече факултетни номера – трябва да може да запишете най-много един. Всяка таблица в MySQL изпълнява това условие;
• Всички стойности по редове в дадена колона трябва да са от един и същи тип данни. Всяка таблица в MySQL изпълнява това условие;
• Няма значение каква е подредбата на колоните и на редовете – това също е изпълнено за всяка таблица в MySQL;
• Два различни реда не трябва да бъдат с идентични стойности. Това вече би могло да бъде нарушено;
• Една релация не трябва да съдържа съставни атрибути. Съставни са такива колони, които записват едни и същи по смисъл и по тип данни. Това също би могло да бъде нарушено.

Първото нарушение на 1НФ в горната таблица е наличието на съставен атрибут. Това (с известна уговорка, че търпи тълкуване!) са оценките - first_session_grade, second_session_grade и third_session_grade. Еднакви са по смисъл (оценки са) и по тип на данните - DOUBLE(3,2). Можем да премахнем този съставен атрибут като обединим оценките по следния начин:

CREATE TABLE students_grades(
 student_name VARCHAR(255) NOT NULL,        -- име на студент
 fac_num BIGINT(12) NOT NULL,               -- факултетен номер
 subject_id INT NOT NULL                    -- идентификатор на предмета
 subject_name VARCHAR(30) NOT NULL,         -- име на предмета
 godina YEAR(4) NOT NULL,                   -- учебна година
 grade DOUBLE(3,2) NULL DEFAULT NULL,       -- оценка
 grade_session ENUM(“1”, “2”, “3”) NOT NULL -- в коя сесия е получена оценката
 professor_name VARCHAR(255) NOT NULL       -- име на водещ преподавател
 professor_id INT NOT NULL                  -- идентификатор на преподавателя
) Engine=InnoDB;

Вторият проблем е възможността на наличие на два или повече идентични реда. Това може да се поправи като се определи „първичният ключ“ на таблицата. За да определим първичния ключ, първо трябва да въведем понятието „функционална зависимост“. Ако имаме две полета А и B, при които от стойността на A можем еднозначно да определим стойността на B, то можем да кажем, че “А определя B” или още ”B е функционално зависимо от A”. Ще бележим това с “A -> B”.

Нека първо определим функционалните зависимости:

• fac_num -> student_name: факултетния номер на даден студент еднозначно определя неговото име;
• subject_id -> subject_name: идентификатора на предмета определя неговото име;
• subject_id -> professor_id: знае се кой е титуляра на даден учебен предмет, т.е. можем да твърдим, че преподавателя е определен чрез предмета;
• professor_id -> professor_name: идентификационния номер на преподавателя еднозначно определя неговото име;
• (fac_num, subject_id) -> (godina, grade, grade_session): оценка, която е получена в дадена година през определена сесия са зависими данни от факултетния номер на студента и от учебния предмет – в противен случай ние не бихме могли да знаем тази оценка на кого е и по кой предмет е.

Сега трябва да определим първичния ключ. Кои са полетата, от които всички останали записи са функционално зависими, а самите те не са зависими от никое друго? Това са fac_num и subject_id – факултетния номер на студент и името на учебния предмет определят неговата оценка, кога е получена и кой е бил преподавателя. Значи нашият първичен ключ ще бъде:

ALTER TABLE students_grades 
ADD CONSTRAINT PRIMARY KEY (fac_num, subject_id);

Виждате, че за 1НФ просто не трябва да има колони с еднакви по смисъл стойности и трябва да има поне едно UNIQUE ограничение.

Продължаваме към втора нормална форма (2НФ). Дефиницията ѝ гласи, че „всяко неключово поле трябва да е във функционална зависимост от първичния ключ и таблицата да не съдържа изчислими полета“.

В дадената таблица няма изчислими полета. Такива биха били например ако една колона може да се представи като математически израз, конкатенация, и т.н. от други колони. Представете си, че имате таблица с покупки, в които има номер на продукт, цена, количество и обща сума. Общата сума естествено се изчислява като „цена * количество“, т.е. е ненужно да я пазим, защото можем да я изчислим.

Примерната таблица обаче все пак нарушава 2НФ поради следните зависимости:

• fac_num -> student_name;
• subject_id -> professor_id;
• subject_id -> subject_name.

Тези зависимости показват, че части от нашия първичен ключ участват в други зависимости. Подобно нещо наричаме „частични зависимости“ и то нарушава 2НФ. Едно от решенията на този проблем е да „разделим“ конфликтните зависимости в отделни релации. Това в нашия пример може да стане по следния начин:

CREATE TABLE subjects(
 subject_id INT NOT NULL               -- идентификатор на предмета
 subject_name VARCHAR(30) NOT NULL,    -- име на предмета
 professor_name VARCHAR(255) NOT NULL, -- име на водещ преподавател
 professor_id INT NOT NULL,            -- идентификатор на преподавателя
 PRIMARY KEY(subject_id)
) ENGINE=InnoDB;

CRЕATE TABLE students_grades(
 student_name VARCHAR(255) NOT NULL,         -- име на студент
 fac_num BIGINT(12) NOT NULL,                -- факултетен номер
 subject_id INT NOT NULL,                    -- идентификатор на предмета
 FOREIGN KEY(subject_id) REFERENCES subjects(subject_id),
 godina YEAR(4) NOT NULL,                    -- учебна година
 grade DOUBLE(3,2) NULL DEFAULT NULL,        -- оценка
 grade_session ENUM(“1”, “2”, “3”) NOT NULL, -- в коя сесия е получена оценката
 PRIMARY KEY(fac_num, subject_id)
) Engine=InnoDB;

След като сме направили тази промяна, трябва да проверим поотделно дали двете нови релации удовлетворяват 1НФ – да, удовлетворяват я, защото и двете имат първични ключове. После трябва да определим дали поотделно удовлетворяват 2НФ: в таблица subjects всичко e функционално зависимо от subject_id, т.е. тя е във 2НФ, но в таблица students имаме проблем, а именно:

• (fac_num, subject_id) -> (godina, grade, grade_session);
• fac_num -> student_name.

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

CREATE TABLE subjects(
 subject_id INT NOT NULL               -- идентификатор на предмета
 subject_name VARCHAR(30) NOT NULL,    -- име на предмета
 professor_name VARCHAR(255) NOT NULL, -- име на водещ преподавател
 professor_id INT NOT NULL,            -- идентификатор на преподавателя
 PRIMARY KEY(subject_id)
) ENGINE=InnoDB;

CREATE TABLE students(
 student_name VARCHAR(255) NOT NULL, -- име на студент
 fac_num BIGINT(12) NOT NULL,        -- факултетен номер
 PRIMARY KEY (fac_num)
) ENGINE=INNODB;

CRЕATE TABLE students_grades(
 fac_num BIGINT(12) NOT NULL,                -- факултетен номер
 FOREIGN KEY(fac_num) REFERENCES students(fac_num),
 subject_id INT NOT NULL,                    -- идентификатор на предмета
 FOREIGN KEY(subject_id) REFERENCES subjects(subject_id),
 godina YEAR(4) NOT NULL,                    -- учебна година
 grade DOUBLE(3,2) NULL DEFAULT NULL,        -- оценка
 grade_session ENUM(“1”, “2”, “3”) NOT NULL, -- в коя сесия е получена оценката
 PRIMARY KEY(fac_num, subject_id)
) Engine=InnoDB;

С така създадените три релации ние вече приведохме базата от данни във 2НФ. Проверете дали е така.

* Забележете – всяка таблица, която има първичен ключ от една единствена колона, е във 2НФ по дефиниция (стига да сме определили ключа коректно разбира се).

Преди да продължим нека дефинираме още един вид зависимост - „транзитивна зависимост“. При нея ако поле A определя B (A -> B), а поле B определя C (B -> C), то казваме, че “A транзитивно определя C”. Ще бележим това с A => C”. Пример от горната релация е subject_id -> professor_id (по даден учебен предмет ние можем да определим еднозначно преподавателя, който води този предмет), а от друга страна имаме professor_id -> professor_name (идентификационния номер на преподавателя определя еднозначно неговото име). Така ние имаме транзитивна зависимост subject_name => professor_name.

За да бъде една релация в трета нормална форма (3НФ) трябва да се премахнат транзитивните зависимости. Формално 3НФ гласи, че релацията трябва да е в 2НФ и всички атрибути извън първичния ключ трябва да са пряко (без транзитивност) зависими от първичния ключ. Такива в students и students_grades нямаме, но, както вече споменахме, има в релацията subject. За да разрешим проблема разделяме таблицата subjects на две релации. Така базата добива следния вид:

CRATE TABLE professors(
 professor_name VARCHAR(255) NOT NULL, -- име на водещ преподавател
 professor_id INT NOT NULL,            -- идентификатор на преподавателя
 PRIMARY KEY(professor_id)
) ENGINE=InnoDB;

CREATE TABLE subjects(
 subject_id INT NOT NULL             -- идентификатор на предмета
 subject_name VARCHAR(30) NOT NULL,  -- име на предмета
 professor_id INT NOT NULL           -- кой води предмета
 FOREIGN KEY(professor_id) REFERENCES professors(professor_id),
 PRIMARY KEY(subject_id)
) ENGINE=InnoDB;

CREATE TABLE students(
 student_name VARCHAR(255) NOT NULL, -- име на студент
 fac_num BIGINT(12) NOT NULL,        -- факултетен номер
 PRIMARY KEY (fac_num)
) ENGINE=INNODB;

CRЕATE TABLE students_grades(
 fac_num BIGINT(12) NOT NULL,                -- факултетен номер
 FOREIGN KEY(fac_num) REFERENCES students(fac_num),
 subject_id INT NOT NULL,                    -- идентификатор на предмета
 FOREIGN KEY(subject_id) REFERENCES subjects(subject_id),
 godina YEAR(4) NOT NULL,                    -- учебна година
 grade DOUBLE(3,2) NULL DEFAULT NULL,        -- оценка
 grade_session ENUM(“1”, “2”, “3”) NOT NULL, -- в коя сесия е получена оценката
 PRIMARY KEY(fac_num, subject_id)
) Engine=InnoDB;

С това приведохме базата от данни към 3НФ. Проверката включва тест дали всяка от релациите е в 1НФ, 2НФ и 3НФ - проверете. При достигната 3НФ обикновено имате достатъчна ефективна за работа база от данни - чрез нея се премахват "грубите грешки".