Пример проектирования и создания

Лабораторная работа № 9-12

БАЗЫ ДАННЫХ «ПРОКАТ АВТОМОБИЛЕЙ»

СРЕДСТВАМИ MICROSOFT ACCESS

Задание

I. Создать базу данных, содержащую следующую информацию: марка автомобиля, тип кузова, цвет кузова, количество дверей, короб­ка передач, обивка салона, другое оснащение (перечислить все до­полнительное оснащение), год выпуска автомобиля, стоимость авто­мобиля, стоимость проката за сутки, государственный регистрацион­ный знак, дата приобретения автомобиля фирмой, дата выдачи авто­мобиля, количество суток проката автомобиля, дата возврата автомо­биля (фактическая), Ф.И.О. клиента, его адрес, телефон, документ и реквизиты документа (серия, номер, когда и кем выдан).

П. Выдать справки:

1) о выданных автомобилях заданной марки на срок, более за­данного числа;

2) о выданных автомобилях в заданный период;

3) о возвращенных автомобилях за определенный месяц;

4) об автомобилях стоимостью не менее заданного числа, воз­вращенных не вовремя;

5) об автомобилях с заданным видом дополнительного оснаще­ния;

6) рассчитать каждому клиенту, вернувшему автомобиль, стои­мость проката с учетом штрафа (за 1 просроченные сутки штраф 10% от стоимости проката).

' III. Создать отчет следующего вида:

Отчет «Скидки»

 

Номер договора	Гос. Знак	Марка	Стоимость проката за сутки	Кол-во суток	Стоимость проката	Скидка	Итого

<количество договоров> <сумма> <сумма>

Примечание: Клиентам, вернувшим автомобиль в срок, сделать скидку (ес­ли срок проката более 10 дней, то скидка 5% от стоимости проката, а если срок проката более 20 дней, то скидка 10% от стоимости проката).

IV. Создать отчет следующего вида, сгруппировав записи по марке автомобиля и отсортировав по полю «ГосЗнак»:

Отчет об автомобилях с кузовом типа

Марка

 

Гос.Знак	Цвет	Количество дверей	Коробка передач	Обивка	Год выпуска	Стоимость

Итого по группе <максимум>

Итого <максимум>

 

Ход работы

/. Проектирование базы данных «ПРОКАТАВТОМОБИЛЕЙ»

При проектировании базы данных необходимо определить:

• отношения между объектами выбранной предметной области;

• количество таблиц в базе данных;

• перечень полей в каждой таблице;

• первичный ключ в каждой таблице;

• внешние ключи для связывания таблиц.

В рассматриваемой базе данных должна храниться информация о двух объектах: об автомобилях и о клиентах. Отношение между эти­ми объектами - прокат автомобиля. Так как каждый клиент может не­однократно брать автомобиль на прокат, и каждый автомобиль может неоднократно быгь выданным на прокат, то вид отношений между объектами «многие-ко-многим». Для осуществления связей между такими объектами необходимо создать таблицу для каждого объекта и третью таблицу для хранения отношения между объектами, отра­жающую информацию о выдаче в прокат автомобилей.

Таким образом, в базе данных нужно создать три таблицы: «Ав­томобили», «Клиенты», «Прокат».

Перечень полей таблицы «Автомобили» приведен в табл. 1. В ка­честве первичного ключа в данной таблице выберем поле «ГосЗнак».

Во второй таблице «Клиенты» первичным ключом будет поле «Код клиента», перечень полей приведен в табл. 2.

Третья таблица «Прокат» будет содержать информацию о прока­те автомобилей (табл. 3). Поля «ГосЗнак» и «Код клиента» являются внешними ключами и введены для связи данной таблицы с таблицами

«Автомобили» и «Клиенты», соответственно. В качестве первичного ключа таблицы «Прокат» выберем поле «Номер договора».

Таблица 1

Перечень полей таблицы «Автомобили» и их типов

 

Имя ноля Тип данных Описание
ГосЗнак	Текстовый	Государственный регистрационный знак
Марка	Текстовый	Название модели автомобиля
Тип кузова	Текстовый	Седан, универсал и т.п.
Цвет	Текстовый	Цвет кузова
Количество дверей	Числовой	Двух-, трех-, четырех- или пятидверный салон
Коробка передач	Текстовый	Автоматическая или механическая
Обивка салона	Текстовый	Велюр, кожа, ткань
Другое оснащение	MEMO	Дополнительные аксессуары
Год выпуска	Числовой	Год выпуска автомобиля заводом
Стоимость автомобиля	Денежный	Стоимость автомобиля при приобрете­нии его фирмой
Стоимость проката	Денежный	Стоимость проката автомобиля за сутки
Дата приобретения	Дата/Время	Дата приобретения автомобиля фирмой

 

Таблица 2

Перечень полей таблицы «Клиенты» и их типов

 

Имя поля	Тип данных Описание
Код клиента	Числовой	Указывается по мере заполнения таблицы
ФИО	Текстовый	Фамилия, имя, отчество клиента (полностью)
Адрес	Текстовый	Указывается полный адрес регистрации клиента
Телефон	Текстовый	Номер телефона для связи с клиентом
Документ	Текстовый	Указывается наименование документа, предъяв­ленного при оформлении договора
Реквизиты	Текстовый	Серия, номер, когда и кем выдан предъявленный документ

Таблица 3

Перечень полей таблицы «Прокат» и их типов

 

Имя поля	Тип данных	Описание
Номер договора	Числовой	Порядковый номер договора
ГосЗнак	Числовой	Берется из таблицы «Автомобили»
Код клиента	Числовой	Берется из таблицы «Клиенты»
Дата выдачи	Дата/Время	Указывается дата заключения договора
Кол-во суток	Числовой	Указывается количество суток проката авто­мобиля
Дата возврата	Дата/Время	Указывается непосредственно при возвраще­нии автомобиля

 

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

Так как все первичные ключи в таблицах простые, то база данных будет находиться и во второй нормальной форме. Все поля каждой таблицы зависят от соответствующих первичных ключей напрямую, поэтому база данных находится и в третьей нормальной форме.

II Создание базы данных «ПРОКАТАВТОМОБИЛЕЙ»

Создать в Microsoft Access новую базу данных и назвать ее «Про­кат автомобилей».

Создать с помощью конструктора таблицу «Автомобили», вклю­чающую нужные поля (табл. 1), Установить свойства полей (табл. 4).

Таблица 4 Свойства некоторых полей таблицы «Автомобили»

 

 

Имя поля	Свойство	Значение свойства
Год выпуска	Размер поля	Целое
Маска ввода
Стоимость автомобиля	Формат поля	Денежный
Стоимость проката	Формат поля	Денежный
Дата приобретения	Формат поля	Краткий формат даты
Маска ввода	00.00.00

 

Для поля «Тип кузова» необходимо создать список подстановки для того, чтобы упростить ввод данных в. таблицу. Для этого предва­рительно нужно создать вспомогательную таблицу «Типы кузовов», состоящую из одного текстового поля «Тип» и ввести названия раз­личных типов кузовов.

За тем в таблице «Автомобили» для поля «Тип кузова» в окне свойств выбрать вкладку Подстановка (Lookup), в строке «Тип эле­мента управления» (Display Control) выбрать Список (List Box), в строке «Тип источника строк» (Row Source Type) выбрать Таблица или запрос (Table/Query), в строке «Источник строк» (Row Source) выбрать таблицу «Типы кузовов».

Аналогично создать список подстановки для полей «Коробка пе­редач» и «Обивка салона». Поле «ГосЗнак» сделать ключевым. Внешний вид таблицы «Ав­томобили» в режиме конструктора представлен на рис. 1.

Рис. 1. Таблица «Автомобили» в режиме конструктора

Аналогично создать с помощью конструктора таблицу «Клиен­ты», включающую нужные поля (см. табл. 2), поле «Код клиента» сделать ключевым.

Внешний вид таблицы «Клиенты» в режиме конструктора пред­ставлен на рис. 2.

Рис. 2. Таблица «Клиенты» в режиме конструктора

Создать с помощью конструктора таблицу «Прокат», включающую нужные поля (см. табл. 3), установить для некоторых полей необходи­мые свойства (табл. 5). Поле «Номер договора» сделать ключевым.

Таблица 5

Внешний вид таблицы «Прокат» в режиме конструктора пред­ставлен на рис. 3.

Рис. 3. Таблица «Прокат» в режиме конструктора

Связать таблицы согласно схеме (рис. 4), установив опции «Обеспечение целостности данных» (Enforce Referential Integrity), «Каскадное обновление связанных полей» (Cascade Update Related Fields), «Каскадное удаление связанных полей» (Cascade Delete Re­lated Records).

Заполнить таблицы данными (см. задание стр. 6)

Рис. 4. Схема данных

III. Создание форм для редактирования таблиц

 

Создать для таблицы «Автомобили» форму с помощью мастера форм. Открыть получившуюся форму в режиме конструктора и соз­дать на ней кнопки «Добавить запись», «Удалить запись» и «Закрыть форму», указав соответствующие действия в мастере создания кно­пок. Внешний вид формы для редактирования таблицы «Автомоби­ли» показан на рис. 5.

