Рис. 3
Роль базы данных — упростить взаимодействие, организацию и анализ связанных данных из различных источников. Данные сохраняются во взаимосвязанных таблицах, что дает возможность манипулировать ими более гибко.
Рис. 4
Строки в таблице называются записями. Также их можно называть кортежами. Столбцы в таблице, как правило, называются полями. Также их можно назвать атрибутами. Поля/атрибуты — это категории, используемые для определения данных в записи (строке).
Примечание
В этой книге для описания строк в таблице мы будем использовать термин «записи», а для описания столбцов — «поля». См. рис. 5 — основная терминология.
Рис. 5
Каждая запись разбита на несколько полей, представляющих собой отдельные элементы данных, которые описывают конкретный элемент. Например, в таблице на рис. 6 хранятся сведения о пациентах определенной больницы, медицинского учреждения или страхового фонда. В данном случае база данных, скорее всего, будет состоять из нескольких таблиц. Понимание того, как таблицы связаны друг с другом, — это и есть ключ к пониманию основной архитектуры базы данных.
Рис. 6
Основные элементы реляционных баз данных
Реляционная база данных — это конструкция базы данных, которая в 1969 году была официально утверждена ученым из IBM Эдгаром Франком Коддом. В следующем году Кодд опубликовал статью под названием «Реляционная модель данных для больших, совместно используемых банков данных» [8]. Девять лет спустя несколько крупных игроков в сфере технологий, в том числе IBM и Relational Software Inc. (позже ставшая Oracle), начали использовать реляционные базы данных в коммерческих целях. Четыре десятилетия спустя реляционная модель становится наиболее распространенной формой проектирования баз данных.
Чтобы получить элементарное представление о работе реляционных баз данных, важно понимать роль ключевых полей.
Реляционная база данных будет содержать множество таблиц, аналогичных таблице patient_info (рис. 7). Таблицы связаны друг с другом с помощью ключевых полей. Как вы видите на рисунке, таблица patient_info содержит поля, являющиеся первичным ключом и внешним ключом. Каждая таблица в реляционной базе данных должна содержать первичный ключ. Первичный ключ — это уникальный идентификатор записи в таблице. Первичный ключ каждой записи должен быть уникальным и не должен быть нулевым (пустым). Обратите внимание на поле PatientID в таблице patient_info. Поскольку это поле используется как первичный ключ, у каждой записи в таблице значение данного поля должно быть уникально. Иными словами, никакие две записи не могут иметь один и тот же PatientID.
Хотя первичный ключ (в данном случае PatientID) должен быть уникальным, другие поля могут содержать данные, повторяющиеся более чем в одной записи. Рассмотрим поле PrimaryCareDoctorID. Например, если Dr. Waynewright, ID 106547 (см. первую строку рис. 7), лечит нескольких пациентов из базы данных, то его имя и идентификатор могут встречаться в нескольких записях таблицы.
Примечание
В реляционной базе данных таблицы часто называют «связями», так как они содержат набор записей (строк), связанных с различными полями (столбцами). Однако в этой книге мы будем использовать термин «таблица». См. рис. 5 — «Основная терминология».
Внешний ключ — это поле в таблице, значение которого соответствует первичному ключу в другой таблице. Предположим, что в дополнение к нашей таблице patient_info в базе данных существует еще одна таблица с именем primary_care_doctors, в которой в качестве первичного ключа используется поле PrimaryCareDoctorID. В таблице primary_care_doctors строка Dr. Waynewright с ID 106547 появится только в одной записи. Именно совпадение различных ключевых полей в разных таблицах обеспечивает исключительно важную взаимосвязь в реляционной базе данных. Как правило, эти связи отображаются в виде схемы базы данных, также известной как диаграмма «сущность — связь» (ERD, Entity Relationship Diagram), которая служит своего рода эскизом для базы данных.
Рис. 7
Рис. 8
Пока не беспокойтесь, что означают символы 1 и ∞. Очень скоро мы к ним вернемся. А сейчас рассмотрим схему и ее взаимосвязи. В данной схеме всего четыре таблицы, и таблицы связаны друг с другом посредством одного или нескольких общих полей. Поле PatientID — это первичный ключ для таблицы patient_info и одновременно внешний ключ для таблицы lab_orders. Точно так же поле HospitalId — первичный ключ для таблицы hospitals, но это внешний ключ для таблицы primary_care_doctors. Очень просто, правда? Давайте рассмотрим другую схему.
Схема на рис. 9 описывает базу данных для обработки заказов и доставки товаров клиентам. И здесь пора поговорить о символах 1 и ∞, которые находятся на схеме на концах соединительных линий. Эти символы описывают связи между таблицами. Когда на одном конце соединительной линии находится символ 1, а на другом — символ ∞, это означает связь между полями таблиц «один-ко-многим».
Рассмотрим подробнее таблицу products (рис. 10). В ней представлены данные о различных товарах и их атрибутах.
Рис. 9
Рис. 10
Поле ProductId — первичный ключ для этой таблицы (обозначено значком ключа). Каждая запись в таблице будет содержать уникальный идентификационный номер товара. Фактически основная цель этой таблицы — каталогизировать атрибуты различных товаров базы данных. Теперь давайте проанализируем связи между таблицами products и suppliers.
Рис. 11
Рис. 12
Между таблицами suppliers и products существует связь «один-ко-многим», основанная на данных поля SupplierId. В таблице suppliers каждая запись будет иметь уникальный идентификационный номер для каждого поставщика. В таблице products может быть несколько записей с одним и тем же идентификационным номером поставщика.
Значок ключа, расположенный рядом с полем SupplierId в таблице suppliers, означает, что SupplierId — это первичный ключ для этой таблицы. В базе данных, безусловно, может содержаться большое количество разных товаров (каждый будет иметь уникальный идентификационный номер), поступающих от одного поставщика и каталогизированных в таблице products. Взгляните на таблицу suppliers, где должен быть только один уникальный идентификационный номер поставщика для каждой записи.
Рис. 13
Примечание
Данные в поле supplierId могут повторяться в нескольких записях в таблице products, но не в таблице suppliers.
Давайте проанализируем взаимосвязь между таблицами products, order_details и orders (рис. 14).
Рис. 14
Таблица order_details имеет два первичных ключа (обозначено значками ключей). Можно трактовать это как составной ключ, то есть для определения первичного ключа используются два или более поля. Хотя технически в примере задействовано два ключа, стоит рассматривать их как единый элемент — собственно первичный ключ.
Комбинация данных в полях, используемая для формирования составного ключа, работает как уникальный идентификатор для любой записи в таблице. Другими словами, если запись OrderId в таблице order_details равна 101, а ProductId той же записи — P006, то мы можем предположить, что никакая другая запись в таблице не будет иметь такую же комбинацию данных этих двух полей. Могут существовать одна или несколько других записей с OrderId, равным 101, а также одна или несколько других записей с ProductId, равным P006, но только одна запись может иметь и 101 в качестве своего OrderId, и P006 в качестве своего ProductId. Эта комбинация данных работает как составной ключ, который, как и любой первичный ключ, обеспечивает уникальный идентификатор для каждой записи в таблице.
Рис. 15
В связи «один-ко-многим» стандартный первичный ключ находится на стороне «один». Например, в таблице orders мы видим, что первичный ключ, поле OrderId, предоставляет уникальный идентификатор для каждой записи в таблице. Сторона связи «ко-многим» находится в таблице order_details. Как вы думаете, почему?
Давайте рассуждать логически. Можно сделать вывод, что роль таблицы order_details — предоставить информацию о различных заказанных товарах. Также допустимо, что любой товар может быть заказан несколько раз несколькими заказчиками при самых разных обстоятельствах, с разными ценами и т. д. Следовательно, поле ProductId не может использоваться само по себе в качестве первичного ключа для таблицы order_details. Кроме того, любой заказ может включать в себя несколько товаров, и если мы проанализируем другие поля, используемые в таблице order_details — UnitPrice, Quantity и Discount, тогда мы четко поймем, что эти поля обозначают свойства определенного товара, а не заказ в целом. Также поле OrderId не может использоваться само по себе в качестве первичного ключа для таблицы order_details. Решение состоит в том, чтобы объединить ProductId и OrderId в составной ключ, тем самым гарантируя, что данные, содержащиеся в столбцах UnitPrice, Quantity и Discount, будут соответствовать уникальному и определенному заказу и уникальному и определенному товару в этом заказе.
Типы данных
Ранее в этой главе мы описывали концепцию метаданных, которые представляют собой детальную информацию обо всех объектах системы. При разработке базы данных с использованием SQL для каждого используемого столбца должен быть назначен определенный тип данных. Типы данных могут незначительно отличаться в зависимости от используемой версии SQL. Однако, как правило, у вас будут числовые, символьные или текстовые типы данных, дата и время, а также логические значения. Рассмотрим каждый из них.
Числовые типы данных
В состав числовых данных входят целочисленные данные, которые служат для отображения целых чисел. Обычно, когда используется целочисленный тип данных, он имеет ограничения на длину. Напомним, что изображенная на рис. 7 таблица содержала сведения о пациентах. Для столбца Weight (Вес) разумно использовать целочисленный тип данных с ограничением до трех цифр. Почему?
1. Вполне достаточно округлить значение в большую и меньшую сторону до ближайшего значения фунта или килограмма и не использовать десятичные знаки.
2. Вряд ли нам понадобится более трех цифр для указания значения веса в фунтах.
Когда целочисленные данные не подходят и возникает необходимость в более точном числовом формате, мы можем использовать формат чисел с плавающей запятой. Как и целочисленные данные, они могут быть ограничены по длине.
Рис. 16*
Примечание
Для типов данных, допускающих более длинные диапазоны цифр, символов и т. д., требуется больший объем памяти в байтах. SQL также допускает денежные (финансовые) типы данных.
Символьные, или текстовые, типы данных
Символьные, или текстовые, типы данных (еще их называют «строковые») могут хранить строки символов как фиксированной, так и переменной длины. Например, если один из столбцов вашей базы данных содержит стандартные шестизначные почтовые индексы Канады (которые включают как цифры, так и буквы), то вам необходимо использовать символьный, или текстовый, тип данных со строкой фиксированной длины из шести символов. Если вы создавали столбец для хранения имени или фамилии клиента, вам необходимо использовать строку переменной длины с ограничением по максимальной и минимальной длине.
Рис. 17
Примечание
В рассмотренных ранее примерах использовались относительно короткие текстовые данные, такие как имена и адрес. Многие базы данных содержат текстовые поля, позволяющие хранить гораздо более длинные строки текста. Некоторые структуры баз данных позволяют каталогизировать многостраничные тексты или даже целые книги.
Дата и время
Дата и время — эти данные важны во многих случаях. SQL предлагает пользователям различные форматы даты и времени: YYYY-MM-DD (ГГГГ-ММ-ДД), YYYY-MM-DD HH: MI:SS (ГГГГ-ММ-ДД ЧЧ: МИ:СС), YY-MM-DD (ГГ-ММ-ДД). Вы также можете отформатировать столбец так, чтобы он содержал только год, в четырех- или двухзначном формате. Например, 2019 или просто 19. На рис. 18 показан пример использования данных даты и времени.
Примечание
В SQL форматы даты/времени содержат встроенные числовые значения, позволяющие базе данных обрабатывать запросы, где в качестве условия задан интервал времени. Например, если вы хотите узнать, сколько клиентов приобрели определенный товар в период с 1 октября 2020 г. по 31 декабря 2020 г., то с помощью SQL вы сможете сгенерировать и отсортировать эти данные.
Рис. 18
Логический тип данных
Логические значения — это данные, принимающие значения True (Правда) или False (Ложь). Например, если вы отвечаете за секретную операцию для правительства или частного лица, вы можете использовать базу данных для отслеживания уровня допуска сотрудников. Если вам необходимо найти список сотрудников с допусками A, B и D, но не обязательно C, то использование логического анализа данных может значительно упростить процесс. На рис. 19 показан пример использования логических данных.
Рис. 19
Примечание
В разных версиях SQL — разные списки распознаваемых типов данных. Некоторые версии SQL, такие как SQL Server и MySQL (описаны далее в этой главе), не дают пользователю возможности присвоить данным тип Boolean. Вместо этого они предоставляют тип данных Bit, который может быть легко преобразован в логический формат.