ОГЛАВЛЕНИЕ

Введение……………………………...……………………………………….….4

§ 1. Проектирование базы данных…………………………….....................5

         1.1 Предметная область..........................................................................5

         1.2 Метод сущность-связь......................................................................5

         1.3 Выделение сущностей.......................................................................5

         1.4 Построение ER-диаграмм.................................................................6

         1.5 Построение предварительных отношений.......................................9

         1.6 Построение конечных отношений...................................................11

         1.7 Конечная диаграмма базы данных.................................................14

Заключение……………………………………………………………………..15

Список используемой литературы………………………………………….16

Приложения…………………………………………………………………….17

         Приложение 1........................................................................................17

         Приложение 2........................................................................................18

         Приложение 3........................................................................................21

         Приложение 4........................................................................................21
Введение.

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

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

1.1 Предметная область

В этой работе проектируется база данных «Сбербанк (вкладчики и вклады)».

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

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

Вкладчик имеет право снимать частичные суммы со своего вклада или пополнять его, если это предусмотрено видом вклада.

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

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

База данных должна содержать следующие данные: вкладчики, вклады, виды вкладов, операции по вкладу, закрытие вклада и архив.

Выделим из этих данных сущности.

Сущность – набор однотипных объектов, организованных по одной схеме.

Атрибут – определенное свойство сущности.

Вкладчик – это явный кандидат на сущность. Поскольку у него могут быть такие атрибуты как: Имя, серия и номер паспорта и т.д. У экземпляров – представителей – данной сущности будут иметься все атрибуты, правда, по большей части, разные у всех.

Вклад – тоже кандидат на сущность. Могут быть атрибуты: номер вклада, сумма вклада ...

Вид вклада – скорее всего – атрибут вклада. Поскольку если вид вклада будет сущностью, то все атрибуты вида и атрибуты вклада будут совпадать. Например: сумма вклада, проценты, валюта, - должны быть и у той, и у другой сущностей.

Операции по вкладу – кандидат на сущность.

Закрытие вклада – атрибут сущности Операции по вкладу. Это будет одной из  операций.   

Архив – кандидат на сущность. В архиве будут храниться уже закрытые вклады.

Сущности выделены.

Вклад, Вкладчик, Операции по вкладу (Операции), Архив.

Перейдем к формированию связей посредством ER-диаграмм.
1.4 Построение ER-диаграмм

ER – диаграмма – (от англ. Entity-Relationship) – диаграмма сущность-связь – она наглядно отражает сущности и связи между ними.

Для начала выразим словесно связи между выделенными сущностями:

Вкладчик имеет Вклад;

Вклад заведен на Вкладчика;

Вклад предполагает Операции;

Операции производятся по вкладу;

Архив содержит Вклады;

Вклады записываются в Архив;

Архив формируется по Операциям (если вклад закрыт, то он отправляется в операции);

Операции формируют Архив;

Архив содержит информацию о Вкладчиках;

Вкладчики,  закрывшие вклады, записываются в Архив.

Представим полученные связи на ER-диаграмме:


Далее определим степени связи и классы принадлежности.

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

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

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



Теперь охарактеризуем каждую степень связи (с.с.) и каждый класс принадлежности (к.п.).

1. Вклад - Вкладчик стоит с.с.  М:1. Это значит, что один вклад может иметь только одного вкладчика, а один вкладчик может иметь несколько вкладов.

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

2. Вклад - Операции – с.с. М:М. То есть, у одного вклада может быть несколько операций, а одна операция может быть у нескольких вкладов.

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

3. Вклад - Архив – с.с. 1:1. То есть, одна запись архива может содержать 1 вклад, а один вклад может быть записан в архиве только один раз.

К.п. сущности Архив обязателен, так как архив должен содержать закрытые вклады, т.е. не может не содержать их. И не каждый вклад может быть в архиве. поэтому у сущности Вклад необязательный к.п.

4. Архив - Вкладчик – с.с. М:1. Одна запись архива содержит одного вкладчика, а один вкладчик, поскольку может иметь несколько вкладов, может быть записан в архиве несколько раз.

К.п. сущности Архив обязателен, так как, поскольку архив должен содержать закрытые вклады, то он должен содержать и их вкладчиков.

5. Операции - Архив – с.с. 1:М. Архив содержит закрытые вклады, то есть вклады, к которым применена операция Закрытие. Значит 1 операция соответствует многим (а именно всем) записям архива.

К.п. сущности Архив обязателен, поскольку архив не может обходиться без операции Закрытия.

Теперь перейдем к построению отношений.
1.5 Построение предварительных отношений

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

