Определение таблиц, домены.

Введение

Язык SQL (Structured Query Language — структурированный язык за­просов) был разработан в 1970 г. компанией ЮМ как язык для рабо­ты с реляционными базами данных. К настоящему времени язык стал стандартным языком, который поддерживается практическими всеми системами управления базами данных как основанными на архитекту­ре клиент/сервер (например, Microsoft SQL Server, Oracle, Sybase) так и системами, расчитанными, в основном, на небольшие базы данных (Fox Pro, Paradox). Более того, удобство языка SQL привело к тому, что на­блюдается тенденция осуществлять с его помощью доступ даже к таким данным (например, с помощью технологии OLE DB), которые обычно обрабатывались иными методами: к электронной почте, к файловой си­стеме, к всевозможным системным журналам и т.д.

Язык SQL не является универсальным языком программирования, например, в SQL отсутствуют управляющие конструкции (циклы, услов­ные операторы). По сути дела, язык состоит только из операторов вы­борки, изменения данных, и управления правами доступа. Традиционно, весь язык SQL разделяется на три части: язык определения данных DDL (data definition language), язык манипулирования данными DML (data manipulation language) и язык управления данными DCL (data control language). К DDL относятся операторы работы со схемой базы данных, к DML — операторы выборки и изменения данных таблиц, не изменя­ющие схему, язык DCL состоит, в основном, из операторов управления транзакциями и назначения прав доступа.

Для того, чтобы язык SQL можно было использовать для реально­го программирования, его либо расширяют добавляя новые операторы, либо операторы SQL используют в программах на других языках про­граммирования. В первом случае получаются всевозможные диалекты SQL, специфические для каждой системы управления базами данных (например, в Microsoft SQL Server соответствующий язык называется Transact-SQL, в Oracle — PL/SQL). Во втором случае, используются раз­личные технологии, позволяющие обращаться к базам данных, такие как встроенный SQL (Embedded SQL), ODBC, OLE DB.

В 1986 г. американским национальным институтом стандартов (ANSI) был принят первый стандарт языка SQL. С тех нор стандарт языка SQL постоянно совершенствуется, а производители систем баз данных стремятся сделать свои продукты как можно более точно со­ответствующими этому стандарту. Мы описывая элементы языка SQL будем следовать стандарту SQL'92 и более поздним.

Создание базы данных

Реляционные базы данных

Реляционная база данных в классическом виде представляет собой ко­нечный набор конечных таблиц (отношений). Другие модели представ­ления данных — иерархические и сетевые — легко представляются в виде отношений. Каждая таблица имеет имя и постоянный набор столб­цов (полей), причем в каждом столбце могут храниться данные только одного типа, например, только целые числа или только даты. Некото­рые системы управления базами данных позволяют хранить в таблицах другие таблицы, однако во-первых, пока это скорее исключение, чем пра­вило, во-вторых, при такой организации возможно хранение избыточной информации, в третьих, это сильно усложняет язык запросов (что бу­дет показано в одной из следующих глав). Таким образом, подобная организация данных имеет больше недостатков, чем достоинств, поэто­му в большинстве случаев нежелательна. Как мы увидим в следующем небольшом примере, этого легко можно избежать.

Пример 1Информацию о книгах в библиотеке можно хранить в та­кой таблице:

Code	author	title	city	publishing	year
6470234	Ope 0.	Теория графов	М.	Наука	1968

То есть, в первый столбец записывается внутренний кодовый номер книги в библиотеке, во второй — информация об авторах, в третий — название и т.д. В данном примере, столбец author содержит для каж­дой книги имя и другие сведения об авторе. Но поскольку авторов часто бывает несколько, то для каждой книги этот столбец должен содер­жать таблицу, в которой хранится информация о каждом из авторов:

code	author	…
…
6479235		l_ name	f name	inst	pos		…
Boolos	George	M.I. T.	Prof.
Jeffrey	B.ichard	Princeton	Prof.
…

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

code	1 пате	f name	inst	pos
6479235	Boolos	George	M.I. T.	Prof.
6479235	Jeffrey	Richard	Princeton	Prof.

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