Рис. 5. Форма для редактирования таблицы «Автомобили» в режиме конструктора

Аналогично создать форму «Клиенты» и добавить на нее необхо­димые кнопки. Внешний вид формы для редактирования таблицы «Клиенты» показан на рис. 6.

Рис. 6. Форма для редактирования таблицы «Клиенты»

Аналогично создать форму «Прокат» и добавить на нее необхо­димые кнопки. Внешний вид формы для редактирования таблицы «Прокат» показан на рис. 7»

Рис. 7. Форма для редактирования таблицы «Прокат»

IV. Создание запросов

Запрос 1. Справки о выданных автомобилях заданной марки на срок, более заданного числа.

Создать запрос на выборку, добавить в него все таблицы базы дан­ных. Перенести в бланк запроса поля: «Номер договора», «ГосЗнак», «Марка», «ФИО», «Дата выдачи», «Кол-во суток», «Дата возврата». Ввести для полей «Марка» и «Кол-во суток» критерии отбора в виде па­раметров. Для полей «Марка» и «Количество суток» в строке «Условие отбора» ввести необходимый критерий отбора в виде параметра.

Внешний вид фрагмента данного запроса в режиме конструктора представлен на рис. 8.

Рис. 8. Запрос 1 в режиме конструктора

Запрос 2. Справки о выданных автомобилях в заданный период.

Создать запрос на выборку, добавить в него таблицу «Прокат». Перенести в бланк запроса поля: «Номер договора», «ГосЗнак», «Код клиента», «Дата выдачи», «Кол-во суток». Ввести для поля «Дата вы­дачи» критерий отбора в виде параметра. Внешний вид данного за­проса в режиме конструктора представлен на рис. 9.

Запрос 3. Справки о возвращенных автомобилях за определен­ный месяц.

Создать запрос на выборку, добавить в него таблицу «Прокат». Перенести в бланк запроса поля: «Номер договора», «ГосЗнак», «Код клиента», «Дата выдачи», «Кол-во суток», «Дата возврата».

Создать вычисляемое поле, в котором вычислить месяц от даты возврата автомобиля. Ввести для созданного поля критерий отбора в виде параметра, отключить вывод на экран. Внешний вид данного за­проса в режиме конструктора представлен на рис. 10.

Рис. 9. Запрос 2 в режиме конструктора

Рис. 10. Запрос 3 в режиме конструктора

Запрос 4. Справки об автомобилях стоимостью не менее задан­ного числа, возвращенных не вовремя.

Создать запрос на выборку, добавить в него таблицы «Прокат» и «Автомобили». Перенести в бланк запроса поля: «ГосЗнак», «Марка»,

«Стоимость автомобиля», «Номер договора», «Код клиента», «Дата выдачи», «Кол-во суток», «Дата возврата».

Ввести для поля «Стоимость автомобиля» критерий отбора в ви­де параметра. Для поля «Кол-во суток» в строке «Условие отбора» создать выражение, вычисляющее разность меду датой возврата и да­той выдачи автомобиля. Внешний вид данного запроса в режиме кон­структора представлен на рис. 11.

Рис. 11. Запрос 4 в режиме конструктора

Запрос 5. Справки об автомобилях с заданным видом дополни­тельного оснащения.

Создать запрос на выборку, добавить в него таблицу «Автомобиль». Перенести в бланк запроса поля: «ГосЗнак», «Марка», «Цвет», «Тип ку­зова», «Год выпуска», «Стоимость проката», «Другое оснащение».

-Создать вычисляемое поле, в котором проверить вхождение за­данного текста (параметра) в поле «Другое оснащение». Отключить для вычисляемого поля вывод на экран. Внешний вид данного запро­са в режиме конструктора представлен на рис. 12.

Запрос 6. Рассчитать каждому клиенту, вернувшему автомобиль, стоимость проката с учетом штрафа (за 1 просроченные сутки штраф 10% от стоимости проката).

Создать вспомогательный запрос на выборку «Возврат автомоби­ля», позволяющий определить, вернул ли клиент автомобиль. Добавить в запрос таблицы «Автомобили» и «Прокат». Перенести в бланк запроса все поля используемых таблиц.

Рис. 12. Запрос 5 в режиме конструктора

Создать вычисляемое поле, в котором проверить, вернул ли кли­ент автомобиль. Внешний вид данного запроса в режиме конструкто­ра представлен на рис. 13.

Рис. 13. Вспомогательный запрос в режиме конструктора

Создать запрос на выборку на основе запроса «Возврат автомо­биля», перенести в бланк запроса поля: «ГосЗнак», «Номер договора»,

«Код клиента», «Стоимость проката», «Дата выдачи», «Кол-во су­ток», «Дата возврата».

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