Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
Глава 1 Введение в базы данных и SQL Эта глава — вводная. В ней мы расскажем об основах баз данных, о том, что такое реляционная мо- дель и зачем нужен язык SQL. Очень важной темой этой главы станет описание предметной области, на основе которой будет спроектирована учебная база данных, которая и будет служить в качестве площадки для изучения языка SQL. Это пособие предназначено в первую очередь для практического освоения языка SQL, а не для изучения теории баз данных, поэтому для изучения теории необходимо обращаться к авторитетным источникам, список которых приведен в конце учебного пособия. 1.1. Что такое базы данных и зачем они нужны Технологии баз данных существовали не всегда. Однако и до их внедрения в практику люди также собирали и обрабатывали данные. Одним из способов хранения данных были так называемые плоские файлы (flat files), которые имели очень простую струк- туру: данные хранились в виде записей, разделенных на поля фиксированной длины. В реальной жизни между элементами данных зачастую возникают сложные связи, которые необходимо перенести и в электронную базу данных. При использовании плоских файлов эти связи организовать сложно, а еще сложнее поддерживать их при изменениях и удалениях отдельных элементов данных. Одним из основных понятий в теории баз данных является модель данных. Мож- но сказать, что она характеризует способ организации данных и основные методы доступа к ним. Сначала были предложены иерархическая и сетевая модели данных. Однако в ходе эволюции теорий и идей была разработана реляционная модель дан- ных, которая сейчас и является доминирующей. Поэтому в настоящее время преоб- ладают базы данных реляционного типа. Их характерной чертой является тот факт, что данные воспринимаются пользователем как таблицы. В распоряжении пользова- теля имеются операторы для выборки данных из таблиц, а также для вставки новых данных, обновления и удаления имеющихся данных. Одним из достоинств реляционной базы данных является ее способность поддержи- вать связи между элементами данных, избавляя программиста от необходимости за- ниматься этой рутинной и очень трудоемкой работой. В те времена, когда технологии 13 Глава 1. Введение в базы данных и SQL реляционных баз данных еще не получили широкого распространения, программи- стам приходилось на процедурных языках вручную реализовывать такие операции, которые сейчас называются каскадным обновлением внешних ключей или каскад- ным удалением записей из подчиненных таблиц (файлов). Здесь слово «вручную» означает, что для выполнения этих операций приходилось писать код, состоящий из элементарных команд, позволяющий добраться до каждой обновляемой или удаляе- мой записи. Тот подход к работе с базами данных назывался навигационным — про- граммист указывал программе конкретный алгоритм поиска записей. Приведем в качестве примера простую ситуацию: в базе данных, построенной на основе файлов, хранится информация о студентах и их экзаменационных оценках, причем личные данные студентов хранятся в одном файле, назовем его условно «Студенты», а экза- менационные оценки — в другом файле, который условно назовем «Успеваемость». Если требуется удалить информацию о конкретном студенте и его экзаменационных оценках, то придется не только выполнить операцию удаления конкретной записи из файла «Студенты», но дополнительно организовать цикл для поиска и удаления тех записей из файла «Успеваемость», у которых ключевое поле имеет то же значение, что и поле в удаляемой записи из файла «Студенты». Работая с реляционными базами данных, программист избавлен от программирова- ния на «атомарном» уровне, потому что современные языки для «общения» с этими базами данных являются декларативными. Это означает, что для получения резуль- тата достаточно лишь указать, что нужно получить, но не требуется предписывать способ получения результата, т. е. как его получить. Система баз данных — это компьютеризированная система, предназначенная для хранения, переработки и выдачи информации по запросу пользователей. Такая си- стема включает в себя программное и аппаратное обеспечение, сами данные, а также пользователей. Современные системы баз данных являются, как правило, многопользовательски- ми. В таких системах одновременный доступ к базе данных могут получить сразу несколько пользователей. Основным программным обеспечением является система управления базами дан- ных. По-английски она называется database management system (DBMS). Кроме СУБД в систему баз данных могут входить утилиты, средства для разработки приложений (программ), средства проектирования базы данных, генераторы отчетов и др. Пользователи систем с базами данных подразделяются на ряд категорий. Первая ка- тегория — это прикладные программисты. Вторая категория — это конечные поль- зователи, ради которых и выполняется вся работа. Они могут получить доступ к базе 14 1.2. Основные понятия реляционной модели данных, используя прикладные программы или универсальные приложения, кото- рые входят в программное обеспечение самой СУБД. В большинстве СУБД есть так называемый процессор языка запросов, который позволяет пользователю вводить команды языка высокого уровня (например, языка SQL). Третья категория пользо- вателей — это администраторы базы данных. В их обязанности входят: создание ба- зы данных, выбор оптимальных режимов доступа к ней, разграничение полномочий различных пользователей на доступ к той или иной информации в базе данных, вы- полнение резервного копирования базы данных и т. д. Систему баз данных можно разделить на два главных компонента: сервер и набор клиентов (или внешних интерфейсов). Сервер — это и есть СУБД. Клиентами являют- ся различные приложения, написанные прикладными программистами, или встро- енные приложения, поставляемые вместе с СУБД. Один сервер может обслуживать много клиентов. Современные СУБД включают в себя словарь данных. Это часть базы данных, которая описывает сами данные, хранящиеся в ней. Словарь данных помогает СУБД выпол- нять свои функции. 1.2. Основные понятия реляционной модели В каждой технологической сфере есть своя терминология. Существуют базовые тер- мины, на которых основываются все дальнейшие рассуждения. Такие термины при- сутствуют и в сфере баз данных. Сейчас мы кратко о них поговорим. В эпоху, предшествующую рождению реляционной теории, базы данных традици- онно рассматривались как набор файлов, состоящих из записей, а записи, в свою очередь, подразделялись на отдельные поля. Поле являлось элементарной единицей данных. В реляционных базах данных пользователь воспринимает данные в виде таблиц. По- этому термину «файл» соответствует термин «таблица», вместо термина «запись» используется термин «строка», а вместо термина «поле» — термин «столбец» (или «колонка» ). Таким образом, таблицы состоят из строк и столбцов, на пересечении которых должны находиться «атомарные» значения, которые нельзя разбить на бо- лее мелкие элементы без потери смысла. В формальной теории реляционных баз данных эти таблицы называют отношения- ми (relations) — поэтому и базы данных называются реляционными. Отношение — 15 Глава 1. Введение в базы данных и SQL это математический термин. При определении свойств таких отношений использу- ется теория множеств. В терминах данной теории строки таблицы будут называться кортежами (tuples) , а колонки — атрибутами. Отношение имеет заголовок, кото- рый состоит из атрибутов, и тело, состоящее из кортежей. Количество атрибутов на- зывается степенью отношения, а количество кортежей — кардинальным числом. Кроме теории множеств, одним из оснований реляционной теории является такой раздел математической логики, как исчисление предикатов. Таким образом, в теории и практике баз данных существует три группы терми- нов. Иногда термины из разных групп используют в качестве синонимов, например, запись и строка. Как мы уже сказали выше, в реляционных базах данных пользователь воспринимает данные в виде таблиц. Рассмотрим простую систему, в которой всего две таблицы. Первая — «Студенты»: № зачетной книжки Ф. И. О. Серия документа Номер документа 55500 Иванов Иван Петрович 0402 645327 55800 Климов Андрей Иванович 0402 673211 55865 Новиков Николай Юрьевич 0202 554390 И вторая — «Успеваемость»: Зачетная книжка Предмет Учебный год Семестр Оценка 55500 Физика 2016/2017 1 5 55500 Математика 2016/2017 1 4 55800 Физика 2016/2017 1 4 55800 Физика 2016/2017 2 3 При работе с базами данных часто приходится следовать различным ограничени- ям , которые могут быть обусловлены спецификой конкретной предметной области. Упрощая реальную ситуацию, примем следующие ограничения: – номер зачетной книжки состоит из пяти цифр и не может быть отрицательным (в разных вузах используются различные схемы присваивания номеров зачетным книжкам, эти схемы могут быть гораздо сложнее принятой нами и могут учиты- вать, например, год поступления студента в вуз); – серия документа, удостоверяющего личность, является четырехзначным числом, а номер документа, удостоверяющего личность, — шестизначным числом; 16 1.2. Основные понятия реляционной модели – номер семестра может принимать только два значения — 1 (осенний семестр) и 2 (весенний семестр); – оценка может принимать только три значения — 3 (удовлетворительно), 4 (хоро- шо) и 5 (отлично): другие оценки выставлять в зачетные книжки не принято. Для идентификации строк в таблицах и для связи таблиц между собой используются так называемые ключи. Потенциальный ключ — это комбинация атрибутов табли- цы, позволяющая уникальным образом идентифицировать строки в ней. Ключ может состоять только лишь из одного атрибута таблицы. Например, в таблице «Студенты» таким идентификатором может быть атрибут «Номер зачетной книжки». В качестве потенциального ключа данной таблицы могут также служить два ее атрибута, взятые вместе: «Серия документа, удостоверяющего личность» и «Номер документа, удо- стоверяющего личность». Ни один из них в отдельности не может использоваться в качестве уникального идентификатора. В таком случае ключ будет составным. При этом важным является то, что потенциальный ключ должен быть неизбыточным, т. е. никакое подмножество атрибутов, входящих в него, не должно обладать свойством уникальности. Потенциальный ключ, включающий два упомянутых атрибута, явля- ется неизбыточным. Ключи нужны для адресации на уровне строк (записей). При наличии в таблице более одного потенциального ключа один из них выбирается в качестве так называемого первичного ключа , а остальные будут являться альтернативными ключами. Рассмотрим таблицы «Студенты» и «Успеваемость». Предположим, что в таблице «Студенты» нет строки с номером зачетной книжки 55900, тогда включать строку с таким номером зачетной книжки в таблицу «Успеваемость» не имеет смысла. Та- ким образом, значения столбца «Номер зачетной книжки» в таблице «Успеваемость» должны быть согласованы со значениями такого же столбца в таблице «Студенты». Атрибут «Номер зачетной книжки» в таблице «Успеваемость» является примером то- го, что называется внешним ключом. Таблица, содержащая внешний ключ, назы- вается ссылающейся таблицей (referencing table). Таблица, содержащая соответству- ющий потенциальный ключ, называется ссылочной (целевой) таблицей (referenced table). В таких случаях говорят, что внешний ключ ссылается на потенциальный ключ в ссылочной таблице. Внешний ключ может быть составным, т. е. может включать более одного атрибута. Внешний ключ не обязан быть уникальным. Проблема обес- печения того, чтобы база данных не содержала неверных значений внешних ключей, известна как проблема ссылочной целостности. Ограничение, согласно которому значения внешних ключей должны соответствовать значениям потенциальных клю- чей, называется ограничением ссылочной целостности (ссылочным ограниче- нием) 17 Глава 1. Введение в базы данных и SQL Обеспечением выполнения ограничений ссылочной целостности занимается СУБД, а от разработчика требуется лишь указать атрибуты, служащие в качестве внешних ключей. При проектировании баз данных часто предусматривается, что при удале- нии строки из ссылочной таблицы соответствующие строки из ссылающейся таблицы должны быть также удалены, а при изменении значения столбца, на который ссыла- ется внешний ключ, должны быть изменены значения внешнего ключа в ссылающей- ся таблице. Этот подход называется каскадным удалением (обновлением). Иногда применяются и другие подходы. Например, вместо удаления строк из ссыла- ющейся таблицы в этих строках просто заменяют значения атрибутов, входящих во внешний ключ, так называемыми NULL-значениями. Это специальные значе- ния, означающие «ничто» или отсутствие значения, они не совпадают со значением «нуль» или «пустая строка». NULL-значение применяется в базах данных и в качестве значения по умолчанию, когда пользователь не ввел никакого конкретного значения. Первичные ключи не могут содержать NULL-значений. Транзакция — одно из важнейших понятий теории баз данных. Она означает набор операций над базой данных, рассматриваемых как единая и неделимая единица ра- боты, выполняемая полностью или не выполняемая вовсе, если произошел какой- то сбой в процессе выполнения транзакции. Таким образом, транзакции являются средством обеспечения согласованности данных. В нашей базе данных транзакцией могут быть, например, две операции: удаление строки из таблицы «Студенты» и уда- ление связанных по внешнему ключу строк из таблицы «Успеваемость». 1.3. Что такое язык SQL Язык SQL — это непроцедурный язык, который является стандартным средством ра- боты с данными во всех реляционных СУБД. Операторы (команды), написанные на этом языке, лишь указывают СУБД, какой результат должен быть получен, но не опи- сывают процедуру получения этого результата. СУБД сама определяет способ вы- полнения команды пользователя. В языке SQL традиционно выделяются группа опе- раторов определения данных (Data Definition Language — DDL), группа операторов манипулирования данными (Data Manipulation Language — DML) и группа опера- торов, управляющих привилегиями доступа к объектам базы данных (Data Control Language — DCL). К операторам языка определения данных (DDL) относятся команды для создания, изменения и удаления таблиц, представлений и других объектов базы данных. Де- тальному рассмотрению этих команд посвящены главы 5 и 8. 18 1.4. Описание предметной области и учебной базы данных К операторам языка манипулирования данными (DML) относятся команды для вы- борки строк из таблиц, вставки строк в таблицы, обновления и удаления строк. Эти команды подробно рассматриваются в главах 6 и 7. Операторы DCL в пособии не рассматриваются, т. к. PostgreSQL позволяет на началь- ном этапе изучения языка SQL обойтись без их использования. 1.4. Описание предметной области и учебной базы данных Чтобы показать все основные возможности языка SQL, нам потребуется база данных. Эта база данных не должна быть слишком сложной, чтобы ее изучение не потребо- вало слишком много времени. Но вместе с тем она должна быть достаточно разнооб- разной, чтобы запросы к ней выглядели бы правдоподобными, почти такими же, как и в реальной работе. В качестве предметной области выберем пассажирские авиаперевозки. Ее ориги- нальное описание и описание базы данных «Авиаперевозки» можно найти по адре- сам https://postgrespro.ru/education/demodb и https://postgrespro.ru/docs/postgrespro/ current/demodb-bookings.html. Надеемся, что эта область знакома многим читателям нашего учебного пособия. Конечно, в учебных целях реальная ситуация намеренно упрощена, но все принципиальные вещи сохранены. Итак, некая российская авиакомпания выполняет пассажирские авиаперевозки. Она обладает своим парком самолетов различных моделей. Каждая модель самолета име- ет определенный код, который присваивает Международная ассоциация авиапере- возчиков (IATA). При этом будем считать, что самолеты одной модели имеют оди- наковые компоновки салонов, т. е. порядок размещения кресел и нумерацию мест в салонах бизнес-класса и экономического класса. Например, если это модель Sukhoi SuperJet-100, то место 2A относится к бизнес-классу, а место 20D — к экономическому классу. Бизнес-класс и экономический класс — это разновидности так называемого класса обслуживания. Наша авиакомпания выполняет полеты между аэропортами России. Каждому аэро- порту присвоен уникальный трехбуквенный код, при этом используются только за- главные буквы латинского алфавита. Эти коды присваивает не сама авиакомпания, а специальные организации, управляющие пассажирскими авиаперевозками. Зача- стую название аэропорта не совпадает с названием того города, которому этот аэро- порт принадлежит. Например, в городе Новосибирске аэропорт называется Толмаче- во, в городе Екатеринбурге — Кольцово, а в Санкт-Петербурге — Пулково. К тому же 19 Глава 1. Введение в базы данных и SQL некоторые города имеют более одного аэропорта. Сразу в качестве примера вспоми- нается Москва с ее аэропортами Домодедово, Шереметьево и Внуково. Добавим еще одну важную деталь: каждый аэропорт характеризуется географическими координа- тами — долготой и широтой, а также часовым поясом. Формируются маршруты перелетов между городами. Конечно, каждый такой марш- рут требует указания не только города, но и аэропорта, поскольку, как мы уже сказа- ли, в городе может быть и более одного аэропорта. В качестве упрощения реальности мы решим, что маршруты не будут иметь промежуточных посадок, т. е. у них будет только аэропорт отправления и аэропорт назначения. Каждый маршрут имеет ше- стизначный номер, включающий цифры и буквы латинского алфавита. На основе перечня маршрутов формируется расписание полетов (или рейсов). В рас- писании указывается плановое время отправления и плановое время прибытия, а также тип самолета, выполняющего этот рейс. При фактическом выполнении рейса возникает необходимость в учете дополнитель- ных сведений, а именно: фактического времени отправления и фактического време- ни прибытия, а также статуса рейса. Статус рейса может принимать ряд значений: – Scheduled (за месяц открывается возможность бронирования); – On Time (за сутки открывается регистрация); – Delayed (рейс задержан); – Departed (вылетел); – Arrived (прибыл); – Cancelled (отменен). Теперь обратимся к пассажирам. Полет начинается с бронирования авиабилета. В на- стоящее время общепринятой практикой является оформление электронных биле- тов. Каждый такой билет имеет уникальный номер, состоящий из 13 цифр. В рамках одной процедуры бронирования может быть оформлено несколько билетов, но каж- дая такая процедура имеет уникальный шестизначный номер (шифр) бронирования, состоящий из заглавных букв латинского алфавита и цифр. Кроме того, для каждой процедуры бронирования записывается дата бронирования и рассчитывается общая стоимость оформленных билетов. В каждый билет, кроме его тринадцатизначного номера, записывается идентифика- тор пассажира, а также его имя и фамилия (в латинской транскрипции) и контактные данные. В качестве идентификатора пассажира используется номер документа, удо- стоверяющего личность. Конечно, пассажир может сменить свой документ, а иной 20 1.4. Описание предметной области и учебной базы данных раз даже фамилию и имя, за время, прошедшее между бронированием билетов в раз- ные дни, поэтому невозможно наверняка сказать, что какие-то конкретные билеты были оформлены на одного и того же пассажира. В каждый электронный билет может быть вписано более одного перелета. Специа- листы называют эти записи о перелетах сегментами. В качестве примера наличия нескольких сегментов можно привести такой: Красноярск — Москва, Москва — Ана- па, Анапа — Москва, Москва — Красноярск. При этом возможно в рамках одного бронирования оформить несколько билетов на различных пассажиров. Для каждо- го перелета указывается номер рейса, аэропорты отправления и назначения, время вылета и время прибытия, а также стоимость перелета. Кроме того, указывается и так называемый класс обслуживания: экономический, бизнес и др. Когда пассажир прибывает в аэропорт отправления и проходит регистрацию билета, оформляется так называемый посадочный талон. Этот талон связан с авиабилетом: в талоне указывается такой же номер, который имеет электронный авиабилет дан- ного пассажира. Кроме того, в талоне указывается номер рейса и номер места в са- молете. Указывается также и номер посадочного талона — последовательный номер, присваиваемый в процессе регистрации билетов на данный рейс. Напомним, что каждому креслу в салоне самолета соответствует конкретный класс обслуживания. Данная информация учитывается при регистрации билетов и оформ- лении посадочных талонов. Если, например, пассажир приобрел билет с экономиче- ским классом обслуживания, то в его посадочном талоне будет указан номер места в салоне экономического класса, но не в салоне бизнес-класса. Следуя приведенному описанию предметной области, можно спроектировать мо- дельную базу данных, предназначенную для изучения языка SQL. Поскольку наше учебное пособие в первую очередь предназначено для изучения языка SQL, а не для овладения искусством проектирования баз данных, то мы приведем лишь диаграмму схемы данных, на которой показаны сущности, выделенные в предметной области, а также их связи и атрибуты. Конкретные же типы данных, первичные и внешние ключи, а также ограничения, наложенные на атрибуты и таблицы, мы покажем уже в последующих главах в процессе рассмотрения команд SQL, предназначенных для физического создания таблиц в базе данных. Приведенную ниже схему можно найти на сайте компании Postgres Professional по адресам, указанным в начале этого раздела. 21 Глава 1. Введение в базы данных и SQL Bookings Бронирования # book_ref ∗ book_date ∗ total_amount Airports Аэропорты # airport_code ∗ airport_name ∗ city ∗ longitude ∗ latitude ∗ timezone Tickets Билеты # ticket_no ∗ book_ref ∗ passenger_id ∗ passenger_name ◦ contact_data Ticket_flights Перелеты # ticket_no # flight_id ∗ fare_conditions ∗ amount Flights Рейсы # flight_id ∗ flight_no ∗ scheduled_departure ∗ scheduled_arrival ∗ departure_airport ∗ arrival_airport ∗ status ∗ aircraft_code ◦ actual_departure ◦ actual_arrival Aircrafts Самолеты # aircraft_code ∗ model ∗ range Boarding_passes Посадочные талоны # ticket_no # flight_id ∗ boarding_no ∗ seat_no Seats Места # aircraft_code # seat_no ∗ fare_conditions 22 Контрольные вопросы и задания Контрольные вопросы и задания 1. Какие группы операторов выделяются в составе языка SQL? 2. Дайте неформальное определение основных понятий реляционной модели данных: отношение, кортеж, атрибут. 3. Для чего нужны внешние ключи в реляционных таблицах? 4. Что такое потенциальный ключ? 5.* Предложите пример избыточного потенциального ключа для одной из таблиц базы данных «Авиаперевозки» и объясните, почему он будет избыточным. 6.* В реализации базы данных «Авиаперевозки» предполагается, что самолеты од- ной модели могут иметь только одну компоновку салона. Представим, что руко- водством принято решение о том, что нужно учитывать возможность наличия различных компоновок для каждой модели. Какие таблицы придется модифи- цировать в таком случае и каким образом? Потребуется ли создавать дополни- тельные таблицы? 23 |