Правила формальной оптимизации

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

Сущности
правила формальной оптимизации - student2.ru Сущность
правила формальной оптимизации - student2.ru Сущность – супертип (Язык) с подтипами (Русский, Иностранный)
Типы связей
правила формальной оптимизации - student2.ru связь (1,0):(N,1)
правила формальной оптимизации - student2.ru связь (1,0):(N,0)
правила формальной оптимизации - student2.ru связь (1,1):(N,1)
правила формальной оптимизации - student2.ru связь (1,1):(1,1)
правила формальной оптимизации - student2.ru связь (N,0):(N,0)
Обозначения для свойств атрибутов
# Первичный ключ
* Обязательный атрибут (NOT NULL)
O Необязательный атрибут (NULL)

Таблица 2. Элементы ER-диаграммы в CASE Studio

Сущности
правила формальной оптимизации - student2.ru Сущность
правила формальной оптимизации - student2.ru Слабая сущность
Типы связей
правила формальной оптимизации - student2.ru Неидентифицирующая (0,N):(1,1)
правила формальной оптимизации - student2.ru Неидентифицирующая (1,N):(1,1)
правила формальной оптимизации - student2.ru Неидентифицирующая (1,1):(1,1)
правила формальной оптимизации - student2.ru Неидентифицирующая (0,1):(0,1)
правила формальной оптимизации - student2.ru Неспецифическая связь (N:N)
правила формальной оптимизации - student2.ru Идентифицирующая (1,1):(1,N)
правила формальной оптимизации - student2.ru Идентифицирующая (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)

Наши рекомендации