Размещено на http://www.allbest.ru/

МИНОБРНАУКИ РОССИИ

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

«Юго-западный государственный университет»

Кафедра ПО ВТ

КУРСОВАЯ РАБОТА

по дисциплине ‹‹Базы данных››

на тему ‹‹База данных организации, выпускающей сертификаты ключей электронно-цифровой подписи››

Специальность 230105 Программное обеспечение вычислительной техники и автоматизированных систем

Автор работы

Скорнякова Юлия Алексеевна

Группа ПО - 92

Руководитель работы В.Г. Белов

Курск, 2012 г.



Реферат

Объем данного курсового проекта равен 62 страницам. В курсовом проекте содержится два приложения, в которых приведены ER-схемы базы данных, код программы. Также в КП содержится постановка задачи.

Ключевые слова:

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

Объект разработки:

Объектом исследования в данной работе является проектирование базы данных в соответствии с заданным вариантом.

Цель работы:

Спроектировать «Базу данных организации, выпускающей сертификаты ключей электронно-цифровой подписи».

Метод проведения работы:

Использование ER-диаграмм, минимизация зависимостей, построение запросов.



Содержание

Определения

Обозначения и сокращения

• Введение
• 1. Техническое задание
• 1.1 Основание для разработки
• 1.2 Назначение разработки
• 1.3 Стандарты
• 1.4 Требования к программе или программному изделию
• 1.4.2 Носители информации
• 1.4.3 Требования к составу и параметрам технических средств
• 2. Технический проект
• 2.1 Описание предметной области
• 2.2 Словарь понятий и терминов
• 2.3 Определение ключей и связей
• 2.4 Определение функциональных зависимостей
• 2.5 Синтез схемы базы данных на основании функциональных зависимостей
• 2.5.1 Построение неизбыточного покрытия
• 2.5.2 Построение леворедуцированного покрытия
• 2.5.3 Построение праворедуцированного покрытия
• 2.5.4 Построение классов эквивалентности
• 2.5.5 Построение редуцированного минимального кольцевого покрытия
• 2.6 Построение запросов на SQL
• 2.6.1 Запрос на выборку
• Заключение
• Библиографический список
• Приложение А
• Приложение Б

• Определения
• В данной работе применены следующие термины с соответствующими определениями:
• База данных: Файлы, снабженные описанием хранимых в них данных и находящиеся под управлением СУБД.
• er-диаграмма: Схема базы данных, спроектированная с помощью программы ERWin.
• Сущность: Любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных.
• Атрибут: Поименованная характеристика сущности.
• Ключ: Минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности.
• Связь: Ассоциирование двух или более сущностей.
• Обозначения и сокращения
• В данной работе применены следующие сокращения:
• БД: База данных
• СУБД: Система управления базами данных
• ФЗ: Функциональная зависимость

Введение

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

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

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

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

1. Техническое задание

1.1 Основание для разработки

база данное зависимость эквивалентность

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

1.2 Назначение разработки

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

1.3 Стандарты

Данная работа оформлена в соответствии со стандартом СТУ 04.02.030.-2008.

1.4 Требования к программе или программному изделию

В программе требуется реализовать учет клиентов: организаций (тип организации, название, адрес, номер телефона), индивидуальных предпринимателей и физических лиц. Также необходимо определить, на какое лицо будет выпущен сертификат: на руководителя организации (или самого ИП) (фамилия, имя отчество, серия и номер паспорта, адрес проживания, телефонный номер, банковские реквизиты, ИНН, КПП, Регистрационный номер ПФР, ОГРН) или уполномоченное лицо (в этом случае необходимо к общим данным указать номер доверенности). Клиент также отправляет заявление в электронной форме, которое в дальнейшем сохраняется под своим номером. Сервисный центр заключает с клиентом договор (номер договора, дата заключения) на сумму, зависящую от указанного тарифа. Затем сервисный центр обязан предоставить клиенту счёт (номер счёта, дата оплаты). Обе стороны также обязаны подписать акт сдачи-приёмки (номер акта, дата). Выдача сертификата происходит исключительно при условии выполнения предыдущих требований, а также клиент обязан предоставить в главный офис организации, выпускающей сертификаты следующие документы: копию выписки из ЕГРЮЛ или ЕГРИП (в зависимости от типа клиента).

1.4.1 Результирующие компоненты изделия

База данных создана в визуальной среде разработки систем управления реляционными базами данных Navicat Premium.

1.4.2 Носители информации

Продукт должен размещаться в виде файлов на CD дисках. В процессе работы могут использоваться Flash, CD-R/-RW, DVD-R/RW или другие носители информации.

1.4.3 Требования к составу и параметрам технических средств

Программный комплекс должен корректно работать на компьютере со следующими техническими характеристиками (минимальные требования):

? процессор Pentium V 2200 МГц;

? оперативная память объемом 1024 Мб;

? жесткий диск Seagate Barracuda, объемом 120 Гб;

? видеоадаптер SVGA;

? клавиатура;

? манипулятор типа “мышь”;

устройство удаленного доступа к сети: модем или сетевая карта.

2. Технический проект

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

