· 4 years ago · May 09, 2021, 04:28 PM
1--USE master;
2--DROP DATABASE ACST;
3--GO
4
5--CREATE DATABASE ACST;
6--GO
7
8--USE ACST;
9--GO
10
11------------ USUŃ TABELE ------------
12
13DROP TABLE IF EXISTS Horse_equipment CASCADE;
14DROP TABLE IF EXISTS Health CASCADE;
15DROP TABLE IF EXISTS Medicine CASCADE;
16DROP TABLE IF EXISTS Event_type CASCADE;
17DROP TABLE IF EXISTS Horse_nutrition CASCADE;
18DROP TABLE IF EXISTS Horse_keeper CASCADE;
19DROP TABLE IF EXISTS Nutrition_type CASCADE;
20DROP TABLE IF EXISTS Nutrition_measurement CASCADE;
21DROP TABLE IF EXISTS Equipment CASCADE;
22DROP TABLE IF EXISTS Horse CASCADE;
23DROP TABLE IF EXISTS Users CASCADE;
24
25
26------------ CREATE - UTWoRZ TABELE I POWIaZANIA ------------
27
28CREATE TABLE Horse
29(
30 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
31 name VARCHAR(30),
32 birthdate VARCHAR(15),
33 isPhoto BOOLEAN DEFAULT FALSE
34);
35
36CREATE TABLE Nutrition_type
37(
38 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
39 name VARCHAR(30) UNIQUE
40);
41
42CREATE TABLE Nutrition_measurement
43(
44 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
45 name VARCHAR(30) UNIQUE
46);
47
48CREATE TABLE Horse_nutrition
49(
50 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
51 horse_id INTEGER NOT NULL REFERENCES Horse (id),
52 nutrition_type_id INTEGER NOT NULL REFERENCES Nutrition_type(id),
53 nutrition_measurement_id INTEGER NOT NULL REFERENCES Nutrition_measurement (id),
54 feeding_time VARCHAR(10) CHECK (feeding_time in ('breakfast', 'dinner', 'supper'))
55);
56
57CREATE TABLE Equipment
58( id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
59 name VARCHAR(30) UNIQUE
60);
61
62CREATE TABLE Horse_equipment
63(
64 horse_id INTEGER NOT NULL REFERENCES Horse (id),
65 equipment_id INTEGER NOT NULL REFERENCES Equipment(id),
66 PRIMARY KEY (horse_id, equipment_id)
67);
68
69CREATE TABLE Health
70(
71 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
72 horse_id INTEGER NOT NULL REFERENCES Horse (id),
73 description VARCHAR(100) NOT NULL
74);
75
76CREATE TABLE Medicine
77(
78 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
79 horse_id INTEGER NOT NULL REFERENCES Horse (id),
80 description VARCHAR(100) NOT NULL
81);
82
83CREATE TABLE Users
84(
85 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
86 name VARCHAR(30),
87 surname VARCHAR(30),
88 telephone INTEGER,
89 e_mail VARCHAR(30) UNIQUE,
90 status VARCHAR(10) DEFAULT 'NIEAKTYWNY',
91 administrator BOOLEAN DEFAULT FALSE,
92 trainer BOOLEAN DEFAULT FALSE
93
94);
95
96CREATE TABLE Horse_keeper
97(
98 horse_id INTEGER NOT NULL REFERENCES Horse(id),
99 user_id INTEGER NOT NULL REFERENCES Users (id),
100 role VARCHAR(10) NOT NULL,
101 PRIMARY KEY (horse_id, user_id)
102);
103
104CREATE TABLE Event_type
105(
106 id INTEGER NOT NULL generated always as identity (increment by 1) PRIMARY KEY,
107 name VARCHAR(20) UNIQUE
108);
109
110
111------------ INSERT DATA ------------
112
113INSERT INTO Nutrition_type (name) VALUES
114('siano'),
115('owies'),
116('sieczka'),
117('musli'),
118('wysłodki'),
119('pudełko');
120
121
122INSERT INTO Nutrition_measurement (name) VALUES
123('0.5 miarki'),
124('1 miarka'),
125('1.5 miarki'),
126('2 miarki'),
127('3 miarki'),
128('wiadro'),
129('<bez miary>');
130
131INSERT INTO Horse (name, birthdate) VALUES
132('Rapidash', '05-05-2009'),
133('Pierun', '04-07-2010'),
134('Czereda', '25-04-2010'),
135('Jowisz', '06-08-2004'),
136('Charyzma', '03-03-2007' ),
137('Olucha', '01-12-1999'),
138('Floryda', '08-02-2020'),
139('Adusia', '08-05-1998'),
140('Damianek', '28-02-1998'),
141('Werwa', '02-01-2015'),
142('Ferrari', '05-05-2009'),
143('Mariola', '04-08-2016'),
144('Wersow', '25-04-2010'),
145('Patec', '06-08-2004'),
146('Krzychu', '03-03-2007' ),
147('Potworek', '01-09-2012'),
148('Strzała', '08-01-2021'),
149('Geralt', '04-12-2008'),
150('Andziaks', '30-06-2013'),
151('Płotka', '07-11-2014');
152
153INSERT INTO Horse (name, birthdate, isPhoto) VALUES
154('Reksio', '25-05-2009', TRUE),
155('Boxdel', '14-07-2012', TRUE),
156('Muranski', '21-04-2010', TRUE),
157('Haribo', '03-07-2008', TRUE);
158
159
160
161------------ PROCEDURY ------------
162
163CREATE PROCEDURE Nowy_kon (imie VARCHAR(30), data_urodzenia DATE)
164 LANGUAGE SQL
165
166AS $$
167
168 INSERT INTO Horse (name) VALUES (name);
169 INSERT INTO Horse (birthdate) VALUES (birthdate);
170$$;
171
172CREATE PROCEDURE Usun_konia (kon INTEGER)
173 LANGUAGE SQL
174
175AS $$
176 DELETE FROM Horse WHERE id = Horse;
177$$;
178
179CREATE PROCEDURE Edytuj_konia (imie VARCHAR(30), data_urodzenia DATE)
180 LANGUAGE SQL
181
182AS $$
183 UPDATE Horse
184 SET name =
185 WHERE id = Horse;
186$$;
187
188
189
190CREATE PROCEDURE Nowy_wpis_leki (kon INTEGER, opis VARCHAR(100))
191 LANGUAGE SQL
192AS $$
193INSERT INTO Medicine(kon_id)VALUES(kon);
194 INSERT INTO Medicine (opis) VALUES (opis);
195$$;
196
197CREATE PROCEDURE Nowy_wpis_zdrowie (kon INTEGER, opis VARCHAR(100))
198 LANGUAGE SQL
199AS $$
200 INSERT INTO Health (kon_id) VALUES(kon);
201 INSERT INTO Health (opis) VALUES (opis)
202$$;
203
204CREATE PROCEDURE Nowe_zywienie (kon INTEGER, pozywienie INTEGER, porcja INTEGER, pora_karmienia VARCHAR(10))
205 LANGUAGE SQL
206AS $$
207 INSERT INTO Horse_nutrition (kon_id) VALUES(kon);
208 INSERT INTO Horse_nutrition (rodzaj_pozywienia_id) VALUES (pozywienie);
209 INSERT INTO Horse_nutrition (porcja_pozywienia_id) VALUES (porcja);
210 INSERT INTO Horse_nutrition (pora_karmienia) VALUES (pora_karmienia)
211$$;
212
213CREATE PROCEDURE Nowy_rodzaj_pozywienia (nazwa VARCHAR(30))
214 LANGUAGE SQL
215AS $$
216 INSERT INTO Nutrition_type (name) VALUES(name)
217$$;
218
219CREATE PROCEDURE Nowa_porcja_pozywienia (nazwa VARCHAR(30))
220 LANGUAGE SQL
221AS $$
222INSERT INTO Nutrition_measurement(nazwa)VALUES(nazwa)
223$$;
224
225CREATE PROCEDURE Nowe_dostepne_wyposazenie (nazwa VARCHAR(30))
226 LANGUAGE SQL
227AS $$
228 INSERT INTO Equipment (nazwa) VALUES(nazwa)
229$$;
230
231CREATE PROCEDURE Nowe_wyposazenie_konia (kon INTEGER, wyposazenie INTEGER)
232 LANGUAGE SQL
233AS $$
234INSERT INTO Horse_equipment(kon_id)VALUES(kon);
235INSERT INTO Horse_equipment(wyposazenie_id)VALUES(wyposazenie)
236$$;
237
238CREATE PROCEDURE Nowy_uzytkownik (imie VARCHAR(30), nazwisko VARCHAR(30), telefon INTEGER, e_mail VARCHAR(30))
239 LANGUAGE SQL
240AS $$
241 INSERT INTO Users (imie) VALUES(imie);
242 INSERT INTO Users (nazwisko) VALUES(nazwisko);
243 INSERT INTO Users (telefon) VALUES(telefon);
244 INSERT INTO Users (e_mail) VALUES(e_mail)
245$$;
246
247CREATE PROCEDURE Nowy_dysponent (kon INTEGER, uzytkownik INTEGER, rola VARCHAR(10) )
248 LANGUAGE SQL
249AS $$
250 INSERT INTO Horse_keeper (kon_id) VALUES(kon);
251 INSERT INTO Horse_keeper (uzytkownik_id) VALUES(uzytkownik);
252 INSERT INTO Horse_keeper (rola) VALUES(rola)
253$$;
254
255CREATE PROCEDURE Nowy_typ_wydarzenia (nazwa VARCHAR(20))
256 LANGUAGE SQL
257AS $$
258 INSERT INTO Event_type (nazwa) VALUES(nazwa);
259$$;
260
261
262------------ SELECT ------------
263
264SELECT * FROM Horse;
265SELECT * FROM Nutrition_type;
266SELECT * FROM Nutrition_measurement;
267SELECT * FROM Horse_nutrition;
268SELECT * FROM Equipment;
269SELECT * FROM Horse_equipment;
270SELECT * FROM Health;
271SELECT * FROM Medicine;
272SELECT * FROM Users;
273SELECT * FROM Event_type;