Тема 1. Базы данных специального назначения Лекция 2: Реляционная модель. Введение в реляционные БД. Основы языка SQL. Учебные цели занятия: Сформировать. - презентация

1 Тема 1. Базы данных специального назначения Лекция 2: Реляционная модель. Введение в реляционные БД. Основы языка SQL. Учебные цели занятия: Сформировать представление о: 1)Понятии реляционной модели данных, 2)Языке SQL и его возможностях, 3)Средствах определения структуры данных и типов данных реляционной модели и языка SQL. Учебные вопросы: 1)Введение в реляционные базы данных 2)Введение в язык SQL 3)Домены, отношения и базовые переменные-отношения Базы данных специального назначения. Лекция 2 1

2 Литература: К. Дж. Дейт. - Введение в системы баз данных, 7-е издание.: Пер. с англ. – М.: Издательский дом «Вильямс», – 1072 с., ил. Дж. Грофф, П. Вайнберг.- SQL: Полное руководство.- Пер. с англ.-2-е изд., перераб. и доп.-К.: Издательская группа BHV, с., ил. SQL в примерах и задачах; учеб. пособие / И.Ф.Астахова, А.П.Толстобров, В.М.Мельников. Мн.: Новое знание, с. Теория и практика построения баз данных/Д.Кренке.- 8-е изд.- СПб.: Питер, с., ил.- (Серия «Классика computer science»).

3 1.Введение в реляционные базы данных 1.1. Реляционная модель реляционная модель данных - формальная основа или теория, на которой базируются реляционные системы. Для таких систем характерно выполнение трех условий: Структурный аспект. Аспект целостности. Аспект обработки.

4 База данных отделов и служащих. Операции выборки, извлечения столбцов и соединения 4 Базы данных специального назначения. Лекция 2 НомОтделаНазваниеОтдБюджет Отд1Экономический10М Отд2Конструкторский12М Отд3Исследовательский5М НомСлужащегоИмяСлужащегоНомОтделаЗарплата С1ИвановОтд121K С2ПетровОтд118K С3НикитинОтд220K С4БуденкоОтд219K ОтделыСлужащие НомОтделаНазваниеОтдБюджет Отд1Экономический10М Отд2Конструкторский12М SELECT (RESTRICT) Выборка строк из Отделы, где БЮДЖЕТ > 8M НомОтделаБюджет Отд110М Отд212М Отд35М PROJECT Извлечение столбцов НомОтдела, Бюджет из Отделы НомОтделаНазваниеОтдБюджетНомСлужащегоИмяСлужащегоЗарплата Отд1Экономический10МС1Иванов21K Отд1Экономический10МС2Петров18K Отд2Конструкторский12МС3Никитин20K Отд2Конструкторский5МС4Буденко19K JOIN: Соединение Отделы и Служащие на основе столбца НомОтдела

5 Назначение некоторых операций базы данных Операция SELECT предназначена для извлечения определенных строк из таблицы. Операция PROJECT предназначена для извлечения определенных столбцов из таблицы. Операция JOIN предназначена для соединения двух таблиц на основе общих значений в общих столбцах. При выполнении операции JOIN строки таблиц соединяются на основе значения некоторого столбца и только тогда, когда две строки соединяемых таблиц содержат в этих полях одинаковое значение. Следует отметить, что в результате выполнения любой из приведенных операций получается также таблица. Это реляционное свойство называется замкнутостью.

6 Таблицы в реляционной системе являются логическими, а не физическими структурами. каждая строка в таблице должна иметь уникальное значение столбца, указанные столбцы в соответствующих таблицах являются потенциальными ключами. Среди всех потенциальных ключей выбирается один, который называется первичным ключом.

7 Реляционная модель 7 Базы данных специального назначения. Лекция 2 Реляционная модель состоит из следующих пяти компонентов: Неограниченный набор скалярных типов (включая, в частности, логический тип или значение истины). Генератор типов отношений и соответствующая интерпретация для таких сгенерированных типов отношений. Возможность определения переменных отношений для таких сгенерированных типов отношений. Операции реляционного присвоения для присвоения реляционных значений таким переменным отношений. Неограниченный набор общих реляционных операторов для получения значений отношений из других значений отношений.

