Учебное пособие Допущено Учебно-методическим объедине-нием по образованию в области прикладной информатики в качестве учебного пособия для студентов высших учебных заведений



страница2/9
Дата22.04.2016
Размер1.28 Mb.
1   2   3   4   5   6   7   8   9

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


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

  • обеспечение быстрого доступа к данным в таблицах;

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

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

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

Фактически также необходимо выяснить, какие домены следует использовать для атрибутов и какая функциональная зависимость между ними существует.

Домен – общая совокупность значений, из которой берется реальное значение атрибутов.

Функциональная зависимость является связью типа многие-к-одному между множествами атрибутов данного отношения.

Пусть R–это отношение, а X и Y – произвольные подмножества множества атрибутов отношения R. Тогда У функционально зависимо от Х тогда и только тогда, когда каждое значение множества Х отношения R связано в точности с одним значением множества У отношения R.

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

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

Особенность проектирования базы данных состоит в том, что речь идет о логическом, а не о физическом макете. Это вовсе не значит, что физический макет не имеет большого значения, а наоборот. Однако:



    • физический макет может рассматриваться как отдельная сопутствующая часть;

    • физический макет является специфическим для каждой СУБД. Логический макет, наоборот, совершенно независим от СУБД.

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

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

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

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

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

Важным моментом проектирования базы данных является нормализация.

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

Контрольные вопросы


  1. Дайте определение понятию нормализация

  2. Как осуществляется поддержание целостности данных на этапе проектирования?


1.9 Нормализация таблиц и ее необходимость

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



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

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

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


  • первая нормальная форма (First Normal Form — 1NF);

  • вторая нормальная форма (Second Normal Form — 2NF);

  • третья нормальная форма (Third Normal Form — 3NF);

  • нормальная форма Бойса—Кодда (B—Codd Normal Form — BCNF);

  • четвертая нормальная форма (Fourth Normal Form — 4NF);

  • пятая нормальная форма, или нормальная форма проекции-соединения 5NF, или PJ/NF).

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

При описании нормальных форм используются несколько понятий:



  • функциональной зависимостью между полями А и В называется зависимость, при которой каждому значению А в любой момент времени соответствует единственное значение В из всех возможных;

  • полной функциональной зависимостью между составным полем А и полем В называется зависимость, при которой поле В зависит функционально от поля А и не зависит функционально от любого подмножества поля А;

  • транзитивная функциональная зависимость между полями А и С наблюдается в том случае, если поле В функционально зависит от поля А и поле С функционально зависит от В. В то же время, обратная зависимость отсутствует;

  • несколько полей взаимно независимы, если ни одно из них не является функционально зависимым от другого поля;

  • неключевым полем таблицы называется каждое поле, не входящее в состав первичного ключа.

Первая нормальная форма


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

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

Таблица, находящаяся в первой нормальной форме, обладает рядом недостатков.

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

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

Чтобы избежать проблем, необходимо привести таблицу ко второй или третьей нормальной форме.


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


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

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

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

Приведение таблицы ко второй нормальной форме позволяет избежать повторения одних и тех же данных, появившегося после приведения таблицы к первой нормальной форме.



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

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

Таблица находится в третьей нормальной форме, если она находится во второй нормальной форме и каждое не ключевое поле нетранзитивно зависит от первичного ключа. Под «нетранзитивной зависимостью» подразумевается отсутствие какой-либо взаимной зависимости.

Нормальная форма Бойса—Кодда


Таблица находится в нормальной форме Бойса—Кодда только в том случае, если любая функциональная зависимость между ее полями сводится к полнофункциональной зависимости от возможного ключа.

Четвертая нормальная форма


Для определения четвертой нормальной формы необходимо ввести дополнительные определения.

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

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


Пятая нормальная форма


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


Контрольные вопросы

  1. Когда таблица считается нормализованной?

  2. Назовите способы приведения таблицы ко 2НФ.


1.10 Краткая характеристика моделей БД

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

На практике используются три основных модели:

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

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

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

Контрольные вопросы

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



2. В чем основное отличие иерархической модели от сетевой модели?

1.11 Классификация баз данных по технологии обработки

За всю историю развития баз данных было разработано множество способов хранения, структуризации и обработки информации. По технологии обработки базы данных подразделяются следующим образом:



Распределенная база данных. Этот способ обработки требует использования нескольких серверов, на которых может храниться пересекающаяся или даже дублирующаяся информация. Для работы с такой базой данных используется система управления распределенными базами данных (СУРБД).

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

Файл-сервер. Эта архитектура предполагает использование выделенного компьютера в качестве сервера файлов. На этом сервере хранятся файлы базы данных, которые по запросу пользователей копируются на их локальные компьютеры. Там и проводится вся основная обработка данных. После того как пользователи выполнят необходимые изменения данных, они копируют файлы обратно на файл-сервер, где другие пользователи, в свою очередь, могут снова их использовать. Кроме того, каждый пользователь может создавать на локальном компьютере свои собственные базы данных, используемые им монопольно. При использовании архитектуры «файл-сервер» производительность системы резко падает с ростом числа пользователей.

Клиент-сервер. При использовании этой архитектуры выделенный компьютер используется не только в качестве хранилища файлов, но и для выполнения основного объема действий по обработке информации. Пользователь (клиент) с рабочей станции отправляет список операций обработки данных (запрос), которые необходимо выполнить, центральному компьютеру (серверу). Сервер выполняет необходимые вычисления и выборку данных и отправляет готовый результат клиенту. Для описания запросов часто используется структурированный язык запросов — SQL (Structured Query Language), специально разработанный для этих целей.

Контрольные вопросы

  1. В чем различие технологий обработки файл-сервер и клиент-сервер?

  2. Может ли пользователь локального компьютера иметь свою локальную базу данных?

1.12 Особенности архитектуры клиент-сервер в распределенных средах


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

Для соединения клиентов и сервера требуется сетевое программное обеспечение. В системах клиент/сервер приложение, работающее на сервере, отвечает за создание и поддержку объектов баз данных в виде таблиц и индексов. Сервер поддерживает целостность ссылок и безопасность, а также обеспечивает возможность восстановления данных в случае широкого спектра отказов.

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

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

Система клиент/сервер – это не только база данных, которая находится в одном месте (ПК), но и доступна с нескольких рабочих станций.

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

Типичные задачи клиенткой стороны:

вывод информации для пользователя;

манипулирование информацией в базе данных и на экране пользователя;

отображение отчетов;

обеспечение выполнения операций, прерванных пользователем;

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

Типичные задачи серверной стороны.

любая операция, которая запускается на сервере, никогда не должна требовать для своего завершения участия пользователя;

ядро базы данных несет ответственность за хранение, обновление и извлечение информации;

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



Контрольные вопросы

  1. Назовите типичные задачи клиентской стороны.

  2. Где поддерживается целостность ссылок и безопасность при архитектуре клиент-сервер?


1.13 Введение в реляционные базы данных
Развитие реляционных баз данных началось в конце 60-х годов, когда появились первые работы, в которых обсуждались возможности использования при проектировании баз данных привычных и естественных способов представления данных — так называемых табличных даталогических моделей.

Основоположником теории считается сотрудник фирмы IBM доктор Эдгар Кодд.

Теория реляционных баз данных имеет под собой мощную математическую основу. Разработанная Э. Коддом теоретическая база стала основой для разработки теории проектирования баз данных.

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

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

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

В один домен могут входить значения из нескольких колонок, объединенных, помимо одинакового типа данных, еще и логически.

Каждый элемент данных в отношении может быть определен с указанием его адреса в формате A[i,j], где А — элемент данных, i — строка отношения, j — номер атрибута отношения.

Количество атрибутов в отношении определяет его порядок.

Множество значений A[i,j] при постоянном i и всех возможных j образуют кортеж. Их количество определяет его мощность, или кардинальное число. Мощность отношения, в отличие от порядка отношения, может со временем меняться.

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

Множество атрибутов отношения являются возможным ключом этого отношения тогда и только тогда, когда удовлетворяются два независимых от времени условия:



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

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

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



1.14 Реляционные базы данных

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

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


  • каждая таблица имеет уникальное в базе данных имя и состоит из однотипных строк;

  • каждая таблица состоит из фиксированного числа колонок и значений. В одной колонке строки не может быть сохранено более одного значения;

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

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

  • полное информационное содержание базы данных представляется в виде явных значений самих данных, и такой метод представления является единственным.

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

