Базы данных. Реляционные базы данных
В данной теме, на примере двух таблиц, определяются основные понятия реляционных баз данных, а именно:
- первичный ключ;
- внешний ключ;
- простой и составной ключ;
- отношение, типы отношений;
- искусственный и естественный ключи;
- главная (master) и подчиненная (detail) таблицы.
Входные данные
Пусть задана база данных работников предприятия, которая состоит из двух таблиц. Первая таблица содержит данные о работнике. Вторая таблица содержит сведения о заработной плате работника.
Таблицы имеют следующую структуру.
«Работник» . Содержит данные о работнике «Зарплата» . Содержит сведения о заработной плате работников.
Вопрос/ответ
1. Что такое первичный ключ в таблице базы данных? Для чего используются первичные ключи?
При работе с таблицами в реляционных базах данных, желательно (необходимо), чтобы каждая таблица имела так называемый первичный ключ .
Первичный ключ – это поле, которое используется для обеспечения уникальности данных в таблице. Это означает, что значение (информация) в поле первичного ключа в каждой строке (записи) таблицы может быть уникальным.
Уникальность необходима во избежание неоднозначности, когда неизвестно к какой записи таблицы можно обратиться, если в таблице есть повторяющиеся записи (две записи имеют одинаковые значения во всех полях таблицы).
Пример. Для таблицы «Работник» можно ввести дополнительное поле, которое будет первичным ключом. Однако, поле (атрибут) «Табельный номер» также обеспечивает уникальность. Так как, теоретически, не может быть двух одинаковых табельных номеров. На практике могут быть случаи, что один и тот же табельный номер будет введен по ошибке и совпадут значения всех полей таблицы. В результате возникнут два одинаковых записи в таблице. Во избежание такой ошибки, лучше создать в таблице дополнительное поле-счетчик, которое обеспечит уникальность.
Также для таблицы «Зарплата» можно ввести дополнительное поле, которое будет первичным ключом.
2. Что такое отношение (связь) между таблицами (relationship)? Пример
Таблицы в реляционной модели данных могут иметь связи между собой. Такие связи называются отношениями. Для таблиц «Работник» и «Зарплата» можно установить связь по полю «Табельный номер».
Пример. Проанализируем таблицы «Работник» и «Зарплата». В этих таблицах можно установить отношение между таблицами на основе поля «Табельный номер». То есть, связь между таблицами происходит на основе поля (атрибуту) «Табельный номер».
Это означает следующее. Если нужно найти начисленную заработную плату в таблице «Зарплата» для работника Иванов И.И., то нужно выполнить следующие действия:
- найти табельный номер работника Иванов И.И. в таблице «Работник». Значение табельного номера равно 7585;
- в таблице «Зарплата» найти все значения, которые равны 7585 (табельный номер);
- выбрать из таблицы «Зарплата» все значения поля «Начислено», которые соответствуют табельному номеру 7585.
Рис. 1. Иллюстрация связи между таблицами. Табельный номер 2145 таблицы «Работник» отображается в таблице «Зарплата»
Рис. 2. Связь (отношение) между полями таблиц
3. Что такое внешний ключ (foreign key)? Пример
Понятие «внешний ключ» есть важным при рассмотрении связанных таблиц.
Внешний ключ – это одно или несколько полей (атрибутов), которые являются первичными в другой таблице и значение которых заменяется значениями первичного ключа другой таблицы.
Пример. Пусть между таблицами «Работник» и «Зарплата» существует взаимосвязь по полю «Табельный номер». В этом случае, поле «Табельный номер» таблицы «Работник» может быть первичным ключом, а поле «Табельный номер» таблицы «Зарплата» внешним ключом. Это означает, что значения поля «Табельный номер» таблицы «Зарплата» заменяются значениями поля «Табельный номер» таблицы «Работник».
4. Что такое рекурсивный внешний ключ?
Рекурсивный внешний ключ – это внешний ключ, который ссылается на одну и ту же таблицу, к которой он принадлежит. В этом случае поле (атрибут), которое соответствует внешнему ключу, есть ключом одного и того же отношения (связи).
5. Могут ли первичный и внешний ключи быть простыми или составными (сложными)?
Первичный, вторичный и внешний ключи могут быть как простыми так и составными (сложными). Простые ключи – это ключи, которые содержат только одно поле (один атрибут). Составные (сложные) ключи – это ключи, которые содержат несколько полей (атрибутов).
6. Какое отличие между искусственным и естественным ключом? Пример
Естественной ключ обеспечивает уникальность из самой сущности предметной области. Бывают случаи, когда значения записей некоторого поля (полей) таблицы есть уникальными. Это поле может быть естественным ключом.
Искусственный ключ вводится дополнительно для обеспечения уникальных значений. Чаще всего искусственный ключ есть полем типа счетчик (counter). В таком поле, при добавлении новой записи (строки) в таблицу, значение счетчика увеличивается на 1 (или другую величину). Если запись удалить из таблицы, то максимальное значение счетчика строк уже не уменьшается, а остается как есть. Как правило, за этим все следит система управления базами данных.
Пример. В таблице «Работник» естественном ключом есть поле (атрибут) «Табельный номер». Поле «Табельный номер» есть само по себе уникальным, так как не может быть двух работников с одинаковым табельным номером.
В таблице «Зарплата» значение во всех четырех полях могут случайно повториться. Поэтому, здесь целесообразно добавить дополнительное поле-счетчик, которое будет искусственным ключом. В этом случае таблица «Зарплата» с дополнительным полем может иметь приблизительно следующий вид:
где поле «Номер» есть искусственным ключом, который обеспечивает уникальность.
7. Какие существуют способы выбора первичного ключа?
Существует 3 способа выбора первичного ключа:
- использовать поле-инкремент (поле-счетчик) как искусственный ключ;
- выбрать из данных одно поле, которое может обеспечить уникальность;
- выбрать из данных несколько полей, которые могут обеспечить уникальность. В этом случае ключ еще будет называться сложным (составным).
8. Что означают термины «главная таблица» (master) и «подчиненная таблица» (detail)?
Если между таблицами есть связь, то одна из них может быть главной (master), а другая подчиненной (detail). Главная таблица отображает все записи, которые помещаются в ней. Подчиненная таблица отображает только те записи, которые соответствуют значению ключа главной таблицы, который на данный момент есть активным (текущим). Если изменяется текущая запись главной таблицы, то изменяется множество доступных записей подчиненной таблицы.
Пример. Если рассмотреть таблицы «Работник» и «Зарплата», то таблица «Работник» есть главной, а таблица «Зарплата» есть подчиненной.
9. Какие существуют типы отношений (связей) между таблицами?
Существует 4 основных типа отношений между таблицами:
- «один к одному» . В этом случае каждой записи одной таблицы соответствует только одна запись другой таблицы;
- «один ко многим» . Это когда одной записи главной таблицы (master) соответствует несколько записей подчиненной таблицы (detail). То есть, каждой записи, которая есть первичным ключом одной таблицы, соответствует несколько записей связанной таблицы;
- «много к одному» . Это когда нескольким записям главной таблицы отвечает одна запись подчиненной таблицы;
- «много ко многим» . Это когда в обоих таблицах существует несколько взаимосвязанных записей.
Пример. Если рассмотреть отношение между таблицами «Работник» и «Зарплата», то это отношения есть типа «один ко многим». Таблица «Работник» есть главной. Таблица «Зарплата» есть подчиненной.
InterBase могут использовать следующие виды ограничений:- PRIMARY KEY - первичный ключ таблицы.
- UNIQUE - уникальный ключ таблицы.
- FOREIGN KEY - внешний ключ , обеспечивает ссылку на другую таблицу и гарантирует ссылочную целостность между родительской и дочерней таблицами .
Примечание о терминологии
Если вы похожи на автора данного курса в том, что любите искать ответы на интересующий вас вопрос комплексно, в разных трудах разных авторов, то вы не могли не заметить некоторую путаницу в определениях главная (master) -> подчиненная (detail) таблицы. Напомним, что главную таблицу часто называют родительской, а подчиненную - дочерней.
Связано это, вероятно, с тем, как интерпретируются эти определения в локальных и SQL -серверных СУБД .
В локальных СУБД главной называется та таблица , которая содержит основные данные, а подчиненной - дополнительные. Возьмем, к примеру, три связанные таблицы. Первая содержит данные о продажах, вторая - о товарах и третья - о покупателях:
Рис.
18.1.
Здесь основные сведения хранятся в таблице продаж, следовательно, она главная (родительская). Дополнительные сведения хранятся в таблицах товаров и покупателей, значит они дочерние. Это и понятно: одна дочь не может иметь двух биологических матерей, зато одна мать вполне способна родить двух дочерей.
Но в SQL -серверах баз данных имеется другое определение связей: когда одно поле в таблице ссылается на поле другой таблицы, оно называется внешним ключом . А поле , на которое оно ссылается, называется родительским или первичным ключом . Таблицу, которая имеет внешний ключ (ссылку на запись другой таблицы) нередко называют дочерней, а таблицу с родительским ключом - родительской. Еще в определении связей говорят, что родитель может иметь только одну уникальную запись , на которую могут ссылаться несколько записей дочерней таблицы .
Так что в приведенном выше примере таблица продаж имеет два внешних ключа: идентификатор товара, и идентификатор покупателя. А обе таблицы в правой части рисунка имеют родительский ключ " Идентификатор ". Поскольку один покупатель или товар могут неоднократно встречаться в таблице продаж, то получается, что обе таблицы в правой части рисунка - родители, а таблица слева - дочерняя. Поскольку сейчас мы изучаем InterBase - SQL сервер БД , этими определениями мы и будем руководствоваться в последующих лекциях. Чтобы далее не ломать голову над этой путаницей, сразу договоримся: дочерняя таблица имеет внешний ключ (FOREIGN KEY ) на другую таблицу.
PRIMARY KEY
PRIMARY KEY - первичный ключ , является одним из основных видов ограничений в базе данных. Первичный ключ предназначен для однозначной идентификации записи в таблице, и должен быть уникальным. Первичные ключи PRIMARY KEY находятся в таблицах, которые принято называть родительскими (Parent ). Не стоит путать первичный ключ с первичными индексами локальных баз данных, первичный ключ является не индексом, а именно ограничением. При создании первичного ключа InterBase автоматически создает для него уникальный индекс . Однако если мы создадим уникальный индекс , это не приведет к созданию ограничения первичного ключа . Таблица может иметь только один первичный ключ PRIMARY KEY .
Предположим, имеется таблица со списком сотрудников. Поле "Фамилия" может содержать одинаковые значения (однофамильцы), поэтому его нельзя использовать в качестве первичного ключа. Редко, но встречаются однофамильцы, которые вдобавок имеют и одинаковые имена. Еще реже, но встречаются полные тезки, поэтому даже все три поля "Фамилия" + "Имя" + "Отчество" не могут гарантировать уникальности записи, и не могут быть первичным ключом. В данном случае выход , как и прежде, в том, чтобы добавить поле - идентификатор , которое содержит порядковый номер данного лица. Такие поля обычно делают автоинкрементными (об организации автоинкрементных полей поговорим на следующих лекциях). Итак,
Первичный ключ - это одно или несколько полей в таблице, сочетание которых уникально для каждой записи .
Если в первичный ключ входит единственный столбец (как чаще всего и бывает), спецификатор PRIMARY KEY ставится при определении столбца :
CREATE TABLE Prim_1(Stolbec1 INT NOT NULL PRIMARY KEY, Stolbec2 VARCHAR(50))
Если первичный ключ строится по нескольким столбцам, то спецификатор ставится после определения всех полей:
CREATE TABLE Prim_2(Stolbec1 INT NOT NULL, Stolbec2 VARCHAR(50) NOT NULL, PRIMARY KEY (Stolbec1, Stolbec2))
Как видно из примеров, первичный ключ обязательно должен иметь ограничение столбца (столбцов) NOT NULL .
UNIQUE
UNIQUE - уникальный ключ . Спецификатор UNIQUE указывает, что все значения данного поля должны быть уникальными, в связи с этим такие поля также не могут содержать значения NULL . Можно сказать, что уникальный ключ UNIQUE является альтернативным вариантом первичного ключа, однако имеются различия. Главное различие в том, что первичный ключ должен быть только один, тогда как уникальных ключей может быть несколько. Кроме того, ограничение UNIQUE не может быть построено по тому же набору столбцов, который был использован для ограничения PRIMARY KEY или другого UNIQUE . Уникальные ключи, как и первичные, находятся в таблицах, которые являются родительскими по отношению к другим таблицам.
Столбец, объявленный с ограничением UNIQUE , как и первичный ключ , может применяться для обеспечения ссылочной целостности между родительской и дочерней таблицами . При этом внешний ключ дочерней таблицы будет ссылаться на это поле (поля). Как и в случае первичного ключа, при создании уникального ключа, для него автоматически будет создан уникальный индекс . Но не наоборот. Пример создания таблицы с одним первичным и двумя уникальными ключами:
CREATE TABLE Prim_3(Stolbec1 INT NOT NULL PRIMARY KEY, Stolbec2 VARCHAR(50) NOT NULL UNIQUE, Stolbec3 FLOAT NOT NULL UNIQUE)
FOREIGN KEY
FOREIGN KEY - внешний ключ . Это очень мощное средство для обеспечения ссылочной целостности между таблицами, которое позволяет не только следить за наличиями правильных ссылок, но и автоматически управлять ими. Внешние ключи содержатся в таблицах, которые являются дочерними (Child ) по отношению к другим таблицам. Ссылочная целостность обеспечивается именно внешним ключом, который ссылается на первичный или
Наличие взаимосвязей, перекрестных ссылок между таблицами - это одно из фундаментальных свойств, отличающих реляционную базу данных от простого набора таблиц. Для реализации таких взаимосвязей почти все СУБД позволяют определять в таблицах первичные и внешние ключи и имеют в своем составе механизмы поддержания ссылочной целостности.
Первичный ключ
Понятия первичного ключа мы уже вскользь касались в статье, посвященной нормализации базы данных. Первичный ключ - это столбец или группа столбцов, однозначно определяющие запись. Первичный ключ по определению уникален: в таблице не может быть двух разных строк с одинаковыми значениями первичного ключа. Столбцы, составляющие первичный ключ, не могут иметь значение NULL. Для каждой таблицы первичный ключ может быть только один.
Уникальный ключ
Уникальный ключ - это столбец или группа столбцов, значения (комбинация значений для группы столбцов) которых не могут повторяться. Отличия уникального ключа от первичного - в том, что:
- уникальных ключей для одной таблицы может быть несколько (вопросик на засыпку для тех, кто прочитал статью про нормализацию: правила какой нормальной формы при этом будут нарушены? ;)
- уникальные ключи могут иметь значения NULL, при этом если имеется несколько строк со значениями уникального ключа NULL, такие строки согласно стандарту SQL 92 считаются различными (уникальными).
Внешний ключ
Внешние ключи - это основной механизм для организации связей между таблицами и поддержания целостности и непротиворечивости информации в базе данных.
Внешний ключ - это столбец или группа столбцов, ссылающиеся на столбец или группу столбцов другой (или этой же) таблицы. Таблица, на которую ссылается внешний ключ, называется родительской таблицей, а столбцы, на которые ссылается внешний ключ - родительским ключом. Родительский ключ должен быть первичным или уникальным ключом, значения же внешнего ключа могут повторяться хоть сколько раз. То есть с помощью внешних ключей поддерживаются связи "один ко многим". Типы данных (а в некоторых СУБД и размерности) соответствующих столбцов внешнего и родительского ключа должны совпадать.
И самое главное. Все значения внешнего ключа должны совпадать с каким-либо из значений родительского ключа. (Заметим в скобках насчет совпадения / несовпадения: нюансы возникают, когда в значениях столбцов вторичного ключа встречается NULL. Давайте пока в эти нюансы вдаваться не будем). Появление значений внешнего ключа, для которых нет соответствующих значений родительского ключа, недопустимо. Вот тут-то мы плавно переходим к понятию ссылочной целостности.
Ссылочная целостность
Первое из правил ссылочной целостности фактически уже изложено в предыдущем абзаце: в таблице не допускается появления (неважно, при добавлении или при модификации) строк, внешний ключ которых не совпадает с каким-либо из имеющихся значений родительского ключа.
Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \"болтающихся в воздухе\" строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:
- CASCADE - обеспечивает автоматическое выполнение в дочерней таблице тех же изменений, которые были сделаны в родительском ключе. Если родительский ключ был изменен - ON UPDATE CASCADE обеспечит точно такие же изменения внешнего ключа в дочерней таблице. Если строка родительской таблицы была удалена, ON DELETE CASCADE обеспечит удаление всех соответствующих строк дочерней таблицы.
- SET NULL - при удалении строки родительской таблицы ON DELETE SET NULL установит значение NULL во всех столбцах вторичного ключа в соответствующих строках дочерней таблицы. При изменении родительского ключа ON UPDATE SET NULL установит значение NULL в соответствующих столбцах соответствующих строк (о как:) дочерней таблицы.
- SET DEFAULT - работает аналогично SET NULL, только записывает в соответствующие ячейки не NULL, а значения, установленные по умолчанию.
- NO ACTION (установлено по умолчанию) - при изменении родительского ключа никаких действий с внешним ключом в дочерней таблице не производится. Но если изменение значений родительского ключа приводит к нарушению ссылочной целосности (т.е. к появлению "висящих в воздухе" строк дочерней таблицы), то СУБД не даст произвести такие изменения родительской таблицы.
Ну а сейчас - от общего к частному.
Ключи и ссылочная целостность в MySQL и Oracle
Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:
- NO ACTION (устанавливается по умолчанию) в более жестком, чем по стандарту SQL 92, варианте: запрещается изменение и удаление строк родительской таблицы, для которых имеются связанные строки в дочерних таблицах.
- ON DELETE CASCADE.
Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.
MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.
Разработчики MySQL обещают реализовать работу с внешними ключами и поддержание ссылочной целостности в версии 5.0. Что ж, когда версия MySQL 5.0 станет стабильной - посмотрим, что там в итоге получится. Очень, очень хотелось бы, чтобы MySQL поддерживала ссылочную целостность (без ущерба для производительности:).
Таблицы
В реляционной базе данных информация организована в виде реляционных таблиц, разделенных на строки и столбцы, на пересечении которых содержатся значения данных .
Таблица – это некоторая регулярная структура, состоящая из конечного набора однотипных записей.
Таблица отражает тип объекта реального мира (сущность). Строки соответствуют экземпляру объекта, конкретному событию или явлению. Столбцы соответствуют атрибутам(признакам, характеристикам, параметрам) объекта, события, явления. У каждой таблицы имеется уникальное имя внутри базы данных, описывающее ее содержимое.
У каждого столбца в таблице есть свое имя, которое обычно служит заголовком столбца. Все столбцы в одной таблице должны иметь уникальные имена, однако разрешается присваивать одинаковые имена столбцам, расположенным в различных таблицах. В реляционной модели данных атрибуты отношений не упорядочены, т. е. обращение к полям всегда происходит по именам, а не по расположению. Однако в языке SQL допускается индексное указание столбцов таблиц, при этом столбцы рассматриваются в порядке слева направо (их порядок определяется при создании таблицы) .
В любой таблице всегда есть как минимум 1 столбец. В стандарте ANSI/ISO не указывается максимально допустимое число столбцов в таблице, однако почти во всех коммерческих СУБД этот предел существует. В СУБД Firebird этот предел составляет 32 767 столбцов.
В РМДреляционной модели данных для обозначения строки отношения используется понятие кортеж. Представлением кортежа на физическом уровне является строка таблицы базы данных. Строки таблицы не имеют имен и определенного порядка. В таблице может содержаться любое количество строк. Вполне допустимо существование таблицы с нулевым количеством строк. Такая таблица называется пустой. Пустая таблица сохраняет структуру, определенную ее столбцами, просто в ней не содержатся данные. Как правило, не накладывается ограничений на количество строк в таблице, и во многих СУБД размер таблиц ограничен лишь свободным дисковым пространством компьютера. В других СУБД имеется максимальный предел, однако он весьма высок – около двух миллиардов строк, а иногда и больше.
Проиллюстрируем более наглядно структуру одной из таблиц учебной базы данных (см. приложение А). На рис. 1.1 приведена структура таблицы Abonent, содержащей сведения об абонентах компаний жилищно-коммунального хозяйства.
Рис. 1.1. Структурареляционной таблицы Abonent
Каждая горизонтальная строка этой таблицы представляет отдельную физическую сущность – одного абонента. Двенадцать строк таблицы вместе представляют всех абонентов. Все данные, содержащиеся в конкретной строке таблицы, представляют собой набор значений атрибутов конкретного абонента, который описывается этой строкой.
Каждый вертикальный столбец таблицы представляет совокупность значений конкретного атрибута объекта. Например, в столбце AccountCD содержатся уникальные номера лицевых счетов абонентов. В столбце Phone содержатся номера телефонов абонентов.
На пересечении каждой строки с каждым столбцом таблицы содержится в точности одно значение данных. Например, в строке, представляющей абонента Конюхова В. С., в столбце Fio содержится значение "Конюхов В.С.". В столбце AccountCD той же строки содержится значение "015527", которое является номером лицевого счета абонента с ФИО Конюхов В. С.
Все значения, содержащиеся в одном и том же столбце, являются данными одного типа. Например, в столбце Fio содержатся только слова, а в столбце StreetCD содержатся целые числа, представляющие идентификаторы улиц. В реляционной модели данных общая совокупность значений, из которой берутся действительные значения для определенных атрибутов (столбцов), называется доменом . Доменом столбца Fio, например, является множество фамилий, имен и отчеств (ФИО) абонентов. Каждый столбец всегда определяется на одном домене.
В реляционных базах данных домен определяется путем задания как минимум некоторого базового типа данных, к которому относятся элементы домена, а часто также и произвольного логического выражения, применяемого к элементам этого типа данных (ограничения домена).
В учебной базе данных определены следующие домены:
§ Boollean (Логический): SMALLINT . Поля, определяемые на этом домене, могут принимать только целочисленные значения, равные 0 или 1. Это достигается наложением в домене условия проверки (CHECK) на принимаемые этим доменом значения.
§ Money (Деньги): NUMERIC(15,2) . Домен предназначен для определения в таблицах полей, хранящих денежные суммы.
§ PKField (Поле ПК): INTEGER . Домен предназначен для определения первичных и внешних ключей таблиц. Ограничение обязательности данных (NOT NULL) на этот домен не наложено. Оно накладывается при объявлении первичного ключа таблицы. Это сделано для того, чтобы можно было определить внешний ключ на этом домене без условия NOT NULL.
§ TMonth (Месяц): SMALLINT . Домен предназначен для определения в таблицах полей, содержащих номера месяцев. Целочисленные значения в таком поле могут находиться в диапазоне 1...12.
§ TYear (Год): SMALLINT . Домен предназначен для определения полей, содержащих номер года. Целочисленные значения могут находиться в диапазоне 1990...2100.
Поскольку строки в реляционной таблице не упорядочены, то нельзя выбрать строку по ее номеру в таблице. В таблице нет «первой», «последней» или «тринадцатой» строки. Тогда каким же образом можно указать в таблице конкретную строку, например строку для абонента с ФИО Аксенов С.А.?
Ключевым элементом данных называется такой элемент, по которому можно определить значения других элементов данных.
В реляционной базе данных в каждой таблице есть 1 или несколько столбцов, значения в которых во всех строках разные. Этот столбец (столбцы) называется первичным ключом таблицы.
Первичный ключ – это атрибут или группа атрибутов, которые единственным образом идентифицируют каждую строку в таблице.
Вернемся к рассмотрению таблицы Abonent учебной базы данных (рис. 1.1). На первый взгляд, первичным ключом таблицы Abonent могут служить и столбец AccountCD, и столбец Fio. Однако в случае если будут зарегистрированы 2 абонента с одинаковыми ФИО, то столбец Fio больше не сможет исполнять роль первичного ключа. На практике в качестве первичных ключей таблиц обычно следует выбирать идентификаторы, такие как уникальный номер лицевого счета абонента (AccountCD в таблице Abonent), идентификатор улицы (StreetCD в таблице Street) и т. д.
Если в таблице нет полей, значения в которых уникальны, для создания первичного ключа в нее обычно вводят дополнительное поле, значениями которого СУБД может распоряжаться по своему усмотрению.
Если первичный ключ представляет собой комбинацию столбцов, то такой первичный ключ называется составным .
Вторичные ключи устанавливаются по полям, которые часто используются при поиске или сортировке данных. В отличие от первичных ключей поля для вторичных ключей могут содержать не уникальные значения.
Ключ – значение (элемент данных) используемое для идентификации или определении адреса записи.
Первичный ключ – это столбец или некоторое подмножество столбцов, которые уникально, т.е. единственным образом определяют строки. Первичный ключ, который вкл в себя более одного столбца называется множественным, или комбинированными, или составным. Правило целостности объектов утверждает, что первичный ключ не может быть полностью или частично пустым, т.е. иметь значении null.
Остальные ключи, которые можно использовать в качестве первичных называются потенциальными или альтернативными ключами .
Внешний ключ – это столбец или подмножество одной табл., который может служить в качестве первичного ключа для другой табл. Внешний ключ табл. является ссылкой на первичный ключ др. табл. правило ссылочной целостности гласит, что внешней ключ может быть либо пустым, либо соответствовать значению первичного ключа, на который он ссылается. Внеш. ключ является неотъемлемой частью реляционной модели, поскольку реализует связи между табл. БД.
16. Реляционный подход к построенной модели. Свойства и требования к реляционной модели. Реляционная модель является удобной и наиболее привычной формы представления данных в виде таблицы. Одним из основных преимуществ реляционной модели является ее однородность. Все данные рассматриваются как хранимые в таблицах в которых каждая строка имеет один и тот же формат. Каждая строка в табл. представляет некоторый объект реального мира или соотношение между объектами.
Основные понятия, с помощью которых определяется реляционная модель является следующее: домен, отношение, кортеж, кардинальность, атрибуты, степень, первичный ключ.
Домен – это совокупность значений, из которой берутся значения соответствующих атрибутов определенного отношения. С т.з. программирования, домен – это тип данных определяемый системой или пользователем.
Первичный ключ – это столбец или некоторое подмножество столбцов, которые уникально, т.е. единственным образом определяют строки.
Модель предъявляет к табл. следующие требования:
1. данные в ячейках табл. должны быть структурно неделимыми.
2. данные в одном столбце должны быть одного типа.
3. каждый столбец должен быть уникальным (недопустимо дублирование столбцов).
4. столбцы размещаются в произвольном порядке.
5. строки размещаются в табл. так же в произвольном порядке.
6. столбцы имеют уникальное наименование.
В целом концепция реляционной модели определяется следующими 12 правилами Кодда:
1. Правило информации (вся информация должна быть представлена исключительно на логическом уровне и только одним способом – в виде значений, содержащихся в табл.).
2. Правило гарантированного доступа (указывает на роль первичных ключей при поиске информации в БД).
3. Правело поддержки недействительных значений (требует, что бы отсутствующие данные можно было представить с помощью недействительных значений null).
4. Правило динамического каталога, основанного на реляционной модели (гласит, что реляционная БД должна содержать набор системных табл. описывающих структуру самой БД).
5. Правило исчерпывающего подъязыка данных (требует, чтобы СУБД использовала язык реляционной БД, например SQL).
6. Правило обновления представлений (касается представлений, которые являются виртуальными таблицами).
7. Правило добавления, обновления и удаления (БД ориентированны на множества).
8. Правело независимости физических данных.
9. Правило независимости логических данных (правила 8-9 означают отдельные пользователя и прикладной программы от низкоуровневой реализации БД).
10. Правило независимости условий целостности (гласит, что язык БД должен поддерживать ограничительные условия).
11. Правило независимости распространения (гласит, что язык БД должен обеспечивать возможность работы с распределенными данными).
12. Правило единственности (предотвращает использование других возможностей для работы с БД).
17.Взаимосвязь в моделях СУБД и их типы. СУБД – средство с помощью которого создаётся БД и осуществляется манипулирование данными в ней (удаление, редактирование данных).связь 1 к 1 означает что одной записи из родительской таблицы соответствует только 1 запись из дочерней табл. Связь 1 ко многим означает что одной записи из одной табл соответствует несколько записей из дочерней табл. Многие ко многим означает что нескольким записям из родительской таблицы соответствует несколько записей из дочерней табл. Многие ко 1 означает что нескольким записям дочерней табл. Соответствуеттолько 1 запись из родительской табл. Функции СУБД: 1)управление данными непосредственно в БД. 2) управление данными непосредственно в памяти компа. 3)управление транзакциями- функция СУБД которая производит ряд операций над БД как единое целое. Транзакция – последовательность операций происходящих из 1 противоречивого состояния в другое.
18. Нормализация отношений в реляционной БД. Формы нормализации. Нормализация - это разбиение таблицы на две или более, обладающих лучшими свойствами при включении, изменении и удалении данных. Цель нормализации сводится к получению такого проекта базы данных, в котором каждый факт появляется лишь в одном месте, т.е. исключена избыточность информации.
Каждая таблица в реляционной БД удовлетворяет условию, в соответствии с которым в позиции на пересечении каждой строки и столбца таблицы всегда находится единственное атомарное значение (т. е в одной ячейке находится одно значение: в текстовом - текст, в числовом только одно число, а не группа чисел перечисленных через запятую), и никогда не может быть множества таких значений. Любая таблица, удовлетворяющая этому условию, называется нормализованной. Фактически, ненормализованные таблицы, т.е. таблицы, содержащие повторяющиеся группы, даже не допускаются в реляционной БД. Всякая нормализованная таблица автоматически считается таблицей в первой нормальной форме, сокращенно 1НФ.
1. Таблица находится в 1НФ, если все данные, содержащееся в таблице атомарны (неделимы).
2. Таблица находится во 2НФ, если она находится в 1НФ и каждый неключевой столбец полностью зависит от первичного ключа.
3. Таблица находится в 3НФ, если она соответствует 2НФ и все неключевые столбцы взаимнонезависимы, т.е. нельзя изменить один из них не изменяя другой.
Существуют также другие нормальные формы, однако обычно нормализацию заканчивают на 3НФ.
19. Этапы проектирования БД – 2 подхода. БД – Проектирование БД – это упорядоченный формализованный процесс создания системы взаимосвязанных описаний, т.е. таких моделей придметной обл., которые связывают хранимые в БД с объектами предметной области, описываемыми этими данными.
Проектирование начинается с анализа придметной обл. и выявление функциональных и других к проектируемой системе. Проектирование обычно выполняется человеком (группой людей) – системным аналитиком .
Основная цель БД – поиск нужной инф-ии в нужный момент времени среди всего информационного пространства БД. Два подхода: «Сущность-связь» и ER диаграмма.
20. Целостность данных и методы доступа. При работе БД должна обеспечиваться целостность данных. Под целостностью данных понимают обеспечения целостности связей между записями в таблицах при удалении записей из первичных таблиц. То есть, при удалении записей из первичных таблиц автоматически должны удаляться связанные с ними записи из вторичных таблиц.
В случае несоблюдения целостности данных со временем в БД накопится большое количество записей во вторичных таблицах связанных с несуществующими записями в первичных таблицах, что приведёт к сбоям в работе БД и её засорению неиспользуемыми данными.
Существует два общих правила целостности БД:
Целостность объектов – требует, чтобы первичные ключи не содержали неопределённых (пустых значений)
Ссылочная целостность – требует, чтобы внешние ключи не содержали несогласованных с родительскими ключами значений.
21.Понятие системы БД и ее структура. Преимущества системы БД. Система - совокупность взаимосвязанных элементов составляющих единое целое. Основные обьекты структуры БД
В качестве операндов в выражении могут быть константы переменные, величины, имена полей, ф-ции, подзапросы.
Наивысший приоритет – унарный знак числа(полож или отриц)
В качестве операторов:
1 Унарные операторы – опред знака числа
2 Операторы присваивания
3 Ариф опер
4 Строковые опер
5 Операторы сравнения
6 Логические операторы
7 Битовые операторы
БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Tables Таблица В ней хранятся данные.
Views Просмотры Это вирт табл, для отображения данных из реал табл.
Stored Procedures Хранимые процедуры Это послед команд SQL
Triggers Триггеры Особый вид хранимой процедуры.
Index Индекс Допол струк позволяющая сортировать данные.
User Defined Function Создаваемые польз ф-ции
Keys Ключ Один из видов ограничения целостности данных
User Defined Data Types Типы данных опред польз
Constrains Ограничение целостности Обьекты для обеспечения логической целостности
Users Пользователи которым определены права доступа к бд
Roles Роли позволяющие обьединять польз в группы
Rules Правила позволяющие контролировать логическую целостность данных
Defaults По умолчанию Стандартные установки бд
22. Архитектура многопользовательских СУБД и их виды. Характеристика каждой архитектуры (Суть, т.е. принцип работы, достоинства и недостатки). СУБД – средство с помощью которого создаётся БД и осуществляется манипулирование данными в ней (удаление, редактирование данных). Приложения БД обычно принято относить к одной из программных архитектур имеющих свои плюсы и минусы. Различают несколько видов архитектур: локальная архитектура (при такой архитектуре и программа (субд) и БД расположены на одном компьютере. Так работает большинство настольных приложений. Д: минимальные затраты, быстрый доступ к БД, высокий уровень безопасности. Н: ограниченное кол-во пользователей, высокие требования к характеристикам ПК.);
файл-серверная архитектура (в ней средства организации и управления БД целиком располагаются на машине клиента, а БД на машине сервере. Д: возможность обслуживания запросов нескольких клиентов. Н: высокая загрузка сети и машины клиентов, низкий уровень защиты данных, низкий уровень управления целостностью и непротиворечивостью информации); архитектура клиент-сервер (взаимодействие между клиентом и сервером происходит на уровне команд языка манипулирования данными, которые обрабатываются на сервере. Сервер БД осуществляет поиск записей и анализирует их. Записи удовлетворяющие условиям накапливаются на сервере и после того как запрос будет обработан, пользователю на клиентскую машину передаются все логические записи. Д: возможность обслуживания запросов нескольких клиентов, снижение нагрузки на сеть, защита данных, сервер реализует управление транзакциями. Н: бизнес логика функциональной обработки и представление данных могут быть одинаковыми у нескольких клиентов. И это увеличивает потребности в ресурсах, низкий уровень управления непротиворечивостью информации); архитектура активный сервер БД; архитектура сервер приложений БД; архитектура сервера БД; архитектура 1 к 1;многопотоковая односерверная архитектура; мультисерверная; серверная архитектура с параллельной обработкой запросов; Интернет архитектура (доступ к БД и СУБД осуществляется через буфер по стандартному протоколу TCP/IP. При этом БД и СУБД могут располагаться как на одном ПК так и на сети. Благодаря стандартизации всех протоколов и интерфейсов взаимодействия в Интернет такие системы легко создавать и внедрять. В этом случае не требуется разрабатывать специальные клиентские приложения или придумывать собственные спецификации обмена данными между сервером и клиентскими местами)
23.Организация БД. Структура документальной БД (физический уровень) и краткая характеристика. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. физический уровень- данные расположенные в файлах или в страничных структурах расположенных на внешних носителях. Файл БД. Эта архитектура позволяет обеспечить логическую и физическую независимость при работе с данными. Логическая независимость- предполагает возможность изменения одного приложения, без корректировки других приложений работающих с этой БД.
Физическая независимость- предполагает возможность переноса хранимой информации с одних носителей на другие при сохранении работоспособности всех приложений
работающих с БД.
24. Стандарт реализации языка SQL и его преимущества. С использованием любых стандартов связанно не только многочисленные, но и в полнее очевидные преимущества. Стандарты ограничивают гибкость и функциональные возможности конкретной реализации. Под реализацией языка SQL понимается программный продукт SQL соответствующего производства. Каждая реализация языка SQL содержит усовершенствование отвечающие требованиям того или иного сервера БД. Эти усовершенствования языка SQL представляют собой дополнительные команды являющиеся добавление к стандартному пакету и доступные только в конкретной реализации.
Преимущества языка SQL.
К преимуществу стандарта SQL может отнести:
1. Появление совместимости стандарта в различных реализациях;
2. Существует повышенная переносимость программного обеспечения и БД в целом при соблюдении универсальности работы администраторов БД.
Язык SQL является основой многих СУБД и к основным достоинствам языка можно отнести:
1. Стандартность;
2. Независимость от конкретных СУБД;
3. Возможность переноса с одной вычислительной системы на другую;
4. Реляционная основа языка;
5. Возможность создания интерактивных запросов;
6. Возможность программного доступа к БД;
7. Обеспечение различного представления данных;
8. Возможность динамического изменения и расширения БД;
9. Поддержка архитектуры клиент-сервер.
25. Правила записи SQL-операторов и их структура (зарезервированные слова, идентификаторы, алфавит, ограничения). Операторы языка SQL состоят из зарезервированных слов и слов пользователя. Зарезервированные слова являются постоянной частью языка SQL и имеют фиксированное значение. Их нельзя разбивать для переноса с одной строки на другую и следует записывать в соответствии с установленным синтаксисом.
Слова, определенные пользователем задаются самим пользователем в соответствии с синтаксическим правилом и представляют собой идентификаторы, т.е. имена различных объектов БД. Слова в операторе языка расставляются так же в соответствии с установленными синтаксическими правилами. Идентификаторы языка предназначены для обозначения объектов БД и являются именами таблиц представлений полей и др. представлений в БД.
Слова в операторе размещаются так же в соответствии с установленными синтаксическими правилами. На формат идентификатора накладываются следующие ограничения:
1. Идентификатор не может иметь длину более 128 слов. 2. Идентификатор всегда должен начинаться с буквы. 3. Идентификатор не может содержать пробелы. Прописные буквы и используются для записи резервных слов. Строчные, употребляются для записи слов определенных пользователями. ::= равно по определению
׀ возможность выбора из нескольких (или). <…> описание с помощью метаязыка структуры языка.
{…} обязательный набор некоторого списка для конструкции.
[…] необязательный набор некоторого списка для конструкции
[,n…] необязательная возможность повторения конструкции до нескольких раз.
26. Основные объекты структуры БД SQL-сервера (таблицы, представления, хранимые процедуры, триггеры). Логическая структура определяет структуру таблиц, взаимоотношений между ними, список пользователей, хранимые процедуры, правила, умолчание, и др. объекты БД.
Таблицы (в них раниться собственно данные, значения).
Представляет собой совокупность, каких либо сведение об объектах, явлениях и процессах реального мира. Ни какие другие данные не могут храниться в таблицах. Обращение к данным в табл. могут осуществлять конкретные объекты.
Структура табл. в SQL: строки , где каждая строка (запись) представляет собой совокупность свойств (атрибутов) конкретного экземпляра объекта; столбцы – представляют собой совокупность атрибутов или атрибут, и имеет имя, тип и размер.
Представления (промотор).
Представлениями – называются вирт-ые табл-цы содержимое, которых определяется запросом. Подробный просмотр представляет собой подобно реальным таблицам набор именованных столбцов и строк с данными. Для конечного пользователя представления выглядит как табл., но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких табл-ах. Эта информация в виде представление не сохраняется в БД как отдельный объект.
Хранимые процедуры .- представляют собой группу команд SQL объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое.
Триггерами называют – специальный класс хранимых процедур, автоматически запускаемых при добавлении, удалении или изменении данных из табл.
27. Основные объекты структуры БД SQL-сервера (функции, индексы, пользовательские типы данных, ограничения целостности, правила, умолчания). Логические данные в SQL сервер организованы в виде объектов: таблицы БД, в которых располагаются данные; просмотры служат для отображения данных из таблицы; хранимые процедуры; триггеры -специальные хранимые процедуры, вызываемые при изменении данных в таблице; создаваемые пользователем функции дополнительные структуры призванные повысить производительность работы с данными (индексы); определяемые пользователем типы данных; ключи; ограничения целостности; пользователи обладающие доступом к БД; роли позволяющие объединять пользователей в группы; правила БД контролируют логическую целостность данных; умолчания или стандартные установки БД. Функции в ЯП это конструкции содержащие часто используемый код. Функция выполняет какие-либо действия над данными и возвращает некоторые значения в результате. Индекс это структура, связанная с таблицей или представлением и предназначенная для ускорения поиска информации в них, содержит отсортированные значения исходной таблицы или представления. Пользовательские типы данных это типы данных, которые создает пользователь на основе системных типов данных, когда в нескольких таблицах необходимо хранить однотипные значения, причем нужно характеризовать одинаковый размер столбцов в соответствующих таблицах тип данных и чувствительность к значению NULL. Ограничение целостности это механизм, который обеспечивает автоматический контроль соответствия данных установленным условиям или ограничениям. К нему относят: ограничение на значение NULL; внешнего ключа и т.д. Правила пользуются для ограничения значений хранимых в столбце таблицы или в пользовательском типе данных. Они существуют как самостоятельные объекты БД, которые связываются с полями таблиц и пользовательскими типами данных. Умолчания это самостоятельный объект БД, представляющий значение которое будет присвоено элементу таблицы при вставке строки, если в команде вставки явно не указано значение для этого столбца.
28. Основные объекты структуры БД SQL-сервера. Выражения и их состав. Применение и правила их объявления. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Выражение – это последовательность констант, имен поле (перемен. величин), имен функций, связанных между собой значениями арифметических действий. Выр-ие представляет собой комбинацию идентификаторов функций знаков логических и арифметических определений, констант и др. объектов. Выр-ие может быть использовано в качестве аргумента в командах хранимых процедур или запросах. Выр-ия состоят из : операторов и операндов. В качестве операндов могут быть: константы, переменные, имена полей, функции подзапроса. Операторы – это знаки операций над одним или несколькими выражениями для создания нового выражения. Среди операторов можно выделить: - унарные операторы- операторы присваивания- арифметические операторы- строковые операторы- операторы сравнения - логические операторы- битовые операторы.
Прежде чем использовать какую либо переменную ее необходимо объявить . Объявление переменных осуществляется через команду вида:
DECLARE{имя_переменной тип}[,…n]. Значение переменной может быть присвоено по средствам команд SET & SELECT. С помощью команды SELECT переменной можно присвоить не только конкретное значение но и результат вычисления выр-я. Команда SET имеет формат: SET@имя переменной::==<значение>.
Команда SELECT имеет формат:
SELECT @ имя_переменной::=<выр-е> from<имя_объекта.>
Пример : DECLARE col integer; SET@ a::= sum(кол-во) from товар.
29. Управляющие конструкции SQL. Синтаксис условного оператора и операторов циклов. Язык SQL является непроцедурным, но тем не менее в среде SQL Server предусмотрен ряд различных управляющих конструкций, без которых невозможно написание эффективных алгоритмов.
Группировка двух и более команд в единый блок осуществляется с использованием ключевых слов BEGIN и END:
<блок_операторов>::=
BEGIN { sql_оператор | блок_операторов } END
Сгруппированные команды воспринимаются интерпретатором SQL как одна команда. Блоки BEGIN...END могут быть вложенными. Некоторые команды SQL не должны выполняться вместе с другими командами (изменения структуры таблиц, хранимых процедур и им подобных), поэтому их совместное включение в конструкцию BEGIN...END не допускается. Нередко определенная часть программы должна выполняться только при реализации некоторого логического условия. Синтаксис условного оператора показан ниже:
<условный_оператор>::= IF лог_выражение { sql_оператор | блок_операторов } [ ELSE {sql_оператор | блок_операторов } ]
Циклы организуются с помощью следующей конструкции:
<оператор_цикла>::= WHILE лог_выражение { sql_оператор | блок_операторов } [ BREAK ] { sql_оператор | блок_операторов } [ CONTINUE ]
Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE
42.Логические операции при формировании операторов поиска нужных записей в БД. БД –
это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. При создании или изменении условий поиска допускается использование стандартных логических операторов: NOT - Логическое отрицание условия, AND - Должны выполняться оба условия, OR - Должно выполняться хотя бы одно из условий. Можно задать несколько условий отбора, соединенных логическим оператором или
(or
), для некоторого поля одним из двух способов: можно ввести все условия в одну ячейку строки Условие отбора,
соединив их логическим операторомили
(or
) или ввести второе условие в отдельную ячейку строки или.
Чтобы объединить несколько условий отбора оператором и (and)
, следует привести их в одной строке. Операторы и
и или
применяются как отдельно, так и в комбинации. Приоритет логических операторов:
1. Оператор отрицания логического выражения - NOT (высший приоритет).
2. Оператор AND.
3. Оператор OR (низший приоритет).
Также при формировании поиска можно использовать оператор Between позволяет задать диапазон значений. Оператор I n позволяет задавать используемый для сравнения список значений. Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны: * - обозначает любое количество символов; ? - любой одиночный символ; # - указывает что в данной позиции должна быть цифра
30. Типы данных языка SQL. Определенные стандартам и правила их объявления (символьные, битовые, точные данные). Данные – это совокупная информация, хранимая в БД в виде одного или нескольких различных типов. С помощью типов данных устанавливаются основные правила для данных содержащихся в конкретном столбце таблицы. В языке SQL стандартом приведено 6 скалярных типа данных: символьные - char , битовые - bit , точные числа - integer , округленные - real , интервальные - interval , дата/время – date , time .
Символьные данные.
Символьные данные состоят из последовательности символов входящие в определенный создателями СУБД набор символов. Чаще всего используют наборы символов ascii и ebcdic. Для определения данных символьного типа используют конструкцию: <символьный тип>::={character[длина] ׀ ׀ [длина]}.
При определении столбца с символьным типом данных параметр длина применяется для указания max кол-ва символов которые могут быть помещены в данный столбец (поле) по умолчанию применяется 1. Символьная строка может быть определена как имеющая фиксированную, так и переменную длину (varying). Если строка определена с фиксированной длиной значений, то при вводе в нее меньшего значения кол-ва символов значение дополниться до указанной длины пробелами добавляемые справа. Если строка с переменной длиной значений то при вводе в нее меньшего кол-ва символов в БД будет сохранены только введенные символы, что позволит достичь экономии внешний памяти.
Битовые данные.
Битовые типы данных используются для определения битовых строк, т.е. последовательность двоичных чисел каждое из которых имеет значение либо 0, либо 1.
Для определения данных битового типа используют конструкцию:
<битовый тип> ::= Bit [данные].
Точные данные.
Тип точных числовых данных применяется для определения чисел, которые имеют точное представления. Данные точного числового типа определяются точностью и длиной дробей части. Точность задает общее кол-во знаков как для целой и дробной части Масштаб указывает кол-во дробных десятичных разрядов числа после запятой.
31. Типы данных языка SQL. Определенные стандартам и правила их объявления (округленные числа, дата и время). Данные – это совокупная информация, хранимая в БД в виде одного или нескольких различных типов. С помощью типов данных устанавливаются основные правила для данных содержащихся в конкретном столбце таблицы. В языке SQL стандартом приведено 6 скалярных типа данных: символьные - char , битовые - bit , точные числа - integer , округленные - real , интервальные - interval , дата/время – date , time .
Округленные данные.
Округленные числа или числа с плавающей запятой представляются т.о. что записываются с помощью мантиссой умноженной на порядок 10ти. Вещественный тип:
<вещественный тип>::= {float [точность] real ׀ double precision}. Параметр точность задает кол-во значащих чисел мантиссы.
Тип дата/время данных.
Тип д/в используется для определения момента времени с некоторой установленной точностью. Стандарт SQL поддерживает формат:
<тип_дата/время> ::= {DATA ׀ TIME [точность] ׀ timestamp [точность] }.
Тип DATA – использует для хранения календарных дат включающих поля (year - год, month - месяц, day - день).
Тип данных TIME – использует поля: hour – часы, minute – минуты, second – секунды; и используется для хранения отметок времени.
Тип timestamp – используется для совместного хранения и даты и времени. Параметр точность задается кол-во дробных десятичных знаков. Наличие ключевого слова with time zone определяет часы и мин. сдвигом зонального времени по отношению универсального координатного времени.
32. Понятие домена и правила объявления. Получение информации о типах данных и преобразование типов. Домен – это набор допустимых значений для одного или нескольких атрибутов. Если в таблице базы данных или в нескольких таблицах присутствуют столбцы, обладающие одними и теми же характеристиками, можно описать тип такого столбца и его поведение через домен, а затем поставить в соответствие каждому из одинаковых столбцов имя домена. Домен определяет все потенциальные значения, которые могут быть присвоены атрибуту. Стандарт SQL позволяет определить домен с помощью следующего оператора:
<определение_домена>::= CREATE DOMAIN имя_домена тип_данных [ DEFAULT значение] [ CHECK (допустимые_значения)]
Каждому создаваемому домену присваивается имя, тип данных, значение по умолчанию и набор допустимых значений. Удаление доменов из базы данных выполняется с помощью оператора:
DROP DOMAIN имя_домена
В случае указания ключевого слова CASCADE любые столбцы таблиц, созданные с использованием удаляемого домена, будут автоматически изменены и описаны как содержащие данные того типа, который был указан в определении удаляемого домена.
Получить список всех типов данных можно из системной таблицы systypes: SELECT * FROM systypes
Нередко требуется конвертировать значения одного типа в значения другого. Наиболее часто выполняется конвертирование чисел в символьные данные и наоборот, для этого используется специализированная функция STR. Для выполнения других преобразований SQL Server предлагает универсальные функции CONVERT и CAST, с помощью которых значения одного типа преобразовываются в значения другого типа, если такие изменения вообще возможны. CONVERT и CAST примерно одинаковы и могут быть взаимозаменяемыми.
33.Встроенные функции языка SQL, их классификация. Язык SQL имеет множество встроенных функций, обеспечивающих дополнительные функциональные возможности. Встроенные функции в SQL можно разделить на следующие группы:
1.Математические функции (sin, cos, exp, pi, power (возведение в степень), radians, round, sqrt и т.д.);
2. Строковые функции (Left - возвращает указанное число символов с начала строки, len - возвращает длину строки, lower - переводит все символы строки в нижний регистр, right - возвращает указанное число символов с конца строки, str - выполняет конвертирование значения числового типа в символьный формат,substring - возвращает для строки подстроку указанной длины с заданного символа, upper - переводит все символы строки в верхний регистр);
3.Функции для работы с датой и временем (datediff - возвращает разницу между указанными частями двух дат, day(month,year) - возвращает число (месяц, год) из указанной даты, getday - возвращает текущее системное время);
4.Функции конфигурирования;
5.Функции системы безопасности;
6.Функции управления метаданными;
7.Статистические функции (используются для получения результирующих итогов, в качестве аргумента используют столбец, а результатом является одно значение, зависящее от типа функции: AVG – среднее значение, SUM, Min, Max, count – количество)
36. Встроенные функции над величинами типа дата языка SQL. Язык SQL имеет множество встроенных функций, обеспечивающих дополнительные функциональные возможности. К функциям типа дата относятся:
GETDATE () - возвращает текущую системную дату;
ISDATE (строка) - проверяет строку на соответствие одному из форматов даты и времени;
DAY (дата) - возвращает число указанной даты;
MONTH (дата) - Возвращает месяц указанной даты;
YEAR (дата) - возвращает год указанной даты;
DATEADD (тип, число, дата) - прибавляет к дате указанное число единиц заданного типа (год, месяц, день, час и т. п.).
37Способы создания БД и операторы языка SQL, с помощью которых можно создать БД. БД –
это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Пользователи БД работают с уже готовыми БД, структура к-рых разраб.администратором бд. Создать бд можно используя операторы DDL к-рые позволяют пользователям не вникать в детали хранения БД, пользов.при этом оперирует понятиями таблица и поле, одновременно имея возможность: манипулирования физ.памятью; с помощью этих операторов можно создать БД; определить структуру новой таблицы; удалить сущ.таблицу; изменить определение сущ.таблицы; определить представление данных; обеспечить условия безопасности БД; создать индексы для доступа к таблицам. Собственно DDL базируется на 3 командах SQL: CREATE-создать, позволяющий определить и создать объект БД; DROP-удалить, применяемый для удаления сущ.объекта БД; ALTER-изменить, с помощью можно изменять определения объекта БД. Использование этих команд Вов время работы позволяет сделать динамической структуру РБД. В СУБД можно одновременно создавать, удалять или изменять таблицы. Поэтому в течении времени бд могут расти. Эти операторы можно использовать как в интерактивном так и в программном режиме SQL. Создание базы данных в системе SQL-сервер осуществляется командой CREATE DATABASE:СREATE DATABASE <имя_базы_данных> ON
38Команда языка SQL создания таблицы (синтаксис, назначение параметров). Пример. Оператор CREATE TABLE – используется для создания новой таблицы.
CREATE TABLE имя_таблицы(
имя_столбца тип_данных ,…
PRIMARY KEY(имя_столбца,…),
FOREIGN KEY имя_ограничения (имя_столбца,…)
REFERENCE имя_таблицы
UNIQUE(имя_столбца,…),
Предложение NOT NULL предотвращает занесение в столбец пустых значений. PRIMARY KEY и FOREIGN KEY – столбец или столбцы первичного и внешнего ключей (в определениях столбцов первичного ключа должно быть указано, что они не могут содержать значения NULL) . UNIQUE – условие уникальности данных.
39 Команда языка SQL изменение структуры таблицы (синтаксис, назначение параметров). Пример. В процессе работы с таблицей иногда возникает необходимость добавить в таблицу некоторую информацию. Для изменения структуры таблицы используется оператор ALTER TABLE:
Например, необходимо изменить таблицу студенты, удалив принимаемое по умолчанию значение 333 столбца группа и изменить принимаемое по умолчанию значение столбца пол на м. изменить таблицу добавив новое поле специальность:
Alter table студенты
Alter группа drop default;
Alter table студенты
Alter пол set default ‘м’;
Alter table студенты
Add специальность varchar(14);
40.Понятие запроса,виды запросов и команда языка SQL , формирующая запросы (синтаксис, назначение параметров). Пример. Оператор SELECT – используется для построения запросов в SQL.
SELECT
{* | [имя_столбца ]} [,...n] FROM имя_таблицы [ псевдоним] [,...n]
Оператор SELECT определяет поля (столбцы), которые будут входить в результат выполнения запроса.
В списке они разделяются запятыми и приводятся в такой очередности, в какой должны быть представлены в результате запроса. Если используется имя поля, содержащее пробелы или разделители, его следует заключить в квадратные скобки.
FROM – определяются имена используемых таблиц; WHERE – выполняется фильтрация строк объекта в соответствии с заданными условиями; GROUP BY – образуются группы строк, имеющих одно и то же значение в указанном столбце; HAVING – фильтруются группы строк объекта в соответствии с указанным условием; SELECT – устанавливается, какие столбцы должны присутствовать в выходных данных; ORDER BY – определяется упорядоченность результатов выполнения операторов. Предикат ALL задает включение в выходной набор всех дубликатов, а Предикат DISTINCT следует применять в тех случаях, когда требуется отбросить блоки данных, содержащие дублирующие записи в выбранных полях.
Оператор BETWEEN используется для поиска значения внутри некоторого интервала, определяемого своими минимальным и максимальным значениями. Оператор IN используется для сравнения некоторого значения со списком заданных значений, при этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. С помощью оператора LIKE можно выполнять сравнение выражения с заданным шаблоном, в котором допускается использование символов-заменителей.(% –любое кол-во произвольных символов; _ - один символ строки; – один из возможных символов, указанный в этих ограничителях). По умолчанию реализуется сортировка по возрастанию - ASC. Для выполнения сортировки в обратной последовательности - DESC.
41.Способы заполнения базы данными в приложении Аccess. Для заполнения БД необходимо щелкнуть два раза левой клавишей мыши по названию необходимой таблицы. При заполнении таблицы в режиме непосредственного ввода на экран выводится таблица. Необходимо заполнить соответствующие поля их значениями. Поле счетчика заполняется автоматически. После заполнения таблицы данными сохранять их не надо – Access все введённые данные сохраняет автоматически. Чаще всего более удобным является режим заполнения таблицы с помощью форм. Форма представляет собой электронный бланк, в котором имеются поля для ввода данных. Пользователь вводит данные в эти поля, и данные автоматически заносятся в таблицы базы.
43.Сортировка данных в базе и фильтрация.Индексирование БД. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Данные, хранящиеся в таблицах БД, должны иметь простые и доступные средства поиска нужной ин-ции. Для этого Access предлагает такие средства, как сортировка и фильтрация. Сортировка используется для удобства нахождения данных. По умолчанию, когда таблица открывается в режиме Таблицы, она упорядочена по значению ключевого поля. Сортировку записей можно производить как по одному, так и по нескольким полям. Для этого нужно установить курсор на любую строку соответствующего столбца или выделить несколько столбцов и выполнить команду СОРТИРОВКА ПО ВОЗР (УБЫВ) меню ЗАПИСИ или воспользоваться соответствующими кнопками инструментальной панели. Для того чтобы вернуться к первоначальному порядку сортировки, нужно выполнить команду УДАЛИТЬ ФИЛЬТР меню ЗАПИСИ. Фильтрация - это способ быстрого отбора записей в режиме таблицы в соответствии с заданным критерием. Произвести отбор записей можно разными способами. Фильтр по выделенному фрагменту исп для того, чтобы найти в таблице значение, которое должно содержать отбираемые записи. Для этого следует выделить в поле запись, которая содержит условие отбора, и выполнить команду Записи → Фильтр → Фильтр по выделенному. Обычный фильтр используется для отбора записей по значениям нескольких полей. Для выполнения данного фильтра используется команда Записи → Фильтр → Изменить фильтр. Расширенный фильтр используется для создания сложных фильтров, так как позволяет сформировать критерий отбора связывая операциями логических функций и и или, команда Записи → Фильтр → Расширенный фильтр. Индекс – основное средство сортировки данных. Основное назначение индексир – оптимизация БД. Индексирование в любом случае требует доп. ресурсов, т.к. этот процесс выражается в создании специальных индексных файлов, в которых данные располагаются в упорядоченном виде по возр или убыв. При работе с БД для стационарных баз (один раз создаются и многократно исп) применяют кластеризованные индексы, которые позволяют упорядочивать данные в базе, присваиваивая каждой записи новый номер по которому в последствии будет осуществляться поиск. В БД, которые предусматривают постоянное обновление содержимого исп некластеризованные индексы, которые предполагают упорядочивание и запоминание ссылок на регистрационный номер записи.
44. Управление курсором в БД. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Под курсором, как правило, понимают получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. Курсор - это объект, связанный с определенной областью памяти. Существуют явные и неявные курсоры. Явный курсор объявляется разработчиком, а неявный курсор не требует объявления. Курсор может возвращать одну строку, несколько строк или ни одной строки. Для запросов, возвращающих более одной строки, можно использовать только явный курсор. Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета. Для управления явным курсором используются операторы CURSOR, OPEN, FETCH и CLOSE. Оператор CURSOR выполняет объявление явного курсора. Оператор OPEN открывает курсор, создавая новый результирующий набор на базе указанного запроса. Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца. Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы. Для объявления явного курсора используется оператор CURSOR. Для работы с курсором можно использовать следующие атрибуты, указываемые после имени курсора: %ISOPEN - возвращает значение TRUE, если курсор открыт; %FOUND - определяет, найдена ли строка, удовлетворяющая условию; %NOTFOUND - возвращает TRUE, если строка не найдена; %ROWCOUNT - возвращает номер текущей строки. При применении неявного курсора нельзя использовать операторы управления курсором OPEN, FETCH и CLOSE.
45.Формирование отчетов в БД Access. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Отчет - средство, которое позволяет извлечь из базы нужную информацию и представить ее в виде, удобном для восприятия, а также подготовить для распечатки отчет, который оформлен соответствующим образом. В Microsoft Access можно создавать отчеты с помощью конструктора и с помощью мастера. Окно отчета в режиме Конструктора идентично окну формы в этом режиме. Пустой макет отчета содержит разделы Верхний колонтитул , Нижний колонтитул , между которыми находится Область данных. Как и при работе с формами, можно изменить размер любого раздела, перетащив его границу. При создании отчёта с помощью мастера на экране появится первое диалоговое окно, в котором нужно выбрать таблицу или запрос, на основе которого будет построен отчет. После этого из списка доступных полей можно выбрать поля, которые будут присутствовать в отчете. После нажатия Далее на экране появится второе окно мастера в котором можно задать группировку. После нажатия Далее выходит третье диалоговое окно, в котором можно задать порядок сортировки для записей. Далее - переход к четвертому окну мастера – можно выбрать макет для будущего отчета: столбец, обычный, выровненный, также можно выбрать ориентацию – книжная или альбомная, а также подобрать ширину столбцов таким образом, чтобы разместить их все на одной странице (по ширине) – поставить флажок в нижней части окна. Далее - пятое окно мастера - можно выбрать стиль оформления отчета - Деловой , Обычный , Полужирный , Сжатый , Спокойный , Строгий . Далее - в последнем окне мастера можно задать название отчета, а также подсказать мастеру, что ему нужно делать после завершения создания отчета - открыть отчет в режиме просмотра или в режиме конструктора .
46.Создание интерфейса базы данных, используя возможности приложения Access. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Microsoft Access – настольная программа реляционного типа, входящяя в состав офисyого пакета Microsoft Office. Ее большим достоинством является простой графический пользовательский интерфейс, позволяющий не только создавать и редактировать свою базу данных, но и разрабатывать разные приложения с помощью встроенных средств. Access отличается от других СУБД тем, что хранит всю информацию в едином файле. выбрать из меню« Файл » команду « Создать» После этого Access откроет окно “Создание”, которое содержит множество предварительно заданных шаблонов. В этом окне, Access объединяет в представленном окне важные функции управления новой базой данных. В правом окне просмотра выглядит первое представление о структуре новой базы данных. Выделив нужный шаблон данных с помощью мыши, нажмите кнопку ОК. Далее нужно указать имя базы данных, после чего нажимаем «Создать». Щелкнув мышью по кнопке “Создать”, Щелкните мышью по кнопке “Далее”. Программа предложит разные имена полей данных, которые узнаются по галочке. Важным моментом является то, что некоторые поля Access задает автоматически - их нельзя игнорировать. Мастер баз данных предложит выбрать различные опции стиля и оформления. После ввода всех данных мастер нужно щелкнуть кнопке «Готово». Заполнение созданной базы данных. Через несколько секунд Access откроет окно, где можно ввести данные в соответствии с выбранными пунктами. Далее, щелкнув мышью по кнопке «Ввод/Просмотр формы», открывается шаблон ввода данных. Кроме всего прочего, программ Microsoft Access автоматически считает каждую 47Настройка безопасности базы данных запись с помощью счетчика. Это нужно для постоянного информирования о текущем числе записей. В поля с именами нужно вносить соответствующие данные.
47.Настройка безопасности базы данных с помощью приложения Access. БД – это именованная совокупность данных отражающая состояние объектов и их отношений в рассматриваемой предметной области. Защита на уровне пользователя - наиболее гибкий и распространенный метод реализации средств защиты базы данных Microsoft Access. Настройки защиты базы данных позволяют указать, будет использоваться специальная процедура входа пользователей или нет. Мастер защиты позволяет установить защиту на уровне пользователя с общей схемой защиты и закодировать базу данных Microsoft Access: Откройте базу данных, которую требуется защитить; В меню Сервис выберите команду Защита, а затем команду Мастер; Выполняйте инструкции, выводящиеся в диалоговых окна мастера. При защите отдельных элементов базы данных, например макета определенных объектов, когда нежелательно создание нескольких уровней доступа для различных групп пользователей, следует рассмотреть возможность защиты приложения без специальной процедуры входа, что не устраняет защиту на уровне пользователя, но существенно снижает ее для всех объектов за исключением указанных. Защитите базу данных с помощью мастера защиты на уровне пользователя. Мастер защиты позволяет установить защиту на уровне пользователя с общей схемой защиты и закодировать базу данных Microsoft Access: Откройте базу данных, которую требуется защитить; В меню Сервис выберите команду Защита, а затем команду Мастер; выполняйте инструкции, выводящиеся в диалоговых окна мастера. Предоставьте учетной записи пользователя «Admin» разрешения на доступ ко всем таблицам, запросам, формам, отчетам и макросам, которые следует сделать доступными для всех пользователей Откройте базу данных. Используемый при входе в базу данных файл рабочей группы должен содержать учетные записи пользователей и учетные записи групп, которым нужно присвоить разрешения. Однако можно присваивать разрешения группам, а позднее добавлять в них пользователей.В меню Сервис выберите команду Защита, а затем команду Разрешения. На вкладке Разрешения выберите параметр пользователи или группы, а затем в списке Пользователи и группы выберите пользователя или группу, которым нужно присвоить разрешения. Выберите нужный тип объекта в раскрывающемся списке Тип объекта, а затем выберите нужный объект в списке Имя объекта. Выделите нескольких объектов в списке Имя объекта, проведя указатель по именам объектов при нажатой кнопке мыши или выбрав имена объектов при нажатой клавише CTRL. В группе Разрешения установите или снимите флажки, определяющие соответствующие разрешения, и нажмите кнопку Применить. Чтобы определить разрешения на доступ к другим объектам для выбранного пользователя или группы, повторите шаги 4 и 5. Повторите шаги с 3 по 5 для других пользователей и групп). Отключите диалоговое окно Вход.