Файл: Учебное пособие СанктПетербург бхвпетербург.pdf

Контрольные вопросы и задания
Не забудьте, что значения 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