Основы правил проектирования базы данных

Основные элементы реляционных баз данных.

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

В
любой таблице можно выделить такие
элементы как записи и поля.

Свойства
полей базы данных.

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

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

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

• Размер
поля — определяет предельную длину (в
символах) данных, которые могут размещаться
в данном поле.

• Формат
поля — определяет способ форматирования
данных в ячейках, принадлежащих полю.

• Маска
ввода — определяет форму, в которой
вводятся данные в поле (средство
автоматизации ввода данных).

• Подпись
— определяет заголовок столбца таблицы
для данного поля (если подпись не указана,
то в качестве заголовка столбца
используется свой-ство Имя поля).

• Значение
по умолчанию — то значение, которое
вводится в ячейки поля автоматически
(средство автоматизации ввода данных).

• Условие
на значение — ограничение, используемое
для проверки правильности ввода данных
(средство автоматизации ввода, которое
используется, как правило, для данных,
имеющих числовой тип, денежный тип или
тип даты).

• Сообщение
об ошибке — текстовое сообщение, которое
выдается автоматически при попытке
ввода в поле ошибочных данных (проверка
ошибочности выполняется автоматически,
если задано свойство Условие на значение).

• Обязательное
поле — свойство, определяющее
обязательность заполнения данного поля
при наполнении базы;

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

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

Современная база данных

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

Таблицы Excel — ничем не отличаются от Oracle и MySQL в контексте прямоугольных (реляционных) конструкций: столбцы и строки = одна ячейка на пересечении имени столбца (поля) и индекса выборки (строка). Если не учитывать меру и объем ручного труда, то, благодаря развитым средствам объединения ячеек по вертикали и горизонтали, Excel опережает даже Oracle!

Excel, по его основной идее, никогда «не светит» динамика, функционал Oracle, и перенести что-то с одного листа на другой «по остаткам» он не может. Здесь Oracle перспективнее, но ее соображения по вопросам миграции больших объемов информации и объединению формализованных позиций из различных источников оставляют желать лучшего. Здесь MySQL перспективнее: она не ставит перед собой глобальных задач, но свою работу исполняет отменно.

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

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

Знания и опыт современных систем управления базами данных (СУБД) учитывают не только вопросы надежности работы, достоверности данных, регламентации доступа и решение вопросов безопасности, но и дают возможность отслеживать негативное внешнее влияние, анализировать возможные атаки и попытки умышленного нанесения вреда.

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

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

Логическое проектирование БД

Замечание 4

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

Определение 1

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

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

Замечание 5

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

Запросы к базе данных и отчёты

Запросы:

• доступность выбираемого в селекторе препарата в выбираемой в селекторе аптеке на данный момент времени;
• группы препаратов, которых нет в выбираемой в селекторе аптеке на данный момент времени;
• аптеки, в которых есть дефицит любого препарата по введённой дате;
• покупки препаратов, которые есть на витринах аптек по введённой дате с указанием препаратов и аптек;
• клиенты, совершившие покупки в выбранной в селекторе аптеке по введённой дате.

Отчёты:

• о количестве покупок со списком аптек по введённой дате с указанием сумм;
• о продажах выбранного препарата во всех аптеках по введённой дате;
• о проданных препаратах стоимостью более 150 рублей по введённой дате;
• о препаратах, которые есть на витрине во всех аптеках;
• о клиентах, зарегистрированных в определённый интервал времени.

Поделиться с друзьями

Реляционные базы данных и язык SQL

2.4. Microsoft Access 2007

2.4.2. Создание базы данных (таблиц и связей между ними) в Access 2007

Рассмотрим этапы создания БД «Деканат» с помощью СУБД Access 2007. Сначала составляем модель «сущность – связь» для базы данных «Деканат». Этапы проектирования модели «сущность – связь» изложены в разделе «Создание БД. Этапы проектирования».

После создания модели запускаем приложение Access 2007. Открывается окно приложение Access 2007 на странице Приступая к работе с Microsoft Access 2007. В разделе Новая пустая база данных щелкаем на пиктограмме Новая база данных. В правой части окна появится информация об имени файла и указана директория для его хранения. По умолчанию имя файла — База данных1.accdb.

