«Скляр А.Я. С43 Введение в InterBase — М.: Горячая линия-Телеком, 2002. с: ил. ISBN 5-93517-062-0. Книга содержит справочные и методические материалы по популярной системе управления базами данных InterBase 5-6. ...»

ББК 32.973

С 43

Скляр А.Я.

С43 Введение в InterBase — М.: Горячая линия-Телеком, 2002. с: ил.

ISBN 5-93517-062-0.

Книга содержит справочные и методические материалы по популярной

системе управления базами данных InterBase 5-6. Рассмотрена методика

проектирования систем переработки информации на основе клиент - серверной технологии. Особое внимание уделено применению средств SQL при

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

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

ББК 32. Производственно-техническое издание Скляр Александр Яковлевич Введение в InterBase Редактор Э.Н. Бадиков Обложка художника В.Г. Ситникова ЛР № 071825 от 16 марта 1999 г.

Подписано в печать 11.06.2002. Формат 60x88/16. Гарнитура Arid Печать офсетная. Уч.-изд. л. 32,64. Тираж 3 000 экз. Изд. № 62. Заказ I* © Скляр А.Я., ISBN 5-93517-062- © Оформление издательства «Горячая линия-Телеком», Введение Базы данных в системах обработки информации Автоматизация технологических и управленческих процессов, без которой немыслимо эффективное решение задач управления промышленным или торговым предприятием, банком, учебным заведением, государственной структурой, основывается на переработке больших объемов информации.

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

Персональные СУБД (Clipper, FoxPro, Clarion и др.) мало приспособлены для создания интегрированных систем, работающих с общей базой.

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

Более мощные системы, основанные на СУБД Btrieve, также не отвечают в полной мере требованиям масштабируемости, необходимой для корпоративной информационной системы. Достоинства Btrieve-систем, позволившие им унаследовать архитектуру и большую часть кода от своих предшественников, разработанных на Clipper и Clarion, что во многом объясняет их большую популярность, становятся тормозом при попытке построения информационных систем на более современных платформах и не обеспечивают переносимость решений.

Основным направлением в разработке автоматизированных информационных систем в настоящее время является ориентация на использование СУБД, базирующихся на SQL-серверах. В чем же состоят преимущества разработки информационных систем на их основе?

4 Введение 1. SQL-серверы прямо ориентированы на создание интегрированных, многопользовательских систем, имея в своем распоряжении развитые словари данных.

2. Средства разработки для этих СУБД оптимизированы в отношении коллективной разработки сложных систем в рамках единой стратегической линии.

3. Развитый механизм обработки транзакций позволяет обеспечить целостность данных при одновременной работе многих пользователей.

4. Использование единого языка доступа к данным (SQL) позволяет упростить переход от одной СУБД к другой.

5. Обеспечивается масштабируемость разрабатываемых систем.

6. Поддерживается возможность работы как в локальной, так и в глобальной сетях.

Рассматриваемая здесь СУБД InterBase в полной мере удовлетворяет всем перечисленным требованиям.

InterBase и область его применения InterBase представляет собой полнофункциональный SQL-сервер.

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

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

• поиск в базе данных по заданным условиям;

• сравнение, сортировку и предоставление данных в табличном виде;

• изменение хранимых данных;

• добавление новых данных в базу;

• удаление данных из базы данных;

• создание новых базы данных и структур данных;

• выполнение программного кода на сервере;

• передачу сообщения другим клиентам, подключенным в данный момент к серверу.

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

Несколько клиентов могут быть связаны с многопоточным процессом сервера одновременно.

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

Отличительными качествами InterBase являются:

• Высокая производительность и надежность сервера при минимальных требованиях к техническим средствам.

• Поддержка стандарта SQL-92, обеспечивающая переносимость • Относительно низкая стоимость продукта.

• Простота установки и поддержки сервера. Удобный и не требующий специальной подготовки механизм администрирования базой Все это делает InterBase прекрасным выбором для реализации корпоративных систем малого и среднего масштаба (с количеством пользователей в несколько десятков). При реализации очень крупных проектов (с сотнями или более пользователей) стоит, наверное, рассмотреть более мощные серверы - типа Oracle или Informix.

Системные требования InterBase InterBase работает на различных платформах, включая Microsoft Windows NT 4.0, Windows 2000, Windows 95, Windows 98 и разные версии операционной системы UNIX.

Windows NT или Windows Память: минимум 16 Мб (для сервера рекомендуется 64).

Процессор: 486DX2 66 МГц минимум; Pentium 100 МГц или больше рекомендуется для мультиклиентского сервера.

Компиляторы: Microsoft Visual C++ 4.2 и Borland C++ 5.0, C++ Builder, Delphi.

Память: минимум 16 Мб (для сервера рекомендуется 64).

Компиляторы: Microsoft Visual C++ 4.2 и Borland C++ 5.0.

Операционная система: HP-UX 10.20.

Должен быть установлен HP DCE/9000 - средство динамической поддержки (DCE-Core).

Память: минимум 32 Мб (для сервера рекомендуется 64).

Процессор: PA-RISC.

Компилятор С: HEWLETT-PACKARD C/HP-UX Версия 10.32.

Компилятор C++: C++ HEWLETT-PACKARD /HP-UX Версия 10.22.

Компилятор ФОРТРАНА: 10.20 выпуска HEWLETT-PACKARD Fortran/9000.

Аппаратная модель: HP/9000 Series 7хх или 8хх.

Операционная система: Solaris 2.5.x или 2.6.x.

Память: минимум 32 Мб (для сервера рекомендуется 64).

Модель процессора: SPARC или UltraSPARC.

Компилятор С: SPARCWorks SC 4.2.

Компилятор C++: SPARCWorks SC3.0.1.

Компилятор ФОРТРАНА: SPARCWorks SC4.0.

Компилятор КОБОЛА: MicroFocus Cobol 4.0.

Компилятор Ады: SPARCWorks SC4.0 Ada compiler.

Основные возможности InterBase InterBase на Windows 95 и Windows NT дает все выгоды от полной системы управления реляционной базой данных (RDBMS). Некоторые ключевые функции InterBase перечислены в следующей таблице.

