Технология проектирования и администрирования баз данных и систем данных-Конспект3

СОДЕРЖАНИЕ

Стр.

ВВЕДЕНИЕ..............................................................................................

1.ИСПОЛЬЗОВАНИЕ SQL.....................................................................

1.1.Пример базы данных......................................................................

1.2.Выбор данных из таблиц................................................................

1.3.Создание и уничтожение таблиц....................................................

1.4.Введение строк в таблицы..............................................................

1.5.Выбор заданных строк и колонок из таблиц.................................

1.6.Упорядочение строк по запросу....................................................

1.7.Предохранение от выбора строк-дубликатов................................

1.8.Запросы к нескольким таблицам....................................................

1.9.Арифметические операции и функции строк символов................

1.10.Вычисление функций групп строк.............................................

1.11.Подзапросы...................................................................................

1.12.Модификация хранимых данных..................................................

1.12.1.Команда UPDATE..................................................................

1.12.2.Команда INSERT....................................................................

1.12.3.Команда DELETE...................................................................

1.13.Динамические изменения структуры базы данных......................

1.14.Альтернативные виды данных......................................................

1.15.Независимость данных.................................................................

1.16.Разделение данных и защита.........................................................

1.17.Понятие транзакции......................................................................

1.18.Понятие триггера..........................................................................

СПИСОК ЛИТЕРАТУРЫ.......................................................................

- 2 -

ВВЕДЕНИЕ

Язык запросов SQL был предложен корпорацией IBM в 70-х годах. Первоначально были созданы два основных продукта на основе SQL - DB2 и SQL/DS; далее на базе операционной системы 0S/2 предложена система Database Manager [1,2]. С тех пор язык SQL был принят большинством фирм, поставляющих на рынок базы данных как для персональных, так и для 'больших' (MAINFRAME) ЭВМ и стал фактическим стандартом получения информации из реляционных баз данных.

Со времени создания SQL четко обособились фактически две ветви в разработке систем для управления базами данных - на основе непроцедурного языка SQL и процедурных языков (язык dBase, Paradox, Clarion и многие другие). Фактически каждый из процедурных языков БД представляет некоторый 'надъязык' (по отношению к уровню Fortran, Pascal, С), ориентированный на обработку записей БД.

Совсем другой подход реализован в SQL - создан непроцедурный язык запросов к БД, в полной мере реализующий принцип DWIM - 'Делай то, что я имею в виду !'. При пользовании SQL пользователь на формальном языке запрашивает некоторую операцию (например, выборку из одной или более баз), а интерпретирующая SQL-система выполняет задание.

Заметим, что при пользовании процедурным языком пришлось бы (с большей или меньшей степенью детализации) описать все (возможно, очень многие) необходимые действия. Таким образом, одна фраза на SQL может быть равноценна целым страницам инструкций на языках уровня С, Pascal, Fortran и др. Однако подобные возможности языка SQL требуют значительных аппаратных ресурсов ЭВМ (соответствующей мощности процессора и многомегабайтной оперативной памяти для интерпретации и выполнения SQL-предложений); таким образом системы на основе SQL оправданы при реализации серьезных приложений (банковские системы, региональные и глобальные БД etc)

В настоящее время поддержка SQL-команд встроена в dBase, Borland Delphi / C++Builder, SQL Windows, PowerBuilder и другие системы. SQL

является обычным для обеспечения работы с БД в сети InterNet - например,

расширение MySQL (www.mysql.com) для языков Perl (www.perl.com, www.cpan.org) и PHP (www.php.net, www.phpclub.net), входящий в пакет

Java.sql интерфейс JDBC (Java DataBase Connectivity) фирмы JavaSoft для языка Java.

Однако SQL (как и многие другие непроцедурные языки) не содержит ряда привычных программистам возможностей - таких, как использование условных выражений и организации циклов. По этим соображениям команды SQL часто встраиваются в процедурные языки программирования, например, в С/C++ и др.

