ГЛАВА 3

Проектирование Интернет-ориентированных баз данных

• Зачем нужны базы данных?
• Основные понятия
• Язык SQL
• Создание новых баз данных и таблиц
• Добавление строк
• Обновление строк
• Удаление строк
• Построение запросов
• Проектирование базы данных
• Определение требований к проектируемой системе
• Анализ предметной области и выявление объектов, представляющих систему
• Описание взаимосвязей между сущностями. Проектирование логической структуры БД
• Проектирование физической структуры БД. Нормализация
• Создание клиентской части СУБД на основе Web-технологий
• Роль скриптов в Web-интерфейсах к СУБД
• Роль технологии ActiveX в Web-интерфейсах к СУБД

Зачем нужны базы данных?

Основное назначение баз данных (БД) — хранение информации, это ясно даже неискушенному в информационных технологиях человеку. Тем не менее, БД — это нечто большее, чем просто электронное хранилище информации. Многим приложениям необходимо сохранять некоторую информацию между сеансами работы. Однако если вы проверите приложения, установленные на вашем компьютере, то наверняка придете к выводу, что большинство из них не использует в этих целях БД. Для хранения данных многие приложения применяют обычный плоский файл. Данные в плоском файле записываются и читаются последовательно, потоком. Поэтому операции для работы с такими файлами программисты часто называют потоковыми операциями. Главное преимущество использования плоских файлов — это простота потоковых операций. Однако при большом объеме хранимой информации такой подход оказывается неэффективным, поскольку, для того чтобы получить доступ к необходимому фрагменту сохраненной информации, нужно считать весь файл данных. Для повышения эффективности можно использовать файлы произвольного доступа, но при этом на плечи программиста ложится забота о физической структуре файла данных. Чем сложнее структура хранимой информации, тем труднее обеспечить эффективность работы.

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

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

Основные понятия

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

В реляционной модели данные хранятся в двумерных таблицах называемых отношениями (relation). Каждая таблица представляет некоторую сущность (entity). На рис. 3.1 приведена таблица supplier, представляющая сущность "Поставщик". Каждая строка такой таблицы содержит информацию об одном экземпляре сущности. В нашем примере это информация о конкретном поставщике. Совокупность значений полей одной строки таблицы называют кортежем. Столбцы таблицы представляют различные характеристики сущности. Такими характеристиками являются название организации поставщика (поле NAME), контактное лицо (CONTRACTOR), номер факса (FAX) и адрес электронной почты (EMAIL). Совокупность значений полей одного столбца таблицы называют доменом.

Рис.3.1.Таблица Supplier, представляющая сущность "Поставщик"

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

В примере, приведенном на рис. 3.2, первичным ключом таблицы STUFFJTYPE является поле ID. Таблица ESSENTIAL_STUFF имеет внешний ключ STUFF_ID. Связь, устанавливаемая этими ключами, позволяет определять, какой материал и в каком количестве необходим к конкретной дате.

Рис. 3.2. Связь таблиц ESSENTIAL_STUFF (необходимые материалы) и STUFFJTYPE (типы материалов)

Ключ ID таблицы STUFF_TYPE называется суррогатным, т. к. он не несет на себе никакой другой смысловой нагрузки, кроме связывания таблиц, и может искусственно генерироваться СУБД. Первичным ключом таблицы, приведенной на рис. 3.1, может быть столбец NAME. Такой ключ называют естественным, поскольку он представлен столбцом, описывающим сущность. Использование естественных первичных ключей позволяет избежать избыточности хранимой информации. Однако естественные ключи могут стать причиной других проблем. Так, в нашем примере при изменении названия некоторой фирмы придется проверить все поля связанных таблиц, в которых название фирмы является внешним ключом, и изменить название фирмы и там. Кроме того, редко удается найти естественный ключ, значения которого гарантированно уникальны. Встречаются разные фирмы с одинаковым названием, разные люди с одинаковыми фамилиями, разные города с одним именем и т. д. А главное требование для первичного ключа — уникальность идентификации экземпляра сущности. Поэтому в большинстве случаев использование суррогатных ключей предпочтительнее, чем естественных.

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

Язык SQL

