Методические указания по проектированию информационных систем в среде MS ACCESS на примере создания БД “Переговоры”.

Первое, что необходимо сделать при проектировании БД – это сформировать перечень основных её элементов. В нашем случае ими будут: название города и его телефонный код; Ф.И.О. абонентов и их телефонные номера; продолжительность и цена телефонных разговоров в национальных денежных единицах; операторы телефонной связи и даты проведения разговоров и др.

Эти и другие элементы представляют в СУБД Access соответствующие поля данных. Поскольку Access является реляционной БД, сформируем из данного перечня необходимое количество таблиц (в нашем случае три).

Создадим модель базы данных “Переговоры”.
Для этого проделаем следующие действия.
1. Первую (главную) таблицу назовём “Телефон” и включим в неё следующие поля: номер телефона абонента, ФИО, город, улица, дом, корпус и номер квартиры. Вторую таблицу назовём “Город”, включив её состав поля: код и название города, а также коэффициент. Последнее поле понадобится для расчета платы за телефонные разговоры). Третью таблицу назовём “Звонки”, включив в её состав поля: код звонка, кто (номер), кому (номер), куда (код города), дата и продолжительность разговора.

В дальнейшем структура БД (количество таблиц и связь между ними), а также структура самих таблиц может быть изменена.

Рекомендуется эти таблицы нарисовать на бумаге, так как это не только помогает эффективно формировать БД, но и устанавливать связи между входящими в её состав таблицами.

2. Для создания любой таблицы требуется, чтобы любое, входящее в её состав поле, было описано рядом параметров, среди которых первичными являются три: название или имя поля, тип данных и размер (длина) поля.

Представим структуру таблицы “Телефон”.

Таблица 1. Структура таблицы "Телефон".

Поле	Тип данных	Размер	Другие свойства
Номер телефона	Числовой	Длинное целое	Совпадения не допускаются
Фамилия	Текстовый
Имя	Текстовый
Отчество	Текстовый
Город	Текстовый
Улица	Текстовый
Дом	Текстовый
Корпус	Текстовый
Квартира	Числовой	Целое

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

Таблица 2.Структура таблицы "Город".

Поле	Тип данных	Размер	Другие свойства
Код города	Числовой	Длинное целое	Совпадения не допускаются
Название города	Текстовый		Совпадения не допускаются
Коэффициент	Числовой	Одинарное с плавающей точкой	Два знака после запятой

Таблица 3. Структура таблицы "Звонки".

Поле	Тип данных	Размер	Другие свойства
Код звонка	Счётчик	Длинное целое	Совпадения не допускаются
Кто (код города)	Числовой	Длинное целое	Подстановка: Поле со списком
Кто (номер)	Числовой	Длинное целое	Подстановка: Поле со списком
Куда (код города)	Числовой	Длинное целое	Подстановка: Поле со списком
Кому (номер)	Числовой	Длинное целое
Дата	Дата/время	Дата	Маска ввода: 00.00.0000
Продолжительность	Числовой	Длинное целое

Формирование элемента "Поле со списком" необходимо для удобства ввода данных в таблицу "Звонки", поскольку данные из этой таблицы в полях "Кто (код города)", "Кто (номер)", "Куда (код города)" выбираются из соответствующих данных таблиц "Телефон" и "Город" и многократно повторяются. При этом, неважно как потом будут вноситься данные в таблицы: непосредственно через таблицу или через соответствующую форму, подстановка будет работать в любом случае. Формирование подстановки "Поле со списком" рассмотрим позже.

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

Каждая таблица включает в свой состав поле кода, используемого обычно как счётчик (идентификатор) главного её параметра и, как правило, являющегося ключевым полем.

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

Обычно можно задать три вида связей между таблицами: Один-ко-многим, Многие-ко-многим и Один-к-одному.

