Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
5.5 Схемы базы данных Схема — это логический фрагмент базы данных, в котором могут содержаться раз- личные объекты: таблицы, представления, индексы и др. В базе данных обязательно есть хотя бы одна схема. При создании базы данных в ней автоматически создается схема с именем public. Когда мы с вами создавали таблицы в базе данных edu, они создавались именно в этой схеме. В каждой базе данных может содержаться более одной схемы. Их имена должны быть уникальными в пределах конкретной базы данных. Имена объектов базы данных (таблиц, представлений, последовательностей и др.) должны быть уникальными в пределах конкретной схемы, но в разных схемах имена объектов могут повторять- ся. Таким образом, можно сказать, что схема образует так называемое пространство имен Посмотреть список схем в базе данных можно так: \dn Список схем Имя | Владелец ----------+---------- bookings | postgres public | postgres (2 строки) В учебной базе данных demo есть схема bookings. Все таблицы созданы именно в этой схеме. Для организации доступа к ней вы уже выполняли команду SET search_path = bookings; Теперь объясним подробнее, что эта команда делает. Если в базе данных создано более одной схемы, то доступ к объектам, содержащимся в конкретной схеме, можно организовать разными способами. Первый заключается в том, чтобы имена объектов предварять именем схемы. Например, для обращения к таблице aircrafts нужно сделать так: SELECT * FROM bookings.aircrafts; Однако такой способ не очень удобен. Другой способ заключается в том, чтобы одну из схем сделать текущей. Среди параметров времени исполнения, которые преду- смотрены в конфигурации сервера PostgreSQL, есть параметр search_path. Его значе- ние по умолчанию можно изменить в конфигурационном файле postgresql.conf. Он содержит имена схем, которые PostgreSQL просматривает при поиске конкретного объекта базы данных, когда имя схемы в команде не указано. Посмотреть значение этого параметра можно с помощью команды SHOW: 99 SHOW search_path; search_path ----------------- "$user", public (1 строка) Схема "$user" присутствует в этом параметре на тот случай, если будут созданы схемы с именами, совпадающими с именами пользователей. Тогда могут упроститься неко- торые операции с базой данных. Однако в базе данных demo нет таких схем, поэтому первый элемент параметра search_path фактически не участвует в работе, в резуль- тате все обращения к объектам базы данных без указания имени схемы будут адре- соваться схеме public. Чтобы изменить порядок просмотра схем при поиске объектов в базе данных, нуж- но воспользоваться командой SET. При этом первой в списке схем следует указать именно ту, которую СУБД должна просматривать первой. Эта схема и станет теку- щей. Конечно, такой список может состоять и всего из одной схемы. Давайте выполним команду SET search_path = bookings; А теперь посмотрим, что получилось: SHOW search_path; 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 следует сделать так: 100 CREATE TABLE my_schema.airports ... Контрольные вопросы и задания 1. При использовании значений по умолчанию с ключевым словом DEFAULT воз- можны и ситуации, когда типичным будет не конкретное значение данных, а способ его получения. Например, если мы захотим фиксировать в каждой стро- ке таблицы «Студенты» (students) имя пользователя базы данных, добавившего эту строку в таблицу, тогда необходимо в определение таблицы добавить еще один столбец. Этот столбец по умолчанию будет получать значение, возвраща- емое функцией 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) будет зависеть от того, экзамен или зачет 101 предусмотрены по данной дисциплине. Если предусмотрен экзамен, тогда до- пускаются значения 3, 4, 5, если зачет — тогда 0 (не зачтено) или 1 (зачтено). Не забудьте, что значения 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, Как вы думаете, что будет, если в ограничении DEFAULT мы «случайно» допу- стим ошибку, написав DEFAULT 6? На каком этапе эта ошибка будет выявлена: уже на этапе создания таблицы или только при вставке строки в нее, если в ко- манде INSERT не указать значение для атрибута mark? Вот эта команда может быть вам полезной для проверки гипотезы, поскольку в ней отсутствует передаваемое значение для атрибута mark: INSERT INTO progress ( record_book, subject, acad_year, term ) VALUES ( 12300, 'Физика', '2016/2017',1 ); 102 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 соответствующим об- разом измените определение внешнего ключа. 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 103 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 было установлено зна- чение по умолчанию для атрибута внешнего ключа в ссылающейся таблице, а во-вторых, это DEFAULT-значение все равно должно присутствовать в одной из строк ссылочной таблицы. Как вы считаете, с учетом сказанного, возможно ли использование 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. 104 9. В таблице «Студенты» (students) есть текстовый атрибут name, на который нало- жено ограничение NOT NULL. Как вы думаете, что будет, если при вводе новой строки в эту таблицу дать атрибуту name в качестве значения пустую строку? Например: INSERT INTO students ( record_book, name, doc_ser, doc_num ) VALUES ( 12300, '', 0402, 543281 ); Наверное, проектируя эту таблицу, мы хотели бы все же, чтобы пустые строки в качестве значения атрибута 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 вы уже познакомились, то сделайте так: ALTER TABLE students ADD CHECK (...); Посмотрите и таблицу «Успеваемость» (progress) на предмет подобных слабых мест. 10. В таблице «Студенты» (students) атрибут «Серия документа, удостоверяющего личность» (doc_ser) имеет числовой тип, однако в сериях таких документов мо- гут встречаться лидирующие нули, которые в числовых столбцах не сохраняют- ся. Например, при записи значения серии «0402» первый ноль не сохранится в таблице. Модифицируйте таблицу students, заменив числовой тип данных на символь- ный, например, character. Как вы думаете, эта операция пройдет без затруд- нений или они все же возможны? Проверьте ваши предположения, выполнив модификацию. 105 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; Поскольку в командах создания таблиц базы данных «Авиаперевозки» мы не указывали имена ограничений для первичных и внешних ключей, то их имена были сформированы автоматически самой СУБД. Как вы думаете, получили ли эти ограничения новые имена после переименования таблицы? Проверьте ваши предположения, выполнив такую операцию с одной из таблиц базы данных «Авиаперевозки», имеющих внешние ключи. 106 13. Представление «Рейсы» (flights_v) и материализованное представление «Марш- руты» (routes) построены на основе таблиц «Рейсы» (flights) и «Аэропорты» (airports). Логично предположить, что при каскадном удалении, например, таб- лицы «Аэропорты», представление «Рейсы» будет также удалено, поскольку при удалении базовой таблицы этому представлению просто неоткуда будет брать данные. А что вы можете предположить насчет материализованного представ- ления «Маршруты»: будет ли оно также удалено или нет? Ведь оно уже содержит данные, в отличие от обычного представления. Так ли, условно говоря, сильна его связь с таблицами, на основе которых оно сконструировано? Проведите необходимые эксперименты, начав с команды |