В сервис-центре должна храниться информация о клиентах, пакете услуг и сертификатах. Под «клиентом» подразумевается организация, индивидуальный предприниматель или физическое лицо. Однако, если клиент не является ИП или физическим лицом, сертификат выпускается не на организацию, а на её представителя: руководителя или уполномоченное лицо. Также для получения сертификата необходимы данные о предоставлении в главный офис необходимых документов. В пакет услуг включены: заявление, договор, акт сдачи-приёмки, и счёт. Все они имеют свой персональный номер. Размер оплаты зависит от выбранного тарифа. Сертификат выпускается после получения документов, подписания акта сдачи-приёмки, договора и оплаты счёта клиентом. Длительность действия сертификата - один год с момента выпуска.

2.2 Словарь понятий и терминов

Словарь понятий и терминов приведен в таблицах 2.1 - 2.12.

Таблица 2.1 - Клиенты

Атрибут	Смысл	Пример
TypeClients	Тип клиента	organisation



Таблица 2.2 - Сведения об организации

Атрибут	Смысл	Пример
type	Тип организации	ОАО
name	Название организации	«Роснедвижимость»
code_str	Код страны	+7
code_gor	Код города	4712
num_tel	Номер телефона	523512
strana	Страна	Россия
oblast	Область	Курская
gorod	Город	Курск
ylizza	Улица	Ленина
dom	Дом	47
INN	ИНН (Идентификационный номер налогоплательщика)	1253647956
KPP	КПП (Код причины постановки на учёт)	853694000
OGRN	ОГРН (Основной государственный регистрационный номер)	1356268320014
CodeIFNS	Код ИФНС (коды Инспекций Федеральной Налоговой Службы)	4601
RegNumberPFR	Регистрационный номер пенсионного фонда РФ	325012122
CodePodrazdFSS	Код подразделения	7800
ReestrNumberStrFSS	Реестровый номер страховат еля	1356247892
NazvPodrazd	Название подразделения	Администрация
EGRUL	Наличие выписки из ЕГРЮЛ (единого государственного реестра юридических лиц)	есть
typepredstavit	Тип представителя	rykovod_org

Таблица 2.3 - Сведения о руководителе организации

Атрибут	Смысл	Пример
Surname	Фамилия	Иванов
Name	Имя	Иван
Patronymic	Отчество	Иванович
Ser_Pasp	Серия паспорта	3805
Num_Pasp	Номер паспорта	363170
Kem_Vid	Кем выдан	ОМ №4 УВД г. Курска
Date_Vid	Дата выдачи	05.05.2010
telephone	Телефон	89611356999
fax	Факс	758966
e-mail	e-mail	ivanov@yandex.ru

Таблица 2.4 - Сведения об уполномоченном представителе организации

Атрибут	Смысл	Пример
Surname	Фамилия	Иванов
Name	Имя	Иван
Patronymic	Отчество	Иванович
Ser_Pasp	Серия паспорта	3805
Num_Pasp	Номер паспорта	363170
Kem_Vid	Кем выдан	ОМ №4 УВД г. Курска
Date_Vid	Дата выдачи	05.05.2010
telephone	Телефон	89611356999
fax	Факс	758966
e-mail	e-mail	ivanov@yandex.ru
NumDoveren	Номер доверенности	17

Таблица 2.5 - Сведения об индивидуальном предпринимателе

Атрибут	Смысл	Пример
Surname	Фамилия	Иванов
Name	Имя	Иван
Patronymic	Отчество	Иванович
Ser_Pasp	Серия паспорта	3805
Num_Pasp	Номер паспорта	363170
Kem_Vid	Кем выдан	ОМ №4 УВД г. Курска
Date_Vid	Дата выдачи	05.05.2010
telephone	Телефон	89611356999
fax	Факс	758966
e-mail	e-mail	ivanov@yandex.ru
INN	ИНН (Идентификационный номер налогоплательщика)	1253647966
EGRIP	Наличие выписки из ЕГРИП (единого государственного реестра индивидуальных предпринимателей)	есть



Таблица 2.6 - Сведения о доверенном лице индивидульного предпринимателя

Атрибут	Смысл	Пример
Surname	Фамилия	Иванов
Name	Имя	Иван
Patronymic	Отчество	Иванович
Ser_Pasp	Серия паспорта	3805
Num_Pasp	Номер паспорта	363170
Kem_Vid	Кем выдан	ОМ №4 УВД г. Курска
Date_Vid	Дата выдачи	05.05.2010
telephone	Телефон	89611356999
fax	Факс	758966
e-mail	e-mail	ivanov@yandex.ru
INN	ИНН (Идентификационный номер налогоплательщика)	1253647966
EGRIP	Наличие выписки из ЕГРИП (единого государственного реестра индивидуальных предпринимателей)	есть
NumDoveren	Номер доверенности	132

Таблица 2.7 - Сведения о физическом лице

Атрибут	Смысл	Пример
Surname	Фамилия	Иванов
Name	Имя	Иван
Patronymic	Отчество	Иванович
Ser_Pasp	Серия паспорта	3805
Num_Pasp	Номер паспорта	363170
Kem_Vid	Кем выдан	ОМ №4 УВД г. Курска
Date_Vid	Дата выдачи	05.05.2010
telephone	Телефон	89611356999
fax	Факс	758966
e-mail	e-mail	ivanov@yandex.ru
INN	ИНН (Идентификационный номер налогоплательщика)	1253647966
EGRIP	Наличие выписки из ЕГРИП (единого государственного реестра индивидуальных предпринимателей)	есть



