База данных "Банковские вклады"

 

 

 

Курсовая работа по дисциплине

«Базы данных»

 

База данных "Банковские вклады"

 

 

 

 

 

Выполнил: ____________________

 

Научный руководитель: ________

                                          

 

 

 

 

Казань – 2010 
Содержание

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

 

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

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

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

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

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

     Проанализировав предметную область, мы можем сказать, что разработка базы данных для банка актуальна. Целью разработки базы данных «Банковские вклады» и автоматизированной системы для работы с ней является повышение качества и скорости обслуживания клиентов, работы с данными о договорах, клиентах, вкладах и счетах работниками банка.

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

База данных «Банковские  вклады» выполнит задачи повышение оперативности работы сотрудников банка, сокращение времени для заключения договора, поиск информации по клиентам и их счетам.

В первой главе данной курсовой содержится информация о основных понятиях реляционных баз данных, во второй главе переходим к проектированию базы данных, рассказывается о ER-модели, реляционной модели, о языке SQL и о проектирование конкретной базы данных.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Глава I

Основные понятия  и определения

1. Описание предметной области

 

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

Данная предметная область представляет собой банк, а точнее его работу с вкладами.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

1.2. Основные понятия реляционных баз данных.

 

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

называемых отношениями [2].

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

      Строки  таблицы - отношения называются  кортежами или записями. Столбцы называются атрибутам и. Термины – атрибут, столбец, колонка, поле обычно используются как синонимы. Каждый атрибут имеет имя, которое должно быть уникальным в конкретной таблице-отношении, однако в разных таблицах имена атрибутов могут совпадать.

      Количество кортежей в таблице-отношении называется кардинальным числом отношения, а количество атрибутов называется степенью отношения.

       Ключ  или первичный ключ отношения – это уникальный идентификатор строк (кортежей), то есть такой атрибут (набор атрибутов), для которого в любой момент времени в отношении не существует строк с одинаковыми значениями этого атрибута (набора атрибутов).

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

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

     Отношения реляционной базы данных обладают следующими свойствами:

- в отношениях не должно быть кортежей – дубликатов;

- кортежи отношений неупорядочены;

- атрибуты отношений также неупорядочены.

     Из этих свойств отношения вытекают следующие важные следствия.

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

- Из неупорядоченности кортежей следует, во-первых, что в отношении не существует другого способа адресации кортежей, кроме адресации по ключу, во-вторых, что в отношении не существует таких понятий как первый кортеж, последний, предыдущий, следующий и т.д.

- Из неупорядоченности атрибутов следует, что единственным способом их адресации в запросах является использование на именования атрибута.

      Относительно свойства реляционного отношения, касающегося отсутствия кортежей - дубликатов, следует сделать важное замечание. В этом пункте SQL не полностью соответствует реляционной модели. А именно, в отношениях, являющихся результатами запросов, SQL допускает наличие одинаковых строк. Д ля их устранения в запросе используется ключевое слово DISTINCT.

       Информация в реляционных базах данных, как правило, хранится не в

одной таблице-отношении, а в нескольких. При создании нескольких таблиц взаимосвязанной информации появляется возможность выполнения более сложных операций с данными, то есть более сложной обработки данных. Для работы со связанными данными из нескольких таблиц важным является понятие так называемых внешних ключей.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Глава II

 

Проектирование  баз данных

2.1  ER-модель (entity-relationship model).

 

     Работу с этой базой начнем с построения модели. Наиболее распространенной является ER-модель (entity-relationship model) – модель «Сущность – связь».

 

     Базовые  понятия:

- Сущность (Объект) – это некоторое целое (явление, понятие), обладающее рядом свойств, относящихся к конкретной предметной области. 

-

- Атрибут сущности (свойство, характеризующее объект) -

- Связь –

 

 

     Объект и его атрибуты соединяются ненаправленными дугами.

 

 

 

 

 

 

 

 

     Связи между объектами могут быть трех видов:

- Один – к одному. Этот тип связи означает, что  каждому объекту первого вида соответствует не более одного объекта второго вида, и наоборот.

 

 

 

 

- Один – ко многим. Этот тип связи означает, что каждому объекту первого вида может соответствовать более одного объекта второго вида, но каждому объекту второго вида соответствует не более одного объекта первого вида.

 

 

 

 

