3.5 Обеспечение целостности бд

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

• либо исключить возможность удаления или изменения данных в ключевом поле главной таблицы, если с этим полем связаны какие-либо поля других таблиц;

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

3.6. Производительность СУБД

Производительность СУБД оценивается следующими факторами:

• временем выполнения запросов, операций импортирования данных из других форматов, временем генерации отчёта;

• скоростью поиска информации, скоростью выполнения операций обновления, вставка, удаление данных;

• максимальным числом параллельных обращений к данным в многопользовательском режиме.

4. Контрольная работа №2

«Обработка информации средствами СУБД»

4.1 Постановка задачи

Используя возможности СУБД MS Access или Open Office Base, требуется рассчитать оплату услуг мобильной связи за период времени в одну неделю. Для этого необходимо:

• создать базу данных из связанных таблиц,

• подготовить запросы для вычисления стоимости телефонных разговоров абонентов по заданным услугам,

• создать формы для удобства работы с базой данных, построить диаграммы,

• вывести бланк квитанции оплаты за услуги мобильной связи.

4.2 Задание на выполнение

Номер варианта задания выбирается по последней M и предпоследней N цифрам номера студенческого билета.

4.2.1 В СУБД Access создать базу данных, состоящую их трех связанных таблиц – «Абоненты», «Заказы» и «Услуги». При этом исходными данными являются коды абонентов с их реквизитами, коды категорий абонентов, виды и тарифы услуг, на которые имеет право абонент заданной сети, а также время и продолжительность разговора или услуги. В таблицах 4.1 и 4.2 приведены исходные данные, в зависимости от номера варианта.

Таблица 4.1.

M	КодУслуги	Сеть
0	01И, 02И, 03В, 04В, 05И, 08М, 09М, 12In, 13S, 14S	Мегафон
1	01И, 02И, 03В, 04В, 05И, 07М, 09М, 11М, 15Д, 16Д	Смартс
2	01И, 02И, 03В, 04В, 05И, 06И, 11М, 12In, 13S, 14S	Би Лайн
3	01И, 02И, 03В, 04В, 06И, 07М, 08М, 10М, 15Д, 16Д	МТС
4	01И, 02И, 03В, 04В, 06И, 08М, 09М, 11М, 13S, 14S	Стриж
5	01И, 02И, 03В, 04В, 06И, 09М, 10М, 12In, 15Д, 16Д	Мегафон
6	01И, 02И, 03В, 04В, 07М, 08М, 11М, 12In, 13S, 14S	Смартс
7	01И, 02И, 03В, 04В, 07М, 09М, 10М, 11М, 15Д, 16Д	Би Лайн
8	01И, 02И, 03В, 04В, 07М, 08М, 09М, 10М, 11In, 13S	МТС
9	01И, 02И, 03В, 04В, 05И, 08М, 10М, 12In, 13S, 14S	Стриж

Таблица 4.2

Услуги сотовой связи		Би Лайн		Мегафон		Смартс
КодУ	Наименование Услуги	Тариф_Д	Тариф_Н	Тариф_Д	Тариф_Н	Тариф_Д	Тариф_Н
01И	Исходящие вызовы на мобильный телефон	0,85р.	0,7р.	0,75р.	0,55р.	1,80р.	1,65р.
02И	Исходящие местные вызовы	1,96р.	1,25р.	1,20р.	1,00р.	1,50р.	1,30р.
03В	Входящие вызовы с мобильных телефонов	0,00р.	0,00р.	0,00р.	0,00р.	0,00р.	0,00р.
04В	Входящие остальные	1,96р.	1,25р.	1,20р.	1,00р.	1,50р.	1,50р.
05И	Исходящие вызовы на 1 любимый номер	0,5р.	0,5р.	0,5р.	0,5р.	0,45р.	0,45р.
06И	Исходящие вызовы на голосовую почту	0,48р.	0,48р.	0,93р.	0,93р.	0,50р.	0,50р.
07М	Междугородние вызовы абонентам сети	1,54р.	1,54р.	0,90р.	0,90р.	0,85р.	0,85р.
08М	Междугородние вызовы в Москву	7,08р.	7,08р.	7,86р.	7,86р.	10,00р.	10,00р.
09М	Междугородние вызовы по России	10,62р.	10,62р.	11,80р.	11,80р.	14,00р.	14,00р.
10М	Междугородние вызовы стран СНГи Европы	35,40р.	35,40р.	29,50р.	29,50р.	37,00р.	37,00р.
11М	Междугородние вызовы остальной мир	86,20р.	86,20р.	68,83р.	68,83р.	78,00р.	78,00р.
12In	Мобильный интернет	7,80р.	7,80р.	7,00р.	7,00р.	7,60р.	7,60р.
13S	Исходящее SMS (за каждое сообщение)	1,05р.	1,05р.	1,05р.	1,05р.	1,00р.	1,00р.
14S	Входящее SMS (за каждое сообщение)	0,00р.	0,00р.	0,00р.	0,00р.	0,00р.	0,00р.
15Д	Передача данных	1,45р.	1,45р.	1,47р.	1,47р.	1,50р.	1,50р.
16Д	Прием данных внутри сети	0,00р.	0,00р.	0,00р.	0,00р.	0,00р.	0,00р.

