How to get a free lab session using this database-schema?

Hey all, thanks in advance for reading!

The problem is to try and find a time in which an instructor is free, a lab is free and the students enrolled in a class are free. There is only one instructor, around 6 lab rooms, and an average of 20 students.

Lab rooms, instructors and students have already times at which they are busy/occupied. Time is split into 1.5 hour blocks starting from 8-930, 930-1100 all the ways to 330-500. There are 5 days to consider, Monday - Friday. If there is not a single block that can fit all students,instructor and lab then the class can be split into sessions.

Here is what i was thinking of as a database and inputs/outputs:

Database:

Table :  Lab
Fields : l_id, l_name, l_capacity, BM893, BM9311, BM11123, BM1232, BM233, BM335, BT893, BT9311, BT11123, BT1232, BT233, BT335, BW893, BW9311, BW11123, BW1232, BW233, BW335, BTH893, BTH9311, BTH11123, BTH1232, BTH233, BTH335, BF893, BF9311, BF11123, BF1232, BF233, BF335
(each field representing a block to time for a day… e.g.  BM893 is Block Monday 8 – 930, all fields are integers where 0 is free and 1 is occupied/busy)

Table: Student
Fields :s_id, s_name, BM893, BM9311, BM11123, BM1232, BM233, BM335, BT893, BT9311, BT11123, BT1232, BT233, BT335, BW893, BW9311, BW11123, BW1232, BW233, BW335, BTH893, BTH9311, BTH11123, BTH1232, BTH233, BTH335, BF893, BF9311, BF11123, BF开发者_运维知识库1232, BF233, BF335
(each field representing a block to time for a day… e.g.  BM893 is Block Monday 8 – 930, all fields are integers where 0 is free and 1 is occupied/busy)

Table: Instructor
Fields : i_id, i_name, BM893, BM9311, BM11123, BM1232, BM233, BM335, BT893, BT9311, BT11123, BT1232, BT233, BT335, BW893, BW9311, BW11123, BW1232, BW233, BW335, BTH893, BTH9311, BTH11123, BTH1232, BTH233, BTH335, BF893, BF9311, BF11123, BF1232, BF233, BF335
(each field representing a block to time for a day… e.g.  BM893 is Block Monday 8 – 930, all fields are integers where 0 is free and 1 is occupied/busy)

Input:

1 instructor (i_id)
All labs
N students (s_id, s_id…etc)

Output:

Time block for session (if more than one section than also s_id of every student in each section)

Could someone please help me with the algorithm?

Thank You very much

---

I would have one table for day of week with 5 rows, M-F

another table with time of day and start times, however many you have (0800, 0930,... 1530)

another table (or query/view) with the cartesian product of days and times

another table with lab details (building, room number, whatever)

another table with student details (student id, first name, last name)

another table with instructor details (instructor id, name, department)

then you have your busy/occupied/unavailable table, with itemType (lab or student or instructor,) day busy (m-f), and time busy (0800-1530). you don't need to store free times because they can be derived from the cartesian product table with all day and time sets.

then you're all set- you have all the data stored you need and can see if there are any rows in the set of all days and times which don't have corresponding rows in the busy table.