Вклад – Вкладчик. Поскольку с.с. М:1 и к.п. М-связной сущности обязателен, то формируется два отношения, соответствующие этим сущностям. Первичными ключами будут являться ключи этих сущностей. Причем ключ 1-связной сущности добавляется в качестве внешнего ключа в М-связную сущность.

Вклад – Операции. С.с. М:М, значит формируются три отношения вне зависимости от к.п. этих сущностей: два отношения соответствуют связываемым сущностям и их ключи являются первичными ключами этих отношений. Третье отношение является связным между первыми двумя.

Вклад -  Архив. С.с. 1:1 и к.п. сущности Архив – обязателен. Под каждую из сущностей формируется по отношению с первичными ключами, являющимися ключами соответствующих сущностей. Далее к отношению, сущность которого имеет обязательный КП, добавляется в качестве атрибута ключ сущности с необязательным КП.

Операции – Архив. С.с. 1:М к.п. М-связной сущности обязателен, то формируется два отношения, соответствующие этим сущностям. Первичными ключами будут являться ключи этих сущностей. Причем ключ 1-связной сущности добавляется в качестве внешнего ключа в М-связную сущность.
В результате построенные предварительные отношения и их связи выглядят так:


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

Вкладчик по приходу в банк заполняет заявление, где указывает Имя и данные паспорта. Ему открывают счет (отношение Вклад), за которым закреплен какой-то номер. На этот счет вкладчик вносит денежные средства определенной купюры. С банком оговариваются вид вклада и проценты.

Далее, если вкладчик захочет сделать какую-либо операцию с вкладом (отношение Вклад_Операция), то ему нужно представить номер счета и выбрать операцию из списка (операции).

Архив должен хранить данные о закрытых вкладах. То есть о таких вкладах, к которым применена операция Закрытие. А в полученной диаграмме Архив связан с отношениями Операции и  Вклад.

Более рационально  представить зависимость Архива от отношения Вклад_Операция, поскольку в этом случае будет возможность отслеживать вклады, к которым применена операция Закрытие и которые должны поместиться в архив.
1.6 Построение конечных отношений

На основании приведенных рассуждений изменим диаграмму таким образом:


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

Вкладчики (
Investors
).

ФИО (InvestorName) – это строковый тип переменной длины VARCHAR. Для его размещения должно хватить 50 символов, поскольку  не так уж много имен, количество символов в котором превосходит 50. А тип переменной длины, поскольку размер типа будет определятся при вводе данных.

Данные паспорта (PasportSN) (первичный ключ) – возьмем Российский стандарт паспорта серия и номер. Серия состоит из 4 цифр, а номер – из 6. Плюс можно добавит пробел, чтобы разделить серию и номер. Для его хранения воспользуемся типом данных CHAR с количеством символов равным 11 (6+4+1).

Номер телефона (PhoneNumber) – тип данных CHAR. Все телефоны имеют федеральный формат номера, а именно: 8-9ХХ-ХХХ-ХХ-ХХ.

Считаем количество символов и получаем 15. Причем номер телефона должен быть уникальным для каждого вкладчика.

Вклады (
Investments
).

Рассмотрим таблицу, содержащую информацию о видах вклада:



У Сбербанка вообще много видов вкладов. Однако из всего множества были выбраны основные, так как другие предваряются словами «Плюс», «Универсал» и др.  

Теперь можно формировать информацию о типах данных.

Номер Счета (AccountNumber) (первичный ключ) – выберем формат номера счета, состоящий из 6 символов. Тогда для его размещения хватит типа INT.

Вид Вклада (Type) – тип VARCHAR. Количество символов будет равняться максимальному количеству символов вида вклада. Как видно из таблицы это 7 символов.

Вкладываемая сумма (InvSum) – тип данных INT. В таблице указано, от какой суммы возможен вклад.

Проценты (Per_Cents) – проценты вклада тип вещественный FLOAT.

Валюта вклада (Currency) – тип данных VARCHAR с количеством символов, равным 6.

Дата открытия вклада (OpenningDate) – тип данных DATETIME

Данные паспорта (PasportSN) – VARCHAR (11) -  внешний ключ, ссылающийся на отношение Вклады.
Операции (
Operations
).

Идентификатор операции (OID) (первичный ключ)  – тип данных INT, причем этот атрибут должен быть автоинкрементируем, то есть увеличиваться на 1 с каждой новой вставкой данных.

Название операции (Operation) – тип данных VARCHAR. В базе данных будут использоваться следующий операции о вкладам: пополнение (Replenishment), снятие (Withdrawals), закрытие  (Closure) и перевод средств на другой счет (Transfer). Считаем количество символов у всех названий операций и получаем 13.
Вклад_Операция (
Investment_Operation
)


Идентификатор (IOID) (первичный ключ) – автоинкрементируемый атрибут типа INT.

Оперируемая сумма (OperatedSum) – тип данных INT.

Счет места назначения (DestAccount) – тип данных INT, как и в поле AccountNumber.

Дата совершения операции (OpDate) – тип DATETIME.

Номер счета (AccountNumber) (внешний ключ на отношение Вклады) – тип INT.

Номер Операции (OID) (внешний ключ на таблицу Операции) – тип INT.

 

Архив (
Archive
)


Идентификатор архива (AID) (первичный ключ) – автоинкрементируемый атрибут типа INT.

Идентификатор (IOID) (внешний ключ на отношение Вклад_Операция)

Данные паспорта (PasportSN) (внешний ключ на отношение Вкладчики) –тип VARCHAR (11)

Дата закрытия (DateOfClosure) – тип DATETIME.
1.7 Конечная диаграмма базы данных

Теперь обобщая  все сказанное выше средствами языка запросов SQL, скрипты которого приведены в приложениях, создаем диаграмму базы данных:


Заключение
Таким образом, была построена модель базы данных Сбербанка по вкладчикам и вкладам. Метод сущность-связь был рассмотрен в конкретной предметной области, учтены возникающие спорные моменты, детально описаны степени связи и классы принадлежности, подробно расписаны атрибуты, их типы данных, английские эквиваленты и накладываемые ограничения.
Список используемой литературы


Роберт Виейра «Программирование баз данных».

Используемые интернет ресурсы:

http://www.info-system.ru/designing/methodology/er/er_theory_er.html

Приложения

Рассмотрим реализацию базы данных на языке SQL

Приложение №1 Построение отношений.

CREATE TABLE Investors

(

            IDInvestor INT NOT NULL IDENTITY,

            InvestorName VARCHAR (50) NOT NULL,

            PasportSN CHAR (11) NOT NULL,

            PhoneNumber CHAR (15) NOT NULL,

            PRIMARY KEY (PasportSN),

            UNIQUE (PhoneNumber)

)

CREATE TABLE Investments

(

            IDInvestment INT NOT NULL IDENTITY,

            AccountNumber INT NOT NULL,

            Type VARCHAR (7) NOT NULL,

            InvSum INT,

            Per_Cents FLOAT NOT NULL,

            Currency VARCHAR (6) NOT NULL,

            OpenningDate DATETIME,

            PasportSN CHAR (11) NOT NULL,

         PRIMARY KEY (AccountNumber),

            FOREIGN KEY (PasportSN) REFERENCES Investors(PasportSN)

)

CREATE TABLE Operations

(

            OID INT NOT NULL IDENTITY,

            Operation VARCHAR (15) NOT NULL,

            PRIMARY KEY (OID)

)

CREATE TABLE Investment_Operation

(

            IOID INT NOT NULL IDENTITY,

            AccountNumber INT NOT NULL,

            OID INT NOT NULL,

            OperateSum INT,

            DestAccount INT NOT NULL,

            OpDate DATETIME,

            PRIMARY KEY (IOID),

FOREIGN KEY (AccountNumber) REFERENCES Investments(AccountNumber),

            FOREIGN KEY (OID) REFERENCES Operations(OID)

)

CREATE TABLE Archive

(

            AID INT NOT NULL IDENTITY,

            IOID INT NOT NULL,

            PasportSN CHAR (11) NOT NULL,

            DateOfClosure DATETIME,

            PRIMARY KEY (AID),

            FOREIGN KEY (IOID) REFERENCES Investment_Operation(IOID),

            FOREIGN KEY (PasportSN) REFERENCES Investors(PasportSN)

)

Приложение №2. Заполнение базы данных первичными данными

Заполняем вкладчиков

INSERT INTO Investors(InvestorName,PasportSN,PhoneNumber)

VALUES ('John McCallister','6705 123876','8-922-420-52-58')

INSERT INTO Investors (InvestorName, PasportSN, PhoneNumber)

VALUES ('John Peter Rys Davis', '6708 321546', '8-933-675-23-43')

INSERT INTO Investors (InvestorName, PasportSN, PhoneNumber)

VALUES ('Ivan Safin', '6758 121543', '8-732-875-23-43')

INSERT INTO Investors(InvestorName, PasportSN, PhoneNumber)

VALUES ('Dimitriy Ozerov', '5691 324567', '8-922-320-65-64')

INSERT INTO Investors (InvestorName, PasportSN, PhoneNumber)

VALUES ('Alecsey Ivanyakov', '6103 391456', '8-546-234-13-11')

INSERT INTO Investors (InvestorName, PasportSN, PhoneNumber)

