Создание базы данных (установка связей между таблицами)

2.4. Microsoft Access 2007

2.4.2. Создание базы данных (таблиц и связей между ними) в Access 2007

Рассмотрим этапы создания БД «Деканат» с помощью СУБД Access 2007. Сначала составляем модель «сущность – связь» для базы данных «Деканат». Этапы проектирования модели «сущность – связь» изложены в разделе «Создание БД. Этапы проектирования».

После создания модели запускаем приложение Access 2007. Открывается окно приложение Access 2007 на странице Приступая к работе с Microsoft Access 2007. В разделе Новая пустая база данных щелкаем на пиктограмме Новая база данных. В правой части окна появится информация об имени файла и указана директория для его хранения. По умолчанию имя файла — База данных1.accdb.

Изменить имя файла и путь к директории для хранения файла БД можно в окне «Файл новой базы данных» щелкнув на пиктограмме «Поиск расположения для размещения базы данных». Установив имя файла — Деканат_2007.accdb и требуемое имя директории в окне «Файл новой базы данных», надо щелкнуть на кнопке ОК, окно закроется.

Далее необходимо щелкнуть на кнопке Создать, чтобы создать пустую базу данных. При создании новой пустой базы данных окно приложения Access 2007 открывается на контекстной вкладке «Режим таблицы». В окне отображается новая пустая таблица с именем Таблица 1 в режиме таблица, представленная на Рис. 1.

Рис. 1.

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

Рис. 2.

Откроется окно Сохранение, в котором надо указать имя Группы студентов и нажать кнопку ОК.

Рис. 3.

Откроется таблица Группы студентов в режиме Конструктор

Рис. 4.

Создаем структуру таблицы Группы студентов. В первую строку колонки «Имя поля» вводим код группы студентов (КодГруппы) и нажимаем клавишу Enter. Курсор переместится в колонку Тип данных. Access по умолчанию назначает тип данных — Счетчик. Нажимаем клавишу Enter, при этом курсор переместится в колонку Описание, при необходимости вводим описание данных.

Первой строке таблицы (поле КодГруппы) Access по умолчанию назначает поле первичного ключа. Для первичного ключа в свойствах поля устанавливается значение Индексированного поля: Да (Совпадения не допускаются). Далее заполняем вторую строку (второе поле таблицы), Имя поля — Название, Тип данных — текстовый. Третья строка: Имя поля — Курс, Тип данных — числовой и четвертая строка Имя поля — Семестр, Тип данных — числовой. При этом для имени поля «Название» в разделе свойства поля необходимо установить размер поля — 6.

Рис. 5.

Затем создаем структуры остальных трех таблиц в соответствии с характеристиками таблиц-объектов Студенты, Дисциплины, Успеваемость. Обязательно соблюдайте указанную последовательность создания структуры таблиц.

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

Структуры остальных таблиц: Студенты, Дисциплины, Успеваемость:

Рис. 6

Рис. 7

Рис. 8

После этого необходимо установить логические связи между всеми таблицами.

Далее >>> 2.4.3. Установка связей между таблицами в СУБД Access 2007

2.4. Microsoft Access 2007

2.4.4. Заполнение таблиц БД Access 2007

Tables БД Access 2007 необходимо заполнять данными в определенной последовательности. Сначала надо заполнять главные tables, а затем подчиненные. Заполнение таблиц для БД Деканат целесообразно выполнять в такой последовательности: Группы студентов, Студенты, Дисциплины и Успеваемость. В этом случае для заполнения полей вторичных ключей (КодГруппы, КодСтудентов, КодДисциплины) можно использовать раскрывающийся список данных.

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

Рассмотрим заполнение таблицы Группы студентов. В окне базы данных Деканат_2007 в области объектов выбираем таблицу Группы студентов, и выполняем на ней двойной щелчок. В окне редактирования появится структура табл. «Группы студентов» в режиме таблицы. Новая table состоит из одной пустой строки.

