База данных Контроль Сессионной успеваемости студентов

Введение 3
1. Постановка задачи 4
2. Средства реализации 5
3. Требования к аппаратному и программному обеспечению 5
4. Соединение с базой данных при помощи технологии ADO 6
4.1. Установка соединения при помощи ADOConnection 7
4.2.Создание запроса 8
4.3. Получение данных 9
4.4.Анализ результирующего набора 14
5. Интерфейс пользователя 15
6. Структура данных 26
6.1.Концептуальная модель 26
6.2.Физическая модель 26
6.3.Таблицы, составляющие базу данных 28
6.4. Скрипты 30
7. Обработка исключительных ситуаций 40
Заключение 43
Список литературы 44
Приложение 45

ShowModal;
end;
procedure Tmain.N12Click(Sender: TObject);
begin
dm1.ADOQDisciplines.SQL.Text:='select * from disciplines where type="курсовая"';
dm1.ADOQDisciplines.Active:=false;
dm1.ADOQDisciplines.Active:=true;
ctype :=3;
accreate.Caption:='Создать ведомость по курсовой';
accreate.ShowModal;
end;
procedure Tmain.N15Click(Sender: TObject);
begin
dm1.ADOQDisciplines.SQL.Text:='select * from disciplines where type="курсовая"';
dm1.ADOQDisciplines.Active:=false;
dm1.ADOQDisciplines.Active:=true;
ctype :=3;
acopen.ShowModal;
end;
procedure Tmain.N16Click(Sender: TObject);
begin
report.ShowModal;
end;
end.
Файл UAcCreate.pas
unit UAcCreate;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,udm1, StdCtrls, DBCtrls;
type
TAcCreate = class(TForm)
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Button1: TButton;
DBLCB1: TDBLookupComboBox;
DBLCB2: TDBLookupComboBox;
DBLCB3: TDBLookupComboBox;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
AcCreate: TAcCreate;
group_id,dis_id,teacher_id : integer;
group_name,dis_name,teacher_fam : string;
students_count,mode:integer;
// 1 - создание ведомости
// 2 - открытие ведомости
ctype:integer;
// 1 - зачёт
// 2 - экзамен
// 3 - курсовая
implementation
uses UAcForm;
{$R *.dfm}
procedure TAcCreate.Button1Click(Sender: TObject);
begin
if length(DBLCB1.Text)=0 then
showmessage('Не указана группа')
else if length(DBLCB2.Text)=0 then
showmessage('Не указан предмет')
else if length(DBLCB3.Text)=0 then
showmessage('Не указан преподаватель')
else
begin
mode:=1;
group_id:=DBLCB1.KeyValue;
dis_id:= DBLCB2.KeyValue;
teacher_id:=DBLCB3.KeyValue;
group_name:=DBLCB1.Text;
dis_name :=DBLCB2.Text;
teacher_fam:=DBLCB3.Text;
dm1.ADOQCheck.SQL.Text:='select * from ac where (groupid = '+inttostr(group_id)+
') and (disciplineid = ' +inttostr(dis_id)+
') and (teacherid = ' +inttostr(teacher_id)+
')';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
if dm1.ADOQCheck.RecordCount > 0 then
begin
case ctype of
1:showmessage('Зачётная ведомость уже сформирована. Для просмотра выберите пункт "Просмотреть ведомости"');
2:showmessage('Экзаменационная ведомость уже сформирована. Для просмотра выберите пункт "Просмотреть ведомости"');
3:showmessage('Ведомость уже сформирована. Для просмотра выберите пункт "Просмотреть ведомости"');
end;
end
else
begin
case ctype of
1:acform.Caption:='Зачётная ведомость';
2:acform.Caption:='Экзаменационная ведомость';
3:acform.Caption:='Ведомость по курсовым работам';
end;
acform.ShowModal;
end;
end;
end;
end.
Файл UAcForm.pas
unit UAcForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,udm1, Grids, StdCtrls,uaccreate;
type
Tacform = class(TForm)
Label1: TLabel;
SG: TStringGrid;
Label2: TLabel;
Label3: TLabel;
teacher: TLabel;
discipline: TLabel;
group: TLabel;
Button1: TButton;
Label4: TLabel;
procedure FormShow(Sender: TObject);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
acform: Tacform;
ac_id: integer;
implementation
uses DB;
{$R *.dfm}
procedure Tacform.FormShow(Sender: TObject);
var
i, stud_id:integer;
begin
if mode = 1 then
begin
// Формирование шапки ведомости
dm1.ADOQCheck.Insert;
dm1.ADOQCheck.FieldByName('teacherid').AsInteger:= teacher_id;
dm1.ADOQCheck.FieldByName('disciplineid').AsInteger:= dis_id;
dm1.ADOQCheck.FieldByName('groupid').AsInteger:= group_id;
dm1.ADOQCheck.Post;
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
end;
dm1.ADOQCheck.SQL.Text:='select id from ac where (groupid = '+inttostr(group_id)+
') and (disciplineid = ' +inttostr(dis_id)+
') and (teacherid = ' +inttostr(teacher_id)+
')';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
ac_id:=dm1.ADOQCheck.fieldbyname('id').AsInteger;
if mode = 1 then
begin
dm1.ADOQCheck.SQL.Text:='select * from academic';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
dm1.ADOQAcCreate.SQL.Text:='select * from students where groupid = '+inttostr(group_id);
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;
students_count:=dm1.ADOQAcCreate.RecordCount;
// Формирование списка
dm1.ADOQAcCreate.First;
for i:=1 to students_count do
begin
stud_id:= dm1.ADOQAcCreate.fieldbyname('id').AsInteger;
dm1.ADOQCheck.Insert;
dm1.ADOQCheck.FieldByName('studentid').AsInteger:= stud_id;
dm1.ADOQCheck.FieldByName('acid').AsInteger:= ac_id;
dm1.ADOQCheck.Post;
dm1.ADOQAcCreate.Next;
end;
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
end;
// Вывод списка
dm1.ADOQAcCreate.SQL.Text:='select s.fam, a.mark '+
'from students s, academic a '+
'where (a.acid ='+inttostr(ac_id)+') and '+
'(s.id=a.studentid)';
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;
students_count:= dm1.ADOQAcCreate.RecordCount;
dm1.ADOQAcCreate.First;
sg.RowCount:= students_count + 1;
group.Caption:=group_name;
discipline.Caption:=dis_name;
teacher.Caption:=teacher_fam;
sg.Cells[0,0]:='Фамилия';
sg.Cells[1,0]:='Оценка';
for i:=1 to students_count do
begin
sg.Cells[0,i]:=dm1.ADOQAcCreate.fieldbyname('fam').AsString;
sg.Cells[1,i]:=dm1.ADOQAcCreate.fieldbyname('mark').AsString;
dm1.ADOQAcCreate.Next;
end;
end;
procedure Tacform.Button1Click(Sender: TObject);
var
i:integer;
begin
dm1.ADOQAcCreate.SQL.Text:='select * '+
'from academic a '+
'where a.acid ='+inttostr(ac_id);
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;
dm1.ADOQAcCreate.First;
for i:=1 to students_count do
begin
dm1.ADOQAcCreate.edit;
dm1.ADOQAcCreate.fieldbyname('mark').AsString:=sg.Cells[1,i];
dm1.ADOQAcCreate.Post;
dm1.ADOQAcCreate.Next;
end;
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;
end;
end.
Файл UAcOpen.pas
unit UAcOpen;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, StdCtrls, DBCtrls, uaccreate;
type
TAcOpen = class(TForm)
Label1: TLabel;
Label2: TLabel;
Button1: TButton;
DBLCB1: TDBLookupComboBox;
DBLCB2: TDBLookupComboBox;
Label3: TLabel;
DBLCB3: TDBLookupComboBox;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
AcOpen: TAcOpen;
implementation
uses UAcForm;
{$R *.dfm}
procedure TAcOpen.Button1Click(Sender: TObject);
begin
if length(DBLCB1.Text)=0 then
showmessage('Не указана группа')
else if length(DBLCB2.Text)=0 then
showmessage('Не указан предмет')
else if length(DBLCB3.Text)=0 then
showmessage('Не указан преподаватель')
else
begin
mode:=2;
group_id:=DBLCB1.KeyValue;
dis_id:= DBLCB2.KeyValue;
teacher_id:= DBLCB3.KeyValue;
group_name:=DBLCB1.Text;
dis_name :=DBLCB2.Text;
teacher_fam:=DBLCB3.Text;
dm1.ADOQCheck.SQL.Text:='select * from ac where (groupid = '+inttostr(group_id)+
') and (disciplineid = ' +inttostr(dis_id)+
') and (teacherid = ' +inttostr(teacher_id)+
')';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
if dm1.ADOQCheck.RecordCount > 0 then
acform.ShowModal
else
begin
case ctype of
1:showmessage('Зачетная ведомость ещё не сформирована. Для создания выберите пункт "Создать ведомости"');
2:showmessage('Экзаменационная ведомость ещё не сформирована. Для создания выберите пункт "Создать ведомости"');
3:showmessage('Ведомость ещё не сформирована. Для создания выберите пункт "Создать ведомости"');
end;
end;
end;
end;
end.
Файл UDisciplines.pas
unit UDisciplines;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids;
type
Tdisciplines = class(TForm)
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
private
{ Private declarations }
public
{ Public declarations }
end;
var
disciplines: Tdisciplines;
implementation
{$R *.dfm}
end.
Файл UDM1.pas
unit UDM1;
interface
uses
SysUtils, Classes, DB, ADODB;
type
TDM1 = class(TDataModule)
ADOQTeachers: TADOQuery;
DSTeachers: TDataSource;
ADOConnection1: TADOConnection;
DADisciplines: TDataSource;
ADOQDisciplines: TADOQuery;
ADOQStudents: TADOQuery;
ADOQGroups: TADOQuery;
ADOQFaculty: TADOQuery;
ADOQAcadem: TADOQuery;
DSStudents: TDataSource;
DSGroups: TDataSource;
DSFaculty: TDataSource;
DSAcadem: TDataSource;
ADOQGroupsid: TIntegerField;
ADOQGroupsname: TWideStringField;
ADOQGroupsfacultyid: TIntegerField;
ADOQStudentsid: TAutoIncField;
ADOQStudentsfam: TWideStringField;
ADOQStudentsname: TWideStringField;
ADOQStudentssurname: TWideStringField;
ADOQStudentszachetid: TIntegerField;
ADOQStudentsgroupid: TIntegerField;
ADOQCheck: TADOQuery;
DSCheck: TDataSource;
ADOQAcCreate: TADOQuery;
DSAcCreate: TDataSource;
ADOQDisciplinesid: TAutoIncField;
ADOQDisciplinesname: TWideStringField;
ADOQDisciplinestype: TWideStringField;
ADOQReport: TADOQuery;
DSReport: TDataSource;
ADOQRepstud: TADOQuery;
AutoIncField1: TAutoIncField;
WideStringField1: TWideStringField;
WideStringField2: TWideStringField;
WideStringField3: TWideStringField;
IntegerField1: TIntegerField;
IntegerField2: TIntegerField;
DSRepstud: TDataSource;
private
{ Private declarations }
public
{ Public declarations }
end;
var
DM1: TDM1;
implementation
{$R *.dfm}
end.
Файл UFaculty.pas
unit UFaculty;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, Grids, DBGrids, ExtCtrls, DBCtrls;
type
Tfaculty = class(TForm)
DBNavigator1: TDBNavigator;
DBGrid1: TDBGrid;
private
{ Private declarations }
public
{ Public declarations }
end;
var
faculty: Tfaculty;
implementation
{$R *.dfm}
end.
Файл UGroups.pas
unit UGroups;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls;
type
Tgroups = class(TForm)
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Label1: TLabel;
DBLookupComboBox1: TDBLookupComboBox;
procedure DBLookupComboBox1Click(Sender: TObject);
procedure DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
private
{ Private declarations }
public
{ Public declarations }
end;
var
groups: Tgroups;
implementation
uses ADODB;
{$R *.dfm}
procedure Tgroups.DBLookupComboBox1Click(Sender: TObject);
begin
dm1.ADOQGroups.SQL.Text:= 'select * from groups where facultyid ='+ vartoStr(DBLookupComboBox1.KeyValue);
dm1.ADOQGroups.Active:= false;
dm1.ADOQGroups.Active:= true;
end;
procedure Tgroups.DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
begin
if (Button=nbInsert) then
dm1.ADOQGroups.FieldByName('facultyid').Value:= vartoStr(DBLookupComboBox1.KeyValue);
end;
end.
Файл UReport.pas
unit UReport;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,udm1, Grids, DBGrids, DBCtrls, StdCtrls;
type
Treport = class(TForm)
Label1: TLabel;
Label2: TLabel;
DBLCB1: TDBLookupComboBox;
DBLCB2: TDBLookupComboBox;
DBGrid1: TDBGrid;
procedure DBLCB1Click(Sender: TObject);
procedure DBLCB2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
report: Treport;
implementation
{$R *.dfm}
procedure Treport.DBLCB1Click(Sender: TObject);
begin
dm1.ADOQRepstud.SQL.Text:= 'select * from students where groupid ='+ vartoStr(DBLCB1.KeyValue);
dm1.ADOQRepstud.Active:= false;
dm1.ADOQRepstud.Active:= true;
end;
procedure Treport.DBLCB2Click(Sender: TObject);
begin
dm1.ADOQReport.SQL.Text:= 'select t.fam,d.type,d.name,a.mark,a.studentid from academic a,disciplines d, teachers t, ac '+
'where (a.acid=ac.id) and (ac.disciplineid=d.id) and (ac.teacherid=t.id) and (a.studentid ='+ vartoStr(DBLCB2.KeyValue)+')';
dm1.ADOQReport.Active:= false;
dm1.ADOQReport.Active:= true;
end;
end.
Файл UStudents.pas
unit UStudents;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls;
type
Tstudents = class(TForm)
Label1: TLabel;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
DBLookupComboBox1: TDBLookupComboBox;
procedure DBLookupComboBox1Click(Sender: TObject);
procedure DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
private
{ Private declarations }
public
{ Public declarations }
end;
var
students: Tstudents;
implementation
{$R *.dfm}
procedure Tstudents.DBLookupComboBox1Click(Sender: TObject);
begin
dm1.ADOQStudents.SQL.Text:= 'select * from students where students.groupid ='+ vartoStr(DBLookupComboBox1.KeyValue);
dm1.ADOQStudents.Active:= false;
dm1.ADOQStudents.Active:= true;
end;
procedure Tstudents.DBNavigator1Click(Sender: TObject;
Button: TNavigateBtn);
begin
if (Button=nbInsert) then
dm1.ADOQGroups.FieldByName('groupid').Value:= vartoStr(DBLookupComboBox1.KeyValue);
end;
end.
Файл UTeachers
unit UTeachers;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids;
type
Tteachers = class(TForm)
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
private
{ Private declarations }
public
{ Public declarations }
end;
var
teachers: Tteachers;
implementation
{$R *.dfm}
end.