SQYHkEcj

· 8 years ago · Jun 04, 2018, 09:20 PM
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
167CREATE FUNCTION spr_tel(@id INT)
168RETURNS BIT
169BEGIN
170	DECLARE @nr_tel VARCHAR(12)
171	SELECT @nr_tel = numer_telefonu FROM dane WHERE id_dane = @id;
172    IF (@nr_tel LIKE '+48[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
173    BEGIN
174        RETURN 1
175    END
176    ELSE IF (@nr_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
177    BEGIN
178        RETURN 1
179    END
180    RETURN 0
181END
182GO
183
184--2b) Sprawdzenie, Å¼e funkcja 1 dziaÅ‚a
185
186SELECT dbo.spr_tel(1);
187
188GO
189
190--3a) Tworzymy funkcjÄ™ 2
191--Funkcja sprawdza ile refundowanych recept zostalo wystawionych danego dnia
192
193CREATE FUNCTION ile_recept (@data DATE)
194RETURNS INT
195BEGIN
196RETURN (SELECT COUNT(*) FROM recepta WHERE data_wystawienia = @data AND refundacja = 1)
197END
198GO
199
200--3b) Sprawdzenie, Å¼e funkcja 2 dziaÅ‚a
201
202SELECT dbo.ile_recept(GETDATE()) AS ile_recept;
203
204GO
205
206
207--4a) Tworzymy procedurÄ™ 1
208--Procedura dodajaca nowe zdjecie z pantomogramu
209
210CREATE PROCEDURE dodaj_zdjecie (@zdjecie VARCHAR(45), @opis VARCHAR(255))
211AS
212INSERT INTO pantomogram(zdjecie, opis) VALUES(@zdjecie, @opis);
213GO
214
215--4b) Sprawdzenie, Å¼e procedura 1 dziaÅ‚a
216
217EXECUTE	dodaj_zdjecie "006.PNG", "Takie o zdjecie";
218SELECT * FROM pantomogram;
219GO
220--5a) Tworzymy procedurÄ™ 2
221--Procedura zmienia dane o danym id
222
223CREATE 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))
224AS
225IF EXISTS (SELECT * FROM dane WHERE id_dane = @id)
226	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
227GO
228
229
230--5b) Sprawdzenie, Å¼e procedura 2 dziaÅ‚a
231
232EXECUTE zmien_dane 3, "Adrian", "Adrianowski", "Dluga", "22", "3", "01-222", "Ulatowo", "+48569874123";
233SELECT * FROM dane;
234GO
235--6a) Tworzymy wyzwalacz 1
236--Trigger reaguje na modyfikacje danych pacjentow w widoku - dane beda zmodyfikowane takze w tabeli pacjent
237
238CREATE TRIGGER modyfik_trig ON dwie_wizyty
239INSTEAD OF INSERT
240AS
241DECLARE modyfik_kursor CURSOR
242FOR SELECT pesel, id_pacjent FROM INSERTED
243DECLARE @pesel VARCHAR(11), @id_pacjent INT
244OPEN modyfik_kursor
245FETCH NEXT FROM modyfik_kursor INTO @pesel, @id_pacjent
246WHILE @@FETCH_STATUS=0
247    BEGIN
248        INSERT INTO pacjent(pesel)
249        VALUES(@pesel)
250    FETCH NEXT FROM modyfik_kursor INTO @pesel, @id_pacjent
251END
252CLOSE modyfik_kursor
253DEALLOCATE modyfik_kursor
254
255DROP TRIGGER modyfik_trig
256
257--6b) Sprawdzenie, Å¼e wyzwalacz 1 dziaÅ‚a
258
259INSERT INTO dwie_wizyty(pesel) VALUES('45671236547')
260SELECT * FROM pacjent;
261
262--7a) Tworzymy wyzwalacz 2
263
264--7b) Sprawdzenie, Å¼e wyzwalacz 2 dziaÅ‚a
265
266--8a) Tworzymy wyzwalacz 3
267
268--8b) Sprawdzenie, Å¼e wyzwalacz 3 dziaÅ‚a
269
270--9a) Tworzymy wyzwalacz 4
271
272--9b) Sprawdzenie, Å¼e wyzwalacz 4 dziaÅ‚a
273
274--10) Tworzymy tabelÄ™ przestawnÄ…