Курсовая работа по MS SQL Server. Вклады в банке

ВВЕДЕНИЕ 7
1 Описание предметной области 8
3 Концептуальная модель базы данных ассоциации шеф-поваров 6
4 Реляционная модель базы данных 15
5 Модель данных в SQL Server 2012 16
6 Создание и заполнение таблиц 17
7 Объект базы данных 31
7.1 Запросы 31
7.2 Представления 51
7.3 Процедуры 56
7.4 Триггеры 71
7.5 Курсор 76
ЗАКЛЮЧЕНИЕ 80
СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ 81

База данных отделения банка необходима для оперативного учета клиентов банка, их вкладов, а также сотрудников отделения; динамичного контроля актуальной информации.
Целью работы является составление и заполнение базы данных отделения банка, заполнение всех клиентов, их вкладов, операций по вкладам и сотрудников отделения.
Цель достигается за счет следующих задач: отражение в базе данных подробной информации обо всех клиентах (вкладчиках): их фамилия, имя, отчество, данные паспорта и адрес; вкладах: вид, дата и срок открытия, размер процентной ставки и период начисления, производимые с вкладом операции; сотрудниках: фамилия, имя отчество, должность и оклад. Установление взаимосвязей между всеми элементами базы данных, позволяет вести оперативный поиск нужной информации в базе данных отделен ия банка по различным критериям.
Задачи решаются с помощью программного продукта SQL Server 2012 путем создания посредством него базы данных «OtBank» на основе концептуальной и реляционной схем, отражающих иерархичность и взаимозависимость данных отделения банка. Поиск, выборка и группировка информации в базе данных реализуется с помощью запросов, хранимых процедур, представлений, триггеров и курсора.
 

summa) from schet) AND valuta.id=Schet.valuta_idGroup by Valuta.nameРезультат выполнения запроса представлен на рисунке 34.Рисунок 34–Результат выполнения запроса7.1.1.2Результатом выполнения запроса является таблица из 2 строк и 2 столбцами. В первом столбце указывается валюта счета. Во втором столбце указывается количество счетов, остаток на которых меньше среднего остатка по всем счетам, а во втором столбце – средний остаток по всем счетам данной валюты.7.1.1.3Запрос считает количество операций с максимальной суммой, и выводит это количество и максимальную сумму операцийЗапрос использует таблицу «Ops_Schet» из которой берется сумма операции и с помощью функции max определяется максимальная, далее с помощью функции count по id операции считается сколько операции было проведено на даннуюсумму.Листинг программного кода:--Запрос выводит максимальную сумму операции и количество операций на данную суммуSelect Count(Ops_Schet.id) AS 'Количество операци', Max(Ops_Schet.summa) As 'максимальная сумма операции'From Ops_SchetWhere Ops_Schet.summa=(Select Max(Ops_Schet.summa) From Ops_Schet)Результат выполнения запроса представлен на рисунке 35.Рисунок 35–Результат выполнения запроса7.1.1.3Результатом выполнения данного запроса является таблица с одной строкой и двумя столбцами, в первом столбце указано количество операций, а во втором – максимальная сумма операции.7.1.1.4 Вывести количество операций, произведенных ‘2013-01-12’Запрос использует таблицу «Ops_Schet», count по id операции считается сколько операции было проведено в определенную дату.Листинг программного кода:--Запрос количество операций, произведенных ‘2013-01-12’Select Count(Ops_Schet.id) AS 'Количество операций'From Ops_SchetWhere Ops_Schet.dateOp=‘2013-01-12’Результат выполнения запроса представлен на рисунке 36.Рисунок 37–Результат выполнения запроса7.1.1.4Результатом выполнения данного запроса является ячейка, в которой указывается общее количество операций, проведенных 2013-01-12.7.1.1.5 Вывести сумму операций, произведенных ‘2013-01-12’Запрос использует таблицу «Ops_Schet», sum по полю summa операции считает общую сумму операций, проведеных в определенную дату.Листинг программного кода:--Запрос количество операций, произведенных ‘2013-01-12’Select sum(Ops_Schet.summa) AS 'сумма операций'From Ops_SchetWhere Ops_Schet.dateOp=‘2013-01-12’Результат выполнения запроса представлен на рисунке 35.Рисунок 37–Результат выполнения запроса7.1.1.5Результатом выполнения данного запроса является ячейка, в которой указывается общая сумма операций, проведенных 2013-01-12.7.1.1.6 Посчитать количество сотрудников на каждой должностиЗапрос использует таблицы «Sotr» и «Dolg», функцию count по полю Fio таблицы SotrЛистинг программного кода:--Запрос количество операций, произведенных ‘2013-01-12’Select Dolg.name, COUNT(Sotr.id) As 'Количество сотрудников'From Dolg, SotrWhere Sotr.Dolg_id=Dolg.idGroup by nameРезультат выполнения запроса представлен на рисунке 38.Рисунок 38–Результат выполнения запроса7.1.1.6Результатом выполнения данного запроса является таблица, в первом столбце которой указывается должность, а во втором – количество сотрудников на данной должности.7.1.1.7 Вывести количество вкладов каждого вкладчикаЗапрос выполняется с помощью программного кода, который использует две таблицы «vkladchik», «schet», через связь этих таблиц по каждому ФИО вкладчика, запрос выводит количество вкладов. Запрос использует функцию count и group by.Листинг программного кода:--Запрос на количество вкладчиков по виду вкладаSelect vkladchik.FIO, COUNT(Schet.Num) As 'Количество счетов'From Vkladchik, SchetWhere vkladchik.id=Schet.vkladchik_idGroup by FIOРезультат выполнения запроса представлен на рисунке 39.Рисунок 39–Результат выполнения запроса7.1.1.7Результатов выполнения запроса является таблица из 2 столбцов, в первом столбце перечислены ФИО всех вкладчиков, а во втором с помощью функции count посчитано количество вкладов данного вкладчика.7.1.1.8 Вывести количество операций каждого вкладаЗапрос выполняется с помощью программного кода, который использует две таблицы «ops_Schet», «schet», через связь этих таблиц по каждому номеру счета, запрос выводит количество операций. Запрос использует функцию count и group by.Листинг программного кода:--Запрос на количество вкладчиков по виду вкладаSelect schet.num, COUNT(ops_Schet.id) As 'Количество операций'From schet, Ops_SchetWhere Ops_Schet.Schet_num=Schet.NumGroup by numРезультат выполнения запроса представлен на рисунке 40.Рисунок 40–Результат выполнения запроса7.1.1.8Результатов выполнения запроса является таблица из 2 столбцов, в первом столбце перечислены номера счетов, а во втором с помощью функции count посчитано количество операций по каждому счету.7.1.1.9 Вывести количество операций каждого видаЗапрос выполняется с помощью программного кода, который использует две таблицы «ops_Schet», «oper», через связь этих таблиц по каждому виду операции, запрос выводит количество операций. Запрос использует функцию count и group by.Листинг программного кода:--Запрос на количество вкладчиков по виду вкладаSelect oper.name, COUNT(ops_Schet.id) As 'Количество операций'From oper, Ops_SchetWhere Ops_Schet.oper_id=oper.idGroup by nameРезультат выполнения запроса представлен на рисунке 41.Рисунок 41–Результат выполнения запроса7.1.1.9Результатов выполнения запроса является таблица из 2 столбцов, в первом столбце перечислены виды операций, а во втором с помощью функции count посчитано количество операций каждого вида.7.1.1.10 Вывести количество документ-завок каждого вкладчикаЗапрос выполняется с помощью программного кода, который использует две таблицы «zayavka_document», «vkladchik», через связь этих таблиц по каждому вкладчику, запрос выводит количество документ-заявок. Запрос использует функцию count и group by.Листинг программного кода:--Запрос на количество операций по виду операцииSelect vkladchik.FIO, COUNT(zayavka_document.id) As 'Количество документ-заявок'From Vkladchik, zayavka_documentWhere vkladchik.id= zayavka_document.vkladchik_idGroup by FIOРезультат выполнения запроса представлен на рисунке 42.Рисунок 42–Результат выполнения запроса7.1.1.10Результатов выполнения запроса является таблица из 2 столбцов, в первом столбце перечислены ФИО вкладчиков, а во втором с помощью функции count посчитано количество документ-заявок каждого вкладчика.7.1.2Запросы с использованием функций7.1.2.1 Вывести количество счетов, остаток денежных средств на которых меньше среднего остатка по всем кладамЗапрос использует одну таблицу «Schet» из которой берется остаток на счете, а также использует функции count, которая считает по считам, сколько их, функция avg,которая по остатку денежных средств на счете вычисляет средний остаток и условие group by.Листинг программного кода:--Запрос выводит количество счетов, с остатком, меньшим среднегоSelect valuta.name As 'Валюта', COUNT(Schet.Num) As 'Количество счетов меньше', AVG(Schet.summa) As 'Среднее'From Schet, valutaWhere Schet.summa<(Select AVG(Schet.summa) from schet) AND valuta.id=Schet.valuta_idGroup by Valuta.nameРезультат выполнения запроса представлен на рисунке 43.Рисунок 43–Результат выполнения запроса 7.1.2.1Результатом выполнения запроса является таблица из 2 строк и 2 столбцами. В первом столбце указывается валюта счета. Во втором столбце указывается количество счетов, остаток на которых меньше среднего остатка по всем счетам, а во втором столбце – средний остаток по всем счетам данной валюты.7.1.2.2Запрос считает количество операций с максимальной суммой, и выводит это количество и максимальную сумму операцийЗапрос использует таблицу «Ops_Schet» из которой берется сумма операции и с помощью функции max определяется максимальная, далее с помощью функции count по id операции считается сколько операции было проведено на данную сумму.Листинг программного кода:--Запрос выводит максимальную сумму операции и количество операций на данную суммуSelect Count(Ops_Schet.id) AS 'Количество операци', Max(Ops_Schet.summa) As 'максимальная сумма операции'From Ops_SchetWhere Ops_Schet.summa=(Select Max(Ops_Schet.summa) From Ops_Schet)Результат выполнения запроса представлен на рисунке 44.Рисунок 44–Результат выполнения запроса 7.1.2.2Результатом выполнения данного запроса является таблица с одной строкой и двумя столбцами, в первом столбце указано количество операций, а во втором – максимальная сумма операции.7.1.2.3 Вывести вкладчика с максимальным остатком на счетеЗапрос использует таблицы «Schet» и «Vkladchik», из которых берется остаток на счете, а также использует функции max, которая из всех вкладов выбирает вклад с максимальным остатком, и условие group by.Листинг программного кода:--Запрос выводит ФИО вкладчика с максимальным остатком на счету, и этот остатокSelect vkladchik.FIO, Schet.summa As 'Остаток на счете' From Schet, vkladchikWhere schet.vkladchik_id=vkladchik.id and Schet.summa=(Select max(Schet.summa) from schet)Результат выполнения запроса представлен на рисунке 45.Рисунок 45–Результат выполнения запроса 7.1.2.3Результатом выполнения запроса является таблица из 2 столбцов. В первом столбце указывается ФИО вкладчика, у которого максимальный остаток на счете. Во втором столбце указывает максимальный остаток на счете. 7.1.2.4 Вывести вкладчика с минимальным остатком на счетеЗапрос использует таблицы «Schet» и «Vkladchik», из которых берется остаток на счете, а также использует функции min, которая из всех вкладов выбирает вклад с минимальным остатком, и условие group by.Листинг программного кода:--Запрос выводит ФИО вкладчика с минимальным остатком на счету, и этот остатокSelect vkladchik.FIO, Schet.summa As 'Остаток на счете' From Schet, vkladchikWhere schet.vkladchik_id=vkladchik.id and Schet.summa=(Select min(Schet.summa) from schet)Результат выполнения запроса представлен на рисунке 46.Рисунок 46–Результат выполнения запроса 7.1.2.4Результатом выполнения запроса является таблица из 2 столбцов. В первом столбце указывается ФИО вкладчика, у которого минимальный остаток на счете. Во втором столбце указывает максимальный остаток на счете. 7.1.2.5 Вывести должность с максимальным окладомЗапрос использует таблицу «Dolg», из которой при помози функции max выбирается максимальный размер оклада, а также условие group by.Листинг программного кода:--Запрос выводит должность с максимальным окладом Select Dolg.name, Dolg.oklad From dolgWhere Dolg.oklad=(Select max(dolg.oklad) from dolg) Результат выполнения запроса представлен на рисунке 47.Рисунок 47–Результат выполнения запроса 7.1.2.5Результатом выполнения запроса является таблица из 2 столбцов. В первом столбце указывается наименование должности с максимальным окладом. Во втором столбце указывается максимальный оклад. Запросы с функциями используются для подсчета, каких либо данных из таблиц базы данных[4].7.1.3Запросы с Having7.1.3.1 Сколько сотрудников имеют должность оператор?Запрос выводит количество сотрудников, которые имеют должность оператор. Реализуется запрос через таблицы «Sotr» и «Dolg», с помощью функции count по коду сотрудника и условия having, в котором название должности является оператор.Листинг программного кода:--Запрос выводит количество сотрудников, которые имеют должность операторSelect Dolg.name, count(Sotr.id) As 'Количество сотрудников'From Dolg, SotrWhere Sotr.Dolg_id=Dolg.idgroup by Dolg.nameHAVING Dolg.name='Оператор' Результат выполнения запроса представлен на рисунке 48.Рисунок 48 – Результат выполненного запроса 7.1.3.1Результатом запроса является таблица, где в первом столбце название должности, в данном случае, по условию, оператор, а во втором столбце количество сотрудников, подсчитанное с помощью функции count.7.1.3.2 На какую сумму были сняты денежные средства со счетов?Запрос выводит сумму операций по снятию денежных средств со счета. Реализуется запрос через таблицы «Ops_Schet», «Oper» с помощью функции sum и условия having,которое обозначает название блюда как роллы ФиладельфияЛистинг программного кода:--Запрос выводит сумму снятых денежных средствselect Oper.name, sum(Ops_Schet.summa) As 'Сумма всех операций'from Oper, Ops_Schetwhere Ops_Schet.Oper_id=Oper.id group by nameHAVING Oper.name=’Снятие денежных средств’ Результат выполнения запроса представлен на рисунке 49.Рисунок 49 – Результат выполненного запроса 7.1.3.2Результатом выполненного запроса является строка, в первой ячейке которой название операции, а во второй ячейке с помощью функции sum подсчитана сумма всех операций.7.1.3.3 Сколько операций сделано в каждый день, в 2013 году?Запрос выводит количество операций в 2013 году, реализуется через таблицу «Ops_Schet», с помощью функции count по коду операции и условия havig, которое отбирает по 2013 году.Листинг программного кода:--Запрос выводит количество операций в 2013 годуSelect DateOp, COUNT(Ops_Schet.id) As 'Кол-во операций'From Ops_Schet Group by DateOpHAVING Ops_Schet.dateOp LIKE '2013%'Результат выполнения запроса представлен на рисунке 50.Рисунок 50- Результат выполненного запроса7.1.3.3Результатом выполненного запроса является таблица в первом столбце, которого перечислены даты, в которые были сделаны заказы, а во втором столбце с помощью функции count, подсчитано количество сделанных заказов в этот день.7.1.3.4 На какую сумму открыли вкладов 2013-01-12?Запрос выводит сумму открытия вкладов за 2013 год, реализуется через таблицы «Ops_Schet», «Oper», с помощью функции sum по сумме открытия счетов и условия having, которое определяет дату 2013-01-12.Листинг программного кода:--Запрос выводит сумму открытия счетов за 2013-01-12Select Ops_Schet.DateOp, Oper.name, sum(Ops_Schet.summa) As 'Сумма операций по открытию'from Oper, Ops_Schetwhere Oper.id = Ops_Schet.Oper_idgroup by Oper.name, DateOpHAVING Ops_Schet.DateOp LIKE '2013-01-12' AND Oper.name='Открытие счета'Результат выполнения запроса представлен на рисунке 51.Рисунок 51 – Результат выполненного запроса 7.1.3.4Результатом выполненного заказа является таблица в первом столбце которого указанная дата операций, во втором столбце наименование операции, а в третьем – сумма операций, подсчитанная с помощью функции sum, по сумме проводимых операций.Условие having используетcя для фильтрации сгруппированной информации и обязательно используется вместе с условием group by[9].7.1.4Запросы с выбором текста7.1.4.1 Какой адрес у вкладчика с номером 6?Запрос выводит адрес вкладчика с номером 6, реализуется через таблицу «Vkladchik».Листинг программного кода:--Запрос выводит адрес вкладчика с номером 6Select adresFrom vkladchikWhere id = 6;Результат выполнения запроса представлен на рисунке 52.Рисунок 52- Результат выполненного запроса 7.1.4.1Результатом выполненного запроса является ячейка, в которой указан адрес вкладчика с кодом 6.7.1.4.2 Какой оклад у сотрудника с номером 5?Запрос выводит оклад сотрудника с номером 5, реализуется через таблицы «sort» и «Dolg».Листинг программного кода:--Запрос выводит оклад сотрудника с номером 5Select okladFrom sotr, dolgWhere sotr.id = 6 and sotr.dolg_id=Dolg.idРезультат выполнения запроса представлен на рисунке 53.Рисунок 53- Результат выполненного запроса 7.1.4.2Результатом выполненного запроса является ячейка, в которой указан оклад сотрудника вкладчика с кодом 5.7.1.4.3 Кто из сотрудников производил операции в 2013 году?Запрос выводит ФИО сотрудников, которые проводили операции в 2013 году, реализуется через таблицы «Ops_Schet» и «Sotr».Листинг программного кода:--Запрос выводит ФИО сотрудниковSelect distinct Sotr.FioFrom sotr, Ops_SchetWhere sotr.id = Ops_Schet.Sotr_id And Ops_Schet.DateOp Like ‘2013%’Результат выполнения запроса представлен на рисунке 54.Рисунок 54- Результат выполненного запроса 7.1.4.3Результатом выполненного запроса является таблица, в которой указаны ФИО сотрудников, которые проводили операции в 2013 году.7.1.4.4 Кто из вкладчиков открывал вклады в 2013 году?Запрос выводит ФИО вкладчиков, которые открывали вклады в 2013 году, реализуется через таблицы «Vkladchik», «Oper», «Schet» и «Ops_Schet».Листинг программного кода:--Запрос выводит ФИО вкладчиковSelect distinct Vkladchik.FIOFrom vkladchik, Ops_Schet, oper, schetWhere vkladchik.id = schet.vkladchik_id AND Schet.num=Ops_Schet.Schet_num And Ops_Schet.Oper_id=Oper.id AND Ops_Schet.DateOp Like '2013%' AND Oper.name='Открытие счета'Результат выполнения запроса представлен на рисунке 55.Рисунок 55- Результат выполненного запроса 7.1.4.4Результатом выполненного запроса является таблица, в которой указаны ФИО вкладчиков, которые открывали счета в 2013 году.7.1.4.5 У кого из вкладчиков имеются вклады в долларах?Запрос выводит ФИО вкладчиков, у которых имеются вклады в долларах, реализуется через таблицы «Vkladchik», «valuta» и «Schet».Листинг программного кода:--Запрос выводит ФИО вкладчиковSelect distinct Vkladchik.FIOFrom vkladchik, schet, valutaWhere vkladchik.id = schet.vkladchik_id And Schet.valuta_id=Valuta.id AND valuta.name='Доллар'Результат выполнения запроса представлен на рисунке 56.Рисунок 56- Результат выполненного запроса 7.1.4.5Результатом выполненного запроса является таблица, в которой указаны ФИО вкладчиков, которые имеют вклады в долларах.7.2 Представления7.2.1Представление с информацией о вкладах вида универсальныйСоздается представление, которое содержит часть информации о вкладах: Фамилия Имя Отчество вкладчика, номер счета, дату открытия, валюту вклада и остаток на счете, реализуется через таблицы «Schet», «Valuta», «Vid_Vklad» и «Vkladchik».Листинг программного кода:--Представление с информацией о счетах вклада универсальныйgocreate view dbo.ONorm asselect Vkladchik.FIO, Schet.num, Schet.date_ot, Valuta.name, Schet.summa from Vkladchik, Schet, Valuta, Vid_Vkladawhere Vkladchik.id=Schet.vkladchik_id AND valuta.id=Schet.valuta_id AND Schet.Vid_Vklada_id=Vid_Vklada.id and Vid_Vklada.name=Модель данных представления первого представлена на рисунке 57.Рисунок 57 – Модель данных таблицы представления 7.2.1На модели данных таблицы первого представления видно, что для представления используются три таблицы «Schet», «Valuta», и «Vkladchik». Из таблицы «Schet» выбираются номер, сумма и дата открытия счета, из таблицы «Valuta» название валюты, из таблицы «Vkladchik» - ФИО владельца счета.Структура таблицы из первого представления представлена на рисунке 58.Рисунок 58 – Структура таблицы представления 7.2.1На структуре таблицы представления видно, что установлен фильтр на имени склада и выбор осуществляться только для вкладов «Стандартный».Результат выполненного представления представлен на рисунке 59.Рисунок 59 – Результат выполненного представления 7.2.1Результатом является таблица, состоящая из 4 столбцов, в первом столбце – ФИО вкладчика, во втором – номер счета, в третьем валюта вклада и в четвертом - сумма.7.2.2Представление с информацией о работниках отделения банкаСоздается представление, которое содержит часть информации о работниках отделения банка: фамилия, имя, отчество работника, его должность и количество обработанных операции по счетам.Листинг программного кода:--Представление с информацией о работниках отделения банкаgocreate view dbo.InfoSotr asselect FIO, name, Count (ops_Schet.id) AS 'Общее количество операций' FROM Sotr, Dolg, Ops_Schet WHERE Sotr.Dolg_id=Dolg.id AND Sotr.id=Ops_Schet.Sotr_id Group by FIO, name;Модель данных Представления второго представлена на рисунке 60.Рисунок 60–Модель данных таблицы представления 7.2.2На модели данных видно что используются таблицы «Sotr», «Dolg», и «Ops_Schet» из которых выбраны ФИО сотрудника, наименование должности и количество операций (суммируется с помощью функции Count)Структура таблицы из второго представления представлена на рисунке 61.Рисунок 61–Структура таблицы представления 7.2.2На структуре данных таблицы представления видно, что фильтров нет.Результат выполненного представления представлен на рисунке 62.Рисунок 62 – Результат выполненного представления 7.2.