Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
CREATE TABLE progress ( record_book numeric( 5 ), ... FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ON DELETE CASCADE ); 2. Запрет удаления строки из таблицы «Студенты» (students), если в таблице «Успе- ваемость» (progress) есть хотя бы одна строка, ссылающаяся на удаляемую стро- ку в таблице «Студенты». Для реализации такой политики в определение внеш- него ключа добавляются ключевые слова ON DELETE RESTRICT или ON DELETE NO ACTION. Если в определении внешнего ключа не предписано конкретное действие, то по умолчанию используется NO ACTION. Оба эти варианта озна- чают, что если в ссылающейся таблице, т. е. «Успеваемость», есть строки, ссы- лающиеся на удаляемую строку в таблице «Студенты», то операция удаления будет отменена, и будет выведено сообщение об ошибке. Отличие между эти- ми двумя вариантами лишь в том, что при использовании NO ACTION можно отложить проверку выполнения ограничения на более поздний строк в рамках транзакции, а в случае RESTRICT проверка выполняется немедленно. Поэтому если бы внешний ключ определили таким образом: CREATE TABLE progress ( record_book numeric( 5 ), ... 76 FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ON DELETE RESTRICT ); или таким: CREATE TABLE progress ( record_book numeric( 5 ), ... FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ); то при попытке удаления строки из таблицы «Студенты» и наличии в таблице «Успеваемость» строк, связанных с ней, операция удаления была бы отменена с выводом сообщения об ошибке. 3. Присваивание атрибутам внешнего ключа в строках таблицы «Успеваемость» значения NULL. Для реализации этого подхода необходимо, чтобы на атрибуты внешнего ключа не было наложено ограничение NOT NULL. Оформляется этот вариант так: CREATE TABLE progress ( record_book numeric( 5 ), ... FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ON DELETE SET NULL ); 4. Присваивание атрибутам внешнего ключа в строках таблицы «Успеваемость» (progress) значения DEFAULT, если оно, конечно, было предписано при созда- нии таблицы. Оформляется этот вариант так (значение во фразе DEFAULT взято произвольное): CREATE TABLE progress ( record_book numeric( 5 ) DEFAULT 12345, ... FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ON DELETE SET DEFAULT ); Важно учитывать, что если в ссылочной таблице нет строки с тем же значением ключевого атрибута, которое было предписано во фразе DEFAULT при создании ссылающейся таблицы, то будет иметь место нарушение ограничения ссылоч- ной целостности и операция удаления не будет выполнена. При выполнении операции UPDATE используются эти же варианты подходов по отношению к обеспечению ссылочной целостности. Аналогом каскадного уда- ления является каскадное обновление: 77 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 ) ); 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 ); 78 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; Если вы уже были подключены к другой базе данных, то можете, не выходя из ути- литы psql, просто подключиться к нужной вам базе данных с помощью команды \connect. Напомним, что команды, имена которых начинаются с символа «\», не явля- ются SQL-командами, а являются командами утилиты psql. Конечно, за этими корот- кими командами, например, \d, могут скрываться сложные SQL-запросы к систем- ным таблицам базы данных. Просто утилита psql избавляет пользователя от необхо- димости вводить эти сложные запросы. Для подключения к базе данных demo изнут- ри psql сделайте так: \connect demo Существует и сокращенный вариант этой команды: \c demo При создании таблиц необходимо учитывать связи между ними. Поэтому сначала должны создаваться ссылочные таблицы, а потом — ссылающиеся. Конечно, возмож- на ситуация, когда образуется иерархия таблиц. Таблица, находящаяся в середине та- кой иерархии, выполняет обе роли: ссылающейся и ссылочной таблицы. Тогда нуж- но продвигаться «вниз» от вершины иерархии, где находится таблица, не имеющая внешних ключей. Если в базе данных нет циклических ссылок таблиц друг на друга, то всегда суще- ствует таблица (или таблицы), которые не ссылаются ни на какие другие таблицы. С них и нужно начинать создание базы данных. Перед созданием очередной таблицы, имеющей внешний ключ (ключи), уже должны существовать все ссылочные таблицы для нее. 79 При наличии циклических ссылок таблиц друг на друга придется воспользоваться ко- мандой ALTER TABLE, о которой речь пойдет в следующем разделе этой главы. Поскольку две первые таблицы — «Самолеты» (aircrafts) и «Места» (seats) — мы уже изучили в главе 3, то перейдем к таблице «Аэропорты» (airports). Она не имеет внешних ключей. В этой таблице в качестве кода аэропорта служат трехбуквенные обозначения, утвер- жденные специальной организацией. При этом используются только буквы латин- ского алфавита. Каждый аэропорт имеет также и полное название. Оно не всегда совпадает с названием города, в котором аэропорт находится. Для города не преду- смотрено отдельной сущности, поэтому таблицы городов нет. Однако название горо- да присутствует в виде атрибута city. Назначение остальных атрибутов ясно из ком- ментариев, приведенных в SQL-команде. Комментарии в языке SQL обозначаются двумя символами «дефис». При создании таблиц в среде утилиты psql вводить комментарии не нужно, но если вы создаете тек- стовый файл, содержащий команды для создания объектов базы данных, то коммен- тарии нужно ввести. Это сделает такой файл более понятным для вас в будущем. 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 можно было ввести лишь первые символы имени таблицы и нажать кла- вишу Tab — psql дополнил бы имя. При этом символов должно быть столько, чтобы они однозначно определяли имя таблицы. В нашем случае есть еще таблица aircrafts, поэтому можно было сделать так: \d airp а затем нажать клавишу Tab. Можно было использовать автодополнение с самого на- чала: введя только первую букву имени таблицы, т. е. «a», сразу нажать Tab — psql дополнит до «air», поскольку есть варианты aircrafts и airports. Дальше вы можете до- бавить букву «p» и нажать Tab, а можете сначала просмотреть возможные варианты, нажав Tab дважды подряд. В результате вы получите примерно такой вывод на экран: Таблица "bookings.airports" Столбец | Тип | Модификаторы --------------+------------------+-------------- airport_code | character(3) | NOT NULL airport_name | text | NOT NULL city | text | NOT NULL 80 longitude | double precision | NOT NULL latitude | double precision | NOT NULL timezone | text | NOT NULL Индексы: "airports_pkey" PRIMARY KEY, btree (airport_code) Ссылки извне: 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. Обратите внимание, что в команде создания таблицы «Аэропорты» (airports) мы ука- зывали для атрибутов longitude и latitude тип данных float, определенный в стандарте SQL. Однако, согласно документации, если при объявлении типа float параметр, за- дающий точность, не указан, то это будет равносильно использованию типа double precision. PostgreSQL предлагает свое расширение — команду COMMENT, которая позволяет создавать комментарии (описания) к различным объектам базы данных. Эти ком- ментарии будут также сохраняться в базе данных. Например, для создания описания столбца city таблицы airports нужно сделать так: COMMENT ON COLUMN airports.city IS 'Город'; Чтобы увидеть описания столбцов таблицы, нужно в команде \d добавить символ «+», например: \d+ airports Следующая таблица — «Рейсы» (flights). Назначение ее атрибутов должно быть в це- лом понятно из комментариев, присутствующих в SQL-команде. Сначала приведем саму команду, а затем сделаем ряд пояснений. 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, -- Аэропорт отправления 81 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 ( actual_arrival IS NULL OR ( actual_departure IS NOT NULL AND actual_arrival IS NOT NULL AND actual_arrival > actual_departure ) ), CHECK ( status IN ( 'On Time', 'Delayed', 'Departed', 'Arrived', 'Scheduled', 'Cancelled' ) ), 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 ) ); В таблице предусмотрено три внешних ключа, которые ссылаются на таблицы «Само- леты» (aircrafts) и «Аэропорты» (airports). В качестве первичного ключа используется так называемый суррогатный ключ, состоящий из одного атрибута — flight_id. Об- ратите внимание, что тип данных этого атрибута — serial, т. е. значения целого типа для этого атрибута будут извлекаться из последовательности. Суррогатный ключ — это уникальный ключ, назначение которого — только идентифицировать строки в таблице. Зачастую для него используются целочисленные значения. Такому ключу не соответствует никакое свойство никакой сущности реального мира. Это — абстрак- ция, позволяющая в ряде случаев упростить определения таблиц, например, за счет сокращения числа атрибутов во внешних ключах до одного. В нашей таблице «Рейсы» (flights) суррогатный ключ как раз и служит для того, чтобы в таблицах, ссылающихся на нее, внешние ключи состояли только из атрибута flight_id. Конечно, существует и естественный уникальный ключ, состоящий из двух атрибу- тов: номер рейса (flight_no) и время вылета по расписанию (scheduled_departure). Для него нам придется создать уникальный ключ, чтобы избежать дублирования значе- ний: очевидно, что в один и тот же момент времени не могут выполняться два (и более) рейса, имеющие один и тот же номер. Обратите внимание, что для атрибутов, имеющих смысл даты/времени, выбран тип данных timestamptz, т. е. временная отметка с указанием часового пояса. Это важно, т. к. перелеты могут совершаться между городами, находящимися в разных часовых поясах, а время вылета и время прилета указывается местное. Поясним смысл каждого из трех ограничений CHECK. Первое ограничение говорит о том, что время прилета по расписанию должно быть больше времени вылета по расписанию. Это представляется очевидным, т. к. длительность полета всегда больше нуля. 82 Второе ограничение более сложное. Его можно условно разделить на две части, со- единенные логической операцией «ИЛИ». Первая часть говорит о том, что если са- молет еще не прилетел (т. е. значение actual_arrival равно NULL), то фактическое вре- мя вылета нас, образно говоря, не интересует. Самолет мог еще не вылететь или уже вылететь. Но даже если он уже и вылетел, и значение атрибута actual_departure отлич- но от NULL, то все равно сравнить его со значением атрибута actual_arrival, которое пока еще равно NULL, невозможно. Речь идет о сравнении вида «>» или «<». Вто- рая часть этого ограничения должна гарантировать, что если самолет уже прилетел, то, во-первых, фактическое время вылета должно быть не равно NULL, а во-вторых, фактическое время прилета должно быть больше фактического времени вылета. И наконец, третье ограничение CHECK ограничивает множество допустимых значе- ний атрибута status следующим списком: – Scheduled — рейс доступен для бронирования (это происходит за месяц до плано- вой даты вылета, а до этого запись о рейсе не существует в базе данных); – On Time — рейс доступен для регистрации (за сутки до плановой даты вылета) и не задержан; – Delayed — рейс доступен для регистрации (за сутки до плановой даты вылета), но задержан; – Departed — самолет уже вылетел и находится в воздухе; – Arrived — самолет прибыл в пункт назначения; – Cancelled — рейс отменен. Просмотреть описание таблицы в базе данных можно так: \d flights Поскольку до сих пор мы давали подробные пояснения по каждой таблице, то сей- час ограничимся только указанием на те сведения, которые могут быть непонятны- ми. В частности, обратите внимание, что для атрибута flight_id указан тип данных integer, а не serial, как предписано в команде для создания этой таблицы. В главе 4 при рассмотрении типа данных serial мы говорили, ссылаясь на документацию, что этот тип является, по сути, удобной синтаксической заменой, избавляющей админи- стратора базы данных от необходимости выполнения SQL-команд для явного созда- ния последовательности и привязки ее к конкретному столбцу таблицы. О том, что значения для этого столбца будут формироваться с помощью последовательности, говорит фраза DEFAULT nextval('flights_flight_id_seq'::regclass) В этой фразе указано и имя последовательности — flights_flight_id_seq. Если выпол- нить команду \d то можно увидеть эту последовательность в списке объектов базы данных. 83 Список отношений Схема | Имя | Тип | Владелец ----------+-----------------------+--------------------+---------- bookings | flights_flight_id_seq | последовательность | postgres (11 строк) Чтобы посмотреть описание последовательности flights_flight_id_seq, нужно исполь- зовать команду \d: |