Системы управления базами данных Access 2007

 

1.По приведенной таблице создать базу данных  «Крупные реки России».

2. Для ввода  данных организуйте форму ввода и заполните созданную вами таблицу.

3. С помощью запросов определите:

а) реки, впадающие  в Карское море;

б) реки, исток  берущие на хребтах;

в) реки, с  длиной более 3000 км;

г) среднюю  площадь каждого км реки;

д) реки с годовым стоком более 100 км³ и длиной более 1000 км.

4. Создайте отчет по имеющейся информации о реках с группировкой по месту впадения  и сортировкой по длине реки.

§23. Проектирование многотабличной базы данных

Импорт данных,  индексация таблиц, маски ввода

Запустите Access: Пуск / Программы / Microsoft Access. После этого в появившемся диалоговом окне необходимо выбрать Новая база данных, указать имя и месторасположения создаваемого вами файла базы данных и нажать клавишу Создать.

Для создания базы данных «Компьютерный  магазин» нам понадобятся 5 таблиц: «Клиенты», «Заказы клиентов», «Товары  на складе», «Персонал», «Учет работы персонала». Таблицы «Клиенты» и  «Товары на складе» импортируем  из электронных таблиц.

Для этого в главном окне базы данных зайдите в меню Внешние данные и в блоке Импорт выберите кнопку Excel. В появившемся диалоговом окне найдите в списке нужный вам («Клиенты компьютерного магазина») и выберите пункт Импортировать данные источника в новую таблицу в текущей базе данных и нажмите кнопку ОК. После этого следует указать, что все импортируемые данные расположены на первом листе, и нажать кнопку Далее. Затем пометьте флажком, что в первой строке расположены заголовки полей импортируемой таблицы, нажмите Далее.

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

Рис. 3.19. Индексирование полей

 

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

 

Рис. 3.20. Определение ключевого  поля

 

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

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

Индексирование требует небольшого дополнительного места на диске  и незначительных затрат процессора на изменение индексов в процессе работы. После индексирования всех полей таблицы следует определить ключ (см. рисунок 3.20). Для таблицы «Клиенты» это будет № паспорта.

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

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

Остальные таблицы будем создавать  непосредственно в СУБД Access. Для этого их  структуру необходимо описать в конструкторе таблиц..

При этом обратите внимание, что сначала  описывается таблица «Персонал» (см. рисунок 3.21).

Рис. 3.21. Описание таблицы Персонал.

 

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

 

 

в

Рис. 3.22. Окно создания маски ввода.

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

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

Рис. 3.23. Изменение маски ввода

 

Обратите внимание, что знак «_»  в нашем случае предусматривает  ввод какой-то цифры и следовательно будет знаком заполнителя. После щелчка по строке «Проба:» у вас автоматически появится образец маски и имя заменится на «Определяется пользователем. Теперь можно нажать кнопку Далее. Где в появившемся диалоговом окне указать, что сохранить созданную маску нужно вместе со знаками, после чего нажимается кнопка Готово. Теперь при заполнении таблицы в поле «Телефон» у вас будет появляться соответствующая маска и пользователь не ошибется в каком виде вносить номер телефона. Самостоятельно создайте маску ввода для поля «Контактный телефон» в таблице «Клиенты»

В таблице «Учет работы» (см. рисунок 3.24) № паспорта рабочего делается средствами мастера подстановки – специального мастера, позволяющего в дальнейшем не вписывать данные в таблицу, а подставлять из созданного списка.

Рис. 3.24. Описание таблицы «Учет работы».

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

Аналогично с помощью мастера  подстановок оформляются поля  «№ паспорта клиента», «№ товара», «заказ оформил» в таблице «Заказы клиентов» (см. рисунок 3.25).  Обратите внимание, что в случае, когда список берется из имеющееся таблицы, между задействованными таблицами необратимо возникает связь. Причем изменения в структуру связанных полей возможно вносить только после разрыва этой связи.

Рис. 3.24. Описание таблицы «Заказы клиентов».

Связывание таблиц

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

Таблица 3.1

Виды связей между таблицами в базе данных

Характеристика полей связи  по видам	1→1	1→∞	∞→1	∞→∞
Поле связи основной таблицы (от которого идет связь)	является ключом	является ключом	не является ключом	не является ключом
Поле связи дополнительной таблицы (к которому идет связь)	является ключом	не является ключом	является ключом	не является ключом
Примечание	таблицы становятся равноправными, и  обеспечивается  взаимнооднозначное  соответствие записей в таблицах	одной записи основной таблицы соответствует  несколько записей дополнительной.	одной записи дополнительной таблицы  соответствует несколько записей основной.	нескольким записям основной таблицы  соответствует несколько записей дополнительной.

 

Для связывания таблиц «Компьютерного магазина» выберите в главном  окне базы данных на панели инструментов схему данных, добавьте в нее недостающие  таблицы, создайте и измените имеющиеся  связи, как показано на рисунке 3.26.

 

Рис. 3.25. Кнопка Схема данных

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

 

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

 

Рис. 3.27. Диалоговое окно изменения  связей

 

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

Использование элементов программирования  при вводе

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

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

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

 

Рис. 3.28. Работа с формой в режиме Конструктора

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

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

Рис. 3.29. Создание полей со списком

 

Для создания кнопки закрытия используйте  диалог кнопки.

Слева выберите Работа с формой, а справа Закрыть форму. После чего можно нажать Готово.

 

Самостоятельная работа

1. Заполните форму по персоналу для 2 продавцов, 1 продавца-консультанта, 1 менеджера по продажам, 2 шоферов, 1 уборщицы, 2 охранников, 1 директора, 1 маркетолога, 1 менеджера по кадрам и (12 человек).
2. Создайте подчиненную форму по отношению к имеющемуся ассортименту товаров для оформления заказов клиентов. В режиме просмотра (Открыть) внесите картинки товаров и заполните для каждого из имеющихся клиентов не менее 2 разных заказов.

§24. Создание запросов к многотабличной БД

Запрос – это элемент системы  управления базами данных, который  представляет собой специальным  образом описанное требование, определяющее состав производимых над БД операций по выборке, удалению и модификации  хранимых данных. Различают запросы  к таблицам (первичные) и непосредственно  к имеющимся запросам (вторичные).  Для подготовки запросов используют два основных языка запросов: QBE (язык запросов по образцу), SQL(структурированный  язык запросов).

 

 

 

 

Простые запросы на выборку полей

Рис. 3.30. Создание простого запроса

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

 

Рис. 3.31. Отбор полей при создании простого запроса

 

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

 

Простые запросы на подведение итогов

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

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