Описание колонок, которое составляет разработчик, принято называть макетом таблицы.

Наиболее важными понятиями являются:

-отношение;

-кортеж;

-кардинальное число;

-атрибут;

-степень;

-домен;

-первичный ключ.



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

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

Рассмотрим возможные типы отношения между таблицами. Если каждой строке в первой таблице в каждый момент времени соответствует ноль или одна строка во второй таблицы и наоборот, то между таблицами установлено отношение «один-к-одному» (1 : 1).

Если каждой строке в первой таблице в каждый момент времени соответствует ноль или одна строка во второй таблицы, но строка второй таблицы связана с единственной строкой первой таблицы, то между ними установлено отношение «один-ко-многим» (1 : М).

Если каждой строке в первой таблице соответствует ноль, одна или несколько строк во второй таблице и наоборот, то между ними установлено отношение «многое-ко-многим» (М : М).

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

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


  1. Какие типы отношений между таблицами вы знаете?

  2. Может ли значение реляционной модели быть разложено на составляющие?


1.15 Первичный и внешний ключи отношения

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

Пусть R – некоторое отношение. Тогда потенциальный ключ К для R – это подмножество множества атрибутов R, обладающее следующими свойствами:

-свойством уникальности. Нет двух различных кортежей (записей) в отношении R с одинаковым значением К;

-свойством неизбыточности. Никакое из подмножеств К не обладает свойством уникальности.

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

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

Дадим определение внешнего ключа.

Пусть R2 – базовое отношение. Тогда внешний ключ, скажем FK в отношении R2 – это подмножество множества атрибутов R2, такое что:

-существует базовое отношениеR1 с потенциальным ключом CK;

-каждое значение FK в текущем значении R2, всегда совпадает со значением СК некоторого кортежа в текущем значении R1.

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

При изменении значения первичного ключа в главной таблице возможны следующие варианты поведения зависимой таблицы:

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

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

-установление (Relation). При удалении строки из главной таблицы соответствующий внешний ключ в зависимой таблице устанавливается в неопределенное значение (NULL). При изменении данных первичного ключа внешний ключ устанавливается в неопределенное значение (NULL).


Контрольные вопросы

1.Дайте понятие первичного и внешнего ключей.

2.Сколько ключей может иметь отношение?

3.Может ли данное реляционной модели быть разложено на составляющие?

4.Опишите поведение зависимой таблицы при условии каскадирования.

1.16 Реляционная система управления базами данных

Реляционная система управления базами данных – это такая система, в которой выполняется как минимум два условия:



  • данные воспринимаются пользователем как таблицы (и никак иначе);

  • в распоряжении пользователя имеются операторы, которые генерируют новые таблицы из старых, и среди которых, по крайней мере, есть операторы SELECT, PROJECT, JOIN.

Операция SELECT предназначена для извлечения определенных строк из таблицы.

Операция PROJECT предназначена для извлечения определенных столбцов из таблицы.

Операция JOIN предназначена для соединения двух таблиц на основе общих значений в общих столбцах.

Следует выделить следующие особенности:



  • во-первых, таблицы представляют абстракцию способа физического хранения данных, в которой множество деталей на уровне памяти – размещение хранимой записи, последовательность хранимых записей, кодировка хранимых данных, предмет хранимых записей, хранимые структуры доступа, такие как индексы и т.д. – скрыто от пользователя. Концептуальный и внешний уровни в реляционной системе являются реляционными, а внутренний, или физический уровень – нет. На самом деле реляционная теория ничего не может сказать о внутреннем уровне вообще; она «заботится» о том, как база данных представлена пользователю;

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

Например. Если служащий S1 работает в отделе D1, то эта информация представлена не указателем, а появлением значения D1 в строке E1 таблицы Служащих. Тем самым таблица Служащих связана с таблицей Отделов значением D1;

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

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

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

Структура – это объекты данных, объектами в основном являются таблицы. Целостность обеспечивается внешними и первичными ключами, а операторы – это SELECT, PROJECT, JOIN и т.д.

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



  • каждая строка таблицы должна включать уникальные значения одного из столбцов, называемого первичным ключом;

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