Изменить имя файла и путь к директории для хранения файла БД можно в окне «Файл новой базы данных» щелкнув на пиктограмме «Поиск расположения для размещения базы данных». Установив имя файла — Деканат_2007.accdb и требуемое имя директории в окне «Файл новой базы данных», надо щелкнуть на кнопке ОК, окно закроется.

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

Рис. 1.

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

Рис. 2.

Откроется окно Сохранение, в котором надо указать имя Группы студентов и нажать кнопку ОК.

Рис. 3.

Откроется таблица Группы студентов в режиме Конструктор

Рис. 4.

Создаем структуру таблицы Группы студентов. В первую строку колонки «Имя поля» вводим код группы студентов (КодГруппы) и нажимаем клавишу Enter. Курсор переместится в колонку Тип данных. Access по умолчанию назначает тип данных — Счетчик. Нажимаем клавишу Enter, при этом курсор переместится в колонку Описание, при необходимости вводим описание данных.

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

Рис. 5.

Затем создаем структуры остальных трех таблиц в соответствии с характеристиками таблиц-объектов Студенты, Дисциплины, Успеваемость. Обязательно соблюдайте указанную последовательность создания структуры таблиц.

Необходимо отметить, что в структуре таблицы «Студенты» для поля КодГруппы (вторичный ключ) установите значение Индексированного поля: Да (Совпадения допускаются) и тип данных — мастер подстановок. В структуре таблицы «Успеваемость» для поля КодСтуденты (вторичный ключ) и поля КодДисциплины (вторичный ключ) установите значение Индексированного поля: Да (Совпадения допускаются) и тип данных — мастер подстановок.

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

Рис. 6

Рис. 7

Рис. 8

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

Далее >>> 2.4.3. Установка связей между таблицами в СУБД Access 2007

В мир плавных форм от точных прямоугольников

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

Мощь и объективность реляционных отношений — бесспорна, но разве динамика колонок и строк наносит ущерб их репутации? Таблица — это просто данные, которые могут иметь шапку (список колонок) или не иметь строк. Пусть таблица — это просто совокупность данных, не обязательно именованная.

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

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

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

• входной информационный поток;
• преобразования и движение информации внутри базы данных;
• выборка данных для использования.

Понятия структуры таблицы отсутствует. Нет ни строк, ни столбцов. Есть абстракция — данное, определенной структуры удовлетворяющее конкретной точке в алгоритме. Если более конкретно, то функция обработки информации требует определенную информацию в конкретном объеме.

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

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

Типы отношений

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

Мощность (cardinality), или количество элементов, отношения показывает, сколько экземпляров одной сущности может соотноситься с экземпляром другой сущности. Тот факт, что бинарное отношение отражает взаимоотношения между двумя сущностями, вовсе не означает, что между ними всегда существует отношение типа “один к одному”.

Отношения между сущностями могут представлять собой и отношения типа “один к од- ному”, и “один ко многим”, и “многие ко многим” или отношения еще какого-то другого типа. Чаще всего встречаются отношения следующих типов (при условии наличия двух сущностей, A и B).

• Один ко многим. В таких отношениях каждый экземпляр сущности A может иметь отношение с несколькими членами другой сущности B. Например, сущность под названием Клиент может брать много книг из библиотеки, но каждая книга за раз может выдаваться только одному единственному Клиенту. Соответственно получается, что между сущностями Клиент и Книга должно существовать отношение типа “один ко многим”. Разумеется, такое отношение может и не существовать при наличии Клиента, который еще не брал никакой Книги. То есть фактически отношение должно гласить следующее: “один Клиент может брать ноль, одну или более Книг”.
• Один к одному. В таких отношениях только один экземпляр любой из сущностей может иметь отношение с экземпляром другой сущности. Например, у каждого человека может быть только один действительный номер карточки социального страхования (Social Security Number — SSN), а каждый номер карточки социального страхования может ссылаться только на одного человека.
• Многие ко многим. В таких отношениях каждый экземпляр сущности A может иметь отношение с одним и более экземплярами сущности B, а каждый экземпляр сущности B — с одним и более экземплярами сущности A. В качестве примера возьмем сущность под названием Кинозвезда и сущность под названием Кинофильм. Каждая кинозвезда может сниматься в нескольких Кинофильмах, и в каждом Кинофильме может принимать участие несколько Кинозвезд. В реальной жизни отношения типа “многие ко многим” обычно разбиваются на более простые отношения типа “один ко многим”, которые, как сложилось, являются наиболее распространенной формой отношений между сущностями.

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

