Логическая структура бд

Логическая структура реляционной базы данных Access является адекватным отображением полученной информационно-логической модели предметной области. Для канонической модели не требуется дополнительных преобразований. Каждый информационный объект модели данных отображается соответствующей реляционной таблицей. Структура реляционной таблицы определяется реквизитным составом соответствующего информационного объекта, где каждый столбец (поле) соответствует одному из реквизитов объекта. Ключевые реквизиты объекта образуют уникальный ключ реляционной таблицы. Для каждого столбца таблицы (поля) задается тип, размер данных и другие свойства. Строки (записи) таблицы соответствуют экземплярам объекта и формируются при загрузке таблицы.

Содержание

Логическая структура реляционной базы данных

Связи между объектами модели данных реализуются одинаковыми реквизитами — ключами связи в соответствующих таблицах. При этом ключом связи типа 1 : M всегда является уникальный ключ главной таблицы. Ключом связи в подчиненной таблице является либо некоторая часть уникального ключа в ней, либо поле, не входящее в состав первичного ключа (например, код фирмы в таблице СКЛАД). Ключ связи в подчиненной таблице называется внешним ключом.

Все связи в полученной информационно-логической модели предметной области «Поставка товара» характеризуются отношением типа 1 : M. Соответственно, связь между таблицами ПОКУПАТЕЛЬ и ДОГОВОР осуществляется по коду покупателя (КОД_ПОК), который является уникальным идентификатором главного объекта ПОКУПАТЕЛЬ и неключевым в объекте ДОГОВОР (см. табл. 2.6).

Связь между таблицами ТОВАР и ПОСТАВКА_ПЛАН осуществляется по уникальному ключу главного объекта ТОВАР — Коду товара, который в подчиненном объекте ПОСТАВКА_ПЛАН является частью ключа (см. табл. 2.6). Аналогично связь между таблицами ТОВАР и ОТГРУЗКА осуществляется по уникальному ключу главного объекта ТОВАР — Коду товара.

Связь между таблицами ДОГОВОР и НАКЛАДНАЯ осуществляется по уникальному ключу главного объекта ДОГОВОР — Номеру договора (НОМ_ДОГ), который в подчиненном объекте НАКЛАДНАЯ не входит в состав ключа (см. табл. 2.7).

Связь между таблицами ДОГОВОР и ПОСТАВКА_ПЛАН осуществляется по уникальному ключу главного объекта ДОГОВОР — Номеру договора (НОМ_ДОГ), который в подчиненном объекте ПОСТАВКА_ПЛАН является частью ключа (см. табл. 2.6). Аналогично связь между таблицами НАКЛАДНАЯ и ОТГРУЗКА осуществляется по уникальному составному ключу главного объекта НАКЛАДНАЯ — НОМ_НАКЛ + + КОД_СК, который в подчиненном объекте ОТГРУЗКА является частью ключа (см. табл. 2.7).

В Access может быть создана схема данных, наглядно отображающая логическую структуру базы данных. Определение одно-многозначных связей в этой схеме должно осуществляться в соответствии с построенной моделью данных. Топология проекта схемы данных практически совпадает с топологией информационно-логической модели. Для модели данных предметной области (см. рис. 2.18), построенной в рассмотренном примере, логическая структура базы данных в виде схемы данных Access приведена на рис. 2.19.

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

Проектирование баз данных: основные принципы

«Отличие государственного деятеля от политика в том, что политик ориентируется на следующие выборы, а государственный деятель — на следующее поколение». У. Черчилль

Терминология уровней

В блогах моих коллег была упомянута трехуровневая архитектура СУБД ANSI/X3/SPARC. Долгое время она являлась своего рода образцом для проектирования информационной системы в целом, состоящей из СУБД и приложений. Если для 1975 года была характерна ситуация, когда большинство разработчиков занималось вопросами реализации физического уровня хранения, то уже к середине-концу 1980-х появилось достаточное число программных продуктов класса универсальных СУБД, реализующих ту или иную модель данных логического уровня. Соответственно, границы физического уровня заметно сдвинулись, оставляя лишь место для тонкой настройки и потянув за собой и логические рамки. Особенности организации внешнего уровня также стало возможным описывать на логическом, прежде всего с помощью схем (пространств имён), видов (view) и хранимых процедур самой СУБД, а также различными инструментами слоя сервера приложений.

