· 5 years ago · Jun 10, 2020, 08:24 PM
1
2-- Зад. 1
3CREATE DATABASE APPOINTMENT
4GO
5
6CREATE TABLE DOCTOR (
7 ID INT IDENTITY PRIMARY KEY,
8 NAME VARCHAR(64) NOT NULL,
9 SPECIALTY VARCHAR(16),
10 GRADUATION DATE NOT NULL,
11 LICENSE# CHAR(16) NOT NULL,
12 CONSTRAINT CH_DOCTOR_SPECIALTY CHECK (SPECIALTY IN ('GP','Surgery','Dermathology','Neurology','Rheumatology')),
13 CONSTRAINT CH_DOCTOR_LICENSE# CHECK ((SPECIALTY = 'GP' AND LICENSE# LIKE 'GP-........')
14 OR (LICENSE# LIKE 'SP-..-........'))
15);
16
17CREATE TABLE PATIENT (
18 ID INT IDENTITY PRIMARY KEY,
19 NAME VARCHAR(64) NOT NULL,
20 UCN CHAR(10) NOT NULL,
21 ADDRESS VARCHAR(256),
22 MAIL VARCHAR(128) NOT NULL,
23 CONSTRAINT UK_PATIENT_UCN UNIQUE(UCN)
24);
25
26CREATE TABLE APPOINTMENT (
27 ID INT IDENTITY PRIMARY KEY,
28 DOCTOR_ID INT,
29 PATIENT_ID INT,
30 DATE DATE NOT NULL,
31 REFERENCE_APP INT,
32 CONSTRAINT UK_DOCTOR_PATIENT UNIQUE(DOCTOR_ID, PATIENT_ID),
33 CONSTRAINT FK_APPOINTMENT_DOCTOR FOREIGN KEY (DOCTOR_ID) REFERENCES DOCTOR(ID),
34 CONSTRAINT FK_APPOINTMENT_PATIENT FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT(ID),
35 CONSTRAINT FK_REFFERENCE_APP FOREIGN KEY (REFERENCE_APP) REFERENCES APPOINTMENT(ID)
36);
37GO
38
39-- Зад. 2
40CREATE VIEW ExamView(DoctorID, DoctorName, PatientUCN, PatientName, AppointmentDate)
41AS
42 SELECT doc.ID,doc.NAME,pat.UCN,pat.NAME,app.DATE
43 FROM APPOINTMENT app
44 JOIN DOCTOR doc ON app.DOCTOR_ID = doc.ID
45 JOIN PATIENT pat ON app.PATIENT_ID = pat.ID
46 WHERE doc.SPECIALTY = 'GP'
47GO
48
49-- Зад. 3
50-- Да се направи така, че в изгледа ExamView да може да се добавят нови прегледи при GP-та, като са изпълнени следните условия
51-- Колоните за INSERT заявката са: ID на лекар, ЕГН на пациент, име на пациент, дата на преглед
52-- INSERT заявката трябва да може да добавя само един такъв преглед
53-- INSERT заявки, която добавят няколко записа наведнъж, не е необходимо да се поддържат.
54
55-- Ако клиентът праща с INSERT заявката към изгледа повече от един ред - да се върне грешка
56-- Лекарят, за когото се добавя преглед, трябва да го има съществуващ в базата и специалността му да е GP. Ако го няма или е с друга специалност - да се върне грешка.
57-- Пациентът може да го няма в базата и в този случай трябва да се добави. Ако го има - добавя се само преглед.
58-- В последния случай, не е необходимо да се проверява дали новоподаденото име съвпада с това в базата.
59
60CREATE TRIGGER ExamViewTrigger ON ExamView
61INSTEAD OF INSERT
62AS
63 IF (SELECT COUNT(*) FROM inserted) > 1
64 BEGIN
65 RAISERROR('Error - user insertions spanse over multiple lines', 11, 1);
66 ROLLBACK
67 END
68
69 IF NOT EXISTS ( SELECT *
70 FROM inserted i
71 JOIN DOCTOR doc ON i.DoctorID = doc.ID
72 WHERE doc.SPECIALTY = 'GP' )
73 BEGIN
74 RAISERROR('Error - doctor is non-existant OR not a GP', 12, 1);
75 ROLLBACK;
76 END
77
78 IF NOT EXISTS ( SELECT i.PatientUCN
79 FROM inserted i
80 JOIN PATIENT pat ON i.PatientUCN = pat.UCN)
81 BEGIN
82 ALTER TABLE PATIENT ALTER COLUMN MAIL VARCHAR(128) NULL
83
84 INSERT INTO PATIENT(UCN, NAME)
85 SELECT PatientUCN, PatientName
86 FROM inserted
87
88 ALTER TABLE PATIENT ALTER COLUMN MAIL VARCHAR(128) NOT NULL
89 END
90
91 IF NOT EXISTS( SELECT i.DoctorID, i.PatientUCN, i.PatientName, i.AppointmentDate
92 FROM inserted i
93 WHERE i.DoctorID IS NOT NULL AND i.PatientUCN IS NOT NULL AND i.PatientName IS NOT NULL AND i.AppointmentDate IS NOT NULL)
94 BEGIN
95 RAISERROR('Invalid insertion is attempted', 14, 1);
96 ROLLBACK;
97 END
98GO
99
100-- Зад. 4
101-- Да се направи така, че при добавяне на нов ред (единствен ред с INSERT заявка в таблицата за прегледи;
102-- INSERT заявки с повече от един ред не е нужно да се поддържат), да се проверява дали новия преглед е при специалист
103-- (лекар със специалност различна от GP). Ако да - да се провери, че рефериращия преглед в новия ред е от GP и
104-- само в този случай INSERT заявката да успее. (Прегледи при GP да не могат да се добаявт директно в таблицата за прегледи)
105
106CREATE TRIGGER SPECIALIST_APPOINTMENT_TRIGGER ON APPOINTMENT
107AFTER INSERT
108AS
109 IF EXISTS ( SELECT *
110 FROM inserted i
111 JOIN DOCTOR doc ON i.DOCTOR_ID = doc.ID
112 WHERE doc.SPECIALTY NOT LIKE 'GP')
113 BEGIN
114 IF NOT EXISTS ( SELECT *
115 FROM inserted i
116 JOIN APPOINTMENT app ON i.REFERENCE_APP = app.ID
117 JOIN DOCTOR doc ON app.DOCTOR_ID = doc.ID
118 WHERE doc.SPECIALTY = 'GP' )
119 BEGIN
120 RAISERROR('Invalid REFERENCE_APPOINTMENT ID', 13, 1);
121 ROLLBACK;
122 END
123 END
124GO