Таблица 2.8 - Банковские реквизиты

Атрибут	Смысл	Пример
RashetChet	Расчётный счёт	12345678912345678936
CorespChet	Корреспондентский счёт	22236325947365982145
BIC	БИК	526975364
NazvBank	Название банка	ЗАО МКБ МОСКОМПРИВАТБАНК
GorBank	Адрес (город)	Москва

Таблица 2.9 - Акт сдачи-приёмки

Атрибут	Смысл	Пример
numberA	Номер	32141234454
date	Дата	04.05.2011

Таблица 2.10 - Договор

Атрибут	Смысл	Пример
date	Дата	04.05.2011
numberD	Номер	123/m234/s12

Таблица 2.11 - Заявление

Атрибут	Смысл	Пример
date	дата	09.11.2011
numberZ	номер	123456654321

Таблица 2.12 - Счёт

Атрибут	Смысл	Пример
numberS	Номер	09.11.2011
DateOpl	Дата оплаты	06.01.2010

Таблица 2.13 - Тарифы

Атрибут	Смысл	Пример
title	Название тарифа	«Diadoc»
sum	Сумма	9000



Таблица 2.14 - Сертификат

Атрибут	Смысл	Пример
startDate	Дата выдачи	04.05.2011
finishDate	До какого числа действителен	04.05.2012
titlesert	Наименование	Иванов Иван Иванович

2.3 Определение ключей и связей

Организация, выпускающая сертификаты ЭЦП предоставляет свои услуги клиентам: Clients, которые направляют в сервисный центр заявление на изготовление сертификата: zayavlenie (idzayavlenie, date, numberZ). Первичным ключом в таблице zayavlenie является idzayavlenie. Затем клиенты подписывают договор с сервисным центром: dogovor (iddogovor, date, numberD). Первичным ключом в таблице dogovor является iddogovor. На следующем шаге клиенты получают и оплечивают счёт: schet (idschet, idtarif, numberS, Date_Opl) в соответствии с выбранным тарифом: tarif (idtarif, title, sum). Первичным ключом в таблице schet является idschet, вторичным - idtarif; в таблице tarif первичный ключ - idtarif. После этого клиентами подписывается акт сдачи-приемки: aktsdpr (idaktsdpr, numberA, date). Первичным ключом в таблице aktsdpr является idaktsdpr. После оплаты и с учётом всех вышеперечисленных документов клиенты получают сертификат: sertificat (idsertificat , startDate , finishDate , titlesert , idtarif , iddogovor , idaktsdpr , idzayvlenie , idschet ). Первичный ключ - idsertificat , вторичные: idtarif , iddogovor , idaktsdpr , idzayvlenie , idschet . Всем клиентам должны принадлежать индивидуальные банковские реквизиты: banki (idbanki, RashetChet, CorespChet, BIC, NazvBank, GorBank). Первичный ключ в таблице banki - idbanki). Следовательно, в таблице Clients (idclients, TypeClients, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki) первичным ключом является idclients, а вторичными: idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki. Клиенты подразделяются на: организации (organization), индивидуальных предпринимателей (ip) и физические лица (fizlic). Помимо своих атрибутов эти таблицы перенимают родительские. Таким образом, в таблице organization (idorganisation, type, name, code_str, code_gor, num_tel, strana, oblast, gorod, ylizza, dom, INN, KPP, OGRN, CodeIFNS, RegNumberPFR, CodePodrazdFSS, NazvPodrazdFSS, EGRUL, typepredstavit, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients) первичный ключ - idorganisation, вторичные: idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients; в таблице ip (idip, Surname, Name, Patronymic, Ser_Pasp, Num_Pasp, Kem_Vid, Date_Vid, telephone, fax, e-mail, INN, EGRIP, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients) первичный ключ - idip, вторичные: idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients); в таблице fizlic(idfizlic, Surname, Name, Patronymic, Ser_Pasp, Num_Pasp, Kem_Vid, Date_Vid, telephone, fax, e-mail, INN, EGRIP, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients) первичный ключ - idfizface, вторичные: idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients). От ИП сертификат может получать уполномоченное лицо: ypolnomlicip(idypolnomlicip, Surname, Name, Patronymic, Ser_Pasp, Num_Pasp, Kem_Vid, Date_Vid, telephone, fax, e-mail, INN, EGRIP, NumDoveren, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients). Первичный ключ в таблице ypolnomlicip - idypolnomlicip, вторичные: idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients. От организации сертификат может получать как руководитель (rykovodorg), так и уполномоченное лицо (ypolnlicorg). В таблице rykovodorg (idrykovod_org, Surname, Name, Patronymic, Ser_Pasp, Num_Pasp, Kem_Vid, Date_Vid, telephone, fax, e-mail, idorganisation, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients) первичный ключ - idrykovod_org, вторичные: idorganisation, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients; В таблице ypolnlicorg (idypolnlicorg, Surname, Name, Patronymic, Ser_Pasp, Num_Pasp, Kem_Vid, Date_Vid, telephone, fax, e-mail, NumDoveren, idorganisation, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients) первичный ключ - idypolnlicorg, вторичные: idorganisation, idaktsdpr, idzayavlenie, iddogovor, idschet, idtarif, idsertificat, idbanki, idclients.