Похожая история случилась и с другим термином. В 1970-х годах для обозначения роли проектирующего и обслуживающего структуры баз данных сотрудника было введено специальное понятие — администратор баз данных. Но в настоящее время уже возникло достаточно чуткое разделение на администраторов-проектировщиков и администраторов по эксплуатации. Например, учебные курсы и сертификации Microsoft по СУБД имеют два основных сюжета: реализация и эксплуатация. Компетенции этих специалистов пересекаются, но сложность моделируемых предметных областей, с одной стороны, и обросших тысячами параметров тонкой настройки и мониторинга на конкретных аппаратных платформах с другой, вынуждают к такому разделению труда. Впрочем, руководство некоторых предприятий зачастую уверено, что администратор БД — это тот работник, что делает резервные копии, раздаёт права доступа, а остальное время генерирует трафик в социальных сетях. Но даже подобный взгляд является прогрессом, ведь в РФ образца 1990х такого сотрудника называли программистом…

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

Модель данных — это инструмент описания

Схема базы данных — это результат использования инструмента

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

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

  • Концептуальный уровень соответствует описаниям сущностей (объектов) предметной области, связям между ними. Используются модели понятийного уровня, семантические модели. Характерная особенность полученных описаний — независимость от используемых моделей данных.
  • Логический уровень описывает схему базы данных в терминах выбранной модели данных. Скорее всего это будет реляционная модель, но если вы столкнётесь с иным походом, то моделирование на логическом уровне также будет специфичным.
  • Физический уровень соответствует описанию схемы данных в конкретной СУБД. Одной логической схеме БД может соответствовать несколько физических схем для разных СУБД, реализующих, тем не менее, одну и ту же модель

Рис. 1. Три уровня абстракции базы банных

Концептуальный уровень представляет собой наибольший интерес для функционального специалиста-проектировщика. Для разработчика информационной системы важнее результат моделирования на концептуальном уровне, который будет использован в логическом проектировании. Если концептуальные модели отсутствуют, проектировщику придётся работать «стандартным» методом прочтения линейных текстов многостраничных функциональных спецификаций, выстраивая по ним логическую схему БД.

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

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

Табл. 1. Соответствие некоторых терминов реляционной модели (логический уровень) их реализаций в СУБД (физический уровень)

Термин реляционной модели

Соответствующие термины РСУБД

Отношение

Таблица-кластер Таблица-куча

Секционированная таблица

Табличная функция

Вид (view, виртуальная таблица)

Проекция

Вид (view)

Материализованный вид

Табличная функция

Кортеж

Строка (запись)

Строка (с построчным сжатием)

Атрибут

Колонка (столбец)

Вычислимая колонка

Вычислимая хранимая колонка

Ключ

Первичный ключ

Ограничение уникальности (unique)

Уникальный индекс

Домен

Встроенный тип Пользовательский тип

Как видно на примере соответствий всего для нескольких терминов, логический уровень значительно отличается от физической реализации в том числе неоднозначностью соответствия. Так в реляционной модели ключом называется минимальный набор атрибутов отношения, позволяющий однозначно идентифицировать кортеж. Ключей, возможно составных, то есть содержащих более одного атрибута, может быть несколько. В РСУБД для каждой таблицы можно определить лишь один ключ, который называется первичным (primary key). Остальные ключи придётся определять соответствующим ограничением целостности или уникальными индексами.

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

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

CREATE VIEW power users AS SELECT u.id, u.name FROM users u INNER JOIN users groups ug ON u.id = ug.id user INNER JOIN groups g ON g.id = ug.id group WHERE g.sysname = ’POWER_USERS’

Однако, с помощью видов можно создавать и полноценные отношения, находящиеся в режиме «только чтение»

CREATE VIEW colors AS SELECT 1 AS id, ‘Красный’ AS name UNION SELECT 2 AS id, ‘Зеленый’ AS name UNION SELECT 3 AS id, ‘Синий’ AS name

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

CREATE FUNCTION users_by_group(@sysname nvarchar(16)) RETURNS TABLE AS RETURN ( SELECT u.id, u.name FROM users u INNER JOIN users_groups ug ON u.id = ug.id_user INNER JOIN groups g ON g.id = ug.id_group WHERE g.sysname = @sysname )

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

