mirror of
https://github.com/matt-fidd/stratos.git
synced 2026-01-01 13:39:26 +00:00
108 lines
4.6 KiB
SQL
108 lines
4.6 KiB
SQL
CREATE TABLE "account" (
|
|
"accountId" varchar(36) NOT NULL,
|
|
"email" varchar(255) NOT NULL,
|
|
"firstName" varchar(50) NOT NULL,
|
|
"otherNames" varchar(255) NOT NULL,
|
|
"lastName" varchar(50) NOT NULL,
|
|
"password" varchar(60) NOT NULL,
|
|
PRIMARY KEY ("accountId"),
|
|
UNIQUE KEY "Unq_account_email" ("email")
|
|
);
|
|
CREATE TABLE "accountClassLink" (
|
|
"accountId" varchar(36) NOT NULL,
|
|
"classId" varchar(36) NOT NULL,
|
|
PRIMARY KEY ("accountId","classId"),
|
|
KEY "accountClassLink_fk1" ("classId"),
|
|
CONSTRAINT "accountClassLink_fk0" FOREIGN KEY ("accountId") REFERENCES "account" ("accountId") ON DELETE CASCADE ON UPDATE NO ACTION,
|
|
CONSTRAINT "accountClassLink_fk1" FOREIGN KEY ("classId") REFERENCES "class" ("classId") ON DELETE CASCADE ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "class" (
|
|
"classId" varchar(36) NOT NULL,
|
|
"name" varchar(50) NOT NULL,
|
|
"subjectId" int(11) NOT NULL,
|
|
PRIMARY KEY ("classId"),
|
|
KEY "class_fk0" ("subjectId"),
|
|
CONSTRAINT "class_fk0" FOREIGN KEY ("subjectId") REFERENCES "subject" ("subjectId") ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "parent" (
|
|
"parentId" varchar(36) NOT NULL,
|
|
"email" varchar(255) NOT NULL,
|
|
"firstName" varchar(50) NOT NULL,
|
|
"otherNames" varchar(50) NOT NULL,
|
|
"lastName" varchar(50) NOT NULL,
|
|
"password" varchar(60) NOT NULL,
|
|
PRIMARY KEY ("parentId"),
|
|
UNIQUE KEY "Unq_parent" ("email")
|
|
);
|
|
CREATE TABLE "passwordReset" (
|
|
"accountId" varchar(36) NOT NULL,
|
|
"token" varchar(60) NOT NULL,
|
|
"nonce" varchar(16) NOT NULL,
|
|
"expires" datetime NOT NULL,
|
|
PRIMARY KEY ("accountId"),
|
|
CONSTRAINT "fk_passwordreset_account" FOREIGN KEY ("accountId") REFERENCES "account" ("accountId") ON DELETE CASCADE ON UPDATE NO ACTION,
|
|
CONSTRAINT "fk_passwordreset_parent" FOREIGN KEY ("accountId") REFERENCES "parent" ("parentId") ON DELETE CASCADE ON UPDATE NO ACTION,
|
|
CONSTRAINT "fk_passwordreset_student" FOREIGN KEY ("accountId") REFERENCES "student" ("studentId") ON DELETE CASCADE ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "student" (
|
|
"studentId" varchar(36) NOT NULL,
|
|
"email" varchar(255) NOT NULL,
|
|
"firstName" varchar(50) NOT NULL,
|
|
"otherNames" varchar(50) NOT NULL,
|
|
"lastName" varchar(50) NOT NULL,
|
|
"password" varchar(60) NOT NULL,
|
|
PRIMARY KEY ("studentId"),
|
|
UNIQUE KEY "Unq_student" ("email")
|
|
);
|
|
CREATE TABLE "studentClassLink" (
|
|
"studentId" varchar(36) NOT NULL,
|
|
"classId" varchar(36) NOT NULL,
|
|
PRIMARY KEY ("studentId","classId"),
|
|
CONSTRAINT "fk_studentclasslink_student" FOREIGN KEY ("studentId") REFERENCES "student" ("studentId") ON DELETE CASCADE ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "studentParentLink" (
|
|
"studentid" varchar(36) NOT NULL,
|
|
"parentId" varchar(36) NOT NULL,
|
|
PRIMARY KEY ("studentid","parentId"),
|
|
KEY "studentParentLink_fk1" ("parentId"),
|
|
CONSTRAINT "studentParentLink_fk0" FOREIGN KEY ("studentid") REFERENCES "student" ("studentId") ON DELETE CASCADE ON UPDATE NO ACTION,
|
|
CONSTRAINT "studentParentLink_fk1" FOREIGN KEY ("parentId") REFERENCES "parent" ("parentId") ON DELETE CASCADE ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "subject" (
|
|
"subjectId" int(11) NOT NULL AUTO_INCREMENT,
|
|
"name" varchar(100) NOT NULL,
|
|
PRIMARY KEY ("subjectId")
|
|
);
|
|
CREATE TABLE "test" (
|
|
"testId" varchar(36) NOT NULL,
|
|
"testTemplateId" varchar(36) NOT NULL,
|
|
"classId" varchar(36) NOT NULL,
|
|
"testDate" date NOT NULL,
|
|
PRIMARY KEY ("testId"),
|
|
UNIQUE KEY "Unq_test" ("testTemplateId","classId","testDate"),
|
|
KEY "fk_test_account" ("classId"),
|
|
CONSTRAINT "fk_test_account" FOREIGN KEY ("classId") REFERENCES "class" ("classId") ON UPDATE NO ACTION,
|
|
CONSTRAINT "fk_test_testtemplate" FOREIGN KEY ("testTemplateId") REFERENCES "testTemplate" ("testTemplateId") ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "testResult" (
|
|
"studentId" varchar(36) NOT NULL,
|
|
"testId" varchar(36) NOT NULL,
|
|
"accountId" varchar(36) NOT NULL,
|
|
"mark" int(11) NOT NULL,
|
|
PRIMARY KEY ("studentId","testId"),
|
|
KEY "fk_testresult_test" ("testId"),
|
|
KEY "fk_testresult_account" ("accountId"),
|
|
CONSTRAINT "fk_testresult_account" FOREIGN KEY ("accountId") REFERENCES "account" ("accountId") ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT "fk_testresult_test" FOREIGN KEY ("testId") REFERENCES "test" ("testId") ON DELETE CASCADE ON UPDATE NO ACTION,
|
|
CONSTRAINT "testResult_fk0" FOREIGN KEY ("studentId") REFERENCES "student" ("studentId") ON DELETE CASCADE ON UPDATE NO ACTION
|
|
);
|
|
CREATE TABLE "testTemplate" (
|
|
"testTemplateId" varchar(36) NOT NULL,
|
|
"accountId" varchar(36) NOT NULL,
|
|
"name" varchar(100) NOT NULL,
|
|
"maxMark" int(11) NOT NULL,
|
|
PRIMARY KEY ("testTemplateId"),
|
|
KEY "test_fk0" ("accountId"),
|
|
CONSTRAINT "test_fk0" FOREIGN KEY ("accountId") REFERENCES "account" ("accountId") ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|