Базы данных на логическоми и функциональном программировании

Базы данных на логическоми и функциональном программировании

Пусть задан документ «Экзаменационная ведомость» следующего вида:

Требуется построить БД для экзаменационных ведомостей.

Занятие 1. Создание таблиц

1.Создание БД

Для работы с БД ее необходимо создать. Для этого:

Вызовите Access.

В появившемся окне установите переключатель «Новая БД» и нажмите кнопку ОК.

В появившемся окне «Файл базы данных» в поле ввода «Имя файла» укажите имя новой БД. Пусть в нашем случае оно будет «Vedom». Нажмите кнопку «Создать».

Появилось окно базы данных. Теперь можно создавать таблицы БД. Реализуем это на примере справочных таблиц, входящих в нашу БД. Предполагаем, что БД нормализована (как осуществляется процесс нормализации, будет рассмотрено далее). Пусть необходимо создать справочную таблицу «Предметы», содержащую перечень предметов, для которых проводится экзамен и имеющую вид:

Заполните бланк таблицы следующим образом:

первая строка. Имя поля: CodPredm, Тип данных: Счетчик, Ключевое поле, Подпись: Код предмета.

вторая строка. Имя поля:Predm, Тип данных: Текстовый, Подпись: Предмет, Размер поля: 30 (Это означает, что под текстовое поле отведено 30 байт, по умолчанию 50).

Щелкните кнопку «Сохранить» или выберите пункт меню «Сохранить». В появившемся диалоговом окне наберите имя таблицы Predmet и нажмите кнопку ОК. Таблица создана.

Аналогичным образом создайте другие справочные таблицы, структура которых приведена ниже.

Таблица Prep (Справочник преподавателей)

Простейший способ работы с таблицами заключается в следующем: таблицу необходимо открыть в режиме таблицы, выбрав пункт меню «Открыть» либо соответствующую кнопку на панели инструментов.

Откройте таблицу Facultet и введите в нее несколько записей.

Откройте таблицу Prep и введите в нее несколько записей. Обратите внимание, что значение поля «Код преподавателя» (CodPrep) устанавливается при вводе новой записи автоматически.

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

Другой подход, который позволяет обрабатывать таблицы, заключается в использовании форм, задающих пользовательский интерфейс. Простейший путь создания формы заключается в использовании «Мастера форм». Для примера рассмотрим, как создать форму для ввода данных о группах и студентах в этих группах. Для этого будет необходимо выполнить следующие действия:

Раскрыть вкладку «Формы» окна базы данных и нажать кнопку «Создать» или выбрать пункт меню «Создание формы с помощью мастера».

В списке диалогового окна «Новая форма» выделить элемент «Мастер форм».

В поле со списком, находящемся в нижней части диалогового окна «Новая форма», содержатся имена таблиц и запросов, которые могут использоваться в качестве источника данных для формы. Выделите в этом списке элемент Group. Нажмите кнопку «ОК». Отобразится первое диалоговое окно «Мастера форм».

Появится список доступных полей. Нажмите кнопку «>>» для копирования всех полей таблицы Group из списка «Доступные поля» в список «Выбранные поля». Размещение этих полей в форме позволит редактировать данные таблицы Group.

Раскройте список «Таблицы/Запросы» и выделите в нем таблицу Student. Список «Доступные поля» изменится, в нем будут перечислены все поля таблицы Student.

Нажмите кнопку «>>» для копирования всех полей таблицы Student в список «Выбранные поля».

Поскольку поле SGroup таблицы Group уже включено в список «Выбранные поля», то нет необходимости включать это поле из таблицы Student в форму. В списке «Выбранные поля» выделите элемент SGroup, а затем нажмите кнопку «<» для удаления поля из списка. Нажмите кнопку «Далее» для отображения второго окна «Мастера форм».

Так как поля, выбранные для отображения в форме, взяты из двух разных таблиц, то «Мастер форм» предоставляет возможность выбрать тип представления данных. Поскольку сведения о студентах являются частью информации о группе, их можно рассматривать как подчиненные данные или подчиненную форму. Выделите тип представления данных «- Group» и установите переключатель «Подчиненные формы». Нажмите кнопку «Далее» для перехода к третьему диалоговому окну «Мастера форм».

В этом окне можно определить параметры оформления подчиненной формы. Выберите переключатель «Ленточный». В этом случае будет создана подчиненная форма, отображающая данные в виде аналогичном табличному, но позволяющие изменять параметры форматирования элементов управления. Нажмите кнопку «Далее» для отображения следующего окна «Мастера форм».

В следующем окне можно выбрать стиль оформления новой формы. Поскольку созданная форма предназначена для ввода данных и не нуждается в специальных эффектах оформления, выделите стиль «Стандартный», а затем нажмите кнопку «Далее» для перехода к последнему окну «Мастера форм».

Укажите имена для основной и подчиненной формы. Выберите переключатель «Открытие формы для просмотра или ввода данных», а затем нажмите кнопку «Готово».

Для создания и редактирования внешнего вида форм можно использовать конструктор форм. Для изменения внешнего вида только что созданной формы нажмите кнопку «Конструктор форм» на панели инструментов. Теперь можно поэкспериментировать с различными методами изменения внешнего вида форм и его содержимого. Работа в конструкторе аналогична работе по созданию и редактированию форм в Visual Basic. Однако, есть ряд различий, связанных с тем, что макет формы представляется в виде трех разделов:

Раздел заголовка формы определяет высоту области заголовка формы.

Этот раздел может понадобиться, если решено добавить к форме заголовок и примечания. Область заголовка формы может содержать текст, графику и другие элементы управления, которые будут выводиться в верхней части формы. При печати формы раздел заголовка формы будет отображаться только на первой странице.

Раздел области данных - это та часть формы, где содержатся основные данные, и который был создан с помощью «Мастера форм».

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

Задание. Создайте формы для ввода данных во все таблицы БД Vedom.

Занятие 3. Создание простых отчетов

Конечным продуктом приложений баз данных является отчет. Наиболее простым способом создания отчета является использование «Мастера отчетов». Процесс создания отчета с его помощью похож на процесс создания формы с помощью «Мастера форм». Создадим отчет «Список студентов по группам». Для этого выполните следующие действия:

Раскройте вкладку «Отчеты» в окне базы данных, а затем щелкните кнопку «Создать». Появиться диалоговое окно «Новый отчет».

Выберите таблицу Student в раскрывающемся списке. Из списка в правом верхнем углу выделите «Мастер отчетов» и нажмите кнопку «ОК». На экране появится начальное окно «Мастера отчетов».

Прежде всего, необходимо определить какие поля будут образовывать строки отчета. Для нашего случая будем считать, что необходимы все поля, но предположим, что вывод фамилии должен предшествовать выводу номера зачетки. Поэтому будем выделять поля в списке «Доступные поля» в следующем порядке: SGroup, FIOS, NZach, YearEnter. После выделения каждого поля нажмите кнопку «>». Поле переместится в список «Выбранные поля» в том порядке, в котором они выбирались в списке «Допустимые поля». Нажмите кнопку «Далее», чтобы перейти ко второму диалоговому окну.

Для данного отчета имеет смысл выбрать группирование по шифру группы. Для этого выберите в первом раскрывающемся списке поле SGroup и щелкните по кнопке «>» и нажмите на кнопку «Далее».

В пределах группы можно сортировать записи по значению произвольного поля. Поле SGroup не предлагается в диалоговом окне в качестве варианта, поскольку по данному полю ведется группирование. Выберите в раскрывающемся списке поле FIOS. По умолчанию принят порядок сортировки по возрастанию. Если необходимо выбрать порядок сортировки по убыванию, нажмите на кнопку справа от раскрывающегося списка. Нажмите на кнопку «Далее».

«Мастер отчетов» предложит выбрать для отчета один из стилей макета. Для этого отчета выберите переключатель «Ступенчатый». Для перехода к следующему диалоговому окну нажмите кнопку «Далее».

Выберите для своего отчета один из предопределенных стилей, например, для этого отчета выберите стиль «Строгий». Нажмите на кнопку «Далее» для перехода к последнему диалоговому окну.

В качестве заголовка отчета введите Список студентов по группам и нажмите кнопку «Готово».

Отредактируйте макет созданного отчета с помощью «Конструктора отчетов». Это делается фактически также как и для форм.

Задание. Создайте отчеты на основе имеющихся таблиц в соответствии с заданием преподавателя. Исследуйте различные возможности (группирование, сортировка, подведение итогов, стили), предоставляемые «Мастером отчетов».

Окно конструктора запросов имеет следующий вид:

В дальнейшем будем изображать только бланк запроса.

Добавление полей в запрос:

Немного преобразуем предыдущий запрос. Пусть требуется создать список фамилий студентов и группы, в которых они учатся. Для этого:

Вызовите конструктор запросов для запроса «Список студентов».

При открытом окне конструктора запросов курсор находится в строке «Поле» бланка запроса в первой колонке. Нажмите кнопку списка, расположенную в первой колонке и выберите поле SGroup.