Достаточно точно можно дать следующее определение: реляционная модель – это способ рассмотрения данных, т.е. предписание для способов представления данных (посредством таблиц) и для способа работы с таким представлением (посредством операторов).

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

Базовые таблицы должны быть именованными. Большинство производных таблиц, наоборот, неименованные.

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

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

Различия между базовой таблицей и представлением часто характеризуется так:



  • базовые таблицы «реально существуют» в том смысле, что они представляют данные, которые действительно хранятся в базе данных;

  • представления, наоборот, «реально не существуют», а просто предоставляют различные способы просмотра «реальных» данных.

Стандартным языком для работы с реляционными базами данных является язык SQL. Этот язык был разработан в компании IBM Research в начале 1970-х годов, его первой крупной реализацией был реляционный прототип IBM – продукта System R. С того времени диалекты языка SQL стали Американским национальным стандартом (ANSI), международным стандартом (ISO), стандартом системы UNIX (X/OPEN), стандартом корпорации IBM, описывающим «общий интерфейс базы данных», который является частью архитектуры системных приложений IBM (System Application Architecture, сокращенно SAA), федеральным стандартом обработки информации (FIPS).

Язык SQL используется для описания реляционных операций, (т.е. операций, которые определяют и обрабатывают данные в реляционной форме).

Язык SQL - это язык, ориентированный на работу с множествами. Язык не включает ни средств управления выполнением программы (ветвлений и циклов), ни средств для создания форм или отчетов. Функции управления реализуются с помощью языков программирования. Однако в некоторые версии SQL, например в Transact - SQL, используемые в Microsoft SQL Server, добавлены два оператора и .

ANSI SQL включает набор стандартных команд, сгруппированных по шести категориям: описание данных, выполнение запросов, манипулирование данными, управление курсором, управление транзакциями, а также администрирование. В 1989 г. в исходный стандарт ANSI были добавлены инструкции для обеспечения целостности данных.

Существуют три способа реализации SQL:


  • Непосредственный вызов. Инструкции SQL передаются в систему управления базами данных (СУБД), которая создает и выводит требуемую таблицу. Примером непосредственного вызова может служить предложение SQL системы dBase IV.

  • Язык модулей. Позволяет записывать в текстовый файл инструкции SQL, которые позднее выполняются приложением. Инструкция SQL должна начинаться с ключевого слова MODULE и определять язык программирования (LANGUAGE).

  • Встроенный SQL. Это наиболее распространенный метод реализации; инструкции SQL генерируются системой управления или включаются как текст в команды языка системы.

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

Элементы Transact - SQL

В большинстве случаев используется упрощенный вариант команды SELECT имеющий следующий синтаксис:

SELECT select list

[INTO new_table]

FROM table_source

[WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_expression [ASC \ DESC] ]
В операторе SELECT указываются столбцы выбираемых данных, место хранения столбцов, критерий отбора данных и порядок сортировки, применяемый к этим данным. Более того, с помощью оператора SELECT можно сгруппировать данные и назначить критерий отбора на уровне группы. SELECT имеет следующий синтаксис:

SELECT [ ALL \ DISTINCT]

[TOP n [PERCENT] [WITH TIES ]]


  • ALL – при указании этого ключевого слова в результат запроса разрешается включение дублирующихся строк. Параметр ALL используется по умолчанию.

  • DISTINCT – это ключевое слово запрещает появление в результате дублирующихся строк.

  • TOP n [PERCENT] [WITH TIES] – использование этой конструкции предписывает серверу выбирать не все строки, а только n первых. Для этого лишь требуется указать ключевое слово ТОР п.

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

При указании ключевого слова WITH TIES в результат будут включены строки, совпадающие с последними выведенными строками при указании ТОР n [PERCENT]. Это позволяет включать в результат все одинаковые строки, не опасаясь, что, например, из 12 одинаковых строк будут выведены только 7. WITH TIES допускается применять только в случае, если строки в результате упорядочены, то есть когда в запросе используется конструкция ORDER BY.



Раздел INTO

При указании этой конструкции результат выполнения запроса будет сохранен в новой таблице. Синтаксис раздела INTO следующий:

[INTO new_table]

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



