Управление базой данных (БД) состоит в создании и отслеживании
состояния различных элементов (объектов) БД, таких как таблицы, индексы,
представления, последователности, триггеры и т.п., а также изменения их
информационного наполнения.
В полном объеме функции управления над любыми объектами БД может
осуществлять только суперпользователь (администратор БД), т.е. тот,
от чьего имени выполнена команда создания БД:
# createdb (в оболочке shell) или
=> CREATE DATABASE (оператор яэыка SQL в мониторе psql).
Простой пользователь БД (database user) может выполнять только те
действия, относительно которых ему предоставлены соответствующие права.
Но если он создает какие-либо объекты (для которых он становится владельцем
(owner)), то в отношении них он может предпринимать любые действия,
в том числе и передавать другим пользователям полномочия на действия с
созданными им объектами.
5.1 Управление атрибутами отношений.
Как указывалось ранее, свойства полей таблиц устанавливаются при
их создании оператором CREATE TABLE, полный синтаксис которого имеет
следующий вид:
CREATE TABLE <имя_таблицы> (
{ <имя_поля> <тип> [<ограничениe_поля>]|<ограничение_таблицы>}
[, ... ] );
В данной конструкции отражено наличие двух типов ограничений - на
значения конкретного атрибута отношения и ограничения таблицы в целом.
Ограничения полей всегда относятся только к одному полю и задаются сразу
же после определения типа, тогда как ограничения таблицы устанавливаются
в специальном блоке, отделенном запятой от всех определений полей. Поля,
на которые распространяются ограничения таблицы, задаются самим этим
ограничением, а не его расположением в команде.
5.1.1. Ограничения поля записываются после определения его типа, но перед
запятой, отделяющей определение одного поля от другого. Синтаксис
ограничения поля имеет вид:
[ CONSTRAINT <имя-ограничения> ]
{ NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT <значение> | CHECK <условие> |
REFRENCES <имя-таблицы>[ (<имя-поля>) ]
ON DELETE <операция>
ON UPDATE <операция>
}
Ограничению поля может быть присвоено имя ( фраза CONSTRAINT ), что удобно
в том случае, когда необходимо в дальнейшем удалить ограничение.
Фигурные скобки в синтаксической конструкции указывают на то, что хотя-бы
один из видов ограничений должен присутствовать.
Существует шесть типов ограничений, задаваемых при помощи ключевых слов,
некоторые из них устанавливаются при задании ограничений другого типа.
- NOT NULL - поле не может содержать "пустого" псевдозначения NULL;
- UNIQUE - поле не может содержать повторяющихся значений (однако
псевдозначение NULL может повторяться, т.к. формально оно считается не
совпадающим ни с каким значением, в том числе и с самим собой);
- PRIMARY KEY - ограничение первичного ключа (допускается только одно
в таблице), эквивалентно UNIQUE и NOT NULL;
- DEFAULT <значение> - пропущенные поля заменяются заданной величиной;
- CHECK <условие> - команды INSERT и UPDATE завершаются успешно лишь
если вводимые или изменяемые значения удовлетворяют условию;
- REFERENCES - эти ограничения состоят из нескольких секций:
REFERENCES <имя-таблицы> [(имя-поля)] - выходные значения
ограничиваемого поля сравниваются со значениями другого поля
в заданной таблице: если совпадения отсутствуют, команды
INSERT и UPDATE завершаются неудачей; если параметр "поле"
не указан, выполняется проверка по первичному ключу другой
таблицы;
ON DELETE <операция> - эта секция указывает, что при выполнении
команды DELETE для задаваемого поля выполняется одна из следующих
операций - NO ACTION (если удаление приводит к нарушению целостности
ссылок, происходит ошибка),
CASCADE (удаление всех записей, содержащихссылки на
удаляемую запись),
SET NULL (поля, содержащие ссылки на удаляемую запись,
заменяются псевдозначениями NULL),
SET DEFAULT (поля со ссылками на удаляемую запись получают
значения по умолчанию),
ON UPDATE <операция> - аналогична секции ON DELETE.
5.2 Индексы.
Индексом называется объект базы данных, позволяющий значительно повысить
скорость обращения к БД за счет ускоренной обработки команд, содержащих
сравительные критерии. Хранимая в индексе инфрмация о размещении данных
по одному или нескольким полям таблицы повышает эффективность поиска
записей при условной выборке (например, с использованием секции WHERE).
Индексы создаются командой:
CREATE INDEX <имя_индекса> ON <таблица>
[ USING <тип> ] (<имя_поля> [, ... ]);
В списке в скобках перечисляются поля по которым строится индекс. Если полей
больше одного, то индекс строится для всех перечисленных полей. Составные
индексы используются в PostgreSQL только при выполнении команд SQL,
осуществляющих поиск по всем индексированным полям с объединением условий
поиска по AND. Если таких операций не предвидится, то создание составного
индекса по нескольким полям не целесообразно, т.к. требует значительных
затрат ресурсов сервера БД. Необязательная фраза USING определяет способ
упорядочения значений индексируемого поля - В-дерево, R-дерево или
хэшрование. По умолчанию упорядочение производится построением В-деревьев.
Например, создание индекса по полю "Название книги" таблицы "Книги"
выполняется командой
=> CREATE INDEX books_title_idx ON books ( title );
Результат создания можно увидеть по команде \d монитора psql
=>\d books
Table "books"
Attribute | Type | Modifier
-----------+---------+---------
id | integer | not null
title | text | not null
author_id | integer |
subject_id | integer |
Indices: books_id_pkey
books_title_idx
Приведенный пример показывает еще и то, что помимо явно задаваемых
индексов (books_title_idx), индекс может быть создан автоматичекси,
если какое-нибудь поле объявлено первичным ключом (primary key) таблицы.
Таким полем в books является поле id и по нему создан индекс books_id_pkey.
Задания
Выполняя задание, необходимо в рабочей тетради предварительно записать
соответствующие операторы SQL, а также вид создаваемых и модифицируемых
таблиц. Все работы по созданию и модификации таблиц выполнять в БД
gr<номер-вашей-группы>
1. Создать таблицу для хранения данных о студентах с полями: фамилия, номер
зачетки, адрес, год поступления в институт, номер специальности, номер
группы.
Ввести следующие ограничения:
- уникальность для номера зачетки,
- отсутствие пустых значений (NULL) для фамилии и года поступления,
- значение специальности по умолчанию 0220100.
Задать первичный ключ (Primary Key) для таблицы студентов.
2. Заполнить данными созданную таблицу (не менее 10 записей), при вводе
попытаться ввести записи с дублирующимися номерами зачетных книжек,
однофамильцами, без фамилии, без года поступления в институт, без
специальности; зафиксировать в рабочей тетради свои действия и реакцию на
них сервера БД. Привести в тетради окончательный вид созданной таблицы.
3. Создать таблицу для хранения данных о сдаче студентами различных
экзаменов по дисциплинам с оценками и заполнить ее данными (каждый студент
сдает не менее трех экзаменов); обеспечить выполнение условий целостности
данных, чтобы экзамены сдавались только студентами, у которых есть зачетные
книжки, а при удалении записей о студентах удалялись бы и записи о сданных
ими экзаменах (каскадное удаление)
| < Предыдущая |
|---|