- Многие – ко многим. Этот тип связи означает, что  каждому объекту первого вида  может соответствовать более  одного объекта второго вида, и наоборот.

 

 

 

 

 

 

 

 

 

 

Рис. 1. ER-модель базы данных «Банковские вклады»

   2.2 Преобразование ER-модели в реляционную модель.

 

         Переход к такой модели сводится к следующим изменениям:

- Это преобразование  сущности в таблицу, вида 

Таблица(Ключ, Атрибут1, Атрибут2,…,АтрибутN)

Имя сущночти становится именем таблицы.

- Каждый атрибут становится столбцом с тем же именем.

- Компоненты каждого  уникального id сущности превращается в ключ отношений.

- Связи становятся  внешними ключами.

- Если в ER-модели существуют подтипы, то они выделяются в отдельную таблицу, либо объединяются в одной.

Удобно имена атрибутов в  масштабе ER-модели сделать уникальными, тогда при построении реляционной  модели их (почти никогда) не придется переименовывать.

Для краткости в примерах пропущены  несущественные неключевые атрибуты.

I. Преобразование  сущностей

1. Преобразование обычной сущность

 

Обычная сущность преобразуется в  отдельную таблицу, полями таблицы  будут все атрибуты сущности:

          Сущность (Ключ, Атрибут1, Атрибут2)

2. Преобразование слабой сущности   

 

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

Ключевые поля всех родительских таблиц войдут в ключ дочерней таблицы. Для дочерней таблицы они будут называться внешними ключами.

          Сущность1 (Ключ1, Ключ2, Атрибут1, Атрибут2)

3. Преобразование подтипов сущностей.

1 способ. Создается одна таблица, в которую помещают все атрибуты. Для того, чтобы указать, к какому подтипу относится объект, приходится вводить дополнительное поле-признак.

          Сущность1(Ключ, Атрибут1, Атрибут2, Атрибут3, Атрибут4, Атрибут4, Признак)

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

          Подтип1(Ключ, Атрибут1, Атрибут2, Атрибут3)

          Подтип2(Ключ, Атрибут1, Атрибут4, Атрибут5)

3 способ. Создается одна таблица для надтипа, и по одной таблице для каждого подтипа, в которую включаются ключевые поля надтипа:

          Сущность1(Ключ, Атрибут1)

          Подтип1(Ключ, Атрибут2, Атрибут3)

          Подтип2(Ключ, Атрибут4, Атрибут5)

II. Преобразование связей

Для связей-двойных ромбов ничего делать не нужно, вся информация уже хранится в таблице слабой сущности.

1. Связь М:М

 

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

           Сущ1Сущ2(Ключ1, Ключ2, Атрибут1).      

2. Связь 1:М  

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

Ключом будет ключ второй сущности.

          Сущ1Сущ2(Ключ1, Ключ2).

2 способ. Новая таблица не создается, а в таблицу дочерней сущности добавляют ключевые поля родительской сущности (в ключ дочерней сущности они входить не будут!). Ключевые поля родительской сущности представляют собой внешний ключ (foreign key) для дочерней сущности.

          Таблица дочерней сущности: Сущность2(Ключ2, Атрибут1, Ключ1).

3. Связь 1:1

      

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

Ключом будет ключ любой сущности.

          Сущ1Сущ2(Ключ1, Ключ2) или Сущ1Сущ2(Ключ1, Ключ2).

2 способ. Точно так же, как и во 2 случае 1:М, новая таблица не создается, а в таблицу одной из сущностей (будем считать ее дочерней) добавляют ключевые поля другой сущности (будем считать ее родительской).

Если связь не является связью “ровно к одному” по отношению к родительской таблице, то есть не все экземпляры сущностей участвуют в связи, поле внешнего ключа в некоторых записях может быть пустым.

          Таблица дочерней сущности: Сущность1(Ключ1, Атрибут1, Ключ2),

или Сущность2(Ключ2, Атрибут2, Ключ1).

3 способ. Две таблицы для сущностей, связанных 1:1, объединяются в одну. Ключом новой таблицы может быть комбинация ключей обеих таблиц. Если хотя бы в одном направлении связь “ровно к одному”, то ключ этой сущности можно считать ключом объединенной таблицы.

           Таблицы Сущность1(Ключ1, Атрибут1) и

           Сущность2(Ключ2, Атрибут2)