На заре развития Интернет во времена распространения сетей FIDO, узлы которых часто содержали администраторы сетей и БД, ходила такая шутка: «Настоящий фидошник умеет делать 2 вещи: читать почту и пить пиво»

Вас заинтересует / Intresting for you:

Инструментальная диалоговая си… 422 просмотров Ирина Светлова Sun, 24 Mar 2019, 06:01:30База данных как объект правово… 264 просмотров Денис Wed, 27 Mar 2019, 03:16:24Жизненный цикл реляционных баз… 968 просмотров Ирина Светлова Tue, 21 Nov 2017, 13:26:01Перенос корпоративных баз данн… 299 просмотров Дэн Fri, 27 Sep 2019, 07:52:18Author: Боба Другие статьи автора:

Описание таблиц базы данных

Рисунок 7 «Клиенты»

Таблица Клиенты — содержит информацию о клиентах гостиницы. Полями таблицы являются персональные данные гостей: ФИО, дата_рождения, адрес, номер_паспорта, телефон. Каждому клиенту присваивается первичный ключ.

Рисунок 8 «Номера»

Таблица Номера – содержит информацию о номерах: (этаж, статус, тип, стоимость, телевизор, ванная_комната, холодильник). Каждому номеру присваивается первичный ключ.

Рисунок 9 «Учет_работы»

Таблица Учет работы – содержит информацию о датах заселения и выселения гостей: (дата_заселения, дата_выселения)). Каждой операции присваивается первичный ключ.

Рисунок 10 «Связь между таблицами в базе данных»

5 Описание программного обеспечения

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

  1. управлять компьютерными ресурсами организации;

  2. обеспечивать пользователя всеми инструментами, необходимыми для извлечения пользы из этих ресурсов;

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

В качестве СУБД для курсовой работы было выбрано SQL Server Management Studio. Среда SQL Server Management Studio — это интегрированная среда для доступа, настройки, управления и администрирования всех компонентов SQL Server. Среда SQL Server Management Studio объединяет большое число графических средств с полнофункциональным редактором сценариев для доступа к SQL Server разработчиков и администраторов с любым опытом работы. Преимущества: Среда SQL Server Management Studio является полнофункциональной программой для управления объектами SQL Server, объединяющей удобный графический интерфейс и богатые возможности для создания скриптов.

В качестве среды разработки была выбрана среда Microsoft Visual Studio.

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

В качестве языка разработки был выбран Visual Basic, это простой и быстрый способ создания программ для Microsoft Windows. Рисунок 11 «Логическая схема работы программы»

6 Руководство пользователя

Рисунок 12 «Главная форма программы»

При запуске приложения вы увидите главную форму с меню.

1) При открытии вкладки «отчеты», можно посмотреть 2 отчета (отчет в виде MS Report Viewer и Отчет в MS Excel). Рисунок2) При открытии вкладки «справочник», можно посмотреть поиск по форме «Учет работы», а так же поменять фон формы. Рисунок 3) При нажатии кнопки «Поиск по клиентам», можно посмотреть информацию по клиентам и найти определенного человека по заданным параметрам. Рисунок 4) При нажатии кнопки «Поиск по номерам», можно посмотреть информацию по номерам и найти определенного человека по заданным параметрам. Рисунок 5) При нажатии кнопки «Бронирование номера», можно посмотреть забронированные даты. Рисунок 13 1) Отчеты:

Отчет в виде MS Report Viewer (Прайс-лист) Рисунок 14 Отчет в MS Excelс использованием технологии COM Рисунок 15

2) Рисунок 16 «Учет_работы»

1) В данном комбобоксе нужно выбрать по какому атрибуту будет выполняться поиск;

2) В данном текстбоксе нужно указать, что именно ищите; 3) При нажатии кнопки «выполнить поиск» произойдет по заданным параметрам; 4) При нажатии «отмена поиска» произойдет сброс последнего поиска;

5) Поле, где будут показаны результаты поиска.

3) Рисунок 17 «Поиск по клиентам»

На данной форме можно найти нужного клиента по заданным критериям (фио, дата_рождения, адрес, номер_паспорта, телефон)

