Кондрашов Юрий Николаевич,

Практические задания для самостоятельной работы

ИСПОЛЬЗОВАНИЕ суБД ACCESS НА ПРИМЕРЕ ВЕДЕНИЯ КАРТОЧКИ СТУДЕНТА

Необходимо сформировать данные для ведения БД «Деканат» и получения «Отчета по итогам сессии».

Этапы реализации:

1. Сформировать структуры следующих четырех таблиц-справочников с соответствующими полями:

Таблица 6.1

Имя таблицы	Поля
Группы	КодГруппы
НомерГруппы
Специальности	КодСпециальности
НазваниеСпециальности
Языки	КодЯзыка
НазваниеЯзыка
Формы_обучения	КодФормыОбучения
НазваниеФормыФбучения
Предметы	КодПредмета
НазваниеПредмета

2. Сформировать структуру таблицы «Студенты» со следующими полями:

ü Код_студента

ü Фамилия

ü Имя

ü Отчество

ü Адрес

ü Общежитие – логическое поле

ü Группа – выборка

ü Специальность – выборка

ü Форма_обучения – выборка

ü Язык – выборка.

Для последних четырех полей задать выборку, т.е. для этого типа данных выбрать «Мастер подстановок» и осуществить следующее:

· подтвердить выбираемый по умолчанию способ подстановки значений в столбец (столбец подстановки использует значения из таблицы или запроса)

· выбрать таблицу или запрос со значениями, которые будет содержать столбец подстановки (см. рис.6.1)

· выбрать поля со значениями, которые будет содержать столбец подстановки

· скрыть ключевой столбец (по умолчанию), тогда для выборки будут предъявлены следующие значения, приведенные на рис. 6.2.

Альтернативным вариантом «Мастера подстановок…» является построение соответствующего запроса через «Свойствахполя», вкладку «Подстановки», строку «Источник строк».

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

Рис. 6.1. Выбор источника столбца-подстановки

Рис. 6.2. Вид столбца-подстановки

3. Сформировать структуру таблицы « УспеваемостьСтудентов» с полями:

ü Код_студента – выборка

ü Код_предмета – выборка

ü Балл

Для полей типа выборка осуществить построения, подобные п. 2. Первичный ключ в таблице сложный, состоящий из полей: «Код_студента» и «Код_предмета».

4. Установить связи между таблицами. Схема данных приведена на рис. 6.3.

5. Создать формы для ведения таблиц «Студенты» и “Успеваемость студентов”. Общий вид связанных форм представлен на рис. 6.4.

6. Создать перекрестный запрос « Успеваемость_студентов». На основе таблиц «УспеваемостьСтудентов», «Студенты» и «Предметы» на первом шаге создается итоговый запрос (для поля «Балл» установите в строке «Групповая операция» значение Avg). На втором шаге надо превратить его в перекрестную таблицу, выбрав пункт меню «Запрос \ Перекрестный» (в появившейся строке «Перекрестная таблица» для поля «КодСтудента» надо выбрать «Заголовок строк», для поля Предмет – «Заголовок столбцов», для поля Балл – «Значение»). Бланк запроса приведен на рис. 6.5. Результатом выполнения запроса будет двумерная таблица.

Рис. 6.3. Схема данных БД «Деканат»

Рис. 6.4. Связанные формы для заполнения данных

Рис. 6.5. Бланк перекрестного запроса

7. На основе запроса «Успеваемость_студентов», таблиц «Студенты» и «Группы» построить с помощью мастера отчетов отчет «Итоги летней сессии 2002/2003 уч. Года» по группам в виде таблицы, где по горизонтали расположены предметы, а по вертикали – фамилии. Страница отчета должна иметь альбомный вид. В режиме конструктора внести необходимые корректировки. Общий вид отчета представлен на ри

Рис. 6.6. Общий вид отчета

8. Создать форму ведения ДБ «Деканат» в следующей последовательности:

· Создать макрос для открытия формы «Студенты» и сохранить его с именем «Студенты».

· Создать макрос для открытия таблиц «Язык», «Группа», «Предметы», «Форма_обучения», «Специальность» и сохранить его с именем «Справочники».

· Создать макрос для открытия запроса «Перекрестный» и сохранить его с именем «Успеваемость».

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

· Создать макрос «Выход» выбором в качестве Макрокоманды команды «Выход». Добавить кнопку этого макроса в форму «Панель кнопок». Раскрыть свойства кнопки (выбор из ее контекстного меню команды «Свойства») и изменить рисунок (например, на образец с дверью «Выход»). Для лучшего оформления стартующей формы поместить на форму рисунок и увеличить форму в размерах (рис. 6.7).

· Реализовать автоматический запуск формы «Панель кнопок». Это можно сделать двумя способами:

a. с сохранением окна базы данных, панели инструментов и стандартного меню. В этом случае необходимо создать макрос для открытия формы «Панель кнопок» и сохранить его с именем “AutoExec”.

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

Рис. 6.7. Форма «Панель кнопок»

9. Дополнить БД «Деканат» новой таблицей «Преподаватели» с использованием макроса для ввода даты:

· Создать таблицу «Преподаватели» с полями «ФИО», «Предмет» (заполняется из списка), «Дата_ввода».

· Создать макрос «Дата_ввода». В качестве «Макрокоманды» выбрать команду «ЗадатьЗначение», в качестве аргумента «Элемент» – имя поля в квадратных скобках, т.е. [Дата_ввода], в качестве аргумента “ Выражение» – встроенную функцию Date ().

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

· В созданную форму ввести несколько записей. Значения поля «Дата_ввода» вводится нажатием кнопки «Дата_ввода» (рис. 6.8).

Рис. 6.8. Форма для заполнения даты ввода

Во всех приведенных в данном разделе вариантах заданий необходимо выполнить следующее:

1. Создать БД.

2. Спланировать и описать структуру базы данных (смотри п.1 каждого варианта). При необходимости нормализовать БД.

3. Установить связи между таблицами.

4. Заполнить таблицы данными, при этом использовать формы и, при необходимости, технологию выборок.

5. Сформировать и выполнить запросы (смотри п.2 каждого варианта).

Вариант 1. " Жители"

1. База «Жители» состоит из 3-х следующих таблиц: «Район», «Здание», «Жильцы».

Состав полей таблицы «Район»

№	Поле
	код района
	Название района

Состав полей таблицы «Здание»

№	Поле
	код дома
	код района
	улица
	№ дома

Состав полей таблицы "Жильцы"

№	Поле
	код жителя
	код дома
	ФИО
	год рождения
	Пол

2. Виды запросов:

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

2.2. Посчитать количество жителей заданного возраста в каждом районе.

2.3. Вывести всех жильцов (ФИО, год рождения), живущих на заданной улице.

Вариант 2. "Жильё"

1. База "Жилье" состоит из 3-х следующих таблиц: "Район", "Здание", "Квартира".

Состав полей таблицы "Район"

№	поле
	код района
	название района

Состав полей таблицы "Здание"

№	поле
	код дома
	код района
	адрес (улица,дом)
	год постройки

Состав полей таблицы "Квартира"

№	поле
	номер квартиры
	код дома
	кол-во жильцов

2. Типы запросов:

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

2.2. Посчитать количество зданий заданного года постройки для каждого района.

2.3. Посчитать количество жителей для каждого района

Вариант 3. "Население"

1. База "Население" состоит из 3-х следующих таблиц: "Регион", "Город", "Население".

Состав полей таблицы "Регион"

№	поле
	код региона
	название региона

Состав полей таблицы "Город"

№	поле
	код города
	код региона
	название города

Состав полей таблицы "Население"

№	поле
	код города
	кол-во мужчин
	кол-во женщин

2. Типы запросов:

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

2.2. Посчитать население каждого города заданного региона.

2.3. Сформировать список городов с населением > 1 млн. человек.

Вариант 4. "Вакансии"

1. База "Вакансии" состоит из 3-х следующих таблиц: "Фирма", "Профессия", "Вакансии".

Состав полей таблицы "Фирма"

№	поля
	код фирмы
	название фирмы
	адрес

Состав полей таблицы "Профессия"

№	поля
	код профессии
	название профессии

Состав полей таблицы "Вакансии"

№	поля
	код фирмы
	код профессии
	средний оклад
	кол-во вакансий

2. Типы запросов:

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

2.2. Вывести список фирм с количеством вакансий и средним окладом для заданной профессии.

2.3. Сформировать список профессий со средним окладом > 3000 руб. для заданной фирмы.

Вариант 5. "Автомагазины"

1. База "Магазины запчастей" состоит из 3-х следующих таблиц: "Магазины", "Автомобили", "Стоимость".

Состав полей таблицы "Магазины"

№	поле
	код магазина
	название магазина
	адрес
	телефон

Состав полей таблицы "Автомобили"

№	поле
	код автомобиля
	марка автомобиля

Состав полей таблицы "Стоимость"

№	поле
	код магазина
	код автомобиля
	год выпуска
	цена

2. Типы запросов:

2.1. Сформировать итоговый запрос со списком магазинов с ценами, марками и годами выпуска автомобилей (в запросе отобразить следующие поля: названия магазинов, марка автомобиля, год выпуска, цена).

2.2. Вывести список магазинов с ценами и годом выпуска для заданной марки автомобиля.

2.3. Вывести список магазинов и марок автомобилей с ценой < 100000 заданного года выпуска.

Вариант 6. "Реализация"

1. База "Реализация" состоит из 3-х следующих таблиц: "Вид товара", "Товары", "Реализация".

Состав полей таблицы "Вид товара"

№	поле
	код вида товара
	название вида товара

Состав полей таблицы "Товары"

№	поле
	код товара
	код вида товара
	название товара
	стоимость приобретения
	цена реализации

Состав полей таблицы "Реализация"

№	поле
	дата
	код товара
	кол-во реализаций

2. Типы запросов:

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

2.2. Вывести прибыль от реализации каждого товара для каждого вида товара.

2.3. Вывести прибыль от реализации каждого товара за заданный период.

Вариант 7. "Поставщики-детали"

1. База "Поставщики-детали" состоит из 3-х следующих таблиц: "Поставщики", "Детали", "Поставки".

Состав полей таблицы "Поставщики"

№	поле
	код поставщика
	имя поставщика
	город

Состав полей таблицы "Детали"

№	поле
	код детали
	наименование детали
	цвет
	ед. измерения
	цена

Состав полей таблицы "Поставки"

№	поле
	код поставщика
	дата
	код детали
	кол-во поставленных деталей

2. Типы запросов:

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

2.2. Вывести список поставленных деталей с их количеством и стоимостью для заданного поставщика.

2.2. Вывести список поставленных деталей с их признаками и количеством для заданного периода.

Вариант 8. "Покупатели"

1. База "Покупатели" состоит из 3-х следующих таблиц: "Покупатели", "Изделие", "Покупки".

Состав полей таблицы "Покупатели"

№	поле
	код покупателя
	имя покупателя
	город

Состав полей таблицы "Изделие

№	поле
	код изделия
	наименование изделия
	ед. измерения
	цена

Состав полей таблицы "Покупки"

№	поле
	код покупателя
	дата
	код изделия
	кол-во купленных изделий

2. Типы запросов:

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

2.2. Вывести список купленных изделий с их количеством и стоимостью для заданного покупателя.

2.2. Вывести суммарную стоимость покупок каждого покупателя за заданную дату.

Вариант 9. "Станции техобслуживания"

1. База "Станции техобслуживания" состоит из 4-х следующих таблиц: "Станции", "Автомобили", "Услуги", "Стоимость".

Состав полей таблицы "Станции"

№	поле
	код фирмы
	название фирмы
	адрес
	телефон

Состав полей таблицы "Автомобили ”

№	поле
	код автомобиля
	марка автомобиля

Состав полей таблицы "Услуги"

№	поле
	код услуги
	название услуги

Состав полей таблицы "Стоимость"

№	поле
	код фирмы
	код автомобиля
	код услуги
	цена

2. Типы запросов:

2.1. Вывести список фирм с их услугами и ценами для заданной марки автомобиля (в запросе отобразить следующие поля: название фирмы, адрес, телефон, марка автомобиля, название услуги, цена).

2.2. Вывести список фирм с ценами услуг для заданной марки автомобиля и суммарной стоимостью услуг < 6000.

2.3. Вывести список всех услуг с их ценами для заданной фирмы.

Вариант 10. "Магазины запчастей"

1. База "Магазины запчастей" состоит из 4-х следующих таблиц: "Магазины", "Автомобили", "Запчасти", "Стоимость".

Состав полей таблицы "Магазины"

№	поле
	код фирмы
	название фирмы
	адрес
	телефон

Состав полей таблицы "Автомобили ”

№	поле
	код автомобиля
	марка автомобиля

Состав полей таблицы "Запчасти"

№	поле
	код запчасти
	название запчасти

Состав полей таблицы "Стоимость"

№	поле
	код фирмы
	код автомобиля
	код запчасти
	цена

2. Типы запросов:

2.1. Вывести список фирм с запчастями и ценами для заданной марки автомобиля (в запросе отобразить следующие поля: название фирмы, адрес, марка автомобиля, название запчасти, цена).

2.2. Построить перекрестный запрос с ценами запчастей каждой фирмы для заданной марки автомобиля.

2.3. Вывести список всех запчастей с их ценами для заданной фирмы.

Вариант 11. "Больница"

1. База "Больница" состоит из 3-х следующих таблиц: "Больница", "Палата", "Пациент".

Состав полей таблицы "Больница"

№	поле
	код больницы
	название больницы
	адрес
	число коек

Состав полей таблицы "Палата"

№	поле
	код палаты
	код больницы
	название палаты
	число коек

Состав полей таблицы "Пациент"

№	поле
	код пациента
	код палаты
	фамилия
	адрес
	год рождения
	пол

2. Типы запросов:

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

2.2. Вывести количество пациентов в каждой больнице.

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

Вариант 12. "Служащие"

1. База "Служащие" состоит из 3-х следующих таблиц: "Компания", "Профессия", "Служащий".

Состав полей таблицы "Компания"

№	поле
	код фирмы
	название фирмы
	город

Состав полей таблицы "Профессия"

№	поле
	код профессии
	название профессии

Состав полей таблицы "Служащий"

№	поле
	код cотрудника
	код фирмы
	код профессии
	фамилия
	адрес
	пол
	оклад

2. Типы запросов:

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

2.2. Вывести список фирм с количеством сотрудников для заданной профессии.

2.3. Построить перекрестный запрос с количеством мужчин, женщин каждой профессии для заданной фирмы.

Контрольные вопросы по теме

1. Понятие реляционной структуры данных. Индексы, первичные и внешние ключи.

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

3. Нормализация данных.

4. СУБД ACCESS. Назначение и области применения. Используемые типы данных.

5. СУБД ACCESS. Создание таблиц в режиме конструктора и мастера.

6. СУБД ACCESS. Создание схемы данных. Представление о целостности данных.

7. СУБД ACCESS. Формы. Создание простых и подчиненных форм в режиме мастера.

8. СУБД ACCESS. Запросы. Создание запросов в режиме конструктора и в режиме мастера.

9. СУБД ACCESS. Макросы.

10. Типы запросов.

11. СУБД ACCESS. Отчеты. Создание отчетов в режиме мастера.

12. Назначение языка SQL. Использование оператора Select для выбора информации из базы данных (Select…From…Order by …Group by).

13. Назначение языка SQL. Задание в операторе Select условий отбора данных из одной или нескольких таблиц (раздел Where).

Список литературы

1. Информатика. Под редакцией профессора Н.В. Макаровой.

2. Практикум по экономической информатике: Учеб. пособие: В 3-х ч. – Ч.I/Под ред. Е.Л. Шумерова, Н.А. Тимаковой, Е.А. Мамонтовой – М.: Перспектива, 2002. – 299 с.

3. Б.С.Послед Acсess 2002 Приложения баз данных. Лекции и упражнения. – Москва-Санкт-Петербург, 2002

4. Информационные технологии (для экономиста). Учебное пособие / Под общ. ред. А.К. Волкова – М., ИНФРА-М, 2001. – 310с.

Авторы

Доктор технических наук, профессор

Кандидат технических наук, доцент

Дата добавления: 2014-12-27