Часто создание графической модели позволяет обнаружить недостатки схемы базы данных. Например, созданная ранее схема позволяет сохранять информацию о клиентах и заказах, но заказы состоят из товаров, взятых со склада компании и проданных клиенту. Однако в данной схеме базы данных не предусмотрена возможность просмотра товаров, заказанных клиентом.
Для решения этой проблемы нужно создать таблицу для хранения сведений о товарах заказа, которая имеет приведенную ниже структуру.
tblOrderItem ID OrderID ItemID Quantity Cost
Теперь между таблицами tblOrder и tblOrderItem существует отношение типа один-ко-многим, как показано на рис. 1.12.
Полностью схему всей базы данных Novelty можно скопировать в виде файла для программы Visio с Web-страницы этой книги на Web-сервере Издательского дома "Вильяме" по адресу: www.williamspublishng.com.
НА ЗАМЕТКУ
Не следует путать процесс создания схемы базы данных с процессом создания программного обеспечения. В большинстве компаний по созданию программного обеспечения используется методология, которая регламентирует решаемые бизнес-задачи, внешний вид программного обеспечения и способ его создания. Их нужно учитывать при разработке базы данных.
Отношение — это способ формального определения того, как две таблицы связаны друг с другом. При определении отношения необходимо сообщить процессору баз данных, через какие два поля связываются две таблицы, участвующие в создании отношения.
РИС. 1.12. Схема базы данных с отношениями между четырьмя таблицами
Полями, создающими отношение, являются первичный ключ (представленный выше в этой главе) и внешний (foreign) ключ. Внешний ключ — это ключ в связанной таблице, который хранит копию первичного ключа из основной таблицы.
Предположим, у вас есть таблицы с характеристиками отделов и сотрудников компании. Отношение между отделом и группой сотрудников можно определить типом один-ко-многим. Каждому отделу присваивается собственный идентификационный номер (ID), и каждый сотрудник имеет свой ID. Но, чтобы указать, в каком отделе работает каждый сотрудник, необходимо сделать копию номера ID отдела в каждой записи, содержащей данные о сотруднике. Итак, чтобы идентифицировать каждого сотрудника как члена некоторого отдела, в таблице Employees (Сотрудники) должно быть предусмотрено поле (именуемое, допустим, Department ID) для хранения ID отдела, в котором работает данный сотрудник. Поле Department ID в таблице Employees служит внешним ключом таблицы Employees, поскольку хранит копию первичного ключа таблицы Departments (Отделы).
Благодаря отношению процессор баз данных "знает", какие две таблицы участвуют в этом отношении и какой внешний ключ связан с первичным ключом. Для прежнего процессора Jet базы данных Access явное объявление отношений не обязательно, но это в ваших же интересах, поскольку при таком объявлении упрощается задача выборки данных из записей двух или нескольких связанных таблиц (подробнее об этом в главе 2, "Запросы и команды на языке SQL"). Отсутствие такого объявления – один из основных недостатков технологии Jet, который можно устранить с помощью переноса унаследованных приложений на основе технологии Jet на платформу ADO.NET. Помимо соответствия связанных записей в отдельных таблицах, отношение определяется для того, чтобы воспользоваться преимуществами ссылочной целостности, под которой понимают свойство процессора базы данных, обеспечивающее непротиворечивость информации, хранимой в многотабличной базе данных. Когда ссылочная целостность имеет место в базе данных, то процессор баз данных препятствует удалению записи в случае, если в базе данных существуют другие записи, связанные с ней.
После того как вы определите отношение в базе данных, это определение сохраняется до тех пор, пока вы не удалите его. Отношения можно создавать графически, используя инструменты Visual Studio .NET, SQL Enterprise Manager, Visio, или с помощью сценариев на языке DDL.
Использование ссылочной целостности для поддержания непротиворечивости данных
Когда таблицы связаны между собой посредством отношений, данные в каждой из связанных таблиц должны оставаться согласованными друг с другом. Ссылочная целостность справляется с этой задачей, отслеживая отношения между таблицами и запрещая выполнение определенных типов операций над записями.
Допустим, у вас есть таблицы tblCustomer и tblOrder. Эти две таблицы связаны через общее поле ID.
Предполагается, что сначала вы регистрируете клиентов в таблице tblCustomer, а затем создаете записи с информацией о заказах в таблице tblOrder. Но что произойдет, если запустить процесс, удаляющий запись с данными о клиенте, который оформил заказы, зарегистрированные в таблице tblOrder? А если создать заказ, для которого не существует действительного значения поля CustomerID? Любой заказ без значения поля CustomerID не будет отгружен, поскольку адрес отгрузки представляет собой функцию от записи в таблице tblCustomer. Когда данные в связанных таблицах страдают от такого рода проблемы, их называют несогласованными или противоречивыми.
Поскольку очень важно, чтобы база данных не стала противоречивой, во многих процессорах баз данных (включая SQL Server) предусмотрен способ определения формальных отношений между таблицами. При формальном определении отношения между двумя таблицами процессор баз данных отслеживает это отношение и препятствует выполнению любой операции, которая "покушается" на ссылочную целостность.
Ссылочная целостность обеспечивается путем генерирования ошибок при выполнении действия, которое могло бы оставить данные в противоречивом состоянии. Например, в базе данных с активизированной ссылочной целостностью при попытке создать заказ, содержащий идентификационный номер (ID) клиента, которого на самом деле не существует, вы получите сообщение об ошибке и "подозрительный" заказ не будет создан.
Проверка ограничений ссылочной целостности с помощью Server Explorer
Для проверки отношения между таблицами tblCustomer и tblOrder попробуем использовать окно Server Explorer среды Visual Studio .NET. Для этого выполните перечисленные ниже действия.
1. Откройте схему базы данных Novelty с двумя таблицами- tblCustomer и tblOrder. Обратите внимание на то, что эта схема содержит и другие таблицы, но в данном случае нас интересует отношение между этими таблицами.
2. Щелкните правой кнопкой мыши на линии отношения между двумя этими таблицами и из контекстного меню выберите команду Property Pages.
3. После появления на экране страницы свойств данного отношения выберите вкладку Relationships, в которой указаны поле ID таблицы tblCustomer, поле CustomerID таблицы tblOrder и ограничения ссылочной целостности в нижней части.
НА ЗАМЕТКУ
По умолчанию при создании отношения задаются ограничения ссылочной целостности (например, нельзя создать заказ для несуществующего клиента), но не заданы условия каскадного удаления. Более подробно эти условия рассматриваются далее.
4. Установите флажок Enforce Relationship for INSERTS and UPDATES (Применять каскадное обновление и удаление), а затем щелкните на кнопке Close.
5. Для сохранения внесенных изменений выберите команду File→Save Relationships.
Для проверки заданного ограничения выполните перечисленные ниже действия.
1. В окне Server Explorer щелкните правой кнопкой мыши на таблице tblOrder и выберите из контекстного меню команду Retrieve Data from Table.
2. Введите заказ для клиента идентификатором которого на самом деле нет в таблице с данными о клиентах.
3. Перейдите в другую строку для автоматического сохранения введенного заказа.
В результате на экране появится диалоговое окно с предупреждением: INSERT statement conflicted with COLUMN FOREIGN KEY constraint ' FK_tblOrder_tblCustomer'. Conflict occurred in database 'Novelty', table 'tblCustomer', column 'ID'. The statement has been terminated. (Команда INSERT конфликтует с ОГРАНИЧЕНИЕМ ПО ВНЕШНЕМУ КЛЮЧУ 'FK_tblOrder_tblCustomer'. Конфликт произошел в базе данных 'Novelty', таблице 'tblCustomer', поле 'ID'. Выполнение команды прекращено.)
4. Щелкните на кнопке OK в окне с предупреждением и отмените команду вставки новой записи с помощью клавиши <Esc>.
В данном случае наша цель заключалась не во вводе нового заказа, а в демонстрации сообщения об ошибке. Однако если вам действительно нужно создать заказ, то в таком случае следует создать запись для клиента, получить его идентификатор ID и использовать его в поле CustomerID при создании заказа.
В рабочем приложении эта проблема обычно решается автоматически с помощью специально созданного пользовательского интерфейса. Далее в книге рассматривается несколько стратегий согласованного управления связанными данными.
