Базы данных (Excel, Access)

Рис.7

Заполнение столбцов «С» и «F» (то есть вычисляемых полей) этой таблицы производилось с помощью распространения формулы. Опишем этот процесс на примере столбца

«С».

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

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

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

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

базой данных в примере на рис.7 является диапазон А2:F9 (а не А3:F9). Рассмотрим на

11

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

1. Работа с данными в режиме «Форма».

Чтобы воспользоваться этим средством, нужно выделить весь диапазон А2:F9 (для этого следует установить указатель мыши в одну из угловых ячеек диапазона, нажать левую кнопку мыши и не отпуская ее переместить указатель в противоположный угол), затем последовательно выбрать пункт меню «Данные» и подпункт «Форма».

Рис.8

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

12

ниями, указанными в «Критериях». Например, можно потребовать от Excel, чтобы в «Форме» показывались только строки, содержащие сведения о студентах второго курса, которые учатся без троек.

2. Работа с данными в режиме «Автофильтр».

Снова выделим весь диапазон А2:F9, затем последовательно выберем пункты меню «Данные», «Фильтр» и «Автофильтр».

Рис.9

Справа от имен столбцов появились кнопки «выпадающего меню». При нажатии на такую кнопку (на рис.9 нажата кнопка поля «Year») показывается список всех различных значений в соответствующем столбце (а также некоторые другие возможности для фильтрации данных). После задания нужного значения, Excel покажет только строки, удовлетворяющие выбранному критерию. Подобным образом можно задавать нужные значения для фильтрации данных в разных столбцах, независимо друг от друга. В больших базах данных таким приемом можно сократить общий объем видимой информации до вполне обозримых размеров.

3. Использование специальных функций (функции базы данных).

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

Excel, которые специально предназначены для этих целей. Рассмотрим правила работы с этими функциями на примере одной из них, функции БИЗВЛЕЧЬ. Эта функция просматривает содержимое базы данных в поисках строки, удовлетворяющей установленному критерию. Если такая строка имеется, то из нее извлекается содержимое поля, указанного пользователем.

Все функции базы данных имеют три параметра:

•диапазон, задающий обрабатываемую базу данных;

•имя поля (столбца), значение которого обрабатывается функцией;

•диапазон, задающий критерий поиска.

Из этих трех параметров особого пояснения требует, по-видимому, только последний. Критерий поиска – это диапазон, содержащий имена полей (идентичные именам столбцов базы данных) и условия, которым должны эти поля удовлетворять. Как всегда, рассмотрим это понятие на примере (см. рис.10).

13

Рис.10

На рис.10 приведено решение задачи о поиске в базе данных, определяемой диапазоном А2:F9, студентки, о которой известно, что зовут ее Елена, что ей более 17 лет и что она учится на 2-м, 3-м или 4-м курсе. Нужно по этим данным установить фамилию студентки, а так же дату ее рождения. Критерий поиска для решения этой задачи построен в диапазоне

А11:D12. Первая строка диапазона – копии заголовков некоторых столбцов базы данных, а именно тех столбцов, которые «участвуют» в формулировке критериев отбора нужной строки базы данных. Вторая строка диапазона – условия, которым должна удовлетворять искомая запись. Условие в ячейке А12 означает, что в поле «Family» искомой строки должен содержаться текст, оканчивающий словом «Елена», а начинается этот текст произвольным количеством произвольных символов (именно это и обозначается символом «*»). Условие в ячейке В12 определяет, что в поле «Age» нужной строки должно содержаться число, большее 17. Наконец, в ячейках C12 и D12 устанавливаются два условия на одно и то же поле «Year», (которые должны выполняться одновременно).

Результаты работы функции БИЗВЛЕЧЬ показаны в ячейках А15 и В15. В обеих этих ячейках содержатся формулы с вызовом этой функции. Различаются эти формулы только вычисляемым значением (т.е. вторым параметром функции).В ячейке А15 вычисляемым значением является поле «Family», а в ячейке В15 – поле «BirthDay». Текст этих формул выглядит следующим образом.

-Ячейка А15: «=БИЗВЛЕЧЬ(А2:F9; ”Family”; А11:D12)».

-Ячейка B15: «=БИЗВЛЕЧЬ(А2:F9; ” BirthDay ”; А11:D12)».

Если функция БИЗВЛЕЧЬ найдет более одной строки, удовлетворяющей критерию поиска, то результатом работы функции будет сообщение «#ЧИСЛО!», если же ни одна строка базы данных не удовлетворяет заданному критерию, то сообщением будет текст

«#ЗНАЧ!».

В заключение, приведем в обзорном порядке еще несколько функций, ориентированных на работу с базами данных Excel. Функция БСЧЕТА позволяет подсчитать количество

14

записей, удовлетворяющих заданному критерию. Например, для приведенной на рис.10 базы данных можно подсчитать суммарное количество студентов, обучающихся на 2-м, 3-м и 4-м курсах, если в некоторую ячейку ввести текст следующей формулы: «=БСЧЕТА(A2:F9; ”Family”; C11:D12)». Данная формула использует в качестве критерия диапазон C11:D12, где установлены нужные условия на значения поля ”Year”. В качестве второго параметра можно указать заголовок любого поля, не содержащего «пустых» (т.е. не заполненных) значений. (в нашем примере выбрано поле ”Family”)

Функция ДМАКС позволяет найти запись с максимальным значением некоторого поля. Формула «=ДМАКС(A2:F9;"BirthDay";C11:C12)», например, определит дату рождения самого молодого из студентов, обучающихся на всех курсах, кроме первого. Аналогично, функция ДМИН позволяет найти запись с минимальным значением некоторого поля.

Наконец, функция БДСУММ находит сумму чисел, расположенных в заданном столбце, при этом учитываются только записи, удовлетворяющие нужному критерию. Например, с помощью формулы «=БДСУММ(A2:F9;F2;D11:D12)» можно определить сумму стипендий, выплачиваемых студентам первых 4-х курсов.

§ 4. Система управления базой данных Microsoft Access

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

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

4.1. Создание таблиц в СУБД Access

После запуска, Access. предлагает три варианта работы. Выберем вариант «Новая база данных», укажем диск и каталог, где будет храниться файл базы данных, а также введем его имя. (Впоследствии, для работы с уже созданной базой данной, нужно будет выбирать вариант «Открыть базу данных» с указанием ее месторасположения и имени). Появится окно «База данных», содержащее шесть закладок, соответственно шести типам объектов, которые может иметь БД. В закладке «Таблицы» выберем режим «Создать» и далее «Конструктор». Создание таблицы в режиме «Конструктор» предоставляет наиболее полные возможности по формированию ее структуры. Этот режим мы и рассмотрим.

Построим таблицу для хранения информации о студентах подобно той, которую мы рассматривали в п.3.4, со следующими изменениями: вместо поля «Age» введем поле «Phone», для хранения данных о номере телефона, а также добавим поле «Code» смысл и назначение которого выяснится позже. (Эта таблица будем в дальнейшем служить иллюстративным примером для пояснения рассматриваемого в данном параграфе материала). Примерный вид окна «конструктора» (с внесенными данными о структуре разрабатываемой нами таблицы) представлен на рис.11.

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

15

Рис.11

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

1. Основные типы данных, поддерживаемые СУБД Access.

1.1. Текстовый тип позволяет хранить и обрабатывать строки символов, аналогично тому, как это реализовано в «Excel». На рис.11 этот тип данных выбран для поля «Family». Разберем смысл некоторых атрибутов текстового поля.

Для атрибута «Размер поля» Access предлагает значение равное 50. Если самый длинный текст, который будет храниться в текстовом поле меньше (или больше) этого числа, то значение этого атрибута нужно соответственно изменить. Максимальное значение этого атрибута, допустимое в Access, равно 255 символам.

Атрибут «Обязательное поле» имеет значения «Да» или «Нет». Установим для поля «Family» значение «Да» у этого атрибута. Тогда Access не разрешит ввести запись с незаполненным полем «Family» (и тем самым будет осуществляться дополнительный контроль за действиями оператора, вводящего информацию в базу данных).

Еще один вид контроля осуществляется с помощью атрибута «Пустые строки». Значение «Нет» этого атрибута не позволит ввести запись, где соответствующее поле заполнено только пробелами.

Атрибуту «Индексированное поле» Access предлагает установить значение «Нет». Если содержимое таблицы нужно будет просматривать (либо выводить на печать), упорядо-

ченным по значению данного поля (вне зависимости то того, в каком порядке вводились данные), то нужно изменить значение этого атрибута либо на «Да (Допускаются совпаде-

16

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

Атрибуты «Обязательное поле» и «Индексированное поле» используются в вышеиз-

ложенном смысле не только для текстовых полей, но и для полей всех других типов.

1.2. Числовой тип данных. На рис.11 этот тип выбран для полей «Year», «Phone» и «Grant». По своему смыслу все эти поля должны хранить числа из существенно различных диапазонов, что устанавливается с помощью атрибута «Размер поля», который для числовых полей имеет следующие значения.

•Значение «Байт» означает, что в данном поле будут храниться «небольшие» целые числа, а именно, целые числа из диапазона от 0 до 255. В нашем примере такое значение «Размера поля» выбрано для поля «Year».

•Значение «Целое» предназначено для хранения целых чисел «побольше», а именно, чисел из диапазона от –32 768 до +32 767. (Отметим, что в нашем примере такой вариант не годился для поля «Phone», поскольку номера телефонов выходят за пределы указанного диапазона).

•Значение «Длинное целое» предназначено для хранения еще больших целых чисел, а именно, чисел из диапазона от –2 147 483 648 до [phone]. (Именно такое значение «Размера поля» установлено для поля «Phone»). «Длинные целые»

– это самые большие целые числа, которые позволяет хранить и обрабатывать современная реализация СУБД Access.

•Значение «С плавающей точкой (4 байт)» предназначено для хранения «дробных» чисел с относительно небольшой точностью: до 7 значащих десятичных цифр. (Для студенческой стипендии такая точность вполне достаточна, поэтому для поля «Grant» выбрано именно это значение «Размера поля»).

•Наконец, значение «С плавающей точкой (8 байт)» предназначено для хранения «дробных» чисел с точностью до 15 значащих десятичных цифр.

1.3. Тип данных Дата/время. Этот тип данных позволяет хранить в одном поле одновременно как календарную дату, так и время суток. С помощью атрибута «Формат поля» можно указать в каком виде Access должен показывать значение поля пользователю.

1.4. Логический тип данных. Этот тип данных удобно использовать в тех случаях, когда поле предназначено для хранения одного из двух возможных значений. С помощью атрибута «Формат поля» можно указать в каком виде Access должен показывать значение поля пользователю. На рис.11 логический тип данных выбран для поля «GoodLearned», а атрибут «Формат поля» задан таким образом, чтобы значения поля показывались пользователю в виде «Да/Нет».

1.5. Тип данных Денежный позволяет хранить числа в том виде, как это требуется в банковских операциях: до 15 десятичных цифр в целой части числа и до 4-х десятичных цифр – в дробной его части.

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

СУБД Access поддерживает еще 3 типа данных, которые используются относительно редко и потому не рассматриваются в настоящем пособии.

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

17

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

2. Первичный ключ таблицы реляционной базы данных.

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

Наличие двух таких записей означало бы очень маловероятную, но возможную ситуацию, что на одном и том же курсе обучаются два студента, с одинаковой фамилией, родившихся в один и тот же день, живущих вместе (и поэтому у них одинаковый телефон), одинаково хорошо (или плохо) успевающих и потому имеющих одинаковую стипендию! Если все же такая ситуация имеет место, и одному из этих студентов-близнецов, например, увеличили стипендию, то как определить: какая из двух одинаковых записей к какому «близнецу» относится и в какую из них вносить изменение в поле «Grant»? В принципе, можно, конечно, изменить поле «Grant» в «первой попавшейся» из этих двух записей (коль скоро они одинаковые, то не все ли равно какую из них выбрать!..). После такого действия записи будут уже различными (и таблица в целом будет удовлетворять первому принципу), но понятно, что поле «Grant» является не очень надежным критерием для идентификации конкретного студента (к тому же стипендию могут вновь уменьшить).

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

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

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

3. Нормализация базы данных.

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

18

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

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

Структура таблицы «Студенты»

Имя поля	Тип данных
StudentCode	Числовой (Длинное целое)
Family	Текстовый (20 символов)
Year	Числовой (Байт)
Speciality	Текстовый (100 символов)

После ввода информации в такую таблицу, ее содержимое может быть, например, та-

ким:

Содержимое таблицы «Студенты»

StudentCode	Family	Year	Speciality
991243	Андреев	4	Иранская филология
991247	Борисов	4	История Японии
991252	Васнецов	4	Иранская филология
991258	Гаврилов	4	Иранская филология
991267	Данилов	4	История Японии
…	…	…	…

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

1. Затрудняется ввод данных. Необходимо, чтобы наименование одной и той же специальности было одинаково введено во всех записях. Если, например, в одном случае в названии специальности «Иранская филология» слова разделены одним пробелом, в другом случае – двумя пробелами, а в третьем случае лишний пробел случайно появился перед первым словом (именно так введены данные в нашем примере), то при обработке данных (скажем, при выборке из таблицы фамилий студентов, обучающихся по этой специальности) СУБД будет трактовать указанные случаи как наименования различных специальностей.

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

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

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

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

19

Структура таблицы «Специальности»

Имя поля	Тип данных
SpecialityCode	Числовой (Длинное целое)
Speciality	Текстовый (100 символов)

Содержимое такой таблицы может быть, например, следующим:

Содержимое таблицы «Специальности»

SpecialityCode	Speciality
522603	Иранская филология
522604	История Японии
522605	Китайская филология
522606	История Турции
…	…

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

Структура таблицы «Студенты» в нормализованной базе данных

Имя поля	Тип данных
StudentCode	Числовой (Длинное целое)
Family	Текстовый (20 символов)
Year	Числовой (Байт)
SpecCode	Числовой (Длинное целое)

Содержимое таблицы студенты, тоже необходимо скорректировать:

Содержимое таблицы «Студенты» в нормализованной базе данных

StudentCode	Family	Year	SpecCode
991243	Андреев	4	522603
991247	Борисов	4	522604
991252	Васнецов	4	522603
991258	Гаврилов	4	522603
991267	Данилов	4	522604
…	…	…	…

В нормализованной базе данных (состоящей из двух таблиц) существенно упрощаются действия по вводу данных, так как название каждой специальности вносится (в таблицу «Специальности») только один раз, а вводить в таблицу «Студенты» код специальности (а не ее наименование) намного проще. Если теперь у какой-либо специальности изменится название, то это изменение нужно внести тоже только один раз в таблицу «Специальности», а в таблице «Студенты» вообще никаких изменений не потребуется. Наконец, общий объем дисковой памяти, требуемый для нормализованной базы данных, заметно меньше, чем он был до нормализации. (Если, допустим, таблица «Студенты» содержала информацию о 1000 студентах, то объем памяти, занимаемый ею до нормализации, составлял 125000 байт. После нормализации эта таблица будет занимать 29000 байт дисковой памяти и кроме того, таблица

20