Таблица. Основные функции InterBase Поддержка сетевых протоколов На всех платформах InterBase поддерживает TCP/IP; для Windows NT каналы NetBEUI; для Netware IPX/SPX Соответствие минимальной конфигу- Стандартный ANSI SQL, доступный Функция Доступ к базам данных Минимальный (оптимистический) уровень блокировки строк ность Хранимые процедуры Обновляемые обзоры Обзоры (виртуальные таблицы) могут отражать изменения данных сразу, как только они происходят Функция Программы (помещенные в специОпределяемые пользователем функфицированную библиотеку на сервеции (UDFs) Внешние объединения Параллельный доступ приложений к Один клиент, читающий таблицу, не Автоматическая двухфазная запись Транзакционный контроль изменений InterBase API Набор функций, которые дают возможность приложениям создавать Server Manager (диспетчер сервера) Windows утилита для резервного функция Утилита командной строки администадминистративных средств базы данратора базы данных (DBA) Заголовочные файлы (Header files) Файлы, включаемые в начале прикладных программ и определяющие Примеры Файлов типа "make" Файл сообщений Файл Interbase.msg, содержащий сообщения, представленные программам пользователя С, готовый к компиляции и компоновке, который может использоваться для запросов к Глава Реляционные базы данных 1.1. Организация хранения данных Для обеспечения эффективного хранения данных, а это означает быстрый поиск, обновление данных, защиту от ошибочных вводов, обеспечение конфиденциальности информации и многое другое, необходима соответствующая их организация. Для быстрого поиска необходимо упорядочение хранимых данных, поддержание связей между ними, контроль на непротиворечивость, обеспечение однократного ввода или изменения при многократном последующем использовании. Ключевую роль при этом играют методы поддержания логических связей между данными. По способам организации хранения связей выделяются такие модели данных, как иерархические, сетевые и реляционные.

Первые иерархические и сетевые СУБД были созданы в начале 60-х годов, что было вызвано необходимостью управления миллионами записей (прежде всего связанных друг с другом иерархическим образом), например при информационной поддержке лунного проекта Аполлон. Среди реализуемых на практике СУБД этого типа наибольшее распространение получила система IMS (Information Management System компании IBM). Достаточно широко используются и другие иерархические системы.

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

Достоинства иерархической модели данных:

1. Относительная простота организации данных.

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

Ограничения иерархической модели:

1. Отсутствие явного разделения логических и физических характеристик модели.

2. Для представления неиерархических отношений данных требуются дополнительные манипуляции.

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

Структура графа делает возможным создание как простых представлений иерархических отношений (например, генеалогических данных), так и более сложных, в том числе рекурсивных.

Сетевая модель данных - это представление данных сетевыми структурами типов записей, связанных отношениями мощности один к одному или один ко многим. В конце 60-х годов конференция по языкам систем данных (Conference on Data Systems Languages, CODASYL) поручила группе DBTG (Database Task Group - группа для разработки стандартов систем управления базами данных) разработать стандарты систем управления базами данных. На DBTG оказывала сильное влияние архиГлава тектура, использованная в одной из самых первых СУБД - Integrated Data Store (IDS), созданной ранее компанией General Electric. Это привело к тому, что была рекомендована сетевая модель.

Документы от 1971 года остаются основной формулировкой сетевой модели, на них ссылаются как на модель CODASYL DBTG. Она послужила основой для разработки сетевых систем управления базами данных нескольких производителей. IDS (Honeywell) и IDMS (Computer Associates) - две наиболее известные коммерческие реализации. В сетевой модели существует две основные структуры данных: типы записей и наборы.

• Тип записей. Совокупность логически связанных элементов данных.

• Набор. В модели DBTG отношение один ко многим между двумя • Простая сеть. Структура данных, в которой все бинарные отношения имеют мощность один ко многим.

• Сложная сеть. Структура данных, в которой одно или несколько бинарных отношений имеют мощность многие ко многим.

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

В модели DBTG возможны только простые сети, в которых все отношения имеют мощность один к одному или один ко многим. Сложные сети, включающие одно или несколько отношений многие ко многим, не могут быть напрямую реализованы в модели DBTG. Следствием возможности создания искусственных формальных записей является необходимость дополнительного объема памяти и обработки, однако при этом модель данных имеет простую сетевую форму и удовлетворяет требованиям DBTG.

Достоинства сетевой модели данных:

4. Гибкая организации данных и, соответственно, возможность представления в базе самых разнообразных данных.

5. Возможность обеспечения исключительно высокой скорости поиска.

Ограничения сетевой модели:

6. Сложность процедур обновления данных, связанная с необходимостью перестроения системы связей.

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

В 1970-1971 годах Е.Ф. Кодд опубликовал две статьи, в которых ввел реляционную модель данных и реляционные языки обработки данных - реляционную алгебру и реляционное исчисление:

• Реляционная алгебра - процедурный язык обработки реляционных • Реляционное исчисление - непроцедурный язык создания запросов.

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

Существует два подхода к проектированию реляционной базы данных.

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

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

1.2. Организация данных в реляционной модели Рассматриваемая здесь СУБД InterBase относится к реляционным системам.

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

Реляционная модель основана на теории множеств и математической логике. Такой фундамент обеспечивает математическую строгость реляционной модели данных.

множеств называется отношением степени п {п-арным отношением).

Мощность множества кортежей, входящих в отношение R, называют мощностью отношения R.

Если множество R конечное, а при работе с базами данных это всегда так, то мощность - это просто число кортежей в отношении.

Понятие отношения фактически лежит в основе всей реляционной теории баз данных. Отношения являются математическим аналогом таблиц. В самом деле, множество i-ых элементов ^ кортежей можно трактовать как i-ый столбец таблицы, а сам кортеж - как ее строку. При этом природа отдельных элементов кортежей не имеет значения. Важно только то, что все они однотипны (принадлежат одному множеству А;).

Сам термин "реляционное представление данных", впервые введенный Коддом [43], происходит от термина relation, понимаемом именно в смысле этого определения.

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

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

Например, отношение, состоящее из трех следующих кортежей {(1, "Иванов", 32), (2, "Петров", 41), (3, "Сидоров", 64)} можно считать таблицей, содержащей данные о сотрудниках и их возрасте. Такая таблица будет иметь три строки и три колонки, причем в каждой колонке содержатся данные одного типа. Заметим, что с практической точки зрения такая таблица, если это сведения о сотрудниках, плоха, поскольку данные в ней по мере прохождения времени придется обновлять, лучше указать год рождения;

если же это данные о медицинском обследовании, то она оптимальна, иначе необходимо было бы давать и год рождения, и год обследования. С точки зрения анализа отношений, это, конечно, не имеет значения, а вот наличие первого столбца существенно. Если в нашу таблицу в дальнейшем будут добавляться строки, то нет никакой гарантии, что не появится Иванов "Второй". Для таблицы это несущественно, но все элементы множества, должны быть различимы, а значит, в нашем отношении не может быть двух одинаковых кортежей. Наличие же первого столбца с номером позволит нам легко отличить Иванова "Первого" от Иванова "Второго".

Простое введение понятия отношения само по себе мало что дает.

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

Теоретико-множественные операторы:

• Объединение • Пересечение • Декартово произведение Специальные реляционные операторы:

• Соединение Не все они являются независимыми, т.е. некоторые из этих операторов могут быть выражены через другие реляционные операторы.

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

Оператор соединения Оператор соединения определяется через операторы декартового произведения и выборки:

Для оператора естественного соединения добавляется оператор проекции, то есть Оператор деления Как уже отмечалось, перечисленные операторы являются, вообще говоря, зависимыми. Однако из них можно выделить группу независимых операторов. Например, группа: объединение, вычитание, декартово произведение, выборка, проекция или объединение, пересечение. Операторы такой группы называют примитивными. Состав таких операторов зависит от выбора базовой группы (либо первая, либо вторая).

Табличное представление данных;

Построение базы данных как системы взаимосвязанных таблиц является непростой задачей. Основное требование состоит в том, чтобы при построении базы данных избежать дублирования данных. При этом дело состоит не столько в росте объемов хранимой информации, сколько в трудности обеспечения их согласованности. Если данные о технических характеристиках оборудования будут храниться в нескольких таблицах, например таблицах, содержащих сведения об установленном оборудовании, таблицах наличия оборудования на складах, таблицах списанного оборудования, то изменения сведений об отдельном узле потребуется вносить во множество строк в нескольких таблицах. При этом трудно гарантировать полноту внесения изменений, что может привести к противоречиям при выборке и обработке данных, а это уже совсем недопустимо. Последнее означает, что все данные должны быть нормализованы с целью обеспечения отсутствия дублирования и, соответственно, однократности ввода или изменения при многократности использования данных. На практике, конечно, для ускорения поиска данных определенное дублирование допускается. Важно лишь, чтобы оно было заранее известно и контролировалось. Обычно это относится к агрегированным данным или данным, полученным в результате специальной обработки.

Внешне таблицы, у которых изменен порядок расположения строк и столбцов, являются различными. С точки зрения обеспечения организации хранения данных это недопустимо. Поэтому в таблицах с каждым столбцом связывается его имя. Порядок строк в таблице не фиксируется (таблица отражает отношение, которое является множеством, а для элементов множества порядок не фиксируется). Таким образом, таблица определяется составом поименованных столбцов вне зависимости от порядка их записи, а также составом строк вне зависимости от порядка их следования.

Рассмотрим подробнее процедуры нормализации.

Таблица находится в Первой Нормальной Форме (1НФ), если она представляет собой отображение отношения. Это означает, что:

• все данные в каждом из ее столбцов однотипны;

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

• все строки таблицы различны.

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

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

Подмножество атрибутов К отношения R будем называть потенциальным ключом, если К обладает следующими свойствами:

1. Свойством уникальности - в отношении не может быть двух различных кортежей с одинаковым значением К.

Свойством неизбыточности - никакое собственное подмножество в К не обладает свойством уникальности.

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

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

Отношение может иметь несколько потенциальных ключей. Традиционно один из потенциальных ключей объявляется первичным, а остальные - альтернативными. Различия между первичным и альтернативными ключами могут быть важны в конкретной реализации реляционной СУБД, но с точки зрения реляционной модели данных, нет оснований выделять таким образом один из потенциальных ключей.

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

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

Отношение R находится во второй нормальной форме (2НФ) тогда и только тогда, когда отношение находится в ШФ и нет неключевых атрибутов, зависящих от части сложного ключа. (Неключевой атрибут это атрибут, не входящий в состав никакого потенциального ключа). Если некоторое множество атрибутов зависит от другого множества атрибутов, то первое множество будем называть зависимой частью функциональной зависимости, а второе - его детерминантом.

Отметим, что, если потенциальный ключ отношения является простым, то отношение автоматически находится в 2НФ.

Атрибуты называются взаимно независимыми, если ни один из них не является функционально зависимым от другого.

Отношение R находится в третьей нормальной форме (ЗНФ) тогда и только тогда, когда отношение находится в 2НФ и все неключевые атрибуты взаимно независимы.

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

Нормальные формы высоких порядков В большинстве случаев нормализации до уровня третьей нормальной формы (3НФ) вполне достаточно, чтобы разрабатывать вполне работоспособные базы данных. В то же время в некоторых случаях этого уровня оказывается недостаточно из-за трудностей с поддержанием логической целостности данных при обновлении информации. Связано это, прежде всего с тем, что в 3НФ могут сохраняться некоторые функциональные зависимости.

НФБК (Нормальная Форма Бойса-Кодда) При приведении отношений с помощью алгоритма нормализации к отношениям в 3НФ неявно предполагалось, что все отношения содержат один потенциальный ключ. Это не всегда верно. Пусть отношение содержит два составных потенциальных ключа А+В и А+С и набор неключевых атрибутов D. С и В находятся в прямой зависимости друг от друга, тогда во всех кортежах, где встречается В, встречается и С. Налицо явная избыточность. В то же время, данное отношение находится во второй нормальной форме, поскольку оно не содержит неключевых атрибутов, зависящих от части сложного ключа. Более того, оно находится и в 3НФ, поскольку не содержит зависимых друг от друга неключевых атрибутов.

В то же время легко можно, разбив данное отношение на 2 (В, С) и (А, В, D) или (А, С, D), устранить избыточность. При таком действии устраняется зависимость между частями потенциального ключа.

Отношение R находится в нормальной форме Бойса-Кодда (НФБК) тогда и только тогда, когда детерминанты всех функциональных зависимостей являются потенциальными ключами.

4НФ и 5НФ (Четвертая и Пятая Нормальные Формы) В ряде случаев зависимость между атрибутами может присутствовать, но при этом не быть однозначной. Например, если отношение (А, В, С) даже находится в нормальной форме Бойса-Кодда (НФБК), то это еще не означает, что его нельзя представить в виде эквивалентной пары отношений (А, В) и (А, С). В последнем случае говорят, что атрибуты (или группы атрибутов) В и С находятся в многозначной зависимости от А.

Если такой зависимости не существует, а значит нельзя провести и эквивалентное разбиение отношения, то отношение находится в четвертой нормальной форме (4НФ).

Эквивалентность группы отношений данному отношению означает, что соединение отношений этой группы совпадает с данным отношением.

Невозможность разбиения отношения на эквивалентную пару отношений опять же еще не означает отсутствие зависимостей внутри отношения. Пусть отношение образуют атрибуты (группы атрибутов) А, В, С, и отношение (А, В, С) находится в 4НФ. Однако может существовать разбиение отношения на группу из трех отношений (А, В), (А, С), (В,С), эквивалентных данному отношению. Если не существует разбиения данного отношения ни на какую группу отношений, эквивалентную данному, то отношение находится в пятой нормальной форме (5НФ).

Рассмотрим на примерах приведение отношения к 4НФ и 5НФ.

Пусть задана следующая таблица (отношение) со столбцами "товар", "поставщик", "потребитель". Соответствующие значения будем для краткости представлять кодами.

Таблица 1.1. Отношение «товар-поставщик-потребитель»

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

Таблица 1.2. Отношение «товар — поставщик»

Таблица 1.3. Отношение «товар - потребитель»

Их соединение по столбцу "товар" дает исходную таблицу, которая изначально не находится в 4НФ и нормализуется данным разбиением.

Рассмотрим еще одну таблицу с аналогичными столбцами.

Таблица 1.4. Отношение «товар-поставщик-потребитель»

Данная таблица уже находится в 4НФ (не существует ее эквивалент- ' ного разбиения на пару таблиц вне зависимости от ее семантики). В то же j время известно, что товары производятся не всеми поставщиками и используются не всеми потребителями и не все потребители имеют контакты с поставщиками. Соответствующие связи можно задать в виде трех отношений.

Таблица 1.5. Отношение «товар-поставщик»

Таблица 1.6. Отношение «товар -потребитель»

Таблица 1.7. Отношение «поставщик- потребитель»

Их последовательное соединение по столбцу "товар" и паре столбцов "поставщик", "потребитель" дает исходную таблицу, которая изначально не находится в 5НФ и нормализуется данным разбиением. (Отметим, что порядок соединения не влияет на результат).

Прежде чем двинуться дальше, сделаем несколько замечаний по содержанию 4НФ и 5НФ.

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

Во вторых, нормализация данных к 4НФ, 5НФ, хотя далеко не всегда приводит к сокращению объемов хранимых данных, дает, тем не менее, то преимущество, что позволяет относительно безболезненно добавлять и удалять строки в соответствующих отношениях. При нарушении требований нормализации для этого пришлось бы делать групповые операции с дополнительными проверками, что при отсутствии явно выделенных смысловых зависимостей чревато ошибками.

Связи между данными, логическая Хранимые в базе данные таблицы логически связаны между собой.;

В частности, одним из результатов процедур нормализации данных;

является появление множества логически связанных таблиц. В общем!

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

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

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

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

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

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

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

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

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

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

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

Кроме того, дополнительный контроль, в том числе и связанный с анализом данных, хранящихся в разных таблицах, может осуществляться специальными программами-триггерами, которые будут включаться при попытке любого изменения данных. Для каждой такой попытки, а именно добавления (insert), модификации (update) и удаления (delete), можно задать свой триггер. Триггеры могут включаться как непосредственно перед соответствующим действием, так и после него. Общее количество триггеров не ограничено. Если триггеру "не понравятся" вводимые данные, он может выдать сообщение об ошибке и прервать соответствующую транзакцию.

Единственным ограничением на используемые методы контроля является то, что сам контроль требует определенных затрат времени и других ресурсов процессора. Поэтому в некоторых случаях массового ввода данных, про которые заранее известно, что они корректны, можно отключать средства контроля, что позволяет заметно сократить время загрузки данных. Правда, перед тем как принимать подобное решение, следует хорошо подумать, не приведет ли это к нежелательным последствиям.

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

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

Следует помнить, что при всяком обновлении данных должны обновляться и индексы. Таким образом, платой за быстрый поиск является увеличение затрат времени на обновление данных. Кроме того, сами индексы после большого числа обновлений становятся несбалансированными, вследствие чего время поиска по ним возрастает. Их можно перестроить, но это также требует затрат времени.

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

Если имеется индекс по группе полей, то поиск по первому из полей групп может прямо использовать этот индекс, следовательно, нет смысла по нему делать отдельный индекс. Если поиск по каким-либо полям редок, то построение по ним индекса тоже не эффективно. К сожалению, более конкретные рекомендации по определению состава индексов едва ли возможны. Оптимальный выбор зависит и от структуры базы и от характера ее использования.

Глава Основы языка SQL 2.1. Унификация доступа к данным Нужно сразу отметить, что как бы эффективно не было организовано хранение данных, одного этого не достаточно для обеспечения решения проблемы реализации удобного и быстрого поиска и обновления информации. Огромное значение имеют алгоритмы процедур доступа к хранимым данным. Появление новых технических и программных решений влечет необходимость изменения алгоритмов поиска и обновления данных, но при этом необходимо обеспечить и возможность использования ранее разработанных программных продуктов. Кроме того, разработка прикладных систем не должна зависеть от выбора той или иной системы управления данными. Последнее особенно важно при изменении масштаба системы, что очень трудно предвидеть заранее, да и дополнительные затраты на ранних этапах попросту невыгодны. При этом очень хочется иметь возможность использовать полученные результаты и при новых разработках. Все это подталкивает к необходимости стандартизации, если не самих алгоритмов доступа к данным, что едва ли возможно без значительных потерь при переходе от одной системы управления базами данных (СУБД) к другой, то хотя бы описаний процедур доступа. В самом деле, если мы на каком-либо языке сумеем описать какие данные мы хотим получить или что мы хотим с ними сделать, а сам алгоритм будет строить компилятор соответствующей СУБД, то описание будет одним и тем же для всех СУБД, понимающих этот язык.

Именно эту проблему и решает язык SQL (Structured Query Language - язык структурированных запросов). Можно много спорить о его достоинствах и недостатках. Во многом он эклектичен и неудобен. Сходные команды имеют различный синтаксис, нет многих привычных по другим языкам функций, многие решения являются результатом не тщательного анализа, а обычного компромисса между разными группами разработчиков СУБД. Словом, недостатков множество. Достоинство только одно, но оно стоит всех недостатков. На нем действительно можно описать требования к поиску и выборке данных, к их модификации, к логической структуре данных, методам поддержания логической целостности данных и их контролю. И, что самое главное, практически все фирмы разрабатывающие СУБД, приняли его как стандарт. Стандарт, конечно, развивается, но следующие версии языка всегда включают более ранние, как свою часть, что и дает необходимую совместимость. Благодаря этому разработчик может не заботиться о том, в среде какой СУБД будет работать его задача, если он, конечно, не использует слишком активно особенности конкретного диалекта SQL.

Сам язык SQL был разработан в 1970 году в компании IBM. В настоящее время он стал стандартным языком, используемым для связи с большинством систем управления базами данных, в том числе таких, как Oracle, INGRES, Informix, Sybase, SQLbase, Microsoft SQL Server, DB2, продуктами SQL/DC, Paradox, Access, Approach и многими другими.

Говоря о языке SQL нужно помнить о его главном назначении.

Во-первых, это описание запросов на поиск и изменение данных в существующей базе. Эту часть будем называть языком управления доступом к данным или языком манипулирования данными (Data Manipulation Language - DML).

Во-вторых, это средства описания хранимых данных, их структуры, правил доступа к ним. Эту часть будем называть языком определения данных (Data Definition Language - DDL).

В-третьих, это средства управления порядком доступа к данным. Эти средства образует группа операторов управления данными (Data Definition Statements). Часто их относят к языку определения данных (DDL). Основные операторы данной группы - GRANT и REVOKE.

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

Select - выборка данных, удовлетворяющих заданным условиям;

Insert - ввод новых данных;

Update - обновление существующих данных;

Delete - удаление данных.

Каждая из этих команд имеет множество вариантов, которые заслуживают отдельного рассмотрения.

2.3. Язык определения данных Для того чтобы работать с данными, необходимо их сначала описать, то есть указать их структуру, формы представления, связи, методы их контроля и многое другое. О типах данных мы еще поговорим в дальнейшем, а пока отметим только, что действий с описаниями также весьма немного. Поскольку функция поиска здесь не имеет самостоятельного значения, то таких действий, по аналогии со сказанным о хранилище данных, остается всего три. Это ввод новых описаний, модификация существующих и удаление ненужных. Каждому из этих действий соответствует своя команда:

Create - ввод новых описаний;

Alter - модификация существующих описаний;

Drop - удаление ненужных описаний.

Каждая из этих команд имеет множество вариантов, связанных как с вариантами описаний, так и с тем фактом, что в описании нуждается множество различных информационных объектов.

2.4. Язык управления порядком Поддержание целостности данных предполагает их защиту от случайного или намеренного искажения. Кроме того, доступ к отдельным данным должен быть ограничен и по соображениям их конфиденциальности. Все это требует ограничения прав доступа к данным различных групп пользователей. Последнее особенно важно в крупных системах с большим количеством пользователей. Предоставление соответствующих прав и их изъятие реализуется следующими командами.

Grant - предоставляет права доступа к специфицированным объектам данных со стороны указанных пользователей или других объектов базы.

Revoke - ликвидирует права доступа к специфицированным объектам данных со стороны указанных пользователей или других объектов базы.

2.5. Данные и метаданные И еще одно замечание. Описания хранимых данных сами по себе также являются данными. А раз так, то нет никаких причин хранить их каким-либо особенным способом, отличным от основной информации.

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

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

2.6. Уровни реализации языка SQL По самому своему существу SQL предназначен только для описания взаимодействия с базой данных. SQL не содержит средств, необходимых для разработки законченных программ и может использоваться только как их часть. В зависимости от назначения SQL может использовать один из трех уровней реализации.

Интерактивный или автономный SQL (ISQL) дает возможность пользователям непосредственно извлекать информацию из базы данных или записывать ее в базу данных. Эта уровень доступен посредством соответствующих утилит или специальных стандартных средств для работы с базами данных. В InterBase это реализуется такими утилитами, как Isql.exe и Wisql32.exe. С их помощью информация может быть загружена в базу данных или извлечена из нее и выведена на экран, печать или в файл.

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

Другое использование статического SQL - модульный язык.

В этом случае модули SQL присоединяются к приложению на 3. Динамический SQL (DSQL) - код SQL, генерируемый приложением во время его выполнения. Динамический SQL заменяет статический в тех случаях, когда необходимый код не может быть определен во время написания приложения, так как он сам зависит от действий пользователя во время выполнения приложения.

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

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

Ключевые слова и конструкции языка будем выделять жирным 2. Элементы описания будем выделять курсивом.

3. Если элемент или группа элементов являются необязательными и могут отсутствовать, то их будем помещать в квадратные скобки [ ], выделенные курсивом. Если квадратные скобки являются элементом SQL, то они будут заданы прямым шрифтом [].

4. Группа элементов, из которых только 1 должен присутствовать в конечной конструкции будем помещать в фигурные скобки { }, а сами элементы разделять вертикальной чертой |.

5. Если какое-либо значение предполагается по умолчанию, то будем выделять его подчеркиванием.

6. Списки, состоящие из группы однородных элементов, будем обозначать в виде LIST_ElemName, где LIST_ выступает в роли префикса, а содержание самого элемента будет описываться отдельно.

7. Описание элемента конструкции будем начинать с ElemName::=, после чего будем давать само описание.

8. Если элемент конструкции может быть составным, то будем его помещать в угловые скобки < >, иначе указывать непосредственно. Все элементы синтаксиса будем писать латиницей, учитывая общие требования к идентификаторам, используемым в SQL.

9. В соответствии с изложенным конструкция LIST_ElemName будет описываться как LIST_ElemName::= Глава Управление доступом в InterBase на основе SQL 3.1. Выборка данных. Команда SELECT Назначение и основные возможности команды Команда SELECT предназначена для выборки данных из базы. С ее помощью можно получить данные, удовлетворяющие заданным условием из одного или нескольких связанных объектов базы. Такими объектами являются, прежде всего, таблицы базы данных, но могут быть и обзоры, и хранимые процедуры, причем в любых сочетаниях. Выбранные данные могут быть агрегированы, отсортированы, с ними можно произвести ряд предварительных вычислений.

Базовый синтаксис команды SELECT Дадим теперь полный синтаксис команды SELECT.

SELECT [TRANSACTION T r a n s a c t i o n ] [INTO LIST_Vars] FROM LIST_ [GROUP BY LIST_] [HAVING ] [UNION [ALL] ] [PLAN ] [FOR UPDATE [OF LIST_col] ], Col [] | :Variable | NULL | USER | RDB$DB_KEY | ?

Col - имя столбца,.-: = [LIST_Dim] у - задает размерность массива.

х - задает нижнюю границу массива (если задано х:у, то индекс в масс иве меняется от х до у) :;= число I ' строка1 I Charsetname ' строка Charsetname - имя используемого набора символов, например WIN1251,WIN1252HT.fl.

. : = Любое корректное SQL выражение, дающее в результате единственное значение.

::= { COUNT (* | [ALL] | DISTINCT ) | SUM ( [ALL] | DISTINCT ) | AVG ( [ALL] | DISTINCT ) | MAX ( [ALL] | DISTINCT ) | MIN ( [ALL] | DISTINCT ) | CAST ( AS ) | UPPER ( ) I GEN_ID (generator, ) В версии InterBase 6 добавлена также такая функция как:

EXTRACT(part FROM ) Управление доступом в InterBase на основе SQL part ::= {DAY | HOUR | MINUTE | MONTH | SECOND | WEEKDAY | YEAR | YEARDAY } ::= Любое корректное SQL выражение, дающее в результате единственное значение типа дата - время.

Vars ::= :Var ::= | table | view | procedure [( [LIST_] )] [alias] [OUTER] } JOIN { | ()} | [NOT] BETWEEN AND | [NOT] LIKE [ESCAPE ] ::= SELECT с одним столбцом, возвращающий одну строку, ::= SELECT с одним столбцом, возвращающий несколько (возможно 0) строк, возвращающий несколько (возможно 0) строк.

[JOIN | MERGE] (LIST) ::= {table | alias} NATURAL | INDEX (LIST_index) | ORDER index DESC [ENDING] ] Отметим, что конструкции INTO и FOR UPDATE доступны только в процедурах и триггерах базы данных, либо во внедренном SQL в приложениях.

Конструкция команды SELECT достаточно сложная, поэтому разбор ее возможностей проведем поэтапно и приведем соответствующие примеры.

Выборка таблицы целиком SELECT * FROM ;

* после SELECT означает выбор всего набора полей таблицы, имя которой задано в tableref.

Пример 3.

SELECT * FROM TREADER;

В примерах используется главным образом база TESTBASE.GDB. Ее структура и содержание приведены в приложении. Случаи, когда для примеров используются другие данные, будут оговариваться отдельно.

Таблица 3.1. Список читателей (включая служебные данные)

RDNAME

RDNUMB

UNIKEY

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

Выборка заданного списка полей таблицы список vail, val2,... задает перечень имен полей таблицы, подлежащих выборке.

SELECT RDNUMB,RDNAME FROM TREADER;

Таблица 3.2. Список читателей (выборочные данные)

RDNUMB RDNAME

А теперь отсортируем их по фамилиям, указав имя поля, по которому ведется сортировка,

SELECT RDNUMB,RDNAME FROM TREADER ORDER BY RDNAME;

или просто его порядковый номер в списке полей выборки (иногда это единственно возможное решение).

SELECT RDNUMB,RDNAME FROM TREADER ORDER BY 2;

Таблица 3.3. Список читателей (выборочные отсортированные данные)

RDNUMB RDNAME

1267- 1401- 1273- 1400- Пока мы брали из таблицы все ее строки, но это нужно далеко не всегда. Выберем теперь только новых читателей (Номер билета >1300).

Пример 3. SELECT RDNUMB, RDNAME FROM TREADER where RDNUMB>='1400' ORDER BY 2;

Таблица З.4. Выборка из списка читателей (выборочные отсортированные данные)

RDNUMB RDNAME

Согласно требованиям нормализации мы разбили данные на множество таблиц, но это разбиение никак не связано с тем, какие данные мы хотим получить. Последнее означает, что необходимо уметь выбирать данные одновременно из нескольких таблиц. Например, так Пример 3.

SELECT * FROM TREADER, TBOOK;

Таблица 3.5. Выборка списка читателей и книг Информационная ценность такого выбора (прямое декартово произведение) явно невелика, выбирать следует только связанные между собой данные, например, связав читателей с взятыми ими книгами, явно указывая поля, которые нам интересны:

SELECT TREADER.rdnumb, TREADER.rdname, TBOOK.booknm

FROM TREADER, TBOOK, TBOOK_READER

where tbook_reader.reader=treader.unikey and tbook_reader.bookkey=tbook.unikey;

Поскольку имена полей в разных таблицах могут повторяться, то их нужно уточнять, предваряя именем таблицы или заменяющим его алиасом, который конечно нужно объявить, например так Пример 3. SELECT a.rdnumb, a.rdname, b.booknm FROM TREADER a, TBOOK b, TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey;

Таблица 3.6. Выборка списка читателей и взятых ими книг

RDNAME BOOKNM

RDNUMB

1267- Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию 1369- 1456- 1273-92 Гребенкина Н. The history of England. Absolute Monarchy 1400-00 Пащенко О. Введение в технологию ATM 1400-00 Пащенко О. Word 6 for Windows Книги, правда, получились без авторов. Попробуем теперь вытащить фамилии авторов.

Пример 3. SELECT a.rdnumb, a.rdname, b.booknm, c.AUNAME FROM TREADER a, TBOOK b, TBOOK_READER ab, TAUTHOR c, TBOOK_AUTHOR bc where ab.reader=a.unikey and ab.bookkey=b.unikey and b.unikey= bc.bookkey and bc.author=c.author;

Таблица З.7. Выборка списка читателей и взятых ими книг с указанием

RDNUMB RDNAME BOOKNM AUNAME

1400-00 Пащенко О. Word 6 for Windows Фаненштих Клаус 1400-00 Пащенко О. Word 6 for Windows Хаселир Райнер Г.

AUNAME

BOOKNM

RDNAME

RDNUMB

1400- 1400- 1400- Гребенкина Н. The history of England. Ab- Бурова И.И.

1273- Арцибашев С. Тесты. Сборник 11 класс. без авторов 1267- Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию Арцибашев С. Математические вопросы Ладыжинская Ольга 1267- 1267-89 Арцибашев С. Тайна Теперь, правда, каждая книга повторяется столько раз, сколько у нее авторов. Поскольку на каждого автора есть отдельная строка, то обойти это просто так невозможно, разве что ограничиться только первым автором в списке:

Пример 3. SELECT a.rdnumb, a.rdname, b.booknm, min(с.AUNAME) FROM TREADER a, TBOOK b, TBOOK_READER ab, TAUTHOR c, where ab.reader=a.unikey and ab.bookkey=b.unikey and b.unikey= bc.bookkey and bc.author=c.author GROUP BY a.rdnumb, a.rdname, b.booknm Таблица 3.8. Выборка списка читателей и взятых ими книг с указанием

RDNUMB BOOKNM MIN

RDNAME

1267-89 Арцибашев С. Математические воАлександровна 1267-89 Арцибашев С. Тайна 1267-89 Арцибашев С. Тесты. Сборник 11 класс. без авторов Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию 1400-00 Пащенко О. Введение в техноло- Буассо Марк Использование функции MIN позволило нам выбирать первого в алфавитном списке автора из группы. Группировка задается перечнем полей, не охватываемых агрегатными функциями, которые необходимо перечислить в опции GROUP BY.

Перейдем теперь к более систематическому рассмотрению команды SELECT.

SELECT ПО ОТДЕЛЬНОЙ ТАБЛИЦЕ. ЗАДАНИЕ ВЫБИРАЕМЫХ ПОЛЕЙ

Рассмотрим подробнее выборку данных из отдельной таблицы:

SELECT [DISTINCT | ALL] {* | L I S T _ < v a l > } Символ * задает выборку всех полей таблицы. Примером может служить уже рассмотренная конструкция примера 3.1:

SELECT * FROM TREADER;

Результат ее работы приведен в табл. 3.1.

Для задания конкретного перечня столбцов их необходимо задать явно, например, как в примере 3.2:

SELECT RDNUMB,RDNAME FROM TREADER;

Результат ее работы приведен в табл. 3.2.

Теперь несколько слов о параметрах запроса DISTINCT и ALL.

Значение ALL (если не указано ничего, то принимается ALL) означает, что будут выбраны все строки таблицы, удовлетворяющие условиям выборки. Об условиях выборки мы поговорим чуть позже.

Значение DISTINCT означает, что будут выбраны только различные строки таблицы. Рассмотрим пример выборки из таблицы читателей, имеющих на руках книги, с параметрами DISTINCT и ALL. Выбирать будем их индивидуальные коды (для выбора фамилий обойтись одной таблицей нельзя, такую выборку мы рассмотрим чуть дальше).

Пример 3. select ALL reader from TBOOK_READER select DISTINCT reader from TBOOK_READER Таблица 3.9. Выборка списка читателей с условиями ALL и DISTINCT select ALL reader from select DISTINCT reader from

TBOOK_READER TBOOK_READER

READER READER

условия ВЫБОРКИ Условия выборки задаются конструкцией WHERE ].

