C, PHP, VB, .NET

Дневникът на Филип Петров


* Контролна работа, 18.04.2015, вариант 2

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

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

er-var2

Задача 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;

 



2 коментара


  1. До една седмица на сайта на проф. Гоцева

  2. Ангелов каза:

    Здравейте,
    Кога и къде да очакваме резултати?

Добави коментар

Адресът на електронната поща няма да се публикува


*