3. Создание и работа по запросам с двухтабличной бд «Авто»

Кейс-ситуация: Вы – менеджер автомобильной фирмы. Для эффективной работы с клиентами Вам необходимо небольшое приложение с данными об автомобилях и клиентах.

Цель учебного кейса: научиться разрабатывать и создавать БД из двух таблиц в режиме Конструктора заполнять ее данными, конструировать запросы к БД.

Задания и алгоритм реализации кейс-ситуации

1. Создайте файл для новой БД (кейс-компонент 3) и сохраните в папке Student/ Личная папка.

2. Создайте новую БД для условной предметной области «Автомобильная фирма», в которой будут храниться следующие сведения об информационных объектах данной предметной области:

модели (код модели; модель; мощность двигателя; цвет; количество дверей; заводская цена; транспортные издержки; предпродажная подготовка (стоимость); специальная модель (да или нет));

клиенты (код модели; номер договора; ФИО - фамилия имя и отчество; почтовый адрес; населенный пункт; телефон; дата заказа; скидка (в %)).

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

1. Выполните доработку таблицы Клиенты, построив комбинированный список для поля Код модели на основе связанной таблицы Модели. Логично бы было представить список имеющихся моделей и их кодов при заполнении поля Код модели таблицы Клиенты. Это облегчило бы ввод данных и избавило от ошибок нарушения целостности.

Выберите в окне БД таблицу Клиенты и перейдите в режим Конструктора;

Для поля Код модели в столбце Тип данных выберите Мастер подстановок;

Click по кнопке Далее в первом окне Мастера;

В следующем окне из приведенного списка таблиц выберите таблицу Модели; Click по кнопке Далее;

Из списка полей выберите поля Модель, Код модели; Click кнопку Далее; задайте вид сортировки в следующем окне;

Сбросьте флажок Скрыть ключевой столбец в следующем окне; Click по кнопке Готово.

4. Создайте схему данных (кейс-компонент 5).

5. В режиме таблицы заполните таблицы записями сначала Модели, а затем Клиенты (кейс-компонент 7), не забывая о сохранении целостности данных и ссылок: записи не должны повторяться и ключевые поля должны быть уникальными; не может у клиента быть ссылки на модель, которой нет в таблице Модели.

6. Составьте простые запросы на выборку, выдающие следующие данные:

модель и цвет;

ФИО и адрес клиента;

номер договора и дата заказа

7) В режиме Конструктора (кейс-компонент 9) составьте следующие запросы, сохранив их с заданными именами:

• Список клиентов: Список клиентов, заказавших указанную модель заданного цвета в заданном году. Процедура проектирования запроса включает в себя следующие этапы: выбор нужных полей из двух связанных таблиц (модель и цвет из таблицы Модели; ФИО, и дата заказа из таблицы Клиенты); установка критериев отбора, например, в поле дата заказа - «>01.03.2010».

• Отпускная цена: Расчет отпускной цены для каждого заказа по формуле: (заводская цена + транспортные издержки + предпродажная подготовка) * (100 - скидка)/100. Выводятся номер договора, модель и отпускная цена.

• Запрос по фамилии: Выдача данных о фамилии, номере договора, дате заказа, модели, мощности двигателя по введенной фамилии клиента (запрос с параметром).

• Запрос по мощности: Выдача данных о моделях, мощность двигателя которых больше введенной величины.

• Клиенты из Кемерово: Выдача данных о клиентах из Кемерово, имеющих скидку на заданную модель.

• Заказы: Выдача данных о заказах. Выводятся номер договора, модель, дата заказа, ФИО клиента и отпускная цена.

8. С помощью перекрестного запроса (кейс-компонент 9) подсчитайте среднюю стоимость каждой модели в заказах. Источником данных будет запрос Заказы, заголовок строки – поле модель, заголовок столбца – номер договора, вычисление среднего будет производиться по полю отпускная цена.