 |
|
Задание на выполнение
Номер варианта задания выбирается по последней M и предпоследней N цифрам номера студенческого билета.
4.2.1 В СУБД Access создать базу данных, состоящую их трех связанных таблиц – «Абоненты», «Заказы» и «Услуги». При этом исходными данными являются коды абонентов с их реквизитами, коды категорий абонентов, виды и тарифы услуг, на которые имеет право абонент заданной сети, а также время и продолжительность разговора или услуги. В таблицах 4.1 и 4.2 приведены исходные данные, в зависимости от номера варианта.
Таблица 4.1.
| M | КодУслуги | Сеть | |
| 01И, 02И, 03В, 04В, 05И, 08М, 09М, 12In, 13S, 14S | Мегафон | ||
| 01И, 02И, 03В, 04В, 05И, 07М, 09М, 11М, 15Д, 16Д | Смартс | ||
| 01И, 02И, 03В, 04В, 05И, 06И, 11М, 12In, 13S, 14S | Би Лайн | ||
| 01И, 02И, 03В, 04В, 06И, 07М, 08М, 10М, 15Д, 16Д | МТС | ||
| 01И, 02И, 03В, 04В, 06И, 08М, 09М, 11М, 13S, 14S | Стриж | ||
| 01И, 02И, 03В, 04В, 06И, 09М, 10М, 12In, 15Д, 16Д | Мегафон | ||
| 01И, 02И, 03В, 04В, 07М, 08М, 11М, 12In, 13S, 14S | Смартс | ||
| 01И, 02И, 03В, 04В, 07М, 09М, 10М, 11М, 15Д, 16Д | Би Лайн | ||
| 01И, 02И, 03В, 04В, 07М, 08М, 09М, 10М, 11In, 13S | МТС | ||
| 01И, 02И, 03В, 04В, 05И, 08М, 10М, 12In, 13S, 14S | Стриж |
Таблица 4.2
| Услуги сотовой связи | Би Лайн | Мегафон | Смартс | ||||
| КодУ | Наименование Услуги | ТарифД | ТарифН | ТарифД | ТарифН | ТарифД | ТарифН |
| 01И | Исходящие вызовы на мобильный телефон | 0,85р. | 0,70р. | 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,50р. | 0,50р. | 0,50р. | 0,50р. | 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,00р. | 7,00р. | 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 | Мобильный интернет | 0,78р. | 0,75р. | 0,70р. | 0,70р. | 0,75р. | 0,70р. |
| 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 | Мобильный интернет | 0,68р. | 0,65р. | 0,80р. | 0,80р. | ||
| 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р. |
ТарифТ – стоимость в рублях одной полной минуты разговора для абонентов Самарской области с сентября 2012 года в интервале времени Т (ТарифД – время с 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 | Задания |
| Исходящие вызовы на мобильный телефон и любимый № | |
| Исходящие местные вызовы и входящие остальные | |
| Входящие и исходящие вызовы с мобильных телефонов | |
| Входящие остальные и SMS сообщения | |
| Все исходящие и все входящие | |
| Междугородние вызовы | |
| Все исходящие и входящие с мобильных телефонов | |
| Все исходящие и все междугородние | |
| Все входящие | |
| Все входящие и все междугородние |
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 | Задачи |
| Список заказов за первые 3 дня недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «родственники» | |
| Наименьшую по длительности услугу для каждой группы кодов услуг по междугородним вызовам | |
| Количество услуг, оказанных при связи с первым (01) абонентом, стоимость каждой из которых превышает 10 руб | |
| Список заказов за последние 2 дня недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «знакомые» | |
| Фамилию абонента, разговор с которым был самым коротким | |
| Количество услуг, оказанных категориям абонентов «сокурсники/сослуживцы» | |
| Список заказов за 2, 3 и 5 день недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «сокурсники /сослуживцы» | |
| Наименьшую стоимость из оказанных услуг для каждой группы кодов услуг по входящим вызовам | |
| Количество услуг, оказанных за неделю по всем междугородним разговорам | |
| Список заказов за нечетные дни недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «друзья» и «деловые партнеры» | |
| Наибольшую стоимость из оказанных услуг для каждой группы кодов услуг по междугородним вызовам | |
| Количество оказанных услуг, тариф которых не превышает 5 руб/мин | |
| Список заказов за первый и последний день недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «знакомые» | |
| Наименьший тариф для каждой группы кодов услуг по междугородним вызовам | |
| Количество услуг, оказанных при связи со вторым (02) абонентом, стоимость каждой из которых не превышает 20 руб | |
| Список заказов за средние 3 дня недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «деловые партнеры» | |
| Средняя стоимость оказанных услуг для каждой группы кодов услуг по исходящим вызовам | |
| Количество оказанных услуг, тариф которых превышает 5 руб/мин | |
| Список заказов за первые два и последние два дня недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «сокурсники (сослуживцы)» | |
| Наибольшую по длительности услугу для каждой группы кодов услуг по исходящим вызовам | |
| Количество оказанных услуг, тариф которых превышает 10 руб/мин | |
| Список заказов за последние 3 дня недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «знакомые» и «родственники» | |
| Средние тарифы для каждой группы кодов исходящих услуг | |
| Количество оказанных исходящих услуг, длительность которых превышает 3 мин | |
| Список заказов за четные дни недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «знакомые» и «сокурсники (сослуживцы)» | |
| Фамилию абонента с самым продолжительным разговором | |
| Количество услуг, оказанных категории абонентов «родственники» | |
| Список заказов за 3, 5 и 6 день недели | |
| Список наименования услуг, даты их предоставления с категорией абонентов «знакомые» и «деловые партнеры » | |
| Средняя стоимость оказанных услуг для каждой группе кодов услуг за междугородние разговоры | |
| Количество абонентов, длительность разговора с которыми превышает 2 минуты |
Примечание 2: в первой задаче каждого варианта дни недели представлять конкретными датами на предоставляемые услуги.
4.2.10. С помощью Мастера форм создать форму «Абоненты» – для четных M и форму «Услуги» – для нечетных M.
4.2.11. Построить диаграмму стоимости услуг абонентов Вашей базы данных. Для четных вариантов M вид диаграммы – кольцевая, а для нечетных M – круговая.
4.2.12. Согласно варианту N построить временную диаграмму, отражающую зависимость показателя, заданного в табл. 4.5, за истекшую неделю. На диаграмме отобразить линию тренда, а так же закон и точность аппроксимации.
Табл. 4.5
| N | Функциональная зависимость |
| ежедневную суммарную стоимость предоставляемых услуг | |
| ежедневный максимальный тариф предоставляемых услуг | |
| ежедневное среднее значение стоимости предоставляемых услуг | |
| ежедневную максимальную стоимость предоставляемых услуг | |
| ежедневную минимальную стоимость предоставляемых услуг | |
| ежедневную суммарную длительность предоставляемых услуг | |
| ежедневный средний тариф предоставляемых услуг | |
| ежедневное среднее значение длительности предоставляемых услуг | |
| ежедневную максимальную длительность предоставляемых услуг | |
| ежедневную минимальную длительность предоставляемых услуг |
Примечание 3: варианты рассматриваются относительно заданной недели.
4.2.13. Подготовить отчет об услугах, предоставленных абоненту мобильной связи за текущую неделю в следующем виде:
Отчет об услугах, предоставленных абоненту <Ваша Фамилия>:
| Дата | Ф И О | Телефон | Код Услуги | Длит/мин | Стоимость |
| 05.12.2011 | Серов А. И. | 01И | 3,5 | 2,50р. | |
| … | … | … | … | … | |
| … | … | … | … | … | … |
| … | … | … | … | … | … |
| 12.12.2011 | Киса Е. С. | 10M | 1,50 | 44,25р. |
К оплате: ХХХ,ХХр.
Для этого сначала на основе таблицы «Абоненты» и запроса «ЗапрСтоимость» создать соответствующий запрос «ЗапрОтчет». В режиме конструктора между запросами «ЗапрБазовый» и «ЗапрСтоимость» создать связи по соответствующим полям (см. Примечание 1). В этом же запросе создать вычисляемое поле Ф И О, в которое внести выражение для объединения полей Фамилия, Имя, Отчество в одно поле. При объединении полей в выражении учесть, что фамилия должна быть представлена полностью, а имя и отчество с сокращением – только первые символы с точкой после сокращения имени и отчества (например, Иванов С. М.). Полученное поле Ф И О, согласно заданию,расположить на место второго столбца. Отсортировать запрос по полю Дата. Затем на основе созданного запроса создать требуемый отчет.