Определение процедуры
Процедуры в модуле SQL определяются в следующем синтаксисе:
<procedure>::=
PROCEDURE <procedure name>
<parameter declaration>...;
<SQL statement>;
<parameter declaration>::=
<parameter name> <data type>
|<SQLCODE parameter>
<SQLCODE parameter>::= SQLCODE
<SQL statement>::=
<close statement>
|<commit statement>
|<delete statement positioned>
|<delete statement searched>
|<fetch statement>
|<insert statement>
|<open statement>
|<rollback statement>
|<select statement>
|<update statement positioned>
|<update statement searched>
Имена всех процедур в одном модуле должны быть различны. Любое имя параметра, содержащегося в операторе SQL процедуры, должно быть специфицировано в разделе объявления параметров. Число фактических параметров при вызове процедуры должно совпадать с числом формальных параметров, указанных при ее объявлении. Список формальных параметров каждой процедуры должен содержать ровно один параметр SQLCODE (код ответа процедуры; возможные значения кодов ответа стандартизованы, но некоторые из них определяются в реализации).
Процедура проектирования
Процесс проектирования информационных систем является достаточно сложной задачей. Он начинается с построения инфологической модели данных (п. 2), т.е. идентификации сущностей. Затем необходимо выполнить следующие шаги процедуры проектирования даталогической модели:
· Представить каждый стержень (независимую сущность) таблицей базы данных (базовой таблицей) и специфицировать первичный ключ этой базовой таблицы.
· Представить каждую ассоциацию (связь вида "многие-ко-многим" или "многие-ко-многим-ко-многим" и т.д. между сущностями) как базовую таблицу. Использовать в этой таблице внешние ключи для идентификации участников ассоциации и специфицировать ограничения, связанные с каждым из этих внешних ключей.
· Представить каждую характеристику как базовую таблицу с внешним ключом, идентифицирующим сущность, описываемую этой характеристикой. Специфицировать ограничения на внешний ключ этой таблицы и ее первичный ключ – по всей вероятности, комбинации этого внешнего ключа и свойства, которое гарантирует "уникальность в рамках описываемой сущности".
· Представить каждое обозначение, которое не рассматривалось в предыдущем пункте, как базовую таблицу с внешним ключом, идентифицирующим обозначаемую сущность. Специфицировать связанные с каждым таким внешним ключом ограничения.
· Представить каждое свойство как поле в базовой таблице, представляющей сущность, которая непосредственно описывается этим свойством.
· Для того чтобы исключить в проекте непреднамеренные нарушения каких-либо принципов нормализации, выполнить описанную в п. 4.6 процедуру нормализации.
· Если в процессе нормализации было произведено разделение каких-либо таблиц, то следует модифицировать инфологическую модель базы данных и повторить перечисленные шаги.
· Указать ограничения целостности проектируемой базы данных и дать (если это необходимо) краткое описание полученных таблиц и их полей.
На рис. 4.6 показан синтаксис предложения, предлагаемого для регистрации принимаемых проектных решений.
Рис. 4.6. Синтаксис описания проектных решений
Для примера приведем описания таблиц "Блюда" и "Состав":
СОЗДАТЬ ТАБЛИЦУ Блюда *(Стержневая сущность) ПЕРВИЧНЫЙ КЛЮЧ (БЛ) ПОЛЯ (БЛ Целое, Блюдо Текст 60, Вид Текст 7) ОГРАНИЧЕНИЯ (1. Значения поля Блюдо должны быть уникальными; при нарушении вывод сообщения "Такое блюдо уже есть". 2. Значения поля Вид должны принадлежать набору: Закуска, Суп, Горячее, Десерт, Напиток; при нарушении вывод сообщения "Можно лишь Закуска, Суп, Горячее, Десерт, Напиток"); СОЗДАТЬ ТАБЛИЦУ Состав *(Связывает Блюда и Продукты) ПЕРВИЧНЫЙ КЛЮЧ (БЛ, ПР) ВНЕШНИЙ КЛЮЧ (БЛ ИЗ Блюда NULL-значения НЕ ДОПУСТИМЫУДАЛЕНИЕ ИЗ Блюда КАСКАДИРУЕТСЯ ОБНОВЛЕНИЕ Блюда.БЛ КАСКАДИРУЕТСЯ) ВНЕШНИЙ КЛЮЧ (ПР ИЗ Продукты NULL-значения НЕ ДОПУСТИМЫУДАЛЕНИЕ ИЗ Продукты ОГРАНИЧИВАЕТСЯ ОБНОВЛЕНИЕ Продукты.ПР КАСКАДИРУЕТСЯ) ПОЛЯ (БЛ Целое, ПР Целое, Вес Целое) ОГРАНИЧЕНИЯ (1. Значения полей БЛ и ПР должны принадлежать набору значений из соответствующих полей таблиц Блюда и Продукты; при нарушении вывод сообщения "Такого блюда нет" или "Такого продукта нет". 2. Значение поля Вес должно лежать в пределах от 0.1 до 500 г.);
Рассмотренный язык описания данных, основанный на языке SQL, позволяет дать удобное и полное описание любой сущности и, следовательно, всей базы данных. Однако такое описание, как и любое подробное описание, не отличается наглядностью.
Для наиболее распространенных реляционных баз данных можно предложить язык инфологического моделирования "Таблица-связь", пример использования которого приведен на рис. 4.7. В нем все сущности изображаются одностолбцовыми таблицами с заголовками, состоящими из имени и типа сущности. Строки таблицы – это перечень атрибутов сущности, а те из них, которые составляют первичный ключ, распологаются рядом и обводятся рамкой. Связи между сущностями указываются стрелками, направленными от первичных ключей или их составляющих.
Рис. 4.7. Инфологическая модель базы данных "Питание", построенная с помощью языка "Таблицы-связи"
Процедура нормализации
Как уже говорилось, нормализация – это разбиение таблицы на несколько, обладающих лучшими свойствами при обновлении, включении и удалении данных. Теперь можно дать и другое определение: нормализация – это процесс последовательной замены таблицы ее полными декомпозициями до тех пор, пока все они не будут находиться в 5НФ. На практике же достаточно привести таблицы к НФБК и с большой гарантией считать, что они находятся в 5НФ. Разумеется, этот факт нуждается в проверке, однако пока не существует эффективного алгоритма такой проверки. Поэтому остановимся лишь на процедуре приведения таблиц к НФБК.
Эта процедура основывается на том, что единственными функциональными зависимостями в любой таблице должны быть зависимости вида K->F, где K – первичный ключ, а F – некоторое другое поле. Заметим, что это следует из определения первичного ключа таблицы, в соответствии с которым K->F всегда имеет место для всех полей данной таблицы. "Один факт в одном месте" говорит о том, что не имеют силы никакие другие функциональные зависимости. Цель нормализации состоит именно в том, чтобы избавиться от всех этих "других" функциональных зависимостей, т.е. таких, которые имеют иной вид, чем K->F.
1. Таблица имеет составной первичный ключ вида, скажем, (К1,К2), и включает также поле F, которое функционально зависит от части этого ключа, например, от К2, но не от полного ключа. В этом случае рекомендуется сформировать другую таблицу, содержащую К2 и F (первичный ключ – К2), и удалить F из первоначальной таблицы:
Заменить T(K1,K2,F), первичный ключ (К1,К2), ФЗ К2->F на T1(K1,K2), первичный ключ (К1,К2), и T2(K2,F), первичный ключ К2.
2. Таблица имеет первичный (возможный) ключ К, не являющееся возможным ключом поле F1, которое, конечно, функционально зависит от К, и другое неключевое поле F2, которое функционально зависит от F1. Решение здесь, по существу, то же самое, что и прежде – формируется другая таблица, содержащая F1 и F2, с первичным ключом F1, и F2 удаляется из первоначальной таблицы:
Заменить T(K,F1,F2), первичный ключ К, ФЗ F1->F2 на T1(K,F1), первичный ключ К, и T2(F1,F2), первичный ключ F1.
Для любой заданной таблицы, повторяя применение двух рассмотренных правил, почти во всех практических ситуациях можно получить в конечном счете множество таблиц, которые находятся в "окончательной" нормальной форме и, таким образом, не содержат каких-либо функциональных зависимостей вида, отличного от K->F.
Для выполнения этих операций необходимо первоначально иметь в качестве входных данных какие-либо "большие" таблицы (например, универсальные отношения). Но нормализация ничего не говорит о том, как получить эти большие таблицы. В следующей главе будет рассмотрена процедура получения таких исходных таблиц, а здесь приведем примеры нормализации.
Пример 4.1. Применим рассмотренные правила для полной нормализации универсального отношения "Питание".
Шаг 1. Определение первичного ключа таблицы.
Предположим, что каждое блюдо имеет уникальное название, относится к единственному виду и приготавливается по единственному рецепту, т.е. название блюда однозначно определяет его вид и рецепт. Предположим также, что название организации поставщика уникально для того города, в котором он расположен, и названия городов уникальны для каждой из стран, т.е. название поставщика и город однозначно определяют этого поставщика, а город – страну его нахождения. Наконец, предположим, что поставщик может осуществлять в один и тот же день только одну поставку каждого продукта, т.е. название продукта, название организации поставщика, город и дата поставки однозначно определяют вес и цену поставленного продукта. Тогда в качестве первичного ключа отношения "Питание" можно использовать следующий набор атрибутов:
Блюдо, Дата_Р, Продукт, Поставщик, Город, Дата_П.
Шаг 2. Выявление полей, функционально зависящих от части состваного ключа.
Поле Вид функционально зависит только от поля Блюдо, т.е.
Блюдо->Вид.
Аналогичным образом можно получить зависимости:
Блюдо->Рецепт(Блюдо, Дата_Р)->ПорцийПродукт->Калорийность(Блюдо, Продукт)->ВесГород->Страна(Поставщик, Город, Дата_П)->Цена
Шаг 3. Формирование новых таблиц.
Полученные функциональные зависимости опредляют состав таблиц, которые можно сформировать из данных универсального отношения:
Блюда (Блюдо, Вид)Рецепты (Блюдо, Рецепт)Расход (Блюдо, Дата_Р, Порций)Продукты (Продукт, Калорийность)Состав (Блюдо, Продукт, Вес (г))Города (Город, Страна)Поставки (Поставщик, Город, Дата_П, Вес (кг), Цена).
Шаг 4. Корректировка исходной таблицы.
После выделения из состава универсального отношения указанных выше таблиц, там остались лишь сведения о поставщиках, для хранения которых целесообразно создать таблицу
Поставщики (Поставщик, Город),
т.е. использовать часть исходного первичного ключа, так как остальные его части уже ничего не определяют.
Пример процедуры создания базы:
CREATE TABLE [dbo].[Fleets](
[Fleet] [nchar](50) NOT NULL,
[Ships_number] [int] NOT NULL,
[Current_planet] [nchar](50) NOT NULL,
[Current_star] [nchar](50) NOT NULL,
[Fleet_owner] [nchar](50) NOT NULL,
CONSTRAINT [PK_Fleets] PRIMARY KEY CLUSTERED
(
[Fleet] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Пример процедуры (Транзакции) заполнения базы:
INSERT INTO
`Stars` (`Galaxy`, `Star`, `Star_class`, `Star_planets`,`Coor_x`, `Coor_y`, `Coor_z`,
`Total_resources`, `Star_owner`,`Textures`, `Description`)
Values
('Agapia','Agata','M',3,20,20,10,'Средние','','res/textures/sun/M.png',''),
('Agapia','Afina','M',2,20,40,5,'Средние','','res/textures/sun/M.png',''),
('Agapia','Afobi','G',5,60,10,10,'Большие','','res/textures/sun/G.png',''),
('Agapia','Vissarion','M',1,50,30,-10,'Малые','','res/textures/sun/M.png',''),
('Agapia','Galaction','K',3,40,40,-30,'Средние','','res/textures/sun/K.png',''),
('Agapia','Glafira','G',4,40,60,40,'Средние','','res/textures/sun/G.png',''),
('Agapia','Glikeria','A',0,20,70,-15,'Нет','','res/textures/sun/A.png',''),
('Agapia','Dorofea','B',0,60,70,-20,'Нет','','res/textures/sun/B.png',''),
('Agapia','Elpida','M',6,80,80,15,'Огромные','','res/textures/sun/M.png',''),
('Agapia','Efimia','A',0,60,50,20,'Нет','','res/textures/sun/A.png',''),
('Agapia','Fedra','F',2,80,30,-20,'Средние','','res/textures/sun/F.png',''),
('Agapia','Fotida','G',7,110,50,-45,'Огромные','','res/textures/sun/G.png',''),
('Agapia','Ieronim','O',0,110,10,50,'Нет','','res/textures/sun/O.png',''),
('Agapia','Iliodor','K',4,130,60,5,'Средние','','res/textures/sun/K.png',''),
('Agapia','Ifigenia','M',4,160,30,15,'Большие','','res/textures/sun/M.png',''),
('Agapia','Kallioppa','O',0,160,80,45,'Нет','','res/textures/sun/O.png',''),
('Agapia','Kalliro','B',0,110,80,-45,'Нет','','res/textures/sun/B.png',''),
('Agapia','Kalomira','A',0,80,90,-25,'Нет','','res/textures/sun/A.png',''),
('Agapia','Kibela','M',3,130,80,-20,'Средние','','res/textures/sun/M.png',''),
('Agapia','Kristallia','G',6,130,35,10,'Огромные','','res/textures/sun/G.png',''),
('Agapia','Ksantippa','A',0,175,50,20,'Нет','','res/textures/sun/A.png',''),
('Agapia','Melania','F',2,30,90,-20,'Средние','','res/textures/sun/F.png',''),
('Agapia','Melpomena','M',2,90,10,-10,'Малые','','res/textures/sun/M.png',''),
('Agapia','Niobea','G',3,90,50,15,'Средние','','res/textures/sun/G.png',''),
('Agapia','Olimpia','M',1,90,80,40,'Малые','','res/textures/sun/M.png','')