Разработать по заданному описанию предметной области концептуальную модель данных малого банка (в виде ER-диаграммы)

Данный оператор имеет видINSERTINTO[ ONLY ] {имя_таблицыимя_представления}[ (столбец[,…]) ]{ (DEFAULT VALUES — VALUES (скалярное_выражение [,…]) -предложение_SELECT);имя_таблицы{ имя_таблицыимя_представления} [ (столбец[,…]) ]Объявляет обновляемую целевую таблицу или представление, в которое будут вставляться данные. Список столбцов можно не указывать, если данныебудут вставляться во все столбцы и их порядок соответствует порядку столбцов в описании таблицы. DEFAULT VALUESВ таблицу вставляются все значения, определяемые параметром DEFAULTстолбца (значения по умолчанию), если таковые существуют, в противномслучае, вставляются пустые значения (NULL).VALUES (скалярное_выражение[,…])Указываются скалярные_выражения, значения которых будут вставлятьсяв целевую таблицу. Количество вставляемых скалярных_выражений должно в точности совпадать с количеством столбцов в списке столбцов. Более того, их значения должны быть совместимы по типу и размеру со столбцами целевой таблицы. Каждое скалярное_выражение в списке соответствует столбцув списке столбцов с тем же порядковым номером. Таким образом, данные изпервого скалярного_выражения попадают в первый столбец, данные из второго — во второй и т. д. до тех пор, пока все столбцы не будут заполнены. При желании можно использовать ключевое слово DEFAULT, чтобы вставитьв столбец значение, заданное по умолчанию, или NULL, чтобы вставить пустоезначение.

скалярное_выражение

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

предложение_SELECTСтроки, извлекаемые данным предложением SELECT, вставляются в целевую таблицу или представление. Значения, извлекаемые SELECT, должны прямо соответствовать столбцам, указанным в списке столбцов. Произведем заполнение созданных таблиц данными. Таблица CLIENTINSERT INTO dbo. client VALUES (1,0)INSERT INTO dbo. client VALUES (2,0)INSERT INTO dbo. client VALUES (3,0)INSERT INTO dbo. client VALUES (4,0)INSERT INTO dbo. client VALUES (5,0)INSERT INTO dbo. client VALUES (6,1)INSERT INTO dbo. client VALUES (7,1)INSERT INTO dbo. client VALUES (8,1)INSERT INTO dbo. client VALUES (9,1)INSERT INTO dbo. client VALUES (10,1)Таблица TYPE_ORGINSERTINTOdbo.type_orgVALUES (1,N'Государственное бюджетное учреждение')INSERT INTO dbo. type_org VALUES (2,N'Государственное казенное учреждение')INSERT INTO dbo. type_orgVALUES (3,N'Частноепредприятие')INSERT INTO dbo. type_orgVALUES (4,N'Кооператив')INSERT INTO dbo. type_orgVALUES (5,N'Закрытоеакционерноеобщество')INSERT INTO dbo. type_orgVALUES (6,N'Открытоеакционерноеобщество')INSERT INTO dbo. type_org VALUES (7,N'Общество с ограниченной ответственностью')Таблица CLIENT_PHIZINSERT INTO [dbo]. client_phiz] VALUES (1,N'Физическоелицо 1', N’Адресфизическоголица 1', 1 111 111 111, N'М', cast ('1970;01−01 00:00:

00.000' AS datetime))INSERT INTO [dbo]. client_phiz] VALUES (2,N'Физическоелицо 2', N’Адресфизическоголица 2', 1 111 111 111, N'М', cast ('1970;05−01 00:00:

00.000' AS datetime))INSERT INTO [dbo]. client_phiz] VALUES (3,N'Физическоелицо 3', N’Адресфизическоголица 3', 1 111 111 111, N'М', cast ('1980;01−01 00:00:

00.000' AS datetime))INSERT INTO [dbo]. client_phiz] VALUES (4,N'Физическоелицо 4', N’Адресфизическоголица 4', 1 111 111 111, N'М', cast ('1990;01−02 00:00:

00.000' AS datetime))INSERT INTO [dbo]. client_phiz] VALUES (5,N'Физическоелицо 5', N’Адресфизическоголица 5', 1 111 111 111, N'М', cast ('1991;12−01 00:00:

00.000' AS datetime))Таблица CLIENT_YURINSERT INTO [dbo]. client_yur] VALUES (6,1,N'Юридическоелицо 1', N'Юридическийадрес 1', 100) INSERT INTO [dbo]. client_yur] VALUES (7,2,N'Юридическоелицо 2', N'Юридическийадрес 2', 100) INSERT INTO [dbo]. client_yur] VALUES (8,5,N'Юридическоелицо 3', N'Юридическийадрес 3', 500) INSERT INTO [dbo]. client_yur] VALUES (9,3,N'Юридическоелицо 4', N'Юридическийадрес 4', 20) INSERT INTO [dbo]. client_yur] VALUES (10,7,N'Юридическоелицо 5', N'Юридическийадрес 5', 1000) ТаблицаYUR_SIGNINSERT INTO [dbo]. yur_sign] VALUES (6, N’Иванов

ИванИванович', N’паспотр', N'0315 124 567 890', N'ФМС 900−00')INSERT INTO [dbo]. yur_sign] VALUES (6, N’Петрова

ИринаВикторовна', N’паспорт', N'0315 222 222 222', N'ФМС 900−00')ТаблицаACCOUNTSET DATEFORMAT ymdINSERT INTO [dbo]. account] VALUES (1, N'11 111 111 111 111 110 656', 10 000.

00, cast ('2015;04−01 00:00:

00.000' AS datetime), NULL) INSERT INTO [dbo]. account] VALUES (2, N'22 222 222 222 222 221 312', 20 000.

00, cast ('2015;04−02 00:00:

00.000' AS datetime), NULL) INSERT INTO [dbo]. account] VALUES (3, N'33 333 333 333 333 331 968', 30 000.

00, cast ('2015;04−03 00:00:

00.000' AS datetime), NULL) INSERT INTO [dbo]. account] VALUES (6, N'66 666 666 666 666 663 936', 2 000 000.

00, cast ('2015;04−14 00:00:

00.000' AS datetime), NULL) INSERT INTO [dbo]. account] VALUES (6, N'66 666 666 666 666 663 936', 3 000 000.

00, cast ('2015;04−14 00:00:

00.000' AS datetime), NULL) INSERT INTO [dbo]. account] VALUES (6, N'66 666 666 666 666 663 936', 5 000 000.

00, cast ('2015;01−14 00:00:

00.000' AS datetime), cast ('2015;04−15 00:00:

00.000' AS datetime)) INSERT INTO [dbo]. account] VALUES (7, N'77 777 777 777 777 770 496', 10 000 000.

00, cast ('2015;02−01 00:00:

00.000' AS datetime), cast ('2015;04−15 00:00:

00.000' AS datetime)) INSERT INTO [dbo]. account] VALUES (7, N'77 777 777 777 777 770 496', 20 000 000.

00, cast ('2015;04−01 00:00:

00.000' AS datetime), NULL) Таким образом, содержание таблиц после добавления записей имеет вид: Account11111111111111111111110000,2 015;01−04 00:00:

00.000NULL22222222222222222222220000,2 015;02−04 00:00:

00.000NULL33333333333333333333330000,2 015;03−04 00:00:

00.000NULL6666666666666666666662000000,2 015;04−14 00:00:

00.000NULL6666666666666666666673000000,2 015;04−14 00:00:

00.000NULL6666666666666666666685000000,2 015;01−14 00:00:

00.2 015;04−15 00:00:

00.77 777 777 777 777 780 888 109 580 288,002015;02−01 00:00:

00.2 015;04−15 00:00:

00.77 777 777 777 777 780 888 109 580 288,002015;04−01 00:00:

00.000NULLNULLNULLNULLNULLNULLClient102030405061718191101Client_phiz1Физическое лицо 1Адрес физического лица 11 111 111 111М1970;01−01 00:00:

00.0002

Физическое лицо 2Адрес физического лица 21 111 111 111М1970;01−05 00:00:

00.0003

Физическое лицо 3Адрес физического лица 31 111 111 111М1980;01−01 00:00:

00.0004

Физическое лицо 4Адрес физического лица 41 111 111 111М1990;02−01 00:00:

00.0005

Физическое лицо 5Адрес физического лица 51 111 111 111М1991;01−12 00:00:

00.000NULLNULLNULLNULLNULLNULLClient_yur61Юридическое лицо 1Юридический адрес 110 072

Юридическое лицо 2Юридический адрес 210 085

Юридическое лицо 3Юридический адрес 350 093

Юридическое лицо 4Юридический адрес 420 107

Юридическое лицо 5Юридический адрес 51000Type_orgYur_sign6Иванов Иван Ивановичпаспотр0315 124 567 890ФМС 900−006Петрова Ирина Викторовнапаспорт0315 222 222 222ФМС 900−00NULLNULLNULLNULLNULLПОСТРОЕНИЕ ЗАПРОСОВФункции языков DML определяются первым словом в предложении (часто называемом запросом), которое почти всегда является глаголом. В случае с SQL эти глаголы — «SELECT» («выбрать»), «INSERT» («вставить»), «UPDATE» («обновить»), и «DELETE» («удалить»). DML содержит операторы, представленные в таблице 7: Таблица 3 — ОператорыDMLОператор

СмыслДействиеDELETEУдалить строки

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

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

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

Оператор, позволяющий сформировать результирующее отношение, соответствующее запросу

Синтаксис оператора SELECT имеет вид: SELECT [ALL — DISTINCT] <�список_выбора> FROМ<�имя_таблицы>…[ WHERE<�условие> ] [ GROUP ВУ<�имя_столбца>,… ]; [ HAVING<�условие> ] [ORDERВY<�имя_столбца> [ASC — DESC],… ]Ключевое слово ALL означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям ^ запроса. Значит, в результирующий набор могут попасть одинаковые строки. И это нарушение принципов теории отношений (в отличие от реляционной алгебры, где по умолчанию предполагается отсутствие дубликатов в каждом результирующем отношении). Ключевое слово DISTINCT означает, что в результирующий набор включаются только различные строки, то есть дубликаты строк результата не включаются в набор. В разделе FROM задается перечень исходных отношений (таблиц) запроса. Варианты запросов:

Сколько открытых сберегательных счетов у банка? SELECT COUNT (*) AS 'Кол-вооткрытыхсчетов' FROM account WHERE account_dateclose IS NULLРис. 4. Результат выполнения запроса

Какиесберегательные счета были закрыты юр. лицами за последний год и каким организациям они принадлежат? LEFT OUTER JOINclient_phiz ON account. client_code = client_phiz.client_code LEFT OUTER JOIN client_yur ON account. client_code = client_yur.client_code WHERE account_dateclose IS NOT NULL AND YEAR (account_dateclose) = YEAR (GETDATE ())Рис. 5. Результат выполнения запроса

Каков общий баланс на всех счетах организации Б? SELECT SUM (account.balance) AS 'Баланс'FROM account JOIN client ON account. client_code=.client.client_code JOIN client_yur ON account. client_code = client_yur.client_code WHERE client_yur.yur_name LIKE 'Юридическоелицо 1'Рис. 6. Результат выполнения запроса

Какие счета принадлежат физическому лицу А? SELECT SUM (account.balance) AS 'Баланс'FROM account JOIN client ON account. client_code=.client.client_code JOIN client_phiz ON account. client_code = client_phiz.client_code WHERE client_phiz.phiz_fioLIKE 'Физическоелицо 1'Рис. 7. Результат выполнения запроса

Каков средний баланс по счетам каждой из организаций с числом служащих большим 100? Вывести информацию в порядке убывания среднего значения. SELECT AVG (account.balance) AS 'Среднийбаланс'FROM account JOIN client_yur ON account. client_code = client_yur.client_code WHERE client_yur.yur_employees > 100GROUP BY client_yur.client_codeORDER BY 1 DESCРис. 8. Результат выполнения запроса

Каков процент сберегательных счетов, с балансом выше $ 1000?SELECT convert (numeric (5,2), count (*)) /(SELECT convert (numeric (5,2), count (*)) FROM account)*100 FROM account WHERE Balance > 1000

Рис. 9. Результат выполнения запроса

Какой максимальный баланс на счете среди всех счетов физических лиц и какому клиенту принадлежит этот счет? SELECT account. balance, c2. phiz_fio FROM client_phiz c2 JOIN account ON account. client_code = c2. client_code WHERE account. balance = (SELECT MAX (account.balance) FROM account JOIN client ON client. client_code=account.client_code WHERE client. client_type=0)Рис. 10. Результат выполнения запроса

ЗАКЛЮЧЕНИЕ

В результате проделанной работы были разработаны графическое представление ER-диаграммы и реляционная модель малого банка в полном соответствии с поставленным заданием. В СУБД MSSQLбыла создана база данных и разработанные таблицы, составлены SQLзапросы. СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫВиейра Р. Программирование баз данных MicrosoftSQLServer 2008 для профессионалов.: пер. с англ. — М.: ООО «И.Д.Вильямс», 2010. -

1072 с. Дейт К. Дж. SQL и реляционная теория. Как грамотно писать код на SQL, — пер. с англ. — Спб.: Символ-Плюс, 2010. — 480 с. Ицик

Бен-Ган. Microsoft SQL Server. Основы T-SQL.: пер. с англ.

Спб.: БХВ — Петербург, 2009 — 432 с. Дейт К. Дж.

Введение

в системы баз данных: пер. с англ. — М.: Издательский дом «Вильямс», 2001 — 1072 с. Кириллов В.

В.

Введение

в реляционные базы данных / В. В. Кириллов, Г. Ю.

Громов. — СПб.: БХВ — Петербург, 2009. — 464 с. Ржеуцкая С. Ю. Базы данных. Язык SQL: учеб. пособие — Вологда: ВоГТУ, 2010 — 159 с.