· 8 years ago · Jun 05, 2018, 08:06 AM
1--ImiÄ™ i nazwisko: Krystian Åukasiak
2--Numer indeksu: 258205
3--Temat bazy danych: Baza danych dla gabinetu stomatologicznego
4
5-- 0) Poprawione rozwiÄ…zanie zadania 1b (skrypt generujÄ…cy strukturÄ™ bazy danych)
6SET DATEFORMAT dmy;
7GO
8
9CREATE TABLE dane(
10 id_dane INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
11 imie varchar(45) NOT NULL,
12 nazwisko varchar(45) NOT NULL,
13 ulica varchar(90),
14 nr_domu varchar(4) NOT NULL,
15 nr_mieszkania varchar(4),
16 kod_pocztowy varchar(6) NOT NULL,
17 miejscowosc varchar(45) NOT NULL,
18 numer_telefonu varchar(12) CHECK (LEN(numer_telefonu) >= 9)
19);
20
21CREATE TABLE zabieg(
22 id_zabieg INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
23 rodzaj_zabiegu varchar(45),
24 opis varchar(255)
25);
26
27CREATE TABLE pantomogram(
28 id_pantomogram INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
29 zdjecie varchar(45) NOT NULL,
30 opis varchar(255) NOT NULL
31);
32
33CREATE TABLE lek(
34 id_lek INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
35 nazwa varchar(255) CHECK(LEN(nazwa) > 3)
36);
37
38CREATE TABLE recepta(
39 id_recepta INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
40 opis varchar(255),
41 data_wystawienia DATE NOT NULL CHECK(data_wystawienia <= GETDATE()),
42 refundacja BIT NOT NULL
43);
44
45CREATE TABLE recepta_has_lek(
46 id_recepta INTEGER FOREIGN KEY REFERENCES recepta(id_recepta),
47 id_lek INTEGER FOREIGN KEY REFERENCES lek(id_lek)
48);
49
50CREATE TABLE dentysta(
51 id_dentysta INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
52 id_dane INTEGER FOREIGN KEY REFERENCES dane(id_dane)
53);
54
55CREATE TABLE pacjent(
56 id_pacjent INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
57 id_dane INTEGER FOREIGN KEY REFERENCES dane(id_dane),
58 pesel varchar(11) NOT NULL CHECK(LEN(pesel) = 11)
59);
60
61CREATE TABLE wizyta(
62 id_wizyta INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
63 id_recepta INTEGER FOREIGN KEY REFERENCES recepta(id_recepta),
64 id_pacjent INTEGER FOREIGN KEY REFERENCES pacjent(id_pacjent),
65 id_dentysta INTEGER FOREIGN KEY REFERENCES dentysta(id_dentysta),
66 data_wizyty DATE NOT NULL,
67 data_nastepnego_przegladu DATE NOT NULL,
68 cena FLOAT
69);
70
71CREATE TABLE historia_leczenia(
72 id_historia_leczenia INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
73 id_zabieg INTEGER FOREIGN KEY REFERENCES zabieg(id_zabieg),
74 id_wizyta INTEGER FOREIGN KEY REFERENCES wizyta(id_wizyta),
75 id_pantomogram INTEGER FOREIGN KEY REFERENCES pantomogram(id_pantomogram)
76);
77
78INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Jan', 'Kowalski', 'Wojskowa', '14', '2', '01-232', 'Gdynia', '+48595484787');
79INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Tomasz', 'Nowak', 'Daleka', '11', '6', '22-444', 'Sopot', '+48646787787');
80INSERT INTO dane (imie, nazwisko, ulica, nr_domu, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Tadeusz', 'Nowy', 'Bliska', '5', '02-454', 'Olsztyn', '+48949787314');
81INSERT INTO dane (imie, nazwisko, nr_domu, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Albert', 'Mederski', '22', '91-874', 'Montowo','+48423647831');
82INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Zenon', 'Wojtyla', 'Cyganska', '76', '32', '99-456', 'Przasnysz', '+48434131277');
83INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Kazimierz', 'Normalny', 'Wawelska', '69', '96', '45-554', 'Ilawa', '+48194236478');
84INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Amadeusz', 'Lokietko', 'Menelska', '54', '3', '99-456', 'Przasnysz', '+48123331337');
85INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Aleksander', 'Kwasniewski', 'Menelska', '3', '2', '14-552', 'Krakow', '+48131454123');
86INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Lech', 'Walesa', 'Stoczni', '5', '123', '54-221', 'Gdansk', '+48412974123');
87INSERT INTO dane (imie, nazwisko, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc, numer_telefonu) VALUES ('Boleslaw', 'Stanislawowski', 'Takasobie', '11', '12', '45-131', 'Wejherowo', '+48499741237');
88
89INSERT INTO zabieg (rodzaj_zabiegu, opis) VALUES ('wyrywanie zeba', 'standardowe wyrywanie zeba');
90INSERT INTO zabieg (rodzaj_zabiegu, opis) VALUES ('wyrywanie zeba madrosci', 'standardowe wyrywanie zeba madrosci');
91INSERT INTO zabieg (rodzaj_zabiegu) VALUES ('lakowanie');
92INSERT INTO zabieg (rodzaj_zabiegu, opis) VALUES ('leczenie', 'leczenie zeba');
93INSERT INTO zabieg (rodzaj_zabiegu) VALUES ('leczenie kanalowe');
94
95INSERT INTO pantomogram (zdjecie, opis) VALUES ('001.PNG', 'BRAK ZEBA DRUGIEGO');
96INSERT INTO pantomogram (zdjecie, opis) VALUES ('002.PNG', 'BRAK ZEBA TRZECIEGO');
97INSERT INTO pantomogram (zdjecie, opis) VALUES ('003.PNG', 'BRAK KOSCI ZUCHWY');
98INSERT INTO pantomogram (zdjecie, opis) VALUES ('004.PNG', 'BRAK NIEPRAWIDLOWOSCI');
99INSERT INTO pantomogram (zdjecie, opis) VALUES ('005.PNG', 'BRAK ZEBA OSMEGO');
100
101INSERT INTO lek (nazwa) VALUES ('Lekarstwo');
102INSERT INTO lek (nazwa) VALUES ('Placebo');
103INSERT INTO lek (nazwa) VALUES ('Valium');
104INSERT INTO lek (nazwa) VALUES ('Pyralgina');
105INSERT INTO lek (nazwa) VALUES ('Ibuprom');
106
107INSERT INTO recepta (opis, data_wystawienia, refundacja) VALUES ('normalna recepta', GETDATE(), 1);
108INSERT INTO recepta (opis, data_wystawienia, refundacja) VALUES ('dziwna recepta', GETDATE(), 0);
109INSERT INTO recepta (opis, data_wystawienia, refundacja) VALUES ('standardowa recepta', GETDATE(), 1);
110INSERT INTO recepta (opis, data_wystawienia, refundacja) VALUES ('normalna recepta', GETDATE(), 0);
111INSERT INTO recepta (opis, data_wystawienia, refundacja) VALUES ('zwykla recepta', GETDATE(), 1);
112
113INSERT INTO recepta_has_lek (id_recepta, id_lek) VALUES (1, 1);
114INSERT INTO recepta_has_lek (id_recepta, id_lek) VALUES (2, 3);
115INSERT INTO recepta_has_lek (id_recepta, id_lek) VALUES (3, 5);
116INSERT INTO recepta_has_lek (id_recepta, id_lek) VALUES (4, 2);
117INSERT INTO recepta_has_lek (id_recepta, id_lek) VALUES (3, 1);
118
119INSERT INTO dentysta (id_dane) VALUES (1);
120INSERT INTO dentysta (id_dane) VALUES (2);
121INSERT INTO dentysta (id_dane) VALUES (3);
122INSERT INTO dentysta (id_dane) VALUES (4);
123INSERT INTO dentysta (id_dane) VALUES (5);
124
125INSERT INTO pacjent (id_dane, pesel) VALUES (6, 28193456142);
126INSERT INTO pacjent (id_dane, pesel) VALUES (7, 12312336142);
127INSERT INTO pacjent (id_dane, pesel) VALUES (8, 57547452142);
128INSERT INTO pacjent (id_dane, pesel) VALUES (9, 34583945823);
129INSERT INTO pacjent (id_dane, pesel) VALUES (10, 27879623472);
130
131INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu) VALUES (1, 1, 1, '22/02/1987', '23/03/1988');
132INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu) VALUES (1, 1, 1, '22/02/1989', '23/03/1990');
133INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu) VALUES (2, 2, 2, '14/05/1997', '11/11/1998');
134INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu) VALUES (3, 3, 3, '23/09/1998', '16/08/2008');
135INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu) VALUES (4, 4, 4, '19/04/1999', '25/09/2006');
136INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu) VALUES (5, 5, 5, '17/08/2005', '19/08/2006');
137
138INSERT INTO historia_leczenia (id_zabieg, id_wizyta, id_pantomogram) VALUES (1, 1, 1);
139INSERT INTO historia_leczenia (id_zabieg, id_wizyta, id_pantomogram) VALUES (2, 2, 2);
140INSERT INTO historia_leczenia (id_zabieg, id_wizyta, id_pantomogram) VALUES (3, 3, 3);
141INSERT INTO historia_leczenia (id_zabieg, id_wizyta, id_pantomogram) VALUES (4, 4, 4);
142INSERT INTO historia_leczenia (id_zabieg, id_wizyta, id_pantomogram) VALUES (5, 5, 5);
143
144GO
145
146--1a) Tworzymy widok
147--Widok bedzie pokazywal wszystkich pacjenow ktorzy mieli conajmniej 2 wizyty
148
149CREATE VIEW dwie_wizyty
150AS
151SELECT p.id_pacjent, p.pesel, COUNT(w.id_pacjent) AS "ilosc_wizyt"
152FROM pacjent p INNER JOIN wizyta w
153ON p.id_pacjent = w.id_pacjent
154GROUP BY p.id_pacjent, p.pesel
155HAVING COUNT (p.id_pacjent) >= 2;
156GO
157
158--1b) Sprawdzenie, że widok działa
159
160SELECT * FROM dwie_wizyty;
161
162
163GO
164
165--2a) Tworzymy funkcjÄ™ 1
166--Funkcja sprawdza poprawnosc numeru telefonu dla danych z danym numerem id
167
168CREATE FUNCTION spr_tel(@id INT)
169RETURNS INT
170BEGIN
171 DECLARE @nr_tel VARCHAR(15)
172 SELECT @nr_tel = numer_telefonu FROM dane WHERE id_dane = @id;
173 IF (@nr_tel LIKE '+48[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
174 BEGIN
175 RETURN 1
176 END
177 ELSE IF (@nr_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
178 BEGIN
179 RETURN 1
180 END
181 RETURN 0
182END
183GO
184
185--DROP FUNCTION spr_tel
186--2b) Sprawdzenie, że funkcja 1 działa
187
188SELECT dbo.spr_tel(1);
189
190GO
191
192--3a) Tworzymy funkcjÄ™ 2
193--Funkcja sprawdza czy recepta o danym id jest refundowana
194
195CREATE FUNCTION czy_refundowana (@id INT)
196RETURNS VARCHAR(5)
197BEGIN
198DECLARE @refundacja BIT
199 SELECT @refundacja = refundacja FROM recepta WHERE id_recepta = @id;
200RETURN CASE
201 WHEN @refundacja = 1 THEN 'true'
202 WHEN @refundacja = 0 THEN 'false'
203 END
204END
205GO
206
207--3b) Sprawdzenie, że funkcja 2 działa
208
209SELECT dbo.czy_refundowana(2)
210GO
211
212
213--4a) Tworzymy procedurÄ™ 1
214--Procedura dodajaca nowe zdjecie z pantomogramu
215
216CREATE PROCEDURE dodaj_zdjecie (@zdjecie VARCHAR(45), @opis VARCHAR(255))
217AS
218INSERT INTO pantomogram(zdjecie, opis) VALUES(@zdjecie, @opis);
219GO
220
221--4b) Sprawdzenie, że procedura 1 działa
222
223EXECUTE dodaj_zdjecie "006.PNG", "Takie o zdjecie";
224SELECT * FROM pantomogram;
225GO
226
227--5a) Tworzymy procedurÄ™ 2
228--Procedura zmienia dane o danym id
229
230CREATE PROCEDURE zmien_dane (@id INT, @imie VARCHAR(45), @nazwisko VARCHAR(45), @ulica VARCHAR(90), @nr_domu VARCHAR(4), @nr_mieszkania VARCHAR(4), @kod_pocztowy VARCHAR(6), @miejscowosc VARCHAR(45), @numer_telefonu VARCHAR(12))
231AS
232IF EXISTS (SELECT * FROM dane WHERE id_dane = @id)
233 UPDATE dane SET imie = @imie, nazwisko = @nazwisko, ulica = @ulica, nr_domu = @nr_domu, nr_mieszkania = @nr_mieszkania, kod_pocztowy = @kod_pocztowy, miejscowosc = @miejscowosc, numer_telefonu = @numer_telefonu WHERE id_dane = @id
234GO
235
236
237--5b) Sprawdzenie, że procedura 2 działa
238
239EXECUTE zmien_dane 3, "Adrian", "Adrianowski", "Dluga", "22", "3", "01-222", "Ulatowo", "+48569874123";
240SELECT * FROM dane;
241GO
242
243--6a) Tworzymy wyzwalacz 1
244--Trigger reaguje na modyfikacje danych pacjentow w widoku - dane beda zmodyfikowane takze w tabeli pacjent
245
246CREATE TRIGGER modyfik_trig ON dwie_wizyty
247INSTEAD OF INSERT
248AS
249DECLARE modyfik_kursor CURSOR
250FOR SELECT pesel, id_pacjent FROM INSERTED
251DECLARE @pesel VARCHAR(11), @id_pacjent INT
252OPEN modyfik_kursor
253FETCH NEXT FROM modyfik_kursor INTO @pesel, @id_pacjent
254WHILE @@FETCH_STATUS=0
255 BEGIN
256 INSERT INTO pacjent(pesel)
257 VALUES(@pesel)
258 FETCH NEXT FROM modyfik_kursor INTO @pesel, @id_pacjent
259END
260CLOSE modyfik_kursor
261DEALLOCATE modyfik_kursor
262
263DROP TRIGGER modyfik_trig
264
265--6b) Sprawdzenie, że wyzwalacz 1 działa
266
267INSERT INTO dwie_wizyty(pesel) VALUES('45671236547')
268SELECT * FROM pacjent;
269GO
270
271--7a) Tworzymy wyzwalacz 2
272
273CREATE TRIGGER zmien_nr ON dane
274AFTER INSERT
275AS
276BEGIN
277 DECLARE @id_dane INT
278 SELECT @id_dane=id_dane FROM INSERTED
279 DECLARE @nr_tel VARCHAR(15)
280 SELECT @nr_tel=numer_telefonu FROM INSERTED
281 IF (dbo.spr_tel(@nr_tel)=0)
282 BEGIN
283 UPDATE dane
284 SET numer_telefonu = null
285 WHERE id_dane = @id_dane
286 END
287END
288
289 --DROP TRIGGER zmien_nr
290--7b) Sprawdzenie, że wyzwalacz 2 działa
291
292SELECT * FROM dane;
293UPDATE dane SET numer_telefonu='666629685' WHERE id_dane=1
294SELECT * FROM dane;
295GO
296
297--8a) Tworzymy wyzwalacz 3
298--Trigger zmienia dodawana miescowosc Warszawa na stolica
299
300CREATE TRIGGER zmien_stolica ON dane
301AFTER INSERT, UPDATE
302AS
303BEGIN
304 DECLARE @miejscowosc VARCHAR(45)
305 DECLARE @id INT
306 SELECT @id = id_dane FROM INSERTED
307 SELECT @miejscowosc = miejscowosc FROM INSERTED
308 BEGIN
309 UPDATE dane
310 SET miejscowosc = 'Stolica'
311 WHERE id_dane = @id AND @miejscowosc = 'Warszawa'
312 END
313END
314
315--DROP TRIGGER zmien_stolica
316--8b) Sprawdzenie, że wyzwalacz 3 działa
317SELECT * FROM dane;
318UPDATE dane SET miejscowosc = 'Warszawa' WHERE id_dane = 2;
319SELECT * FROM dane;
320GO
321
322--9a) Tworzymy wyzwalacz 4
323
324CREATE TRIGGER zmniejsz_cene ON wizyta
325AFTER INSERT
326AS
327BEGIN
328 DECLARE @cena FLOAT
329 DECLARE @id INT
330 SELECT @id = id_wizyta FROM INSERTED
331 SELECT @cena = cena FROM INSERTED
332 BEGIN
333 UPDATE wizyta
334 SET cena = @cena * 0.95
335 WHERE id_wizyta = @id
336 END
337END
338
339--DROP TRIGGER zmniejsz_cene
340--9b) Sprawdzenie, że wyzwalacz 4 działa
341
342INSERT INTO wizyta (id_recepta, id_pacjent, id_dentysta, data_wizyty, data_nastepnego_przegladu, cena) VALUES (2, 2, 2, '14/05/1997', '11/11/1998', 200);
343SELECT * FROM wizyta;
344
345--10) Tworzymy tabelÄ™ przestawnÄ…
346
347UPDATE wizyta SET cena = 150;
348SELECT * FROM wizyta
349PIVOT(
350 SUM(cena)
351 FOR data_wizyty IN ([22/02/1987], [14/05/1997], [23/09/1998], [19/04/1999], [17/08/2005])
352)
353AS r;