На рисунке 2.1 приведена ER-схема, отображающая структуру базы данных и связи.

Рисунок 2.1 - ER-схема базы данных

2.4 Определение функциональных зависимостей

Один и тот же расчётный счёт не может одновременно принадлежать двум и более клиентам.

RaschetShet, KorrespShet, BIC -> clients.

Одно и то же наименование сертификата не может одновременно принадлежать двум и более клиентам.

titlesert -> clients.

Один и тот же номер заявления не может одновременно принадлежать двум и более клиентам.

numberZ -> clients.

Один и тот же номер акта сдачи-приёмки не может одновременно принадлежать двум и более клиентам.

numberA -> clients.

Один и тот же номер договора не может одновременно принадлежать двум и более клиентам.

numberD -> clients.

Один и тот же номер счёта не может одновременно принадлежать двум и более клиентам.

numberS -> clients.

Один и тот же ИНН ИП не может одновременно принадлежать двум и более клиентам.

INNip -> clients.

Один и тот же ИНН уполномоченного лица ИП не может одновременно принадлежать двум и более клиентам.

INNyip -> clients.

Один и тот же ИНН физического лица не может одновременно принадлежать двум и более клиентам.

INNfl -> clients.

Один и тот же ИНН организации не может одновременно принадлежать двум и более клиентам.

INNorg -> clients.

Одни и те же паспортные данные руководителя организации не могут одновременно принадлежать двум и более клиентам.

SerPaspr, Num_Paspr->clients

Одни и те же паспортные данные уполномоченного лица организации не могут одновременно принадлежать двум и более клиентам.

SerPaspy, Num_Paspy->clients.

Один и тот же номер счёта не может соответствовать двум и более наименованиям тарифа.

NumberS->title.

Одному клиенту не может соответствовать два и более наименования тарифа

clients->title.

Исходные функциональные зависимости представлены на рисунке 2.2.

RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients numberS -> clients INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title clients -> title

Рисунок 2.2 - Исходные функциональные зависимости

2.5 Синтез схемы базы данных на основании функциональных зависимостей

2.5.1 Построение неизбыточного покрытия

Этапы построения неизбыточного покрытия представлены на рисунке 2.3.

Построение неизбыточного покрытия.

Неизбыточное покрытие g: RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients numberS -> clients INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title clients -> title

Рисунок 2.3 - Построение неизбыточного покрытия

2.5.2 Построение леворедуцированного покрытия

Этапы построения леворедуцированного покрытия представлены на рисунке 2.4. Построение леворедуцированного покрытия.

Леворедуцированное покрытие g: RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients numberS -> clients INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title clients -> title

Рисунок 2.4 - Построение леворедуцированного покрытия

2.5.3 Построение праворедуцированного покрытия

Этапы построения праворедуцированного покрытия представлены на рисунке 2.5.

Построение праворедуцированного покрытия.

Праворедуцированное покрытие g: RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients numberS -> clients INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title clients -> title

Рисунок 2.5 - Построение праворедуцированного покрытия

2.5.4 Построение классов эквивалентности

Этапы построения классов эквивалентности представлены на рисунках 2.6 - 2.8.

Построение классов эквивалентности.



1. Ef( RaschetShet, KorrespShet, BIC ): RaschetShet, KorrespShet, BIC -> clients 2. Ef( titlesert ): titlesert -> clients 3. Ef( numberZ ): numberZ -> clients 4. Ef( numberA ): numberA -> clients 5. Ef( numberD ): numberD -> clients 6. Ef( numberS ): numberS -> clients 7. Ef( INNip ): INNip -> clients 8. Ef( INNyip ): INNyip -> clients 9. Ef( INNfl ): INNfl -> clients

Рисунок 2.6 - Построение классов эквивалентности

10. Ef( INNorg ): INNorg -> clients 11. Ef( SerPaspr, Num_Paspr ): SerPaspr, Num_Paspr -> clients 12. Ef( SerPaspy, Num_Paspy ): SerPaspy, Num_Paspy -> clients 13. Ef( NumberS ): NumberS -> title 14. Ef( clients ): clients -> title Построение минимального покрытия на основе прямой функциональной определяемости Редуцированное минимальное покрытие g: RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients numberS -> clients

Рисунок 2.7 - Построение классов эквивалентности

INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title clients -> title

Рисунок 2.8 - Построение классов эквивалентности

2.5.5 Построение редуцированного минимального кольцевого покрытия

Определение CF-зависимостей по классам эквивалентности представлено на рисунках 2.9 - 2.11.

Построение кольцевого покрытия.



