Тема 1.6. Системы управления базами данных

Тема 1.6. Системы управления базами данных

1.1.1.    Основные понятия баз данных

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

База данных (БД) – это поименованная и организованная (структурированная) совокупность взаимосвязанных данных, которые отражают состояние объектов конкретной предметной области и находятся под центральным программным управлением. База данных удерживает такой минимальный излишек информации, который позволяет удовлетворить запросы каждого из многочисленных пользователей[1].

Предметная область – часть реальной среды, которая отражается и описывается в базе данных.

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

Система управления базами данных (СУБД) – это комплекс программных и языковых средств, необходимых для создания баз данных, поддержания их в актуальном состоянии и организации поиска в них необходимой информации[2].

По технологии обработки данных базы данных делятся на централизованные БД и распределенные БД.

Централизованная БД хранится в памяти одной вычислительной системы. Эта вычислительная система может быть компонентом сети ЭВМ. Системы централизованных баз данных с сетевым доступом организуются по двум архитектурам: «файл-сервер» и «клиент-сервер».

Распределенная БД состоит из нескольких частей, которые хранятся в разных ЭВМ вычислительной сети.

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

Объектом называется элемент информационной системы, сведения о котором хранятся в базе данных. Иногда объект также называют сущностью (от англ, entity).

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

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

Экземпляр записи – реализация записи, содержащая конкретные значения полей.

Файл – это множество одинаковых по структуре экземпляров записей.

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

Вторичный ключ – это поле, значение которого может повторяться  в нескольких записях. По вторичному ключу можно найти несколько экземпляров записи. Вторичные ключи выполняют роль поисковых или группировочных признаков.

Ядром любой базы данных является модель данных.

Модель данных – совокупность структур данных и операций их обработки.

В теории систем управления базами данных выделяют модели трех основных типов: иерархическую, сетевую и реляционную.

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

Рис. 20. Схема иерархической модели данных

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

Рис. 21. Схема сетевой модели данных

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

В реляционной модели объекты и взаимосвязи между ними представляются с помощью таблиц (см. рис. 22).

Рис. 22. Схема реляционной модели данных

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

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

Таблицы находятся между собой в соответствующих отношениях (связях). Собственно говоря, термин «реляционная» происходит от английского relation – отношение.

Благодаря своей простоте и естественности представления реляционной модели на ПК она в настоящее время получила наибольшее распространение в системах автоматизированной обработки данных. Одной из самых популярных систем управления базами данных, построенных на основе реляционных моделей, является СУБД Microsoft Access.

1.1.2.   Работа со списками данных в Excel

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

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

Основными задачами при работе с базами данных являются:

-          организация ввода данных;

-          просмотр данных;

-          сортировка данных;

-          фильтрация данных;

-          подведение итогов.

Весь инструмент работы со списками (базами данных) в Excel сосредоточен в одном пункте меню – Данные.

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

Т.е. возможности, которые предоставляет Excel для работы со списками, это есть функции работы с базами данных.

База данных (БД) – это таблица, организованная следующим образом:

столбец – поле записи;

первая строка – имена полей;

остальные строки – записи БД;

Если заголовки граф расположены в нескольких строках электронной таблицы, то в качестве имен полей используется только последняя строка заголовка, именно эта строка и включается в диапазон списка (базы данных). Например, в таблице на рис.23 ячейки B2:E2 не включены в диапазон списка.

Рис. 23. Структура списка (базы данных)

Все поля базы данных должны иметь уникальные имена, т.е. не может быть в одной базе данных два поля с одинаковыми именами. Например, в таблице на рис.24 ячейки B3:D3 не могут быть строкой имен полей, т.к. имена полей в ячейках C3 и D3 совпадают.

Рис. 24. Пример неправильного оформления списка

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

Рис. 25. Пример неправильного форматирования шапки таблицы-списка

Организация ввода и просмотра данных.

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

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

-         Задание Критерия в форме позволяет просматривать через окно формы только записи, удовлетворяющие условиям критерия.

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

Фильтрация данных. Excel дает возможность выборочно работать с данными, удовлетворяющими определенным условиям. Отфильтровать список – значит, скрыть все строки за исключением тех, которые удовлетворяют заданным условиям отбора. Фильтрация списка дает возможность выводить на экран необходимое подмножество элементов списка. Закончив работу с ограниченным числом записей, можно снова вывести на экран весь список. Для фильтрации списков Excel предоставляет две команды: Автофильтр и Расширенный фильтр.

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

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

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

Расширенный фильтр. Команда Данные/Фильтр/Расширенный фильтр предоставляет пользователю более широкие возможности по созданию сложных критериев фильтрации. При работе с расширенным фильтром пользователь создает в специальном диапазоне условий критерий по определенным правилам. Результат фильтрации может быть показан на месте исходного списка или выведен в новое место этого же листа.

При работе расширенный фильтр опирается на три области:

Область данных – база данных или список.

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

-   критерии могут содержать константы, операции отношения, формулы;

-   в критериях могут применяться шаблоны (символы ? и *);

-   условия можно записывать в виде: <знак отношения><значение>. В критериях можно использовать все знаки отношения (>, >=, <, <=, <>), кроме знака = (равно), т.к. с этого символа в Excel начинаются формулы;

-   в диапазоне критериев можно ввести любое количество условий. Если условия находятся в одной строке, то они считаются соединенными логическим оператором И, если в разных – то логическим оператором ИЛИ.

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

Ниже приводятся примеры запросов (критериев поиска) для списка (базы данных), представленного на рис.26.

Пример 1. Отобрать записи и вывести фамилию, возраст и оклад тех сотрудников, у кого оклад больше 3000.

