Руководство по межтабличным связям
Одной из целей создания хорошей структуры базы данных является устранение избыточности (повторения) данных. Для этого нужно распределить данные по нескольким отдельным тематически организованным таблицам, чтобы каждый факт был представлен один раз. В приложении Access будет предоставлен способ сбора разбросанных данных — это делается путем помещения общих полей в связанные таблицы. Чтобы корректно выполнить это действие, нужно понять взаимосвязи между таблицами и описать эти взаимосвязи в базе данных.
В этой статье
- Введение
- Типы связей между таблицами
- Зачем создавать связи между таблицами?
- Понятие о целостности данных
- Просмотр связей между таблицами
Введение
После создания таблицы для каждой темы в базе данных нужно предоставить приложению Accessсредства, с помощью которых можно будет при необходимости объединять сведения. Это делается путем создания общих полей в связанных таблицах и настройки связей между таблицами. После этого можно создавать запросы, формы и отчеты, одновременно отображающие сведения из нескольких таблиц. Например, приведенная ниже форма содержит сведения из нескольких таблиц:
1. Эта форма содержит данные из таблицы клиентов,
4. и сведений о заказах.
Имя клиента в поле Плательщик получено из таблицы «Клиенты», значения кода заказа и даты заказа — из таблицы «Заказы», наименование товара — из таблицы «Товары», а цена и количество — из таблицы «Заказано». Чтобы можно было передать данные в форму, эти таблицы связаны друг с другом несколькими способами.
В приведенном примере поля в таблицах должны быть согласованы таким образом, чтобы отображать сведения об одном и том же заказе. Это согласование осуществляется путем установления связей между таблицами. Связь между таблицами устанавливает отношения между значениями в ключевых полях — часто между полями, имеющими одинаковые имена в обеих таблицах. В большинстве случаев с первичным ключом одной таблицы, являющимся уникальным идентификатором каждой записи, связывается внешний ключ другой таблицы. Например, для связывания сотрудников с заказами, за которые они отвечают, можно создать связь между полями «Код сотрудника» в таблицах «Сотрудники» и «Заказы».
1. Поле «Код сотрудника» отображается в двух таблицах: как первичный ключ.
2. и как внешний ключ.
Типы связей между таблицами
В Access есть три типа связей между таблицами.
- Связь «один-ко-многим» Рассмотрим базу данных, в которой учитываются заказы, включающую таблицы «Клиенты» и «Заказы» в качестве примера. Клиент может разместить любое количество заказов. Следовательно, у любого клиента, представленного в таблице «Клиенты», может быть много заказов, представленных в таблице «Заказы». Поэтому связь между таблицами «Клиенты» и «Заказы» — это отношение «один-ко-многим». Чтобы создать отношение «один-ко-многим» в структуре базы данных, добавьте первичный ключ на стороне «один» в таблицу на стороне «многие» в виде дополнительного поля или полей. В данном примере необходимо добавить новое поле — поле «Код» из таблицы «Клиенты» — в таблицу «Заказы» и назвать его «Код клиента». После этого Access сможет использовать номер «Код клиента» из таблицы «Заказы» для поиска клиента каждого заказа.
- Связь «многие-ко-многим» Рассмотрим связь между таблицами «Товары» и «Заказы». Отдельный заказ может включать несколько товаров. С другой стороны, один товар может входить в несколько заказов. Таким образом, для каждой записи в таблице «Заказы» может существовать несколько записей в таблицы «Товары». Таким образом, для каждой записи в таблице «Заказы» может существовать несколько записей в таблице «Заказы». Эта связь называется отношением «многие-ко-многим». Обратите внимание, что для определения существующей схемы отношений «многие ко многим» между вашими таблицами, очень важно рассматривать обе стороны отношений. Чтобы представить связь «многие-ко-многим», нужно создать третью (связующую) таблицу, в которой она разбивается на две связи «один-ко-многим». Первичные ключи двух таблиц вставляются в третью таблицу. В результате в третьей таблице сохраняются все экземпляры связи. Например, таблицы «Заказы» и «Продукты» имеют связь «многие-ко-многим», определяемую путем создания двух связей «один-ко-многим» в таблице «Заказано». В одном заказе может быть много продуктов, и каждый продукт может быть указан во многих заказах.
- Связь «один-к-одному» При отношении «один-к-одному» каждая запись в первой таблице может иметь не более одной связанной записи во второй таблице, и наоборот. Отношения этого типа используются нечасто, поскольку обычно сведения, связанные таким образом, хранятся в одной таблице. Отношение «один-к-одному» используется для разделения таблицы, содержащей много полей, с целью отделения части таблицы по соображениям безопасности, а также с целью сохранения сведений, относящихся к подмножеству записей в главной таблице. После определения такого отношения у обеих таблиц должно быть общее поле.
Зачем создавать связи между таблицами?
Связи между таблицами можно создать непосредственно с помощью окна «Схема данных» или путем перетаскивания поля из области Список полей. Access использует связи между таблицами для того, чтобы решить, как связать таблицы для использования их в объекте базы данных. Существует несколько причин для создания связей между таблицами перед созданием других объектов базы данных (форм, запросов, отчетов).
- Связи между таблицами предоставляют сведения для структурирования запросов Для работы с записями из нескольких таблиц часто приходится создавать запросы, соединяющие таблицы. Запрос сопоставляет значения в поле первичного ключа первой таблицы с полем внешнего ключа второй таблицы. Например, чтобы получить строки, в которых перечисляются все заказы для каждого из клиентов, можно создать запрос, соединяющий таблицу «Клиенты» с таблицей «Заказы» на основе поля «Код клиента». В окне «Схема данных» можно вручную указать поля для соединения. Но если связь между таблицами уже существует, Access использует соединение по умолчанию на основе существующей связи между таблицами. Кроме того, при использовании одного из мастеров запросов Access использует сведения об уже определенных связях между таблицами, чтобы предоставить пользователю выбор и подставить в параметры свойств соответствующие значения по умолчанию.
- Связи между таблицами предоставляют сведения для структурирования форм и отчетов При создании формы или отчета в Access используются сведения об уже определенных межтабличных связях, чтобы предоставить пользователю выбор и предварительно заполнить параметры свойств соответствующими значениями по умолчанию.
- Связи между таблицами — это та основа, с помощью которой можно обеспечить целостность данных, чтобы в базе данных не было потерянных записей. Потерянная запись — это запись со ссылкой на несуществующую запись (например, запись заказа со ссылкой на отсутствующую запись клиента). При создании базы данных сведения распределяются по таблицам, в каждой из которых есть первичный ключ. После этого к связанным таблицам добавляются внешние ключи, имеющие ссылки на первичные ключи. Эти пары из внешнего и первичного ключей формируют основу для связей между таблицами и многотабличных запросов. Поэтому важно, чтобы ссылки «внешний ключ — первичный ключ» оставались синхронизированными. Целостность данных, которая зависит от связей в таблице, гарантирует, что ссылки остаются синхронизированными.
Понятие о целостности данных
При создании базы данных сведения распределяются по множеству тематически организованных таблиц, чтобы свести к минимуму избыточность данных. После этого в Access предоставляются средства сбора разбросанных данных путем создания в связанных таблицах общих полей. Например, чтобы создать связь «один-ко-многим», добавьте первичный ключ из таблицы на стороне «один» как дополнительное поле в таблицу на стороне «многие». Чтобы соединить данные, Access подставляет значение из таблицы на стороне «многие» в соответствующее поле таблицы на стороне «один». Таким образом, значения таблицы на стороне «многие» связаны с соответствующими значениями на стороне «один».
Предположим, между таблицами «Грузоотправители» и «Заказы» существует связь «один-ко-многим», и нужно удалить грузоотправителя. Если у грузоотправителя, которого нужно удалить, есть заказы в таблице «Заказы, они станут потерянными записями после удаления записи грузоотправителя. В таблице «Заказы» останется код грузоотправителя, но он будет недействителен, поскольку запись, на которую он ссылается, уже не существует.
Задача сохранения целостности данных состоит в предотвращении появления потерянных записей и поддержании ссылок в синхронизированном состоянии, чтобы описанная выше гипотетическая ситуация никогда не возникла.
Обеспечение целостности данных включается для конкретного отношения между таблицами. После активации, Access будет отклонять любые операции, нарушающие целостность данных для этой межтабличной связи. Это означает, что Access будет отклонять как любые обновления, изменяющие целевой объект ссылки, так и удаление такого целевого объекта. Возможно, у вас может быть полностью допустимая потребность в изменении первичного ключа для поставщика, у которого есть заказы в таблице «Заказы». В этом случае необходимо, чтобы Access выполнил автоматическое обновление всех задействованных строк в рамках одной операции. Таким образом, Access гарантирует, что обновление будет полностью завершено, а база данных не будет находиться в несогласованном состоянии, когда некоторые строки обновлены, а другие — нет. Для этого в Access имеется параметр Каскадное удаление связанных записей. Если при включении обеспечения целостности данных был включен параметр Каскадное удаление связанных полей, то при последующем обновлении первичного ключа Access автоматически обновляет все связанные с ним поля.
Может понадобиться удалить строку и все связанные записи — например, запись грузоотправителя и все связанные с ним заказы. Для этого в Access имеется параметр Каскадное удаление связанных записей. Если при обеспечении целостности данных выбрать параметр Каскадное удаление связанных записей, а затем удалить запись на стороне первичного ключа в отношении, Access автоматически удалит все записи со ссылкой на первичный ключ.
Просмотр связей между таблицами
Чтобы просмотреть межтабличные связи, щелкните Схема данных на вкладке Работа с базами данных. Откроется окно «Схема данных», в котором будут отображены все существующие связи. Если связи еще не были определены или это окно открывается впервые, приложение Access предложит добавить в окно таблицу или запрос.
Вызов окна «Схема данных»
- Щелкните «Файл»,выберите«Открыть», а затем выберите и откройте базу данных.
- На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных.
- На вкладке Конструктор в группе Связи нажмите кнопку Все связи.
Связь между таблицами представляется как линия между таблицами в окне «Схема данных». Связь, не обеспечивающая целостность данных, отображается как тонкая линия между общими полями, поддерживающими связь. Если выбрать связь, щелкнув линию, то линия станет жирной. Если обеспечить целостность данных для этой связи, линия станет толще на концах. Кроме того, над жирной частью линии с одной стороны связи будет отображаться цифра 1, а с другой стороны — символ бесконечности (∞).
Когда открыто окно «Схема данных», на ленте доступны указанные ниже команды.
На вкладке Конструктор в группе Сервис
- Изменить связи . Открывает диалоговое окно Изменение связей . При выборе линии связи можно щелкнуть элемент Изменить связи, чтобы изменить связь между таблицами. Можно также дважды щелкнуть линию связи.
- Очистить макет . Запрещает отображение всех таблиц и связей в окне «Схема данных». Имейте в виду, что эта команда только скрывает таблицы и связи, но не удаляет их.
- Отчет о связях . Создает отчет, отображающий таблицы и связи базы данных. В отчете отображаются только таблицы и связи, не скрытые в окне «Схема данных».
На вкладке Конструктор в группе Отношения
- Добавление таблиц (добавление таблицы в Access 2013 Позволяет показывать в окне «Отношения» выбор таблиц.
- Скрыть таблицу . Скрывает выбранную таблицу в окне «Схема данных».
- Прямые связи . Отображает все связи и связанные таблицы для выбранной таблицы в окне «Схема данных», если они еще не отображены.
- Все связи . Отображает все связи и связанные таблицы базы данных в окне «Схема данных». Имейте в виду, что скрытые таблицы (таблицы, для которых установлен флажок Скрытый в диалоговом окне Свойства) и их связи не будут отображены, если не установлен флажок «Показывать скрытые объекты» в диалоговом окне «Параметры переходов».
- Закрыть . Закрывает окно «Схема данных». Если в макет окна «Схема данных» были внесены какие-либо изменения, будет предложено сохранить их.
Связи между таблицами базы данных
Связи — это довольна важная тема, которую следует понимать при проектировании баз данных. По своему личному опыту скажу, что осознав связи, мне намного легче далось понимание нормализации базы данных.
1.1. Для кого эта статья?
Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.
1.2. Как вы можете применить эти знания?
- Процесс создания баз данных станет для вас легче и понятнее.
- Понимание связей между таблицами поможет вам легче освоить нормализацию, что является очень важным при проектировании базы данных.
- Разобраться с чужой базой данных будет значительно проще.
- На собеседовании это будет очень хорошим плюсом.
2. Благодарности
Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!
3.1. Как организовываются связи?
Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.
3.2. Виды связей
Связи делятся на:
- Многие ко многим.
- Один ко многим.
- с обязательной связью;
- с необязательной связью;
- Один к одному.
- с обязательной связью;
- с необязательной связью;
4. Многие ко многим
Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:
- Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
- Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.
4.1. Как построить такие таблицы?
Мы уже имеем две таблицы, описывающие работника и профессию. Теперь нам нужно установить между ними связь многие ко многим. Для реализации такой связи нам нужен некий посредник между таблицами «Employee» и «Position». В нашем случае это будет некая таблица «EmployeesPositions» (работники и должности). Эта таблица-посредник связывает между собой работника и должность следующим образом:
| EmployeeId | PositionId |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 3 |
Слева указаны работники (их id), справа — должности (их id). Работники и должности на этой таблице указываются с помощью id’шников.
На эту таблицу можно посмотреть с двух сторон:
- Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
- Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.
4.2. Реализация
Диаграмма
Код на T-SQL
create table dbo.Employee ( EmployeeId int primary key, EmployeeName nvarchar(128) not null, EmployeeAge int not null ) -- Заполним таблицу Employee данными. insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (1, N'John Smith', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (2, N'Hilary White', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (3, N'Emily Brown', 22) create table dbo.Position ( PositionId int primary key, PositionName nvarchar(64) not null ) -- Заполним таблицу Position данными. insert into dbo.Position(PositionId, PositionName) values(1, N'IT-director') insert into dbo.Position(PositionId, PositionName) values(2, N'Programmer') insert into dbo.Position(PositionId, PositionName) values(3, N'Engineer') -- Заполним таблицу EmployeesPositions данными. create table dbo.EmployeesPositions ( PositionId int foreign key references dbo.Position(PositionId), EmployeeId int foreign key references dbo.Employee(EmployeeId), primary key(PositionId, EmployeeId) ) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 1) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 2) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (2, 3) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (3, 3)Объяснения
С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы
- ссылаемся атрибутом PositionId таблицы EmployeesPositions на атрибут PositionId таблицы Position;
- атрибутом EmployeeId таблицы EmployeesPositions — на атрибут EmployeeId таблицы Employee;
4.3. Вывод
Для реализации связи многие ко многим нам нужен некий посредник между двумя рассматриваемыми таблицами. Он должен хранить два внешних ключа, первый из которых ссылается на первую таблицу, а второй — на вторую.
5. Один ко многим
Эта самая распространенная связь между базами данных. Мы рассматриваем ее после связи многие ко многим для сравнения.
Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).
В этом случае мы наблюдаем следующее: пользователь может иметь многие номера телефонов, но нельзя сказать, что номеру телефона принадлежит определенный пользователь.
Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).
Как мы видим, это отношение один ко многим.
5.1. Как построить такие таблицы?
Пользователей будет представлять некая таблица «Person» (id, имя, фамилия, возраст), номера телефонов будет представлять таблица «Phone». Она будет выглядеть так:
| PhoneId | PersonId | PhoneNumber |
|---|---|---|
| 1 | 5 | 11 091-10 |
| 2 | 5 | 19 124-66 |
| 3 | 17 | 21 972-02 |
Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.
5.2. Почему мы не делаем тут таблицу-посредника?
Таблица-посредник нужна только в том случае, если мы имеем связь многие-ко-многим. По той простой причине, что мы можем рассматривать ее с двух сторон. Как, например, таблицу EmployeesPositions ранее:
- Каждому работнику принадлежат несколько должностей (многие).
- Каждой должности принадлежит несколько работников (многие).
5.3. Реализация
Диаграмма
Код на T-SQL
create table dbo.Person ( PersonId int primary key, FirstName nvarchar(64) not null, LastName nvarchar(64) not null, PersonAge int not null ) insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (5, N'John', N'Doe', 25) insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (17, N'Izabella', N'MacMillan', 19) create table dbo.Phone ( PhoneId int primary key, PersonId int foreign key references dbo.Person(PersonId), PhoneNumber varchar(64) not null ) insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (1, 5, '11 091-10') insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (2, 5, '19 124-66') insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (3, 17, '21 972-02') Объяснения
Наша таблица Phone хранит всего один внешний ключ. Он ссылается на некого пользователя (на строку из таблицы Person). Таким образом, мы как бы говорим: «этот пользователь является владельцем данного телефона». Другими словами, телефон знает id своего владельца.
6. Один к одному
Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).
Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:
| DisabledPersonId | EmployeeId |
|---|---|
| 1 | 159 |
| 2 | 722 |
| 3 | 937 |
Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.
Выполнив это мы получили связь один к одному.
Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.
6.1. Вывод
Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.
6.2. Реализация
Диаграмма
Код на T-SQL
create table dbo.Employee ( EmployeeId int primary key, EmployeeName nvarchar(128) not null, EmployeeAge int not null ) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (159, N'John Smith', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (722, N'Hilary White', 29) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (937, N'Emily Brown', 19) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (100, N'Frederic Miller', 16) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (99, N'Henry Lorens', 20) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (189, N'Bob Red', 25) create table dbo.DisabledEmployee ( DisabledPersonId int primary key, EmployeeId int unique foreign key references dbo.Employee(EmployeeId) ) insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (1, 159) insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (2, 722) insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (3, 937)Объяснения
Таблица DisabledEmployee имеет атрибут EmployeeId, что является внешним ключом. Он ссылается на атрибут EmployeeId таблицы Employee. Кроме того, этот атрибут имеет ограничение unique, что говорит о том, что в него могут быть записаны только уникальные значения. Соответственно, работник может быть записан в эту таблицу не более одного раза.
7. Обязательные и необязательные связи
Связи можно поделить на обязательные и необязательные.
7.1. Один ко многим
- Один ко многим с обязательной связью:
К одному полку относятся многие бойцы. Один боец относится только к одному полку. Обратите внимание, что любой солдат обязательно принадлежит к одному полку, а полк не может существовать без солдат. - Один ко многим с необязательной связью:
На планете Земля живут все люди. Каждый человек живет только на Земле. При этом планета может существовать и без человечества. Соответственно, нахождение нас на Земле не является обязательным
У одной биологической матери может быть много детей. У ребенка есть только одна биологическая мать.
А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.
7.2. Один к одному
- Один к одному с обязательной связью:
У одного гражданина определенной страны обязательно есть только один паспорт этой страны. У одного паспорта есть только один владелец. - Один к одному с необязательной связью:
У одной страны может быть только одна конституция. Одна конституция принадлежит только одной стране. Но конституция не является обязательной. У страны она может быть, а может и не быть, как, например, у Израиля и Великобритании.
У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.
А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.
7.3. Многие ко многим
Любая связь многие ко многим является необязательной. Например:
Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).
А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.
8. Как читать диаграммы?
Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.
Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.
- Возле таблицы Person находится золотой ключик. Он обозначает слово «один».
- Возле таблицы Phone находится знак бесконечности. Он обозначает слово «многие».
9. Итоги
- Связи бывают:
- Многие ко многим.
- Один ко многим.
1) с обязательной связью;
2) с необязательной связью. - Один к одному.
1) с обязательной связью;
2) с необязательной связью.
- Связи организовываются с помощью внешних ключей.
- Foreign key (внешний ключ) — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.
10. Задачи
Для лучшего усвоения материала предлагаю вам решить следующие задачи:
- Описать таблицу фильм: id, название, длительность, режиссер, жанр фильма. Обратите внимание на то, что у фильма может быть более одного жанра, а к одному жанру может относится более, чем один фильм.
- Описать таблицу песня: id, название, длительность, певец. При этом у песни может быть более одного певца, а певец мог записать более одной песни.
- Реализовать таблицу машина: модель, производитель, цвет, цена
- Описать отдельную таблицу производитель: id, название, рейтинг.
- Описать отдельную таблицу цвета: id, название.
У одной машины может быть только один производитель, а у производителя — много машин. У одной машины может быть много цветов, а у одного цвета может быть много машин.
- Добавить в БД из пункта 6.2. таблицу военно-обязанных по типу того, как мы описали отдельную таблицу DisabledEmployee.
- SQL
- Microsoft SQL Server
Тест: СУБД MS Access (2 часть)
Внимание! Все тесты в этом разделе разработаны пользователями сайта для собственного использования. Администрация сайта не проверяет возможные ошибки, которые могут встретиться в тестах.
Информатика. Тест по теме СУБД MS Access. 11 класс
Система оценки: 5 балльная
Список вопросов теста
Вопрос 1
К основным свойствам полей таблицы БД относят:
Варианты ответов
- имя
- тип
- размер
- формат
- подпись
- значение по умолчанию
- условие назначение
- заголовок
Вопрос 2
Заголовок столбца таблицы для поля определяет:
Варианты ответов
- имя поля или подпись
- подпись
- название поля
- заголовок поля
Вопрос 3
Какой тип данных необходимо выбрать для ввода номеров телефонов NNN-NN-NN?
Варианты ответов
- Числовой
- Текстовый
- Дата/время
- MEMO
Вопрос 4
Для чего предназначено окно «Схема данных»?
Варианты ответов
- Для просмотра таблиц
- Для редактирования записей
- Для создания связей между запросами и формами
- Для создания связей между таблицами
Вопрос 5
Какой тип связи необходимо установить, если одной записи в таблице А может соответствовать несколько записей в таблице В, а одной записи в таблице В — несколько записей в таблице А?
Варианты ответов
- «Один-к-одному».
- «Один-ко-многим».
- «Много-к-одному».
- «Много-ко-многим».
Вопрос 6
Какому требованию должны соответствовать ключевые поля?
Варианты ответов
- Должны быть типа «Счетчик».
- Должны содержать вложение.
- Не должны повторяться.
- Должны быть типа MEМО.
Вопрос 7
Между какими объектами устанавливаются связи?
Варианты ответов
- Между запросами
- Между формами
- Между отчетами
- Между таблицами
Вопрос 8
Каково назначение ключевого поля?
Варианты ответов
- Сортировка данных.
- Фильтрация данных.
- Создание новых таблиц.
- Создание связей между таблицами.
Вопрос 9
Для хранения данных в СУБД Аccess используется:
Варианты ответов
- форма
- таблица
- отчет
- запрос
Вопрос 10
Для чего предназначено диалоговое окно «Добавить таблицу» в окне «Схема данных»?
Варианты ответов
- Для выбора таблиц, с которыми устанавливаются связи.
- Для создания формы.
- Для создания запроса.
- Для удаления записей из таблицы.
Вопрос 11
Что называют первичным ключом таблицы?
Варианты ответов
- Строку таблицы, содержащую уникальную информацию
- Столбец таблицы, содержащий неуникальную информацию
- Совокупность поле таблицы, которые однозначно определяют каждую запись
- Столбец таблицы, содержащий уникальную информацию
Вопрос 12
В каком режиме формы можно добавить элементы управления?
Варианты ответов
- В режиме мастера
- В режиме автоформы
- В режиме конструктора
- В режиме таблицы
Вопрос 13
Режим таблицы СУБД MS Access позволяет:
Варианты ответов
- вносить и редактировать данные в таблице
- создавать таблицу и редактировать ее, вносить данные
- создавать таблицы, формировать отчеты и запросы
- создавать таблицы и формы
Вопрос 14
В СУБД MS Access отчеты создаются на вкладе «Создание» с помощью кнопки:
Варианты ответов
- Пустой отчет
- Конструктор отчетов
- Мастер отчетов
Вопрос 15
Выделяют следующие способы создания таблиц в СУБД Access:
Варианты ответов
- В виде представления таблицы аналогично работе в табличном редакторе.
- С помощью службы Access и дополнительного компонента SharePoint.
- С помощью конструктора вручную.
Вопрос 16
В режиме таблицы СУБД MS Access нельзя:
Варианты ответов
- Удалять и добавлять поля.
- Редактировать записи.
- Верный вариант отсутствует.
Вопрос 17
Для чего предназначен перекрестный запрос?
Варианты ответов
- Для создания итоговой таблицы
- Для замены данных
- Для вывода данных, соответствующих условию отбора
- Для внесения изменений в несколько таблиц
Вопрос 18
Создание индексов для поля с каким типом данных не допускается при фильтрации данных:
Варианты ответов
- гиперссылка
- текст
- дата/время
- числовой
Вопрос 19
Макросы в СУБД MS Access используются для:
Варианты ответов
- ввода и редактирования данных
- автоматизации работы
- создания подпрограмм
- экспорта данных
Вопрос 20
Компонент SharePoint можно использовать, для:
Варианты ответов
- создания базы данных на компьютере
- создания веб-приложений баз данных
- построения диаграмм на основе базы данных
- создание резервной копии базы данных
Вопрос 21
Какие данные можно экспортировать в документ MS Word из MS Access?
Варианты ответов
- таблицу
- запрос
- форму
- отчёт
Вопрос 22
Экспорт и импорт данных можно выполнить на вкладке:
Варианты ответов
- Главная
- Работа с базами данных
- Внешние данные
- Конструктор
Вопрос 23
Диаграмма в СУБД MS Access может быть построена при помощи:
Варианты ответов
- Команды «Диаграммы» на вкладке «Создание»
- С помощью команды «Мастер диаграмм» на вкладке «Главная».
- С помощью выбора в контекстном меню при нажатии на таблицу правой кнопки мыши «Сводная диаграмма»
- С помощью команды «Мастер диаграмм» на вкладке «Работа с базами данных»
Какой тип связи необходимо установить если одной записи в таблице а может соответствовать несколько
Методические указания по
проектированию информационных систем
в среде MS ACCESS
на примере создания БД “Переговоры”.
Первое, что необходимо сделать при проектировании БД – это сформировать перечень основных её элементов. В нашем случае ими будут: название города и его телефонный код; Ф.И.О. абонентов и их телефонные номера; продолжительность и цена телефонных разговоров в национальных денежных единицах; операторы телефонной связи и даты проведения разговоров и др.
Эти и другие элементы представляют в СУБД Access соответствующие поля данных. Поскольку Access является реляционной БД, сформируем из данного перечня необходимое количество таблиц (в нашем случае три).
Создадим модель базы данных “Переговоры”.
Для этого проделаем следующие действия.
1. Первую (главную) таблицу назовём “Телефон” и включим в неё следующие поля: номер телефона абонента, ФИО, город, улица, дом, корпус и номер квартиры. Вторую таблицу назовём “Город”, включив её состав поля: код и название города, а также коэффициент. Последнее поле понадобится для расчета платы за телефонные разговоры). Третью таблицу назовём “Звонки”, включив в её состав поля: код звонка, кто (номер), кому (номер), куда (код города), дата и продолжительность разговора.
В дальнейшем структура БД (количество таблиц и связь между ними), а также структура самих таблиц может быть изменена.
Рекомендуется эти таблицы нарисовать на бумаге, так как это не только помогает эффективно формировать БД, но и устанавливать связи между входящими в её состав таблицами.
2. Для создания любой таблицы требуется, чтобы любое, входящее в её состав поле, было описано рядом параметров, среди которых первичными являются три: название или имя поля, тип данных и размер (длина) поля.
Представим структуру таблицы “Телефон”.
Таблица 1. Структура таблицы «Телефон».
Аналогичным образом составим и остальные структуры названных таблиц. При этом следует обратить внимание на то, что поля отдельных (подчиненных) таблиц, связанных с главной таблицей, должны иметь одинаковые параметры.
Таблица 3. Структура таблицы «Звонки».
Формирование элемента «Поле со списком» необходимо для удобства ввода данных в таблицу «Звонки», поскольку данные из этой таблицы в полях «Кто (код города)», «Кто (номер)», «Куда (код города)» выбираются из соответствующих данных таблиц «Телефон» и «Город» и многократно повторяются. При этом, неважно как потом будут вноситься данные в таблицы: непосредственно через таблицу или через соответствующую форму, подстановка будет работать в любом случае. Формирование подстановки «Поле со списком» рассмотрим позже.
Таблицы между собой связываются посредством общих полей, т.е. одинаковых по форматам и, как правило, по названию, имеющихся в обеих таблицах. Такая организация данных позволяет уменьшить избыточность хранимых данных, упрощает их ввод и организацию запросов и отчётов.
Каждая таблица включает в свой состав поле кода, используемого обычно как счётчик (идентификатор) главного её параметра и, как правило, являющегося ключевым полем.
После формирования таблиц устанавливают между ними связи, формируя на бумаге схему данных, разработав информационно-логическую модель реляционной базы данных.
Обычно можно задать три вида связей между таблицами: Один-ко-многим, Многие-ко-многим и Один-к-одному.
Наиболее часто используется тип связи между таблицами “ Один-ко-многим” . В такой связи каждой записи в таблице «А» может соответствовать несколько записей в таблице “В” (поля с этими записями называют внешними ключами), а запись в таблице “В” не может иметь более одной соответствующей ей записи в таблице “А”. Такая связь создаётся, когда только одно из полей таблицы является ключевым или имеет уникальный индекс, т.е. значения в нём не повторяются.
При связи “ Многие-ко-многим” одной записи в таблице “А” может соответствовать несколько записей в таблице “В”, а одной записи в таблице “В” – несколько записей в таблице “А”. Такая схема реализуется только с помощью третьей (связующей) таблицы, ключ которой состоит по крайней мере из двух полей, одно из которых является общим с таблицей “А”, а другое – общим с таблицей “В”. Она фактически представляет две связи типа “ один-ко-многим” через третью таблицу, ключ которой состоит по крайней мере из двух полей, общих для двух других таблиц.
При связи “ Один-к-одному” запись в таблице “А” может иметь только одну связанную запись в таблице “В” и наоборот. Этот тип связи используют редко, так как такие данные могут быть помещены в одну таблицу. Связь с отношением “ Один-к-одному” применяют для разделения очень широких таблиц, для отделения части таблицы в целях её защиты, а также для сохранения сведений, относящихся к подмножеству записей в главной таблице. Она создается, когда оба связываемых поля являются ключевыми или имеют уникальные индексы.
3. Создадим базу данных “ Переговоры ” в Access.
Создание любой БД (в том числе “Переговоры”) целесообразно осуществлять на основе предварительно разработанной модели. Эта работа была проделана в п.2. Теперь приступим к реализации модели с помощью СУБД Access.
Для этого загрузим программу “Access”.
В открывшемся окне этой программы найдём кнопку “Файл” в верхней строке меню программы, откроем выпадающее меню “Файл” и активизируем в нём команду “Создать. ”.
В появившемся окне “Создание файла” выберем пункт “ Новая база данных”, активизируем его. В появившемся окне “Файл новой базы данных” найдём строку “Имя файла” и введём в неё имя проектируемой БД. Присвоим создаваемому в Access файлу имя “ Переговоры” и нажмём на кнопку “Создать”. В результате в выбранной нами папке появится файл с именем “ Переговоры.mdb ”.
Рис.1. База данных «Peregovori».
Предварительно следует создать папку , где будет находиться создаваемая БД. В нашем случае это папка с именем или фамилией студента, которая расположена в папке с именем – номер учебной группы, а та, в свою очередь, расположена в папке “Мои документы”.
В результате появится новое окно с именем “ Peregovori: база данных ” (Рис. 1) .
Для создания первой таблицы БД в левой колонке этого окна выбираем и активизируем кнопку “Таблицы”. При этом в правой части этого окна появляется три команды (три варианта создания таблиц в режиме конструктора, с помощью мастера или путём ввода данных), из которых предпочтительно выбрать режим создания таблицы с помощью мастера или конструктора.
Учиться создавать таблицы лучше в режиме конструктора, поэтому выбираем и активизируем вариант “ Создание таблиц в режиме конструктора ”. При этом откроется окно таблицы, в котором следует ввести сформированные ранее поля таблицы.
Создадим сначала, например, таблицу «Телефон». Её параметры описаны выше (см. Таблица 1). В левый столбец «ИМЯ ПОЛЯ» вводят имена полей, а в столбец «ТИП ДАННЫХ» — нужный тип данных для каждого из них, в правый столбец «ОПИСАНИЕ» вносят текстовые комментарии, которые при заполнении таблицы данными будут высвечиваться в последней строке окна в качестве подсказки (так сделает в таблице «Звонки»).
Рис.2. Таблица «Телефон». Определение полей данных.
Закончив вводить в таблицу необходимые данные, закрываем ее, присвоив таблице имя «Телефон». Далее аналогичным образом создаем оставшиеся две таблицы (Рис. 5.3 и Рис. 5.4.).
Рис.3. Таблица «Город». Определение полей данных.
Рис.4. Таблица «Звонки». Определение полей данных.
Сами данные в таблицы можно вводить двумя способами. Первый способ: выбрать соответствующую таблицу и заполнять ее (вносить дополнения или изменения); второй способ: вводить данные через форму. Будем вводить данные через формы, работу с которыми рассмотрим позже.
4. Разработаем схему данных
Создадим связи между таблицами.
Для перехода к этой работе разберемся с тем, как определять и устанавливать связи.
Так, например, любой абонент может по телефону позвонить нескольким разным абонентам, поэтому связь между объектами (таблицами) «Телефон» и «Звонки» будет «Один-ко-многим» (
).
В один и тот же город может позвонить много абонентов. В этом случае, связь между объектами (таблицами) «Город» и «Звонки» также будет «Один-ко-многим» ().
В одном и том же городе много абонентов телефонной связи (телефонных аппаратов). В этом случае связь между объектами (таблицами) «Город» и «Телефон», тоже будет «Один-ко-многим» ().
Информационно-логическая модель реляционной БД формируется в меню «Сервис» по команде «Схема данных». При этом автоматически открывается окно «Добавление таблиц», из которого с помощью кнопки «Добавить» поочередно переносят в модель все таблицы и нажимают кнопку «Закрыть».
От соответствующих полей подчинённых таблиц следует установить связи согласно определениям, сделанным в предыдущем пункте.
Например, для установления связи между таблицами «Город» и «Телефон» подводят курсор мыши к ключевому полю «Названия города» в таблице «Город», щёлкают левой кнопкой мыши и, не отпуская её, перетаскивают курсор на поле «Город» в таблицу «Телефон», а затем отпускают кнопку мыши. Схема выполненных связей БД «Переговоры» представлена на рис. 5.
Рис.5. Схема данных.
Если правой клавишей мыши «кликнуть» по какой-либо линии связи между таблицами, то откроется окно, в котором можно «Изменить связь…» или «Удалить связь».
Выберем режим «Изменить связь…». В открывшемся окне «Изменение связей»: 1) установим флажок («галочку») в свойстве «Обеспечение целостности данных», а затем;
2) установим флажки в свойствах «Каскадное обновление связанных полей» и «Каскадное удаление связанных записей» (Рис. 6).
Рис.6. Изменение связей.
При этом отредактированные записи в одной таблице (например, «Город»), связанные с записями другой (например, «Телефон»), изменятся в последней автоматически. Аналогично, удаление данных из одной таблицы, связанной с какой-либо другой таблицей, повлечет удалении данных из обеих таблиц.
По окончании установления связей окно схемы данных закрывают, сохранив её макет.
5. Рассмотрим формирование подстановки «Поле со списком».
- откроем таблицу «Звонки» в режиме Конструктора;
- выберем поле «Кто (код города)»;
- в нижней части окна в «Свойствах поля» выберем вкладку «Подстановка»;
- для поля «Тип элемента управления» установим «Поле со списком»,
- для поля «Тип источника строк» установим «Таблица или запрос»;
- в поле «Источник строк» cформируем запрос. Для этого нажмём на самую правую кнопку в этом поле [. ]. В результате откроется окно построителя запросов. Выберём таблицу «Город», поле «Код города», и установим флажок «Вывод на экран».
Рис.7. Построитель запросов. Поле со списком.
В результате в свойствах поля «Кто (код города)» таблицы «Звонки» в закладке «Подстановка» будут внесены данные, представленные на Рис. 8 .
Рис.8. Таблица «Звонки». Поле со списком.
Аналогичные процедуры выполняются для полей «Кто (номер)» и «Куда (код города)». В результате будет создан «скелет» БД «Переговоры». Теперь необходимо определить её содержание.
6. Создадим формы для ввода данных в подготовленные таблицы.
Формы обеспечивают гибкий способ ввода, редактирования, просмотра и удаления данных. Они фактически являются шаблонами, управляющими отображением информации. Форма может отображать одновременно все поля одной или нескольких записей и вмещать несколько десятков полей на одном экране. Если полей много, то для каждой записи можно создать многостраничную форму. Можно создать форму-меню для вызова других форм, таблиц, запросов или отчётов. В форме каждое поле можно разместить в точно заданном месте, выбрать для него цвет или заливку и добавить элементы управления текстом для эффективного ввода данных.
Создать форму можно несколькими способами: с помощью конструктора, мастера автоформ и др. (Рис. 9).
Рис.9. Окно создания новых форм.
Для создания формы, например, «Город», выполяют следующие действия: • находясь в объекте «Таблицы», выбирают таблицу «Город», а в верхнем меню «Вставка» — режим «Форма»;
• В появившемся окне «Новая форма» выбирают режим, например, «Автоформа: в столбец» и нажимают на кнопку «ОК».
При этом будет создана форма для ввода данных в таблицу «Город». Её требуется сохранить, дав ей имя, например, «Город» (Рис. 10).
Рис.10. Вариант формы «Город».
Если требуется корректировка и доработка формы, то для перехода в режим конструктора необходимо в верхнем меню «Вид» выбрать команду «Конструктор». Возврат в режим работы с формой осуществляется через команду «Режим формы» в верхнем меню «Вид». Можно корректировать форму другим способом: перейти в объекты «Форма», выбрать нужную форму и кликнуть по кнопке «Конструктор».
Для создания формы «Абонент» воспользуемся мастером создания форм: •в поле «Таблицы и запросы» выберем «Таблица: Телефон» (Рис. 11); из доступных полей выберем все (в общем случае, при необходимости можно варьировать какие поля и в какой последовательности выводить в форме).
Рис.11. Мастер создания форм. Поля.
• выбираем внешний вид формы, в нашем случае «в один столбец» (Рис. 12).
Рис.12. Мастер создания форм. Внешний вид.
•следующий шаг — определение стиля формы. Выберем стандартный стиль (Рис. 13).
Рис.13. Мастер создания форм. Стиль.
•в заключение процедур с Мастером создания форм, зададим имя формы (Рис. 14) и откроем её для ввода данных .
Рис.14. Мастер создания форм. Имя.
В итоге, разработанная с помощью Мастера форма будет выглядеть следующим образом (Рис. 15).
Рис.15. Форма «Абонент».
Аналогично, любым из описанных способов создаём форму для последней таблицы «Звонки».
Формирование подстановки «Поле со списком» можно сделать при редактировании форм.
Делают это следующим образом.
В форме «Города», например, таким образом, создадим поле «Города». Для этого вызовем эту форму и перейдём в режим конструктора форм. Затем в панели элементов нажмём мышью на элемент «Поле со списком» и, установив курсор мыши в нужном месте экрана, потянем его вправо и вниз формируя это поле. Изначально оно состоит из двух частей: элемента с названием формируемого поля и собственно самого поля.
Встав в название поля, можно изменить его размеры, шрифт и само название. Вызвав правой клавишей мыши выпадающее меню самого поля, надо перейти в команду «Свойства» и в выпавшем окне свойств данного поля установить следующие параметры: • Для поля «Данные» выбрать из таблицы имя поля формируемого списка.
• Для поля «Тип источника строк» установить режим «Таблица и запрос», а в поле «Источник строк» сформировать запрос.
• Для этого надо нажать на самую правую кнопку в этом поле [. ] и в строках запроса «Поле» «Имя таблицы» установить необходимые данные. Для того, чтобы в форме в поле со списком появлялись названия городов, нужно в первых двух колонках запроса в строку «Поле» установить название поля с названиями городов из таблицы «Город», имя которой автоматически отразится в строке «Имя таблицы».
2 шаг. (Рис. 17)
4 шаг. (Рис. 19)
6 шаг. (Рис. 21)
На предыдущих рисунках 16-21 представлены пошаговые операции в Мастере создания полей (Поле со списком).
Рассмотрим пример формирования переключателей с помощью «Панели элементов». Можно создавать как одиночные, так и групповые переключатели.
2 шаг. (Рис. 23)
4 шаг. (Рис. 25)
7. Теперь заполним все таблицы необходимыми данными.
В каждой таблице должно быть не менее десяти записей .
Рис.26. Таблица «Телефон». Данные.
Рис.27. Таблица «Город». Данные.
Рис.28. Таблица «Звонки». Данные.
8. Сортировка данных
Для удобства просмотра записей их можно сортировать в таблице в определённой последовательности, например, в порядке убывания или возрастания какого-либо характеризующего поле (столбец) параметра. Функция сортировки относится к процессу фильтрации данных.
Записи таблицы всегда располагаются в файле БД в том порядке, в котором они были включены в таблицу. Сортировку можно произвести по нескольким полям одновременно.
Сначала выбирают поля, используемые для сортировки, помещая курсор в любую его запись. После этого щёлкают по кнопке сортировки и данные отображаются в отсортированном порядке. По умолчанию в Access сортировка записей начинается с крайнего левого выделенного столбца. При этом записи таблицы будут отсортированы сначала по крайнему левому выделенному столбцу, затем (для одинаковых значений в первом сортируемом столбце) — по второму и т.д. Если нужно восстановить порядок отображения записей, используют команду «Записи», «Удалить фильтр».
Отбор данных с помощью фильтра
Фильтр — это набор условий, применяемых для отбора подмножества записей. В Access существуют фильтры четырёх типов: 1) по выделенному фрагменту, 2) обычный, 3) расширенный и 4) по вводу.
Фильтр по выделенному фрагменту — это способ быстрого отбора записей по выделенному образцу.
Например, нужно просмотреть в таблице записи только о конкретном абоненте. Для этого выделяют фамилию абонента в таблице «Телефон» (становятся в любую ячейку таблицы с его фамилией), нажимают правую клавишу мыши и щёлкают по кнопке «Фильтр по выделенному». Access выбирает записи, для которых значение в соответствующем столбце (например, «Фамилия») равно тексту, соответствующему выбранной фамилии. При этом в строке состояния окна таблицы присутствует слово «ФЛТП», а кнопка с изображением воронки на панели инструментов — кнопка фильтрования, затенена. Это означает, что используется фильтр. При нажатии на неё происходит отключение всех фильтров.
Установки фильтра не пропадают, просто он будет отключен. Фильтр по выделенному может собирать вместе критерии выбора при каждом использовании кнопки «Фильтр по выделенному». Ели поместить курсор в столбец, в котором был установлен режим фильтрации и щёлкнуть по кнопке «Фильтр по выделенному», то произойдет фильтрация данных.
Если требуется просмотреть значения, которые не удовлетворяют какому-то критерию, например, просмотреть всех абонентов, кроме одного, то находясь внутри любой ячейки с его фамилией нужно щёлкнуть правой клавишей мыши и в выпадающем меню выбрать команду «Исключить выделенное». При этом произойдет инверсный выбор.
Более сложные условия фильтрации можно задать, если перейти в меню «Записи» и в команде «Фильтр» выбрать пункт «Расширенный фильтр. «.
Средства сортировки, фильтрации, поиска и замены данных реализованы в Access как автоматически создаваемые запросы данных.
9. Создание запросов
Конструирование запроса проводиться в 3 этапа: 1. Выбор полей,
2. Условия отбора нужных записей данных,
3. Сортировка записи БД.
Допустим, что нас интересует сколько абонентов позвонили в город Москву. Для решения этой задачи перейд`м к объекту «Запросы» и в режиме «Создание запросов с помощью мастера» создадим Запрос на выборку по таблице «Звонки». При этом сортировку результатов можно произвести, например, по полю, в котором представлены фамилии абонентов.
В окне «Создание простых запросов» (Рис. 29) выберем таблицу «Звонки», а из неё выберем поля: «Кто (код города)», «Кто номер», «Куда (код города)», «Кому (номер)», «Дата», «Продолжительность», нажмём кнопку «Далее».
Рис.29. Создание простых запросов.
Затем в следующем окне выберем вариант «Подробный отчёт», и перейдём по кнопке «Далее» в следующее окно. Зададим имя запроса, например, «Москва» и режим открытия запроса для просмотра данных, а затем нажмём кнопку «Готово».
В итоговой таблице запроса будут выведены все имеющиеся звонки. Необходимо выбрать только звонки в Москву. Для этого в режиме конструктора откроем запрос «Москва» . В поле «Куда (код города)» введём условие отбора, а именно, равенство коду города Москвы (495). Теперь для всех строк запроса это поле будет одинаковым. Чтобы не выводить его на экран, убирём соответствующую галочку.
Рис.30. Запрос «Москва». Конструктор.
В результате выполнения такого запроса получитсятаблица, представленная на рис. 31 .
Рис.31. Запрос «Москва».
В данном случае сортировка была осуществлена по поля «Дата».
Аналогичным образом можно создать запросы: «ИнфоГород» (Рис. 32) и «Город без абонентов» (Рис. 33) по таблице «Город», запрос «Переговоры абонентов» по таблицам «Телефон», «Звонки» и «Город» (Рис. 3, Рис. ).
Запрос «ИнфоГород» выдаёт информацию по городам, отсортированным по возрастанию (использованы все поля таблицы «Город», но в ином виде, удобном для пользователя).
Рис.32. Запрос «ИнфоГород».
Запрос «Город без абонентов» выдает информацию (код города и название) тех городов, в которых не проживает ни один из абонентов, имеющихся в нашей базе данных «Переговоры» (Рис. 33).
Рис.33. Запрос «Город без абонентов».
Запрос «Переговоры абонентов» выдаёт подробную информацию по телефонным переговорам всех абонентов. Какие поля были выбраны, из каких таблиц, в каком порядке, а так же установленные свойства можно узнать, обратившись к Рис. 34.
Рис.34. Запрос «Переговоры абонентов». Конструктор.
Сортировка ведётся по четырём полям «Фамилия», «Имя», «Отчество», «Дата» и именно в такой последовательности. (Рис. 35).
Рис.35. Запрос «Переговоры абонентов».
Создайте самостоятельно вспомогательный запрос «Переговоры Коэф» (по конструктивной схеме рис. 36), который будет использоваться для расчёта стоимости телефонных переговоров каждого абонента.
Рис.36. Запрос «Переговоры Коэф». Конструктор.
В результате запрос «Переговоры Коэф» должен выглядеть следующим образом (Рис. 37).
Рис.37. Запрос «Переговоры Коэф».
Создадим запрос «Переговоры Стоимость». Для этого используем не таблицу, как это делалось ранее, а запрос «Переговоры Коэф». Выберем все поля, но отобразим не все и в следующем порядке: «Фамилия», «Имя», «Отчество», «Дата», «Продолжительность» (звонка), «Город» (откуда звонит абонент), «Название города» (куда звонит абонент). Создадим два новых поля: «Стоимость мин» (минуты разговора) и «Итог» (сумма за звонок) — Рис. 38.
Рис.38. «Переговоры Стоимость». Конструктор.
В поле «Стоимость мин» необходимо посчитать стоимость одной минуты разговора с тем или иным городом. Предположим, что стоимость минуты разговора вычисляется как среднее арифметическое коэффициентов городов, в том случае, если абоненты находятся в разных городах. При переговорах в пределах одного города плата не взымается. С помощью построителя выражений в поле «Стоимость мин» строим логическое выражение, удовлетворяющее условию (рис. 39).
Рис.39. Построитель выражений. Поле «Стоимость мин».
Вычислим стоимость всего разговора. Это стоимость минуты, умноженная на «Продолжительность» разговора (Рис. 40).
Рис.40. Построитель выражений. Поле «Итог».
Для полей «Стоимость мин» и «Итог» установим формат «Денежный» в «Свойствах поля». В итоге, запрос «Переговоры Стоимость» примет вид (Рис. 41).
Рис.41. Запрос «Переговоры Стоимость».
10. Создание отчётов
Создадим отчёт «Стоимость услуг», в котором сформируем данные для каждого абонента о том, когда, куда он звонил, как долго разговаривал. Ввыведем стоимость минуты каждого конкретного разговора и итог к оплате.
Для этого выберем объект «Отчёты», режим «Создание отчёта с помощью мастера». В открывшемся окне выберем запрос «Переговоры Стоимость», выделим необходимые поля. Выбираем группировку по четырём полям «Фамилия», «Имя», «Отчество», «Город» (в котором проживает абонент). По этим же полям устанавливаем сортировку по возрастанию.
Выберем макет «Блок» и ориентацию «Альбомная» для отчёта. Установим флажок «Настроить ширину полей для размещения на одной странице». Ззатем, в следующем окне, выберем стиль отчёта, например, строгий, а в последнем окне, зададим имя отчёта — «Стоимость услуг». Нажмём кнопку «Готово».
В результате получится готовый отчёт, который можно хранить в БД, а также распечатать (Рис. 42).
Рис.42. Отчет «Стоимость услуг».
Самостоятельно создайте отчёт «Переговоры абонентов», в котором сформируйте данные по переговорам для каждого абонента дата звонка; город, куда звонил; номер телефона, по которому звонил; длительность разговора (Рис. 43).
Рис.43. Отчет «Переговоры абонентов».
Заметим что, если в отчёт необходимо включить какие-либо математические действия, например, вычисление среднего балла, полученного учащимся по нескольким дисциплинам или в процессе изучения одной дисциплины, то поступают следующим образом:
1) Обычным образом в режиме Мастера создают отчёт, включив в него необходимые существующие поля таблиц. Полученный отчёт открывают и переходят в режиме «Конструктор». Активизируют элемент «Поле» в «Панели элементов» и вставляют его в «Область данных» отчёта.
Это поле состоит из двух частей: имени и содержания поля. Текст, автоматически созданный в части «имя поля», можно заменить, в части «содержание поля». По правой клавише мыши вызывают меню, в котором активизируют команду «Свойства». В появившемся окне переходят в строку «Данные» и нажимают на самую правую кнопку [. ], которая вызывает окно «Построитель выражений». С помощь встроенных в него элементов строят, например, следующее выражение: «=([Зачет №1]+[Зачет №2]+[Зачет №3])/3».
2) Для оценок типа среднего балла следует в строке «Число десятичных знаков» установить цифру «1». По окончании выполнения этих процедур нажимают кнопку «OK».
Чтобы проверить и убедиться в том, что в Отчёте появились необходимые данные, надо перейти в меню «Вид» и выбрать в нём режим «Предварительный просмотр».