заменяются на 

            Сущ1Сущ2(Ключ1, Атрибут1, Ключ2, Атрибут2)

            [или, возможно, Сущ1Сущ2(Ключ1, Атрибут1, Ключ2, Атрибут2),

             или Сущ1Сущ2(Ключ1, Атрибут1, Ключ2, Атрибут2)].

Примечание 1:

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

 

Рассмотрим связь 1:M, способ 2. Переименован внешний ключ.

           Сущность1(Ключ1, Атрибут1, ЕщеОдинКлюч1).

Примечание 2:

Для связей с арностью более 2 обычно применяется  тот же способ, что и для бинарной связи M:M – создается новая таблица, содержащая ключевые поля всех связанных таблиц.

 

Сущ1Сущ2Сущ3(Ключ1, Ключ2, Ключ3).

 

         Покажем преобразование ER-модели данной базы данных в реляционную.

        Создадим таблицу Клиент (Klient).  id_klient является здесь первичным ключом.

Клиент (id_клиента, ФИО, дата_рождения, адрес, паспорт, пол, телефон)

         Каталог вкладов и валют:

Вклады (id_вклада, наименование, срок, проценты)

Валюта (id_валюты, наименование)

          Таблица со счетами клиентов:

Счет(id_счета, сумма)

          Таблица Операторы:

Оператор( id_оператора, ФИО, телефон)

          Создадим таблицу Договор. Он  будет содержать несколько внешних  ключей.

Договор(id_договора, id_счета, id_вклада , id_валюта, дата_открытия)

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

Заключение_договора(id_клиента, id_договора, id_оператора)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.3 SQL  (Structured Query Language)

 

    В качестве среды программирования для этой базы данных будем использоваться СУБД SQL Server[7].

    SQL Server – это хорошо масштабируемый, полностью реляционный, быстродействующий многопользовательский сервер баз данных масштаба предприятия, способный обрабатывать большие объемы данных для клиент-серверных приложений.

     Язык  SQL относится к классу непроцедурных языков программирования. В отличие от универсальных процедурных языков, которые также могут быть использованы для работы с базами данных, язык SQL ориентирован не на записи, а на множества.

     Существуют и используются  две формы языка SQL: интерактивный SQL и встроенный SQL. Интерактивный SQL используется для непосредственного ввода SQL - запросов пользователями получения результата в интерактивном режиме. Встроенный SQL состоит из команд SQL, встроенных внутрь программ, которые обычно написаны на некотором другом языке (Паскаль, С, С++ и д.р.). Это делает программы, написанные на таких языках, более мощными, гибкими и эффективными, обеспечивая их применение для работы с данными, хранящимися в реляционных базах. При этом, однако, требуются дополнительные средства обеспечения интерфейса SQL с языком, в который он встраивается.

      И интерактивный, и встроенный SQL подразделяются на следующие составные части.

       Язык Определения Данных – DDL (Data Definition Language), дает возможность создания, изменения и удаления различных объектов базы данных (таблиц, индексов, пользователей, привилегий и т.д.).

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

       Язык Обработки  Данных – DML (Data Manipulation Language), предоставляет возможность выборки информации из базы данных и ее преобразования.

      Тем не менее,  это не два различных языка,  а компоненты единого SQL.

 

      Для создания таблиц используется команда create table.

 

create table имя_таблицы

(список_описаний_полей [список_ограничений_таблицы]);

      Описание поля  имеет формат:

имя_поля тип_поля [(размер)]

[null] [not null]

[primary key]

[identity]

[references имя_таблицы (имя_поля)]

      Чаще всего используют  типы полей:

Varchar – строковый тип переменной длины;

Numeric – числовой тип;

Datatime – тип дата/время;

Null – специальное «неопределенное» значение. Ограничение null/not null служит для указания, что данный тип поля допускает/запрещает ввод null-значений.

Primary key – ограничение, указывающее, что в данной таблице данное поле представляет собой первичный ключ.

Identity – ограничение, указывающее, что данное поле представляет собой счетчик, т.е, значения в данное поле вставляется автоматически с нарастанием при вставке строки.

    Покажем это на примере одной из таблиц, которая содержит 3 внешних ключа (остальные таблицы в приложении 1):

 

создаем таблицу

 

create table Zakluchenie_dogovora  

 

описываем список полей

 