code	keyword	rank
…
6479234	граф	100
6479234	порядок	50
6479234	группа	10
…

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

Задача 1 (Библиотека) Постройте таблицы, в которых можно хра­нить информацию о читателях (имя, адрес, телефон, e-mail), и о вы­даваемых им книгах.

Задача 2 (CD) Постройте таблихщ для хранения сведений о ком-пахт дисках (фирма-производитель, номер, серия, композитор, испол­нители, треки и т.д.)

Давая задачи, мы будем использовать 4-5 различных баз данных, в скоб­ках будет указываться база данных, к которой относится задача.

Определение таблиц, домены.

Совокупность имен таблиц, их столбцов и типов столбцов называется сигнатурой или схемой базы данных. Кроме них схема базы данных со­держит вторичные элементы, такие как представления, процедуры или права доступа. Схема создается с помощью оператора create schema:

create schema <имя схемы>

<перечень элементов схемы>

Элементы схемы - определения таблиц, представлений, ограничений це­лостности, доменов и других объектов, образующих единое целое.

Рассмотрим основной оператор языка DDL — определение новой таб­лицы в схеме базы данных. В простейшем случае оператор имеет такой вид:

create table <имя таблицы>

(<описание столбцов>)

Здесь <описание столбцов> — это набор записей следующего вида, раз­деленных запятыми:

|<имя столбца> <тип столбца>

Стандартные имена типов: integer — целые числа, float — действи­тельные числа, char (длина), var char (длина) - символьные строки по­стоянной и переменной, соответственно, длины, date — дата. Каждая реализация языка SQL накладывает какие-то свои ограничения на зна­чения этих типов и вводит свои собственные типы данных.

Пример 2 Рассмотрим, пример определения таблицы с информацией о студентах:

create table student(

l_ name char(20),

f name char(20),

m name char(20),

nomer char(6),

gr nomer integer)

В данном примере определяется таблица student, которая имеет, 5 столбцов с именами l_name, f_name, m_name, nomer и gr_nomer соответ­ственно, каждый из первых четырех столбцов хранит строки. Макси­мальная длина строк в первых трех столбцах — 20 символов, в чет­вертом — 6 символов. Последний столбец храпит целые числа.

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

('Иванов', 'Иван', 'Иванович', '041000', 11),

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

Рассмотрим еще раз пример 1.

Пример 3 Так как количество экземпляров каждой книги в библиоте­ке обычно больше единицы, то если создавать для каждого экземпляра книги отдельную строку, в таблице появятся повторяющиеся, строки. Но вместо того, чтобы иметь в этой таблице отдельную строку для каждого экземпляра книги, можно просто добавить к таблице новый столбец, который и будет хранить количество экземпляров:

Code	author	title	city	publishing	year	number
		…
6479234	Ope 0.	Теория графов	М.	Наука	1968	20
		…

Удаление строки из исходной таблицы будет соответствовать умень­шению поля number на 1, а добавление повторяющейся строки — уве­личению на 1.

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

Например, в таблице student (пример 2) эта группа состоит из одного поля nomer. Для указания первичного ключа в операторе create table используется дополнительная декларация:

|primary кеу (<список полей первичного ключа>)

Пример 4В случае таблицы student это выглядит так:

create table student(

l_name char(20),

f_name char(20),

m_name char(20),

nomer char(6),

gr_nomer integer,

primary key(nomer))

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

create table ball(

stud_nomer char(6),

dis char(50),

dat date,

form char(10),

res integer,

primary key(stud_nomer, dis, dat))

В данной таблице stud_nomer — номер студенческого билета. Очевидно, что столбцы nomer из таблицы student и stud_nomer из таблицы ball содержат одну и ту же информацию и имеют один и тот же тип. Если бы таблица student содержала еще поле

phone_nomer char(6)

для хранения номера домашнего телефона студента, то это уже был бы другой тип информации, несмотря на совпадение типа данных. Точно так же, столбы gr_nomer и res в таблицах student и ball соответствен­но хранят различного рода информацию. Для того, чтобы более четко разделять столбцы с разной информацией, удобно определить домены. Домен — тип данных, предназначенный для хранения однородной ин­формации.

Пример 5В примере со студентами можно создать следующие до­мены,:

• create domain name_type char(20) — для хранения имен,

• create domain nomer_type char(6) -- номера зачетных книжек,

• create domain dis_type char(50) - названия дисциплин,

• create domain form_type char(10) — типы отчетности,

• create domain result_type integer — итоговые оценки,

• create domain group_type integer номера групп.

С учетов этих доменов описания двух предыдущих таблиц будут вы­глядеть так:

create table student(

l_name name_type,

f_name name_type,

m_name name_type,

nomer nomer_type,

gr_nomer group_type,

primary key(nomer))

create table ball(

stud_nomer nomer_type,

dis dis_type,

dat date,

form form_type,

res result_type,

primary key(stud_nomer, dis, dat))

Среди всех значений, которые может принимать какое-либо поле, есть одно специальное значение — NULL, которое означает отсутствие данных. В ряде случаев, данные в столбце не должны принимать такое значение. В частности, его, как правило, не должны принимать поля, образующие первичный ключ. Для указания этого свойства поля по­сле его определения пишется NOT NULL. To же самое можно написать в определении домена. В нашем примере значение NULL по смыслу может содержаться только в полях dat и res таблицы ball, так как только значения этих столбцов заранее неизвестны и вносятся позднее.

Еще один атрибут полей — значение по умолчанию. Иногда бывает так, что одно из значений поля используется гораздо чаще всех осталь­ных или же имеет некоторый специальный смысл. Например, в нашем примере для поля form чаще всего будет использоваться значение 'Экза­мен'. Если определить значение по умолчанию для какого-либо поля, то при работе с данными в ряде случаев это значение можно не указывать,

что мы потом увидим на примерах. Для указания значения по умолча­нию после определения поля пишется слово DEFAULT и это значение. То же можно писать и в определении домена. DEFAULT можно комбиниро­вать с NOT NULL.

Пример 6. С учетом вышесказанного, для нашей базы данных студен­тов следует определить такую схему:

create schema students

create domain name_type char(20) NOT NULL create domain nomer_type char(6) NOT NULL create domain dis_type char(50) NOT NULL create domain form_type char(10) NOT NULL

DEFAULT 'Экзамен' create domain result_type integer create domain group_type integer NOT NULL create table student(

l_name name_type,

f_name name_type,

m_name name_type,

nomer nomer_type,

gr_nomer group_type,

primary key(nomer))

create table ball(

stud_nomer nomer_type,

dis dis_type,

dat date,

form form_type,

res result_type,

primary key(stud_nomer, dis, dat))

create table professor(

dis_name dis_type,

gr group_type,

prof name,

primary key(dis_name, gr))

Последняя таблица содержит имена преподавателей, ведущих предме­ты в группах.

Задача 3 (Университет) Добавьте к схеме students таблицы для хранения сведений о преподавателях (полное имя, ученая степень, должность, список работ).

Задача 4 (Библиотека) Напишите операторы определения схемы для базы данных, построенной в примере 1.

Задача 5 (CD) Напишите операторы определения схемы для базы данных, построенной в примере 2.

Задача 6 (Банк) Создайте схему базы данных bank для хранения информации о клиентах и счетах банка, а так же всех операциях со счетами (имя, адрес клиента, номер счета, сумма, дата открытия и последнего изменения, когда какие суммы вносились и снимались, каким, образом). Учесть, что каждый счет может иметь нескольких владельцев, а каждый клиент — быть владельцем (или совладельцем) нескольких счетов.

Задача 7 (Супермаркет) Создайте схему базы данных supermarket для хранения информации о товарах в магазине и их продажах (на­звание товара, производитель, вид товара, группа товара, единица из­мерения, цена, количество единиц товара в магазине, когда, сколько продано и вместе с какими другими товарами).

Извлечение данных