База данных кредитного отдела банка

ЛАБОРАТОРНАЯ РАБОТА

«БАЗА ДАННЫХ КРЕДИТНОГО ОТДЕЛА БАНКА»

(читайте по ходу сноски!!!!)

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

Задание.

1. В MS Access создайте новую базу данных. В качестве имени БД введите свою фамилию.
2. Создайте структуру таблицы «Кредитные договоры», состоящую из полей: Номер кредитного договора (ключ)¹, Наименование клиента, Телефон (установите маску ввода с учетом кода Москвы), Статус (установите значения: юридическое и физическое лицо; а также условие на значение), Отраслевая принадлежность, Номер лицевого счета²,  Код валюты, Условия погашения (установите значение по умолчанию «ежемесячно», а условие на значение «ежемесячно» или «единовременно»), Процент неустойки по платежам, Процент неустойки по процентам, Группа риска (установите значения), Вид кредита (установите значение по умолчанию – краткосрочный).
3. Создайте структуру таблицы «Обеспечение кредита» – поля: Номер кредитного договора (ключ), Код вида обеспечения, Стоимость (в рублях).
4. Создайте структуру таблицы «Вид обеспечения» – поля: Код вида обеспечения (ключ), Наименование вида обеспечения.
5. Создайте структуру таблицы «Валюта»³ – поля: Код валюты (ключ), Наименование валюты, Курс.
6. Установите связи между таблицами⁴.
7. Заполните исходными данными таблицы «Валюта», «Вид обеспечения», «Обеспечение кредита».
8. Создайте форму на основании таблицы «Кредитные договоры».
9. Заполните таблицу «Кредитные договоры» при помощи полученной формы.
10. Экспортируйте таблицу «Кредитные договоры» в табличный процессор MS Excel и присвойте ей имя «Подробности кредитного договора».
11. Измените структуру таблицы «Подробности кредитного договора», оставив поля: Номер кредитного договора и Наименование клиента; и добавив поля: Сумма кредита, Процентная ставка, Дата заключения договора, Дата прекращения договора, Срок погашения (рассчитывается с помощью функций «даты и время»), Размер ежемесячной выплаты по кредиту (рассчитывается с помощью финансовой функции ПЛТ()).
12. В табличном процессоре MS Excel создайте таблицу «Календарь выплат», состоящую из следующих полей: Месяц, Номер кредитного договора, Размер ежемесячной выплаты по кредиту, Сумма ежемесячного платежа, погашающая основной долг, по плану (рассчитывается с помощью финансовой функции ОСПЛТ()),Сумма ежемесячного платежа, погашающая основной долг, по факту, Сумма по выплачиваемым процентам  по плану (рассчитывается с помощью финансовой функции ПРПЛТ()),Сумма по выплачиваемым процентам по факту.
13. Импортируйте таблицы «Подробности кредитного договора» и «Календарь выплат» в созданную базу данных с помощью команды (Файл — Внешние данные — Импорт … и последующей серии шагов, предлагаемой системой). Примечание: при импортировании исключите повторяющиеся поля.
14. Создайте связи с новыми таблицами.
15. Создайте запрос на обновление о замене номера кредитного договора хххх5 на хххх55.
16. Создайте запрос, содержащий полную информацию по физическим и юридическим лицам (Примечание: необходимо в конструкторе отметить сортировку по полю «Статус»).
17. Создайте параметрический запрос «Текущие платежи», отражающий информацию по текущим платежам всех клиентов до определенного месяца и содержащий следующие поля Номер кредитного договора, Наименование клиента, Платеж по основному долгу по плану, Платеж по основному долгу по факту, Выплата процентов по плану, Выплата процентов по факту, Сумма ежемесячного платежа по плану, Сумма ежемесячного платежа по факту.
18. Создайте запрос «Погашение кредита», содержащий графы: Номер кредитного договора, Наименование клиента, Внесено в уплату основного долга (фактически), Уплачено процентов (фактически), Всего внесено, Количество взносов (с использованием функции Count), Осталось внести в уплату основного долга (Примечание: данный запрос предусматривает группировку по номеру кредитного договора).
19. Создайте запрос «Обеспечение», содержащий следующие поля: Номер кредитного договора, Наименование клиента, сумма кредита, вид обеспечения, стоимость обеспечения, валюта, достаточность (да/нет). (Примечание: Достаточность обеспечения рассчитывается исходя из того, что стоимость обеспечения должна превышать сумму кредита на 20%). (учитывайте Курс валюты)
20. Создайте запрос на создание новой таблицы «Обеспечение кредита1», поместив поля Номер кредитного договора, наименование клиента из таблицы «Кредитные договоры» ; поля: Сумма кредита, Процентная ставка, Срок погашения, Размер ежемесячной выплаты по кредиту из таблицы «Подробности кредитного договора»; поля: вид обеспечения, стоимость обеспечения, достаточность из запроса «Обеспечение». (с помощью Мастера запросов).
21. Создайте перекрестный запрос, выводящий сумму кредита физических и юридических лиц по группам риска. (учитывайте Курс валюты)
22. Создайте перекрестный запрос, отражающий среднюю сумму взноса по месяцам и статусу. (учитывайте Курс валюты)
23. Создайте запрос для отображения информации о кредитных договорах, заключенных с 15.02.текущего года по 15.03.текущего года. (интервал дат подберите с учетом данных в Вашей БД)
24. Создайте запрос «Общая информация» с графами: Общий фонд кредитования, Минимальная и максимальная процентная ставка по всем кредитам с группировкой по группам риска.
25. Создайте запрос, отражающий полную информацию о клиенте, который взял максимальный кредит.
26. Создайте запрос, выводящий информацию о клиентах, у которых сумма кредита больше среднего значения этого показателя по всей таблице.
27. Создайте сводную таблицу для анализа данных, используя поля: Отраслевая принадлежность, Код валюты, Статус (таблица «Кредитные договоры»); Сумма кредита (таблица «Подробности кредитного договора»). В данной таблице создайте вычисляемые поля: «Плата за риск» (0,3% от суммы кредита) и «Плата за издержки при оформлении кредита» (0,2% от суммы кредита). Добавьте в таблицу итоговые поля, отражающие минимальный и максимальный размер кредита, а также общую сумму кредита по выбранным полям. На базе сводной таблицы постройте диаграмму.
28. Создайте сложную форму «Подробности кредитного договора», в которой представлена общая информация по кредитным договорам (используя таблицы «Подробности кредитного договора» и/или «Кредитные договоры») и подробная информация (таблица «Календарь выплат»)
29. Создайте полный отчет по юридическим и физическим лицам с выведением средней ставки и общего фонда кредитования (Примечание: данный отчет предусматривает группировку по статусу и с выведением по каждой группе средней ставки кредитования и общего фонда кредитования).
30. Создать отчет «Обеспечение» по запросу «Обеспечение». Вывести максимальный и минимальный размер кредита.
31. Создайте кнопочную форму для работы с базой данных (кнопка – выполнить запрос «Погашение кредита»; кнопка – открыть сложную форму «Подробности кредитного договора»; кнопка – просмотр отчета «Обеспечение»; кнопка – закрытие формы; кнопка – закрытие базы данных; кнопка – открытие таблицы «Календарь выплат» в MS Excel).

