Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
CREATE TABLE progress ( record_book numeric( 5 ), ... FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ON UPDATE CASCADE ); В случае каскадного обновления измененные значения ссылочных атрибутов копируются в ссылающиеся строки ссылающейся таблицы, т. е. новое значение атрибута record_book из строки таблицы «Студенты» будет скопировано во все строки таблицы «Успеваемость», ссылающиеся на обновленную строку. После рассмотрения всех видов ограничений целостности, которые можно указать для базы данных, мы можем привести окончательные определения таблиц «Студен- ты» и «Успеваемость». Окончательными они являются лишь в том смысле, что имен- но их нужно брать за основу при выполнении заданий, приведенных в конце главы. Эти определения ни в коем случае не являются идеальными, эталонными. Выполняя задания, вы это увидите сами. Прежде чем создавать таблицы, создайте базу данных edu: createdb -U postgres edu Подключитесь к ней: psql -d edu -U postgres Создайте обе таблицы: CREATE TABLE students ( record_book numeric( 5 ) NOT NULL, name text NOT NULL, doc_ser numeric( 4 ), doc_num numeric( 6 ), PRIMARY KEY ( record_book ) ); 104 5.2. Создание и удаление таблиц CREATE TABLE progress ( record_book numeric( 5 ) NOT NULL, subject text NOT NULL, acad_year text NOT NULL, term numeric( 1 ) NOT NULL CHECK ( term = 1 OR term = 2 ), mark numeric( 1 ) NOT NULL CHECK ( mark >= 3 AND mark <= 5 ) DEFAULT 5, FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ON DELETE CASCADE ON UPDATE CASCADE ); 5.2. Создание и удаление таблиц Настало время познакомить вас с оставшимися таблицами базы данных «Авиапере- возки». Рекомендуем вам, прежде чем приступать к дальнейшей работе, освежить в памяти описание этой предметной области, приведенное в главе 1. Поскольку в главе 3 вы уже создавали таблицы с помощью команды CREATE TABLE, то мы изберем такую стратегию: мы представим вам команды для создания остав- шихся таблиц, но выполнять эти команды уже не требуется, достаточно будет только просматривать описания таблиц с помощью команды \d утилиты psql. В главе 3 мы сначала представляли описание каждой создаваемой таблицы: назна- чения и имена столбцов, их типы данных и ограничения, которым каждый столбец должен удовлетворять. А уже после этого мы переходили непосредственно к SQL- команде создания таблицы в базе данных — CREATE TABLE. Но в этой главе мы, во избежание повторов, сразу будем показывать команды CREATE TABLE, дополняя их подробными комментариями. Если вы еще не запустили утилиту psql, то запустите ее и подключитесь к базе данных demo с учетной записью пользователя СУБД с именем postgres: psql -d demo -U postgres Выберите в качестве текущей схемы схему bookings: SET search_path TO bookings; 105 Глава 5. Основы языка определения данных Если вы уже были подключены к другой базе данных, то можете, не выходя из ути- литы psql, просто подключиться к нужной вам базе данных с помощью команды \ connect. Напомним, что команды, имена которых начинаются с символа «\», яв- ляются не SQL-командами, а командами утилиты psql. Конечно, за этими короткими командами, например \d, могут скрываться сложные SQL-запросы к системным таб- лицам базы данных. Просто утилита psql избавляет пользователя от необходимости вводить эти сложные запросы. Для подключения к базе данных demo изнутри psql сделайте так: \connect demo Существует и сокращенный вариант этой команды: \c demo При создании таблиц необходимо учитывать связи между ними. Поэтому сначала должны создаваться ссылочные таблицы, а потом — ссылающиеся. Конечно, возмож- на ситуация, когда образуется иерархия таблиц. Таблица, находящаяся в середине такой иерархии, выполняет обе роли: ссылающейся и ссылочной таблицы. Тогда нуж- но продвигаться «вниз» от вершины иерархии, где находится таблица, не имеющая внешних ключей. Если в базе данных нет циклических ссылок таблиц друг на друга, то всегда существу- ет таблица (или таблицы), которая не ссылается ни на какие другие таблицы. С нее и нужно начинать создание базы данных. Перед созданием очередной таблицы, име- ющей внешние ключи, уже должны существовать все ссылочные таблицы для нее. При наличии циклических ссылок таблиц друг на друга придется воспользоваться командой ALTER TABLE, о которой речь пойдет в следующем разделе этой главы. Поскольку две первые таблицы — «Самолеты» (aircrafts) и «Места» (seats) — мы уже изучили в главе 3, то перейдем к таблице «Аэропорты» (airports). Она не име- ет внешних ключей. В этой таблице в качестве кода аэропорта служат трехбуквенные обозначения, утвер- жденные специальной организацией. При этом используются только буквы латин- ского алфавита. Каждый аэропорт имеет также и полное название. Оно не всегда совпадает с названием города, в котором аэропорт находится. Для города не преду- смотрено отдельной сущности, поэтому таблицы городов нет. Однако название го- рода присутствует в виде атрибута city. Назначение остальных атрибутов ясно из комментариев, приведенных в SQL-команде. Комментарии в языке SQL обозначаются двумя символами «дефис». При создании таблиц в среде утилиты psql вводить комментарии не нужно, но если вы создаете 106 5.2. Создание и удаление таблиц текстовый файл, содержащий команды для создания объектов базы данных, то ком- ментарии нужно ввести. Это сделает такой файл более понятным для вас в будущем. CREATE TABLE airports ( airport_code char( 3 ) NOT NULL, -- Код аэропорта airport_name text NOT NULL, -- Название аэропорта city text NOT NULL, -- Город longitude float NOT NULL, -- Координаты аэропорта: долгота latitude float NOT NULL, -- Координаты аэропорта: широта timezone text NOT NULL, -- Часовой пояс аэропорта PRIMARY KEY ( airport_code ) ); Посмотрите описание этой таблицы: \d airports В команде \d можно было ввести лишь первые символы имени таблицы и нажать клавишу чтобы они однозначно определяли имя таблицы. В нашем случае есть еще таблица aircrafts, поэтому можно было сделать так: \d airp а затем нажать клавишу psql дополнит до «air», поскольку есть варианты aircrafts и airports. Дальше вы мо- жете добавить букву «p» и нажать В результате вы получите примерно такой вывод на экран: Таблица "bookings.airports" Столбец | Тип | Модификаторы --------------+------------------+-------------- airport_code | character(3) | NOT NULL airport_name | text | NOT NULL city | text | NOT NULL longitude | double precision | NOT NULL latitude | double precision | NOT NULL timezone | text | NOT NULL Индексы: "airports_pkey" PRIMARY KEY, btree (airport_code) 107 Глава 5. Основы языка определения данных Ссылки извне: TABLE "flights" CONSTRAINT "flights_arrival_airport_fkey" FOREIGN KEY (arrival_airport) REFERENCES airports(airport_code) TABLE "flights" CONSTRAINT "flights_departure_airport_fkey" FOREIGN KEY (departure_airport) REFERENCES airports(airport_code) В этом выводе в выражении bookings.airports слово bookings означает имя схе- мы . Как мы уже говорили ранее, это, упрощенно говоря, раздел базы данных, в ко- тором и создаются таблицы и другие объекты. По умолчанию используется схема public, но в базе данных demo создана схема bookings. Поскольку мы задавали первичный ключ, то для его реализации был автоматически создан индекс. Имя индекса в нашем случае — airports_pkey. Оно было сгенери- ровано ядром PostgreSQL. Указан также и тип индекса — btree, т. е. B-дерево. Далее в круглых скобках приводится список ключевых атрибутов. В нашем случае он состо- ит из одного атрибута — airport_code. Обратите внимание, что в команде создания таблицы «Аэропорты» мы указывали для атрибутов longitude и latitude тип данных float, определенный в стандарте SQL. Однако, согласно документации, если при объявлении типа float параметр, за- дающий точность, не указан, то это будет равносильно использованию типа double precision. PostgreSQL предлагает свое расширение — команду COMMENT, которая позволяет со- здавать комментарии (описания) к различным объектам базы данных. Эти коммен- тарии будут также сохраняться в базе данных. Например, для создания описания столбца city таблицы airports нужно сделать так: COMMENT ON COLUMN airports.city IS 'Город'; Чтобы увидеть описания столбцов таблицы, нужно в команде \d добавить символ «+», например: \d+ airports Следующая таблица — «Рейсы» (flights). Назначение ее атрибутов должно быть в це- лом понятно из комментариев, присутствующих в SQL-команде. Сначала приведем саму команду, а затем сделаем ряд пояснений. 108 5.2. Создание и удаление таблиц CREATE TABLE flights ( flight_id serial NOT NULL, -- Идентификатор рейса flight_no char( 6 ) NOT NULL, -- Номер рейса scheduled_departure timestamptz NOT NULL, -- Время вылета по расписанию scheduled_arrival timestamptz NOT NULL, -- Время прилета по расписанию departure_airport char( 3 ) NOT NULL, -- Аэропорт отправления arrival_airport char( 3 ) NOT NULL, -- Аэропорт прибытия status varchar( 20 ) NOT NULL, -- Статус рейса aircraft_code char( 3 ) NOT NULL, -- Код самолета, IATA actual_departure timestamptz, -- Фактическое время вылета actual_arrival timestamptz, -- Фактическое время прилета CHECK ( scheduled_arrival > scheduled_departure ), CHECK ( status IN ( 'On Time', 'Delayed', 'Departed', 'Arrived', 'Scheduled', 'Cancelled' ) ), CHECK ( actual_arrival IS NULL OR ( actual_departure IS NOT NULL AND actual_arrival IS NOT NULL AND actual_arrival > actual_departure ) ), PRIMARY KEY ( flight_id ), UNIQUE ( flight_no, scheduled_departure ), FOREIGN KEY ( aircraft_code ) REFERENCES aircrafts ( aircraft_code ), FOREIGN KEY ( arrival_airport ) REFERENCES airports ( airport_code ), FOREIGN KEY ( departure_airport ) REFERENCES airports ( airport_code ) ); В таблице предусмотрено три внешних ключа, которые ссылаются на таблицы «Са- молеты» и «Аэропорты». В качестве первичного ключа используется так называемый суррогатный ключ , состоящий из одного атрибута — flight_id. Обратите внима- ние, что тип данных этого атрибута — serial, т. е. значения целого типа для этого атрибута будут извлекаться из последовательности. Суррогатный ключ — это уни- кальный ключ, назначение которого — только идентифицировать строки в таблице. Зачастую для него используются целочисленные значения. Такому ключу не соот- ветствует никакое свойство никакой сущности реального мира. Это — абстракция, позволяющая в ряде случаев упростить определения таблиц, например, за счет со- кращения числа атрибутов во внешних ключах до одного. В нашей таблице «Рейсы» суррогатный ключ как раз и служит для того, чтобы в таблицах, ссылающихся на нее, 109 Глава 5. Основы языка определения данных внешние ключи состояли только из атрибута flight_id. Существует и естественный уникальный ключ, состоящий из двух атрибутов: номер рейса (flight_no) и время вылета по расписанию (scheduled_departure). Для него нам придется создать уникальный ключ, чтобы избежать дублирования значе- ний: очевидно, что в один и тот же момент времени не могут выполняться два (и бо- лее) рейса, имеющие один и тот же номер. Обратите внимание, что для атрибутов, имеющих смысл даты/времени, выбран тип данных timestamptz, т. е. временная отметка с указанием часового пояса. Это важ- но, т. к. перелеты могут совершаться между городами, находящимися в разных часо- вых поясах, а время вылета и время прилета указываются местные. Поясним смысл каждого из трех ограничений CHECK. Первое ограничение говорит о том, что время прилета по расписанию должно быть больше времени вылета по расписанию. Это представляется очевидным, т. к. длительность полета всегда больше нуля. Второе ограничение CHECK задает множество допустимых значений атрибута status следующим списком: – Scheduled — рейс доступен для бронирования (это происходит за месяц до плано- вой даты вылета, а до этого запись о рейсе не существует в базе данных); – On Time — рейс доступен для регистрации (за сутки до плановой даты вылета) и не задержан; – Delayed — рейс доступен для регистрации (за сутки до плановой даты вылета), но задержан; – Departed — самолет уже вылетел и находится в воздухе; – Arrived — самолет прибыл в пункт назначения; – Cancelled — рейс отменен. Третье ограничение более сложное. Его можно условно разделить на две части, соеди- ненные логической операцией «ИЛИ». Первая часть говорит о том, что если самолет еще не прилетел (т. е. значение actual_arrival не определено), то фактическое вре- мя вылета нас, образно говоря, не интересует. Самолет мог еще не вылететь или уже вылететь. Но даже если он уже и вылетел, и значение атрибута actual_departure отлично от NULL, то все равно сравнить его со значением атрибута actual_arrival, которое пока еще не определено, невозможно. Речь идет о сравнении вида > или <. 110 5.2. Создание и удаление таблиц Вторая часть этого ограничения должна гарантировать, что если самолет уже при- летел , то, во-первых, фактическое время вылета должно быть отлично от NULL, а во-вторых, фактическое время прилета должно быть больше фактического времени вылета. Просмотреть описание таблицы в базе данных можно так: \d flights Поскольку до сих пор мы давали подробные пояснения по каждой таблице, то сейчас ограничимся только указанием на те сведения, которые могут быть непонятными. В частности, обратите внимание, что для атрибута flight_id указан тип данных integer, а не serial, как предписано в команде для создания этой таблицы. В гла- ве 4 при рассмотрении типа данных serial мы говорили, ссылаясь на документа- цию, что этот тип является, по сути, удобной синтаксической заменой, избавляющей администратора базы данных от необходимости выполнения SQL-команд для явного создания последовательности и привязки ее к конкретному столбцу таблицы. О том, что значения для этого столбца будут формироваться с помощью последовательно- сти, говорит фраза DEFAULT nextval('flights_flight_id_seq'::regclass) В этой фразе указано и имя последовательности — flights_flight_id_seq. Если выполнить команду \d, то можно увидеть эту последовательность в списке объектов базы данных. Список отношений Схема | Имя | Тип | Владелец ----------+-----------------------+--------------------+---------- bookings | flights_flight_id_seq | последовательность | postgres (11 строк) Чтобы посмотреть описание последовательности flights_flight_id_seq, нужно использовать команду \d: \d flights_flight_id_seq 111 Глава 5. Основы языка определения данных В базе данных есть еще одна таблица, не имеющая внешних ключей, — «Бронирова- ния» ( bookings). Это довольно простая таблица. В ней всего три атрибута. Атрибут «Номер бронирования» (book_ref) является первичным ключом. Посколь- ку он представляет собой шестизначную комбинацию латинских букв и цифр, то в качестве типа данных для него выбран тип character (сокращенно — char). Для атрибута «Дата бронирования» (book_date) выбран тип данных timestamptz — временная отметка с часовым поясом, т. к. билеты могут приобретаться в городах, находящихся в различных часовых поясах. В главе 4 мы уже говорили о том, что в случаях, требующих точных вычислений, необ- ходимо использовать числа с фиксированной точностью. Работа с денежными сум- мами как раз и является одним из таких случаев. Поэтому для атрибута «Полная сто- имость бронирования» (total_amount) выбирается тип данных numeric, при этом масштаб, т. е. число цифр справа от десятичной точки (запятой), будет равен 2. CREATE TABLE bookings ( book_ref char( 6 ) NOT NULL, -- Номер бронирования book_date timestamptz NOT NULL, -- Дата бронирования total_amount numeric( 10, 2 ) NOT NULL, -- Полная стоимость бронирования PRIMARY KEY ( book_ref ) ); С таблицей «Бронирования» (bookings) по внешнему ключу связана таблица «Биле- ты» ( tickets). В качестве первичного ключа в ней служит атрибут «Номер билета» (ticket_no). Хотя уникальные тринадцатизначные номера билетов — числовые, но в них могут присутствовать лидирующие нули, поэтому числовой тип данных здесь не годится, а приходится использовать тип character (сокращенно — char). В качестве идентификатора пассажира будет использоваться номер документа, удо- стоверяющего личность, а номера таких документов могут содержать, например, лидирующие нули, поэтому атрибут «Идентификатор пассажира» (passenger_id) будет не числовым, а символьным — varchar. Атрибут «Имя пассажира» (passenger_name) содержит имя и фамилию пассажира, записанные заглавными латинскими буквами, а вот отчество не используется. Тип данных, конечно, text. Очень интересный атрибут «Контактные данные пассажира» (contact_data). Его особенность в том, что эти данные могут иметь некоторую структуру, но при этом со- здавать дополнительные атрибуты в таблице нецелесообразно. С такими данными — 112 5.2. Создание и удаление таблиц их называют слабоструктурированными — PostgreSQL хорошо умеет работать: для них предусмотрены типы json и jsonb. В нашей таблице используется тип jsonb и вот почему: хотя ввод данных такого типа несколько замедляется из-за необходи- мости выполнения разбора данных, но этот разбор выполняется однократно, только при вводе, а последующая обработка уже разобранных данных ускоряется. Подробно типы json и jsonb рассмотрены в главе 4. Внешним ключом в таблице будет атрибут «Номер бронирования» (book_ref), по- скольку в рамках каждой процедуры бронирования может быть оформлено более од- ного билета. |