| [NOT] LIKE [ESCAPE ] | IS [NOT] NULL {ALL | SOME | ANY} () | EXISTS ( ) | SINGULAR ( ) | [NOT] CONTAINING | NOT | AND } Первый случай: !=}) Пример 3. select UNIKEY, BOOKNM from TBOOK where MATHERKEY=5;

Таблица 3.10. Список книг - беллетристики

UNIKEY BOOKNM

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

Например, выберем всех читателей, чья фамилия лежит в диапазоне от А до Н.

Пример 3. select RDNUMB, RDNAME from TREADER where RDNAME>='A' AND RDNAME= | после конструкции FROM. Формат такого объединения имеет вид ON | () [OUTER] } JOIN JOIN... ON - соединение строк таблиц на основе условия, заданного после ON.

Тип соединения задается ключевым словом INNER или OUTER; если ни одно из них не указано, то принимается INNER.

Тип соединения INNER задает "внутреннее соединение". В таблицах соединяются только те строки, для которых выполняется Ключевое слово OUTER (внешний) не является обязательным, оно не используется ни в каких операциях с данными и имеет смысл только в комбинации со спецификацией типа соединения (LEFT, RIGHT, FULL).

Таблицу в соединении будем называть левой, если она стоит перед (слева) ключевым словом JOIN, и правой, если она стоит после (справа) от него.

