kMnH0UG2

1create table Lector
2	(
3		id int not null,
4		name char(40) not null,
5		primary key (id)
6		);
7
8
9create table Subject
10	(
11		id int not null,
12		name char(40) not null,
13		control char(40),
14		primary key (id)
15		);
16
17
18create table Day 
19	(
20		id int not null,
21		name char(20) not null
22		);
23
24create table Schedule
25	(
26		lector_id int not null,
27		subject_id int not null,
28		day_id int not null,
29		class_number int,
30		foreign key (lector_id) references Lector,
31		foreign key (subject_id) references Subject,
32		foreign key (day_id) references Day
33		);
34
35// Обмежимо контроль двома видами: екзамен та залік
36
37alter table Subject with check
38add constraint control
39check (control = 'exam' or control = 'credit')
40
41// Припустимо що в день може бути тільки з 0 по 8 пару
42
43alter table Schedule with check
44add constraint class_number
45check (class_number >= 0 and class_number <= 8)
46
47// Додамо обчислювальне поле, яке обчислює чи приймає лектор екзамен. Можливі значення 'yes' або 'no'
48
49create function isLectorTakeExam(@lector_id int)
50	returns char(3)
51	as
52	begin
53		declare @exams_count int
54		set @exams_count = select(count(distinct *) from Subject 
55							join Schedule on Schedule.subject_id = Subject.id
56							where Schedule.lector_id = @lector_id
57							and Subject.control = 'exam')
58		if @exams_count > 0
59			set @result = 'yes'
60		else
61			set @result = 'no'
62		return @result
63	end
64
65alter table Lector
66add is_take_exam  as isLectorTakeExam(id)
67
68
69// Порахуємо таблицю днів за рівнем зручності для викладача
70
71
72create function theMostConvenientDays(@lector_id)
73	returns @t table(day_name int(20))
74	as
75	begin
76		declare @curr_day int
77		declare @temp_table table (day_id int, classes_count int, has_windows int)
78		declare Days cursor for (select id from Day)
79		open Days
80			fetch next from Days into @curr_day
81			while @@fetch_status = 0
82			begin
83				declare @classes_count int
84				set @classes_count = (select count (*) from Schedule
85										where Schedule.lector_id = @lector_id 
86										and Schedule.day_id = @curr_day)
87				declare @has_windows int
88				if exists (
89						select * 
90						from Schedule
91							where Schedule.lector_id = @lector_id
92							and Schedule.day_id = @curr_day
93							and ((select max(class_number) from Schedule
94									where Schedule.lector_id = @lector_id
95									and Schedule.day_id = @curr_day)
96								-
97								((select min(class_number) from Schedule
98									where Schedule.lector_id = @lector_id
99									and Schedule.day_id = @curr_day)
100								+ 1)
101								>
102								@classes_count
103					)
104					set @has_windows = 1
105				else
106					set @has_windows = 0
107
108				insert @temp_table values (@curr_day, @classes_count, @has_windows)
109			end
110		close Days
111
112		insert @t (select Day.name from Days
113					join @temp_table on Day.day_id = @temp_table.day_id
114					order by @temp_table.classes_count asc, @temp_table.has_windows asc)
115		return
116	end
117
118	select theMostConvenientDays(0)