Раздел FROM

FROM {}

Ключевое слово FROM описывает таблицы или представления, связанные с изменяемой таблицей. Если в базе данных имеются таблицы или представления, ссылающиеся на изменяемую таблицу, то для обеспечения целостности данных необходимо указать эти объекты при выполнении команды UPDATE. Если выполняется, изменение данных, никак не влияющих на другие таблицы, то указание FROM не требуется. Порядок указания таблиц и представлений после ключевого слова FROM не влияют на результат работы команды UPDATE. Ссылки на исходные таблицы и представления указываются в конструкции UPDATE.

Добавление директивы WHERE

Используя тот же самый базовый оператор SELECT, можно сузить результаты запроса, добавив директиву WHERE.

В директиве WHERE в качестве критерия отбора строк можно использовать несколько столбцов.

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


Таблица 1. Операторы сравнения директивы WHERE

Описание

Оператор

=

Равно

>

Больше чем

<

Меньше чем

>=

Больше либо равно

<=

Меньше либо равно

<>

Не равно

IN

Входит в заданный список значений

between... and

Входит в интервал значений

like

Содержит значение, соответствующее заданному шаблону. Шаблон для сравнения – это строка, состоящая из одного или нескольких символов подстановки. Список символов-шаблонов можно найти в справочной системе SQL Server.

Помимо перечисленных выше операторов сравнения, значения столбцов, включаемые в директиву WHERE, можно проверять с помощью логических операторов AND и OR. При использовании оператора AND возвращаемые строки должны отвечать обоим условиям, расположенным слева и справа от оператора AND. Оператор же OR требует, чтобы было выполнено хотя бы одно из заданных условий.



Добавление директивы ORDER BY

Достаточно добавить директиву ORDER BY, чтобы отсортировать записи по любому столбцу. Для сортировки в порядке убывания используется ключевое слово DESC.

В Microsoft SQL Server предусмотрено несколько вариантов сортировки. По умолчанию используется словарный порядок, не учитывающий регистр букв (Dictionary Order, Caseinsensitive). Порядок сортировки определяется во время установки SQL Server и позже его переопределить нельзя.

Использование директивы WHERE для объединения таблиц

Не всегда будет достаточно просмотра данных только из одной таблицы. Данные в таблице могут быть связаны с данными других, таблиц базы данных. Для объединения нескольких таблиц вместе в одном операторе SELECT, можно использовать директиву WHERE:

SELECT author.au_Iname, author.au_fname, titles.title

FROM author, titleauthor, titles

WHERE authors.au_id =titleauthor.au_id AND

Titleauthor.title_id=titles.title_id

ORDER BY authors.au_Iname, authors.au_fname, titles.title

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



Использование оператора JOIN для объединения таблиц

Объединение таблиц может быть реализовано и другим способом, а именно – с помощью оператора JOIN. Этот метод объединения таблиц соответствует стандарту ANSI и имеет следующий синтаксис:

SELECT столбец 1, столбец 2, столбец З

FROM таблица 1 оператор_объединения таблица 2

ON критерий_объединения

Рассматриваемый нами оператор JOIN определяет, каким образом должны быть возвращены строки из объединяемых таблиц. Директива ON действует подобно директиве WHERE, указывая в каких полях в объединяемых таблицах должно проверяться равенство значений. Различные операторы объединения описаны в таблице 2.


Таблица 2. Операторы объединения таблиц

Оператор объединения


Описание


1

2

CROSS JOIN


Возвращает каждую строку из первой таблицы, объединенную с каждой строкой из второй таблицы. Количество строк результата равно произведению числа строк в каждой таблице.

INNER JOIN


Возвращает все строки из каждой таблицы, удовлетворяющие критерию отбора, который задан в директиве where, при условии совпадения значений в объединяемых полях, указанных в директиве ON.

LEFT [OUTER] JOIN


Возвращает все строки таблицы, которые находятся слева в объединении, удовлетворяющие критерию отбора директивы where, и только те строки таблицы, расположенные справа в объединении, в которых существует совпадение по объединяемым полям, заданным в директиве ON



1

2

RIGHT [OUTER] JOIN

