· Разработан интерфейс пользователя

Наряду с этим, в итоге выполнения дипломного проекта были разработаны:

· Рабочие программы на языках Delphi, удовлетворяющие всем требованиям технического задания.

· Форма выходных документов, коими являются групповые и частные отчеты.

· Инструкция по установке и пользованию системой.

Были проведены:

· Процедура тестирования, согласно ГОСТ 19.301-79 «Программа и методика испытаний». Были исправлены выявленные в процессе тестирования ошибки и недочеты.

· Оценка надежности и качества функционирования системы

Наряду с этим, был проведен расчет себестоимости и технико-экономическое обоснование разработки, приведены рекомендации по безопасности жизнедеятельности и экологии.

Возможным путем развития проекта может стать переход от клиент-серверной к многозвенной архитектуре приложения баз данных. При этом вся обработка данных будет производиться на сервере приложений. На сторону клиента будут приходить лишь результаты запросов. Введение в многозвенную архитектуру ВЕБ-сервера позволит осуществлять работу компонентов системы с помощью Интернет-технологий.

В результате выполнения дипломного проекта был получен полностью функциональный программный комплекс, сочетающий в себе редактор тестов, модуль администрирования пользователей и просмотра отчетов, модуль тестирования знаний. Разработанный комплекс удовлетворяет всем требованиям технического задания и готов к применению на практике.

Список использованных источников

1. Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя/ Пол Нильсен М.: ООО «И.Д. Вильямс», 2008. -- 1232с.:ил.

2. Губер, Мартин. Понимание SQL/Мартин Губер -- М.: 1993

3. Архангельский, А.Я. Программирование в Delphi 7/А. Я. Архангельский --М.: ООО «Бином-Пресс», 2003г.--1152с.:ил.

4. Дарахвелидзе, П. Г. Программирование в Delphi 7/ П. Г. Дарахвелизде, Е. П. Марков -- СПб.: БХВ-Петербург, 2003.--784с.:ил.

5. Липаев, В.В. Отладка сложных программных комплексов / В.В. Липаев - М.: Энергоатомиздат, 1993 - 251 с.

6. Липаев, В.В. Методы обеспечения качества крупномасштабных программных средств / В.В. Липаев - М.: СИНТЕГ, 2003 - 350 с.

7. Меняев М.Ф. Содержание организационно-экономической части дипломного проекта (для проектов, связанных с разработкой программного обеспечения): Учеб. пособие / М.Ф. Меняев _ М.: МГТУ им. Н.Э. Баумана, 2002._ 24 с.

8. Кэнту, М. Delphi 7 для профессионалов/ М. Кэнту.--СПб.:Питер,2004.--1101с.:ил.

9. МУ 2.2.4.706-98/МУ ОТ РМ 01-98.

Приложение А

Скрипт генерации новой базы данных

CREATE TABLE Answers

(

Answer_id int IDENTITY (1,1) ,

Answer_question_id int NOT NULL ,

Answer_number integer NULL ,

Answer_text text NULL ,

Answer_score integer NULL ,

Answer_is_right bit NULL ,

Answer_corresp integer NULL ,

Answer_picture image NULL

)

go

ALTER TABLE Answers

ADD CONSTRAINT XPKAnswers PRIMARY KEY NONCLUSTERED (Answer_id ASC)

go

CREATE TABLE Group_sections

(

Gs_id int IDENTITY (1,1) ,

Gs_groupe_id int NOT NULL ,

Gs_section_id int NOT NULL

)

go

ALTER TABLE Group_sections

ADD CONSTRAINT XPKGroup_sections PRIMARY KEY NONCLUSTERED (Gs_id ASC)

go

CREATE TABLE Groups

(

Groupe_id int IDENTITY (1,1) ,

Groupe_name varchar(512) NULL ,

Groupe_description varchar(512) NULL ,

Groupe_hidden bit NULL

)

go

ALTER TABLE Groups

ADD CONSTRAINT XPKGroups PRIMARY KEY NONCLUSTERED (Groupe_id ASC)

go

CREATE TABLE Questions

(

Question_id int IDENTITY (1,1) ,

Question_test_id int NOT NULL ,

Question_number integer NULL ,

Question_time char(8) NULL ,

Question_type integer NULL ,

Question_weigth integer NULL ,

Question_Text text NULL ,

Question_picture image NULL

)

go

ALTER TABLE Questions

ADD CONSTRAINT XPKQuestions PRIMARY KEY NONCLUSTERED (Question_id ASC)

go

CREATE TABLE Sections

(

Section_id int IDENTITY (1,1) ,

Section_name varchar(512) NULL ,

section_hidden bit NULL

)

go

ALTER TABLE Sections

ADD CONSTRAINT XPKSections PRIMARY KEY NONCLUSTERED (Section_id ASC)

go

CREATE TABLE Tests

(

Test_ID int IDENTITY (1,1) ,

Test_description varchar(512) NULL ,

Test_section_ID int NOT NULL ,

Test_name varchar(512) NULL ,

Test_type integer NULL ,

Test_author varchar(512) NULL ,

Test_question_count integer NULL ,

Test_is_random_answers bit NULL ,

Test_is_time_limit bit NULL ,

Test_time_limit char(8) NULL ,

Test_is_back bit NULL ,

Test_question_limit integer NULL

)

go

ALTER TABLE Tests

ADD CONSTRAINT XPKTests PRIMARY KEY NONCLUSTERED (Test_ID ASC)

go

CREATE TABLE User_answers

(

User_answer_ID int IDENTITY (1,1) ,

User_answer_qnumber integer NULL ,

User_answer_question text NULL ,

User_answer_answer text NULL ,

User_answer_time char(8) NULL ,

User_answer_is_right bit NULL ,

User_answer_score integer NULL ,

User_answer_answered bit NULL ,

User_answer_user_result_id int NOT NULL

)

go

ALTER TABLE User_answers

ADD CONSTRAINT XPKUser_answers PRIMARY KEY NONCLUSTERED (User_answer_ID ASC)

go

