|
|
Глава 5. Создание многотабличной реляционной базы данных В этой главе...
В этой главе будет представлен пример создания многотабличной базы данных. Первый шаг при проектировании такой базы — решить, что в ней должно быть, а чего не должно. Второй шаг состоит в том, чтобы установить, каким образом имеющиеся в базе элементы будут связаны друг с другом, и создать таблицы с учетом этой информации. Я расскажу, как использовать ключи для получения быстрого доступа к индивидуальным табличным записям и индексам. База данных должна не просто хранить данные, но и защищать их от повреждений. Далее в этой главе обсуждаются методы защиты целостности данных. Одним из самых важных методов является нормализация, поэтому я расскажу о различных "нормальных" формах и проблемах, решаемых с их помощью. Проектирование базы данных При проектировании базы данных выполните следующие основные действия (подробно о каждом из них рассказывается в последующих разделах).
Возможно, вы захотите назначить ключом какой-либо столбец таблицы или комбинацию ее столбцов. Ключи позволяют быстро найти в таблице нужную вам строку. В последующих разделах подробно описываются эти действия, а также рассматриваются некоторые другие вопросы, связанные с проектированием базы данных. Действие 1: определение объектов Первое действие, которое предстоит выполнить при проектировании базы данных, — это решить, какие аспекты системы являются достаточно важными, чтобы быть включенными в модель. Каждый такой аспект рассматривайте как объект и составьте список этих объектов — всех, которые только придут вам в голову. И не пытайтесь гадать, каким образом эти объекты связаны друг с другом. Всего лишь попробуйте внести их в список. Совет: Было бы полезно иметь команду из нескольких человек, знакомых с системой, которую вы моделируете. Они могли бы проводить "мозговой штурм" и реагировать на высказанные друг другом идеи. Вместе вы, вероятно, разработаете более полный и точный набор объектов. Когда вы решите, что получился достаточно полный набор объектов, можете приступать к следующему действию: определить, каким образом эти объекты связаны друг с другом. Некоторые из них являются главными и играют важную роль в получении нужных вам результатов. Другие же являются подчиненными объектами по отношению к главным. Кроме того, следует окончательно решить, какие объекты вообще не являются частью модели. Действие 2: определение таблиц и столбцов Главные объекты переводятся в таблицы базы данных. И у каждого из главных объектов имеется набор связанных между собой атрибутов, которые переводятся в столбцы соответствующей таблицы. Например, во многих базах данных, применяемых в бизнесе, имеется таблица CUSTOMER (клиент), в которой хранятся имена клиентов, адреса и другая информация о них. Каждый из атрибутов клиента, такой, например, как имя, улица, город, штат, почтовый код, номер телефона и адрес в Internet, становится столбцом в таблице CUSTOMER. Внимание: При проектировании структуры базы данных важно учитывать интересы ее будущих пользователей, а также лиц, принимающих решения на основе ее информации. Если созданная вами "разумная" структура не будет соответствовать способу, каким эти люди работают с информацией, то в лучшем случае ваша система может разочаровать своих пользователей. Она может даже выдавать неверную информацию, что намного хуже, чем просто быть трудной в использовании. Такого не должно быть! Хорошо подумайте, перед тем как принимать решение о структуре таблиц. Рассмотрим пример, который показывает процесс создания многотабличной базы данных. Скажем, вы только что основали VetLab — клиническую микробиологическую лабораторию, где проводятся анализы проб, присланных из ветеринарных фирм. Конечно, вам бы хотелось иметь данные:
У каждого их этих объектов имеются связанные между собой атрибуты. Для клиента такие атрибуты — название, адрес и другая информация для контакта. Для анализа — название и стандартная оплата. Для каждого сотрудника — его адрес, телефон, должность, квалификация и уровень оплаты. О заказе необходимо знать, кто его заказчик, когда заказ был оформлен и какой именно анализ в нем указан. А что касается результатов анализов, то необходимо знать данные, полученные при его проведении, был ли анализ предварительным или окончательным, а также номер его заказа. Действие 3: точное определение таблиц Теперь для каждого объекта вам необходимо точно определить таблицу, а для каждого атрибута — столбец. В табл. 5.1 показаны таблицы базы данных VetLab. Таблица 5.1. Таблицы базы данных VetLab.
Таблицы, определенные в табл. 5.1, можно создать или с помощью инструмента для быстрой
Эти таблицы относятся друг к другу посредством общих атрибутов (столбцов).
Есть простой способ сделать таблицу неотъемлемой частью реляционной базы. Надо связать эту таблицу с помощью общего столбца как минимум с еще одной таблицей из той же базы. Отношения между таблицами показаны на рис. 5.1.
Рис. 5.1. Таблицы и связи базы данных VetLab На рис. 5.1 показаны четыре различных отношения типа "один ко многим". В изображении отношения одна стрелка указывает на сторону "один", а двойная — на сторону "многие".
Как видно на рисунке, атрибут, который связывает одну таблицу с другой, может в каждой из этих таблиц называться по-своему. Однако тип данных у этих атрибутов должен совпадать. Хотя главными компонентами базы данных являются таблицы, но другие ее элементы также играют определенную роль. В главе 1 домен табличного столбца был определен как набор всех значений, которые допустимы для этого столбца. Создание с помощью ограничений четко определенных доменов табличных столбцов — это важная часть проектирования базы данных. Реляционными базами данных пользуются не только те, кто разговаривает на американском варианте английского языка. С этими базами можно работать, используя и другие языки, даже те, у которых другие символьные наборы. Даже если база данных создана с использованием только английского языка, некоторые приложения могут запросить специальные символьные наборы. SQL:2003 позволяет точно определить тот набор, который вам нужно использовать. Фактически можно для каждого табличного столбца использовать отдельный символьный набор. В языках, отличных от SQL, подобной гибкости обычно нет. Сопоставление, или последовательность сопоставления, — это набор правил, которые определяют, каким образом сравниваются друг с другом строки, состоящие из элементов определенного символьного набора. Каждый символьный набор имеет свое сопоставление по умолчанию. В сопоставлении по умолчанию для символьного набора ASCII В следует после А, а С — после В. Поэтому при сравнении считается, что А меньше В, а С больше В. С другой стороны, SQL:2OO3 дает возможность применять к символьному набору и другие сопоставления. Повторяю снова, что в других языках подобной степени гибкости обычно нет. Иногда данные в базе кодируются с помощью одного символьного набора, но работать с ними нужно с помощью другого набора. У вас, например, есть данные, закодированные в немецком символьном наборе, но те немецкие символы, которые не входят в набор ASCII, на вашем принтере не печатаются. Трансляция — это функциональная возможность SQL:2003, позволяющая преобразовывать символьные строки из одного набора в другой. Трансляция, например, позволяет преобразовывать один символ в два, в частности немецкий u — в ue из ASCII, или может преобразовывать символы из нижнего регистра в верхний. Можно даже преобразовать один алфавит в другой, например алфавит языка иврит в символы ASCII. Ускорение работы базы данных с помощью ключей В проектировании баз данных используется хорошее правило: в таблице каждая строка отличается от любой другой, т.е. каждая строка должна быть уникальной. Иногда вам может потребоваться для определенной цели — например, для статистического анализа — извлечь из своей базы некоторые данные и создать на их основе таблицы, где строки не обязательно являются уникальными. Для данного конкретного случая правило отсутствия дублирования может не выполняться. Однако в общем его нужно строго придерживаться. Ключ — это атрибут или сочетание атрибутов, которое однозначно определяет строку в таблице. Чтобы получить доступ к строке, необходимо иметь способ, позволяющий отличить эту строку от всех остальных. Так как ключи уникальны, они позволяют выполнять такой доступ. Более того, ключ никогда не должен содержать неопределенное значение. При использовании неопределенных ключей две строки, содержащие такие ключевые поля, были бы неотличимы друг от друга. Что касается примера с ветеринарной лабораторией, то здесь в качестве ключей вы можете назначить подходящие для этого столбцы. В таблице CLIENT хороший ключ получается из столбца ClientName. Этот ключ может отличить любого клиента от всех остальных. Таким образом, ввод значения в этот столбец становится обязательным для каждой строки таблицы CLIENT. Из столбцов TestName и EmployeeName получаются хорошие ключи для таблиц TESTS и EMPLOYEE. To же относится к столбцам OrderNumber и ResultNumber таблиц ORDERS и RESULTS соответственно. В любом случае надо проверять, вводится ли в каждую строку уникальное значение ключа. Ключи могут быть двух видов: первичные и внешние. Ключи, о которых говорилось в предыдущем абзаце, на самом деле являются первичными. Благодаря им гарантируется уникальность строк. О внешних ключах рассказывается в этом разделе чуть позже. Первичные ключи Чтобы реализовать в базе данных VetLab идею ключей, при создании таблицы можно сразу указывать ее первичный ключ. В следующем примере будет достаточно одного столбца (при условии, что у фирм-клиентов VetLab разные названия):
Здесь ограничение NOT NULL (не может быть неопределенным значением), которое было в предыдущем определении таблицы CLIENT, заменено другим ограничением — PRIMARY KEY (первичный ключ). Второе ограничение подразумевает первое, потому что первичный ключ не может иметь неопределенное значение. Несмотря на то что большинство СУБД позволяет создавать таблицу без единого ключа, важно помнить, что все таблицы базы данных должны иметь первичный ключ. Поэтому нужно ввести ограничение NOT NULL в таблицы TESTS, EMPLOYEE, ORDERS и RESULTS вместе с ограничением PRIMARY KEY, как показано в следующем примере: CREATE TABLE TESTS ( TestName CHARACTER (30) PRIMARY KEY, StandardCharge CHARACTER (30) ) ; Иногда в таблице ни один единичный столбец не может гарантировать уникальность строки. В таких случаях можно использовать составной ключ. Он является сочетанием столбцов, совместное использование которых гарантирует уникальность. Представьте, что некоторые клиенты VetLab — это фирмы, имеющие свои отделения в нескольких городах. В таком случае поля ClientName будет недостаточно, чтобы различить два разных отделения одного и того же клиента. Чтобы решить эту проблему, можно определить следующий составной ключ:
Внешний ключ — это столбец или группа столбцов в таблице, соответствующие первичному ключу (т.е. ссылающиеся на первичный ключ) из другой таблицы базы данных. Внешний ключ сам по себе может и не быгь уникальным, но должен однозначно называть столбец в той таблице, на которую он ссылается. Если столбец ClientName — это первичный ключ таблицы CLIENT, то каждая строка этой таблицы должна иметь в столбце ClientName уникальное значение. В свою очередь, в таблице ORDERS ClientName является внешним ключом. Этот внешний ключ соответствует первичному ключу таблицы CLIENT, но в таблице ORDERS он может и не быть уникальным. На самом деле вы, конечно же, надеетесь, что внешний ключ не является уникальным. Ведь если бы каждая из фирм ваших клиентов сделала у вас только один заказ и больше этого не повторяла, то ваш бизнес довольно быстро бы прекратился. На самом деле вы надеетесь, что каждой строке таблицы CLIENT соответствует много строк таблицы ORDERS, показывая этим, что почти все ваши клиенты постоянно пользуются вашими услугами. Следующее определение таблицы ORDERS показывает, каким образом в операторе CREATE можно задавать внешние ключи:
Внешние ключи таблицы ORDERS связывают ее с первичными ключами таблиц CLIENT, TESTS и EMPLOYEE. Работа с индексами Спецификация SQL:2003 к теме индексов не обращается, но это не значит, что они являются редкой или даже необязательной частью системы баз данных. Индексы поддерживаются каждой реализацией SQL, но общего соглашения по их поддержке не существует. В главе 4 было показано, как создать индекс с помощью RAD-инструмента Microsoft Access. Чтобы разобраться, как индексы используются в конкретной системе управления базами данных, необходимо обратиться к ее документации. Что такое индекс Данные в таблице обычно отображаются в том порядке, в каком их в нее первоначально ввели. Однако такой порядок может не иметь ничего общего с тем порядком, в котором затем требуется эти данные обрабатывать. Скажем, что вы, например, хотите обрабатывать таблицу CLIENT в такой последовательности, чтобы значения в столбце ClientName располагались в алфавитном порядке. Но на такую сортировку записей таблицы требуется определенное время. И чем таблица больше, тем сортировка проходит дольше. Что если в вашей таблице сто тысяч записей? Или миллион? А ведь в некоторых приложениях таблицы такого размера не являются редкостью. Даже при выполнении лучших алгоритмов сортировки, чтобы выстроить записи таблицы в нужном порядке, все равно придется в таком случае проделать примерно двадцать миллионов сравнений и миллионы перестановок. И пусть у вас очень быстрый компьютер, но подождать все-таки придется. Индексы могут оказаться прекрасным средством экономии времени. Индекс — это подчиненная таблица, или таблица поддержки, которая сопровождает свою таблицу данных. Каждой строке таблицы данных соответствует определенная строка таблицы индекса. Однако порядок расположения строк в таблице индекса другой. Небольшой пример таблицы данных показан в табл. 5.2. Таблица 5.2. Таблица CLIENT
Строки следуют в таком порядке, что значения в столбце ClientName располагаются не по алфавиту. Строки находятся в том порядке, в каком их кто-то вводил. Индекс для этой таблицы CLIENT может выглядеть примерно так, как табл. 5.3. Таблица 5.3. Индекс по названию клиента для таблицы CLIENT
В индексе находятся поле, которое является его основой (в данном случае это поле ClientName), и указатель к таблице данных. Значение указателя, расположенное в каждой строке индекса, является номером соответствующей строки таблицы данных. Зачем нужен индекс Предположим, мне нужно обрабатывать таблицу в том порядке, при котором значения из поля ClientName будут выстроены по алфавиту. При этом у меня есть индекс, в котором значения из этого поля выстроены именно по алфавиту. Тогда свою работу я смогу выполнять так же быстро, как если бы сами записи таблицы данных были выстроены в том же порядке. У меня есть возможность последовательно перемещаться по строкам индекса и при этом немедленно переходить, используя указатель индекса, на те строки таблицы данных, которые соответствуют строкам индекса. При использовании индекса время обработки таблицы пропорционально N, где N — количество ее строк. А при выполнении той же операции, но без индекса, время обработки таблицы Например, у вас есть таблица с 1000000 записей (N = 1000000) и на обработку каждой записи уходит одна миллисекунда (одна тысячная секунды). Если у вас есть индекс, то на обработку всей таблицы уйдет только 1000 секунд, т.е. меньше 17 минут. Однако, чтобы получить тог же результат без индекса, таблицу придется обрабатывать 100000x20 раз. Таким образом, этот процесс должен занять 20000 секунд, т.е. больше пяти с половиной часов. Думаю, вы согласитесь, что разница между семнадцатью минутами и пятью с половиной часами довольно-таки существенная. Это и есть та разница, которая создается индексированием записей. Поддержание индекса Если индекс создан, то его необходимо поддерживать. К счастью, вместо вас индексы поддерживает ваша система СУБД, которая обновляет их каждый раз, когда вы обновляете соответствующие таблицы данных. На этот процесс требуется немного дополнительного времени, но эти затраты того стоят. После того как вы создали индекс и СУБД его поддерживает, индекс всегда готов ускорять обработку ваших данных. И неважно, сколько раз он будет для этого использоваться. Совет: Ясно, что лучше всего создавать индекс тогда, когда создается соответствующая ему таблица данных. Если индекс создается в самом начале и тогда же начинает поддерживаться, то вам не придется мучиться позднее с его созданием. Тогда на эту операцию придется тратить целый сеанс, длящийся довольно долго. Попробуйте предвидеть все способы, которые вам потребуются для доступа к данным, а затем для каждой такой возможности создайте свой индекс. В некоторых продуктах СУБД имеется возможность отключать поддержку индексов. Такое, возможно, придется делать в некоторых практических приложениях, где на обновление индексов уходит много времени, которое нужно экономить. Иногда даже приходится производить обновление индексов специальной операцией, выполняемой в промежутках между часами пик. Внимание: Не попадитесь на удочку, создавая индексы для получения относящихся к заказам данных, которые вы вряд ли когда-нибудь будете использовать. С поддержкой индекса связана определенная потеря производительности, потому что эта поддержка является дополнительной операцией, которую компьютер должен выполнять каждый раз, когда модифицирует поле индекса или добавляет (удаляет) строку в таблице данных. Чтобы добиться оптимальной производительности, создавайте только те индексы, которые действительно собираетесь использовать в качестве ключей получения данных, и только для таблиц, имеющих большое количество строк. Ведь в противном случае индексы только ухудшат производительность. Совет: Вам, возможно, потребуется составить нечто похожее на месячный или квартальный отчет, где нужны данные, выстроенные в каком-либо необычном порядке. В таком случае, перед тем как генерировать отчет, создайте индекс. Затем, после того как отчет будет готов, удалите этот индекс. Он не должен обременять СУБД, которой пришлось бы его поддерживать в течение долгого времени между отчетами. Обеспечение целостности База данных представляет ценность лишь тогда, когда вы в достаточной степени уверены, что находящиеся в этой базе данные правильные. Например, неправильные данные в медицинских, авиационных и космических базах данных могут привести к человеческим жертвам. Неправильные данные в других приложениях приводят не к таким плачевным последствиям, но нанести ущерб все же могут. Поэтому проектировщик базы данных должен гарантировать, что в нее неправильные данные никогда не попадут. Некоторые неприятности нельзя остановить на уровне базы данных. Об их заблаговременном предотвращении (перед тем как они нанесут ущерб базе) должен позаботиться прикладной программист. Каждый, кто каким-либо образом отвечает за работу с базой данных, должен осознавать, какие опасности угрожают целостности ее данных, и принять все меры, чтобы свести эти опасности к нулю. В базах данных целостность может быть нескольких видов, причем довольно-таки разных. Разными могут быть и неприятности, которые представляют угрозу целостности. В следующих разделах рассказывается о трех видах целостности: смысловой, доменной и ссылочной. Кроме того, рассматриваются некоторые угрозы целостности баз данных. Смысловая целостность Каждая таблица базы данных соответствует какому-либо объекту реального мира. Такой объект может быть физическим или умозрительным, но его существование в некотором смысле не зависит от базы данных. Если таблица полностью соответствует объекту, который она моделирует, то она обладает смысловой целостностью. Чтобы иметь такую целостность, таблица должна иметь первичный ключ. Этот ключ однозначно определяет каждую строку таблицы. Если его нет, то нет и уверенности, что вы получите нужную вам строку. Чтобы поддержать целостность объекта, необходимо для столбца или группы столбцов, из которых состоит первичный ключ, указать NOT NULL. Кроме того, для первичного ключа еще необходимо ограничение UNIQUE. В некоторых реализациях SQL такое ограничение указывается непосредственно в определении таблицы. В других же реализациях его приходится задавать уже после того, как будет указано, каким образом данные следует добавлять в таблицу, изменять и удалять из нее. Проще всего добиться, чтобы первичный ключ был и NOT NULL, и UNIQUE, — это использовать ограничение PRIMARY KEY, как показано в следующем примере:
Альтернативой этому способу является использование ограничения NOT NULL в сочетании с UNIQUE:
Доменная целостность Обычно вы не можете гарантировать, что конкретный элемент данных из базы правильно введен, но можете хотя бы определить, разрешено ли его использование. У многих элементов данных набор возможных значений является ограниченным. Если вводится значение, которое не входит в этот набор, то такой ввод должен считаться ошибочным. Например, Соединенные Штаты состоят из 50 штатов, округа Колумбия, Пуэрто-Рико и еще нескольких владений. У каждой из этих территорий имеется код, состоящий из двух символов и признанный почтовой службой США. И если в базе данных имеется столбец State (штат), то доменную целостность можно обеспечить, требуя, чтобы любой ввод в этот столбец был одним из разрешенных двух-символьных кодов. Если оператор вводит код, не входящий в список принятых кодов, он тем самым нарушает доменную целостность. Проверяя соблюдение доменной целостности, вы можете отказываться принимать любую нарушающую эту целостность операцию. Опасения за доменную целостность возникают при вводе в таблицу новых данных, выполняемом с помощью оператора INSERT или UPDATE. Домен для столбца можно установить с помощью оператора CREATE DOMAIN (создать домен), причем до того, как использовать этот столбец в операторе CREATE TABLE. Это показано в следующем примере, где перед созданием таблицы TEAM (команда) со столбцами TeamName (имя команды) и League (лига) создается домен LeagueDom (домен значений лиги):
Домен для столбца League состоит из двух разрешенных значений: American (американская) и National (национальная). СУБД не позволит успешно выполнить ввод или обновление в таблице TEAM, если в столбце League вводимой или обновляемой строки появляется значение, которое отличается от American или National. Ссылочная целостность Даже если в базе данных для каждой таблицы установлены целостность объекта и доменная целостность, то этой базе все равно грозят неприятности, происходящие из-за того, что связь одной таблицы с другой не согласована. В большинстве хорошо спроектированных баз данных в каждой таблице имеется как минимум один столбец, который ссылается на столбец из другой таблицы той же базы. Такие ссылки играют важную роль при поддержании общей целостности базы данных. Впрочем, те же ссылки делают возможными аномалии обновления. Аномалии обновления — это неприятности, которые могут происходить после обновления значений в строке базы данных. В целом отношения между таблицами не являются равноправными. Обычно одна таблица зависит от другой. Скажем, у вас, например, имеется база данных с таблицами CLIENT (фирма-клиент) и ORDERS (заказы). Вы можете намеренно ввести в таблицу CLIENT данные фирмы-клиента еще до того, как ею будут сделаны какие-либо заказы. Однако в таблицу ORDERS нельзя будет ввести ни одного заказа, если в первой, CLIENT, не будет записи для клиента, делающего этот заказ. Получается, что таблица ORDERS зависит от таблицы CLIENT. Такой порядок часто называют родительско-дочерним отношением таблиц, при котором CLIENT — это родительская, a ORDERS — дочерняя таблица. Дочерний элемент базы данных зависит от родительского. Обычно первичный ключ родительской таблицы — это столбец (или группа столбцов), который имеется и в дочерней таблице. И там он уже является внешним ключом. Во внешнем ключе могут находиться неопределенные значения, и ему не нужно быть уникальным. Аномалии обновления возникают несколькими способами. Например, фирма-клиент не делает у вас заказов, и вы хотите удалить ее данные из базы. И если она уже сделала у вас некоторые заказы, данные о которых записаны в таблице ORDERS, то удаление ее данных из таблицы CLIENT может вызвать трудности. Дело в том, что тогда в дочерней таблице ORDERS остались бы записи, для которых не было бы соответствующих записей в главной таблице CLIENT. Аналогичные трудности могут возникнуть и тогда, когда запись в дочернюю таблицу добавляется, а соответствующее добавление в родительскую еще не сделано. Все изменения первичного ключа, происходящие в любой строке родительской таблицы, должны отражаться в соответствующих внешних ключах всех дочерних таблиц. Если этого не произойдет, образуются аномалии обновления. Большинство трудностей, связанных со ссылочной целостностью, можно свести к минимуму, если тщательно управлять процессом обновления. В некоторых случаях необходимо каскадное удаление из родительской таблицы и ее дочерних таблиц. Чтобы при удалении строки из родительской таблицы произошли каскадные удаления, необходимо из всех дочерних таблиц удалить строки, значение внешнего ключа которых равно значению первичного ключа строки, удаляемой из главной таблицы. Рассмотрим следующий пример:
Ограничение NameFK делает поле ClientName внешним ключом, который указывает на столбец ClientName таблицы CLIENT. Когда в таблице CLIENT удаляется строка, то в таблице ORDERS автоматически удаляются все строки, у которых в столбце ClientName имеется то же значение, что и в столбце ClientName удаляемой строки таблицы CLIENT. Происходит каскадное удаление— вначале в таблице CLIENT, а затем в таблице ORDERS. To же самое верно для внешних ключей таблицы ORDERS, которые являются первичными ключами таблиц TESTS и EMPLOYEE. Возможно, вы не хотите производить каскадное удаление, а вместо этого хотите заменить внешний ключ подчиненной таблицы значением NULL. Проанализируйте следующий вариант предыдущего примера:
Ограничение SalesFK определяет поле Salesperson внешним ключом, который указывает на столбец EmployeeName таблицы EMPLOYEE. Если сотрудница, работавшая вашим представителем при оформлении заказов, уходит из компании, вы удаляете ее строку из таблицы EMPLOYEE. Co временем ее место займет другой работник. А сейчас удаление строки с ее данными из таблицы EMPLOYEE приводит к заменам значений. Эти замены состоят в том, что в таблице ORDERS во всех строках с заказами, оформленными этим представителем, столбцу Salesperson присваивается неопределенное значение. Есть и другой способ предохранить базу от несогласованных данных. Он состоит в том, чтобы отказаться от разрешения добавлять строки в дочернюю таблицу, пока в родительской таблице не появится соответствующая им строка. Если вы откажетесь разрешать добавление строк в дочерней таблице, пока не будет нужной строки в родительской таблице, то таким образом предотвратите появление "строк-сирот" в дочерней таблице. Это позволит легко поддерживать согласованность таблиц. Еще одна возможность состоит в том, чтобы запретить изменять первичный ключ таблицы. В этом случае можно не беспокоиться об обновлении внешних ключей в других таблицах, которые зависят от этого первичного ключа. Области возможных трудностей Покушения на целостность данных приходится ждать с самых разных сторон. Некоторые из этих неприятностей возникают только в многотабличных базах, в то время как другие могут произойти даже в базах, в которых имеется только одна таблица. Необходимо уметь распознавать эти угрозы и сводить вероятность их появления к минимуму. Ввод неправильных данных В документах или файлах с исходной информацией, которыми вы пользуетесь для заполнения своей базы, могут быть неправильные данные. Они могут быть неправильным вариантом правильных данных или быть совсем не теми данными, которые вам нужны. Проверки с помощью диапазонов допустимых значений показывают, имеют ли данные доменную целостность. Хотя такие проверки и помогают преодолеть некоторые трудности, но, откровенно говоря, далеко не все. Если в полях имеются неправильные значения, не выходящие из допустимого диапазона, то в результате проверок ничего подозрительного обнаружено не будет. Ошибка оператора Ваши исходные данные могут быть правильными, но оператор при вводе поймет их неправильно. Ошибка такого рода может привести к тем же трудностям, что и ввод неправильных данных. И некоторые из средств для их преодоления такие же. Проверки с помощью диапазонов допустимых значений в этом случае полезны, но не являются панацеей. Есть еще одно решение. Оно состоит в том, чтобы все вводимые данные независимо проверялись еще и вторым оператором. Такой подход обходится дорого, потому что независимая проверка удваивает количество работников и затраченное на работу время. Хотя в некоторых случаях, когда целостность данных очень важна, дополнительные усилия и затраты себя оправдывают. Механическое повреждение При механическом повреждении носителя данных, на котором была открыта таблица базы данных, данные этой таблицы могут быть испорчены. Главная защита против подобной неприятности — резервное копирование. Злой умысел Не следует исключать возможности умышленной порчи данных. Ваша первая линия обороны — это запрещение доступа к базе данных потенциально опасным пользователям, а также ограничение доступа всем остальным только тем, что им нужно. Вторая линия —- хранение резервных копий данных в безопасном месте. Периодически проверяйте, насколько защищена ваша база данных. Избыточность данных В тех базах, которые организованы иерархически, избыточность данных — это существенная проблема. Впрочем, она имеет место и в реляционных базах. Такая избыточность не только означает ненужный расход места на диске и замедление обработки, но также может привести и к серьезному повреждению данных. Если вы храните один и тот же элемент данных в двух разных таблицах базы данных, то одна его копия может быть изменена, а другая, что находится во второй таблице, может остаться прежней. Такая ситуация приводит к расхождениям, и может случиться так, что вы не сможете сказать, какой из двух вариантов является правильным. Желательно сводить избыточность данных к минимуму. Определенная избыточность необходима, чтобы первичный ключ одной таблицы служил внешним ключом другой. Однако любой другой избыточности старайтесь избегать. Самый распространенный метод уменьшения избыточности в проекте называется нормализацией. Он состоит в том, что одна таблица из базы данных разбивается на две или несколько простых. Правда, после того как вы уберете большую часть избыточности из проекта базы данных, то, возможно, обнаружите, что ее производительность стала никуда не годной. Операторы очень часто используют избыточность специально для того, чтобы ускорить обработку данных. В предыдущем примере в таблице ORDERS для определения источника каждого заказа имеется только название фирмы-клиента. При оформлении заказа, чтобы получить адрес этой фирмы, вам необходимо соединить таблицу ORDERS с таблицей CLIENT. Если в результате такого соединения таблиц программа, которая распечатывает заказы, будет работать очень медленно, то вам, возможно, захочется выполнять избыточное хранение адреса фирмы-клиента, т.е. еще и в таблице ORDERS, а не только в CLIENT. Преимущество такой избыточности состоит в более быстрой распечатке заказов, но это происходит за счет замедления и усложнения любого обновления адреса клиента. Совет: Распространенная среди пользователей практика состоит в том, чтобы вначале проектировать базу данных с малой избыточностью и с высокой степенью нормализации, а затем, обнаружив, что важные приложения работают медленно, выборочно добавлять избыточность и уменьшать нормализацию. Главное слово здесь — это "выборочно". Добавляемая избыточность предназначена для определенной цели, и так как она представляет определенный риск, предпринимайте соответствующие меры к тому, чтобы избыточность вызывала проблем не больше, чем решала. Превышение технических возможностей базы данных Система базы данных может работать безукоризненно годами, а затем периодически начать выдавать ошибки, которые постепенно становятся все более серьезными. Это означает наступление одного из пределов емкости системы. Существуют пределы количества строк таблицы, а также предел столбцов, ограничений и других характеристик. Поэтому старайтесь контролировать соответствие текущего размера и содержимого вашей базы данных техническим характеристикам вашей СУБД. Если вы обнаружили, что предел какого-либо свойства уже близок, займитесь улучшением возможностей системы. Или заархивируйте старые данные, к которым вы больше не обращаетесь, а затем удалите их из базы данных. Ограничения В этой главе об ограничениях говорилось как о механизме, благодаря которому в табличный столбец могут быть введены только данные из домена этого столбца. Ограничение (constraint) — это правило, за исполнением которого следит СУБД. После определения базы данных можно создавать определения таблиц с заданными в этих определениях ограничениями (такими, например, как NOT NULL). Благодаря СУБД вы никогда не сможете успешно выполнить никакой транзакции, если она нарушает какое-либо ограничение. Помни: В вашем распоряжении имеются ограничения трех разных видов.
Ограничения столбцов Пример ограничения столбца показан в следующем операторе языка определения данных DDL:
В этом операторе ограничение NOT NULL, примененное к столбцу ClientName, указывает на то, что этот столбец не может принимать неопределенное значение. Другое ограничение, которое можно применять к столбцу, — это UNIQUE. Оно указывает на то, что каждое значение, находящееся в столбце, должно быть уникальным. Ограничение CHECK (проверка) особенно полезно тем, что может принимать в качестве аргумента любое корректное выражение. Рассмотрим следующий пример:
В VetLab стандартная плата за проведение анализа всегда должна быть больше или равна нулю. Кроме того, ни один из стандартных анализов не стоит больше 200 долларов. Благодаря ограничению CHECK в столбец StandardCharge не попадет никакое значение, находящееся вне диапазона 0 <= STANDARD_CHARGE <= 200. А вот еще способ установить то же самое ограничение: CHECK (StandardCharge BETWEEN 0.0 AND 2 00.0) Ограничения таблиц Ограничение PRIMARY KEY указывает на то, что столбец, к которому оно применено, является первичным ключом. Таким образом, это ограничение относится ко всей таблице и эквивалентно комбинации двух ограничений столбца: NOT NULL и UNIQUE. Это ограничение, как показано в следующем примере, можно задавать в операторе CREATE:
Утверждения Утверждение (assertion) задает ограничение для более чем одной таблицы. В следующем примере для создания утверждения применяется условие поиска, составленное для столбцов из двух таблиц
Благодаря этому утверждению дата анализа не будет предшествовать дате заказа теста. Нормализация базы данных Среди способов организации данных есть такие, которые лучше всех остальных, а есть такие, которые более логичны, некоторые — проще. Кроме того, имеются и такие, которые, как только начинается использование базы данных, лучше других предотвращают несоответствие данных. Если правильно не организовать структуру базы данных, то эта база станет жертвой множества разных неприятностей (которые называются аномалиями изменения). Чтобы их предотвратить, можно нормализовать структуру базы данных. Нормализация обычно влечет за собой разделение в базе данных одной таблицы на две или несколько простых. Аномалии изменения так называются потому, что проявляются в таблице базы данных при добавлении в нее, изменении в ней или удалении из этой таблицы данных. Иллюстрацией того, каким образом могут проявляться аномалии изменения, является таблица, приведенная на рис. 5.2.
Рис. 5.2. Эта таблица SALES ведет к аномалиям изменения Ваша компания продает моющие средства для дома и предметы личной гигиены, и за один и тот же товар все покупатели платят одинаково. Все данные содержатся в таблице SALES — например, о продажах стирального порошка, зубной пасты и отбеливателя. Теперь предположим, что покупатель 1001 уехал и больше ничего у вас не приобретает. И так как он не собирается больше ничего приобретать, то вам не интересно, что же он приобретал раньше. Поэтому вы хотите удалить его строку из таблицы. Однако если вы это сделаете, то не только потеряете данные о том, что покупатель 1001 приобретал стиральный порошок, но йотом, что стиральный порошок стоит 12 долларов. Такая ситуация называется аномалией удаления. Удаляя одни данные (о том, что покупатель 1001 приобретал стиральный порошок), вы нечаянно удалите другие (о том, что стиральный порошок стоит 12 долларов). В той же таблице можно наблюдать и аномалию вставки. Скажем, вы хотите добавить к своим товарам еще и сухой дезодорант по цене 2 доллара. Но эти данные нельзя будет поместить в таблицу SALES до тех пор, пока сухой дезодорант не потребуется какому-нибудь покупателю. Трудность с изображенной на рисунке таблицей SALES заключается в том, что эта таблица слишком универсальна. В ней есть данные и о том, что именно приобрели у вас покупатели, и о том, сколько стоят купленные товары. Эту таблицу необходимо разбить на две другие, и каждая из них будет посвящена только одной теме (рис. 5.3).
Рис. 5.3. Таблица SALES разбита на две другие На рис. 5.3 изображено, что таблица SALES разделена на две новые таблицы.
Вот теперь можно удалять из таблицы CUST_PURCH строку с данными о покупателе 1001, не теряя при этом других данных, — о том, что стиральный порошок стоит 12 долларов. Данные о ценах теперь хранятся в другой таблице, PROD_PRICE. И еще, данные о сухом дезодоранте можно заносить в таблицу PROD_PRICE, независимо от того, купил кто-то этот товар или нет. Дело в том, что информация о покупках хранится не в этой таблице, а в CUST_PURCH. Нормализацией называется процесс разделения одной таблицы на множество других, каждая из которых посвящена отдельной теме. Нормализация, которая решает одну проблему, может не оказывать никакого влияния на другие. И чтобы в конце концов получить такие таблицы, которые посвящены каждая единственной теме, может потребоваться несколько последовательных нормализации. В базе данных у каждой таблицы должна быть одна и только одна главная тема. Ведь если таблица посвящена хотя бы двум темам, то в такой таблице иногда бывает трудно что-то понять. Таблицы можно классифицировать по видам тех аномалий изменения, которым эти таблицы подвержены. В своей статье, выпущенной в 1970 году (первой, где была описана реляционная модель), доктор И.Ф. Кодд (E.F. Codd) диагностирует три источника аномалий изменения и для "лечения" от этих аномалий выписывает три "лекарства". Это первая, вторая и третья нормальные формы (1НФ, 2НФ, ЗНФ). В последующие годы И.Ф. Кодд и другие специалисты открыли как другие виды аномалий, так и средства против них — новые нормальные формы. Нормальная форма Бойса-Кодда (НФБК) (Boyce-Codd normal form, BCNF), четвертая нормальная форма (4НФ) и пятая нормальная форма (5НФ) — каждая из них обеспечивала еще более высокую защиту от аномалий изменения, чем их предшественницы. В 1981 году появилась статья, написанная Р. Фейджином (R. Fegin), где описана доменно-ключевая нормальная форма (ДКНФ) (domain/key normal form, DKNF). Эта последняя нормальная форма гарантирует отсутствие аномалий изменения. Нормальные формы являются вложенными в том смысле, что таблица, находящаяся в 2НФ, автоматически находится и в ШФ. Аналогично, таблица, которая находится в ЗНФ, находится ив 2НФ и т.д. Для большинства приложений приведения базы данных в ЗНФ вполне достаточно, чтобы обеспечить в этой базе высокую степень целостности. Впрочем, чтобы была абсолютная уверенность в целостности базы данных, необходимо привести ее в ДКНФ. После проведения максимально возможной нормализации своей базы данных вам для увеличения ее производительности, вероятно, захочется выполнить выборочную денормализацию. В таком случае надо полностью отдавать себе отчет, с какими аномалиями вы, возможно, столкнетесь. Первая нормальная форма Чтобы быть в первой нормальной форме (1НФ), таблица должна обладать такими качествами.
Таблица (отношение), находящаяся в первой нормальной форме, хотя и имеет "иммунитет" к некоторым видам аномалий изменения, но все равно подвержена остальным. Первой нормальной форме соответствует таблица SALES (см. рис. 5.2), но, как уже говорилось, эта таблица подвержена аномалиям удаления и вставки. Так что эта нормальная форма может быть полезной в одних приложениях и ненадежной в других. Вторая нормальная форма Чтобы оценить вторую нормальную форму, необходимо понимать, что такое функциональная зависимость. Функциональная зависимость — это связь между атрибутами. Один атрибут функционально зависит от другого, если значение второго атрибута определяет значение первого. Значение первого атрибута можно определить, зная значение второго. Предположим, например, что у таблицы имеются атрибуты (столбцы): StandardCharge (стандартная плата), NumberOfTests (число анализов) и TotalCharge (общая плата), которые связаны следующей формулой: TotalCharge = StandardCharge * NumberOfTests В таком случае столбец TotalCharge функционально зависим от двух других: Standard-Charge и NumberOfTests. Если известны значения StandardCharge и NumberOfTests, то можно определить значение TotalCharge. Каждая таблица в первой нормальной форме должна иметь уникальный первичный ключ. Этот ключ может состоять из одного или множества столбцов. Ключ, состоящий из множества столбцов, называется составным. Чтобы таблица была во второй нормальной форме (2НФ), все ее неключевые атрибуты (столбцы) должны зависеть от всего ключа. Таким образом, каждое отношение в 1НФ, которое имеет ключ, состоящий из одного атрибута, автоматически находится во второй нормальной форме. Если у отношения имеется составной ключ, то все неключевые атрибуты должны зависеть от всех компонентов ключа. Пусть у вас есть таблица с неключевыми атрибутами, которые не выполняют это условие. Тогда вам, возможно, захочется разбить таблицу на не менее чем две новые, чтобы в каждой из них все неключевые атрибуты зависели от всех компонентов первичного ключа. Звучит достаточно запутанно? Тогда для ясности рассмотрим пример. Пусть имеется таблица SALES_TRACK (данные о продажах), аналогичная таблице SALES (продажи) (см. рис. 5.2). Правда, вместо того чтобы записывать для каждого покупателя только одну покупку, вы вводите для него строку каждый раз, когда он впервые покупает какой-либо вид товара. Кроме того, первые покупатели товара (те, у кого значения столбца CustomerlD лежат в диапазоне 1001-1009) получают скидку. Некоторые строки этой таблицы приведены на рис. 5.4.
Рис. 5.4. В таблице SALESJRACK составной ключ состоит из столбцов CustomerlD и Product На рис. 5.4 столбец CustomerlD однозначно строку не определяет. В двух строках его значения равны 1001. Еще в двух равны 1010. Однако строку однозначно определяет комбинация столбцов CustomerlD и Product. Вместе эти столбцы и являются составным ключом. Если бы не то условие, что одни покупатели имеют скидку, а другие нет, то таблица не была бы во второй нормальной форме, потому что столбец Price (цена), являющийся неключевым атрибутом, зависел бы только от столбца Product (товар). Но так как часть покупателей имеет скидку, то PRICE зависит и от CustomerlD, и от Product, так что таблица все же находится во второй нормальной форме. Третья нормальная форма Все-таки есть аномалии изменения, против которых таблицы во второй нормальной форме беззащитны. Эти аномалии связаны с транзитивными зависимостями. Помни: Транзитивная зависимость имеет место тогда, когда один атрибут зависит от второго, а второй, в свою очередь, от третьего. Удаления в таблице, имеющей такие зависимости, могут вызвать ненужную потерю информации. Отношение в третьей нормальной форме — это отношение во второй нормальной форме, не имеющее транзитивных зависимостей. Снова посмотрим на таблицу SALES (продажи) (см. рис. 5.2), которая, как вам известно, находится в первой нормальной форме. Пока для каждого значения CustomerlD (идентификатор покупателя) можно вводить только одну строку, то имеется первичный ключ, состоящий из одного атрибута, поэтому таблица находится во второй нормальной форме. Однако таблица все равно подвержена аномалиям. А что если покупателю 1010, к примеру, не повезет с отбеливателем и он вернет свою покупку, получив назад деньги? Вы собираетесь удалить из таблицы третью строку, в которой записаны данные о том, что покупатель 1010 приобрел отбеливатель. Но тут возникает проблема. Если строка будет удалена, то также будут удалены данные о том, что цена отбеливателя составляет 4 доллара. Такая ситуация является примером транзитивной зависимости. Атрибут Price (цена) зависит от атрибута Product (товар), который, в свою очередь, зависит от первичного ключа CustomerlD. Проблема транзитивной зависимости решается с помощью разделения таблицы SALES на две. Две таблицы, CUST_PURCH (покупки) и PROD_PRICE (цена товара), составляют базу данных, находящуюся в третьей нормальной форме (см. рис. 5.3). Доменно-ключевая нормальная форма (ДКНФ) После того как база данных оказалась в третьей нормальной форме, большинство шансов на возникновение аномалий изменения было сведено на нет. Впрочем, большинство, но не все. Для исправления этих оставшихся неполадок как раз предназначены нормальные формы, находящиеся внутри третьей. Примерами таких форм являются нормальная форма Бойса-Кодда (НФБК), четвертая нормальная форма (4НФ) и пятая нормальная форма (5НФ). Каждая форма сводит на нет угрозу какой-либо аномалии изменения, но не дает гарантии защиты от всех таких аномалий. Такую гарантию дает только доменно-ключевая нормальная форма (ДКНФ). Помни: Отношение находится в доменно-ключевой нормальной форме (ДКНФ), если каждое ограничение в этом отношении является логическим следствием определения ключей и доменов. Ограничением в этом определении называется любое правило, которое можно проверить. Ключ — это уникальный идентификатор табличной строки, а домен — набор разрешенных значений атрибута. Снова посмотрим на базу данных (см. рис. 5.2), которая находится в 1НФ. Это необходимо, чтобы увидеть, каким образом привести эту базу в ДКНФ. Таблица: SALES(CustomerlD,Product,Price) Ключ: CustomerID Ограничения: CustomerlD определяет Product PRODUCT определяет Price CustomerlD должен быть целым числом больше 1000 Как заставить работать ограничение 3 (атрибут CustomerlD должен быть целым числом больше 1000)? Можно всего лишь так определить домен CustomerlD, чтобы в него входило это ограничение. Таким образом, ограничение становится логическим следствием домена столбца CustomerlD. Product и зависит от CustomerlD, a CustomerlD — это ключ, так что трудностей с ограничением 1 не будет, поскольку оно является логическим следствием определения ключа. Однако трудность есть с ограничением 2: Price зависит от (является логическим следствием) Product, a Product не является ключом. Справиться с трудностью можно, разделив таблицу SALES на две. В одной из них в качестве ключа используется CustomerlD, а в другой — Product. Такая схема приведена на рис. 5.3. База данных на этом рисунке находится не только в ЗНФ, но и в ДКНФ. Помни: Проектируйте базы данных так, чтобы они по возможности были в ДКНФ. В таком случае ключевые и доменные ограничения определяют все требуемые ограничения, и аномалии изменений исключены. А если структура базы данных спроектирована так, чтобы ее нельзя было привести в ДКНФ, то ограничения необходимо встроить в прикладную программу, которая использует базу данных. Сама база данных не дает гарантии, что ограничения будут соблюдаться. Ненормальная форма Ненормальность иногда полезна. Возможно, вы увлеклись нормализацией, и вас занесло слишком далеко. Ведь базу данных можно разбить на такое количество таблиц, что вся она станет громоздкой и неэффективной. Ее работа может застопориться. Так что часто оптимальная структура должна быть в какой-то степени денормализованной. На самом деле базы данных, используемые в практической деятельности, никогда не нормализованы до уровня ДКНФ. Впрочем, чтобы исключить возможность повреждения данных, происходящего из-за аномалий изменений, максимально нормализуйте проектируемую вами базу данных. После этого мосты еще не сожжены. Если производительность вас не удовлетворяет, проверьте свой проект — можно ли с помощью некоторой денормализации увеличить производительность, не жертвуя при этом целостностью. Выборочно добавляя избыточность и проводя денормализацию, вы получите базу данных, которая будет и эффективной, и защищенной от аномалий. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
| На правах рекламы: |