Заполнение производится по записям (по строкам). Поле счетчика (поле КодСтудента) заполняется автоматически. Переход к следующему полю осуществляется нажатием клавиши ТаЬ. После ввода первой записи курсор смещается на следующую запись. После заполнения table Группы студентов имеет следующий вид (рисунок 1).

Рис. 2.

Аналогичным образом заполняется table Студенты (рис.2). Необходимо отметить, что в табл. Студенты поле КодГруппы заполняется из раскрывающегося списка.

Рис. 2.

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

Из списка выбираем тип данных для поля КодГруппы «Мастер подстановок». В процессе назначения типа данных «Мастер подстановок» для поля КодГруппы табл. «Студенты» выбираем следующие данные: на первом шаге Группы студентов, а затем Название и далее щелкаем Готово. Закрываем таблицу Студенты. После этого восстанавливаем связи (отношения) в окне Схема данных. Продолжаем заполнение таблицы Студенты с использованием раскрывающегося списка для поля КодГруппы.

Далее заполняется table Дисциплины (рис. 3).

Рис. 3.

Затем заполняется table Успеваемость (рис. 4).

Рис. 4.

В таблице Успеваемость поля КодДисциплины и КодСтудента заполняются из раскрывающихся списков.

Необходимо отметить, что в базах данных Access 2007 применяются различные методы перемещения по таблице. Переходить от записи к записи можно с помощью: клавиш управления курсором; кнопок из области Запись, расположенных внизу таблицы в режиме таблицы; команды Перейти в группе Найти, расположенной на ленте. Для перемещения от поля к полю (слева направо) применяются клавиши Tab и Enter, а в обратном направлении Shift+Tab.

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

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

Далее >>> Раздел: 2.4.5. Создание запросов в базе данных Access 2007

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

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

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

Подсистемы являются группами объектов. Содержащиеся в них объекты имеют связи с объектами, которые находятся «снаружи». Чтобы посмотреть, как устроена любая из этих подсистем, например подсистема Характеристики, вы можете кликнуть на ней прямо в схеме, и увидите её устройство.

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

Как определить связи между таблицами

При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем AutoNumber. Вы можете сопоставить поле AutoNumber с полем Number, только если свойство FieldSize обоих совпадающих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство theFieldSizeproperty обоих полей имеет значение Long Integer. Даже если оба совпадающих поля являются числовыми полями, они должны иметь параметр sameFieldSizeproperty.

Как определить связи «один ко многим» или «один к одному»

Чтобы создать связь «один ко многим» или «один к одному», выполните следующие действия.

1. Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.

2. В Access 2002 и Access 2003 выполните следующие действия.

   1. Нажмите F11, чтобы переключиться в окно базы данных.
   2. В меню Инструменты выберите Связи.

   В Access 2007, Access 2010 или Access 2013 нажмите Связи в группе Показать/Скрыть на вкладке Инструменты базы данных.

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

4. Дважды щелкните названия таблиц, которые вы хотите связать, а затем закройте диалоговое окно Показать таблицу.  Чтобы создать связь между одной и той же таблицей, добавьте эту таблицу два раза.

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

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

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

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

7. Нажмите кнопку Создать, чтобы создать связь.

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

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

   Примечание

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

Как определить связь «многие ко многим»

Чтобы создать связь «многие ко многим», выполните следующие действия.

1. Создайте две таблицы, которые будут иметь связь «многие ко многим».

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

3. В связующей таблице установите первичный ключ, чтобы включить основные ключевые поля из двух других таблиц. Например, в связующей таблице «TitleAuthors» первичный ключ будет состоять из полей OrderID и ProductID.

   Примечание

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

   1. Откройте таблицу в Конструкторе.

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

   3. В Access 2002 или в Access 2003 нажмите на Первичный ключ на панели инструментов.

      В Access 2007 нажмите на Первичный ключ в группе Инструменты на вкладке Дизайн.

      Примечание

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

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