Язык SQL ориентирован на операции с данными, представленными в виде

- 3 -

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

SQL-язык естественно вошел в современную идеологию систем

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

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

Из реализующих подобный подход инструментальных систем следует назвать SQLBase, SQLWindows (фирма GUPTA Corp.), EasyCASE (Evergreen Tools, Inc.), PowerBuilder (PowerSoft), SyBase и InterBase, Delphi и C++Builder (Borland Int.) и другие.

Внедрение клиент-серверных технологий в бывшем СССР значительно отстало от мирового уровня еще и из-за того, что мощные современные приложения-серверы применялись в армии США, что автоматически накладывало ограничения на их использование в странах Восточного блока (это относится, например, к мощному серверу Borland InterBase).

Важной причиной использования SQL является совместимость, являющаяся следствием фактического стандарта SQL (в настоящее время существуют стандарты ANSI - Американского национального института стандартов и стандарт корпорации IBM). Конечно, не все реализации поддерживают полные возможности SQL; пожалуй, наиболее полно и последовательно стандарт SQL реализован в программном продукте корпорации ORACLE (Oracle Corporation, Belmont, California, USA) и в сервере InterBase (Borland Int).

Стандарт SQL является совместной разработкой ANSI (American National Standards Institute) и ISO (International Organization for Standardization), в 1986

году опубликовавших серию стандартов SQL/86. Первый международный стандарт языка SQL был принят в 1989 году (стандарт SQL/89) и устанавливал многие важные свойства языка как определяемые реализацией (что дало большой простор для расхождений между различными реализациями SQL, причем многие аспекты языка вообще не упоминались в SQL/89). В 1992 году принят новый стандарт языка - SQL/92, известны стандарты SQL/99 и SQL/J (расширение SQL для Java). Сказанное фактически констатирует определенное разнообразие реализации языка SQL в существующих системах.

Заметим, что язык SQL разработан для работы с реляционными базами

- 4 -

данных (РБД) и фактически представляет собой систему управления реляционными базами данных (СУРБД).

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

Логические структуры баз данных

Логическая

Достоинство

Недостаток

структура

Иерархическая

Представление структур данных

Чрезмерная

модель

в виде дерева практически не

громоздкость

при

ограничивает

сложность

описании

данных

со

логических

связей

между

сложными

порциями данных

логическими связями

Сетевая модель

Представление

данных

в

виде

Сложность реализации

произвольного

графа позволяет

представлять структуру

данных

любой сложности

Реляционная

Представление структур данных

Представление

модель

в виде совокупности связанных

сложных

структур

отношениями

(relation)

таблиц

требует

большого

весьма наглядно и удобно для

количества

связанных

реализации обработки данных

таблиц

Объектно-

Заключающийся в объединении

Для

большинства

ориентирован-

данных и функций для их

приложений класса БД

ная модель

обработки в единое целое синтез

объектно-

особенностей

сетевой

и

ориентированный

реляционной

моделей

и

подход

неоправданно

объектной технологии

выгоден

сложен

при

создании

высокоструктурированных БД

Реляционные системы берут свое начало в математической теории множеств. Они были предложены в конце 1968 года доктором Э.Ф.Коддом из фирмы IBM, который впервые осознал, что можно использовать математику для придания надежной основы и строгости в области управления базами данных.

РБД можно определить как БД, которая представляется пользователю набором связанных между собой таблиц и ничем кроме таблиц. Над таблицами определены операции - проекции, соединения и др. Говорят, что база данных представлена в первой, второй, третьей нормальной форме и нормальной форме Бойса-Кодда, если она удовлетворяет определенным требованиям [2...4]. Некоторые авторы считают, что первая нормальная форма - 1НФ

- 5 -

