Окончание табл. 1
Таблица |
Ключ
| Имя поля |
Тип данных
|
Описание
|
Размер поля
| Клиенты |
К
| Код
Тип клиента
Название
Телефон
Город
Улица
Дом
Квартира |
Счетчик
Числовой
Текстовый
Текстовый
Числовой
Числовой
Числовой
Числовой
|
Автонумератор (ключ таблицы)
Выберите тип клиента (числ. код)
Введите название клиента
Введите телефон клиента
Выбор города (вн.поле, числ. код)
Выбор улицы (вн.поле, числ. код)
Введите № дома клиента
Введите № квартиры
|
Длинное целое
30
15
Длинное целое
Длинное целое
Целое
Целое
| Заказы |
К
| Код заказа
Дата заказа
Сотрудник
Клиент
Общая сумма
|
Числовой
Дата/время
Числовой
Числовой
Денежный
|
Номер заказа (ключ таблицы)
Дата оформления заказа
Выбор сотрудника (числ. код)
Выберите клиента (числ. код)
Стоимость заказанных ценностей
|
Длинное целое
Длинное целое
Длинное целое
| Сотрудники |
К
| Табельный №
ФИО сотрудника
Телефон
Подчиняется
|
Числовой
Текстовый
Текстовый
Числовой
|
Табельный номер сотрудника
ФИО сотрудника
Введите телефон сотрудника
Выберите руководителя (числ. код)
|
Длинное целое
20
15
Длинное целое
| Составы заказов |
К
К
| Код заказа
Ценность
Цена продажи
Количество
ЕИ
Сумма
|
Числовой
Числовой
Денежный
Числовой
Числовой
Денежный
|
№ заказа(в составе двойного ключа)
Выбор ценности (в составе ключа)
Введите цену 1 ЕИ ценности
Кол-во одной ценности в заказе
Выбор единицы измерения кол-ва
Сумма ценности одного наим-ния
|
Длинное целое
Длинное целое
Действительное
Длинное целое
|
Свойству Шкала поля Количество присвойте значение 6. Столько значащих цифр может потребоваться, например, для учёта миллиграммов при вводе килограммов. Установите значение по умолчанию равным 1, это облегчит ввод данных при розничной продаже штучного товара. Поле Вес таблицы Единицы измерения по типу и размеру такое же, как поле Количество. Для поля Дата заказа можно на закладке Макет установить Маску ввода, например, в виде «99.99.99;0;_». Если она не задана, то при вводе даты год можно не указывать, по умолчанию будет введено текущее значение года.
Внимание! Проверьте, чтобы для всех внешних полей таблиц (они входят в состав отношений со стороны многие – ∞), например Код заказа и Ценность в таблице Составы заказов, поля Клиент и Сотрудник в таблице Заказы, была отключена уникальность – значение Совпадения не допускаются. Для этих полей должен быть установлен вариант Допускаются совпадения.
7. После создания всех таблиц следует задать схему данных, т. е. установить связи между таблицами и задать их свойства. Находясь в окне базы данных, нажмите кнопку Схема данных на панели инструментов. При необходимости нажмите кнопку Отобразить таблицу и двойным щелчком мыши на именах в списке таблиц в окне Добавление таблиц добавьте таблицы в схему.
Связи в схеме данных, подписанные цифрой 1 и знаком многие (∞), обеспечивают ссылочную целостность данных. При этом в случае удаления записей в таблицах допускается удаление только тех, на которые не осталось ссылок в других таблицах базы данных. Для установления связей (отношений) с контролем целостности перетащите мышью связываемое поле одной таблицы на связываемое поле другой. В открывшемся окне установите флажок Обеспечение целостности данных и нажмите кнопку Создать. При этом тип связи один ко многим (1:∞) или многие к одному (∞:1) отразится в схеме данных (см. рис. 1). Для связи между таблицами Города и Улицы установите флажок каскадное удаление связанных записей. Это существенно облегчит работу, так как появится возможность удалять города вместе с составом подчинённых им улиц. Это же свойство можно установить для связи между таблицами Клиенты и Типы клиентов. Тогда после подтверждения удаления одного из типов клиентов будут удалены все клиенты этого типа.
8. Во всех связях установите свойство каскадное обновление связанных полей, но действие этого эффективного механизма обновления данных будет проявляться только в связях, в которых связываемое ключевое поле не являет счётчиком. Например, при смене табельного номера сотрудника в таблице Сотрудники обновятся все ссылки на этот номер в поле Сотрудник таблицы Заказы. Изменить же в таблицах значение поля типа счётчик просто не удастся.
Для связи между таблицами Заказы и Составы заказов установите флажки каскадное обновление связанных полей и каскадное удаление связанных записей. Это существенно облегчит работу, т. к. появится возможность автоматически менять ссылки на код заказа в таблице Составы заказов при его изменении в таблице Заказы, удалять заказ вместе с входящим в него составом заказа. Установка свойства каскадное удаление связанных записей между таблицей Ценности и Составы заказов не будет оправдана, т. к. при реализации услуг, товаров и др. обычно нельзя изменять состав уже введённых документов – выписанных заявок, накладных, счетов-фактур или записей в книге продаж. Удаление отдельных записей в имеющихся документах и тем более записей во всех документах, связанных с заданной номенклатурой (ценностью), недопустимо. Установите это свойство для связей между таблицами Города и Улицы, Типы клиентов и Клиенты, Типы ценностей и Ценности.
9. Для установления рекурсивных связей между полями Подчиняется и Табельный № в таблице Сотрудники, а также между полями Базовая и Код в таблице Единицы измерения можно использовать два способа, которые в итоге приводят к одному результату.
Первый способ заключается в следующем. В окне схемы данных щёлкните мышью по одной из связей или по пустому месту между окнами таблиц. В открывшемся окне «Изменение связей» нажмите на кнопку «Новое…». Затем в окне «Создание» в полях со списками с надписями «Левая таблица» и «Правая таблица» выберите одну и ту же таблицу, например Сотрудники. Далее выберите «Левый столбец» и «Правый столбец. В рассматриваемом случае это будут поля Табельный номер и Подчиняется. Нажав кнопку Ок и установив свойства Обеспечение целостности данных и каскадное обновление связанных полей, нажмите кнопку Создать. Закрыв с сохранением окно схемы данных и открыв его вновь, вы получите ключ к пониманию второго способа построения рекурсивной связи.
В окне появится формально новая таблица Сотрудники_1. Её следует воспринимать в качестве тождественной таблице Сотрудники, т.е. можно считать совпадающей с ней. Построенная связь будет представлена на схеме связью между первичным ключом таблицы Сотрудники_1 – столбцом Табельный № – и внешним полем таблицы Сотрудники – столбцом Подчиняется. При попытке установить вторую связь между любыми двумя таблицами также происходит добавление таблицы с очередным «индексом» в конце её названия. В результате при таком механизме построения в окне схемы данных каждая таблица может быть связана не более чем с двумя формально различными таблицами.
Второй способ построения «рекурсии» будет связан с повторным добавлением таблицы Сотрудники в схему базы данных и построения связи между таблицами-близнецами посредством связывания столбцов Табельный номер и Подчиняется. При этом порядок соединения не имеет значения, т. к. любой из двух возможных вариантов построения связи после сохранения схемы и её повторного открытия приведёт к одному результату, описанному выше в первом способе построения: связаны будут столбцы Сотрудники. Подчиняется и Сотрудники_1.Табельный номер. Реализуйте второй способ при построении рекурсивной связи в таблице Единицы измерения.
10. После построения схемы данных введите в таблицы по 2 – 3 записи в порядке, учитывающем установленные связи. Например, только после ввода записи в таблицу Типы клиентов соответствующее значение кода типа клиента удастся ввести в поле Тип клиента таблицы Клиенты. Лишь после ввода записи в таблицу Города соответствующий код города удастся ввести в поле Город таблицы Клиенты (или поле можно оставить пустым).
11. Можно избавиться от ручного ввода кодов в таблицы, а в дальнейшем и в экранные формы. Для этого используют поля со списками и при вводе данных устанавливают, к примеру, значение числового кода для поля Клиент путём выбора из списка названия одного из клиентов из справочника Клиенты. Этим и объясняется то, что в качестве имени поля вместо Код клиента используется имя Клиент.
Подключение полей со списками произведём к внешним числовым полям: 1) Тип ценности, 2) Основная ЕИ в таблице Ценности; 3) Тип клиента, 4) Город, 5) Улица в таблице Клиенты; 6) Сотрудник и 7) Клиент в таблице Заказы; 8) Ценность и 9) ЕИ в таблице Составы заказов, 10) Город в таблице Улицы, 11) Подчиняется в таблице Сотрудники, 12) Базовая в таблице Единицы измерения. Для этого в окне конструктора выделите внешнее поле, содержащее числовой код. Перейдите в нижнем подокне Свойства поля на закладку Подстановка. На закладке Подстановка этих полей установите значения следующих параметров (табл. 2).
Таблица 2
Установка свойств подстановочных элементов управления
для заданных полей таблиц
Тип элемента управления
|
Поле со списком
|
Тип источника строк
|
Таблица или запрос
|
Источник строк
|
Таблица с названием, соответствующим имени поля. Например, Города для поля 9) Город в таблице Улицы, а в таблице Клиенты:
Типы клиентов для поля 3) Тип клиента,
Города для поля 4) Город,
Улицы для поля 5) Улица
|
Присоединённый столбец
|
1
|
Число столбцов
|
2 для полей 1-5 и 9-12, 3 для поля Сотрудник, 4 – для Клиент, 5 – для Ценность
|
Ширина столбцов
|
0; 2,5 для полей 1-5 и 9-12 или только «0»,
т. к. ширину последнего столбца можно не указывать. 0; 2,5; 1,5 – для Сотрудник,
0; 0; 2,5; 1,5 – для Клиент,
0; 0; 2,5; 0; 1,5 – для Ценность
|
В качестве разделителя между числами выступает точка с запятой, а запятая служит для разделения дробной и целой части десятичного числа. Нулевой размер первого столбца нужен для того, чтобы при раскрытии (распахивании) списка не выводился код справочника. А вот ширину последнего столбца из заданного числа столбцов в списке выбора (n-го из n) можно и не указывать. Например, ввести 0 вместо 0;2,5 . В дальнейшем все установленные параметры подключения (подстановки) для поля со списком можно будет изменить.
После подключения к полю таблицы поля со списком проверьте работу этого подстановочного элемента управления в режиме просмотра таблицы и при необходимости проверьте правильность установки его свойств.
12. После построения схемы данных и выполнения подстановки полей со списком можно в порядке, учитывающем установленные связи, заполнить таблицы отладочными данными, скопировать, удалить или отредактировать записи и, используя кнопки панели инструментов, выполнить другие простые операции с табличными данными: сортировку, фильтрацию, поиск значений в столбце.
Периодически сохраняйте базу данных на дискетах и других носителях информации. Это позволит снизить опасность потери данных или сведёт потери к минимуму при сбоях в сети, при активизации вирусов и в других случаях, которые могут привести к повреждению файла базы данных.
Лабораторная работа № 2
Построение запросов на выборку данных.
Другие типы запросов
Цель. Изучение средств и методов построения запросов на выборку данных. Запросы с параметрами. Использование в запросах рассчитываемых полей. Обзор и порядок использования других типов запросов: на изменение, на удаление, на сохранение и др.
Замечание. Эта работа не обязательна для построения ИС «Реализация ценностей», а служит для рассмотрения методов построения запросов. Работу можно пропустить и вернуться к ней позже в случае необходимости. Некоторые из рассматриваемых запросов используются далее при построении отчётов и диаграмм.
Для выбора информации из таблиц базы данных и представления её в табличном виде служат запросы на выборку. Они позволяют выбирать данные из одной или нескольких таблиц одновременно, группировать записи по некоторым критериям и получать итоги и расчёты по данным этих групп.
Порядок выполнения работы
1. Разберём на примере порядок и средства построения запросов. Создадим запрос с именем Заказы клиентов, выдающий полную информацию о клиентах по всем заказам. Базовой для запроса будет таблица Заказы, а используемым справочником – таблица Клиенты.
В окне базы данных выберите объекты Запросы, нажмите кнопку Создать и, используя режим Конструктор, откройте окно построителя запросов. В Access имеется также отдельная команда «Создание запроса в режиме конструктора». Добавьте в запрос таблицы Заказы и Клиенты, щёлкнув мышью дважды на их имени в окне Добавление таблиц. На панели инструментов «Связь» есть специальная кнопка Отобразить таблицу для вызова этого окна. Перетащите вниз из таблиц в строку с надписью Поле имена полей Дата заказа, Название, Телефон, Код заказа, Общая сумма. Это можно сделать также, щёлкнув дважды мышью на именах полей. Выбранные поля и будут содержаться в итоговой таблице. Установите сортировку по возрастанию по полям Дата заказа, Название и Общая сумма. Можно также установить Условие отбора для поля Общая сумма, например, >300, но это будет уже другой запрос – Заказы на сумму свыше 300 рублей. Закройте окно построителя и сохраните запрос. Выполните запрос. Вы увидите данные из двух таблиц в виде одной таблицы с выбранными полями.
Задание. Добавьте в запрос сортировку по названиям типов клиентов. Подсказка: для этого требуется добавить в запрос поле Тип клиента из таблицы Типы клиентов. Путём копирования созданного запроса, выполнения команды вставки и внесения изменений создайте упомянутый выше запрос.
2. Создайте запрос с параметрами, выбрав указанные выше таблицы и те же самые поля. Для этого сделайте копию запроса Заказы клиентов в буфер памяти и вставьте его в список под именем Заказы за период. В Условие отбора поля Дата заказа запишите оператор Between [Начальная дата] And [Конечная дата] , где [Начальная дата] и [Конечная дата] – параметры, которые будут запрошены позже, в ходе выполнения запроса, а в итоговой таблице будут выведены записи в диапазоне введённых дат. Выполните запрос и просмотрите результаты.
Задание. Создайте запрос о заказах клиентов из заданного города.
3. Создайте запрос с рассчитываемым полем и с именем Проверка суммы. Пусть требуется проверить правильность расчёта при вводе значения поля Сумма в таблице Составы заказов. Постройте запрос, включающий таблицы Составы заказов и Ценности, перенесите из них в строку Поле следующие поля: Название, Количество, Цена продажи, Сумма. Добавьте ещё одно – рассчитываемое – поле На сумму, записав в свободной колонке в строке Поле выражение На сумму: [Количество]*[Цена продажи]. При просмотре запроса значения из поля Сумма должны совпадать со значениями вычисляемого поля На сумму. Для корректировки используйте копирование.
4. Нажав на заголовке в верхней части окна любого из запросов правую кнопку мыши, выберите в контекстном меню команду Режим SQL, просмотрите текст запроса на структурированном языке запросов SQL (Structured Query Language).
5. Обзор и порядок использования других типов запросов: на создание таблицы, на изменение, на добавление, на удаление. Создайте копию любого запроса. Войдите в режим конструктора запросов. Нажав правую кнопки мыши или выбрав в меню команду Запрос, измените тип запроса на Создание таблицы…, Обновление, Добавление…, Удаление. Осознанно изменяйте содержание запросов и с особой осторожностью, чтобы не удалить ценные данные, выполняйте запросы.
Лабораторная работа № 3
|
