🗊Презентация Системы баз данных

Системы баз данных Иванюкович Владимир Александрович

Содержание курса Концепция баз данных и СУБД Модели данных (иерархическая, сетевая, реляционная) Типы связей Ключи и целостность Реляционная алгебра Основы SQL Проектирование реляционных баз данных

Рекомендуемая литература К.Дж. Дейт. Введение в системы баз данных. Восьмое издание. – М.: Вильямс, 2005. – 1328 С.) В. Иванюкович. Системы баз данных. Вводный курс. Учебное пособие для студентов специальности 1-40 01 02. –Мн., МГЭУ им. А.Д. Сахарова, 2010. – 193 С.

Концепция баз данных Файлы, содержащие описание структур хранения данных и сведения о данных и находящиеся под управлением СУБД, называются базами данных. СУБД – это программный продукт, предназначенный для создания структур хранения данных, а также для ввода, хранения и обработки самих данных.

Создание структуры хранения данных СОЗДАТЬ ТАБЛИЦУ Расписание (Номер_Рейса Целое, Дни_Недели Текст (8), Пункт_Отправления Текст (24), Время_Вылета Время, Пункт_Назначения Текст (24), Время_Прибытия Время, Тип_Самолета Текст (8), Стоимость_Билета Денежный);

Поиск и обработка данных ВЫБРАТЬ Номер_Рейса, Дни_Недели, Время_Вылета ИЗ ТАБЛИЦЫ Расписание ГДЕ Пункт_Отправления = 'Москва' И Пункт_Назначения = 'Минск' И Время_Вылета > ‘17’; ВЫБРАТЬ КОЛИЧЕСТВО (Номер_Рейса) ИЗ ТАБЛИЦЫ Расписание ГДЕ Пункт_Отправления = 'Москва' И Пункт_Назначения = 'Минск';

Иерархическая модель данных

Сетевая модель данных

Реляционная модель

продолжение

Реляционная модель

Реляционная модель (relation – отношения) данные на концептуальном уровне представляются в виде двумерных таблиц, в которых хранится информация о сущностях; строки называются кортежами или записями и содержат информацию об экземплярах сущности; столбцы называются полями и содержат информацию об атрибутах сущности. реляционные системы представляют более простую среду для разработки баз данных, чем иерархические или сетевые; программное обеспечение, основанное на реляционной алгебре, позволяет организовать практически любое манипулирование данными.

Идея реляционной модели данных Строки таблицы с n колонками, состоящими из элементов множеств A1, A2, …, An, можно представить как подмножество в прямом произведении A1A2… An. Строки образуют список из n элементов, по одному из каждого множества Ai, а вся таблица представляет собой n-арное отношение. Например, таблицу КЛИЕНТЫ можно рассматривать как подмножество множества A1A2A3A4, где A1 – множество кодов клиентов, A2 – множество имен клиентов, A3 – множество их адресов, An – множество названий организаций. Один из элементов этого отношения – строка К1, Андрей, Минск, ИНКО. Представленные таким образом таблицы можно обрабатывать, используя алгебру отношений на множествах.

Связь «один-к-одному» (1:1): В каждый момент времени каждому представителю сущности А соответствует 1 или 0 представителей сущности В, а каждому представителю сущности В соответствует 1 или 0 представителей сущности А.

Связь «один-ко-многим» (1:М): одному представителю сущности А соответствуют 0, 1 или несколько представителей сущности В, а любому представителю сущности В соответствует 1 или 0 представителей сущности А.

Связь «многие-ко-многим» (N:М): каждому представителю сущности А может соответствовать множество представителей сущности В, а каждому представителю сущности В может соответствовать множество представителей сущности А.

Ключи и целостность реляционных данных (1) Целостность (integrity – неприкосновенность, сохранность, целостность) – правильность данных в любой момент времени. Поддержание целостности базы данных – защита данных от неверных изменений или разрушений. Основные механизмы обеспечения целостности данных связаны с понятием первичных и внешних ключей.

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

Ключи и целостность данных (3) Потенциальные ключи Первичный и альтернативные ключи являются частным случаем потенциального ключа. Надмножество потенциального ключа называется суперключом (например, суперключом для отношения Проекты является множество атрибутов {Пр№, Имя_проекта}). Суперключ обладает свойством уникальности, но не обязательно обладает свойством несократимости. Потенциальный ключ – это частный случай суперключа .

Ключи и целостность данных (4) Целостность объектов Правило целостности объектов: Ни один элемент первичного ключа базового отношения не может быть Null-значением. Если кортеж имеет Null-значение некоторого атрибута, то это означает, что в таком кортеже значение атрибута по какой-то причине отсутствует.

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

Ключи и целостность данных (6) Целостность по ссылкам Целостность по ссылкам: База данных не должна содержать несогласованных значений внешних ключей. Т.е., если В ссылается на А, то А должно существовать. Правило целостности по ссылкам позволяет поддерживать базу данных в корректном состоянии.

Ключи и целостность данных (8) Внешние ключи Правило внешних ключей предполагает принятие решения: Что должно случиться при попытке удалить объект ссылки внешнего ключа? Что должно случиться при попытке обновить потенциальный ключ, на который ссылается внешний ключ? Существует две возможности: ограничение – «ограничить» операции до момента появления первой ссылки; каскадирование – «каскадировать» операции, удаляя или обновляя все соответствующие атрибуты.

Ключи и целостность данных (9) Внешние ключи Реляционная модель допускает появление Null-значений среди атрибутов внешних ключей! Определение внешнего ключа: Внешний ключ FK в отношении R2 – это подмножество множества атрибутов R2 такое, что существует базовое отношение R1 с потенциальным ключом CK, для которого каждое значение FK в текущем значении R2 или является Null-значением, или совпадает со значением CK некоторого кортежа в текущем значении R1.

Ключи и целостность данных (10) Целостность атрибута Значение каждого атрибута берется из соответствующего домена.

Типы данных Категории Character string – Строки символов; Bit string – Строки битов; Exact numeric – Рациональные (целые и действительные) числа с плавающей десятичной точкой; Approximate numeric – Вещественные числа (с плавающей точкой); Date time – значения даты и времени; Interval – интервалы даты и времени.

Строковые типы данных Character (n) – строка фиксированной длины n. Если символов меньше чем n, то добавляются пробелы. Синонимы – Char(n). Character varying (n) – строка переменной длины, длинной менее n. Синонимы: Char varying, Charvar. National Character (National Char, NChar) – совпадает с типом Char, только хранит лишь стандартизованные многобайтовые или двухбайтовые знаки (Unicode). National Character Varying – то же для строк переменной длины. Unicode – единое множество 16-разрядных чисел, которое представляет знаки почти всех мировых языков. Содержит 65536 = 216 знаков. В СУБД Access к строковым типам данных относятся: text и memo.

Битовые типы данных BIT (n) – строка фиксированной длины (фиксированные числа битов). Max длина определяется СУБД. Если длина строки меньше n, то получите сообщение об ошибке. В стоке BIT перед первой кавычкой должна стоять латинская В, например, В'01001' – это строка типа BIT(5). Bit varying – аналогично, как Charvar. Тип данных BIT используется для хранения так называемых больших бинарных объектов (Binary Large Object – BLOB) – например, звук, изображение. В СУБД Access к BIT типу данных относятся: YES, NO, BINARY, OLE OBJECT.

Точные числовые типы данных Точность – число значащих цифр в записи числа; Масштаб – число цифр справа от десятичной точки (масштаб ≤ точности). Типы: Numeric (точность [,масштаб]) – представляет произвольное рациональное число. Decimal – аналогичен NUMERIC, но только задает нижнюю границу точности, т.е. СУБД может выбрать большую точность, чем заказано пользователем. Integer (или INT) – представляет произвольное целое число. SMALLINT – повторяет INT, только интервал допустимых значений уже. В СУБД Access: DECIMAL, INTEGER, BYTE, LONG INTEGER.

Пример: Хранение числа 123,55

Вещественные числовые типы данных Числа с плавающей точкой применяются для хранения приближенных числовых значений. FLOAT (точность) – представляет произвольное рациональное приближение действительного числа с плавающей точкой. Значение точности представляется не в количестве значащих десятичных цифр, а в количестве битов. Точность не должна быть меньше 1. Для преобразования десятеричной точности в бинарную надо умножить десятеричную точность на 3.32193. Например, 7 знаков точности дают 24 бита. REAL – совпадает с FLOAT, но точность вводить не надо, ее автоматически определяет СУБД. Числа типа REAL называют числами одинарной точности с плавающей точкой. DOUBLE PRECISION –числа двойной точности с плавающей точкой. В СУБД Access – SINGLE, DOUBLE.

