Определение таблиц, домены.
Введение
Язык 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 для хранения информации о товарах в магазине и их продажах (название товара, производитель, вид товара, группа товара, единица измерения, цена, количество единиц товара в магазине, когда, сколько продано и вместе с какими другими товарами).
Извлечение данных