Минимальное кольцевое покрытие ( RaschetShet, KorrespShet, BIC; ) -> clients ( titlesert; ) -> clients ( numberZ; ) -> clients ( numberA; ) -> clients ( numberD; ) -> clients ( numberS; ) -> clients ( INNip; ) -> clients ( INNyip; ) -> clients ( INNfl; ) -> clients ( INNorg; ) -> clients ( SerPaspr, Num_Paspr; ) -> clients ( SerPaspy, Num_Paspy; ) -> clients ( NumberS; ) -> title ( clients; ) -> title Получение кольцевого минимального редуцированного покрытия Естественное характеристическое множество для кольцевого покрытия f(C): RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients numberS -> clients INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title

Рисунок 2.9 - Построение редуцированного минимального кольцевого покрытия



clients -> title Минимальное редуцированное кольцевое покрытие C ( RaschetShet, KorrespShet, BIC; ) -> clients ( titlesert; ) -> clients ( numberZ; ) -> clients ( numberA; ) -> clients ( numberD; ) -> clients ( numberS; ) -> clients ( INNip; ) -> clients ( INNyip; ) -> clients ( INNfl; ) -> clients ( INNorg; ) -> clients ( SerPaspr, Num_Paspr; ) -> clients ( SerPaspy, Num_Paspy; ) -> clients ( NumberS; ) -> title ( clients; ) -> title Естественное характеристическое множество f(C): RaschetShet, KorrespShet, BIC -> clients titlesert -> clients numberZ -> clients numberA -> clients numberD -> clients

Рисунок 2.10 - Построение редуцированного минимального кольцевого покрытия



numberS -> clients INNip -> clients INNyip -> clients INNfl -> clients INNorg -> clients SerPaspr, Num_Paspr -> clients SerPaspy, Num_Paspy -> clients NumberS -> title clients -> title R0 = ( RaschetShet, KorrespShet, BIC, clients ) K0 = { RaschetShet, KorrespShet, BIC } R1 = ( titlesert, clients ) K1 = { titlesert } R2 = ( numberZ, clients ) K2 = { numberZ } R3 = ( numberA, clients ) K3 = { numberA } R4 = ( numberD, clients ) K4 = { numberD } R5 = ( numberS, clients ) K5 = { numberS } R6 = ( INNip, clients ) K6 = { INNip } R7 = ( INNyip, clients ) K7 = { INNyip } R8 = ( INNfl, clients ) K8 = { INNfl } R9 = ( INNorg, clients ) K9 = { INNorg } R10 = ( SerPaspr, Num_Paspr, clients ) K10 = { SerPaspr, Num_Paspr } R11 = ( SerPaspy, Num_Paspy, clients ) K11 = { SerPaspy, Num_Paspy } R12 = ( NumberS, title ) K12 = { NumberS } R13 = ( clients, title ) K13 = { clients }

Рисунок 2.11 - Построение редуцированного минимального кольцевого покрытия

2.6 Построение запросов на SQL

2.6.1 Запрос на выборку

Словесная формулировка запроса

Перечислить все наименования сертификатов, действующих с 01.09.2011, учитывая: а) номер заявления должен находиться в промежутке от 500 до 800; б) номер договора должен быть меньше 700; в) номер счёта должен находиться в промежутке от 400 до 900; г) номер акта сдачи-примки должен находиться в промежутке от 300 до 1500; д) наименование тарифа - «Diadoc».

Текст запроса на языке SQL

Текст запроса на языке SQL представлен на рисунке 2.12.

SELECT zayavlenie.numberZ, dogovor.numberD, sertificat.startDate, schet.numberS, tarif.title, aktsdpr.date from sertificat JOIN zayavlenie on zayavlenie.idzayavlenie = sertificat.idzayvlenie JOIN dogovor on dogovor.iddogovor = sertificat.iddogovor JOIN schet on schet.idschet = sertificat.idschet JOIN tarif on tarif.idtarif = schet.idtarif JOIN aktsdpr on aktsdpr.idaktsdpr = sertificat.idaktsdpr WHERE (zayavlenie.numberZ >500 AND zayavlenie.numberZ<800) AND dogovor.numberD <700 AND (aktsdpr.numberA >300 AND aktsdpr.numberA<1500) AND (schet.numberS >400 AND schet.numberS<900) AND sertificat.startDate = '2011-09-01' AND tarif.title = 'Diadoc'

Рисунок 2.12 - Текст запроса на языке SQL

Исходное дерево запроса

Исходное дерево для запроса представлено на рисунке 2.13.



Рисунок 2.13- Исходное дерево запроса

Расчет сложности запроса

Расчет сложности исходного запроса приведен ниже.

, (2.1)

где - результат естественного соединения таблиц;

- таблицы, участвующие в соединении;

- псевдонимы, используемые вместо имен таблиц для сокращения записи;

- операция естественного соединения.

, (2.2)

2.3)

где - число записей в таблице ;

- число записей в таблице С;

- число записей в таблице O;

- число различных значений в столбце таблицы zayavlenie;

- число различных значений в столбце таблицы sertificat.

,

,

,

.

, (2.4)

, (2.5)

где - число блоков, записанных в таблице ;

- длина одной записи таблицы в блоках;

- длина одной записи таблицы в байтах;

- размер одного блока.

Очевидно, что

, (2.6)