LEFT (OUTER) - тип соединения "левое (внешнее)". Левое соединение таблиц включает в себя все строки из левой таблицы и те строки из правой таблицы, для которых выполняется. Для строк из левой таблицы, для которых не найдено соответствия в правой, в столбцы, извлекаемые из правой таблицы, заносятся значения NULL.

RIGHT (OUTER) - тип соединения " правое (внешнее)". Правое соединение таблиц включает в себя все строки из правой таблицы и те строки из левой таблицы, для которых выполняется.

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

FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений.

При использовании внутреннего соединения конструкции JOIN и WHERE полностью взаимозаменяемы. Использование соединенных таблиц часто облегчает восприятие оператора SELECT, особенно, когда используется естественное соединение. Если не использовать соединенные таблицы, то при выборе данных из нескольких таблиц необходимо явно указывать условия соединения в разделе WHERE. Если при этом пользователь указывает сложные критерии отбора строк, то в разделе WHERE смешиваются такие семантически различные понятия, как условия связи таблиц, так и условия отбора строк. Каких-либо иных дополнительных преимуществ конструкция JOIN перед конструкцией WHERE не имеет, так что выбор представляется делом вкуса.

Перепишем запрос из примера 3.6. с использованием конструкции JOIN.

Пример 3. SELECT a.rdnumb, a.rdname, b.booknm