Возвращает все строки таблицы, которые находятся справа в объединении, удовлетворяющие критерию отбора директивы where, и только те строки таблицы, расположенные слева в объединении, в которых существует совпадение по объединяемым полям, заданным директивой ON.

FULL [OUTER] JOIN

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

С помощью оператора объединения команду SQL из предыдущего примера можно записать следующим образом:

SELECT authors.au_Iname, authors. au_fname, titles.title FROM

(authors INNER JOIN titleauthor

ON authors.au_id = titleauthor.au_id) INNER JOIN titles

ON titleauthor.title_id = titles.title_id

ORDER BY authors.au_Iname, authors.au_fname, titles.title

Итоговые функции в операторах SQL

Описываемые в данном разделе функции возвращают итоговые значения для заданных столбцов или выражений в форме итоговых сумм, количества записей, средних значений и т.п. Итоговая функция может возвращать одно значение для всех строк, отобранных запросом. Если же в оператор SQL была добавлена директива GROUP BY, то такое итоговое значение вычисляется на каждом уровне группировки. Список итоговых функций, которые можно использовать в запросах, приведен в таблице 3.


Таблица 3. Итоговые функции

Итоговая функция

Описание

AVG

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

COUNT

Возвращает количество значений, отличных от NULL, в указанном столбце или выражении. Если в качестве выражения задан символ звездочки (например, count (*)), то результатом является число строк, отобранных запросом.

MIN

Возвращает минимальное значение в заданном столбце или выражении.

МАХ

Возвращает максимальное значение в заданном столбце или выражении.

SUM

Возвращает сумму всех значений в заданном столбце или выражении.

В следующем примере функция count применяется ко всем строкам таблицы titles без группировки:

SELECT count (title) 'наименований'

FROM titles

Результат представляет собой количество записей в таблице titles.

Использование директивы GROUP BY

Предположим, что вам нужно сгруппировать все похожие строки результата выполнения запроса по значениям одного или нескольких столбцов. Это можно сделать, задав требуемые столбцы в директиве GROUP BY. Если задано несколько столбцов, то строки сначала группируются по первому столбцу, а затем (внутри этих групп) – по второму столбцу и т.д.

В следующем примере директива GROUP BY используется вместе с итоговой функцией, чтобы показать, как эта функция применяется к значениям внутри каждой отдельной группы:

SELECT authors.au_Iname, authors.au_fname, count (titles.title) 'Названий'

FROM authors, titleauthor, titles

WHERE


authors.au_id = titleauthor. au_id AND

titleauthor.title_id = titles.title_id

GROUP BY authors.au_Iname, authors.au_fname

Использование директивы HAVING

Подобно директиве WHERE, директива HAVING используется для задания критерия отбора возвращаемых запросом данных. Различие связано с уровнем, на котором выполняется проверка критерия. В директиве WHERE критерий используется для ограничения количества строк, возвращаемых запросом. А затем директива GROUP BY формирует из этих строк группы и вычисляет заданные значения. После этого критерии директивы HAVING используется для ограничения количества групп в соответствии с данными на уровне группы.

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

SELECT a.au_Iname, a.au_fname, count (c.title) 'Названий'

FROM authors a, titleauthor b, titles с

WHERE a.au_id = b.au_id AND b.title_id = c.title_id

GROUP BY a.au_Iname, a.au_fname

HAVING count (c.title) > 1



Cоздание таблиц

Создание таблиц выполняется инструкцией:

CREATE TABLE имя таблицы

(имя_столбца тип [NULL | NOT NULL] [DEFAULT значение_по_умолчанию]

[ограничение_на_столбец]... ;

[, имя_столбца тип [NULL | NOT NULL] [DEFAULT значение_по_умолчанию]

[ограничение на столбец]...]...

[ограничение на таблицу]...)


Ограничения на таблицу используют более одного столбца.

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

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

В ряде систем имеются специальные команды (не связанные с оператором CREATE TABLE) для обработки значений по умолчанию, правил и ограничений на целостность. Первые СУБД иногда перекладывали вопросы, связанные со значениями по умолчанию и ограничениями на целостность, на приложения и не поддерживали соответствующие команды SQL.


1   2   3   4   5   6   7   8   9


База данных защищена авторским правом ©bezogr.ru 2016
обратиться к администрации

    Главная страница