Лекция 4. Реляционная модель хранения данных. Логическое моделирование реляционных баз данных.

**1-ый учебный вопрос: Определение реляционной модели данных.** Реляционная модель данных, это модель хранения данных (построения базы данных), состоящая из отношений и схемы отношений. Отношение – это двухмерная плоская таблица, а схема отношений – это описание заголовков столбцов этой таблицы. Схему отношений можно также назвать метаинформацией для отношения. В реляционной модели данных отношение и схема отношений записываются следующим образом: сперва указывается название отношения, потом в скобках, разделенные запятой, перечисляются все пары названий столбцов и их типы данных. Приведем пример отношения и его схемы отношения. Students (sid: string, name: string, login: string, age: integer). В данном случае отношением является Students (студенты), а его схемой отношения – все, что находится в скобках. Отдельно выделим использованные для элементов типы данных: string.- строковая переменная, integer – целочисленная переменная. Отметим, что в логической модели реляционных данных используется ограниченный набор типов данных. Помимо уже перечисленных это может быть значение с плавающей точкой float, дата/время (темпоральный тип): date, массив данных array. Тип данных, определенный для столбца, можно считать его доменом (доменным ограничением). При наличии нескольких экземпляров отношения, оно будет выглядеть, как показано на рис. 1.  **2-ой учебный вопрос: Язык запросов SQL и реализация в нем функционала реляционной модели данных.** Язык SQL (SEQUEL) – это язык запросов для реляционной модели данных. С помощью инструкций этого языка осуществляется создание, модификация элементов реляционной модели хранения данных, а также манипулирование хранимыми в базе данными. Инструкции языка представляют собой близкий к естественному языку текст обращения к СУБД. Одной из самых простых инструкций является инструкция визуализации содержимого таблицы без каких-либо ограничений. Чтобы увидеть содержимое таблицы, вводится следующая инструкция: SELECT * FROM {имя таблицы}; Результатом приведенной выше инструкции является визуализация отношения на экране ПК. В данной конструкции SELECT и FROM являются обязательными операторами и будут участвовать в любом запросе. Символ * указывает на то, что результатом выполнения запроса будут все экземпляры отношения без исключения. Единственной переменной, задаваемой в данном запросе, является имя таблицы, к которой следует обращение. Данный запрос, через форму пользовательского приложения, через специальное приложение управления БД или любым иным доступным способом передается в реляционную СУБД, которая в свою очередь выполняет команды запроса и в ответе выдает результат в виде сформированной результатной таблицы. Запросы с инструкцией SELECT могут быть вычисляемыми. Для этого, к базовой инструкции добавляется оператор WHERE, который позволяет сформулировать вычисляемую часть запроса. Предположим, мы хотим вывести из таблицы, приведенной на рис. 1 только тех студентов, возраст (age) которых - ровно 18 лет. Инструкция с вычисляемой частью в этом случае будет выглядеть следующим образом: SELECT * FROM Students WHERE Age = 18 Обратите внимание, что в этом случае было явно указано название таблицы Students, а после оператора WHERE было установлено вычисляемое ограничение выборки Возраст равен 18. Результатом этой выборки, полученным СУБД будет таблица, показанная на рис. 2.  Язык SQL состоит из нескольких групп инструкций. Группа инструкций, поддерживающая создание, удаление, модификацию таблиц (отношений) называется Data Definition Language (DDL). Ниже будет рассмотрен принцип написания инструкций DDL SQL, которые позволяют создать, удалить и модифицировать реляционные таблицы в БД. Для создания реляционной таблицы используется инструкция CREATE TABLE. В общем, наиболее минималистичном для нормальной работы виде эта инструкция выглядит следующим образом: CREATE TABLE {имя таблицы} (схема отношения: пары атрибут-тип данных). Покажем пример составления инструкции для создания реляционного отношения, показанного на рис. 1. CREATE TABLE Students (sid CHAR (20), name CHAR (30), login CHAR (20), age INTEGER, gpa REAL) Обратим внимание на типы данных, использованные в данной инструкции. Реализация инструкции SQL такого рода приводит к созданию физической модели данных, соответственно и набор типов данных сформирован, отталкиваясь от требований СУБД, которая будет управлять данными. В данной инструкции присутствуют следующие типы данных: CHAR (character, символьный с ограничением в 20 символов), CHAR (с ограничением в 30 символов), INTEGER (простой, целочисленный), REAL (реальные числа, дробные значения). Более подробно тема запросов и типов данных для разных СУБД будет рассмотрена в практическом курсе и в ходе лабораторных работ. Для изменения существующей реляционной таблицы (любые изменения в схеме отношения) используется инструкция ALTER TABLE. В зависимости от того, какого характера изменения необходимо реализовать в схеме отношения, эта инструкция будет выглядеть по-разному. Приведем пример для случая добавления в схему отношений нового столбца. Так, для таблицы Студенты (рис. 1) добавим новый столбец Первогодки (first_year) с типом данных (доменным ограничением) integer. Это инструкция выглядит следующим образом: ALTER TABLE Students ADD COLUMN first_year: INTEGER Оператор ADD COLUMN сообщит СУБД о том, что под изменениями в схеме отношения в данном случае понимается добавление нового столбца к уже существующим. После остается только указать пару {название столбца}: {тип данных}. Обращу внимание на то, что выполняя эту инструкцию надо будет заранее решить, какими значениями будут заполнены все уже имеющиеся экземпляры отношения в этом столбце. Это могут быть как неопределенные значения NULL, так и значения по умолчанию (DEFAULT). Для удаления существующей реляционной таблицы используется инструкция DROP TABLE. Стоит отметить, что при использовании этой инструкции будет удалено как отношение, так и схема отношения, иными словами – таблица будет полностью уничтожена. В случае, если схему отношения нужно сохранить, следует использовать инструкцию TRUNCATE TABLE. Приведем пример для случая удаления таблицы Студенты (Students, рис. 22). Это инструкция выглядит следующим образом: DROP TABLE Students. Остальные группы инструкций языка SQL, также, как и более продвинутые варианты реализации инструкций, приведенных выше будут рассмотрены в материалах практических и лабораторных работ по данному курсу. **3-ой учебный вопрос: Реляционные ограничения целостности.** Одной из задач реляционных СУБД является предотвращение ввода в базу данных некорректной информации. Ограничение целостности – это условие, сформулированное в схеме базы данных, ограничивающее данные, которые могут храниться в БД. Ограничение целостности первый раз определяется в процессе создания схемы отношения, после чего проверяются каждый раз, когда в отношении происходят какие-то изменения. Проверка ограничений целостности осуществляется СУБД. Задаются ограничения пользователем БД или, в некоторых случаях СУБД автоматически. В языке SQL ограничения целостности могут быть сформулированы операторами PRIMARY KEY, CHECK, UNIQUE, FOREIGN KEY и другими. В рамках данной лекции будут рассмотрены ограничения целостности первичного ключа (PRIMARY KEY) и внешнего ключа (FOREIGN KEY). Первичный ключ отношения – это атрибут, входящий в схему отношения и при этом, однозначно определяющий каждый экземпляр данного отношения (не повторяется не при каких обстоятельствах ни для одного из экземпляров данного отношения). Первичный ключ может быть выбран как из ключей-кандидатов, как наиболее подходящий из имеющихся в схеме отношения атрибутов, так и быть искусственно создан администратором БД (как правило, для тех случаев, когда в схеме отношения нет ключей-кандидатов). Ограничения первичного ключа имеют собственное имя и задаются в процессе создания реляционного отношения с помощью оператора SQL CONSTRAINT. В качестве примера, ниже продемонстрируем процедуру создание ограничения целостности с помощью первичного ключа для отношения, приведенного на рис. 1. CREATE TABLE Students (sid CHAR (20), name CHAR (30), login CHAR (20), age INTEGER, gpa REAL, CONSTRAINT StudentsKey PRIMARY KEY (sid)) В данном примере созданное ограничение будет иметь имя StudentsKey, и создано оно будет на основе значений столбца sid (идентификационный номер студента). Будем полагать, что идентификационный номер студента для каждого экземпляра студента уникальный, что удовлетворяет условию целостности для первичного ключа отношения. Внешний ключ отношения – элемент, обеспечивающий целостность в случае, когда информация, требуемая в одном отношении, хранится в другом отношении. В случае, когда такая информация изменяется, она должна быть проверена в другом отношении и, возможно, также модифицирована, для того, чтобы данные остались согласованными. Для демонстрации внешнего ключа представим, что помимо таблицы Студент (рис. 22) в базе данных есть связанная с ней таблица Записаны (Enrolled). Имеются в виду курсы, на которые записан студент. Приведем ниже схему отношения Enrolled. Enrolled (sid: string, cid: string, grade: string). Обратите внимание на то, что помимо атрибутов cid (идентификационный номер курса) и grade (оценка) в схеме отношения есть атрибут sid (идентификационный номер студента), который на самом деле принадлежит другому отношению – Студент, при этом являясь его первичным ключом. Для отношения Записаны этот атрибут является внешним ключом, поддерживающим целостность данных, хранимых в этом отношении. Так, если вдруг у одного из студентов изменится его идентификационный номер, то СУБД отреагирует на эти изменения в первичном ключе отношения студент и соответствующим образом модифицирует внешний ключ в отношении Записаны. Графически связи первичного и внешнего ключей показаны на рис. 3.  Из рис. 3 следует, что внешний ключ, в отличие от первичного однозначно не идентифицирует каждый экземпляр сущности Записаны. Напротив, студент с идентификационным номером 53666 записан и посещает три курса. Отсюда следует, что основной задачей внешнего ключа является ограничение целостности в реляционных связях между сущностями. Стоит оговориться, что в примере на рис. 3 внешний ключ также является частью составного первичного ключа сущности Enrolled (sid + cid), так как только комбинация этих двух атрибутов однозначно идентифицирует каждый экземпляр этой сущности. Ниже приведен код SQL инструкции на создание отношения Enrolled с внешним ключом sid. CREATE TABLE Enrolled (sid CHAR (20), cid. CHAR (20), grade CHAR (2), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students) Упомянутый в листинге выше элемент REFERENCES указывает на таблицу, откуда будут взяты значения для внешнего ключа sid. Ограничение целостности, устанавливаемое при помощи внешнего ключа, также называется ссылочной целостностью. Существует три варианта управления ссылочной целостностью. Все они программируют действия СУБД в случае, когда происходят изменения в источнике данных для внешнего ключа. По умолчанию СУБД устанавливает запрет на изменения в атрибуте, являющемся источником значений для внешнего ключа. Эти изменения возможны лишь тогда, когда будет снято ограничение ссылочной целостности с отношений. В СУБД такое ограничение называется NO ACTION. Пользователем может быть установлено ограничение, которое в случае изменений в атрибуте, являющимся источником значений для внешнего ключа выполнит аналогичные изменения во всех ссылающихся значениях внешнего ключа. Следует обратить внимание на то, что в случае удаления одного из значений, все экземпляры с соответствующим значением внешнего ключа будут полностью удалены. В СУБД такое ограничение называется CASCADE. Пользователем может быть установлено ограничение, которое в случае изменений в атрибуте, являющимся источником значений для внешнего ключа во всех ссылающихся значениях внешнего ключа установит неопределенные значения NULL или же значения по умолчанию DEFAULT. В СУБД такое ограничение называется SET NULL или же SET DEFAULT. Ниже показан SQL код установления разных вариантов ссылочной целостности для разных действий с данными. CREATE TABLE Enrolled (sid CHAR (20), cid. CHAR (20), grade CHAR (2), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT) В случае удаления (delete) связанных с внешним ключом значений будет выполнено каскадное удаление соответствующих экземпляров отношения Enrolled. В случае изменений (update) в связанных с внешним ключом значениях будет выполнена замена текущего значения в соответствующих экземплярах отношения Enrolled на значение по умолчанию (default). **4-ой учебный вопрос: Введение в пользовательские представления.** Пользовательское представление или представление (VIEW) – это виртуальная таблица БД, содержащая внутри себя не данные сами по себе, а запрос на их формирование. Эта таблица с точки зрения СУБД практически не отличается от обычной таблицы БД, т.е. с ней можно работать с помощью запросов языка SQL (создание, изменение, удаление таблицы; вывод выборки данных на экран). Данного рода элемент БД часто используется с целью обеспечения безопасности данных (ограничивая выборкой доступ к чувствительной информации), а также для упрощения работы с часто используемыми инструкциями на выборку данных (SELECT). Ниже будут приведены инструкции на создание и удаление представления на основе данных из таблицы на рис. 1. CREATE VIEW GoodStudents (sid, gpa) AS SELECT sid, gpa FROM Students WHERE gpa > 3.0 Обратите внимание на то, что представление создается инструкцией CREATE VIEW. Внутри представления нет схемы отношения как в обычной таблице, а вместо этого через элемент AS приводится инструкция выборки SELECT. Далее, при обращении к представлению GoodStudents с помощью запроса на выборку SELECT пользователь может получить информацию о студентах с хорошей успеваемостью (средний балл больше 3.0). Запрос на выборку к представлению ничем не отличается от обычного запроса на выборку к таблице. SELECT * FROM GoodStudents Удаление представления, по аналогии с удалением таблицы осуществляется инструкцией DROP VIEW. ***Вопросы для самостоятельного изучения по итогам лекции.*** 1. В чем ключевое отличие концептуальной модели данных от логической модели данных? 2. Что такое запрос к реляционной базе данных? Где можно использовать результаты этой инструкции? 3. Возможна ли сортировка на физическом уровне таблицы? Возможна ли сортировка на логическом уровне запроса или представления? 4. Приведите несколько примеров необходимости использования пользовательского представления. ***Тестовые задания для самопроверки.*** 1. Столбцы таблицы называются А) записи Б) индексы В) поля Г) ключи 2. Свойства объектов в логической реляционной модели данных называют: А) кортежами Б) схемой В) атрибутами 3. Какой тип связи имеется и допускается использовать в ER модели, но не существует в физическом модуле? А) многие ко многим Б) один к одному В) один ко многим 4. Программа, которая выполняет некоторые действия с информацией в базе данных, сама хранится в базе данных, при этом вызываемая пользователем это: А) хранимая процедура Б) триггер В) функция языка SQL 5. Представление данных — это ... А) таблица, содержащая действительные данные Б) виртуальная таблица, содержащая в теле инструкцию выборки SELECT В) совокупность метаинформации о таблице базы данных 6. Особенность поля со свойством IDENTITY состоит в том, что … А) оно имеет свойство автоматического наращивания Б) данные хранятся не в самом поле, а в другом месте, а в поле хранится только указатель В) максимальный размер числа, хранящегося в нем, не может превышать 255 Г) оно предназначено для ввода целых чисел Д) автоматически превращает поле в первичный ключ 7. Оператор CREATE относится к группе операторов: А) манипуляции данными Б) описания данных В) задания прав доступа в базе данных Г) защиты, восстановления данных и прочие операторы 8. Какая из перечисленных функций СУБД не является ограничением целостности? А) PRIMARY KEY Б) WHERE В) CHECK 9. Какой из перечисленных вариантов ссылочной целостности не позволит удалить значение потомка, связанное с первичным ключом родителя? А) CASCADE Б) SET DEFAULT В) NO ACTION 10. В каком элементе семантики SQL инструкции на выборку указывается ограничение, применяемое к массиву данных? А) TARGET LIST Б) RELATION LIST В) QUALIFICATION 11. Запрос «ALTER TABLE table1 ADD c1 int NOT NULL» принадлежит группе операторов (языку): А) определения данных (DDL) Б) манипуляции данными (DML) В) определения доступа к данным (DCL) Г) управления транзакциями (TCL)