8 Иллюстрация к реляционному присвоению 8 Базы данных специального назначения. Лекция 2 НомСлужащегоИмяСлужащегоНомОтделаЗарплата С1ИвановОтд121K С2ПетровОтд118K С3НикитинОтд220K Служащие НомСлужащегоИмяСлужащегоНомОтделаЗарплата С1ИвановОтд121K С2ПетровОтд118K С3НикитинОтд220K С4БуденкоОтд219K Служащие

9 мы удалили строку о сотруднике с фамилией «Буденко» (его номер «С4»): DELETE Служащие WHERE НомСлужащего=«С4». Концептуально это можно описать следующим образом: Старое значение отношения Служащие было заменено в целом совершенно другим, новым значением отношения. Т.е. приведенная операция удаления строки, по сути, - это просто другой, упрощенный способ записи операции реляционного присвоения: Служащие := Служащие MINUS ( Служащие WHERE НомСлужащего=«С4») Ключевое слово MINUS используется для описания оператора реляционной разности. Соответственно INSERT и UPDATE являются также иными формами записи соответствующих операций реляционного присвоения.

10 Предикат и истинное высказывание Рассмотрим важный, способ представления смысла отношений: Во-первых, данное отношение r и заголовок отношения r представляют определенный предикат или логическую функцию. Во-вторых, каждая строка в теле отношения r представляет собой определенное истинное высказывание, полученное из предиката путем подстановки определенных значений аргументов соответствующего типа вместо местодержателей или параметров этого предиката. Например, в случае, показанном на слайде, предикат будет следующим: Служащий с номером НомСлужащего по фамилии ИмяСлужащего работает в отделе с номером НомОтдела и получает зарплату Зарплата. Здесь параметрами являются НомСлужащего, ИмяСлужащего, НомОтдела и Зарплата, которые соответствуют 4-м столбцам переменной-отношения Служащие. Примером соответствующего истинного высказывания может быть: Служащий с номером C1 по фамилии Иванов работает в отделе с номером Отд1 и получает зарплату 21 тыс. ден. ед..

11 Можно сказать, что: типы- объекты (множества объектов), которые можно обсуждать; отношения – факты (множества фактов), касающиеся объектов, которые можно обсуждать. Важно понимать, что каждое отношение имеет связанный с ним предикат, включая отношения, полученные с помощью реляционных операторов, например оператора соединения. Пример 2.3. Отношение «Отделы», представленное на рис. 2.1 и три результирующих отношения, представленные на рис. 2.2, имеют следующие предикаты: «Отделы»: «Отдел с номером НомОтдела называется НазваниеОтд и имеет бюджет Бюджет». Выборка строк из «Отделы», где Бюджет > 8M: «Отдел с номером НомОтдела называется НазваниеОтд и имеет бюджет Бюджет, который больше 8 миллионов денежных единиц». Извлечение столбцов НомОтдела и Бюджет из «Отделы»: «Отдел с номером НомОтдела имеет какое-то название и бюджет Бюджет». Соединение переменных-отношений «Отделы» и «Служащие» на основе столбца НомОтдела: «Отдел с номером НомОтдела называется НазваниеОтд и имеет бюджет Бюджет, а служащий с номером НомСлужащего по фамилии ИмяСлужащего работает в отделе с номером НомОтдела и получает зарплату Зарплата».

12 Реляционная БД – это такая БД, которая воспринимается ее пользователями как множество переменных, значениями которых являются отношения. Поэтому реляционные системы иногда называют системами автоматической навигации. Ответственность за то, как именно выполняется автоматическая навигация, несет компонент СУБД – оптимизатор.

13 Базовые переменные-отношения и представления 13 Базы данных специального назначения. Лекция 2 Исходные (заданные) переменные-отношения называются базовыми переменными-отношениями, а присвоенные им значения называются базовыми отношениями (или значениями базовых отношений). Отношение, которое получено или может быть получено из базового отношения в результате выполнения каких-либо реляционных выражений, называется производным отношением. EMP#ENAMEDEPT#SALARY С1ИвановОтд121K С2ПетровОтд118K С3НикитинОтд220K С4БуденкоОтд219K CREATE VIEW TOPEMP AS (EMP WHERE SALARY >= 19K) { EMP#, ENAME, SALARY }

