Главная / Банки и базы данных / Проектирование реляционных баз данных

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

ЛАБОРАТОРНАЯ РАБОТА № 2

1. ЦЕЛЬ РАБОТЫ

1. Изучение основных понятий реляционных баз данных.

2. Изучение принципов создания и использования таблиц.

2. РЕЛЯЦИОННЫЕ БАЗЫ ДАННЫХ

Теория реляционной базы данных была разработана в начале 70-х годов Д. Коддом на основе математической теории отношений. В реляционной базе данных все данные хранятся в виде прямоугольных таблиц, при этом все операции над базой данных сводятся к манипуляциям с таблицами.

Таблица состоит из строк и столбцов и имеет уникальное имя в базе данных. База данных содержит множество таблиц, связь между которыми устанавливается с помощью совпадающих полей. В каждой из таблиц содержится информация о каких-либо объектах одного типа (группы). Отношение между объектами определяет отношение между таблицами. Visual FoxPro поддерживает четыре типа отношений между таблицами: Один-к-одному, один-ко-многим, много-к-одному, много-ко-многим.

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

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

Отношение Много-к-одному аналогично рассмотренному ранее типу Один-ко-многим. Тип отношения между объектами зависит от нашей точки зрения. Например, если рассматривать отношение между сотрудниками и предприятиями, то получим отношение Много-к-одному.

Отношение Много-ко-многим возникает между двумя таблицами в тех случаях, когда: 1) одна запись из первой таблицы может быть связана более чем с одной записью из второй таблицы; 2) одна запись из второй таблицы может быть связана более чем с одной записью из первой таблицы.

В качестве примера можно привести магазин оптовой торговли. Рассмотрим две группы объектов.

1. Список товаров, производимых предприятиями поставщиками (таблица Suppgood).

2. Список товаров, заказанных потребителями (таблица Ordsale).

Структура таблиц Suppgood и Ordsale приведена в табл. 2.1 и 2.2 соответственно.

Таблица 2.1.

Структура таблицы Suppgood

Наименование

Тип

Описание

1

Supplier

Integer

Код поставщика

2

Goods

Integer

Код товара

3

UnitPrice

Currency

Цена

4

MinQuant

Numeric(12, 2)

Минимальная партия

5

DelivPeriod

Numeric(12, 2)

Срок поставки

Таблица 2.2.

Структура таблицы Ordsale

Наименование

Тип

Описание

1

Customer

Integer

Код покупателя

2

Goods

Integer

Код товара

3

OrderDate

Date

Дата заказа

4

OrderQuant

Numeric(10)

Заказанное количество

5

UnitPrice

Currency

Цена

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

3. ПРОЕКТИРОВАНИЕ НОРМАЛИЗОВАННЫХ БАЗ ДАННЫХ

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

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

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

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

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

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

Структура таблицы Sales приведена в табл. 3.1.

Таблица 3.1.

Структура таблицы Sales

Наименование

Тип

Описание

1

Customer

Integer

Код покупателя

2

Company

Character(20)

Предприятие

3

LastName

Character(16)

Фамилия покупателя

4

FirstName

Character(16)

Имя покупателя

5

SecondName

Character(16)

Отчество покупателя

6

Phone

Character(10)

Телефон покупателя

7

Zip

Character(10)

Почтовый индекс

8

Country

Character(20)

Страна

9

Region

Character(20)

Область

10

City

Character(20)

Город

11

Address

Character(60)

Остальная часть адреса

12

CreditLimit

Currency

Кредит

13

CustNotes

Memo

Дополнительные сведения о покупателе

14

Order

Integer

Номер заказа

15

OrderDate

Date

Дата заказа

16

OrderQuant

Numeric(10)

Заказанное количество товара

17

DelivDate

Date

Дата продажи

18

DelivQuant

Numeric(10)

Проданное количество товара

19

Manager

Integer

Код менеджера, продавшего товар

20

ManagerName

Character(40)

Имя менеджера

21

GoodsGrp

Character(40)

Группа товара

22

Goods

Integer

Код товара

23

GoodsName

Character(60)

Наименование товара

24

UnitPrice

Numeric(10, 2)

Цена

25

OrderNotes

Character(254)

Примечание к заказу

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

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

2. При изменении адреса или телефона покупателя необходимо корректировать все записи, содержащие сведения о заказах этого покупателя.

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

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

3.1. Первая нормальная форма таблицы

Таблица, структура которой приведена в таблице 3.1, является ненормализованной. Таблица в первой нормальной форме (1НФ) должна удовлетворять следующим требованиям:

1. Ни одна из ее строк не должна содержать в любом своем поле более одного значения.

2. Ни одно из ее ключевых полей не должно быть пусто.

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

Структуры таблиц Customer, Phoncust, Ordsalem, Ordsaled приведены на рис. 3.1. Таблица Customer содержит данные о покупателях. Для этой таблицы первичный индекс определяется с ключевым полем Customer. Аналогичным образом для таблицы Ordsalem создается первичный индекс с ключевым полем Order. Таким образом, для таблиц Customer и Ordsalem решена проблема повторяющихся групп. Для исключения повторяющихся записей в таблице Phoncust нужно создать составной первичный индекс с ключевыми полями Customer и Phone. Таблица Ordsaled содержит сведения о товарах, содержащихся в заданном заказе. Для исключения повторяющихся записей можно построить составной первичный индекс, состоящий из полей Order и Goods.

Рис. 3.1. Первая нормальная форма

После того как повторяющиеся объекты отделены и определены поля, которые образуют уникальный индекс в каждой таблице, считается, что таблица находится в 1НФ. Поэтому (с учетом ограничений) можно считать, что таблицы Customer, Ordsalem и Ordsaled находятся в 1НФ.

3.2. Вторая нормальная форма

Таблица находится во второй нормальной форме (2НФ), если:

1) она удовлетворяет условиям 1НФ;

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

Рассмотрим таблицу Ordsalem, в которой составной индекс образуют поля Order и Goods. Данная таблица не является таблицей в 2НФ, поскольку поля GoodsName, UnitPrice и GoodsGrp однозначно определяются только одним из ключевых полей (Goods).

Для приведения таблицы к 2НФ необходимо выделить из таблицы Ordsalem таблицу Goods, которая будет содержать информацию о товарах каждого типа. Результат изображен на рис. 3.2.

l3r2.bmp

Рис. 3.2. Вторая нормальная форма

3.3. Третья нормальная форма

Таблица находится в третьей нормальной форме (3НФ), если:

1) она удовлетворяет условиям 2НФ;

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

l3r3.bmp

Рис. 3.3. Третья нормальная форма

Сведение таблицы к 3НФ предполагает разделение таблицы с целью помещения в отдельную таблицу (или несколько таблиц) столбцов, которые не зависят от полного ключа. Например, поле ManagerName таблицы Ordsalem содержит имена менеджеров, которые однозначно определяются значением поля Manager. Для приведения этой таблицы к 3НФ необходимо создать новую таблицу Manager (рис. 3.3).

4. ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

Для создания базы данных в Visual FoxPro можно воспользоваться одним из следующих способов:

1. Выполнить команду меню File | New. В открывшемся окне диалога New выбрать опцию Database, а затем нажать кнопку New File.

2. В командном окне выполнить команду

CREATE DATABASE [CDataBaseName | ?]

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

4.1. Создание таблиц

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

1. Выбрать команду Database | New Table.

2. Выбрать команду New Table контекстного меню.

3. Нажать кнопку New Table на панели инструментов Database Designer.

Окно диалога Table Designer содержит три вкладки: Fields, Indexes и Table. На вкладке Fields определяются поля таблицы. Вкладка Indexes служит для определения индексов таблицы. Table позволяет определить для таблицы условие достоверности вводимых данных и триггеры добавления, удаления и модификации данных. Каждое поле таблицы характеризуется наименованием, типом и шириной поля. Для числовых полей дополнительно задается количество десятичных знаков.

4.2. Индексы

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

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

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

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

1. Выбрать таблицу.

2. Открыть окно конструктора таблиц Table Designer и выбрать вкладку Indexes.

Каждый индекс в Visual FoxPro имеет имя, на которое можно в дальнейшем ссылаться при упорядочении данных в соответствии с данным индексом. Имя индекса содержится в поле Name. Переключатель Order определяет вид упорядочивания значений индексного выражения. Type задает тип создаваемого индекса, значения которого представлены в табл. 4.1.

Таблица 4.1.

Типы индексов

Тип индекса

Описание

Regular

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

Unique

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

Candidate

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

Primary

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

Поле Expression служит для ввода индексного выражения, которое можно построить с помощью Expression Builder, нажав кнопку, расположенную правее поля ввода.

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

4.3. Отношения между таблицами

В Visual FoxPro имеется возможность устанавливать постоянные отношения между таблицами, которые будут поддерживаться при создании форм, отчетов и запросов. При определении отношений одна из таблиц является родительской, а другая – дочерней. Для родительской таблицы должен быть определен первичный ключ или ключ-кандидат, а для дочерней – индекс для связи с родительской таблицей. Например, при задании отношений между таблицами Customer и Phoncust для таблицы Customer должен быть определен первичный ключ, а таблица Phoncust должна содержать индекс, не являющийся уникальным, индексное выражение которого состоит из кода покупателя, по которому осуществляется связь между таблицами.

Для определения отношений между таблицами необходимо:

1. Открыть окно конструктора базы данных и выбрать родительскую таблицу.

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

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

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

5. РАБОТА С ТАБЛИЦАМИ БАЗЫ ДАННЫХ

5.1. Рабочие области

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

SELECT NWorkArea | CTableAlias

Первые десять рабочих областей идентифицируются номерами от 1 до 10 или буквами от A до J включительно. Области с 11-й по 25-ю обозначаются номерами или буквенно-цифровыми именами W11–W25. Если в качестве параметра указать цифру 0, произойдет переход в первую свободную рабочую область. Кроме этого, рабочие области и таблицы могут идентифицироваться так называемыми псевдонимами (CTableAlias). Псевдонимом по умолчанию является само имя находящейся в ней таблицы. В качестве псевдонима можно указать и любое другое слово в команде USE.

Одновременно даже в одной команде можно иметь доступ к полям других таблиц. В этом случае имя поля из неактивной области – составное. Имени поля предшествует имя рабочей области или псевдоним, разделенные знаками «->» или точкой «.»:

SELECT a

A. FirstName = "Петр"

Goods->UnitPrice = 100

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

5.2. Открытие и закрытие таблиц

Чтобы открыть таблицу базы данных, используется команда

USE [[DatabaseName!]Table | ?] [IN NWorkArea | CTableAlias] [AGAIN] [ALIAS CTableAlias] [EXCLUSIVE] [SHARED]

Здесь [DatabaseName!]Table | ? – имя базы данных (если необходимо) и имя таблицы в ней либо открытие диалога Open. IN NWorkArea | CTableAlias – рабочая область или псевдоним таблицы. AGAIN – повторное открытие таблицы в другой области, например:

USE customer. dbf IN a

USE ordsalem. dbf IN b

USE customer. dbf IN c AGAIN

SELECT d

USE ordsalem. dbf AGAIN

ALIAS CTableAlias – новый псевдоним таблицы. EXCLUSIVE – открытие таблицы для эксклюзивного использования в сети. SHARED – открытие таблицы для общедоступного использования в сети.

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

Чтобы закрыть таблицу в текущей рабочей области, используется команда USE без параметров, а для закрытия всех таблиц базы данных – CLOSE TABLES ALL.

5.3. Добавление и удаление записей

Команда APPEND [BLANK] добавляет новую запись в конец текущей таблицы и (если включено предложение BLANK) открывает окно редактирования.

Команда INSERT [BEFORE] [BLANK] вставляет новую запись в текущую таблицу сразу после (а если указано BEFORE, то перед) текущей записью таблицы.

Следующая команда помечает записи на удаление

DELETE [Scope] [FOR lExpression1] [WHILE lExpression2] [IN nWorkArea | cTableAlias]

Здесь Scope – предложения вида: ALL – все записи, NEXT NRecords – от текущего номера записи до NRecords, RECORD NRecordNumber – номер записи, REST – от текущей записи до последней. FOR LExpression1 – пометка тех записей, для которых LExpression1 будет истинно. WHILE LExpression2 – записи помечаются до тех пор, пока LExpression2 истинно. IN NWorkArea | CTableAlias – рабочая область или псевдоним.

Записи, помеченные на удаление, физически не удаляются из таблицы до тех пор, пока не выдана команда PACK. Для удаления всех записей таблицы используется команда ZAP. Чтобы снять или восстановить пометки на удаление, используется команда RECALL с такими же параметрами, что и DELETE.

5.4. Просмотр и редактирование данных

Команда BROWSE открывает Browse-окно и отображает записи таблицы. Синтаксис команды следующий:

BROWSE [FIELDS FieldList] [FONT CFontName [, NFontSize]] [STYLE CFontStyle] [FOR LExpression1 [REST]] [NOAPPEND] [NODELETE] [NOEDIT | NOMODIFY] [NORMAL] [NOWAIT] [TITLE CTitleText] [WHEN LExpression3] [WINDOW WindowName1] [IN [WINDOW] WindowName2 | IN SCREEN] [COLOR SCHEME NSchemeNumber]

Здесь FIELDS FieldList – список отображаемых полей. FONT CFontName [, NFontSize] – тип и размер шрифта. STYLE CFontStyle – стиль шрифта. FOR LExpression1 – отображение только тех записей, для которых выражение LExpression1 истинно. NOAPPEND – запрет добавления записей. NODELETE – запрет пометки записей на удаление. NOEDIT | NOMODIFY – запрет изменений в таблице (кроме добавления и пометки на удаление). NORMAL – установка свойств Browse-окна по умолчанию. NOWAIT – продолжение выполнения программы после открытия Browse-окна. TITLE CTitleText – заголовок Browse-окна (по умолчанию имя таблицы). WHEN LExpression3 – разрешение редактирования только тех записей, для которых выражение LExpression3 истинно. WINDOW WindowName1 – Browse-окно принимает свойства окна WindowName1. IN [WINDOW] WindowName2 | IN SCREEN – открывает Browse-окно внутри родительского окна WindowName2 или в главном окне FoxPro.

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

Для редактирования данных можно воспользоваться командой

EDIT [FIELDS FieldList] [Scope] [FOR LExpression1] [WHILE LExpression2] [FREEZE FieldName] [NOAPPEND] [NODELETE] [NOEDIT | NOMODIFY] [NORMAL] [NOWAIT] [TITLE CTitleText] [WHEN LExpression4] [WINDOW WindowName1] [IN [WINDOW] WindowName2 | IN SCREEN] [COLOR SCHEME NSchemeNumber]

Здесь Scope – см. описание команды DELETE. WHILE LExpression2 – записи отображаются в Edit-окне до тех пор, пока значение выражения LExpression2 истинно. FREEZE FieldName – разрешено редактирование только поля FieldName, остальные поля только отображаются. Все остальные параметры команды работают аналогично параметрам команды BROWSE.

6. ПОИСК ДАННЫХ

6.1. Последовательный поиск

Следующая команда осуществляет поиск в текущей таблице:

LOCATE FOR LExpression1

[Scope] [WHILE LExpression2]

Здесь LExpression1 – логическое выражение, которому должна удовлетворять искомая запись. Scope – см. описание команды DELETE. WHILE LExpression2 – поиск продолжается до тех пор, пока LExpression2 истинно.

Для продолжения поиска начатого с помощью команды LOCATE FOR используется команда CONTINUE, которая перемещает указатель на следующую запись, удовлетворяющую логическому выражению LExpression1.

Кроме рассмотренных команд имеется полезная функция поиска

LOOKUP(ReturnField, ESearchExpression, SearchedField)

Функция ищет первое вхождение искомого выражения ESearchExpression в поле SearchedField активной таблицы и возвращает значение поля ReturnField из той же таблицы.

6.2. Ускоренный поиск

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

SEEK EExpression

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

SEEK(EExpression [, NWorkArea | CTableAlias])

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

7. ИНДИВИДУАЛЬНЫЕ ЗАДАНИЯ

Для всех вариантов заданий необходимо сделать следующее:

1. Привести базу данных в нормализованный вид.

2. Средствами Visual FoxPro создать таблицы базы данных.

3. Определить в таблицах соответствующие индексы.

4. Установить связи между таблицами.

5. Заполнить таблицы произвольными данными.

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

Варианты заданий:

1. Дано отношение «Студент», содержащее следующие сведения: фамилия, имя и отчество студента; домашний адрес; номер зачетной книжки; средний балл; название кафедры; фамилия, имя и отчество заведующего кафедрой; телефон кафедры; название факультета; фамилия, имя и отчество декана; телефон деканата.

2. Дано отношение «Сотрудник», содержащее следующие сведения: фамилия, имя и отчество сотрудника; домашний адрес; рабочий телефон; должность; оклад; название кафедры; фамилия, имя и отчество заведующего кафедрой; телефон кафедры; название факультета; фамилия, имя и отчество декана; телефон деканата.

3. Дано отношение «Склад», содержащее следующие сведения: наименование товара; единицы измерения; стоимость одной единицы; количество (в единицах измерения); наименование поставщика; адрес поставщика; телефон поставщика; номер заказа; дата выполнения заказа; количество заказанного товара; наименование получателя; телефон получателя.