-- ----------------------- -- Tworzenie bazy danych -- ----------------------- USE master GO IF DB_ID('BazaPrzychodni') IS NULL CREATE DATABASE BazaPrzychodni GO USE BazaPrzychodni -- ---------------- -- Usuwanie tabel -- ---------------- IF OBJECT_ID('Wizyta','U') IS NOT NULL DROP TABLE Wizyta IF OBJECT_ID('Pacjent','U') IS NOT NULL DROP TABLE Pacjent IF OBJECT_ID('Wlasciciel','U') IS NOT NULL DROP TABLE Wlasciciel IF OBJECT_ID('Lekarz','U') IS NOT NULL DROP TABLE Lekarz IF OBJECT_ID('Diagnoza','U') IS NOT NULL DROP TABLE Diagnoza GO -- ------------------ -- Tworzenie tabel -- ------------------ IF OBJECT_ID('Diagnoza','U') IS NULL CREATE TABLE Diagnoza ( ID INT NOT NULL PRIMARY KEY, Choroba VARCHAR(100) NOT NULL ) IF OBJECT_ID('Lekarz','U') IS NULL CREATE TABLE Lekarz ( ID INT NOT NULL PRIMARY KEY, Imie_Nazwisko VARCHAR(100) NOT NULL, Specjalizacja VARCHAR(100) NOT NULL, PESEL BIGINT NOT NULL, Adres VARCHAR(70) NOT NULL, Miasto_Kod VARCHAR(50) NOT NULL, Telefon VARCHAR(10) NOT NULL ) IF OBJECT_ID('Wlasciciel','U') IS NULL CREATE TABLE Wlasciciel ( ID INT NOT NULL PRIMARY KEY, Imie_Nazwisko VARCHAR(100) NOT NULL, PESEL BIGINT NOT NULL, Adres VARCHAR(70) NOT NULL, Miasto_Kod VARCHAR(50) NOT NULL, Telefon VARCHAR(10) NOT NULL ) IF OBJECT_ID('Pacjent','U') IS NULL CREATE TABLE Pacjent ( ID INT NOT NULL PRIMARY KEY, Imie VARCHAR(20) NOT NULL, ID_Wlasciciel INT FOREIGN KEY REFERENCES dbo.Wlasciciel(ID), Plec CHAR(6) CHECK (Plec in ('samiec', 'samica')), Gatunek VARCHAR(20) NOT NULL, Rasa VARCHAR(50) NOT NULL, Wiek INT NOT NULL, Masc VARCHAR(20) NOT NULL ) IF OBJECT_ID('Wizyta','U') IS NULL CREATE TABLE Wizyta ( ID INT IDENTITY(1,1) PRIMARY KEY, [Data] DATETIME NOT NULL, ID_Pacjent INT NOT NULL FOREIGN KEY REFERENCES dbo.Pacjent(ID), ID_Wlasciciel INT FOREIGN KEY REFERENCES dbo.Wlasciciel(ID), ID_Diagnoza INT NOT NULL FOREIGN KEY REFERENCES dbo.Diagnoza(ID), ID_Lekarz INT NOT NULL FOREIGN KEY REFERENCES dbo.Lekarz(ID), Objawy VARCHAR(100) NOT NULL, Leczenie VARCHAR(100) NOT NULL, Szczepienie BIT NOT NULL ) GO -- --------------------------------- -- Wstawianie wartości do tabel -- --------------------------------- INSERT INTO Diagnoza (ID, Choroba) VALUES (1, 'pasożyty zewnętrzne - świerzb'), (2, 'pasożyty zewnętrzne - wszoły'), (3, 'pasożyty zewnętrzne - pchły'), (4, 'pasożyty zewnętrzne - roztocza'), (5, 'cukrzyca'), (6, 'nadczynność tarczycy'), (7, 'niedoczynność tarczycy'), (8, 'zatrucie toksynami bakteryjnymi'), (9, 'atopowe zapalenie skóry'), (10,'zapalenie skóry'), (11,'zapalenie spojówki oka'), (12,'złamanie kończyny'), (13,'przemieszczenie stawu'), (14,'uszkodzenie tkanek skóry'), (15,'przeziębienie'), (16,'zapalenie pęcherza') GO INSERT INTO Lekarz (ID, Imie_Nazwisko, Specjalizacja, PESEL, Adres, Miasto_Kod, Telefon) VALUES (1, 'Mariusz Szczepaniak', 'specjalista chirurgii weterynaryjnej', 64083146972, 'ul. Klonowa 1', '61-468 Poznań', '797964635'), (2, 'Jacek Grochowski', 'specjalista radiologii weterynaryjnej',78041554792, 'ul. Lipowa 2/22', '61-478 Poznań', '518901018'), (3, 'Natalia Jarzębowska', 'specjalista chirurgii weterynaryjnej', 70010654487, 'ul. Akacjowa 3', '61-431 Poznań', '764830180'), (4, 'Marta Kołodziej', 'specjalista chorób psów i kotów', 83092674526, 'ul. Dębowa 4a', '61-458 Poznań', '675569040') GO INSERT INTO Wlasciciel (ID, Imie_Nazwisko, PESEL, Adres, Miasto_Kod, Telefon) VALUES (1, 'Łukasz Biegański', 71120597318, 'Błękitna 42/3', '60-656 Poznań', '797964634'), (2, 'Adam Kołodziej', 91100521676, 'Gronowa 4', '61-306 Poznań', '615891115'), (3, 'Adrian Maciejczyk', 54021035497, 'Os. Jana III Sobieskiego 5/9','60-688 Poznań', '722661326'), (4, 'Damian Grygiel', 93101795319, 'Pszenna 7', '61-663 Poznań', '597515716'), (5, 'Grzegorz Stankowski', 00272589733, 'Szewska 27', '60-111 Poznań', '527804971'), (6, 'Stanisław Moskała', 96021926535, 'Os. Przyjaźni 7/14', '61-682 Poznań', '739397747'), (7, 'Waldemar Grabowski', 68090952795, 'Garncarska 4/6', '61-817 Poznań', '778607590'), (8, 'Kamil Juskowiak', 80121522634, 'Os. Bolesława Chrobrego 23/8','60-681 Poznań', '792685521'), (9, 'Karol Bieliński', 72052442811, 'Rybaki 15/3', '61-883 Poznań', '686479160'), (10,'Marek Niedzwiecki', 84062025296, 'Kwiatow 11/6', '61-882 Poznań', '691266740'), (11,'Mikołaj Dwojak', 52031246799, 'Wierzbowa 14', '61-853 Poznań', '683813104'), (12,'Alicja Sawicka', 52013045967, 'Sadowa 40', '61-657 Poznań', '745483842'), (13,'Aleksandra Witkowska', 61092776745, 'Os. Stefana Batorego 26/5','60-687 Poznań', '665376109'), (14,'Ania Mazur', 91113056864, 'Os. Lecha 42/3', '61-294 Poznań', '668470403'), (15,'Ewa Maciejewska', 89101164662, 'Taczaka 8/2', '61-818 Poznań', '665443969'), (16,'Dorota Pawlak', 66012944424, 'Os. Zwycięstwa 12/6', '61-646 Poznań', '713598160'), (17,'Danuta Makowska', 59040211567, 'Gromadzka 3', '61-655 Poznań', '599443459'), (18,'Irena Czarnecka', 78050132989, 'Burgundzka 2/22', '61-616 Poznań', '697669494'), (19,'Kamila Andrzejewska', 95060953348, 'Os. Stefana Batorego 7', '61-614 Poznań', '674755082'), (20,'Karolina Kowalczyk', 57032725647, 'Młyńska 3/2', '61-729 Poznań', '773782789') GO INSERT INTO Pacjent (ID, Imie, ID_Wlasciciel, Plec, Gatunek, Rasa, Wiek, Masc) VALUES (1, 'Oreo', 19, 'samiec', 'kawia domowa', 'gładkowłosa', 2, 'biało-czarna'), (2, 'Kiki', 6, 'samica', 'kawia domowa', 'Teddy', 4, 'trikolor'), (3, 'Delta', 6, 'samica', 'kawia domowa', 'Rozetka', 4, 'biała'), (4, 'Lili', 4, 'samica', 'kawia domowa', 'gładkowłosa', 7, 'kremowa'), (5, 'Azor', 11, 'samiec', 'pies', 'jamnik krótkowłosy', 7, 'rudy'), (6, 'Lala', 5, 'samica', 'pies', 'owczarek niemiecki krótkowłosy', 4, 'czarna'), (7, 'Figaro', 17, 'samiec', 'pies', 'owczarek niemiecki długowłosy', 12, 'sezamowa'), (8, 'Amor', 8, 'samiec', 'pies', 'akita', 6, 'czarna podpalana'), (9, 'Pimpek', 9, 'samiec', 'pies', 'nierasowy', 6, 'czarna podpalana'), (10,'Drops', 13, 'samiec', 'pies', 'nierasowy', 6, 'czarna podpalana'), (11,'Kleks', 11, 'samiec', 'kot', 'europejski krótkowłosy', 3, 'szylkretowa'), (12,'Łatka', 12, 'samica', 'kot', 'europejski krótkowłosy', 6, 'czarna'), (13,'Inka', 10, 'samica', 'kot', 'europejski krótkowłosy', 2, 'czarna'), (14,'Salvador', 14, 'samiec', 'kot', 'brytyjski', 8, 'niebieska'), (15,'Izma', 10, 'samica', 'kot', 'europejski krótkowłosy', 4, 'trikolor'), (16,'Falka', 16, 'samica', 'kot', 'rosyjski', 5, 'niebieska'), (17,'Dafne', 15, 'samica', 'królik', 'miniaturowy gładkowłosy', 2, 'biała'), (18,'Saba', 2, 'samica', 'królik', 'mały baran', 1, 'szara'), (19,'Aurel', 7, 'samiec', 'królik', 'olbrzym srokacz', 3, 'kremowa'), (20,'Bugs', 18, 'samiec', 'królik', 'kalifornijski', 2, 'szara'), (21,'Wąsik', 2, 'samiec', 'królik', 'mały baran', 3, 'biała'), (22,'Bambina', 12, 'samiec', 'szynszyl','szynszyla mała', 2, 'szara'), (23,'Figa', 20, 'samica', 'papuga', 'papużka falista', 3, 'żółto-zielona'), (24,'Edek', 20, 'samiec', 'papuga', 'papużka falista', 3, 'niebiesko-żółta'), (25,'Bonnie', 3, 'samica', 'papuga', 'nimfa', 4, 'żółta'), (26,'Clyde', 1, 'samiec', 'papuga', 'nimfa', 2, 'szara') GO INSERT INTO Wizyta ([Data], ID_Pacjent, ID_Wlasciciel, ID_Diagnoza, ID_Lekarz, Objawy, Leczenie, Szczepienie) VALUES ('2020-06-1 10:00:00', 23, 20, 1, 1, 'gąbczasta narośl na dziobie, kanaliki', 'maść przeciw pasożytom', 0), ('2020-06-1 10:00:00', 24, 20, 1, 3, 'gąbczasta narośl na dziobie, kanaliki', 'maść przeciw pasożytom', 0), ('2020-06-1 10:30:00', 25, 3, 13, 2, 'nie wznosi się do lotu, uszkodzenie prawego skrzydła', 'unieruchomienie skrzydła, lek przeciwbólowy', 0), ('2020-06-2 11:00:00', 26, 1, 4, 1, 'wyskubuje sobie piórka, widoczne czarne pasożyty na piórach', 'lek przeciw pasożytom', 0), ('2020-06-2 11:30:00', 1, 19, 1, 3, 'wypadanie sierści, drapanie się, strupki, brak apetytu', 'lek przeciw pasożytom, lek przeciwbólowy', 0), ('2020-06-4 12:00:00', 11, 11, 1, 4, 'wylizywanie sierści, drapanie się', 'lek przeciw pasożytom', 1), ('2020-06-4 12:30:00', 15, 10, 7, 4, 'nagły spadek wagi, zwiększony apetyt, wysokie TSH', 'stały lek, zrównoważona dieta', 0), ('2020-06-4 13:00:00', 2, 6, 5, 1, 'nagły spadek wagi, zwiększone pragnienie, wysoki poziom glukozy', 'zrównoważona dieta', 0), ('2020-06-5 13:30:00', 8, 8, 7, 4, 'nadmierna masa ciała, zwiększone pragnienie, wysokie TSH', 'zrównoważona dieta', 1), ('2020-07-6 14:30:00', 20, 18, 8, 3, 'biegunka, osowiałość, brak apetytu', 'antybiotyk, kroplówka, prebiotyk, lek przeciw biegunce', 1), ('2020-07-6 15:30:00', 3, 6, 1, 3, 'wypadanie sierści, drapanie się, strupki, brak apetytu', 'lek przeciwbólowy, antybiotyk', 0), ('2020-07-6 16:30:00', 22, 12, 14, 1, 'rany po walce z innym zwierzęciem', 'lek przeciwbólowy, antybiotyk', 0), ('2020-07-6 17:30:00', 18, 2, 10, 3, 'świąd, miejscowe wypadanie sierści', 'środek odkażający, lek przeciwbólowy', 0), ('2020-07-7 11:00:00', 5, 11, 5, 4, 'apatia, osowiałość, zwiększone pragnienie, wysoki poziom glukozy', 'zrównoważona dieta', 1), ('2020-07-7 12:00:00', 6, 5, 14, 2, 'rany po walce z innym zwierzęciem', 'lek przeciwbólowy, antybiotyk, środek odkażający', 1), ('2020-07-7 13:30:00', 4, 4, 7, 3, 'nadmierna masa ciała, zwiększone pragnienie, wysokie TSH', 'hormon, zrównoważona dieta', 0), ('2020-07-10 10:00:00',19, 7, 6, 1, 'nadmierna masa ciała, zwiększone pragnienie, niskie TSH', 'hormon, zrównoważona dieta', 0), ('2020-07-10 14:30:00',23, 20, 14, 1, 'rany po walce z innym zwierzęciem', 'lek przeciwbólowy, antybiotyk, środek odkażający', 0), ('2020-07-10 15:00:00',4, 4, 14, 3, 'rany po walce z innym zwierzęciem', 'lek przeciwbólowy, antybiotyk, środek odkażający', 0), ('2020-07-10 16:30:00', 7, 17, 8, 4, 'biegunka, osowiałość, brak apetytu', 'antybiotyk, kroplówka, prebiotyk, lek przeciw biegunce', 1), ('2020-08-3 11:00:00', 12, 12, 16, 4, 'nietrzymanie moczu, napinanie się przy oddawaniu moczu, chowanie się', 'antybiotyk, lek przeciwzapalny, suplementy wspomagające', 0), ('2020-08-3 11:30:00', 14, 14, 9, 4, 'miejscowe wypadanie sierści, rany, wylizywanie', 'środek odkażający, lek przeciwbólowy', 1), ('2020-08-4 13:00:00', 1, 19, 8, 3, 'biegunka, osowiałość, brak apetytu', 'antybiotyk, kroplówka, prebiotyk, lek przeciw biegunce', 0), ('2020-08-4 14:30:00', 10, 13, 16, 1, 'nietrzymanie moczu, napinanie się przy oddawaniu moczu', 'antybiotyk, lek przeciwzapalny, suplementy wspomagające', 1), ('2020-08-4 15:00:00', 13, 10, 15, 3, 'kichanie, wypływ surowiczy z nosa, osowiałość', 'antybiotyk, szczególna opieka w domu', 0), ('2020-08-6 15:30:00', 26, 1, 4, 1, 'wyskubuje sobie piórka, widoczne czarne pasożyty na piórach', 'lek przeciw pasożytom', 0), ('2020-08-6 16:00:00', 6, 5, 12, 2, 'utykanie, osowiałość, chowanie się', 'lek przeciwbólowy, antybiotyk', 1), ('2020-08-6 16:30:00', 3, 6, 16, 3, 'nietrzymanie moczu, napinanie się przy oddawaniu moczu, piszczenie','antybiotyk, suplementy wspomagające', 0), ('2020-09-6 13:00:00', 16, 16, 15, 4, 'łzawienie, wypływ surowiczy z nosa, apatia', 'antybiotyk', 0), ('2020-09-7 12:30:00', 9, 9, 14, 1, 'rany po walce z innym zwierzęciem', 'lek przeciwbólowy, antybiotyk', 1), ('2020-09-7 14:00:00', 10, 13, 9, 4, 'zwierzę nadmiernie się drapie, wszoły na skórze', 'preparat przeciw wszołom', 1), ('2020-09-7 15:30:00', 24, 20, 14, 2, 'rany po walce z innym zwierzęciem', 'lek przeciwbólowy, antybiotyk', 0), ('2020-09-7 16:00:00', 17, 15, 16, 1, 'nietrzymanie moczu, napinanie się przy oddawaniu moczu, piszczenie', 'antybiotyk, lek przeciwzapalny, suplementy wspomagające', 0), ('2020-09-8 11:30:00', 5, 11, 16, 4, 'nietrzymanie moczu, napinanie się przy oddawaniu moczu, chowanie się', 'antybiotyk, lek przeciwzapalny, suplementy wspomagające', 0), ('2020-09-8 13:00:00', 14, 14, 16, 4, 'nietrzymanie moczu, napinanie się przy oddawaniu moczu', 'antybiotyk, lek przeciwzapalny, suplementy wspomagające', 1), ('2020-09-8 14:30:00', 22, 12, 2, 3, 'zwierzę nadmiernie się drapie, wszoły na skórze', 'preparat przeciw wszołom', 0), ('2020-09-8 15:00:00', 21, 2, 3, 3, 'wypadanie sierści, drapanie się, strupki, brak apetytu', 'lek przeciw pasożytom, lek przeciwbólowy', 0) GO -- --------------------------------- -- Widoki -- --------------------------------- IF OBJECT_ID('Najczęściej odwiedzany lekarz', 'V') IS NOT NULL DROP VIEW MostVisitedVet GO CREATE VIEW MostVisitedVet AS ( SELECT TOP(1) l.Imie_Nazwisko, COUNT(l.ID) AS liczba_wizyt FROM Lekarz AS l LEFT JOIN Wizyta AS w ON l.ID = w.ID_Lekarz GROUP BY l.Imie_Nazwisko ORDER BY liczba_wizyt DESC ) GO IF OBJECT_ID('Najczęściej szczepiony gatunek zwierzęcia', 'V') IS NOT NULL DROP VIEW MostVaccinatedSpecies GO CREATE VIEW MostVaccinatedSpecies AS ( SELECT TOP(1) p.Gatunek, COUNT(w.Szczepienie) AS Liczba_szczepień FROM Pacjent AS p LEFT JOIN Wizyta AS w ON p.ID = w.ID_Pacjent GROUP BY p.Gatunek ) GO IF OBJECT_ID('', 'V') IS NOT NULL DROP VIEW MostVisitingPatients GO CREATE VIEW MostVisitingPatients AS ( SELECT TOP (3) dbo.Wlasciciel.Imie_Nazwisko, COUNT(dbo.Wizyta.ID) AS Liczba_wizyt FROM dbo.Wlasciciel INNER JOIN dbo.Wizyta ON dbo.Wlasciciel.ID = dbo.Wizyta.ID_Wlasciciel GROUP BY dbo.Wlasciciel.Imie_Nazwisko ) GO -- --------------------------------------------- -- Procedury -- --------------------------------------------- IF OBJECT_ID('CHOROBY_DANEGO_GATUNKU','P') IS NOT NULL DROP PROCEDURE CHOROBY_DANEGO_GATUNKU GO CREATE PROCEDURE CHOROBY_DANEGO_GATUNKU (@Gatunek VARCHAR(30)) AS BEGIN SELECT dbo.Pacjent.Gatunek, dbo.Diagnoza.Choroba FROM dbo.Wizyta INNER JOIN dbo.Pacjent ON dbo.Wizyta.ID_Pacjent = dbo.Pacjent.ID INNER JOIN dbo.Diagnoza ON dbo.Wizyta.ID_Diagnoza = dbo.Diagnoza.ID WHERE dbo.Pacjent.Gatunek = @Gatunek END IF OBJECT_ID('OBJAWY_DANYCH_CHOROB','P') IS NOT NULL DROP PROCEDURE OBJAWY_DANYCH_CHOROB GO CREATE PROCEDURE OBJAWY_DANYCH_CHOROB (@Choroba VARCHAR(50)) AS BEGIN SELECT dbo.Diagnoza.Choroba, dbo.Wizyta.Objawy FROM dbo.Wizyta INNER JOIN dbo.Diagnoza ON dbo.Wizyta.ID_Diagnoza = dbo.Diagnoza.ID WHERE dbo.Diagnoza.Choroba = @Choroba END -- --------------------------------------------- -- Raporty -- --------------------------------------------- PRINT 'Najczęsciej odwiedzany lekarz' SELECT * FROM MostVisitedVet; PRINT 'Najczęściej szczepiony gatunek zwierzęcia' SELECT * FROM MostVaccinatedSpecies; PRINT 'Najczęstsze choroby danego gatunku (szynszyla)' EXEC CHOROBY_DANEGO_GATUNKU @Gatunek = 'szynszyl' PRINT 'Najczęstsze objawy chorób' EXEC OBJAWY_DANYCH_CHOROB @Choroba = 'cukrzyca' PRINT 'Najczęściej odwiedzający właściciele zwierząt' SELECT * FROM MostVisitingPatients; ------------------------------------------------ -- Usunięcie bazy -- Zakomentuj, jeśli uruchamiasz skrypt na serwerze MSSQL ------------------------------------------------ USE master GO IF DB_ID('BazaPrzychodni') IS NOT NULL DROP DATABASE BazaPrzychodni GO