WikiStart/RelationalDesign: polnenje.sql

-- се поставува патека односно шема во која ќе се извршуваат командите

set search_path = project;

-- бришење на старите податоци од табелите

DELETE FROM student;
DELETE FROM task;
DELETE FROM feedback;
DELETE FROM school_year ;
DELETE FROM term ;
DELETE FROM subject_at_term ;
DELETE FROM calendar_event ;
DELETE FROM repeating_event ;
DELETE FROM non_repeating_event ;
DELETE FROM e_mail ;
DELETE FROM reminder ;
DELETE FROM place ;
DELETE FROM dashboard ;
DELETE FROM notes ;
DELETE FROM professor ;
DELETE FROM subject_book ;
DELETE FROM author ;
DELETE FROM chapter ;

DELETE FROM has_taken_a_subject;
DELETE FROM holds_subject;
DELETE FROM holds_event;
DELETE FROM happens_at_place;
DELETE FROM recommended_book ;
DELETE FROM recommended_chapter ;
DELETE FROM has_consultations ;
DELETE FROM year_has_subject ;
DELETE FROM dashboard_event ;
DELETE FROM subject_event ;
DELETE FROM creates_task ;
DELETE FROM student_event ;
DELETE FROM student_task ;
DELETE FROM gives_feedback ;
DELETE FROM dashboard_task ;
DELETE FROM book_author ;

-- Внесување test data во табелата 'student'
INSERT INTO student (student_id, username, s_first_name, s_last_name, s_password)
VALUES
  (91324, 'SaraS', 'Sara', 'Stojilkova', 'password123'),
  (13467, 'MagiStoj', 'Margarita', 'Stojkovska', 'securepass'),
  (98727, 'IlinkaS', 'Ilinka', 'Stavrova', 'secret789');

-- Внесување test data во табелата 'subject_book'
INSERT INTO subject_book (book_id, book_title, b_year_of_publishment, book_edition)
VALUES
  (245, 'Mathematics Textbook', 2020, '2nd Edition'),
  (10, 'History of Software', 2019, '1st Edition'),
  (89, 'Algebra for Beginners', 2010, '1st Edition');

-- Внесување test data во табелата 'chapter'
INSERT INTO chapter (book_id, chapter_number, chapter_title)
VALUES
  (245, 1, 'Introduction'),
  (10, 2, 'Fundamentals'),
  (89, 1, 'Introduction to Algebra');
 

-- Внесување test data во табелата 'author'
INSERT INTO author (author_id, author_name, a_short_biography)
VALUES
  (1, 'John Matheson', 'Renowned mathematician and author.'),
  (2, 'Alice Historian', 'Specialized in ancient history.'),
  (3, 'Vangel Ajanovski', 'Profesor na FINKI.');

-- Внесување test data во табелата 'feedback'
INSERT INTO feedback (fb_id, fb_date, fb_title, fb_description, fb_rating)
VALUES
  (1, '2024-01-23', 'Great', 'Odlicna aplikacija kolegi. Mn lesna za koristenje', 5),
  (2, '2024-01-24', 'OK', 'Bi sakal da ima pomoderen dizajn', 3),
  (45, '2022-05-21', 'Super', 'Sekojdnevno ja koristam aplikacijata, super e!', 5);

-- Внесување test data во табелата 'e_mail'
INSERT INTO e_mail (student_id, e_mail_id, e_mail_address)
VALUES
  (91324, 28, 'sara.stojilkova@gmail.com'),
  (13467, 45, 'margarita.stojkovska@hotmail.com'),
  (98727, 789, 'ilinka.stavrova@students.com');

-- Внесување test data во табелата 'school_year' 
INSERT INTO school_year (student_id, starting_year, finishing_year)
VALUES
  (91324, 2023, 2024),
  (13467, 2023, 2024),
  (98727, 2023, 2024);

-- Внесување test data во табелата 'term'
INSERT INTO term (student_id, starting_year, finishing_year, term_type, term_start_date, term_end_date)
VALUES
  (91324, 2023, 2024, 'Zimski', '2023-09-01', '2024-01-20'),
  (13467, 2023, 2024, 'Zimski', '2022-01-15', '2023-01-20'),
  (98727, 2023, 2024, 'Leten', '2024-02-01', '2024-06-20');

 -- Внесување test data во табелата 'calendar_event'