В критерий данного примера запишем условие в виде отношения: >3000.

Область данных – A3:D12.

Диапазон условий (F3:F4).

Целевая область (А15:С15):

Рис. 26. Область данных, диапазон условия и область вывода для Примера 1

После того, как сформированы все области, выбираем команду Данные/Фильтр/Расширенный фильтр. В появившемся диалоговом окне заполняем все поля, не вводя адреса диапазонов с клавиатуры, а выделяя соответствующие диапазоны на электронной таблице.

Рис. 27. Пример работы с окном Расширенный фильтр

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

Пример 2. Отобрать записи сотрудников, возраст которых 24 года.

В этом условии знак равно не ставится.

Диапазон условий (C21:D22):

Для данного запроса список мы отфильтруем на месте. Диалоговое окно Расширенный фильтр представлено на рис.25.

Чтобы вернуть исходную таблицу после фильтрации на месте необходимо выбрать команду Данные/Фильтр/Показать все.

Рис. 28. Фильтрация списка на месте

Пример 3. Отобрать записи тех сотрудников, у кого оклад больше 3000 и возраст меньше 35 лет.

В этом примере два условия соединяются логической операцией И (И Оклад>3000, И Возраст <35), следовательно, в диапазоне критерия они располагаются в одной строке.

Диапазон условий – четыре ячейки (C24:D25):

Пример 4. Отобрать записи сотрудников младше 25 и старше 50 лет.

В этом примере два условия соединяются логической операцией ИЛИ (ИЛИ Возраст<25, ИЛИ Возраст>50), следовательно, в диапазоне критерия они располагаются в разных строках одного столбца (одного поля – Возраст).

Диапазон условий – три ячейки (C26:С28):

Пример 5. Отобрать записи сотрудников, у которых телефон начинается на цифры 46.

В этом примере при записи условия используем шаблон 46* (* заменяет любое количество любых символов). То есть будут отобраны записи, в которых значение поля Телефон начинается на 46.

Диапазон условий (C30:С31):

Вычисляемые условия отличаются от обычных условий тем, что позволяют использовать значения, возвращаемые формулой.

Три правила записи вычисляемых условий:

-         Заголовок над вычисляемым условием должен отличаться от любого имени поля в списке: он может быть пустым или содержать произвольный текст. Если заголовок пустой, он все равно включается в диапазон условия.

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

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

Пример 6. Отобрать записи сотрудников, у которых оклад больше среднего оклада.

В этом примере используем вычисляемое условие. В левой части этого условия указываем адрес ячейки D4, которая является первой ячейкой с данными в столбце Оклад. В процессе фильтрации вместо этого адреса подставляются по порядку адреса всех остальных ячеек поля Оклад до конца списка, т.е. до ячейки D12.

Диапазон условий (C33:С34):

или

диапазон условий (C33:С34):

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

Синтаксис таких функций в общем виде:

<функция>(база данных; поле; диапазон условия)

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

Пример 7. В ячейке А21 определить минимальный возраст сотрудников, оклад у которых больше 4000.

Или (второй вариант)

Пример 8. Подсчитать количество сотрудников старше 40 лет.

Пример 9. Определить сумму окладов сотрудников младше 30 лет.

Подведение итогов. Агрегирование данных списков состоит в формировании промежуточных итогов, создании консолидированных и сводных таблиц.

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

Консолидация. Еще одним полезным средством обработки данных в Excel является консолидация. Консолидация – особый способ вычисления итогов для диапазона ячеек. Консолидируемые данные могут находиться на одном и том же или нескольких листах одной рабочей книги, а также различных рабочих книг. Все такие таблицы должны иметь абсолютно идентичную структуру. Соединение не является механическим. Итоговая таблица будет содержать только одну строку с ключевым полем, а числовые данные в ней будут суммами (или другими функциями – при консолидации доступны все функции статистических итогов: сумма, среднее, максимум, минимум и т.д.) всех строк объединенного подмножества.

Ключевым полем считается самое левое поле (колонка) таблицы или ее выделенной части. Консолидация осуществляется командой Данные/Консолидация.

Сводные таблицы являются весьма мощным и гибким инструментом анализа и трансформации данных в среде Microsoft Excel. С его помощью можно сделать практически любой «разрез» таблицы, получить итоги по любым данным. Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как Итоги, Фильтр, Консолидация. С помощью Сводной таблицы можно произвольным образом изменить структуру исходных данных, располагая заголовки строк и столбцов удобным образом, а также получить все необходимые промежуточные итоги и сортировки. Построение сводной таблицы осуществляется с помощью Мастера сводных таблиц, вызываемого командой Данные/Сводная таблица.

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

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

1 шаг – Определение типа источника данных;

2 шаг – определение адреса источника данных;

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

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

В поле Данные будут отображаться собственно данные – суммы, среднее, минимум, доли и т.д.

4 шаг – указание местоположения готовой сводной таблицы.

Контрольные вопросы

1.     Что такое база данных?

2.     Для чего предназначены СУБД?

3.     Что такое запись, поле записи в базе данных?

4.     Что такое первичный и вторичный ключи?

5.     Охарактеризуйте каждый из типов моделей данных.

6.      Что такое база данных в Excel? Перечислите основные задачи при работе с базами данных.

7.     Что такое фильтрация списка, и какие способы фильтрации Вы знаете?

8.     Каковы правила составления критерия при фильтрации списков?

9.     Каковы особенности работы с функциями категории «Работа с базой данных»?

10. Что такое промежуточные итоги и как их получить?

11. Охарактеризуйте операцию консолидации.

12.  Что такое сводные таблицы?

Скачано с www.znanio.ru