1) В данном комбобоксе нужно выбрать по какому атрибуту будет выполняться поиск;

2) В данном текстбоксе нужно указать, что именно ищите; 3) При нажатии кнопки «выполнить поиск» произойдет по заданным параметрам; 4) При нажатии «отмена поиска» произойдет сброс последнего поиска;

5) Поле, где будут показаны результаты поиска.

Рисунок 18 «Поиск по номерам»

На данной форме можно найти нужный номер по заданным критериям (этаж, статус, тип, стоимость, тв, ванная_комната, холодильник)

1) В данном комбобоксе нужно выбрать по какому атрибуту будет выполняться поиск;

2) В данном текстбоксе нужно указать, что именно ищите; 3) При нажатии кнопки «выполнить поиск» произойдет по заданным параметрам; 4) При нажатии «отмена поиска» произойдет сброс последнего поиска;

5) Поле, где будут показаны результаты поиска.

Рисунок 19 «Бронирование номера»

  1. В данном текстбоксе нужно вписать нужный код операции.

  1. При нажатии на кнопку «Учет работы», появится форма «Учет работы» Рисунок 24

  1. В данный тексбокс вписывается код номера.

  1. При нажатии на кнопку «Номера», появится форма «Поиск номера» Рисунок 22

  1. В данный текст бокс нужно вписать нужный клиента.

  1. При нажатии на кнопку «Клиенты», появится форма «Поиск по клиентам» Рисунок 20

  1. В DateTimePicker выбирается нужная дата.

  2. В DateTimePicker выбирается нужная дата.

I. Простейшие типы баз данных

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

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

1. Простые структуры данных

Первый и простейший способ хранения данных – текстовые файлы. Метод применяется и сегодня для работы с небольшими объёмами информации. Для разделения полей используется специальный символ: запятая или точка с запятой в csv-файлах датасетов, двоеточие или пробел в *nix-подобных системах:

/etc/passwd в *nix системе root:x:0:0:root:/root:/bin/bash daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin bin:x:2:2:bin:/bin:/usr/sbin/nologin sys:x:3:3:sys:/dev:/usr/sbin/nologin sync:x:4:65534:sync:/bin:/bin/sync games:x:5:60:games:/usr/games:/usr/sbin/nologin man:x:6:12:man:/var/cache/man:/usr/sbin/nologin lp:x:7:7:lp:/var/spool/lpd:/usr/sbin/nologin mail:x:8:8:mail:/var/mail:/usr/sbin/nologin news:x:9:9:news:/var/spool/news:/usr/sbin/nologin backup:x:34:34:backup:/var/backups:/usr/sbin/nologin list:x:38:38:Mailing List Manager:/var/list:/usr/sbin/nologin nobody:x:65534:65534:nobody:/nonexistent:/usr/sbin/nologin syslog:x:102:106::/home/syslog:/usr/sbin/nologin bob:x:1000:1000:Bob Smith,,,:/home/bob:/bin/bash

Следствия:

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

Примеры:

  • /etc/passwd и /etc/fstab в *nix-системах
  • csv-файлы

2. Иерархические базы данных

В отличие от текстовых таблиц, в следующем типе БД появляются связи между объектами. В иерархических базах данных каждая запись имеет одного «родителя». Это создаёт древовидную структуру, в которой записи классифицируются по их отношениям с цепочкой родительских записей.

Пример построения иерархических связей

Следствия:

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

Примеры:

  • файловые системы
  • DNS
  • LDAP

3. Сетевые базы данных

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

Пример связей в сетевой базе данных

Следствия:

  • сетевые базы данных представляются не деревом, а общим графом
  • ограничены теми же шаблонами доступа, что иерархические БД

Примеры:

  • IDMS

II. Реляционные БД

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

Следствия:

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

Примеры:

  • MySQL
  • MariaDB
  • PostgreSQL
  • SQLite

III. NoSQL базы данных

NoSQL – группа типов БД, предлагающих подходы, отличные от стандартного реляционного шаблона. Говоря NoSQL, подразумевают либо «не-SQL», либо «не только SQL», чтобы уточнить, что иногда допускается SQL-подобный запрос.

5. Базы данных «ключ-значение»

