Приложение б

Запросы создания базы данных

CREATE DATABASE "db_TestingSystem"

WITH OWNER = "postgres"

ENCODING = 'UTF8'

TABLESPACE = "pg_default";

CREATE TABLE "public"."TExaminator" (

"id" SERIAL,

"Name" VARCHAR NOT NULL,

"Password" BYTEA NOT NULL,

"Photo" BYTEA,

CONSTRAINT "TExaminator_pkey" PRIMARY KEY("id")

) WITHOUT OIDS;

CREATE TABLE "public"."TExaminee" (

"id" SERIAL,

"Name" VARCHAR NOT NULL,

"Code" VARCHAR,

"idGroup" SERIAL,

CONSTRAINT "TExaminee_pkey" PRIMARY KEY("id"),

CONSTRAINT "TExaminee_fk" FOREIGN KEY ("idGroup")

REFERENCES "public"."TGroup"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE

) WITHOUT OIDS;

CREATE TABLE "public"."TGroup" (

"id" SERIAL,

"Name" VARCHAR NOT NULL,

CONSTRAINT "TGroup_pkey" PRIMARY KEY("id")

) WITHOUT OIDS;

CREATE TABLE "public"."TSubject" (

"id" SERIAL,

"Name" VARCHAR NOT NULL,

CONSTRAINT "TSubject_pkey" PRIMARY KEY("id")

) WITHOUT OIDS;

CREATE TABLE "public"."TTest" (

"id" SERIAL,

"Name" VARCHAR NOT NULL,

"idSubject" SERIAL,

CONSTRAINT "TTest_pkey" PRIMARY KEY("id"),

CONSTRAINT "TTest_fk" FOREIGN KEY ("idSubject")

REFERENCES "public"."TSubject"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE

) WITHOUT OIDS;

CREATE TABLE "public"."TQuestion" (

"id" SERIAL,

"Content" VARCHAR NOT NULL,

"MaxScore" INTEGER NOT NULL,

"idTest" INTEGER DEFAULT nextval('"TQuestion_idSection_seq"'::regclass) NOT NULL,

CONSTRAINT "TQuestion_pkey" PRIMARY KEY("id"),

CONSTRAINT "TQuestion_fk" FOREIGN KEY ("idTest")

REFERENCES "public"."TTest"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE

) WITHOUT OIDS;

CREATE TABLE "public"."TCurrentTest" (

"id" SERIAL,

"idTest" SERIAL,

"idExaminator" SERIAL,

"idGroup" SERIAL,

"idSubject" SERIAL,

CONSTRAINT "TCurrentTest_pkey" PRIMARY KEY("id"),

CONSTRAINT "TCurrentTest_fk" FOREIGN KEY ("idTest")

REFERENCES "public"."TTest"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE,

CONSTRAINT "TCurrentTest_fk1" FOREIGN KEY ("idExaminator")

REFERENCES "public"."TExaminator"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE,

CONSTRAINT "TCurrentTest_fk2" FOREIGN KEY ("idGroup")

REFERENCES "public"."TGroup"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE

) WITHOUT OIDS;

CREATE TABLE "public"."TPassedTest" (

"id" INTEGER DEFAULT nextval('"TAnswer_id_seq"'::regclass) NOT NULL,

"idExaminee" INTEGER DEFAULT nextval('"TAnswer_idExaminee_seq"'::regclass) NOT NULL,

"idSubject" INTEGER DEFAULT nextval('"TAnswer_idSubject_seq"'::regclass) NOT NULL,

"idTest" INTEGER DEFAULT nextval('"TAnswer_idTest_seq"'::regclass) NOT NULL,

"idExaminator" INTEGER DEFAULT nextval('"TAnswer_idExaminator_seq"'::regclass) NOT NULL,

"Date" TIMESTAMP WITHOUT TIME ZONE NOT NULL,

CONSTRAINT "TAnswer_pkey" PRIMARY KEY("id"),

CONSTRAINT "TAnswer_fk" FOREIGN KEY ("idExaminee")

REFERENCES "public"."TExaminee"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE,

CONSTRAINT "TAnswer_fk1" FOREIGN KEY ("idSubject")

REFERENCES "public"."TSubject"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE,

CONSTRAINT "TAnswer_fk2" FOREIGN KEY ("idTest")

REFERENCES "public"."TTest"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE,

CONSTRAINT "TAnswer_fk3" FOREIGN KEY ("idExaminator")

REFERENCES "public"."TExaminator"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE

) WITHOUT OIDS;

CREATE TABLE "public"."TResult" (

"id" SERIAL,

"idQuestion" SERIAL,

"Answer" VARCHAR NOT NULL,

"Score" INTEGER,

"idPassedTest" INTEGER DEFAULT nextval('"TResult_idAnswer_seq"'::regclass) NOT NULL,

CONSTRAINT "TResult_pkey" PRIMARY KEY("id"),

CONSTRAINT "TResult_fk" FOREIGN KEY ("idPassedTest")

REFERENCES "public"."TPassedTest"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE,

CONSTRAINT "TResult_fk1" FOREIGN KEY ("idQuestion")

REFERENCES "public"."TQuestion"("id")

ON DELETE NO ACTION

ON UPDATE CASCADE

NOT DEFERRABLE

) WITHOUT OIDS;

70