Пример проектирования простой базы данных в MS SQL
В качестве примера спроектируем несложную базу данных информационной системы кинотеатра. При этом, решим следующие задачи:
1 Анализ предметной области
Зачастую, кинотеатр состоит из нескольких залов разной конфигурации, а посетителю предоставляется возможность выбора билета, для этого ему отображается текущее состояние зала. Выбранные места посетитель сообщает кассиру, который вводит их в систему и места помечаются как «проданные». Это «основной» сценарий использования информационной системы, однако надо учесть следующее:
Из этого описания понятны основные функции системы, изображенные на рисунке с помощью нотации диаграммы прецедентов UML. На диаграмме не отображена роль администратора базы данных, так как администратор обычно взаимодействует с системой не через интерфейс, а через выполнение SQL-запросов.
Несмотря на то, что мы не будет разрабатывать интерфейс информационной системы и текстовые описания прецедентов, дальше нас будут интересовать данные, необходимые для выполнения того или иного прецедента, а для этого надо выделить и описать сущности. Иначе, невозможно определить «какие данные должен вводить менеджер при добавлении фильма». Основные сущности, данные которых потребуются во время работы, показаны на рисунке, при этом используется нотация диаграммы классов UML. Каждый прямоугольник соответствует одной сущности, внутри записаны поля и типы данных.
Каждая сущность, кроме hall_row содержит поле id, которое идентифицирует объект. У сущности hall_row поле id не нужно, так как в одном и том же зале кинотеатра (id_hall) не могут повторяться номера рядов (number).
Когда пользователь выберет зал и прокат — система должна отобразить заполненность зала, при этом надо отобразить конфигурацию зала с пометкой занятых и свободных мест. Под конфигурацией зала тут имеется ввиду, что разные залы имеют разный размер, а ряды зала могут иметь различное количество мест. Поэтому в базе данных зал (hall) составляется из рядов (hall_row), одним из параметров которых является вместимость (capacity).
2 Построение концептуальной модели
Выше были отображены основные сущности, но не отображены роли пользователей, хотя их тоже должна хранить система. Они показаны ниже на ER-диаграмме в нотации Чена [1].
На диаграмме выделены роли кассира и менеджера, а также основные отношения между сущностями. На диаграмме нет роли администратора, но его роль заключается в:
На диаграмме не отражена роль посетителя, так как:
На диаграмме проставлены кратности связей, например, видно, что один менеджер может добавить много (N) прокатов. В этой базе не оказалось связей типа N:M, сложных или рекурсивных связей — такие связи являются препятствиями в проектировании и решаются изменением ее структуры.
Для формирования схемы данных необходимо сначала дополнить ER-диаграмму реквизитами сущностей (уточнить ее) — результат приведен на рисунке.
В ходе анализа этой диаграммы были найдены несколько недочетов, допущенных при выделении сущностей системы:
Исправленная ER-диаграмма приведена ниже:
Таблица менеджеров и кассиров не объединены в таблицу Users так как вопросы разграничения прав доступа в различных СУБД решаются по-разному. Так, в MS SQL пользователи добавляются с помощью специальных запросов типа:
CREATE LOGIN Manager_Name WITH PASSWORD=’Some Passwrd’;
при этом вообще нет необходимости хранить информацию об их логинах и паролях в таблицах. Однако, вопросы разграничения доступа решаются позже — на этапе физического проектирования.
3 Физическое проектирование
ER-диаграмма отражает основные таблицы, связи и атрибуты, на ее основе можно построить модель БД. На ER-диаграммы нет стандарта, но есть ряд нотаций (Чена, IDEFIX, Мартина и т.п.) [2], но на модель предметной области не удалось найти ни стандарта, ни нотаций. Однако, в ходе построения такой диаграммы обязательно выделяются ключевые поля (внешние и внутренние), иногда — индексы и типы данных. Схема базы данных, приведенная на рисунке, выполнена с использованием открытого инструмента plantuml [3], при этом:
Своя СУБД за 3 недели. Нужно всего лишь каждый день немного времени…
Своя СУБД за 3 недели. Нужно всего-лишь каждый день немного времени уделять архитектуре; и всё остальное время вкалывать на результат, печатая и перепечатывая сотни строк кода.
По закону Мерфи, если есть более одного проекта на выбор — я возьмусь за самый сложный из предложенных. Так случилось и с последним заданием курса о системах управления базами данных (СУБД).
Постановка задачи
Согласно ТЗ, требовалось создать СУБД с нуля на Vanilla Python 3.6 (без сторонних библиотек). Конечный продукт должен обладать следующими свойствами:
Подход к проектированию
Разработать СУБД с нуля казалось нетривиальной задачей (как ни странно, так оно и оказалось). Поэтому мы — ecat3 и @ratijas — подошли к этому делу научно. В команде нас только двое (не считая себя и мою персону), а значит распиливать задачи и координировать их выполнение в разы легче, чем, собственно, их выполнять. По окончании распила вышло следующе:
| Задача | Исполнитель(-и) |
|---|---|
| Парсер + AST + REPL | ratijas, написавший не один калькулятор на всяких lex/yacc |
| Структура файла базы | ecat3, съевший собаку на файловых системах |
| Движок (низкоуровневые операции над базой) | ecat3 |
| Интерфейс (высокоуровневая склейка) | Вместе |
Изначально времени было выделено две недели, поэтому предполагалось выполнить индивидуальные задачи за неделю, а оставшееся время совместно уделить склейке и тестированию.
С формальной частью закончили, перейдем к практической. СУБД должна быть современной и актуальной. В современном Иннополисе актуальным является месседжер Телеграм, чат-боты и общение в группах с помощью «/слештегов» (это как #хештеги, только начинаются со /слеша). Поэтому язык запросов, близко напоминающий SQL, мало того что не чувствителен к регистру, так ещё и не чувствителен к /слешу непосредственно перед любым идентификатором: ‘SELECT’ и ‘/select’ это абсолютно одно и то же. Кроме того, подобно всякому студенту университета, каждая команда (statement) языка должна завершаться ‘/drop’. (Конечно же, тоже независимо от регистра. Кого вообще в такой ситуации волнует какой-то там регистр?)
Так родилась идея названия: dropSQL. Собственно /drop‘ом называется отчисление студента из университета; по некоторым причинам, это слово получило широкое распространение у нас в Иннополисе. (Ещё один местный феномен: аутизм, или, более корректно, /autism. Но мы приберегли его на особый случай.)
Первым делом разложили грамматику dropSQL на BNF (и зря — левые рекурсии не подходят нисходящим парсерам).
Работаем на результат! Никаких исключений!
После нескольких месяцев с Rust в качестве основного языка, крайне не хотелось снова погрязнуть в обработке исключений. Очевидным аргументом против исключений является то, что разбрасываться ими дорого, а ловить — громоздко. Ситуация ухудшается тем, что Python даже в версии 3.6 с Type Annotations, в отличие от той же Java, не позволяет указать типы исключений, которые могут вылететь из метода. Иначе говоря: глядя на сигнатуру метода, должно стать ясно, чего от него ожидать. Так почему бы не объеденить эти типы под одной крышей, которая называется «enum Error»? А над этим создать ещё одну «крышу» под названием Result; о нём пойдёт речь чуть ниже. Конечно, в стандартной библиотеке есть места, которые могут «взорваться»; но такие вызовы в нашем коде надежно обложены try’ями со всех сторон, которые сводят любое исключение к возврату ошибки, минимизируя возникновение внештатных ситуаций во время исполнения.
Итак, было решено навелосипедить алгебраический тип результата (привет, Rust). В питоне с алгебраическими типами всё плохо; а модуль стандартной библиотеки enum больше напоминает чистую сишечку.
В худших традициях ООП, используем наследование для определения конструкторов результата: Ok и Err. И не забываем про статическую типизацию (Ну мааам, у меня уже третья версия! Можно у меня будет наконец статическая типизация в Python, ну пожаалуйста?):
Отлично! Приправим немного соусом из функциональных преобразований. Далеко не все понадобятся, так что возьмем необходимый минимум.
И сразу пример использования:
Писанина в таком стиле всё ещё занимает по 3 строчки на каждый вызов. Но зато приходит четкое понимание, где какие ошибки могли возникнуть, и что с ними произойдет. Плюс, код продолжается на том же уровне вложенности; это весьма важное качество, если метод содержит полдюжины однородных вызовов.
Парсер, IResult, Error::, REPL, 9600 бод и все-все-все
Парсер занял значительную часть времени разработки. Грамотно составленный парсер должен обеспечить пользователю удобство использования фронтэнда продукта. Важнейшими задачами парсера являются:
Один из первых вопросов, требовавших ответа: как быть с ошибками? Как быть — выше уже разобрались. Но что вообще представляет собой ошибка? Например, после «/create table» может находиться «if not exists», а может и не находиться — ошибка ли это? Если да, то какого рода? где должна быть обработана? («Поставьте на паузу», и предложите свои варианты в комментариях.)
Первая версия парсера различала два вида ошибок: Expected (ожидали одно, а получили что-то другое) и EOF (конец файла) как подкласс предыдущего. Всё бы ничего, пока дело не дошло до REPL. Такой парсер не различает между частиным вводом (началом команды) и отсутствием чего-либо (EOF, если так можно сказать про интерактивный ввод с терминала). Только неделю спустя, методом проб и ошибок удалось найти схему, удовлетворяющую нашей доменной области.
Схема состоит в том, что всё есть поток, а парсер — лишь скромный метод next() у потока. А также в том, что класс ошибки должен быть переписан на алгебраический (подобно Result), и вместо EOF введены варианты Empty и Incomplete.
А самое приятное, что поток можно «собрать» в один большой список всех IOk(элементов), что выдает next() — до первой IErr(), разумеется. При чем список вернется лишь когда IErr содержала Empty; в противном случае, ошибка пробросится выше. Эта конструкция позволяет легко и элегантно построить REPL.
Characters
Этот поток проходит по символам строки. Единственный тип ошибки: Empty в конце строки.
Tokens
Поток токенов. Его второе имя — Лексер. Тут встречаются и ошибки, и строки без закрывающих кавычек, и всякое…
Каждый тип токенов, включая каждое ключевое слово по отдельности, представлен отдельным классом-вариантом абстрактного класса Token (или лучше думать про него как про enum Token?) Это для того, чтобы парсеру команд (statements) было удобно кастовать токены к конкретным типам.
Statements
Кульминация, парсер собственной персоной. Вместо тысячи слов, пару сниппетов:
Про бинарный формат
Глобально файл базы поделен на блоки, и размер файла кратен размеру блока. Размер блока по умолчанию равен 12 КиБ, но опционально может быть увеличен до 18, 24 или 36 КиБ. Если Вы дата сатанист на магистратуре, и у вас очень большие данные — можно поднять даже до 42 КиБ.
Блоки пронумерованы с нуля. Нулевой блок содержит метаданные обо всей базе. За ним 16 блоков под метаданные таблиц. С блока #17 начинаются блоки с данными. Указателем на блок называется порядковый номер блока
Метаданных базы на текущий момент не так много: название длиной до 256 байт и кол-во блоков с данными.
Мета-блок таблицы самый непростой. Тут хранится название таблицы, список всех колонок с их типами, количество записей и указатели на блоки данных.
Количество таблиц фиксировано в коде. Хотя это относительно легко может быть исправлено, если хранить указатели на мета-блоки таблиц в мета-блоке базы.
Указатели на блоки работают по принципу указателей в inode. Об этом прекрасно писал Таненбаум и дюжины других уважаемых людей. Таким образом, таблицы видят свои блоки с данными как «страницы». Разница в том, что страницы, идущие с точки зрения таблицы по порядку, физически располагаются в файле как бог на душу положит. Проводя аналогии с virtual memory в операционках, страница: virtual page number, блок: physical page number.
Блоки данных не имеют конкретной структуры сами по себе. Но когда их объеденить в порядке, продиктованном указателями, они предстанут непрерывным потоком записей (record / tuple) фиксированной длины. Таким образом, зная порядковый номер записи, извлечь или записать её — операция практически константного времени, O(1 * ), с амортизацией на выделение новых блоков при необходимости.
Первый байт записи содержит информацию о том, жива ли эта запись, или была удалена. Остальные работу по упаковке и распаковке данных берет на себя стандартный модуль struct.
Операция /update всегда делает перезапись «in-place», а /delete только подменяет первый байт. Операция VACUUM не поддерживается.
Про операции над таблицами, RowSet и джойны
Пришло время прочно скрепить лучшее из двух миров несколькими мотками скотча.
MC справа — драйвер БД, оперирующий над записями по одной за раз, используя порядковый номер внутри таблицы как идентификатор. Только он знает, какие таблицы существуют, и как с ними работать.
Их первый совместный сингл про креативность. Создать новую таблицу не сложнее, чем взять первый попавшийся пустой дескриптор из 16, и вписать туда название и список колонок.
Затем, трек с символическим названием /drop. В домашней версии демо-записи происходит следующее: 1) найти дескриптор таблицы по имени; 2) обнулить. Кого волнуют неосвобожденные блоки со страницами данных?
Insert снисходителен к нарушению порядка колонок, поэтому перед отправкой кортежа на запись, его пропускают через специальный фильтр transition_vector.
Далее речь пойдет про работу с записями таблиц, поэтому позвольте сразу представить нашего рефери: RowSet.
Главный конкретный подвид этого зверя — TableRowSet — занимается выборкой всех живых (не удаленных) записей по порядку. Прочие разновидности RowSet в dropSQL реализуют необходимый минимум реляционной алгебры.
| Оператор реляционной алгебры | Обозначение | Класс |
|---|---|---|
| Проекция | π(ID, NAME) expr | |
| Переименование | ρa/b expr | |
| Выборка | σ(PRICE>90) expr | |
| Декартово произведение | PRODUCTS × SELLERS | |
| Inner Join (назовём это расширением) | σ(cond)( A x B ) |
Кроме того ещё есть программируемый MockRowSet. Он хорош для тестирования. Также, с его помощью возможен доступ к мастер-таблице под названием «/autism«.
Два последних: /update и /delete используют наработки предшественника. При чем /update применяет технику, похожую на описанный выше «transition_vector».
Такой вот концерт! Спасибо за внимание! Занавес.
Хотелки
Пока что не сбывшиеся мечты:
Создание базы данных SQL и работа с таблицами в SQL
В этой статье мы рассмотрим создание базы данных SQL и создание таблицы SQL, используя команды в клиенте mysql. Предполагается, что этот инструмент запущен и подключен к серверу базы данных MySQL.
Создание новой базы данных MySQL
Новая база данных создается с помощью оператора SQL CREATE DATABASE, за которым следует имя создаваемой базы данных. Для этой цели также используется оператор CREATE SCHEMA. Например, для создания новой базы данных под названием MySampleDB в командной строке mysql нужно ввести следующий запрос:
Если все прошло нормально, команда сгенерирует следующий вывод:
Если указанное имя базы данных конфликтует с существующей базой данных MySQL, будет выведено сообщение об ошибке:
В этой ситуации следует выбрать другое имя базы данных или использовать опцию IF NOT EXISTS. Она создает базу данных только в том случае, если она еще не существует:
Создание таблицы SQL
Новые таблицы добавляются в существующую базу данных с помощью оператора CREATE TABLE SQL. За оператором CREATE TABLE следует имя создаваемой таблицы, а далее через запятые список имен и определений каждого столбца таблицы:
CREATE TABLE имя_таблицы ( определение имени_столбца, определение имени_таблицы …, PRIMARY KEY = (имя_столбца) ) ENGINE = тип_движка;
В определении столбца задается тип данных, может ли столбец быть NULL, AUTO_INCREMENT. Оператор CREATE TABLE также позволяет указать столбец (или группу столбцов) в качестве первичного ключа.
Прежде чем будет создавать таблицу, нужно выбрать базу данных. Это делается с помощью оператора SQL USE:
Значения NULL и NOT NULL
Если для столбца указано значение NULL, тогда пустые строки будут добавляться в таблицу. И наоборот, если столбец определяется как NOT NULL, тогда пустые строки не будут добавлены.
Первичные ключи
Первичный ключ — это столбец, используемый для идентификации записей в таблице. Значение столбца первичного ключа должно быть уникальным. Если несколько столбцов объединены в первичный ключ, то комбинация значений ключей должна быть уникальной для каждой строки.
Первичный ключ определяется с помощью оператора PRIMARY KEY во время создания таблицы. Если используется несколько столбцов, они разделяются запятой:
В следующем примере создается таблица с использованием двух столбцов в качестве первичного ключа:
AUTO_INCREMENT
Когда столбец определяется с помощью AUTO_INCREMENT, его значение автоматически увеличивается каждый раз, когда в таблицу добавляется новая запись. Это удобно при использовании столбца в качестве первичного ключа. Благодаря AUTO_INCREMENTне нужно писать инструкции SQL для вычисления уникального идентификатора для каждой строки.
AUTO_INCREMENT может быть присвоен только одному столбцу в таблице. И он должен быть проиндексирован (например, объявлен в качестве первичного ключа).
Значение AUTO_INCREMENT для столбца можно переопределить, указав новое при выполнении инструкции INSERT.
Можно запросить у MySQL самое последнее значение AUTO_INCREMENT, используя функцию last_insert_id() следующим образом:
Определение значений по умолчанию при создании таблицы
Значения по умолчанию используются, когда значение не определено при вставке в базу данных.
Значения по умолчанию задаются с помощью ключевого слова DEFAULT в операторе CREATE TABLE. Например, приведенный ниже запрос SQL задает значение по умолчанию для столбца sales_quantity:
Типы движков баз данных MySQL
Движки различных типов могут сочетаться в одной базе данных. Например, некоторые таблицы могут использовать движок InnoDB, а другие — MyISAM. Если во время создания таблицы движок не указывается, то по умолчанию MySQL будет использовать MyISAM.
Чтобы указать тип движка, который будет использоваться для таблицы, о поместите соответствующее определение ENGINE= после определения столбцов таблицы:
Пожалуйста, опубликуйте ваши комментарии по текущей теме статьи. За комментарии, отклики, лайки, дизлайки, подписки низкий вам поклон!
Пожалуйста, опубликуйте ваши мнения по текущей теме материала. За комментарии, отклики, подписки, дизлайки, лайки низкий вам поклон!
Урок 1. Создание объектов базы данных и отправка запросов к ним
На этом занятии вы узнаете, как создать базу данных, создать таблицу в базе данных и получить доступ к данным таблицы и изменить их. Поскольку это занятие является введением к использованию языка Transact-SQL, в нем не используются и не описываются многие параметры, доступные для этих инструкций.
Transact-SQL могут быть написаны и пересланы компоненту Компонент Database Engine следующими способами:
При помощи среды SQL Server Management Studio. Предполагается, что вы используете среду Среда Management Studio, но можно также использовать среду Среда Management Studio Express, которая может быть загружена бесплатно с веб-узла центра загрузки Майкрософт.
Соединившись из создаваемого приложения.
Исходный код исполняется в компоненте Компонент Database Engine таким же образом и с теми же разрешениями, независимо от того, как был передан исходный код инструкций.
Чтобы выполнить инструкцию языка Transact-SQL в среде Среда Management Studio, откройте среду Среда Management Studio и соединитесь с экземпляром компонента Компонент SQL Server Database Engine.
Предварительные требования
Для работы с этим руководством необходима среда SQL Server Management Studio и доступ к экземпляру SQL Server.
Если у вас нет экземпляра SQL Server, создайте его. Чтобы создать экземпляр, выберите свою платформу по следующим ссылкам. При выборе проверки подлинности SQL используйте учетные данные SQL Server.
Создание базы данных
Как и у многих инструкций языка Transact-SQL, у инструкции CREATE DATABASE имеется обязательный параметр: имя базы данных. CREATE DATABASE Кроме этого, у инструкции имеется ряд необязательных параметров, таких как расположение на диске, где требуется хранить файлы базы данных. При выполнении инструкции CREATE DATABASE без дополнительных параметров для многих из них SQL Server использует значения по умолчанию.
В окне редактора запросов введите, но не выполняйте, следующий код:
С помощью указателя выделите слова CREATE DATABASE и нажмите клавишу F1. Должен открыться раздел CREATE DATABASE электронной документации на Microsoft SQL Server. Таким же способом можно найти полный синтаксис инструкции CREATE DATABASE и других инструкций, используемых в данном учебнике.
При создании базы данных сервер SQL Server создает копию базы данных model и присваивает ей указанное имя базы данных. Эта операция обычно занимает несколько секунд, если только с помощью дополнительного параметра не указан большой исходный размер базы данных.
Когда в одном пакете представлено несколько инструкций, они разделяются с помощью ключевого слова GO. Ключевое слово GO является необязательным, если в пакете содержится только одна инструкция.
Создание таблицы
Чтобы создать таблицу, нужно указать имя таблицы, имена и типы данных для каждого столбца таблицы. Также рекомендуется указывать, допускаются ли значения NULL для каждого из столбцов. Для создания таблицы необходимо иметь разрешение CREATE TABLE и разрешение ALTER SCHEMA для схемы, которая будет содержать таблицу. Предопределенная роль базы данных db_ddladmin имеет эти разрешения.
Большинство таблиц имеют первичный ключ, состоящий из одной или нескольких столбцов таблицы. Первичный ключ всегда уникален. Компонент Компонент Database Engine потребует выполнения условия неповторения значения первичного ключа в таблице.
Список типов данных и ссылки на их описание см. в разделе Типы данных (Transact-SQL).
Компонент Компонент Database Engine может быть установлен с учетом регистра и без учета регистра. Если компонент Компонент Database Engine установлен с учетом регистра, имена объектов должны иметь одно и тоже имя. Например, таблица с именем OrderData будет отличаться от таблицы ORDERDATA. Если компонент Компонент Database Engine установлен без учета регистра, эти два имени таблицы будут рассматриваться как одна таблица, то есть имя может быть использовано только один раз.
Переключение соединения редактора запросов на базу данных TestData
Создание таблицы
Вставка данных в таблицу и их обновление
После создания таблицы Products в нее можно вставлять данные с помощью инструкции INSERT. После вставки данных содержимое строки изменяется с помощью инструкции UPDATE. Предложение WHERE предназначено для ограничения числа строк, изменяемых в процессе выполнения инструкции UPDATE до одной строки. Чтобы ввести следующие данные, потребуется четыре инструкции.
| ProductID | ProductName | Цена | ProductDescription |
|---|---|---|---|
| 1 | Clamp | 12,48 | Workbench clamp |
| 50 | Screwdriver | 3,17 | Flat head |
| 75 | Tire Bar | Tool for changing tires. | |
| 3000 | 3 mm Bracket | 0,52 |
Базовый синтаксис: INSERT, имя таблицы, список столбцов, VALUES, а затем список добавляемых значений. Два дефиса в начале строки означают, что строка является примечанием и текст не будет обрабатываться компилятором. В этом случае примечание описывает возможные варианты синтаксиса.
Вставка данных в таблицу
Если вставка выполнена, перейдите к следующему шагу.
Если вставка завершается сбоем, это может быть вызвано тем, что в таблице Product уже есть строка с таким ИД продукта. Чтобы продолжить, удалите все строки в таблице и повторите предыдущий шаг. TRUNCATE TABLE удаляет все строки в таблице.
Выполните следующую команду, чтобы удалить все строки в таблице:
После усечения таблицы повторите команду INSERT на этом шаге.
Обновление таблицы продуктов
Чтение данных из таблицы
Чтение данных в таблице
Полезные функции в инструкции SELECT
Сведения о работе с функциями, которые используются в инструкциях SELECT, см. в следующих разделах: