Схема данных. Схема данных в Access

Определение связей между таблицами в базе данных Access

Схема данных

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

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

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

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

Типы связей

Тип отношения в создаваемой Microsoft Access связи зависит от способа определения связываемых полей.

Отношение «один-ко-многим»

Отношение «один-ко-многим» является наиболее часто используемым типом связи между таблицами. В отношении «один-ко-многим» каждой записи в таблице A могут соответствовать несколько записей в таблице B, но запись в таблице B не может иметь более одной соответствующей ей записи в таблице A.

Отношение «один-к-одному»

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

Отношение «многие-ко-многим»

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

Определение связей между таблицами

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

Предположим, что требуется установить связь между таблицами "Кафедра" и "Преподаватель" через поле ККАФ (код кафедры). В таблице "Кафедра" это поле является уникальным ключом, а в таблице "Преподаватель" — внешним ключом. Если схема данных создается заново, то при нажатии на кнопку "Схема данных" поверх окна схемы данных появится окно "Добавление таблицы" . В этом окне следует выделить требуемые таблицы и нажать "Добавить" .

В результате в окно схемы данных будут добавлены графические образы двух таблиц:

Необходимо перетащить мышью поле ККАФ таблица "Кафедра" на поле ККАФ таблицы "Преподаватель" . В открывшемся окне "Изменение связей" следует установить флажок "Обеспечение целостности данных" . В этом случае Access будет выдавать предупреждающие сообщения о неправильном вводе данных, если, например, в поле ККАФ подчиненной таблицы "Преподаватель" будет введено значение, отсутствующее в поле ККАФ базовой таблицы "Кафедра" .

Обратите внимание, что Access автоматически определил тип связи как "один-ко-многим".

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

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

Для установления связей по составному ключу необходимо в окне "Изменение связей" в полях "Таблица/Запрос" и "Связанная таблица/запрос" вручную выбрать из списков пары связываемых полей. На рисунке показан пример связи по составному ключу.

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

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

1. Что представляет собой схема данных БД?
2. Каково назначение схемы данных?
3. Как открыть схему данных в Access?
4. Как установить связь между таблицами?
5. Между какими полями таблиц устанавливается связь?
6. Каково обязательное условие при установлении связи?
7. Перечислите типы связей между таблицами. Охарактеризуйте их.
8. Как определить связи между таблицами?
9. От чего зависит выбор отношения в создаваемой Microsoft Access связи?
10. В каком случае создается отношение "один-ко-многим"? "Один-к-одному"? "Многие-ко-многим"?
11. В каком случае создается неопределенное отношение?
12. К каким последствиям приводит создание неопределенных отношений?

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

Постоянные данные в среде базы данных включают в себя схему и базу данных. Система управления базами данных (СУБД) использует определения данных в схеме для обеспечения доступа и управления доступом к данным в базе данных .

Схема как структура базы данных

Схема базы данных (от англ. Database schema ) - её структура, описанная на формальном языке, поддерживаемом СУБД. В реляционных базах данных схема определяет таблицы , поля в каждой таблице (обычно с указанием их названия, типа, обязательности), и ограничения целостности (первичный , потенциальные и внешние ключи и другие ограничения).

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

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

Видео по теме

Схема как объект базы данных

Есть и другое понятие схемы в теории баз данных.

В Oracle схема привязывается только к одному пользователю (USER) и является логическим набором объектов базы данных. Схема создаётся при создании пользователем первого объекта, и все последующие объекты, созданные этим пользователем, становятся частью этой схемы.

Схема может включать другие объекты, принадлежащие этому пользователю:

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

Существуют и подобъекты схемы, такие как:

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

Существуют объекты, независимые от схемы:

• каталоги,
• профили,
• роли,
• сегменты,
• табличные области,
• пользователи.

Уровни схемы базы данных

• Концептуальная схема - карта концепций и их связей

раскрытии одного уровня иерархии рядом с записью главной таблицы отображаются связанные записи подчиненной. Для записи подчиненной таблицы также могут быть открыты связанные записи и т. д. Например, для таблиц ПОКУПАТЕЛЬ, ДОГОВОР (рис. 1.2), связанных отношением 1: М, для каждой записи таблицы ПОКУПАТЕЛЬ могут быть отображены и отредактированы связанные записи в таблице ДОГОВОР.

Схема данных

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

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

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

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

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

Объекты Access

База данных Access включает следующие сохраняемые в одном accdb-файле объекты:

 таблицы ,запросы ,схемы данных , непосредственно имеющие отношение к базе данных;

 формы ,отчеты ,макросы имодули , называемые объектами приложения.

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

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

рументов и формы. Для создания программного кода используются модули на языке VBA и макросы.

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

Объекты представлены в области навигации окна базы данных Access. Все операции по работе с объектами собственно базы данных и приложений начинаются в этом окне.

Таблицы (Tables) создаются пользователем для хранения данных об одной сущности - одном информационном объекте модели данных предметной области. Таблица состоит из полей (столбцов) и записей (строк). Каждое поле содержит одну характеристику информационного объекта предметной области. В записи собраны сведения об одном экземпляре информационного объекта.

База данных Access может включать до 32 768 объектов (в том числе формы, отчеты и т. д.). Одновременно может открываться до 2048 таблиц.

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

Схема данных (Relationships) определяет, с помощью каких полей таблицы связываются между собой, как будет выполняться объединение данных этих таблиц, нужно ли проверять связную целостность при добавлении и удалении записей, изменении ключей таблиц. Схемы данных в области навигации в окне базы данных отображаются только в проектах Access, работающих с базами данных сервера. Для отображения схемы данных в базах данных Access используется командаСхема данных (Relationships), размещенная на вкладке лентыРабота с базами дан-

ных (Database Tools) в группеОтношения (Relationships).

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

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

Макросы (Macros) являются программами, состоящими из последовательности макрокоманд, которая выполняется по вызову или при наступлении некоторого события в объекте приложения или его элементе управления. Макросы позволяют автоматизировать некоторые действия в приложении пользователя. Создание макросов осуществляется в диалоговом режиме путем выбора нужных макрокоманд и задания параметров, используемых ими при выполнении. В Access 2010 обновлен конструктор макросов. Его новые возможности упрощают создание, редактирование макросов, позволяют сокращать количество ошибок кода и более эффективно создавать надежные приложения. В Access 2010 появилась новая возможность - макросы данных, позволяющие изменять данные на основе событий в исходных таблицах. Макросы данных используются для добавления логики к данным и сосредоточения ее в исходных таблицах. В Web-приложениях Access, базирующихся на базах данных, опубликованных в SharePoint, для программирования необходимо использовать только макросы, так как код VBA несовместим со средствами Webпубликации.

Модули (Modules) содержат процедуры на языке Visual Basic for Applications.

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

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

Сводные таблицы и сводные диаграммы

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

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

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

по всем или некоторым месяцам, кварталам, годам

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

Разделение пользователей и схем

Схема - это коллекция объектов базы данных, имеющая одного владельца и формирующая одно пространство имен. (Две таблицы в одной и той же схеме не могут иметь одно и то же имя.) Компонент Database Engine поддерживает именованные схемы с использованием понятия принципала (principal). Как уже упоминалось, принципалом может быть индивидуальный принципал и групповой принципал.

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

Отделение пользователей базы данных от схем дает значительные преимущества, такие как:

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

несколько индивидуальных принципалов могут владеть одной схемой посредством членства в ролях или группах Windows;

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

Каждая база данных имеет схему по умолчанию, которая используется для определения имен объектов, ссылки на которые делаются без указания их полных уточненных имен. В схеме по умолчанию указывается первая схема, в которой сервер базы данных будет выполнять поиск для разрешения имен объектов. Для настройки и изменения схемы по умолчанию применяется параметр DEFAULT_SCHEMA инструкции CREATE USER или ALTER USER. Если схема по умолчанию DEFAULT_SCHEMA не определена, в качестве схемы по умолчанию пользователю базы данных назначается схема dbo .

Инструкция CREATE SCHEMA

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

USE SampleDb; GO CREATE SCHEMA poco AUTHORIZATION Vasya GO CREATE TABLE Product (Number CHAR(10) NOT NULL UNIQUE, Name CHAR(20) NULL, Price MONEY NULL); GO CREATE VIEW view_Product AS SELECT Number, Name FROM Product; GO GRANT SELECT TO Alex; DENY UPDATE TO Alex;

В этом примере создается схема poco, содержащая таблицу Product и представление view_Product. Пользователь базы данных Vasya является принципалом уровня базы данных, а также владельцем схемы. (Владелец схемы указывается посредством параметра AUTHORIZATION . Принципал может быть владельцем других схем и не может использовать текущую схему в качестве схемы по умолчанию.)

Две другие инструкции, применяемые для работы с разрешениями для объектов базы данных, GRANT и DENY, подробно рассматриваются позже. В этом примере инструкция GRANT предоставляет инструкции SELECT разрешения для всех создаваемых в схеме объектов, тогда как инструкция DENY запрещает инструкции UPDATE разрешения для всех объектов схемы.

С помощью инструкции CREATE SCHEMA можно создать схему, сформировать содержащиеся в этой схеме таблицы и представления, а также предоставить, запретить или удалить разрешения на защищаемый объект. Как упоминалось ранее, защищаемые объекты - это ресурсы, доступ к которым регулируется системой авторизации SQL Server. Существует три основные области защищаемых объектов: сервер, база данных и схема, которые содержат другие защищаемые объекты, такие как регистрационные имена, пользователи базы данных, таблицы и хранимые процедуры.

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

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

Принципалом уровня базы данных может быть пользователь базы данных, роль или роль приложения. (Роли и роли приложения рассматриваются в одной из следующих статей.) Принципал, указанный в предложении AUTHORIZATION инструкции CREATE SCHEMA, является владельцем всех объектов, созданных в этой схеме. Владение содержащихся в схеме объектов можно передавать любому принципалу уровня базы данных посредством инструкции ALTER AUTHORIZATION .

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

Инструкция ALTER SCHEMA

Инструкция ALTER SCHEMA перемещает объекты между разными схемами одной и той же базы данных. Инструкция ALTER SCHEMA имеет следующий синтаксис.

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

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

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

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

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

Возможности

Теперь, когда вы захотите ускорить работу с окном "Схема данных", Узнайте, как