Практическая работа к разработке урока "Создание многотабличной базы данных"

Практическая работа

Задание 1. Работа со связанными таблицами

1. Создайте базу данных Students, которая будет хранить результаты экзаменов студентов одной группы за один семестр. Можно обойтись и одной таблицей, в которую включить поля «Фамилия», «Имя», «Предмет», «Преподаватель», «Оценка». Но тогда очень много данных будет повторяться, так как каждый студент сдает несколько экзаменов, и каждый экзамен сдают многие студенты.

Поэтому в режиме Конструктора создайте поочередно три таблицы — «Студенты» (табл. 1), «Предметы» (табл. 2), «Оценки» (табл. 3) — заполните их.

Для того: в таблице "Студенты" первое поле "Код студента" будет иметь тип счетчик - Ключевое поле, остальные — текстовый тип.

Задайте Ключевое поле, нажав на кнопку Ключевое поле вкладки Конструктор на Ленте (Работа с таблицами). Аналогично поступите по все таблицах.

В таблице "Предметы" первое поле "Код предмета" будет иметь тип счетчик, остальные — текстовый тип. В третьей таблице "Оценки" первое поле "Код оценки" будет иметь тип счетчик, а остальные — числовой тип.

Таблица 1. Таблица «Студенты»

Код студента	Фамилия	Имя
1	Антонова	Марина
2	Борисов	Ярослав
3	Волков	Константин
4	Волошина	Светлана
5	Кашина	Наталья
6	Леньков	Алексей
7	Матвеев	Роман
8	Наумова	Татьяна
9	Юдинцева	Оксана

Таблица 2. Таблица «Предметы»

Код предмета	Название	Преподаватель
1	Информатика	Половина
2	Психология	Сивакова
3	Математика	Полыгалова
4	История	Климова

Таблица 3. Таблица «Оценки»

Код оценки	Код студента	Код предмета	Оценка
1	1	1	5

Рис. 1. База данных «Students»

В результате вы должны получить базу данных Students из трех пока не связанных таблиц.

 

2. Заполните таблицу «Оценка».

Обратите внимание, что выставлять оценки неудобно. Чтобы выставить Волкову «отлично» по математике, надо помнить коды и студента и предмета, но Access может оперировать с кодами, а информацию выдавать в текстовой форме, сделайте это для таблицы «Предметы»: Конструктор ® Мастер подстановок ® и «Название» ® Предметы".

Теперь в режиме таблицы будут видны названия предметов.

3. Создайте подстановку для фамилий студентов по алгоритму, описанному в пункте 2, в качестве таблицы источника данных выберите таблицу «Студенты», а в ней поля «Код студента» и «Фамилия» ® Студенты" .

4.    Сделайте так, чтобы подстановка выполнялась из двух полей, т. е. выводились фамилия и имя: Конструктор в таблице Оценки выберите поле «Студенты» → Подстановка (в Свойствах поля) → Источник строк и щелкните по кнопке с многоточием справа (откроется построитель запросов) → [Фамилия] &" "&[Имя] Внутри кавычек обязательно поставьте пробел.

Теперь будет видна и фамилия студента и его имя.

5. Access самостоятельно после выполнения подстановки установил связи между таблицами, связав поля с одинаковыми именами и совместимыми типами. Убедитесь в том, для этого надо щелкнуть на пиктограмме Схема данных во вкладке Работа с базами данных на Ленте.

6. Заполните таблицу "Оценки" на всех студентов и все предметы. Оценки возьмите произвольно.

7. Добавьте сведения еще о двух-трех студентах в таблицу «Студенты». Перейдите в таблицу «Оценки».

Обратите внимание, что информация о добавленных студентах оказалась в конце списка. Чтобы упорядочить список по алфавиту, вызовите построитель запросов для поля «Студенты» таблицы "Оценки" и установите сортировку по возрастанию. Сделайте то же самое для поля «Предметы». Заполните данные для новых студентов.

8. Скройте поле «Код оценки» (оно необходимо для Access, a для пользователя мало интересно): → во вкладке Главная на панели Записи откройте Дополнительно → Ширина столбца → . Этого же результата можно добиться перетаскиванием правой границы столбца к левой границе окна.

9. Обратите внимание, что Access позволяет хранить несколько записей о сдаче одним и тем же студентом одного и того же предмета. Сделайте так, чтобы набор «студент-предмет» стал уникальным. Для этого в режиме Конструктор для таблицы «Оценки» установите для этих полей опцию ключевое поле: выделите с помощью мыши оба поля → Ключевое поле или через вкладку Конструктор.

10. Теперь таблица «Оценки» будет выглядеть примерно так.