Наиболее часто используется тип связи между таблицами “Один-ко-многим”. В такой связи каждой записи в таблице "А" может соответствовать несколько записей в таблице “В” (поля с этими записями называют внешними ключами), а запись в таблице “В” не может иметь более одной соответствующей ей записи в таблице “А”. Такая связь создаётся, когда только одно из полей таблицы является ключевым или имеет уникальный индекс, т.е. значения в нём не повторяются.

При связи “Многие-ко-многим” одной записи в таблице “А” может соответствовать несколько записей в таблице “В”, а одной записи в таблице “В” – несколько записей в таблице “А”. Такая схема реализуется только с помощью третьей (связующей) таблицы, ключ которой состоит по крайней мере из двух полей, одно из которых является общим с таблицей “А”, а другое – общим с таблицей “В”. Она фактически представляет две связи типа “один-ко-многим” через третью таблицу, ключ которой состоит по крайней мере из двух полей, общих для двух других таблиц.

При связи “Один-к-одному” запись в таблице “А” может иметь только одну связанную запись в таблице “В” и наоборот. Этот тип связи используют редко, так как такие данные могут быть помещены в одну таблицу. Связь с отношением “Один-к-одному” применяют для разделения очень широких таблиц, для отделения части таблицы в целях её защиты, а также для сохранения сведений, относящихся к подмножеству записей в главной таблице. Она создается, когда оба связываемых поля являются ключевыми или имеют уникальные индексы.

3. Создадим базу данных “Переговоры” в Access.
Создание любой БД (в том числе “Переговоры”) целесообразно осуществлять на основе предварительно разработанной модели. Эта работа была проделана в п.2. Теперь приступим к реализации модели с помощью СУБД Access.

Для этого загрузим программу “Access”.
В открывшемся окне этой программы найдём кнопку “Файл” в верхней строке меню программы, откроем выпадающее меню “Файл” и активизируем в нём команду “Создать...”.

В появившемся окне “Создание файла” выберем пункт “Новая база данных”, активизируем его. В появившемся окне “Файл новой базы данных” найдём строку “Имя файла” и введём в неё имя проектируемой БД. Присвоим создаваемому в Access файлу имя “Переговоры” и нажмём на кнопку “Создать”. В результате в выбранной нами папке появится файл с именем “Переговоры.mdb”.

Рис.1. База данных "Peregovori".

Предварительно следует создать папку, где будет находиться создаваемая БД. В нашем случае это папка с именем или фамилией студента, которая расположена в папке с именем – номер учебной группы, а та, в свою очередь, расположена в папке “Мои документы”.

В результате появится новое окно с именем “Peregovori: база данных” (Рис. 1) .

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

Учиться создавать таблицы лучше в режиме конструктора, поэтому выбираем и активизируем вариант “Создание таблиц в режиме конструктора”. При этом откроется окно таблицы, в котором следует ввести сформированные ранее поля таблицы.

Создадим сначала, например, таблицу "Телефон". Её параметры описаны выше (см. Таблица 1). В левый столбец "ИМЯ ПОЛЯ" вводят имена полей, а в столбец "ТИП ДАННЫХ" - нужный тип данных для каждого из них, в правый столбец "ОПИСАНИЕ" вносят текстовые комментарии, которые при заполнении таблицы данными будут высвечиваться в последней строке окна в качестве подсказки (так сделает в таблице "Звонки").

Рис.2. Таблица "Телефон". Определение полей данных.

Закончив вводить в таблицу необходимые данные, закрываем ее, присвоив таблице имя "Телефон". Далее аналогичным образом создаем оставшиеся две таблицы (Рис. 5.3 и Рис. 5.4.).

Рис.3. Таблица "Город". Определение полей данных.

Рис.4. Таблица "Звонки". Определение полей данных.

Сами данные в таблицы можно вводить двумя способами. Первый способ: выбрать соответствующую таблицу и заполнять ее (вносить дополнения или изменения); второй способ: вводить данные через форму. Будем вводить данные через формы, работу с которыми рассмотрим позже.

