Лекции по системам управления базами данных - файл n1.doc

Лекции по системам управления базами данных
Скачать все файлы (140 kb.)

Доступные файлы (1):
n1.doc140kb.18.02.2014 22:05скачать

n1.doc

1   2   3
Модель сущность-связь

Наиболее распространенные средства проектирования данных – модели сущность-связь (ER-модели).

Впервые введены в 1970 Ченом. Включают аналоги всех семантических объектов, описанные в таблице.

Базовые понятия ER-диаграммы:

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

Слабая сущность – существование сущности зависит от другой сущности.

Сильная – не является слабой.

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

Экземпляр атрибута – характеристика отдельного элемента множества. Определяется типом характеристики и ее значением – значение атрибута. В ER-модели атрибут ассоциируется с сущностью.

2. Связь – поименованная ассоциация между двумя сущностями. Мощность связи – отношение количества экземпляров одной сущности к:

Один к одному, один ко многим, многие ко многим.

Один к одному - каждый экземпляр сущности А соответствует одному и только одному экземпляру сущности В.

Один ко многим – один экземпляр сущности А соответствует нескольким экземплярам сущности В. Каждый экземпляр сущности В соответствует только одному экземпляру сущности В.

Многие ко многим – несколько экземпляров сущности А соответствуют нескольким экземплярам сущности В.

Правила моделирования: 1. Можно использовать только конструктивные элементы.

2. Отдельные блоки соединены ребрами и дугами. Неориентируемые – для трех и более сущностей .2 сущности – направленные дуги.

3. каждый компонент информации может быть только одним элементом.

4. Не нужно допускать дублирование атрибутов во многих сущностях.

5. на ER-диаграмме указываются только связи между сущностями.

6. у каждой сущности существует атрибут.

7. атрибуты есть не только у сущностей, но и у связей.

8. каждая сущность имеет один тип, однако у некоторых сущностей может быть несколько типов.

Пр: если некоторый работник является программистом и все программисты работники, тогда программист - подтип работник.

Тип сущности работник – супертип сущности программист.

Программисты автоматически обладают всеми свойствами работников, но обратное не верно.

Аналогично сущность программист автоматически участвует в связи работник, но обратное не верно.

Поэтому подтип наследует свойства и связи супертипа.

ER-диаграмма используется в качестве основы в любой методике проектирования.

SQL (структурированный язык запросов).

История развития языка

Стандартный язык запросов по работе с реляционными базами данных.

Работа над языков началась в 1970 г, связанна с появлением реляционной модели.

1975г – прототипы, основанные на SQL.

1980гг – первые коммерческие продукты.

1983г – работа комитета по стандартизации SQL.

SQL 86, SQL 89, SQL 2, SQL 3 (в начале 90х)

200г – SQL 3, начало использования.

SQL в коммерческих организациях:

1979г – появление Oracle

1981-82гг – ДВ2

1984г – Informix

1986г – Sybase

Соответствие языку SQL, но различие диалектов.

SQL-system:

1989г – первый международный стандарт. Представляет собой очень общие черты языка, допускает очень широкое толкование элементов языка. Отсутствовали разделы: манипулирование схемой БД, динамический SQL. Стандарт синтаксиса и семантики некоторых операторов (пр: операторы выборки).

1992г – стандарт связан с SQL 2. Добавлены элементы: расширено манипулирование таблицами, добавлено манипулирование системой БД, рассмотрена возможность управления доменами, новые типы данных, новые функции. Возможности подключения к БД, развитие динамического SQL.

Стандарт SQL 3 появился несколько позже. В него добавлены триггеры, абстрактные типы данных, расширены модели транзакций.

Не относится к традиционным языкам программирования, так как не содержит традиционные операторы, содержит только набор стандартных операторов доступа к данным, хранящимся в БД.

Операторы SQL встраиваются в базовый язык программирования, которым может быть любой стандартный язык.

Структура SQL

Реляционная алгебра отличается от SQL тем, что в реляционной алгебре рассматриваются запросы к БД, а в SQL кроме запросов существуют и операторы (полный язык).

Одни операторы соответствуют языку описания данных (DDL), а другие предназначены для управления (DMP).

DDL – Date Definition Language

DMP – Date Main Pulation

DDL:

Оператор: CREATE TABLE. Смысл: создать таблицу. Действие: создание таблицы.

Оператор: DROP TABLE. Смысл: удалить таблицу. Действие: удаление таблицы.

Оператор: ALTER TABLE. Смысл: изменение таблицы. Действие: изменение структуры создавшейся таблицы.

Оператор: CREATE VIEW. Смысл: создает представление. Действие: создает виртуальную таблицу, соответствующую некоторому запросу.

Оператор: ALTER VIEW. Смысл: изменяет представление. Действие: Изменяет ранее созданное представление.

Оператор: DROP VIEW. Смысл: удаляет представление. Действие: Удаляет ранее созданное представление.

Оператор: CREATE INDEX. Смысл: Создать индекс. Действие: Создает индекс для обеспечения более быстрого доступа по атрибутам таблицы.

Оператор: DROP INDEX. Смысл: Удалить индекс. Действие: Удаляет индекс.

DMP:

Оператор: DELETE. Смысл: Удалить строки. Действие: удалить одну или несколько строк, соответствующую условиям фильтрации из базовой таблицы.

Оператор: INSERT. Смысл: вставить строку. Действие: Вставить одну строку в базовой таблице. Допускает модификации.

Оператор: UPDATE. Смысл: Обновить строку. Действие: Обновить значение одного или нескольких столбцов в одной или нескольких строках соответствующих условиям фильтрации.

DQL – Data Query Language

Оператор: SELECT. Смысл: Выбрать строки. Действие: заменяет все операторы реляционной алгебры и позволяет сформировать результирующее отношение, соответствующее запросу.

Средства управления транзакциями

Оператор: COMMIT. Смысл: завершить транзакцию. Действие: завершает комплексную взаимосвязанную обработку информации, объединенной в транзакцию.

Оператор: ROLLBACK. Смысл: откатить транзакции. Действие: отменяет изменения, проведенные в ходе проведения транзакции.

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

Средства администрирования данными:

Оператор: ALTER DATABASE. Смысл: изменить БД. Действие: изменяет набор основных объектов БД.

Оператор: ALTER DBAREA. Смысл: изменяет область хранения БД. Действие: изменяет ранее созданную область хранения БД.

Оператор: ALTER PASSWORD. Смысл: изменяет пароль. Действие: изменяет пароль.

Оператор: CREATE DATABASE. Смысл: создать БД. Действие: создает новую БД, определив основные параметры для нее.

Оператор: CREATE DBAREA. Смысл: создает новую область хранения. Действие: создать новую область хранения и сделать ее доступной для размещения данных.

Оператор: DROP DATABASE. Смысл: удалить БД. Действие: удалить существующую БД, если вы имеете право выполнить это действие.

Оператор: DROP DBAREA. Смысл: удалить область хранения БД. Действие: Удалить существующую область хранения, если в ней на настоящий момент не располагаются активные данные.

Оператор: GRANT. Смысл: предоставляет права. Действие: представляет права доступа на ряд действий над некоторым объектом БД.

Оператор: REVOKE. Смысл: лишить прав. Действие: Лишает права доступа к некоторым объектам или некоторым действиям над объектом БД.

Программный SQL

Оператор: DECLARE. Смысл: лишить прав. Действие: задает некоторое имя и определяет связанный с ним запрос к БД, который соответствует виртуальному набору данных.

Оператор: OPEN. Смысл: открыть курсор. Действие: формирует виртуальный набор данных, соответствующий описанию указанного курсора и текущему состоянию БД.

Оператор: FETCH. Смысл: Считать строку из множества строк, определенных курсором. Действие: считывает очередную строку, заданную параметром команды из виртуального набора данных соответствующих открытому курсору.

Оператор: CLOSE. Смысл: закрыть курсор. Действие: прекращает доступ к виртуальному набору данных, соответствующих указанному курсору.

Оператор: PREPARE. Смысл: подготовить оператор SQL к динамическому выполнению. Действие: сгенерируется план выполнения запроса, соответствующий заданному оператору SQL.

Оператор: EXECUTE. Смысл: выполняет оператор SQL, который ранее был подготовлен к динамическому выполнению. Действие: выполняет ранее подготовленный план запроса.

Достоинства последнего стандарта SQL:

1. повсеместная распространенность;

2. быстрое обучение в простых случаях;

3. можно осуществить связь с различными языками;

4. поддержка DRS (технология связи данных).

Недостатки: 1. Несоответствие реляционной модели данных (не существует требований первичного ключа, наличие дубликатов).

2. Недостаточно продуманный механизм неопределенных значений.

3. сложность формулировок и громоздкость.

Оператор выбора SELECT

Синтаксис: SELECT [ALL/DISTINCT] <список полей>|*

FROM <список таблиц>

[WHERE <предикат – условие выборки>]

[GROUP BY <список полей результата>]

[HAVING <предикат – условие для группы>]

[ORDER BY <список полей – упорядочить вывод>]

All – означает, что в результирующий набор строк, включаются все строки, удовлетворяющие условиям запроса.