Выбор системы управления и программных средств БД

От выбора системы управления БД зависит практическая реализация информационной системы. Наиболее значимыми критериями в процессе выбора становятся параметры:

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

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

Нормальные формы

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

Вначале введем некоторые термины. Основной структурой для хранения данных в базе данных является таблица. Имена столбцов таблицы называются полями таблицы. Строки таблицы называются записями таблицы. Содержимое ячейки таблицы на пересечении строки и столбца называется значением поля данной записи.

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

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

Таблица находится во 2-ой нормальной форме, если она находится в 1-ой нормальной форме и в ней выбрано одно поле со следующими свойствами:

• Значение поля не пусто для любой записи.
• Значение поля уникально для любой записи так, что по этому значению можно однозначно определить запись.

Такое поле называется ключевым полем таблицы или первичным ключом таблицы.

Замечание 2

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

Для рассматриваемого примера о товарах и поставщиках нужно спроектировать две таблицы:

Таблица «Товары»

Таблица «Поставщики»

Краткие сведения о субд Access

База
данных в Access
представляет собой единый большой
объект, который объединяет такие
составляющие, как таблицы, отчеты,
запросы, формы и т.д. и позволяет хранить
их в одном файле.

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

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

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

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

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

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

Фундаментальные знания и жесткие конструкции

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

Алгоритм не может быть фиксированным. Все должно быть определено в динамике. Заслуги квалифицированных разработчиков несомненны, но лежат они вовсе не в изящных формах решений от Oracle, MySQL или ограниченного в возможностях Access. Иная таблица Excel может обеспечить динамичный контент и не требовать участия программиста более менее приличное время после завершения работ.

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

Этапы или коллектив: баланс приоритетов

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

• системность;
• этапность;
• обратная связь с любого момента времени, до самой начальной позиции.

Эти положения абстрактны, но присутствуют в любой теоретической и практической технологии создания эффективной базы данных.

Никакая технология не развивается сама по себе, ее двигают вперед люди

Квалификация коллектива разработчиков имеет важное значение. Информационная модель базы данных — это не только каркас, но и информационные потоки

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

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

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

Выполнить работу нужно сегодня, новая задача, современное оборудование, свежие технологии, …

Возможен обратный вариант. Есть Excel и Access и «обильные» данные в этих форматах с давних времен, когда еще был жив и здравствовал Windows for Workgoups. Частично остались данные dBase и Quattro. Сегодня эти слова уже забылись, но информация осталась, она востребована и нуждается в извлечении и формировании новых представлений.

Инфологическое проектирование

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

• описание типов объектов,
• ограничения целостности, связанные с описанным типом,
• процессы, приводящие к эволюции предметной области – переходу её в другое состояние.

Инфологическую модель можно создавать с помощью нескольких методов и подходов:

1. Функциональный подход отталкивается от поставленных задач. Функциональным он называется, потому что применяется, если известны функции и задачи лиц, которые с помощью проектируемой базы данных будут обслуживать свои информационные потребности.
2. Предметный подход во главу угла ставит сведения об информации, которая будет содержаться в базе данных, при том, что структура запросов может не быть определена. В этом случае в исследованиях предметной области ориентируются на её максимально адекватное отображение в базе данных в контексте полного спектра предполагаемых информационных запросов.
3. Комплексный подход по методу «сущность-связь» объединяет достоинства двух предыдущих. Метод сводится к разделению всей предметной области на локальные части, которые моделируются по отдельности, а затем вновь объединяются в цельную область.

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

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

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

Для каждой отдельной сущности выбираются атрибуты (набор свойств), которые в зависимости от критерия могут быть:

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

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

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

Анализ предметной области

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

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

В каждой из аптек регистрируется покупка каждой единицы препарата и все покупки, совершённые одним клиентом в одной аптеки в один момент времени, прикрепляются к корзине покупок.

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

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

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