C, PHP, VB, .NET

На представената ER диаграма е показан фрагмент от база от данни за лечебните процедури, които се прилагат върху пациентите в клиника. За всеки пациент (Patient) се пази EГН и име. За леченията (Threatment) пазим уникален идентификационен номер и цена. За докторите (Doctor) пазим уникален идентификационен номер и име. Всяка лечебна процедура (Procedure) е извършена в точно определено време и в точно определен номер на стая.

Задача 1. Създайте базата от данни на езика SQL, като внимателно подбирате подходящи типове за данните. Бъдете внимателни при определянето на първичния ключ за таблица Procedure (той не е отбелязан на ER диаграмата, а вие сами трябва да го подберете). Обяснете със свободен текст защо точно по този начин сте определили първичния ключ на тази таблица.

Решение: Таблиците patient, treatment и doctor са напълно тривиални:

CREATE TABLE patient(
  egn BIGINT(10) UNSIGNED PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE treatment(
  id SMALLINT UNSIGNED PRIMARY KEY,
  price DECIMAL(6,2) NOT NULL
);

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

Таблица procedures е с пет колони, три от които са външни ключове. На този етап ще създадем таблицата без PK:

CREATE TABLE procedures(
  room_no TINYINT UNSIGNED NOT NULL,
  time DATETIME NOT NULL,
  patient_egn BIGINT(10) UNSIGNED  NOT NULL,
  FOREIGN KEY(patient_egn) REFERENCES patient(egn),
  treatment_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(treatment_id) REFERENCES treatment(id),
  doctor_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(doctor_id) REFERENCES doctor(id)
);

Определянето на първичния ключ тук не е очевидно. Първо трябва да помислим върху всички ограничения върху таблицата, които ще дойдат от реалния живот. Очевидно не може един пациент да се намира в две различни стаи по едно и също време. Аналогично не е възможно един доктор да лекува в две различни стаи по едно и също време. Следват обаче още въпроси, които не са изяснени в условието. Ще позволим ли на двама или повече пациенти да бъдат лекувани в една и съща стая по едно и също време? Ако го позволим, може ли даден доктор, който се намира в стая с много пациенти, да лекува двама или повече едновременно? Ще позволим ли на двама или повече доктори да лекуват в една и съща стая по едно и също време? В зависимост от отговорите на тези въпроси може да се поставят едни или други UNIQUE ограничения в таблицата. Най-лесното решение би било да забраним всички тези усложнения на задачата като поставим съответните ограничения (и от гледна точка на контролната работа това би било прието за вярно, стига да е разсъждавано коректно), но едва ли би било коректно от гледна точка на реалния свят. Затова тук ще приемем, че има много легла в една стая, както и че е възможно един лекар да лекува по много пациенти накуп с една обща процедура (каквито например са често груповите терапии на психолозите и т.н.).

Преди да определим ключовете обаче ще възникне още един нов въпрос - възможно ли е двама или повече доктори да третират един и същи пациент едновременно? И ако позволим  го третират едновременно, това не би трябвало да се отрази негативно върху заплащането на пациента - ще бъде лошо ако той трябва да заплати двойно за една процедура, само защото е била извършена от двама души от лекарския персонал. А ще разрешим ли различни видове процедури да бъдат извършвани в една и съща стая по едно и също време? Ако да, може ли един доктор може ли да извършва различни лечения върху различни пациенти едновременно? Виждате, че когато в една база от данни се намешат сложни взаимовръзки и едновременно с това таблицата не е нормализирана, нещата стават далеч от тривиални. Затова тук ще приемем, че само един доктор може да извършва процедура върху пациент в дадено време (все едно ще пазим само главния лекуващ лекар, а ако трябва да пазим и други лекари, участващи в дадена процедура, може да ги записваме в отделна таблица или просто задачата ще се реши по различен начин).

Обобщено приемаме следните ограничения:

• В една стая могат да бъдат лекувани много пациенти едновременно;
• Един доктор може да лекува много пациенти едновременно в една стая;
• За пациентите ще пазим само главния лекуващ лекар, който извършва процедурата, независимо дали има други лекари, които му помагат;
• Един пациент може да приеме много процедури (лечения) по едно и също време. Това е обвързано и с позволение на един доктор да извършва много процедури по едно и също време.

Е достигаме и до определянето на първичния ключ - с тази ненормализирана таблица няма начин да решим задачата така, че да не позволим аномалии при вмъкване, изтриване и промяна на информация, но все пак можем да направим следното приближение, което да отговаря на посочените четири точки, с два кандидат-ключа:

ALTER TABLE procedures 
ADD CONSTRAINT `time_patient_treatment_unique`
UNIQUE(time, patient_egn, treatment_id),
ADD CONSTRAINT `time_patient_doctor_unique`
UNIQUE(time, patient_egn, doctor_id);

Ограниченията един пациент или един доктор да не може да бъде в две различни стаи по едно и също време не се покрива от тези ключове. Тези ограничения обаче не могат да бъдат приложени, защото биха "прецакали" други. Затова те трябва да се направят или с CHECK, или от приложението, което ще работи с базата от данни. Същото важи за други ограничения, които не са покрити от така дефинираните.

Който и от двата ключа да приемем за първичен, ще свърши работа. В случая ще подберем този с пациенти и лечения:

ALTER TABLE procedures 
DROP INDEX `time_patient_treatment_unique`,
ADD CONSTRAINT PRIMARY KEY (time, patient_egn, treatment_id);

Бележка: В задачата на контролната работа беше допустимо дори просто да се добави нова колона "id" в таблица procedures и тя да се използва за първичен ключ, но не и без да се направят разсъждения относно коректността на данните в таблицата.

Задача 2. Изведете списък с имената на пациентите, id на лекарите, номерата на стаите и времето на провеждането на процедурите за тези пациенти, които са лекувани от доктори с име „Иван Иванов“ и върху които е било прилагано лечение №10. Обръщаме внимание, че може да има повече от един доктор с име „Иван Иванов“ в системата – именно затова в заявката трябва да се изведе тяхното id.

Решение: Решението на тази задача е в общи линии тривиално:

SELECT patient.name, doctor.id, 
       procedures.room_no, procedures.time
FROM procedures
JOIN patient ON procedures.patient_egn = patient.egn
JOIN doctor ON procedures.doctor_id = doctor.id
WHERE procedures.treatment_id = 10
        AND 
      doctor.name="Иван Иванов";

Задача 3. Изведете имената на пациентите и общите суми за лечения, които тези пациенти са заплатили, но само за процедури, които са били приложени от лекар с id 10 в стая номер 15.

Решение: Тук отново заявката e без нещо нестандартно:

SELECT patient.name, SUM(treatment.price)
FROM procedures
JOIN patient ON patient.egn = procedures.patient_egn
JOIN treatment ON treatment.id = procedures.treatment_id
WHERE procedures.doctor_id = 10 AND procedures.room_no = 15
GROUP BY patient.egn;