Продолжение табл. 4.2

Услуги сотовой связи		МТС		Стриж
КодУ	Наименование Услуги	Тариф_Д	Тариф_Н	Тариф_Д	Тариф_Н
01И	Исходящие вызовы на мобильный телефон	0,40р.	0,40р.	1,00р.	1,00р.
02И	Исходящие местные вызовы	1,96р.	1,48р.	1,00р.	0,50р.
03В	Входящие вызовы с мобильных телефонов	0,00р.	0,00р.	0,00р.	0,00р.
04В	Входящие остальные	1,96р.	1,48р.	1,00р.	1,00р.
05И	Исходящие вызовы на 1 любимый номер	0,40р.	0,40р.	0,50р.	0,50р.
06И	Исходящие вызовы на голосовую почту	1,20р.	1,20р.	1,60р.	1,20р.
07М	Междугородние вызовы абонентам сети	1,96р.	1,96р.	1,50р.	1,50р.
08М	Междугородние вызовы в Москву	13,5р.	13,5р.	4,80р.	3,40р.
09М	Междугородние вызовы по России	12,5р.	12,5р.	9,30р.	9,90р.
10М	Междугородние вызовы стран СНГи Европы	30,0р.	30,0р.	28,50р.	28,50р.
11М	Междугородние вызовы остальной мир	66,2р.	66,5р.	68,80р.	68,80р.
12In	Мобильный интернет	6,8р.	6,8р.	8,00р.	8,00р.
13S	Исходящее SMS (за каждое сообщение)	1,10р.	1,10р.	1,00р.	1,00р.
14S	Входящее SMS (за каждое сообщение)	0,00р.	0,00р.	0,00р.	0,00р.
15Д	Передача данных	1,96р.	1,48р.	1,00р.	0,80р.
16Д	Прием данных внутри сети	0,00р.	0,00р.	0,00р.	0,00р.

Тариф_Т – стоимость в рублях одной полной минуты разговора для абонентов Самарской области с сентября 2011 года в интервале времени Т (ТарифД – время с 08:00 до 23:59, ТарифН – время с 00:00 до 07:59).

4.2.2 Таблица «Абоненты» должна отражать список ваших товарищей, родственников, знакомых, сокурсников/сослуживцев (порядка 5-7 человек), а так же организаций, с которыми вы имеете деловые/ производственные телефонные переговоры (порядка 3). В таблице предусмотреть поля: КодАбонента, Фамилия, Имя, Отчество, Адрес, Телефон, Категория. Тип данных поля КодАбонента – Числовой, остальных полей – Текстовый. Абоненты могут принадлежать разным телефонным службам. Код абонента – его порядковый номер в данной таблице. Категория Абонента – закодированные отношения с абонентом (Р-родственник, Д-друг, П-партнер, З-знакомый, С-сокурсник, О-особый, Л-лучшие друзья и т.п). В качестве ключевого поля задать поле Код Абонента.

4.2.3. Таблица «Услуги» должна отражать перечень услуг (10 записей), заданных вариантом (см. табл.4.1) и содержать поля: КодУслуги, НаименованиеУслуги, Тариф_Д, Тариф_Н (согласно табл.4.1 и 4.2). Тип данных полей Тариф_Т установить Денежный, а КодУслуги – Текстовый. В качестве ключевого поля задать поле КодУслуги.

4.2.4. Таблица «Заказы» должна содержать для каждого закодированного абонента некоторые виды услуг из таблицы «Услуги» за период в 1 неделю, предшествующую моменту выполнения задания. В таблице предусмотреть поля: КодАбонента, КодУслуги, Дата, Начало, Окончание. Тип данных полей Дата, Начало, Окончание установить Дата/время (формат поля Дата – краткий формат даты, формат полей Начало, Окончание – длинный формат времени). В качестве ключевых полей задать поля КодАбонента и КодУслуги.

