В начало

Темы лабораторных работ

1. Базы данных в Microsoft Access 2000

1.1. Как устроена база данных

Microsoft Access 2000, как и Microsoft Excel 2000, использует табличное представление данных, но предлагает иной способ работы с таблицами. Если Microsoft Excel 2000 ориентирован в основном на манипуляции с численными данными, на их получение, анализ, выявления зависимостей, графическое отображение, то Microsoft Access 2000 лучше приспособлен для ввода, поиска и отбора данных, в том числе сложно организованных и достаточно объемных. Все таблицы Microsoft Excel 2000 одинаковы, а ячейки таблиц могут содержать любые данные. В Microsoft Access 2000 всякая таблица имеет задаваемый пользователем набор поименованных столбцов (полей таблицы), предназначенных для хранения данных определенного типа. Каждая строка такой таблицы представляет собой запись, в полях которой могут храниться сведения о некотором объекте (действии, обстоятельстве - это уже зависит от способа интерпретации данных пользователем). Например, в таблице Книги могут быть поля Автор, Название книги, Год издания и Цена.

Если бы возможности Microsoft Access 2000 сводились к созданию, заполнению, просмотру и сортировке таблиц фиксированной структуры, эта система не имела бы никаких особенных преимуществ перед Microsoft Excel 2000 или даже таблицами Microsoft Word 2000. Принципиальное отличие Microsoft Access 2000 от этих средств заключается в возможности конструировать и выполнять запросы, результатами которых являются таблицы, по определенным правилам создаваемые на основе существующих (см. п. 1.5).

База данных Microsoft Access 2000 состоит из таблиц и запросов, форм для просмотра и редактирования таблиц, отчетов, которые задают способ оформления данных из таблиц в виде документов, а также макросов и модулей - программ, которые используются при обработке данных. Все это хранится в одном файле (с расширением .mdb), что довольно удобно при переносе базы данных с компьютера на компьютер.

1.2. Таблицы

Таблица базы данных Microsoft Access 2000 представляет собой совокупность записей (строк) фиксированной структуры. Каждая запись состоит из полей, в которых могут храниться данные определенных типов (см. Пример 1).

Пример 1

Таблица Книги состоит из записей со следующими полями:

Таблица Авторы состоит из полей:

Microsoft Access 2000 поддерживает также следующие типы данных: Счетчик (специальный тип данных для генерации уникальных ключей - см. п. 1.3), Логический (может принимать значения Истина или Ложь), Поле объекта OLE (например, для хранения документов Microsoft Word или рисунков), Гиперссылка (для работы с Internet).

1.3. Ключи

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

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

Поля типа Поле МЕМО, Поле объекта OLE и Гиперссылка не могут быть ключевыми.

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

Задание

Как Вы думаете, имеет ли смысл добавить поле типа Счетчик в таблицу Книги?

1.4. Индексы

По каждому полю, за исключением полей типа Поле МЕМО, Поле объекта OLE и Гиперссылка, можно построить индекс, что значительно ускоряет операции поиска, в которых критерием служит значение этого поля. Например, если Вы собираетесь выполнять поиск книг по авторам, имеет смысл проиндексировать таблицу Книги по полю Автор. Обобщенно можно сказать, что индекс представляет собой список, в котором для каждого значения проиндексированного поля указаны записи, в которых проиндексированное поле принимает это значение. При добавлении, изменении и удалении записей индекс необходимо обновлять. В Microsoft Access 2000 индекс строится и обновляется автоматически - достаточно указать, что поле является индексируемым.

Решая, по каким полям будут построены индексы, рекомендуется соблюдать следующие правила:

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

Ключевые поля обычно индексируют, но можно проиндексировать и не ключевое поле.

1.5. Запросы

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

Пример 2

Из таблицы Книги можно выбрать все записи, у которых в поле Автор находится значение Набоков, а значение в поле Год издания не превосходит 01.01.1966. Более сложные запросы позволяют конструировать одну таблицу из нескольких. Предположим, что кроме таблицы Книги, в базе данных есть еще таблица Авторы с полями Автор и Год рождения автора. Тогда из этих двух таблиц можно сформировать одну, с полями Автор, Год рождения автора, Название книги, Год издания и Цена. Для чего? Например, для того чтобы отсортировать книги по годам рождения их авторов или с помощью Microsoft Excel исследовать зависимость цены книги от года рождения ее автора. Замечательно, что с такими «синтетическими» таблицами можно делать примерно то же самое, что и со всеми остальными, в частности использовать их в других запросах.

Для описания запросов используется специальный язык программирования - SQL (Structured Query Language - язык структурированных запросов). В Microsoft Access 2000 предусмотрено визуальное средство создания запросов - конструктор, который преобразует «нарисованную» пользователем схему в предложение на SQL. Подробно о конструировании запросов см. п. 4.3.1.

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

1.6. Отношения между таблицами. Целостность данных

Пожалуйста, прочитайте очень внимательно. Отношение - это центральное понятие используемой в Microsoft Access 2000 реляционной модели данных. Разобравшись в том, что такое отношения между двумя таблицами и какими эти отношения бывают, Вы сможете в дальнейшем легко конструировать нужные Вам таблицы, запросы и отчеты. И, наоборот: без знакомства с этим понятием о полноценной работе с Microsoft Access 2000 не может быть и речи.

В предыдущих примерах (см. Пример 1 и Пример 2) мы имели дело с двумя таблицами: Книги и Авторы, - в которых хранились сведения о книгах и об их авторах соответственно. При этом предполагалось, что у каждой книги есть один автор. Поскольку поле Автор не было объявлено уникальным, в таблице Книги могло быть несколько записей с одинаковым значением в этом поле. Иначе говоря, у каждого автора могло быть несколько книг. Если перейти от книг и авторов к записям в таблицах, то можно сказать, что одной записи в таблице Авторы может соответствовать несколько записей в таблице Книги. Теперь мы готовы сформулировать понятие отношения между двумя таблицами. Соответствие записей таблицы B записям в таблице A называется отношением между таблицей A и таблицей B.

Как применяется понятие отношения между таблицами? Если некоторым записям таблицы A поставлены в соответствие некоторые записи таблицы B, то из них можно составить результирующую таблицу по следующим правилам:

• (a) каждая запись объединенной таблицы состоит из записи таблицы A и соответствующей ей записи таблицы B;
• (b) в объединенную таблицу включаются все записи, которые могут быть составлены по правилу (a).

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

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

Задание

Попробуйте сформулировать, что такое правое внешнее объединение таблиц.

Обычно различают три типа отношений между таблицами:

«Один ко многим» - каждой записи из первой таблицы может соответствовать много записей из второй. Очень распространенный тип отношения. Группа - студенты, фирма - сотрудники, родитель - дети, тип товаров - товары.

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

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

Каким образом отношения между таблицами реализуются в Microsoft Access 2000? Для установления соответствия записей используется условие равенства значения некоторого поля в одной таблице значению однотипного поля в другой. В таких случаях говорят, что таблицы связаны по этому полю.

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

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

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

Авторы в базе данных останутся связанные с ней записи в таблице Книги. Если таблица Книги связана с таблицей Авторы через поле Автор, то мы не должны допускать, чтобы при изменении значения этого поля у записи в таблице Авторы значение соответствующего поля у связанных с ней записей в таблице Книги оставалось неизменным. Это проблема, решение которой, вообще говоря, не является тривиальным, называется проблемой обеспечения целостности данных. Разные СУБД предлагают разные способы решения этой проблемы. В классических xBase-системах (dBASE, FoxPro) эту проблему нужно было решать «руками», - например, написав фрагмент кода, который после удаления записи из таблицы Авторы удалял бы связанные с ней записи из таблицы Книги.

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

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

Средства обеспечения ссылочной целостности будут работать описанным здесь образом только в том случае, если в таблице Авторы поле Автор будет уникальным ключом (см. п. 1.3) и по нему будет построен индекс (см. п. 1.4).

Если поле, по которому связаны таблицы, будет уникальным ключом в обеих таблицах, и для обеих таблиц по этому полю будут построены индексы, то получится отношение «один к одному».

1.7. Нормализация

Обладание большим словарным запасом и знание грамматики еще не означает умения сочинять стихи - у стихосложения есть свои законы, к тому же это искусство. Нечто подобное можно сказать о проектировании баз данных. В предыдущих пунктах мы рассмотрели основные структурные элементы, из которых в Microsoft Access 2000 строятся базы данных, и способы соединения этих элементов друг с другом. Теперь посмотрим, как собрать из них пригодную к использованию базу данных.

Процедура проектирования базы данных рассматривается нами не абстрактно, а на конкретном примере.

Соответствующие файлы баз данных находятся в папке Примеры_Access_2000_RUS\Нормализация.

Предположим, один букинист решил занести в базу данных сведения обо всех, имеющихся у него книгах. Недолго думая, он создал одну таблицу, каждая запись которой содержала полную информацию об одной из книг. В этой таблице были поля Автор, Дата рождения автора, Биография автора, Название книги, Аннотация, Объем, Издательство, Год издания и Цена (рис. 1).

Рисунок 1. Проектирование базы данных. Шаг 1

Примеры_Access_2000_RUS\Нормализация\Книги_1.mdb. Все данные собраны в одной таблице, что приводит к многократному повторению одной и той же информации. Это чревато противоречиями в данных и потерей не утративших актуальности данных (биографических сведений об авторах, аннотаций к литературным произведениям) при удалении строк (книг).

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

Рисунок 2. Проектирование базы данных. Шаг 2

Примеры_Access_2000_RUS\Нормализация\Книги_2.mdb. Информация об авторах и о книгах разделена на две таблицы, связанные по полю Автор. Тип связи - «один ко многим». Установлены режимы каскадного удаления и обновления. При удалении автора удаляются связанные с ним книги. Редактирование поля Автор в таблице Авторы не приводит к разрывам связей (значение поля Автор в связанных записях тоже обновляется). В таблице Книги в поле Автор невозможно ввести значение, отсутствующее в таблице Авторы. Дублирование аннотаций не устранено.

Теперь букинисту стало гораздо легче, но некоторые проблемы все же оставались. Так, продавая книгу, он терял сведения о литературном произведении, а именно, аннотацию. И если такая книга оказывалась у него вновь, то аннотацию приходилось вводить заново. Тогда неуемный библиофил разделил таблицу Книги еще на две: Произведения и Книги. В первую перешли поля Название книги и Аннотация, а во второй остались поля Объем, Издательство, Год издания и Цена, а также Название книги - для связи с таблицей Произведения. Кроме того, поскольку в таблице Книги не осталось полей, которые однозначно идентифицировали бы отдельные экземпляры книг (что важно для букинистов), туда было добавлено поле Код книги типа Счетчик.

Рисунок 3. Проектирование базы данных. Шаг 3

Примеры_Access_2000_RUS\Нормализация\Книги_3.mdb. Информация о книгах и произведениях разделена на две таблицы Произведения и Книги, связанные по полю Название книги. Тип записи - «один ко многим». У каждого автора может быть много произведений, а у каждого произведения - много книг, в которых оно напечатано. С другой стороны, у каждой книги может быть один автор, а в каждой книге - только одно произведение. Для обеих связей заданы режимы каскадного удаления и обновления.

Некоторое время букинист был очень доволен такой структурой базы данных, но когда он стал кроме художественной, заниматься еще и научно-технической литературой, снова начались сложности. Если раньше он мог считать, скажем, Ильфа и Петрова, одним автором, то теперь подобные условности принимать было решительно невозможно. У одной книги могло быть много авторов, а могло не быть авторов вообще (у ГОСТов, например). Вдобавок один и тот же автор мог принимать участие в написании разных книг в составе разных авторских коллективов. Ситуация требовала связать таблицы Авторы и Произведения отношением типа «многие ко многим», что и было сделано - в базу данных добавили таблицу Авторство (рис. 4).

Рисунок 4. Проектирование базы данных. Шаг 4

Примеры_Access_2000_RUS\Нормализация\Книги_4.mdb. Таблицы Авторы и Произведения связаны отношением типа «многие ко многим» с помощью таблицы Авторство. Произведение может не иметь автора вообще или иметь нескольких авторов. При удалении произведения или автора связанные с ними записи из таблицы Авторство будут автоматически удалены (каскадное удаление). При изменении названия книги в таблице Произведения или имени автора в таблице Авторство целостность данных нарушена не будет (каскадное обновление).

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

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

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

В примере встречаются отношения типа «один ко многим» (Произведения - Книги) и «многие ко многим» (Авторы - Произведения). Остается показать, для чего может понадобиться отношение «один к одному».

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