WikiStart/RelationalDesign: polnenje.sql

File polnenje.sql, 11.3 KB (added by 121232, 10 months ago)
Line 
1-- се поставува патека односно шема во која ќе се извршуваат командите
2
3set search_path = project;
4
5-- бришење на старите податоци од табелите
6
7DELETE FROM student;
8DELETE FROM task;
9DELETE FROM feedback;
10DELETE FROM school_year ;
11DELETE FROM term ;
12DELETE FROM subject_at_term ;
13DELETE FROM calendar_event ;
14DELETE FROM repeating_event ;
15DELETE FROM non_repeating_event ;
16DELETE FROM e_mail ;
17DELETE FROM reminder ;
18DELETE FROM place ;
19DELETE FROM dashboard ;
20DELETE FROM notes ;
21DELETE FROM professor ;
22DELETE FROM subject_book ;
23DELETE FROM author ;
24DELETE FROM chapter ;
25
26DELETE FROM has_taken_a_subject;
27DELETE FROM holds_subject;
28DELETE FROM holds_event;
29DELETE FROM happens_at_place;
30DELETE FROM recommended_book ;
31DELETE FROM recommended_chapter ;
32DELETE FROM has_consultations ;
33DELETE FROM year_has_subject ;
34DELETE FROM dashboard_event ;
35DELETE FROM subject_event ;
36DELETE FROM creates_task ;
37DELETE FROM student_event ;
38DELETE FROM student_task ;
39DELETE FROM gives_feedback ;
40DELETE FROM dashboard_task ;
41DELETE FROM book_author ;
42
43-- Внесување test data во табелата 'student'
44INSERT INTO student (student_id, username, s_first_name, s_last_name, s_password)
45VALUES
46 (91324, 'SaraS', 'Sara', 'Stojilkova', 'password123'),
47 (13467, 'MagiStoj', 'Margarita', 'Stojkovska', 'securepass'),
48 (98727, 'IlinkaS', 'Ilinka', 'Stavrova', 'secret789');
49
50-- Внесување test data во табелата 'subject_book'
51INSERT INTO subject_book (book_id, book_title, b_year_of_publishment, book_edition)
52VALUES
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'
58INSERT INTO chapter (book_id, chapter_number, chapter_title)
59VALUES
60 (245, 1, 'Introduction'),
61 (10, 2, 'Fundamentals'),
62 (89, 1, 'Introduction to Algebra');
63
64
65-- Внесување test data во табелата 'author'
66INSERT INTO author (author_id, author_name, a_short_biography)
67VALUES
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'
73INSERT INTO feedback (fb_id, fb_date, fb_title, fb_description, fb_rating)
74VALUES
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'
80INSERT INTO e_mail (student_id, e_mail_id, e_mail_address)
81VALUES
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'
87INSERT INTO school_year (student_id, starting_year, finishing_year)
88VALUES
89 (91324, 2023, 2024),
90 (13467, 2023, 2024),
91 (98727, 2023, 2024);
92
93-- Внесување test data во табелата 'term'
94INSERT INTO term (student_id, starting_year, finishing_year, term_type, term_start_date, term_end_date)
95VALUES
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'
101INSERT INTO calendar_event (calendar_event_id, event_name , event_type , event_description, event_start_time, event_end_time)
102VALUES
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'
108INSERT INTO reminder (calendar_event_id, r_id, minutes_before_event, r_flag)
109VALUES
110 (16578, 134, 30, true),
111 (26543, 769, 15, true),
112 (98352, 249, 15, true);
113
114-- Внесување test data во табелата 'place'
115INSERT INTO place (place_id, building_name, room_name)
116VALUES
117 (1, 'FINKI Podrum', 'Lab 301'),
118 (9, 'Dekanat', 'Soba 10'),
119 (12, 'FEIT', 'Lab 102');
120
121-- Внесување test data во табелата 'dashboard'
122INSERT INTO dashboard (student_id, dashboard_id, dashboard_date)
123VALUES
124 (91324, 14, '2023-01-22'),
125 (13467, 26, '2023-07-19'),
126 (13467, 27, '2023-08-20');
127
128-- Внесување test data во табелата 'notes'
129INSERT INTO notes (student_id, dashboard_id, note_id, notes_date, note_text)
130VALUES
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'
136INSERT INTO professor (professor_id, p_first_name, p_last_name, p_role, p_email)
137VALUES
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'
143INSERT INTO subject_at_term (student_id, starting_year, finishing_year, term_type, term_subject_id, subject_name)
144VALUES
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'
150INSERT INTO has_taken_a_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, student_id_ref)
151VALUES
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'
157INSERT INTO holds_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, professor_id)
158VALUES
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'
164INSERT INTO task (task_id, task_name, task_description, task_priority)
165VALUES
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'
171INSERT INTO creates_task (task_id, student_id, starting_year, finishing_year, term_type, term_subject_id)
172VALUES
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'
178INSERT INTO task (task_id, task_name, task_description, task_priority)
179VALUES
180 (44444, 'Generalka', '', 3);
181
182 -- Внесување test data во табелата 'student_task'
183INSERT INTO student_task (task_id, student_id)
184VALUES
185 (44444, 98727);
186
187 -- Внесување test data во табелата 'book_author'
188INSERT INTO book_author (author_id, book_id)
189VALUES
190 (1, 245),
191 (2, 10),
192 (3, 89);
193
194 -- Внесување test data во табелата 'happens_at_place'
195INSERT INTO happens_at_place (calendar_event_id, place_id)
196VALUES
197 (16578, 1),
198 (26543, 9),
199 (98352, 12);
200
201 -- Внесување test data во табелата 'recommended_chapter'
202INSERT INTO recommended_chapter (calendar_event_id, book_id, chapter_number)
203VALUES
204 (16578, 245, 1),
205 (26543, 245, 1),
206 (26543, 89, 1),
207 (98352, 89, 1);
208
209 -- Внесување test data во табелата 'calendar_event - 2'
210INSERT INTO calendar_event (calendar_event_id, event_name , event_type , event_description, event_start_time, event_end_time)
211VALUES
212 (66666, 'Konsultacii bazi', 'Konsultacii', 'Odbrana na faza 2', '16:00', '18:00');
213
214 -- Внесување test data во табелата 'has_consultations'
215INSERT INTO has_consultations (calendar_event_id, professor_id)
216VALUES
217 (66666, 1);
218
219 -- Внесување test data во табелата 'subject_event'
220INSERT INTO subject_event (calendar_event_id, student_id, starting_year, finishing_year, term_type, term_subject_id)
221VALUES
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'
227INSERT INTO holds_event (calendar_event_id, professor_id)
228VALUES
229 (16578, 1),
230 (26543, 24),
231 (98352, 34);
232
233-- Внесување test data во табелата 'dashboard_task'
234INSERT INTO dashboard_task (task_id, student_id, dashboard_id)
235VALUES
236 (11111, 91324, 14),
237 (22222, 13467, 26),
238 (33333, 13467, 27);
239
240-- Внесување test data во табелата 'gives_feedback'
241INSERT INTO gives_feedback (fb_id, student_id)
242VALUES
243 (1, 13467),
244 (2, 91324),
245 (45, 91324);
246
247-- Внесување test data во табелата 'recommended_book'
248INSERT INTO recommended_book (student_id, starting_year, finishing_year, term_type, term_subject_id, book_id)
249VALUES
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'
255INSERT INTO dashboard_event (calendar_event_id, student_id, dashboard_id)
256VALUES
257 (66666, 91324, 14),
258 (16578, 13467, 26),
259 (98352, 13467, 26);
260
261-- Внесување test data во табелата 'year_has_subject'
262INSERT INTO year_has_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, student_id_ref, starting_year_ref, finishing_year_ref)
263VALUES
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'
269INSERT INTO calendar_event (calendar_event_id, event_name , event_type, event_start_time, event_end_time)
270VALUES
271 (88888, 'Rabotilnica za pretpriemnistvo', 'Rabotilnica', '14:00', '16:00');
272
273-- Внесување test data во табелата 'student_event'
274INSERT INTO student_event (calendar_event_id, student_id)
275VALUES
276 (88888, 91324);
277
278-- Внесување test data во табелата 'repeating_event'
279INSERT INTO repeating_event (calendar_event_id, event_week_day, repeats_every_n_weeks, starting_date, ending_date)
280VALUES
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'
286INSERT INTO non_repeating_event (calendar_event_id, event_date)
287VALUES
288 (88888, '2024-02-07'),
289 (66666, '2024-01-23');