(требующая, чтобы всякий столбец в любой таблице являлся 'атомным’, то есть единственным, неделимым, а не списком) является предпосылкой того, что БД является реляционной. Более изощренные определения РБД можно найти, например, в книге [5].

С реляционной моделью данных связаны понятия ключа и индекса. Ключи предназначены для идентификации записей в таблице; простой ключ состоит из одного поля, а составной ключ - из комбинации полей (поля, по которым построен ключ, именуются ключевыми). В таблице может быть определен только один первичный ключ, который используется для ускорения выполнения запросов к БД; при выполнении запроса на поиск записи благодаря упорядочиванию значений ключа реализуется непосредственный доступ к нужной записи без (последовательного) просмотра всей таблицы.

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

Организация связи (отношений) между таблицами (двумя или несколькими) называется связыванием или соединением таблиц; для связывания таблиц используются поля связи, которые должны быть индексированными. Связь между таблицами определяет отношение подчиненности, при котором одна таблица является главной (родительской, или мастером - Master), а вторая - подчиненной (дочерней, детальной - Detail).

Между таблицами возможны следующие виды отношений:

*‘один-к-одному’ (записывается как ‘1:1’);

*‘один-ко-многим’ (записывается как ‘1:n’);

*‘много-к-одному’ (записывается как ‘n:1’);

*‘много-ко-многим (записывается как ‘m:n’)’.

Отношение ‘один-к-одному’ означает, что записи главной таблицы сопоставлена одна запись в подчиненной таблице, отношение один-ко- многим’ (встречается наиболее часто) отражает факт соответствия нескольких записей подчиненной таблицы одной записи главной таблицы, отношение ‘много-к-одному’ равносильно предыдущему при взгляде со стороны подчиненной таблицы; отношение ‘много-ко-многим’ на практике встречается достаточно редко.

В SQL определены внешние (foreign) и родительские (parent) ключи,

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

[1,2].

- 6 -

Принятый в настоящее время подход к проектированию БД основан на модели типа ‘сущность - связь’ (Entity-Relationship model, ER) и был предложен в 1970-х годах Питером Ченом (Peter Chen). Существует достаточное количество программных продуктов, позволяющих (часто в графическом режиме) проектировать сложные БД (устанавливать связи между таблицами, генерировать необходимые SQL-запросы и др.); одним из известных подобных CASE-систем является, например, пакет EWwin (см. работу [7] и др.)

В последние годы появляются утверждения о недостаточной эффективности SQL при создании корпоративных распределенных информационных систем и необходимости перехода к более гибким системам типа TUXEDO System, TEKNETRON и др. [8]; однако SQL нашел свою нишу и, похоже, прижился прочно и надолго.

При практическом применении используются Windowsориентированные системы программирования Delphi и/или C++Builder для IBM PC. Системы установлены на ПЭВМ и доступны пользователю в диалоговом режиме.

Целью работы является ознакомление на практике с возможностями применения языка SQL для реализации разнообразных запросов к БД и их (запросов) возможной оптимизации. Синтаксис SQL дан (в некоторых случаях) применительно к стандарту Oracle как наиболее полно реализующему возможности SQL; практическая работа ведется в среде систем

Delphi или C++Builder.

Предполагается предварительное изучение литературы по теории и практике работы БД и знакомство с основами работы на IBM PC-совместимых ПЭВМ.

1. ИСПОЛЬЗОВАНИЕ SQL

Существуют три формы SQL: интерактивный (Interactive), статический (Static) и динамический (Dynamic). Функционируют они одинаково, но используются по-разному.

* Интерактивный SQL применяется для непосредственной работы с БД - пользователь вводит SQL-оператор, он сразу же выполняется и пользователь видит результат выполнения (или код ошибки).

* Статический SQL содержит SQL-операторы, жестко закодированные в теле исполняемого приложения. Наиболее распространен встроенный SQL (Embedded SQL), где SQL-код включается в исходный текст (базовой) программы, написанной на другом языке (например, С или Pascal); при использовании встроенного SQL результаты выполнения операторов SQL перенаправляются в переменные, которыми оперирует базовая программа. К настоящему времени SQL встроен в языки Ada, Cobol, Fortran, C, Pascal,

- 7 -

PL/1, Java, Mumps (теперь M).

* Динамический SQL также является частью приложения, но конкретный SQL-код генерируется во время выполнения (Run Time), а не вводится заранее.

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

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

*SELECT - выборка строк из таблиц

*CREATE - создание таблицы

*INSERT - ввод строки в таблицу

1.1.Пример базы данных

Базы данных в терминологии SQL состоят из таблиц. К понятию таблицы наиболее близок аналог файла таких БД, как dBase, Clarion etc и состоит из некоторого количества строк (аналог ЗАПИСЕЙ, если пользоваться терминологией не-SQL языков); причем системы Delphi / С++Builder поддерживают множество форматов файлов таблиц, что, однако, практически никак не отражается на использование SQL. Запомним это: неважен тип СУБД - был бы реализован язык SQL.

Большинство нижеследующих примеров будет использовать DEPT - таблицу, содержащую информацию об отделах компании, и таблицу ЕМР, содержащую информацию о работниках данной компании.

							Таблица DEPT
	DEPTNO			DNAME			LOC
	(код отдела)			(название отдела)			(место расположения)
10				ACCOUNTIG			NEW YORK
20				RESEARCH			DALLAS
30				SALES			CHICAGO
40				OPERATIONS			BOSTON

Таблица ЕМР

EMPNO	ENAME	JOB
(таб.	(имя)	(долж-

MGR	HIREDATE
	(дата
	приема на

SAL	COMM	DEPTNO
	(комис-
(оклад)	сион-	(отдел)

- 8 -

	номер)				ность)				работу)				ные)
7369			SMITH		CLERK		7902		17-DEC-80		8,00.00				20
7499			ALLEN		SALESMAN		7698		20-FEB-81		1,600.00	300.0			30
7521			WARD		SALESMAN		7698		22-FEB-81		1,250.00	500.0			30
7566			JONES		MANAGER		7839		02-APR-81		2,975.00				20
7654			MARTIN		SALESMAN		7698		28-SEP-81		1,250.00	1,400.0			30
7698			BLAKE		MANAGER		7839		01-MAY-81		2,850.00				30
7782			CLARK		MANAGER		7839		09-JUN-81		2,450.00				10
7788			SCOTT		ANALYST		7566		09-NOV-81		3,000.00				20
7839			KING		PRESIDENT				17-NOV-81		5,000.00				10
7844			TURNER		SALESMAN		7698		08-SEP-81		1,500.00				30
7876			ADAMS		CLERK		7788		23-SEP-81		1,100.00				20
7900			JAMES		CLERK		7698		03-DEC-81		950.00				30
7902			FORD		ANALYST		7566		03-DEC-81		3,000.00				20
7934			MILLER		CLERK		7782		23-JAN-82		1,300.00				10

Каждая таблица состоит из колонок (вертикальных) и	строк
(горизонтальных). Таблица DEPT имеет три колонки (называемые DEPTNO,
DNAME и LOC) и 4 строки (одна для каждого отдела номер 10,20,30,40).
Строка состоит из полей. Каждое поле содержит значение	данных на

пересечении строки и колонки. Например, в первой строке таблицы DEPT значение 10 хранится в поле DEPTNO, значение ACCOUNTING хранится в поле DNAME, значение NEW YORK - в поле LOC.

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

*всякий столбец таблицы обладает в этой таблице уникальным именем;

*столбцы таблицы упорядочиваются слева направо, т.е. столбец 1, столбец 2, ... столбец n. C точки зрения пользователя порядок, в котором определены имена столбцов, становится порядком, в котором должны вводиться в них данные, если не предварять при вводе каждое значение именем соответствующего столбца;

*строки таблицы не упорядочены (их последовательность определяется лишь последовательностью ввода в таблицу);

*в поле на пересечении строки и столбца любой таблицы всегда имеется в

точности одно значение данных и никогда не должно быть множества значений (правда, это 'атомарное' значение может быть достаточно объемным, например, таким, как рецепт некоего блюда или много мегабайтный мультимедиа-файл);

* всем строкам таблицы соответствует одно и то же множество столбцов, хотя в определенных столбцах любая строка может содержать пустые значения (NULL-значения), т.е. может не иметь значений для этих

- 9 -

столбцов; * все строки таблицы обязательно отличаются друг от друга хотя бы

единственным значением, что позволяет однозначно идентифицировать

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

минимум из стольких отношений, сколько отдельных таблиц она включает. Нечеткость многих терминов, используемых в сфере обработки данных,

заставила Э.Ф.Кодда отказаться от них и предложить новые или дать более точные определения существующим. Так, он не мог использовать широко распространенный термин 'ЗАПИСЬ', который в различных ситуациях может означать экземпляр записи, либо тип записей, запись в стиле Кобола (которая допускает повторяющиеся группы) или плоскую запись (которая их не допускает), логическую запись или физическую, запись, хранимую запись или виртуальную запись и т.д. Вместо этого он использовал термин 'кортеж длины N' или просто 'кортеж', которому дал точное определение.

В основополагающих работах [4,5] можно подробно познакомиться с терминологией реляционных баз данных (есть русскоязычные переводы), здесь же будем использовать неформальные их эквиваленты:

таблица	- для отношения
строка (или запись)	- для кортежа
столбец (или поле)	- для атрибута

Заметим также, по определению принимается, что 'запись' означает 'экземпляр записи', а 'поле' означает 'имя и тип поля'.

Контрольные вопросы

1.Какие основные логические структуры БД применяются в настоящее время

?

2.Что такое реляционная БД ?

3.Что такое таблица реляционной БД и из чего состоит таблица ?

4.Какие типы отношений возможны между таблицами ?

-10 -

5.В чем заключается универсальность языка SQL перед специализированными языками работы с БД ?

6.Каковы основные свойства строк и столбцов реляционной БД ?

7.Чем отличаются интерактивная, статическая и динамическая форма применения языка SQL ?

8.Какие основные действия над таблицами определены в языке SQL ?

1.2.Выбор данных из таблиц. Команда SELECT

Выборка данных из БД является наиболее распространенной операцией SQL. Обращение к базе данных называется запросом и для его реализации необходимо использовать команду SELECT. Базовая команда (предписание) SELECT состоит из двух частей, носящих названия клауз

(clause):

SELECT некоторые данные (имя (имена) колонки)

FROM таблица или некоторые таблицы (имя (имена) таблицы) ;

Клауза SELECT всегда вводится первой, а за ней следует клауза FROM. Вывод запрошенной информации в данном случае (спецификация вывода не определена) осуществляется на экран дисплея.

Рассмотрим выборку данных в таблицах DEPT и ЕМР, используя некоторые простые запросы SQL.

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

SELECT DEPTNO,DNAME,LOC --- вводит пользователь !

FROM DEPT; ---то же...

	DEPTNO			DNAME			LOC
10				ACCOUNTIG			NEW YORK
20				RESEARCH			DALLAS
30				SALES			CHICAGO
40				OPERATIONS			BOSTON

SELECT-запрос возвращает значение (в отличие от других, описанных ниже, SQL-предписаний). Обычно SQL-запрос возвращает множество строк (в частном случае ни одной), причем указатель (обычно реализованный в виде стрелки или цветовой подсветки определенной строки) в каждый момент указывает на одну из них.

В вышеприведенном примере запроса были перечислены имена всех колонок таблицы DEPT (а именно DEPTNO, DNAME, LOC) в клаузе SELECT.