В базах данных «ключ-значение» для хранения информации вы предоставляте ключ и объект данных, который нужно сохранить. Например, JSON-объект, изображение или текст. Чтобы запросить данные, отправляете ключ и получаете blob-объект.

Следствия:

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

Примеры:

  • Redis
  • memcached
  • etcd

6. Документная база данных

Документные базы данных (также документоориентированные БД или хранилища документов), совместно используют базовую семантику доступа и поиска хранилищ ключей и значений. Такие БД также используют ключ для уникальной идентификации данных. Разница между хранилищами «ключ-значение» и документными БД заключается в том, что вместо хранения blob-объектов, документоориентированные базы хранят данные в структурированных форматах – JSON, BSON или XML.

Следствия:

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

Примеры:

  • MongoDB
  • RethinkDB

7. Графовая база данных

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

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

Следствия:

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

Примеры:

  • Neo4j
  • JanusGraph
  • Dgraph

8. Колоночные базы данных

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

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

Следствия:

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

Примеры:

  • Cassandra
  • HBase

9. Базы данных временных рядов

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

Следствия:

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

Примеры:

  • OpenTSDB
  • Prometheus
  • InfluxDB
  • TimescaleDB

IV. Комбинированные типы

NewSQL и многомодельные БД являются разными типами баз данных, но решают одну группу проблем, вызванных полярными подходами SQL или NoSQL-стратегии. Почему бы не объединить преимущества обеих групп?

10. NewSQL базы данных

NewSQL базы данных наследуют реляционную структуру и семантику, но построены с использованием более современных, масштабируемых конструкций. Цель – обеспечить большую масштабируемость, нежели реляционные БД, и более высокие гарантии согласованности, чем в NoSQL. Компромисс между согласованностью и доступностью является фундаментальной проблемой распределённых баз данных, описываемой теоремой CAP.

Следствия:

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

Примеры:

  • MemSQL
  • VoltDB
  • Spanner
  • Calvin
  • CockroachDB
  • FaunaDB
  • yugabyteDB

11. Многомодельные базы данных

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

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

Следствия:

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

Примеры:

Часть 2. Структура СУБД, таблицы и типы данных

Первая часть Мы продолжаем создавать наш простенький эмулятор биржи. Вот что мы сделаем:

  • Создадим схему организации базы данных.
  • Распишем что, как и где хранится.
  • Узнаем, как данные связаны друг с другом.
  • Начнём изучать основы SQL на примере команды создания таблицы SQL CREATE TABLE, Data Definition Language (DDL) языка SQL.
  • Продолжим писать Java-программу. Основные функции СУБД в части java.sql по созданию нашей базы данных реализуем программно, используя JDBC и трехзвенную (3-tier) архитектуру.

Эти две части вышли более объёмными, поскольку нам необходимо ознакомиться с основами SQL и организацией СУБД изнутри, и привести аналогии с Java. Чтобы не утомлять листингами кода, в конце приведены ссылки на соответствующий commit github-репозитория с программой.

Дизайн СУБД

Описание приложения

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

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

Биржа работает тиками – фиксированными периодами времени (в нашем случае — 1 мин). В течение тика может поменяться курс акции, далее совершиться покупка или продажа акций трейдером.

Структура данных эмуляции биржи

Назовем отдельные сущности биржи моделями. Чтобы избежать ошибок округления, с финансовыми суммами будем работать через класс BigDecimal (подробности можно узнать по ссылке в конце статьи). Распишем структуру каждой модели подробнее: Акция:

Атрибут Тип Описание
name Srting Наименование
changeProbability int Вероятность смены курса в процентах на каждом тике
startPrice BigDecimal Начальная стоимость
delta int Максимальная величина в процентах, на которую может смениться текущая стоимость

Курс акции:

Атрибут Тип Описание
operDate LocalDateTime Время (тик) выставления курса
share Акция Ссылка на акцию
rate BigDecimal Курс акции

Трейдер:

Атрибут Тип Описание
name String Время (тик) выставления курса
sfreqTick int Частота совершения операций. Задана периодом, в тиках, спустя который трейдер совершает операции
cash BigDecimal Сумма денег, помимо акций
traidingMethod int Используемый трейдером алгоритм. Зададим его числом-константой, реализация алгоритма будет (в следующих частях) в Java-коде
changeProbability int Вероятность выполнения операции, в процентах
about String Вероятность смены курса, в процентах, на каждом тике

