Правила формальной оптимизации
1. Операция проекции должна выполняться раньше произведения или соединения.
2. Операция селекции должна выполняться раньше произведения или соединения.
3. Операции проекции и селекции должны выполняться совместно.
Проекция и селекция уменьшают объем результата, а произведения и соединение – увеличивают.
Рекомендуемая литература
1. Гарсиа-Молина Гектор, Ульман Джеффри, Уидом Дженнифер. Системы баз данных. Полный курс.: Пер с англ. – М.: Издательский дом «Вильямс», 2003. – 1088 с.
2. Дейт К. Дж. Введение в системы баз данных, 8-е издание: Пер. с англ. – СПб.: Издательский дом «Вильямс», 2005. – 1328 с.
3. Коголовский М.Р. Энциклопедия технологий баз данных: Эволюция технологий; Технологии и стандарты; Инфраструктура; Терминология – М.: Финансы и статистика, 2002. – 800 с.
Методические рекомендации
по выполнению лабораторной работы № 2
В качестве CASE-средства при проектировании схемы базы данных необходимо использовать Oracle Designer или CASE Studio.
При работе с Oracle Designer после запуска в диалоговом окне необходимо ввести имя пользователя и пароль, далее выбрать свое приложение в списке.
Для выполнения задания необходимо знакомство со следующим набором приложений из пакета Oracle Designer:
· Entity Relationship Diagrammer – создание и редактирование ER-диаграммы;
· Database Desing Transformer – трансформатор ER-диаграммы в реляционную модель (Server Model)
· Design Editor – просмотр и модификация реляционной модели (Server Model)
· Generate Database from Server Model – генерация файла с SQL инструкции для создания физической модели данных
CASE Studio при работе с ER-диаграммами поддерживаtт стандарт IDEF1X. При создании новой модели данных в CASE Studio следует задать, для какой СУБД она проектируется, т.к. приложение имеет возможность построения полной физической модели базы данных с использованием индивидуальных свойств каждой БД: типы и свойства атрибутов (стандартные БД и пользователя), возможности описания ключей (первичные и внешние), связей, условий соблюдения ссылочной целостности, пользователей и их групп (ролей), возможности написания хранимых процедур и пр. Для выполнения конверсии физической модели для другой СУБД (опция Database Convertion) с сохранением в виде копии. Для генерации файла со скриптом для создания физической модели данных требуется воспользоваться опцией Generate script.
Таблица 1. Элементы ER-диаграммы в Oracle Designer
| Сущности | |
| Сущность |
| Сущность – супертип (Язык) с подтипами (Русский, Иностранный) |
| Типы связей | |
| связь (1,0):(N,1) |
| связь (1,0):(N,0) |
| связь (1,1):(N,1) |
| связь (1,1):(1,1) |
| связь (N,0):(N,0) |
| Обозначения для свойств атрибутов | |
| # | Первичный ключ |
| * | Обязательный атрибут (NOT NULL) |
| O | Необязательный атрибут (NULL) |
Таблица 2. Элементы ER-диаграммы в CASE Studio
| Сущности | |
| Сущность |
| Слабая сущность |
| Типы связей | |
| Неидентифицирующая (0,N):(1,1) |
| Неидентифицирующая (1,N):(1,1) |
| Неидентифицирующая (1,1):(1,1) |
| Неидентифицирующая (0,1):(0,1) |
| Неспецифическая связь (N:N) |
| Идентифицирующая (1,1):(1,N) |
| Идентифицирующая (1,1):(0,N) |
| Обозначения для свойств атрибутов | |
| (PK) | Первичный ключ |
| (FK) | Внешний ключ |
Рекомендуемая литература
1. Коголовский М.Р. Энциклопедия технологий баз данных: Эволюция технологий; Технологии и стандарты; Инфраструктура; Терминология – М.: Финансы и статистика, 2002. – 800 с.
2. Кренке Д. Теория и практика построения баз данных, 9-е издание: Пер. с англ. – СПб.: «Питер», 2005. – 864 с.
3. Питер Колетски, Д-р Поль Дорси. Oracle Desiner. Настольная книга пользователя, 2-е издание – М.: Издательство «ЛОРИ», 1999. – 592 с.
4. Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. Базы данных: Учебник для высших учебных заведений, 5-е издание – М.: Бином-Пресс; СПб.: КОРОНА принт, 2006. – 736 с.
5. http://www.idefinfo.ru (описания стандартов проектирования)
6. http://www.intuit.ru/department/database/basedbw/3/3.html (описание CASEStudio).
7. http://www.interface.ru/oracle/des2000x.html (Описание Oracle Designer).
Методические рекомендации
по выполнению лабораторной работы № 3
Таблица 3. Инструкции языка SQL
| Вид | Инструкция | Назначение |
| Data Definition Language (DDL) | CREATE TABLE | Создание таблицы |
| DROP TABLE | Удаление таблицы | |
| ALTER TABLE | Изменение структуры таблицы | |
| CREATE INDEX | Создание индекса | |
| DROP INDEX | Удаление индекса | |
| CREATE VIEW | Создание представления | |
| DROP VIEW | Удаление представления | |
| Data Manipulation Language (DML) | SELECT | Выборка записей |
| UPDATE | Изменение записей | |
| INSERT | Вставка записей | |
| DELETE | Удаление записей |
Таблица 4. Агрегирующие функции
| Агрегирующая функция | Результат | Примечание |
| SUM([DISTINCT] выражение) | Сумма [различных] значений | только для числовых выражений, NULL значения игнорируются |
| AVG([DISTINCT] выражение) | Средняя величина [различных] значений | только для числовых выражений, NULL значения игнорируются |
| COUNT([DISTINCT] выражение) | Количество [различных] ненулевых значений | для всех типов выражений, NULL значения игнорируются |
| COUNT(*) | Количество выбранных строк | считают и NULL значения |
| MAX(выражение) | Максимальное значение | для всех типов выражений, NULL значения игнорируются |
| MIN(выражение) | Минимальное значение | для всех типов выражений, NULL значения игнорируются |
Рассмотрим на примерах использование основных SQL инструкций.
Пример 1.
Для добавления новой таблицы в базу данных, используется инструкция CREATE TABLE.
CREATE TABLE films (
film_id INTEGER NOT NULL,
film_name VARCHAR(100) NOT NULL,
film_time time,
film_director VARCHAR(50) NOT NULL,
film_actors VARCHAR(255),
film_year INTEGER NOT NULL,
PRIMARY KEY (film_id))
Эта инструкция присваивает новой таблице имя FILMS и определяет для каждого ее столбца имя и тип данных, хранимых в нем.
Пример 2.
Для изменения структуры уже определенных таблиц используется инструкция ALTER TABLE.
ALTER TABLE film_distributions ADD FOREIGN KEY (film_id) REFERENCES films(film_id) ON DELETE CASCADE
Пример 3.
Для удаления таблицы из базы данных используют инструкцию DROP TABLE
DROP TABLE films
Пример 4.
Для выборки данных во всех SQL-запросах используется инструкция SELECT.
SELECT * FROM films
WHERE films.film_year=1999
Результатом выборки будет список фильмов вышедших в 1999 году:
| FILM_ID | FILM_NAME | FILM_TIME | FILM_DIRECTOR | FILM_ACTORS | FILM_YEAR |
| Ghost Dog: The Way of the Samurai | Jim Jarmusch | Forest Whitaker,John Tormey,Cliff Gorman,Henry Silva,Isaach de Bankole,Frank Minucci | |||
| Man on the moon | Milos Forman | Jim Carrey,Danny DeVito,Courtney Love,Paul Giamatti,Vincent Schiavelli |
Для построения выборки из нескольких таблиц используется два способа соединения отношений:
1. Соединение по одноименным атрибутам с помощью условия WHERE;
2. Соединение двух таблиц с помощью внешнего соединения LEFT (RIGHT, FULL) OUTER JOIN.
Рассмотрим и сравним следующие два запроса.
Пример 5.
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id,
clients
WHERE clients.client_id=rented_films.client_id
| CLIENT_FIO | RENT_START_DATE | RENT_END_DATE |
| Соколов Михаил Евгеньевич | 28.06.2006 | 29.08.2006 |
| Тимкина Наталья Дмитриевна | 12.09.2006 | 13.09.2006 |
| Колосов Антон Павлович | 11.10.2005 | 13.10.2005 |
| Соколов Михаил Евгеньевич | 01.05.2006 | 02.05.2006 |
| Гладкий Петр Сергеевич | 05.06.2006 | 06.06.2006 |
| Гладкий Петр Сергеевич | 13.09.2006 | NULL |
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films,
returned_rented_films,
clients
WHERE clients.client_id=rented_films.client_id AND
returned_rented_films.rent_id=rented_films.rent_id
| CLIENT_FIO | RENT_START_DATE | RENT_END_DATE |
| Тимкина Наталья Дмитриевна | 12.09.2006 | 13.09.2006 |
| Соколов Михаил Евгеньевич | 28.06.2006 | 29.08.2006 |
| Соколов Михаил Евгеньевич | 28.06.2006 | 29.08.2006 |
| Соколов Михаил Евгеньевич | 01.05.2006 | 02.05.2006 |
| Гладкий Петр Сергеевич | 05.06.2006 | 06.06.2006 |
| Колосов Антон Павлович | 11.10.2005 | 13.10.2005 |
Механизм работы этих двух способов соединения несколько различен. В случае соединения через условие WHERE будет возвращено столько записей, сколько имеют совпадения по одноименному связующему атрибуту. При использовании OUTER JOIN количество записей в выборке будет равно количеству записей в таблице слева от JOIN. Каждой записи таблицы слева будет сопоставлена, согласно заданному условию, запись из таблицы справа, если же соответствующей записи из правой таблицы нет, то будет сопоставлено NULL-значение.
Пример 6.
Нередко возникают ситуации, когда какой-либо запрос необходимо очень часто выполнять. В этой случае можно создать представление данных, основанное на данном запросе и в дальнейшем делать из него выборку как из обычной таблицы. Создание представления данных осуществляется с помощью инструкции CREATE VIEW.
Следующий пример создает представление данных client_list основанное на предыдущем запросе.
CREATE VIEW client_list AS
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id, clients
WHERE clients.client_id=rented_films.client_id
Пример 7.
Пример использования функций агрегирования (выборка с группировкой).
SELECT film_name,cnt_clients
FROM films,
(SELECT film_id, COUNT(DISTINCT client_id) AS cnt_clients
FROM rented_films GROUP BY film_id) cnt
WHERE films.film_id=cnt.film_id
ORDER BY film_name
В данном примере получим список фильмов с указанием их наименования и количества клиентов, бравших каждый фильм. Если клиент брал один и тот же фильм несколько раз, то при подсчете он будет считаться только 1 раз.
Пример 8.
Для добавления новой информации в базу данных в языке SQL используется инструкция INSERT.
INSERT INTO medium_type_directory (medium_type) VALUES ('dvd')
В таблицу medium_type_directory добавлена новая запись.
Пример 9.
Инструкция DELETE удаляет какую-либо информацию из базы данных.
DELETE FROM clients WHERE client_id=15
В примере выполняется удаление записи о клиенте с client_id равным 15.
Пример 9.
Обновление уже существующей в базе данных информации выполняется, используя инструкцию UPDATE.
UPDATE clients
SET client_phone_number=’795-55-78-48’
WHERE client_fio=’John N. Doe’
В данном примере у клиента John N. Doe будет изменен номер телефона.
Рекомендуемая литература
1. Боуман Джудит С., Дарновски Марси, Эмерсон Сандра Л. Практическое руководство по SQL, 4-е издание: Пер. с англ. – М.: Издательский дом «Вильямс», 2001. – 352 с.
2. Грофф Дж., Файнберг П. Энциклопедия SQL. 3-е издание: Пер. с англ. – СПб.: Питер, 2003. — 896 с.
3. Кренке Д. Теория и практика построения баз данных, 9-е издание: Пер. с англ. – СПб.: «Питер», 2005. – 864 с.
4. http://www.firststeps.ru/sql/oracle/oracle3.html (SQL, диалект Oracle)
5. http://www.sql-ex.ru (SQL, диалект Oracle)
6. http://megalib.com/items.php?idsubject=6 (Статьи по SQL)