При заполнении таблицы «Заказы» по каждому из абонентов предусмотреть:

• разные виды услуг (порядка 2-3 видов) из таблицы «Услуги»;

• произвольные даты предоставления услуг за выбранную неделю (в формате DD.MM.YY);

• произвольное время начала разговора в формате HH:MM:SS (включая ночное и дневное время предоставления услуг);

• произвольное время окончания разговора в формате HH:MM:SS, но с учетом реальной длительности оказания услуги. При этом длительность услуги в основном не должна быть равна целому числу минут.

Если в варианте присутствует SMS сообщение, то время передачи сообщения принять за 1мин.

4.2.5. Связать таблицы между собой по соответствующим ключевым полям.

4.2.6. Отсортировать таблицу «Абоненты» в алфавитном порядке.

4.2.7. Используя фильтр по варианту N, в таблице «Заказы» отобразить только записи заказов, указанных в табл. 4.3.

Таблица 4.3

N	Задания
0	Исходящие вызовы на мобильный телефон и любимый №
1	Исходящие местные вызовы и входящие остальные
2	Входящие и исходящие вызовы с мобильных телефонов
3	Входящие остальные и SMS сообщения
4	Все исходящие и все входящие
5	Междугородние вызовы
6	Все исходящие и входящие с мобильных телефонов
7	Все исходящие и все междугородние
8	Все входящие
9	Все входящие и все междугородние

4.2.8. Рассчитать стоимость оказанных услуг по всем заказам за заданный период времени. Для этого, сначала подготовить базовый запрос – «ЗапрБазовый», в котором рассчитать длительность разговора по каждому пункту заказов. Запрос готовиться на основе таблицы «Заказы», и должен содержать поля: КодАбонента, КодУслуги, Дата, Начало, Окончание предоставляемой услуги. В запросе создать вычисляемые поля Часы, Минуты, Секунды, ДлитРазговора, где с помощью стандартных функций вычислить количество часов (функция Hour()) в формате HH, минут (функция Minute()) в формате MM, секунд (функция Second()) в формате SS и продолжительность разговора (функция TimeSerial()) в формате времени HH:MM:SS. Кроме того, создать поле Длит/мин, в которое ввести выражение [ДлитРазговора]*24*60, пересчитывающее формат времени HH:MM:SS в десятичный эквивалент – минуты, т.к. тарифы оплаты за предоставляемые услуги заданы в рублях за 1 полную минуту, а реальный разговор, как правило, длится несколько минут с секундами. С помощью функции Round() округлить выражение в поле Длит/ мин с точностью до 2 знаков после запятой.

Отсортировать запрос в порядке возрастания продолжительности (Длин/мин) предоставляемых услуг.

На основе базового запроса и таблицы «Услуги» создать новый запрос – «ЗапрСтоимость» для расчета стоимости услуг, предоставляемых абоненту заданной мобильной сети за истекшую неделю. Этот запрос должен содержать в своем составе поля КодАбонента, КодУслуги, Дата, Начало, Длит/мин из базового запроса и поля Тариф_Д, Тариф_Н из таблицы «Услуги». В запросе создать вычисляемое поле Стоимость, в которое ввести выражение для расчета стоимости предоставляемых услуг с учетом длительности разговора и тарифов на предоставляемые услуги. Для построения выражения использовать функцию IIf(<условие>, <выражение1>, <выражение 2>). Установить формат поля Стоимость – Денежный.

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

Отсортировать «ЗапрСтоимость» по дате и по длительности в порядке возрастания их значений.

4.2.9. В соответствии с номером варианта M (табл. 4.4) подготовить запросы на отбор соответствующей информации из базы данных за истекшую неделю. Сохранить запросы под именами ЗапрЗадача1, …, ЗапрЗадача4.

Таблица 4.4

M	Задачи
0	Список заказов за первые 3 дня недели
	Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «родственники»
	Наименьшую по длительности услугу для каждой группы кодов услуг по междугородним вызовам
	Список услуг, оказанных при связи с первым (01) абонентом, стоимость каждой из которых превышает 10 руб
1	Список заказов за последние 2 дня недели
	Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «знакомые»
	Фамилию абонента, разговор с которым был самым коротким
	Список услуг, оказанных категориям абонентов «сокурсники/сослуживцы»
2	Список заказов за 2, 3 и 5 день недели
	Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «сокурсники /сослуживцы»
	Наименьшую стоимость из оказанных услуг для каждой группы кодов услуг по входящим вызовам
	Список услуг, оказанных за неделю по всем междугородним разговорам