DISTINCT – означает, что в результирующий набор включаются только различные строки, то есть дубликаты строк результата не включаются в набор.

* - в результирующий набор включаются все столбцы из исходных таблиц запроса.

[ FROM … - задается перечень исходных отношений или таблиц запроса.

[ WHERE … - задаются условия отбора строк результата или условия соединения кортежа исходных таблиц.

[ GROUP BY … - задается список полей группировки.

[ HAVING … - задаются предикаты условия, которые накладываются на каждую группу.

[ ORDER BY … - задается список полей упорядочивания результата, то есть список полей, которые определяют порядок сортировки в результирующих отношениях.

В выражении условия раздела WHERE могут быть использованы следующие предикаты:

1. предикаты сравнения (=, !=, (><), >, <, >=, <=);

2. предикат between A and В принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон включая границы диапазона.

Предикат: not between А and В, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал включая его границы.

3. Предикат IN (…) – предикат вхождения в множество. Истинен тогда, когда сравниваемое значение попадает в заданное множество значений. Множество значений задается перечислением или встроенным подзапросом.

Not in () – истинен тогда, когда сравниваемое значение не входит в заданное множество.

4. LIKE – предикат сравнения с образцом. Требует задания шаблона, с которым сравнивается заданное значение. Предикат будет истинным, если сравниваемое значение соответствует шаблону.

Not like – противоположный смысл.

В шаблоне предиката LIKE могут быть включены специальные символы: _ - используется для обозначения любого одиночного символа.

% - для обозначения любой произвольной последовательности символов.

Другие символы обозначают сами себя.

5. IS NULL - предикат сравнения с неопределенным значением. Неопределенное значение для реляционной модели рассматривается как значение, неизвестное на данный момент времени. При появлении дополнительной информации в любой момент времени может быть заменено некоторое конкретное значение. При сравнении неопределенных значений не действуют стандартные правила сравнения. Одно неопределенное значение не бывает равным другому.

Для выявления равенства значения некоторого атрибута неопределенному принимают стандартные предикаты:

<имя атрибута> IS NUL

<имя атрибута> IS NOT NULL

Если указанный атрибут имеет неопределенное значение, то предикат IS NULL принимает значение «истина», а IS NOT NULL – «ложь» и наоборот.

Введение понятия NULL превращает двузначную логику в трехзначную, то есть появляется новая таблица истинности:

A B not A A ^ B A v B

T T F T T

T F F F T

T NULL F NULL T

F T T F T

F F T F F

F NULL T F NULL

NULL T NULL NULL T

NULL F NULL F NULL

NULL NULL NULL NULL NULL

6. Предикат существования – EXIST, предикат несуществования – NOT EXIST. Относятся к встроенным подзапросам.

Рассмотрим детально первые три строки оператора SELECT:

SELECT – ключевое слово, которое сообщает СУБД, что это команда запроса.

Далее может следовать способ выборки с удалением дубликатов (DISTINCT) или без удаления (ALL). Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблицы или символом * (для выбора всех столбцов).

FROM – ключевое слово, после следует пробел и далее перечисляются через запятую имена таблиц, используемых в качестве источника информации. Если используется более одного имени таблицы, то подразумевается что над перечисленными таблицами осуществляется операция «декартово произведение».

Таблицам можно присвоить имена-псевдонимы. Это полезно для осуществления операции соединения таблицы с собой или для доступа с подзапроса и текущей записи запроса.

Все последующие разделы оператора SELECT необязательны, то есть все последующие операции рассматриваются как декартово произведение.

Пр: SELECT * FROM R1,R2 – декартово произведение таблиц R1 и R2.

Рассмотрим БД, которое моделирует сдачу сессии некоторым учебным заведением:

Таблицы - R1,R2,R3.

R1=(ФИО, дисциплина, оценка); R2=(ФИО, группа); R3=(группа, дисциплина).

R1 – таблица

ФИО Дисциплина Оценка

Ф1 УД 4

Ф2 УД 5

Ф3 УД 3

Ф1 ССУБД Null

Ф2 ССУБД 3

Ф3 ССУБД 3

R2 – таблица

ФИО Группа

Ф1 07ИСТ

Ф2 06ИСТ

Ф3 05ИСТ

R3 – таблица

Группа Дисциплина

07ИСТ УД

06ИСТ УД

06ИСТ ССУБД

05ИСТ УД

SELECT DISTINCT Группа FROM R3

SELECT ФИО FROM R1 WHERE дисциплина=«УД» AND оценка=«5» - список студентов, сдавших УД на 5.

SELECT ФИО, дисциплина FROM R2,R3 WHERE R2.группа=R3.группа – список студентов с указанием дисциплин, которые сдают экзамены.

Отрицание условий с помощью операции отрицания

Для всех рассматриваемых типов операций можно построить их отрицание, чтобы рассмотреть противоположные условия.

Ключевое слово NOT обращает смысл операции, с которой оно используется. Ключевое слово not используется с операциями следующим образом:

NOT BETWEEN

IS NOT NULL

NOT IN

NOT EXIST

NOT LIKE

NOT UNIQUE

Неравенство

Есть возможность проверки неравенства при помощи операции отрицания. При проверке неравенства фактически отрицается операция проверки равенства.

Другой метод проверки доступен в некоторых реализациях SQL.

Пр:

WHERE SALARY o ‘20000’

WHERE SALARY !=’20000’

Во втором случае для отрицания равенства используется восклицательный знак. В некоторых SQL в дополнение к стандартному знаку неравенства используется восклицательный знак в совокупности со знаком равенства.

NOT BETWEEN

WHERE SALARY NOT BETWEEN ‘20000’ AND ‘30000’ – зарплата не должна находиться между 20000 и 30000, включая крайние значения диапазона.

Пр: SELECT *

FROM PRODUCTS_TBL WHERE NOT BETWEEN 5.95 AND 14.5;

NOT IN

Любое из возвращенных значений зарплаты не должно равняться какому-нибудь значению из заданного списка.

WHERE SALARY NOT IN (‘20000’,’30000’,’40000’)

Пр: SELECT *

FROM PRODUCTS_TBL WHERE PROD_ID NOT IN (‘*13’,’9*’,’87’,’119’);

NOT LIKE

WHERE SALARY NOT LIKE ‘200%’ – любое значение не начинающееся с 200

WHERE SALARY NOT LIKE ‘%200%’ – любое значение не имеющее ни в какой позиции 200

WHERE SALARY NOT LIKE ‘_00%’ – любое значение, не имеющее 00 во второй и третьей позициях

WHERE SALARY NOT LIKE ‘2_%_%’ – любое значение, не начинающееся с двойки и имеющее как минимум три символа

Пр: SELECT PROD_DESC

FROM PRODUCTS_TBL WHERE PROD_DESC NOT LIKE ‘П%’;

IS NOT NULL

Используется, когда нужно убедиться, что значение не пустое.

WHERE SALARY IS NOT NULL – выбрать только строки с непустыми значениями

Пр: SELECT EMP_XD,LAST_NAME,FIRST_NAME,PAGER

FROM EMPLOYEEJTBL WHERE PAGER IS NOT NULL;

NOT EXIST

WHERE NOT EXIST (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE EMPLOYEE_ID=-333333) – проверка отсутствия EMPLOYEE_ID со значением -333333 в таблице EMPLOYEE_TBL

Пр: SELECT MAX (COST) FROM PRODUCTS_TBL WHERE NOT EXIST (SELECT COST FROM PRODUCTS_TBL WHERE COST>100);

NOT UNIQUE

WHERE NOT UNIQUE (SELECT SALARY FROM EMPLOYEE_TBL) – проверка SALARY на наличие неуникальных значений

Арифметические операции

+ (сложение)

- (вычитание)

* (умножение)

/ (деление)

Сложение

SELECT SALARY+BONUS FROM EMPLOYEE_PAY – значение SALARY складывается со значением BONUS для каждой строки данных

SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY+BONUS>’40000’; - выбор строк для которых значение SALARY+BONUS превышает 40000

Вычитание

SELECT SALARY-BONUS FROM EMPLOYEE_PAY_TBL; – значение BONUS вычитается из значения SALARY

SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY-BONUS>’40000’;

Умножение

SELECT SALARY*10 FROM EMPLOYEE_PAY_TBL; - значение SALARY умножается на 10

SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY*10>’40000’;

Комбинирование арифметических операций

При комбинировании сначала выполняются операции умножения и деления, затем – сложения и вычитания. Можно управлять порядком выполнения операций с помощью скобок. Заключенное в скобки выражение означает необходимость рассматривать выражение как единый блок.

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

SELECT SALARY*10+1000 FROM EMPLOYEE_PAY_TBL WHERE SALARY>’20000’;

SELECT SALARY/52+BONUS FROM EMPLOYEE_PAY_TBL;

SELECT (SALARY-1000+BONUS)/52*1.1 FROM EMPLOYEE_PAY_TBL

Странное выражение:

SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY
1   2   3
Учебный текст
© perviydoc.ru
При копировании укажите ссылку.
обратиться к администрации