Tworzę bazę danych która ma umożliwiać zapisy na zajęcia, utknąłem przy sprawdzaniu daty i czasu podczas zapisywania studenta na dany kurs czy nie nakładają mu się jakiekolwiek course_section z danego course
Cała baza wygląda tak:
CREATE TABLE "semester" (
"semester_id" serial primary key,
"semester_describtion" varchar);
CREATE TABLE "course" (
"c_id" serial not null,
"c_name" varchar NOT NULL,
"c_describtion" varchar,
"semester_id" int,
"startDate" DATE,
"endDate" DATE,
PRIMARY KEY (c_id, semester_id),
FOREIGN KEY (semester_id) references semester(semester_id));
CREATE TABLE "staff" (
"staff_id" serial primary key,
"staff_lastname" varchar(40) NOT NULL,
"staff_firstname" varchar(40) NOT NULL,
"staff_email" varchar(40));
CREATE TABLE "student" (
"s_id" serial PRIMARY KEY,
"s_lastname" varchar(40) NOT NULL,
"s_firstname" varchar(40) NOT NULL,
"semester_id" int default '1',
foreign key (semester_id) references semester(semester_id));
CREATE TABLE "location" (
"location_id" serial,
"room_nr" int,
"building_id" varchar(40) DEFAULT 'D5',
"max_students" int default '10',
primary key (location_id, max_students));
CREATE TABLE "course_section" (
"c_section_id" serial,
"c_id" int NOT NULL,
"semester_id" int NOT NULL,
"staff_id" int,
"location_id" int not null,
"c_section_date" DATE,
"c_section_time" TIME default '08:00',
"c_section_during" TIME default '01:30',
"c_section_type" TEXT not null,
"max_students" int default '10',
"students" int default '0',
check(c_section_type = 'L' OR c_section_type = 'W' OR c_section_type = 'C' OR c_section_type = 'I'),
primary key (c_id, staff_id, location_id,c_section_date,c_section_time),
foreign key (c_id, semester_id) references course(c_id, semester_id),
foreign key (staff_id) references staff(staff_id),
foreign key (location_id, max_students) references location(location_id, max_students)
);
CREATE TABLE "enrollment" (
"s_id" int not null,
"mark" int,
"c_id" int not null,
"semester_id" int not null,
primary key (s_id,c_id,semester_id),
foreign key (s_id) references student(s_id),
foreign key (c_id, semester_id) references course(c_id, semester_id));
A tutaj jest funkcja sprawdzająca datę i czas:
create function check_date() returns trigger as '
declare
date1 date;
time1 time;
begin
Select c_section_date, c_section_time into date1, time1 from enrollment e
inner join course c using (c_id)
inner join course_section cs using (c_id);
IF EXISTS (select e.s_id , cs.c_section_date, cs.c_section_time from enrollment e
inner join course c using (c_id)
inner join course_section cs using (c_id)
where new.s_id=e.s_id AND date1=c_section_date and time1 = c_section_time) THEN
RAISE NOTICE ''Zajecia nakladaja sie'';
Return null;
ELSE
return new;
END IF;
END;
' language 'plpgsql';
create trigger t_check_date before insert on enrollment for each row execute procedure check_date();
select e.s_id , cs.c_section_id, cs.c_id, cs.c_section_date, cs.c_section_time from enrollment e
inner join course c using (c_id)
inner join course_section cs using (c_id);
insert into semester values ('1','opis semestru');
insert into student (s_lastname,s_firstname, semester_id) values ('Jacek','Sznycel1','1');
insert into staff (staff_lastname,staff_firstname,staff_email) values ('Ledy w ','Halogenach','ogarniam@tablice.pl');
insert into location (location_id,max_students,room_nr) values ('1','1','101');
insert into location (location_id,max_students,room_nr) values ('2','2','102');
insert into location (location_id,max_students,room_nr) values ('3','3','103');
insert into course (c_name,c_describtion,semester_id) values ('SQL','bardzo warzne','1');
insert into course (c_name,c_describtion,semester_id) values ('SQL2','bardzo warzne','1');
insert into course (c_name,c_describtion,semester_id) values ('SQL3','bardzo warzne','1');
insert into course_section (c_id,semester_id,staff_id,location_id,c_section_type,max_students,c_section_date) values ('1','1','1','3','L','3','2017-01-01');
insert into course_section (c_id,semester_id,staff_id,location_id,c_section_type,max_students,c_section_date) values ('2','1','1','3','L','3','2017-01-01');
insert into course_section (c_id,semester_id,staff_id,location_id,c_section_type,max_students,c_section_date,c_section_time) values ('3','1','1','3','L','3','2017-01-01','09:35');
insert into enrollment (s_id,c_id,semester_id) values ('1','1','1');
insert into enrollment (s_id,c_id,semester_id) values ('1','2','1');
--------------tu powinien być raise notice
insert into enrollment (s_id,c_id,semester_id) values ('1','3','1');
Gdy zapisuje studenta na zajęcia (enrollment) po raz pierwszy wszystko jest ok, potem za każdym razem wyskakuje RAISE NOTICE niezależnie od tego czy c_section_time się różni czy nie.
Dodam, że jest to mój pierwszy projekt z baz danych więc ewentualne uwagi odnośnie pisania kodu mile widziane :))