FROM TREADER a JOIN TBOOK b on EXISTS(SELECT * FROM

TBOOK_READER ab where a b. r e a d e r = a. u n i k e y and ab.bookkey=b.unikey);

Таблица. 3.22. Выборка списка читателей и взятых ими книг

RDNUMB RDNAME BOOKNM

1400-00 Пащенко О.

1273-92 Гребенкина Н. The history of England. Absolute Monarchy 1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие 1267-89 Арцибашев С. Математические вопросы динамики вязкой А теперь проделаем то же самое, но используя внешние соединения.

Пример 3. SELECT a.rdnumb, a.rdname, b.booknm F O TREADER a LEFT JOIN TBOOK b on EXISTS(SELECT * F O TBOOK_READER ab where a b. r e a d e r = a. u n i k e y and ab.bookkey=b.unikey);

Таблица 3.23. Выборка списка всех читателей и взятых ими книг

RDNUMB RDNAME BOOKNM

1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию

BOOKNM

RDNVMB RDNAME

1267- 1267- 1369- 1456- 1273-92 Гребенкина Н.

1400- 1400- 7407- Пример 3. SELECT a.rdnumb, a.rdname, b.booknm FROM TREADER a RIGHT JOIN TBOOK b on EXISTS(SELECT * FROM TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey);