Второе поле определим с помощью перетаскивания. Для этого в списке полей таблицы Student в верхней панели окна конструктора выделите поле FIOS и, удерживая левую кнопку мыши, перетащите поле во вторую колонку строки «Поле» в бланке запроса по образцу. Бланк запроса по образцу будет иметь вид:

4. Выполните запрос.

Задание условий выбора и порядка результатов сортировки:

Пусть нужно создать список студентов, обучающихся в одной из групп, причем фамилии студентов должны быть упорядочены по алфавиту. Для этого:

Войдите в режим конструктора запроса и в столбце бланка запроса, содержащим поле SGroup, в строке Условие отбора наберите шифр группы, имеющийся в таблице. Пусть, например, это будет «АП51». По умолчанию предполагается проверка условия равенства. Другие отношения (<>, >=, <=, >, <, Like, In, Between) необходимо указывать.

Сбросьте флажок в строке «Вывод на экран» для поля SGroup, чтобы оно не отображалось при запуске запроса.

Встаньте на столбец FIOS в строку «Сортировка» и нажмите клавишу <F4>, чтобы отобразить список способов сортировки. Выберите способ сортировки «по возрастанию». Бланк запроса примет вид:

Выполните запрос.

Вызов запросов из формы:

Пусть необходимо создать запрос, осуществляющий вывод списка студентов из группы, определяемой пользователем. Для этого выполните следующие действия:

Создайте в режиме конструктора форму «Список студентов в группе». При создании формы не указывайте источник данных.

Установите на форме элемент управления: Поле со списком с наименованием «Шифр группы». Нажмите на панели инструментов кнопку «Мастер элементов», если она не утоплена. Таблица Group содержит шифры групп, из которых будет осуществляться выбор. Поэтому в первом окне «Мастера списков» установите переключатель «Таблица или запрос …» и нажмите кнопку «Далее».

В списке нового диалогового окна выберите таблицу Group. Нажмите кнопку «Далее». Появится третье диалоговое окно «Мастера списков».

Для создаваемого списка необходимо поле SGroup. Поэтому выделите это поле в списке «Доступные поля» и нажмите кнопку «>» для перемещения поля в список «Выбранные поля». Нажмите кнопку «Далее». Появится следующее окно.

В этом окне отображается список значений для поля со списком. Убедитесь, что флажок «Скрыть ключевой столбец» сброшен. Нажмите кнопку «Далее».

В последнем диалоговом окне можно установит подпись поля со списком. В текстовом окне этого диалогового окна введите Шифр группы и нажмите кнопку «Готово».

Вернитесь в бланк запроса. Встаньте на строку «Условие отбора» первого столбца и удалите, содержащийся там текст. Щелкните правой клавишей и выберите команду «Построить». Появится окно «Построителя выражений». В этом окне в нижнем левом углу содержится список всех используемых объектов. Выберите Form и раскройте список всех форм, в этом списке выберите форму «Список студентов в группе».

В среднем нижнем окне появится список элементов управления, находящихся на форме. Выберите Поле со списком Шифр группы и щелки щелкните клавишу «Вставить». Нажмите «ОК», чтобы закрыть «Построитель выражений». Бланк запроса должен принять вид.

Вернитесь в конструктор форм.

Отключите на панели кнопку «Мастер элементов» и установите на панели кнопку управления. Установите для этой кнопки свойство «Название»: «Список_студентов». Выберите в свойствах вкладку «События». Среди событий выберите событие «Нажатие кнопки» и в появившемся списке выберите пункт «Программа». Введите текст DoCMD.OpenQuery “Список студентов”.

Вызовите форму на выполнение. Выберите с помощью выпадающего списка шифр группы и нажмите кнопку управления.

Использование более сложных условий.

Пусть требуется построить следующий запрос: «Построить список студентов заданной группы, поступивших в институт либо после 2002 года, либо до 1999». Условие, по которому будут выбираться данные в запросе, можно записать следующим образом:

Forms![Список студентов в группе]![Шифр группы] ((YearEnter => 2002) (YearEnter <= 1999)).

Для того чтобы построить данный запрос выполните следующие действия:

Откройте предыдущий запрос в режиме «Конструктора».

Добавьте в третий столбец бланка запроса поле YearEnter.

В строке Условие отбора этого столбца установите =>2002, а в стоке Или - <= 1999.

Вызовите форму на выполнение.

Вычисления в запросах.

В запросах можно использовать выражения в условиях и для создания вычисляемых полей. Пусть необходимо создать запрос: «Вывести список студентов, обучающихся в заданной группе, и срок их обучения». Для этого необходимо выполнить следующие действия:

Вызовите «Конструктор запросов» для предыдущего запроса.

Удалите третий столбец бланка запроса.

В первом свободном столбце введите имя столбца «Срок обучения:», а затем выражение:

Year(Date()) - [YearEnter]

Если не ввести имя поля и двоеточие, то вычисляемому полю будет присвоено имя «Выражение1». Бланк запроса примет вид:

4. Вызовите форму на выполнение.

Создайте следующие запросы и формы для их вызова (возможно размещение нескольких запросов на одной форме):

Составить список групп для заданного факультета, в которых более 20 студентов.

Составить список групп для заданного факультета и курса, в которых заданное количество студентов (число студентов должно задаваться в диапазоне).

Определить список предметов, принимавшихся заданным преподавателем.

Создать список ведомостей по экзаменам, принимавшимся в весеннем семестре

Создать список групп, сдававших экзамены заданному преподавателю в конкретном месяце.

Составить список преподавателей, принимавших экзамены в период с заданного момента времени по текущий момент времени.

Составить список студентов, получивших неудовлетворительные оценки или не аттестованных по какому-либо предмету.

Занятие 6. Построение многотабличных запросов

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

Внутреннее соединение (или эквисоединение) обычно используют при создании запросов на выборку. Результирующее множество запроса содержит записи одной таблицы, имеющие совпадающие значения в связанных полях другой (WHERE поле1 = поле2). В большинстве случаев соединения основываются на уникальном значении поля первичного ключа в одной таблице и значении поля внешнего ключа в другой таблице, если таблицы связаны отношением «один - ко - многим». Если в таблице «многие» записи с заданной величиной отсутствуют, то соответствующие записи таблицы «один» в результирующее множество не включаются. Access автоматически создает соединение между таблицами, если в них имеются поля с одинаковыми именами, одно из которых является ключевым, либо если соединение было задано в окне связей.

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

Рекурсивное соединение связывает данные в одной таблице. Создание этого типа соединения выполняется путем добавления в запрос копии таблицы и связывания полей идентичных таблиц.

Соединение по отношению (или тета-соединение) связывает данные некоторым отношением. Это может быть любое отношение, исключая равенство.

Построим запрос: «На каких курсах и в каких группах преподается дисциплина Базы данных». Для этого выполним следующие действия:

Создайте в режиме «Конструктора» новый запрос. С помощью окна «Добавление таблиц» включите в окно запроса таблицы Group, Vedom и Predmet. Обратите внимание, что между таблицами будут установлены связи.

В первый свободный столбец бланка запроса включите поле Predm из таблицы Predmet. Установите в этом столбце Условие отбора равное “Базы данных”. Сбросьте флажок Вывод на экран.

Во второй столбец включите поле SGroup из таблицы Vedom, а в третий - Curs из таблицы Group. Выполните запрос.

Использование в запросах условий Like, In, Between.

Like определяет шаблон, которому должно соответствовать искомое строковое выражение. Наиболее часто используются символы шаблона *, замещающий любое число знаков, и ? - замещающий один символ.

In определяет, является ли строковое выражение элементом списка значений.

Between определяет, находится ли значение поля в определенном диапазоне.

Реализуем запрос «Выдать список студентов, либо не аттестованных, либо получивших неудовлетворительную оценку по математическим дисциплинам, поступившим в институт в период с 1999 по 2001 год». Будем предполагать, что название математических дисциплин содержит в той или иной форме производные от слова «математика». Для построения данного запроса в конструкторе необходимо добавить таблицы Ball, Student, Predmet, Ekzamen и Vedom. Бланк запроса должен иметь следующий вид:

Построить следующие запросы:

Определить предметы, сдававшиеся в осеннем семестре.

Составить список групп, сдававших «Физику», в весеннем семестре.

Определит список преподавателей, принимающих экзамены на факультете «Информационных технологий».

Задание 7.

Создание запросов с использованием статистических функций

В качестве статистических функций используются следующие функции:

Avg() - вычисляет арифметическое среднее набора чисел, содержащееся в указанном типе запроса;

Count() - вычисляет количество непустых записей, возвращаемых запросом;

First() - возвращает значение поля из первой записи результирующего набора;

Last() - возвращает значение поля из последней записи результирующего набора;

Max() - возвращает максимальное из набора значений, содержащихся в указанном поле;

Min() - возвращает минимальное из набора значений, содержащихся в указанном поле;

Sum() - возвращает сумму значений, содержащихся в указанном поле;

Var() - возвращает дисперсию, вычисляемую по набору значений, содержащихся в данном поле.

Реализуем запрос: «Определить количество студентов, поступивших в институт в разные годы». Для создания этого запроса выполните следующие действия:

Создайте новый запрос и добавьте в него таблицу Student.

Выберите команду «Вид. Групповые операции» либо щелкните правой кнопкой мыши по бланку запроса и выберите в контекстном меню команду «Групповые операции». В бланк запроса добавляется строка Групповые операции, содержащая по умолчанию в каждой ячейке операцию «Группировка». Эта операция позволяет сгруппировать данные, полученные из таблицы или запроса, относительно каждого уникального значения поля, для которого определена эта операция.

Добавьте в бланк запроса поле YearEnter, для которого в строке Групповые операции определена операция «Группировка».

Добавьте в бланк запроса поле FIOS, для которого в выпадающем списке в строке Групповые операции выберите операцию Count.

Выполните запрос.

Задание: Создайте следующие запросы:

Определить средний балл, полученный студентами разных групп по различным предметам.

Определить средний возраст студентов по факультетам.

Определить количество неудовлетворительных оценок, полученных по физике по факультетам.

Определить студента, имеющего максимальный средний балл на факультете «Информационных технологий».

Использование запросов на изменение

Запросы на изменение позволяют создавать новые таблицы и обновлять данные в уже имеющихся. Будем рассматривать четыре типа запросов на изменение:

Запросы на создание таблицы создают таблицы на основе данных, содержащихся в результирующем множестве запроса.

Запросы на добавление позволяют добавить в таблицу создаваемые запросом данные.

Запросы на удаление удаляют из таблицы записи, которые соответствуют данным результирующего множества запроса.

Запросы на обновление изменяют значение существующих полей таблицы в соответствии с записями результирующего множества.

Создание новой таблицы при помощи запроса. Пусть требуется построить таблицу, в которой содержится перечень предметов и преподавателей, преподающих эти предметы, на различных факультетах с указанием полугодия, когда принимается экзамен. Реализовать этот запрос можно в два этапа. На первом этапе осуществляется разработка запроса на выборку данных, как это было показано выше. Бланк запроса должен иметь вид:

Убедитесь в правильности выполнения запроса. Создайте на его основе таблицу. Для этого выберите команду «Запрос. Создание таблицы». Введите Ведение предметов в поле «Имя таблицы». Нажмите на панели инструментов кнопку «Запуск». Выводится приглашение подтвердить добавление указанного числа записей. Нажмите кнопку «Да». Убедитесь, что таблица создана.

Создание запросов на добавление данных. Запрос на добавление данных создает структуру новой таблицы на основе записей запроса. Запрос на добавление включает новые данные в уже существующую таблицу. Для построение запроса на добавление выбирается команда «Запрос. Добавление».

Здесь следует учитывать, что в бланке запроса появиться новая строка Добавление. В этой строке указываются поля таблицы, в которую происходит добавление данных, а в строках Поле и Имя таблицы - откуда происходит добавление.

Запросы на удаление записей. Создайте запрос на выборку и включите в него все поля (перетащите элемент списка полей *), а затем добавьте поля, определяющие критерий отбора уничтожаемых данных. Если не задать условие, то, преобразовав запрос на выборку в запрос на удаление и выполнив его, можно удалить все записи используемой таблицы.

Для преобразования запроса на выборку в запрос на удаление выберите команду «Запрос. Удаление». Строки «Сортировка» и «Вывод на экран» исчезают, появляется строка «Удаление». Значение «Из» в первой ячейке строки «Удаление» вместе с выражением, заданным в ячейке «Поле», указывают откуда произойдет удаление. Столбцы со значением «Условие» в строке «Удаление» определяют критерий отбора записей для удаления.

Запросы для обновления записей. Запросы на обновление позволяют изменять данные в таблице. Они используются для обновления значений полей с помощью одного общего выражения. Для создания запроса на обновление требуется выполнить следующие действия:

Откройте «Конструктор запросов». С помощью «Запрос. Обновление» установите режим обновления. В бланке запроса появиться новая строка Обновление, в которой указывается значение, на которое будет меняться значение поля в данном столбце. Условие определяет критерий отбора записей для обновления.

Задание. Создать следующие запросы:

1. Создать таблицу с перечнем студентов факультета информатики, поступившим в институт в 2001 году, имеющим задолженности с указанием предметов, по которым имеются задолженности.

2. Создать запрос, позволяющий добавлять новую группу на факультет. Для обращения к запросу создайте форму.

3. Создайте запрос для удаления студентов, имеющих более пяти задолженностей в течение трех лет.

4. Создайте запрос, позволяющий изменять Ф.И.О. Преподавателя, который проводит экзамен.