(id_klient integer not null,                      

id_dogovor integer not null,

id_operator integer not null,

 

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

 

constraint kl_dog_op primary key (id_klient, id_dogovor, id_operator),

 

ограничение внешнего ключа определяется так:

 

constraint id_kli foreign key (id_klient)

 

(id_kli – имя_ограничения, в скобочках указывается список_полей)

 

references Klient (id_klient),

 

(тут пишется родительская таблица  и в скобочках внешний ключ)

 

constraint id_dog foreign key (id_dogovor)

references dogovor (id_dogovor),

constraint id_op foreign key (id_operator)

references operator (id_operator));

 

     Для добавления новых строк в таблицу служит команда insert:

Insert [into] имя_таблицы [(список_полей)]

Values (список_значений)

    Например,

insert into Klient (id_klient, FIO, data_rozhd, adress, pasport, pol, tel)

values (17, 'Чехов', '4.07.1989','ул. Навигаторная, дом 87, кв 79', 555, 'м', 350099725);

    Для обновления данных  используется команда update:

Update имя таблицы

Set поле1=выражение1 [,.., полеN=выражениеN]

[where условие]

    Например,

Update Klient

Set tel=345768695

Where tel = 350099725

    Для удаления данных используется команда delete:

Delete [from] имя_таблицы [where условие]

   Например:

Delete from Klient where FIO='Чехов';

   Если опция  пропущена, удалены будут все  строки таблице.

     Заполним  текстовыми данными нашу базу  «Банковские вклады» на примере  одной из таблиц. Возьмем таблицу Операторы(Рис.2)

 

insert into Operator( id_operator, fam, telefon)

values (1, 'Понедельников', '112233');

insert into Operator( id_operator, fam, telefon)

values (2, 'Вторников', '223344');

insert into Operator( id_operator, fam, telefon)

values (3, 'Среднедельникова', '334455');

 

      Посмотрим результат заполнения, для этого выполним следующую команду (Результаты заполнения остальных таблиц находятся в Приложение 2)

select*from Operator

 

                                        Рис.2 Таблица «Операторы»

                                

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.4 DQL. Запросы

 

      DQL – Data Query Language – язык запросов, составная часть SQL[6].

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

      Предложение SELECT может состоять из многих фраз и в упрощенном виде имеет следующий синтаксис:

 

SELECT [DISTINCT]

{*| столбец1,…,столбецN}

From {таблица1,…,таблицаN}

 

Выборка из одной таблицы:

    Запрос. Вывести  полную информацию о всех клиентах(Рис.3)

select*from Klient

 

Рис. 3.

 

Использование условий отбора:

       Для выбора отдельных строк по критериям используется ключевое слово where.

     Запрос. Выбрать клиентов, которые заключили договора 2008-06-16 (рис.4)

select * from Klient a

Where ‘2008-06-16’ in (Select data_open From Dogovor b Where a.id_klient=b.id_klient)

 

Рис.4

 

Использование агрегирующих функций:

         Для подсчета итоговых значений используются функции sum, count, max, min, avg. если не используется группировка строк, запрос с использованием итоговой функции вернет ровно одну строку.

 

        Запрос. Выбрать имена и номера операторов, обслуживающих более одного клиента (рис.5)

Select id_operator, fam From Operator a

Where 1<(Select COUNT(Distinct id_klient) From Dogovor

Where id_operator=a.id_operator);

 

                                                        Рис.5

 

Сортировка:

       Для сортировки используется ключевое слово order by и имя поля или его номер в списке полей выборки.

        Запрос. Напечатать список клиентов, отсортированный по алфавиту (рис. 6)

select FIO from Klient order by 1

 

                                                          Рис.6

 

Подзапросы:

        Подзапрос может быть несвязанным, т.е. в формулировке подзапроса нет ссылки на главный запрос. В этом случае подзапрос выполняется один раз при выполнении главного запроса (рис. 7)

        Запрос. Вывести данные о клиенте, у которого самый большой счет.

 

select FIO, data_rozhd, adress, tel from Klient

where id_klient =(select id_klient from Dogovor

where id_schet=(select id_schet from Schet

where summa=(select max(summa) from Schet)))

 

 

Рис.7

 

      Запрос. Выбрать клиентов, у которых договор  заключал оператор с идентификационным номером 2 (Рис.8)

select FIO from Klient

where id_klient in (select id_klient from Zakluchenie_dogovora

where id_operator=(select id_operator from Operator

where id_operator='2'))