В современных реляционных СУБД для описания и манипулирования данными используются диалекты языка SQL. Математической основой языка SQL является реляционная алгебра и реляционное исчисление. Стандарт языка SQL регламентируется Американским институтом стандартов (American National Standard's Institute — ANSI) и международной организацией стандартизации (International Organization for Standardization — ISO). Этот стандарт называется ISO-ANSI SQL. Последнюю версию стандарта в литературе именуют SQL/92. К сожалению, ни один из коммерческих продуктов не поддерживает в полной мере этого стандарта. Дж. Дейт в книге ['] называет языки существующих реляционных СУБД "надмножеством подмножества" SQL/92. Поскольку, не поддерживая некоторых аспектов стандарта, в других отношениях они превосходят его.

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

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

Создание новых баз данных и таблиц

Некоторые СУБД имеют визуальные средства для создания БД, описания таблиц и взаимосвязей между ними. Другие же требуют выполнить для этого специальные запросы SQL. Подмножество языка SQL, позволяющее создавать и описывать БД, называется DDL (Data Definition Language). СУБД MS SQL Server 7.0 имеет и визуальные средства описания БД и возможность сделать это посредством команд SQL.

Создать новую базу данных можно с помощью команды

CREATE DATABASE <name>,

где <name> — имя создаваемой БД.

Далее к созданной БД необходимо добавить таблицы. Это можно сделать с помощью команды CREATE TABLE. Команда имеет следующий формат:

CREATE TABLE <table name>

( { <column name> <data type> [<size>] [<colconstrnt> ...]},...); [<tabconstrnt>] .,..);

где:

<tabie name> — имя создаваемой таблицы;

<coiumn name> — имя столбца таблицы;

<data type> — тип значений столбца таблицы;

<size> — размер (актуален для некоторых типов данных, например для строк);

<coiconstrnt> — ограничения, накладываемые на значения столбца;

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

В качестве значений параметра <data type> могут быть использованы следующие типы:

INTEGER (ЦЕЛОЕ ЧИСЛО),

CHAR (СИМВОЛЬНОЕ ЗНАЧЕНИЕ)

VARCHAR (СИМВОЛЬНОЕ ЗНАЧЕНИЕ, СОХРАНЯЮТСЯ ТОЛЬКО НЕ ПУСТЫЕ СИМВОЛЫ)

DECIMAL (ДЕСЯТИЧНОЕ ЧИСЛО),

FLOAT (ЧИСЛО С ПЛАВАЩЕЙ ТОЧКОЙ) ,

DOUBLE PRECISION (УДВОЕННАЯ ТОЧНОСТЬ С ПЛАВАИЦЕЙ ТОЧКОЙ),

DATETIME (ДАТА И ВРЕМЯ),

BOOL (ВУЛЕВОЕ ЗНАЧЕНИЕ)

 Замечание 

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

В качестве значений параметра <coiconstmt> могут быть использованы следующие:

NOT NULL (HE НУЛЕВОЙ),

UNIQUE (УНИКАЛЬНЫЙ),

PRIMARY KEY (ПЕРВИЧНЫЙ КЛЮЧ),

CHECK(<predicate>) (ПРОВЕРКА предиката),

DEFAULT = (ПО УМОЛЧАНИЮ = <value expression> значимому выражению)

REFERENCES <table name> (ССЫЛКА НА имя таблицы [(<column name> .,..)] [ ( имя столбца)])

Параметр <tabconstrnt> может принимать одно из следующих значений:

UNIQUE (УНИКАЛЬНЫЙ),

PRIMARY KEY (ПЕРВИЧНЫЙ КЛЮЧ),

CHECK (<predicate>)(ПРОВЕРКА предиката)

FOREIGN KEY<<column name>) (ВНЕШНИЙ КЛЮЧ)

