timetable-generator/create_tables at master · asdoc/timetable-generator · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE TABLE Year (
  year_id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Department (
  department_id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Semester (
  semester_id INTEGER PRIMARY KEY,
  year_id INTEGER,
  name INTEGER,
  department_id INTEGER,
  FOREIGN KEY(year_id) REFERENCES Year(year_id),
  FOREIGN KEY(department_id) REFERENCES Department(department_id)
);

CREATE TABLE Teacher (
  teacher_id INTEGER PRIMARY KEY,
  department_id INTEGER,
  name TEXT,
  FOREIGN KEY(department_id) REFERENCES Department(department_id)
);

CREATE TABLE Subject (
  subject_id INTEGER PRIMARY KEY,
  semester_id INTEGER,
  name TEXT,
  is_practical INTEGER,
  FOREIGN KEY(semester_id) REFERENCES Semester(semester_id)
);

CREATE TABLE Class (
  class_id INTEGER PRIMARY KEY,
  year_id INTEGER,
  department_id INTEGER,
  name TEXT,
  FOREIGN KEY(year_id) REFERENCES Year(year_id),
  FOREIGN KEY(department_id) REFERENCES Department(department_id)
);

CREATE TABLE Assignment (
  assignment_id INTEGER PRIMARY KEY,
  teacher_id INTEGER,
  subject_id INTEGER,
  load INTEGER,
  FOREIGN KEY(teacher_id) REFERENCES Teacher(teacher_id),
  FOREIGN KEY(subject_id) REFERENCES Subject(subject_id)
);