CREATE TABLE User_results

(

User_result_id int IDENTITY (1,1) ,

User_result_comleted bit NULL ,

User_result_time_begin datetime NULL ,

User_result_time_end datetime NULL ,

User_result_completed_questions integer NULL ,

User_result_right_questions integer NULL ,

User_result_score integer NULL ,

User_result_percent_right float NULL ,

User_result_total_questions integer NULL ,

User_result_test_title varchar(128) NULL ,

User_result_User_id int NOT NULL ,

User_result_test_id int NOT NULL

)

go

ALTER TABLE User_results

ADD CONSTRAINT XPKUser_results PRIMARY KEY NONCLUSTERED (User_result_id ASC)

go

CREATE TABLE Users

(

User_id int IDENTITY (1,1) ,

User_group_id int NOT NULL ,

User_name varchar(512) NULL ,

User_code varchar(128) NULL ,

User_password varchar(128) NULL ,

User_info varchar(512) NULL ,

User_deleted bit NULL ,

User_mail varchar(128) NULL ,

User_grants integer NULL ,

User_disable_test bit NULL

)

go

ALTER TABLE Users

ADD CONSTRAINT XPKUsers PRIMARY KEY NONCLUSTERED (User_id ASC)

go

ALTER TABLE Answers

ADD CONSTRAINT R_6 FOREIGN KEY (Answer_question_id) REFERENCES Questions(Question_id)

ON DELETE CASCADE

ON UPDATE NO ACTION

go

ALTER TABLE Group_sections

ADD CONSTRAINT R_9 FOREIGN KEY (Gs_section_id) REFERENCES Sections(Section_id)

ON DELETE CASCADE

ON UPDATE NO ACTION

go

ALTER TABLE Group_sections