где - объем в байтах поля ;

- объем в байтах поля ;

,

,

.

.

.

.

, (2.7)

,

,

,

,

. (2.8)

.

Очевидно, что

, (2.9)

,

,

,

.

.

.

, (2.10)

,

,

,

,

. (2.11)

.

Очевидно, что

, (2.12)

,

,

,

.

.

.

, (2.13)

,

,

,

,

. (2.14)

.

Очевидно, что

, (2.15)

,

,

,

.

.

.

, (2.16)

,

,

,

,

. (2.17)

.

Очевидно, что

, (2.18)

,

,

,

.

.

.

(2.19)

где - операция селекции с логической формулой .

Формула имеет вид:

, (2.20)

,

, (2.21)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

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

Окончательно имеем:

, (2.22)

.

Оптимизированное дерево запроса на основании операции селекции

Оптимизированное дерево для запроса представлено на рисунке 2.14.



Рисунок 2.14 - Оптимизированное дерево запроса

Расчет сложности оптимизированного запроса на основании селекции

Расчет сложности оптимизированного запроса на основании селекции приведён ниже.

, (2.23)



где - операция селекции с логической формулой .

Формула имеет вид:

, (2.24)

,

,

, (2.25)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.26)

где - операция селекции с логической формулой .

Формула имеет вид:

, (2.27)

,

,

, (2.28)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.29)

где - результат естественного соединения таблиц;

- таблицы, участвующие в соединении;

- псевдонимы, используемые вместо имен таблиц для сокращения записи;

- операция естественного соединения.

, (2.30)

(2.31)

,

,

,

.

, (2.32)

, (2.33)

где - число блоков, записанных в таблице ;

- длина одной записи таблицы в блоках;

- длина одной записи таблицы в байтах;

- размер одного блока.

Очевидно, что

, (2.34)

где - объем в байтах поля ;

- объем в байтах поля ;

,

,

.

.

.

.

, (2.35)

где - операция селекции с логической формулой .

Формула имеет вид:

, (2.36)

,

,

, (2.37)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.38)

,

,

,

,

, (2.39)

.

Очевидно, что

, (2.40)

,

,

,

.

.

.

, (2.41)

где - операция селекции с логической формулой .

Формула имеет вид:

, (2.42)

,

,

, (2.43)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.44)

,

,

,

,

, (2.36)

.

Очевидно, что

, (2.45)

,

,

,

.

.

.

, (2.46)

где - операция селекции с логической формулой .

Формула имеет вид:



, (2.47)

,

,

, (2.48)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.49)

,

,

,

,

, (2.50)

.

Очевидно, что

, (2.51)

,

,

,

.

.

.

, (2.52)

где - операция селекции с логической формулой .

Формула имеет вид:

, (2.53)

,

,

, (2.54)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.55)

,

,

,

,

, (2.56)

.

Очевидно, что

, (2.57)

,

,

,

.

.

.

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

Окончательно имеем:

, (2.58)

.

Оптимизированное дерево запроса на основании операции проекции

Оптимизированное дерево запроса на основании операции проекции представлено на рисунке 2.15.



Рисунок 2.15 - Оптимизированное дерево запроса на основании операции проекции

На рисунке 2.16 представлены результаты Xi, i.



X1={sertificat.startDate, sertificat.idtarif, sertificat.iddogovor, sertificat.idaktsdpr, sertificat.idzayavlenie, sertificat.idschet}. X2={zayavlenie.idzayavlenie, zayavlenie.numberZ}. X3={sertificat.startDate, sertificat.idrarif, sertificat.iddogovor, sertificat.idaktsdpr, sertificat.idschet, zayavlenie.numberZ }. X4={dogovor.iddogovor, dogovor.numberD}. X5={ sertificat.startDate, sertificat.idrarif, sertificat.idaktsdpr, sertificat.idschet, zayavlenie.numberZ, dogovor.numberD }. X6={schet.idschet, schet.numberS}. X7={ sertificat.startDate, sertificat.idrarif, sertificat.idaktsdpr, zayavlenie.numberZ, dogovor.numberD, schet.numberS}. X8={aktsdpr.idaktsdpr, aktsdpr.numberA}. X9={ sertificat.startDate, sertificat.idrarif , zayavlenie.numberZ, dogovor.numberD, schet.numberS , aktsdpr.numberA }. X10={tarif.idtarif, tarif.title}. X11={ sertificat.startDate, zayavlenie.numberZ, dogovor.numberD, schet.numberS, aktsdpr.numberA, tarif.title }.

Рисунок 2.16

Расчет сложности запроса

Расчет сложности оптимизированного запроса приведен ниже.

, (2.59)

где X1={sertificat.startDate, sertificat.idtarif, sertificat.iddogovor, sertificat.idaktsdpr, sertificat.idzayavlenie, sertificat.idschet};

, (2.60)

где - операция селекции с .

, (2.61)

,

,

, (2.62)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.63)

где X2={zayavlenie.idzayavlenie, zayavlenie.numberZ};

, (2.64)

где - операция селекции с .

, (2.65)

,

,

, (2.66)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.67)



где - результат естественного соединения таблиц;

- таблицы, участвующие в соединении;

- псевдонимы, используемые вместо имен таблиц для сокращения записи;

- операция естественного соединения.

, (2.68)

(2.69)

,

,

,

.

, (2.70)

, (2.71)

где - число блоков, записанных в таблице ;

- длина одной записи таблицы в блоках;

- длина одной записи таблицы в байтах;

- размер одного блока.

Очевидно, что

, (2.72)

где - объем в байтах поля ;

- объем в байтах поля ;



,

,

.

.

.

.

, (2.73)

где X3={sertificat.startDate, sertificat.idrarif, sertificat.iddogovor, sertificat.idaktsdpr, sertificat.idschet, zayavlenie.numberZ };

, (2.74)

где X4={dogovor.iddogovor, dogovor.numberD};

, (2.75)

где - операция селекции с .

, (2.76)

,

,

, (2.77)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.78)

,

,

,

,

, (2.79)

.

Очевидно, что

, (2.80)

,

,

,

.

.

.

, (2.81)

где X5={ sertificat.startDate, sertificat.idrarif, sertificat.idaktsdpr, sertificat.idschet, zayavlenie.numberZ, dogovor.numberD };

, (2.82)

где X6={schet.idschet, schet.numberS};

, (2.83)

где - операция селекции с .

, (2.84)

,

,

, (2.85)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.86)

,

,

,

,

, (2.87)

.

Очевидно, что

, (2.37)

,

,

,

.

.

.

, (2.88)

где X7={ sertificat.startDate, sertificat.idrarif, sertificat.idaktsdpr, zayavlenie.numberZ, dogovor.numberD, schet.numberS};

, (2.89)

где X8={aktsdpr.idaktsdpr, aktsdpr.numberA};

, (2.90)

где - операция селекции с .

, (2.91)

,

,

, (2.92)

.

Так как операция селекции не изменяет структуру таблицы, то

,

.

, (2.93)

,

,

,

,

, (2.94)

.

Очевидно, что

, (2.95)

,

,

,

.

.

.

, (2.96)

где X9={ sertificat.startDate, sertificat.idrarif , zayavlenie.numberZ, dogovor.numberD, schet.numberS , aktsdpr.numberA };

, (2.97)

где X10={tarif.idtarif, tarif.title};

, (2.44)

где - операция селекции с .

, (2.98)

,

,

, (2.99)

.

Так как операция селекции не изменяет структуру таблицы, то



,

.

, (2.100)

,

,

,

,

, (2.101)

.

Очевидно, что

, (2.102)

,

,

,

.

.

.

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

Окончательно имеем:

, (2.103)

.



Заключение

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

Для достижения цели был решен ряд задач: составление описания предметной области; составление словаря понятий и терминов; построение исходной модели (ER-диаграммы) базы данных; определение функциональных зависимостей; синтез схемы базы данных на основании функциональных зависимостей; построение оптимизированной концептуальной модели; построение запросов на выборку.

Библиографический список

1. Атре, Ш., Структурный подход к организации баз данных. / [Текст] : Атре, Ш. - М.: Финансы и статистика, 1983. - 320 с.

2. Бойко, В.В., Проектирование баз данных информационных систем. / [Текст] : Бойко, В.В., Савинков, В.М., - М.: Финансы и статистика, 1989. - 351 с.

3. Гарсия-Молина, Гектор, Ульман, Джеффри, Д., Уидом, Дженнифер Системы баз данных. Полный курс. : Пер. с англ. - М. : Издательский дом “Вильямс”, 2003. - 188 с. : ил.

4. Дейт, К., Руководство по реляционной СУБД DB2. / [Текст]: Дейт, К. - М.: Финансы и статистика, 1988. - 320 с.

5. Джексон, Г., Проектирование реляционных баз данных для использования с микроЭВМ. / [Текст] : Джексон, Г., -М.: Мир, 1991. - 252 с.

6. Кириллов, В.В., Структуризованный язык запросов (SQL). / [Текст] : Кириллов, В.В., - СПб.: ИТМО, 1994. - 80 с.

7. Мартин, Дж., Планирование развития автоматизированных систем. / [Текст] : Мартин, Дж., - М.: Финансы и статистика, 1984. - 196 с.

8. Мейер, М., Теория реляционных баз данных. / [Текст] : Мейер, М.,- М.: Мир, 1987. - 608 с.

9. Тиори, Т., Проектирование структур баз данных. / [Текст] : Тиори, Т., Фрай, Дж., В 2 кн., - М.: Мир, 1985. Кн. 1. - 287 с.: Кн. 2. - 320 с.