Зачем в 1С:Предприятии понадобилась ER-модель

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

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

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

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

Современные прикладные решения 1С:Предприятия содержат большое количество объектов конфигурации, 10 тысяч и более. При таком количестве объектов задача нахождения их взаимных связей с помощью имеющихся инструментов становится довольно трудоёмкой. Причём трудоёмкость растёт не только за счёт прямого увеличения времени поиска ссылок среди большого количества объектов. Она растёт и косвенно, за счёт того, что найденные связи необходимо как-то запомнить и визуализировать. И если таких связей много, встаёт вопрос выбора подходящего внешнего инструмента.

Использование ER-модели (Entity-Relationship Model) как раз решает эту проблему. ER-модель представляет любую структуру данных в виде совокупности сущностей, обладающих атрибутами. Эти сущности взаимодействуют между собой при помощи связей.

В наших терминах, в терминах 1С:Предприятия, сущность это объект конфигурации, а атрибут это реквизит объекта конфигурации в широком смысле: реквизит, измерение, ресурс и т.д. Таким образом, ER-модель базы данных 1С:Предприятия это набор (никак не структурированный) объектов конфигурации (с их реквизитами), между которыми существуют некоторые связи. А схема данных это инструмент, позволяющий визуализировать эту модель.

Всё это теоретическое отступление мы написали только ради того, чтобы обратить ваше внимание на две важные вещи.

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

Во-вторых, схема данных это не аналог и не замена дерева объектов конфигурации. Это ещё один инструмент разработки, но он, можно сказать, имеет свою собственную аудиторию.

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

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

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

2.4. Системы управления базами данных и экспертные системы

2.4.3.2. Установка связей между таблицами в СУБД Access

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

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

Рис. 1.

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

Рис. 2.

Следующий шаг — это установка связей между таблицами в окне Схема данных. Для этого в окне Схема данных необходимо отбуксировать (переместить) поле КодГруппы из таблицы Группы на соответствующее поле таблицы Студенты, в результате этой операции появится окно «Изменение связей».

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

Рис. 3.

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

Рис. 4.

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

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

2.4.3.3. Заполнение таблиц

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

На экране появится структура таблицы БД в режиме таблицы. Новая таблица состоит из одной пустой строки.

Рис. 5.

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

Для заполнения поля MEMO в таблице (колонка Место рождения) нажимаем комбинацию клавиш <Shif+F2>, предварительно установив курсор в поле MEMO. Открывается диалоговое окно Область ввода, после ввода или редактирования данных в этом окне щелкаем на кнопке ОК.

После заполнения таблица Студенты имеет следующий вид.

Рис. 6.

Аналогичным образом заполняются остальные таблицы: Группы Студентов, Успеваемость, Дисциплины.

Рис. 7.

Рис. 8.

Рис. 9.

В приложении Access применяются различные методы перемещения по таблице. Переходить от записи к записи можно с помощью: клавиш управления курсором; кнопки из области Запись, расположенной внизу таблицы в режиме таблицы; команды Правка — Перейти.. Для перемещения от поля к полю (слева направо) применяются клавиши Tab и Enter, а в обратном направлении Shift+Tab.

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

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

Далее …>>>Тема: 2.4.4. Формирование запросов

Сценарий 1. Какие объекты конфигурации использует данный объект

Это самый простой сценарий. Например, у вас есть регистр Продажи. Вам нужно узнать, какие объекты конфигурации использует это регистр. То есть, на какие объекты конфигурации ссылаются его реквизиты, измерения и ресурсы.

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

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

EDT сразу же показывает вам ER-диаграмму. В неё включаются все объекты первого уровня, которые использует регистр Продажи.

На этой схеме данных видно, что регистр Продажи использует документ РасходТовара и справочники Товары и Контрагенты. Эти связи исходят из регистра. Кроме этого на схеме показаны и взаимные связи между самими используемыми объектами (между документом и справочниками).

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

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

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

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

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

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

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