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

Введение

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

Отношение «многие ко многим» убирает это ограничение моделирования, позволяя факту (например, балансу счета) быть связанным с несколькими членами того же измерения (баланс объединенного счета может рассматриваться как атрибут двумя или несколькими владельцами объединенного счета).

Концептуально связь измерения «многие ко многим» в службах Analysis Services эквивалентна связям в реляционной модели и поддерживает те же виды сценариев. Примеры подобной связи приведены ниже.

• Студенты записаны на несколько курсов, каждый курс имеет несколько студентов.

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

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

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

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

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

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

Нажмите кнопку, чтобы открыть окно «Определение связи» и проверить тип связи, а также узнать, какая промежуточная группа мер используется в связи.

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

База данных Кинга

Эта учебная база данных представляет собой сведения о корпорации Кинга. Среди таблиц:

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

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

Масштабные же таблицы, такие как «сотрудники фирмы», «фирмы-покупатели», «договоры о продаже» и «акты продаж» связаны сразу с несколькими сущностями, причем с некоторыми — при помощи «посредников» отношением многие-ко-многим. Таблица «фирмы-покупатели» сама является посреднической, как таковая, ведь в ней есть многие поля, заимствованные из других таблиц и являющиеся внешними ключами. Кроме того, масштабность и взаимосвязь базы данных «Корпорации Кинга» такова, что все отношения неразрывно коррелируют между собой и влияют одно на другое. Разрушение хотя бы одного из них повлечет за собой деструкцию целостности всей БД.

Создание и изменение отношений 1:N между сущностями

Откройте обозреватель решений.

В разделе Компоненты раскройте узел Сущности, затем раскройте сущность, с которой требуется работать.

Выберите Отношения 1:N.

Чтобы изменить отношение или просмотреть сведения для отношения, выберите отношение и нажмите на панели инструментов «Действия» кнопку Другие действия, затем выберите Изменить.
— ИЛИ —
Чтобы добавить новое отношение, выберите Создать отношение «один ко многим».

Важно!
Если кнопка Создать отношение «один ко многим» не отображается на панели инструментов «Действия», то создать отношение 1:N для этой сущности невозможно.

Для нового отношения в разделе Определение отношения выберите в списке Связанная сущность сущность для связывания.

Примечание
При указании связанной сущности задается значение по умолчанию в поле Имя. Если изменить связанную сущность перед ее сохранением, соответственно изменится и значение поля Имя.

Выберите, будет ли это поле доступно для поиска или нет.

В разделе Поле поиска укажите значение для поля в поле Отображаемое имя.

Важно!
При указании значения Отображаемое имя задается значение по умолчанию в поле Имя

Если изменить Отображаемое имя поля поиска перед сохранением данных, значение в поле Имя не изменится. Поэтому необходимо ввести в поле Имя информативное значение перед сохранением данных.

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

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

В разделе Поведение отношений выберите в списке Тип отношений один из следующих вариантов.

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

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

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

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

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

Дополнительные сведения:

1. Выберите Сохранить и закрыть, чтобы закрыть форму Отношение.

2. Выполнив настройки, опубликуйте их:

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

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

Примечание

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

Связь один ко многим

Последнее обновление: 17.12.2015

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

public class Player
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public int Age { get; set; }

    public int? TeamId { get; set; }
    public Team Team { get; set; }
}
public class Team
{
    public int Id { get; set; }
    public string Name { get; set; } // название команды

    public ICollection<Player> Players { get; set; }
	public Team()
    {
        Players = new List<Player>();
    }
}
public class SoccerContext : DbContext
{
    public SoccerContext() : base("SoccerContext")
    {}

    public DbSet<Player> Players { get; set; }
    public DbSet<Team> Teams { get; set; }
}

Добавление и вывод:

using(SoccerContext db = new SoccerContext())
{
    // создание и добавление моделей
    Team t1 = new Team { Name = "Барселона" };
    Team t2 = new Team { Name = "Реал Мадрид" };
    db.Teams.Add(t1);
    db.Teams.Add(t2);
    db.SaveChanges();
    Player pl1 = new Player {Name = "Роналду", Age = 31, Position = "Нападающий", Team=t2 };
    Player pl2 = new Player { Name = "Месси", Age = 28, Position = "Нападающий", Team=t1 };
    Player pl3 = new Player { Name = "Хави", Age = 34, Position = "Полузащитник", Team=t1 };
    db.Players.AddRange(new List<Player>{pl1, pl2,pl3});
	db.SaveChanges();
                
	// вывод 
    foreach(Player pl in db.Players.Include(p=>p.Team))
        Console.WriteLine("{0} - {1}", pl.Name, pl.Team!=null?pl.Team.Name:"");
    Console.WriteLine();
    foreach(Team t in db.Teams.Include(t=>t.Players))
    {
        Console.WriteLine("Команда: {0}", t.Name);
        foreach(Player pl in t.Players)
        {
            Console.WriteLine("{0} - {1}", pl.Name, pl.Position);
        }
        Console.WriteLine();
    }
}

Результат вывода:

Редактирование:

//редактирование
t2.Name = "Реал М."; // изменим название
db.Entry(t2).State = EntityState.Modified;
// переведем игрока из одной команды в другую
pl3.Team = t2;
db.Entry(pl3).State = EntityState.Modified;
db.SaveChanges();

При удалении объектов, связанных отношением «один-ко-многим» нам надо учитывать то, что по умолчанию даже если внешний ключ допускает значение null
(как в данном случае свойство TeamId в классе Player), мы не сможем просто так удалить одну модель, если она имеет ссылки на другую модель.
Например, удаление команды в данном случае выльется в ошибку, если какой-то объект Player имеет ссылку на эту команду. Что делать в этом случае?
В этом случае нам надо установить для внешнего ключа TeamId в таблице игроков ограничение . Данное ограничение позволит
при удалении связанного объекта устанавливать для внешнего ключа значение null.

Установку ограничения можно сделать вручную, изменим схему базы данных, либо программно. Рассмотрим, как это сделать программно. Для этого перед удалением нам надо выполнить
соответствующую команду SQL, чтобы добавить ограничение:

 db.Database.ExecuteSqlCommand("ALTER TABLE dbo.Players ADD CONSTRAINT Players_Teams FOREIGN KEY (TeamId) REFERENCES dbo.Teams (Id) ON DELETE SET NULL");
 

Здесь добавляется ограничение «Players_Teams» в таблицу игроков, которая называется, как правило, dbo.Players или просто Players. Это ограничение указывает, что для
внешнего ключа TeamId из таблицы Players, который связан со столбцом Id из таблицы dbo.Teams, устанавливается правило «ON DELETE SET NULL», то есть установка null по удалению.

И после этого мы можем выполнить удаление:

//удаление игрока
Player pl_toDelete = db.Players.First(p => p.Name == "Роналду");
db.Players.Remove(pl_toDelete);
// удаление команды     
Team t_toDelete = db.Teams.First();
db.Teams.Remove(t_toDelete);
db.SaveChanges();

При удалении команды свойство Team у объектов Player получает значение null.

НазадВперед

Для чего все это нужно?

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

Правильно настроив связи, можно быть уверенным, что ничего не потеряется.

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

• < Назад
• Вперёд >

Новые статьи:

• Объединение таблиц – UNION

• Соединение таблиц – операция JOIN и ее виды

• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Microsoft Access

Офисный программный продукт от «Майкрософт» известен на рынке ПО уже довольно продолжительное время. Он поставляется вместе с текстовым редактором Worfd, табличным процессором Excel и прочими, входящими в линейку «офиса». Можно Access (читается как «аксес», дословный перевод — «доступ») приобрести и отдельно от его «коллег». Рекомендуется купить, разумеется, лицензионный софт, но ни для кого не секрет, сколько пиратских репаков можно найти на просторах Сети, в виде обычных файлов или раздач торрентов. «Майкрософт Аксес» доступен даже в портативной сборке. Она, не требующая инсталляции и особых навыков работы с ПК, лучше всего подходит для выбора, если ПО не будет использоваться продолжительно и часто.

Из контекста ясно, что «Майкрософт Аксес» — это система управления базами данных. Причем одна из популярнейших. Она является реляционной, что значит, она основана на логической модели данных, которая в ходе своей работы обращается к теории множеств и логике первого порядка. Связь многие-ко-многим в Access (примеры будут даны в ходе объяснения) реализуется очень и очень просто. Рассмотрим ее.

Как в access

В разделе Программное обеспечение на вопрос Как в Access установить связи между таблицами, с поддержкой целостности данных заданный автором Алексей Ковалёв лучший ответ это оздание связей «один ко многим» или «один к одному»Чтобы создать связь вида «один ко многим» или «один к одному», воспользуйтесь приведенной ниже последовательностью действий:1. Закройте все открытые таблицы. Создавать или изменять связи между открытыми таблицами нельзя.2. В Access версий 2002 или 2003 выполните указанные ниже действия.a. Нажмите клавишу F11, чтобы перейти в окно базы данных.b. В меню Сервис выберите команду Связи.В Access 2007 нажмите кнопку Связи в группе Показать или скрыть вкладки Инструменты для баз данных.3. Если в базе данных отсутствуют связи, то автоматически появится диалоговое окно Добавление таблицы. Если окно Добавление таблицы не появилось, но при этом нужно добавить таблицы в список связываемых, выберите команду Добавить таблицу в меню Связи.4. Дважды щелкните названия таблиц, которые необходимо связать, после чего закройте диалоговое окно Добавление таблицы. Чтобы связать таблицу с самой собой, добавьте ее два раза.5. Перетащите связываемое поле из одной таблицы на связываемое поле в другой. Чтобы перетащить несколько полей, нажмите клавишу CTRL, щелкните каждое поле, а затем перетащите их.В большинстве случаев понадобится перетащить поле первичного ключа (выделенное полужирным текстом) из одной таблицы на аналогичное поле (часто с тем же самым названием) , называемое внешним ключом, другой таблицы.6. Появится окно Изменение связей. Убедитесь, что в каждом из столбцов отображаются названия нужных полей. Если нужно, их можно изменить.При необходимости задайте параметры связи. Если требуются сведения о конкретном элементе окна Изменение связей, нажмите кнопку со знаком вопроса, а затем щелкните соответствующий элемент. Эти параметры будут подробно описаны ниже.7. Чтобы установить связь, нажмите кнопку Создать.8. Повторите действия с 5 по 8 для каждой пары связываемых таблиц.При закрытии диалогового окна Изменение связей Microsoft Access спросит, нужно ли сохранить макет. Вне зависимости от ответа на этот вопрос создаваемые связи сохраняются в базе данных.Примечание. Создавать связи можно не только в таблицах, но и в запросах. При этом, однако, не обеспечивается целостность данных.Создание связей «многие ко многим»Чтобы создать связь вида «многие ко многим», выполните указанные ниже действия.1. Создайте две таблицы, которые необходимо связать отношением «многие ко многим».2. Создайте третью таблицу, называемую соединительной, и добавьте в нее поля с теми же определениями, что и поля первичных ключей в каждой из двух других таблиц. Поля первичных ключей соединительной таблицы служат внешними ключами. В соединительную таблицу, как и в любую другую, можно добавить и другие поля.3. Задайте первичный ключ этой таблицы таким образом, чтобы он включал в себя поля первичных ключей обеих основных таблиц. Например, первичный ключ соединительной таблицы «АвторыКниг» будет состоять из полей «ИД_заказа» и «ИД_продукта».Примечание. Чтобы создать первичный ключ, выполните указанные ниже действия.a. Откройте таблицу в режиме конструктора.b. Выберите одно или несколько полей, которые необходимо определить в качестве первичного ключа. Чтобы выбрать одно поле, щелкните знак выбора строки для нужного поля.Чтобы выбрать несколько полей, удерживайте нажатой клавишу CTRL и щелкните знак выбора строки для каждого из полей.c. В Access версий 2002 или 2003 нажмите кнопку Первичный ключ на панели инструментов.В Access 2007 нажмите кнопку Первичный ключ в группе Сервис вкладки Структура.Примечание. Чтобы порядок следования полей в первичном ключе, состоящем из нескольких полей, отличался от такового в таблице, нажмите кнопку Индексы на панели инструментов, в результате чего появится диалоговое окно Индексы, в котором можно изменить порядок следования полей индекса под названием КлючевоеПоле.

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

При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем 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. Определите связь один-ко-многим между каждой основной и связующей таблицами.