Таблица 3.24. Выборка списка читателей и всех книг

RDNUMB RDNAME BOOKNM

BOOKNM

RDNAME

RDNUMB

1400- 1273- 1267- 1267-89 Арцибашев С.

1267-89 Арцибашев С. Тайна Пример 3. SELECT а.rdnumb, а.rdname, b.booknm FROM TREADER a FULL JOIN TBOOK b on EXISTS(SELECT * FROM TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey);

Таблица 3.25. Выборка списка всех читателей и всех книг

RDNUMB RDNAME BOOKNM

BOOKNM

RDNUMB RDNAME

1400-00 Пащенко О.

Гребенкина Н. The history of England. Absolute Monarchy 1273- 1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию 1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости 1267-89 Арцибашев С. Тайна 1369- 1401- В табл. 3.23-3.25 выделены: курсивом строки, появившиеся дополнительно за счет использования левого соединения, подчеркиванием правого.

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

Пример 3. select UNIKEY, (SELECT auname from tauthor where tbook_author.author=tauthor.author) auname, (SELECT BOOKNM from tbook where tbook_author.bookkey=tbook.unikey) bookname from tbook_author;

Таблица 3.26. Выборка списка авторов и книг

BOOKNAME

AUNAME

UNIKEY

Подбельский Вадим Валериевич 71 Розенталь Д.Э. Справочник по правописанию и литературной правке Следует, правда, заметить, что в данном случае то же самое можно было бы получить заметно проще:

select a.UNIKEY, b.auname, с. B O N from tbook_author a, tauthor b, tbook с where a.author=b.author and a.bookkey=c.unikey

ДОПОЛНИТЕЛЬНАЯ ОБРАБОТКА РЕЗУЛЬТАТОВ, ФИЛЬТРАЦИЯ

И СОРТИРОВКА

В ряде случаев уже выбранные данные нуждаются в дополнительной обработке. Часть такой обработки можно выполнить в рамках SQL запросов.

Прежде всего, это упорядочение данных. Полученные данные могут быть отсортированы по значениям одного или нескольких столбцов запроса. Сортировка задается конструкцией ORDER BY:

[ORDER BY ] DESC [ENDING] ] [, ].

Как видно из описания синтаксиса, в списке упорядочения могут быть как имена столбцов, так и их порядковые номера (но не выражения или алиасы).

Пример 3. SELECT unikey, bnumber n1, bnumber*bnumber n

FROM TBOOK_PLACE

ORDER BY n1;

Результат- "Dynamic SQL Error. SQL error code = -206. Column unknown. N1".

А если указать вместо алиаса имя столбца, то все будет в порядке.

Пример 3. SELECT unikey, bnumber nl, bnumber*bnumber n

FROM TBOOK_PLACE

ORDER BY bnumber;

Таблица 3.27. Тестовая выборка №1 из TBOOK_PLACE

UNIKEY

А как отсортировать по столбцу N2, которому не соответствует ни одно поле таблицы? В этом случае единственный возможный путь - явно указать номер столбца в списке выборки. В нашем случае - 3. Поскольку одному значению поля N2 соответствует несколько строк, то для обеспечения однозначности выборки упорядочим ее дополнительно по убыванию значений первого столбца.

SELECT unikey, bnumber nl, bnumber*bnumber n

FROM TBOOK_PLACE

ORDER BY 3, 1 DESC;

ИЛИ SELECT unikey, bnumber nl, bnumber*bnumber n

FROM TBOOK_PLACE

ORDER BY 3, UNIKEY DESC;

Конструкция ASC[ENDING], DESCENDING] указывает на вид сортировки: по возрастанию или убыванию соответственно. Если не указано ничего, то принимается ASC.

Таблица 3.28. Тестовая выборка №2 из ТВООК_fLACE Сортировка, конечно, не является единственным видом обработки результатов. Мы уже рассматривали получение суммарных данных в запросах на основе применения агрегатных функций. При этом мы получали единственную строку итогов по базе, однако в ряде случаев необходимо провести агрегирование данных по заданной группе признаков. Например, это могут быть данные о суммарных продажах по филиалам торговой фирмы, объемы производства по цехам, по месяцам года и тому подобное. Таких примеров можно привести множество. Для реализации подобных задач применяется конструкция GROUP BY:

Рассмотрим модификацию примера 3.27, который у нас не получился. Для этого включим в него группировку по читателям.

select SUM(l), KDNAME FROM TREADER GROUP BY RDNAME;

Получим список читателей и единичек. Запрос сработал, но результат малоинтересен.

Теперь получим такой же список, но по каждому читателю выдадим количество книг, которые он взял.

Пример 3. select a.RDNAME, SUM(l) FROM TREADER a, TBOOK_READER b where (a.UNIKEY=b.READER) GROUP BY RDNAME;

Таблица 3.29. Список читателей с указанием количества взятых ими

RDNAME

Арцибашев С.

Гребенкина Н.

Пащенко О.

Светлова В.

Стародуб Е.

Отметим, что в списке выборки могут находиться только агрегируемые выражения и выражения, перечисленные в списке GROUP BY.

Следующая конструкция HAVING может показаться излишней. В самом деле, фильтрация данных прекрасно выполняется с помощью конструкции WHERE. В чем отличие конструкции HAVING от WHERE? Конструкция WHERE применяется к каждой строке и определяет, будет ли она включена в выборку или нет. Конструкция HAVING применяется к уже полученной выборке и фильтрует ее.

В большинстве случаев последнее просто неэффективно, но не всегда, поскольку некоторые данные в процессе выборки просто неизвестны.

Например, в случае запроса в примере 3.42 мы не можем отбросить читателей, взявших менее 2 книг, поскольку пока расчет не выполнен, это просто неизвестно. Но когда он уже сделан, такой фильтр легко задать, используя HAVING.

Пример 3. select a.RDNAME, SUM(l) sumbook FROM TREADER a, TBOOK_READER b where (a.UNIKEY=b.READER) GROUP BY RDNAME HAVING SUM(1)>1;

Таблица 3.30. Список читателей, взявших более 1 книги с указанием

RDNAME SUMBOOK

Арцибашев С.

Пащенко О.

ОБЪЕДИНЕНИЕ НЕСКОЛЬКИХ ОДНОТИПНЫХ ЗАПРОСОВ В ОДИН

UNION [ALL] объединяет результаты выборки из двух или более таблиц, которые идентичны по структуре (результаты выборки, а не таблицы; сами таблицы могут иметь и разную структуру). Если используется конструкция ALL, то в выборке могут присутствовать и одинаковые строки, полученные из разных таблиц.

Если поля или выражения, выбираемые из разных таблиц и помещаемые в один столбец, имеют разные имена, то им необходимо дать псевдоним.

Пример 3. select RDNAME, CAST("Активный читатель" as VARCHAR(20)) RD_Active

FROM TREADER a

WHERE EXISTS(

b.READER=a.UNIKEY HAVING count(1)>2) UNION select RDNAME, CAST("Средний читатель" as VARCHAR(20)) RD_Active

FROM TREADER a

WHERE EXISTS(

b.READER=a.UNIKEY HAVING count(1)0) UNION select RDNAME, CAST("Пассивный читатель" as VARCHAR(20)) RD_Active

FROM TREADER a

WHERE NOT EXISTS (select * from TBOOK_READER b where b.READER=a.UNIKEY);

Хаблица 3.31. Список читателей по их активности

RDNAME

Арцибашев С.

Грамотный Н.Е.

Гребенкина Н.

Пащенко О.

Светлова В.

Стародуб Е.

В списке полей выборки могут появляться и отдельные элементы полей - массивы.

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

3.2. Добавление данных.

Назначение и основные возможности команды Команда INSERT предназначена для добавления данных в базу. С ее помощью можно добавить в указанную таблицу или представление одну или сразу несколько строк. Возможность применения операции добавления каким-либо пользователем определяется тем, какие права доступа были ему даны (см. команды GRANT и REVOKE).

Синтаксис команды Insert Полный синтаксис команды Insert имеет вид

INSERT INTO

[(LIST_col)] { A U S (LIST_) | } ;

::= имя_таблицы или имя_ обзора | NULL | USER | RDB$DB_KEY | ?

} [COLLATE collation] = num |' string' | charsetname ' string ' = любое допустимое в SQL выражение, имеющее своим результатом единственное значения для столбца | GEN_ID ( generator, ) В версиях InterBase, начиная с 6, допустимо использовать также функцию EXTRACT(part FROM ).

part ::= [DAY | HOUR | MINUTE | MONTH | SECOND | WEEKDAY|YEAR|YEARDAY] ::= Любое корректное SQL выражение, дающее в результате единственное значение типа дата - время.

udf- пользовательская функция = SELECT, возвращающий несколько строк (возможно 0) со столбцами в том же порядке и того же типа, что и в списке, заданном в конструкции INTO.

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

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

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

