Хронологические данные в SQL
1.
Этапы проектирования базы данных:
1. Определение цели создания базы данных.
2. Определение таблиц, которые должна содержать база данных.
3. Определение необходимых в таблице полей.
4. Задание первичного ключа для каждой таблицы.
5. Определение связей между таблицами.
6. Обновление структуры базы данных.
7. Добавление данных и создание других объектов базы данных.
1. Определение цели создания базы данных:
На первом этапе проектирования базы данных необходимо определить цель создания базы данных, основные ее функции и информацию, которую она должна содержать. То есть нуж-но определить основные темы таблиц базы данных и информацию, которую будут содержать поля таблиц.
База данных должна отвечать требованиям тех, кто будет непосредственно с ней работать. Для этого нужно определить темы, которые должна покрывать база данных, отчеты, которые она должна выдавать, проанализировать формы, которые в настоящий момент используются для записи данных, сравнить создаваемую базу данных с хорошо спроектированной, подоб-ной ей базой.
2. Определение таблиц, которые должна содержать база данных:
Одним из наиболее сложных этапов в процессе проектирования базы данных является разра-ботка таблиц, так как результаты, которые должна выдавать база данных (отчеты, выходные формы и др.) не всегда дают полное представление о структуре таблицы.
При проектировании таблиц вовсе не обязательно использовать Microsoft Access. Сначала лучше разработать структуру на бумаге. При проектировке таблиц рекомендуется руково-дствоваться следующими основными принципами:
- Информация в таблице не должна дублироваться. Не должно быть повторений и между таблицами.
Когда определенная информация храниться только в одной таблице, то и изменять ее при-дется только в одном месте. Это делает работу более эффективной, а также исключает воз-можность несовпадения информации в разных таблицах.
- Каждая таблица должна содержать информацию только на одну тему.
Сведения на каждую тему обрабатываются намного легче, если содержатся они в независи-мых друг от друга таблицах. Например, адреса и заказы клиентов хранятся в разных табли-цах для того, чтобы при удалении заказа информация о клиенте осталась в базе данных.
3. Определение необходимых в таблице полей:
Каждая таблица содержит информацию на отдельную тему, а каждое поле в таблице содер-жит отдельные сведения по теме таблицы. Например, в таблице с данными о клиенте могут содержаться поля с названием компании, адресом, городом, страной и номером телефона. При разработке полей для каждой таблицы необходимо помнить:
- Каждое поле должно быть связано с темой таблицы.
- Не рекомендуется включать в таблицу данные, являющиеся результатом выражения.
- В таблице должна присутствовать вся необходимая информация.
- Информацию следует разбивать на наименьшие логические единицы (Например, поля «Имя» и «Фамилия», а не общее поле «ФИО»).
4. Задание первичного ключа для каждой таблицы:
С тем чтобы Microsoft Access мог связать данные из разных таблиц, например, данные о кли-енте и его заказы, каждая таблица должна содержать поле или набор полей, которые будут однозначно идентифицировать каждую запись в таблице. Такое поле или набор полей назы-вают первичным ключом.
5. Определение связей между таблицами:
После распределения данных по таблицам и определения ключевых полей необходимо определить связи между таблицами. Для этого надо служит кнопка Схема данных. Связи нужны для того, чтобы обеспечить синхронное изменение одноименных полей в разных таблицах. Самый распространенный вид связи - «один-ко-многим».
6. Обновление структуры базы данных:
После проектирования таблиц, полей и связей необходимо еще раз просмотреть структуру базы данных и выявить возможные недочеты. Желательно это сделать на данном этапе, пока таблицы не заполнены данными.
Для проверки необходимо ввести несколько записей в каждую таблицу и посмотреть, отве-чает ли база данных поставленным требованиям. Рекомендуется также создать черновые вы-ходные формы и отчеты и проверить, выдают ли они требуемую информацию. Кроме того необходимо исключить из таблиц все возможные повторения данных.
7. Добавление данных и создание других объектов базы данных:
Если структуры таблиц отвечают поставленным требованиям, то можно вводить все данные. Затем можно создавать любые запросы, формы, отчеты, макросы и модули.
2.
Модель сущность-связь (ER-модель) - модель данных, позволяющая описывать концептуальные схемы предметной области.
ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями.
Во время проектирования баз данных происходит преобразование ER-модели в конкретную схему базы данных на основе выбранной модели данных (реляционной, объектной, сетевой или др.).
ER-модель представляет собой формальную конструкцию, которая сама по себе не предписывает никаких графических средств её визуализации.
Атрибут сущности - это именованная характеристика, являющаяся некоторым свойством сущности. Наименование атрибута должно быть выражено существительным в единственном числе (возможно, с характеризующими прилагательными).
Примерами атрибутов сущности "Сотрудник" могут быть такие атрибуты как "Табельный номер", "Фамилия", "Имя", "Отчество", "Должность", "Зарплата" и т.п.
Ключ сущности - это неизбыточный набор атрибутов, значения которых в совокупности являются уникальными для каждого экземпляра сущности. Неизбыточность заключается в том, что удаление любого атрибута из ключа нарушается его уникальность. Сущность может иметь несколько различных ключей.
Связь - это некоторая ассоциация между двумя сущностями. Одна сущность может быть связана с другой сущностью или сама с собою. Связи позволяют по одной сущности находить другие сущности, связанные с нею.
Каждая связь имеет два конца и одно или два наименования. Наименование обычно выражается в неопределенной глагольной форме: "иметь", "принадлежать" и т.п. Каждое из наименований относится к своему концу связи. Иногда наименования не пишутся ввиду их очевидности.
Каждая связь может иметь один из следующих типов связи:
· Связь типа один-к-одному означает, что один экземпляр первой сущности (левой) связан с одним экземпляром второй сущности (правой). Связь один-к-одному чаще всего свидетельствует о том, что на самом деле мы имеем всего одну сущность, неправильно разделенную на две.
· Связь типа один-ко-многим означает, что один экземпляр первой сущности (левой) связан с несколькими экземплярами второй сущности (правой). Это наиболее часто используемый тип связи. Левая сущность (со стороны "один") называется родительской, правая (со стороны "много") - дочерней.
· Связь типа много-ко-многим означает, что каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, и каждый экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности. Тип связи много-ко-многим является временным типом связи, допустимым на ранних этапах разработки модели. В дальнейшем этот тип связи должен быть заменен двумя связями типа один-ко-многим путем создания промежуточной сущности.
Каждая связь может иметь одну из двух модальностей связи:
· Модальность "может" означает, что экземпляр одной сущности может быть связан с одним или несколькими экземплярами другой сущности, а может быть и не связан ни с одним экземпляром.
· Модальность "должен" означает, что экземпляр одной сущности обязан быть связан не менее чем с одним экземпляром другой сущности.
3.
Элементы модели "сущность-связь"
Моделирование структуры базы данных при помощи алгоритма нормализации, описанного в предыдущих главах, имеет серьезные недостатки:
1. Первоначальное размещение всех атрибутов в одном отношении является очень неестественной операцией. Интуитивно разработчик сразу проектирует несколько отношений в соответствии с обнаруженными сущностями. Даже если совершить насилие над собой и создать одно или несколько отношений, включив в них все предполагаемые атрибуты, то совершенно неясен смысл полученного отношения.
2. Невозможно сразу определить полный список атрибутов. Пользователи имеют привычку называть разными именами одни и те же вещи или наоборот, называть одними именами разные вещи.
3. Для проведения процедуры нормализации необходимо выделить зависимости атрибутов, что тоже очень нелегко, т.к. необходимо явно выписать все зависимости, даже те, которые являются очевидными.
В реальном проектировании структуры базы данных применяются другой метод - так называемое, семантическое моделирование. Семантическое моделирование представляет собой моделирование структуры данных, опираясь на смысл этих данных. В качестве инструмента семантического моделирования используются различные варианты диаграмм сущность-связь (ER - Entity-Relationship).
Диаграммы классов UML включают в себя, как частный случай, диаграммы "сущность- связь" (E-R диаграммы), которые часто используются для логического проектирования баз данных. Но если в классических E-R диаграммах внимание сосредоточено только на данных, диаграммы классов - это шаг вперед: они позволяют моделировать также и поведение. В реальной базе данных подобные логические операции обычно трансформируются в триггеры или хранимые процедуры.
Моделирование схемы производится следующим образом:
1. Идентифицируйте классы вашей модели, состояние которых должно сохраняться и после завершения работы создавшего их приложения.
2. Создайте содержащую эти классы диаграмму классов и характеризуйте их как устойчивые с помощью стандартного помеченного значения persistent (устойчивый). Для работы со специфическими деталями базы данных вы можете определить и свои собственные помеченные значения.
3. Раскройте структурные особенности классов. В общем случае это означает, что надо детально специфицировать их атрибуты и обратить особое внимание на ассоциации и их кратности.
4. Поищите типичные структурные образцы, усложняющие проектирование физической базы данных, например циклические ассоциации, ассоциации "один к одному" и n-арные ассоциации. При необходимости создайте промежуточные абстракции для упрощения логической структуры.
5. Рассмотрите поведение этих классов, раскрывая операции, важные для доступа к данным и поддержания их целостности. В общем случае для лучшего разделения обязанностей бизнес-правила, отвечающие за манипуляции на борами объектов, должны быть инкапсулированы в слое, находящемся над этими устойчивыми классами.
6. Старайтесь использовать в своей работе инструментальные средства, позволяющие преобразовать логический проект в физический.
В UML диаграмма классов является типом диаграммы статической структуры. Она описывает структуру системы, показывая её классы, их атрибуты и операторы, а также взаимосвязи этих классов.
Агрегация- это разновидность ассоциации при отношении между целым и его частями. Как тип ассоциации агрегация может быть именованной. Одно отношение агрегации не может включать более двух классов (контейнер и содержимое).
Агрегация встречается, когда один класс является коллекцией или контейнером других. Причём по умолчанию, агрегацией называют агрегацию по ссылке, то есть когда время существования содержащихся классов не зависит от времени существования содержащего их класса. Если контейнер будет уничтожен, то его содержимое — нет.
Графически агрегация представляется пустым ромбиком на блоке класса и линией, идущей от этого ромбика к содержащемуся классу.
Композиция - более строгий вариант агрегации. Известна также как агрегация по значению.
Композиция имеет жёсткую зависимость времени существования экземпляров класса контейнера и экземпляров содержащихся классов. Если контейнер будет уничтожен, то всё его содержимое будет также уничтожено. Графически представляется, как и агрегация, но с закрашенным ромбиком.
Различия между композицией и агрегацией:
Целое композиции должно иметь мультипликатор 0..1 или 1, что показывает, что часть является частью только одного целого. В агрегации же может быть любой мультипликатор.
Приведём наглядный пример. Комната является частью квартиры, следовательно, здесь подходит композиция, потому что комната без квартиры существовать не может. А, например, мебель не является неотъемлемой частью квартиры, но в то же время, квартира содержит мебель, поэтому следует использовать агрегацию
4.
Реляционная модель данных (РМД) – логическая модель данных, прикладная теория построения баз данных, которая является приложением к задачам обработки данных.
На реляционной модели данных строятся реляционные базы данных. Реляционная модель данных включает следующие компоненты:
- Структурный аспект (составляющая) — данные в базе данных представляют собой набор отношений.
- Аспект (составляющая) целостности - отношения (таблицы) отвечают определенным условиям целостности. РМД поддерживает декларативные ограничения целостности уровня домена (типа данных), уровня отношения и уровня базы данных.
- Аспект (составляющая) обработки (манипулирования) — РМД поддерживает операторы манипулирования отношениями.
Термин «реляционный» означает, что теория основана на математическом понятии отношение. В качестве неформального синонима термину «отношение» часто встречается слово таблица. Необходимо помнить, что «таблица» есть понятие нестрогое и неформальное и часто означает не «отношение» как абстрактное понятие, а визуальное представление отношения на бумаге или экране. Некорректное и нестрогое использование термина «таблица» вместо термина «отношение» нередко приводит к недопониманию. Наиболее частая ошибка состоит в рассуждениях о том, что РМД имеет дело с «плоскими», или «двумерными» таблицами, тогда как таковыми могут быть только визуальные представления таблиц. Отношения же являются абстракциями, и не могут быть ни «плоскими», ни «неплоскими».
Для лучшего понимания РМД следует отметить три важных обстоятельства:
- модель является логической, то есть отношения являются логическими (абстрактными), а не физическими (хранимыми) структурами;
- для реляционных баз данных верен информационный принцип: всё информационное наполнение базы данных представлено одним и только одним способом, а именно — явным заданием значений атрибутов в кортежах отношений; в частности, нет никаких указателей (адресов), связывающих одно значение с другим;
- наличие реляционной алгебры позволяет реализовать декларативное программирование и декларативное описание ограничений целостности, в дополнение к навигационному (процедурному) программированию и процедурной проверке условий.
Наиболее известными альтернативами реляционной модели являются иерархическая модель, и сетевая модель. Некоторые системы, использующие эти старые архитектуры, используются до сих пор. Кроме того, можно упомянуть об объектно-ориентированной модели, на которой строятся так называемые объектно-ориентированные СУБД, хотя однозначного и общепринятого определения такой модели нет.
6.При проектировании базы данных решаются две основных проблемы:
- Каким образом отобразить объекты предметной области в абстрактные объекты модели данных, чтобы это отображение не противоречило семантике предметной области и было по возможности лучшим (эффективным, удобным и т.д.)? Часто эту проблему называют проблемой логического проектирования баз данных.
- Как обеспечить эффективность выполнения запросов к базе данных, т.е. каким образом, имея в виду особенности конкретной СУБД, расположить данные во внешней памяти, создание каких дополнительных структур (например, индексов) потребовать и т.д.? Эту проблему называют проблемой физического проектирования баз данных.
Реляционная модель данных в подавляющем большинстве случаев вполне достаточна для моделирования любых данных. Однако проектирование базы данных в терминах схемы отношений на практике может вызвать большие затруднения, т.к. в этой модели изначально не предусмотрены механизмы описания семантики предметной области. С этим связано появление семантических моделей данных, которые позволяют описать конкретную предметную область гораздо ближе к интуитивному пониманию и, в то же время, достаточно формальным образом.
Проектирование реляционной базы данных фактически сводится к устранению избыточных функциональных зависимостей (а при необходимости избыточных многозначных зависимостей и зависимостей по соединению) из предварительного набора отношений, полученного каким-либо способом (например, из диаграммы сущность связь). В том случае, когда проектируемая база данных сравнительно невелика (общее число атрибутов не первышает 20-30), предварительный набор отношений можно представить в виде одного отношения, называемого универсальным. В него включаются все представляющие интерес атрибуты.
Хронологические данные в SQL
Продолжая тему проектирования БД, рассмотрим хранение и обработку изменяющейся во времени информации. Примеры, когда необходимо помнить историю изменений или регистрировать последовательности событий, мы можем найти в самых разных областях: архив документов, история изменения цен или котировок, журнал событий (аудит), журнал хозяйственных операций, протоколы измерений эксперимента (показания датчиков) и т.д.
Следует выделить два наиболее общих случая, для которых требуется использование временных рядов: изменение во времени состояния объекта и регистрация событий, происходящих с ним.
В первом случае перед разработчиком стоит задача хранить историю изменения объекта (как правило, документа), чтобы иметь возможность восстановить его состояние в заданный момент времени. Кроме собственно организации структур данных, рассматриваемых в рамках статьи, необходимо создать целую подсистему, основанную на принципах документооборота. Наиболее критичным при этом будет время отклика системы для получения проекций документов на определенный момент времени в оперативном режиме. Во втором случае требуется хранить и восстанавливать историю действий, связанных с объектом. Как правило, эти данные предназначены для последующей аналитической обработки, поэтому здесь более важной будет скорость первичного сбора информации.
Перечисленные действия не являются взаимоисключающими. Например, хранение истории изменения атрибутов документа не исключает ведения журнала произведенных с ним операций. В технической литературе также иногда встречается термин «темпоральные базы данных», но он относится только к первому случаю.
5.Нормализация - метод создания набора отношений с заданными свойствами на основе требований к данным, установленных в некоторой организации.
Процесс нормализации является формальным методом, позволяющим определять отношения на основе их первичных или потенциальных ключей и функциональных зависимостей, существующих между их атрибутами. Проектировщики баз данных могут использовать нормализацию в виде наборов тестов, применяемых к отдельным отношениям с целью нормализации реляционной схемы до заданной конкретной формы, что позволит предотвратить возможное возникновение аномалий обновления.
Нормализация - это формальный метод анализа отношений на основе их первичного ключа (или потенциальных ключей) и существующих функциональных зависимостей. Он включает ряд правил, которые могут использоваться для проверки отдельных отношений таким образом, чтобы вся база данных могла быть нормализована до желаемой степени. Если некоторое требование не удовлетворяется, то противоречащее данному требованию отношение должно быть разделено на отношения, каждое из которых (в отдельности) удовлетворяет всем требованиям нормализации.
Чаще всего нормализация осуществляется в виде нескольких последовательно выполняемых этапов, каждый из которых соответствует определенной нормальной форме, обладающей известными свойствами. В ходе нормализации формат отношений становится все более ограниченным (строгим) и менее восприимчивым к аномалиям обновления.
Первая нормальная форма (1НФ)
· Ненормализованная форма (ННФ) - таблица, содержащая одну или несколько повторяющихся групп данных.
· Первая нормальная форма (1НФ) - отношение, в котором на пересечении каждой строки и каждого столбца содержится одно и только одно значение.
Для преобразования ненормализованной таблицы в первую нормальную форму (1НФ) в исходной таблице следует найти и устранить все повторяющиеся группы данных. Повторяющейся группой называется группа, состоящая из одного или нескольких атрибутов таблицы, в которой возможно наличие нескольких значений для единственного значения ключевого атрибута (атрибутов) таблицы. Существуют два способа исключения повторяющихся групп из ненормализованных таблиц.
Вторая нормальная форма (2НФ) - отношение, которое находится в первой нормальной форме и каждый атрибут которого, не входящий в состав первичного ключа, характеризуется полной функциональной зависимостью от этого первичного ключа.
Нормализация отношений 1НФ с приведением к форме 2НФ предусматривает устранение частичных зависимостей. Если в отношении между атрибутами существует частичная зависимость, то функционально-зависимые атрибуты удаляются из него и помещаются в новое отношение вместе с копией их детерминанта.
Третья нормальная форма (ЗНФ) - отношение, которое находится в первой и во второй нормальных формах и не имеет атрибутов, не входящих в первичный ключ атрибутов, которые находились бы в транзитивной функциональной зависит от этого первичного ключа.
Нормализация отношений 2НФ с образованием отношений ЗНФ предусматривает устранение транзитивных зависимостей. Если в отношении существует транзитивная зависимость между атрибутами, то транзитивно зависимые атрибуты удаляются из него и помещаются в новое отношение вместе с копией их детерминанта.
Третья нормальная форма (ЗНФ) - отношение, находящееся в первой и второй нормальной форме, в котором ни один атрибут, отличный от атрибута первичного ключа, не является транзитивно зависимым ни от одного потенциального ключа.
При использовании этих общих определений формы ЗНФ необходимо убедиться в отсутствии частичных и транзитивных зависимостей от всех потенциальных ключей, а не только от первичного ключа. Такое требование может повлечь за собой усложнение процесса нормализации, но эти общие определения налагают дополнительные ограничения на отношения и могут позволить выявить скрытую избыточность в отношениях, которая в ином случае могла остаться незамеченной.
Нормальная форма Бойса-Кодда (НФБК) основана на функциональных зависимостях, в которых учитываются все потенциальные ключи отношения. Тем не менее в форме НФБК предусмотрены более строгие ограничения по сравнению с общим определением формы ЗНФ.
Нормальная форма Бойса-Кодда (НФБК): отношение находится в НФБК тогда и только тогда, когда каждый его детерминант является потенциальным ключом.
Для проверки принадлежности отношения к НФБК необходимо найти все его детерминанты и убедиться в том, что они являются потенциальными ключами. Напомним, что детерминантом является один атрибут или группа атрибутов, от которой полностью функционально зависит другой атрибут.
Различие между ЗНФ и НФБК заключается в том, что функциональная зависимость А—>В допускается в отношении ЗНФ, если атрибут В является первичным ключом, а атрибут А не обязательно является потенциальным ключом. Тогда как в отношении НФБК эта зависимость допускается только тогда, когда атрибут А является потенциальным ключом. Следовательно, нормальная форма Бойса-Кодда является более строгой версией формы ЗНФ, поскольку каждое отношение НФБК является также отношением ЗНФ, но не всякое отношение ЗНФ является отношением НФБК.