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

          Подтип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'))