Действия трейдеров:

Атрибут Тип Описание
operation int Тип операции (покупка или продажа)
traider Трейдер Ссылка на трейдера
shareRate Курс акции Ссылка на курс акции (соответственно на саму акцию, её курс и время его выставление)
amount Long Количество акций, участвующих в операции

Для обеспечения уникальности каждой модели, добавим атрибут id типа long. Данный атрибут будет уникальным в пределах экземпляров модели и будет однозначно его определять. Атрибуты, ссылающиеся на другие модели (трейдер, акция, курс акции), могут использовать этот id для однозначного определения соответствующей модели. Сразу приходит мысль, что мы могли бы использовать Map<Long, Object> для хранения подобных данных, где Object – соответствующая модель. Однако попробуйте реализовать это в коде при следующих условиях:

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

…и вы столкнётесь с задачами, требующими должной квалификации и времени на реализацию. Не стоит «изобретать велосипед». Многое уже продумано и написано за нас. Так что мы будем использовать то, что уже проверено годами.

Хранение данных в Java

Рассмотрим акцию. В Java мы создали для этой модели определенный класс Share c полями name, changeProbability, startPrice, delta. А множество акций хранили как Map<Long, Share>, где ключом служит уникальный идентификатор для каждой акции. public class Share { private String name; private BigDecimal startPrice; private int changeProbability; private int delta; } Map<Long, Share> shares = new HashMap<>(); shares.put(1L, new Share(«ibm», BigDecimal.valueOf(20.0), 15, 10)); shares.put(2L, new Share(«apple», BigDecimal.valueOf(14.0), 25, 15)); shares.put(3L, new Share(«google», BigDecimal.valueOf(12.0), 20, 8)); … shares.put(50L, new Share(«microsoft», BigDecimal.valueOf(17.5), 10,4 )); Для доступа к нужной акции по идентификатору применяем метод shares.get(id). Для задачи нахождения по имени акции или цене, мы бы перебирали в цикле все записи в поисках нужной и так далее. Но мы пойдём другим путём, и будем хранить значения в СУБД.

Хранения данных в СУБД

Сформулируем начальный свод правил хранения данных для СУБД:

  • Данные в СУБД организованы в таблицы (TABLE), представляющие собой набор записей.
  • Все записи имеют одинаковые наборы полей. Они задаются при создании таблицы.
  • Для поля можно выставить значение по умолчанию (DEFAULT).
  • Для таблицы можно выставить ограничения (CONSTRAINT), описывающие требования к её данным чтобы обеспечить их целостность. Это можно сделать на этапе создания таблицы (CREATE TABLE) или добавить позже (ALTER TABLE … ADD CONSTRAINT).
  • Наиболее распространённые CONSTRAINT:
    • Первичный ключ PRIMARY (Id в нашем случае).
    • Уникальное значение поле UNIQUE (VIN для таблицы автотранспорта).
    • Проверка поля CHECK (значение процентов не может быть больше 100). Одно из частных ограничений на поле – NOT NULL или NULL, запрещающее/разрешающее хранить NULL в поле таблицы.
    • Ссылка на стороннюю таблицу FOREIGN KEY (ссылка на акцию в таблице курсов акций).
    • Индекс INDEX (индексирование поля для ускорения поиска значений по нему).
    • Выполнение модификации записи (INSERT, UPDATE) не произойдёт, если значение её полей противоречат ограничениям (CONSTRAINT).
  • Каждая таблица может иметь ключевое поле (или несколько), по которой можно однозначно определить запись. Такое поле (или поля, если они формируют составной ключ) образует первичный ключ таблицы — PRIMARY KEY.
    • Первичный ключ обеспечивает уникальность записи в таблице, по нему создается индекс, что дает быстрый доступ по значению ключа ко всей записи.
    • Наличие первичного ключа существенно облегчает создание ссылок между таблицами. Далее мы будем использовать искусственный первичный ключ: для первой записи id = 1, каждая следующая запись будет вставляться в таблицу с увеличенным на единицу значением id. Такой ключ часто называют AutoIncrement или AutoIdentity.