INSERT INTO calendar_event (calendar_event_id, event_name , event_type , event_description, event_start_time, event_end_time)
VALUES
  (16578, 'Predavanje Bazi', 'Predavanje', 'Introduction to SQL', '16:33', '18:00'),
  (26543, 'Predavanje Kalkulus', 'Predavanje', 'Funkcii', '13:00', '14:00'),
  (98352, 'Vezbi Bazi', 'Vezbi', 'Izrabotka na ER dijagram', '18:00', '18:45');
 
-- Внесување test data во табелата 'reminder'
INSERT INTO reminder (calendar_event_id, r_id, minutes_before_event, r_flag)
VALUES
  (16578, 134, 30, true),
  (26543, 769, 15, true),
  (98352, 249, 15, true);

-- Внесување test data во табелата 'place'
INSERT INTO place (place_id, building_name, room_name)
VALUES
  (1, 'FINKI Podrum', 'Lab 301'),
  (9, 'Dekanat', 'Soba 10'),
  (12, 'FEIT', 'Lab 102');

-- Внесување test data во табелата 'dashboard'
INSERT INTO dashboard (student_id, dashboard_id, dashboard_date)
VALUES
  (91324, 14, '2023-01-22'),
  (13467, 26, '2023-07-19'),
  (13467, 27, '2023-08-20');

-- Внесување test data во табелата 'notes'
INSERT INTO notes (student_id, dashboard_id, note_id, notes_date, note_text)
VALUES
  (91324, 14, 13, '2023-01-22', 'Ne zaboravaj da zemes potvrda od studentski prasanja.'),
  (13467, 26, 2, '2023-07-19', 'Zapisi semestar.'),
  (13467, 27, 27, '2023-08-20', 'Prijavi se na anketa za ispit Bazi.');

-- Внесување test data во табелата 'professor'
INSERT INTO professor (professor_id, p_first_name, p_last_name, p_role, p_email)
VALUES
  (1, 'Vangel', 'Ajanovski', 'Profesor', 'vangel.ajanovski@profesor.com'),
  (24, 'Sara', 'Milevska', 'Asistent', 'sara.milevska@efinki.com'),
  (34, 'Smile', 'Smilevski', 'Profesor', 'smiles@finki.com');

 -- Внесување test data во табелата 'subject_at_term'
INSERT INTO subject_at_term (student_id, starting_year, finishing_year, term_type, term_subject_id, subject_name)
VALUES
  (91324, 2023, 2024, 'Zimski', 56, 'Napredno programiranje'),
  (13467, 2023, 2024, 'Zimski', 23, 'Bazi na podatoci'),
  (98727, 2023, 2024, 'Leten', 67, 'Internet Tehnologii');
 
-- Внесување test data во табелата 'has_taken_a_subject'
INSERT INTO has_taken_a_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, student_id_ref)
VALUES
  (91324, 2023, 2024, 'Zimski', 56, 91324),
  (13467, 2023, 2024, 'Zimski', 23, 13467),
  (98727, 2023, 2024, 'Leten', 67, 98727);

-- Внесување test data во табелата 'holds_subject'
INSERT INTO holds_subject (student_id, starting_year, finishing_year, term_type, term_subject_id, professor_id)
VALUES
  (91324, 2023, 2024, 'Zimski', 56, 34),
  (13467, 2023, 2024, 'Zimski', 23, 1),
  (98727, 2023, 2024, 'Leten', 67, 24);
  
-- Внесување test data во табелата 'task'
INSERT INTO task (task_id, task_name, task_description, task_priority)
VALUES
  (11111, 'dbLearn zadaci', 'Resavanje na serija od dbLearn', 2),
  (22222, 'Proekt', 'Dorabotka na faza 2 od proekt po bazi na podatoci', 1),
  (33333, 'Podgotovka za vtor kolokvium','Resavanje na auditoriski vezbi 6-11', 1);
 
 -- Внесување test data во табелата 'creates_task'
INSERT INTO creates_task (task_id, student_id, starting_year, finishing_year, term_type, term_subject_id)
VALUES
  (11111, 13467, 2023, 2024, 'Zimski', 23),
  (22222, 13467, 2023, 2024, 'Zimski', 23),
  (33333, 98727, 2023, 2024, 'Leten', 67);
  
 -- Внесување test data во табелата 'task - 1'
INSERT INTO task (task_id, task_name, task_description, task_priority)
VALUES
  (44444, 'Generalka', '', 3);
  
 -- Внесување test data во табелата 'student_task'