Приложение А

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

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using MySql.Data.MySqlClient; namespace WindowsFormsApplication2 {public partial class Form1 : Form {public Form1() {InitializeComponent();} private void button1_Click(object sender, EventArgs e) {///Руководитель - таблица string connectionString = "Database=sert;Data Source=localhost; User Id=root;Password=''"; MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); DateTime startDate = DateTime.Now; for (int i = 1; i <= 1000000; i++) {string commandText = "insert into zayavlenie (idzayavlenie, date, numberZ) VALUES("+i.ToString()+",'2012-04-02',"+i.ToString()+")"; MySqlCommand command = new MySqlCommand(commandText, connection); command.ExecuteNonQuery(); string commandText1 = "insert into dogovor (iddogovor, date, numberD) VALUES(" + i.ToString() + ",'2008-01-01'," + i.ToString() + ")"; MySqlCommand command1 = new MySqlCommand(commandText1, connection); command1.ExecuteNonQuery(); string commandText2 = "insert into schet (idschet, idtarif, DateOpl, numberS) VALUES(" + i.ToString() +"," +i.ToString()+",'2008-01-01'," + i.ToString() + ")"; MySqlCommand command2 = new MySqlCommand(commandText2, connection); command2.ExecuteNonQuery(); string commandText3 = "insert into aktsdpr (idaktsdpr, date, numberA) VALUES(" + i.ToString() + ",'2008-01-01'," + i.ToString() + ")"; MySqlCommand command3 = new MySqlCommand(commandText3, connection); command3.ExecuteNonQuery();

Рисунок А.1

string commandText4 = "insert into tarif (idtarif, title, sum) VALUES(" + i.ToString() + ",'Diadoc'," + "9000" + ")"; MySqlCommand command4 = new MySqlCommand(commandText4, connection); command4.ExecuteNonQuery(); string commandText5 = "insert into sertificat (idsertificat, startDate, finishDate, titlesert, idtarif, iddogovor, idaktsdpr, idzayvlenie, idschet) VALUES(" + i.ToString() + ",'2008-01-01'," + "'2012-04-02'," + "'title'," + i.ToString() + "," + i.ToString() + "," + i.ToString()+","+ i.ToString() + "," + i.ToString() + ")"; MySqlCommand command5 = new MySqlCommand(commandText5, connection); command5.ExecuteNonQuery(); } DateTime finishDate = DateTime.Now; double resultDate = finishDate.Subtract(startDate).TotalSeconds; connection.Close(); MessageBox.Show("На всё ушло: "+resultDate.ToString()); } } }

Рисунок А.2



Приложение Б

Тексты запросов на языке SQL

Текст исходного запроса на языке SQL представлен на рисунке Б1.

SELECT zayavlenie.numberZ, dogovor.numberD, sertificat.startDate, schet.numberS, tarif.title, aktsdpr.date from sertificat JOIN zayavlenie on zayavlenie.idzayavlenie = sertificat.idzayvlenie JOIN dogovor on dogovor.iddogovor = sertificat.iddogovor JOIN schet on schet.idschet = sertificat.idschet JOIN tarif on tarif.idtarif = schet.idtarif JOIN aktsdpr on aktsdpr.idaktsdpr = sertificat.idaktsdpr WHERE (zayavlenie.numberZ >500 AND zayavlenie.numberZ<800) AND dogovor.numberD <700 AND (aktsdpr.numberA >300 AND aktsdpr.numberA<1500) AND (schet.numberS >400 AND schet.numberS<900) AND sertificat.startDate = '2011-09-01' AND tarif.title = 'Diadoc'

Рисунок Б1- Текст исходного запроса на языке SQL

Текст запроса, оптимизированного на основании селекции, на языке SQL представлен на рисунке Б2.

SELECT sertificat.startDate, sertificat.titlesert from sertificat JOIN (SELECT * from zayavlenie WHERE (numberZ>500 AND numberZ<800)) as t1 on t1.idzayavlenie = sertificat.idzayvlenie JOIN (SELECT * from dogovor where numberD < 700) as t2 on sertificat.iddogovor = t2.iddogovor JOIN (SELECT * from schet where( numberS > 400 AND numberS <900)) as t3 on sertificat.idschet = t3.idschet JOIN (SELECT * from aktsdpr where( numberA > 300 AND numberA <1500)) as t4 on sertificat.idaktsdpr = t4.idaktsdpr JOIN (SELECT * from tarif where title='Diadoc') as t5 on sertificat.idtarif = t5.idtarif where startDate='2011-09-01'

Рисунок Б2- Текст запроса, оптимизированного на основании селекции, на языке SQL



Текст запроса, оптимизированного на основании проекции, объединённой с селекцией, на языке SQL представлен на рисунке Б3.

SELECT sertificat.startDate, sertificat.titlesert from sertificat JOIN (SELECT idzayavlenie, numberZ from zayavlenie WHERE (numberZ>500 AND numberZ<800)) as t1 on t1.idzayavlenie = sertificat.idzayvlenie JOIN (SELECT iddogovor, numberD from dogovor where numberD < 700) as t2 on sertificat.iddogovor = t2.iddogovor JOIN (SELECT idschet, numberS from schet where( numberS > 400 AND numberS <900)) as t3 on sertificat.idschet = t3.idschet JOIN (SELECT idaktsdpr, numberA from aktsdpr where( numberA > 300 AND numberA <1500)) as t4 on sertificat.idaktsdpr = t4.idaktsdpr JOIN (SELECT idtarif, title from tarif where title='Diadoc') as t5 on sertificat.idtarif = t5.idtarif where startDate='2011-09-01'

Рисунок Б3- Текст запроса, оптимизированного на основании проекции, объединённой с селекцией, на языке SQL представлен на рисунке

Размещено на Allbest.ru