Собственно, таблица акций: Можно ли в таком случае использовать в качестве ключа имя акции? По большому счёту — да, только вот есть вероятность, что какая-то компания выпускает разные акции и именует их только собственным названием. В таком случае уникальности уже не будет. На практике искусственный первичный ключ используют довольно часто. Согласитесь, использование ФИО в качестве уникального ключа в таблице, содержащей записи по людям, не обеспечит уникальности. Как и использование комбинации ФИО и даты рождения.

Типы данных в СУБД

Как и в любом другом языке программирования в SQL существует типизация данных. Приведём наиболее распространённые типы данных SQL: Целые типы

SQL-тип SQL-синонимы Соответствие в Java Описание
INT INT4,INTEGER java.lang.Integer 4-байтовое целое, -2147483648 … 2147483647
BOOLEAN BOOL, BIT java.lang.Boolean True, False
TINYINT java.lang.Byte 1-байтовое целое, -128 … 127
SMALLINT INT2 java.lang.Short 2-байтовое целое, -32768 … 32767
BIGINT INT8 java.lang.Long 8-байтовое целое, -9223372036854775808 … 9223372036854775807
AUTO_INCREMENT INCREMENT java.lang.Long Инкрементальный счётчик, уникальный для таблицы. Если в неё вставляют новое значение, он увеличивается на единицу Сгенерированные значения никогда не повторяются.

Вещественные

SQL-тип SQL-синонимы Соответствие в Java Описание
DECIMAL(N,M) DEC, NUMBER java.math.BigDecimal Десятичная дробь с фиксированной точностью (N цифр целой части и M — дробной). В основном предназначены для работы с финансовыми данными.
DOUBLE FLOAT8 java.lang.Double Вещественное число двойной точности (8 байт).
REAL FLOAT4 java.lang.Real Вещественное число одинарной точности (4 байта).

Строковые

SQL-тип SQL-синонимы Соответствие в Java Описание
VARCHAR(N) NVARCHAR java.lang.String Строка в формате UNICODE длины N. Длина ограничена значением 2147483647 Полностью загружает содержимое строки в память.

Дата и время

SQL-тип SQL-синонимы Соответствие в Java Описание
TIME java.time.LocalTime, java.sql.Time Хранение времени (до наносекунд), при конвертации в DATETIME, в качестве даты выставляется 1 янв 1970.
DATE java.time.LocalDate, java.sql.Timestamp Хранение дат в формате yyyy-mm-dd, время выставляется как 00:00
DATETIME TIMESTAMP java.time.LocalDateTime, java.sql.Timestamp Хранение даты + времени (без учёта временных зон).

Хранение больших объемов данных

Стиль написания кода в SQL

Для многих языков существуют рекомендации по оформлению кода. Обычно такие документы содержат правила именования переменных, констант, методов и иных языковых структур. Так, для Python существует PEP8, для Java — Oracle Code Conventions for Java. Для SQL создано несколько разных сводов, которые несколько отличаются друг от друга. Невзирая на это, следует выработать привычку придерживаться правил при оформлении кода, особенно если вы работаете в команде. Правила могут быть, например, следующими (разумеется, вы можете разработать для себя другой набор правил, главное придерживайтесь их в дальнейшем):

  • Ключевые и зарезервированные слова, в том числе команды и операторы, нужно писать прописными буквами: CREATE TABLE, CONSTRAINT…
  • Имена таблиц, полей и прочих объектов не должны совпадать с ключевыми словами языка SQL (см. ссылку в конце статьи), но могут содержать их в себе.
  • Имена таблиц должны отражать их назначение. Они записываются строчными буквами. Слова в наименовании отделены друг от друга подчёркиваниями. Слово в конце должно быть во множественном числе: traiders (трейдеры), share_rates (курс акций).
  • Имена полей таблиц должны отражать их назначение. Их нужно записывать строчными буквами, слова в наименовании нужно оформлять в стиле Camel Case, а слово в конце нужно использовать в единственном числе: name (наименование), share_rates (курс акций).
  • Поля искусственных ключей должны содержать слово id.
  • Имена CONSTRAINT должны удовлетворять правилам именования таблиц. Также они должны включать участвующие в них поля и таблицы, начинаться со смыслового префикса: check_ (проверка значения поля), pk_ (первичный ключ), fk_ (внешний ключ), uniq_ (уникальность поля), idx_ (индекс). Пример: pk_traider_share_actions_id (первичный ключ по полю id для таблицы traider_share_actions).
  • И так далее, по мере изучения SQL список правил будет пополняться/изменяться.