VALUES ('Andrey Verbickiy', '6870 345656', '8-970-231-11-21')

INSERT INTO Investors (InvestorName, PasportSN, PhoneNumber)

VALUES ('Ryslan Golov', '6310 342187', '8-980-514-22-36')
INSERT INTO Investors (InvestorName, PasportSN, PhoneNumber)

VALUES ('Andreas Alson', '5416 867412', '8-950-310-16-17')

Заполняем вклады

INSERT INTO Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (623613,'Deposit',10000,9.5,'Dollar','11.12.2008 17:10','6705 123876')

INSERT INTO Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (571234,'Demand',80,0.1,'Euro','12.1.2008 12:00','6103 391456')

INSERT INTO Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (417232,'Pension',1000,9.5,'Rouble','06.13.2008 15:00','5691 324567')

INSERT INTO Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (254178,'Term',5000,2.1,'Dollar','10.25.2008 14:15','6708 321546')

INSERT INTO Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (712345,'Demand',10000,0.25,'Dollar','11.27.2008 18:03','6758 121543')

INSERT INTO

Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (322145,'Pension',5000,0.25,'Rouble','12.20.2008 18:00', '6870 345656')

INSERT INTO

Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

INSERT INTO

VALUES (812345,'Deposit',15000,8.25,'Rouble','10.28.2008 17:00', '6310 342187')

INSERT INTO

Investments(AccountNumber,Type,InvSum,Per_Cents,Currency,OpenningDate,PasportSN)

VALUES (325091,'Deposit',50000,5.75,'Dollar','11.23.2008 19:30', '5416 867412'')

Заполняем Операции

INSERT INTO Operations(Operation)

VALUES ('Closure')

INSERT INTO Operations(Operation)

VALUES ('Withdrawals')

INSERT INTO Operations(Operation)

VALUES ('Transfer')

INSERT INTO Operations(Operation)

VALUES ('Replenishment')

Заполняем Вклад_Операция

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (2,254178,100,254178,'02.10.2009 15:40')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (3,417232,200,535721,'01.16.2009 12:30')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (4,571234,95,571234,'03.01.2009 11:00')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (2,623613,2000,623613,'02.03.2009 17:23')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (4,712345,10000,712345,'04.06.2009 13:40')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (1,254178,0,254178,'12.05.2009 17:40')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (1,712345,0,712345,'12.02.2009 11:24')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (1,417232,0,417232,'12.10.2009 15:33')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (2,322145,100, 322145,'06.29.2009 13:25')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (4,812345,5000, 812345,'05.28.2009 17:00')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (4,812345,5000, 812345,'05.15.2009 13:04')

INSERT INTO Investment_Operation(OID,AccountNumber,OperateSum,DestAccount,OpDate)

VALUES (3,325091,5000, 437913,'11.23.2008 19:30')

Заполняем Архив

INSERT INTO Archive (IOID,PasportSN,DateOfClosure)

VALUES (6, '6708 321546' ,'12.05.2009 17:40')

INSERT INTO Archive (IOID,PasportSN,DateOfClosure)

VALUES (7, '6758 121543' ,'12.02.2009 11:24')

INSERT INTO Archive (IOID,PasportSN,DateOfClosure)

VALUES (8, '5691 324567','12.10.2009 15:33')
Приложение №3 Триггер

При снятии средств проверка на их наличие

CREATE TRIGGER T1

ON Investment_Operation FOR INSERT

AS IF EXISTS (SELECT InvSum FROM Investments Inv

                                   JOIN INSERTED I ON Inv.AccountNumber = I.AccountNumber

                                   WHERE InvSum = 0 AND OID = 2)

BEGIN

            ROLLBACK

            PRINT 'Sorry! You can not withdraw money from an account in which there is no money'

END
Приложение №4. Представления и запросы

Представления

1. Список процентов банка по вкладам за месяц

CREATE VIEW V1

AS

SELECT AccountNumber, Per_Cents FROM Investments

            WHERE MONTH(OpenningDate) = 11

2. Количество операций по каждому вкладу за месяц

CREATE VIEW V2

AS

SELECT AccountNumber, COUNT(OID) AS 'Number of Operations' FROM Investment_Operation

            WHERE MONTH(OpDate) = 2

Запросы

1. Список закрытых вкладов за текущий месяц

SELECT AccountNumber FROM Investment_Operation

            WHERE OID = 1 AND MONTH(OpDate) = MONTH(GETDATE())

2. Количество вкладчиков по каждому виду вклада

SELECT Type, COUNT(InvestorName) AS 'Number of Investors' FROM Investors It

                        JOIN Investments Im ON It.PasportSN = Im.PasportSN

GROUP BY Type