3	Список заказов за нечетные дни недели
	Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «деловые партнеры»
	Наибольшую стоимость из оказанных услуг для каждой группы кодов услуг по междугородним вызовам
	Количество оказанных услуг, тариф которых не превышает 5 руб/мин
4	Список заказов за первый и последний день недели
	Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «знакомые»
	Наименьший тариф для каждой группы кодов услуг по междугородним вызовам
	Список услуг, оказанных при связи со вторым (02) абонентом, стоимость каждой из которых не превышает 20 руб
5	Список заказов за средние 3 дня недели
	Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «деловые партнеры»
	Средняя стоимость оказанных услуг для каждой группы кодов услуг по исходящим вызовам
	Список оказанных услуг, тариф которых превышает 5 руб/мин
6	Список заказов за первые два и последние два дня недели
	Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «сокурсники (сослуживцы)»
	Наибольшую по длительности услугу для каждой группы кодов услуг по исходящим вызовам
	Список оказанных услуг, тариф которых превышает 10 руб/мин
7	Список заказов за последние 3 дня недели
	Список наименования услуг, даты их предоставления с категорией абонентов «знакомые» и «родственники»
	Средние тарифы для каждой группы кодов исходящих услуг
	Количество оказанных исходящих услуг, длительность которых превышает 3 мин
8	Список заказов за четные дни недели
	Список наименования услуг, даты их предоставления с категорией абонентов «знакомые» и «сокурсники (сослуживцы)»
	Фамилию абонента, разговор с которым был самым продолжительным
	Список услуг, оказанных категории абонентов «родственники»
9	Список заказов за 3, 5 и 6 день недели
	Список наименования услуг, даты их предоставления с категорией абонентов «знакомые» и «деловые партнеры »
	Средняя стоимость оказанных услуг для каждой группе кодов услуг за междугородние разговоры
	Список абонентов, длительность разговора с которыми превышает 2 минуты

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

4.2.10. С помощью Мастера форм создать форму «Абоненты» – для четных M и форму «Услуги» – для нечетных M.

4.2.11. Построить диаграмму стоимости услуг абонентов Вашей базы данных. Для четных M вид диаграммы – кольцевая, а для нечетных M – круговая.

4.2.12. Согласно варианту N построить временную диаграмму, отражающую зависимость показателя, заданного в табл. 4.5, за истекшую неделю. На диаграмме отобразить линию тренда, а так же закон и точность аппроксимации.

Табл. 4.5

N	Функциональная зависимость
0	ежедневную суммарную стоимость предоставляемых услуг
1	ежедневный максимальный тариф предоставляемых услуг
2	ежедневное среднее значение стоимости предоставляемых услуг
3	ежедневную максимальную стоимость предоставляемых услуг
4	ежедневную минимальную стоимость предоставляемых услуг
5	ежедневную суммарную длительность предоставляемых услуг
6	ежедневный средний тариф предоставляемых услуг
7	ежедневное среднее значение длительности предоставляемых услуг
8	ежедневную максимальную длительность предоставляемых услуг
9	ежедневную минимальную длительность предоставляемых услуг

Примечание 3: варианты рассматриваются относительно заданной недели.

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

Отчет об услугах, предоставленных абоненту <Ваша Фамилия>:

Дата	Ф И О	Телефон	Код Услуги	Длит/мин	Стоимость
05.12.2011	Серов А. И.	321911	01И	3,5	2,50р.
…	…	…		…	…
…	…	…	…	…	…
…	…	…	…	…	…
12.12.2011	Киса Е. С.	280025	10M	1,50	44,25р.

К оплате: ХХХ,ХХр.

Для этого сначала на основе таблицы «Абоненты» и запроса «ЗапрСтоимость» создать соответствующий запрос «ЗапрОтчет». В режиме конструктора между запросами «ЗапрБазовый» и «ЗапрСтоимость» создать связи по соответствующим полям (см. Примечание 1). В этом же запросе создать вычисляемое поле Ф И О, в которое внести выражение для объединения полей Фамилия, Имя, Отчество в одно поле. При объединении полей в выражении учесть, что фамилия должна быть представлена полностью, а имя и отчество с сокращением – только первые символы с точкой после сокращения имени и отчества (например, Иванов С. М.). Полученное поле Ф И О, согласно заданию, расположить на место второго столбца. Отсортировать запрос по полю Дата. Затем на основе созданного запроса создать требуемый отчет.