4. Разработаем схему данных

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

Так, например, любой абонент может по телефону позвонить нескольким разным абонентам, поэтому связь между объектами (таблицами) "Телефон" и "Звонки" будет "Один-ко-многим" ( ).
В один и тот же город может позвонить много абонентов. В этом случае, связь между объектами (таблицами) "Город" и "Звонки" также будет "Один-ко-многим" ( ).
В одном и том же городе много абонентов телефонной связи (телефонных аппаратов). В этом случае связь между объектами (таблицами) "Город" и "Телефон", тоже будет "Один-ко-многим" ( ).

Информационно-логическая модель реляционной БД формируется в меню "Сервис" по команде "Схема данных". При этом автоматически открывается окно "Добавление таблиц", из которого с помощью кнопки "Добавить" поочередно переносят в модель все таблицы и нажимают кнопку "Закрыть".
От соответствующих полей подчинённых таблиц следует установить связи согласно определениям, сделанным в предыдущем пункте.
Например, для установления связи между таблицами "Город" и "Телефон" подводят курсор мыши к ключевому полю "Названия города" в таблице "Город", щёлкают левой кнопкой мыши и, не отпуская её, перетаскивают курсор на поле "Город" в таблицу "Телефон", а затем отпускают кнопку мыши. Схема выполненных связей БД "Переговоры" представлена на рис. 5.

Рис.5. Схема данных.

Если правой клавишей мыши "кликнуть" по какой-либо линии связи между таблицами, то откроется окно, в котором можно "Изменить связь…" или "Удалить связь".

Выберем режим "Изменить связь…". В открывшемся окне "Изменение связей":

1) установим флажок ("галочку") в свойстве "Обеспечение целостности данных", а затем;
2) установим флажки в свойствах "Каскадное обновление связанных полей" и "Каскадное удаление связанных записей" (Рис. 6).

Рис.6. Изменение связей.

При этом отредактированные записи в одной таблице (например, "Город"), связанные с записями другой (например, "Телефон"), изменятся в последней автоматически. Аналогично, удаление данных из одной таблицы, связанной с какой-либо другой таблицей, повлечет удалении данных из обеих таблиц.

По окончании установления связей окно схемы данных закрывают, сохранив её макет.

5. Рассмотрим формирование подстановки "Поле со списком".

Осуществим эту работу следующим образом:

откроем таблицу "Звонки" в режиме Конструктора;
выберем поле "Кто (код города)";
в нижней части окна в "Свойствах поля" выберем вкладку "Подстановка";
для поля "Тип элемента управления" установим "Поле со списком",
для поля "Тип источника строк" установим "Таблица или запрос";
в поле "Источник строк" cформируем запрос. Для этого нажмём на самую правую кнопку в этом поле [...]. В результате откроется окно построителя запросов. Выберём таблицу "Город", поле "Код города", и установим флажок "Вывод на экран".

Рис.7. Построитель запросов. Поле со списком.

В результате в свойствах поля "Кто (код города)" таблицы "Звонки" в закладке "Подстановка" будут внесены данные, представленные на Рис. 8 .

Рис.8. Таблица "Звонки". Поле со списком.

Аналогичные процедуры выполняются для полей "Кто (номер)" и "Куда (код города)". В результате будет создан "скелет" БД "Переговоры". Теперь необходимо определить её содержание.

6. Создадим формы для ввода данных в подготовленные таблицы.

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

Создать форму можно несколькими способами: с помощью конструктора, мастера автоформ и др. (Рис. 9).

Рис.9. Окно создания новых форм.

Для создания формы, например, "Город", выполяют следующие действия:

• находясь в объекте "Таблицы", выбирают таблицу "Город", а в верхнем меню "Вставка" - режим "Форма";
• В появившемся окне "Новая форма" выбирают режим, например, "Автоформа: в столбец" и нажимают на кнопку "ОК".

