Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
ALTER TABLE seats RENAME CONSTRAINT seats_fare_conditions_fkey TO seats_fare_conditions_code_fkey; Как всегда, проверим, что получилось: \d seats И в заключение этого раздела вернемся к таблице «Классы обслуживания». Мы преду- смотрели в ней первичный ключ, но ведь значения атрибута «Наименование класса обслуживания» (fare_conditions_name) также должны быть уникальными, дубли- рование значений не допускается. Давайте добавим ограничение уникальности по этому столбцу: ALTER TABLE fare_conditions ADD UNIQUE ( fare_conditions_name ); И как всегда, на всякий случай проверим, что получилось: \d fare_conditions 122 5.4. Представления 5.4. Представления При работе с базами данных зачастую приходится многократно выполнять одни и те же запросы, которые могут быть весьма сложными и требовать обращения к несколь- ким таблицам. Чтобы избежать необходимости многократного формирования таких запросов, можно использовать так называемые представления (views). Если речь идет о выборке данных, то представления практически неотличимы от таблиц с точки зре- ния обращения к ним в командах SELECT. Упрощенный синтаксис команды CREATE VIEW, предназначенной для создания представлений, таков: CREATE VIEW имя-представления [ ( имя-столбца [, ...] ) ] AS запрос; В этой команде обязательными элементами являются имя представления и запрос к базе данных, который и формирует выборку из нее. Если список имен столбцов не указан, тогда их имена «вычисляются» (формируются) на основании текста запроса. Давайте создадим простое представление. В главе 3 мы решали задачу: подсчитать количество мест в салонах для всех моделей самолетов с учетом класса обслуживания (бизнес-класс и экономический класс). Запрос был таким: SELECT aircraft_code, fare_conditions, count( * ) FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; На его основе создадим представление и дадим ему имя, отражающее суть этого представления. CREATE VIEW seats_by_fare_cond AS SELECT aircraft_code, fare_conditions, count( * ) FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; Теперь мы можем вместо написания сложного первоначального запроса обращаться непосредственно к представлению, как будто это обычная таблица. 123 Глава 5. Основы языка определения данных SELECT * FROM seats_by_fare_cond; В отличие от таблиц, представления не содержат данных. При каждом обращении к представлению в команде SELECT данные выбираются из таблиц, на основе которых это представление создано. СУБД PostgreSQL предлагает свое расширение команды CREATE VIEW, а именно — фразу OR REPLACE. Если представление уже существует, то можно его не удалять, а просто заменить новой версией. Однако нужно помнить о том, что при создании новой версии представления (без явного удаления старой с помощью команды DROP VIEW) должны оставаться неизменными имена столбцов представления. Если же вы хотите изменить имя хотя бы одного столбца, то сначала нужно удалить представле- ние с помощью команды DROP VIEW, а уже затем создать его заново. Имена столбцов можно явно указать в команде, но если они не указаны, то СУБД сама «вычислит» эти имена. В только что созданном нами представлении третий столбец получит имя count. Если мы захотим изменить это имя, то возможны два способа: первый заключается в том, чтобы создать псевдоним для этого столбца с помощью ключевого слова AS, а второй — в указании списка имен столбцов в начале команды CREATE VIEW. Попробуем воспользоваться первым способом (обратите внимание на добавление фразы OR REPLACE и ключевого слова AS после вызова функции count): CREATE OR REPLACE VIEW seats_by_fare_cond AS SELECT aircraft_code, fare_conditions, count( * ) AS num_seats FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; Однако СУБД выдаст сообщение об ошибке: ОШИБКА: изменить имя столбца "count" на "num_seats" в представлении нельзя В чем дело? А дело в том, что при первоначальном создании этого представления третий столбец уже получил имя count (такое имя ему дала СУБД). Поэтому если мы хотим воспользоваться вариантом команды с фразой OR REPLACE, мы не должны изменять названия столбцов ни путем указания псевдонимов, ни с помощью спис- ка имен столбцов, приводимого в начале команды. Так что если мы все же захотим 124 5.4. Представления изменить имя столбца в представлении, нам придется сначала удалить это представ- ление, а затем создать его заново. DROP VIEW seats_by_fare_cond; CREATE OR REPLACE VIEW seats_by_fare_cond AS SELECT aircraft_code, fare_conditions, count( * ) AS num_seats FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; А вот и второй способ задания имен столбцов в представлении — с помощью списка их имен, заключенного в скобки: DROP VIEW seats_by_fare_cond; CREATE OR REPLACE VIEW seats_by_fare_cond ( code, fare_cond, num_seats ) AS SELECT aircraft_code, fare_conditions, count( * ) FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; Представления позволяют облегчить развитие и модификацию базы данных, потому что они могут позволить сохранить интерфейс неизменным, но сам запрос, кото- рый лежит в основе конкретного представления, может измениться. При этом для прикладного программиста представление останется неизменным, поэтому не по- требуется переделывать запросы к этому представлению в прикладной программе. В базе данных «Авиаперевозки» создано представление «Рейсы» (flights_v), скон- струированное на основе таблицы «Рейсы» (flights), но содержащее дополнитель- ную информацию, а именно: – подробные сведения об аэропорте вылета (departure_airport, departure_airport_name, departure_city); – подробные сведения об аэропорте прибытия (arrival_airport, arrival_airport_name, arrival_city); 125 Глава 5. Основы языка определения данных – местное время вылета, как плановое, так и фактическое (scheduled_departure_local, actual_departure_local); – местное время прибытия, как плановое, так и фактическое (scheduled_arrival_local, actual_arrival_local); – продолжительность полета, как плановая, так и фактическая (scheduled_duration, actual_duration). Мы только опишем все столбцы представления, а SQL-команду для его создания при- ведем в главе 6. Описание атрибута Имя атрибута Тип PostgreSQL Идентификатор рейса flight_id integer Номер рейса flight_no char(6) Время вылета по расписанию scheduled_departure timestamptz Время вылета по расписанию, местное время в пункте отправления scheduled_departure_local timestamp Время прилета по расписанию scheduled_arrival timestamptz Время прилета по расписанию, местное время в пункте прибытия scheduled_arrival_local timestamp Планируемая продолжительность полета scheduled_duration interval Код аэропорта отправления departure_airport char(3) Название аэропорта отправления departure_airport_name text Город отправления departure_city text Код аэропорта прибытия arrival_airport char(3) Название аэропорта прибытия arrival_airport_name text Город прибытия arrival_city text Статус рейса status varchar(20) Код самолета, IATA aircraft_code char(3) Фактическое время вылета actual_departure timestamptz Фактическое время вылета, местное время в пункте отправления actual_departure_local timestamp Фактическое время прилета actual_arrival timestamptz Фактическое время прилета, местное время в пункте прибытия actual_arrival_local timestamp Фактическая продолжительность полета actual_duration interval Известно, что в сфере железнодорожных пассажирских перевозок время в расписа- нии движения поездов и в билетах указывается московское. А в пассажирских авиа- 126 5.4. Представления перевозках, напротив, время в билетах указывается местное. Это касается и времени вылета и времени прилета. Если пункты отправления и назначения находятся в раз- личных часовых поясах, то время вылета будет привязано к одному часовому поясу, а время прилета — к другому. Поэтому в нашем представлении «Рейсы» (flights_v) предусмотрены четыре столб- ца, отображающие местное время: два из них относятся к пункту отправления — scheduled_departure_local и actual_departure_local, а два других относят- ся к пункту прибытия — scheduled_arrival_local и actual_arrival_local. В качестве типа данных для этих четырех столбцов выбран тип timestamp without time zone (сокращенно — просто timestamp), а не timestamp with time zone (timestamptz). Причина в том, что при выборе timestamptz время автоматически преобразовывалось бы при выводе данных к текущему часовому поясу, установлен- ному на компьютере пользователя, а нам нужно сохранить его значения такими, ка- кими они являются в пункте отправления и пункте назначения. Для перевода значения типа timestamptz (с часовым поясом) в значение типа timestamp (без часового пояса) служит конструкция AT TIME ZONE, подробно рас- смотренная в разделе 9.9 «Операторы и функции даты/времени» документации. Так- же существует и эквивалентная функция timezone, которая и используется здесь для пересчета московского времени в местное. Если вы испытываете затруднения с пониманием операций преобразования значе- ний типа timestamptz в значения типа timestamp, рекомендуем вам обратиться к разделу документации 8.5.1.3 «Даты и время». Посмотреть описание представления в базе данных можно с помощью команды \d flights_v В представлении «Рейсы» много столбцов, поэтому при выводе информации из него в виде таблицы каждая строка на экране будет сворачиваться «змейкой», что не очень наглядно. Утилита psql предлагает альтернативный — расширенный — способ вывода информации, который включается с помощью команды \x Для возвращения к табличному формату вывода нужно выполнить эту же команду еще раз. Включив расширенный вывод, выполните команду для выборки данных из представ- ления «Рейсы». 127 Глава 5. Основы языка определения данных SELECT * FROM flights_v; -[ RECORD 1 ]--------------+------------------------- flight_id | 1 flight_no | PG0405 scheduled_departure | 2016-09-13 13:35:00+08 scheduled_departure_local | 2016-09-13 08:35:00 scheduled_arrival | 2016-09-13 14:30:00+08 scheduled_arrival_local | 2016-09-13 09:30:00 scheduled_duration | 00:55:00 departure_airport | DME departure_airport_name | Домодедово departure_city | Москва arrival_airport | LED arrival_airport_name | Пулково arrival_city | Санкт-Петербург status | Arrived aircraft_code | 321 actual_departure | 2016-09-13 13:44:00+08 actual_departure_local | 2016-09-13 08:44:00 actual_arrival | 2016-09-13 14:39:00+08 actual_arrival_local | 2016-09-13 09:39:00 actual_duration | 00:55:00 Бывают ситуации, когда заранее известно, что возможна попытка удаления несуще- ствующего представления. В таких случаях обычно стараются избежать ненужных сообщений об ошибке отсутствия представления. Для этого в команду DROP VIEW до- бавляют фразу IF EXISTS. Например: DROP VIEW IF EXISTS flights_v; Как мы уже говорили ранее, представление является фактически сохраненным за- просом к базе данных. Этот запрос получает имя, которым можно воспользоваться в предложении FROM команды SELECT для получения результатов этого запроса. PostgreSQL предлагает свое расширение — так называемое материализованное пред- ставление. Упрощенный синтаксис команды CREATE MATERIALIZED VIEW, предна- значенной для создания материализованных представлений, таков: CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] имя-мат-представления [ ( имя-столбца [, ...] ) ] AS запрос [ WITH [ NO ] DATA ]; 128 5.4. Представления В момент выполнения команды создания материализованного представления оно заполняется данными, но только если в команде не было фразы WITH NO DATA. Ес- ли же она была включена в команду, тогда в момент своего создания представле- ние остается пустым, а для заполнения его данными нужно использовать команду REFRESH MATERIALIZED VIEW. Материализованное представление очень похоже на обычную таблицу. Однако оно отличается от таблицы тем, что не только сохраняет данные, но также запоминает запрос, с помощью которого эти данные были собраны. В нашей учебной базе данных «Авиаперевозки» имеется материализованное пред- ставление — «Маршруты» (routes). Как вы могли заметить, таблица «Рейсы» содер- жит избыточность: для одного и того же номера рейса, отправляющегося в различные дни, повторяются коды аэропортов отправления и назначения, а также код самоле- та. Таким образом, из этой таблицы можно извлечь информацию о маршруте, т. е. номер рейса, аэропорты отправления и назначения. Эта информация не зависит от конкретной даты вылета. Опишем все столбцы представления «Маршруты», а SQL-команду для его создания приведем в главе 6. Описание атрибута Имя атрибута Тип PostgreSQL Номер рейса flight_no char(6) Код аэропорта отправления departure_airport char(3) Название аэропорта отправления departure_airport_name text Город отправления departure_city text Код аэропорта прибытия arrival_airport char(3) Название аэропорта прибытия arrival_airport_name text Город прибытия arrival_city text Код самолета, IATA aircraft_code char(3) Продолжительность полета duration interval Дни недели, когда выполняются рейсы days_of_week integer[ ] Обратите внимание на тип данных последнего столбца — «Дни недели, когда выпол- няются рейсы». Это массив целых чисел. Если впоследствии вам потребуется обновить данные в материализованном пред- ставлении, то выполните команду REFRESH MATERIALIZED VIEW routes; 129 Глава 5. Основы языка определения данных Кончено, как и любой другой объект базы данных, материализованное представле- ние можно удалить. DROP MATERIALIZED VIEW routes; Подводя итог раздела, назовем положительные стороны использования представле- ний. 1. Упрощение разграничения полномочий пользователей на доступ к хранимым данным. Разным типам пользователей могут требоваться различные данные, хранящие- ся в одних и тех же таблицах. Это касается как столбцов, так и строк таблиц. Со- здание различных представлений для разных пользователей избавляет от необ- ходимости создавать дополнительные таблицы, дублируя данные, и упрощает организацию системы управления доступом к данным. 2. Упрощение запросов к базе данных. Запросы к базе данных могут включать несколько таблиц и быть весьма слож- ными и громоздкими, при этом такие запросы могут выполняться часто. Ис- пользование представлений позволяет скрыть эти сложности от прикладного программиста и сделать запросы более простыми и наглядными. 3. Снижение зависимости прикладных программ от изменений структуры таблиц базы данных. В процессе развития информационной системы структура таблиц базы данных может изменяться. Столбцы представления, т. е. их имена, типы данных и по- рядок следования, — это, образно говоря, интерфейс к запросу, который реа- лизуется данным представлением. Если этот интерфейс остается неизменным, то SQL-запросы, в которых используется данное представление, корректиро- вать не потребуется. Нужно будет лишь в ответ на изменение структуры базовых таблиц, на основе которых представление сконструировано, соответствующим образом перестроить запрос, выполняемый данным представлением. 4. Снижение времени выполнения сложных запросов за счет использования мате- риализованных представлений. В материализованных представлениях можно сохранять результаты выполне- ния запросов, которые формируются длительное время, но при этом допускают их формирование заранее, а не обязательно в момент возникновения потребно- сти в результатах этого запроса. Если, например, какой-нибудь сводный отчет формируется длительное время, а запросы к отчету будут неоднократными, то 130 5.5. Схемы базы данных может оказаться целесообразным сформировать его заранее и сохранить в ма- териализованном представлении. Тем не менее нужно учитывать, что применимость материализованных пред- ставлений весьма ограничена. Не следует заменять ими все сложные запросы. Одним из недостатков является то, что их необходимо своевременно обновлять с помощью команды REFRESH, чтобы они содержали актуальные данные. 5.5. Схемы базы данных Схема — это логический фрагмент базы данных, в котором могут содержаться раз- личные объекты: таблицы, представления, индексы и др. В базе данных обязательно есть хотя бы одна схема. При создании базы данных в ней автоматически создается схема с именем public. Когда мы с вами создавали таблицы в базе данных edu, они создавались именно в этой схеме. В каждой базе данных может содержаться более одной схемы. Их имена должны быть уникальными в пределах конкретной базы данных. Имена объектов базы дан- ных (таблиц, представлений, последовательностей и др.) должны быть уникальными в пределах конкретной схемы, но в разных схемах имена объектов могут повторять- ся. Таким образом, можно сказать, что схема образует так называемое пространство имен Посмотреть список схем в базе данных можно так: \dn Список схем Имя | Владелец ----------+---------- bookings | postgres public | postgres (2 строки) В учебной базе данных demo есть схема bookings. Все таблицы созданы именно в этой схеме. Для организации доступа к ней вы уже выполняли команду SET search_path = bookings; 131 Глава 5. Основы языка определения данных Теперь объясним подробнее, что эта команда делает. Если в базе данных создано более одной схемы, то доступ к объектам, содержащимся в конкретной схеме, можно организовать разными способами. Первый заключается в том, чтобы имена объектов предварять именем схемы. Например, для обращения к таблице aircrafts нужно сделать так: SELECT * FROM bookings.aircrafts; Однако такой способ не очень удобен. Другой способ заключается в том, чтобы одну из схем сделать текущей. Среди параметров времени исполнения, которые преду- смотрены в конфигурации сервера PostgreSQL, есть параметр search_path. Его зна- чение по умолчанию можно изменить в конфигурационном файле postgresql.conf. Он содержит имена схем, которые PostgreSQL просматривает при поиске конкретного объекта базы данных, когда имя схемы в команде не указано. Посмотреть значение этого параметра можно с помощью команды SHOW: SHOW search_path; search_path ----------------- "$user", public (1 строка) Схема "$user" присутствует в этом параметре на тот случай, если будут созданы схе- мы с именами, совпадающими с именами пользователей. Тогда могут упроститься некоторые операции с базой данных. Однако в базе данных demo нет таких схем, по- этому первый элемент параметра search_path фактически не участвует в работе, в результате все обращения к объектам базы данных без указания имени схемы бу- дут адресоваться схеме public. Чтобы изменить порядок просмотра схем при поиске объектов в базе данных, нуж- но воспользоваться командой SET. При этом первой в списке схем следует указать именно ту, которую СУБД должна просматривать первой. Эта схема и станет теку- щей. Конечно, такой список может состоять и всего из одной схемы. Давайте выполним команду SET search_path = bookings; А теперь посмотрим, что получилось: SHOW search_path; 132 Контрольные вопросы и задания search_path ------------- bookings (1 строка) Да, действительно, теперь первой будет просматриваться схема bookings. А для об- ращения к объектам, например, таблицам, в схеме public (если бы они в ней были) нам пришлось бы указывать имя схемы public перед именами этих объектов. Ес- ли бы мы решили добавить схему public в список просматриваемых схем, то нужно было бы включить ее в команду SET: SET search_path = bookings, public; Узнать имя текущей схемы можно с помощью встроенной функции current_schema (обратите внимание на отсутствие скобок при вызове функции в команде SELECT). SELECT current_schema; current_schema ---------------- bookings (1 строка) При создании объектов базы данных, например таблиц, необходимо учитывать сле- дующее: если имя схемы в команде не указано, то объект будет создан в текущей схеме. Если же вы хотите создать объект в конкретной схеме, которая не является текущей, то нужно указать ее имя перед именем создаваемого объекта, разделив их точкой. Например, для создания таблицы airports в схеме my_schema следует сде- лать так: CREATE TABLE my_schema.airports ... Контрольные вопросы и задания 1. При использовании значений по умолчанию с ключевым словом DEFAULT воз- можны и ситуации, когда типичным будет не конкретное значение данных, а способ его получения. Например, если мы захотим фиксировать в каждой строке таблицы «Студенты» имя пользователя базы данных, добавившего эту строку в таблицу, тогда необходимо в определение таблицы добавить еще один 133 Глава 5. Основы языка определения данных столбец. Этот столбец по умолчанию будет получать значение, возвращаемое функцией current_user. CREATE TABLE students ( record_book numeric( 5 ) NOT NULL, name text NOT NULL, doc_ser numeric( 4 ), doc_num numeric( 6 ), who_adds_row text DEFAULT current_user, -- добавленный столбец PRIMARY KEY ( record_book ) ); Эта функция — current_user — будет вызываться не при создании таблицы, а при вставке каждой строки. При этом в команде INSERT не требуется указы- вать значение для столбца who_adds_row, поскольку функция current_user будет вызываться самой СУБД PostgreSQL: INSERT INTO students ( record_book, name, doc_ser, doc_num ) VALUES ( 12300, 'Иванов Иван Иванович', 0402, 543281 ); Давайте пойдем дальше и пожелаем фиксировать не только имя пользователя базы данных, добавившего строку в таблицу, но также и момент времени, когда это было сделано. Самостоятельно внесите модификацию в определение табли- цы students для решения этой задачи, а затем выполните команду INSERT для проверки полученного решения. Если до выполнения этого упражнения вы еще не ознакомились с командой ALTER TABLE, то вместо модифицирования определения таблицы сначала уда- лите ее, а затем создайте заново: DROP TABLE students; CREATE TABLE students ... 2. Посмотрите, какие ограничения уже наложены на атрибуты таблицы «Успевае- мость» (progress). Воспользуйтесь командой \d утилиты psql. А теперь пред- ложите для этой таблицы ограничение уровня таблицы. В качестве примера рассмотрим такой вариант. Добавьте в таблицу progress еще один атрибут — «Форма проверки знаний» (test_form), который может принимать только два значения: «экзамен» или «зачет». Тогда набор допусти- мых значений атрибута «Оценка» (mark) будет зависеть от того, экзамен или за- чет предусмотрены по данной дисциплине. Если предусмотрен экзамен, тогда допускаются значения 3, 4, 5, если зачет — тогда 0 (не зачтено) или 1 (зачтено). 134 Контрольные вопросы и задания Не забудьте, что значения NULL для атрибутов test_form и mark не допуска- ются. Новое ограничение может быть таким: ALTER TABLE progress ADD CHECK ( ( test_form = 'экзамен' AND mark IN ( 3, 4, 5 ) ) OR ( test_form = 'зачет' AND mark IN ( 0, 1 ) ) ); Проверьте, как будет работать новое ограничение в модифицированной таб- лице progress. Для этого выполните команды INSERT, как удовлетворяющие ограничению, так и нарушающие его. В таблице уже было ограничение на допустимые значения атрибута mark. Как вы думаете, не будет ли оно конфликтовать с новым ограничением? Проверьте эту гипотезу. Если ограничения конфликтуют, тогда удалите старое ограниче- ние и снова попробуйте добавить строки в таблицу. Подумайте, какое еще ограничение уровня таблицы можно предложить для этой таблицы? 3.* В определении таблицы «Успеваемость» (progress) на атрибуты term и mark наложены как ограничения CHECK, так и ограничение NOT NULL. Возникает во- прос: не является ли ограничение NOT NULL избыточным? Ведь в ограничении CHECK явно указаны допустимые значения. Проверьте гипотезу об избыточности ограничения NOT NULL в данном случае. Для этого модифицируйте таблицу, убрав ограничение NOT NULL, и попробуйте добавить в нее строку с отсутствующим значением атрибута term (или mark). 4. В определении таблицы «Успеваемость» (progress) для атрибута mark не толь- ко задано ограничение CHECK, но и установлено значение по умолчанию с по- мощью ключевого слова DEFAULT: ... mark numeric( 1 ) NOT NULL CHECK ( mark >= 3 AND mark <= 5 ) DEFAULT 5, ... 135 Глава 5. Основы языка определения данных Как вы думаете, что будет, если в ограничении DEFAULT мы «случайно» допу- стим ошибку, написав DEFAULT 6? Если в команде INSERT не указать значение для атрибута mark, то на каком этапе эта ошибка будет выявлена: уже на этапе создания таблицы или только при вставке строки в нее? Вот эта команда может быть вам полезной для проверки гипотезы, поскольку в ней отсутствует передаваемое значение для атрибута mark: INSERT INTO progress ( record_book, subject, acad_year, term ) VALUES ( 12300, 'Физика', '2016/2017',1 ); 5. В стандарте SQL сказано, что при наличии ограничения уникальности, вклю- чающего один или более столбцов, все же возможны повторяющиеся значения этих столбцов в разных строках, но лишь в том случае, если это значения NULL. PostgreSQL придерживается такого же подхода. Модифицируйте определение таблицы «Студенты» (students), добавив огра- ничение уникальности по двум столбцам: doc_ser и doc_num. А затем про- верьте вышеприведенное утверждение, добавив в таблицу не только строки, со- держащие конкретные значения этих двух столбцов, но также и по две строки, имеющие следующие свойства: – одинаковые значения столбца doc_ser и NULL-значения столбца doc_num; – NULL-значения столбца doc_num и столбца doc_ser. Подобные вещи возможны, так как NULL-значения не считаются совпадающи- ми. Это можно проверить с помощью команды SELECT (null = null); Она даст такой результат (т. е. NULL): ?column? ---------- (1 строка) 6. Модифицируйте определения таблиц «Студенты» (students) и «Успеваемость» (progress). В таблице students в качестве первичного ключа назначьте ком- бинацию атрибутов doc_ser и doc_num, а в таблице progress соответствую- щим образом измените определение внешнего ключа. 136 Контрольные вопросы и задания CREATE TABLE students ( record_book numeric( 5 ) NOT NULL UNIQUE, name text NOT NULL, doc_ser numeric( 4 ), doc_num numeric( 6 ), PRIMARY KEY ( doc_ser, doc_num ) ); Обратите внимание, что для атрибутов doc_ser и doc_num можно не указывать ограничение NOT NULL: они входят в состав первичного ключа, а в нем NULL- значения не допускаются, поэтому ограничение NOT NULL фактически подра- зумевается при включении атрибута в состав первичного ключа. CREATE TABLE progress ( doc_ser numeric( 4 ), doc_num numeric( 6 ), 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 ( doc_ser, doc_num ) REFERENCES students ( doc_ser, doc_num ) ON DELETE CASCADE ON UPDATE CASCADE ); Теперь и первичный, и внешний ключи — составные. Проверьте их действие, добавив несколько строк в каждую таблицу. 7.* Модифицируйте определение таблицы «Успеваемость» (progress), а если по- требуется, то и определение таблицы «Студенты» (students), чтобы изучить все варианты реагирования СУБД на обновление строк в ссылочной таблице, в данном случае — students. Последовательно изменяйте определение внеш- него ключа таблицы progress, испробовав варианты ON UPDATE CASCADE, ON UPDATE RESTRICT, ON UPDATE SET NULL и ON UPDATE SET DEFAULT. Для получе- ния информативной картины введите несколько строк в обе таблицы, а затем выполняйте операцию UPDATE, подбирая значения ключевых атрибутов таким образом, чтобы вызвать ожидаемую реакцию СУБД. Учтите, что при использовании фразы ON UPDATE SET DEFAULT необходимо, чтобы, во-первых, с помощью ключевого слова DEFAULT было установлено зна- чение по умолчанию для атрибута внешнего ключа в ссылающейся таблице, 137 Глава 5. Основы языка определения данных а во-вторых, это значение по умолчанию все равно должно присутствовать в одной из строк ссылочной таблицы. Как вы считаете, с учетом сказанного, воз- можно ли использование ON UPDATE SET DEFAULT в нашем случае? Попробуйте обосновать или, наоборот, опровергнуть целесообразность исполь- зования каждой из этих политик — CASCADE, RESTRICT, SET NULL и SET DEFAULT — при выполнении операции UPDATE в реальной информационной системе, предназначенной для учета успеваемости студентов. 8. В таблице «Успеваемость» (progress) есть атрибут «Учебная дисциплина» (subject). Это текстовый атрибут. Одинаковые наименования учебных дисци- плин записываются в таблицу progress многократно. Создайте еще одну таб- лицу — «Учебные дисциплины» (subjects), в которой будет два атрибута: «Идентификатор учебной дисциплины» (subject_id) и «Учебная дисциплина» (subject). Тип данных первого из них будет integer, а второго — text. В ка- честве первичного ключа будет служить subject_id, а второй атрибут будет уникальным. Введите в новую таблицу две-три строки для различных учебных дисциплин. Модифицируйте таблицу progress, заменив атрибут subject на subject_id. Тип данных нового атрибута будет integer. Поскольку тип данных изменится, то для замены первоначальных значений, хранящихся в этом столбце, на новые придется использовать конструкцию USING (о ней говорится в тексте главы). Добавьте в определение таблицы progress еще один внешний ключ, который будет ссылаться на таблицу subjects. В составе этого внешнего ключа будет только один атрибут — subject_id. Мы видим, что таблица может иметь боль- ше одного внешнего ключа. Таким образом, структура связей в реальной базе данных может оказаться весьма сложной. Теперь введите несколько строк и в таблицу progress, учитывая ее связь с но- вой таблицей subjects. 9. В таблице «Студенты» (students) есть текстовый атрибут name, на который на- ложено ограничение NOT NULL. Как вы думаете, что будет, если при вводе новой строки в эту таблицу дать атрибуту name в качестве значения пустую строку? Например: INSERT INTO students ( record_book, name, doc_ser, doc_num ) VALUES ( 12300, '', 0402, 543281 ); 138 Контрольные вопросы и задания Наверное, проектируя эту таблицу, мы хотели бы все же, чтобы пустые строки в качестве значения атрибута name не проходили в базу данных? Какое реше- ние вы можете предложить? Видимо, нужно добавить ограничение CHECK для столбца name. Если вы еще не изучили команду ALTER TABLE, то удалите табли- цу students и создайте ее заново с учетом нового ограничения, а если вы уже познакомились с командой ALTER TABLE, то сделайте так: ALTER TABLE students ADD CHECK ( name <> '' ); Добавив ограничение, попробуйте теперь вставить в таблицу students строку (row), в которой значение атрибута name было бы пустой строкой (string). Давайте продолжим эксперименты и предложим в качестве значения атрибута name строку, содержащую сначала один пробел, а потом — два пробела. INSERT INTO students VALUES ( 12346, ' ', 0406, 112233 ); INSERT INTO students VALUES ( 12347, ' ', 0407, 112234 ); Для того чтобы «увидеть» эти пробелы в выборке, сделаем так: SELECT *, length( name ) FROM students; Оказывается, эти невидимые значения имеют ненулевую длину. Что делать, чтобы не допустить таких значений-невидимок? Один из способов: возложить проверку таких ситуаций на прикладную программу. А что можно сделать на уровне определения таблицы students? Какое ограничение нужно предло- жить? В разделе 9.4 документации «Строковые функции и операторы» есть функция trim. Попробуйте воспользоваться ею. Если вы еще не изучили коман- ду ALTER TABLE, то удалите таблицу students и создайте ее заново с учетом нового ограничения, а если уже познакомились с ней, то сделайте так: ALTER TABLE students ADD CHECK (...); Есть ли подобные слабые места в таблице «Успеваемость» (progress)? 10. В таблице «Студенты» (students) атрибут «Серия документа, удостоверяюще- го личность» (doc_ser) имеет числовой тип, однако в сериях таких документов могут встречаться лидирующие нули, которые в числовых столбцах не сохраня- ются. Например, при записи значения серии «0402» первый ноль не сохранится. Модифицируйте таблицу students, заменив числовой тип данных на символь- ный, например, character. Как вы думаете, эта операция пройдет без затруд- нений или они все же возможны? Проверьте ваши предположения, выполнив модификацию. 139 Глава 5. Основы языка определения данных 11.* В таблице «Рейсы» (flights) есть ограничение, которое регулирует соотноше- ния значений фактического времени вылета и фактического времени прилета. Как вы думаете, не является ли выражение actual_arrival IS NOT NULL во второй части условного оператора OR избыточным? CREATE TABLE flights ( ... CHECK ( actual_arrival IS NULL OR ( actual_departure IS NOT NULL AND actual_arrival IS NOT NULL AND actual_arrival > actual_departure ) ), ... Проверьте ваши предположения на практике. Для этого сначала удалите суще- ствующее ограничение с помощью команды ALTER TABLE flights DROP CONSTRAINT имя-ограничения; Как определить имя этого ограничения? С помощью команды \d flights получите описание таблицы flights, а в нем есть названия всех ограничений. Затем создайте это же ограничение, но в модифицированном виде: ALTER TABLE flights ADD CHECK ( actual_arrival IS NULL OR ( actual_departure IS NOT NULL AND actual_arrival > actual_departure ) ); Попробуйте добавить в таблицу flights две-три строки, подбирая такие зна- чения атрибутов actual_departure и actual_arrival, чтобы проверить все возможные исходы этих проверок. Конечно, вместо добавления новых строк можно модифицировать одну и ту же строку с помощью команды UPDATE. 12. Команда ALTER TABLE позволяет переименовать таблицу. Например: ALTER TABLE table_name RENAME TO new_table_name; 140 Контрольные вопросы и задания Поскольку в командах создания таблиц базы данных «Авиаперевозки» мы не указывали имена ограничений для первичных и внешних ключей, то их имена были сформированы автоматически самой СУБД. Как вы думаете, получили ли эти ограничения новые имена после переименования таблицы? Проверьте ваши предположения, выполнив такую операцию с одной из таблиц базы данных «Авиаперевозки», имеющих внешние ключи. 13. И представление «Рейсы» (flights_v), и материализованное представление «Маршруты» (routes) построены на основе таблиц «Рейсы» (flights) и «Аэро- порты» (airports). Логично предположить, что при каскадном удалении, на- пример, таблицы «Аэропорты», представление «Рейсы» будет также удалено, поскольку при удалении базовой таблицы этому представлению просто неот- куда будет брать данные. А что вы можете предположить насчет материализованного представления «Маршруты»: будет ли оно также удалено или нет? Ведь оно уже содержит дан- ные, в отличие от обычного представления. Так ли, условно говоря, сильна его связь с таблицами, на основе которых оно сконструировано? Проведите необходимые эксперименты, начав с команды DROP TABLE airports; Если вам потребуется восстановить все объекты базы данных, то вы всегда смо- жете воспользоваться файлом demo_small.sql и просто повторить процедуру развертывания учебной базы данных, которая описана в главе 2. Поэтому смело экспериментируйте с таблицами и представлениями. 14. Представления (views) могут быть обновляемыми. Это значит, что можно с по- мощью команд INSERT, UPDATE и DELETE, применяемых к представлению, вне- сти изменения в таблицу, лежащую в основе этого представления. Самостоятельно ознакомьтесь с этим вопросом с помощью документации (см. описание команды CREATE VIEW) и, создав простое представление над одной из таблиц базы данных «Авиаперевозки», выполните несколько команд с целью внесения изменений в эту таблицу. 15. Определение таблицы можно изменить с помощью команды ALTER TABLE. Ана- логичные команды существуют и для изменения представлений и материали- зованных представлений: ALTER VIEW и ALTER MATERIALIZED VIEW. Самосто- ятельно ознакомьтесь с их возможностями с помощью документации. 141 Глава 5. Основы языка определения данных 16. Как вы думаете, при изменении данных в таблицах, на основе которых скон- струировано материализованное представление, содержимое этого представ- ления тоже синхронно изменяется или нет? Если содержимое материализованного представления изменяется синхронно с базовыми таблицами, то продемонстрируйте это. Если же оно остается неиз- менным, то покажите, как его синхронизировать с базовыми таблицами. 17. Представления могут быть, условно говоря, вертикальными и горизонтальными. При создании вертикального представления в список его столбцов включается лишь часть столбцов базовой таблицы (таблиц). Например: CREATE VIEW airports_names AS SELECT airport_code, airport_name, city FROM airports; SELECT * FROM airports_names; В горизонтальное представление включаются не все строки базовой таблицы (таблиц), а производится их отбор с помощью фраз WHERE или HAVING. Например: CREATE VIEW siberian_airports AS SELECT * FROM airports WHERE city = 'Новосибирск' OR city = 'Кемерово'; SELECT * FROM siberian_airports; Конечно, вполне возможен и смешанный вариант, когда ограничивается как список столбцов, так и множество строк при создании представления. Подумайте, какие представления было бы целесообразно создать для нашей базы данных «Авиаперевозки». Необходимо учесть наличие различных групп пользователей, например: пилоты, диспетчеры, пассажиры, кассиры. Создайте представления и проверьте их в работе. 18.* Предположим, что нам понадобилось иметь в базе данных сведения о техниче- ских характеристиках самолетов, эксплуатируемых в авиакомпании. Пусть это будут такие сведения, как число членов экипажа (пилоты), тип двигателей и их количество. 142 Контрольные вопросы и задания Следовательно, необходимо добавить новый столбец в таблицу «Самолеты» (aircrafts). Дадим ему имя specifications, а в качестве типа данных выбе- рем jsonb. Если впоследствии потребуется добавить и другие характеристики, то мы сможем это сделать, не модифицируя определение таблицы. ALTER TABLE aircrafts ADD COLUMN specifications jsonb; ALTER TABLE Добавим сведения для модели самолета Airbus A320-200: UPDATE aircrafts SET specifications = '{ "crew": 2, "engines": { "type": "IAE V2500", "num": 2 } }'::jsonb WHERE aircraft_code = '320'; UPDATE 1 Посмотрим, что получилось: SELECT model, specifications FROM aircrafts WHERE aircraft_code = '320'; model | specifications -----------------+------------------------------------------- Airbus A320-200 | {"crew": 2, "engines": {"num": 2, "type": "IAE V2500"}} (1 строка) Можно посмотреть только сведения о двигателях: SELECT model, specifications->'engines' AS engines FROM aircrafts WHERE aircraft_code = '320'; model | engines -----------------+--------------------------------- Airbus A320-200 | {"num": 2, "type": "IAE V2500"} (1 строка) 143 Глава 5. Основы языка определения данных Чтобы получить еще более детальные сведения, например, о типе двигателей, нужно учитывать, что созданный JSON-объект имеет сложную структуру: он со- держит вложенный JSON-объект. Поэтому нужно использовать оператор #> для указания пути доступа к ключу второго уровня. SELECT model, specifications #> '{ engines, type }' FROM aircrafts WHERE aircraft_code = '320'; model | ?column? -----------------+------------- Airbus A320-200 | "IAE V2500" (1 строка) Задание. Подумайте, какие еще таблицы было бы целесообразно дополнить столбцами типа json/jsonb. Вспомните, что, например, в таблице «Билеты» (tickets) уже есть столбец такого типа — contact_data. Выполните модифи- кации таблиц и измените в них одну-две строки для проверки правильности ваших решений. 144 |