14 ( TOPEMP WHERE SALARY < 21K) { EMP#, SALARY } ( (EMP WHERE SALARY > = 19K) { EMP#, ENAME, SALARY} WHERE SALARY < 21K) { EMP#, SALARY } После определенного количества перегруппировок это выражение может быть упрощено и может принять следующий вид: ( EMP WHERE SALARY > = 19K AND SALARY < 21K) { EMP#, SALARY } первоначальная операция над представлением конвертируется в эквивалентную операцию над соответствующей базовой переменной-отношением (при этом она оптимизируется).

15 Транзакции Транзакция – логическая единица работы, обычно включающая несколько операций над базой данных. Для пользователя должна иметься возможность указать системе, что отдельные операции являются частью одной транзакции. Для этого используются операции BEGIN TRANSACTION, COMMIT и ROLLBACK. Как правило, транзакция начинается при выполнении операции BEGIN TRANSACTION и прекращается при выполнении операции COMMIT или ROLLBACK. BEGIN TRANSACTION/* Перевести деньги со счета A на счет B */ UPDATE account A/* Снятие денег со счета A */ UPDATE account B/* Помещение денег на счет B */ IF THEN COMMIT;/* Нормальное завершение */ ELSE ROLLBACK/* Аварийное завершение (откат) */ ENDIF

16 Транзакции Свойства транзакций: Атомарность – гарантия (с логической точки зрения), что операции будут выполнены полностью или не выполнены вовсе, даже если в системе до окончания процесса выполнения произойдет сбой. Продолжительность – гарантия того, что если транзакция успешно выполнила оператор COMMIT, то все выполненные ею изменения будут реализованы в БД, даже если в системе в какой-то момент произойдет сбой. Изолированность. Если в системе выполняется одновременно несколько транзакций, то все изменения, сделанные одной из них, не будут видны остальным, пока она не выполнит оператор COMMIT. Упорядоченность. При параллельном выполнении нескольких транзакций, операции которых чередуются между собой, гарантируется, что осуществление этих операций будет упорядоченным (serializable), т.е. результат будет таким же, как при строго последовательном выполнении этих же транзакций в произвольном порядке.

17 2.1 Обзор языка SQL Определение БД поставщиков и деталей: типы данных 17 Базы данных специального назначения. Лекция 2 CREATE TABLE S (SNCHAR(5), SNAMECHAR(20), STATUSNUMERIC(5), CITYCHAR(15), PRIMARY KEY ( SN ) ); CREATE TABLE P (PNCHAR(6), PNAMECHAR(20), COLORCHAR(6), WEIGHTNUMERIC(5,1), CITYCHAR(15), PRIMARY KEY ( PN ) ); CREATE TABLE SP (SNCHAR(5), PNCHAR(6), QTYNUMERIC(9), PRIMARY KEY (SN, PN), FOREIGN KEY (SN ) REFERENCES S, FOREIGN KEY (PN ) REFERENCES P); CHARACTER ( n )INTEGERDATEINTERVAL BIT (n)SMALLINT TIME NUMERIC (r, q)FLOAT ( p )TIMESTAMPDECIMAL(p,q)

18 2.2 Каталог в языке SQL: Основные компоненты информационной схемы 18 Базы данных специального назначения. Лекция 2 SCHEMA (схемы) DOMAINS (домены) TABLES (таблицы) VIEWS (представления) COLUMNS (столбцы) DOMAIN_CONSTRAINTS (ограничения для домена) TABLE_CONSTRAINTS (ограничения для таблицы) REFERENTIAL_CONSTRAINTS (ссылочные ограничения) ASSERTIONS (утверждения)

19 2.3 Представления. Пример представления в языке SQL 19 Базы данных специального назначения. Лекция 2 CREATE VIEW GOOD_SUPPLIER ASSELECT SN, STATUS, CITY FROM S WHERE STATUS > 15. SELECT SN, STATUS FROM GOOD_SUPPLIER WHERE CITY=Москва Определение представления:Определение запроса к представлению: SELECT GOOD_SUPPLIER.SN, GOOD_SUPPLIER.STATUS FROM (SELECT SN, STATUS, CITY FROM S WHERE STATUS > 15) AS GOOD_SUPPLIER WHERE GOOD_SUPPLIER.CITY = Москва Запрос после подстановки представления: SELECT SN, STATUS FROM S WHERE STATUS > 15 AND CITY = Москва Запрос после упрощения:

20 2.4 Транзакции Для операторов COMMIT и ROLLBACK в языке SQL есть прямые аналоги. Это операторы COMMIT WORK и ROLLBACK WORK соответственно (в обоих случаях слово WORK – необязательное). Но в языке SQL нет явного оператора, соответствующего оператору BEGIN TRANSACTION. Неявно транзакция начинается всякий раз, когда выполняется оператор, способный «инициализировать транзакцию» (transaction-initiating), но только в том случае, когда никакая транзакция не выполняется. Таковыми операторами являются практически все операторы, которые мы обсуждаем в этом разделе. Замечание. Некоторые реализации языка SQL имеют в своем составе аналоги оператора BEGIN TRANSACTION, которые в явном виде позволяют задавать начало выполнения транзакции. Примером такой реализации может послужить СУБД Microsoft SQL Server 2000.

21 2.5 Взаимодействие приложений и СУБД Для реализации возможности взаимодействия прикладных приложений, написанных прикладными программистами, с реляционной СУБД, использующей язык SQL, возможны несколько вариантов: Первый вариант состоит во внедрении SQL-операторов в синтаксические конструкции того или иного языка и поддержка их на уровне компилятора, который осуществляет достаточно прозрачно взаимодействие с СУБД. Второй вариант представляет форму взаимодействия, когда с языком программирования поставляется несколько библиотек, которые реализуют возможность взаимодействия с СУБД

22 3. Домены, отношения и базовые переменные-отношения Основой современной технологии БД является реляционная модель. в реляционной модели рассматриваются три принципиальных аспекта данных – структура данных, манипулирование данными поддержание целостности данных.

23 Термины, используемые для описания структур данных 23 Базы данных специального назначения. Лекция 2

24 3.1 Домены Домен – это не что иное, как тип данных. В частности, возможно, простой, определяемый системой, подобно типам INTEGER и CHAR. В общем случае этот тип определяется пользователем. Прежде всего, домен, или тип данных, это множество значений – всех возможных значений рассматриваемого типа. Например, тип INTEGER – это множество всех целых чисел. Говоря о каком-либо типе данных необходимо помнить об операторах, которые могут корректно применяться к значениям этого типа. Другими словами, значениями заданного типа можно манипулировать только с помощью операторов, определенных для этого типа. Типы данных можно разделить на скалярные и нескалярные.

25 3.2 Значения отношений Как уже отмечалось, следует различать собственно отношения и переменные- отношения (т.е. переменные, значениями которых являются отношения). В данном подразделе рассматриваются значения отношений. Прежде всего дадим точное определение термина отношение:

26 Отношение, заголовок, тело, атрибут, кортеж (определения) 26 Базы данных специального назначения. Лекция 2 Пусть задано множество из n типов или доменов T i (i=1,2,…,n), причем все они необязательно должны быть различными. Тогда r будет отношением, определенным на этих типах, если оно состоит из двух частей: заголовка и тела, где: а) заголовок – это множество из n атрибутов вида A i :T i ; здесь A i – имена атрибутов (которые должны отличаться одно от другого) отношения r, а T i – соответствующие имена типов (i=1,2,…,n). б) тело – это множество из m кортежей t; здесь t в свою очередь, является множеством компонентов вида A i :V i, в которых V i – значение типа T i, т.е. значение атрибута для атрибута A i в кортеже t (i=1,2,…,n). Замечание: Следует отметить, что заголовок отношения также называется схемой отношения. Значения m и n называются соответственно кардинальностью и степенью отношения r.

27 отношение и таблица – это в действительности не одно и то же. Отношение – это некоторый абстрактный вид объекта, соответствующий данному ранее определению, а таблица – это конкретное изображение данного абстрактного объекта. Свойства отношений: Отсутствие одинаковых кортежей. Отсутствие упорядочения кортежей (сверху вниз). Отсутствие упорядочения атрибутов (слева направо). Каждый кортеж содержит ровно одно значений для каждого атрибута.

28 3.3 Средства SQL определения типов и структур данных 28 Базы данных специального назначения. Лекция 2 CREATE DOMAIN [ ] [ ] ; CREATE DOMAINCREATE TABLE ALTER DOMAINALTER TABLE DROP DOMAINDROP TABLE DROP DOMAIN ; CREATE TABLE ( ); DROP TABLE ; Базовые таблицы Домены Используемые операторы:

29 Отличия между настоящими доменами и конструкциями языка SQL: Домены языка SQL – это просто синтаксические сокращения. Они не относятся к истинному типу данных, определяемых пользователем. Значения доменов языка SQL не могут быть «произвольной внутренней сложности». Их сложность ограничена сложностью встроенных типов. Домены языка SQL определяются в терминах одного из встроенных типов, а не в терминах другого домена, определенного пользователем. На практике каждый домен SQL должен определяться в терминах только одного из существующих встроенных типов. Домены языка SQL не могут иметь больше одного «допустимого» представления, которое определяется их физическим представлением. В языке SQL нет строго контроля типов и выполняемая проверка правильности типов совершенно незначительна. Язык SQL не содержит возможностей по определению пользователем операций, применяемых к данному домену. Допустимыми являются лишь встроенные операции, применимые к соответствующим представлениям этого типа.