Проектирование СУБД

Непосредственно перед созданием СУБД её нужно спроектировать. Конечная схема содержит таблицы, набор полей, CONSTRAINT, ключи, условия по умолчанию для полей, связи между таблицами и прочие сущности БД. В интернете можно найти множество бесплатных online/offline дизайнеров для проектирования небольших СУБД. Попробуйте вбить в поисковик что-то вроде “Database designer free”. Такие приложения обладают полезными дополнительными свойствами:

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

Например, dbdiffo.com выделяет ключи, показывает меткой NN непустые поля и AI(AutoIncrement) – счётчики:

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

Итак, у нас есть схема. Теперь перейдём непосредственно к созданию таблиц (CREATE TABLE). Для этого нам желательно иметь предварительные данные:

  • имя таблицы
  • имена и тип полей
  • ограничения (CONSTRAINTS) на поля
  • значения по умолчанию для полей (при наличии)
  • первичный ключ (PRIMARY KEY) при наличии
  • связи между таблицами (FOREIGN KEY)

Не будем изучать досконально все опции команды CREATE TABLE, рассмотрим основы SQL на примере создания таблицы для трейдеров: CREATE TABLE traiders( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, freqTiсk INTEGER NOT NULL, cash DECIMAL(15,2) NOT NULL DEFAULT 1000, tradingMethod INTEGER NOT NULL, changeProbability INTEGER NOT NULL DEFAULT 50, about VARCHAR(255) NULL ); ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod CHECK(tradingMethod IN (1,2,3)); ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability CHECK(changeProbability <= 100 AND changeProbability > 0) Разберём подробнее:

  • CREATE TABLE traiders (описание полей) — создание таблицы с указанным именем, в описании поля разделяются запятой. Любая команда завершается точкой с запятой.
  • Описание поля начинается с его имени, далее следует тип, CONSTRAINT и значение по умолчанию.
  • id BIGINT AUTO_INCREMENT PRIMARY KEY – поле id целого типа — это первичный ключ и инкрементный счётчик (для каждой новой записи для поля id будет генерироваться значение на единицу больше ранее созданного для этой таблицы).
  • cash DECIMAL(15,2) NOT NULL DEFAULT 1000 – поле cash, десятичная дробь, 15 цифрами до запятой и две после (финансовые данные, например, доллары и центы). Не может принимать NULL-значений. Если значение не задано, оно получит значение 1000.
  • about VARCHAR(255) NULL – поле about, строка до 255 символов длиной, может принимать пустые значения.

Заметим, что часть CONSTRAINT-условий мы можем задать после создания таблицы. Рассмотрим конструкцию, для модификации структуры таблицы и её полей: ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения CHECK(условие) на примерах:

  • CHECK(tradingMethod IN (1,2,3)) – поле tradingMethod может принимать только значения 1,2,3
  • CHECK(changeProbability <= 100 AND changeProbability > 0) – поле changeProbability может принимать целые значения в диапазоне от 1 до 100

Связи между таблицами

Для разбора описания связей между таблицами посмотрим создание share_rates: CREATE TABLE share_rates( id BIGINT AUTO_INCREMENT PRIMARY KEY, operDate datetime NOT NULL, share BIGINT NOT NULL, rate DECIMAL(15,2) NOT NULL ); ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id) Ссылку на значения другой таблицы можно задать следующим образом: ALTER TABLE таблица_из_которой_ссылаемся ADD FOREIGN KEY (поле_которое_ссылается) REFERENCES таблица_на_которую_ссылаемся(поле_на_которое_ссылаемся) Пусть в shares мы имеем записи по акциям, например, для id=50 храним акции Microsoft с начальной ценой 17.5, дельтой 20 и шансом изменения 4%. Для таблицы share_rates мы получаем три основных свойства:

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

Последние два пункта служат для обеспечения целостности хранимых данных. Создание таблиц SQL нашей эмуляции и примеры SQL запросов вы можете посмотреть в реализации Java методов соответствующих классов по ссылке на github-репозиторий в конце статьи. Третья часть

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *