Задания для самостоятельной работы
Задание 1. Создайте таблицу в Microsoft office Access 2007 на основе шаблона «События». (В той же базе данных «База работников» создайте таблицу №3 под именем «Проведение выставок», выбрав команду Создание - Шаблоны таблиц - События). И заполните таблицу 5-6 записями (название выставок и дат придумайте сами). Сохраните.
Задание 2. Создайте таблицу в Microsoft office Access 2007 с помощью конструктора таблиц. (В той же базе данных «База работников» создайте таблицу №4 под именем «Студенты и задания»).
Заполните Имя поля следующими данными (заголовками столбцов):
КодСтудент, Фамилия, Описание задания, Начальная дата, Конечная дата, Замечания.
И соответственно Тип данных:
КодСтудент – СЧЕТЧИК,
Фамилия, Описание задания, Замечания – ТЕКСТОВЫЙ,
Начальная дата, Конечная дата – ДАТА/ВРЕМЯ.
И заполните эту таблицу следующими данными (см. таблицу)
КодСтудент
|
Фамилия
|
Описание задания
|
Начальная дата
|
Конечная дата
|
Замечания
|
1
|
Иванов
|
Электронная почта
|
21.03.09
|
15.05.09
|
|
2
|
Петров
|
Телеконференция
|
10.02.09
|
20.05.09
|
|
3
|
Гаврелеева
|
Браузер
|
20.01.09
|
15.04.09
|
|
4
|
Соколова
|
Служба FTP
|
15.01.09
|
25.04.09
|
|
5
|
Мухина
|
Поисковые системы Интернет
|
30.01.09
|
10.05.09
|
|
6
|
Апареева
|
Интернет 2
|
23.02.09
|
30.05.09
|
|
7
|
Глинкина
|
IP-телефония
|
20.02.09
|
12.05.09
|
|
8
|
Сорина
|
Подключение к Интернету
|
25.03.09
|
30.05.09
|
|
Сохраните набранные данные и при автоматическом запросе системы о создании ключевого поля, нажмите кнопку ДА.
Лабораторно-практическая работа №5. Связывание таблиц в схеме данных.
Цель: научить студентов работать с многотабличной структурой данных, устанавливать взаимосвязи между таблицами.
Теоретические сведения.
В СУБД Access процесс создания реляционной базы данных включает создание схемы данных. Схема данных наглядно отображает таблицы и связи между ними, а также обеспечивает использование связей при обработке данных. В схеме данных устанавливаются параметры обеспечения целостности связей в базе данных.
Таким образом, осуществляется неразрывная связь внемашинного проектирования базы данных с этапом ее создания с помощью СУБД. В схеме данных, построенной по нормализованной модели данных предметной области, могут быть установлены одно-однозначные и одно-многозначные связи. Для таких связей обеспечивается поддержание целостности взаимосвязанных данных, при которой не допускается наличия в базе данных подчиненной записи без связанной с ней главной, при первоначальной загрузке базы данных и ее корректировках. Связи, определенные в схеме данных, используются автоматически при разработке многотабличных форм, запросов, отчетов, существенно упрощая процесс их конструирования.
Взаимосвязи таблиц
При создании в Access схемы данных в ней определяются и запоминаются связи между таблицами. Это позволяет системе автоматически использовать связи, один раз определенные в схеме данных, при создании форм, запросов, отчетов на основе взаимосвязанных таблиц, а пользователь освобождается от необходимости указывать эти связи при конструировании этих объектов. Схема данных базы графически отображается в своем окне, где таблицы представлены списками полей, а связи - линиями между полями разных таблиц.
Одно-многозначные (1:М) или одно-однозначные (1:1) связи. Схема данных прежде всего ориентирована на работу с таблицами, отвечающими требованиям нормализации, между которыми могут быть установлены одно-многозначные (1:М) или одно-однозначные (1:1) связи, для которых может автоматически поддерживаться связная целостность. Поэтому схему данных целесообразно строить в соответствии с информационно-логической моделью.
При построении схемы данных Access автоматически определяет по выбранному полю связи тип отношения между таблицами. Если поле, по которому нужно установить связь, является уникальным ключом как в одной таблице, так и в другой, Access выявляет отношение один-к-одному. Если поле связи является уникальным ключом в одной таблице (главной таблицы связи), а в другой таблице (подчиненной таблице связи) является не ключевым или входит в составной ключ, то есть значения его могут повторяться, Access выявляет отношение один-ко-многим между записями главной таблицы к подчиненной. В этом случае можно задать автоматическое поддержание целостности связей.
Отношение многие-ко-многим. Отношение многие-ко-многим предполагает, что каждой записи в одной таблице соответствует несколько записей в другой. При этом каждая сторона отношения выглядит как отношение один-ко-многим.
Однако если рассматривать взаимосвязь таблиц с двух сторон, становится очевидным, что ни одна из таблиц не может быть главной и для их связывания необходима третья таблица.
Связующая таблица представляет собой промежуточную таблицу, которая служит мостом между двумя таблицами в отношении многие-ко-многим. Ее ключ состоит из ключевых полей этих таблиц, с каждой из которых она связана отношением один-ко-многим. Помимо ключевых полей, связующая таблица должна содержать хотя бы одно поле, которого нет в связываемых таблицах, но которое имеет значение для каждой из них. Таким образом, отношение многие-ко-многим складывается из отношений многие-к-одному и один-ко-многим.
Связи-объединения. Между двумя таблицами может быть установлена связь-объединение по некоторому полю связи. Для связи-объединенияможет быть выбран один из трех способов объединения записей:
Способ 1 - объединение только тех записей, в которых связанные поля обеих таблиц совпадают (производится по умолчанию);
Способ 2 - объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из первой таблицы, для которых нет связанных во второй, с пустой записью второй таблицы;
Способ 3 - объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из второй таблицы, для которых нет связанных в первой, с пустой записью первой таблицы.
Такой тип связи может быть определен, если связь характеризуется отношением 1:1 или 1:М, а также если тип отношения не может быть определен системой, то есть если не выполняются условия для этих отношений. Например, при выборе в главной таблице в качестве поля связи неключевого поля или поля, входящего в составной ключ, Access сообщает, что тип отношения не может быть определен. В этом случае между таблицами возможно установление только связи-объединения.
Связь-объединение обеспечивает объединение записей таблиц, имеющих одинаковые значения в поле связи. Причем производится объединение каждой записи из одной таблицы с каждой записью из другой таблицы при условии равенства значений в поле связи. Кроме того, если выбран второй или третий вариант в результат объединения могут быть добавлены записи из таблицы, для которых нет логически связанных записей в другой таблице. Последние два варианта часто необходимы при решении практических задач. Примером такой задачи может быть формирование записей студентов с результатами успеваемости как в случае полученной оценки по предмету, так и при отсутствии оценки. При отсутствии оценки соответствующее поле будет пустым.
Создание схемы данных
Создание схемы данных начинается в окне Базы данных (Database) с выполнения команды Сервис|Схема данных (Tools|Relationships) или нажатия кнопки Схема данных (Relationships) на панели инструментов базы данных.
Включение таблиц в схему данных. После нажатия кнопки Схема данных (Relationships) открывается окно Добавление таблицы (Show Table) , в котором можно выбрать таблицы и запросы, включаемые в схему данных. Для размещения таблицы в окне Схема данных(Relationships) надо выделить ее в окне Добавление таблицы (Show Table) и нажать кнопку Добавить (Add). Для выделения нескольких таблиц надо, удерживая клавишу , щелкнуть мышью на каждой из этих таблиц. Включив все нужные таблицы в схему данных, нажать кнопку Закрыть(Close).
В результате в окне Схема данных (Relationships) будут представлены все включенные таблицы со списком своих полей. Далее можно приступать к определению связей между ними.
Создание связей между таблицами. При определении связей в схеме данных удобно использовать информационно-логическую модель в каноническом виде, по которой легко определить главную и подчиненную таблицу каждой одно-многозначной связи, поскольку в такой модели главные объекты всегда размещены выше подчиненных. Эти связи являются основными в реляционных базах данных, т. к. одно-однозначные связи используются лишь в редких случаях, когда приходится разделять большое количество полей, определяемых одним и тем же ключом, по разным таблицам, имеющим разный регламент обслуживания.
Устанавливая в окне схемы данных связи типа 1:М между парой таблиц, надо выделить в главной таблице уникальное ключевое поле, по которому устанавливается связь. Далее, при нажатой кнопке мыши, протащить курсор в соответствующее поле подчиненной таблицы.
При создании связи по составному ключу необходимо выделить все поля, входящие в ключ главной таблицы, и перетащить их на одно из полей связи в подчиненной таблице. Для выделения всех полей, входящих в составной уникальный ключ, необходимо отмечать поля при нажатой клавише . После создания связи откроется окно Изменение связей (Edit Relationships). При этом в строке Тип отношения (Relationship Type) автоматически установится тип один-ко-многим (One-To-Many).
При составном ключе связи в окне Изменение связей (Edit Relationships) необходимо для каждого поля ключа в главной таблице ТАБЛИЦА/ЗАПРОС (Table/Query) выбрать соответствующее поле подчиненной таблицы, названной СВЯЗАННАЯ ТАБЛИЦА/ЗАПРОС (Related Table/Query).
Обеспечение целостности данных
При создании схемы данных пользователь включает в неё таблицы и устанавливает связи между ними. Для связей типа 1:1 и 1:М можно задать параметр обеспечения связной целостности данных, а также автоматическое каскадное обновление и удаление связанных записей.
Обеспечение связной целостности данных означает, что Access при корректировке базы данных обеспечивает для связанных таблиц контроль за соблюдением следующих условий:
В подчиненную таблицу не может быть добавлена запись с несуществующим в главной таблице значением ключа связи;
В главной таблице нельзя удалить запись, если не удалены связанные с ней записи в подчиненной таблице;
Изменение значений ключа связи в записи главной таблицы невозможно, если в подчиненной таблице имеются связанные с ней записи.
При попытке пользователя нарушить эти условия в операциях добавления и удаления записей или обновления ключевых данных в связанных таблицах Access выводит соответствующее сообщение и не допускает выполнения операции.
Установление между двумя таблицами связи типа 1:М или 1:1 и задание для нее параметров целостности данных возможно только при следующих условиях:
Связываемые поля имеют одинаковый тип данных, причем имена полей могут быть различными;
Обе таблицы сохраняются в одной базе данных Access;
Главная таблица связывается с подчиненной по первичному простому или составному ключу (уникальному индексу) главной таблицы.
Access автоматически отслеживает целостность связей при добавлении и удалении записей и изменении значений ключевых полей, если между таблицами в схеме данных установлена связь с параметрами обеспечения целостности. При действиях, нарушающих целостность связей таблиц, выводится сообщение. Access не позволяет установить параметр целостности для связи таблиц, если ранее введенные в таблицы данные не отвечают требованиям целостности.
Каскадное обновление и удаление связанных записей
Если для выбранной связи обеспечивается поддержание целостности, можно задать режим каскадного обновления связанных полей и режимкаскадного удаления связанных записей.
В режиме каскадного обновления связанных полей при изменении значения поля связи в записи главной таблицы, Access автоматически изменит значения в соответствующем поле в подчиненных записях.
В режиме каскадного удаления связанных записей при удалении записи из главной таблицы будут автоматически удаляться все связанные записи в подчиненных таблицах. При удалении записи из главной таблицы выполняется каскадное удаление подчиненных записей на всех уровнях, если этот режим задан на каждом уровне.
При удалении записей непосредственно в таблице или через форму выводится предупреждение о возможности удаления связанных записей.
Постановка задачи. Разработать систему, обеспечивающую ведение базы данных по поездам, расписанию движения поездов, пассажирах и билетах. База данных должна содержать информацию о времени прибытия и отправки поездов, тип поезда, пунктах отправления и прибытия. Для автоматизации процесса выдачи билета необходимо хранить Ф.И.О. и паспортные данные пассажира, а также размер льгот. Кроме того, в билете указывается номер вагона, номер места, место отправления и место прибытия и дата получения билета. Создать интерфейс, удобный для работы, включающий удобные и понятные формы, отчеты. Организовать работу с таблицами, поиск по ним, формирование и печать отчетов.
Порядок выполнения задачи.
1. Создайте базу данных. В меню Файл выберите команду Создать и из списка шаблонов на закладке Общие выберите шаблон «База данных». Сохраните создаваемую БД под именем ЖДВокзал.mdb.
2. С помощью конструктора создайте следующие таблицы: тПассажиры, содержащую информацию о пассажирах (поля: КодПассажира, ФИО, Паспорт, Льгота); тПоезда, содержащую информацию о поездах (поля: КодПоезда, Остановка, ВремяПриб, ВремяОтпр, ВремяСтоянки, Наценка); тБилеты, содержащую информацию о продаваемых билетах (поля: КодБилета, КодПассажира, КодПоезда, НомерВагона, НомерМеста, Откуда, Куда, Дата); расписание движения поездов хранится в таблице тРасписание (поля: КодРасписания, КодПоезда, ТипПоезда, ПунктОтпр, ПунктПриб, Период).
3. Свяжите таблицы в схему данных, показанную на рисунке
4. Выполните подстановку поля КодПоезда из таблицы тПоезда в одноименные поля таблиц тБилеты и тРасписание и подстановку поляКодПассажира из таблицы тПассажиры в одноименное поле таблицы тБилеты.
5. Заполните таблицы данными, введя в каждую из них не менее пяти записей.
6. С помощью запроса на выборку выведите список пассажиров, имеющих льготы.
7. С помощью запроса выведите список тех поездов, которые останавливаются в Белгороде.
8. С помощью запроса на выборку выведите список пассажиров, купивших билеты в первом квартале текущего года.
9. Выведите полную информацию о тех пассажирах, которые купили билеты на поезд, останавливающийся в Воронеже 7-го числа текущего месяца.
10. Выведите информацию о тех поездах, время следования в пути которых превышает одни сутки.
11. С помощью запроса выведите информацию о тех поездах, которые не включены в расписание движения.
12. Подсчитайте общую сумму льгот пассажиров, купивших билеты в текущем месяце.
13. С помощью запроса создайте сводную таблицу, в которую занесите прошлогодние данные о пассажирах, билетах и поездах.
14. На основе данных таблиц тПассажиры и тБилеты постройте перекрестный запрос.
|