Рис. 1. Примерный вид окна Схема данных

¹ Задайте номер кредитного договора в виде текстового поля не менее 5 символов (буквы Ю или Ф (для юридических и физических лиц соответственно) и цифр) .

² Лицевой счет клиента банка – 20 символов: РППББВВВКООООЛЛЛЛЛЛЛ

где:  Р (1 позиция) – номер балансового раздела;

П (2 позиции) – номер счета первого  порядка;

Б (2 позиции) – номер счета второго  порядка;

В (3 позиции) – цифровой код валюты;

К (1 позиция) – контрольный ключ;

О (4 позиции) – номер филиала;

Л (7 позиций) – лицевая часть  счета.

³ Для облегчения создания и заполнения таблицы «Валюта» целесообразнее на странице официального сайта ЦБ РФ http://www.cbr.ru/currency_base/daily.asp раскрыть гиперссылку «Официальные курсы валют на заданную дату, устанавливаемые ежедневно». Выделить таблицу со сведениями ЦБ РФ о курсах валют к рублю и скопировать ее в Буфер обмена.  Открыть новый документ табличного процессора Excel. Вставить в него из Буфера обмена таблицу со сведениями ЦБ РФ о курсах валют к рублю. Присвоить диапазону данной таблицы в документе Excel имя. Сохранить документ Excel в виде файла на диск. (Поиск в Интернете и копирование таблицы с данными из сайта ЦБ РФ выполнять не следует, если уже имеется файл Excel с информацией о курсах валют).

В окне Базы данных Access выделить объект Таблицы и выполнить команды:

Файл → Внешние данные → Импорт →  Найти и выделить файл Excel → Кнопка Импорт → Переключатель Именованные диапазоны → Выделить требуемый диапазон →  Кнопка Далее → Кнопка Далее → Переключатель В новой таблице → Кнопка Далее → Кнопка Далее →Переключатель Определить ключ →  Выбрать поле ключа Цифровой код валюты →  Кнопка Далее  → Ввести имя таблицы базы данных → Кнопка Готово →  ОК

Открыть импортированную таблицу  в режиме Конструктора. Проверить наличие ключевого поля. Уточнить типы полей (задать для поля Курс тип Денежный вместо типа Числовой). Уточнить общие параметры полей (сократить длину).

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