11. Сделайте так, чтобы в поле «Оценка» вместо цифр можно было записывать «отлично», «хорошо» и т. д. Для этого создайте отдельную таблицу «Список оценок» с полями Код (тип поля - числовой) и Оценка прописью (тип поля - текстовый) и используйте Мастер подстановок для таблицы «Оценки». При создании таблицы «Список оценок» учтите, что существует четыре обозначения оценки: 5 — «отлично», 4 — «хорошо», 3 — «удовлетворительно», 2 — «неудовлетворительно».

Установите связь новой таблицы с прежними.

12. Заполните таблицу «Оценки» еще для нескольких студентов (не для всех) и для некоторых предметов (не для всех).

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

14. Отсортируйте таблицу по полю «Студенты».

15. Предъявите работу преподавателю.

Задание для закрепления нового материала

Создайте три таблицы: Группы, Списки, Личные данные.

1. Создайте структуру таблицы Группы. Поле «Учебная группа»  сделайте ключевым. Сохраните таблицу, не заполняя её.
2. Создайте структуру таблицу Списки. Ключевое поле Код студента (тип числовой). Сохраните таблицу, не заполняя её.
3. Создайте структуру таблицу Личные данные по образцу. Сохраните таблицу, не заполняя её.
4. Установите связи между таблицами
5. Заполните таблицу Группы
6. Для заполнения двух других таблиц создайте общую форму.

Таблица Группы

Учебная группа	Преподаватель
101	Верзаков С.А.
102	Белоусов А.И.
103	Масалова В.А.
104	Новикова Е.В.
105	Зачёсова Т.П.

Таблица Списки

Код студента	Фамилия	Имя	Отчество	Год рождения	Школа	класс	Учебная группа
	Иванова	Анна	Ивановна	2001	1	9	101
	Баранова	Ирина	Алексеевна	2000	3	10	102
	Корнилова	Ольга	Владимировна	2001	5	9	103
	Воробьёв	Алексей	Петрович	2000	1	10	101
	Воробьёв	Алексей	Иванович	2001	3	9	104
	Воробьёв	Олег	Григорьевич	2002	5	8	105
	Скоркин	Александр	Евгеньевич	1999	1	11	101
	Володина	Анна	Алексеевна	2001	3	9	102
	Новосёлов	Алексей	Антонович	2000	5	10	103
	Александрова	Елена	Алексеевна	2001	1	9	101

Таблица Личные данные

Код студента	Адрес	Номер телефона
	Центральная 11-15	5-17-22
	Солнечная 8-117	5-18-22
	Сиреневый 7-16	5-19-22
	Центральная 14-81	5-20-22
	Сиреневый 7-16	5-21-22
	Солнечная 2-121	5-22-22
	Школьная 5-34	5-23-22
	Центральная 30-7	5-24-22
	Сиреневый 7-16	5-25-22
	Солнечная 6-34	5-26-22

7. Выполнить следующие задания:

1)      Выбрать студентов, год рождения которых 2001.

2)      Составить список учеников школы №5, 10 класса.

3)      Выбрать студентов, родившихся позднее 2000 года.

4)      Выберите всех учеников школы №1, фамилии которых начинаются на букву А.

5)      Вывести список учеников с номерами телефонов (показать поля «Фамилия», «Имя», «Отчество», «Номер телефона»)

6)      Составить запрос на адреса девочек, имя которых Анна.

Дополнительное  задание

1. Создайте новую базы данных в программе Microsoft Access, сохраните ее в своей рабочей папке под именем Сотрудники.

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

3. Перейдите в режим таблицы и введите 10 записей в созданную таблицу.

4. Сохраните заполненную таблицу.

5. Выполните редактирование: измените год рождения 4-госотрудника, поменяйте его отчество.

6. Создайте таблицу «Отдел», состоящей из полей Отдел, Руководитель в режиме работы таблицы. В окне Базы данных выберите вкладку Создание ® Таблица. Появится пустая таблица. Тип полей будет выбран автоматически в зависимости от введенной информации.

7. Переименуйте столбец  Код  (щелкнув на названии двойным щелчком) на Отдел и замените тип данных на текстовый (вкладка Режим таблицы на Ленте), а второй столбец на Руководитель.

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

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

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

11. Создайте схему данных. Щелкните по кнопке  Схема данных (вкладка Работа с базами данных) ® Отобразить таблицу. В появившемся окне Добавление таблицы выделите таблицу Отделы и щелкните по кнопке Добавить. Аналогично добавьте таблицу Список сотрудников. В окне Схема данных появится условный вид добавленных таблиц. Щелкните по кнопке Закрыть окна Добавление таблицы. Поставьте курсор мыши на поле Отдел в таблице Отделы и, не отпуская клавишу мыши, перетащите курсор на поле Отдел таблицы Список сотрудников. Отпустите мышку. Появится диалоговое окно Связи.

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

Появится связь «один-ко-многим» (так как в одном отделе может работать несколько сотрудников). Закройте схему данных.

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

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

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