1 | -- се поставува патека односно шема во која ќе се извршуваат командите
|
---|
2 |
|
---|
3 | set search_path = project;
|
---|
4 |
|
---|
5 | -- бришење на старите податоци од табелите
|
---|
6 |
|
---|
7 | DELETE FROM student;
|
---|
8 | DELETE FROM task;
|
---|
9 | DELETE FROM feedback;
|
---|
10 | DELETE FROM school_year ;
|
---|
11 | DELETE FROM term ;
|
---|
12 | DELETE FROM subject_at_term ;
|
---|
13 | DELETE FROM calendar_event ;
|
---|
14 | DELETE FROM repeating_event ;
|
---|
15 | DELETE FROM non_repeating_event ;
|
---|
16 | DELETE FROM e_mail ;
|
---|
17 | DELETE FROM reminder ;
|
---|
18 | DELETE FROM place ;
|
---|
19 | DELETE FROM dashboard ;
|
---|
20 | DELETE FROM notes ;
|
---|
21 | DELETE FROM professor ;
|
---|
22 | DELETE FROM subject_book ;
|
---|
23 | DELETE FROM author ;
|
---|
24 | DELETE FROM chapter ;
|
---|
25 |
|
---|
26 | DELETE FROM has_taken_a_subject;
|
---|
27 | DELETE FROM holds_subject;
|
---|
28 | DELETE FROM holds_event;
|
---|
29 | DELETE FROM happens_at_place;
|
---|
30 | DELETE FROM recommended_book ;
|
---|
31 | DELETE FROM recommended_chapter ;
|
---|
32 | DELETE FROM has_consultations ;
|
---|
33 | DELETE FROM year_has_subject ;
|
---|
34 | DELETE FROM dashboard_event ;
|
---|
35 | DELETE FROM subject_event ;
|
---|
36 | DELETE FROM creates_task ;
|
---|
37 | DELETE FROM student_event ;
|
---|
38 | DELETE FROM student_task ;
|
---|
39 | DELETE FROM gives_feedback ;
|
---|
40 | DELETE FROM dashboard_task ;
|
---|
41 | DELETE FROM book_author ;
|
---|
42 |
|
---|
43 | -- Внесување test data во табелата 'student'
|
---|
44 | INSERT INTO student (student_id, username, s_first_name, s_last_name, s_password)
|
---|
45 | VALUES
|
---|
46 | (91324, 'SaraS', 'Sara', 'Stojilkova', 'password123'),
|
---|
47 | (13467, 'MagiStoj', 'Margarita', 'Stojkovska', 'securepass'),
|
---|
48 | (98727, 'IlinkaS', 'Ilinka', 'Stavrova', 'secret789');
|
---|
49 |
|
---|
50 | -- Внесување test data во табелата 'subject_book'
|
---|
51 | INSERT INTO subject_book (book_id, book_title, b_year_of_publishment, book_edition)
|
---|
52 | VALUES
|
---|
53 | (245, 'Mathematics Textbook', 2020, '2nd Edition'),
|
---|
54 | (10, 'History of Software', 2019, '1st Edition'),
|
---|
55 | (89, 'Algebra for Beginners', 2010, '1st Edition');
|
---|
56 |
|
---|
57 | -- Внесување test data во табелата 'chapter'
|
---|
58 | INSERT INTO chapter (book_id, chapter_number, chapter_title)
|
---|
59 | VALUES
|
---|
60 | (245, 1, 'Introduction'),
|
---|
61 | (10, 2, 'Fundamentals'),
|
---|
62 | (89, 1, 'Introduction to Algebra');
|
---|
63 |
|
---|
64 |
|
---|
65 | -- Внесување test data во табелата 'author'
|
---|
66 | INSERT INTO author (author_id, author_name, a_short_biography)
|
---|
67 | VALUES
|
---|
68 | (1, 'John Matheson', 'Renowned mathematician and author.'),
|
---|
69 | (2, 'Alice Historian', 'Specialized in ancient history.'),
|
---|
70 | (3, 'Vangel Ajanovski', 'Profesor na FINKI.');
|
---|
71 |
|
---|
72 | -- Внесување test data во табелата 'feedback'
|
---|
73 | INSERT INTO feedback (fb_id, fb_date, fb_title, fb_description, fb_rating)
|
---|
74 | VALUES
|
---|
75 | (1, '2024-01-23', 'Great', 'Odlicna aplikacija kolegi. Mn lesna za koristenje', 5),
|
---|
76 | (2, '2024-01-24', 'OK', 'Bi sakal da ima pomoderen dizajn', 3),
|
---|
77 | (45, '2022-05-21', 'Super', 'Sekojdnevno ja koristam aplikacijata, super e!', 5);
|
---|
78 |
|
---|
79 | -- Внесување test data во табелата 'e_mail'
|
---|
80 | INSERT INTO e_mail (student_id, e_mail_id, e_mail_address)
|
---|
81 | VALUES
|
---|
82 | (91324, 28, 'sara.stojilkova@gmail.com'),
|
---|
83 | (13467, 45, 'margarita.stojkovska@hotmail.com'),
|
---|
84 | (98727, 789, 'ilinka.stavrova@students.com');
|
---|
85 |
|
---|
86 | -- Внесување test data во табелата 'school_year'
|
---|
87 | INSERT INTO school_year (student_id, starting_year, finishing_year)
|
---|
88 | VALUES
|
---|
89 | (91324, 2023, 2024),
|
---|
90 | (13467, 2023, 2024),
|
---|
91 | (98727, 2023, 2024);
|
---|
92 |
|
---|
93 | -- Внесување test data во табелата 'term'
|
---|
94 | INSERT INTO term (student_id, starting_year, finishing_year, term_type, term_start_date, term_end_date)
|
---|
95 | VALUES
|
---|
96 | (91324, 2023, 2024, 'Zimski', '2023-09-01', '2024-01-20'),
|
---|
97 | (13467, 2023, 2024, 'Zimski', '2022-01-15', '2023-01-20'),
|
---|
98 | (98727, 2023, 2024, 'Leten', '2024-02-01', '2024-06-20');
|
---|
99 |
|
---|
100 | -- Внесување test data во табелата 'calendar_event'
|
---|
101 | INSERT INTO calendar_event (calendar_event_id, event_name , event_type , event_description, event_start_time, event_end_time)
|
---|
102 | VALUES
|
---|
103 | (16578, 'Predavanje Bazi', 'Predavanje', 'Introduction to SQL', '16:33', '18:00'),
|
---|
104 | (26543, 'Predavanje Kalkulus', 'Predavanje', 'Funkcii', '13:00', '14:00'),
|
---|
105 | (98352, 'Vezbi Bazi', 'Vezbi', 'Izrabotka na ER dijagram', '18:00', '18:45');
|
---|
106 |
|
---|
107 | -- Внесување test data во табелата 'reminder'
|
---|
108 | INSERT INTO reminder (calendar_event_id, r_id, minutes_before_event, r_flag)
|
---|
109 | VALUES
|
---|
110 | (16578, 134, 30, true),
|
---|
111 | (26543, 769, 15, true),
|
---|
112 | (98352, 249, 15, true);
|
---|
113 |
|
---|
114 | -- Внесување test data во табелата 'place'
|
---|
115 | INSERT INTO place (place_id, building_name, room_name)
|
---|
116 | VALUES
|
---|
117 | (1, 'FINKI Podrum', 'Lab 301'),
|
---|
118 | (9, 'Dekanat', 'Soba 10'),
|
---|
119 | (12, 'FEIT', 'Lab 102');
|
---|
120 |
|
---|
121 | -- Внесување test data во табелата 'dashboard'
|
---|
122 | INSERT INTO dashboard (student_id, dashboard_id, dashboard_date)
|
---|
123 | VALUES
|
---|
124 | (91324, 14, '2023-01-22'),
|
---|
125 | (13467, 26, '2023-07-19'),
|
---|
126 | (13467, 27, '2023-08-20');
|
---|
127 |
|
---|
128 | -- Внесување test data во табелата 'notes'
|
---|
129 | INSERT INTO notes (student_id, dashboard_id, note_id, notes_date, note_text)
|
---|
130 | VALUES
|
---|
131 | (91324, 14, 13, '2023-01-22', 'Ne zaboravaj da zemes potvrda od studentski prasanja.'),
|
---|
132 | (13467, 26, 2, '2023-07-19', 'Zapisi semestar.'),
|
---|
133 | (13467, 27, 27, '2023-08-20', 'Prijavi se na anketa za ispit Bazi.');
|
---|
134 |
|
---|
135 | -- Внесување test data во табелата 'professor'
|
---|
136 | INSERT INTO professor (professor_id, p_first_name, p_last_name, p_role, p_email)
|
---|
137 | VALUES
|
---|
138 | (1, 'Vangel', 'Ajanovski', 'Profesor', 'vangel.ajanovski@profesor.com'),
|
---|
139 | (24, 'Sara', 'Milevska', 'Asistent', 'sara.milevska@efinki.com'),
|
---|
140 | (34, 'Smile', 'Smilevski', 'Profesor', 'smiles@finki.com');
|
---|
141 |
|
---|
142 | -- Внесување test data во табелата 'subject_at_term'
|
---|
143 | INSERT INTO subject_at_term (student_id, starting_year, finishing_year, term_type, term_subject_id, subject_name)
|
---|
144 | VALUES
|
---|
145 | (91324, 2023, 2024, 'Zimski', 56, 'Napredno programiranje'),
|
---|
146 | (13467, 2023, 2024, 'Zimski', 23, 'Bazi na podatoci'),
|
---|
147 | (98727, 2023, 2024, 'Leten', 67, 'Internet Tehnologii');
|
---|
148 |
|
---|
149 | -- Внесување test data во табелата 'has_taken_a_subject'
|
---|
150 | INSERT INTO has_taken_a_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, student_id_ref)
|
---|
151 | VALUES
|
---|
152 | (91324, 2023, 2024, 'Zimski', 56, 91324),
|
---|
153 | (13467, 2023, 2024, 'Zimski', 23, 13467),
|
---|
154 | (98727, 2023, 2024, 'Leten', 67, 98727);
|
---|
155 |
|
---|
156 | -- Внесување test data во табелата 'holds_subject'
|
---|
157 | INSERT INTO holds_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, professor_id)
|
---|
158 | VALUES
|
---|
159 | (91324, 2023, 2024, 'Zimski', 56, 34),
|
---|
160 | (13467, 2023, 2024, 'Zimski', 23, 1),
|
---|
161 | (98727, 2023, 2024, 'Leten', 67, 24);
|
---|
162 |
|
---|
163 | -- Внесување test data во табелата 'task'
|
---|
164 | INSERT INTO task (task_id, task_name, task_description, task_priority)
|
---|
165 | VALUES
|
---|
166 | (11111, 'dbLearn zadaci', 'Resavanje na serija od dbLearn', 2),
|
---|
167 | (22222, 'Proekt', 'Dorabotka na faza 2 od proekt po bazi na podatoci', 1),
|
---|
168 | (33333, 'Podgotovka za vtor kolokvium','Resavanje na auditoriski vezbi 6-11', 1);
|
---|
169 |
|
---|
170 | -- Внесување test data во табелата 'creates_task'
|
---|
171 | INSERT INTO creates_task (task_id, student_id, starting_year, finishing_year, term_type, term_subject_id)
|
---|
172 | VALUES
|
---|
173 | (11111, 13467, 2023, 2024, 'Zimski', 23),
|
---|
174 | (22222, 13467, 2023, 2024, 'Zimski', 23),
|
---|
175 | (33333, 98727, 2023, 2024, 'Leten', 67);
|
---|
176 |
|
---|
177 | -- Внесување test data во табелата 'task - 1'
|
---|
178 | INSERT INTO task (task_id, task_name, task_description, task_priority)
|
---|
179 | VALUES
|
---|
180 | (44444, 'Generalka', '', 3);
|
---|
181 |
|
---|
182 | -- Внесување test data во табелата 'student_task'
|
---|
183 | INSERT INTO student_task (task_id, student_id)
|
---|
184 | VALUES
|
---|
185 | (44444, 98727);
|
---|
186 |
|
---|
187 | -- Внесување test data во табелата 'book_author'
|
---|
188 | INSERT INTO book_author (author_id, book_id)
|
---|
189 | VALUES
|
---|
190 | (1, 245),
|
---|
191 | (2, 10),
|
---|
192 | (3, 89);
|
---|
193 |
|
---|
194 | -- Внесување test data во табелата 'happens_at_place'
|
---|
195 | INSERT INTO happens_at_place (calendar_event_id, place_id)
|
---|
196 | VALUES
|
---|
197 | (16578, 1),
|
---|
198 | (26543, 9),
|
---|
199 | (98352, 12);
|
---|
200 |
|
---|
201 | -- Внесување test data во табелата 'recommended_chapter'
|
---|
202 | INSERT INTO recommended_chapter (calendar_event_id, book_id, chapter_number)
|
---|
203 | VALUES
|
---|
204 | (16578, 245, 1),
|
---|
205 | (26543, 245, 1),
|
---|
206 | (26543, 89, 1),
|
---|
207 | (98352, 89, 1);
|
---|
208 |
|
---|
209 | -- Внесување test data во табелата 'calendar_event - 2'
|
---|
210 | INSERT INTO calendar_event (calendar_event_id, event_name , event_type , event_description, event_start_time, event_end_time)
|
---|
211 | VALUES
|
---|
212 | (66666, 'Konsultacii bazi', 'Konsultacii', 'Odbrana na faza 2', '16:00', '18:00');
|
---|
213 |
|
---|
214 | -- Внесување test data во табелата 'has_consultations'
|
---|
215 | INSERT INTO has_consultations (calendar_event_id, professor_id)
|
---|
216 | VALUES
|
---|
217 | (66666, 1);
|
---|
218 |
|
---|
219 | -- Внесување test data во табелата 'subject_event'
|
---|
220 | INSERT INTO subject_event (calendar_event_id, student_id, starting_year, finishing_year, term_type, term_subject_id)
|
---|
221 | VALUES
|
---|
222 | (16578, 13467, 2023, 2024, 'Zimski', 23),
|
---|
223 | (26543, 91324, 2023, 2024, 'Zimski', 56),
|
---|
224 | (98352, 13467, 2023, 2024, 'Zimski', 23);
|
---|
225 |
|
---|
226 | -- Внесување test data во табелта 'holds_event'
|
---|
227 | INSERT INTO holds_event (calendar_event_id, professor_id)
|
---|
228 | VALUES
|
---|
229 | (16578, 1),
|
---|
230 | (26543, 24),
|
---|
231 | (98352, 34);
|
---|
232 |
|
---|
233 | -- Внесување test data во табелата 'dashboard_task'
|
---|
234 | INSERT INTO dashboard_task (task_id, student_id, dashboard_id)
|
---|
235 | VALUES
|
---|
236 | (11111, 91324, 14),
|
---|
237 | (22222, 13467, 26),
|
---|
238 | (33333, 13467, 27);
|
---|
239 |
|
---|
240 | -- Внесување test data во табелата 'gives_feedback'
|
---|
241 | INSERT INTO gives_feedback (fb_id, student_id)
|
---|
242 | VALUES
|
---|
243 | (1, 13467),
|
---|
244 | (2, 91324),
|
---|
245 | (45, 91324);
|
---|
246 |
|
---|
247 | -- Внесување test data во табелата 'recommended_book'
|
---|
248 | INSERT INTO recommended_book (student_id, starting_year, finishing_year, term_type, term_subject_id, book_id)
|
---|
249 | VALUES
|
---|
250 | (91324, 2023, 2024, 'Zimski', 56, 245),
|
---|
251 | (91324, 2023, 2024, 'Zimski', 56, 10),
|
---|
252 | (13467, 2023, 2024, 'Zimski', 23, 89);
|
---|
253 |
|
---|
254 | -- Внесување test data во табелата 'dashboard_event'
|
---|
255 | INSERT INTO dashboard_event (calendar_event_id, student_id, dashboard_id)
|
---|
256 | VALUES
|
---|
257 | (66666, 91324, 14),
|
---|
258 | (16578, 13467, 26),
|
---|
259 | (98352, 13467, 26);
|
---|
260 |
|
---|
261 | -- Внесување test data во табелата 'year_has_subject'
|
---|
262 | INSERT INTO year_has_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, student_id_ref, starting_year_ref, finishing_year_ref)
|
---|
263 | VALUES
|
---|
264 | (91324, 2023, 2024, 'Zimski', 56, 91324, 2023, 2024),
|
---|
265 | (13467, 2023, 2024, 'Zimski', 23, 13467, 2023, 2024),
|
---|
266 | (98727, 2023, 2024, 'Leten', 67, 98727, 2023, 2024);
|
---|
267 |
|
---|
268 | -- Внесување test data во табелата 'calendar_event - 3'
|
---|
269 | INSERT INTO calendar_event (calendar_event_id, event_name , event_type, event_start_time, event_end_time)
|
---|
270 | VALUES
|
---|
271 | (88888, 'Rabotilnica za pretpriemnistvo', 'Rabotilnica', '14:00', '16:00');
|
---|
272 |
|
---|
273 | -- Внесување test data во табелата 'student_event'
|
---|
274 | INSERT INTO student_event (calendar_event_id, student_id)
|
---|
275 | VALUES
|
---|
276 | (88888, 91324);
|
---|
277 |
|
---|
278 | -- Внесување test data во табелата 'repeating_event'
|
---|
279 | INSERT INTO repeating_event (calendar_event_id, event_week_day, repeats_every_n_weeks, starting_date, ending_date)
|
---|
280 | VALUES
|
---|
281 | (16578, 'Wednesday', 1, '2023-10-10', '2024-01-07'),
|
---|
282 | (26543, 'Tuesday', 1, '2023-11-02', '2024-01-09'),
|
---|
283 | (98352, 'Thursday', 3, '2023-11-15', '2024-02-15');
|
---|
284 |
|
---|
285 | -- Внесување test data во табелата 'non_repeating_event'
|
---|
286 | INSERT INTO non_repeating_event (calendar_event_id, event_date)
|
---|
287 | VALUES
|
---|
288 | (88888, '2024-02-07'),
|
---|
289 | (66666, '2024-01-23'); |
---|