Добавление отдельной строки При добавлении отдельной строки в списке INTO перечисляются столбцы, в которые вводятся значения, а сами значения задаются в списке VALUES. Вместо списка столбцов можно указать символ "*"; в этом случае предполагается, что вводятся значения всех столбцов таблицы в специфицированном при создании таблицы порядке. Поскольку порядок столбцов может изменяться, то такая конструкция является потенциально опасной и лучше ее не применять.

Рассмотрим добавление строки в таблицу ТВООК.

Пример 3. Таблица 3.32. Содержание TREADER перед вводом (select * from

UNIKEY RDNUMB RDNAME

INSERT INTO TREADER (RDNUMB, RDNAME)

VALUES ('1111-98', 'Пугачева А.Б.');

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

Возможный результат приведен в табл. 3-33. В Вашем случае он может быть и иным в зависимости от состояния генератора SYSNUMBER.

Отметим, что триггер для таблицы TREADER в нашем случае предусматривает обязательность указания полей RDNUMB и RDNAME, поэтому Вам просто не удастся выполнить INSERT без их указания.

Таблица 3.33. Содержание TREADER после ввода (select * from

RDNAME

UNIKEY RDNUMB

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

Здесь все время говорилось о команде INSERT применительно к таблицам, однако она точно так же применима и для представлений, но с некоторыми оговорками, которые мы сделаем при рассмотрении представлений.

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

Добавление группы строк Одной командой INSERT можно добавить в таблицу и несколько строк. Перечень добавляемых строк в этом случае задается конструкцией SELECT.

В качестве примера рассмотрим ту же таблицу TREADER. Добавим в нее в качестве читателей авторов книг из таблицы TAUTHOR, а номера читательских билетов для них придумаем сами. Например, номер можно сформировать из поля AUTHOR, к которому будем добавлять цифры 00.

INSERT INTO TREADER (RDNUMB, RDNAME)

SELECT CAST(AUTHOR as VARCHAR(8)) || '-00', AUNAME FROM

TAUTHOR

where АUNАMЕ [WHERE | W E E CURRENT ::= col = Здесь мы рассмотрим только первую часть синтаксиса. По второй части лишь отметим, что он обеспечивает изменение одной строки, соответствующей текущей строке курсора. Работа с курсорами возможна только в процедурах и триггерах базы данных, либо во внедренном SQL в приложениях и будет рассмотрена позже.

col [] | : variable | udf ( [ [,...]]) | NULL | USER | ?} [COLLATE collation] ::= [LIST_Dim] Dim ::.= [x:]y у - задает размерность массива.

х - задает нижнюю границу массива (если задано х:у, то индекс в массиве меняется от х до у) ::= num | ' string' | charsetname ' string' ::= любое допустимое в SQL выражение, имеющее своим результатом единственное значения для столбца | GEN_ID ( generator, ) В версиях InterBase, начиная с 6, допустимо использовать также функцию EXTRACT(part FROM ).

WEEKDAY | YEAR | YEARDAY ] ::= Любое корректное SQL выражение, дающее в результате единственное значение типа дата - время.

udf - пользовательская функция | [NOT] LIKE [ESCAPE ] |IS [NOT] NULL Такая конструкция может оказаться неудобной, если часть данных из диапазона нужно исключить. В этом случае удобнее задать список значений ключевого поля явно.

Пример 3.

DELETE FROM TREADER

where UNIKEY in (84,85,86);

Глава Описание данных на основе SQL 4.1. Организация данных в InterBase.

Данные в реляционных базах данных, в частности в InterBase, хранятся в плоских таблицах. В каждом столбце таблицы хранятся данные одного определенного типа. При этом нужно помнить, что данные, по которым осуществляется поиск, должны быть простого (неструктурированного) типа, для которого определены операции сравнения. Данные других типов допускаются, если можно так сказать, "в порядке исключения" и для работы с ними используются средства, выходящие за пределы стандарта SQL.

Приведем перечень обрабатываемых в InterBase типов данных.

SMALLINT - слово, короткое целое (2-байтовое) со знаком (от -32 768 до 32 767).

INTEGER - двойное слово, длинное целое (4-байтовое) со знаком (от -2 147 483 648 до 2 147 483 647).

FLOAT - числа с плавающей точкой одинарной точности (4 байта) значащих цифр.

DOUBLE PRECISION - числа с плавающей точкой двойной точности (8 байтов) - 15 значащих цифр.

DECIMAL (размер, точность) / NUMERIC (размер, точность). Размер переменной (от 1 до 15) указывает гарантированную точность переменной, то есть число значащих цифр. Точность (от 1 до 15) задает число цифр после запятой (должно быть меньше или равно размеру). Например, DECIMAL(10,3) содержит числа в формате: ppppppp.sss Типы данных DECIMAL и NUMERIC имеют смысл только для внешнего представления данных. В базе они реально хранятся в одном из основных числовых форматов (SMALLINT, INTEGER, FLOAT или DOUBLE PRECISION).

• Если размер и точность не указаны, то данные хранятся как • Если точность не указана, то принимается 0. Хранимый тип при этом будет зависеть от размера. Если размер меньше 5, то SMALLINT. Если размер е [5, 9], то INTEGER. Если размер • Если указаны и размер, и точность, то хранимый тип будет зависеть от введенной величины размера. Если размер меньше 5, то SMALLINT. Если размер е [5, 9], то INTEGER. Если размер больше 9, то DOUBLE PRECISION. То есть, числа типа DATE в версии до 6 или TIMESTAMP в версиях от 6 (8 байт) с 1.01.100 до 29.02.32768, включает также данные о времени; DATE в версии от 6 - 4 байта (только дата); TIME в версии от 6 - 4 байта (только время).

CHAR(n) / CHARACTER(n) n символов (от 1 до 32 767 байт) - строка фиксированной длины; максимальная длина - 32К.

CHARACTER VARYING(n) / VARCHAR(n) / CHAR VARYING(n) n символов (от 1 до 32 767 байт) - строка переменной длины; максимальная длина - 32К.

NCHAR(n) / NATIONAL CHARACTER(n) / NATIONAL CHAR(n) n символов (от 1 до 32 767 байт) - строка фиксированной длины, использующая кодовый набор ISO8859_1.

NCHAR VARYING (n) / NATIONAL CHARACTER VARYING (n) / NATIONAL CHAR VARYING (n) n символов (от 1 до 32 767 байт) строка переменной длины, использующая кодовый набор ISO8859_1.

BLOB [SUB_TYPE { int | subtype_name}] [SEGMENT SIZE int] [CHARACTER SET charname] / BLOB [( seglen [, subtype])].

• 0 - неструктурированный, обычно используется для двоичных данных или данных неопределенного типа • 2 - двоичное языковое представление BLR (Binary language representation) • 3 - Access control list • 5 - закодированное описание метаданных текущей таблицы • 6 - описание ненормально завершенной транзакции к нескольким {SMALLINT | INTEGER \ FLOAT | DOUBLE PRECISION)[ < a r ray_dim>] | {DECIMAL | NUMERIC} [(precision [, scale])] [] | {DATE | TIME | TIMESTAMP) []

| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}

[( int)] [] [CHARACTER SET charname]

| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}

[VARYING] [(int)][] | BLOB [SUB_TYPE { i n t | subtype_name}] [SEGMENT SIZE int] [CHARACTER SET charname] | BLOB [( seglen [, subtype])] :::= x[::y] Замечание 1. Квадратные скобки при описании массива являются синтаксическим элементом, а не признаком его необязательности.

Замечание 2. Тип данных не может быть переопределен при использовании домена в описании таблиц.

Замечание 3. Типы данных TIME и TIMESTAMP допустимы в версиях, начиная с 6.0..

Подробнее о типах данных уже говорилось выше.

DEFAULT {literal | NULL | USER} - задание значения по умолчанию.

Значения по умолчанию присваивается соответствующему атрибуту при создании новой строки в таблице, если его значение не указано явно (например, отсутствует в списке ввода команды Insert), literal указывает значение явно, NULL задает признак "Нет значения", USER - имя пользователя, создающего запись. Для полей типа "дата" можно указать NOW в этом случае вводится текущая дата.

CHECK - dom_condition задает ограничение (описание контроля данных при вводе и изменении).

/ VALUE [NOT] BETWEEN AND / VALUE [NOT] LIKE [ESCAPE ] / VALUE [NOT] IN ( LIST_) | VALUE IS [NOT] NULL | VALUE [NOT] CONTAINING < v a l > | VALUE [NOT] STARTING [WITH] | NOT Подчеркнем, что при использовании конструкции CHECK необходимо помнить следующее:

• Домены создаются независимо друг от друга и, тем более, от каких-либо таблиц, следовательно, CHECK в домене не может ссылаться ни на какой другой домен или столбец таблицы.

• Домен может иметь только одну конструкцию CHECK.