30 Форма записи: CREATE DOMAIN [ ] [ ] ; Необязательный параметр определение значения по умолчанию задает значение по умолчанию, которое будет применяться к каждому столбцу, определенному на этом домене и не имеющему собственного явно заданного значения по умолчанию. Значение этого параметра должно иметь вид DEFAULT, где значение по умолчанию может быть как литералом, именем 0-адического оператора (без операндов, CURRENT_DATE) или значением NULL. В параметре ограничения указываются все ограничения, накладываемые на домен.

31 оператор DROP DOMAIN, имеющего следующий синтаксис: DROP DOMAIN ; Здесь параметр режим может принимать значения RESTICT или CASCADE.

32 Оператор CREATE TABLE (обращаем внимание, что слово TABLE подразумевает только базовую таблицу, также как и в операторах ALTER TABLE, DROP TABLE). Синтаксис выражения следующий: CREATE TABLE ( ); Здесь каждый элемент базовой таблицы является либо определением столбца, либо определением ограничения базовой таблицы. В последнем случае элемент задает ограничение поддержки целостности данных, которое будет применяться к создаваемой таблице. Этот вопрос мы рассмотрим в последующих лекциях. Определение столбца, в свою очередь, выглядит следующим образом: [ ] Параметр указывает название столбца, параметр задает используемый тип данных иди домен, а необязательный параметр определяет значение по умолчанию для соответствующего столбца, которое подавляет значение по умолчанию, указанное для домена.

33 Существующее определение базовой таблицы можно изменить в любое время с помощью оператора ALTER TABLE, который позволяет производить следующие изменения: Добавить столбец; Задать для существующего столбца новое значение по умолчанию; Удалить значение по умолчанию для существующего столбца; Удалить существующий столбец; Задать новые ограничения целостности; Удалить существующие ограничения целостности. Пример для первого случая приведен ниже: ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT -1; Этот оператор добавляет в базовую таблицу S столбец с именем DISCOUNT типа INTEGER со значением по умолчанию -1.

34 существующую базовую таблицу можно уничтожить с помощью оператора DROP TABLE: DROP TABLE ; Параметр режим принимает те же значения RESTRICT и CASCADE, смысл которых аналогичен.

35 1.Понятие реляционной модели данных. Основные черты. Строгое определение. 2.Отношения и переменные-отношения. Определение и смысл отношений. Примеры. 3.Оптимизация: цели основы для ее выполнения. Каталог: понятие и назначение. Транзакции: определение, назначение и способ организации. 4.Базовые переменные-отношения и представления. Производные отношения. Примеры. 5.Язык SQL: история, возможности, соотношение с реляционной моделью. Каталог в SQL: структура и состав. Представления и транзакции в SQL. Взаимодействие приложений с реляционными БД, динамический SQL. 6.Язык SQL: средства описания/изменения структуры данных и типов данных. Встроенные типы данных и домены. Примеры. Вопросы на самоподготовку: 35 Базы данных специального назначения. Лекция 2