Создание многотабличной реляционной базы данных

В этой главе…

· Что должно быть в базе данных

· Определение отношений между элементами базы данных

· Связывание таблиц с помощью ключей

· Проектирование целостности данных

· Нормализация базы данных

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

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

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

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

1. Решите, какие объекты должны быть в вашей базе данных.

2. Установите, какие из этих объектов должны быть таблицами, а какие – столбцами этих таблиц.

3. Определите таблицы в соответствии с тем, как вы решили организовать объекты.

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

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

Действие 1: определение объектов.

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

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

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

Действие 2: определение таблиц и столбцов.

Главные объекты переводятся в таблицы базы данных. И у каждого из главных объектов имеется набор связанных между собой атрибутов, которые переводятся в столбцы соответствующей таблицы. Например, во многих базах данных, применяемых в бизнесе, имеется таблица CUSTOMER (клиент), в которой хранятся имена клиентов, адреса и другая информация о них. Каждый из атрибутов клиента, такой, например, как имя, улица, город, штат, почтовый код, номер телефона и адрес в Internet, становится столбцом в таблице CUSTOMER.

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

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

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

· о клиентах;

· о выполненных анализах;

· о сотрудниках;

· о заказах;

· о результатах.

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

Действие 3: точное определение таблиц.

Теперь для каждого объекта вам необходимо точно определить таблицу, а для каждого атрибута – столбец. В табл. 5.1 показаны таблицы базы данных VetLab.

Таблица 5.1. Таблицы базы данных VetLab.

Таблица	Столбцы
CLIENT (фирма-клиент)	Client Name (название фирмы-клиента)
	Address 1 (адрес 1)
	Address 2 (адрес 2)
	City (город)
	State (штат)
	Postal Code (почтовый код)
	Phone (телефон)
	Fax (факс)
	Contact Person (контактный представитель)
TESTS (анализы)	Test Name (название анализа)
	Standard charge (стандартная цена)
EMPLOYEE (сотрудник)	Employee Name (фамилия сотрудника)
	Address 1 (адрес 1)
	Address 2 (адрес 2)
	City (город)
	State (штат)
	Postal Code (почтовый код)
	Home Phone (домашний телефон)
	Office Extension (телефонный номер в офисе)
	Hire Date (дата приема на работу)
	Job classification (трудовая классификация)
	Hourly/Salary/Commission (почасовая оплата/зарплата/комиссионные)
ORDERS (заказы)	Order Number (номер заказа)
	Client Name (название фирмы-клиента)
	Test ordered (заказанный анализ)
	Responsible Salesperson (сотрудник, принявший заказ)
	Order Date (дата заказа)
RESULTS (результаты)	Result Number (номер результата)
	Order Number (номер заказа)
	Result (результат)
	Date Reported (сообщенная дата)
	Preliminary / Final (предварительный/окончательный)

Таблицы, определенные в табл. 5.1, можно создать или с помощью инструмента для быстрой разработки приложений (Rapid Application Development, RAD), или с помощью языка определения данных (Data Definition Language, DDL), входящего в состав SQL, как показано ниже.

CREATE TABLE CLIENT (
ClientName	CHARACTER (30),	NOT NULL,
Address1	CHARACTER (30),
Address2	CHARACTER (30),
City	CHARACTER (25),
State	CHARACTER (2),
PostalCode	CHARACTER (10),
Phone	CHARACTER (13),
Fax	CHARACTER (13),
ContactPerson	CHARACTER (30));
CREATE TABLE TESTS (
TestName	CHARACTER (30)	NOT NULL,
StandardCharge	CHARACTER (30));
CREATE TABLE EMPLOYEE (
EmployeeName	CHARACTER (30)	NOT NULL,
Address1	CHARACTER (30),
Address2	CHARACTER (30),
City	CHARACTER (25),
State	CHARACTER (2),
PostalCode	CHARACTER (10),
HomePhone	CHARACTER (13),
OfficeExtension	CHARACTER (4),
HireDate	DATE,
JobClassification	CHARACTER (10),
HourSalComm	CHARACTER (1));
CREATE TABLE ORDERS (
OrderNumber	INTEGER	NOT NULL,
ClientName	CHARACTER (30),
TestOrdered	CHARACTER (30),
Salesperson	CHARACTER (30),
OrderDate	DATE);
CREATE TABLE RESULTS (
ResultNumber	INTEGER	NOT NULL,
OrderNumber	INTEGER
Result	CHARACTER (50),
DateReported	DATE,
PrelimFinal	CHARACTER (1));

Эти таблицы относятся друг к другу посредством общих атрибутов (столбцов).

· Таблица CLIENT связана с таблицей ORDERS с помощью столбца ClientName.

· Таблица TESTS связана с таблицей ORDERS с помощью столбца TestName (TestOrdered).

· Таблица EMPLOYEE связана с таблицей ORDERS с помощью столбца ЕmployeeName (Salesperson).

· Таблица RESULTS связана с таблицей ORDERS с помощью столбца OrderNumber.

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

Рис. 5.1. Таблицы и связи базы данных VetLab

На рис. 5.1 показаны четыре различных отношения типа "один ко многим". В изображении отношения одна стрелка указывает на сторону "один", а двойная – на сторону "многие".

· Один клиент может сделать множество заказов, но каждый заказ делается одним и только одним клиентом.

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

· Каждый заказ принимается одним и только одним сотрудником (т.е. представителем вашей компании), но каждый представитель может принимать (и, как вы надеетесь, принимает) множество заказов.

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

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