Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Раздел 1: Модель, генерация и наполнение базы данных

Раздел 1: Модель, генерация и наполнение базы данных 1.1. Общее описание модели

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

Модели баз данных «Библиотека» и «Большая библиотека» полностью идентичны (отличаются эти базы данных только количеством записей). Здесь всего семь таблиц:

•genres — описывает литературные жанры:

og_id — идентификатор жанра (число, первичный ключ);

og_name — имя жанра (строка);

•books — описывает книги в библиотеке:

ob_id — идентификатор книги (число, первичный ключ);

ob_name — название книги (строка);

ob_year — год издания (число);

ob_quantity — количество экземпляров книги в библиотеке (число);

•authors — описывает авторов книг:

oa_id — идентификатор автора (число, первичный ключ);

oa_name — имя автора (строка);

•subscribers — описывает читателей (подписчиков) библиотеки:

os_id — идентификатор читателя (число, первичный ключ);

os_name — имя читателя (строка);

•subscriptions — описывает факты выдачи/возврата книг (т.н. «подписки»):

osb_id — идентификатор подписки (число, первичный ключ);

osb_subscriber — идентификатор читателя (подписчика) (число, внешний ключ);

osb_book — идентификатор книги (число, внешний ключ);

osb_start — дата выдачи книги (дата);

osb_finish — запланированная дата возврата книги (дата);

osb_is_active — признак активности подписки (содержит значение Y, если книга ещё на руках у читателя, и N, если книга уже возвращена в библиотеку);

•m2m_books_genres — служебная таблица для организации связи «многие ко многим» между таблицами books и genres:

ob_id — идентификатор книги (число, внешний ключ, часть составного

первичного ключа);

og_id — идентификатор жанра (число, внешний ключ, часть составного первичного ключа);

•m2m_books_authors — служебная таблица для организации связи «многие ко многим» между таблицами books и authors:

ob_id — идентификатор книги (число, внешний ключ, часть составного

первичного ключа);

oa_id — идентификатор автора (число, внешний ключ, часть составного первичного ключа).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 5/545

Общее описание модели

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

Общая схема базы данных представлена на рисунке 1.a. Скрипты генерации баз данных для каждой СУБД вы можете найти в исходном материале, ссылка на который приведена в предисловии{4}.

Рисунок 1.a — Общая схема базы данных

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 6/545

Модель для MySQL

1.2. Модель для MySQL

Модель базы данных для MySQL представлена на рисунках 1.b и 1.c. Обратите внимание на следующие важные моменты:

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

•Строки представлены типом varchar длиной до 150 символов (чтобы гарантированно уложиться в ограничение 767 байт на длину индекса; 150*4 = 600, MySQL выравнивает символы UTF-строк на длину в четыре байта на символ при операциях сравнения).

•Поле sb_is_active представлено характерным для MySQL типом данных enum (позволяющим выбрать одно из указанных значений и очень удобным для хранения заранее известного предопределённого набора значений — Y и N в нашем случае).

•Поле g_name сделано уникальным, т.к. существование одноимённых жанров недопустимо.

•Поля sb_start и sb_finish представлены типом date (а не более полными, например, datetime), т.к. мы храним дату выдачи и возврата книги с точностью до дня.

*pfK a_id: INTEGER UNSIGNED

«FK»

+FK_m2m_books_authors_authors(INTEGER)

+FK_m2m_books_authors_books(INTEGER)

«PK»

+PK_m2m_books_authors(INTEGER, INTEGER)

Рисунок 1.b — Модель базы данных для MySQL в Sparx EA

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 7/545

Модель для MySQL

Рисунок 1.c — Модель базы данных для MySQL в MySQL Workbench

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 8/545

Модель для MS SQL Server

1.3. Модель для MS SQL Server

Модель базы данных для MS SQL Server представлена на рисунках 1.d и 1.e. Обратите внимание на следующие важные моменты:

•Первичные ключи представлены знаковыми целыми числами, т.к. в MS SQL Server нет возможности сделать bigint, int и smallint беззнаковыми (а tinyint, наоборот, бывает только беззнаковым).

•Строки представлены типом nvarchar длиной до 150 символов (как из соображений аналогии с MySQL, так и чтобы гарантированно уложиться в ограничение 900 байт на длину индекса; 150*2 = 300, MS SQL Server для хранения и сравнения символов в национальных кодировках использует два байта на символ).

•Поле sb_is_active представлено типом char длиной в один символ (т.к. в MS SQL Server нет типа данных enum), а для соблюдения аналогии с MySQL на это поле наложено ограничение check со значением [sb_is_active] IN ('Y', 'N').

•Как и в MySQL, поля sb_start и sb_finish представлены типом date (а не более полными, например, datetime), т.к. мы храним дату выдачи и возврата книги с точностью до дня.

+PK_m2m_books_authors(int, int)

Рисунок 1.d — Модель базы данных для MS SQL Server в Sparx EA

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 9/545