При этом будет создана форма для ввода данных в таблицу "Город". Её требуется сохранить, дав ей имя, например, "Город" (Рис. 10).

Рис.10. Вариант формы "Город".

Если требуется корректировка и доработка формы, то для перехода в режим конструктора необходимо в верхнем меню "Вид" выбрать команду "Конструктор". Возврат в режим работы с формой осуществляется через команду "Режим формы" в верхнем меню "Вид". Можно корректировать форму другим способом: перейти в объекты "Форма", выбрать нужную форму и кликнуть по кнопке "Конструктор".

Для создания формы "Абонент" воспользуемся мастером создания форм:

•в поле "Таблицы и запросы" выберем "Таблица: Телефон" (Рис. 11); из доступных полей выберем все (в общем случае, при необходимости можно варьировать какие поля и в какой последовательности выводить в форме).

Рис.11. Мастер создания форм. Поля.

• выбираем внешний вид формы, в нашем случае "в один столбец" (Рис. 12).

Рис.12. Мастер создания форм. Внешний вид.

•следующий шаг - определение стиля формы. Выберем стандартный стиль (Рис. 13).

Рис.13. Мастер создания форм. Стиль.

•в заключение процедур с Мастером создания форм, зададим имя формы (Рис. 14) и откроем её для ввода данных .

Рис.14. Мастер создания форм. Имя.

В итоге, разработанная с помощью Мастера форма будет выглядеть следующим образом (Рис. 15).

Рис.15. Форма "Абонент".

Аналогично, любым из описанных способов создаём форму для последней таблицы "Звонки".

Формирование подстановки "Поле со списком" можно сделать при редактировании форм.
Делают это следующим образом.

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

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

• Для поля "Данные" выбрать из таблицы имя поля формируемого списка.
• Для поля "Тип источника строк" установить режим "Таблица и запрос", а в поле "Источник строк" сформировать запрос.
• Для этого надо нажать на самую правую кнопку в этом поле [...] и в строках запроса "Поле" "Имя таблицы" установить необходимые данные. Для того, чтобы в форме в поле со списком появлялись названия городов, нужно в первых двух колонках запроса в строку "Поле" установить название поля с названиями городов из таблицы "Город", имя которой автоматически отразится в строке "Имя таблицы".

Эти же процедуры можно проделать автоматически с помощью мастера создания полей.

1 шаг. (Рис. 16)

2 шаг. (Рис. 17)

3 шаг. (Рис. 18)

4 шаг. (Рис. 19)

5 шаг. (Рис. 20)

6 шаг. (Рис. 21)

На предыдущих рисунках 16-21 представлены пошаговые операции в Мастере создания полей (Поле со списком).

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

Одиночный переключатель удобно использовать одновременно с полем таблицы, в котором устанавливается один из двух взаимоисключающих параметров, например, в таблицу "Телефон" можно включить поле "Пол", которое имеет два логических значения (Да/Нет). В этом случае групповой переключатель будет использоваться для выбора пола абонентов (мужского или женского). Форма создания такого переключателя представлена на рис. 22 - 25.

1 шаг. (Рис. 22)

2 шаг. (Рис. 23)

3 шаг. (Рис. 24)

4 шаг. (Рис. 25)

7. Теперь заполним все таблицы необходимыми данными.

В каждой таблице должно быть не менее десяти записей !!!

Рис.26. Таблица "Телефон". Данные.

Рис.27. Таблица "Город". Данные.

Рис.28. Таблица "Звонки". Данные.

8. Сортировка данных

Для удобства просмотра записей их можно сортировать в таблице в определённой последовательности, например, в порядке убывания или возрастания какого-либо характеризующего поле (столбец) параметра. Функция сортировки относится к процессу фильтрации данных.

Записи таблицы всегда располагаются в файле БД в том порядке, в котором они были включены в таблицу. Сортировку можно произвести по нескольким полям одновременно.

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