REFERENCES <table name> (ССЫЛКА НА имя таблицы [(<column name> .,.. )]

[( имя столбца)].

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

Customer — информация о заказчиках, содержит поля:

id — первичный ключ;

Name — название организации заказчика;

Fax — номер факса для отправки счет фактуры;

Town — место расположения.

 Orders — информация о ходе выполнения заказа, содержит поля:

id — первичный ключ;

Customer_id — внешний ключ для ссылки на организацию заказчика;

Date — дата поступления заказа;

Paid — был ли оплачен заказ;

Executed — был ли выполнен заказ. 

OrderItem — информация об отдельных пунктах заказа, содержит поля:

id — первичный ключ;

Product_id — внешний ключ для ссылки на продукт;

Quantity -- количество заказанных единиц;

Order_id — внешний ключ, ссылающийся на заказ, к которому относится строка.

Product — информация о продуктах на складе, содержит поля:

id — первичный ключ;

Name — название товара;

Unit — единица измерения;

Price — стоимость единицы;

stock — количество на складе;

Suppiier_id — внешний ключ для ссылки на организацию поставщика.

 Supplier — информация о поставщиках, содержит поля:

id — первичный ключ;

Name — название организации поставщика;

Fax — номер факса;

Town — место расположения.

Для создания этих таблиц необходимо выполнить SQL-запросы, приведенные в листинге 3.1.

Листинг 3.1. Создание таблиц

/* Создание таблицы Customer */

 CREATE TABLE Customer 

(Id INTEGER NOT NULL,

Name VARCHAR(5O),

Fax CHAR(15),

Town CHAR(40)

PRIMARY KEY (Id)); 

/* Создание таблицы Orders */

 CREATE TABLE Orders

(Id INTEGER NOT NULL,

Customer_Id INTEGER,

Date DATETIME,

Paid BOOL,

Executed BOOL,

PRIMARY KEY (Id) ,

FOREIGN KEY(Customer_Id) REFERENCES Customer (Id)); 

/* Создание таблицы Orderltem */ 

CREATE TABLE Orderltem (Id INTEGER NOT NULL,

Product_Id INTEGER,

Quantity INTEGER,

PRIMARY KEY (Id),

FOREIGN KEY(Product_Id) REFERENCES Product (Id));

/* Создание таблицы Product */ 

CREATE TABLE Product

(Id INTEGER NOT NULL,

Name VARCHAR(5O),

Unit VARCHAR(20),

Price MONEY,

Stock INTEGER,

Supplier_Id INTEGER,

PRIMARY KEY (Id),

FOREIGN KEY(Supplier_Id) REFERENCES Supplier (Id)); 

/* Создание таблицы Supplier */ 

CREATE TABLE Supplier

(Id INTEGER NOT NULL,

Name VARCHAR(50),

Fax CHAR(15),

Town CHAR(40)

PRIMARY KEY (Id));

Добавление строк

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

INSERT INTO < table name> [(<column name> ., . ] 

{ VALUES ( <value expression> ., . .) } 

|<query>;

Вот несколько примеров использования этой команды:

/* Вставка в таблицу всех полей */

INSERT INTO Supplier

VALUES (1, 'Московский завод панельных конструкций', '234-34-53');

/* Вставка выбранных полей */

INSERT INTO Supplier(Id, Name) VALUES) 2, 'Фирма "Стройдом"');

/* Вставка многих строк из другой таблицы */

INSERT INTO Supplier_Backup(Name, Fax, Town)

VALUES (SELECT Name, Fax, Town FROM Supplier);

Обновление строк

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

UPDATE <tablename>

SET { | }.,. .< column name> = <value expression>

[ WHERE <predicate> ] ;

Примеры использования:

/* Обновление конкретной записи */

UPDATE Supplier

SET Fax = '124-45-11'

WHERE Name = 'Фирма "Стройдом"';

/* Обновление всех записей в таблице */

UPDATE Product

SET Stock = 0;

/* Увеличим стоимость всех товаров на 10% */

UPDATE Product

SET Price = Price + Price*10/100;

 

Удаление строк

Для удаления информации из таблицы используют команду DELETE:

DELETE FROM <table name> 

[ WHERE <predicate> ];

Пример:

/* Удаление товаров, которых нет в наличии */ 

DELETE FROM Product WHERE Stock = 0;

 

Построение запросов

Для выборки необходимой информации из БД используется команда SELECT, имеющая следующий формат:

SELECT * | ( [ DISTINCT | ALL] < value expression >.,..}

FROM'{ < table name > [ < alias > ] }.,..

[WHERE <predicate>]

[GROUP BY { <column name> ] <integer> }.,..]

[HAVING <predicate>]

[ORDER BY { <column name> I <integer> }.,..]

[{ UNION [ALL]

Рассмотрим элементы, используемые в команде SELECT:

•  <vaiue expression> — выражение, которое производит значение. Оно может включать в себя или содержать <coiumn namex В простом случае это название столбцов исходной таблицы, которые мы хотим видеть в результирующем множестве;
•  <table name> — имя или синоним, таблицы или представления;
•  <alias> — временный синоним для <tabie name>, определенный в этой таблице и используемый только в этой команде;
•  <predicate> — условие, которое может быть верным или неверным для каждой строки или комбинации строк таблицы в предложении FROM;
•  <column name> — имя столбца в таблице;
•  <integer> — число с десятичной точкой. В данном случае, оно показывает <vaiue expression в предложении SELECT с помощью идентификации его местоположения в этом предложении.

В команде SELECT могут также присутствовать ключевые слова:

•  DISTINCT — указывает,. что в результирующем множестве каждая строка должна быть уникальна;
•  FROM — указывает, что далее следуют имена исходных таблиц и (или) представлений;
•  WHERE — позволяет задать условия для выбора записей;
•  GROUP BY — позволяет задать условия группировки записей;
•  HAVING — позволяет задать условия выбора для группы;
•  ORDER BY — определяет порядок записей в результирующем множестве;
•  UNION — позволяет указать таблицу, для объединения.

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

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

/* Получить проекцию всех столбцов таблицы Customer */ 

SELECT * FROM Customer;

Рис. 3.3. Проекция всех столбцов таблицы Customer

/* Получить проекцию всех столбцов таблицы Orders */

 SELECT * FROM Orders;

Рис. 3.4. Проекция всех столбцов таблицы Orders

/* Получить проекцию всех столбцов таблицы OrderItem */

 SELECT * FROM Orderltem;

Рис. 3.5. Проекция всех столбцов таблицы Orderltem

/* Получить проекцию всех столбцов таблицы Product */ 

SELECT * FROM Product;

Рис. 3.6. Проекция всех столбцов таблицы Product

/* Получить проекцию всех столбцов таблицы Supplier */ 

SELECT * FROM Supplier;

Рис. 3.7. Проекция всех столбцов таблицы Supplier

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

/* Получить проекцию выбранных столбцов таблицы */ 

SELECT Name, Price, Stock FROM Product;

После выполнения такой команды мы получим результирующее множество (рис. 3.8).

Рис. 3.8. Проекция столбцов Name, Price, Stock таблицы Product

Допустим, что нас интересует, на какую сумму хранится каждого товара на складе. Для этого, в команде SELECT мы можем использовать арифметические действия. Результат выполнения команды приведен на рис. 3.9.

/* Подсчитать общую стоимость каждого товара на складе */ 

SELECT Name, Price*Stock AS All_Price FROM Product;

Рис. З.9. Стоимость продукции, хранящейся на складе

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

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

/* Выбрать товары, цена которых больше либо равна 1000

или меньше либо равна 100 */ 

SELECT Name, Price FROM Product

WHERE Price >= 1000 OR Price <= 100;

Рис. 3.10. Товары, цены на которые не попадают в заданный диапазон

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

/* Выбрать товары, цена которых находится в интервале от 100 до 1000 */

 SELECT Name, Price FROM Product

WHERE Price BETWEEN 100 AND 1000;

Рис. 3.11. Товары, цены на которые попадают в заданный диапазон

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

/* Вывести список товаров, упорядоченных по имени в алфавитном порядке */

 SELECT Name, Price FROM Product ORDER BY Name;

Рис. 3.12. Товары и цены, упорядоченные по имени товара в алфавитном порядке

Заметьте, что если после этого выполнить команду:

SELECT * FROM Product;

то получим результат, как на рис. 3.13.

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

Рис. 3.14.Товары и цены, упорядоченные по цене в убывающем порядке

В приведенном ранее примере строки были упорядочены по возрастанию значения поля Name. Допустим теперь, что нам надо упорядочить строки таблицы по убыванию цены на товар. Тогда команда будет иметь вид:

/* Вывести список товаров, упорядоченных по цене в убывающем порядке*/

 SELECT Name, Price FROM Product 

ORDER BY Price DESC;

Результат выполнения этой команды приведен на рис. 3.14.

В приведенных выше примерах, после ключевого слова FROM, мы указывали только одну таблицу. В реальных запросах часто приходится обрабатывать данные из нескольких таблиц одновременно. Таблица Product с помощью внешнего ключа Suppiier_id связана с таблицей Supplier. Допустим, нам необходимо получить результирующую таблицу, в которой кроме названия, цены и количества товара на складе, будет указан и поставщик товара.

Для этого можно выполнить команду:

/* Сцепление полей из двух таблиц */

SELECT Product.Name, Price, Stock, Supplier.Name AS Supplier FROM Product, Supplier

WHERE Supplier.Id = Product.Supplier_Id;

Результат выполнения команды приведен на рис. 3.15.

Рис. 3.15. Результат сцепления полей из двух таблиц

В приведенном примере обе таблицы Product и Supplier имеют поле Name. В таких случаях, чтобы различать поля в разных таблицах, в SQL-запросе используют полное имя поля, включающее название таблицы или ее псевдоним. В данном случае использовались имена полей Product.Name и Supplier.Name.

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

/* Выбрать товары, поставщики которых находятся в Москве, и цены на них */

 SELECT Name, Price FROM Product

WHERE Supplier_Id IN ( SELECT Id FROM Supplier WHERE Town = 'Москва'};

Результат выполнения такой команды приведен на рис. 3.16.

Рис. 3.16. Товары, поставщики которых находятся в Москве, и цены на них

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

SELECT Id FROM Supplier WHERE Town = 'Москва');

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

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

/* Подсчитать общую стоимость всех товаров, поставленных отдельными поставщиками */

SELECT Supplier.Name, Supplier.Town, SUM(Product.Price) AS Total 

FROM Product, Supplier

WHERE Product.Supplier_Id = Supplier.Id

GROUP BY Supplier.Name, Supplier.Town;

Результат выполнения такой команды приведен на рис. 3.17.

Рис. 3.17. Общая стоимость всех товаров, поставленных отдельными поставщиками

Здесь, происходит сцепление таблиц Supplier и Product. Строки в результирующем множестве группируются по значению полей supplier.Name, Supplier.Town. Для каждой группы считается сумма цен на товары.

Заметим, что в результирующем множестве каждая группа представлена одной строкой. И если бы вместо SUM (Product. Price), в SQL-запросе мы написали бы просто Product. Price, то в результате выполнения произошла бы ошибка. Поскольку для группы, определяемой этими полями, значение Product.Price не является общим. Таким образом, если мы используем группировку записей, то после ключевого слова SELECT мы можем указывать лишь имена тех столбцов, по которым осуществляется группировка, либо агрегативные функции.

Иногда необходимо объединить наборы строк из двух таблиц. Например, если мы хотим получить список всех партнеров фирмы, то нам нужно объединить таблицы поставщиков и заказчиков. Для этого используется ключевое слово UNION:

/* Объединение двух таблиц*/

SELECT Name FROM Supplier

UNION

SELECT Name FROM Customer

Результат выполнения запроса приведен на рис. 3.18.

Рис. 3.18. Результат объединения записей из двух таблиц

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

/* Вывести список товаров без повторяющихся наименований */ 

SELECT DISTINCT Name FROM Product;

Результат выполнения команды приведен на рис. 3.19.

Рис. 3.19. Список товаров без повторений

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

Для этого можно использовать запрос:

/* Вывести товары, наименования которых встречаются более одного раза */ 

SELECT pl.Name, pi.Price, Supplier.Name AS Supplier

 FROM Product p1, Product p2, Supplier WHERE p1.Id <> p2.Id AND 

pl.Name = p2.Name AND pl.Supplier_Id = Supplier.Id ORDER BY pl.Name;

Результат выполнения команды приведен на рис. 3.20.

Рис. 3.20. Список товаров, встречающихся в таблице Product более одного раза

Для получения нужного результата нам пришлось использовать сцепление таблицы с самой собой. Для этого мы ввели для таблицы Product два псевдонима p1 и р2 и выбрали строки с одинаковыми именами, но с разными первичными ключами. Кроме того, чтобы узнать, кто из поставщиков предлагает товар по более низкой цене, мы делаем сцепление с таблицей supplier. Для удобства чтения, результат мы упорядочиваем по названию товара.

Мы можем также получить минимальную цену на товары, предлагаемые разными поставщиками. Для этого нам снова придется воспользоваться агрегативной функцией:

/* Вывести товары, наименования которых встречаются более одного раза, с минимальной ценой */

SELECT pl.Name, MIN(pi.Price) AS Min_Price 

FROM Product p1, Product p2, Supplier WHERE pi.Id 0 p2.Id AND pi.Name = p2.Name AND

 pi.Supplier_Id = Supplier.Id;

Результат выполнения команды приведен на рис. 3.21.

Рис. 3.21. Минимальная цена на товары, встречающиеся в таблице Product более одного раза

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

/* Вывести количество товаров по оплаченным, но еще не выполненным заказам */

SELECT Product.Name, Quantity, Customer.Name AS Customer

FROM Product, Orders, Orderltem, Customer

WHERE Product.Id = Orderltem.Product_Id AND Orderltem.Order_Id = Orders.ID AND Orders.Customer_Id = Customer.Id AND Orderltem.Order_Id IN (SELECT Id From Orders WHERE Paid = True AND Executed = False)

Результат выполнения команды приведен на рис. 3.22.

Рис. 3.22. Список товаров, входящих в оплаченные, но еще не выполненные заказы

Проектирование базы данных

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

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

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

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

Определение требований к проектируемой системе

От качества выполнения этого этапа во многом зависит успех проекта в целом. Проблемы здесь заключаются в том, что заказчику тяжело описать свои требования, на понятном для команды разработчиков языке. Кроме того, заказчик обычно не представляет, насколько сложна реализация той или иной функциональности. Вместе с тем, часто необходимо, чтобы стоимость и сроки разработки не выходили за заранее определенные рамки. Программист же, с другой стороны, обычно не знает всех тонкостей бизнес-процесса, которые ему придется отражать в проектируемой системе. Поэтому в процессе определения требований к разработке должны участвовать как представители заказчика (эксперты предметной области), так и представители команды разработчиков (программисты, аналитик). Минимально жизнеспособная команда — это один разработчик и один эксперт предметной области. Для анализа требований можно применить технику диаграмм вариантов использования (Use Case). Вариант использования имеет следующие свойства:

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

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

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

Рис. 3.23. Пример диаграммы вариантов использования

Анализ предметной области и выявление объектов, представляющих систему

Параллельно описанию вариантов использования анализируется предметная область, которую будет представлять проектируемая система. На основе требований, предъявляемых к системе, идентифицируют все сущности. Сущность определяется как некоторый объект, представляющий интерес. Этот объект должен иметь экземпляры, отличающиеся друг от друга и допускающие однозначную идентификацию. Для выявления сущностей применяется морфологический анализ требований. Потенциально сущностями являются все существительные, входящие в постановку задачи и относящиеся к предметной области. Так, при проектировании системы автоматизации работы отдела кадров сущностями являются: РАБОТНИК, ПРОФЕССИЯ, ОТДЕЛ и др.

Далее идентифицируются характеристики этих сущностей. Например, сущность РАБОТНИК может включать такие характеристики, как Фамилия, Имя, Отчество, Профессия, Зарплата. Идентифицируя характеристики, можно выявить дополнительные сущности.

Описание взаимосвязей между сущностями. Проектирование логической структуры БД

Выявляя сущности, значимые в рамках разрабатываемой системы, параллельно анализируются связи между ними. Помочь в выявлении связей может анализ глаголов, входящих в постановку задачи. Для описания сущностей и связей между ними обычно используют некоторую графическую нотацию. Одна из наиболее популярных нотаций для описания структуры БД — ER-диаграммы. ER — это сокращение от Entity-Relation (Сущность-Связь). С точки зрения проектирования структуры БД для нас важны такие характеристики связей, как степень связи и класс принадлежности связи. ER-диаграммы позволяют отразить эти особенности и полностью описать логическую структуру БД. Существуют различные варианты обозначений элементов ER-диаграмм. Поэтому используемые в примерах обозначения могут существенно отличаться от тех, которые используются, например, в CASE-средстве, которым вы пользуетесь.

Для объяснения терминов степень связи и класс принадлежности рассмотрим несколько примеров отношений между сущностями.

Сущность РАБОТНИК связана отношением ВОЗГЛАВЛЯЕТ с сущностью ОТДЕЛ. Будем считать, что работник может возглавлять только один отдел. С другой стороны, отдел может иметь только одного руководителя. Говорят, что такая связь имеет степень "один-к-одному" (рис. 3.24).

Сущность РАБОТНИК связана с сущностью ОТДЕЛ еще и отношением РАБОТАЕТ. В любом отделе может работать множество работников. Однако работник может работать только в одном конкретном отделе. Говорят, что такая связь имеет степень "один-ко-многим" (см. рис. 3.25).

Рис. 3.24. Обозначение отношения "один-к-одному"

Рис. 3.25. Обозначение отношения "один-ко-многим"

Сущность РАБОТНИК связана с сущностью ПРОФЕССИЯ отношением ИМЕЕТ. Любой работник может иметь несколько профессий. Разные работники могут иметь одну и ту же профессию. Отходя от норм русского языка, можно сказать, что "любая профессия может принадлежать разным работникам". Такое отношение называют "многие-ко-многим" (рис. 3.26).

Рис. 3.26. Обозначение отношения "многие-ко-многим"

Приведенные примеры объясняют значение характеристики "степень связи". Другой важной характеристикой связи является "класс принадлежности". Так, рассмотренному ранее отношению ВОЗГЛАВЛЯЕТ обязательно принадлежит сущность ОТДЕЛ, поскольку любой отдел должен иметь руководителя. Однако для сущности РАБОТНИК эта связь принадлежит необязательно. Так как не любой работник должен быть начальником отдела. С учетом этого уточнения графическое обозначение связи между ОТДЕЛОМ и РАБОТНИКОМ должно иметь вид (рис. 3.27):

Рис. 3.27. Обозначение класса принадлежности связи

Все вышеприведенные примеры представляют бинарные связи. Иногда при анализе выявляются и более сложные типы связей. Пусть, например, нам необходимо хранить в БД информацию о том, в исполнении каких проектов участвует работник. В выполнении любого проекта может участвовать множество работников. Поэтому мы должны хранить в БД информацию о том, каковы обязанности каждого работника при выполнении всех проектов, в которых он участвует. В этой связи участвуют три сущности: РАБОТНИК, ПРОЕКТ, ОБЯЗАННОСТЬ, и такая связь называется тернарной. Обычно подобные связи можно легко разложить на бинарные (рис. 3.28).

Рис. 3.28. а — тернарная связь, б — разложение на бинарные связи

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

Проектирование физической структуры БД. Нормализация

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

Первая нормальная форма (INF — First Normal Form). Основа реляционного представления данных. Говорят, что данные находятся в первой нормальной форме, если они представлены двумерной таблицей. При этом ячейки таблицы не должны содержать одновременно несколько значений.

Вторая нормальная форма (2NF — Second Normal Form). Таблица находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и данные во всех не ключевых столбцах полностью зависят от первичного ключа. Под полной зависимостью понимается, что значение в каждом не ключевом столбце однозначно определяется значением первичного ключа. Если в таблице существует хотя бы одно поле, независящее от первичного ключа, то для приведения этой таблицы ко второй нормальной форме это поле можно добавить к первичному ключу. Однако более предпочтительным обычно является разбиение таблицы на проекции.

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

Третья нормальная форма (3NF — Third Normal Form) требует, чтобы таблица находилась во второй нормальной форме, и все не ключевые столбцы были независимы друг от друга.

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

Существуют нормальные формы более высоких порядков (4NF и 5NF), однако на практике они применяются редко.

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

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

•  Если связь между сущностями имеет степень "один-к-одному", и класс принадлежности связи для обеих сущностей является обязательным, то такие сущности могут быть представлены с помощью одной таблицы.
•  Если связь между сущностями имеет степень "один-к-одному", и класс принадлежности связи для одной сущности является обязательным, а для другой нет, то необходимо использовать две таблицы. При этом столбец ссылок на первичный ключ таблицы, соответствующий сущности не обязательно принадлежащей связи, становится внешним ключом таблицы, соответствующей сущности, которая обязательно принадлежит связи.
•  Если связь между сущностями имеет степень "один-к-одному", и класс принадлежности связи для обеих сущностей не является обязательным, то можно, как и в предыдущем случае, использовать две таблицы. Выбор, в какую таблицу добавить внешний ключ, для ссылки на другую таблицу, в таком случае не имеет значения. Однако в поле внешнего ключа будут встречаться неопределенные значения в тех случаях, когда экземпляру сущности из этой таблицы нет соответствующего экземпляра из другой таблицы. Обычно значение внешнего ключа в таких случаях определяют как NULL (нулевое). Если такой подход не приемлем, то необходимо использовать три таблицы. По одной для каждой сущности. Третья таблица должна содержать два внешних ключа. Один из внешних ключей ссылается на экземпляры сущностей из первой таблицы, а другой — на экземпляры сущностей из второй таблицы. Таким образом, каждой паре экземпляров сущностей, между которыми существует связь, соответствует строка в третьей таблице.
•  Если связь между сущностями имеет степень "один-ко-многим", и класс принадлежности связи для обеих сущностей является обязательным, то необходимо использовать две таблицы. При этом столбец ссылок на первичный ключ таблицы, соответствующий односвязной сущности, становится внешним ключом таблицы соответствующей n-связанной сущности.
•  Если связь между сущностями имеет степень "один-ко-многим", и класс принадлежности связи для односвязной сущности является обязательным, а n-связанной сущности нет, то так же, как и в предыдущем случае, используют две таблицы.
•  Если связь между сущностями имеет степень "один-ко-многим", и класс принадлежности связи для n-связанной сущности является обязательным, а односвязной сущности нет, то можно использовать три таблицы. По одной для каждой сущности. Третья таблица должна содержать два внешних ключа. Строка в третьей таблице соответствует паре экземпляров сущностей, между которыми существует связь.
•  Если связь между сущностями имеет степень "один-ко-многим", и класс принадлежности связи для обеих сущностей является не обязательным, то так же используются три таблицы.
•  Если связь между сущностями имеет степень "многие-ко-многим", то независимо от степени принадлежности, необходимо использовать три таблицы. По одной для каждой сущности и третью таблицу с двумя внешними ключами.
  

Создание клиентской части СУБД на основе Web-технологий

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

Однако средой выполнения Web-страницы является браузер, а Win32 приложения — операционная система (хотя, следует отметить, что в последних версиях Windows браузер интегрирован в ОС). В общем случае, Web-страница имеет гетерогенную реализацию, выполняя лишь функции диалога, а настольное приложение имеет большие функциональные возможности, часто не обладая свойством переносимости.

Занимаясь проектированием Web-интерфейса, следует помнить, что все элементы, которые можно реализовать с помощью языка HTML, делятся на статические и элементы форм. Статические элементы отображают информацию, которая не может непосредственно изменяться пользователем. Элементы форм позволяют вводить некоторые данные (текстовые, булевые), которые затем отправляются серверному модулю для обработки.

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

В табл. 3.1 приведены элементы форм, которыми можно пользоваться при проектировании Web-интерфейса.

Таблица 3.1. Сравнительный анализ элементов языка HTML и Delphi

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

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

Роль скриптов в Web-интерфейсах к СУБД

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

Итак, что может делать скрипт, применительно к интерфейсу СУБД:

1. Вывод на экран пользователя сообщений, предупреждений и т. д., используя стандартные диалоговые средства Windows (рис. 3.29).

2. Проверка вводимых пользователем данных (рис. 3.30).

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

Рис. 3.29. Вывод предупреждения средствами JavaScript

Рис. 3.30. Вывод сообщения о некорректности введенных данных

3. Улучшение средств навигации по БД.

Используя меню, созданное на языке JavaScript, можно организовать на Web-странице удобные блоки вложенных ссылок (рис. 3.31).

Рис. 3.31. Использование JavaScript для навигации по базе данных

4. Выбор средств обработки данных, посылаемых пользователем.

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

Роль технологии ActiveX в Web-интерфейсах к СУБД

Использование компонентной модели построения приложений позволяет перейти к интегрированным решениям на базе Web-технологий. Технология ActiveX переносит многие преимущества клиентских программ в среду Интернет/интранет. Благодаря ее использованию, многие возможности настольных приложений становятся доступными для Web-интерфейсов. Все, что можно сделать обычными средствами Delphi, может быть в них использовано. Например, элемент VXChart, позволяющий строить диаграммы, посредством ActiveX, встроенный в Web-страницу, дает поистине фантастические возможности пользователю, чего нельзя добиться непосредственно на языке HTML.

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

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