Главная страница
Навигация по странице:

  • 1.1 Что такое базы данных и зачем они нужны

  • 1.2 Основные понятия реляционной модели

  • «таблица»

  • № зачетной книжки Ф. И. О. Серия документа Номер документа

  • Зачетная книжка Предмет Учебный год Семестр Оценка

  • 1.4 Описание предметной области и учебной базы данных

  • Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П


    Скачать 0.9 Mb.
    НазваниеУчебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
    Дата14.11.2022
    Размер0.9 Mb.
    Формат файлаpdf
    Имя файла4_1_1_SQL_Postgres_Bazovy_kurs_2017.pdf
    ТипУчебно-практическое пособие
    #787182
    страница2 из 28
    1   2   3   4   5   6   7   8   9   ...   28
    1 Введение в базы данных и SQL
    Эта глава — вводная. В ней мы расскажем об основах баз данных, о том, что такое ре- ляционная модель и зачем нужен язык SQL. Очень важной темой этой главы станет описание предметной области, на основе которой будет спроектирована учебная база данных, которая и будет служить в качестве площадки для изучения языка SQL. Это пособие предназначено в первую очередь для практического освоения языка SQL, а не для изучения теории баз данных, поэтому для изучения теории необходимо об- ращаться к авторитетным источникам, список которых приведен в конце учебного пособия.
    1.1 Что такое базы данных и зачем они нужны
    Технологии баз данных существовали не всегда. Однако и до их внедрения в практи- ку люди также собирали и обрабатывали данные. Одним из способов хранения дан- ных были так называемые плоские файлы (flat files), которые имели очень простую структуру: данные хранились в виде записей, разделенных на поля фиксированной длины. В реальной жизни между элементами данных зачастую возникают сложные связи, которые необходимо перенести и в электронную базу данных. При использо- вании плоских файлов эти связи организовать сложно, а еще сложнее поддерживать их при изменениях и удалениях отдельных элементов данных.
    Одним из основных понятий в теории баз данных является модель данных. Мож- но сказать, что она характеризует способ организации данных и основные методы доступа к ним. Сначала были предложены иерархическая и сетевая модели данных.
    Однако в ходе эволюции теорий и идей была разработана реляционная модель дан- ных, которая сейчас и является доминирующей. Поэтому в настоящее время преоб- ладают базы данных реляционного типа. Их характерной чертой является тот факт,
    что данные воспринимаются пользователем как таблицы. В распоряжении пользова- теля имеются операторы для выборки данных из таблиц, а также для вставки новых данных, обновления и удаления имеющихся данных.
    Одним из достоинств реляционной базы данных является ее способность поддержи- вать связи между элементами данных, избавляя программиста от необходимости за- ниматься этой рутинной и очень трудоемкой работой. В те времена, когда технологии реляционных баз данных еще не получили широкого распространения, программи- стам приходилось на процедурных языках вручную реализовывать такие операции,
    которые сейчас называются каскадным обновлением внешних ключей или каскад- ным удалением записей из подчиненных таблиц (файлов). Здесь слово «вручную»
    означает, что для выполнения этих операций приходилось писать код, состоящий из элементарных команд, позволяющий добраться до каждой обновляемой или удаляе- мой записи. Тот подход к работе с базами данных назывался навигационным — про- граммист указывал программе конкретный алгоритм поиска записей. Приведем в ка- честве примера простую ситуацию: в базе данных, построенной на основе файлов,
    хранится информация о студентах и их экзаменационных оценках, причем, личные данные студентов хранятся в одном файле, назовем его условно «Студенты», а эк- заменационные оценки — в другом файле, который условно назовем «Оценки». Ес- ли требуется удалить информацию о конкретном студенте и его экзаменационных
    9
    оценках, то придется не только выполнить операцию удаления конкретной записи из файла «Студенты», но дополнительно организовать цикл для поиска и удаления тех записей из файла «Оценки», у которых ключевое поле имеет то же значение, что и поле в удаляемой записи из файла «Студенты».
    Работая с реляционными базами данных, программист избавлен от программирова- ния на «атомарном» уровне, потому что современные языки для «общения» с этими базами данных являются декларативными. Это означает, что для получения резуль- тата достаточно лишь указать, что нужно получить, но не требуется предписывать способ получения результата, т. е. как его получить.
    Система баз данных — это компьютеризированная система, предназначенная для хранения, переработки и выдачи информации по запросу пользователей. Такая си- стема включает в себя программное и аппаратное обеспечение, сами данные, а также пользователей.
    Современные системы баз данных являются, как правило, многопользовательски- ми. В таких системах одновременный доступ к базе данных могут получить сразу несколько пользователей.
    Основным программным обеспечением является система управления базами дан- ных. По-английски она называется database management system (DBMS). Кроме СУБД
    в систему баз данных могут входить утилиты, средства для разработки приложений
    (программ), средства проектирования базы данных, генераторы отчетов и др.
    Пользователи систем с базами данных подразделяются на ряд категорий. Первая ка- тегория — это прикладные программисты. Вторая категория — это конечные поль- зователи, ради которых и выполняется вся работа. Они могут получить доступ к базе данных, используя прикладные программы или универсальные приложения, кото- рые входят в программное обеспечение самой СУБД. В большинстве СУБД есть так называемый процессор языка запросов, который позволяет пользователю вводить команды языка высокого уровня (например, языка SQL). Третья категория пользо- вателей — это администраторы базы данных. В их обязанности входят: создание ба- зы данных, выбор оптимальных режимов доступа к ней, разграничение полномочий различных пользователей на доступ к той или иной информации в базе данных, вы- полнение резервного копирования базы данных и т. д.
    Систему баз данных можно разделить на два главных компонента: сервер и набор клиентов (или внешних интерфейсов). Сервер — это и есть СУБД. Клиентами являют- ся различные приложения, написанные прикладными программистами, или встро- енные приложения, поставляемые вместе с СУБД. Один сервер может обслуживать много клиентов.
    Современные СУБД включают в себя словарь данных. Это часть базы данных, которая описывает сами данные, хранящиеся в ней. Словарь данных помогает СУБД выпол- нять свои функции.
    1.2 Основные понятия реляционной модели
    В каждой технологической сфере есть своя терминология. Существуют базовые тер- мины, на которых основываются все дальнейшие рассуждения. Такие термины при-
    10
    сутствуют и в сфере баз данных. Сейчас мы кратко о них поговорим.
    В эпоху, предшествующую рождению реляционной теории, базы данных традици- онно рассматривались как набор файлов, состоящих из записей, а записи, в свою очередь, подразделялись на отдельные поля. Поле являлось элементарной единицей данных.
    В реляционных базах данных пользователь воспринимает данные в виде таблиц. По- этому термину «файл» соответствует термин «таблица», вместо термина «запись»
    используется термин «строка», а вместо термина «поле» — термин «столбец» (или
    «колонка»
    ). Таким образом, таблицы состоят из строк и столбцов, на пересечении которых должны находиться «атомарные» значения, которые нельзя разбить на бо- лее мелкие элементы без потери смысла.
    В формальной теории реляционных баз данных эти таблицы называют отношения-
    ми (relations)
    – поэтому и базы данных называются реляционными. Отношение —
    это математический термин. При определении свойств таких отношений использу- ется теория множеств. В терминах данной теории строки таблицы будут называться
    кортежами (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
    При работе с базами данных часто приходится следовать различным ограничени-
    ям
    , которые могут быть обусловлены спецификой конкретной предметной области.
    Упрощая реальную ситуацию, примем следующие ограничения:
    11

    – номер зачетной книжки состоит из пяти цифр и не может быть отрицательным
    (в разных вузах используются различные схемы присваивания номеров зачетным книжкам, эти схемы могут быть гораздо сложнее принятой нами и могут учиты- вать, например, год поступления студента в вуз);
    – серия документа, удостоверяющего личность, является четырехзначным числом,
    а номер документа, удостоверяющего личность — шестизначным числом;
    – номер семестра может принимать только два значения — 1 (осенний семестр) и 2
    (весенний семестр);
    – оценка может принимать только три значения — 3 (удовлетворительно), 4 (хорошо)
    и 5 (отлично): другие оценки выставлять в зачетные книжки не принято.
    Для идентификации строк в таблицах и для связи таблиц между собой используются так называемые ключи. Потенциальный ключ — это комбинация атрибутов табли- цы, позволяющая уникальным образом идентифицировать строки в ней. Ключ может состоять и только лишь из одного атрибута таблицы. Например, в таблице «Студен- ты» таким идентификатором может быть атрибут «Номер зачетной книжки». В ка- честве потенциального ключа данной таблицы могут также служить два ее атрибута,
    взятые вместе: «Серия документа, удостоверяющего личность» и «Номер документа,
    удостоверяющего личность». Ни один из них в отдельности не может использоваться в качестве уникального идентификатора. В таком случае ключ будет составным. При этом важным является то, что потенциальный ключ должен быть не избыточным, т. е.
    никакое подмножество атрибутов, входящих в него, не должно обладать свойством уникальности. Потенциальный ключ, включающий два упомянутых атрибута, явля- ется не избыточным.
    Ключи нужны для адресации на уровне строк (записей). При наличии в таблице более одного потенциального ключа один из них выбирается в качестве так называемого
    первичного ключа
    , а остальные будут являться альтернативными ключами.
    Рассмотрим таблицы «Студенты» и «Успеваемость». Предположим, что в таблице
    «Студенты» нет строки с номером зачетной книжки 55900, тогда включать строку с таким номером зачетной книжки в таблицу «Успеваемость» не имеет смысла. Та- ким образом, значения столбца «Номер зачетной книжки» в таблице «Успеваемость»
    должны быть согласованы со значениями такого же столбца в таблице «Студенты».
    Атрибут «Номер зачетной книжки» в таблице «Успеваемость» является примером то- го, что называется внешним ключом. Таблица, содержащая внешний ключ, назы- вается ссылающейся таблицей (referencing table). Таблица, содержащая соответству- ющий потенциальный ключ, называется ссылочной (целевой) таблицей (referenced table). В таких случаях говорят, что внешний ключ ссылается на потенциальный ключ в ссылочной таблице. Внешний ключ может быть составным, т. е. может включать бо- лее одного атрибута. Внешний ключ не обязан быть уникальным. Проблема обеспе- чения того, чтобы база данных не содержала неверных значений внешних ключей,
    известна как проблема ссылочной целостности. Ограничение, согласно которому значения внешних ключей должны соответствовать значениям потенциальных клю- чей, называется ограничением ссылочной целостности (ссылочным ограниче-
    нием)
    Обеспечением выполнения ограничений ссылочной целостности занимается СУБД,
    а от разработчика требуется лишь указать атрибуты, служащие в качестве внешних
    12
    ключей. При проектировании баз данных часто предусматривается, что при удале- нии строки из ссылочной таблицы соответствующие строки из ссылающейся таблицы должны быть также удалены, а при изменении значения столбца, на который ссыла- ется внешний ключ, должны быть изменены значения внешнего ключа в ссылающей- ся таблице. Этот подход называется каскадным удалением (обновлением).
    Иногда применяются и другие подходы. Например, вместо удаления строк из ссы- лающейся таблицы в этих строках просто заменяют значения атрибутов, входящих во внешний ключ, так называемыми NULL-значениями. Это специальные значе- ния, означающие «ничто» или отсутствие значения, они не совпадают со значением
    «нуль» или «пустая строка». NULL-значение применяется в базах данных и в качестве значения по умолчанию, когда пользователь не ввел никакого конкретного значения.
    Первичные ключи не могут содержать NULL-значений.
    Транзакция — одно из важнейших понятий теории баз данных. Она означает набор операций над базой данных, рассматриваемых как единая и неделимая единица ра- боты, выполняемая полностью или не выполняемая вовсе, если произошел какой- то сбой в процессе выполнения транзакции. Таким образом, транзакции являются средством обеспечения согласованности данных. В нашей базе данных транзакци- ей могут быть, например, две операции: удаление строки из таблицы «Студенты» и удаление связанных по внешнему ключу строк из таблицы «Успеваемость».
    1.3 Что такое язык SQL
    Язык SQL — это непроцедурный язык, который является стандартным средством ра- боты с данными во всех реляционных СУБД. Операторы (команды), написанные на этом языке, лишь указывают СУБД, какой результат должен быть получен, но не опи- сывают процедуру получения этого результата. СУБД сама определяет способ вы- полнения команды пользователя. В языке SQL традиционно выделяются группа опе- раторов определения данных (Data Definition Language — DDL), группа операторов манипулирования данными (Data Manipulation Language — DML) и группа опера- торов, управляющих привилегиями доступа к объектам базы данных (Data Control
    Language — DCL).
    К операторам языка определения данных (DDL) относятся команды для создания, из- менения и удаления таблиц, представлений и других объектов базы данных. Деталь- ному рассмотрению этих команд посвящены главы 5 и 8.
    К операторам языка манипулирования данными (DML) относятся команды для вы- борки строк из таблиц, вставки строк в таблицы, обновления и удаления строк. Эти команды подробно рассматриваются в главах 6 и 7.
    Операторы DCL в пособии не рассматриваются, т. к. PostgreSQL позволяет на началь- ном этапе изучения языка SQL обойтись без их использования.
    13

    1.4 Описание предметной области и учебной базы данных
    Чтобы показать все основные возможности языка SQL, нам потребуется база данных.
    Эта база данных не должна быть слишком сложной, чтобы ее изучение не потребова- ло слишком много времени. Но, вместе с тем, она должна быть достаточно разнооб- разной, чтобы запросы к ней выглядели бы правдоподобными, почти такими же, как и в реальной работе.
    В качестве предметной области выберем пассажирские авиаперевозки. Ее ориги- нальное описание и описание базы данных «Авиаперевозки» можно найти по ад- ресам https://postgrespro.ru/education/demodb и https://postgrespro.
    ru/docs/postgrespro/current/demodb-bookings.html. Надеемся, что эта об- ласть знакома многим читателям нашего учебного пособия. Конечно, в учебных це- лях реальная ситуация намеренно упрощена, но все принципиальные вещи сохране- ны.
    Итак, некая российская авиакомпания выполняет пассажирские авиаперевозки. Она обладает своим парком самолетов различных моделей. Каждая модель самолета име- ет определенный код, который присваивает Международная ассоциация авиапере- возчиков (IATA). При этом будем считать, что самолеты одной модели имеют оди- наковые компоновки салонов, т. е. порядок размещения кресел и нумерацию мест в салонах бизнес-класса и экономического класса. Например, если это модель Sukhoi
    SuperJet-100, то место 2A относится к бизнес-классу, а место 20D — к экономическому классу. Бизнес-класс и экономический класс — это разновидности так называемого класса обслуживания.
    Наша авиакомпания выполняет полеты между аэропортами России. Каждому аэро- порту присвоен уникальный трехбуквенный код, при этом используются только за- главные буквы латинского алфавита. Эти коды присваивает не сама авиакомпания,
    а специальные организации, управляющие пассажирскими авиаперевозками. Зача- стую название аэропорта не совпадает с названием того города, которому этот аэро- порт принадлежит. Например, в городе Новосибирске аэропорт называется Толмаче- во, в городе Екатеринбурге — Кольцово, а в Санкт-Петербурге — Пулково. К тому же некоторые города имеют более одного аэропорта. Сразу в качестве примера вспоми- нается Москва с ее аэропортами Домодедово, Шереметьево и Внуково. Добавим еще одну важную деталь: каждый аэропорт характеризуется географическими координа- тами — долготой и широтой, а также часовым поясом.
    Формируются маршруты перелетов между городами. Конечно, каждый такой марш- рут требует указания не только города, но и аэропорта, поскольку, как мы уже сказа- ли, в городе может быть и более одного аэропорта. В качестве упрощения реальности мы решим, что маршруты не будут иметь промежуточных посадок, т. е. у них будет только аэропорт отправления и аэропорт назначения. Каждый маршрут имеет ше- стизначный номер, включающий цифры и буквы латинского алфавита.
    На основе перечня маршрутов формируется расписание полетов (или рейсов). В рас- писании указывается плановое время отправления и плановое время прибытия, а также тип самолета, выполняющего этот рейс.
    При фактическом выполнении рейса возникает необходимость в учете дополнитель- ных сведений, а именно: фактического времени отправления и фактического време- ни прибытия, а также статуса рейса. Статус рейса может принимать ряд значений:
    14

    – Scheduled (за месяц открывается возможность бронирования);
    – On Time (за сутки открывается регистрация);
    – Delayed (рейс задержан);
    – Departed (вылетел);
    – Arrived (прибыл);
    – Cancelled (отменен).
    Теперь обратимся к пассажирам. Полет начинается с бронирования авиабилета. В на- стоящее время общепринятой практикой является оформление электронных биле- тов. Каждый такой билет имеет уникальный номер, состоящий из 13 цифр. В рамках одной процедуры бронирования может быть оформлено несколько билетов, но каж- дая такая процедура имеет уникальный шестизначный номер (шифр) бронирования,
    состоящий из заглавных букв латинского алфавита и цифр. Кроме того, для каждой процедуры бронирования записывается дата бронирования и рассчитывается общая стоимость оформленных билетов.
    В каждый билет, кроме его тринадцатизначного номера, записывается идентифика- тор пассажира, а также его имя и фамилия (в латинской транскрипции) и контактные данные. В качестве идентификатора пассажира используется номер документа, удо- стоверяющего личность. Конечно, пассажир может сменить свой документ, а иной раз даже фамилию и имя, за время, прошедшее между бронированием билетов в раз- ные дни, поэтому невозможно наверняка сказать, что какие-то конкретные билеты были оформлены на одного и того же пассажира.
    В каждый электронный билет может быть вписано более одного перелета. Специа- листы называют эти записи о перелетах сегментами. В качестве примера наличия нескольких сегментов можно привести такой: Красноярск — Москва, Москва — Ана- па, Анапа — Москва, Москва — Красноярск. При этом возможно в рамках одного бро- нирования оформить несколько билетов на различных пассажиров. Для каждого пе- релета указывается номер рейса, аэропорты отправления и назначения, время вы- лета и время прибытия, а также стоимость перелета. Кроме того, указывается и так называемый класс обслуживания: экономический, бизнес и др.
    Когда пассажир прибывает в аэропорт отправления и проходит регистрацию билета,
    оформляется так называемый посадочный талон. Этот талон связан с авиабилетом: в талоне указывается такой же номер, который имеет электронный авиабилет данного пассажира. Кроме того, в талоне указывается номер рейса и номер места в самолете.
    Указывается также и номер посадочного талона — последовательный номер, присва- иваемый в процессе регистрации билетов на данный рейс.
    Напомним, что каждому креслу в салоне самолета соответствует конкретный класс обслуживания. Данная информация учитывается при регистрации билетов и оформ- лении посадочных талонов. Если, например, пассажир приобрел билет с экономиче- ским классом обслуживания, то в его посадочном талоне будет указан номер места в салоне экономического класса, но не в салоне бизнес-класса.
    Следуя приведенному описанию предметной области, можно спроектировать мо- дельную базу данных, предназначенную для изучения языка SQL. Поскольку наше учебное пособие в первую очередь предназначено для изучения языка SQL, а не для овладения искусством проектирования баз данных, то мы приведем лишь диаграмму
    15

    1   2   3   4   5   6   7   8   9   ...   28


    написать администратору сайта