учебник хахаева. Хахаев_ИТТ. Информационные таможенные технологии
 Скачать 1.27 Mb.
|
|
ключа отношения (ключевого поля или ключевой комбинации полей). Первичный ключ (Primary key, РК) – атрибут или комбинация атрибутов,, значения которого во всех строках различны. Первичные ключи могут быть логическими (естественными) и суррогатными (искусственными). Наличие ключа – требование целостности сущности. Естественный ключ содержится в самих данных (например, номер паспорта). Однако проблема состоит в том, что со временем многие характеристики атрибутов сущности (объектов предметной области) могут изменяться (паспорт может быть заменён). Искусственный ключ вводится дополнительно для каждого объекта таблицы как уникальное значение. На рис. 7 показан столбец «Код», в котором содержатся значения искусственного ключа. Внешний ключ (Foreign Key, FK) – дополнительное поле, позволяющее по значениям из текущей таблицы получать данные из другой таблицы по значению первичного ключа (связывание таблиц). Постреляционная модель данных представляет собой расширенную реляционную модель, в которой отменено требование атомарности атрибутов. Она использует трёхмерные структуры, позволяя хранить в полях таблицы другие таблицы («многомерная база данных»). Такой подход расширяет возможности по описанию сложных объектов реального мира и частично снимает необходимость связывания таблиц. Нормализация в реляционной модели данных В реляционной модели таблицы должны быть представлены в одной из нормальных форм. Как правило, выделяют следующие нормальные формы • Первая нормальная форма (1NF, 1НФ) • Вторая нормальная форма (2NF, 2НФ) • Третья нормальная форма (3NF, 3НФ) 21 • Нормальная форма Бойса-Кодда (BKNF, НФБК) • Четвёртая нормальная форма (4NF, 4НФ) • Пятая нормальная форма (нормальная форма проекции-соединения, 5NF, PJ/NF, 5НФ). Четвёртая и пятая нормальные формы в реальных задачах встречаются редко, потому при рассмотрении нормализации (приведения описания сущности в ту или иную нормальную форму) на этих НФ останавливаться не будем. В качестве примера рассмотрим отношение «Научная_работа», в котором приведены описания сотрудников, участвующих в научно-исследовательских работах (НИР) и выполняющих в рамках той или иной НИР определённые задачи. Каждый сотрудник имеет табельный номер и должность. Каждый сотрудник участвует в одной или нескольких НИР и за участие в каждой НИР получает отдельную надбавку, которая зависит только от должности сотрудника. Каждый сотрудник может в рамках одной НИР выполнять только одну задачу. Первая нормальная форма (1NF, 1НФ) для описанного отношения получается тогда, когда все атрибуты атомарны и существует ключ сущности. Ключом в данном случае является комбинация табельного номера и кода НИР. По табельному номеру однозначно определяется должность, от которой зависит надбавка. Схема данных отношения «Научная_работа» показана на рис. 8 (ключевая комбинация атрибутов обведена рамкой), а соответствующая таблица данных — на рис. 9. Рис. 8. Схема данных отношения «Научная_работа» Рис. 9. Таблица для отношения «Научная_работа». 22 Вторая нормальная форма (2NF, 2НФ) может быть получена, если таблица находится в 1НФ, повторяющиеся значения неключевых атрибутов выносятся во внешнюю таблицу (справочник). В рассматриваемом примере такими повторяющимися значениями являются должность и надбавка, определяемые табельным номером. Поэтому имеет смысл выделить отдельное отношение «Сотрудники» и упростить отношение «Научная_работа», превратив его в отношение «НИР_Задачи», как показано на рис. 10. Рис. 10. База данных по научной работе в 2НФ. Однако нужно заметить, что отношение «Сотрудники» устроено таким образом, что по табельному номеру однозначно определяется должность, а из должности — величина надбавки (должность зависит от табельного номера, а величина надбавки — от должности). Это означает, что величину надбавки можно узнать просто по табельному номеру. Такие зависимости называются транзитивными. Третья нормальная форма (3NF, 3НФ) получается, когда база данных находится в 2НФ, справочники организуются так, что исключаются транзитивные зависимости. В нашем случае отношение «Сотрудники» для исключения транзитивных зависимостей может быть разделено на отношения «Сотрудники1» и «Шкала_надбавок» (рис. 11). Нормальная форма Бойса-Кодда (BCNF, НФБК) получается, когда база данных находится в 3НФ и в отношениях исключаются возможные перекрывающиеся ключи. Если в первоначальной таблице для сущности «Научная_работа» добавить атрибут «Фамилия» (рис. 12), то получим схему данных, показанную на рис. 13. 23 Рис. 11. Устранение транзитивных зависимостей в отношении «Сотрудники». Рис. 12. Отношение «Научная_работа» с фамилиями сотрудников. Рис. 13. Схема данных отношения «Научная_работа» с перекрывающимися ключами. Тогда в базе данных изменяется справочник сотрудников (отношение «Сотрудники2») и отношения будут выглядеть следующим образом (рис. 14). 24 Рис. 14. Модификация базы данных при устранении перекрывающихся ключей. Типы данных в базах данных В таблице реляционной базы данных каждый атрибут сущности (объекта предметной области) должен иметь один и тот же тип данных для всех экземпляров сущности. Основные типы данных, используемые в реляционных базах данных, можно определить следующим образом: • Текст – строка от 1 до 256 символов • Большой текст – последовательность символов длиной до 64 кбайт • Число – целое или с плавающей точкой; • Дата/время – структура, состоящая из чисел и содержащая номер года, номер месяца, день, час, минуту и секунду • Логическое значение (Boolean) – возможны только два значения: TRUE (истина) и FALSE (ложь) • Большой двоичный объект (BLOB – Binary Large Object) – набор байтов произвольного размера. В поле такого типа могут храниться графические файлы (рисунки), фрагменты программного кода, объекты мультимедиа (аудио- или видео-фрагменты). Данные этого типа обрабатываются программами, внешними по отношению к базе данных. 25 Системы управления базами данных (СУБД) Системы управления базами данных (СУБД) – специальные программы для создания, изменения и поиска информации в базах данных. СУБД должна обладать следующими необходимыми возможностями: • Возможность определения структуры файла данных • Возможность проверки соответствия типов данных (целостности данных) при их вводе • Возможность поиска информации по заданным признакам и представления результатов поиска в удобном для пользователя виде • Возможность защиты данных от несанкционированного доступа • Возможность создания резервных копий данных и экспорта данных в файлы стандартных форматов Выбор СУБД для конкретного применения определяется следующими критериями: • Свойства базы (количество полей и записей в таблице, типы данных в полях таблицы, наличие и количество связанных таблиц) • Количество пользователей, которые должны одновременно работать с базой данных Исходя из этих критериев, можно определить классы программ, применимых для различных вариантов работы с базами данных. Простейшая база данных – список, с которым работает один пользователь — является одним из граничных случаев базы данных. Типовые характеристики базы: до 256 полей, до 65 000 записей, типы данных – текст, числа и дата/время. Для обработки такой базы можно использовать любую программу табличного процессора (электронную таблицу) из доступных офисных пакетов. Промышленная база данных является другим граничным случаем базы данных. Это база данных с многопользовательским сетевым доступом. Характеристики базы: размер таблицы ограничен только объёмом дискового пространства, в полях таблиц – все возможные типы данных, количество связанных таблиц ограничено только объёмом дискового пространства. Для обработки таких баз используются серверы баз данных (SQL-серверы). После появления персональных компьютеров возникла необходимость обрабатывать на них достаточно сложные базы данных, в то время как ресурсов для установки SQL-сервера не хватало. 26 Тогда появились программы, в какой-то мере обеспечивающие работу с многотабличными базами (СУБД для ПК). Они используются для обработки многотабличных баз одним пользователем и являются промежуточным вариантом между простейшей и промышленной базами данных. Типовые характеристики базы: размер таблицы – до 256 полей, до 65 000 записей, в таблице – все типы данных, одновременно может обрабатываться несколько таблиц (обычно до 16). Однако ресурсы современных ПК таковы, что вполне возможна установка одного или даже нескольких SQL-серверов, что обеспечивает гораздо более высокую надёжность и функциональность, чем использование СУБД для ПК. Современная тенденция переноса хранения данных в Интернет («облачные» сервисы), большое количество доступных для использования вариантов SQL-серверов с удобными пользовательскими интерфейсами к ним лишает этот класс программ всякого смысла. Запросы_как_элемент_базы_данных_В_общем_случае_можно_выделить_следующие_компоненты_базы_данных:•Таблицы'>Запросы как элемент базы данных В общем случае можно выделить следующие компоненты базы данных: • Таблицы содержат информацию о характеристиках объектов предметной области (записи). • Формы обеспечивают удобный ввод и просмотр информации в таблицах. • Запросы обеспечивают выбор нужной пользователю информации из таблиц. • Отчёты как результаты запросов, представленные в виде форматированных документов. Запрос – это команда на выбор информации из базы данных по определённым признакам (критериям) для программы, работающей с базой данных. • Фильтр – простейший вариант запроса, применяется в электронных таблицах и СУБД для ПК. • QBE-запрос (Query By Example) – применяется в СУБД для ПК и иногда при работе с серверами баз данных. • SQL-запрос (Standard/Structured Query Language) – основный вид запросов при работе с серверами баз данных (SQL-серверами). При рассмотрении запросов будем использовать уже описанную выше базу данных по научной работе, а также простую базу данных по поставкам товаров (рис. 15). 27 Рис. 15. Простая база данных по импортируемым товарам. Фильтры позволяют пользователю наглядно устанавливать признаки выбора данных из таблиц. Самый простой вариант фильтра — Автофильтр. Включается с помощью команд главного меню электронной таблицы (указатель активной ячейки должен находиться внутри диапазона, занимаемого базой данных). При включённом Автофильтре в строке с заголовками полей появляются значки раскрывающегося списка, по любому столбцу можно устанавливать значения, в результате будут показываться строки, в которых значения в этом столбце (поле) соответствует выбранному. Можно использовать Автофильтр одновременно для нескольких столбцов (полей). Признаком наличия фильтра является изменения вида кнопок раскрывающихся списков. На рис. 16 показан выбор товаров в ящиках, импортированных из Финляндии, реализованный с помощью Автофильтра. 28 Рис. 16. Применение Автофильтра к базе данных в электронной таблице. Стандартный (пользовательский) фильтр позволяет в диалоговом окне указать наименования полей, значения, которые нужно выбирать из этих полей (возможно использование символов подстановки, например * – любая последовательность символов, ? – любой одиночный символ), а также связывать условия для различных полей логическими функциями И и ИЛИ. Следует отметить, что нельзя использовать обе логические функции одновременно. Для каждого набора критериев можно использовать только один вариант логической функции. Пример стандартного фильтра для базы данных по импортируемым товарам показан на рис. 17. Рис. 17. Пример стандартного (пользовательского) фильтра. QBE-запросы (запрос по шаблону/образцу) применяются при работе с СУБД для ПК и при работе с различными интерфейсными программами для SQL-серверов. При создании запроса устанавливаются признаки (шаблоны) для поиска по любому количеству полей. В шаблонах используются символы подстановки (метасимволы), например * – любая последовательность символов, ? – любой одиночный символ. Если условия располагаются в одной строке запроса, они связаны логической функцией «И», а если в разных – то функцией «ИЛИ». На рис. 18 показан пример запроса по базе данных импортируемых товаров, в котором выводятся наименования товаром и страны происхождения 29 для налоговой нагрузки в диапазоне от 15% до 25%. Рис. 18. Пример QBE-запроса для БД по импортируемым товарам. На рис. 19 показан результат выполнения такого запроса по исходным данным рис. 15. Рис. 19. Пример результатов QBE-запроса. Если база данных находится в нормальной форме со справочниками (2НФ, 3НФ или НФБК), то для показа полного набора данных в запросе производится связывание таблиц по внешним ключам. На рис. 20 показан пример связывания таблиц для базы данных по научной работе, описанной выше. Запрос по связанным таблицам позволяет проводить некоторые вычисления. Пример запроса, показывающего общую сумму надбавок по всем проектам для сотрудников, имеющих надбавки по каждой НИР более 1000 руб, показан на рис. 21. 30 Рис. 20. Пример связывания таблиц в СУБД для ПК. Рис. 21. Пример QBE-запроса с вычислениями по связанным таблицам. Результат запроса на основе данных из приведённого выше примера показан на рис. 22. Рис. 22, Результат QBE-запроса по связанным таблицам. SQL-запросы используются в основном при работе с серверами баз данных (SQL-серверами), но могут использоваться и в некоторых СУБД для ПК (например Apache OpenOffice Base или LibreOffice Base). SQL – это стандартизованный язык запросов к базам данных. Запрос на SQL представляет собой небольшую программу, которая интерпретируется и выполняется SQL-сервером. Для примера, показанного на рис. 20, SQL-запрос может выглядеть следующим образом: 31 SELECT "НИР_Задачи"."Код_НИР", "НИР_Задачи"."Задача_НИР", "Сотрудники2"."Фамилия", SUM( "Шкала_надбавок"."Надбавка" ) FROM "Сотрудники2", "НИР_Задачи", "Шкала_надбавок" WHERE "Сотрудники2"."Таб_номер" = "НИР_Задачи"."Таб_номер" AND "Шкала_надбавок"."Должность" = "Сотрудники2"."Должность" AND "Шкала_надбавок"."Надбавка" > 10000 GROUP BY "НИР_Задачи"."Код_НИР", "НИР_Задачи"."Задача_НИР", "Сотрудники2"."Фамилия" Общие принципы создания SQL-запросов и их синтаксис описывается в семействе стандартов ISO/IEC 9075. За время после принятия первого стандарта на SQL было принято несколько спецификаций: • SQL-86 • SQL-92 • SQL-99 • SQL:2003 • SQL:2008. Сейчас для общего описания SQL действует ISO/IEC 9075-1:2008 (SQL-2008). Каждая следующая версия включает в себя все возможности предыдущей версии стандарта. Таким образом, при работе с современными SQL-серверами можно использовать SQL-92, но нет гарантий, что использование SQL-2008 возможно для всех вариантов SQL-серверов. Поскольку SQL-запросы являются по сути программами (сценариями), то их создание и выполнение легко автоматизировать, их можно включать в какие-то прикладные автоматизированные системы и использовать даже при работе с SQL-серверами, связь с которыми осуществляется через компьютерные сети. Выделяются следующие варианты SQL-запросов • Запросы на создание/удаление БД • Запросы на подключение к БД • Запросы на создание/удаление таблиц • Запросы на выборку • Запросы на изменение • Запросы на создание/удаление привилегий Несмотря на наличие нескольких диалектов SQL, зная принцип и имея справочник всегда можно правильно построить запрос. Запросы на создание/удаление БД обычно требует привилегий администратора базы данных. Для создания БД с именем baza1 используется команда CREATE DATABASE baza1; 32 а для удаления БД — команда DROP DATABASE baza1; (наличие символа «;» в конце строки обязательно). При составлении SQL-запросов принято записывать ключевые слова языка прописными буквами, а имена объектов и значения переменных — строчными. Запрос на подключение к БД может выполняться с привилегиями любого пользователя и применяется при обращении к серверу через компьютерную сеть CONNECT TO baza1 USER vasya/12345; Здесь строка после ключевого слова USER — имя и пароль пользователя базы данных. Пароль передаётся в открытом виде, поэтому сеансы связи с SQL-серверами рекомендуется шифровать (см. далее). Запросы на создание таблиц описывают все поля и из типы, а также устанавливают (при необходимости) значения по умолчанию, если значения не могут отсутствовать (NOT NULL). Например, для таблицы «Шкала_надбавок» с искусственным ключом запрос на создание будет выглядеть примерно так: CREATE TABLE Шкала_надбавок (id INT(4) UNSIGNED NOT NULL, Должность CHAR(20) DEFAULT 'Ассистент' NOT NULL, Надбавка INT(6) DEFAULT '0' NOT NULL, PRIMARY KEY(id)); Запросы на удаление таблиц требует только указания имени таблицы. Полезно также использовать условие «IF EXIST», которое обеспечивает корректную обработку попытки удаления несуществующей таблицы: DROP TABLE IF EXIST Шкала_надбавок; Запросы на создание пользователей требуют привилегий администратора БД (или сервера БД). В запросе указывается база данных, набор привилегий (перечисляется через запятую) и параметры учётной записи пользователя — имя, узел сети Интернет, на котором этот пользователь зарегистрирован, а также пароль. Пароль указывается в открытом виде. Пример запроса на создание пользователя: GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON scienceworks.* TO vasya@localhost IDENTIFIED BY '12345'; Запросы на удаление пользователей не имеют каких либо особенностей и сложностей. Например DROP USER vasya; |