ADD CONSTRAINT R_10 FOREIGN KEY (Gs_groupe_id) REFERENCES Groups(Groupe_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION

go

ALTER TABLE Questions

ADD CONSTRAINT R_5 FOREIGN KEY (Question_test_id) REFERENCES Tests(Test_ID)

ON DELETE CASCADE

ON UPDATE NO ACTION

go

ALTER TABLE Tests

ADD CONSTRAINT R_8 FOREIGN KEY (Test_section_ID) REFERENCES Sections(Section_id)

ON DELETE CASCADE

ON UPDATE NO ACTION

go

ALTER TABLE User_answers

ADD CONSTRAINT R_16 FOREIGN KEY (User_answer_user_result_id) REFERENCES User_results(User_result_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION

go

ALTER TABLE User_results

ADD CONSTRAINT R_14 FOREIGN KEY (User_result_User_id) REFERENCES Users(User_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION

go

ALTER TABLE User_results

ADD CONSTRAINT R_15 FOREIGN KEY (User_result_test_id) REFERENCES Tests(Test_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION

go

ALTER TABLE Users

ADD CONSTRAINT R_7 FOREIGN KEY (User_group_id) REFERENCES Groups(Groupe_id)

ON DELETE CASCADE

ON UPDATE NO ACTION

Go

CREATE TRIGGER tD_Answers ON Answers FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Answers */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Questions R/6 Answers on child delete no action */

/* ERWIN_RELATION:CHECKSUM="000139e1", PARENT_OWNER="", PARENT_TABLE="Questions"

CHILD_OWNER="", CHILD_TABLE="Answers"

P2C_VERB_PHRASE="R/6", C2P_VERB_PHRASE="R/6",

FK_CONSTRAINT="R_6", FK_COLUMNS="Answer_question_id" */

IF EXISTS (SELECT * FROM deleted,Questions

WHERE

/* %JoinFKPK(deleted,Questions," = "," AND") */

deleted.Answer_question_id = Questions.Question_id AND

NOT EXISTS (

SELECT * FROM Answers

WHERE

/* %JoinFKPK(Answers,Questions," = "," AND") */

Answers.Answer_question_id = Questions.Question_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last Answers because Questions exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Answers ON Answers FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Answers */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insAnswer_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Questions R/6 Answers on child update no action */

/* ERWIN_RELATION:CHECKSUM="000157da", PARENT_OWNER="", PARENT_TABLE="Questions"

CHILD_OWNER="", CHILD_TABLE="Answers"

P2C_VERB_PHRASE="R/6", C2P_VERB_PHRASE="R/6",

FK_CONSTRAINT="R_6", FK_COLUMNS="Answer_question_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(Answer_question_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Questions

WHERE

/* %JoinFKPK(inserted,Questions) */

inserted.Answer_question_id = Questions.Question_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update Answers because Questions does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_Group_sections ON Group_sections FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Group_sections */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/9 Group_sections on child delete no action */

/* ERWIN_RELATION:CHECKSUM="0002881c", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/9", C2P_VERB_PHRASE="R/9",

FK_CONSTRAINT="R_9", FK_COLUMNS="Gs_section_id" */

IF EXISTS (SELECT * FROM deleted,Sections

WHERE

/* %JoinFKPK(deleted,Sections," = "," AND") */

deleted.Gs_section_id = Sections.Section_id AND

NOT EXISTS (

SELECT * FROM Group_sections

WHERE

/* %JoinFKPK(Group_sections,Sections," = "," AND") */

Group_sections.Gs_section_id = Sections.Section_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last Group_sections because Sections exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/10 Group_sections on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/10", C2P_VERB_PHRASE="R/10",

FK_CONSTRAINT="R_10", FK_COLUMNS="Gs_groupe_id" */

IF EXISTS (SELECT * FROM deleted,Groups

WHERE

/* %JoinFKPK(deleted,Groups," = "," AND") */

deleted.Gs_groupe_id = Groups.Groupe_id AND

NOT EXISTS (

SELECT * FROM Group_sections

WHERE

/* %JoinFKPK(Group_sections,Groups," = "," AND") */

Group_sections.Gs_groupe_id = Groups.Groupe_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last Group_sections because Groups exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Group_sections ON Group_sections FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Group_sections */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insGs_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/9 Group_sections on child update no action */

/* ERWIN_RELATION:CHECKSUM="0002f0e4", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/9", C2P_VERB_PHRASE="R/9",

FK_CONSTRAINT="R_9", FK_COLUMNS="Gs_section_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(Gs_section_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Sections

WHERE

/* %JoinFKPK(inserted,Sections) */

inserted.Gs_section_id = Sections.Section_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

select @nullcnt = count(*) from inserted where

inserted.Gs_section_id IS NULL

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update Group_sections because Sections does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/10 Group_sections on child update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/10", C2P_VERB_PHRASE="R/10",

FK_CONSTRAINT="R_10", FK_COLUMNS="Gs_groupe_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(Gs_groupe_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Groups

WHERE

/* %JoinFKPK(inserted,Groups) */

inserted.Gs_groupe_id = Groups.Groupe_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

select @nullcnt = count(*) from inserted where

inserted.Gs_groupe_id IS NULL

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update Group_sections because Groups does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_Groups ON Groups FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Groups */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/7 Users on parent delete cascade */

/* ERWIN_RELATION:CHECKSUM="0001bdd4", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Users"

P2C_VERB_PHRASE="R/7", C2P_VERB_PHRASE="R/7",

FK_CONSTRAINT="R_7", FK_COLUMNS="User_group_id" */

DELETE Users

FROM Users,deleted

WHERE

/* %JoinFKPK(Users,deleted," = "," AND") */

Users.User_group_id = deleted.Groupe_id

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/10 Group_sections on parent delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/10", C2P_VERB_PHRASE="R/10",

FK_CONSTRAINT="R_10", FK_COLUMNS="Gs_groupe_id" */

IF EXISTS (

SELECT * FROM deleted,Group_sections

WHERE

/* %JoinFKPK(Group_sections,deleted," = "," AND") */

Group_sections.Gs_groupe_id = deleted.Groupe_id

)

BEGIN

SELECT @errno = 30001,

@errmsg = 'Cannot delete Groups because Group_sections exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Groups ON Groups FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Groups */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insGroupe_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/7 Users on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00022ae6", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Users"

P2C_VERB_PHRASE="R/7", C2P_VERB_PHRASE="R/7",

FK_CONSTRAINT="R_7", FK_COLUMNS="User_group_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Groupe_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,Users

WHERE

/* %JoinFKPK(Users,deleted," = "," AND") */

Users.User_group_id = deleted.Groupe_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Groups because Users exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/10 Group_sections on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/10", C2P_VERB_PHRASE="R/10",

FK_CONSTRAINT="R_10", FK_COLUMNS="Gs_groupe_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Groupe_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,Group_sections

WHERE

/* %JoinFKPK(Group_sections,deleted," = "," AND") */

Group_sections.Gs_groupe_id = deleted.Groupe_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Groups because Group_sections exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_Questions ON Questions FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Questions */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Questions R/6 Answers on parent delete cascade */

/* ERWIN_RELATION:CHECKSUM="0001fbb8", PARENT_OWNER="", PARENT_TABLE="Questions"

CHILD_OWNER="", CHILD_TABLE="Answers"

P2C_VERB_PHRASE="R/6", C2P_VERB_PHRASE="R/6",

FK_CONSTRAINT="R_6", FK_COLUMNS="Answer_question_id" */

DELETE Answers

FROM Answers,deleted

WHERE

/* %JoinFKPK(Answers,deleted," = "," AND") */

Answers.Answer_question_id = deleted.Question_id

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/5 Questions on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="Questions"

P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="R/5",

FK_CONSTRAINT="R_5", FK_COLUMNS="Question_test_id" */

IF EXISTS (SELECT * FROM deleted,Tests

WHERE

/* %JoinFKPK(deleted,Tests," = "," AND") */

deleted.Question_test_id = Tests.Test_ID AND

NOT EXISTS (

SELECT * FROM Questions

WHERE

/* %JoinFKPK(Questions,Tests," = "," AND") */

Questions.Question_test_id = Tests.Test_ID

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last Questions because Tests exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Questions ON Questions FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Questions */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insQuestion_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Questions R/6 Answers on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00026fe1", PARENT_OWNER="", PARENT_TABLE="Questions"

CHILD_OWNER="", CHILD_TABLE="Answers"

P2C_VERB_PHRASE="R/6", C2P_VERB_PHRASE="R/6",

FK_CONSTRAINT="R_6", FK_COLUMNS="Answer_question_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Question_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,Answers

WHERE

/* %JoinFKPK(Answers,deleted," = "," AND") */

Answers.Answer_question_id = deleted.Question_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Questions because Answers exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/5 Questions on child update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="Questions"

P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="R/5",

FK_CONSTRAINT="R_5", FK_COLUMNS="Question_test_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(Question_test_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Tests

WHERE

/* %JoinFKPK(inserted,Tests) */

inserted.Question_test_id = Tests.Test_ID

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update Questions because Tests does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_Sections ON Sections FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Sections */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/8 Tests on parent delete cascade */

/* ERWIN_RELATION:CHECKSUM="00019e4b", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Tests"

P2C_VERB_PHRASE="R/8", C2P_VERB_PHRASE="R/8",

FK_CONSTRAINT="R_8", FK_COLUMNS="Test_section_ID" */

DELETE Tests

FROM Tests,deleted

WHERE

/* %JoinFKPK(Tests,deleted," = "," AND") */

Tests.Test_section_ID = deleted.Section_id

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/9 Group_sections on parent delete cascade */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/9", C2P_VERB_PHRASE="R/9",

FK_CONSTRAINT="R_9", FK_COLUMNS="Gs_section_id" */

DELETE Group_sections

FROM Group_sections,deleted

WHERE

/* %JoinFKPK(Group_sections,deleted," = "," AND") */

Group_sections.Gs_section_id = deleted.Section_id

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Sections ON Sections FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Sections */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insSection_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/8 Tests on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00022bb1", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Tests"

P2C_VERB_PHRASE="R/8", C2P_VERB_PHRASE="R/8",

FK_CONSTRAINT="R_8", FK_COLUMNS="Test_section_ID" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Section_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,Tests

WHERE

/* %JoinFKPK(Tests,deleted," = "," AND") */

Tests.Test_section_ID = deleted.Section_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Sections because Tests exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/9 Group_sections on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Group_sections"

P2C_VERB_PHRASE="R/9", C2P_VERB_PHRASE="R/9",

FK_CONSTRAINT="R_9", FK_COLUMNS="Gs_section_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Section_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,Group_sections

WHERE

/* %JoinFKPK(Group_sections,deleted," = "," AND") */

Group_sections.Gs_section_id = deleted.Section_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Sections because Group_sections exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_Tests ON Tests FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Tests */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/5 Questions on parent delete cascade */

/* ERWIN_RELATION:CHECKSUM="0002ff97", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="Questions"

P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="R/5",

FK_CONSTRAINT="R_5", FK_COLUMNS="Question_test_id" */

DELETE Questions

FROM Questions,deleted

WHERE

/* %JoinFKPK(Questions,deleted," = "," AND") */

Questions.Question_test_id = deleted.Test_ID

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/15 User_results on parent delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/15", C2P_VERB_PHRASE="R/15",

FK_CONSTRAINT="R_15", FK_COLUMNS="User_result_test_id" */

IF EXISTS (

SELECT * FROM deleted,User_results

WHERE

/* %JoinFKPK(User_results,deleted," = "," AND") */

User_results.User_result_test_id = deleted.Test_ID

)

BEGIN

SELECT @errno = 30001,

@errmsg = 'Cannot delete Tests because User_results exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/8 Tests on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Tests"

P2C_VERB_PHRASE="R/8", C2P_VERB_PHRASE="R/8",

FK_CONSTRAINT="R_8", FK_COLUMNS="Test_section_ID" */

IF EXISTS (SELECT * FROM deleted,Sections

WHERE

/* %JoinFKPK(deleted,Sections," = "," AND") */

deleted.Test_section_ID = Sections.Section_id AND

NOT EXISTS (

SELECT * FROM Tests

WHERE

/* %JoinFKPK(Tests,Sections," = "," AND") */

Tests.Test_section_ID = Sections.Section_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last Tests because Sections exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Tests ON Tests FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Tests */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insTest_ID int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/5 Questions on parent update no action */

/* ERWIN_RELATION:CHECKSUM="0003c096", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="Questions"

P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="R/5",

FK_CONSTRAINT="R_5", FK_COLUMNS="Question_test_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Test_ID)

BEGIN

IF EXISTS (

SELECT * FROM deleted,Questions

WHERE

/* %JoinFKPK(Questions,deleted," = "," AND") */

Questions.Question_test_id = deleted.Test_ID

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Tests because Questions exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/15 User_results on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/15", C2P_VERB_PHRASE="R/15",

FK_CONSTRAINT="R_15", FK_COLUMNS="User_result_test_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(Test_ID)

BEGIN

IF EXISTS (

SELECT * FROM deleted,User_results

WHERE

/* %JoinFKPK(User_results,deleted," = "," AND") */

User_results.User_result_test_id = deleted.Test_ID

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Tests because User_results exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Sections R/8 Tests on child update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Sections"

CHILD_OWNER="", CHILD_TABLE="Tests"

P2C_VERB_PHRASE="R/8", C2P_VERB_PHRASE="R/8",

FK_CONSTRAINT="R_8", FK_COLUMNS="Test_section_ID" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(Test_section_ID)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Sections

WHERE

/* %JoinFKPK(inserted,Sections) */

inserted.Test_section_ID = Sections.Section_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

select @nullcnt = count(*) from inserted where

inserted.Test_section_ID IS NULL

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update Tests because Sections does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_User_answers ON User_answers FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on User_answers */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* User_results R/16 User_answers on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00017376", PARENT_OWNER="", PARENT_TABLE="User_results"

CHILD_OWNER="", CHILD_TABLE="User_answers"

P2C_VERB_PHRASE="R/16", C2P_VERB_PHRASE="R/16",

FK_CONSTRAINT="R_16", FK_COLUMNS="User_answer_user_result_id" */

IF EXISTS (SELECT * FROM deleted,User_results

WHERE

/* %JoinFKPK(deleted,User_results," = "," AND") */

deleted.User_answer_user_result_id = User_results.User_result_id AND

NOT EXISTS (

SELECT * FROM User_answers

WHERE

/* %JoinFKPK(User_answers,User_results," = "," AND") */

User_answers.User_answer_user_result_id = User_results.User_result_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last User_answers because User_results exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_User_answers ON User_answers FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on User_answers */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insUser_answer_ID int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* User_results R/16 User_answers on child update no action */

/* ERWIN_RELATION:CHECKSUM="0001ac05", PARENT_OWNER="", PARENT_TABLE="User_results"

CHILD_OWNER="", CHILD_TABLE="User_answers"

P2C_VERB_PHRASE="R/16", C2P_VERB_PHRASE="R/16",

FK_CONSTRAINT="R_16", FK_COLUMNS="User_answer_user_result_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(User_answer_user_result_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,User_results

WHERE

/* %JoinFKPK(inserted,User_results) */

inserted.User_answer_user_result_id = User_results.User_result_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

select @nullcnt = count(*) from inserted where

inserted.User_answer_user_result_id IS NULL

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update User_answers because User_results does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_User_results ON User_results FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on User_results */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* User_results R/16 User_answers on parent delete no action */

/* ERWIN_RELATION:CHECKSUM="0003a738", PARENT_OWNER="", PARENT_TABLE="User_results"

CHILD_OWNER="", CHILD_TABLE="User_answers"

P2C_VERB_PHRASE="R/16", C2P_VERB_PHRASE="R/16",

FK_CONSTRAINT="R_16", FK_COLUMNS="User_answer_user_result_id" */

IF EXISTS (

SELECT * FROM deleted,User_answers

WHERE

/* %JoinFKPK(User_answers,deleted," = "," AND") */

User_answers.User_answer_user_result_id = deleted.User_result_id

)

BEGIN

SELECT @errno = 30001,

@errmsg = 'Cannot delete User_results because User_answers exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Users R/14 User_results on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Users"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/14", C2P_VERB_PHRASE="R/14",

FK_CONSTRAINT="R_14", FK_COLUMNS="User_result_User_id" */

IF EXISTS (SELECT * FROM deleted,Users

WHERE

/* %JoinFKPK(deleted,Users," = "," AND") */

deleted.User_result_User_id = Users.User_id AND

NOT EXISTS (

SELECT * FROM User_results

WHERE

/* %JoinFKPK(User_results,Users," = "," AND") */

User_results.User_result_User_id = Users.User_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last User_results because Users exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/15 User_results on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/15", C2P_VERB_PHRASE="R/15",

FK_CONSTRAINT="R_15", FK_COLUMNS="User_result_test_id" */

IF EXISTS (SELECT * FROM deleted,Tests

WHERE

/* %JoinFKPK(deleted,Tests," = "," AND") */

deleted.User_result_test_id = Tests.Test_ID AND

NOT EXISTS (

SELECT * FROM User_results

WHERE

/* %JoinFKPK(User_results,Tests," = "," AND") */

User_results.User_result_test_id = Tests.Test_ID

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last User_results because Tests exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_User_results ON User_results FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on User_results */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insUser_result_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* User_results R/16 User_answers on parent update no action */

/* ERWIN_RELATION:CHECKSUM="000452af", PARENT_OWNER="", PARENT_TABLE="User_results"

CHILD_OWNER="", CHILD_TABLE="User_answers"

P2C_VERB_PHRASE="R/16", C2P_VERB_PHRASE="R/16",

FK_CONSTRAINT="R_16", FK_COLUMNS="User_answer_user_result_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(User_result_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,User_answers

WHERE

/* %JoinFKPK(User_answers,deleted," = "," AND") */

User_answers.User_answer_user_result_id = deleted.User_result_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update User_results because User_answers exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Users R/14 User_results on child update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Users"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/14", C2P_VERB_PHRASE="R/14",

FK_CONSTRAINT="R_14", FK_COLUMNS="User_result_User_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(User_result_User_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Users

WHERE

/* %JoinFKPK(inserted,Users) */

inserted.User_result_User_id = Users.User_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

select @nullcnt = count(*) from inserted where

inserted.User_result_User_id IS NULL

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update User_results because Users does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Tests R/15 User_results on child update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Tests"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/15", C2P_VERB_PHRASE="R/15",

FK_CONSTRAINT="R_15", FK_COLUMNS="User_result_test_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(User_result_test_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Tests

WHERE

/* %JoinFKPK(inserted,Tests) */

inserted.User_result_test_id = Tests.Test_ID

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

select @nullcnt = count(*) from inserted where

inserted.User_result_test_id IS NULL

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update User_results because Tests does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tD_Users ON Users FOR DELETE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* DELETE trigger on Users */

BEGIN

DECLARE @errno int,

@errmsg varchar(255)

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Users R/14 User_results on parent delete no action */

/* ERWIN_RELATION:CHECKSUM="00023254", PARENT_OWNER="", PARENT_TABLE="Users"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/14", C2P_VERB_PHRASE="R/14",

FK_CONSTRAINT="R_14", FK_COLUMNS="User_result_User_id" */

IF EXISTS (

SELECT * FROM deleted,User_results

WHERE

/* %JoinFKPK(User_results,deleted," = "," AND") */

User_results.User_result_User_id = deleted.User_id

)

BEGIN

SELECT @errno = 30001,

@errmsg = 'Cannot delete Users because User_results exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/7 Users on child delete no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Users"

P2C_VERB_PHRASE="R/7", C2P_VERB_PHRASE="R/7",

FK_CONSTRAINT="R_7", FK_COLUMNS="User_group_id" */

IF EXISTS (SELECT * FROM deleted,Groups

WHERE

/* %JoinFKPK(deleted,Groups," = "," AND") */

deleted.User_group_id = Groups.Groupe_id AND

NOT EXISTS (

SELECT * FROM Users

WHERE

/* %JoinFKPK(Users,Groups," = "," AND") */

Users.User_group_id = Groups.Groupe_id

)

)

BEGIN

SELECT @errno = 30010,

@errmsg = 'Cannot delete last Users because Groups exists.'

GOTO ERROR

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

CREATE TRIGGER tU_Users ON Users FOR UPDATE AS

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* UPDATE trigger on Users */

BEGIN

DECLARE @NUMROWS int,

@nullcnt int,

@validcnt int,

@insUser_id int,

@errno int,

@errmsg varchar(255)

SELECT @NUMROWS = @@rowcount

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Users R/14 User_results on parent update no action */

/* ERWIN_RELATION:CHECKSUM="00026ae8", PARENT_OWNER="", PARENT_TABLE="Users"

CHILD_OWNER="", CHILD_TABLE="User_results"

P2C_VERB_PHRASE="R/14", C2P_VERB_PHRASE="R/14",

FK_CONSTRAINT="R_14", FK_COLUMNS="User_result_User_id" */

IF

/* %ParentPK(" OR",UPDATE) */

UPDATE(User_id)

BEGIN

IF EXISTS (

SELECT * FROM deleted,User_results

WHERE

/* %JoinFKPK(User_results,deleted," = "," AND") */

User_results.User_result_User_id = deleted.User_id

)

BEGIN

SELECT @errno = 30005,

@errmsg = 'Cannot update Users because User_results exists.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

/* Groups R/7 Users on child update no action */

/* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Groups"

CHILD_OWNER="", CHILD_TABLE="Users"

P2C_VERB_PHRASE="R/7", C2P_VERB_PHRASE="R/7",

FK_CONSTRAINT="R_7", FK_COLUMNS="User_group_id" */

IF

/* %ChildFK(" OR",UPDATE) */

UPDATE(User_group_id)

BEGIN

SELECT @nullcnt = 0

SELECT @validcnt = count(*)

FROM inserted,Groups

WHERE

/* %JoinFKPK(inserted,Groups) */

inserted.User_group_id = Groups.Groupe_id

/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */

IF @validcnt + @nullcnt != @NUMROWS

BEGIN

SELECT @errno = 30007,

@errmsg = 'Cannot update Users because Groups does not exist.'

GOTO ERROR

END

END

/* ERwin Builtin 2 февраля 2009 г. 2:18:54 */

RETURN

ERROR:

raiserror @errno @errmsg

rollback transaction

END

go

ПРИЛОЖЕНИЕ Б

Образцы экранных форм

Рисунок Б.1 - Главная форма редактора тестов

Рисунок Б.2 - Реакция системы на потенциальную логическую ошибку



Рисунок Б.3 - Главная форма модуля администрирования пользователей

Рисунок Б.4 - Окно аутентификации пользователя модуля тестирования



Рисунок Б.5 - Окно выбора теста модуля тестирования

Приложение В

Текст программных модулей

Модуль редактора тестов

unit uEditorDataModule;

interface

uses

SysUtils, Windows, Dialogs, Messages, Classes, DB, ADODB;

type

TDataModule1 = class(TDataModule)

ADOConnection: TADOConnection;

tabelSections: TADOTable;

tabelTests: TADOTable;

tabelQuestions: TADOTable;

tabelAnswers: TADOTable;

dsSections: TDataSource;

dsTests: TDataSource;

dsQuestions: TDataSource;

dsAnswers: TDataSource;

tabelTestsTest_ID: TAutoIncField;

tabelTestsTest_section_ID: TIntegerField;

tabelTestsTest_name: TStringField;

tabelTestsTest_description: TStringField;

tabelTestsTest_author: TStringField;

tabelTestsTest_type: TIntegerField;

tabelTestsTest_question_count: TIntegerField;

tabelTestsTest_question_limit: TIntegerField;

tabelTestsTest_is_random_answers: TBooleanField;

tabelTestsTest_is_time_limit: TBooleanField;

tabelTestsTest_time_limit: TStringField;

tabelTestsTest_is_back: TBooleanField;

universalQuery: TADOQuery;

userQuery: TADOQuery;

dsUser: TDataSource;

groupQuery: TADOQuery;

dsGroup: TDataSource;

procedure dsSectionsDataChange(Sender: TObject; Field: TField);

procedure dsTestsDataChange(Sender: TObject; Field: TField);

procedure dsQuestionsDataChange(Sender: TObject; Field: TField);

procedure dsQuestionsStateChange(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

DataModule1: TDataModule1;

implementation

uses uEditorMain, StdCtrls, Controls;

{$R *.dfm}

procedure TDataModule1.dsSectionsDataChange(Sender: TObject;

Field: TField);

begin

ZeroMemory(@currentSection, sizeof(TSection));

currentSection.ID := tabelSections.FieldByName('Section_ID').AsInteger;

currentSection.Name := tabelSections.FieldByName('Section_name').AsString;

end;

procedure TDataModule1.dsTestsDataChange(Sender: TObject; Field: TField);

begin

if tabelTests.State = dsBrowse then

begin

teacherForm.testTypeCB.ItemIndex := tabelTests.FieldByName('Test_type').AsInteger;

teacherForm.timeLimitEdit.Text := tabelTests.FieldByName('Test_Time_limit').AsString;

end;

end;

procedure TDataModule1.dsQuestionsDataChange(Sender: TObject;

Field: TField);

begin

if tabelQuestions.State = dsBrowse then

begin

teacherForm.questionTypeCB.ItemIndex := tabelQuestions.FieldByName('Question_type').AsInteger;

teacherForm.questionTimLimitEdit.Text := tabelQuestions.FieldByName('Question_time').AsString;

end;

with teacherForm do

begin

if questionTypeCB.ItemIndex in [0, 1, 4] then

begin

answerNumberLabel.Visible := false;

answerScoreLabel.Visible := false;

answerNumberEdit.Visible := false;

answerScoreEdit.Visible := false;

answerCorrespLabel.Visible := false;

answerCorrespMemo.Visible := false;

//--------------------------------

answerTextLabel.Visible := true;

answerTextLabel.Left := answerPanel.Left + 5;

answerTextMemo.Left :=answerDBCtrlGrid.Left + 5;

answerTextMemo.Width := answerDBCtrlGrid.Width - 80;

answerDBCtrlGrid.Refresh;

end

else if questionTypeCB.ItemIndex = 2 then

begin

answerNumberLabel.Visible := false;

answerScoreLabel.Visible := false;

answerNumberEdit.Visible := false;

answerScoreEdit.Visible := false;

//---------------------------------------

answerTextLabel.Visible := true;

answerTextLabel.Left := answerDBCtrlGrid.Left + 5;

answerTextMemo.Left :=answerDBCtrlGrid.Left + 5;

answerTextMemo.Width := (answerDBCtrlGrid.Width - 80) div 2;

answerCorrespLabel.Visible := true;

answerCorrespLabel.Left := answerDBCtrlGrid.Left + answerTextMemo.Width + 20;

answerCorrespMemo.Visible := true;

answerCorrespMemo.Left := answerCorrespLabel.Left;

answerCorrespMemo.Width := answerTextMemo.Width - 30;

answerDBCtrlGrid.Refresh;

end

else if questionTypeCB.ItemIndex = 3 then

begin

answerScoreLabel.Visible := false;

answerScoreEdit.Visible := false;

answerCorrespLabel.Visible := false;

answerCorrespMemo.Visible := false;

//---------------------------------

answerNumberLabel.Visible := true;

answerNumberEdit.Visible := true;

answerNumberLabel.Left := answerDBCtrlGrid.Left + 5;

answerNumberEdit.Left := answerNumberLabel.Left;

answerTextLabel.Left := answerDBCtrlGrid.Left + 70;

answerTextMemo.Left := answerTextLabel.Left;

answerTextMemo.Width := answerDBCtrlGrid.Width - answerTextMemo.Left - 80;

answerDBCtrlGrid.Refresh;

end

end;

end;

procedure TDataModule1.dsQuestionsStateChange(Sender: TObject);

begin

if tabelQuestions.State = dsEdit then

teacherForm.answerPanel.Enabled := false

else

teacherForm.answerPanel.Enabled := true;

end;

end.

unit uEditorAutentification;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls, Buttons;

type

TautentificationForm = class(TForm)

loginEdit: TEdit;

passwordEdit: TEdit;

loginLabel: TLabel;

passwordLabel: TLabel;

btnOK: TBitBtn;

btnCancel: TBitBtn;

procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);

private

{ Private declarations }

public

{ Public declarations }

end;

var

autentificationForm: TautentificationForm;

implementation

uses uEditorDataModule, uEditorMain;

{$R *.dfm}

procedure TautentificationForm.FormCloseQuery(Sender: TObject;

var CanClose: Boolean);

var

sqlStr : string; begin

if (Sender as TautentificationForm).ModalResult = mrOk then

begin

if ((loginEdit.Text = '') and (passwordEdit.Text = '')) then

begin

MessageBox(Handle,'Введите имя учетной записи и пароль','Ошибка!!!',MB_OK or MB_ICONERROR);

CanClose := false;

end

else

begin

DataModule1.ADOConnection.Open;

sqlStr := 'SELECT * from Users Where (User_group_id=1 or User_group_id=2)' +

' and (User_name=''' + loginEdit.Text + ''' and User_password=''' +

passwordEdit.Text + ''')';

//ShowMessage(sqlStr);

try

DataModule1.ADOConnection.Open;

DataModule1.groupQuery.Open;

DataModule1.userQuery.Close;

DataModule1.userQuery.SQL.Text := sqlStr;

DataModule1.userQuery.Open;

except

end;

if DataModule1.userQuery.RecordCount = 1 then

begin

DataModule1.tabelSections.Open;

DataModule1.tabelTests.Open;

DataModule1.tabelQuestions.Open;

DataModule1.tabelAnswers.Open;

end

else

begin

MessageBox(Handle,'Доступ запрещен!!!','Ошибка!!!',MB_OK or MB_ICONERROR);

teacherForm.Close;

end;

end;

end

else

begin

teacherForm.Close;

end;

end;

end.

unit uEditorMain;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, Menus, ToolWin, ComCtrls, ExtCtrls, StdCtrls, DBCtrls, Grids,

DBGrids, Mask, Buttons, dbcgrids, ActnList, XPStyleActnCtrls, ActnMan,

StdActns, ExtActns, ActnCtrls, XPMan, ImgList, RxCombos, RxRichEd,

DBRichEd, AppEvent, SpeedBar, RxLookup, ToolEdit, RXDBCtrl;

type

TteacherForm = class(TForm)

MainMenu: TMainMenu;

mFile: TMenuItem;

mTest: TMenuItem;

mUsers: TMenuItem;

mOptions: TMenuItem;

mHelp: TMenuItem;

mTQuestion: TMenuItem;

mTAnswer: TMenuItem;

mTQueAdd: TMenuItem;

mTQueDel: TMenuItem;

N5: TMenuItem;

mTQueFirst: TMenuItem;

mTQuePrev: TMenuItem;

mTQueNext: TMenuItem;

mTQueLast: TMenuItem;

mTAnsAdd: TMenuItem;

mTAnsDel: TMenuItem;

StatusBar: TStatusBar;

ToolBar1: TToolBar;

XPManifest1: TXPManifest;

ImageList1: TImageList;

ActionList1: TActionList;

ColorDialog1: TColorDialog;

RichEditBold1: TRichEditBold;

RichEditItalic1: TRichEditItalic;

RichEditUnderline1: TRichEditUnderline;

RichEditStrikeOut1: TRichEditStrikeOut;

RichEditBullets1: TRichEditBullets;

RichEditAlignLeft1: TRichEditAlignLeft;

RichEditAlignRight1: TRichEditAlignRight;

RichEditAlignCenter1: TRichEditAlignCenter;

PageControl: TPageControl;

tabTest: TTabSheet;

Splitter2: TSplitter;

Splitter3: TSplitter;

topPanel: TPanel;

Splitter1: TSplitter;

testOptionsPanel: TPanel;

discriptionLabel: TLabel;

authorLabel: TLabel;

testTypeLabel: TLabel;

discriptionMemo: TDBMemo;

authorEdit: TDBEdit;

optionGB: TGroupBox;

isRandomAnswersCB: TDBCheckBox;

isTimeLimitCB: TDBCheckBox;

isBackCB: TDBCheckBox;

timeLimitEdit: TComboEdit;

testTypeCB: TComboBox;

btnNewTest: TBitBtn;

btnDelTest: TBitBtn;

btnTestAccept: TBitBtn;

btnTestCancel: TBitBtn;

btnNewSection: TBitBtn;

selectTestPanel: TPanel;

testGB: TGroupBox;

testLookupList: TRxDBLookupList;

sectionGB: TGroupBox;

sectionLookupCB: TRxDBLookupCombo;

rightDownPanel: TPanel;

Splitter4: TSplitter;

answerPanel: TPanel;

answerNumberLabel: TLabel;

answerTextLabel: TLabel;

answerIsRightLabel: TLabel;

answerScoreLabel: TLabel;

answerCorrespLabel: TLabel;

answerDBCtrlGrid: TDBCtrlGrid;

answerIsRightCheck: TDBCheckBox;

answerTextMemo: TDBMemo;

answerScoreEdit: TDBEdit;

answerNumberEdit: TDBEdit;

answerCorrespMemo: TDBMemo;

answerNavigator: TDBNavigator;

questionDetailPanel: TPanel;

questionHeaderLabel: TLabel;

questionTypeLabel: TLabel;

questinWeightLabel: TLabel;

questionTimeLimitLabel: TLabel;

questionHeaderEdit: TDBEdit;

questionTypeCB: TComboBox;

questionWeightEdit: TDBEdit;

ToolBar2: TToolBar;

fontsComboBox: TFontComboBox;

ToolButton10: TToolButton;

fontSizeComboBox: TComboBox;

tbBold: TToolButton;

tbItalic: TToolButton;

tbUnderline: TToolButton;

ToolButton4: TToolButton;

tbLeft: TToolButton;

tbCenter: TToolButton;

tbRight: TToolButton;

ToolButton8: TToolButton;

tbBullets: TToolButton;

ToolButton11: TToolButton;

tbInsertObject: TToolButton;

btnQuestAccept: TBitBtn;

btnQuestCancel: TBitBtn;

questionTextEdit: TRxDBRichEdit;

questionTimLimitEdit: TRxDBComboEdit;

leftDownPanel: TPanel;

questionGrid: TDBGrid;

questionNavigator: TDBNavigator;

procedure isTimeLimitCBClick(Sender: TObject);

procedure btnTestAcceptClick(Sender: TObject);

procedure btnNewSectionClick(Sender: TObject);

procedure btnNewTestClick(Sender: TObject);

procedure btnDelTestClick(Sender: TObject);

procedure answerNavigatorClick(Sender: TObject; Button: TNavigateBtn);

procedure fontsComboBoxChange(Sender: TObject);

procedure fontSizeComboBoxChange(Sender: TObject);

procedure ToolButton11Click(Sender: TObject);

procedure btnTestCancelClick(Sender: TObject);

procedure btnQuestAcceptClick(Sender: TObject);

procedure btnQuestCancelClick(Sender: TObject);

procedure questionTextEditEnter(Sender: TObject);

procedure tbBoldClick(Sender: TObject);

procedure tbItalicClick(Sender: TObject);

procedure tbUnderlineClick(Sender: TObject);

procedure questionTextEditSelectionChange(Sender: TObject);

procedure tbLeftClick(Sender: TObject);

procedure tbCenterClick(Sender: TObject);

procedure tbInsertObjectClick(Sender: TObject);

procedure questionNavigatorClick(Sender: TObject;

Button: TNavigateBtn);

procedure FormActivate(Sender: TObject);

private

function CurrText: TRxTextAttributes;

{ Private declarations }

public

{ Public declarations }

end;

TSection = record

ID : integer;

Name : string;

end;

TTest = record

ID : integer;

sectionID : integer;

name : string;

discription : string;

author : string;

testType : integer;

questionCount : integer;

quistionLimit : integer;

isRandomAnswer : boolean;

timeIsLimit : boolean;

timeLimit : string;

isBack : boolean;

end;

TQuestion = record

ID : integer;

testID : integer;

number : integer;

time : string;

answerQuantity : integer;

queType : integer;

weight : integer;

text : string;

picture : TMemoryStream;

end;

TAnswer = record

ID : integer;

questionID : integer;

number : integer;

text : string;

score : integer;

isRight : boolean;

corresp : array [1..10,1..10] of integer;

picture : TMemoryStream;

end;

var

teacherForm: TteacherForm;

currentSection, newSection : TSection;

currentTest, newTest : TTest;

currentQuestion, newQuestion : TQuestion;

currentAnswer, newAnswer : TAnswer;

sqlString : string;

implementation

uses uEditorDataModule, DB, ADODB, Math, uEditorAutentification;

{$R *.dfm}

function TteacherForm.CurrText: TRxTextAttributes;

begin

if questionTextEdit.SelLength > 0 then

Result := questionTextEdit.SelAttributes

else

Result := questionTextEdit.DefAttributes;

end;

procedure TteacherForm.isTimeLimitCBClick(Sender: TObject);

begin

if isTimeLimitCB.Checked = true then

timeLimitEdit.Enabled := True

else

timeLimitEdit.Enabled := False;

end;

procedure TteacherForm.btnTestAcceptClick(Sender: TObject);

begin

try

with DataModule1.tabelTests do

begin

Edit;

FieldByName('Test_type').AsInteger := testTypeCB.ItemIndex;

FieldByName('Test_time_limit').AsString := timeLimitEdit.Text;

Post;

Refresh;

end;

except

end;

end;

procedure TteacherForm.btnNewSectionClick(Sender: TObject);

begin

newSection.Name := InputBox('Создание нового раздела','Введите название раздела','');

if newSection.Name <> '' then

begin

sqlString := 'INSERT INTO Sections (Section_name)' +

'VALUES (''' + newSection.Name + ''')';

try

DataModule1.universalQuery.SQL.Clear;

DataModule1.universalQuery.SQL.Text := sqlString;

DataModule1.universalQuery.ExecSQL;

DataModule1.tabelSections.Close;

DataModule1.tabelSections.Open;

except

on exception do

MessageBox(Handle, 'Ошибка создания раздела', 'Ошибка', MB_OK or MB_ICONERROR);

end;

end

end;

procedure TteacherForm.btnNewTestClick(Sender: TObject);

begin

newTest.name := InputBox('Создание нового теста','Введите название теста','');

if newTest.name <> '' then

begin

sqlString := 'INSERT INTO Tests (Test_section_ID,Test_name)' +

'VALUES (' + DataModule1.tabelSections.FieldByName('Section_ID').AsString +

',''' + newTest.name + ''')';

try

with DataModule1.universalQuery do

begin

SQL.Clear;

SQL.Text := sqlString;

ExecSQL;

end;

DataModule1.tabelTests.Close;

DataModule1.tabelTests.Open;

except

on exception do

MessageBox(Handle, 'Ошибка создания теста', 'Ошибка', MB_OK or MB_ICONERROR);

end;

end;

end;

procedure TteacherForm.btnDelTestClick(Sender: TObject);

begin

sqlString := 'DELETE FROM Tests WHERE Test_ID = '+

DataModule1.tabelTests.FieldByName('Test_ID').AsString;

with DataModule1.universalQuery do

begin

SQL.Clear;

SQL.Text := sqlString;

ExecSQL;

end;

DataModule1.tabelTests.Close;