er-learning/er-learning-database.sql at main · suleparlat/er-learning · 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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
CREATE TABLE "tblClasses" (
  "classId" SERIAL PRIMARY KEY,
  "className" varchar,
  "teacherId" int,
  "createdTime" datetime,
  "updateTime" datetime
);

CREATE TABLE "tblStudents" (
  "studentId" SERIAL PRIMARY KEY,
  "studentFirstName" varchar,
  "studentLastName" varchar,
  "number" int,
  "notes" text,
  "classId" int,
  "familyId" int,
  "createdTime" datetime,
  "updateTime" datetime
);

CREATE TABLE "tblFamilies" (
  "familyId" SERIAL PRIMARY KEY,
  "familyName" varchar,
  "telephone" varchar,
  "address" text,
  "email" text,
  "createdTime" datetime,
  "updateTime" datetime
);

CREATE TABLE "tblTeachers" (
  "teacherId" SERIAL PRIMARY KEY,
  "teacherName" varchar,
  "createdTime" datetime,
  "updateTime" datetime
);

CREATE TABLE "tblHomeworks" (
  "homeworkId" SERIAL PRIMARY KEY,
  "homeworkName" varchar,
  "lessonId" int,
  "teacherId" int,
  "createdTime" datetime,
  "updateTime" datetime
);

CREATE TABLE "tblLessons" (
  "lessonId" SERIAL PRIMARY KEY,
  "lessonName" varchar,
  "teacherId" int
);

CREATE TABLE "tblLessonContents" (
  "lessonContentId" SERIAL PRIMARY KEY,
  "lessonContentName" varchar,
  "contentType" int,
  "lessonId" int,
  "createdTime" datetime,
  "updateTime" datetime
);

CREATE TABLE "tblHomeworkGrades" (
  "homeworkGradeId" SERIAL PRIMARY KEY,
  "homeworkId" int,
  "studentId" int,
  "studentNotes" text,
  "familyNotes" text,
  "createdTime" datetime,
  "updateTime" datetime
);

ALTER TABLE "tblClasses" ADD FOREIGN KEY ("classId") REFERENCES "tblStudents" ("classId");

ALTER TABLE "tblFamilies" ADD FOREIGN KEY ("familyId") REFERENCES "tblStudents" ("familyId");

ALTER TABLE "tblTeachers" ADD FOREIGN KEY ("teacherId") REFERENCES "tblClasses" ("teacherId");

ALTER TABLE "tblTeachers" ADD FOREIGN KEY ("teacherId") REFERENCES "tblHomeworks" ("teacherId");

ALTER TABLE "tblLessons" ADD FOREIGN KEY ("lessonId") REFERENCES "tblHomeworks" ("lessonId");

ALTER TABLE "tblTeachers" ADD FOREIGN KEY ("teacherId") REFERENCES "tblLessons" ("teacherId");

ALTER TABLE "tblHomeworks" ADD FOREIGN KEY ("homeworkId") REFERENCES "tblHomeworkGrades" ("homeworkId");

ALTER TABLE "tblStudents" ADD FOREIGN KEY ("studentId") REFERENCES "tblHomeworkGrades" ("studentId");

ALTER TABLE "tblLessons" ADD FOREIGN KEY ("lessonId") REFERENCES "tblLessonContents" ("lessonId");