Календарные типы данных DATE – имеет формат YYYY-MM-DD. TIME – имеет формат HH:MM:SS. Можно добавить аргумент “точность” для долей секунд. TIMESTAMP – имеет формат YYYY-MM-DD_ HH:MM:SS. Интервальные типы данных.

Реляционная алгебра Замкнутость; Правила наследования имен атрибутов; Правила наследования потенциальных ключей; Совместимость по типу: Два отношения совместимы по типу, если каждое из них имеет одно и то же множество имен атрибутов и соответствующие атрибуты определены на одном и том же домене.

Традиционные реляционные операции (1) Объединение Объединением двух совместимых по типу отношений А и В (A UNION B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих А или В или обоим отношениям. При этом совпадающие кортежи записываются один раз.

Традиционные реляционные операции (2) Пересечение Пересечением дух совместимых по типу отношений А и В (A INTERSECT B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, которые принадлежат одновременно обоим отношениям А и В.

Традиционные реляционные операции (3) Вычитание Вычитанием двух совместимых по типу отношений А и В (A MINUS B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих отношению А и не принадлежащих отношению В.

Традиционные реляционные операции (4) Произведение Декартово произведение двух отношений А и В (A TIMES B), где А и В не имеют общих имен атрибутов, определяется как отношение с заголовком, который представляет собой сцепление (конкатенацию) двух заголовков исходных отношений А и В, и телом, состоящим из множества всех кортежей t таких, что t представляет собой сцепление кортежа a, принадлежащего отношению А, и кортежа b, принадлежащего отношению В. Кардинальное число результата равняется произведению кардинальных чисел исходных отношений, а степень равняется сумме их степеней.

Специальные реляционные операции (1) Выборка Выборка (RESTRICT или SELECT) – это сокращенное название -выборки, где  обозначает любой скалярный оператор сравнения (=, , , > и т.д.). -выборкой из отношения А по атрибутам X и Y (A WHERE X  Y) (порядок учитывается!) называется отношение, имеющее тот же заголовок, что и отношение А, и тело, содержащее множество всех кортежей t отношения А, для которых проверка условия «X  Y» дает значение истина. Атрибуты X и Y должны быть определены на одном и том же домене, а оператор сравнения  должен иметь смысл для данного домена.

Специальные реляционные операции (2) Проекции Проекцией (PROJECT) отношения А по атрибутам X, Y, …, Z, где каждый из атрибутов принадлежит отношению А, называется отношение с заголовком {X,Y,…,Z} и телом, содержащим множество кортежей с атрибутами, совпадающими с соответствующими атрибутами отношения А. Т.е., с помощью операции проекции получается вертикальное подмножество исходного отношения

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

Естественное (или внутреннее) соединение (4)

Специальные реляционные операции (5) Внешнее соединение При внешнем соединении кортеж, который невозможно соединить с кортежем соответствующей таблицы из-за отсутствия совпадающих значений, будет помещен в результирующую таблицу, а для присоединенных атрибутов значения определены не будут, т.е., им присвоят Null-значения.

Специальные реляционные операции (6) Соединения Отношения можно соединять по атрибутам, имеющим либо общие домены, либо сопоставимые домены, когда значения данных из одного домена можно сопоставить со значениями данных из другого домена. Соединение обладает свойствами ассоциативности и коммутативности. Если отношения А и В не имеют общих имен атрибутов, то естественное соединение превращается в декартово произведение.

Специальные реляционные операции (7) Делением (DIVIDED BY) двух отношений, бинарного и унарного, является отношение, содержащее все значения одного атрибута бинарного отношения, которые соответствуют (в другом атрибуте) всем значениям в унарном отношении.

Дополнительные реляционные операции Операция расширения EXTEND A ADD expr AS Z; Результат: Отношение с заголовком, эквивалентным заголовку отношения А, расширенному новым атрибутом Z, который рассчитывается скалярным выражением expr для кортежа отношения А. Операция расширения обеспечивает возможность горизонтального или построчного вычисления.

Пример: Подсчитать количество поставок, сделанных каждым поставщиком. EXTEND Поставщики ADD COUNT ((Поставки RENAME П№ AS X) WHERE X= №) AS Кол_П;

Дополнительные реляционные операции. Операция подведения итогов SUMMARIZE A BY (A1,A2,…,An) ADD expr AS Z; Результат: Отношение с заголовком {A1,A2,…,An,Z} и с телом, содержащим все такие кортежи t, которые являются кортежами проекции отношения А по атрибутам A1,A2,…,An, расширенного значением для нового атрибута Z. Значение Z подсчитывается вычислением итогового значения expr по всем кортежам отношения А. Пример: SUMMARIZE Поставки BY(Д№) ADD SUM(Кол) AS Общ_кол

Пример: Подсчитать количество поставок, сделанных каждым поставщиком. SUMMARIZE Поставки BY (П№) ADD COUNT AS Кол_П;

Дополнительные реляционные операции. Возможные операции: Переименование имени поля: Детали RENAME Гор AS Гор_Д Присвоение: Поставки := Поставки MINUS (Поставки WHERE Кол = 0); Обновление: INSERT (Поставщики WHERE Гор_П=Минск ) INTO Temp; UPDATE (Поставщики WHERE Гор_П=Брест) СТАТУС<40; DELETE Поставщики WHERE Статус < 20;

Примеры использования реляционной алгебры для выражения словесных запросов в виде формул (1) Получить имена поставщиков, которые поставляют деталь Д2. ((Поставки JOIN Поставщики) WHERE Д№=’Д2’) [Имя_П];

Примеры использования реляционной алгебры для выражения словесных запросов в виде формул (2) Получить имена поставщиков, которые поставляют по крайней мере одну черную деталь. (((Детали WHERE Цв = ‘Черный’) JOIN Поставки) [П№] JOIN Поставщики) [Имя_П]; или (((Детали WHERE Цв = ‘Черный’) [Д№] JOIN Поставки) JOIN Поставщики) [Имя_П];

Примеры использования реляционной алгебры для выражения словесных запросов в виде формул (3) Получить имена поставщиков, которые поставляют все детали. ((Поставки [П№,Д№] DIVIDED BY Детали [Д№] JOIN Поставщики) [Имя_П];

Примеры использования реляционной алгебры для выражения словесных запросов в виде формул (4) Получить номера поставщиков, которые поставляют по крайней мере все те детали, которые поставляет поставщик П2. Поставки [П№,Д№] DIVIDED BY (Поставки WHERE Имя_П=’П2’) [Д№]

Примеры использования реляционной алгебры и SQL для выражения словесных запросов в виде формул (5) Получить имена поставщиков, которые не поставляют деталь Д2. ((Поставщики [П№] MINUS (Поставки WHERE Д№=’Д2’) [П№]) JOIN (Поставщики) [Имя_П]; SQL: SELECT DISTINCT Поставщики.[Имя_П] FROM Поставщики WHERE Поставщики.[П.№] NOT IN (SELECT Поставки.[П№] FROM Поставки WHERE Поставки.[Д№]=’Д2’);

Основы SQL (Structured Query Language)

Синтаксис SQL запятые используются для разделения компонентов списка параметров; точки используются для отделения имен таблиц от имен полей; точка с запятой ставится в конце инструкции Jet SQL; квадратные скобки используются для выделения имен полей только тогда, когда в именах используются пробелы или другие знаки пунктуации, не разрешенные в SQL; одинарная кавычка применяется для описания строчных переменных; символы * и ? используются для маскирования окончания или одного символа соответственно; символ # применяется для представления одной цифры в операторе LIKE.

Классификация операторов SQL Операторы определения данных – определяют содержимое реляционной базы данных в виде таблиц и представлений; Операторы манипулирования данных – используются для извлечения, вставки, обновления и удаления данных, содержащихся в таблицах и представлениях; Операторы управления данными – ограничивают доступ к данным.

Типы данных Категории Character string – Строки символов; Bit string – Строки битов; Exact numeric – Рациональные (целые и действительные) числа с плавающей десятичной точкой; Approximate numeric – Вещественные числа (с плавающей точкой); Date time – значения даты и времени; Interval – интервалы даты и времени.

Создание и обслуживание таблиц CREATE TABLE Проекты (Пр№ CHAR(3) NOT NULL PRIMARY KEY, ИмяПр CHAR(15) UNUQUE, Гор CHAR(20)); Ограничения на атрибуты: NOT NULL – не разрешает присваивать значения NULL; DEFAULT – задает значения по умолчанию; PRIMARY KEY – задает первичный ключ для таблицы; FOREIGN KEY (или REFERENCES) – задает внешний ключ; UNIQUE – не позволяет вводить в столбец повторяющиеся значения; CHECK – ограничивает с помощью логических выражений значения, которые могут добавляться в столбец.

Создание внешних ключей CREATE TABLE Поставки (П№ CHAR(3) NOT NULL REFERENCES Поставщики, Пр№ CHAR(5) NOT NULL REFERENCES Проекты, Д№ CHAR(3) NOT NULL REFERENCES Детали, Кол INTEGER DEFAULT ′???′ CONSTRAINT ключ PRIMARY KEY (П№ , Пр№, Д№));

Обеспечение целостности данных по ссылкам CREATE TABLE Поставки (П№ CHAR(3) REFERENCES Поставщики ON UPDATE CASCADE ON DELETE SET NULL, Пр№ CHAR(5) NOT NULL REFERENCES Проекты RESTRICT, Д№ CHAR(3) NOT NULL REFERENCES Детали, Кол INTEGER DEFAULT ′???′ CONSTRAINT ключ PRIMARY KEY (П№ , Пр№, Д№));

Обеспечение целостности атрибута CREATE TABLE Детали (Д№ CHAR(3) NOT NULL PRIMARY KEY, Имя_Д CHAR(15) UNUQUE, Цвет CHAR(10) CHECK (Цвет='Черный' OR Цвет ='Красный' OR Цвет ='Желтый' OR Цвет ='???'), Вес INTEGER, Гор CHAR(20));

Редактирование таблицы

Управление данными Доступ к данным Виды полномочий: SELECT, UPDATE, ALL GRANT UPDATE ON Поставки TO USER1; Полномочия для всех пользователей: GRANT UPDATE ON Поставки TO PUBLIC; Удаление полномочий: REVOKE UPDATE ON Поставки FROM USER1;

Запрос на выборку SELECT [ALL/DISTINCT] [TOP n [PERCENT]] список полей FROM имена таблиц [WHERE условие отбора] [ORDER BY столбцы сортировки [ASC/DESC]]; ALL – включает все строки, соответствующие указанным далее условиям отбора; DISTINCT (ключевое слово из ANSI SQL-92) – исключает строки с повторяющимися данными на основе только данных результирующего набора записей; TOP n [PERCENT] ограничивает количество записей в результирующей таблице первыми n или n% набора.

Запрос на выборку Пример SELECT Имя_Д, Вес FROM Детали WHERE Вес>500 ORDER BY [Вес] DESC;

Статистические функции SELECT статистическая функция (имя поля) AS заголовок поля [, список полей] FROM имена таблиц [WHERE условие отбора] GROUP BY условие группировки [HAVING условие для результата] [ORDER BY столбцы сортировки];

Статистические функции. Пример 1 Подсчитать общее количество деталей: SELECT SUM(Поставки.Кол) FROM Поставки; Можно рассчитать несколько статистических выражений: SELECT MIN(Кол), MAX(Кол), SUM(Кол), AVG(Кол) FROM Поставки;

Статистические функции. Пример 2 Количество кортежей в отношении: SELECT COUNT (*) AS Кол_кортежей FROM Поставки;

Статистические функции. Пример 3 Применение статистических функций к отдельным группам кортежей: SELECT Пк.ПN, SUM(Пк.Кол) FROM Поставки AS Пк GROUP BY Пк.ПN;

Статистические функции. Пример 3 Ограничения на результат: SELECT Пк.ПN, SUM (Пк.Кол) FROM Поставки AS Пк GROUP BY Пк.ПN HAVING COUNT(*)>2;

Создание соединений (1) Произведение двух отношений: SELECT * FROM Проекты, Поставки; Соединение: SELECT * FROM Проекты, Поставки WHERE Проекты.ПрN=Поставки.ПрN;

Создание соединений (2) Можно соединить произвольное число отношений: SELECT DISTINCT П.Имя_П, Д.Имя_Д, Пр.Имя_Пр, Пк.Кол FROM Поставщики AS П, Детали AS Д, Проекты AS Пр, Поставки AS Пк WHERE Д.ДN=Пк.ДN AND П.ПN=Пк.ПN AND Пр.ПрN=Пк.ПрN AND Пк.Кол>500;

Создание соединений (3) SELECT список полей FROM имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица ON условие связи [WHERE условие отбора] [ORDER BY столбцы сортировки]; Тип соединения: INNER – соединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения; LEFT (RIGHT) –левое внешнее соединение включает все записи из первой (левой) таблицы и присоединяет к ним записи из второй таблицы, если связующие поля содержат одинаковые значения. Правое внешнее соединение включает все записи из второй (правой) таблицы и присоединяет к ним записи из первой таблицы, если связующие поля содержат одинаковые значения. Конструкция ON условие связи описывает связь между полями соединений.

Создание соединений Пример 1 Соединение отношений Проекты и Поставки базы данных Проекты-Поставщики-Детали SELECT DISTINCT Пр.Имя_Пр, Пр.Гор, Пк.ДN, Пк.Кол FROM (Проекты Пр INNER JOIN Поставки Пк) ON Пр.ПрN=Пк.ПрN;

Создание соединений Пример 2 Какие детали поставляются несколькими поставщиками? SELECT F.ПN, S.ПN, F.ДN FROM Поставки AS F, Поставки AS S WHERE F.ДN=S.ДN; Добавим: AND F.ПN<>S.ПN и DISTINCT

Вложенные запросы SELECT список полей FROM список таблиц WHERE [имя таблицы.] имя поля IN (SELECT оператор выборки [GROUP BY условие группировки] [HAVING условие отбора]) [ORDER BY столбцы сортировки];

Вложенные запросы Пример 1 Найти номера поставщиков, поставляющих хотя бы одну черную деталь. SELECT Пк.ПN FROM Поставки Пк WHERE Пк.ДN IN (SELECT Д.ДN FROM Детали Д WHERE Д.Цвет='Черный');

Вложенные запросы Пример 2 Можно добавить имена поставщиков: SELECT П.Имя_П FROM Поставщики П WHERE П.ПN IN (SELECT Пк.ПN FROM Поставки Пк WHERE Пк.ДN IN (SELECT Д.ДN FROM Детали Д WHERE Д.Цв='Черный'));

Вложенные запросы Пример 3 Такой же результат можно получить соединением: SELECT DISTINCT П.Имя_П FROM Поставщики П, Поставки Пк, Детали Д WHERE П.ПN=Пк.ПN AND Пк.ДN=Д.ДN AND Д.Цв='Черный'; Как лучше?

Вложенные запросы Пример 4 Проверка на существование : SELECT * FROM Поставщики П WHERE П.ПN NOT IN (SELECT Пк.ПN FROM Поставки Пк);

Запрос на объединение SELECT оператор выборки UNION SELECT оператор выборки [GROUP BY условие группировки] [HAVING итоговое условие] [UNION SELECT оператор выборки [GROUP BY условие группировки] [HAVING итоговое условие]] [UNION …] [ORDER BY столбцы сортировки];

Запрос на объединение Пример SELECT Имя_П AS Наименование FROM Поставщики WHERE Гор=’Минск’ UNION SELECT Имя_Пр AS Наименование FROM Проекты WHERE Гор=’Минск’ ORDER BY Наименование;

Оператор EXISTS Оператор EXISTS в предложении WHERE выполняет проверку на существование данных, которые удовлетворяют критериям соответствующего вложенного запроса, и возвращает булево значение «истина» или «ложь». Пример. Найти имена поставщиков, которые поставляют деталь Д1: SELECT DISTINCT П.Имя_П FROM Поставщики AS П WHERE EXISTS (SELECT * FROM Поставки AS Пк WHERE Пк.ПN=П.ПN AND Пк.ДN='Д1');

Два решения задачи: Найти номера деталей, поставляемых поставщиком из города, название которого начинается с буквы М SELECT DISTINCT Пк.ДN FROM Поставки AS Пк WHERE Пк.ПN IN (SELECT П.ПN FROM Поставщики AS П WHERE П.Гор LIKE 'М*'); SELECT DISTINCT Пк.Д№ FROM Поставки Пк WHERE EXISTS (SELECT * FROM Поставщики П WHERE П.П№ = Пк.П№ AND Гор LIKE ′М*′);

Можно добавить сведения из третьей таблицы: SELECT DISTINCT Д.Имя_Д FROM Детали AS Д WHERE EXISTS (SELECT DISTINCT Пк.ДN FROM Поставки AS Пк WHERE EXISTS (SELECT * FROM Поставщики AS П WHERE П.ПN=Пк.ПN AND Гор LIKE 'М*') AND Д.ДN=П.ДN);

Реализация операции пересечения Пересечение таблиц Детали и Поставщики по полю Гор SELECT DISTINCT Д.Гор FROM Детали AS Д WHERE EXISTS (SELECT * FROM Поставщики П WHERE Д.Гор=П.Гор);

Реализация операции вычитания (1) Разность таблиц Детали и Поставщики по полю Гор SELECT DISTINCT Д.Гор FROM Детали Д WHERE NOT EXISTS (SELECT * FROM Поставщики П WHERE Д.Гор=П.Гор);

Реализация операции пересечения (2) Разность таблиц Детали и Поставщики по полю Гор SELECT DISTINCT Д.Гор FROM Детали Д WHERE (SELECT COUNT (*) FROM Поставщики П WHERE Д.Гор = П.Гор) >0;

Реализация операции деления Получить номера поставщиков, поставляющих все детали SELECT DISTINCT Пк.ПN FROM Поставки AS Пк WHERE NOT EXISTS (SELECT Д.ДN FROM Детали AS Д WHERE NOT EXISTS (SELECT Пк1.ДN FROM Поставки AS Пк1 WHERE Пк1.ПN=Пк.ПN AND Пк1.ДN=Д.ДN));

Запросы на изменение записей Добавление записей: INSERT INTO таблица-получатель SELECT список полей FROM таблица-источник; [WHERE условие удаления]; Удаление записей: DELETE FROM имя таблицы [WHERE условие удаления]; Создание таблицы: SELECT список полей INTO новая таблица FROM исходная таблица [WHERE условие выбора]; Обновление: UPDATE имя таблицы SET имя_поля_1=значение [,имя_поля_2=значение[,…]] [WHERE условие обновления];

Перекрестные запросы TRANSFORM статистическая функция (имя поля) [AS наименование] SELECT список полей FROM имя таблицы PIVOT поле [IN (значение_1[, значение_2[, ...]])];

Перекрестные запросы Пример Представить данные о количествах деталей, поставленных каждым поставщиком. TRANSFORM Sum(Пк.Кол) SELECT Пк.ПN, Sum(Пк.Кол) AS [ВСЕГО:] FROM Поставки AS Пк GROUP BY Пк.ПN PIVOT Пк.ДN;

Проектирование баз данных. Проблемы, которые необходимо избегать Аномалии обновления – из-за избыточности данных при их обновлении необходимо просматривать все данные, тем не менее, может возникнуть ситуация, когда не все данные будут обновлены (потенциальная противоречивость данных). Аномалии включения – возможна ситуация, когда в базу нельзя ввести данные, прежде чем не будут получены и введены некоторые дополнительные сведения. Аномалии удаления – обратная проблема может возникнуть при удалении некоторых данных (возможна потеря полезной информации). Не минимизировано количество Null-значений. Так же как избыточность, неопределенные значения являются источниками потенциальных проблем в реляционных базах данных, так как невозможно определить, что они означают. Поэтому их использование желательно свести к минимуму.

Нормализация отношений Нормализация – это разбиение (или декомпозиция) таблицы на две или более, обладающих лучшими свойствами при добавлении, изменении и удалении данных; Нормализованное отношение; Нормальные формы.

Функциональные зависимости (1) Пусть X и Y – произвольные подмножества множества атрибутов отношения R. Y функционально зависит от X тогда и только тогда, когда каждое значение множества X связано в точности с одним значением множества Y. Обозначение: XY

Функциональные зависимости (2) Тривиальные зависимости – те, которые не могут не выполняться: {П№,Д№}П№ Неприводимые зависимости: Атрибут В неприводимо зависим от составного атрибута А, если он функционально зависит от А и не зависит функционально от любого подмножества атрибута А.

Функциональные зависимости (3) Диаграмма функциональных зависимостей для учебной базы данных «Проекты, Поставщики, Детали»

Первая нормальная форма (1) Таблица находится в первой нормальной форме (1НФ) тогда и только тогда, когда ни одна из ее строк не содержит в любом своем поле более одного значения и ни одно из ее ключевых полей не пусто.

Первая нормальная форма (2) Универсальное отношение

Первая нормальная форма (3) Диаграмма функциональных зависимостей

Первая нормальная форма (4) Аномалии Вставка (Insert). Нельзя вставить данные о поставщике (П5), не указав деталь (Null-значение в ключевом поле недопустимо). Удаление (Delete). При удалении некоторого кортежа приходится удалять слишком много другой информации (удаление информации о поставке удаляет информацию о поставщике). Обновление (Update). Избыточная информация может привести к несовместимым результатам. Если поставщик П1 переехал в другой город, а обновление сделано не во всех кортежах, то база данных будет содержать противоречивую информацию.

Вторая нормальная форма (1) Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все ее поля, не входящие в первичный ключ, связаны неприводимой зависимостью с первичным ключом.

Вторая нормальная форма (2) Диаграмма функциональных зависимостей отношения, приведенного к 2НФ

Вторая нормальная форма (3) Преобразованные отношения

Вторая нормальная форма (4) Аномалии Вставка – нельзя включить данные о некотором городе и его статусе, пока в нем нет поставщика. Удаление – при удалении поставщика теряется информация о статусе города. Обновление – статус городов повторяется несколько раз. При изменении статуса города приходится просматривать множество строк, чтобы исключить получение противоречивого результата, но вероятность ошибки остается.

Транзитивные зависимости Если выполняются функциональные зависимости АВ и ВС, то выполняется также и функциональная зависимость АС. Возможная декомпозиция:

Третья нормальная форма (1) Отношение находится в третьей нормальной форме (3НФ) тогда и только тогда, когда оно находится в 2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Другими словами: Таблица находится в третьей нормальной форме (3НФ), если она находится в 2НФ и ни одно из ее неключевых полей не зависит функционально от любого другого неключевого поля.

Третья нормальная форма (2) Варианты декомпозиции Функциональные зависимости: П№Город П№ Статус ГородСтатус Варианты декомпозиции: А: (П№, Город) и (Город, Статус) В: (П№, Город) и (П№, Статус) С: (П№, Статус) и (Город, Статус)

Третья нормальная форма (3) Декомпозиция с сохранением зависимости (Риссанен (Rissanen)). Проекции R1 и R2 отношения R независимы тогда и только тогда, когда каждая функциональная зависимость в отношении R является логическим следствием функциональных зависимостей в проекциях R1 и R2; общие атрибуты проекций R1 и R2 образуют потенциальный ключ, по крайней мере, для одной из них. Т.е., отношение R{A,B,C}, удовлетворяющее функциональным зависимостям A→B и B→C, следует разбивать на проекции {A,B} и {B,C}, а не {A,B} и {A,C}

Нормальная форма Бойса-Кодда (1) (Bouce-Codd) Определение 3НФ не корректно, если отношение имеет два или более потенциальных ключа; два потенциальных ключа являются сложными и они перекрываются

Нормальная форма Бойса-Кодда (2) Отношение находится в нормальной форме Бойса-Кодда (НФБК) тогда и только тогда, когда детерминанты являются потенциальными ключами. Или Таблица находится в нормальной форме Бойса-Кодда (НФБК), тогда и только тогда, когда любая функциональная зависимость между ее полями сводится к неприводимой функциональной зависимости от потенциального ключа.

Нормальная форма Бойса-Кодда (3) Пример отношения в НФБК Отношение Поставщик (П№, Имя_П, Статус, Город) с неперекрывающимися ключами

Нормальная форма Бойса-Кодда (4) Пример Отношение СДП с атрибутами (С,Д,П). Ограничения: Каждый студент изучает данный предмет у одного преподавателя ({С,Д}П); Каждый преподаватель ведет только один предмет (но каждый предмет может преподаваться несколькими преподавателями) (ПД). Есть два перекрывающихся ключа – {С,Д} и {С,П}

Схема учебной базы данных