INSERT INTO student_task (task_id, student_id)
VALUES
  (44444, 98727);
  
 -- Внесување test data во табелата 'book_author'
INSERT INTO book_author (author_id, book_id)
VALUES
  (1, 245),
  (2, 10),
  (3, 89);
  
 -- Внесување test data во табелата 'happens_at_place'
INSERT INTO happens_at_place (calendar_event_id, place_id)
VALUES
  (16578, 1),
  (26543, 9),
  (98352, 12);
 
 -- Внесување test data во табелата 'recommended_chapter'
INSERT INTO recommended_chapter (calendar_event_id, book_id, chapter_number)
VALUES
  (16578, 245, 1),
  (26543, 245, 1),
  (26543, 89, 1),
  (98352, 89, 1);  
  
 -- Внесување test data во табелата 'calendar_event - 2'
INSERT INTO calendar_event (calendar_event_id, event_name , event_type , event_description, event_start_time, event_end_time)
VALUES
  (66666, 'Konsultacii bazi', 'Konsultacii', 'Odbrana na faza 2', '16:00', '18:00');
  
 -- Внесување test data во табелата 'has_consultations'
INSERT INTO has_consultations  (calendar_event_id, professor_id)
VALUES
  (66666, 1);
  
 -- Внесување test data во табелата 'subject_event'
INSERT INTO subject_event (calendar_event_id, student_id, starting_year, finishing_year, term_type, term_subject_id)
VALUES
  (16578, 13467, 2023, 2024, 'Zimski', 23),
  (26543, 91324, 2023, 2024, 'Zimski', 56),
  (98352, 13467, 2023, 2024, 'Zimski', 23);

-- Внесување test data во табелта 'holds_event' 
INSERT INTO holds_event (calendar_event_id, professor_id)
VALUES
  (16578, 1),
  (26543, 24),
  (98352, 34);
  
-- Внесување test data во табелата 'dashboard_task' 
INSERT INTO dashboard_task (task_id, student_id, dashboard_id)
VALUES
  (11111, 91324, 14),
  (22222, 13467, 26),
  (33333, 13467, 27);
  
-- Внесување test data во табелата 'gives_feedback'
INSERT INTO gives_feedback (fb_id, student_id)
VALUES
  (1, 13467),
  (2, 91324),
  (45, 91324);
  
-- Внесување test data во табелата 'recommended_book'
INSERT INTO recommended_book (student_id, starting_year, finishing_year, term_type, term_subject_id, book_id)
VALUES
  (91324, 2023, 2024, 'Zimski', 56, 245),
  (91324, 2023, 2024, 'Zimski', 56, 10),
  (13467, 2023, 2024, 'Zimski', 23, 89);

-- Внесување test data во табелата 'dashboard_event'
INSERT INTO dashboard_event  (calendar_event_id, student_id, dashboard_id)
VALUES
  (66666, 91324, 14),
  (16578, 13467, 26),
  (98352, 13467, 26);
  
-- Внесување test data во табелата 'year_has_subject'
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)
VALUES
  (91324, 2023, 2024, 'Zimski', 56, 91324, 2023, 2024),
  (13467, 2023, 2024, 'Zimski', 23, 13467, 2023, 2024),
  (98727, 2023, 2024, 'Leten', 67, 98727, 2023, 2024);
 
-- Внесување test data во табелата 'calendar_event - 3'
INSERT INTO calendar_event (calendar_event_id, event_name , event_type, event_start_time, event_end_time)
VALUES
  (88888, 'Rabotilnica za pretpriemnistvo', 'Rabotilnica', '14:00', '16:00');
  
-- Внесување test data во табелата 'student_event'
INSERT INTO student_event (calendar_event_id, student_id)
VALUES
  (88888, 91324);
  
-- Внесување test data во табелата 'repeating_event'
INSERT INTO repeating_event (calendar_event_id, event_week_day, repeats_every_n_weeks, starting_date, ending_date)
VALUES
  (16578, 'Wednesday', 1, '2023-10-10', '2024-01-07'),
  (26543, 'Tuesday', 1, '2023-11-02', '2024-01-09'),
  (98352, 'Thursday', 3, '2023-11-15', '2024-02-15');
  
-- Внесување test data во табелата 'non_repeating_event'
INSERT INTO non_repeating_event (calendar_event_id, event_date)
VALUES
  (88888, '2024-02-07'), 
  (66666, '2024-01-23');