• Конструкция CHECK не может быть переопределена при описании атрибута (поля) таблицы. Если при описании поля в таблице, ссылающегося на домен, имеющий CHECK, указана своя конструкция CHECK, то действовать будут оба ограничения, то есть будет работать конструкция CHECK () AND Пример 4. Объявляет тип массива из 12 элементов.

Пример 4.

CREATE DOMAIN USERNAME AS VARCHAR(20) DEFAULT USER;

Объявляет тип поля для хранения имени пользователя. Если имя пользователя при создании новой записи явно не указывается, то в него будет заноситься имя того пользователя, который создает запись.

Пример 4.

CREATE DOMAIN MONTH AS SMALLINT CHECK( VALUE BETWEEN 1 AND

12);

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

Пример 4. CREATE DOMAIN D_ELEM AS CHAR(2) CHECK (VALUE IN ('Au', 'Ag', 'Pt', 'Pd', 'Os', 'Ir', 'Rb', 'Rt'));

Задает допустимые значения для типа полей задания драгоценных металлов.

Пример 4.

CREATE DOMAIN PVEIGHT AS NUMERIC(12, 2) DEFAULT NULL CHECK

((VALUE IS NULL) OR (VALUE>1.25));

Задает допустимые значения для типа полей задания весовых характеристик (если вес не задан, то NULL, иначе - не меньше технологически минимального веса для данного производства).

Теперь при создании таблицы можно использовать введенные домены.

Пример 4.

CREATE TABLE ABX (

A MVEIGHT, В USERNAME, С MONTH, D D_ELEM, Е PVEIGHT, KEY INTEGER,...

Изменение доменов Изменение доменов осуществляется командой ALTER DOMAIN.

С помощью команды ALTER DOMAIN можно изменить любые характеристики домена, кроме типа данных (включая размеры массива) и установок NOT NULL. Сделанные изменения воздействуют на атрибуты (поля) всех таблиц, где использовался измененный домен.

Команду ALTER DOMAIN может выдать либо его создатель, либо пользователь SYSDBA, либо другой пользователь с правами системного администратора.

Для изменения типа поля или установки NOT NULL необходимо удалить домен командой DROP DOMAIN, если это возможно (если домен используется для описания столбцов каких-либо таблиц, то удалить его нельзя, поскольку они при этом «подвисают»), а затем создать его снова с требуемыми характеристиками.

Синтаксис команды ALTER DOMAIN выглядит следующим образом:

ALTER DOMAIN name { [SET DEFAULT { literal / NULL / USER}] | [DROP DEFAULT] | [DROP CONSTRAINT] Конструкции, name и literal имеют тот же синтаксис, что и в команде CREATE DOMAIN.

Приведем примеры использования команды ALTER DOMAIN, основываясь на примерах для CREATE DOMAIN.

Пример 4.

ALTER DOMAIN D_ELEM DROP CONSTRAINT;

'Na', ' K ' ) ) ;

Выполнить замену ограничения одной командой нельзя! По синтаксису команды ограничение можно или удалить или добавить, прямая замена невозможна, так что замену следует делать в два этапа.

Пример 4. ALTER DOMAIN USERNAME SET DEFAULT '***';

Замена значения по умолчанию. Новая установка заменяет старую.

Удаление доменов осуществляется командой DROP DOMAIN. С помощью этой команды можно удалить описание домена.

Если домен используется в каких-либо таблицах, то удалить его нельзя (соответствующая команда завершится аварийно). В такой ситуации необходимо предварительно удалить в таблицах все столбцы, использующие домен, который мы хотим удалить.

Синтаксис команды DROP DOMAIN выглядит следующим образом:

DROP DOMAIN name;

Пример 4. DROP DOMAIN D_ELEM;

4.3. Таблицы Перед тем как перейти к созданию таблиц, необходимо выполнить проектирование базы данных и нормализацию таблиц.

Далее следует определить, какие таблицы и с какими столбцами (полями) подлежат созданию.

Создать, если это не было сделано ранее, саму базу и выполнить команду соединения с базой (см. CREATE DATABASE... и CONNECT USER PASSWORD ).

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

Создание таблицы, точнее, ее описания и "пустографки" осуществляется командой CREATE TABLE.

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

Создание таблиц. Команда CREATE TABLE Команда CREATE TABLE имеет следующий синтаксис:

CREATE TABLE table /'EXTERNAL fFILEJ " "] (LIST_ [, LIST_J);

table - имя создаваемой таблицы. Имя таблицы внутри базы должно быть уникальным, EXTERNAL [FILE] "" задает таблицу, данные которой размещаются во внешней (не InterBase) таблице или файле, - описание поля (атрибута в терминах отношений) таблицы, - описание ограничений логической целостности для таблицы в целом.

Описание полей таблицы Для описания полей используется следующий синтаксис:

main} [DEFAULT {literal \ HULL \ USER}] [COLLATE collation] Первая строка относится к обязательным атрибутам описания столбца (поля):

col - имя столбца; должно быть уникальным в пределах таблицы;

/ datatype \ COMPUTED [BY] (< expr>) \ domain) задают тип данных в столбце, где datatype - любой допустимый в InterBase тип данных, а именно:

{SMALLINT y_dim>] I {DECIMAL | NUMERIC] [(precision [, scale])] [ ] I {DATE | TIME | TIMESTAMP} []

I {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}

[( int)] [] [CHARACTER SET charname]

I {NCHAR | NATIONAL CHARACTER \ NATIONAL CHAR]

[VARYING] | BLOB [SUB_TYPE {int | subtype_name)] [SEGMENT SIZE int] [CHARACTER SET charname] | BLOB [( seglen [, subtype])] ::= [hIST_] ТИПЫ данных TIME и TIMESTAMP допустимы в версиях, начиная с 6.0.

domain — имя определенного ранее домена (описание домена, в свою очередь, содержит описание типа данных);

COMPUTED [BY] (< expr>) - выражение для вычисляемого столбца. Значения вычисляемых столбцов рассчитываются в соответствии с задаваемым выражением всякий раз при обращении к ним. Выражение может быть любым допустимым в InterBase выражением, возвращающим единственное значение простого типа (не массив). Например, допустимы выражения с конструкцией SELECT. Используя нашу базу, можно добавить в таблицу TBOOK_AUTHOR два поля В1 и В2, которые будут использоваться для выбора автора и названия книги. Для добавления полей используется команда ALTER TABLE, синтаксис которой будет рассмотрен ниже.

Пример 4. a l t e r TABLE TBOOK_AUTHOR add Bl varchar(60) COMPUTED BY ((select a.auname from tauthor a where a.author=tbook_author.author)j;

alter TABLE TBOOK_AUTHOR add B2 COMPUTED BY ((select a.booknm from tbook a where a.unikey=tbook_author.bookkey));

Тогда результат работы select * from tbook_author;

Можно представить в виде таблицы.

Таблица 4.1. Перечень книг с указанием авторов и названий (вычисляемые Unikey Author Vnikey Вторая строка в синтаксисе описания полей (DEFAULT {literal | NULL | USER}) задает значение поля при создании новой строки таблицы.

При добавлении в таблицу новых строк не требуется, вообще говоря, задание значений всех ее столбцов. В то же время каждое поле в таблице должно иметь хоть какое-то допустимое значение. Для того чтобы задать значение столбца в этом случае, и служит конструкция DEFAULT. Если она отсутствует, то это эквивалентно конструкции DEFAULT NULL.

Если тип поля задан с помощью Домена, в котором присутствует конструкции DEFAULT, а в описании поля таблицы DEFAULT отсутствует, то в качестве значения по умолчанию принимается значение, заданное в описании домена. Если конструкция DEFAULT в описании поля таблицы указана явно, то значение по умолчанию будет взято из нее вне зависимости от указанного в описании домена (значение переопределяет заданное в домене).

literal задает значение поля явно и по типу должно соответствовать объявленному полю. В качестве допустимого значения для полей типа DATE может быть сегодняшняя дата, задаваемая конструкцией NOW.

NULL задает значение NULL Поскольку оно предполагается изначально, то задавать его стоит только при необходимости переопределить значение, указанное в домене. Последнее возможно, если в домене не указана USER указывает, что в поле будет занесено символьное имя USER'a, создавшего запись. Длина этого поля должна соответствовать требованиям к длине для имен пользователей (обычно от 8 до 16 символов) и использовать ту же или совместимую кодовую таблицу (character set).

Пример 4.

CREATE DOMAIN USERNAME AS VARCHAR(20)

CREATE TABLE ABC (

ABC_DATE DATE DEFAULT "NOW",

ABC_USER USERNAME DEFAULT USER,

ABC_COUNT DOUBLE PRECISSION DEFAULT

NOT NULL из третьей строки указывает, что поле не может содержать значение NULL (ни при создании, ни при обновлении данных). Отметим, что явно задаваемое значение NULL не должно конфликтовать с конструкцией NOT NULL, как в COUNT INTEGER DEFAULT NULL NOT NULL.